Error when creating script to copy Stored Procedures

Our DB has around 30 SProcs - I need to move them into a script, so that it can be easily added to another server. The way I'm creating the script is to highlight all the SProcs, then copy - in my notepad screen, I paste, which gives me one script, which includes all the individual creation scripts for the Sprocs.

However, I'm getting an error when I create the script -

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

So - what's a good, and/or easy way to structure the script, so that I can easily find WHERE to put Which SProc Script, in the list?

Hey,

You could follow those steps:

Right click Database in Sql Server Management Studio -> Tasks->Generate Scripts->choose databse ->...->check Stored Procedure->next...

|||

That still gives me the same error - I guess what I'm asking is:

how can I figure out what order the Stored Procedures need to be scripted, so this error doesn't happen?

|||

Hi

The generate script option only guarantees that the script can pass compile and the order is determined by design.

If you don't mind you could execute script twice and error should go away in the second time.

Hope this helps.

|||

Hi,

If you are sure that all procedures you need are in your script do not worry about the errors, procedures will be created and will work. The only problem will be if you call procedures from another table which are not scripted or if you script your procedures into another database (with different name) and you used call like exec [Sourcedatabase].[dbo].[Procedurename] so it is possible in this case that your procedures will not work if database [Sourcedatabase] is not visible on your server from inside your destination database so check your script for "long" calls.

If you would like to have procedures in correct order you can write TSQL script which will script procedures for you in correct order, I hear about application which can do it for you and you can do it by hands by ordering your procedures in the script the way that procedure is defined before is called by another procedure.

Thanks

JPazgier

Error trying to copy stored procedures from one db to another

Hello,

The export menu in DTS is a bit confusing for me. I have some stored procedures in one SQL Server and want to copy them to another. The username is the same for both SQL Servers. When I try copying the stored procedure with defaul settings, I get a progress bar (24 percent ccompleted, 62 percent completed...), then when its done it says "failed to copy objects from sql server to sql server". When I double click the error for details it says "User or role '[username]' already exists in the current database". Then I try again, unchecking "use default options" for my export. I go in to alter the options, uncheck "copy database users and database groups" (I've also tried unchecking "copy object-level permissions"), run it, get progress bar (like above), and then get another error that says "There is no such user or group '[username]'".

My user name is correct, I am able to access both Sql Servers with this username, and I should have all of the appropriate permissions.

I think maybe there is a checkbox I'm not unchecking or something stupid like that.

Thanks in advance for any feedback.Wild guess here: the user who owns the sp is not dbo ?|||Wild guess here: the user who owns the sp is not dbo ?

Sorry, your msg is a bit cryptic for me. What are "sp" and "dbo"? My guess on the latter is "database owner". I will check with the administrator to find out if I can get a username with more privileges. It is a shared Sql Server with a web hosting company and there are dozens of databases that I can see in there, but do not have access to, so users and permissions are a little hairy, i'm sure.

error running sp_stored_procedures

I downloaded the code example "Running Stored Procedures with ASP.NET" (http://www.dotnetjunkies.com/Tutorial/9AE62C44-3841-4687-B906-2F6D4A5A4622.dcik)
and I'm having trouble filtering the sp's that are populating the drop down box. Here's my code: (all I did was add the one parameter)

Dim ds As New DataSet

Dim conn As New SqlConnection( _
Data source=" & DatabaseServer.Text & _
";User id=" & Userid.text & _
";Password=" & Password.Text & _
";Initial catalog=" & Database.Text)

Dim cmd As New SqlCommand("sp_stored_procedures", conn)
Dim adpt As New SqlDataAdapter(cmd)
Try
cmd.Parameters.Add("@.sp_name", SqlDbType.NVarchar, 390).Value = CType("my%", String)

so if I comment out the cmd.Parameters.Add ... line it all works fine and I get every sp, but if I uncomment it I get " Incorrect syntax near 'sp_stored_procedures'" every time no matter what i put in the string.

I have a feeling I'm doing something stupid :) Anyone have any ideas? The query works fine in query analyzer with the same arguments that I'm trying in the code.Add:

cmd.CommandType=CommandType.StoredProcedure|||...and you shouldn't begin the name of your stored procedures with the sp_ prefix. This prefix should always be reserved only for system stored procedures.|||"
...and you shouldn't begin the name of your stored procedures with the sp_ prefix. This prefix should always be reserved only for system stored procedures.

"

It IS a system stored procedure.|||"
Add:

cmd.CommandType=CommandType.StoredProcedure
"

Doh!

Thanks!