x

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, 2010 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, 2010 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][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
more ▼

answered Sep 05, 2010 at 06:00 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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, 2010 at 10:52 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

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, 2010 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, 2010 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x108
x8

asked: Sep 04, 2010 at 07:00 AM

Seen: 1434 times

Last Updated: Sep 04, 2010 at 08:47 AM