Solved

Copy the stored procedures between two MSSQL 2000 servers

Posted on 2003-10-23
12
970 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 143

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 143

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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