#sql_server

[dztechs]

كيف تعمل جداول SQL Server المُؤقتة

تُخزِّن الجداول المُؤقتة في SQL Server البيانات بشكل مُؤقت. يُمكنك إجراء نفس العمليات — مثل SELECT و INSERT و DELETE و UPDATE — على جدول مُؤقت كما تفعل مع جدول SQL عادي. الجداول المُؤقتة موجودة في قاعدة بيانات tempdb وتكون مرئية فقط أثناء مدة الاتصال. عند إنهاء الاتصال ، يتم حذف الجدول المؤقت بواسطة SQL Server. يمكنك أيضًا حذفه بشكل صريح في أي وقت. تحقق من كيفية تثبيت وإعداد Microsoft SQL Server على Ubuntu. Read the full article

[nehaarticles]

SQL Server JOINS with sql server, install visual studio, install sql server, architecture, management studio, data types, db operations, log

The join clause allows us to retrieve data from two or more related tables into a meaningful result set. In a JOIN query, a condition indicates how two tables are related:

[adtubeindia-blog]

SQL server replication

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.

For more click here

[shailensukul]

Restoring Database

  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

Copyright 2012 Shailen Sukul

[shailensukul]

Dynamic Rows to Columns

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

Copyright 2012 Shailen Sukul

[shailensukul]

SQL Server DTC Error

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).

Copyright 2012 Shailen Sukul

[shailensukul]

Script to search in Stored Proces

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%'

Copyright 2012 Shailen Sukul

[shailensukul]

How to use FOR XML to return an Xml Document with a root element

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

Copyright 2012 Shailen Sukul