Error Trying To Copy Stored Procedures From One Db To Another
Sep 8, 2004
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.
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?
I need to copy the stored procedures from a database on one server to another. I used 121 WAM to copy the DB tables over but don't know how to copy the stored procedures over.
Can someone point me in the right direction?
Ultimately, what I would like to do is have the 2nd server update from the 1st server so I can set up a redundant DB.
hi I'm working in VS2005 with SQL server Express database.How can I copy some tables, stored procedures and diagrams from one database to another? thanks
is there a way to copy whole stored procedures to another database? I have aproximately 80 SPs in a database, I want to copy whole SPs into another DB. Is there a practical way to copy them? Thanks
Hello all! What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB??? So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this? The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me! Any help would be greatly appreciated!!!
I have 2 databases, one that we use called MyShop and one that I develop oncalled TestShop.After I have a stored procedure working the way I want in TestShop , isthere a way to just copy the SP to the other database without the copy andpaste method?. Same if I have a new table. Any way to add it in withoutrecreating it in the MyShop database?I am using Sql Server 2000ThanksAndy
I am looking to implement a system whereby a given user can 'authorise' changes to the system itself and promote from test to live. For instance a user might be running an application which has a SQL 2005 database DataA opena nd runnign stored procedures from that. I'd like a Stored procedure in that able to copy over a given stored procedure from a different database - DataB and copy into DataA. Is that possible? Also is it possible from T-SQL to copy files on the servers underlying filesystem? Ie to copy some .aspx files from one location to another. Regards Clive
Can I make a copy of my development database DEV on same SQL SERVER machine, rename it to TEST and stored procedures to be updated automatically for statements likeUPDATE [DEV].[dbo].[Company]SET [company_name] = @company_nameto becomeUPDATE [TEST].[dbo].[Company]SET [company_name] = @company_namein order not to edit each individual stored procedure for updating it ?
Hi all, I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now . my web site is ready I didn't have problem to upload my site to my hoster. Now I want to upload all my tables and my stored procedure create locally with VWD express How can i do it ? NB: I know i can't design DB (create/modify tables and stored proc) with express edition thank's for your help
Hi all, In my code, I will call a stored procedure which is stored on the SQL server. However, I got errors on the stored procedure (The multi-part identifier "Datetime.Today" could not be bound.). Anyone know how to fix it. Thanks in advance.
ALTER PROCEDURE [dbo].[spr_getScheduleJob]( @ToMobNum varchar(16), @OADC varchar(11), @MsgScheduleTime smalldatetime, @MsgTxt varchar(160), @CompUserName varchar(20) ) asset nocount on Declare @TodayJobs as smalldatetimeSet @TodayJobs = Datetime.Todayselect * from Bulk_ScheduledOutgoing where @TodayJobs <= @MsgScheduleTime
I was wondering if anyone knows how to suppress error messages that get returned from a stored procedure. For example, in a stored procedure I have a convert statement that takes some dates and substrings them together into a new date format:
Some of the data I am working with is not guaranteed to be good. For example, sometimes the users had entered 2/2/97 - which is invalid - no leap year in 1997. So, the convert to datetime fails.
The typical error message about the convert pops up on the screen. Can I shut that off.....and still catch @@error <> 0....?
Hello,I was wondering if there were any built-in objects to handle error handling, in any of the SQL Server objects, or just to use event log or trace or debug or something like that. Or is it not recommended at all to use try.. catch, for whatever reason.Thanks a lot.
is there an error handler in sql stored procedures? For example if i want to do a drop something and there is a lock, i want to try return if it droped it or not and if not then try again later
I've got a weird one here. I'm running a DTS package on SQL Server2005. It copies a bunch of stored procedures. I renamed them on theoriginating server and ran the DTS again.The came over with the old name and code!I deleted the DTS and built it from scratch, and the same thinghappened.I ran SELECT * FROM sys.objects where type = 'P' on the source serverand the names were correctI'm explicitly checking which sp to copy rather than using Copy all. Ican see the sp namesI've deleted and recreated the sp on the source server using scriptsI've checked the source server nameI've Refreshed everywhereNothing worksWhy is up_Department_GetAllBySchool trying to be be pulled over whenit doesn't exist?Why is up_Department_GetBySchool not being pulled over when it doesexist?I've heard that SQL 2005 pre-SP2 has a problem where renaming anobject that has a text definition (like sprocs, functions, triggers,views) doesn't update the definition. So if you pull that objectdefinition and run it into your new database, it will use the originalscript, which has the original name.I ransp_helptext 'up_Department_GetBySchool'and checked the CREATE statement at the top. Sure enough, it had theold textI asked our NetAdmin to install SP2 on our server. Then I ransp_refreshsqlmodule 'up_Department_GetForSchool'and got this error:Invalid object name 'up_Department_GetAllForSchool'.So even the code which was supposed to fix it, doesn't. Has anyoneelse had this problem, and managed to fix it?--John Hunter
I got the following error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I've been researching this problem for weeks and I haven't gotten very far with it so I was hoping to get some help here.
Here's the error information we get: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This particular error comes from a .net application, but we've seen similar "query timeout errors" from a vb6 application roughly 6 months ago.
Here are the facts we've narrowed down:
1) The timeout happens at seemingly random times (very sporadic). 2) It's currently only happening on a few stored procedures (if requested, I'll provide one of the stored procedures, but it's basically a complex search procedure). 3) To resolve the timeout error, we've found 2 temporary solutions:
A) have all clients exit the program thus closing all active connections (less than 10 connection in a 4-5 user setup) B) I run the following script when the timeout occurs and then the stored procedure runs smoothly:
Code Block
sp_configure 'remote query timeout', 0 reconfigure with override sp_configure 'remote query timeout', 600 reconfigure with override 4) Running the stored procedure from our application and from SQL Management studio express, is the same, except management studio doesn't time out and actually runs as long as it takes (roughly 1 minute 20 seconds or under 1 second after the script above runs).
We're pretty stumpted and it's happened at 5 different client sites with little in common. One of our sites is even running SQL express off the workstation with nothing else running and it still occurs.
I'm open to trying practically anything at this point, but unfortuntenly we have not been able to reproduce this behavior in our testing enviroment so I can't give much information for others to reproduce.
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
this error comes up when I run a form to supmit information to the DB. .
"Procedure or function 'aspnet_Users_CreateUser' expects parameter '@Lname', which was not supplied" I have it declared in the procedure. so why is it telling me it was not supplied. Here is what I have:
I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.
@Cycle sql_variant = 2 WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication AS BEGIN SET NOCOUNT ON; declare @execution_id bigint
[code]....
When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large or Unknown token received from SQL Server
or Protocol error in TDS stream or Bad token from SQL Server: Datastream processing out of sync. or Invalid cursor state or TDS Buffer Link Too Large or Function sequence error
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
