#sql_server

SQL server replication, Replication allows maintaining the same database multiple copies at different locations. SQL server replication helps to manage a whole database redundancy whereas mirroring helps to manage some part of the database (a set of required objects) at users location. Alternations made at various clients locations are synchronized to the primary server. SQL Server replication is an object feature.

  Create a database maintenance plan

Click on Manage connections and enter connection information for the source and target databases

From the toolbox, add a “Execute T-SQL Statement Task” and name it “Backup PROD”

Double click on it, set its connection to the source database and enter the following SQL:

BACKUP DATABASE TO DISK='\\server\Backup\.bak' WITH NOFORMAT, INIT

From the toolbox, add another “Execute T-SQL Statement Task” and name it “Kill Active Connections on Staging”

Double click on it, set its connection to the destination database and enter the following SQL:

alter database set offline with rollback immediate alter database set online go

From the toolbox, add another “Execute T-SQL Statement Task” and name it “Restore Staging”

Double click on it, set its connection to the destination database and enter the following SQL:

RESTORE DATABASE FROM DISK=’\\server\Backup\.bak' WITH REPLACE

Connect the success arrows of the tasks in the order added

Optionally, connect the error arrows to a “Notify Operator Task”

Go to the SQL Server Agent, expand the Jobs folder and set a schedule for your maintenance plan

Summary

Do you have a situation where you have data in your table rows that you want to transpose into columns? Furthermore, you are not sure of the number of columns you want to transpose?

Read on below for a straightforward approach on how to use the Sql Server PIVOT statement combined with dynamic SQL to easily transpose any number of columns.

Consider that you have a table with a variable number of columns called MyColumn. Then consider a table which has an entry for every column where it links some data to the column.

You want to transpose the resultset so that the columns in the rows become acutal columns. The trick here is to understand that you will need to build a dynamic query so that you can pass in a variable number of columns.

Preparation

-- Create the sample tables CREATE TABLE MyColumn([ID] VARCHAR(255), [Name] VARCHAR(255)) CREATE TABLE MyDynamicData([RowID] INT, [ColumnID] VARCHAR(255), [Data] VARCHAR(255)) -- Create the dynamic columns INSERT INTO MyColumn([ID], [Name]) SELECT 'A', 'Column A' INSERT INTO MyColumn([ID], [Name]) SELECT 'B', 'Column B' INSERT INTO MyColumn([ID], [Name]) SELECT 'C', 'Column C' INSERT INTO MyColumn([ID], [Name]) SELECT 'D', 'Column D' -- Create data which is linked to columns DECLARE @counter INT DECLARE @rows INT SET @counter = 1 SET @rows = 1000 WHILE (@counter < @rows) BEGIN INSERT INTO MyDynamicData([RowID], [ColumnID], [Data]) SELECT @counter, 'A', 'Col A Row ' + CAST(@counter AS VARCHAR) + ' Data' INSERT INTO MyDynamicData([RowID], [ColumnID], [Data]) SELECT @counter, 'B', 'Col B Row ' + CAST(@counter AS VARCHAR) + ' Data' INSERT INTO MyDynamicData([RowID], [ColumnID], [Data]) SELECT @counter, 'C', 'Col C Row ' + CAST(@counter AS VARCHAR) + ' Data' INSERT INTO MyDynamicData([RowID], [ColumnID], [Data]) SELECT @counter, 'D', 'Col D Row ' + CAST(@counter AS VARCHAR) + ' Data' SET @counter = @counter + 1 END SELECT * FROM MyColumn SELECT * FROM MyDynamicData

Pivoting the data

DECLARE @cols NVARCHAR(2000) SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + [Name] FROM MyColumn ORDER BY '],[' + [Name] FOR XML PATH('') ), 1, 2, '') + ']' DECLARE @sql VARCHAR(2000) SET @sql = 'SELECT [RowID], ' + @cols + ' FROM ( SELECT A.[RowID], B.[Name], A.[Data] FROM MyDynamicData AS A INNER JOIN MyColumn AS B ON A.[ColumnID]=B.[ID] GROUP BY A.[RowID], B.[Name], A.[Data] ) AS SourceTable PIVOT ( MAX([Data]) FOR [Name] IN (' + @cols + ') ) AS PivotTable ' EXEC (@sql)

The resulting data will be correctly pivoted.

Reference: http://msdn.microsoft.com/en-us/library/ms177410.aspx Keywords: SQL Server 2010 2005 PIVOT TRANSPOSE

Error Message Server: Msg 7391, Level 16, State 1, Procedure p_SP1, Line XThe operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. Resolution

See http://support.microsoft.com/default.aspx?scid=kb;en-us;329332 and http://support.microsoft.com/?kbid=899191 Here is an excellent explanation from David Adamson’s blog (http://www.dotnetjunkies.com/WebLog/daveadamson/archive/2005/11/22/133928.aspx).

Script to search stored procedures:

select upper(SysObjects.Name),SysComments.Text

from SysObjects, SysComments

where SysObjects.type='P'

and (SysObjects.ID = SysComments.ID)

and SysComments.Text like '%SearchString%'

An alternative way:

select *

from INFORMATION_SCHEMA.ROUTINES

where ROUTINE_TYPE='PROCEDURE'

and ROUTINE_DEFINITION LIKE '%SearchString%'

The query below shows how to create an XML document using FOR XML from a table (based on Sql Server 2000). To start with, run the sql script further below to create the sample table, and populate it with some data. Once the select script is run, data will be returned in the form:

<Employees>

<Employee>

<field1>field1>

...

Employee>

Employees>

Query

select 1 as Tag,

NULL as Parent,

NULL as [Employees!1!Employee!xml],

NULL as [Employee!2!OfficerId!xml],

NULL as [Employee!2!GivenName!xml],

NULL as [Employee!2!Surname!xml],

NULL as [Employee!2!LoginCode!xml],

NULL as [Employee!2!IsActive!xml]

UNION ALL

select 2,

1,

NULL,

Officer_ID,

Officer_Given_Names,

Officer_Surname,

CRTS_Login_Code,

Is_Active

from staging.dbo.tbl_Officer

for xml explicit

Table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Officer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tbl_Officer]

GO

CREATE TABLE [dbo].[tbl_Officer] (

[Officer_ID] [int] NOT NULL ,

[Officer_Given_Names] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Officer_Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[CRTS_Login_Code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Is_Active] [tinyint] NOT NULL

) ON [PRIMARY]

GO