June 20, 2011 at 9:33 am
Hi,
We have just tested a migration to SQL 2008 and we are getting the error "ambiguous column name", now I know what this is, the problem we have is we need to check all 300+ sp's to find the instances. Does anyone know of a quick way to find these? a script, a tool, etc
Thanks,
itlee.
June 20, 2011 at 9:38 am
Here's something I built for SQL 2005 upgrade testing. It should work for 2008 as well. It will find any compile-time problems with procs and UDFs, and can be extended pretty easily.
I borrowed parts of it from a script on this site. I don't remember who originally posted that.
Try it on a dev copy of your database, see if it finds what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2011 at 9:57 am
Did you mean to attach or link something?
June 21, 2011 at 6:39 am
Sure did. Kids, the lesson here is don't post when suffering major sleep deprivation.
Here you go.
DECLARE
@sql VARCHAR(MAX),
@Text VARCHAR(MAX),
@ProcName VARCHAR(500),
@ProcName1 VARCHAR(500)
DECLARE @T TABLE (
ProcName VARCHAR(200),
sql VARCHAR(MAX),
ErrorMessage VARCHAR(4000))
DECLARE c CURSOR
FOR SELECT
name,
definition
FROM
sys.all_objects
INNER JOIN sys.sql_modules
ON all_objects.object_id = sql_modules.object_id
WHERE
type IN ('p', 'tf', 'if')
AND name NOT LIKE 'dt_%'
AND name NOT LIKE 'sys_%'
OPEN C
FETCH NEXT FROM c INTO @ProcName, @Text
WHILE @@FETCH_STATUS = 0
BEGIN
SET @text = REPLACE(@text, @ProcName, @ProcName + 'CreateTest') -- change proc name
BEGIN TRY
EXEC (@text) -- try to create the proc
INSERT
@T
VALUES
(
@ProcName,
@text,
ERROR_MESSAGE()) -- record procs that couldn't be created
END TRY
BEGIN CATCH
INSERT
@T
VALUES
(
@ProcName,
@text,
ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH
IF EXISTS ( SELECT
*
FROM
sys.all_objects
WHERE
name LIKE '%' + @procname + 'createtest'
AND type = 'p' )
BEGIN
SET @sql = 'drop procedure '
+ (SELECT
name
FROM
sys.all_objects
WHERE
name LIKE '%' + @procname + 'createtest'
AND type = 'p')
EXEC (@sql)
END
IF EXISTS ( SELECT
*
FROM
sys.all_objects
WHERE
name LIKE '%' + @procname + 'createtest'
AND type IN ('if', 'tf') )
BEGIN
SET @sql = 'drop function '
+ (SELECT
name
FROM
sys.all_objects
WHERE
name LIKE '%' + @procname + 'createtest'
AND type IN ('if', 'tf'))
EXEC (@sql)
END
FETCH NEXT FROM c INTO @ProcName, @Text
END
CLOSE c
DEALLOCATE c
SELECT
*
FROM
@T
go
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 21, 2011 at 7:20 am
Nice script!!!
June 21, 2011 at 8:53 am
wow, that is a great script, you saved us hours of work. in the end we found 14 sp's that wouldn't parse/compile.
this script is going in my toolbox.
thank you very much.
itlee.
June 22, 2011 at 8:20 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 22, 2011 at 1:38 pm
Gus,
I just put that one in my tool box.
Todd Fifield
June 22, 2011 at 1:54 pm
Just keep in mind, everyone who uses it, all it tests for is "does it compile". It doesn't make sure it works right, it just tests compilation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 22, 2011 at 2:58 pm
Very nice club to have in the bag. Saved.
I had to qualify this part
FROM
sys.all_objects
INNER JOIN sys.sql_modules
ON all_objects.object_id = sql_modules.object_id
by adding the sys after the ON clause. Does that not cause an error for you guys?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 22, 2011 at 3:03 pm
toddasd (6/22/2011)
Very nice club to have in the bag. Saved.I had to qualify this part
FROM
sys.all_objects
INNER JOIN sys.sql_modules
ON all_objects.object_id = sql_modules.object_id
by adding the sys after the ON clause. Does that not cause an error for you guys?
Nope and I'm on a )*(/$"*(U case sensitive server. Sql 2005 standard sp3-ish 32 bit.
June 22, 2011 at 3:29 pm
Ninja's_RGR'us (6/22/2011)
Nope and I'm on a )*(/$"*(U case sensitive server.
Ya, major PITA. Screw everything about that. :pinch:
On both my 2005 and 2008 servers, I get
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "all_objects.object_id" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "sql_modules.object_id" could not be bound.
without the qualifiers. I don't understand...?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 24, 2011 at 10:08 am
toddasd (6/22/2011)
Ninja's_RGR'us (6/22/2011)
Nope and I'm on a )*(/$"*(U case sensitive server.
Ya, major PITA. Screw everything about that. :pinch:
On both my 2005 and 2008 servers, I get
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "all_objects.object_id" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "sql_modules.object_id" could not be bound.
without the qualifiers. I don't understand...?
What compatibility level is the database you're running it in?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 24, 2011 at 10:14 am
Bingo. Compatability was 80.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply