[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 973
  • Last Modified:

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.



0
rhinoceros
Asked:
rhinoceros
  • 5
  • 4
  • 2
  • +1
1 Solution
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now