XCopy is great but sometimes you need something a little more powerful. Powershell can give you the ability to perform more complex manipulations.
Here we’re finding the previous weekday and using it to find, copy and rename a set of files into an automated regression testing environment.
Continue reading “Simple PS script to copy files from one share to another”
Looking for an easy way to know what port number your SQL Server is listening on? You can of course go to the Configuration Manager or check through the registry… but an even simpler way is to query the view
SYS.DM_EXEC_CONNECTIONS which will tell you provided you’re connected over TCP/IP
Continue reading “What port number does my SQL Server listen on?”
Fellow developers are often surprised at how much I use command line and xp_cmdshell. The reason for this is two fold:
- It saves a lot of time because it means that you don’t always have to remote on to the server to view debugging information or execute local commands. e.g. Is XYZ service running? Who’s logged on to the box? Run that SSIS package.
- You can view results from a different security context which is often the key for successfully debugging connectivity issues.
I always keep a script just for the purpose which I consider to be an essential part of any database developer/DBA toolkit. This is a selection of tasks that crop up again and again and will often save you a lot of time. Especially if you use
This post list a selection of tasks that come up again and again together with the necessary command lines to speed up the process!
Continue reading “The Developer DBA toolkit”
This is a simple script that finds all (backup) files in a directory that match a pattern specified. It then derives the database name from the filepath and attempts to do a full restore. It should be executed in SQLCMD mode.
This is a script I created purely for a continuous integration setup on my local machine. Continue reading “A simple restore script”
A simple piece of code to help you get the execution plan and the query text for a currently running query. This is especially useful when dynamic sql is being run against your database or if DBCC InputBuffer reports that a stored procedure is being run. You can actually view the execution plan for the batch that it’s running. It works using the 2005 management views and functions.
Continue reading “Get the query and execution plan for a session”
Or more accurately: Preparing an encrypted TDE enabled database for restore on a Standard Edition SQL Server
I recently had the challenge of restoring an encrypted database on to a standard edition server to enable further development on the database code. It took some work, but to save you some time, I’ve listed the steps (and the T-SQL) in this article that you need to take in order to accomplish this.
Continue reading “Disabling TDE on SQL Server 2008”
Many of you will have heard the mantra that loops are bad, set based is good. But how do you get around them?
The fact of the matter is that there are very few circumstances where a loop is the only way to achieve your objective. A look through the SQL forums will show you many a technique to turn iterative row by row processing into single transaction set based processing. I’m refering to hierarchical query techniques (see nested set theory) and of course the subject of this post, the numbers table.
Continue reading “A practical use of a numbers table.”
In an earlier post, I demonstrated how to send HTML formatted mail using the script task. Another frequently requested use of the mail task is to be able to send query results within the email message body. Here’s how…
Continue reading “SSIS – Writing SQL results to a string variable”
I talked in a previous post about the possibility of using the format command and bulk insert task inside of a foreach loop in order to load all your tables using a single package… I don’t much like the method as it means you have to run each table load in series, and you’re not taking advantage of SSIS (high speed dataflow task, parallelism and eliminating staging with a single pass transformation).
Continue reading “Dynamic table loading in SSIS (Part 2)”
A very frequent requirement in a database, especially in an OLTP system is to be able to capture the identity and values of the last inserted record in a table so that we can use it to populate the foreign key in a child table and any other tables that require it.
It is easy enough to capture the last inserted identity if we are only dealing one record at a time using
@@IDENTITY, but what happens when we need to do batch inserts? Well, the old way of doing things would mean you had the following options:
- Execute a loop (either a cursor or a WHILE statement), and for each iteration we’d write a record in each of the child tables
- You’d have to select the same data twice, once to insert the audit records and once to update the data
- You’d rely on a trigger to write to the audit table
Well that should now not be necessary as using the OUTPUT clause we can capture all the data we’ve inserted, updated or deleted in a temporary area for use later on, or say in an auditing scenario, we can output the data directly to the auditing table. More importantly we can use that data in a set based transaction instead of row based. The advantages here are obvious.
Amazon Link: Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes
Continue reading “OUTPUT clause – the SQL developers panacea.”