question

Anthony peiris avatar image
Anthony peiris 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
sql-server-2005linked-serverudf
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes Kev,It is understood and executed by the linkedServer, (oracle 10g) , as said earlier when select is executed stand alone that works fine,.
0 Likes 0 ·
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
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.