SQLDMO generating CREATE script for unexisting SP.
Hi...
I need some help with SQLDMO.
I'm developing an automation to generate versions of a set of applications automatically.
The SQL part is being held by SQLDMO, generating the drop and create scripts.
The problem is that one of my stored procedures is being scripted like this:
/****** Object: StoredProcedure [dbo].[sp_SearchEntity] Script Date: 06/06/2007 14:39:08 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Sear
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[sp_SearchCustomer]
...
1. As you can see the SP is identified as sp_SearchEntity as it should be but the actual create statement is creating a sp_SearchCustomer that doesn't even exist.
2. Generating the script from SQL Management Studio this sp_SearchCustomer doesn't appear.
3. The DROP statement isn't scripted by SQLDMO... only the Create.
4. sp_SearchEntity exists and must be scripted. SQLDMO scripts this SP DROP correctly but on the CREATE changes the name to SearchCustomer where the CREATE code is for the sp_SearchEntity SP.
I can't recall if the SP once was named sp_SearchCustomer but now I'm sure there is no sp_SearchCustomer on my database.
Any ideas?
Thanks!
Alex
I need some help with SQLDMO.
I'm developing an automation to generate versions of a set of applications automatically.
The SQL part is being held by SQLDMO, generating the drop and create scripts.
The problem is that one of my stored procedures is being scripted like this:
/****** Object: StoredProcedure [dbo].[sp_SearchEntity] Script Date: 06/06/2007 14:39:08 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Sear
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[sp_SearchCustomer]
...
1. As you can see the SP is identified as sp_SearchEntity as it should be but the actual create statement is creating a sp_SearchCustomer that doesn't even exist.
2. Generating the script from SQL Management Studio this sp_SearchCustomer doesn't appear.
3. The DROP statement isn't scripted by SQLDMO... only the Create.
4. sp_SearchEntity exists and must be scripted. SQLDMO scripts this SP DROP correctly but on the CREATE changes the name to SearchCustomer where the CREATE code is for the sp_SearchEntity SP.
I can't recall if the SP once was named sp_SearchCustomer but now I'm sure there is no sp_SearchCustomer on my database.
Any ideas?
Thanks!
Alex
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Hi Alex,
Sorry for the delayed response.
I'm really not sure why its not working. When I get that kind of "disconnect", I do the drop/recreate/recompile route and it usually fixes the problem. I know Enterprise Manager is generating the scripts correctly, but did you try to drop and recreate the object?
Sorry for the delayed response.
I'm really not sure why its not working. When I get that kind of "disconnect", I do the drop/recreate/recompile route and it usually fixes the problem. I know Enterprise Manager is generating the scripts correctly, but did you try to drop and recreate the object?
ASKER
Hi...
Doing as you say works, no problem.
The thing is that I'm managing an application (in fact 17 that work together) and each application has its own database.
So I'm dealing with a 17 databases, some with 100+ tables and around 600 SP's.
I don't have the exact number here, but surely I'm dealing with more that 5000 db objects here and I don't know witch have been renamed...
The ultimate solution will be generating the script on the EM for the entire DB and apply it on another instance...
Meanwhile, I've being thinking about this, and I came up with a RegEx solution.
While generating the scripts, I can validate if the name is correct and if not correct it "by hand".
I'll give it a shot tomorrow or so and get back here.
Anyway, keep me posted if you have any more ideas! :)
Thanks!
Alex
Doing as you say works, no problem.
The thing is that I'm managing an application (in fact 17 that work together) and each application has its own database.
So I'm dealing with a 17 databases, some with 100+ tables and around 600 SP's.
I don't have the exact number here, but surely I'm dealing with more that 5000 db objects here and I don't know witch have been renamed...
The ultimate solution will be generating the script on the EM for the entire DB and apply it on another instance...
Meanwhile, I've being thinking about this, and I came up with a RegEx solution.
While generating the scripts, I can validate if the name is correct and if not correct it "by hand".
I'll give it a shot tomorrow or so and get back here.
Anyway, keep me posted if you have any more ideas! :)
Thanks!
Alex
Will do.
ASKER
Hi,
the problem is that SQLServer doesn't handle renames properlly.
It doesn't update the syscomments table, so the scripts end up wrong.
Although this thread didn't actually gave me the solution it pointed me to it.
Thanks,
Alex
the problem is that SQLServer doesn't handle renames properlly.
It doesn't update the syscomments table, so the scripts end up wrong.
Although this thread didn't actually gave me the solution it pointed me to it.
Thanks,
Alex
ASKER
Your link describes quite well what's going on with me here.
But I still can't find an answer for the Enterprise Manager scripts are generated correctly and mine, using, SQLDMO don't.
I allways eard that Enterprise MAnager tool uses SQLDMO for its scripting functionalities.
Why isn't my implementation working?
Thanks!
Alex