How can I migrate all objects alogwith all constraints,SP,Triggers etc from Development instance

Hi fellows,

I have to migrate all objects alogwith all constraints,SP,Triggers, indexes etc from Development instance to Production instance of a DB, all those things are created through wizard ie. Sql server 2000 Enterprise Manager. if i use DTS it only mirates data along with tables and views but constraints,SP,Triggers, indexes etc not yet copied.

can any body help me how can I solve this problem by copying all objects alogwith all constraints,SP,Triggers etc from Development instance to Production instance.

This is Sql server 2000 Cluster environment.

thanks in advance for any help

rahman

I would script all the objects out using Enterprise Manager and then run them all in the other environment.

-Jamie

|||

Dear Jamie

Thank you very much for your reply

would you like to give me a hence to write script for all the object's constraints (ie. Default,Check,Primary Key, Foreign Key etc) and indexes using Enterprise Manager.

I am new in SQL Server

Thanks in advance

|||

1. Right-click on an object.

2. Point at All tasks--> Generate SQL Script...

-Jamie

|||

Dear Jamie

Thanks for your reply.

I know this script only create table/view but not any constrains attached with it. As you know DTS also creates Table/views and imports the data too.

it'll not solve the problem, I m trying to write scripts for constraints getting info. from some views of Information_Schema.

|||

Thanks Jamie,

I didn't see the options available in Options Tab of All tasks--> Generate SQL Script

thank you very much

sql

How can I migrate all objects alogwith all constraints,SP,Triggers etc from Development inst

Hi fellows,

I have to migrate all objects alogwith all constraints,SP,Triggers, indexes etc from Development instance to Production instance of a DB, all those things are created through wizard ie. Sql server 2000 Enterprise Manager. if i use DTS it only mirates data along with tables and views but constraints,SP,Triggers, indexes etc not yet copied.

can any body help me how can I solve this problem by copying all objects alogwith all constraints,SP,Triggers etc from Development instance to Production instance.

This is Sql server 2000 Cluster environment.

thanks in advance for any help

rahman

I would script all the objects out using Enterprise Manager and then run them all in the other environment.

-Jamie

|||

Dear Jamie

Thank you very much for your reply

would you like to give me a hence to write script for all the object's constraints (ie. Default,Check,Primary Key, Foreign Key etc) and indexes using Enterprise Manager.

I am new in SQL Server

Thanks in advance

|||

1. Right-click on an object.

2. Point at All tasks--> Generate SQL Script...

-Jamie

|||

Dear Jamie

Thanks for your reply.

I know this script only create table/view but not any constrains attached with it. As you know DTS also creates Table/views and imports the data too.

it'll not solve the problem, I m trying to write scripts for constraints getting info. from some views of Information_Schema.

|||

Thanks Jamie,

I didn't see the options available in Options Tab of All tasks--> Generate SQL Script

thank you very much

How can I implement error handling within a SQL script that uses b

I am trying to incorporate error handling into a SQL script that creates
several database objects.
Those objects are referenced later in the script by other blocks of code.
It is my understanding that I need to use a new batch to reference an object
that was just created. I can do this by inserting a GO statement after the
object creation statement.
But how do I implement error handling within a SQL script that uses batches?
IOW I don't want a subsequent batch to execute if a previous batch failed.
If I didn't have separate batches in my script I could use a RETURN or GOTO
statement to handle errors. But with batches I have the following problem:
--use RETURN
PRINT '1'
GO
PRINT '2'
RETURN
GO
PRINT '3'
--OUTPUT
1
2
3
--use GOTO
PRINT '1'
GO
PRINT '2'
GOTO error
GO
PRINT '3'
ERROR:
PRINT 'ERROR'
--OUTPUT
1
Server: Msg 133, Level 15, State 1, Line 2
A GOTO statement references the label 'error' but the label has not been
declared.
3
ERROR
Is the only solution to break up the script to run as indivifual batchs and
then evaluate each one after execution?Use the
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE
id=object_id(@.v_objectname))
BEGIN
-- start processing
END
ELSE
BEGIN
PRINT @.v_objectname + ' was not created'
END|||Do you need a new batch to reference an object created in the current bacth?
try this
create table t1 (A int)
insert into t1 values(1)
select * from t1
drop table t1
Or did I misunderstand u?|||On Wed, 31 May 2006 10:36:02 -0700, Dave wrote:
(snip)
>But how do I implement error handling within a SQL script that uses batches
?
> IOW I don't want a subsequent batch to execute if a previous batch failed.[/color
]
Hi Dave,
There's no good answer for this. Since the cient sends just one batch to
the server, waits for it to execute, then sends the other one, there is
no simple way to have an error in batch #1 abort batch #2.
There are kludges, though. The easiest (but nastiest) is to use a
RAISERROR with a severity of at least 20 - those are considered fatal,
and prompt SQL Server to terminate the connection after sending the
error message.
Somewhat more work (but still the version I'd prefer) is to use a table
to carry over status information. This can be a temp table. For
instance:
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
Hugo Kornelis, SQL Server MVP

How can I get a list of all objects accessible by a login using sys* tables

How can I get a list of all objects (like tables, views, stored procedures, etc) accessible by a login (user), or vice versa?

The question has been answered in this thread located in the SMO section of the forum.