Call Variable as Param from Variable Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Call Variable as Param from Variable Query

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-08-14 : 02:54:57

Hi,I have a (Microsoft DB2 OLE DB Provider) connection.

I tried the below query with a paramater but when i select parameters it gives error.

Error: Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft DB2 OLE DB Provider)

So I tried SQL command is stored in a variable, but can't call another variable as a parameter from SQL command is stored in a variable

Variable Query: select DISTINCT rtrim(Code_Srce) as Code_Srce from b610clivF.SIH where SIINVD = ? (Variable Date)

Variable Date : 20070101

Can anyone assist please or how do i fix in order to accept parameters.

Regards,

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2007-08-14 : 04:26:03

[code]CREATE PROCEDURE stp_MyCodeSrce
@DATE DateTime
AS
SELECT DISTINCT
rtrim(Code_Srce) as Code_Srce
FROM
b610clivF.SIH WHERE SIINVD = @DATE
GO
[/code]
Now just call that stored procedure... make sure you pass it the parameter, DATE...

"Impossible is Nothing"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-08-14 : 07:27:11

Hi, Thanks for the reply but to be honest this is my first time & I'm clueless.
Please Assist. This is what I'm doing.

1.Create Variable called Query(all in one line):
CREATE PROCEDURE stp_MyCodeSrce @DATE DateTime AS SELECT Code_Srce FROM b610clivF.SIH WHERE
SIINVD = @DATE GO

2. Variable DateFrom is a global variable define from SQL Source. (SQL derived query = 20070101)
so i cant use the @DATE DateTime?

3. How do i call the Global defined variable: DateFrom from query
CREATE PROCEDURE stp_MyCodeSrce @DateFrom ? AS SELECT Code_Srce FROM b610clivF.SIH WHERE
SIINVD = @DateFrom? GO

4. In OLE DB source task - select command from variable - then i select Variable: Query

5. OR In OLE DB source task - how do i execute the stored procedure?

Please any help, regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources