MSSQL using UDF across servers
Hey all,
I am running into an issue trying to call a user defined function across servers. Then error I am getting is The multi-part identifier "myserver.database.schema.functionname" could not be bound.
This is the code I am trying to use:
Code:
declare @storeNo INT
declare @vendorID INT
declare @payAgreed BIT
select @sql = N'select @store = IST.Store_No, @vendor = IVS.Vendor_ID
FROM POHeader H
inner join ' + @DBString + '[Store] IST on H.BusinessUnit = IST.BusinessUnit_ID
inner join ' + @DBString + '[Vendor] IVS on IVS.Store_No = IST.Store_No'
exec sp_executesql @sql
, N'@store INT OUTPUT,@vendor int OUTPUT'
, @store = @storeNO OUTPUT, @vendor = @vendorID OUTPUT
select @sql = N'SET @payAgreedCost = ' + @DBString + '[fn_IsPayByAgreedCostStoreVendor(@storeNo, @vendorID, getdate())]'
exec sp_executesql @sql
, N'@payAgreedCost BIT OUTPUT'
, @payAgreedCost = @payAgreed OUTPUT
any ideas on how to work around this?
Thanks!
Re: MSSQL using UDF across servers
you need to have linked servers on, and the initiating server needs to know about the destination server. Once you have that set up, you should be able to access the function using: linkservername.databasename.dbo.functionname
-tg
Re: MSSQL using UDF across servers
The joins work fine across servers, do those not require linked server being turned on?
Re: MSSQL using UDF across servers
then there's something missing from the multi identifier that isn't right. I know that in the past, I've had to use dbo. even when using UDFs from inside the same db.
So the question is, what is "@DBString" at the moment the call is made?
-tg
Re: MSSQL using UDF across servers
yeah the error message contains the full path
ServerName.DBName.dbo.Functionname
Re: MSSQL using UDF across servers
I moved the ] to the end of the function name and now I get a new error message:
Invalid column name 'servername'
Re: MSSQL using UDF across servers
I didn't even see that initially... try taking the [ and ] off completely.
-tg
Re: MSSQL using UDF across servers
yeah that gives the same error... I have been asking around and did some poking around, everything points to wrapping a sp around the function then calling the sp. Gonna try that in the morning.