May 8, 2012 at 12:47 pm
Hello All,
I have taken it upon my self to build a client configuration database for my company. This is meant to be an automated discover process where all you need to do is create a linked server and this database and jobs will poll the linked server to import any relevant databases along with various information about the database and client.
1 particular piece of information I am trying to capture from each database is the version of the application. I am running into an error with the query below as the table does not exist but it seems SQL is still trying to validate the query even though the table doesn't exist. If I remove the query and place a print statement I get the else print statement of "Doesn't Exist".
IFEXISTS (SELECT 1 FROM MSVSQL04.M_Test_DB.sys.Objects WHERE Name = 'DBREGISTRY' and type = 'U')
BEGIN
SELECTLEFT(Value, CHARINDEX(' (',Value)-1) AS [Version]
FROMMSVSQL04.M_Test_DB.dbo.DBRegistry
WHERELabel = 'Version'
END
ELSE
PRINT 'Doesnt Exist'
Here is the error I am getting. The OLE DB provider "SQLNCLI10" for linked server "MSVSQL04" does not contain the table ""M_Test_DB"."dbo"."DBRegistry"". The table either does not exist or the current user does not have permissions on that table.
Well its exactly right the table does not exist my only guess is SQL is still trying to validate but not execute.
Has anyone run into this before?
Any help on this would be greatly appreciated.
Thank you,
Jeremy
May 8, 2012 at 1:00 pm
drgn38 (5/8/2012)
Hello All,I have taken it upon my self to build a client configuration database for my company. This is meant to be an automated discover process where all you need to do is create a linked server and this database and jobs will poll the linked server to import any relevant databases along with various information about the database and client.
1 particular piece of information I am trying to capture from each database is the version of the application. I am running into an error with the query below as the table does not exist but it seems SQL is still trying to validate the query even though the table doesn't exist. If I remove the query and place a print statement I get the else print statement of "Doesn't Exist".
IFEXISTS (SELECT 1 FROM MSVSQL04.M_Test_DB.sys.Objects WHERE Name = 'DBREGISTRY' and type = 'U')
BEGIN
SELECTLEFT(Value, CHARINDEX(' (',Value)-1) AS [Version]
FROMMSVSQL04.M_Test_DB.dbo.DBRegistry
WHERELabel = 'Version'
END
ELSE
PRINT 'Doesnt Exist'
Here is the error I am getting. The OLE DB provider "SQLNCLI10" for linked server "MSVSQL04" does not contain the table ""M_Test_DB"."dbo"."DBRegistry"". The table either does not exist or the current user does not have permissions on that table.
Well its exactly right the table does not exist my only guess is SQL is still trying to validate but not execute.
Has anyone run into this before?
Any help on this would be greatly appreciated.
Thank you,
Jeremy
The code is still parsed before the IF statement is executed. For what you are trying to accomplish, you will need to use dynamic sql to execute the query if the table exists.
May 8, 2012 at 1:08 pm
Thank you for your reply Lynn.
I already was as the Server and DBNames are passed in dynamically in a looping process. I guess I could do try to do dynamic with in dynamic :). Was just hoping there was a better way to do this. What I don't understand is say in your normal practice of IF table exists then drop table. Seems like if validation were an issue these would also fail.
OPEN DBs
FETCH NEXT FROM DBs INTO @ClientID, @ServerName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
-- GET VERSION INFO
SET@SQL = '
IFEXISTS (SELECT 1 FROM '+ @ServerName +'.'+ @DBName +'.sys.Objects WHERE Name = ''DBREGISTRY'')
BEGIN
SELECT
'''+ @DBName +'''
,LEFT(Value, CHARINDEX('' ('',Value)-1) AS [Version]
FROM'+ @ServerName +'.'+ @DBName +'.dbo.DBREGISTRY
WHERELABEL = ''Version''
END'
--INSERT INTO @VList
PRINT(@SQL)
FETCH NEXT FROM DBs INTO @ClientID, @ServerName, @DBName
END
CLOSE DBs
DEALLOCATE DBs
May 8, 2012 at 1:21 pm
drgn38 (5/8/2012)
Thank you for your reply Lynn.I already was as the Server and DBNames are passed in dynamically in a looping process. I guess I could do try to do dynamic with in dynamic :). Was just hoping there was a better way to do this. What I don't understand is say in your normal practice of IF table exists then drop table. Seems like if validation were an issue these would also fail.
OPEN DBs
FETCH NEXT FROM DBs INTO @ClientID, @ServerName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
-- GET VERSION INFO
SET@SQL = '
IFEXISTS (SELECT 1 FROM '+ @ServerName +'.'+ @DBName +'.sys.Objects WHERE Name = ''DBREGISTRY'')
BEGIN
SELECT
'''+ @DBName +'''
,LEFT(Value, CHARINDEX('' ('',Value)-1) AS [Version]
FROM'+ @ServerName +'.'+ @DBName +'.dbo.DBREGISTRY
WHERELABEL = ''Version''
END'
--INSERT INTO @VList
PRINT(@SQL)
FETCH NEXT FROM DBs INTO @ClientID, @ServerName, @DBName
END
CLOSE DBs
DEALLOCATE DBs
Your code above will not work. Please lookup dynamic sql in Books Online.
May 8, 2012 at 1:42 pm
Hello Lynn,
Sorry I am having issues finding the link do you happen to have that. I would love to take a look to see the proper way for writing dynamic SQL as I have never had any issues writing it this way. I am using the same dynamic type queries for about 4 other functions with out issues. The only issue I am having is the fact that it cant parse the query because the table doesn't exist?
Thank you,
Jeremy
May 8, 2012 at 1:45 pm
drgn38 (5/8/2012)
Hello Lynn,Sorry I am having issues finding the link do you happen to have that. I would love to take a look to see the proper way for writing dynamic SQL as I have never had any issues writing it this way. I am using the same dynamic type queries for about 4 other functions with out issues. The only issue I am having is the fact that it cant parse the query because the table doesn't exist?
Thank you,
Jeremy
Nevermind, I missed part of your code. You are fine.
May 8, 2012 at 1:55 pm
No worries thank you very much for your assistance.
May 10, 2012 at 9:15 am
I was able to get around this by using dynamic within dynamic as follows. If anyone knows a better way I am all ears.
OPEN DBs
FETCH NEXT FROM DBs INTO @ClientID, @ServerName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
-- GET VERSION INFO
SET@SQL = '
IFEXISTS (SELECT 1 FROM '+ @ServerName +'.'+ @DBName +'.sys.Objects WHERE Name = ''DBREGISTRY'')
EXEC(''
SELECT
'''''+ @DBName +'''''
,LEFT(Value, CHARINDEX('''' ('''',Value)-1) AS [Version]
FROM'+ @ServerName +'.'+ @DBName +'.dbo.DBREGISTRY
WHERELABEL = ''''Version'''''')
'
INSERT INTO @VList
EXEC(@SQL)
FETCH NEXT FROM DBs INTO @ClientID, @ServerName, @DBName
END
CLOSE DBs
DEALLOCATE DBs
Thank you,
Jerms
May 23, 2012 at 1:05 pm
Try Dynamic SQL
I had the same problem and resolved as code given below.
DECLARE @tSQLNvarchar(2000)
SET @tSQL = 'SELECT * FROM RemoteSQL.MyDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '''+'dbo'+''' AND TABLE_NAME = '''+'MyTablel'+''''
exec dbo.sp_executesql @tSQL
PRINT @@ROWCOUNT
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply