Thursday, November 10, 2011
Thursday, July 7, 2011
Change is good, even if it appears big and scary at first…
Ever find a MUCH easier way of doing things and just fell in LOVE with it? It was that way for me once I discovered table valued functions in SQL Server. From that moment on I saw everything as a function “hill” to climb. My happiest moment: turning a query which took over 90 seconds down to 3 seconds because of this discovery and a subsequent re-architecture of all database calls.
Fast forward to… now… the OUTPUT clause. I know, I know, been around since SQL 2005 but until you wrap your head around it, create a new database or have time to re-architect something old, you can’t really get the implications until you incorporate it in a new database design.
One call to a database to update or delete can not only save your data, but OUTPUT the data you want to save in another table for, oh I don’t know, change management, historical, CYA implications.
DELETE FROM [table] OUTPUT deleted.* INTO [tablearchive] FROM [table1] t1 JOIN [table] ON t1.ID=[table].table1_ID WHERE ID = 123
Double duty – deleting (or updating or inserting for that matter) and saving whatever details from that statement into another table. Saving yourself either an additional call from the front end, or at the very least, an additional SQL statement.
Just keeping it simple.
Monday, June 13, 2011
Honestly, I use this one several times a month, so putting it here for safekeeping.
Tuesday, March 8, 2011
public void DoesNotLeakConnections()
Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5"))
SqlCommand sqlcommand1 = s qlconnection1.CreateCommand();
sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";
sqlcommand1.ExecuteNonQuery(); //this throws a SqlException every time it is called. sql connection1.Close(); //Still never gets called. }// Here sqlconnection1.Dispose is _guaranteed_}
Tuesday, January 4, 2011
If so, try searching the entire solution for the ID – you should find the original object you copied and the search will also give you a hint of what object it was copied to that you need to reproduce.
In the package, do a Edit -> Find and Replace -> Entire Solution and paste in the annoying and mysterious ID in the error message received from the SQL Server Agent job that fails. This search should reveal at least two, most likely four lines where this ID shows up. Select which object is in the .dtsx package which is failing. If you scroll to the right in the Find Results screen, you should find the user friendly name of the object. It will be something like:
</DTS:Property DTS:Name=”ObjectName”>Some Object Name</DTS:Property>
You can also view the package contents by finding it with explorer and opening it with Notepad to reveal the XML. In this view search for the string of your object id not being found, and this will show you your friendly name for the object.
Now you have the user friendly name of the object. You should RE-create (from scratch – do not copy/paste) the connection. Use this new connection in the task(s) necessary, then delete the old connection, rebuild and deploy. Test again, if possible, from SQL Server Agent.
From my research it seems that Visual Studio has issues sometimes with copying connections from one package to another. Sometimes it keeps the old ID in the manifest or logging, and sometimes it even erases the ID and coughs up blood.
Hopefully, helpfully yours… Lara