Running a conditional query on a database on a linked server generates error msg 7314

[MickelR]

Hi,

I am wondering if someone would be able to solve this mystery for me. I try to get information from tables on a linked server. Linked server is linked through SA account which has sysadmin rights on both the server as well as the linked server.

The idea is that I probe the information_schema.tables to see if the table exists, if it does I would like to insert the table data and number of records into a fact table. If the table does not exist, I would like to insert the table data with the message that the table does not exist into the same fact table. To accomplish this I used the following T-SQL query:

IF EXISTS(SELECT NULL FROM [LinkedServer].[Database].[INFORMATION_SCHEMA].[TABLES] WHERE TABLE_NAME = 'NonExistentTable')
BEGIN
INSERT INTO [dbo].[FactTable] SELECT 'nvarchar' AS DatabaseNaam, GETDATE() AS Datum, 'ReportName' AS Overzicht, 'NonExistentTable' AS Brontabel, count(*) AS Records, 'Ok' AS Opmerking FROM [LinkedServer].[Database].[dbo].[NonExistentTable]
END
ELSE
	INSERT INTO [dbo].[FactTable] SELECT 'nvarchar' AS DatabaseNaam, GETDATE() AS Datum, 'ReportName' AS Overzicht, 'NonExistentTable' AS Brontabel, 0 AS Records, 'Table does not exist' AS Opmerking

When I run this query on the local server, I get the error message:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "LinkedServer" does not contain the table ""LinkedDatabase"."dbo"."NonExistentTableName"". The table either does not exist or the current user does not have permissions on that table.

This message comes from the (first) condition that actually is not met. The second condition applies to the table in the error message, because the table does not exist. It looks like that the entire query is pre-validated before execution. How does that work with using the IF...THEN...ELSE function?

Here comes the real odd part: this entire query does work when it is directly executed on the linked server itself. I don't see how possible credentials can be in the way, since I do use the build-in SA account to link the servers together with the actual intention of preventing credential problems.

Any help would be appreciated.

 

[gmmastros]

Just out of curiosity, does this work?

Declare @SQL nvarchar(4000)

IF EXISTS(SELECT NULL FROM [LinkedServer].[Database].[INFORMATION_SCHEMA].[TABLES] WHERE TABLE_NAME = 'NonExistentTable')
BEGIN
    Set @SQL = '
               INSERT 
               INTO	[dbo].[FactTable] 
               SELECT	''nvarchar'' AS DatabaseNaam, 
                        GETDATE() AS Datum, 
                        ''ReportName'' AS Overzicht, 
                        ''NonExistentTable'' AS Brontabel, 
                        count(*) AS Records, 
                        ''Ok'' AS Opmerking 
               FROM	[LinkedServer].[Database].[dbo].[NonExistentTable]'
	exec (@SQL)
END
ELSE
    INSERT 
    INTO    [dbo].[FactTable] 
    SELECT  'nvarchar' AS DatabaseNaam, 
            GETDATE() AS Datum, 
            'ReportName' AS Overzicht, 
            'NonExistentTable' AS Brontabel, 
            0 AS Records, 
            'Table does not exist' AS Opmerking

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MickelR]

Hi, it does work, but I am baffled as to why. Would you mind if I said this does not make sense at all?

Thank you for your help, though. At least I can finish my project.

 

[gmmastros]

You said...

It looks like that the entire query is pre-validated before execution.

You were right. By building a sql string and executing it, only the string is validated, not the contents of the string. The contents of the string is only validated when it encounters the "Exec (@SQL)" part.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom