MSSQL using UDF across servers

[Besoup]

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:

				  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!

[techgnome]

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

[Besoup]

The joins work fine across servers, do those not require linked server being turned on?

[techgnome]

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

[Besoup]

yeah the error message contains the full path

ServerName.DBName.dbo.Functionname

[Besoup]

I moved the ] to the end of the function name and now I get a new error message:
Invalid column name 'servername'

[techgnome]

I didn't even see that initially... try taking the [ and ] off completely.

-tg

[Besoup]

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.