asked
When executed as statement the query runs fine, but when enclosed in a table valued function throws an error
I have this UDF SQL Server 2005 ALTER function [dbo].[ftsls030](@comno VARCHAR(3)) RETURNS @T TABLE(COMNO VARCHAR(3),T$CUNO VARCHAR(6),T$ITEM VARCHAR(16),T$CNTR VARCHAR,T$DILE INT,T$STDT varchar(10),T$TDAT varchar(10),T$DISC DECIMAL(6,4)) /*---------------------------------------------------------------------------------------------------------------------------------------------------- DECLARE @T TABLE(COMNO VARCHAR(3),T$CUNO VARCHAR(6),T$ITEM VARChAR(16),T$CNTR VARCHAR,T$DILE INT,T$STDT varchar(10),T$TDAT varchar(10),T$DISC DECIMAL(6,4)) DECLARE @COMNO VARcHAR(3);SELECT @COMNO='010' -----------------------------------------------------------------------------------------------------------------------------------------------------*/ begin set quoted_identifier off if dbo.fsBaanServer()="Hades" Begin IF @COMNO='010' INSERT @T select * from openQuery(Hades ,"select '010' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yyyy-mm-dd') t$tdat,to_char(t$disc,'999.99') t$disc from baan.ttdsls030010 where to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd') and to_char(current_date,'yyyy-mm-dd') <= to_char(t$tdat,'yyyy-mm-dd')" ) IF @COMNO='020' INSERT @T SELECT * FROM OPENquery(hades,"select '020' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yyyy-mm-dd') t$tdat,to_char(t$disc,'999.99') t$disc from baan.ttdsls030020 where to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd') and to_char(current_date,'yyyy-mm-dd') <= to_char(t$tdat,'yyyy-mm-dd')") end if dbo.fsBaanServer()="Athena" Begin IF @COMNO='010' INSERT @T select * from openQuery(athena ,"select '010' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yyyy-mm-dd') t$tdat,to_char(t$disc,'999.99') t$disc from baan.ttdsls030010 where to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd') and to_char(current_date,'yyyy-mm-dd') <= to_char(t$tdat,'yyyy-mm-dd')" ) IF @COMNO='020' INSERT @T SELECT * FROM OPENquery(Athena,"select '020' comno, trim(t$cuno) t$cuno,trim(t$item) t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') t$stdt,to_char(t$tdat,'yyyy-mm-dd') t$tdat,to_char(t$disc,'999.99') t$disc from baan.ttdsls030020 where to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd') and to_char(current_date,'yyyy-mm-dd') <= to_char(t$tdat,'yyyy-mm-dd')") end return end Which compiles fine .. But when I do select * from ftsls030("010") I get the following error > .Net SqlClient Data Provider: Msg 102, > Level 15, State 1, Line 5 Incorrect > syntax near 'select '020' comno, > trim(t$cuno) t$cuno,trim(t$item) > t$item,t$cntr,t$dile,to_char(t$stdt,'yyyy-mm-dd') > t$stdt,to_char(t$tdat,'yy'. BTW: when select clause is executed as is it works fine too.. How can I resolve this ? Thanks
can you alter the function to have 10,20,30+40 instead of 10,20,10,20 and add those results to your question, just so we know where the issue is?
Can you confirm that the linked servers that these queries are being run on (not SQL) know what the `trim()` and `to_char()` functions are?
Yes Kev,It is understood and executed by the linkedServer, (oracle 10g) , as said earlier when select is executed stand alone that works fine,.
OK can you now isolate the conditional logic from the insert, so maybe make the body of the function just insert @t select * from openquery(...........) and see what that does when you invoke the function
You will need to set `quoted_identifier` off before creating the function, and also in the calling code before referencing the function. Sorry for the brevity, on the fail phone... Edit -> OK, back on the PC now. Further reading from [MSDN][1]: >When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure. >When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed. [1]:
http://msdn.microsoft.com/en-us/library/ms174393.aspx
