Friday, October 29, 2010

Windows OS versioning and problem with @@version

Today I came across a problem in getting the OS name with @@version variable in SQL Server.The @@version  variable returns the build of NT engine(say Windows NT 5.2,6.0).If its 5.2 then the OS would be windows 2003 server, 6.0 indicates Vista.

Now if you are running on Windows7 or Windows2008 R2 then @@version will return Windows NT 6.1.
This is confusing since DBA can't make out whether the operating System is Windows 7 or Windows2008 R2.The reason is because Windows 2008 R2 uses the same engine of Windows 7.
The solution is you use command line utility systeminfo.exe which will display detailed configuration information about a computer and its operating system, including operating system configuration, security information, product ID, and hardware properties, such as RAM, disk space, and network cards.

If you are running on SQL Server 2005 or SQL Server 2008 then this utility can be executed using xp_cmdshell.
Please don't forget to enable xp_cmdshell  using sp_configure

Below attached is the sample script that will get you OS Name.

create table #tmpOSName

(Details varchar(8000))

insert into #tmpOSName(Details)

exec master..xp_cmdshell 'systeminfo'

select rtrim(ltrim(SUBSTRING(Details,9,LEN(Details)))) from #tmpOSName where Details like 'OS Name%'
drop table #tmpOSName

Please note this is dependent on xp_cmdshell ; so that needs to be enabled first.

Wednesday, October 27, 2010

SSIS job fails:The affinity mask and affinity I/O mask configuration should not conflict

I was called today to help an issue with maintenance plan in SQL Server 2005.The Integrity Job created with SSIS Package was failing. Neither the output file nor Job history had much detail except the error:

The package execution returned DTSER_FAILURE

To debug the issue I decided to capture trace with all events under Errors and Warning selected along with SQL:StmtStarting,SQL:StmtCompleted,SQL:BatchStarting,SQL:BatchCompleted ,RPC:Started
and RPC:Completed events selected.

After analyzing the trace file the error in which I was interested was Error: 5834, Severity: 16, State: 1 and  just below that it had entry

The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

On checking the configuration of the server it was found that  the same processor was affinitize for both SQL Server worker thread scheduling and for I/O processing.

Under this configuration when SQL Server executes RECONFIGURE statements it throws an exception.
I changed these values , set it separately ,restarted SQL Server (and of course SQL Agent)and tried executing  job .The integrity job succeeded without any error.These affinity value shouldn’t be shared for same processors or you should set  Automatically set processor affinity mask for all processors and Automatically set I/O affinity mask for all processors option.

Tuesday, October 26, 2010

Help SQL Server to identify each other across internet

Recently I bought a new laptop and had SQL Server 2008 installed on that. The old one carried SQL Server 2005 Express edition.

Thought why not try to identify each of these SQL Server instances  when both of them are connected to internet or try registering SQL Server express edition instance in the laptop that carry SQL Server 2008.

The first step that you need to do is make sure that the instance which you want to register runs in a static port. There are two types of ports which can be used by SQL Server (Static and Dynamic).Static ports once set are not changed when SQL Server is restarted. Dynamic ports are changed every time SQL Server restarts.

To set a static port Open SQL Server Configuration Manager ->SQL Server Network Configuration->Select the Protocols for your instance->Select TCP/IP on the right side and open its properties.Go to IP Addresses tab, scroll down and check if under IP All category you can find TCP Dynamic Ports and TCP Port.Make sure that you don't have any value in TCP Dynamic Ports and enter a value for TCP Port (say 2315). Now restart SQL Server instance. SQL Server instance would use run on this port and it woudn't change regardless the number of time SQL instance gets restarted.

The second step is to make sure that an exception is added in firewall settings for the port underwhich SQL Server runs.In our case it would be 2315.

To add exception you need to go to run and execute firewall.cpl. This will open the firewall window using which you can set exception.Click on allow a program through firewall.Go to the exception tab and click on add port.A new window will pop up. Just enter the Port Number and associate it with a name.Click on OK and then apply.Thats all you are all set to access this SQL Server from other computer in internet.

 To verify go to the other computer and open SQL Server Management studio.Try registering the SQL Server for which you have added exception by  giving IP address and port number.For example if the IP of SQL Server is and port number was 2315 then try accessing it  as,2315.If you want to hide IP and
access SQL Server instance with name please add an alias in Client Configuration Utility.