Microsoft SQL Server Tips & Tricks

In the below blog, I am going to share my experience on one of the unique case which I have worked couple of days back.

The issue will arise if you have installed SQL with the default collation and your machine is using English (US). Now say for example you want to do business in a country where the default language is other than English (US) and default date format is “DD-MM-YYYY” instead of MM-DD-YYYY”.  Moreover, your application have already been developed and the code cannot be changed to accept the date format in “MM-DD-YYYY”.

You may get the following error message when you are trying to update the table from SSMS directly.

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

If you run  dbcc useroptions you get the below output :

Set Option                                                                                                                       Value

-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------

textsize                                                                                                                         2147483647

language                                                                                                                         us_english

dateformat                                                                                                                       mdy

datefirst                                                                                                                        7

lock_timeout                                                                                                                     -1

quoted_identifier                                                                                                                SET

arithabort                                                                                                                       SET

ansi_null_dflt_on                                                                                                                SET

ansi_warnings                                                                                                                    SET

ansi_padding                                                                                                                     SET

ansi_nulls                                                                                                                       SET

concat_null_yields_null                                                                                                          SET

isolation level                                                                                                                  read committed

(13 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

From the above output we can see that the dateformat is “mdy”.

Now you go to the advance option and change the option to British English as in the below screen shot :

And run dbcc useroptions

Again, we will see the same output as shown below :

Set Option                                                                                                                       Value

-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------

textsize                                                                                                                         2147483647

language                                                                                                                         us_english

dateformat                                                                                                                       mdy

datefirst                                                                                                                        7

lock_timeout                                                                                                                     -1

quoted_identifier                                                                                                                SET

arithabort                                                                                                                       SET

ansi_null_dflt_on                                                                                                                SET

ansi_warnings                                                                                                                    SET

ansi_padding                                                                                                                     SET

ansi_nulls                                                                                                                       SET

concat_null_yields_null                                                                                                          SET

isolation level                                                                                                                  read committed

(13 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Hence this means that even we have changed the server option to British English, we are still seeing the old values for dbcc useroptions.

We get the above value, even after we have changed the language setting on the OS level.

Now if we use set dateformat dmy

And do an insert in the table we are able to do the changes, but not through the application and we can’t incorporate “set” option in the connection string. So now the question is, how can we achieve our desired result. The answer is a bit tricky :

Ø Change the OS settings.

Ø Change the server setting to British English.

Ø Now create a new user and use dbcc useroptions

Ø Now you would be seeing the option have changed.

Hence, if you have changed the settings then re-create the users which are related to the application and you would be able to use the new settings.

Additional Information:

http://msdn.microsoft.com/en-us/library/ms189751.aspx

Gaurav Srivastava

SE, Microsoft SQL Server

Reviewed by:

Nickson Dicson

Tech Lead, Microsoft SQL Server

When we run SQL 2005 setup we get the following error:

SERVERNAME: Setup has detected a problem with Microsoft .Net Framework installation and cannot proceed. Microsoft .Net Framework 2.0 is either not installed on this system or is corrupt.

This is a seemingly ambiguous error which does not directly deal with the underlying .NET being corrupt

Cause

Installation of Non-standard Microsoft fonts cause errors during SQL server 2005 setup

Resolution

·         From the core (local) log obtained this error:

<Func Name='PerformDetections'>

0

<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>

<Func Name='DisplaySCCWizard'>

CSetupBootstrapWizard - unknown error 0x80070057

<EndFunc Name='DisplaySCCWizard' Return='-2147024809' GetLastError='183'>

Error: Action "InvokeSqlSetupDllAction" threw an exception during execution.

Failed to load the Wizard UI. There is either a problem with .Net framework installation or it is not installed. : -2147024809

Message displayed to user

                Setup has detected a problem with Microsoft .Net Framework installation and cannot proceed. Microsoft .Net Framework 2.0 is either not installed on this system or is corrupt.

·         Conventional methods to tackle the .NET error did not resolve the issue

·         We tried multiple reinstallation attempts of the .NET framework 2.0

·         We had encountered previous issues where issues were caused by the Non-standard Microsoft fonts and on uninstallation of these and subsequent restart we were able to run the SQL setup successfully

How did we arrive here?

·         We went ahead to obtain a memory dump of the SQL Server setup and found an exception

Exception message:

Font 'Albertus Extra Bold (W1)' does not support style 'Regular'

·         We went ahead and removed the 'Albertus Extra Bold (W1)'  font

      (Refer : http://support.microsoft.com/kb/314960 )

·         After a server reboot when we ran setup again we were successful.

Good afternoon techies! I recently worked with some customers on SQL Server 2005 login failure issues. Thought I’d make a blog post on what exactly we do @ PSS in such situations and specifically share my thoughts on login failure issues relating with password policy checks.

Symptoms

Typical symptoms you would notice are these:-

a)       Your SQL Server logins start failing

b)      SA account is unable to login

c)       Any SQL Agent jobs for which a SQL login is the owner start failing.

d)      Users/Application which use SQL authenticated logins are experiencing login failures.

For e.g., you might notice these error messages in the job history,

Message: The job failed.  Unable to determine if the owner (sa) of job ITALIAN_JOB has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).

If you look up the SQL Error logs, you should see this,

2009-09-29 11:38:35.65 Logon Error: 18456, Severity: 14, State: 10.
2009-09-29 11:38:35.65 Logon Login failed for user 'SqlLogin'. [CLIENT: 172.30.199.199]

Let me tell you something more about “State: 10”. This state is reached while evaluating the password policy checks. So any SQL Login which has is_policy_checked=1, are eligible for state 10 failures.

You might ask me as to how does this affect your SQL Logins??

It does! If the SQL Server service account gets locked out on the domain controller, all SQL Server authenticated logins which use domain password policy enforcement feature (CHECK_POLICY) will be unable to login to the SQL Server until the service account is "unlocked".

And more importantly check_policy is by default ON for any SQL login you create, unless you explicitly turn it off.

So how does this check_policy work?

SQL Server after all is an application and it has to rely on the exposed Windows API’s to do work for it when it comes to any external authentication. This is implemented by call to WinAPI NetValidatePasswordPolicy to implement password complexity, history, lockout etc. Now, in SQL the thread which calls this API runs under the context of the SQLSvc account. So, if the SvcAccount is locked out, this operation fails & hence the login to SQL fails. Make sense ?

If you look up the documentation for the NetValidatePasswordPolicy API, it does 3 types of validation

NetValidateAuthentication (for checking password expiration and account lockout policy)

NetValidatePasswordChange (password validation when change password is done)

NetValidatePasswordReset (password validation during when password reset is done. Can also reset the lockout state)

Next up, check if all the accounts which owned jobs or experienced login failures have the check_policy turned ON. You can verify all the logins which have the check_policy turned on running the query,

select * from sys.sql_logins where is_policy_checked = 1 

More importantly, since SQL 2005 SP2, we have added new ring buffer entries (sys.dm_os_ring_buffers) for various security errors. For more info on this read RDorr’s blog at http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-sql-server-2005-sp2-security-ring-buffer-ring-buffer-security-error.aspx

So, lets query the security ring buffer for more information,

select * from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'

The Record column will contain the API Name as well as any ErrorCode that was returned by the API.

Sample output with error

RING_BUFFER_SECURITY_ERROR      3435668357     <Record id = "14" type ="RING_BUFFER_SECURITY_ERROR" time ="3435668357">

<Error>
<SPID>67</SPID>
<APIName>NetValidatePwdPolicy</APIName>
<CallingAPIName>CAPIPwdPolicyManager::ValidatePwdForLogin</CallingAPIName>
<ErrorCode>0x8</ErrorCode>
</Error>

As you can see a WinAPI call to NetValidatePwdPolicy is made to validate the password and there was a failure there. This is returned by GetLastError WinAPI. In the above logs the error returned was "0x8"

0x8 -> 8 (decimal) -> Not enough storage is available to process this command.

Interesting you should see this here. This means your system event logs should certainly have some kind of Kerberos/netlogon errors reported. Here is a sample,

11/11/2009 2:32:11 PM NETLOGON Error None 5719 N/A SFRDBC2 "This computer was not able to set up a secure session with a domain controller in domain LITTLER-US due to the following: Not enough storage is available to process this command. 

This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator. 

11/11/2009 2:32:11 PM Kerberos Error None 7 N/A SFRDBC2 The kerberos subsystem encountered a PAC verification failure.  This indicates that the PAC from the client _besadmin in realm STARWARS.COM had a PAC which failed to verify or was modified.  Contact your system administrator

These are some of the things you can do:-

1)       Verify if the service account is locked out. If it is, then have your sysadmin unlock it. But you still need to unlock your SQL account or restart sql service for them to work again. Also thinking ahead you might want to audit and find out who/why locked out the service account.

Now this can happen for any of the following reasons :-

a) Invalid attempts (by someone or some application) using the SQLSvc account to login to Windows.

b) SvcAccount is disabled on the DC.

c) Password has expired for the SvcAccount etc.

2)       If service account is NOT locked out and you are experiencing errors similar to ones above, the quick fix for this issue, is to disable CHECK_POLICY for the SQL Logins

In case you this is not an option and you want to avoid such issues in the future, we have introduced a new trace flag T4614. Trace flag 4614 when enabled allows SQL Server authenticated logins (eg. SA) that use Windows domain password policy enforcement (check_policy = ON) to log on to the instance even though the SQL Server service account is locked out or disabled on the Windows domain controller.

Note: this was introduced in build 2005.90.2194 and can be enabled as a dynamic trace flag using DBCC TRACEON (4614,-1). Read more about it at http://support.microsoft.com/default.aspx?scid=kb;EN-US;925744

Here is a sample script I wrote using which you can change disable check_policyfor ALL SQL logins (please test before use).

DECLARE @name sysname
DECLARE @tmpname nvarchar (1024)
set @tmpname = ''
DECLARE login_curs CURSOR FOR
select name from sys.sql_logins where is_policy_checked = 1;

OPEN login_curs
fetch next from login_curs into @name
WHILE @@fetch_status = 0
BEGIN
print 'Altering property CHECK_POLICY for Login: ' + @name
set @tmpname = N'ALTER LOGIN '+@name+' WITH CHECK_POLICY = OFF'
exec sp_executesql @tmpname
FETCH NEXT FROM login_curs INTO @name
END

CLOSE login_curs
DEALLOCATE login_curs
GO

3)       Get your System/Active Directory administrator to look into the Kerberos warnings/failures to see what the issue is.

I hope this helped clear up some of the concepts and helps you troubleshoot Login failures in SQL. As always, stay tuned for more. Cheers!

Sudarshan Narasimhan
Technical Lead, Microsoft SQL Server

BCP is a very commonly used operation and sometime very critical and failure could cause loss of operation time and inconsistent data. Below is one of the cause resulting in bcp operation failure and the error indicates some schema change issue. here is more about it.

Error
====

The BCP fails with the below error.

SQLState = 37000, NativeError = 4891
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Insert bulk failed due to a schema change of the target table.
BCP copy in failed

This error says that there have been changes in the schema that require the re-compilation of the plan and Insert bulk cannot support recompiles. This looks like a timing issue having to do with bcp sending row data to the server with an out-of-date schema due to schema level changes happening due to other SQL Server operation like Auto Update Statistics or Parallel execution on the same target table.

Any Operation on the Target tables which would require the recompilation of the query will cause this error messages.

In a normal scenario, the Schema Change could be addressed by a re-compile, however it turns out that for BCP re-compiles are not possible because at compile time, we are reading metadata from the input stream and we set up our statement based on that, and then basically discard that metadata. If we recompile, that metadata is completely lost, and compilation is no longer possible.

Below are few(among many) of the causes, why the BCP fails with the above error as recompile is required.

1) Parallel Bulk Inserts happening on the Same table and foreign keys are enabled on the table. This could lead to the deadlock.

2) Parallel Online rebuild index operation running.

3) Auto update stats.

In fact the problem comes from an optimization done for the constraints checking:

http://msdn.microsoft.com/en-us/library/ms186247(SQL.90).aspx

When constraints are disabled, a schema modify lock might be taken to update the metadata. But, this can interfere with other commands (such as an online index build) or transactions. For example, a snapshot isolation transaction accessing the target table might fail due to concurrent DDL changes.

Below is some of the action plan you can try, this helped in my case though.

1) Drop the Constraints before the BCP run and recreate them after the run

2) Disable the Auto update stats (To isolate the issue)

3) Check if any parallel index rebuilds happening.

If still the issue persists after implementing the above change, collect the Profiler trace to capture the activity when bcp is failing to further investigation.

Check the following link which explains about the auto update stats functionality and the locks applied. :

http://support.microsoft.com/kb/195565

Sandeep Dasam
SE, Microsoft SQL Server

Reviewed by:
Nickson Dicson
Tech lead, Microsoft SQL Server

You encounter report server connection failure with below error message. Below is one of the instance which I witnessed while connecting through SSMS, it fails while using the server name (SERVERNAMEABC)

Error:
=====

TITLE: Connect to Server
------------------------------

Cannot connect to SERVERNAMEABC.

------------------------------

ADDITIONAL INFORMATION:

The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. (Microsoft.SqlServer.Management.UI.RSClient)

------------------------------

The request failed with HTTP status 404: Not Found. (Microsoft.SqlServer.Management.UI.RSClient)

------------------------------

BUTTONS:

OK

------------------------------

The above error message is self-explanatory and suggests that there is some issues connecting to report server. Error 404 is a very generic error message.

Below is what I tried in my environment to help resolve the issue.

1)       Try to connect to SQL Server reporting service in SQL Server management studio using http://localhost/reportserver  or http://servername/reportserver

2)       Check if this is successful.

3)       If connection to SSRS is successful with http://localhost/reportserver  and fails with <server name>

4)       Modify the rsreportserver.config as below.

<URL ROOT>http://SERVERNAMEABC/reportserver <URL ROOT>

Make sure you are able to use the above link in IE to connect to reporting service successfully.

After this you should be able to use the <server name> to connect to SSRS in SQL Server management studio

By,
Nikesh Mhatre
SE, Microsoft SQL Server

Reviewed by,
Nickson Dicson
Tech lead, Microsoft SQL Server

NOTE:

This method should not be attempted on the following jobs:

Ø  Reindex

Ø  Update Statistics

Ø  Replication

Ø  Backup

How many times have we encountered a SQL job which runs for a long time (more than expected) only to error out or even worse hang! 

There is no automated method but this is a long winding method to Schedule jobs on the server which terminate upon reaching a cut off time. Using this method it’s like setting a timeout for the SQL job. Supposing our end objective was to run a stored procedure on SQL server whose execution needs to terminate (when it reaches a pre-decided cut off time), we need to do this:

In SQL server management studio:

CREATE PROC sp_userstoredproc as

Begin

set nocount off

declare @cmd1 varchar(1000),@cmd2 varchar(1000)

set @cmd1 = 'if ''?'' not in (''master'',''model'',''tempdb'',''msdb'') print ''Database Name:?'''

set @cmd2 = 'if ''?'' not in (''master'',''model'',''tempdb'',''msdb'') select [object_id],[name] from [?].sys.objects where type = ''U'''

exec sp_MSforeachdb @command1=@cmd1,@command2=@cmd2

End

We can then schedule this using one the two options:-

1) The Task Scheduler utility (Taskschd.msc)

2) The AT command:  Directions to use the AT command to schedule the above job would be as follows

AT \\SERVERNAME 3:00 /every:M,T,W,Th,F sqlcmd -SSERVERNAME\INSTANCENAME -Q"exec sp_userstoredproc" -E

In order to set the cut-off time for the job execution we need to navigate to the following dialog box present in the task created.

Another good script to automatically get an email notification when jobs are running for a long time is given here: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon

Recently we encountered a case wherein one of the user application which internally calls sp_column stored procedure in SQL server. This application is taking longer time to execute in SQL server 2005 SP3 when compared to SQL server 2000. This application uses sp_columns stored procedure to enumerate the column names. We noticed that there is a significant difference in the execution of sp_columns on table with '_' and without '_' as part of table name.

For Example, the below statement

exec sp_columns N'doctab2',NULL,NULL,NULL

Runs Quicker than the statement

exec sp_columns N'doc_tab2',NULL,NULL,NULL

[Cause]

=====================================================================================================

Different query is used for the table names which are having wild card characters in their name, in the stored procedure sp_columns. The Query is selected based on the condition whether the table name(input parameter) is having wild card character or not(variable: fusepattern).

[Work Around]

=====================================================================================================

We do not have any other option to make the Stored procedure work the same way for the table with and without underscore. One alternative would be to use the stored procedure exec sp_columns_90 instead of sp_columns as we can pass on a parameter (fusepattern) which decides whether or not to use condition to select the query. This stored procedure is undocumented and should be used as a last resort.

http://msdn.microsoft.com/en-us/library/ms187961(SQL.90).aspx

[More Information]

=====================================================================================================

-- In our case exec sp_columns N'f[_]ui',NULL,NULL,NULL  is taking around 550 Ms

-- Whereas all the other exec sp_columns on the tables without '_' are taking less than 10 Ms each.

We checked the stored procedure in detail. Below are the two queries being executed. based on if the escape character exist or not.

select TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
TABLE_OWNER = s_cov.TABLE_OWNER,
TABLE_NAME = s_cov.TABLE_NAME,
COLUMN_NAME = s_cov.COLUMN_NAME,
DATA_TYPE = s_cov.DATA_TYPE_28,
TYPE_NAME = s_cov.TYPE_NAME_28,
"PRECISION" = s_cov.PRECISION_28,
"LENGTH" = s_cov.LENGTH_28,
SCALE = s_cov.SCALE,
RADIX = s_cov.RADIX,
NULLABLE = s_cov.NULLABLE,
REMARKS = s_cov.REMARKS,
COLUMN_DEF = s_cov.COLUMN_DEF,
SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE_28,
SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH_28,
ORDINAL_POSITION = s_cov.ORDINAL_POSITION,
IS_NULLABLE = s_cov.IS_NULLABLE,
SS_DATA_TYPE = s_cov.SS_DATA_TYPE
from
sys.spt_columns_odbc_view s_cov
where
is_cov.object_id = @table_id -- (2nd) (@table_name is null or o.name like @table_name)

-- (2nd) and (@table_owner is null or schema_name(o.schema_id) like @table_owner)

and (@column_name is null or s_cov.COLUMN_NAME = @column_name) -- (2nd) and (@column_name is NULL or c.name like @column_name) and s_cov.ODBCVER = @ODBCVer
order by 17

If there is no pattern observed.

And below is the query where is the pattern is observed.

select
TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
TABLE_OWNER = s_cov.TABLE_OWNER,
TABLE_NAME = s_cov.TABLE_NAME,
COLUMN_NAME = s_cov.COLUMN_NAME,
DATA_TYPE = s_cov.DATA_TYPE_28,
TYPE_NAME = s_cov.TYPE_NAME_28,
"PRECISION" = s_cov.PRECISION_28,
"LENGTH" = s_cov.LENGTH_28,
SCALE = s_cov.SCALE,
RADIX = s_cov.RADIX,
NULLABLE = s_cov.NULLABLE,
REMARKS = s_cov.REMARKS,
COLUMN_DEF = s_cov.COLUMN_DEF,
SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE_28,
SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH_28,
ORDINAL_POSITION = s_cov.ORDINAL_POSITION,
IS_NULLABLE = s_cov.IS_NULLABLE,
SS_DATA_TYPE = s_cov.SS_DATA_TYPE
from
sys.spt_columns_odbc_view s_cov
where is_cov.ODBCVER = @ODBCVer and (@table_name is null or s_cov.TABLE_NAME like @table_name) and (@table_owner is null or schema_name(s_cov.SCHEMA_ID) like @table_owner) and
(@column_name is null or s_cov.COLUMN_NAME like @column_name)
order by 2, 3, 17

-- The Second query is used for the Underscore and is slow comparatively as it is using the like operator to filter the results which is costly.

-- In SQL server 2000 a different set of queries are used.  Which gives almost same duration for tables with underscore and non underscore.

-- We Obtained the Execution plans for the Queries

-- We observed the main difference is seen with the tables : sysobjrdb and sysschobjs

-- These tables are having index scan when compare to the index seek for the good execution.

-- We cannot access these tables directly. We could only access them using the DAC connection.

-- Tried with the DAC connection to view the data.

-- We will also be able to execute the view. sys.spt_columns_odbc_view using DAC connection.

.

Explanation for the Workaround

--------------------------------

The Execution of the stored procedure sp_columns, for the table with wildcard and without wildcard character, varies based on a variable 'fusepattern'. If this variable is set to ‘0’ , then it would use the faster query and if it is ‘1’,then it uses the slower query. The value is set to 1 if we have the wildcard character as per the code of sp_columns as below.

if (@fUsePattern = 1) -- Does the user want it?

begin

if ((isnull(charindex('%', @full_table_name),0) = 0) and
(isnull(charindex('_', @full_table_name),0) = 0) and
(isnull(charindex('[', @table_name),0) = 0) and
(isnull(charindex('[', @table_owner),0) = 0) and
(isnull(charindex('%', @column_name),0) = 0) and
(isnull(charindex('_', @column_name),0) = 0) and
(@table_id <> 0))

begin
print 'i am in the if block for wild char'
select @fUsePattern = 0 -- not a single wild char, so go the fast way.
end
end

The reason why we have separate queries is because we may not get the Table_id if we have wildcard character as above. And so we use the slower query with like operators instead of faster query with ‘=’ operator.

One workaround for this would be to use the new stored procedure sp_columns_90. This stored procedure has new parameter to specify the fUsePattern value. We can specify the parameter of value ‘0’ so that it would always use the faster query irrespective of whether we have the wildcard character or not. The syntax would be as below.

exec sp_columns_90 N'doctab2','TOWERUAT',NULL,NULL,2,0

exec sp_columns_90 N'doc_tab2','TOWERUAT',NULL,NULL,2,0

Sandeep Dasam
Microsoft SQL Server

Reviewed by

Ouseph Devis
Technical Lead, Microsoft Sql Server

Problem Description in our scenario:

============================

We have a SQL server 2005 (9.00.4035 Enterprise Edition X64) , when we try to open the configuration manager we get the below

error :

--------------------------

SQL Server Configuration Manager

---------------------------

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.

The specified module could not be found. [0x8007007e]

Environment :

===========

SQL : sql server 2005 9.00.4035 Ent X64 .
OS   : Win server 2003 Ent X64

Cause of the issue in our scenario:

==========================

The following path was missing from the system path of the Environment variables. 

%SystemRoot%\system32 & %SystemRoot%\system32\WBEM

Troubleshooting :
==============

•We confirmed that the WMI was consistent by running a WBEMTEST successfully.

•We then checked the Environmental variables.

In my computer->properties->advanced->Environmental variables saw the below variables ( user and system )

User path      : D:\apps_pub\bsys3020;C:\DOCUME~1\jl19811\LOCALS~1\Temp\jl19811\commands

System Path : %systemroot%\system32;C:\WINNT\system32\wbem;%systemroot%\system32\wbem;C:\apps_srv\sdk15640\DataAccess64\ADONET\dll;C:\apps_srv\sdk15640\DataAccess64\OLEDB\dll;C:\apps_srv\sdk15640\DataAccess\ADONET\dll;C:\apps_srv\sdk15640\DataAccess\OLEDB\dll;C:\apps_srv\sdk15640\DataAccess\ODBC\dll;C:\apps_srv\sdk15640\DataAccess64\ODBC\dll;C:\apps_srv\sdk15640\OCS-15_0\lib3p64;C:\apps_srv\sdk15640\OCS-15_0\lib3p;C:\apps_srv\sdk15640\OCS-15_0\dll;C:\apps_srv\sdk15640\OCS-15_0\bin;D:;;C:\apps_pub\sybase\sdk12521\DataAccess\OLEDB\dll;C:\apps_pub\sybase\sdk12521\DataAccess\ODBC\dll;C:\apps_pub\sybase\sdk12521\OCS-12_5\lib3p;C:\apps_pub\sybase\sdk12521\ADO.NET\dll;C:\apps_pub\sybase\sdk12521\OLEDB;C:\apps_pub\sy base\sdk12521\ODBC;C:\apps_pub\sybase\sdk12521\OCS-12_5\dll;C:\apps_pub\sybase\sdk12521\OCS-12_5\bin;C:\DOCUME~1\sc27431\LOCALS~1\Temp\sc27431\commands;D:\apps_pub\bsys3020;C:\apps_srv\mssql\90\Tools\binn\;C:\apps_srv\mssql (x86)\90\Tools\binn\;C:\apps_srv\mssql\90\DTS\Binn\;C:\apps_srv\mssql (x86)\90\DTS\Binn\;C:\apps_srv\mssql (x86)\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\

The highlighted part in BOLD was missing in the system path of the Environment variables.

Workaround in our scenario:

======================

We added %systemroot%\system32;C:\WINNT\system32\wbem;%systemroot%\system32\wbem  to the system path of the Environment variables and rebooted the server and after this we were able to  open the configuration manager.

Note: This post should not be treated as the Microsoft’s Recommendation or Resolution to the problem, it is only a workaround which worked in our environment and so we would like to share it.

Manikandan Veeruchamy
SE, Microsoft Sql Server

Reviewed by

Karthick Krishnamurthy
TL,Microsoft Sql Server

Recently we faced an issue where in clustered instance of sql server 2005 was taking long time to come online on a windows 2008 cluster.

      We checked the Error log and found the following

2009-10-05 10:58:27.41 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
 

2009-10-05 10:59:54.39 Server      Resource Manager Creation Failed: 0x8004d01c(XACT_E_CONNECTION_DOWN)

The Error Code 0x8004d01c means “A connection with the transaction manager was lost”

From the Error log we see that there is 1 min spend to communicate to MSDTC service. So it appears that the delay is caused while communicating MSDTC resource. We checked and confirmed that MSDTC was installed as a clustered resource which is requirement for sql server 2005 installed in a cluster.

We checked the Event viewer log and found the following warning reported for MSDTC

MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system HOU150W8UCSQL1D.

      From the above error message it seems sql server service was failing to communicate with clustered MSDTC since MSDTC was failing to use mutual authentication.

We found that Network DTC Access which allows communication with DTC over the network was not selected which prevented sql server to communicate a clustered instance of MSDTC.

For more information on what Network DTC Access is used for, please refer http://support.microsoft.com/kb/899191. This article also mentions that Mutual Authentication cannot be used in a clustered environment.

So to resolve this we enabled  Network DTC Access with no Authentication as follows. You can also follow the steps mentioned in the KB http://support.microsoft.com/kb/817064

a)    Start the Component Services administrative tool. To do this, click Start, click Run, type dcomcnfg.exe, and then click OK.

b)    In the console tree of the Component Services administrative tool, expand Component Services, expand Computers, right-click My Computer, and then click Properties.

c)     Click the MSDTC tab, and then click Security Configuration.

d)    Check the Network DTC Access with no authentication

However we observed that after making this change if the sql server and MSDTC resource are on the same  node of the cluster, sql server comes online
very fast. When the MSDTC and sql server resource are on another nodes it takes long time to come online.

We saw the following messages in the Application log at this time

MS DTC is unable to communicate with MS DTC on a remote system. MS DTC on the primary system established an RPC binding with MS DTC on the secondary system. However, the secondary system did not create the reverse RPC binding to the primary MS DTC system before the timeout period expired. Please ensure that there is network connectivity between the two systems.  Error Specifics:

We disabled the firewall on both the nodes of the cluster and found that sql server came online quickly irrespective of the nodes.

However we did not want to disable firewall but wanted to find some alternative to enable firewall and allow exceptions ports for DTC and RPC.

We added the program “C:\Windows\System32\msdtc.exe” in the windows firewall exception list as specified in the KB article http://support.microsoft.com/kb/899191 but even that alone did not help.

We used the article  http://support.microsoft.com/?id=250367 and set the registry to enable range of 200 ports from 5000-5200 for dynamic RPC allocation. Since for a cluster, dynamic RPC allocations requires at-least 200 ports for to perform certain operations.

For Windows 7 and Windows Server 2008 R2 we have option to create an Inbound Rule in the firewall for the range of ports. However in our case i.e  Windows server 2008 we can enable rule for only 1 port at a time so we used the following  powershell script to enable range of ports in the windows firewall in elevated mode

$Range1=[system.string]::Join(",",(5000..5200))
netsh advfirewall firewall add rule name="Ports 5000-5200" dir=in protocol=tcp localport=$Range1 action=allow

After creating the firewall exceptions using the above script the MSDTC issue was resolved and sql server was coming online on both the nodes of the cluster without any delay irrespective of the MSDTC resource.

Parikshit Savjani
SE, Microsoft SQL server

Reviewed By

Sudarshan Narsimhan
Technical Lead, Microsoft SQL Server

Below is the script that would first identify and display Orphaned users. Then this output is passed to the next level which identifies the objects owned by this User in all the databases

I have implemented temporary tables hence the entire script should be executed every time.

You can convert them to tables and script them to a Stored Procedure.

====================================================================

-----Start of Script-----

Set Nocount on

Declare @OrphLogins Table (SID Varchar(200), NTlogin Varchar(200))

 -- Inserting the orphaned NT user into temp table

Insert into @OrphLogins EXEC Sp_ValidateLogins

 --Display the number of Orphaned Users

Select NTLogin As "Orphaned Logins" From @OrphLogins

DECLARE @Login varchar(200)

DECLARE Orphcursor CURSOR FOR

SELECT NTLogin from @OrphLogins

OPEN OrphCursor

FETCH NEXT FROM OrphCursor INTO @Login

WHILE @@FETCH_STATUS = 0

BEGIN

Declare @TSequel Varchar(MAX), @DatabaseO Varchar(MAX)

    Select @DatabaseO = ' SrPri.name COLLATE DATABASE_DEFAULT as Login, DbPri.Name  COLLATE DATABASE_DEFAULT as [User],

 orph.name COLLATE DATABASE_DEFAULT As [Name],

 orph.type_desc COLLATE DATABASE_DEFAULT As [Object Type]

 From %D%.sys.objects orph

    Join %D%.sys.database_principals DbPri ON Coalesce(orph.principal_id,

 (Select Sch.Principal_ID From %D%.sys.schemas Sch Where Sch.Schema_ID = orph.schema_id)) = DbPri.principal_id

    Left Join %D%.sys.server_principals SrPri On SrPri.sid = DbPri.sid '

    Select @TSequel = 'SELECT * FROM

    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

                     + Replace(@DatabaseO, '%D%', [name])

    From master.sys.databases

    Where [name] = 'master'

    Select @TSequel = @TSequel + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '

                     + Replace(@DatabaseO, '%D%', [name])

    From master.sys.databases

    Where [name] != 'master'

    Select @TSequel = @TSequel + ') LL  Where Login = ''' + @Login + ''''

    --print @sql

    EXEC (@TSequel)

   FETCH NEXT FROM OrphCursor

   INTO @Login

END

CLOSE OrphCursor

DEALLOCATE OrphCursor

GO

-----End of script-----

Example Output

==========================

Orphaned Logins

--------------------

Domain\deluser2

LocalMachine\deluser3

DBID  DBName        Login                 User                  Name                 Object Type

----- ------------- --------------------- --------------------- -------------------- ---------------------

16    Test          LocalMachine \deluser3  LocalMachine\deluser3  Table1               USER_TABLE

16    Test          LocalMachine \deluser3  LocalMachine\deluser3  View1                VIEW

16    Test          LocalMachine \deluser3  LocalMachine\deluser3  proc1                SQL_STORED_PROCEDURE

16    Test          LocalMachine \deluser3  LocalMachine\deluser3  table2               USER_TABLE

16    Test          LocalMachine \deluser3  LocalMachine \deluser3  spLogin_OwnedObjects SQL_STORED_PROCEDURE

30    TransPublish  LocalMachine \deluser3  LocalMachine \deluser3  Table10              USER_TABLE

Levi Justus
Technical lead, Microsoft Sql Server

While installing sql server 2008 setup we might face following warning in the installation window

 Receive a warning about the network binding order on the Setup Support Rules page when  install SQL Server 2008 in a failover cluster

 The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced    configuration to change the binding order

Cause

We might see the above warning due to following reason

1)  The domain network is not the first bound network.

2) This issue may also occur when you have a disabled network adapter or a ghosted network adapter on the computer.

Note A network adapter that is enumerated in the Windows registry but that is hidden in Device Manager is called a ghosted network adapter. This issue may occur when you change a network connection's TCP/IP configuration from DHCP to a static IP. This issue may also occur when you have added and removed network adapters multiple times.

Resolution

SOLUTION I

=======================================================

For Disabled network adapter or ghosted network adapter we need to follow the below KB article:

 http://support.microsoft.com//kb/955963

 SOLUTION II
=======================================================

Here is the description to verify whether domain network is not the first bound network:

We get a warning message on Network Binding Order Setup Support rule:
The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configuration to change the binding order 

<Detail .txt >
Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.NetworkBindingFacet
NetworkBindingFacet: Looking up network binding order.
NetworkBindingFacet:   Network: 'Local Area Connection* 8' Device: '\Device\{4DB91193-72F1-4713-A938-EB73F27CFEC8}' Domain: '' Adapter Id: '{4DB91193-72F1-4713-A938-EB73F27CFEC8}'
NetworkBindingFacet:   Network: 'Production Team' Device: '\Device\{0BF4D354-E6E9-480C-91CF-DC598282C4C1}' Domain: 'UPHS.PENNHEALTH.PRV' Adapter Id: '{0BF4D354-E6E9-480C-91CF-DC598282C4C1}'
NetworkBindingFacet:   Network: 'Heart Beat' Device: '\Device\{5AC63784-8088-40F7-93C8-37F9CD03D445}' Domain: '' Adapter Id: '{5AC63784-8088-40F7-93C8-37F9CD03D445}'
NetworkBindingFacet:   Network: 'BackUp Network' Device: '\Device\{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}' Domain: '' Adapter Id: '{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}'
IsDomainInCorrectBindOrder: The top network interface 'Local Area Connection* 8' is bound to domain '' and the current domain is 'contoso.com'.
Evaluating rule        : IsDomainNetworkTopOfBindings
Rule running on machine: TESTLAB12
Rule evaluation done   : Warning
Rule evaluation message: The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced    configuration to change the binding order.
Send result to channel: RulesEngineNotificationChannel
</Detail.txt>

 Local Area Connection*8 has a GUID value of {4DB91193-72F1-4713-A938-EB73F27CFEC8} but we are not sure to which network adapter it corresponds to.

 To find out to which network adapter Local Area Connection *8 correspond to run the following command to list the NIC configuration:

"wmic nicconfig get description, SettingID > C:\nicconfig.txt"

 From the NIC configuration it shows that GUID value {4DB91193-72F1-4713-A938-EB73F27CFEC8} which is for Local Area Connection*8 from detail.txt corresponds to Microsoft Failover Cluster Virtual Adapter

<Nicconfig.txt>
HP NC380T PCIe DP Multifunc Gig Server Adapter                                      {52AEDCB0-9E8E-4243-9D5D-ED86E602DF23} 
HP NC380T PCIe DP Multifunc Gig Server Adapter #2                                 {5AC63784-8088-40F7-93C8-37F9CD03D445} 
WAN Miniport (IP)                                                                                      {7F583D1B-4203-4AD0-A6A0-4BD007D21735} 
HP Network Team #1                                                                                 {0BF4D354-E6E9-480C-91CF-DC598282C4C1} 
Microsoft Failover Cluster Virtual Adapter                                                    {4DB91193-72F1-4713-A938-EB73F27CFEC8} 
RAS Async Adapter                                                                                     {7C264673-3322-4312-AE60-D09B4E71E368} 
HP NC373i Multifunction Gigabit Server Adapter                                         {C6CEC36A-3D03-4C32-ACD0-2DB2851A841D} 
HP NC373i Multifunction Gigabit Server Adapter                                         {5C3D2BA3-B15C-4FBB-A093-B42C80C39B81} 
</Nicconfig.txt>

When "IsDomainNetworkTopOfBindings" rule is run by the sql server 2008 setup, it queries registry using WMI and gets the results from the following registry key :

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Linkage\<Bind>

So we can check the Bind order in registry as shown

<Bind>
\Device\{4DB91193-72F1-4713-A938-EB73F27CFEC8}                                : Microsoft Failover Cluster Virtual Adapter        
\Device\{0BF4D354-E6E9-480C-91CF-DC598282C4C1}                               : HP Network Team #1                                
\Device\{5AC63784-8088-40F7-93C8-37F9CD03D445}                               : HP NC380T PCIe DP Multifunc Gig Server Adapter #2 
\Device\{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}                               : HP NC380T PCIe DP Multifunc Gig Server Adapter #2 
</Bind>

To avoid the above warning message we need to change the Bind Order and reorganize it in a way that domain bound network is top in the list. Domain bound network can be found from detail.txt
 NetworkBindingFacet:   Network: 'Production Team' Device: '\Device\{0BF4D354-E6E9-480C-91CF-DC598282C4C1}' Domain: 'contoso.com' Adapter Id: '{0BF4D354-E6E9-480C-91CF-DC598282C4C1}'

Once the binding order is changed we need to run the installation and the warning will not be reported.

Meera R Sinhasane
SE, Microsoft Sql Server

Reviewed By

Amit Banerjee
Technical Lead, Microsoft Sql Server

Often we required the service account to be changed and this requires a restart of the service for the change to come in to effect.

Below is a vbscript that uses WMI to achieve this. This script has not been tested on Services that depend on other services. Will update this once those tests are complete.

Note: This is not recommended for Sql server service. In order to change the service account for sql server we can use sql server configuration manager 

Dim ChangeActStr

Dim Switch

‘You can use other parameters of SC.EXE in the below string like password etc

ChangeActStr="sc config ""<Custom Service Name>"" obj= ""LOCALSYSTEM"""

Set shell = CreateObject("wscript.shell")

Shell.run ChangeActStr,true

Switch=2

While Switch<>1

Set objCollection = GetObject("WinMgmts:").ExecQuery ("Select * from Win32_Service where name='<custom service name>'")

‘Exit if the Service does not exist

If objCollection.count = 0 Then

Wscript.Quit

End if

‘Using the for loop since you can use the same code if you modify the select query above for multiple services.

‘In our case we will have only one row returned

For Each obj in objCollection

                If UCASE(obj.State) <> "STOPPED" Then

                                obj.StopService

                                Wscript.Sleep 2000

                                Set ObjCollection = Nothing

                                Switch=2

                ElseIf UCASE(obj.State) ="STOPPED" Then

                                obj.StartService

                                Wscript.Sleep 2000

                                Switch=1

                End If

Next

Wend

Levi Justus
Technical Lead, Microsoft Sql Server

Summary

No matter how simple a UDF is, there's a large performance penalty paid when they're used.  This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more.  The penalty is incurred because the SQL Server database engine must create its own internal cursor like processing.  It must invoke each UDF on each row.  If the UDF is used in the WHERE clause, this may happen as part of the filtering the rows.  If the UDF is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing. It's the row by row processing that slows SQL Server the most. In many cases an expression can replace the functionality that the UDF provides and offer a significant performance benefit over a UDF (see examples below).

More Information

SETUP FOR THE TEST

======================

--- Create Test Table

CREATE TABLE UDF_parameters (param1 int, param2 int, param3 int, param4 int)

-- Create Clustered Index

CREATE CLUSTERED INDEX CLU1 ON UDF_parameters(Param4)

--Code for the UDF.

CREATE FUNCTION dbo.divide_func(@numerator as int, @denominator as int, @default as float)

RETURNS float

BEGIN

                if @denominator = 0

                                RETURN @default

                if @numerator = 0

                                RETURN @default

                RETURN @numerator/@denominator

END

--- Insert records in the table ----

declare @count int

set @count =1 -----> This will insert 1 million records to the table

while @count <1000001

begin

                INSERT INTO UDF_parameters values(@count+3,@count+2,@count+1, @count)

                set @count = @count+1

end

Using UDF in Query SELECT list

SET STATISTICS TIME ON

GO

select PARAM1, DEVIDED_VALUE = dbo.divide_func(param2,param3,CAST(param4 as float)), param4 from UDF_parameters

GO

SET STATISTICS TIME OFF

GO

Rows Executes StmtText

------ -------- ---------------------------------------------------------------------------------------------------------------

500000 1 select PARAM1, DEVIDED_VALUE = dbo.divide_func(param2,param3,CAST(param4 as float)), param4 from UDF_parameters

500000 1 |--Compute Scalar(DEFINE:([Expr1004]=[TEST1].[dbo].[divide_func](parameter... )

500000 1 |--Clustered Index Scan(OBJECT:([TEST1].[dbo].[UDF_parameters].[CLU1]))

If you notice the plan, we would see that the Function call is being made, for all the rows being returned by the Clustered Index Scan.

If you take a profiler trace while running this statement, you would notice the multiple executions of the UDF. (Image at the end of the Page)

Query with UDF in Where Clause

SET STATISTICS TIME ON

GO

select PARAM1,param2,param3, param4 from UDF_parameters where dbo.divide_func(param2,param3,CAST(param4 as float)) = 1

GO

SET STATISTICS TIME OFF

GO

Rows Executes StmtText

------ -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------500000 1 select PARAM1,param2,param3, param4 from UDF_parameters where dbo.divide_func(param2,param3,CAST(param4 as float)) = 1

500000 1 |--Filter(WHERE:([TEST1].[dbo].[divide_func]([TEST1].[dbo].[UDF_parameters].[param2],[TEST1].[dbo].[UDF_parameters].[param3],CONVERT(float(53),[TEST1].[dbo].[UDF_parameters].[param4],0))=(1.000000000000000e+000)))

500000 1 |--Clustered Index Scan(OBJECT:([TEST1].[dbo].[UDF_parameters].[CLU1]))

      Notice a similar behaviour in multiple executions of the UDF, when we have the UDF in the where clause. (Image at the end of the Page)

Using Expression In Query

SET STATISTICS TIME ON

GO

SELECT PARAM1, 

DEVIDED_VALUE =

                   CASE

                                WHEN PARAM3=0 THEN param4

                                WHEN param2=0 THEN param4

                                ELSE param2/param3

                   END ,

PARAM4

from UDF_parameters

GO

SET STATISTICS TIME OFF

GO

Rows Executes StmtText

------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

500000 1 SELECT PARAM1,DEVIDED_VALUE = CASE WHEN PARAM3=0 THEN param4 WHEN param2=0 THEN param4 ELSE param2/param3 END ,PARAM4 from UDF_parameters

0 0 |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [TEST1].[dbo].[UDF_parameters].[param3]=(0) THEN [TEST1].[dbo].[UDF_parameters].[param4] ELSE CASE WHEN [TEST1].[dbo].[UDF_parameters].[param2]=(0) THEN [TEST1].[dbo].[UDF_parameters].[param4] ELSE [TEST1].[dbo].[UDF_parameters].[param2]/[TEST1].[dbo].[UDF_parameters].[param3] END END))

500000 1 |--Clustered Index Scan(OBJECT:([TEST1].[dbo].[UDF_parameters].[CLU1]))

Notice, how using a case expression, has only on execution.

Comparison of the time taken by the Different Statements.

Rows Returned

WITH UDF in Select List

UDF in Where Clause

With Expression

0.5 Million

26 seconds

26 seconds

4 seconds

1.0 Million

52 seconds

52 seconds

8 seconds

1.5 Million

86 seconds

87 seconds

12 seconds

Snapshots of Profiler Traces

==============================

As we can see, the query when using the function is slower than the one using the CASE expression.

In this case I was using very simple parameters, but if we have some complex parameters (like those involving mathematical operation) we would see the response time increase even more.

by
Sourabh Agarwal
Technical Lead, Microsoft SQL Server