September 19, 2011 at 5:33 pm
Hi,
I am trying to run a query against each database, which has a particular column:
EXEC sp_MSforeachdb N'IF ''?'' LIKE ''MyDb__''
BEGIN
USE [?];
IF EXISTS (SELECT * FROM syscolumns (nolock) WHERE name = ''PriceListID'' and OBJECT_NAME(id) = ''Company'')
SELECT
''?'' DbName, C.*
FROM
Company C
WHERE
AND ISNULL(C.PriceListID, 0) = 0
END';
I am trying to avoid the error for the databases which do not have PricelistId column in Company table by using IF EXISTS statement, but I am still getting the error, that the column does not exist. If there an option to turn on/off to fix this?
Thanks.
September 20, 2011 at 2:48 am
The error comes from the algrebiser as it binds the query to the objects involved, it appears before execution, that's why the EXISTS doesn't help
You can change the select to dynamic SQL, that way it only gets parsed and run if the EXISTS returns true
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2011 at 11:18 am
Sorry guys if I did not get something but this scripts works for me:
EXEC sp_Msforeachdb
'
if left(''?'', 5) = ''MyDB_''
begin
use [?];
if exists (select * from syscolumns where name = ''PriceListID'' and object_name(id) = ''Company'')
select * from Company
end
'
IMO there is an error in original code while checking DB name.
September 20, 2011 at 11:35 am
Yuri, your code will work fine because it doesn't explicitly reference a column that doesn't exist.
This will fail though:
EXEC sp_Msforeachdb
'
if left(''?'', 5) = ''MyDB_''
begin
use [?];
if exists (select * from syscolumns where name = ''PriceListID'' and object_name(id) = ''Company'')
select PriceListID from Company
end
'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2011 at 12:04 pm
"The more I learn, the more I learn how little I know"-
Just now I got that problem was in WHERE clause that refers column name explicitly.
Thanks Gail, my bad 🙂
September 21, 2011 at 1:01 am
GilaMonster (9/20/2011)
The error comes from the algrebiser as it binds the query to the objects involved, it appears before execution, that's why the EXISTS doesn't helpYou can change the select to dynamic SQL, that way it only gets parsed and run if the EXISTS returns true
I've tried this and still got the same error:
EXEC sp_MSforeachdb N'IF ''?'' LIKE ''MyDb__''
BEGIN
DECLARE @s-2 nvarchar(4000)
SELECT @s-2 = ''USE [?];
IF EXISTS (SELECT * FROM syscolumns (nolock) WHERE name = "PriceListID" and OBJECT_NAME(id) = "Company")
SELECT "?" DbName, C.* FROM Company C
WHERE ISNULL(CE.PriceListID, 0) = 0
''
exec(@s)
END';
September 21, 2011 at 1:14 am
Dynamic SQL for the Select, not the exists.
The point is that you need the query with the column name to only be parsed if the exists returns true. Both the initial code and this last version have the exists and the select in the same scope and hence they will both be parsed before the exists runs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2011 at 1:34 am
GilaMonster (9/21/2011)
Dynamic SQL for the Select, not the exists.The point is that you need the query with the column name to only be parsed if the exists returns true. Both the initial code and this last version have the exists and the select in the same scope and hence they will both be parsed before the exists runs.
Sorry, but I've tried to put select only into dynamic SQL and the query returned nothing. I could not even print out that dynamically built select query.
Gail, would you be able to give me an example of what you are suggesting?
September 21, 2011 at 2:17 am
Untested, but I think this is close at least
EXEC sp_MSforeachdb N'DECLARE @s-2 VARCHAR(200);
USE ?;
IF ''?'' LIKE ''MyDb__''
BEGIN
IF EXISTS (SELECT * FROM sys.columns WHERE name = ''PriceListID'' and OBJECT_NAME(object_id) = ''Company'')
BEGIN
SET @s-2 = ''SELECT ''''?'''' AS DbName, C.* FROM Company C
WHERE ISNULL(C.PriceListID, 0) = 0''
EXEC(@s)
END
END
';
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2011 at 2:34 am
GilaMonster (9/21/2011)
Untested, but I think this is close at least
EXEC sp_MSforeachdb N'DECLARE @s-2 VARCHAR(200);USE ?;
IF ''?'' LIKE ''MyDb__''
BEGIN
IF EXISTS (SELECT * FROM sys.columns WHERE name = ''PriceListID'' and OBJECT_NAME(object_id) = ''Company'')
BEGIN
SET @s-2 = ''SELECT ''''?'''' AS DbName, C.* FROM Company C
WHERE ISNULL(C.PriceListID, 0) = 0''
EXEC(@s)
END
END
';
No output whatsoever, except:
Command(s) completed successfully.
September 21, 2011 at 6:45 am
Then you have no databases matching the name that have the table with column.
Add some print statements inside the two IFs so you can see which is not returning true.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2011 at 6:56 am
I tested this out on my local server, modified for tables and columns that I have and it worked fine (and returned expected results). So either you have no databases with the name MyDb__ (that's MyDB with 2 characters after DB) or you have no table called Company in those databases or the table Company does not have that column
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2011 at 6:57 am
Case sensitive server?
Got one here, really annoying as hell!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply