Multiple SSIS instances on the same machine

You can only have one instance of SSIS (SQL Server Integration Services) installed on a machine at a time.

But you can have multiple SSIS database instances on one machine at the same time. See this post on how to do that.

Get the SQL Server Instance name from T-SQL

To get the SQL Server instance name with T-SQL use one of the following two statements:

• select @@SERVERNAME
• select SERVERPROPERTY('SERVERNAME')

Important: The SERVERNAME property of the SERVERPROPERTY function uses the computer's current network name. So, if the computer (on which the SQL Server instance is installed) had its network name changed after the SQL Server instance was installed, the SERVERPROPERTY function will pick up the new name - @@SERVERNAME will not! @@SERVERNAME will use the network name of the computer at the time when the instance of SQL Server was installed.

Click here to see more properties of the SERVERPROPERTY function.

Get the names of all tables used in a SQL Server view

I want to be able to quickly identify the tables which make up a SQL Server view. I know I can just open the script and look at it, but I have the following problems:

1. All the scripts are one liners for some strange reason.
2. And some of the views contain large numbers of tables in the joins.

So I came up with this script. It will work in SQL Server 2000 and 2005.

Take note: This script is by no means foolproof, and I haven't tested it very extensively. I am even ignoring the tables' schemas altogether. And if a view and a table would have the same name my script wouldn't know the difference.
But hey, it returns the information I want, so I am happy.

I didn't use the system view INFORMATION_SCHEMA.VIEWS because it truncates a view's T-SQL script in the VIEW_DEFINITION column when the script is too long.

I rather used syscomments because it spreads a view's script over several records when the script is too long for the text column. This enabled me to search the whole script for tables names.
I am not sure if syscomments will split a script in the middle of a word when the script is too long for the text column. If that would be the case I could miss some table names because they can be split.
(syscomments is a system view in the sys schema in SQL Server 2005. In SQL Server 7.0 and 2000 it is a system table in the dbo schema.)

Undocumented Stored Procedures

Apparently there are extended stored procedures which are not documented in the Books Online. In this post I want to create a list of those stored procs with links to articles about how to use them. So far I have only one proc, but I will update this post as soon as I come across more.

• xp_ReadErrorLog (SQL Server 2005)

I don't think this one is an extended proc, but also undocumented:

• EXEC sp_MSForEachTable 'EXEC sp_spaceused [?]' - It allows one to execute a T-SQL statement against each table in a database.

KILLERSITES.COM

Most web developers (including myself) are guilty of abusing HTML to some extent. With abuse I mean using the wrong tools (tags) for the job.

A common "mistake" is to use tables to format the layout of your web page.

KILLERSITES.COM teaches you how to build web pages using the correct tags in the correct places.

Enjoy!

Best Practices for Troubleshooting Slow Running Queries

A colleague forwarded this article to me. Although the article contains very useful and valid tips, he pointed out that it does not discuss poorly written T-SQL as a possible cause for slow running queries.

Poorly written T-SQL is probably such a huge topic that it deserves an article of its own.

Anyhow, I hope you can use some of the troubleshooting tips.

A Software Developer Explaining His Job to an Engineer

I found this while reading a thread in The Code Project Lounge. I think it's brilliant!

A software developer explaining his job to an engineer:

Imagine that you are hired to build a bridge over a river. The width of the river increases slightly every day, except when it shrinks. Your budget does not allow for you to use concrete or steel - you can only afford timber and cut stone. Gravity changes from hour to hour, as does the viscosity of air. Your only tools are a hacksaw, a chainsaw, a rubber mallet, and a length of rope.

Welcome to my world.