Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Use parameters with Execute SQL task Expand / Collapse
Author
Message
Posted Saturday, August 6, 2005 9:42 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 7, 2014 11:40 AM
Points: 769, Visits: 246

I would like to use a parameter with the execute sql task but nothing is working like BOL says it should. I am using the June CTP.

I have a query something like

Select ProductID, ProductName
from Production.Product
Where ProductID = ?

The Parameter mapping page looks like this:
User:roductID, Input, Long, ?

Clicking Parse Query returns this: "The query failed to parse. Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."

BOL also says you can do this

Select ProductID, ProductName
from Production.Product
where ProductID = @Param1

Parameter Mapping page:
User:roductID, Input, Long, @Param1
OR
User:roductID, Input, Long, ?

Returns error "The query failed to parse. Must declare the scalar variable @Parm1.".

The only way to clear this error is by declaring @Param1 inside the SQL statement.

Declare @Param1 int
Select ProductID, ProductName
from Production.Product
where ProductID = @Param1

But this doesn't actually send User:roductID in place of @Param1.

I have also tried this

Select ProductID, ProductName
from Production.Product
where ProductID = Param1

with Parameter Mapping page:
User:roductID, Input, Long, Param1

The query will parse, but errors out when executed. By running Profiler, I can see that the statement is passed to SQL as is, without the replacement.

Any help will be appreciated. I have a big deadline in a couple of days.

Kathi



Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #208143
Posted Tuesday, August 9, 2005 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Post #208716
Posted Wednesday, August 10, 2005 8:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 9:30 AM
Points: 489, Visits: 489

Hello Kath

I experienced similar problems when trying to do this.  In the end I resorted to a stored proc, and passing the parameters seemed to work just fine.

 

 

 




Post #209096
Posted Wednesday, August 10, 2005 9:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 7:56 AM
Points: 33, Visits: 5

what data type of your variable???

i experiance the same problem in the past i set to my variable type Int, however i changed the type to integer or decimal and it's work.

 

 

 

------------------------------------------------------------

Imagination is more important then knowledge

 



. . .
Post #209156
Posted Thursday, August 25, 2005 7:19 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 7, 2014 11:40 AM
Points: 769, Visits: 246
I finally got a good answer on this one.  Use '?' as the placeholder in the query and use numbers starting at 0 for the parameter name.  Works like a charm!

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #214192
Posted Wednesday, August 31, 2005 10:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, Visits: 188

Kathi,

I think BOL is a bit light on this. Could you use the "Send Feedback" link on the appropriate page to let the author know - it seems alot of people have had this problem!

 

-Jamie

 



Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Post #215800
Posted Wednesday, August 31, 2005 10:42 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 7, 2014 11:40 AM
Points: 769, Visits: 246
I talked with a developer at MS about this. He said that the parameters for each provider is different and right now, there is no source listing the differences. There should be a white paper by RTM.

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #215802
Posted Monday, October 3, 2005 9:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 11:05 AM
Points: 2, Visits: 24
I don't understand how this worked.   Can you send a sample?
Post #225539
Posted Monday, October 3, 2005 3:18 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 7, 2014 11:40 AM
Points: 769, Visits: 246
This parameter stuff just about drove me nuts. All my 2005 stuff is at home, so I'll try to post how I solved the problem tonight.

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #225651
Posted Tuesday, October 4, 2005 8:37 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 7, 2014 11:40 AM
Points: 769, Visits: 246

Depending on the type of connection, the parameters are set up differently. Even then, things didn't always work for me. If you use an ADO.NET connection manager, the parameters should start with the @ symbol.  If you use the OLE DB connection manager, the parameters are numeric starting with 0. I had the best luck with the ADO.NET connection manager.

I am trying to find out from MS if there is a white paper available. I'll post what I find out here.

 

 



Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #225869
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse