Microsoft – SQL Server – Calling User Defined Function over Linked Server (the wire)

I had a temporary need to call a CLR based User Defined Function through Linked Server. But, could not do so.

The call I tried is:

select [DBServer].[DBUtil].[dbo].hello()

But, repeatedly got this error message:

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name ‘DBServer’.


One possibly work-around is to use sp_executeSQL

declare @sqlStatement nvarchar(600)
declare @parmDefinition nvarchar(600)
declare @IPAddress nvarchar(80)
declare @hostname nvarchar(80)

set @sqlStatement = N'Set @hostnameOut = [DBUtil].[dbo].[IPtoHostName](@input)'
set @parmDefinition = N'@input varchar(255), @hostnameOut varchar(255) output'
set @IPAddress = '10.0.50.101'
exec [RemoteServer].[DBUtil].[dbo].sp_executesql 
	  @statement = @sqlStatement  
         ,@params = @parmDefinition
         ,@input=@IPAddress
	 ,@hostnameOut = @hostname output
print @hostname

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s