CODEWITHSUNDEEP
sql-server
SANJOG
SANJOG

Reputation: 31

Add a parameter in a SQL Server linked connection

select @dbName="DBNAME"

select Distributorid  from [180.552.528.854,21414].[@dbName].DBO.DistributorMaster where Distributorid =XXXXXX

ERROR: The OLE DB provider "SQLNCLI10" for linked server[180.552.528.854,21414] does not contain the table ""@dbName"."DBO"."DistributorMaster"". The table either does not exist or the current user does not have permissions on that table.

Upvotes: 2

Views: 182

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Try This. Dynamic variable value query .

            declare 
            @dbname nvarchar(max) ='dbName',


            @query nvarchar(max) ='select Distributorid  from [180.552.528.854,21414].[@dbName].DBO.DistributorMaster 
            where Distributorid =XXXXXX'

            SET @Query=REPLACE(@Query,'@dbname',@dbname)

            --print(@Query)

            execute SP_executesql  @Query

Upvotes: 4

N00b Pr0grammer
N00b Pr0grammer

Reputation: 4647

When querying the Linked Server, you need to specify the fully qualified table name in the following pattern:

SERVER.DATABASE.SCHEMA.OBJECT

In addition to this, please check whether the table is listed on the Linked Server with the help of SQL Server Management Studio, under the Linked Servers.

EDIT: Please check this site, if you've implemented all the steps noted on this site completely.

And also a common point that should've been missed is the following:

Adding ", @datasrc=@ServerName" to your sp_addlinkedserver statement.

Upvotes: -1

Related Questions