Dynamic SQL - date to varchar - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL - date to varchar

Author

Topic

tiss0183
Starting Member

18 Posts

Posted - 2008-06-18 : 12:05:16

I cannot execute a dynamic SQL function when I convert a datetime to a string:

declare @date_key datetime
set @date_key = '5/1/08'

select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI'
,'Execute dbFinance..spCalc ''' + convert(varchar(20), @date_key, 101) +'''')

The code below produces the exact same text but DOES work:
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','Execute dbFinance..spCalc ''05/01/2008''')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 12:16:59

Try this;-
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI'
,'Execute dbFinance..spCalc ''''' + convert(varchar(20), @date_key, 101) +''''''')'

tiss0183
Starting Member

18 Posts

Posted - 2008-06-18 : 15:18:01

That results in this error msg: Incorrect syntax near '+'.

Did you mean to put the final right parenthesis within the quote marks?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 15:36:40

Try

declare @date_key datetime, @sql varchar(8000)
set @date_key = '5/1/08'

set @sql='select * from OPENROWSET(''SQLOLEDB'',''Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI'',''Execute dbFinance..spCalc '''+cast(@date_key as varchar(20))+''''')'

EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

tiss0183
Starting Member

18 Posts

Posted - 2008-06-18 : 17:23:13

I had tried something similar.
When I run the code I get the error message:
Incorrect syntax near 'May'.

When I use my convert function, instead of CAST, I get this msg:
Incorrect syntax near '05'.

This is the @sql string from your code:
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','Execute dbFinance..spCalcBrokPay_SecurityDeposits 'May 1 2008 12:00AM'')

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-18 : 17:38:03

The query in OPENROWSET cannot be dynamic.

You can create the whole query in a local variable and execute that.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-19 : 19:38:31

You ma also try

declare @date_key datetime, @sql varchar(8000)
set @date_key = '5/1/08'

set @sql='select * from OPENROWSET(''SQLOLEDB'',''Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI'',''Execute dbFinance..spCalc '''''+cast(@date_key as varchar(20))+''''''')'

EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources