Simple PS script to copy files from one share to another

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.

Read more ›

TwitterMore
Posted in DBA, Nuggets, Powershell, Scripting

Include HTML in a SSIS Configuration File

This stumped me for a while, but a useful pointer from a colleague reminded me how this can be achieved…

Quite simply as the configuration file is XML we can use CDATA so that the content won’t be parsed. Here’s an example below:

<!-- Package replaces {SQLResults} with an HTML table showing Count by User for the last loaded date -->
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::EmailBody].Properties[Value]" ValueType="String">
<ConfiguredValue>
<![CDATA["
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body>
<div style="background-color:#fff;padding:20px;">
<table cellpadding="0" cellspacing="0">
<tr>{Message}</tr>
<tr>{SQLResults}</tr>
</table>
</body>
</html>
"]]>
</ConfiguredValue>
</Configuration>

Using the CDATA attribute inside the XML nodes will allow you to add HTML or any other XML tags inside your configuration file.

Posted in Nuggets, Scripting, SQL Server, SSIS

What port number does my SQL Server listen on?

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

Read more ›

Posted in Database, DBA, Nuggets, SQL Server

The Developer DBA toolkit

Fellow developers are often surprised at how much I use command line and xp_cmdshell. The reason for this is two fold:

  1. 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.
  2. 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 xp_cmdshell.

This post list a selection of tasks that come up again and again together with the necessary command lines to speed up the process!
Read more ›

Posted in DBA, Nuggets, Techie

A cleaner meaner website!

Well it has taken a long time to finally move off of WordPress 2.0, but we have finally done it. Welcome to the new look Protean IT!

Posted in Techie, Website, www

A simple restore script

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. Read more ›

Posted in Database, DBA, Nuggets, Scripting, SQL Server

Isn’t it ironic

It’s quite ironic that being an ETL and database developer by trade, it has actually been easier to upgrade this website manually, literally copying and pasting each of the blog entries.

SSIS which is the integration tool I have on my current machine, has no easy way to insert data into a MySQL database. It can be done, but it means setting up a foreach container and mapping recordset parameters to an Execute SQL task. Far too much effort for a one off upgrade.

Posted in Techie, www

ASP.Net – Sending Email Asynchronously

I’ve spent a lot of time over the last few months building a website, and as always, there is much help and information available on the internet. Something I struggled with however was the sending of emails… How can I send an email from my site without blocking the calling process? “Use sendAsync!” I hear people shouting. Having tried this I can say that it doesn’t help where asp.net is concerned. I implemented it myself, and whilst it is almost seamless on a web server with fast connectivity to the smtp box, what I found is there are occasions where this just isn’t fast enough, and the user experience suffers. Here’s what I have done to resolve this… I hope it saves someone the head scratching that I went through 😉

Read more ›

Posted in .Net, asp.net, C#, Website

Get the query and execution plan for a session

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.

Read more ›

Posted in Database, DBA, Microsoft, Nuggets, Performance Tuning, SQL Server

Disabling TDE on SQL Server 2008

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.

Read more ›

Posted in Database, DBA, Microsoft, Nuggets, SQL Server, TDE