Solved

Copy the stored procedures between two MSSQL 2000 servers

Posted on 2003-10-23
12
954 Views
Last Modified: 2008-02-26
I want to copy specified database to another one online Mssql 2000 server.

- Two MSSQL server are not located on same AD, so I could not use "Copy Database Wizard". (no permisson on network)

- I only use "Export data". e.g. database "ABC" export from server A to server B,  but it only be copied all tables, not include my created procedures in stored procedures


1. How to copy all by "Export data" ?
2. On the other hand, also how can I do if  stored procedure copy only ?

Thanks help.



0
Comment
Question by:rhinoceros
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 9605293
You might use the Enterprise Manager to generate the "script" to build the stored procedures, and run that script on the other server.
Hope this helps
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9605317
Thanks reply.


But I don't know how to generate the "sciprt", can tell me more, or even give me the example if possible please ?


Thanks

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 9605326
Using the enterprise manager:
-> right-click the database
 -> All Tasks -> Generate SQL Scripts
Then, a form is opened which should be "self-explaining" ...
Cheers
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9605349
Sorry friend,

I know it, but I mean I don't know how to use "Generate SQL Script" to do my job ?


:(
0
 
LVL 23

Expert Comment

by:adathelad
ID: 9605423
Hi,

The easiest way for you to do it is click on the Stored Procedures section in your source database and select all the stored procedures you want to copy so they are highlighted. Then just do a right click -> All Tasks -> Generate Sql Scripts. This will automatically set everything up ready, so all you need to do is either:

a) click OK in the window that pops up and save the script to a file
or
b) click "Preview..." and then copy and paste the script it displays

HTH
0
 
LVL 23

Expert Comment

by:adathelad
ID: 9605435
In fact an ever quicker way would be to select the stored procedures as before, do Ctrl-C (Copy) and then open Query Analyser on the target database and do Ctrl-V (Paste). This will paste the scripts for the selected stored procedures you copied.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9606839
adathelad,

One word of caution:  This last approach does not include the permissions, as in:

Grant Execute On ...

Anthony
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9612000
Hi  adathelad,

>> a) click OK in the window that pops up and save the script to a file

I had done to follow your instruction, but how to restore the script file into new database.

>> do Ctrl-C (Copy) and then open Query Analyser on the target database and do Ctrl-V (Paste)

This step is only for each stored procedures copied (one by none) as I understood, it's impossible for large amount stored procedures files (over 100 files for my database).


This is my first time to do it, can help again please ? thanks !

0
 
LVL 23

Accepted Solution

by:
adathelad earned 50 total points
ID: 9612607
a) Open the .SQL file in Query Analyser on the target database on the target server and then execute the script by pressing F5 (or click green "Play" arrow in top toolbar)

b) No, you can copy them all at once by selecting multiple procedures (e.g. hold Ctrl and click each one or hold down shift and select a range.
Once copied just run in Query Analyser as described in a)
HTH
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9612699
Thanks, it can be generated the stored procedure in new databae.

But the steps a & b, some error came out

a) * only one
Cannot not add rows to sysdepends for the current stored procedure because it depends on on the missing object 'Getabc'. The stored procedure will still be created

'Getabc' is one of stored procedure


b)
Cannot not add rows to sysdepends for the current stored procedure because it depends on on the missing object 'Getabc'. The stored procedure will still be created

Cannot not add rows to sysdepends for the current stored procedure because it depends on on the missing object 'dbo.dt_adduserobject_vsc'. The stored procedure will still be created

Cannot not add rows to sysdepends for the current stored procedure because it depends on on the missing object 'dbo.dt_setpropertyid'. The stored procedure will still be created

......more



For these error messages, may have something will affect the stored procedure in new database ?

0
 
LVL 23

Expert Comment

by:adathelad
ID: 9613056
The stored procedures were created OK. Those warnings you got are because not all the objects used by a stored procedure are/were in the database when the CREATE script ran.

The warnings are just to let you know that the procedures have been created, but some of the objects they use (e.g. tables, other stored procedures) do not exist (or at least didn't exist when you ran the script)
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9618576
Ok ic

Thank you very much

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 70
t-sql month question 8 43
Report Builder 9 31
Increasing Identity length in sql server 4 20
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now