This procedure will migrate all your stored procedures located on on server to another via linked server. Just specify the linked server name, remote database, and local database. [cc lang=”sql”] CREATE PROCEDURE [dba].[pr_refresh_create_procedures] @linked_server varchar(100), @source_db varchar(100), @target_db varchar(100) AS –EXEC [dba].[pr_refresh_create_procedures] ‘LOCALSERVER’, ‘yourdbname’, ‘targetdb_dev’ SET @source_db = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’; DECLARE @sql Nvarchar(max) DECLARE @Name varchar(255) = @target_db IF OBJECT_ID(N’admindb.tmp.shelldb_copy_procedures’) IS NOT NULL DROP TABLE admindb.tmp.shelldb_copy_procedures SET @sql = ‘SELECT p.name, m.Definition INTO admindb.tmp.shelldb_copy_procedures FROM ‘ + @source_db + ‘.sys.objects p WITH (NOLOCK) INNER JOIN ‘ + @source_db + ‘.sys.sql_modules m WITH (NOLOCK) ON p.object_id = m.object_id WHERE type IN (”FN”, ”IF”, ”TF”, ”P”, ”V”, ”TT”)’ exec(@sql) DECLARE c CURSOR FOR SELECT Definition FROM admindb.tmp.shelldb_copy_procedures OPEN c FETCH NEXT FROM c INTO @sql WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = REPLACE(@sql,””,”””) set @sql = N’execute ‘ + QUOTENAME(@name) + N’.dbo.sp_executesql N”’ + @sql + ”” EXEC(@sql) FETCH NEXT FROM c INTO @sql END CLOSE c DEALLOCATE c [/cc]
Continue reading ...
This script will allow you to specify a linked server name, a remote database on that linked server, and a local database name in which to copy all the tables from on server onto another. [cc lang=”sql”] CREATE PROCEDURE [dba].[pr_refresh_create_tables] @linked_server varchar(100), @source_db varchar(100), @target_db varchar(100), @debug_mode int = 0 AS — dba.pr_refresh_create_tables ‘10.10.60.58’, ‘yourdbname’, ‘targetdb’, 0 — SELECT * FROM admindb.dbo.refresh_table_columns BEGIN DECLARE @SQL nvarchar(max); DECLARE @SQLColumnList nvarchar(max); DECLARE @SQLSelectColumnList nvarchar(max); DECLARE @CRLF nvarchar(2); DECLARE @Tab nvarchar(1); DECLARE @RecordKeyName nvarchar(50); DECLARE @RecordKeyDataType nvarchar(50); DECLARE @TableID int; DECLARE @TableCount int; DECLARE @source varchar(max) = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’ DECLARE @TableName varchar(255); DECLARE @TableList TABLE ( TableID int IDENTITY(1, 1) NOT NULL, TableName varchar(255) ); IF OBJECT_ID(N’admindb.dbo.refresh_table_columns_tmp’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns_tmp IF OBJECT_ID(N’admindb.dbo.refresh_table_columns’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns IF OBJECT_ID(N’admindb.tmp.refresh_create_tables’) IS NOT NULL DROP TABLE admindb.tmp.refresh_create_tables CREATE TABLE admindb.dbo.refresh_table_columns_tmp ( RowID int NOT NULL, TableName varchar(255), ColumnName varchar(255), IsIdentity bit, DataType varchar(50), IsDataTypeCast bit, IsNullable bit, IsLast bit ); CREATE TABLE admindb.tmp.refresh_create_tables ( ID INT identity(1,1), table_name varchar(255), create_table varchar(max) ) SET NOCOUNT ON; SET @CRLF = CHAR(13) + CHAR(10); SET @Tab = CHAR(9); SET @sql = ‘INSERT INTO admindb.dbo.refresh_table_columns_tmp ( RowID, TableName, ColumnName, IsIdentity, DataType, IsDataTypeCast, IsNullable ) SELECT RowID = ROW_NUMBER() OVER(PARTITION BY t.TableName ORDER BY t.IsIdentity DESC, t.ORDINAL_POSITION), TableName, ColumnName, IsIdentity, DataType, IsDataTypeCast, IsNullable FROM ( SELECT DISTINCT TableName = c.TABLE_NAME, ColumnName = c.COLUMN_NAME, IsIdentity = sc.is_identity, DataType = ”” + CASE WHEN LOWER(c.DATA_TYPE) IN (”numeric”, ”decimal”, ”real”) THEN LOWER(c.DATA_TYPE) […]
Continue reading ...
Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command. Adding a linked Server using the GUI There are two ways to add another SQL Server as a linked server. Using the first method, you need to specify the actual server name as the “linked server name”. What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name. This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server. I like to avoid this method even though it is easier to initially setup. The rest of the steps will guide you through setting up a linked server with a custom name: To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) The “New Linked Server” Dialog appears. (see below). For “Server Type” make sure “Other Data Source” is selected. (The SQL Server option will force you to specify the literal SQL Server Name) Type in a friendly name that describes your linked server (without spaces). I use AccountingServer. Provider – Select “Microsoft OLE DB Provider for SQL Server” Product Name – type: SQLSERVER (with no spaces) Datasource – type the actual […]
Continue reading ...
When using OPENQUERY you will sometimes want to make calls to the same server you are working from. The most common reason for this is to query the output of a stored procedure into a temporary table. For that example, follow this link: (Insert Results of Stored Procedure Into Table) Before doing that you will have to create a linked server. You can either create the server programatically: [cc lang=”sql”] EXEC sp_addlinkedserver @server=’LOCALSERVER’, @srvproduct=’SQLSERVER’, @provider=’SQLNCLI’, @datasrc=’SERVERNAMEINSTANCENAME’ [/cc] Or you can create the linked server manually: In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) The “New Linked Server” Dialog appears. Type in a friendly name that describes your local server (without spaces). I use LOCALSERVER. Provider – Select “Microsoft OLE DB Provider for SQL Server” Product Name – type: SQLSERVER (with no spaces) Datasource – type the SERVERNAMEINSTANCENAME ProviderString – Blank Catalog – Optional (If entered use the default database you will be using) Within the same Dialog on the left menu under “Select a Page”, select Security Select the radio button named “Be made using the login’s current security context” Click OK, and the new linked server is created
Continue reading ...
When optimizing procedures often times you need to rewrite the procedure in order to speed it up. However, when you’re finished how can you be sure that the result set is still the same? This article will show you how to run a thorough test to make sure. We’ll execute the old and new stored procedure then compare their result sets by using a checksum aggregate. We’ll also compare rowcounts and elapsed execution time. The benefit of this test harness is that it will allow you to loop through and execute the procedures with as many dynamic parameter values as you wish, then compare the result set of the old procedure vs the new procedure for each execution. First, we need to do a couple things to set up our environment. Setup a local linked server Create a wrapper procedure We need the local linked server in order to dynamically retrieve the result set of the stored procedure using OPENQUERY. We could skip this step and create the temp table explicitly if we knew all the columns and data types being returned, but sometimes this is a hassle because there could be many columns that are returned and explicitly creating a temp table for each procedure we wanted to test is a pain. And that’s probably why you’re here in the first place isn’t it? So to create a local linked server, do the following: [cc lang=”sql”] EXEC sp_addlinkedserver @server=’LOCALSERVER’, @srvproduct=’SQLSERVER’, @provider=’SQLNCLI’, @datasrc=’Your_Server_Name_Here’ [/cc] For this example, the server name […]
Continue reading ...
