SQL Server 2008 :: Clone Stored Procedures From Database A To B
Apr 24, 2015
I have this requirement where some store procedures from a "seed" database need to be replicated to another database (on demand, so replication is not suppose to be use in this scenario).
I know it can be achieved by exporting the store procedures and then execute that at the B database but I want something a bit more automatic since it can be a large number of sprocs. I am trying something like this (still in dev):
SET NOCOUNT ON;
--
SELECT ROW_NUMBER() OVER(ORDER BY definition) seq, definition base
into #sprocs
FROM databaseA.[sys].[procedures] p
INNER JOIN databaseA.sys.sql_modules m ON p.object_id = m.object_id
[Code] ....
But I am sure there are way better ways to accomplish that...
I have a table "t_prod_cat" which contains hierarchical data which is used in production to present data.
CREATE TABLE [dbo].[t_prod_cat]( [cat_node_id] [bigint] IDENTITY(1,1) NOT NULL, [advertiser_id] [bigint] NOT NULL, [cat_hid] [hierarchyid] NULL, [level] AS ([cat_hid].[GetLevel]()) PERSISTED, CONSTRAINT [PK_t_prod_cat] PRIMARY KEY CLUSTERED ( [cat_node_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
In order not to impact the production website during the time an advertiser is editing (the editing might take much time and also mainly because at any time during the editing, the advertiser could cancel all the changes he did), I was thinking of transferring all the data linked to that advertiser to another table and let the advertiser apply any modifications up to the moment he will commit the changes.
Therefore, I would like to "CLONE" the hierarchy related to a certain advertiser_id to another table "t_prod_cat_work"
CREATE TABLE [dbo].[t_prod_cat_work]( [temp_cat_node_id] [bigint] NOT NULL, [temp_cat_hid] [hierarchyid] NOT NULL, [advertiser_id] [bigint] NOT NULL ) ON [PRIMARY]
What can be the easiest way to clone all the hierarchical data (multi-levels) from 't_prod_cat' to 't_prod_cat_work' for a certain advertiser_id ?
I have a column in a table, which have the stored procedure name stored in each row. Now, I need to execute each SP in the table dynamically. I'm trying to construct a SQL but not able to fire them!!
DECLARE @sql VARCHAR(MAX) SELECT @sql = STUFF((SELECT '; GO EXEC ' + StoredProcedureName + '' FROM MyTable FOR XML PATH ('')),1,5,'') print @sql EXEC sp_executesql @sql
I'm trying to replace a table name in 250 stored procedures. I found this script below which does a good job but it also finds tables with similar names. How can I limit the replacement to the exact table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.
-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100) -- text to search for SET @searchFor = 'MyTable001' -- text to replace with SET @replaceWith = '[MyTable002]'
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
HiI was in the situation of cloning a SQL server database last night.Today, a dependent application was behaving a little erratically thoughmost things were fine after sorting out some ntfs permissions. I'verecovered this database from backup exec before from a hot-backup, thisis the first time I've attempted this procedure though.Could I check with the experts here as to whether what I did was OK?Been googling to see if it is really the right thing to do, haven'tcome up with anything definitive...Assume server SOURCE and DEST:install DEST to same specification, OS, SQL patch level as SOURCE. Samepaths where applicable.shut down SQL server on bothCopy all datafile folder (d:sqldata) which is the full databaseincluding MASTER etc all over to DEST.Shut SOURCE down.Rename DEST to SOURCE, disable SQL server starting, set networksettings to those of SOURCE and reboot.Wait for things to settle after reboot and name change, bring SQLdatabase up.Client application now connects, I can't see any problems, but therehave been some strange anomalies in satellite applications today. Allhave been bounced to see if it's just a change they needed to notice,to no avail.Was that an OK operation to have done, or is there something just aboutto bite me when I'm not looking?Be grateful for any advice, I spent today pulling a lot of hair out.Thanks in advance :)AW
I've followed the steps in http://www.sqlteam.com/article/debugging-stored-procedures-in-visual-studio-2005 & in the MSDN for configuring and setting up debugging SQL 2005 stored procedures in VS 2008 (seems to be the same as in VS 2005). Everything works fine until I Step Into the Stored Procedure. Everything says that a yellow arrow will appear on the left and I can start going line by line. I never get the yellow arrow.
If I set a breakpoint, it is automatically disabled. The pop-up warning says, "The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded." I can't find anything about this message or problem on Microsoft's site or on the web. Any assistance is appreciated.
P.S. I'm running VS 2008 Professional Edition Version 9.0.2.1022.8 RTM
Is the any easy way to clone a database programatically? I've searched the web but did not come up with anything.
At the moment I create a new blank database (based on the model database) and create tables, indexes, etc. via asp. (The databases are always exactly the same)
It seems to me that it would be a better option to create one database with tables, indexes, etc and copy it to a new database (on the same server).
One of the options I'm considering would be to create a database, fill it with tables and indexes, etc. and then detach it. Whenever I need a new database all I would have to do is use the file system object to copy and rename the mdf file and then reattach the new mdf file.
I realise that I could also make the changes to the model database so that all new databases have the same structure but that would be my last resort.
Is there any easier way to do this? A stored procedure perhaps?
How do I go about creating a cloned copy of an existing database on the same server? I basically want to set up a test copy of a production database, but let it reside on the same server with a slightly different name, prefixed with 'Testing_".
I typically just use Studio Express Manager and not scripts to do admin tasks. Here's what I tried that didn't work so well:
* Had full backups of production db * Detached production database I wanted to clone * From within Win Explorer, copy the .mdf and .ldf files within same data folder and gave them a new name, prefixing with "Testing_" * Re-attached prodution db * Attached "Testing_..." database
Sql Express seems confused at this point with new database I attached. It references the full path of the database I copied from (c:program filesmicrosoft sql servermssql.1data...". I rename the datbase, removing full path and prefixing with "Testing_".
* Using Sql authentication, changed the default database for the Login account to the new cloned copy of database. * Did some query level data sanitizing to the cloned copy of the db. I am positive I performed this on the correct db. * Was able to connect/authenticate to the cloned copy through a custom VB app on my dev machine, and all looked well. * Tried to connect to the cloned db from another machine and couldn't. Went back to my dev machine and noticed the original production db looked hosed -- there were no tree nodes available to it! Was still able to connect to the cloned "Testing_" db copy, but not able to connect/authenticate anymore to the production copy. * Detached cloned copy of "Testing_" db and moved it out of the data folder. * Detached production db. Got an error msg indicating operation failure of some kind, but the database was gone from the tree. * Reattached the production db but couldn't hit it from my custom vb app. Re-did the login and user accounts used by the app and was able to hit it. However, the data sanitizing I had done earlier in the cloned copy clearly was visibile in the production db! * Had to restore production db from backup. Had problems authenticating from app, and recreated User account before the app was to connect.
Q1: What I am doing wrong? Sql Express was obviously as confused as me somewhere along the line.
Q2: Is specifying a default database used by a Login account necessary for a VB app like mine. I think it was something I discovered in the past, however, this would seemingly present a problem whenever I want my app to hit a different database, established through a connection string.
I have a database (SQL 2005) with two schemas (dbo and s1) and with tables defined in dbo and tables defined in s1. The stored procedures are also defined in both schemas, some of them in dbo some of them in s1. Some of the stored procedures query tables from dbo and s1 at the same time. I want to have a new db role with access to the database only through sps and no other access read/write to the tables. I created a new db role and granted execute permission to it and assigned a user to it. When I execute stored procedure defined in dbo with query against dbo tables, it works as expected. However, if I run stored procedure defined in s1 with query to table in dbo, I receive error about missing select permission for the table in dbo. I am not sure why, but I can assume there is an issue with the ownership chain. I can grand read/write permission for the tables, but this will break our original requirement for limited access to the db only through sp. The other option is to have another role r2, with read/write, privilege and to use EXECUTE AS r2 in the sp.
I would like to ask first why the error for missing select permission happens and is there another way to have role restricted to only execute permission for all stored procedures.
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!!
Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.
Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.
This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.
I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.
As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.
Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
I have a periodic backup task, and when I need a development copy on which to test code, I restore my most recent backup to a different name, switch the ODBC link to that name on my development machine and have a current copy of the database to play with.
I've been doing this for years, and it works great. Just now I did it, and suddenly my development machine is unable to run any stored procedures that have the 'Execute as owner' clause in their definition. I'm using domain accounts, my personal account is the owner of the database, and everything works on the production copy, which is in the same instance on the same machine.
The test copy is identical to the production copy, which continues to work fine - it was just created using by restoring the backup of the production copy, but I can't run anything with this clause. As soon as I delete the 'Execute as owner' line, the procedure is suddenly available. If I put it back, I'm locked out again.
The error message is: The server principal “sa” is not able to access the database “WhateverDB” under the current security context
Not sure if there is anyway to do hots on sql server .From all I haveheard an EMC can do clones and fractures in order to implement a hotbackup or some kind of it in sql server.Seemingly the DBA has to do something toenable the fracture of the clone . Is anyone familiar with this ?Your input is highly appreciatedMB
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
) ================================================= In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code: --Form_Kimmel.vb-- Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
End Class ==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Hi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
Hello everyone,I face currently a problem where I could need some input for searchingthe source of the ProblemSystem: SQL Server 9.0I fill from Database A with triggers Database B, everything worksfine.On Database B there is a Stored Procedures that checks the records andadd additional information accordingly, this Stored Procedures isnormally called by the application on "update and insert" in theaccording table.When I try to call this Stored Procedures from the Database A, thetrigger does not work anymore, even if I do a try catch over the wholetrigger, he never reach the Catch and the insert I try to do there toget the error message.On both Databases the user, that is taken to execute the trigger isexistent and DB-Owner of both Databases.If I go and execute the Stored Procedures manually after an insert orupdate to Database B everything works fine.I also already tried to check on Database B if there is an insert orupdate from Database A and if, to execute the Stored Procedures, withthe same result, nothing and all happens anymore, neither update onDatabase A and also not on Database B.And also I cant catch the error as the Try/Catch is not working.Hope I could explain it understandable and maybe someone remembersalready having the same problem.Thanks & Best regardsPascal
I've never dealt with stored procedures much.. but i have a new database created.. imported the tables, but the stored procedures didnt get copied over..
Is there an easy way to export them.. perhaps to a .sql file ... then import them or run a script on the other database..
I have never done much with the query window before, so i'm not sure how to handle this.. as there are around 20 stored procedures that need imported..
I am having three table tblTest,tblTestQuestion,tblAnswers
Each test can have multiple question and each Question can have multiple answers.
Now I am already having records in database. I wants to create clone copy of existing test except testdetails in tblTest because the test will be unique, and then insert questions and answers into their respective tables.
I was trying to create SP but stuck.
Please find below tables structure
[code=" SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblAnswer](
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
I am attempting to compile a list of questions that will enable meassess 'at risk' stored procedures that need to be remediated in orderto minimize unplanned downtime and enhance database performance. Isthere a subset of criteria (parameters) from the MS SQL Server BestPractices Analyzer that can be used for this purpose? Any pointers tothe appropriate documentation is most welcome.Thanks in advance.
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
