January 16, 2014 at 2:55 am
We run following TSQL on SQL 2012 Standard server and receive the error mentioned below. However the same code works without issues on other SQL 2012 Servers apart from this one 🙁 -
declare @Server_Name SYSNAME
, @Start_Time DATETIME
, @End_Time DATETIME
set @Server_Name = @@SERVERNAME
IF @Start_Time IS NULL
SELECT @Start_Time = GETDATE()-1
IF @End_Time IS NULL
SELECT @End_Time = GETDATE()
DECLARE @cmd NVARCHAR(MAX)
SELECT @cmd = '
DECLARE @a NVARCHAR(MAX);
SELECT @a = ''SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM tempdb.sys.objects WHERE name = ''''TempSQLErrorLog'''' AND type = ''''U'''')
BEGIN
CREATE TABLE tempdb.dbo.TempSQLErrorLog (
[LogDate] DATETIME,
[ProcessInfo] VARCHAR(50),
[Text] VARCHAR(1000) )
END
ELSE
BEGIN
TRUNCATE TABLE tempdb.dbo.TempSQLErrorLog;
END
DECLARE @no SMALLINT
SELECT @no = 0
WHILE (@no < 7)
BEGIN
INSERT INTO tempdb.dbo.TempSQLErrorLog
EXEC master.SYS.XP_READERRORLOG @no, 1, null, null , '
+ '''''' + CONVERT(VARCHAR(19),@start_time,120) + '''''' + ', '
+ '''''' + CONVERT(VARCHAR(19),@end_time,120) + '''''' + '
SELECT @no = @no + 1
END
''
EXEC master.dbo.sp_executesql @statement = @a -- execute command stored in @a on linked server
'
-- PRINT @cmd -- for debug purposes
EXEC (@cmd) -- exec the nested exec statement
SELECT @Server_Name as Instance_name
, MIN(Logdate) as Log_date
, MIN(Processinfo) as SPID_no
, Text
, COUNT(*) as no_of_times
FROM tempdb.dbo.TempSQLErrorLog
GROUP BY [Text]
----------------
Error returned below -
Msg 0, Level 11, Line 0 - A severe error occurred on the current command. The results, if any should be discarded.
On checking the Event log and SQL error log we found there are no errors reported and no dump files are created either. What we found is that it fails on the following TSQL as the results are stored in the table.
SELECT @Server_Name as Instance_name
, MIN(Logdate) as Log_date
, MIN(Processinfo) as SPID_no
, Text
, COUNT(*) as no_of_times
FROM tempdb.dbo.TempSQLErrorLog
GROUP BY [Text]
However the above if run seperately works fine. Does anyone have had similar issues or knows how to troubleshoot and resolve this issue?
Version of all SQL 2012 Servers are 11:0:3000.
Any help welcome. Thanking you in anticipation.
Kailash.
January 16, 2014 at 7:21 am
If the same code doesn't work, then I'd start checking settings. First, default ANSI settings on the server. Collation on the server and the database. Also check collation on tempdb. Not sure after that, at least nothing else is coming to mind immediately.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 16, 2014 at 7:35 am
Thank you for your advice Grant. I will check the settings as suggested and get back shortly. It is really strange that I get no errors logged in any of the logs too.
Regards,
Kailash.
January 16, 2014 at 7:53 am
Just checked ANSI settings and Server/database/Tempdb collation is the same as the other server where this query works. 🙁
January 16, 2014 at 8:05 am
There must be differences. No such thing as magic. Differing data sets leading to an error, maybe corruption on that database, something. Keep looking and look in and around settings and versions, because that's the explanation any time we're looking at a single query that works in one place and not another when the versions of SQL Server are the same.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 22, 2014 at 7:33 am
As wierd as it sounds this issue was resolved by restarting the SQL Server.
Maybe just restarting the SQL services could do the trick too.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply