xSQL Profiler version 1.6 now available for download

We have just released a new version of xSQL Profiler that adds the following features:

• ability to load the trace data directly to the central repository from a UNC path without having to go through the monitored servers temp db;
• the option to set different polling intervals for each trace on each target server;
• the option to configure server level traces reducing the number of traces that will need to be started on the monitored servers

xSQL Profiler allows you to configure and schedule SQL traces to run simultaneously on multiple servers and automatically collects all trace data into a central repository.

You can download the new version from: http://www.xsql.com/download/sql_server_profiler/

Trouble connecting to remote SQL Server instance

Cannot connect to server1\instance1… SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified…

From location1, machine1 connects to a remote SQL Server instance, no trouble. Take machine1 move to location2, very similar environment – machine1 fails to connect, the error message is the one above. You verify your SQL Server configuration and everything looks ok, and besides if something was not right you wouldn’t be able to connect from location1 either. So what is the problem?

It turns out location2 firewall is blocking UDP traffic and that is why you are not being able to connect. When the client sends the request to the server asking to connect to server1\instance1 it is the SQL Browser service (on that server) that handles the request and responds with more detailed information to the client, information that the client needs in order to establish the connection to the server. That communication between the SSMS and the SQL Browser service happens over UDP (port 1434). So, when the UDP traffic is filtered out the client does not hear back from the SQL Browser and consequently is not able to establish the connection.

So what can you do if you can’t change the firewall configuration? If you know where your SQL Server is listening then all you need to do is specify the protocol and the port number as shown on the pictures below and you will be able to connect – in this case the SQL Browser service is bypassed since the client already has all the info it needs to connect to that SQL Server instance.

What if you don’t know what port SQL Server is listening on? If you can connect from some other location to that server you can try Microsoft’s Port Query utility (this is a great tool for diagnosing your connection problems when you can not connect also) http://www.microsoft.com/downloads/details.aspx?familyid=89811747-C74B-4638-A2D5-AC828BDC6983&displaylang=en they also have a UI add on for this that you can download from here: http://www.microsoft.com/downloads/details.aspx?familyid=8355E537-1EA6-4569-AABB-F248F4BD91D0&displaylang=en – it will show you the response from the SQL Browser service indicating which port your SQL Server instance is listening.

Backup database structure only

Sometimes you need to back up the database structure (schema) only – it can be because you simply would like to maintain an audit trail of database schema changes or because you need to compare the schemas of two databases that you can’t access directly from one location.

Whatever your reasons maybe xSQL Object provides the ideal solution to accomplish this – with a click you can take a snapshot of the SQL Server database schema. The schema snapshot contains all the schema information but no data and consequently is a very small size file. You can then use xSQL Object to compare this schema snapshot to other snapshots or to live databases and see exactly what changed from one schema version to another.

T-SQL: set end date to first or fifteenth of the month

Here is a simple assignment: if the subscription date is from first to 15th of the month then the expiration date should be set to the 15th of the same month a number of years later; if the subscription date is from 16th to the end of the month then the expiration date should be set to the first of the following month a given number of years later.

I am sure there are many ways to do this but here is one:

DECLARE @sDate DATETIME -- subscription date
DECLARE @sYears tinyint -- number of years the subscription will last
SET @sDate = getdate()
SET @sYears = 1

IF datepart(dd, @sDate) <= 15
SELECT DATEADD(year, @sYears, @sDate) - datepart(dd, @sDate) + 15
ELSE
SELECT DATEADD(year, @sYears, DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0))

Script Executor - package t-sql scripts into executables

In addition of allowing you to deploy multiple scripts to multiple databases directly from the interface Script Executor also allows you to package all your scripts into an executable.

An executable package in the context of the Script Executor is a small, dynamically generated executable file that contains embedded in it scripts, list of databases against which the scripts will be executed, and a small piece of code to run these scripts. You can run it as you would run any other Windows program.

Executable packages are suited for deploying Sql script to the end-users/clients. A user can run it without having Script Executor, or SSMS installed.

SQL Server execute scripts - multiple scripts, multiple databases

You launch SQL Server Management Studio – open script #1 and execute it against database 1 on SQL Server 1, then execute it against database 1 on SQL Server 2… then open t-sql script #2 and execute it against database 1 on SQL Server 1 etc… what a painful process, you dread the day you have to go through this (sometimes that is everyday). What a waste of resources too – a $100K database administrator spending hours executing scripts on tenths of server!

No more! You can now use Script Executor to take care of this job for you. One time only, you will go through a simple, and may I suggest fun, process of organizing your scripts into virtual containers and your databases into groups; then, map those script containers to the database groups; set an order of precedence if you need to and voila you have a package that you can now execute with a single click; or, you can even schedule it to run before you even make it to the office.

Script Executor will execute each script in the order you have defined against each database based on the mappings you have defined and once completed it will generate a comprehensive report allowing you to see how the script execution went on each target and also easily “flip through” the result sets that those scripts may generate.

Deploying (executing) multiple scripts against multiple SQL Server databases has never been easier.

You can download a free trial version of Script Executor from: http://www.xsql.com/download/script_executor/

A free community edition of Script Executor with limited functionality is also available: http://www.xsql.com/download/script_executor/

Applies to:

• SQL Server execute t-sql scripts
• SQL Server execute multiple scripts
• SQL Server deploy scripts to multiple databases
• SQL Server package t-sql scripts
• SQL Server run multiple scripts

Kindle, Zune, iPod - a free gift with every purchase

This month only we are giving away a free cool gift with every license purchase - kindle wireless, Zune, iPod Touch etc. Check out the details here: http://www.xsql.com/promotions/