Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Pete_N
Posting Yak Master
181 Posts |
Posted - 2013-04-04 : 10:43:34
|
I am trying to write a function to look up details from a different server. The two servers are linked.FunctionALTER FUNCTION [dbo].[func_CRMgetInputHeaders](@ClearedFundsDate SMALLDATETIME, @ProcessingDate SMALLDATETIME, @CreditNumberOf DECIMAL(11,0), @CrecitValueOf DECIMAL(11,2), @DebititNumberOf DECIMAL(11,0), @DebitValueOf DECIMAL(11,2), @USERID varchar(6))RETURNS @InputHdrTable Table(Licence Varchar(6) NULL, FileSerial varchar(6) NULL , Transdetid varchar(50) NULL )ASBEGINDeclare @t_items decimal(11,0)Declare @t_value decimal(11,2)DECLARE @Licence varchar(6)DECLARE @Serial varchar(6)DECLARE @Transsetid uniqueidentifierSET @t_items = @CreditNumberOf + @DebititNumberOfSET @t_Value = @CrecitValueOf + @DebitValueOf SELECT @Licence = Licence, @Serial = SerialNumber, @Transsetid = LedgerKey FROM Server1.DB1.dbo.TranSet WHERE SubmissionValue = @t_Value AND TransactionCount = @t_items AND DATEDIFF(d, PaymentDate , @ClearedFundsDate) = 0 INSERT INTO @InputHdrTable SELECT @Licence, @Serial, @TranssetidRETURN ENDSQLselect * from [dbo].[func_CRMgetInputHeaders] ((SELECT TOP 1 [ClearedFundsDate], [UFIuhl1ProcessingDate], [DSRCnumberof], [DSRCvalueof], [DSRDnumberof], [DSRDvalueof], [UFIuserNumber] [dbo].[TempINPUT]))Error messages Msg 116, Level 16, State 1, Line 8Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.Msg 313, Level 16, State 3, Line 1An insufficient number of arguments were supplied for the procedure or function dbo.func_CRMgetInputHeaders. |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-04 : 11:03:39
|
I probably wouldn't choose to create a function with a linked server call. I would either create a stored procedure on the remote server and execute that from your local server OR replicate that [TranSet] table to your local server and use the local version.Having said that the error seems to be from your SQL which doesn't include a FROM keyword in front of [dbo].[TempINPUT].perhaps one of these would work:select * from [dbo].[func_CRMgetInputHeaders] ( (SELECT TOP 1 [ClearedFundsDate], [UFIuhl1ProcessingDate], [DSRCnumberof], [DSRCvalueof], [DSRDnumberof], [DSRDvalueof], [UFIuserNumber] FROM [dbo].[TempINPUT]))select f.*from [dbo].[TempINPUT] tcross apply dbo.func_CRMgetInputHeaders (t.[ClearedFundsDate], t.[UFIuhl1ProcessingDate], t.[DSRCnumberof], t.[DSRCvalueof], t.[DSRDnumberof], t.[DSRDvalueof], t.[UFIuserNumber]) f Be One with the OptimizerTG |
|
|
Pete_N
Posting Yak Master
181 Posts |
Posted - 2013-04-04 : 13:02:21
|
Hi,Thanks for the reply. The first query still returns the same errors, however the second query works great .thank you |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-04 : 14:00:51
|
good. Thanks for reporting back.Be One with the OptimizerTG |
|
|
|
|
|
|
|