MSsql replication on stored proceedures

I tried running a DTS package to copy over a database and it's stored proceedures, and it errored saying "could not create database object" (real descriptive huh?)

So i just imported the data over, great fine. Now i need to copy over all the Stored proceedures, is there an easy way to do this too, and secondly, if you have any idea what that error message could pertain to, please drop a reply, thanks, anything will help.

By far the easiest way is to open up Enterprise Manager, open up the database in question and navigate to the stored procedures node. Now, right click on any user procedure, select All Tasks and Generate Script. You will be now be presented with a dialog that lets you select what objects you want to script. Click on Show all and check the box next to All Procedures. Finally press the preview button. After a short period a window will come up with a script for generating all of the stored procedures in your database. Just open up a query analyzer window to the new database, paste in the script and hit run. All done !!

--- messorian

edit: almost forgot, that is with sql 2000, I assume it will work in 7 but, your mileage may very.

Ok, that is quite the hot tip, but it is more of a one time only solution. This will be a DR (obviously if i'm doing replications) so if new stored proceedures are created... i'll need to get those, and i can't go in and manually do this every 1 hour.....

In that case you should seriously consider using SQL Servers built in backup commands. It will really be worth your time to look in BOL (boooks online) and read up on the "CREATE DATABASE" command. Once you know how to backup and restore a database, you can create a job in sql and have the backup command run on the hour.

-- messorian

btw, here is a simple version of the backup command that backs the database northwind up to a shared network drive. Of course be careful with this because you will need the appropriate rights on the share:

yea, i'm not a beginner, i know how to do that.

That's backing up the database

we've actually had the production sever and the DR up for some time with replication. Which is fine, now we're trying to not backup the database on another server, we're actually migrating it perminately. Which is harder than a simple replication. So my title was misconstrewing, sorry.....

I can't migrate the production server now because it fails on "sql object' which i can't find any documentation on as to what that means(look at the name, that doesn't give me much to work with), and can't get the DTS packages to work because they fail for that reason. If i set up replicaion for just the DB, it works, i add the stored proceedures into the replication, it fails, i'm going to do some research as to which stored proceedure is causing it to fail, but that right there is telling me there's some kind of dependancy with the stored proceedures.

I think it has to do because the db was installed by a 3rd party vender. I really have no idea how the db intereacts with the webserver i just need to move it, When i try and copy over in the DTS package, the spot where primary keys are shown the keys instead of appearing like regular keys, look like the keys with red arrows over them. I think they're called index keys...... and someone was telling me you can't migrate those. But that sounds a bit absoludicrous to me, i might just end up using the software to install the db on the other machine, and try and import the data from that machine or export. This just seems a bit fishy to me as to why this isn't working....... I've never seen a dependancy like this causeing it to fail...