April 24, 2012 at 8:01 am
I have a table on a server that I want to centrally write to. DDL is below:
CREATE TABLE [dbo].[DBSchemaChangeMonitor](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[Captured] [datetime2](7) NOT NULL,
[Server] [nchar](256) NOT NULL,
[DBName] [nchar](256) NULL,
[Command] [nchar](50) NOT NULL,
[Application] [nchar](500) NULL,
[spid] [int] NOT NULL,
[Object] [nchar](500) NULL,
[Login] [nchar](256) NULL,
[ClientProcessID] [int] NULL,
[WorkStation] [nchar](256) NULL,
[InsertedOn] [date] NULL,
CONSTRAINT [PK_DBSchemaChangeMonitor] PRIMARY KEY CLUSTERED
(
[RecID] ASC,
[Captured] ASC,
[Server] ASC,
[Command] ASC,
[spid] ASC
)
I have a SQL Agent job that executes a procedure that reads through the default MSSQL trace files and logs DDL operations into this central table (obviously there are linked servers between the boxes, with appropriate permissions).
INSERT INTO [SERVER].F1Settings.dbo.DBSchemaChangeMonitor
SELECT
start_time,
server_name,
database_name,
ddl_operation,
'[' + CAST(object_type as varchar (6)) + '] ' + application_name,
spid,
obj_name,
login_name,
clipid,
host, GETDATE()
FROM @temp_trace
WHERE --object_type not in (21587) -->> This is Table Statistics (not needed)
obj_name NOT IN ('abc')
ORDER BY
server_name, start_time DESC
When running the code it fails because it clearly doesn't recognize the identity column on the central server table - why?
Msg 213, Level 16, State 1, Procedure dba_TrackSchemaChanges, Line 110
Column name or number of supplied values does not match table definition.
Is there some check across DB servers that I'm missing?
NOTE :: When i run this on the server locally (the central server) I do not get this error.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 24, 2012 at 8:05 am
Not completely sure... do you have identity insert on? Have you tried qualifying the insert statement and see if that helps?
Jared
CE - Microsoft
April 24, 2012 at 8:10 am
You need to specify the columns of the table you are inserting the data into. Although your first colum,n is an identity column, it is being counted as one of the columns for the insert.
April 24, 2012 at 8:13 am
Lynn Pettis (4/24/2012)
You need to specify the columns of the table you are inserting the data into. Although your first colum,n is an identity column, it is being counted as one of the columns for the insert.
+1 Oh yeah... The column names still need to be qualified with an identity insert, just don't qualify the identity. Its these things I forget when I am not developing anymore...
Jared
CE - Microsoft
April 24, 2012 at 8:14 am
Also, it is good practice to explicitly declare the columns your are inserting into. Ran into this in a project at a previous employer. we changed the datatype of a column, moving it from its originally defined location to the end of the column list (added new column with new data type, coverted data from old column to new column, dropped old column, renamed new column to old column name). It broke the app, as an insert expected the column that had been changed to be in the 6th position during an insert. Had the column names been explicitly declared in the insert, this would not have mattered.
April 24, 2012 at 8:15 am
I knew I should have completely finished my first cup of coffee for the morning before posting this 😀
Issue resolved (walking away with tail between my legs in shame...)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 24, 2012 at 8:18 am
MyDoggieJessie (4/24/2012)
I knew I should have completely finished my first cup of coffee for the morning before posting this 😀Issue resolved (walking away with tail between my legs in shame...)
No shame, just a refresher 😉
Jared
CE - Microsoft
April 24, 2012 at 8:23 am
SQLKnowItAll (4/24/2012)
MyDoggieJessie (4/24/2012)
I knew I should have completely finished my first cup of coffee for the morning before posting this 😀Issue resolved (walking away with tail between my legs in shame...)
No shame, just a refresher 😉
No problem. I have been there before myself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply