Importance of transaction in SQL Server

Importance of transaction in SQL Server

Recently I have written few articles on error handling (list given in the last paragraph of this article) topic whereas I have used Transaction in my TSQL script there and have promised blog reader that I will come up with more details and explanation of transaction. Finally today I thought to write about transaction.

Transaction is one of the most important part for developer to be master in development. We might have cases where we are doing more then one combination of INSERT / UPATE / DELETE (DML statements) in TSQL code block. It is quite possible that any of the DML operation failed in-between execution and we want to revert back any DML changes made prior from that failure to keep consistency and integrity. Transaction comes to your help for this need and makes your database ACID (Atomicity, Consistency, Isolation and Durability) compliant (Click here to know more about ACID.).

According to me, there should be Begin Transaction, Commit Transaction and ROLLBACK Transaction with each TSQL block if it is using DML statements, no matter whether code block has one or more DML operation going.

Transaction creates locking so it is also advisable to keep your transaction as short as possible to avoid locking and blocking issue.

Let us see how we can use transaction in TSQL code block. We will need to table with some data for demonstration. Let us create two table and define foreign key between them.

[sourcecode language=”sql”]
CREATE TABLE tblEmployeeDetail
(
ID INT IDENTITY(1,1) CONSTRAINT pk_tblEmployeeDetail_Id PRIMARY KEY
,EmployeeName Varchar(100)
,Designation VARCHAR(50)
,City Varchar(20)
)
GO

CREATE TABLE tblEmployeeSalary
(
ID INT IDENTITY(1,1)
,EmployeeID INT CONSTRAINT fk_tblEmployeeDetail_id FOREIGN KEY REFERENCES tblEmployeeDetail(ID)
,Salary INT NOT NULL
)
GO

INSERT INTO tblEmployeeDetail
SELECT ‘Ritesh Shah’,’DBA’,’Ahmedabad’ UNION ALL
SELECT ‘Teerth Shah’,’Developer’,’Ahmedabad’
GO

INSERT INTO tblEmployeeSalary
SELECT 1,50000 UNION ALL
SELECT 2,45000
GO[/sourcecode]

Execute following UPDATE statements which will ROLLBACK transaction when error come across.

[sourcecode language=”sql”]
–We are now going to update designation in EmployeeDetail table
–and update the salary. if there is an issue in any of the UPDATE statement
–both table should be in the previous state
BEGIN TRY
BEGIN TRANSACTION
UPDATE tblEmployeeDetail SET Designation=’DBA Manager’ WHERE ID=1

–giving NULL value though it is not allowed in SALARY field (as it is NOT NULL field)
–it will generate an error and the Designation in tblEmployeeDetail table will also revert back
UPDATE tblEmployeeSalary SET Salary=NULL WHERE EmployeeID=1
COMMIT TRANSACTION
END TRY
BEGIN CATCH
declare @trancount int;
set @trancount = @@trancount;
IF @trancount>0
BEGIN
ROLLBACK;
THROW
END
END CATCH
GO

–Confirm whether any record is changed or not
SELECT * FROM tblEmployeeDetail
SELECT * FROM tblEmployeeSalary
GO
[/sourcecode]

Here is the error I have received while executing UPDATE code block.

1Error

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

If you are interested to read error handling article I have written previously, have a look at list below:

Error handling with “THROW” command in SQL Server 2012 (Click Here)
List of Errors and severity level in SQL Server with catalog view sysmessages (Click Here)
Create custom error message by sys.sp_addmessage in SQL Server 2012 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Create custom error message by sys.sp_addmessage in SQL Server 2012

Create custom error message by sys.sp_addmessage in SQL Server 2012

error

Social media is a wonderful way to share knowledge. I have my own Facebook page where I used to share my knowledge. Yesterday I see one message in my Facebook inbox. One SQL Server community member asked me whether it is possible to create our own error messages in SQL Server or not. He told me that after reading two of my previous articles (Error handling with “THROW” command in SQL Server 2012 and List of Errors and severity level in SQL Server with catalog view sysmessages) related to error handling, he has looked at catalog view “Master.DBO.SysMessages” and he wanted to define his own custom error messages.

Microsoft have given control to us to create custom messages as per our need and system stored procedure “sys.sp_addmessage” helps us to do this.

Let us look at one example for this:

[sourcecode language=”sql”]
–I want to add error with error number 50001
–let us check whether any error is having same number or not
–in my case, I don’t find any error with 50001 number
select * from master.dbo.sysmessages where error =’50001′;

–adding error message with 50001 number and severity 16
–if record duplicate records comes, we can execute this error
EXEC sys.sp_addmessage 50001, 16, ‘%s is already there in table %s.’;
GO

–now we will get one row for default language (in my case it is 1033 which is US English)
–with 50001 number
select * from master.dbo.sysmessages where error =’50001′;
[/sourcecode]

Now, we have one custom message ready with us, let us check it by creating one sample table with some sample data row.

[sourcecode language=”sql”]

–now testing the error message whether it is working fine or not.
BEGIN TRY
DECLARE @Name VARCHAR(50),@City AS VARCHAR,@Count INT
SET @Name=’Rajan Shah’
SET @City=’Mumbai’

SELECT @Count=COUNT(1) FROM TestCustomError WHERE Name=@Name

–In this case, I have considered that Name column should be unique
–there may or may not be Primary or Unique Key defined
–but we can test it in business logic
–there may be argument for this approach but I just wanted to show
–whether custom error is working or not.
IF @Count<=0
BEGIN
BEGIN TRANSACTION
INSERT INTO TestCustomError (Name,City)
SELECT @Name,@City
COMMIT TRANSACTION
END
ELSE
BEGIN
DECLARE @ErrMessage varchar(500) = FORMATMESSAGE(50001, @Name, ‘TestCustomError’);
THROW 50001, @ErrMessage, 1;
END
END TRY

BEGIN CATCH
THROW;
END CATCH
GO
[/sourcecode]

Scope of this article was to show how to add custom error message and check it. I have kept one loophole by not putting ROLLBACK anywhere in the code which itself is an interesting topic and out of the scope of this article. I will cover this point very soon.

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

List of Errors and severity level in SQL Server with catalog view sysmessages

List of Errors and severity level in SQL Server with catalog view sysmessages

Few days back I have written “Error handling with “THROW” command in SQL Server 2012”. After reading this article, one of the regular blog readers has asked me question why I have used 16 severities for the error?

This is really interesting question so I thought to answer him in the form of this blog post so that everyone who is unaware with error messages in SQL Server can be benefited.

16 is a default severity level and used for most user exception and that was the reason I have used it. You can get a list of error messages along with its severity level and error number from following catalog view.

[sourcecode language=”sql”]

SELECT *

FROM master.dbo.sysmessages

WHERE

error=8134 — error message number I got in previous article

AND msglangid = 1033; –language selection, 1033 represents US english

[/sourcecode]

 

There are total number of 10542 error message comes by default for language id 1033 (US English). Error messages given in total 22 language so 10542 error * repeated 22 times = 231924 rows in sysmessages.

Generally we have sixteen different severity level in SQL Server 2012 and each severity represents criticalness of an error. You can get a list of severity from the following TSQL.

[sourcecode language=”sql”]

SELECT DISTINCT severity

FROM master.dbo.sysmessages;

[/sourcecode]

Here is the brief description of different severity.

Severity level 0-10: These are just information message not actual error.

Severity level 11 to 16: These are errors caused due to user mistakes. We have tried to divide value by 0 in previous article and hence we got severity error 16.

Severity Level 17: This severity indicates that an operation making SQL Server out of resources or exceeding defined limit. That may be disk space or lock limit.

Severity Level 18: This error represents nonfatal internal software error.

Severity Level 19: This error represents some non-configurable internal limit has been exceeded and the current batch process is terminated. To be very frank, I have not seen this severity practically in my life.

Severity Level 20: This severity indicates current statement has encountered a problem and because of this severity level client connection with SQL Server will be disconnected.

Severity Level 21: This severity indicates that you have encountered a problem that affects all processes in the current database.

Severity Level 22: This error indicates problem with database table or index. It may be corrupt or damaged.

Severity Level 23: This error indicates problem with database integrity which may be fixed by DBCC command.

Severity Level 24: This error indicates problem with the hardware of SQL Server. Need to check disk drive and related hardware extensively.

Older version of SQL Server had Severity Level 25 as well but it is unexpected system error and doesn’t list in SQL Server 2012’s sysmessages catalog view.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Error handling with “THROW” command in SQL Server 2012

Error handling with “THROW” command in SQL Server 2012

error

Error handling is one of the essential skill developer should have. It was very difficult to handle error efficiently till SQL Server 2000. After SQL Server 2000, we have get TRY…CATCH in SQL Server along with RAISERROR in SQL Server 2005. RAISERROR is even improved in form of “THROW” in SQL Server 2012.

Let us see how “THROW” command works for error handling efficiently in SQL Server 2012 because without know the error/bug in application, you can’t solve it. It is not possible/feasible, sometime, in big production environment to replicate same issue in development database, at the sametime, we can’t execute some command/query on live environment if it is affecting client’s data so efficient error handling is required so that you can log proper error along with its message, error number and other important things.

Let us create sample temporary table in AdventureWorks2012 database, if you don’t have Adventureworks2012 database with you, you can use your own database as I will be having temp table for this demonstration.

[sourcecode language=”sql”]
IF OBJECT_ID(‘tempdb..#TestingTHROWCommand’) IS NOT NULL
DROP TABLE #TestingTHROWCommand

CREATE TABLE #TestingTHROWCommand
(
ID INT IDENTITY(1,1)
,Name VARCHAR(50)
,OvertimeAmount INT
)

INSERT INTO #TestingTHROWCommand
SELECT ‘Ritesh Shah’,15 UNION ALL
SELECT ‘Teerth Shah’,0 UNION ALL
SELECT ‘Rajan Jain’,9
GO[/sourcecode]

Now let us try to execute one UPDATE statement where we will have one division operator which divide on “OvertimeAmount” field. We have value “0” with row number 2 so obviously we will be facing an error.

[sourcecode language=”sql”]
BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE()
ROLLBACK TRANSACTION
END CATCH
GO[/sourcecode]

As soon as we will execute above code, we will be greeted with an error because on row # 2, we have 0 which will try to divide 95 and 0 can’t be used to divide anything. Here is the error message we will get:

[sourcecode language=”sql”]
(0 row(s) affected)
8134 Divide by zero error encountered. 3
[/sourcecode]

Look at the error number, message and error line number given above. Now, we will try to handle error with different way as follow:

[sourcecode language=”sql”]
BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE()
ROLLBACK TRANSACTION
RAISERROR(@ErrorMsg,16,1)
END CATCH
GO[/sourcecode]

We have same UPDATE statement so obviously we will be getting error message again but this time, it will come by “RAISERROR” command. Here is the output of RAISERROR.

[sourcecode language=”sql”]
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 10
Divide by zero error encountered.[/sourcecode]

Look at the error number and error line. It seems wrong because of “RAISERROR”. Let us now try to handle the error with “THROW” command.

[sourcecode language=”sql”]
BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW
END CATCH
GO[/sourcecode]

we will again receive proper error message, number and line with “THROW” command. Have a look:

[sourcecode language=”sql”]
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.[/sourcecode]

I have recently seen many of the developer who are using SQL Server 2012, still uses “RAISERROR” as against “THROW”. I would highly recommend start using “THROW” command.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Script backup of replication setup of SQL Server by SSIS and SMO

Script backup of replication setup of SQL Server by SSIS and SMO

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO”, “Backup Linked Server script in SQL Server by SSIS” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for replication publisher, subscriber, article etc. in SQL Server instance by SSIS and SMO.

Replication is one of the important High Availability and disaster recovery option for DBA which is being used by application load balancing too. When I failover (manually or automatically) the server, I suppose to have same replication and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package which uses SMO script to generate the script of replication along with its article of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of replication every week.

Let us now create new SSIS project and start creating package.

Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).

ReplicationBackupScriptTask

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Microsoft.SQLServer.Replication .NET Programming Interface

Apart from that, have following extra namespaces in “NameSpaces” region in your script window.

[sourcecode language=”c”]

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using Microsoft.SqlServer.Replication;

using System.IO;

using System.Collections.Specialized;

[/sourcecode]

Now here is the code you have to place in your “Main” method of script window.

[sourcecode language=”c”]

// TODO: Add your code here

string ServerName;

string UserName;

string Password;

string FolderDate;

string NetworkPath;

ServerName = &quot;ServerName&quot;;

UserName = &quot;Login&quot;;

Password = &quot;Password&quot;;

FolderDate = DateTime.Now.ToShortDateString().Replace(‘\\’,’_’).Replace(‘/’,’_’);

NetworkPath = @&quot;D:\&quot;+ ServerName.Replace(‘\\’, ‘ ‘).ToString() + @&quot;\Replicaiton\&quot;+FolderDate+&quot;\\&quot;;

ServerConnection conn = new ServerConnection();

conn.LoginSecure = false;

conn.Login = UserName;

conn.Password = Password;

conn.ServerInstance = ServerName;

Server srv = new Server(conn);

System.IO.Directory.CreateDirectory(NetworkPath);

ReplicationServer RS = new ReplicationServer(conn);

try

{

foreach (ReplicationDatabase RD in RS.ReplicationDatabases)

{

if (RD.HasPublications)

{

foreach (TransPublication TP in RD.TransPublications)

{

TextWriter tw = new StreamWriter(NetworkPath + &quot;\\&quot; + TP.Name.ToString() + &quot;.sql&quot;);

tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));

tw.Close();

}

}

}

}

catch (Exception eh)

{

//MessageBox.Show(eh.ToString());

}

Dts.TaskResult = (int)ScriptResults.Success;

[/sourcecode]

Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.

Here is the screen capture I have received after I ran it manually.

ScriptBackupReplicationPublisherSSISPackageRun

We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Backup Linked Server script in SQL Server by SSIS

Backup Linked Server script in SQL Server by SSIS

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database only, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for all Linkedserver in SQL Server instance by SSIS.

As long as possible, I, personally, try to avoid linked server but even in some scenario DBA used to use linked server because of any reason (don’t want to start debate of whether to use linked server or not). while making a plan for failover or for creating another server/instance of one of the production server, DBA has to keep Linked Server in mind otherwise some package, view, SP etc. can break in new server/instance if it is uses linked server.

Let us now create one SSIS package to generate the script of all Linked Server from SQL Server. I used to keep monthly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of linked server every month.

Let us now create new SSIS project and start creating package.

Have one “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.

[sourcecode language=”sql”]
SET FMTONLY OFF
Declare @SQL Varchar(Max)
Set @SQL =
‘Declare @LinkedServer Table ( LinkedServer Varchar(Max) )
Declare @status smallint,
@server sysname,
@srvid smallint,
@srvproduct nvarchar(128),
@allsetopt int,
@provider nvarchar(128),
@datasrc nvarchar(4000),
@location nvarchar(4000),
@provstr nvarchar(4000),
@catalog sysname,
@netname varchar(30),
@srvoption varchar(30),
@loclogin varchar(30),
@rmtlogin varchar(30),
@selfstatus smallint,
@rmtpass sysname,
@passwordtext nvarchar(128),
@i int,
@lsb tinyint,
@msb tinyint,
@tmp varbinary(256)

select @allsetopt=number from master.dbo.spt_values
where type = ”A” and name = ”ALL SETTABLE OPTIONS”

declare d cursor for
SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource, location, providerstring, catalog, srvnetname
from master..sysservers where srvid > 0 open d

fetch next from d into @srvid, @status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname

SET NOCOUNT ON

while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values(”——————–” + @Server + ”——————–”)
If @status in (64,65)
Begin
Insert Into @LinkedServer values( ”sp_addserver”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ””””)
Insert Into @LinkedServer values(” GO”)
If @status = 64
Begin
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values( ” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values( ” @optname = ””rpc””,”)
Insert Into @LinkedServer values( ” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
End
exec (”declare r cursor for select l.name, r.remoteusername from sysremotelogins r join sysservers s on r.remoteserverid = s.srvid join syslogins l
on r.sid = l.sid where s.srvname = ”””+ @server + ””””) open r fetch next from r into @loclogin, @rmtlogin
while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ”sp_addremotelogin”)
Insert Into @LinkedServer values(” @remoteserver = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @loginame = ”””+ @loclogin + ”””,”)
Insert Into @LinkedServer values(” @remotename = ”””+ @rmtlogin + ””””)
Insert Into @LinkedServer values(” GO”)

fetch next from r into @loclogin, @rmtlogin end close r deallocate r
End
Else
Begin
If exists (select * from tempdb..sysobjects where name like ”#tmpsrvoption%”)
Begin
drop table #tmpsrvoption
End

Create Table #tmpsrvoption ( srvoption varchar(30) )

insert #tmpsrvoption
select v.name from master.dbo.spt_values v, master.dbo.sysservers s
where srvid = @srvid and (v.number & s.srvstatus)=v.number and (v.number & isnull(@allsetopt,4063)) <> 0 and v.number not in (-1, isnull(@allsetopt,4063))
and v.type = ”A” Insert Into @LinkedServer values( ”sp_addlinkedserver”)

Insert Into @LinkedServer values(” @server = ”””+ @server + ””””)
Insert Into @LinkedServer values(”, @srvproduct = ””” + @srvproduct + ””””)
If @srvproduct <> ”SQL Server”
Begin
Insert Into @LinkedServer values( ”, @provider = ””” + isnull(@provider,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @datasrc = ””” + isnull(@datasrc,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @location = ””” + isnull(@location,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @provstr = ””” + isnull(@provstr,”NULL”) + ”””” )
Insert Into @LinkedServer values(”, @catalog = ””” + isnull(@catalog,”NULL”) + ””””)
End

Insert Into @LinkedServer values( ” GO”)
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””rpc””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
Insert Into @LinkedServer values(”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””rpc out””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
Insert Into @LinkedServer values(”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””data access””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)

declare s cursor for SELECT srvoption from #tmpsrvoption open s
fetch next from s into @srvoption while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ”””+ @srvoption + ”””,”)
Insert Into @LinkedServer values(” @optvalue = ””true”””)
Insert Into @LinkedServer values(” GO”)

fetch next from s into @srvoption
End

close s
deallocate s

If exists (select * from tempdb..sysobjects where name like ”#tmplink%”)
Begin
drop table #tmplink
End

create table #tmplink ( rmtserver sysname, loclogin sysname null, selfstatus smallint, rmtlogin sysname null )

insert #tmplink exec (”sp_helplinkedsrvlogin ”””+ @server + ””””)

declare ll cursor for select loclogin, selfstatus, rmtlogin from #tmplink order by rmtlogin open ll fetch next from ll

into @loclogin, @selfstatus, @rmtlogin while (@@FETCH_STATUS<>-1)

begin If (@selfstatus = 1 and @loclogin is null)

Begin

Insert Into @LinkedServer values( ”sp_addlinkedsrvlogin”)

Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)

Insert Into @LinkedServer values(” @useself = ””true”””)

Insert Into @LinkedServer values(” GO”)

End

Else If (@selfstatus = 1 and @loclogin is not null)
Begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””true””,”)
Insert Into @LinkedServer values(” @locallogin = ”””+ @loclogin + ”””,”)
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
Insert Into @LinkedServer values(” GO”)
End Else If (@selfstatus = 0 and @rmtlogin is null)
Begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””false””,”)
Insert Into @LinkedServer values(” @locallogin = NULL,”)
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
Insert Into @LinkedServer values(” GO”)
End Else If (@selfstatus = 0)
Begin
exec (”declare password cursor for select l.password from master..sysservers s join master..syslogins l on s.srvid = l.sid
where s.srvname = ”””+ @server + ””” and l.name = ”””+ @rmtlogin + ””””)

open password fetch next from password into @rmtpass while @@fetch_status = 0
begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””false””,”)

If (@loclogin is null)
Begin
Insert Into @LinkedServer values(” @locallogin = NULL,”)
End
Else
Begin
Insert Into @LinkedServer values(” @locallogin = ”””+ @loclogin + ”””,”)
End

If (@rmtlogin is null)
Begin
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
End
Else
Begin
Insert Into @LinkedServer values(” @rmtuser = ”””+ @rmtlogin + ”””,”)
End

If (@rmtpass is null)
Begin
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
End
Else
Begin
Insert Into @LinkedServer values(” @rmtpassword = ”””+ @rmtpass + ””””)
End

Insert Into @LinkedServer values(” GO”)

fetch next from password into @rmtpass
end
close password

deallocate password
End

fetch next from ll into @loclogin, @selfstatus, @rmtlogin End close ll deallocate ll
End

If @netname <> @server
Begin
Insert Into @LinkedServer values( ”sp_setnetname”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @network_name = ”””+ @netname + ””””)
End

fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname
End close d deallocate d Select [LinkedServer] From @LinkedServer’

Exec (@SQL)
[/sourcecode]

Here is the screen capture of “OLE DB Source” property.

2PropertyofDataflow

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “LinkedServer.sql” which I already had. You can keep blank SQL file in your destination.

33FileDestination

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “LinkedServer” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

FinalExecutionBackupLinkedServerScriptSSISPackage

We have many different server/instance and need script for all Linked Server from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

Note: If you have set password for your linked server, this script won’t decrypt password and give it to you. You have to manually change it in the script this SSIS package will create.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

I have written few more Linked Server related articles in past, have a look if you are interested.

  • Create Linked server with Excel 2007 worksheet in SQL Server 2005 (Click Here)
  • Linked Server in SQL Server 2005 from ACCESS 2007 (click Here)
  • Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider (Click Here)
  • Linked Server is not configured for data access Error: 7411 in SQL Server 2005(Click Here)
  • Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10″ for linked server “LinkServerName” was unable to begin a distributed transaction. (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

GRANT VIEW DEFINITION permission to all Stored Procedures in SQL Server

GRANT VIEW DEFINITION permission to all Stored Procedures in SQL Server

PermissionI have restricted all user from viewing script/text of stored procedure, views except DBA this is the part of our security policy for live environment. Generally DBA account should have permission to see the code of SP, View, Trigger etc. in live environment but I have requirement to let one person see script of all SPs. I have simply execute “GRANT VIEW DEFINITION” command if I had to let him view only few SPs but there are 100s of SPs in our database and I have to give him permission for all stored procedures. It is hectic to give permission for each SP manually and that is why I have quickly created one small TSQL script which give permission for “VIEW DEFINITION” on all stored procedures to given user.

TSQL script I am providing here is basically for “VIEW DEFINITION” permission for all “Stored Procedure” but you can twist this TSQL code quickly for your need to give any different permission to any other objects.

Let us first create One sample login and user for AdventureWorks2012 database. If you don’t have AdventureWorks2012 database, you can have it in your test or beta database.

[sourcecode language=”sql”]
USE [master]
GO

CREATE LOGIN [TestUser] WITH PASSWORD=N’testing’,
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks2012]
GO

CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
[/sourcecode]

Now, Here is the TSQL script which will give permission to the “TestUser” user in Adventureworks2012 database to view all stored procedures.

[sourcecode language=”sql”]
DECLARE @tblSPList TABLE (
SPID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
SPName SYSNAME
)

DECLARE @SPName SYSNAME,
@RowCounter INT,
@RecordCounter INT,
@UserName VARCHAR(100),
@ExecuteSQL VARCHAR(1000)

SET @UserName=’TestUser’

INSERT INTO @tblSPList (SPName)
SELECT ‘[‘+ROUTINE_SCHEMA+’].[‘+ROUTINE_NAME+’]’ FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘PROCEDURE’

SET @RecordCounter = (SELECT count(*) FROM @tblSPList)
SET @RowCounter = 1

WHILE (@RowCounter < @RecordCounter + 1)
BEGIN
SELECT @SPName = SPName
FROM @tblSPList
WHERE SPID = @RowCounter
SET @ExecuteSQL = N’Grant VIEW Definition on ‘ + rtrim(cast(@SPName AS VARCHAR(128))) + ‘ to [‘ + @UserName +’]’

–commenting following EXEC statement so that
–one can verify before execute
–EXEC(@ExecuteSQL)

–Print Execute Statement
PRINT @ExecuteSQL

SET @RowCounter += 1
END
GO
[/sourcecode]

You can modify this script to given different permission for different object.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Automatic script backup of mail profile in SQL Server by SSIS

Automatic script backup of mail profile in SQL Server by SSIS

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO” and “Automatic script backup of email operator in SQL Server by SSIS and SMO”, today I come up with article which generates the script for all mail profile in SQL Server instance by SSIS.

Mail Profile is one of the important configuration for DBA because of this mail profile I get to know the status of many different jobs and other important things. When I failover (manually or automatically) the server, I suppose to have same mail profile(s) and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package to generate the script of all mail profile of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of email operator every week.

Let us now create new SSIS project and start creating package.

Have one “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.

[sourcecode language=”sql”]

SELECT ‘–Create Account’ AS CMD

UNION ALL

SELECT ‘EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ”’ + m.name + ”’,

@description = ”’ + m.description + ”’,

@email_address = ”’ + m.email_address + ”’,

@replyto_address = ”’ + m.replyto_address + ”’,

@display_name = ”’ + m.display_name + ”’,

@mailserver_name = ”’ + s.servername + ”’,

@mailserver_type = ”’ + s.servertype + ”’,

@port = ”’ + cast(s.port as nvarchar) + ”’,

@username = ”’ + isnull(c.credential_identity,0) + ”’,

@password = ”x”,

@use_default_credentials = 0,

@enable_ssl = 0′ AS CMD

FROM msdb.dbo.sysmail_account m

LEFT OUTER JOIN msdb.dbo.sysmail_server s

ON m.account_id = s.account_id

LEFT OUTER JOIN master.sys.credentials c

ON s.credential_id = c.credential_id

UNION ALL

SELECT ‘–Create Profile’ AS CMD

UNION ALL

SELECT ‘

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = ”’ + name + ”’,

@description = ”’ + isnull(description,’NULL’)+ ”’

‘ AS CMD

FROM msdb.dbo.sysmail_profile

UNION ALL

SELECT ‘–Create Link For Profile to Account’ AS CMD

UNION ALL

SELECT ‘

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = ”’+ a.name +”’,

@account_name = ”’ + b.name + ”’,

@sequence_number = 1 ‘ as CMD

FROM msdb.dbo.sysmail_profile as a

JOIN msdb.dbo.sysmail_account as b on a.name = b.name

GO

[/sourcecode]

Here is the screen capture of “OLE DB Source” property.

2OLEDBSource

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “MailProfile.sql” which I already had. You can keep blank SQL file in your destination.

3FlatFileProperty

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “CMD” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

4SuccessfullyRanPackage

We have many different server/instance and need script for all mail profiles from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Automatic script backup of email operator in SQL Server by SSIS and SMO

Automatic script backup of email operator in SQL Server by SSIS and SMO

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO” and today I come up with article which generates the script for all email operator in SQL Server instance by SSIS and SMO.

Email Operator is one of the important configuration for DBA because of this operator I get to know the status of many different jobs and other important things. When I failover (manually or automatically) the server, I suppose to have same operator and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package which uses SMO script to generate the script of all email operator of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of email operator every week.

Let us now create new SSIS project and start creating package.

Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).

1Package

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Apart from that, have following extra namespaces in “NameSpaces” region in your script window.

[sourcecode language=”C”]

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using System.IO;

using System.Collections.Specialized;

[/sourcecode]

Now here is the code you have to place in your “Main” method of script window.

[sourcecode language=”C”]

// TODO: Add your code here

StringCollection sc = new StringCollection();

ScriptingOptions so = new ScriptingOptions();

so.IncludeDatabaseContext = true;

string ServerName;
string UserName;
string Password;
string FolderDate;

ServerName = "ServerName";
UserName = "Login";
Password = "Password";
FolderDate = DateTime.Now.ToShortDateString().Replace(‘\\’,’_’).Replace(‘/’,’_’);

ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = UserName;
conn.Password = Password;
conn.ServerInstance = ServerName;

Server srv = new Server(conn);
System.IO.Directory.CreateDirectory("D:\\MyServerName\\Operators\\" + FolderDate.ToString());
try
{
string script = "";
string OperatorName;

//Loop over all the jobs
foreach (Operator O in srv.JobServer.Operators)
{
//Output name in the console
Console.WriteLine(O.Name.ToString());

OperatorName = O.Name.ToString();
sc = O.Script(so);

//Get all the text for the job
foreach (string s in sc)
{
script += s;
}

//Generate the file
TextWriter tw = new StreamWriter("D:\\MyServerName\\Operators\\" + FolderDate.ToString() + "\\" + OperatorName.Replace(‘:’, ‘_’).ToString() + ".sql");
tw.Write(script);
tw.Close();

//Make the string blank again
script ="";
}
}
catch
{
//MessageBox.Show(eh.ToString());
}

Dts.TaskResult = (int)ScriptResults.Success;
[/sourcecode]

Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.

We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

UNPIVOT in SQL Server to convert column to row

UNPIVOT in SQL Server to convert column to row

Recently I got one new stored procedure to deploy in our live server. Before I deploy it to live, I have checked it and found heavy read and CPU in that SP. While investigating I found that CURSOR has been used in that SP and I can easily eliminate that CURSOR by using UNPIVOT in TSQL.

PIVOT and UNPIVOT both are wonderful enhancement in TSQL from SQL Server 2005. I really appreciate it, I have written many articles for PIVOT so far but unfortunately I haven’t written anything related to UNPIVOT in all these years so I just wanted to share this UNPIVOT feature with my blog reader so that SQL / .NET developer can use it whenever it is needed.

Pivot can easily converts your row data into column by aggregating values. Refer following links to learn more about PIVOT.

PIVOT –Fixed Column Cross Tab Query in Microsoft SQL Server 2005 (Click Here)
Dynamic PIVOT with month number to month name as header in SQL Server 2008 (Click Here)
PIVOT task in Data Flow Transformation in SSIS 2008 (Click Here)
Dynamic PIVOT with WHERE condition in SQL Server 2005 (Click Here)
Generic stored procedure for PIVOT in SQL Server (Click Here)

UNPIVOT is completely opposite to PIVOT. UNPIVOT converts your column to row. Let us understand this by an example:

Let us create one table with sample data:

[sourcecode language=”sql”]

IF OBJECT_ID(‘tempdb..#UnPivotSampleTable’) IS NOT NULL
DROP TABLE #UnPivotSampleTable

CREATE TABLE #UnPivotSampleTable
(
Customer VARCHAR(15)
,January INT
,February INT
)
GO

INSERT INTO #UnPivotSampleTable
SELECT ‘Ritesh’,1,6 UNION ALL
SELECT ‘Rajan’,NULL,8 UNION ALL
SELECT ‘Teerth’,3,NULL
GO

SELECT * FROM #UnPivotSampleTable
GO
[/sourcecode]

Here is the output of SELECT query. It shows customer name and quantity of order they have had in month of January and February.

1SELECTQuery

Now I just wanted to see which customer has given order in which month. I am not at all concern about the quantity; one of the easy ways to do this is UNPIVOT. Here is the TSQL for the same.

[sourcecode language=”sql”]

&amp;nbsp;

SELECT *

FROM

(

SELECT Customer,Orders

FROM #UnPivotSampleTable

UNPIVOT

(

VALUE

FOR Orders in (January,February)

) UnPivo

) tab

GO

[/sourcecode]

Here is the output of UNPIVOT which will show the name of month along with customer name. Ritesh has order in JAN as well as in FEB so there will be two rows for him and rest of the customer will have one row in result set.

2Unpivot

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.