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 Information cannot be derived from SQL statements with sub-select queries

Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.

Here's the query:

update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'

Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?

I guess you are using OLE DB connection. I have seen similar problems with Execute SQL task involving complex queries with parameters and OLE DB connection.

A simple work around is to set "ByPassPrepare" to true. Try this and let us know if it helped. Otherwise, I can investigate the issue more to suggest other work arounds.|||Kaarthik's answer is most likely the best way to go, however since you state that

Thanh Duong wrote:

i'm having this almost exact query in alot of places. Any way around this?

you may want to wrap this command in a Store Proc or View anyway. Then the parameters should work fine against the SP too.|||

Still failed with bypassprepare=true. I have a var mapped to parameter name 0 (like how i'm doing it with all other execute sql task). Any other solutions not involving sprocs?

Error: 0xC002F210 at Update Last Do_Seq_No, Execute SQL Task: Executing the query "update GCDE_SEQ

set LAST_NO =

(select max(CAST(OLD_DO_NO AS INT))

from DONL_DO_HDR)

,UPDT_USER = ?

,UPDT_DT = getdate()

where SEQ_NM = 'DO_SEQ_NO'" failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||I could not repro your problem.

In "Execute SQL Task" I set the following properties:
ConnectionType=OLE DB
SQLStatement= update UserTable
set age = (select max(CAST(age AS INT)) from AgeTable)
,[name] = ?
,somedate = getdate()
where sex = 'M'
BypassPrepare=True

Parameter Mapping: Variable Name=nameVar, Direction=Input, Data Type=VARCHAR, Parameter Name=0

nameVar is a String variable.

I could successfully execute this task.

If I set BypassPrepare=False, the same task fails throwing the error message "[Execute SQL Task] Error: Executing the query "update UserTable
set age = (select max(CAST(age AS..." failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The tables I used for this taks are created as follows:

create table UserTable (
age int,
[name] varchar(10),
somedate datetime,
sex varchar(2)
)
create table AgeTable (
age varchar(5)
)|||THANKS FOR TRYING, ILL MESS AROUND WITH IT SOME MORE AND SEE WHAT I'M DOING WRONGsql

parameter cannot set the command text for dataset

Hello

I get the following error in a very quick test system i created:

An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'test'.

Error during processing of the CommandText expression of dataset 'test'.

My sql is:

= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)

There is nothing obviously wrong in the code as far as i can see

The parameter 'test1' is of type 'string'

The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'

I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine

Thanks

t.test from test?

You tried running this in sql?

|||

Yes, it works fine in sql if you strip out the bits that obviously wont work in query analyser

this works:

= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = 1" )

this does not:

= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = " & Parameters!test1.Value)

why?

there is a parameter called test1

|||

Found the answer!

You have to have another table with a different name into which the parameter links (in the section 'available values' / 'from query')

I created another table called test2, et voila! it works

Paging and printing without the toolbar

Is there a way to issue the report print command or paging command from code.
I would like to hide the toolbar and use another button in our ASP.NET
application to kick off printing or tell the reportviewer to go to another
page.
ThanksPerhaps you could use the findstring to search for the literal in a report
ie
http://server/Reportserver?/SampleReports/Product
Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
Search for URL Access in Books on line and you will get all of the URL
parameters, etc... There is also code which shows you how to put and IE
browswer instance in a windows form, and put a report into it...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"NeedToKnow" wrote:
> Is there a way to issue the report print command or paging command from code.
> I would like to hide the toolbar and use another button in our ASP.NET
> application to kick off printing or tell the reportviewer to go to another
> page.
> Thanks|||I do not understand. I have looked through URL Access and why would I want to
find a literal. I want to send the command to reportviewer through a URL
that tells the report viewer to print the current report to the printer.
"Wayne Snyder" wrote:
> Perhaps you could use the findstring to search for the literal in a report
> ie
> http://server/Reportserver?/SampleReports/Product
> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>
> Search for URL Access in Books on line and you will get all of the URL
> parameters, etc... There is also code which shows you how to put and IE
> browswer instance in a windows form, and put a report into it...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NeedToKnow" wrote:
> > Is there a way to issue the report print command or paging command from code.
> > I would like to hide the toolbar and use another button in our ASP.NET
> > application to kick off printing or tell the reportviewer to go to another
> > page.
> >
> > Thanks|||You asked two question. He answered one of them (the one about going to
another page).
As far as printing, I don't think what you want is possible with the report
viewer control. Are you using the one from VS 2005?
I wanted to do something similar with the winform report viewer control but
found out that until it is rendered I can't request for it to print. So for
Windows I am setting a property to print and then responding to the
rendering event and if their is a request to print, I print and then close
the form. However, I am not familiar with the webform control from VS 2005.
If you are doing this in RS 2000 then I have even less to offer as a
suggestion.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
>I do not understand. I have looked through URL Access and why would I want
>to
> find a literal. I want to send the command to reportviewer through a URL
> that tells the report viewer to print the current report to the printer.
> "Wayne Snyder" wrote:
>> Perhaps you could use the findstring to search for the literal in a
>> report
>> ie
>> http://server/Reportserver?/SampleReports/Product
>> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>>
>> Search for URL Access in Books on line and you will get all of the URL
>> parameters, etc... There is also code which shows you how to put and IE
>> browswer instance in a windows form, and put a report into it...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "NeedToKnow" wrote:
>> > Is there a way to issue the report print command or paging command from
>> > code.
>> > I would like to hide the toolbar and use another button in our ASP.NET
>> > application to kick off printing or tell the reportviewer to go to
>> > another
>> > page.
>> >
>> > Thanks|||It is vs 2005.
I don't want to print the form, since my report is several pages long. Is
there a way to do this?
"Bruce L-C [MVP]" wrote:
> You asked two question. He answered one of them (the one about going to
> another page).
> As far as printing, I don't think what you want is possible with the report
> viewer control. Are you using the one from VS 2005?
> I wanted to do something similar with the winform report viewer control but
> found out that until it is rendered I can't request for it to print. So for
> Windows I am setting a property to print and then responding to the
> rendering event and if their is a request to print, I print and then close
> the form. However, I am not familiar with the webform control from VS 2005.
> If you are doing this in RS 2000 then I have even less to offer as a
> suggestion.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
> news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
> >I do not understand. I have looked through URL Access and why would I want
> >to
> > find a literal. I want to send the command to reportviewer through a URL
> > that tells the report viewer to print the current report to the printer.
> >
> > "Wayne Snyder" wrote:
> >
> >> Perhaps you could use the findstring to search for the literal in a
> >> report
> >>
> >> ie
> >> http://server/Reportserver?/SampleReports/Product
> >> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
> >>
> >>
> >> Search for URL Access in Books on line and you will get all of the URL
> >> parameters, etc... There is also code which shows you how to put and IE
> >> browswer instance in a windows form, and put a report into it...
> >> --
> >> Wayne Snyder MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >>
> >> I support the Professional Association for SQL Server ( PASS) and it''s
> >> community of SQL Professionals.
> >>
> >>
> >> "NeedToKnow" wrote:
> >>
> >> > Is there a way to issue the report print command or paging command from
> >> > code.
> >> > I would like to hide the toolbar and use another button in our ASP.NET
> >> > application to kick off printing or tell the reportviewer to go to
> >> > another
> >> > page.
> >> >
> >> > Thanks
>
>