x
login about faq Site discussion (meta-askssc)

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

more ▼

asked Sep 04 '10 at 07:00 AM in Default

Anthony peiris gravatar image

Anthony peiris
1 1 1 1

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?

Sep 04 '10 at 09:03 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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:

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.

more ▼

answered Sep 05 '10 at 06:00 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.2k 56 63 87

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Sep 04 '10 at 10:52 AM

Kev Riley gravatar image

Kev Riley ♦♦
46.1k 38 43 69

Yes Kev,It is understood and executed by the linkedServer, (oracle 10g) , as said earlier when select is executed stand alone that works fine,.

Sep 04 '10 at 02:44 PM Anthony peiris

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

Sep 05 '10 at 12:16 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1834
x92
x6

asked: Sep 04 '10 at 07:00 AM

Seen: 910 times

Last Updated: Sep 04 '10 at 08:47 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.