Test if Table Exists in Linked Server (Server Name is a Parameter)

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I need to test if a table exists in a linked server, where linked server is a parameter (it has to be), that's why I'm using exec method. I tried many ways but I didn't succeed.

SQL

Declare @LinkedServerName varchar(50)
Declare @DS varchar(50)
Declare @username varchar(50)
Declare @pswd varchar(12)
Declare @TableExists int

Set @DS = 'test\TestDB'
Set @LinkedServerName = 'LinkedServerAutoAddDrop'
Set @username = 'ua'
Set @pswd = 'pass'
Set @TableExists = 0

if not exists(select * from sys.servers where name = @LinkedServerName)
BEGIN
    EXEC sp_addlinkedserver   
       @server=@LinkedServerName, 
       @srvproduct='',
       @provider='SQLNCLI', 
       @datasrc=@DS

    EXEC sp_addlinkedsrvlogin @LinkedServerName, N'false', NULL, @username, @pswd
    exec sp_serveroption @server=@LinkedServerName, @optname='rpc', @optvalue='TRUE'
    exec sp_serveroption @server=@LinkedServerName, @optname='rpc out', @optvalue='TRUE'
END


exec('IF (EXISTS (SELECT * FROM OPENQUERY([' + @LinkedServerName + '], ''select * from LinkedDB.INFORMATION_SCHEMA.TABLES Where TABLE_NAME = ''''TableName'''''')))
BEGIN
    exec (''Set ' + @TableExists + ' = 1'')

END')

IF (@TableExists = 1)
BEGIN
    exec('Insert Into ...')
END

Posted 10-Mar-14 4:43am

saleem_deek

Updated 10-Mar-14 4:46am

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 10-Mar-14 11:14am

Can you elaborate on 'I didn't succeed'?

saleem_deek 11-Mar-14 4:54am

The main problem is with "exec (''Set ' + @TableExists + ' = 1''). It throws an error that TableExists should be declared. Plus I tried other ways in writing this specific line but couldn't figure it out.

2 solutions

Solution 1

please!!!!! what is that you want to select a db in linked server its so simple

SQL

select * from servername.dbname.dbo.tablename
 

 



 --but if u want to check whether a table exists then do the following

 IF  NOT EXISTS (SELECT * FROM servername.dbname.sys.tables where name = @tablename)

BEGIN
CREATE TABLE [dbo].[YourTable](
    ....
    ....
    ....
) 

END

Posted 10-Mar-14 18:13pm

AndrewCharlz

Comments

saleem_deek 11-Mar-14 4:51am

Andrew no need to be so bothered with my post, take it easy.
I know it's simple normally, but I mentioned in my question what is specifically my problem, linked server name is a parameter so I can't use "select * from servername.dbname.dbo.tablename", and checking how a table exists in a simple query I know it too, but again, you didn't read my question well.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Accepted Answer · 2014-03-10T23:15:00

Solution 2

You problem is that you are using EXEC. EXEC is starts a new transaction that has no idea about your variables declared outside of it...
You have to learn and use sp_executesql fot things like this...
http://technet.microsoft.com/en-us/library/ms188001.aspx[^]

Posted 10-Mar-14 23:15pm

Kornfeld Eliyahu Peter

Comments

saleem_deek 11-Mar-14 6:24am

Thank you :)

Kornfeld Eliyahu Peter 11-Mar-14 6:25am

You're welcome