How to pass date in where clause when using OpenQuery to a linked server (Sybase) from SQL 2000
Tibor Karaszi
EXEC(@sql)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"R. Kaushik" <pro...@del2.vsnl.net.in> wrote in message
news:87593018.02121...@posting.google.com...
> In a SQL Server 2000, I have configured a Sybase linked server. I am
> trying to write a stored procedure where I can access the dataset in
> this linked server:
>
> DECLARE @DEST_DEPT_FROMDT datetime
> SET @DEST_DEPT_FROMDT = '10/29/2002'
>
> SELECT * FROM OPENQUERY(SPA, 'SELECT * FROM oy301shipment WHERE
> (dest_dept_dt BETWEEN ''10/29/2002'' AND ''10/30/2002'')')
>
> The code above works great. However, I need to be able to make the
> dates dynamic. So if I change the SELECT statement to use
> @DEST_DEPT_FROMDT instead of '10/29/2002', I cannot get it to work:
>
> SELECT * FROM OPENQUERY(SPA, 'SELECT * FROM oy301shipment WHERE
> (dest_dept_dt BETWEEN ''@DEST_DEPT_FROMDT'' AND ''10/30/2002'')')
>
> I get the following error:
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [SYBASE][ODBC Sybase driver][SQL
> Server]Syntax error during implicit conversion of VARCHAR value
> '@DEST_DEPT_FROMDT' to a DATETIME field.
> ]
>
> Tried with different number of (') characters around @DEST_DEPT_FROMDT
> but that was of no use. I would greatly appreciate if somebody could
> suggest what should I be doing to get this to work. Interesting thing
> is that this error comes with datetime field only. The dynamic
> paramaeters where a string is used works great.
>
> Thanks in advance,
>
> R. Kaushik
Michael Peppler
> In a SQL Server 2000, I have configured a Sybase linked server. I am
> trying to write a stored procedure where I can access the dataset in
> this linked server:
> SELECT * FROM OPENQUERY(SPA, 'SELECT * FROM oy301shipment WHERE
> (dest_dept_dt BETWEEN ''@DEST_DEPT_FROMDT'' AND ''10/30/2002'')')
Lose the quotes around @DEST_DEPT_FROMDT.
Michael
--
Michael Peppler Data Migrations, Inc.
mpep...@peppler.org http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
R. Kaushik
> > (dest_dept_dt BETWEEN ''@DEST_DEPT_FROMDT'' AND ''10/30/2002'')')
>
> Lose the quotes around @DEST_DEPT_FROMDT.
>
> Michael
Thanks for your response. Unfortunately, that did not help. I removed
the quotes around @DEST_DEPT_FROMDT:
SELECT * FROM OPENQUERY(SPA, 'SELECT * FROM oy301shipment WHERE
(dest_dept_dt BETWEEN @DEST_DEPT_FROMDT AND ''10/30/2002'')')
and got the following error:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [SYBASE][ODBC Sybase driver][SQL
Server]Must declare variable '@DEST_DEPT_FROMDT'.
]
I think the Sybase considered this as it's own variable while it was
declared in the SQL Server.
R. Kaushik
R. Kaushik
> My guess is that you have to build a string of the whole lot and then execute that string:
> EXEC(@sql)
>
Tibor,
You are very much right. I followed your post and Steve Sills's (with
Thanks):
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=%23x427Z5tAHA.2224%40tkmsftngp03
Exec('
Select *
From OpenQuery(My400, ''
Select '''+''''+@var+''''+''' as "COLNAME"
From MyTable
Where MyColumn = '''+''''+@var+''''+'''
'')
')
However, the issue in using the Exec is that I cannot use returned
results into the CURSOR like this:
DECLARE WKLY_SHIPMENT CURSOR FOR
Exec('
Select *
From OpenQuery(My400, ''
Select '''+''''+@var+''''+''' as "COLNAME"
From MyTable
Where MyColumn = '''+''''+@var+''''+'''
'')
')
OPEN WKLY_SHIPMENT
FETCH NEXT FROM WKLY_SHIPMENT INTO @COLNAME
WHILE @@FETCH_STATUS = 0
The DECLARE .... CURSOR FOR requires a SELECT statement. Any
suggestions on how can I retrieve the Field values and put those
inside the variable @COLNAME w/o using Cursor.
R. Kauhsik
Tibor Karaszi
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"R. Kaushik" <pro...@del2.vsnl.net.in> wrote in message
news:87593018.02121...@posting.google.com...
Anthony Mandic
> Thanks for your response. Unfortunately, that did not help. I removed
> the quotes around @DEST_DEPT_FROMDT:
>
> SELECT * FROM OPENQUERY(SPA, 'SELECT * FROM oy301shipment WHERE
> (dest_dept_dt BETWEEN @DEST_DEPT_FROMDT AND ''10/30/2002'')')
>
> and got the following error:
>
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [SYBASE][ODBC Sybase driver][SQL
> Server]Must declare variable '@DEST_DEPT_FROMDT'.
> ]
>
> I think the Sybase considered this as it's own variable while it was
> declared in the SQL Server.
The issue is what is Sybase ASE seeing. Based on the above
it is seeing the plain string "@DEST_DEPT_FRMDT" which, of
course, is undefined as a variable to it. Sybase ASE auditing
should reveal this but the error message is clear enough.
So its either a bug in MS SQL Server or you're not using it
correctly. Check its docs on this OPENQUERY() function to see
what it says about passing local variables. I suspect that
you may have to fashion a variable string outside the call
and then pass that (i.e. OPENQUERY(SPA, @sql_string)).
-am © 2002
Hal Berenson
without manipulating the content. So you can't include a reference to a
local variable in the string. You need to build the string up yourself,
substituting any local variables with their value. Then pass the final
string to Sybase via OpenQuery.
--
Hal Berenson
True Mountain Consulting
"Anthony Mandic" <am_i...@start.com.au> wrote in message
news:3DFB2FE3...@start.com.au...