Long running SQL Agent job

We tried to launch an .exe from SQL Server Agent Job and job keeps on running, it need to be stopped manually.

Steps to troubleshoot
#1. Any program that has windows interaction i.e. opens any type of window e.g. Notepad, ms-paint or excel etc.

#2. So it simply means we can only use console programs and that too without any prompts or wait for input.
Therefore, we can't execute Date or Time (DOS internal command) as they expect input.

Execute the .exe given in job step from command prompt to validate there are no wait for input.

Hope it helps

Performance Improvement

Update Statistics: 

Let's look at following query.

SELECT *
FROM customer
WHERE city = 'Pune'
 AND phone = '2020666666' -- dummy Number 

Above query contain two fields in the "WHERE" clause and suppose there are two indexes defined, each containing one field. One very important notion to remember is that the optimizer can only use ONE index per table.

Therefore, it has to make a decision as to which index to use. Ideally, optimizer should pick index created on phone but in absence of updated statistics it can pick cityIndex.

This decision can be informed one if statistics are up to date.


Avoid foreign key constraints

Foreign keys constraints ensure data integrity at the cost of performance. Therefore, if performance is your primary goal you can push the data integrity rules to your application layer. A good example of a database design that avoids foreign key constraints is the System tables in most databases. Every major RDBMS has a set of tables known as system tables. These tables contain meta data information about user databases. Although there are relationships among these tables, there is no foreign key relationship. This is because the client, in this case the database itself, enforces these rules.

Find Range of continous values

There are many ways to find out gaps & range, but this standard gaps-and-island solution that I found on net is coolest one. Basic idea of this solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

The value could be int or date.

DECLARE @data TABLE (i INT)

INSERT INTO @data
VALUES (1)
 ,(2)
 ,(4)
 ,(5)
 ,(10)
 ,(11)
 ,(15)
 ,(16)
 ,(17)
 ,(18)
 ,(19)
 ,(20)
 ,(21);

WITH tab
AS (
 SELECT i d
  ,ROW_NUMBER() OVER (
   ORDER BY i
   ) r
 FROM @data
 )
SELECT min(d)
 ,max(d)
FROM tab
GROUP BY d - r

Output: 

Hope it helps

Easy way to split string

We generally use function to split a delimited string into rows. The code for same is listed below

CREATE FUNCTION
 [dbo].[fnSplitString] (
 @string NVARCHAR(MAX)
 ,@delimiter CHAR(1)
 )
RETURNS@output  
TABLE (splitdata NVARCHAR(MAX))

BEGIN
 DECLARE @start INT
  ,@end INT

 SELECT @start = 1
  ,@end = CHARINDEX(@delimiter, @string)

 WHILE @start < LEN(@string) + 1
 BEGIN
  IF @end = 0
   SET @end = LEN(@string) + 1

  INSERT INTO @output (splitdata)
  VALUES (SUBSTRING(@string, @start, @end - @start))

  SET @start = @end + 1
  SET @end = CHARINDEX(@delimiter, @string, @start)
 END

 RETURN
END GO

We will be using XQuery method to split the string in easy and efficient way.
Following steps are taken to split the string.

#1. Convert the string into formatted XML, 
for that replace the delimitor with any tag (used in example).
#2. Remove empty nodes like '' or ''
#3. list value of all nodes 


USE tempdb

DECLARE @x VARCHAR(MAX)
DECLARE @x1 XML
DECLARE @t1 DATETIME
DECLARE @t2 DATETIME

SET @x = '100|101|102|'
SET @x = replicate(@x, 1)

SELECT @x1 = cast
(replace
(replace
('<x>' + replace
(@x, '|', '</x><x>')
 + '</x>', '<x ></x>', '')
, '<x></x>', '')
 AS XML)


SET @t1 = getdate() --  start time of process

SELECT X.x1.value('.', 'varchar(max)')
FROM @x1.nodes('//x') X(x1) -- XQuery

SET @t2 = getdate() --  End time of process

SELECT datediff(ms, @t1, @t2) -- Find time difference in milliseconds
SET @t1 = getdate() --  start time of process

SELECT *
FROM [dbo].[fnSplitString](@x, '|')

SET @t2 = getdate() --  End time of process

SELECT datediff(ms, @t1, @t2)

Benchmarks for XQuery method to split the string
 -- XQuery
 -- 0ms for 3
 -- 0ms for 30
 -- 6ms for 300
 -- 36ms for 3000
 -- 350ms for 30000
 -- 7s for 300000

Benchmarks for Procedure method to split the string
 -- 3ms for 3
 -- 10ms for 30
 -- 100ms for 300
 -- Procedure 1m for 3000
 -- Procedure 11s for 30000
 -- Procedure 2m15s for 300000

Conclusion: Looking at performance and simplicity of XQuery, I will recommend it over procedural way. Hope it helps.

Easy Way to remove HTML tags in SQL Server

We were looking for a way to remove/strip HTML tags from varchar columns. On making search found some code samples, which suggested to create a stored procedure like following.

Click to view Stored Procedure StripHtml

CREATE FUNCTION [dbo].[StripHtml] (@HTMLText VARCHAR (MAX))
RETURNS VARCHAR (MAX)
AS
BEGIN
DECLARE @Start  int
DECLARE @End    int
DECLARE @Length int

-- Replace the HTML entity & with the '&' character (this needs to be done first, as
-- '&' might be double encoded as '&')
SET @Start = CHARINDEX ('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX ('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity < with the '<' character
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '<')
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity > with the '>' character
SET @Start = CHARINDEX ('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '>')
SET @Start = CHARINDEX ('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity & with the '&' character
SET @Start = CHARINDEX ('&amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX ('&amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity   with the ' ' character
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, ' ')
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Remove anything between tags
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = CHARINDEX ('>', @HTMLText, CHARINDEX ('<', @HTMLText))
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = CHARINDEX ('>', @HTMLText, CHARINDEX ('<', @HTMLText))
SET @Length = (@End - @Start) + 1
END

RETURN LTRIM( RTRIM(@HTMLText))

END

GO

This stored procedure does what it says, but it is lot of code and procedural. So, thought of using XML data type and value method of SQL Server as embedded below.

NB: HTML text must be properly formatted otherwise we will get XML Parsing error.

Hope it Helps.

0x8004271A ODSOLE Extended Procedure Error in srv_convert.

We were trying to make a hosted web service call and retrieve the response. Following code segment was working fine for smaller responses.

set nocount on
declare @objWinHttp int
declare @strLine varchar(8000)
declare @hr int

exec @hr =sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objWinHttp out
print @hr
EXEC sp_OAMethod @objWinHttp, 'Open',NULL,'GET','http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT',false
EXEC sp_OAMethod @objWinHttp, 'Send',NULL
--- Problematic call
exec @hr =sp_OAGetProperty @objWinHttp, 'ResponseText',@strLine OUtPUT
---
print @hr
print @strLine
exec sp_OADestroy @objWinHttp

but when response text is larger than 4000 bytes it failed with following error
-2147211494

OLE Automation Error Information
  HRESULT: 0x8004271a
  Source: ODSOLE Extended Procedure
  Description: Error in srv_convert.
Reason:
Length of response > 4000.
Microsoft has description about the issue at http://support.microsoft.com/kb/325492

Solution:
As a workaround we created a temporary table with nvarchar column having size as max. We inserted the output of procedure to the table

--- Changed the call : removed @strLine OUtPUT
Create table #tmp(dt nvarchar(max))
insert into #tmp
exec @hr =sp_OAGetProperty @objWinHttp, 'ResponseText' --,@strLine OUtPUT
 Select dt from #tmp -- single column/single row.
Drop Table #tmp -- clean up
---

It worked for us. Hope it help others.

SQL Agent + Excel issue

We have a SSIS package which is suppose to execute macro. This SSIS package works fine when it is executed from the development environment but fails with following message when package is scheduled as job.


System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'XYZ.xls'. There are several possible reasons:

The file name or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook.

Solution/Workaround:
it is a workaround for a bug in Windows:

1. Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)
2. Set Full control permissions for directory Desktop (for user "SYSTEM")

Please do this for both 32 and 64 bit folder.

Hope it helps
 

provider: TCP Provider, error:

Error:
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

General Explanation:
This error will show if a connection is drawn from the connection pool and the connection to the server has been lost.
There is no way for a connection in the pool to know that the connection has been severed.

Reason:
There are a few common reasons for this.
1 The server has been restarted, this will close the existing connections.
2 Someone or something has killed the SPID that is being used. Connection drop or Deadlock where a victim is chosen could be one of the reason.

Probale Solutions:
#1 Connection pooling (Not recommended)
Because of reason 1, we tried turning off connection pooling at application level even that did not help.

#2 TCP Chimney Offload
This may also be caused by other, non SQL Server related, reasons. 
I have for example seen issues where the TCP Chimney Offload feature on the server machine is turned ON, causing this. Short info about this feature;
When TCP Chimney Offload is enabled and the NIC implements what is called the TCP Offload Engine, then some of the TCP processing is handed over to the hardware, i.e. the NIC.
By doing this, the CPU is offloaded, and since TCP could require a lot of processing this would mean that the CPU will be allowed to perform other tasks.

We tried by disabling it on server but it did not help.

#3. Deadlock
We checked using profiler for deadlock events, but there were none in trace.
 
#4 Setting Static IP address on NIC
It helped us in solving the issue.



Hope it helps.

Myth : NOLOCK as silver bullet for performance

It is general practice to recommend NOLOCK to improve performance of query, though it result in dirty reads. But if dirty reads are fine in context of a code segment NOLOCK is recommended most of time. While looking for Error 605 explanation and remedy stumbled upon this on microsoft site.

Error 601

Severity Level 12

Message Text

Could not continue scan with NOLOCK due to data movement.

Explanation

When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft SQL Server is not able to continue the scan.

Action

This error aborts the query. Either resubmit the query or remove the NOLOCK locking hint.

Database 'XYZ' is Already Open and Can Only Have One User at a Time

Database 'XYZ' is Already Open and Can Only Have One User at a Time

Reason:
The reason you are receiving this error is that your DB is set to single user mode.

Solution:
#1 To correct this error go into Enterprise Manager and right click on the DB 'XYZ' and go to Properties, than go to the options tab and uncheck the Single User check box.

#2 If we wish to from a separate connection

a) find who has what connections

exec sp_who

b) Disconnect the SPID which is using database 'XYZ'

kill 53

c) set database back to multiuser

alter database XYZ set multi_user