SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Copying Triggers from One Db to another
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amitranjan
Starting Member

India
45 Posts

Posted - 02/23/2010 :  02:45:32  Show Profile  Reply with Quote
Hi, I have a databse in sql 2008 in which i have triggers on Insert, Update and Delete. I am also having a replica of same db say backup where i need to copy all the triggers using scripts or any other method. I dont want to copy database.


Thanks
AMit

amit Ranjan

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2010 :  03:17:08  Show Profile  Reply with Quote
You can generate a script (in SSMS) of all the triggers you want to copy, and then run that script on the other machine.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/23/2010 :  03:21:38  Show Profile  Visit webfred's Homepage  Reply with Quote
http://mangalpardeshi.blogspot.com/2009/03/script-all-store-procedures-in-database.html
There you can also see a select to create all triggers in a database


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/23/2010 :  03:46:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Here is mine that scripts out to text file
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/13/script-out-procedures-and-functions-part-2.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2010 :  03:53:57  Show Profile  Reply with Quote
Personally I haven't found such a script that work. I don't know how SSMS does it, but all scripts I have seen (and sp_helptext) split overly long lines and truncate after the first page of SProc's script.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/23/2010 :  04:14:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Personally I haven't found such a script that work. I don't know how SSMS does it, but all scripts I have seen (and sp_helptext) split overly long lines and truncate after the first page of SProc's script.


I am not sure. But when I tried my script it scripted out the script to text file as you see when you query sp_helptext 'procedure name'

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2010 :  04:50:12  Show Profile  Reply with Quote
sp_helptext (source) says:

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/

so I assume that is organised to work with Query Analyser etc. with column width set to 256 characters.

There's a fairly significant amount of code in that SProc, so I doubt the "Select * From syscomments" style solutions are going to be up to much!
Go to Top of Page

amitranjan
Starting Member

India
45 Posts

Posted - 02/23/2010 :  05:41:07  Show Profile  Reply with Quote
SELECT O.Name as ProcName
,M.Definition as CreateScript
,O.Create_Date
,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id


@ All
I tried above script from mangal pardeshi and found it working with a small change. After modified above to this

SELECT M.Definition as CreateScript
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id


this gave me all triggers of that database. Then i right clicked and exprted that as CSV file. Now opened it in Notepad++ or Sql Query Window and added

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

at the beginning of each triger and executed on the desired database. And it ran!!!..

amit Ranjan
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/23/2010 :  05:44:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also try the Link I posted. It scripts out all the triggers to a text file seperated by GO
Try it and let us know the result

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/23/2010 :  05:50:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

sp_helptext (source) says:

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/

so I assume that is organised to work with Query Analyser etc. with column width set to 256 characters.

There's a fairly significant amount of code in that SProc, so I doubt the "Select * From syscomments" style solutions are going to be up to much!


I still dont understand your point. Are you saying that usage of sp_helptext is not reliable?

This is the code that script out the script of sp_helptext

Can you run this and see if all texts are exported?


create table scripts(sps varchar(8000))
 
declare @texts varchar(1000)

select @texts='sp_helptext '''+'sp_helptext'+'''' 
EXEC('insert into scripts(sps) EXEC '+ @texts)         
insert into scripts(sps) 
select 'GO'
EXEC master..xp_cmdshell 'bcp "select * from dworksweb..scripts" queryout "c:\scripts.txt" -c -T' 
drop table scripts




Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 02/23/2010 05:51:53
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/23/2010 :  05:54:49  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by amitranjan

SELECT O.Name as ProcName
,M.Definition as CreateScript
,O.Create_Date
,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id


@ All
I tried above script from mangal pardeshi and found it working with a small change. After modified above to this

SELECT M.Definition as CreateScript
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id


this gave me all triggers of that database. Then i right clicked and exprted that as CSV file. Now opened it in Notepad++ or Sql Query Window and added

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

at the beginning of each triger and executed on the desired database. And it ran!!!..

amit Ranjan


Fine!


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

amitranjan
Starting Member

India
45 Posts

Posted - 02/23/2010 :  05:56:03  Show Profile  Reply with Quote
quote:
Originally posted by webfred

quote:
Originally posted by amitranjan

SELECT O.Name as ProcName
,M.Definition as CreateScript
,O.Create_Date
,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id


@ All
I tried above script from mangal pardeshi and found it working with a small change. After modified above to this

SELECT M.Definition as CreateScript
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id


this gave me all triggers of that database. Then i right clicked and exprted that as CSV file. Now opened it in Notepad++ or Sql Query Window and added

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

at the beginning of each triger and executed on the desired database. And it ran!!!..

amit Ranjan


Fine!


No, you're never too old to Yak'n'Roll if you're too young to die.



Thanks you r a life saver...

amit Ranjan
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2010 :  05:56:58  Show Profile  Reply with Quote
"SELECT M.Definition as CreateScript
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id
"

Like I said, I don't think these simple SELECTs work 100%.

This only selects the first 8,000 characters from the trigger. Maybe yours are smaller than that, maybe if I used BCP or somesuch I would work around that limit.

Much easier to just use SSMS, right click, generate script, ...

All the ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING etc are correctly taken care off (you are assuming they should all be ON, that's not necessarily/universally the case) ... and maybe there are other things SSMS does which are potential pitfalls.

If you want to export your objects as a scheduled task (to put them in version-control-system, or some such reason, then fine. I just don't think it is worth it for a one-off.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/23/2010 :  06:00:05  Show Profile  Visit webfred's Homepage  Reply with Quote
A trigger with 8k chatacters would be the work of the devil


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2010 :  06:17:20  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan
I still dont understand your point. Are you saying that usage of sp_helptext is not reliable?


If you have a line in your SProc with more than 256 characters sp_HelpText will insert line-breaks at 256 character positions(s)

quote:

Can you run this and see if all texts are exported?


I did try it in SQL 2008. I get:

output
----------------------------------------------------------------------------------- 
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL

but I did SELECT * FROM SCRIPTS and the lines are split at 256 character position

Here's an example

CREATE PROCEDURE TEMP_LongLine
AS
BEGIN
-- NOTE: This needs to be all on one line!

--..!....1....!....2....!....3....!....4....!....5....!....6....!....7....!....8....!....9....!..100 ...!....1....!....2....!....3....!....4....!....5....!....6....!....7....!....8....!....9....!..200 ...!....1....!....2....!....3....!....4....!....5....XY....6....!....7....!....8....!....9....!..300

SELECT 1
END
GO
sp_helptext 'TEMP_LongLine'
GO
DROP PROCEDURE TEMP_LongLine
GO

gives me

Text
----------------------------------------------------------- 
CREATE PROCEDURE TEMP_LongLine
AS
BEGIN
--..!....1....!....2....!....3....!....4....!....5...   ...200....!....1....!....2....!....3....!....4....!....5....X
Y....6....!....7....!....8....!....9....!..300
SELECT 1
END

I've chopped out the middle of the line to keep this post reasonable width, but I'm sure you get the idea
(My Query Analyser is set to column-width of 8,000 chars)

Edited by - Kristen on 02/23/2010 06:20:58
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/23/2010 :  07:35:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I get your point now.
So do you think there is a change that a word can be splitted to next line that would cause syntax error?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2010 :  09:27:25  Show Profile  Reply with Quote
If it was in a quoted string that would probably change the behaviour of the application (but not upset SQL)

sp_HelpText goes to a lot of trouble to split the text at 256 character boundary ...

... and also to bolt-back-together the multiple (8,000 byte) text pages ...

sys.sql_modules uses object_definition(sys.sysschobjs.id) - which seems to only get the first 8,000 characters ...

sp_HelpText is interrogating sys.syscomments - which itself is a VIEW to bolt-back-together again all the (new) tables that have scripts in them.

Would make more sense (with VARCHAR(MAX) now available etc.) to go straight to the underlying table I reckon ...

Maybe there is a, newer, replacement SProc for sp_HelpText that gets the whole script?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/23/2010 :  10:18:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

If it was in a quoted string that would probably change the behaviour of the application (but not upset SQL)

sp_HelpText goes to a lot of trouble to split the text at 256 character boundary ...

... and also to bolt-back-together the multiple (8,000 byte) text pages ...

sys.sql_modules uses object_definition(sys.sysschobjs.id) - which seems to only get the first 8,000 characters ...

sp_HelpText is interrogating sys.syscomments - which itself is a VIEW to bolt-back-together again all the (new) tables that have scripts in them.

Would make more sense (with VARCHAR(MAX) now available etc.) to go straight to the underlying table I reckon ...

Maybe there is a, newer, replacement SProc for sp_HelpText that gets the whole script?


1 I dont think a single line of actual stored procedure may exceed 8000 characters in real life (One can write but very difficult to maintain and read)

2 When query on object_defnition (object_id('myproc')) it displays first 8000 characters. But when you bcp out it exports all characters

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2010 :  10:47:03  Show Profile  Reply with Quote
I agree 8,000 characters per-line would be a reasonable limit - whereas I find I exceed 256 quite commonly.

However, the 8,000 characters (or whatever) line may have to "span" two object_defnition (object_id('myproc')) blocks - but if they are silently concatenated when you use BCP you are "good-to-go"!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000