SQL Server Compare – sy.configurations – Using Linked Server

Background

Needed a quick code to compare SQL Instance configurations across two SQL SQL Server Instances.

Prerequisite

Please create a linked server named Secondary to the other SQL Server Instance.

Sample Code

Code

Here is a sample code for comparing SQL Server configuration’s setting set via sp_configure.

 

select 
		  [basedOn] = cast (serverproperty('servername') as sysname)
		, [name] = tblSCSrc.name collate Latin1_General_100_BIN2
		, [valuePrimary] = tblSCSrc.value
		, [valueSecondary] = tblSCDes.value
		, [missing]
			= case 
					when ( tblSCDes.[value_in_use] is null) then 1
					else 0
			 end

from   [master].sys.configurations tblSCSrc

left outer join [SECONDARY].[master].sys.configurations tblSCDes


	on tblSCSrc.[name] = tblSCDes.[name] collate Latin1_General_100_BIN2

where  (

			(
					   ( tblSCSrc.[value_in_use] != tblSCDes.[value_in_use] )
					or (tblSCDes.[value_in_use] is null )
			)

		)

union

select 
		  [basedOn] = tblSCDes.[basedOn]
		, tblSCSrc.name collate Latin1_General_100_BIN2
		, [valuePrimary] = tblSCSrc.value
		, [valueSecondary] = tblSCDes.value
		, [missing]
			= case 
					when ( tblSCDes.[value_in_use] is null) then 1
					else 0
			 end

from   [master].sys.configurations tblSCSrc

right outer join
(
	select *
	from   openquery
	(
	  	   [SECONDARY]
		 , 'select 
				      [basedOn]
						 = cast (serverproperty(''servername'') as sysname)

					, tblSC.* 
			from [master].sys.configurations tblSC 
			'
	)
)
		 tblSCDes

	on tblSCSrc.[name] = tblSCDes.[name] collate database_default

where  (

			(

				   (tblSCSrc.[value_in_use] is null )

			)

		)

Output

comparesqlserversettings_20170225_0426

 

SQL Server Management Studio ( SSMS ) – Non-Printable Characters

 

Background

Through the use of a Microsoft LightSwitch application that we are working on, I have been eating my dog food.

 

Data Entry

As one who hates the sheer drudgery of data entry, I end up doing quite a bit of copy and paste.

I copy and paste data from the original Excel file into the LightSwitch application.

And, things have been mostly OK; as the data that needs to be copied over is minimal.

But, recently I started noticing little errors cropping up.

I launched SQL Server Profiler and noticed the errors are due to referential integrity.

The error was traced to trying to place data into secondary tables whereas those same data are not in our primary data.

 

TroubleShooting

 

Query Data

Launched Sql Server Management Studio ( SSMS ), and queried for the data and they appear to be in the Primary table.

 

Issue

After a bit of head scratching traced the problem back to white spaces at the end of the primary key fields.

 

Remediation

As always took to the Net to determine fixes and workarounds.

Outline

Here are the workarounds that we found thus far:

  1. Using Transact SQL
    • Identify data that have non-printable characters ( CRLF )
    • Strip out the Non-Printable characters

 

Code

Using Transact SQL

Here the functions that I found on the Net

Sourced

  1. dbo.fn_ShowWhiteSpace
  2. dbo.fn_nonPrintableStringClean

 

Code Snippet

 
use master
go
 

 
if schema_id('npc') is null
begin
 
    exec('create schema [npc] authorization [dbo];');
 
end
go
 
if object_id('[npc].[fn_ShowWhiteSpace]') is null
begin
 
    exec('CREATE FUNCTION [npc].[fn_ShowWhiteSpace]() RETURNS varchar(8000) AS BEGIN return 1/0 END')
 
end
go
 
ALTER FUNCTION [npc].[fn_ShowWhiteSpace]
(
    @str varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
    /*
        Michael Riley - AKA Gunny
        <a href="http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq">http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq</a>
 
        Michael Riley - AKA Gunny - Profile
        <a href="http://stackoverflow.com/users/195983/michael-riley-aka-gunny">http://stackoverflow.com/users/195983/michael-riley-aka-gunny</a>
 
    */
 
     DECLARE @ShowWhiteSpace varchar(8000);
     DECLARE @expanded bit
 
     set @expanded = 1
 
     SET @ShowWhiteSpace = @str
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(32), '[?]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(13), '[CR]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(10), '[LF]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(9),  '[TAB]')
 
     if (@expanded = 1)
     begin
 
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(1),  '[SOH]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(2),  '[STX]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(3),  '[ETX]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(4),  '[EOT]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(5),  '[ENQ]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(6),  '[ACK]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(7),  '[BEL]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(8),  '[BS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(11), '[VT]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(12), '[FF]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(14), '[SO]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(15), '[SI]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(16), '[DLE]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(17), '[DC1]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(18), '[DC2]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(19), '[DC3]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(20), '[DC4]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(21), '[NAK]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(22), '[SYN]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(23), '[ETB]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(24), '[CAN]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(25), '[EM]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(26), '[SUB]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(27), '[ESC]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(28), '[FS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(29), '[GS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(30), '[RS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(31), '[US]')
 
    end --if (@expanded = 1)
 
    RETURN(@ShowWhiteSpace)
 
END
go
 
grant execute on [npc].[fn_ShowWhiteSpace] to public
go

 

 

 

 
use [master]
go
 
set ansi_nulls on
go
set quoted_identifier on
go
 

if schema_id('npc') is null
begin
 
    exec('create schema [npc] authorization [dbo];');
 
end
go
 
if object_id('[npc].[fn_StringClean]') is null
begin
 
    exec('CREATE FUNCTION [npc].[fn_StringClean]() RETURNS varchar(8000) AS BEGIN return 1/0 END')
 
end
go
 
ALTER function [npc].[fn_StringClean] 
(
 @strIn as varchar(8000)
)
returns varchar(8000)
as
begin
 
    /*
        Blog
        How to remove unprintable characters from various fields in table
        <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf3e072-f194-4bff-87ee-07376927a7f8/how-to-remove-unprintable-characters-from-various-fields-in-table?forum=transactsql">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf3e072-f194-4bff-87ee-07376927a7f8/how-to-remove-unprintable-characters-from-various-fields-in-table?forum=transactsql</a>
 
        Profile:
        NaveenCR
        <a href="https://social.msdn.microsoft.com/profile/naveencr/?ws=usercard-mini">https://social.msdn.microsoft.com/profile/naveencr/?ws=usercard-mini</a>
    */
 
    declare @iPtr as int
 
    set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 
    while @iPtr > 0 
    begin
    
      set @strIn = replace(@strIn COLLATE LATIN1_GENERAL_BIN, substring(@strIn, @iPtr, 1), '')
 
      set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 
     end
 
     return ( @strIn )
 
end
go
 
grant execute on [npc].[fn_StringClean] to public
go

 

 

Lab

Code
 

 

    select 
 
              tblLS.[serverName]
 
            , [serverName_Len] 
                = len(tblLS.[serverName])
 
            , [serverNameTrim]
                 = ltrim(rtrim(tblLS.[serverName]))
 
            , [serverName_Trim(Len]
                 = len(ltrim(rtrim(tblLS.[serverName])))
 
            , [ShowWhiteSpace]
                = [master].dbo.[fn_ShowWhiteSpace](tblLS.servername)
 
            , [serverName_NPClean]
                 = [master].[npc].[fn_StringClean]  (tblLS.[serverName])
 
            , [serverName_NPClean_Len] 
                = datalength([master].[npc].[fn_StringClean]  (tblLS.[serverName]))
 
    from   [DBLAB].dbo.[listofServers.2017022] tblLS
 
    where  (
                (
                    (
                         tblLS.servername 
                            != [master].[npc].[fn_StringClean] (tblLS.servername) 
                    )
                )
          )    

 

 

 

Output

dbo_listofservers_20170222_1036pm

 

Explanation
  1. Here are the columns that are being shown
    1. serverName
      • The actual Server Name
      • The length of the Server Name
    2. serverName Trimmed
      • Using ltrim and rtrim we trim the Server Name
      • We get the length of the trimmed column
    3. Show whitespace
      • Show the serverName and display Non-Printable characters
    4. Show “Cleaned” serverName
      • Show cleaned Server Name
      • Show length of cleaned Server Name

 

Microsoft Connect Items

  1. SQl Server Management Studio should show new lines in records. – by Michael Freidgeim
    • Submitted By :- Michael Freidgeim
    • Item ID :-381955
    • Date Submitted :- 2008-Nov-14th 4 AM
    • Link
    • Description:-
      • 1. If there are newLines characters in the record data, SQl Server Management Studio in Grid view shows the record as one string, without indication that newlines are present.
        It causes confusion to the user, invalid interpretation of the data and even data loss.See scenario in my post
        http://geekswithblogs.net/mnf/archive/2008/11/13/sql-server-management-studio-doesnt-show-new-lines-in-records.aspx
        It will be good if newLines will be shown as actual new lines(consistent with SQL Server 2000 Enterprize Manager) or as some special character(e.g \n or |) to inform user.
        Ideally method on new line presentation could be configured in Tools/Options/Query Results/SQL Server/Results to Grid.
      • 2. By the way, separate suggestion: make Results to Text Maximum Length of the output default  to 8192  (rather than 256) to avoid truncations, that are not obvious.
    • Microsoft Feedback
      • Posted by Seshagiri ( Microsoft ) on 2011-May-31st 7:33 AM
        • Hi Michael,
          This is related to the connect item 381955. We evaluated the feedback carefully and are able to reproduce the cases you listed. However because of the following reasons we are unfortunately not able to attend to this request:

          • 1. new lines in grid
            When using the results to grid option, we use the standard Windows grid control to display the results. This grid control treats each cell value as a plain text and hence the new line characters are ignored. The Save as function when executed from the results section just takes the content from the grid as it is and hence the newline is not found in the new file created from the grid. However if you use the results to text option or the results to file the new line characters are retained. SSMS is not really intended to be a reporting tool, so we are unable to spend much time on the result formatting, especially if some workarounds exist like mentioned above.
          • 2. Max characters when using results to text
            The number specified here is used to preallocate memory for the text column data. A larger value for default could mean lot of unnecessary memory being allocated but not used. Hence it is left to the user to increase the value based on their needs.
            I hope the above sounds reasonable. I am currently closing the issue as a wont-fix. However in case there is anything not mentioned previously please let us know and we will surely take a relook.
          • Thank you
            Seshagiri
            PM, SSMS

SQL Server – SQL Server Agent – Job “Job History (OptimizePlan.Subplan_1)” Failed

Background

Reviewing SQL Server Agent jobs found ones that consistency fails.

One of them is a Maintenance Plan that Organizes Indexes.

 

Error Message

SQL Server Agent

Image

jobhistory-optimizeplan-subplan-20170222-0245pm-cropped-up

 

Textual

Executed as user: DBLAB\sql. … Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:00:40 PM Progress: 2017-02-22 15:00:41.05 Source: {04448243-3A2E-4299-BC33-5780CE3F7DEC} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp…”.: 100% complete End Progress Progress: 2017-02-22 15:00:43.82 Source: Progress Progress: 2017-02-22 15:00:43.86 Source: Reorganize Index Task Execut… The package execution fa… The step failed.

 

TroubleShooting

Maintenance Plan

Review the Maintenance Plan and here is what things look like.

OptimizePlan

Image

maintenanceplan_optimizeplan_20170222_0249pm-cropped-up

 

OptimizePlan

Reporting and Logging

Reporting and Logging ( Default )

Here is the default setting with “Log extended information” off.

reportingandlogging_20170222_0250pm

Reporting and Logging ( Post Changes )

Here is what happens when we set “Log extended information” on.

reportingandlogging_20170222_0259pm

 

Logfiles

Went to the identified Log Folder and sought for files matching the maintenance Plan we are trying to dig into.

optimizeplan_folder_20170222_0251pm

 

Logfile Contents

Image

content_20170222_0449pm

Text


USE [LNCD]
GO
ALTER INDEX [NCD_PK] ON [dbo].[NCD] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
USE [LNCD]
GO
ALTER INDEX [RelatedCase_PK] ON [dbo].[RelatedCase] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Reorganize index on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Object: Tables and views
Compact large objects
Task start: 2017-02-19T00:00:12.
Task end: 2017-02-19T00:01:56.
Failed:(-1073548784) Executing the query "ALTER INDEX [IX_FullNCDCategories_NCDID] ON [dbo]...." failed with the following error:
"The index "IX_FullNCDCategories_NCDID" on table "FullNCDCategories" cannot be reorganized because page level locking is disabled.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:
GO

GO

Explanation

  1. The error Message has the following content
    • USE LNCD
      • Changing to database LNCD
    • Failed:(-1073548784) Executing the query “ALTER INDEX [IX_FullNCDCategories_NCDID] ON [dbo]….” failed with the following error
      • The name of the index is IX_FULLNCCategories
    • “The index “IX_FullNCDCategories_NCDID” on table “FullNCDCategories” cannot be reorganized because page level locking is disabled.”.
      • The targeted table is FULLNCDCategories
    • The Index can not reorganized because page level locking is disabled on the targeted table

 

Remediation

Identify impacted tables

Identify Impacted tables on current database

Code


SELECT 
		  [database] = db_name()

		, [schema]   = schema_name(tblSO.schema_id)

		, [object]   = tblSO.[name]

		, [index]    = tblSI.[name]

		, [allowRowLocks]
			= case ( tblSI.[allow_row_locks])
				when 1 then 'Yes'
				else 'No'
			  end

		, [allowPageLocks]
			= case ( tblSI.[allow_page_locks] )
				when 1 then 'Yes'
				else 'No'
			  end

FROM sys.objects tblSO 

inner join sys.indexes tblSI 

	on tblSO.object_id = tblSI.object_id 

WHERE tblSO.[type] = 'U' 

and ( 
		   ( tblSI.[allow_row_locks] = 0)  
		or ( tblSI.[allow_page_locks] = 0 ) 
	) 

and  tblSO.[is_ms_shipped] = 0 

ORDER BY 
		tblSO.[name]



Image

identifyimpactedtablesoncurrentdb_20170222_0518pm

 

Identify Impacted tables on all databases

Code


DECLARE @commandPLLD	varchar(1000) 

declare @tblObjectPageLevelLockingDisabled TABLE
(
	  [database]			sysname
	, [schema]				sysname
	, [object]				sysname
	, [index]				sysname  null
	, [allow_row_locks]		int null
	, [allow_page_locks]	int null

	, [sqlStatementPreserve] 
			as 
				  'use ' + quoteName([database]) + ';' 
				+ '  '
				+ 'ALTER INDEX '
				+ QuoteName([index])
				+ ' ON '
				+ QuoteName([schema])
				+ '.'
				+ QuoteName([object])
				+ ' '
				+ '	SET (  '
				+ '		   ALLOW_PAGE_LOCKS = '
				+ case([allow_page_locks])
						when 1 then ' ON '
						when 0 then ' OFF '
				  end
				+ '		 , ALLOW_ROW_LOCKS = '
				+ case([allow_row_locks])
						when 1 then ' ON '
						when 0 then ' OFF '
				  end

				+ '		) '


	, [sqlStatementRevise] 
			as 
				  'use ' + quoteName([database]) + ';' 
				+ '  '
				+ 'ALTER INDEX '
				+ QuoteName([index])
				+ ' ON '
				+ QuoteName([schema])
				+ '.'
				+ QuoteName([object])
				+ ' '
				+ '	SET (  '
				+ '		   ALLOW_PAGE_LOCKS = ON  '
				+ '		 , ALLOW_ROW_LOCKS = ON  '
				+ '		) '

)

SELECT @commandPLLD = 'USE [?]; SELECT [database] = db_name(), schema_name(tblSO.schema_id), tblSO.name, tblSI.[name],  tblSI.[allow_row_locks]	, tblSI.[allow_page_locks] FROM sys.objects tblSO inner join sys.indexes tblSI on tblSO.object_id = tblSI.object_id WHERE tblSO.type = ''U'' and ( ( tblSI.allow_row_locks = 0)  or ( tblSI.allow_page_locks = 0 ) ) and  tblSO.[is_ms_shipped] = 0 ORDER BY tblSO.name ' 

insert into @tblObjectPageLevelLockingDisabled
(
	  [database]
	, [schema]
	, [object]
	, [index]
	, [allow_row_locks]		
	, [allow_page_locks]	
)
EXEC sp_MSforeachdb @commandPLLD


select *
		
from   @tblObjectPageLevelLockingDisabled tblPLLD




Explanation

The code snippet above does the following:

  1. Uses sp_MSforeachdb to run the same simple discovery code across all databases
  2. Queries the sys.indexes table looking for
    • allow_page_locks equal to 0
  3. When found it captures the
    • Current SQL for preserving the current Index State
    • The SQL to revise the current state unto set allow_page_lcoks to 1 & allow_page_locks to 1

Output

identifyimpactedtables_20170222_0506pm-croppedup

 

Ran

Captured the script from the sqlStatementRevise column.

Image

jobhistory-optimizeplan-subplan-20170222-0524pm-croppedup

Explanation

All of twenty-seven minutes later, I and we are good.

 

Aireforge Studio

Background

Noticed performance differences on a database job that runs across a few servers.

And, so what to do, but identify differences across the servers.

 

SQL Server Data Tools (SSDT ) & Oracle Data Modeler

I like both SQL Server Data Tools and Oracle Data Model, but really wanted something lite.

As for SSTS, I wanted something lightweight, no install.

And, as for Oracle Data Modeler, I did not want to have to download JDBC Drivers and also something that at least theoretically generate Schema Upgrade Scripts for databases other than Oracle.

 

Googled

As always Googled for free tools.

There were are a lot of hits and struggled to pay attention to fine details as to which products are actually free and the limitations; that is does it only work for 40 tables, etc.

 

AireForge Studio

Finally, found Aireforge Studio.

It seems I got lucky as it does more that I had in mind; as it compares at the Instance & Database configuration levels, as well.

Most other tools compare individual database objects.

 

Download

Aireforge is downloadable from here.

Installed

Installed it.

 

Usage

Configured

Here is out screen once we have configured  the Instances, we are targeting.

configureallservers-croppedup

Compare

Here we choose the SQL Instances, we want to compare.

compare_20170220_0447am

 

Compare Results

Instance Properties

instanceproperties_20170220_0406am-croppedup

Explanation
  1. Here, a big Gotcha
    • On the Test box, we are still running RTM.
    • Whereas on the other boxes, we are running SP2

 

Startup Properties

startupparameters_20170220_0441am-croppedup

Explanation
  1. On other environments besides D (Dev), we installed against Drive E:
    • As install targeted drive E:, the master database file are on E:
    • The full filename for the master database is used during SQL Server Startup or shall we say bootstrap

 

Job Step Configuration

jobsteps_20170220_0417am-croppedup

Explanation
  1. Unlike other compare result visuals, we are only showing three SQL Instances above
    • The reason being that we took the Test Instance down to upgrade RTM to the latest Service Pack ( SP2 )
  2. The lone Job Step identified is “IndexOptimize

 

Blog

AireForge’s original name is OmniCompare.

Here are some other links:

  1. Blog

SQL Server – Network Connectivity – Error – Connection Failed – SQLState – 08001 // SQL Server Error – 17

 

Ticket

Received a very descriptive ticket this morning.  It read

Short Description: 2 computers are not able to access the SQL Server

Ticket Summary: There are 2 computers that are trying to access the SQL Server – OGDBvm
IP addresses of computers are: 10.0.4.24, 10.0.4.23.

When trying to connect to the SQL server, they receive the error – attached.

If a SQL user “maple,” uses the odbc connection from computer 10.0.4.22 – it can connect successfully.

If the SQL user “maple,” tries to connect from the computers 10.0.4.24 and 10.0.4.23 – they receive and error – see attached.

We have had the network team verify that there is nothing blocking the connection. Can you check the server to see if connections are allowed from these systems.

This issue originally started happening on 1/27/2017 – ticket. INC0891787.

This is a critical application for the department and currently they can only access it from one computer.

TroubleShooting

Client Computer

ODBC & Netstat

On client computer, accessed ODBC Control Panel applet and tried connecting to Data Source.

On another window on same machine, launched DOS Command Shell and issued netstat -an | find “SYN_SENT”.

Immediately confirmed that we are seeing noticed SYN_SENT on port 1433 targeting DB Server.

 

 

DB Server

Query Analyzer ( ISQLW)

It is a SQL Server 2000 Server and so issued isqlw and even had problems connecting locally.

Image

tcpipsockets_20170217_1214pm

Textual

Server: Msg 17, Level 16, State 1, Line 0

[Microsoft] [ODBC SQL Server Driver] [TCP/IP Sockets] SQL Server does not exist or access denied.

Server: Msg 10061, Level 16, State 1, Line 0

[Microsoft] [ODBC SQL Server Driver] [TCP/IP Sockets] ConnectionOpen (Connect()).

 

Task Manager

Using Task manager noticed High Memory Consumption by SQL Server Process.

Stop DB Server hoping it is just a runway DB Engine, but no not that easy.

 

SQL Server – Error Log

Reviewed SQL Server Error Log

Image

sqlserverlogs__20170112_2118

Image – Listening On

sqlserverlogs__listeningon_20170112_2118

Textual

SQL server listening on Shared Memory, Named Pipes.

 

Explanation

Confirmed that we listening on Shared Memory and Named Pipes.

But, I do really need to be listening on TCP/IP.

 

SQL Server Network Configuration

Launch SQL Server Network Configuration and confirmed that TCP is enabled and it is listening on SQL Server Default TCP Port of 1433.

TCP/IP – Port – 1433

tcpip-port-1433-cropped-up

TCP/IP – Port – 50000

Temporarily changed to another port, 50000, that I know is not being used

tcpip-port-50000-cropped-up

 

SQL Server Restarted

Restarted SQL Server so that the new TCP/IP can be effectual, but still not listening on TCP.

Confirmed by issuing “netstat -anb“.  Noticed our SQL Server Port is not one of the Listening Ports.

Code

netstat -anb

 

Networking

When bac to Control Panel and accessed Networking and TCP/IP Protocol.

 

tcpipfiltering_20170217_1248pm

Ensured that TCP/IP Filtering is not enabled.

Resolution

Took to the net to determine what can cause SQL Server not to listen on its assigned TCP-Port.

Now thinking back to yester years, or is Wonder years, before Blaster Worm.

And, all the havoc that occurred that Friday night.

Validate Current Version

Connect to box insisting on np and ran “select @@version“.

Image

version_20170217_1210pm-croppedup

Textual

Microsoft SQL Server 2000 – 8.00.194 ( Intel X86)

SQL Server 2000 Version Matrix

Here is the Version Matrix for SQL Server 2000

versioning_v2000

Explanation

Mapping Our Version # ( 8.00.194 ) to the matrix confirms that we are running SQL Server 200 Release To Manufacturing ( RTM).

 

Download SQL Server 2000 SP4

The fix of course is to download Microsoft SQL Server 2000 SP4.

Applied Patched

Applied patched.

Restarted Box

Restarted box.

 

Validation

SQL Server Error Log

Launched SQL Server Enterprise Manager and reviewed error log

sqlserverlogs__20170117_0307pm

SQL Server Error Log – Listening On

Image

listeningon

Textual

SQL server listening on TCP, Shared Memory, Named Pipes.

 

Dedication

Dedicated to the Network Firewall Team

Our Network Firewall guy says that even he were to run monitoring all day, the problem is still occurring at the Application Layer.

He continued that he is seeing traffic originate from the client workstation, but the Server is rejecting the request right away.

And, that the server is not even listening in on the port we are targeting.

The moral of the story is that when a Network Engineer is offering you a bet, don’t take it.  Go do your homework.

Sql Server Management Studio ( SSMS ) – Query Results – Max Column Length

Background

Ran a query that gives me the most expensive queries.

As I try to capture the SQL Column I am finding out that the results is chopped off.

 

TroubleShooting

SQL Server Management Studio

Query Results

Access the Options Menu and transversed to Query Results \ SQL Server \ Results to Grid

resultstogrid

And, determine the max Non XML data is 65535 Characters.

 

What is Column Length?

Changed the query to emit the column length…

statementlength

Explanation

  1. Our most expensive query is 147,020 characters
  2. Some other queries are 16,736 and another is 11,133

 

StackOverflow

Googled for a fix and as always found one via Stackoverflow.

Link

Copy Current Cell

stackoverflow_question-11897950

Visualize

stackoverflow_question-11897950_visualize

 

SSMSBoost

Journeyed to SSMS Boost web site here

Available Binaries

Downloaded version Specific

download

Download & Install

Downloaded and Installed SSMS Boost

 

License

Retrieve User/machine Has Token

Retrieved User/Machine Token through running SSMS and accessing menu items SSMSBoost / About License.

ssmsboost-aboutlicense-croppedup

 

Request Community License

Requested Community License by going here.

requestingfreelicense-cropped-up

Here is the Free License Request Form that comes up.

requestlicense-cropped

 

Received License

Received license over email

 

Applied License

 

Usage

Ran the query again.

Grid

copycurrentcell1to1-cropped-up

 

Select Grid Cell

Selected the Grid Cell and right clicked on our selection.

Chose “Copy current cell 1:1” from the dropdown menu.

We were able to capture our cell’s content.

 

Editor

NotePad++

Was able to paste into Notepad++.

Contents

notepadplusplus-summary

Summary

In Notepad++, accessed the menu item View \ Summary.

Here is what our summary window looks like:

notepadplusplus-summary-20170216-0604pm

Explanation:

We have about over 43 thousand characters.

 

Dedicated

Dedicated to SSMSBoost’s directory Andrei Rantsevich.

 

Summary

From the Query’s contents:

(@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 bit,@p__linq__3 uniqueidentifier,@p__linq__6 bit,@p__linq__4 uniqueidentifier,@p__linq__5 bit)SELECT
[Project40].[Id] AS [Id],
[Project40].[C1] AS [C1],
[Project40].[Id2] AS [Id1],
[Project40].[Id5] AS [Id2],
[Project40].[Id1] AS [Id3]

we can see that the query is not handwritten, but generated via Microsoft .Net Linq.

SQL Server Agent – Log File Viewer – Extraneous Entries – Statistics Time

Introduction

On our pre-launch call this morning, one of our product managers confirmed that she received an alert that our Data WareHouse data feed failed overnight.

 

TroubleShooting

SQL Server Management Studio

Launched SQL Server Management Studio and accessed the Job’s History by following the steps below:

  1. Access the Object Explorer
  2. Choose the SQL Server Instance
  3. Access the node SQL Server Agent\Jobs\[Job-Name]
  4. Select the job and from the drop-down menu, choose View History

 

Image

objectexplorer-croppedup

 

Log File Viewer

Contents

Here is the “Log File Viewer” contents of our failing job.

Image

sqlserveragent-jobs-build-logfileviewer-20170216-0905am-cropped

 

 

Textual

 


(Message 3612)  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 2 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 671 ms,  elapsed time = 2319 ms. [SQLSTATE 01000] (Message 3612)  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 2 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 641 ms,  elapsed time = 1002 ms. [SQLSTATE 01000] (Message 3612)  SQL Server parse and compile time:     CPU time = 3 ms, elapsed time = 3 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 422 ms,  elapsed time = 612 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 0 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 2 ms. [SQLSTATE 01000] (Message 3612)  SQL Server parse and compile time:     CPU time = 194 ms, elapsed time = 194 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 13266 ms,  elapsed time = 11558 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server parse and compile time:     CPU time = 2719 ms, elapsed time = 2906 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 939 ms,  elapsed time = 272 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 11 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 1140 ms,  elapsed time = 1136 ms. [SQLSTATE 01000] (Message 3612)  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 0 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 1 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 1 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 3 ms. [SQLSTATE 01000] (Message 3612)  SQL Server parse and compile time:     CPU time = 17 ms, elapsed time = 17 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 171 ms,  elapsed time = 181 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 32 ms,  elapsed time = 32 ms. [SQLSTATE 01000] (Message 3612)  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 0 ms. [SQLSTATE 01000] (Message 3613)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 4 ms. [SQLSTATE 01000] (Message 3612)  SQL Server Execution Times:    CPU time = 0 ms,  elapse...  The step failed.

 

Find Source

Checked Job’s Configuration

Checked Job’s and individual step configuration, but was not able to find any configurable item that specifically asked for statistics to be included.

 

Check SQL Modules

Next checked SQL Modules.

And, please note that this needs to be undertaken on each targeted database.

 

Code


select 

		  [database]
			= db_name()

		, [name]
			= 
				  quotename(object_schema_name(tblSSM.[object_id]))
				+ '.'
				+ quotename(object_name(tblSSM.[object_id]))

		, [type]
			= tblSO.[type_desc]

		, [definition]
			= tblSSM.[definition]

from   sys.sql_modules tblSSM

inner join sys.objects tblSO

	on tblSSM.[object_id] = tblSO.[object_id]

where  
		(

			( tblSSM.[definition] like '%statistics%' )

		)




 

Image

sqlservermodules

 

Review Module’s Code

Issue sp_helptext against the identified module.

Code


exec sp_helptext '[build].[CourseBuild]'

Output

setstatisticstimeon_found_02

Explanation

  1. Found reference to “Set Statistics Time On” in the identified Stored Procedure.

 

Addendum

Addendum -2017-Feb-16th

Commented out “set statistics time on“.

And, re-ran the failing SQL Server Job.

Output

Here is a more succinct job output.

sqlserveragent-jobs-build-logfileviewer-20170216-1042am-cropped-up

Textual


Duration	00:16:36
Sql Severity	16
Sql Message ID	3621

Executed as user: NT SERVICE\SQLSERVERAGENT. ...eration. [SQLSTATE 01003] (Message 8153)

Explanation

  1. SQL Message ID
    • 3621

Review Error Message ID




select 

		  [messageID] = tblSM.message_id
		, [languageID] = tblSM.language_id
		, [severityID] = tblSM.[severity]
		, [isEventLogged] = tblSM.[is_event_logged]
		, [text] = tblSM.[text]

from   sys.messages tblSM

where  (

			-- target Specific Message ID
			(
				tblSM.[message_id] in 
					( 
						  3621
						, 8153
					)
			)

			-- target Specific Language ID
			and
			(
				tblSM.[language_id] in 
					( 
						1033
					)
			)

	   )	

 

Output

sys_messages_20170216

Summary

As always got sidetracked.

Did not find the actual problem, but found out why the problem remains tucked away.