Multi-Value parameter syntax error

I am new to reporting services...
I've created a report that uses a multi-value parameter based ona dataset
usign a simple select statement. It works fine when a single value is
selected, but when multiple values are selected returns the error Incorrect
Syntax near ','.
How can the report code be modified to pass multiple values with the correct
syntax?See my response to your other posting (which is basically, show us what you
did). My guess is you have an incorrect SQL statement OR you are not going
against SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:7342117C-9715-477F-A9F4-E748C2EA3A77@.microsoft.com...
>I am new to reporting services...
> I've created a report that uses a multi-value parameter based ona dataset
> usign a simple select statement. It works fine when a single value is
> selected, but when multiple values are selected returns the error
> Incorrect
> Syntax near ','.
> How can the report code be modified to pass multiple values with the
> correct
> syntax?
>

Multi-Value Parameter Problem

I'm creating a report that selects data from one table. I'm trying to
add a parameter that will allow the user to select the data based on a
selection from one column (SpindleName). I've added this column in the
report parameters, checked the multi-value box, set the available
values to "from query" to the dataset & field, set the default values
to "from query" to the same dataset & field. When I execute the query,
I get asked for the SpindleName, but the box contains no values other
than "NULL" and "BLANK". If I type the spindle name into the box, I
get the results. Why isn't the list being populated with the values
from the spindlename column ?
Here is the query:
SELECT StationName, SpindleName, PartId, TimeStamp
FROM dbo.Readings
WHERE SpindleName IN (@.SpindleName)
I'm using SQL 2005 Express & VS 2005
Any advice would be appreciated.
Thank-you
JeffWhat is the source query for the parameter?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||I may be wrong but it seems to me there is some confusion here. Several
things:
1. Unless you have cascading parameters (the list to select from parameter B
is dependent on the selection from parameter A) then you should have a query
that returns the value you want to select from.
2. Default means a single value. If you point to a dataset with multiple
values I don't know what it does except that is incorrect.
So, for instance by my take on this you want to this.
Have two datasets. One dataset that returns the list of SpindleName. The
other that retrieves the data you desire.
Select distinct SpindleName from Readings
Set both your label and value for the SpindleName parameter to the field
SpindleName from the above query.
Then your second query you have below will return what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||For your point #2, if your parameter's default value points to a dataset
("From Query") with multiple values, they will show up with all of them
selected in the drop down list. This is desirable in some cases.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23P5CZRGrIHA.4376@.TK2MSFTNGP06.phx.gbl...
>I may be wrong but it seems to me there is some confusion here. Several
>things:
> 1. Unless you have cascading parameters (the list to select from parameter
> B is dependent on the selection from parameter A) then you should have a
> query that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvica1@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>> I'm creating a report that selects data from one table. I'm trying to
>> add a parameter that will allow the user to select the data based on a
>> selection from one column (SpindleName). I've added this column in the
>> report parameters, checked the multi-value box, set the available
>> values to "from query" to the dataset & field, set the default values
>> to "from query" to the same dataset & field. When I execute the query,
>> I get asked for the SpindleName, but the box contains no values other
>> than "NULL" and "BLANK". If I type the spindle name into the box, I
>> get the results. Why isn't the list being populated with the values
>> from the spindlename column ?
>> Here is the query:
>> SELECT StationName, SpindleName, PartId, TimeStamp
>> FROM dbo.Readings
>> WHERE SpindleName IN (@.SpindleName)
>> I'm using SQL 2005 Express & VS 2005
>> Any advice would be appreciated.
>> Thank-you
>> Jeff
>|||On May 2, 10:53=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I may be wrong but it seems to me there is some confusion here. Several
> things:
> 1. Unless you have cascading parameters (the list to select from parameter= B
> is dependent on the selection from parameter A) then you should have a que=ry
> that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvi...@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>
> > I'm creating a report that selects data from one table. I'm trying to
> > add a parameter that will allow the user to select the data based on a
> > selection from one column (SpindleName). I've added this column in the
> > report parameters, checked the multi-value box, set the available
> > values to "from query" to the dataset & field, set the default values
> > to "from query" to the same dataset & field. When I execute the query,
> > I get asked for the SpindleName, but the box contains no values other
> > than "NULL" and "BLANK". If I type the spindle name into the box, I
> > get the results. Why isn't the list being populated with the values
> > from the spindlename column ?
> > Here is the query:
> > SELECT StationName, SpindleName, PartId, TimeStamp
> > FROM dbo.Readings
> > WHERE SpindleName IN (@.SpindleName)
> > I'm using SQL 2005 Express & VS 2005
> > Any advice would be appreciated.
> > Thank-you
> > Jeff- Hide quoted text -
> - Show quoted text -
Thanks Bruce - it worked perfectly!
Regards
Jeff

Multi-Select Error, easy question

I am trying to show/hide a table based on a multi-select parameter in VS 2005
beta 2.
In the parameter I have the following:
Data Type: String
Multi-value - checked
Label: General Info
Value: GeneralInfo
In the properties for the report I have the following under Visibility:
=IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
When I execute the report and choose the only parameter value I get the
following. (once I get this to work I will add additional parameters)
Error:
Processing Error
"The Hidden expression for the table 'Table_Header' contains an error:
Overload resolution failed because no Public '=' can be called with these
arguments:
Public Shared Operator =(a As String, b As String) As Boolen':
Argument matching parameter 'a' connot convert from 'Object()' to 'String'.
Thanks!!!
--
Thank You!Once you mark a parameter as "multi-value", the .Value property will return
an object[] with all selected values. If only one value is selected, it will
be an object array of length = 1. Object arrays cannot be directly compared
with Strings.
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used
e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
>I am trying to show/hide a table based on a multi-select parameter in VS
>2005
> beta 2.
> In the parameter I have the following:
> Data Type: String
> Multi-value - checked
> Label: General Info
> Value: GeneralInfo
> In the properties for the report I have the following under Visibility:
> =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> When I execute the report and choose the only parameter value I get the
> following. (once I get this to work I will add additional parameters)
>
> Error:
> Processing Error
> "The Hidden expression for the table 'Table_Header' contains an error:
> Overload resolution failed because no Public '=' can be called with these
> arguments:
> Public Shared Operator =(a As String, b As String) As Boolen':
> Argument matching parameter 'a' connot convert from 'Object()' to
> 'String'.
> Thanks!!!
> --
> Thank You!|||Robert, thanks my man, thanks for taking the time to respond. I'll try this
out first thing tomorrow.
Thanks!
Shane
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> To access individual values of a multi value parameter you can use
> expressions like this:
> =Parameters!MVP1.IsMultiValue
> boolean flag - tells if a parameter is defined as multi value
> =Parameters!MVP1.Count
> returns the number of values in the array
> =Parameters!MVP1.Value(0)
> returns the first selected value
> =Join(Parameters!MVP1.Value)
> creates a space separated list of values
> =Join(Parameters!MVP1.Value, ", ")
> creates a comma separated list of values
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Robert, thanks for your reply. Very valuable information for this report and
for my future reports.
You wrote, "Object arrays cannot be directly compared with strings." Do you
know how I could do this 'indirectly'?
Let's say the user selects 'GeneralInfo' and 'Contact Info' in the
multi-select. Do you know of a way to evaluate their selection so I can take
action on it? (such as visability)
Thanks again for your help.
Shane
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> To access individual values of a multi value parameter you can use
> expressions like this:
> =Parameters!MVP1.IsMultiValue
> boolean flag - tells if a parameter is defined as multi value
> =Parameters!MVP1.Count
> returns the number of values in the array
> =Parameters!MVP1.Value(0)
> returns the first selected value
> =Join(Parameters!MVP1.Value)
> creates a space separated list of values
> =Join(Parameters!MVP1.Value, ", ")
> creates a comma separated list of values
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Hi Robert, don't worry about replying again. I think I figured it out. I
need to split it after I join it, right?
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> To access individual values of a multi value parameter you can use
> expressions like this:
> =Parameters!MVP1.IsMultiValue
> boolean flag - tells if a parameter is defined as multi value
> =Parameters!MVP1.Count
> returns the number of values in the array
> =Parameters!MVP1.Value(0)
> returns the first selected value
> =Join(Parameters!MVP1.Value)
> creates a space separated list of values
> =Join(Parameters!MVP1.Value, ", ")
> creates a comma separated list of values
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Yes. The Join() will create a string from a multi dimensional object array.
The Split() function is the inverse function; it splits the string into a
multi dimensional array.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
news:05BDA0D3-9D01-4C62-A844-99FE9F2BD791@.microsoft.com...
> Hi Robert, don't worry about replying again. I think I figured it out. I
> need to split it after I join it, right?
> --
> Thank You!
>
> "Robert Bruckner [MSFT]" wrote:
>> Once you mark a parameter as "multi-value", the .Value property will
>> return
>> an object[] with all selected values. If only one value is selected, it
>> will
>> be an object array of length = 1. Object arrays cannot be directly
>> compared
>> with Strings.
>> To access individual values of a multi value parameter you can use
>> expressions like this:
>> =Parameters!MVP1.IsMultiValue
>> boolean flag - tells if a parameter is defined as multi value
>> =Parameters!MVP1.Count
>> returns the number of values in the array
>> =Parameters!MVP1.Value(0)
>> returns the first selected value
>> =Join(Parameters!MVP1.Value)
>> creates a space separated list of values
>> =Join(Parameters!MVP1.Value, ", ")
>> creates a comma separated list of values
>> =Split("a b c", " ")
>> to create a multi value object array from a string (this can be used
>> e.g. for drillthrough parameters, subreports, or query parameters)
>> See also MSDN:
>> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
>> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
>> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
>> >I am trying to show/hide a table based on a multi-select parameter in VS
>> >2005
>> > beta 2.
>> >
>> > In the parameter I have the following:
>> > Data Type: String
>> > Multi-value - checked
>> > Label: General Info
>> > Value: GeneralInfo
>> >
>> > In the properties for the report I have the following under Visibility:
>> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
>> >
>> > When I execute the report and choose the only parameter value I get the
>> > following. (once I get this to work I will add additional parameters)
>> >
>> >
>> > Error:
>> > Processing Error
>> > "The Hidden expression for the table 'Table_Header' contains an error:
>> > Overload resolution failed because no Public '=' can be called with
>> > these
>> > arguments:
>> > Public Shared Operator =(a As String, b As String) As Boolen':
>> > Argument matching parameter 'a' connot convert from 'Object()' to
>> > 'String'.
>> >
>> > Thanks!!!
>> >
>> > --
>> > Thank You!
>>

Multiple/optional parameters

Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

Hello John,

Yes it can be done in a single report but you would have to create an extra parameter and do all the logic in the Stored procedure.

1.) Create an extra parameter in the Report Parameters called ParamYear as Datatype string.

2.) In the available values select Non-Queried and enter FisacalYear in Label and Value and also the CalenderYear.

3.) In the stored Procedure the logic would be like this:

If @.Parm = fisicalyear

Begin

End

Else

Begin

End

Hope this helps..

|||

I'm running all my queries against the cube but I guess the logic would be similar.

I should include another parameter and the first thing they select is Fiscal or Calender and then the proper hirarchies are offered to pick from?

|||It depends on the hirarchies. You can set the default value which one you want first and follow on. In the SP it gets the value for the one it selected. so hirarchies doesn't matter I believe.|||Can you hide a parameter list and then display one parameter or the other depending on the data from the first parameter?|||

Yes , it is possible. I personally did not do it but this link might be helpful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087731&SiteID=1

John, I have this saved in my Code-Sample database and this was answered in the same forum.

This is possible. I'll give an example using the AdventureWorks db. Suppose you want to have a report with all the contacts. The first filter on your report will filter on the Title column, the second will filter on LastName. When a user selected a Title in the dropdownlist, the dropdownlist will be filled with all the LastName's that have the same Title as the one selected.

Here is how to do this:

1.) create a dataset 'TITLES' with the following sql statement:
SELECT DISTINCT Title FROM Person.Contact ORDER BY Title

2.) create a second dataset 'LASTNAMES' with the following sql statement:
SELECT DISTINCT LastName FROM Person.Contact WHERE Title = @.Title

3.)create a third dataset 'CONTACTDETAILS' with the following sql statement:
SELECT * FROM Person.Contact WHERE Title = @.Title AND LastName = @.LastName

4.) Go to the Layout tab and open the \Report\Report Parameters menu

Normally you will see two parameters: Title and LastName. Make sure that Title is the first parameter.

Select the Title parameter and set the following settings:
Available values\From Query\DataSet = TITLES
Available values\From Query\Value Field = Title
Available values\From Query\Label Field = Title

Select the LastName parameter and set the following settings:
Available values\From Query\DataSet = LASTNAMES
Available values\From Query\Value Field = LastName
Available values\From Query\Label Field = LastName

Click OK to leave the Report Parameters menu.

5.)Drag a table on your report and add fields from the CONTACTDETAILS dataset to it.

If everything went well and you run your report, you will first need to select a value for the Title filter. After that, the LastName filter will be filled based on the value selected from the Title filter. When selecting a name in there, the report will be shown.

|||

I'm not quite doing the same thing.

I want a report that will drop down a list of fiscal year/months or calender year/months depending upon user input. I've thought about creating a report with 2 subreports, one a fiscal year report and one a calender year report. The subreports will be toggled from the main report. I can default to one and let the user toggle between reports. No parameters will be passed, the user will make appropriate selection in the subreport.

Multiple while fetch cursor code

I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or?

What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.

For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @.instrumentlinje remains empty.

If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument" statement doesn't execute.

DELETE FROM ALL_tbl_instrumentkoder

DECLARE @.medlem int
DECLARE @.instrument varchar(10)
DECLARE @.instrumentlinje varchar(150)

DECLARE medlemmer_cursor CURSOR FOR
SELECT medlemsnummer
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

DECLARE instrumenter_cursor CURSOR FOR
SELECT [MCPS Kode]
FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

OPEN medlemmer_cursor

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

WHILE @.@.FETCH_STATUS = 0
BEGIN

OPEN instrumenter_cursor
FETCH NEXT FROM instrumenter_cursor INTO @.instrument

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument
FETCH NEXT FROM instrumenter_cursor INTO @.instrument
END

CLOSE instrumenter_cursor

INSERT INTO ALL_tbl_instrumentkoder VALUES(@.medlem, @.instrumentlinje)

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

END

CLOSE medlemmer_cursor
DEALLOCATE medlemmer_cursor
DEALLOCATE instrumenter_cursorWell, I suspect the problem is related to referencing a variable in your cursor definition, but you shouldn't be using a cursor anyway.

Here is a simpler (non-cursor) method:

First, create this function:
create function dbo.instrumentlinje(@.medlem int)
returns varchar(4000) as
begin
declare @.instrumentlinje
select @.instrumentlinje = isnull(@.instrumentlinje + ' ', '') + MCPS Kode
from Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

return @.instrumentlinje
end

Then, run this code:
insert into ALL_tbl_instrumentkoder
(medlem,
instrumentlinje)
select medlemsnummer,
dbo.instrumentlinje(medlem)
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

Warning! Not tested for syntax errors, and you may need to edit object ownership.|||Basically the same way I did it in Access.. just a greenhorn when it comes to SQL-server.

Thanks man :-)|||TSQL is similar to Access SQL, though there are a few syntactical differences. The concept of avoiding cursors and loops in favor of set-based operations is the same, though.

Multiple updates on multiple conditions?

Hi,

I'm looking for a way, if possible, to update a field to different values based on different criteria. For example, Field X should be A if condition 1 is true, it should be B if condition 2 is true, etc. I'm not sure if there's a way to use a CASE in an UPDATE statement?

Thanks

Maybe something like:

update urTable
set X = case when condition_1 = target1 then A
when condition_2 = target2 then B
else C
end
where filterColumn = someCondition

Multiple Transaction Logs

I have just come from an Oracle background and am trying to equate SQL server methodologies to Oracle. I have a couple of questions based on the same principle which is the usage of multiple transaction logs. This is what I have been able to find out from the docs and other posts:

If you have multiple transaction logs they will all be used in a sequential manner before being wrapped around. That is, they will all be filled before they are reused.

My first question is will they be used sequentially or concurrently. SQL Server seems to stripe everything so I am inclined to believe the latter. This question has been asked before but from other posts/docs there seems to be a difference of opinion. I am of course equating transaction logs to redo/archived logs so I want to know categorically (links would be greatly appreciated) as it will have a huge impact on any disaster and recovery plans that I implement.

The second question is a spin off and may or may not be relevant dependant on the answer of the first. If the transaction logs are written to concurrently, that is, entries are striped, why bother with multiple logs? I don't see any benefits. In fact, in a recovery scenario recovering transactions from mutliple striped logs would appear to adversely affect MTTR.

Feel free to point out the errors of my ways as I am all ears and eager to learn.

Thanks in advance.My advice is to get the poop straight from the horse (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_3t2d.asp).

I'd also recommend two books, the first is the SQL 2000 Resource Kit (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=lL5zATKAxC&isbn=0735612668&itm=2) which has a great chapter on Oracle to MS-SQL, along with a lot of other VERY useful information. The second is Inside Microsoft SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=lL5zATKAxC&isbn=0735609985&itm=1).

Peruse a bit, then come back... I'm sure that you'll still have questions, you'll just be able to form them a bit better!

-PatP|||OK, found the answer, I think. The logs are used sequentially. In case anybody else wants the answer, when there are multiple log files, the first one fills up, then the logical log moves on to the second physical logfile etc. When the last physical log is filled up it then wraps around to the beginning of the first physical log again. This is the best description that I have found and it implies, but does not say categorically confirm that if you have declared two logfiles in the create database statement, i.e.

log on
(name='test1_log1',
filename='E:\SQLServer\test1\Logs\test1_log1.LDF',
size=5mb,
maxsize=10mb),
(name='test1_log2',
filename='E:\SQLServer\test1\Logs\test1_log2.LDF',
size=5mb,
maxsize=10mb)

test1_log1 would be used, then test1_log2 before wrapping around back to test1_log1.

I had actually researched this issue quite considerably before I posted this question, including reading 3 SQLServer books. The purpose and use of the log is described, but not this issue. If you look at the following post you will can see that there is a difference of opinion where some think they are used concurrently and some think that they are used sequentially:

http://www.mcse.ms/showthread.php?threadid=1184072&perpage=10&pagenumber=1

I tried to imply this before when I posted the thread. My main problem was that folks couldn't agree between themselves how they were used so I was hoping for some clarification.

Thans for responding anyway.|||No problemo!

I've never seen an instance where more than one log file was ever in use at one time. Theoretically there is a tiny window when two adjacent (sequential) log files might be active for the duration of one DML statement, but I've never seen that happen in the real world.

I've found lots of discussions of log files, and how people think those files are used. I've never seen anything to contradict any of the sources that I sited in my previous posting. I don't believe that there is any way to have more than one log file active for a given database at one time, beyond the tiny window theoretically allowed by the log sequencing mechanism.

-PatP|||That was the reason why I was looking into it. All of the configurations we have here have one log, but the docs say you can specify more than one. In Oracle more than one redo log group is mandatory and the reason is quite straight forward. In SQL Server the transaction log can expand, so one of the main reasons for having more than one is eliminated. I was wondering why you would want more than one, other than the lack of disk space to allow it to expand or manual duplexing (which obviously not the case as I have discovered). I was thinking that it may provide more flexibility in a recovery scenario or maybe performance reasons when it comes to truncating and backing up the transaction log(s). I have see some posts where this action can sometimes adversely effect user performance.

Maybe I am digging a bit too deep too early in the process, and I'm sure everything will become clear with the passage of time, but any pointers on this subject would still be appreciated.|||There are probably a gazillion different reasons for allowing multiple log files, but the one that I've hit most frequently is when someone sets up a database (and of course its log file too) on a drive that is "bigger than they'll ever need". Through experience, I've discovered that only infinity is really infinity... For one reason or another, you eventually use "more than you'll ever need" in almost every case! When the database consumes all of the available disk, you can create a new log file on a different disk drive in order to get enough breathing room to fix the underlying problem.

I'm sure that there are lots of other reasons, but that one alone is enough to justify the functionality to me.

There is a lot of information within BOL (SQL Server Books Online), but the real meaty stuff is in the books that I cited in my previous posting. Kalen has forgotten more about indexing than I'll ever know, and the Resource Kit ought to be required reading for someone with your technical background... It gets into the "care and feeding" that an Oracle DBA is accustomed to, explains which things are important to SQL Server and which are irrelevant.

If you are in a major metro area, it doesn't hurt to look for a user group. Even if you aren't in a major metro, check out PASS (http://www.sqlpass.org) for more insight.

-PatP|||That's the conclusion I was coming to. I appreciate the input. I'm coming up to speed quite nicely on the fundamentals of SQLServer but still receptive to any direction that might facilitate this process more easily. Thanks.|||Sure,

SQL Server is simply easier than Oracle...

Notice I didn't say better...

The multiple Redo Logs allow for some pretty substantial amount of transactions...

In all cases I've ever seen in SQL Server, ther's only 1..

I dump tranny logs every 10 minutes in some environments...so in that cas etheres more than 1...I guess that's where the confusion comes in...

Redo Logs are like partitioned logs...which sql server doesn't have...I don't think (And you know, that happens waaaay to much)|||From Books Online:

Transaction Logs
A database in Microsoft SQL Server 2000 has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.

SQL Server uses the transaction log of each database to recover transactions. The transaction log is a serial record of all modifications that have occurred in the database as well as the transaction that performed each modification. The transaction log records the start of each transaction. It records the changes to the data and enough information to undo the modifications (if necessary later) made during each transaction. For some large operations, such as CREATE INDEX, the transaction log instead records the fact that the operation took place. The log grows continuously as logged operations occur in the database.

The transaction log records the allocation and deallocation of pages and the commit or rollback of each transaction. This allows SQL Server either to apply (roll forward) or back out (roll back) each transaction in the following ways:

A transaction is rolled forward when you apply a transaction log. SQL Server copies the after image of every modification to the database or reruns statements such as CREATE INDEX. These actions are applied in the same sequence in which they originally occurred. At the end of this process, the database is in the same state it was in at the time the transaction log was backed up.

A transaction is rolled back when you back out an incomplete transaction. SQL Server copies the before images of all modifications to the database since the BEGIN TRANSACTION. If it encounters transaction log records indicating that a CREATE INDEX was performed, it performs operations that logically reverse the statement. These before images and CREATE INDEX reversals are applied in the reverse of their original sequence.
At a checkpoint, SQL Server ensures that all transaction log records and database pages modified are written to disk. During the recovery process of each database that occurs when SQL Server is restarted, a transaction must be rolled forward only when it is not known whether all the data modifications in the transaction were actually written from the SQL Server buffer cache to disk. Because a checkpoint forces all modified pages to disk, it represents the point at which the startup recovery must start rolling forward transactions. Because all pages modified before the checkpoint are guaranteed to be on disk, there is no need to roll forward anything done before the checkpoint.

Transaction log backups enable you to recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure. Transaction log backups should be a consideration in your media recovery strategy. For more information,|||Data devices are written to in concurrent fashion (if more than 1 is defined), while log devices are ... well, that was already mentioned. There is a very good reason to have multiple small sized trx log files defined for servers with 5+ user databases and 2+ disk arrays dedicated to log devices.|||Hey Brett, what's up with citing BOL article that has nothing to do with the question?

Multiple time dimensions and inventory measures.

I got 2 time-dimentions:

[Week]
Hierarchised dimension based on Year - Halfyear - Week - Day !

The supply calculated based on the Week-dimension
sum(
generate
(
ascendants([Week].currentmember),
iif(
[Week].currentmember IS
[Week].firstsibling,
{},
[Week].firstsibling:
[Week].prevmember
)
) + [Week].currentmember
,
[Measures].[Mutation]
)

[Posting Period]
Hierarchised dimension based on Year - Quarter - Month - Day !

The supply calculated based on the Posting Period-dimension
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + [Posting Period].currentmember
,
[Measures].[Mutation]
)

My question:

iif(Is the Week-time-dimension used in the pivottable or cube-browse ?,
--Yes
sum(
generate
(
ascendants([Week].currentmember),
iif(
[Week].currentmember IS
[Week].firstsibling,
{},
[Week].firstsibling:
[Week].prevmember
)
) + [Week].currentmember
,
[Measures].[Mutation]
)
,
--No, the posting period time-dimension will be used probably
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + [Posting Period].currentmember
,
[Measures].[Mutation]
)
)

Is there a way to merge the 2 time-dimensions without having duplicates in my measures ?

Kind regards,
GG

Here is an example from Adventure Works that shows how you can detect whether a user has selected members from the "Calendar" or "Fiscal" hierarchy. You should be able to adapt this to the problem you are trying to solve.

WITH

MEMBER Measures.[Time Hierarchy Used]

AS

CASE

WHEN [Date].Fiscal.CurrentMember.Level.Ordinal > 0 THEN

"Fiscal"

ELSE

"Calendar"

END

SELECT

{[Date].Calendar.DefaultMember.Children} ON ROWS,

--{[Date].Fiscal.DefaultMember.Children} ON ROWS,

{Measures.[Time Hierarchy Used]} ON COLUMNS

FROM

[Adventure Works]

HTH,

Steve

MULTIPLE TABLE QUERY!

I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJ
Anthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>
|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =
category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =
category_group.category_id
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

MULTIPLE TABLE QUERY!

I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJAnthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =category_group.category_id
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Multiple Table Grouping

Hi,
I have two tables based on the same dataset.
The way the report is setup i have it set up with two tables.
One table shows the latest month and the other table shows a 12 month period
of the same data.
It is also grouped by division number.
What i want to do is show both tables (latest and 12 month) for division A
then a page break and then both tables for division B.
Can this be done.Yes, but AFAIK only with 4 Tables. Two for div a and two for div b.
Another thing would be to put the tables horizontally together (in a
retangle ) and do your page break on a group basis.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Fez" <Fez@.discussions.microsoft.com> schrieb im Newsbeitrag
news:702B1045-289B-41DF-A8D0-83A570389534@.microsoft.com...
> Hi,
> I have two tables based on the same dataset.
> The way the report is setup i have it set up with two tables.
> One table shows the latest month and the other table shows a 12 month
> period
> of the same data.
> It is also grouped by division number.
> What i want to do is show both tables (latest and 12 month) for division A
> then a page break and then both tables for division B.
> Can this be done.|||Well the reason i have two tables is because of the size of the inforamtion.
I have to split the data up into 2 tables.

multiple stored procedure...or 1 dynamic procedure?

Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
JustinHi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH|||Hi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH

Hi,

Yeah I did have a quick read of the dynamic sql like Jesse provided...and was able to solve the previous problem with it...as i was a bit under the pump when i read it, i did not read the entire thing, and missed anything relating different amounts of dynamic columns...

You have pretty much answered what i was asking anyway, so it looks like im in for a long session of writing stored procs...

Thanks Again,
Justin