"IF EXISTS..." against remote database

[SHardy]

Hi,

I was trying to perform a one of task to gather information from LOADS of tables from two different SQL Server 2000 DBs. They are both similar, but one is the backend of a LIVE system, the other is the backend of a TEST system. As such, there are likely to be several structural differences between them.

Also, they reside on different servers.

I have collated a load of table & field details in a spreadsheet, and produced a load of SQL commands to copy into Query Analyzer. I then run the code from a local database.

This is in the following format for each table/field combo:

IF EXISTS (SELECT * FROM [remoteserver].[dbname].dbo.[sysobjects] WHERE [name] = (N'tablename'))
INSERT INTO SomeLocalTable (SomeField) SELECT [AnyField] AS [SomeField] FROM [remoteserver].[dbname].dbo.[tablename]

Therefore testing that the table exists before trying to extract data from it.

However, when it comes across a table that does not exist the following error is returned:

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'remoteserver' does not contain table '"dbname"."dbo"."tablename"'. The table either does not exist or the current user does not have permissions on that table.

The IF EXIST is supposed to stop this happening.

Now, I know that this would work if it was all run on the same server as the source table(s).

Why does it ignore the check and try to parse the INSERT INTO line when the source table is on a remote server?

Is there anyway to prevent this behaviour?

I know that some tables will not exist on one version of the DB, and I want to skip past those lines.

Many thanks,
Simon

 

[vongrunt]

Perhaps there is another remote object named 'Tablename'. Add AND xtype = 'U' in WHERE clause. What happens?

Another possibility is - not enough permissions to access that table.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SHardy]

What would be the expected effect of adding?:

AND xtype = 'U'

I'm guessing that this simply limits the IF EXIST clause to return TRUE for user tables.

This made no difference to me. A user table could still exist in the TEST sysyem and not in the LIVE system.

Also, permissions are definitely not a problem.

I am guessing that it is related to using linked servers, but I don't know why or how to prevent it.

 

[vongrunt]

> This made no difference to me. A user table could still exist in the TEST sysyem and not in the LIVE system.

What if remote DB has stored proc, function or something named 'tablename'? EXISTS() check without xtype='U' would pass, but INSERT INTO would fail.

But if you said that made no difference... this query:

SELECT * FROM [remoteserver].[dbname].dbo.[sysobjects] WHERE [name] = (N'tablename')

returns one row?

My next guess is (was?) naturally about permissions. And third one - maybe missing BEGIN/END in T-SQL code (after EXISTS() check)?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SHardy]

The

SELECT * FROM [remoteserver].[dbname].dbo.[sysobjects] WHERE [name] = (N'tablename')

does NOT return any rows. Therefore the IF EXISTS should return FALSE and skip the INSERT INTO.

I have also tried adding BEGIN/END and I have tried using OPENQUERY. Neither made any difference.

If I run the same code on the remote server then it runs with no problem. It appears that it is related to the fact that the source (FROM) for the INSERT INTO statement is on a linked server. This fact seems to make it parse the INSERT INTO statement even though the IF EXISTS statement will prevent it from actually running.

Hope this makes sense.

I think the best way of seeing this in action is to duplicate the problem, if you can.

 

[vongrunt]

Weird thing, I simulated everything a hour ago - no error(s) we are talking about (SQL2000 SP4, Std/Dev/Ent).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]