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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-20 : 07:25:28
|
| Andy writes "How can I pass date parameters using OPENQUERY? I have a linked SQL server DB, and must use OPENQUERY due to performance problems. The only way that I have found to pass parameters when using OPEN query is with dynamic SQL. I have successfully used character parameters, but when I try to use a date parameter, I receive the error "Syntax error converting datetime from character string". Below is a simplified version of what I am trying to run with only the date field in the WHERE clause.CREATE PROCEDURE SP_PAYMENTS @DATE datetime = '1999-11-01'AS DECLARE @SQL varchar(4000)SET @SQL = 'SELECT * FROM OPENQUERY(LinkedServer, ''SELECT * FROM dbo.Table WHERE PaymentDate = ''' + @DATE + ''''')'EXEC (@SQL)I have also tried "CONVERT(datetime, @DATE)" instead of just @Date.I can get it to work if I use the 4 part name instead of OPENQUERY, but the performance difference is huge, not to mention the issue in the client side with timeouts.SQL Server 2000 Enterpise SP3Windows 2000 ServerUsing EM to create stored procedure and QA to execute during development.Linked database is also SQL Server 2000 on different server.Thanks for any help!" |
|
|
SqlStar
Posting Yak Master
121 Posts |
Posted - 2003-03-20 : 09:17:05
|
| HiPlease try like this....Select 'Select * from dbo.Table WHERE PaymentDate = ''' + Convert(char(10), @DATE, 120) + ''''It may rectify ur pblm.":-) IT Knowledge is power :-)" |
 |
|
|
|
|
|
Passing date parameters using OPENQUERY