TSQL - Refresh all orphaned users of a database after a restore

I've used this script for years, not sure where I pilfered it from but putting this here for safekeeping.

Lovin' the OUTPUT clause...

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.

Simple usage:

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.

Find Columns Named ? in SQL Database (SQL Server 2005 and above)

I've used this script for many, many years. I'm sure I borrowed the majority of it from a site. It comes in handy when I'm looking for all references of a particular column in a database.

Honestly, I use this one several times a month, so putting it here for safekeeping.

System.Data.SqlClient.SqlException: Timeout expired

You are most likely getting this because you are leaking connections, a good rule to follow is borrowed from Angel Saenz-Badillos: blog post.

public void DoesNotLeakConnections()
{
     Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5"))
     {
          sqlconnection1.Open();
          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_}

SQL Server SSIS: The connection "{SSIS Object ID, crazy long string}” is not found.

I would “hanker” a guess you copied a connection into a new SSIS Package, changed the name and successfully ran the package from Visual Studio. Yet, deploying it to the server produced the above error and when you search for the ID of the object the error message specifies, you can’t find it. Am I right? Even with a visual search through the IDs, you are unable to find the pesky ID stated in the error in your package, correct?

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