June 2, 2010 at 4:57 pm
We recently upgraded from SQL Server 2005 to SQL Sever 2008. I have a script that loads data from one server into StagingTables on a linked server (Cyrodiil). In SS2005 the scripts ran fine twice a day. Now when I run the script I get the following severe error messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The linked server is set up and working correctly. The following script reads data currently on the linked server in the specified database and table, so I know the connection is correct.
select *
from [cyrodiil].[stagingtables].[dbo].[CCU_SCd_01b_ADM2_Historical_Counts_YTD_SHT] scd
where count_dte = (select max(count_dte) from [cyrodiil].[stagingtables].[dbo].[CCU_SCd_01b_ADM2_Historical_Counts_YTD_SHT])
However, the following insert script is throwing the severe error. When I comment out these lines, the select statement that pulls the source data runs fine. As soon as I uncomment these two lines, I get the severe error.
INSERT INTO [cyrodiil].[Stagingtables].[dbo].[CCU_SCd_01b_ADM2_Historical_Counts_YTD_SHT]
([Year], Term, Division, Stage, [Count], Count_Dte, [User_Name])
When I mouse over the 4-part table reference in the INSERT or SELECT statements, it says invalid object name and everything is in red. The same is true for the quoted Year, Count, and User_Name columns (invalid column).
What do I need to do to get the linked server to be recognized as a valid source?
Paul Pennington
June 3, 2010 at 2:02 am
Please post the full INSERT statement that prompts the error, and the CREATE TABLE statements for the tables involved.
There have been a number of bugs around linked servers, many of which depend on specific query plan shapes or column types.
If you are able to produce an estimated query plan (without the 'severe error' message) please right-click that, save it as a *.sqlplan plan file and upload it as an attachment to your reply.
If you are not particularly interested in the cause, or suggestions for syntax workarounds, just update your installation to at least SP1. If we can identify the exact cause, we should be able to identify which Cumulative Update is needed to fix the problem. Of course, it might just be that the query needs tweaking for 2008. Not everything that worked (especially 'unusual' constructions) in 2005 will work unmodified in 2008.
Many times, the 'severe error' message results from SSMS not handling a server-reported error correctly. You may get a better error message by tracing both servers involved, or even running the same query from SQLCMD or OSQL. It's true.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 3, 2010 at 8:11 am
Paul,
Thank you for taking a look at this. It may have been a QUOTED_IDENTIFIER issue. Late in the day, I found out that SET QUOTED_IDENTIFIER was turned off for some reason. I turned it on and refreshed yesterday, but it did not make a difference. When I logged in this morning to get the information you requested, it immediately ran and loaded the data into StagingTables on the linked server.
In the interest of narrowing down what happened for the future, here is the information you asked for.
"Please post the full INSERT statement that prompts the error, and the CREATE TABLE statements for the tables involved."
1. Full INSERT statement (SELECT statement runs when 2 INSERT lines are commented out
/**********************************************************
**
**Admissions Stages Historical Totals and Current To Date
**
**J. Paul Pennington
**
**February 7, 2008
**
**
**Last edited 12/05/2008
***********************************************************/
use tmseprd
Declare @COUNT_DTE datetime, @StartYr char(4), @EndYr char(4)
SET @COUNT_DTE = convert(char(20),getdate(),100)
SET @StartYr = '2005'
SET @EndYr = '2011'
INSERT INTO [cyrodiil].[Stagingtables].[dbo].[CCU_SCd_01b_ADM2_Historical_Counts_YTD_SHT]
([Year], Term, Division, Stage, [Count], Count_Dte, [User_Name])
-- Prospect Stage - all 3 divisions
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'1-Prospect' as Stage,
count(distinct sht.id_num) as [Count],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('02', '102', '202', '302', '40')
and sht.div_cde in ('cl', 'cp', 'sm')
and sht.trm_cde in ('10', '20', '40')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
-- Leads
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'2-Lead' as Stage,
count(distinct sht.id_num) as [Count],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('03', '103', '203', '303')
and sht.div_cde in ('cl', 'cp', 'sm')
and sht.trm_cde in ('10', '20', '40')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
-- Application Form Submitted
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'3-Applied-Form' as Stage,
count(distinct sht.id_num) as [COUNT],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('04', '104', '204', '304')
and sht.div_cde in ('cl', 'cp', 'sm')
and sht.trm_cde in ('10', '20', '40')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
-- Full Application Received
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'4-Applied-Full' as Stage,
count(distinct sht.id_num) as [COUNT],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('11', '12', '13', '14', '111', '112', '113', '114', '211', '212', '213', '214', '311', '312', '313', '314')
and sht.div_cde in ('cl', 'cp', 'sm')
and sht.trm_cde in ('10', '20', '40')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
-- Accepted Less Declined
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'5-Accepted' as Stage,
sum(case
when sht.hist_stage in ('111', '112', '113') then 1
when sht.hist_stage = '115' then -1
else 0
end) as [COUNT],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('111', '112', '113', '115')
and sht.div_cde in ('cl')
and sht.trm_cde in ('10', '20')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'5-Accepted' as Stage,
sum(case
when sht.hist_stage in ('211', '212', '213') then 1
when sht.hist_stage = '215' then -1
else 0
end) as [COUNT],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('211', '212', '213', '215')
and sht.div_cde in ('cp')
and sht.trm_cde in ('10', '20', '40')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'5-Accepted' as Stage,
sum(case
when sht.hist_stage in ('311', '312', '313') then 1
when sht.hist_stage = '315' then -1
else 0
end) as [COUNT],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('311', '312', '313', '315')
and sht.div_cde in ('sm')
and sht.trm_cde in ('10', '20')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
-- Enrolled Less Withdrawn
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'6-Enrolled' as Stage,
sum(case
when sht.hist_stage in ('118', '119', '120') then 1
when sht.hist_stage = '122' then -1
else 0
end) as [COUNT],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('118', '119', '120', '122')
and sht.div_cde in ('cl')
and sht.trm_cde in ('10', '20')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'6-Enrolled' as Stage,
sum(case
when sht.hist_stage in ('218', '219', '220') then 1
when sht.hist_stage = '222' then -1
else 0
end) as [COUNT],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('218', '219', '220', '222')
and sht.div_cde in ('cp')
and sht.trm_cde in ('10', '20', '40')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
union
select sht.yr_cde as [YEAR], sht.trm_cde as TERM, sht.div_cde as DIVISION,
'6-Enrolled' as Stage,
sum(case
when sht.hist_stage in ('318', '319', '320') then 1
when sht.hist_stage = '322' then -1
else 0
end) as [COUNT],
@COUNT_DTE as COUNT_DTE, 'PPENNINGTON' as [USER_NAME]
from stage_history_tran sht
where sht.yr_cde >= @StartYr and sht.yr_cde < @EndYr
and sht.hist_stage in ('318', '319', '320', '322')
and sht.div_cde in ('sm')
and sht.trm_cde in ('10', '20')
and add_to_count_dup = 'Y'
group by sht.div_cde, sht.yr_cde, sht.trm_cde
order by sht.div_cde, sht.yr_cde, sht.trm_cde, stage
/*
-- Get Loaded Data [This SELECT statement runs fine, indicating that the linked server is working][/color]
BEGIN
select *
from [cyrodiil].[stagingtables].[dbo].[CCU_SCd_01b_ADM2_Historical_Counts_YTD_SHT] scd
where count_dte = (select max(count_dte) from [cyrodiil].[stagingtables].[dbo].[CCU_SCd_01b_ADM2_Historical_Counts_YTD_SHT])
END
*/
2. StagingTables (on linked server Cyrodiil) - Table Creation Script
USE [master]
GO
/****** Object: Database [StagingTables] Script Date: 06/03/2010 09:40:45 ******/
CREATE DATABASE [StagingTables] ON PRIMARY
( NAME = N'Staging Tables', FILENAME = N'F:\Data\Staging Tables.mdf' , SIZE = 359424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Staging Tables_log', FILENAME = N'E:\Logs\Staging Tables_log.ldf' , SIZE = 172992KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [StagingTables] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [StagingTables].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [StagingTables] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [StagingTables] SET ANSI_NULLS OFF
GO
ALTER DATABASE [StagingTables] SET ANSI_PADDING OFF
GO
ALTER DATABASE [StagingTables] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [StagingTables] SET ARITHABORT OFF
GO
ALTER DATABASE [StagingTables] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [StagingTables] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [StagingTables] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [StagingTables] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [StagingTables] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [StagingTables] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [StagingTables] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [StagingTables] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [StagingTables] SET QUOTED_IDENTIFIER ON
GO
ALTER DATABASE [StagingTables] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [StagingTables] SET DISABLE_BROKER
GO
ALTER DATABASE [StagingTables] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [StagingTables] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [StagingTables] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [StagingTables] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [StagingTables] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [StagingTables] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [StagingTables] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [StagingTables] SET READ_WRITE
GO
ALTER DATABASE [StagingTables] SET RECOVERY FULL
GO
ALTER DATABASE [StagingTables] SET MULTI_USER
GO
ALTER DATABASE [StagingTables] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [StagingTables] SET DB_CHAINING OFF
GO
EXEC [StagingTables].sys.sp_addextendedproperty @name=N'Description', @value=N'Staging Tables Database'
GO
June 3, 2010 at 8:32 am
Paul,
Sorry. It still is an issue after all. I thought my script was logged in to the source server, but was instead logged in to Cyrodiil (destination). I have an older copy of the tmseprd database on that server for testing purposes. So the script ran assuming I wanted to pull the data from there. When I reconnected the script to Jicsdata (the source server) I again received the severe error messages, so it is not resolved.
Paul Pennington
June 4, 2010 at 6:01 am
I have found a workaround for the "severe error" that I received from the previously presented script. The INSERT statement and SELECT statements had the columns in a different order than the table. So I wrapped the UNION statements as a subquery with a final SELECT statement that had the columns in the same order as the destination table. I then reordered the INSERT statement to also reflect the table order. Now the script loads the data as it should, with no errors.
So could the columns being out of order in the INSERT statement create the error situation?
Paul Pennington
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply