Parameter problem

hi,
i m using following query in my dataset query string

SELECT OrderNumber, OrderDate, Name AS Client, orderstatus
FROM Orders
Where @.Param

i m sending boolean exp as value for @.param as string like " YEAR(ORderDate) = YEAR(getDate()) "

but is showing following error

An expression of non-boolean type specified in a context where a condition is expected,near '@.param'. (Microsoft Sql Server, Error:4145)

please help me,

thanks
You cannot do that unless you use dynamic Sql in a procedure, passing the string as the parameter which should be avoided.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||thanks for reply

but can u explain further because i m new to it
|||

You dont need to send the parameter string because whatever you trying to do can be achieved in plain SQL in dataset query itself:

SELECT OrderNumber, OrderDate, Name AS Client, orderstatus
FROM Orders
Where YEAR(OrderDate) = YEAR(GETDATE())

Shyam

|||Wee that depends. if you have the same query everything with carying parameters you can use something like Where YEAR(@.SomeVar) = YEAR(Getdate()), the parameter will automatically mapped to the report if you write such a query in the pane of the query editor in the report designer. but if you want to pass in a whole condition rather than just a parameter you would probably need to pass it to a stored procedure to make it execute in a dynamic SQL statement.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

sql

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter maximum string size question

Does anyone happen to know what is the maximum string size that can be passed in a parameter w/o it truncating or throwing an error?

Thank you in advance,

As it is implemented as a .NET type (System.String) it could take theoretically up to 2^31-1 chars, but passing the parameter through the webservice or the URL will make you only to have your URL expanded to the maximum of 2083 chars to support all the methods like GET / POST / Webservices.

http://support.microsoft.com/default.aspx?scid=KB;en-us;q208427

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thank you Jens. I believe that is what I was looking for.

Parameter Mapping format string

Hi,

I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.

The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?

Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @.[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.

Thanks in advance

Hello Prabha!

The best way to set parameter mappings on your OLE DB source is by using the "Parameters" button in the edit dialog for the source component. Once you've clicked that, a pop-up will appear with a grid where you can select variables you want to map to parameters of your SQL statement. If you have problems with this approach, feel free to post a follow up and one of us will be sure to help you from there.

Thanks, -David

|||

Hi David,

I agree that the suggested approch will help in configuing parameterized SQL Command. But my scenario is to use named query as source.

I do not get the 'Parameters' button as I have selected named query option in OLEDB source. However, I am allowed to change the SQL command property to have the parameter place holder '?' under Properties window after configuring the OLEDB connection Manager.

There is also one more property called 'ParameterMapping' under custom properties of OLEDB source. I do not find any reference on the format of the parameter mapping. Can you forward any reference on this ?

Thanks,

Prabha

|||

Sorry, Prabha, I sort of missed that you're using a DSV named query.

Officially, Named Queries don't support parameters, but as you found, there are ways to manually change the SQL in your DSV, such as by changing the xml directly. I wouldn't recommend that because other things using these DSVs are likely to break.

That having been said, I was able to convince the OLE DB source to bind in variables to the parameters I added manually to the Named Query's SQL, by setting the ParameterMapping manually. The format is:

"<param-name>","<variable-guid>";...

But, instead of constructing it manually, which seems kind of error prone, try this:

1) In the OLE DB Source editor, copy the SQL for your named query to the clipboard.

2) Switch to "SQL Command" mode for your OLE DB Source.

3) Paste the query from the clipboard to the SQL editor.

4) Click "Parameters...".

5) For each parameter, choose a variable.

6) Close the parameters screen, close the OLE DB source editor.

7) Find the "ParameterMapping" field in the Properties editor, copy that to the clipboard.

8) Re-open the OLE DB source editor, switch back to the Named Query.

9) Close the OLE DB source editor

10) Paste the ParameterMapping value from the clipboard back into the properties window

A lot of steps, but I think it's better than trying to hand-assemble the thing. Fair warning though, this isn't supported, so it could stop working at any time. From what I know, DSVs don't officially support parameters, so the safe bet would be to switch to SQL Command mode for this.

-David

|||

Thank you so much David, The given work around works well.

- Prabha

Parameter Limitations?

Hi all,
I am calling a SP from RS inwhich there is an optional parameter of string
type. I allow the parameter to be blank and the results returned are
correct. When i try to filter my results by adding a name (ie Tic-Tot's
Daycare) and run the preview again the results are the same as if i left the
parameter blank. When i run the SP in the data view and include the name it
returns the filtered results. When i run the SP in QA i get the filtered
result. My only thought is the odd chars in the string ( single quote and
hyphen) could be messing up the parameter passing. Can some one please
confirm this or give a different suggestion for this issue.
Thanks in advance,
SPOSince the string delimiter in SQL is a single quote, I would suspect that is
your problem... You might try to double up on single quotes in your
parameters ,( which escapes the quote in SQL)... First eliminate the quote
from the parameter and see if it works...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"iamspo" <iamspo@.discussions.microsoft.com> wrote in message
news:5097986A-9711-4802-B839-9BB7A8C22F49@.microsoft.com...
> Hi all,
> I am calling a SP from RS inwhich there is an optional parameter of string
> type. I allow the parameter to be blank and the results returned are
> correct. When i try to filter my results by adding a name (ie Tic-Tot's
> Daycare) and run the preview again the results are the same as if i left
> the
> parameter blank. When i run the SP in the data view and include the name
> it
> returns the filtered results. When i run the SP in QA i get the filtered
> result. My only thought is the odd chars in the string ( single quote and
> hyphen) could be messing up the parameter passing. Can some one please
> confirm this or give a different suggestion for this issue.
> Thanks in advance,
> SPO|||I actually found the error. Since i added the name filter parameter as a
second version to this SP it was not picked up by RS. I went in and added
the parameter (report => report parameters...) but never went back and added
it to the parameter list in the data set. Once i did think the data set knew
which parameter to associate this value to and sent it to the SP. The result
returned were correct, none of the special char's used caused an issue.
Thanks for the help!!!
"iamspo" wrote:
> Hi all,
> I am calling a SP from RS inwhich there is an optional parameter of string
> type. I allow the parameter to be blank and the results returned are
> correct. When i try to filter my results by adding a name (ie Tic-Tot's
> Daycare) and run the preview again the results are the same as if i left the
> parameter blank. When i run the SP in the data view and include the name it
> returns the filtered results. When i run the SP in QA i get the filtered
> result. My only thought is the odd chars in the string ( single quote and
> hyphen) could be messing up the parameter passing. Can some one please
> confirm this or give a different suggestion for this issue.
> Thanks in advance,
> SPO

Parameter Available Values

If I have a paramter that has query based available values, how do I get it to use a connection string specified in another paramter to do the query?

The scenario is that I have one set of reports that pull from a number of databases that have identical schemas and each serve a different customer. i need to be able to somehow populate the valid values with the right data from the appropriate database at runtime.

Nevermind, I found it.

I just had to populate an array of ParameterValues with just the connection string and then do a call to GetReportParameters, that made sure that the other parameters had the data that they needed.