Add a parameter in a SQL Server linked connection

Question

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.

declare @dbName varchar(50) = 'DBNAME'; Declare Local variable — Viki888
– Viki888, Commented Nov 28, 2016 at 7:53

Mr. Bhosale · Accepted Answer · 2016-11-28 08:19:29Z

4

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

answered Nov 28, 2016 at 8:19

Mr. Bhosale

3,1161 gold badge19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

N00b Pr0grammer · Accepted Answer · 2016-11-28 08:09:54Z

-1

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.

edited Nov 28, 2016 at 8:09

answered Nov 28, 2016 at 8:00

N00b Pr0grammer

4,6676 gold badges36 silver badges49 bronze badges

1 Comment

SANJOG Over a year ago

yes all is working but when ever i use parameter its throw error

Collectives™ on Stack Overflow

Add a parameter in a SQL Server linked connection

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related