Error in Generating scripts

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  August 16, 2010 at 10:00 pm

  #236674

  Hi all,

  I'm using the following script to take scripts of all the sp's in the DB

  SET @method = 'Databases("testDB").' +'StoredProcedures("'

  + @SPName +

  '").Script' + '(' + CAST (@ScriptType AS CHAR) + ',"D:\BackUp\DBscripts ' + convert(varchar(12),getdate()) + '.sql")'

  EXEC sp_OAMethod @oServer, @method , @TSQL OUTPUT

  FETCH NEXT FROM CsrDB INTO @SPName

  I've recently renamed few SP's to get more clarity on the naming convention. After changing the names the script is taking the old SP names only. The "IF EXISTS " statement deletes the new SP'. But while creating it creates in the name of Old SP. How to resolve this.

  Regards

  Ami

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  August 17, 2010 at 4:19 am

  #1207727

  Anamika (8/16/2010)

  ---

  The "IF EXISTS " statement deletes the new SP'.

  I didnt get this statement is the post code so post complete code .

  Additionally check the Sp name with SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P'

  it must return the SP's modified name.

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 17, 2010 at 6:17 am

  #1207794

  if you used the GUI, you can rename a stored procedure without having changed it's underlying definition. that was at least true in SQL 2000 with Enterpise Manager.

  so i could create a procedure pr_find , but if i use the GUI to right click>>Rename the object, it WILL NOT change the underlying script that was stored , i believe. I jsuted tested it, and in SQL 2008, it does change the underlying definition...can someone check in SQL 2005?

  easy enought to test...just rename a procedure, then right click it and CREATE to new window.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Kenneth.Fisher

  SSC-Insane

  Points: 20396

  More actions

  August 17, 2010 at 9:31 am

  #1207972

  There is also as of 2005 a view called sys.procedures you can use. However don't forget to tie in to sys.schemas as your procedure is technicall named schema.procedure_name even if schema = "dbo" and if it isn't dbo you can run into some problems.

  Also if you use sp_rename or rename through the GUI it will change the name in the system catalogs without changing the creation script. Instead for SPs at least you should drop the old SP and create a new one. (Don't forget to check any permissions before you do the drop however.) Unless someone knows a better way?

  Kenneth

  Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  August 17, 2010 at 9:34 am

  #1207973

  Anamika (8/16/2010)

  ---

  Hi all,

  I'm using the following script to take scripts of all the sp's in the DB

  SET @method = 'Databases("testDB").' +'StoredProcedures("'

  + @SPName +

  '").Script' + '(' + CAST (@ScriptType AS CHAR) + ',"D:\BackUp\DBscripts ' + convert(varchar(12),getdate()) + '.sql")'

  EXEC sp_OAMethod @oServer, @method , @TSQL OUTPUT

  FETCH NEXT FROM CsrDB INTO @SPName

  I've recently renamed few SP's to get more clarity on the naming convention. After changing the names the script is taking the old SP names only. The "IF EXISTS " statement deletes the new SP'. But while creating it creates in the name of Old SP. How to resolve this.

  Regards

  Ami

  Instead of going through all of that, why don't you just read the definitions from sys.SQL_Modules???

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  August 17, 2010 at 11:23 pm

  #1208299

  Hi all,

  Thanks for the inputs.

  I've checked in 2005. the Script Procedure --> Create gives the correct modified SP name. But while creating from the mentioned script it is giving the old name.

  I've changed the script to take from

  SELECT name FROM SYSOBJECTS WHERE XTYPE = 'P'

  select object_name(object_id) from sys.sql_modules

  select distinct(routine_name) from TestDB.information_schema.routines

  the output of statements gives the new SP name But while creating the SP Create Script the SP's are created with the old name.

  Thanks,

  Regards,

  -Ami

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  August 18, 2010 at 12:07 am

  #1208314

  Anamika (8/17/2010)

  ---

  Hi all,

  Thanks for the inputs.

  I've checked in 2005. the Script Procedure --> Create gives the correct modified SP name. But while creating from the mentioned script it is giving the old name.

  I've changed the script to take from

  select object_name(object_id) from sys.sql_modules

  select distinct(routine_name) from TestDB.information_schema.routines

  the output of statements gives the new SP name But while creating the SP Create Script the SP's are created with the old name.

  Thanks,

  Regards,

  -Ami

  and what about this query SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P' is it also giving you old name ?

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  August 18, 2010 at 2:34 am

  #1208354

  Hi,

  SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P'

  output of this query gives the new SP name.

  My problem is while getting the create script for the same gets created in old name only.

  Thanks,

  Regards,

  -Ami

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  August 18, 2010 at 3:23 am

  #1208367

  Anamika (8/18/2010)

  ---

  Hi,

  SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P'

  output of this query gives the new SP name.

  My problem is while getting the create script for the same gets created in old name only.

  Thanks,

  Regards,

  -Ami

  you cant always rely on these DMVs for some information.try to use system objects like sysobjects , sysindexes

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  August 18, 2010 at 3:24 am

  #1208368

  Anamika (8/17/2010)

  ---

  Hi all,

  Thanks for the inputs.

  I've checked in 2005. the Script Procedure --> Create gives the correct modified SP name. But while creating from the mentioned script it is giving the old name.

  I've changed the script to take from

  SELECT name FROM SYSOBJECTS WHERE XTYPE = 'P'

  select object_name(object_id) from sys.sql_modules

  select distinct(routine_name) from TestDB.information_schema.routines

  the output of statements gives the new SP name But while creating the SP Create Script the SP's are created with the old name.

  Thanks,

  Regards,

  -Ami

  post your complete sql script , i am sure your are overlooking something

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  August 18, 2010 at 4:10 am

  #1208379

  Hi,

  DECLARE @SPName VARCHAR(150)

  DECLARE CURSCRIPT CURSOR FOR

  SELECT name FROM SYSOBJECTS WHERE XTYPE = 'P'

  DECLARE @oServerint

  DECLARE @methodvarchar(300)

  DECLARE @TSQLvarchar(4000)

  DECLARE @ScriptType int

  EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT

  EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'

  EXEC sp_OAMethod @oServer, 'Connect', NULL, 'server1'

  SET @ScriptType =1|4|32|64|256|262144

  open curscript

  fetch next from curscript into @SPName

  WHILE @@FETCH_STATUS = 0

  begin

  SET @method = 'Databases("testdb").' +'StoredProcedures("'

  + @SPName +

  '").Script' + '(' + CAST (@ScriptType AS CHAR) + ',"D:\DailyBackUp\Scripts ' + convert(varchar(12),getdate()) + '.sql")'

  EXEC sp_OAMethod @oServer, @method , @TSQL OUTPUT

  FETCH NEXT FROM curscript INTO @SPName

  end

  CLOSE curscript

  DEALLOCATE curscript

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  August 18, 2010 at 6:25 pm

  #1208837

  Bhuvnesh (8/18/2010)

  ---

  Anamika (8/18/2010)

  ---

  Hi,

  SELECT * FROM [font="Arial Black"]SYSOBJECTS[/font] WHERE XTYPE = 'P'

  output of this query gives the new SP name.

  My problem is while getting the create script for the same gets created in old name only.

  Thanks,

  Regards,

  -Ami

  you cant always rely on these DMVs for some information.try to use system objects like [font="Arial Black"]sysobjects [/font], sysindexes

  ??? :blink:

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  August 18, 2010 at 9:59 pm

  #1208877

  Jeff Moden (8/18/2010)

  ---

  Bhuvnesh (8/18/2010)

  ---

  Anamika (8/18/2010)

  ---

  Hi,

  SELECT * FROM [font="Arial Black"]SYSOBJECTS[/font] WHERE XTYPE = 'P'

  output of this query gives the new SP name.

  My problem is while getting the create script for the same gets created in old name only.

  Thanks,

  Regards,

  -Ami

  you cant always rely on these DMVs for some information.try to use system objects like [font="Arial Black"]sysobjects [/font], sysindexes

  ??? :blink:

  jeff i didnt get you here

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Kenneth.Fisher

  SSC-Insane

  Points: 20396

  More actions

  August 19, 2010 at 8:30 am

  #1209156

  There are of course a number of different system views that you can use (and just to note everything I've noticed in the above post has been system VIEWS not tables). My personal favorites are sysobjects and syscomments. If you look at the results from this code:

  select * from sysobjects where name = 'kentest'

  select * from syscomments where id in (select id from sysobjects where name = 'kentest')

  sp_rename 'kentest','kentemp'

  select * from sysobjects where name = 'kentemp'

  select * from syscomments where id in (select id from sysobjects where name = 'kentemp')

  You should notice that while the NAME of the table has been changed the underlying code (in syscomments, sys.sql_modules, information_schema.routines etc) still shows CREATE kentest. The only way (to my knowledge) to fix the problem is going to be to drop the stored procedure and re-create it with the new name.

  Kenneth

  Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  August 19, 2010 at 10:05 pm

  #1209448

  Hi All,

  Thanks for everyone's input.

  What ever you use sys.objects or information schemas the sp_OAMethod gives the script with the old SP name only.

  When you right on

  SP name -->Script Stored Procedure and the

  Tasks--> generate scripts gives the correct name.

  As I put this in Sql Agent Job I've dropped & recreated those renamed SP's.

  Thanks,

  Best Regards,

  Ami.