Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SHardy
Starting Member
35 Posts |
 Posted - 2006-03-08 : 08:07:26
|
| 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 1OLE 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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-08 : 08:44:51
|
| Hi SHardy, Welcome to SQL Team!"Why does it ignore the check and try to parse the INSERT INTO line when the source table is on a remote server?"Dunno!"Is there anyway to prevent this behaviour?"EXEC an SProc instead of the INSERT to achieve the same aim?Kristen |
 |
|
|
SHardy
Starting Member
35 Posts |
Posted - 2006-03-08 : 09:44:20
|
| Would the stored procedure have to be on the linked server? Or could it be in the local db?I am new to SQL Server, and have not yet touched upon stored procedures, but I will give it a go.Thanks,Simon |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-08 : 10:04:46
|
| You will mostly likely need to use EXEC() to do the INSERT statement, since it appears that SQL Server is trying to parse and validate the T-SQL before executing it.The good news is, if you use dynamic SQL like, you should be able to just loop through the table names yourself in 1 single command w/o scripting out a hundred of these statements.i.e.,For each Table in your spreadsheet- create a string variable containg the SQL to test for the table for existance and do the insert- Use EXEC() or sp_executeSQL() to run the SQLLoop |
|
|
|
|
|
|
|
|
"IF EXISTS..." against remote database