Featured Articles
Facts to Learn about Triggers and Transactions by Snehalatha K Thommandram
Invoking Triggers using OLEDB Destination in SSIS by Snehalatha K Thommandram
Dynamic SQL Resources by Jeremiah Peschka
Time to Get Tested by Todd Robinson
Time to Get SMOoth by Todd Robinson
Facts to Learn about Triggers and Transactions (SQL 2005)
1) A trigger always works as if there is a pending transaction. This is
because a trigger is fired when the DML statement for which it is written
is executed, regardless of whether the statement is executing in the
implicit or explicit transaction mode.
2) In versions later than SQL 7.0 the @@TranCount global variable only gets
incremented if the count is 0 before you begin the transaction
Best Practices Based on the Facts Above.
a) In a trigger, always start an explicit transaction keeping in mind the
pending transaction that invoked the trigger won't cover the batch in the
trigger.
b) If you are too lazy (like me) to start a nested transaction and try to
use COMMIT OR ROLLBACK inside trigger, you will end up getting an error 3609
along with the message "the batch is terminated"
Source: Rollbacks and Commits in Stored Procedures and Triggers (BOL)
Invoking Triggers using OLEDB Destination in SSIS
A trigger is automatically fired, when a DML statement for which it is written is executed for e.g.: Insert, Update and Delete. That is not always the case if you use SSIS OLE-DB component task, I was new to BI development and I got excited to test the insert trigger defined on a particular table using SSIS.
So, I opened BI Studio and created a simple package to insert rows using Execute SQL Task, wrote a source query and an OLE-DB destination to insert rows in to the table, only to realize, the trigger won’t be fired unless you explicitly use the FIRE_TRIGGERS option using the Advanced Editor of the OLE-DB Destination.
Here is how you do it:
In the OLE-DB Destination Editor set the Data Access Mode to “Table or View – Fast Load” and hit OK.
Right Click the OLE-DB Destination Editor and Click “Show Advanced Editor”
Click on the Component properties tab and in the Fast Load Option type “FIRE_TRIGGERS” preceded by a comma. Set FastLoadMaxInsertCommit Size to 1. Otherwise the trigger (in this case, insert trigger) would be fired only once.
Note: FastLoadOption is only available when set the Data Access Mode to Table or View – Fast Load”
The above work around may not be performance-friendly but it helped me test whether a trigger that I am interested in is working properly or not.
Dynamic SQL Resources
Finding good resources on working with dynamic SQL can be difficult. Many DBAs and developers ignore this powerful tool and choose to work with more cumbersome solutions.
Stored Procedures - Executing & Dynamic SQL - SQL Server Pedia has a short article about working with the EXEC statement and the sp_executesql system stored procedure. Understanding how both of these work is fundamental to working with dynamic SQL.
SQLQueryStress - SQLQueryStress is a tool developed by Adam Macahanic that you can use to stress test queries and determine the frequency of query plan recompilation, amongst other things.
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 - This whitepaper from Microsoft covers a number of the issues that can make dynamic SQL perform poorly: namely compile and recompile times as well as parameter sniffing. While this paper does not give any dynamic SQL specific solutions, it highlights the causes of these issues and provides tools and techniques to identify and solve these issues.
Automating T-SQL Testing - I'm shamelessly linking to myself here, but with good reason. I put together a T-SQL testing framework that makes heavy use of dynamic SQL. The benefit of this framework is that you can easily use it to run automated tests against dynamic SQL, especially when your dynamic SQL has optional parameters.
The Curse and Blessings of Dynamic SQL - This is probably the grandaddy of all resources on dynamic SQL. Erland Sommarskog works through many good coding practices for dynamic SQL and brings up security, formatting, and performance concerns and provides ways to address them.
One of the frequent links I like to visit is the Microsoft Certifications blog by Gerry O’Brien. Here he lists information on the certifications for developer and SQL Server technologies. In his blog, you will find information on current beta exams which are in development. Best of all, these posts most often include a registration code good for the beta period which can be used to take the exam for FREE!
I will stress though that if you do take them up on this wonderful offer that you
Show up – Nothing would make Gerry sadder then to offer these and have people not take them.
Be prepared – Look on the Microsoft Certification site for the objectives. These will give you an idea what will be on the exam.
Leave constructive comments – This is your chance to tell Microsoft how well they are doing! If a question might have an ambiguous answer, let them know, but also explain why you think so. If a question is hard to read or formatted weird, let them know how it could be better. The most important thing to remember is that your comments will be used to make a better exam experience for everyone.
Good luck on your next exam!
I just started a new position at a company where my main goal is to help them look over the performance, stability and disaster recovery of all their SQL Servers. During the first few days, you have to become acclimated to the systems, find out who supports what, and ultimately find out what are the servers that you’ll ultimately be responsible for.
They set me up with my workstation, granted me access to the database administrators group, and let me have at it. My first order of business was just to see what’s out there on the network. Previously, if you just wanted a list of all the servers that could be discovered on the network, you would use Osql with the –l parameter. It would return a list of all the servers it was able to browse on the network.
I ran this and saw many more rows than the 40 or so I was expecting. Obviously, there were some servers out there that hadn’t been documented. I wanted to gather a bit of details about these servers, specifically the version, just so I could see what I was facing. So what was the best way to do this?
I decided that I would use SQL Management Objects (SMO) to gather the same list of servers and hopefully get a bit more information out of them. Once I had my list I could delve further.
I brought up Visual Studio and started a new C# console application. In order to use SMO in your project, you need to add the references to the appropriate assemblies. Since I just want to list the servers out on the network as well as their version, at this time I will only need the Microsoft.SqlServer.Smo namespace.
Now I can add it to the list of directives at the top of the app so I don’t have to type everything out.
using Microsoft.SqlServer.Management.Smo;
Since I’m going to use a DataTable to store these values, I also want to add the directive
using System.Data;
Now I’m ready to start to get the info I want.
Inside of the Main function, the first thing I want to do is get a list of all the servers that are broadcasting on the network. To do this, I can use the EnumAvailableSqlServers method. You can specify the name of the server or instance you want to discover by providing the string as a parameter, but in this case, we want to see them all, so we don’t pass anything. This method in the SmoApplication class returns a DataTable with the enumerated list of SQL Servers. This table contains the columns Name, Server, Instance, Version, and isLocal and isClustered
DataTable dt = SmoApplication.EnumAvailableSqlServers();
Now that we’ve populated a table with the servers, we can iterate through the collection
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
Server svr = new Server((string)dr["Name"]);
Console.Write(svr.Name);
//Comment out the following line for a list just like OSql -l
Console.Write(" "+ svr.Information.VersionString);
Console.WriteLine();
}
}
Now when we run this application, we get a list of all the servers that are broadcasting on the network along with its version. In the next article, we’ll take this code and build on it to create a list of servers that we can access, as well as a list of “rogue” servers.