Copy the stored procedures between two MSSQL 2000 servers

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.



LVL 13
rhinocerosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
rhinocerosAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rhinocerosAuthor Commented:
Sorry friend,

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


:(
0
adatheladCommented:
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
adatheladCommented:
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
Anthony PerkinsCommented:
adathelad,

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

Grant Execute On ...

Anthony
0
rhinocerosAuthor Commented:
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
adatheladCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rhinocerosAuthor Commented:
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
adatheladCommented:
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
rhinocerosAuthor Commented:
Ok ic

Thank you very much

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.