Alter Table - Add IDENTITY Col - Not Working in SP

[JayKusch]

Afternoon and Hmmmm...

I have an SP that loads a temp table (#TmpTable) with and INSERT INTO ... SELECT ...

After the table is loaded I want to Alter #TmpTable and ADD an Indentity Column as such ...

ALTER TABLE #TmpTable
ADD ID_Key [Int] IDENTITY (1, 1) NOT NULL

Problem is ... The table never gets altered, ie column is not added, and reference to the column later in the SP fails because it does not exist.

After it fails I can select on the temp table and see all of the columns defined EXCEPT the ID that is created w/ the Alter table command.

In Query Analyzer, I can highlight the alter table command, fire it off, select the table and SEE the field now defined at the end of the temp table.

I can even add a GO right after the Alter Table statement in the SP BUT ... if you try to save it w/ the GO in this position, all of the code below the GO for the SP is dropped as expected.

WHAT AM I MISSING and how do I correct this. Not having much luck w/ turning the IDENTITY_INSERT on and off either.

Thanks ALL ...

Thanks

J. Kusch

 

[ClaireHCM]

if your stored proc is no too complicated and lengthy. can you post it on the forum?

 

[JayKusch]

Give it a try ...


CREATE TABLE #DBs_To_Report (
[DBTR_DB_Name] [VarChar] (400) NULL,
[DBTR_DB_Total_Size] [Int] NULL,
[DBTR_DB_Used_Size] [Int] NULL,
[DBTR_DB_Path] [VarChar] (400) NULL,
[DBTR_TLog_Total_Size] [Int] NULL,
[DBTR_TLog_Used_Size] [Int] NULL,
[DBTR_TLog_Path] [VarChar] (400) NULL,
[DBTR_Backup_Mode] [VarChar] (20) NULL,
[DBTR_Last_Complete_Backup] [DateTime] NULL,
[DBTR_Last_Log_Backup] [DateTime] NULL
) ON [PRIMARY]

INSERT INTO #DBs_To_Report

SELECT DatabaseName, -- as 'Database Name',
TotalSize, -- as 'Datafile Size (MB)',
UsedSize, -- as 'Datafile Used (MB)',
NULL, -- DB Path
LogSize, -- as 'LogFile Size (MB)',
LogUsed, -- as 'LogFile Used (MB)',
NULL, -- TLog Path
BackupMode, -- as 'Backup Mode',
--'Last Complete Backup' =
CASE LastCompleteBackup
WHEN '1900-01-01 00:00:00.000' THEN CreationDate
ELSE LastCompleteBackup
END,
--'Last Log Backup' =
CASE LastLogBackup
WHEN '1900-01-01 00:00:00.000' THEN CreationDate
ELSE LastLogBackup
END
FROM CDMSMasterDatabase..DatabaseTable WITH(NOLOCK)
WHERE DatabaseName NOT IN ('Model', 'MSDB', 'Northwind', 'Pubs', 'TempDb')
ORDER BY ServerName,
DatabaseName

ALTER TABLE #DBs_To_Report
ADD DBTR_Key [Int] IDENTITY (1, 1) NOT NULL

DECLARE @SQL_Command nVarChar(4000)

DECLARE @DB_Name nVarChar(400)
DECLARE @DBFileName nVarChar(400)
DECLARE @TLogFileName nVarChar(400)
DECLARE @DBFileSize nVarChar(400)
DECLARE @TLogFileSize VarChar(400)

DECLARE @DirSizeText nVarChar(400)
DECLARE @DirSizeTextIn nVarChar(400)


DECLARE @Record_Count Int
DECLARE @Record_Max Int

SET @Record_Count = 1
SET @Record_Max = ( Select Count(*) FROM #DBs_To_Report )

TRUNCATE TABLE Monitor_DB_and_TLog_Statistics


-- Start reporting on the DBs

WHILE @Record_Count <= @Record_Max

BEGIN
SET @DB_Name = ( SELECT DBTR_DB_Name
FROM #DBs_To_Report
WHERE DBTR_Key = @Record_Count )
BEGIN
-- Get Data File Name

-- Get Data File Name

SET @SQL_Command = '(SELECT @DBFileName = RTRIM(filename)
FROM ' + RTRIM(LTRIM(@DB_Name)) + '.dbo.SysFiles
WHERE Name = ' + '''' + RTRIM(LTRIM(@DB_Name)) + '_Data' + '''' + ' )'

EXECUTE sp_executesql @SQL_Command,
-- list of variables passed to the sp_executesql
N'@DB_Name VarChar(100),@DBFileName VarChar(600) OUTPUT',
-- variables
@DB_Name,
@DBFileName OUTPUT

-- Get TLog Name

SET @SQL_Command = '(SELECT @TLogFileName = RTRIM(filename)
FROM ' + RTRIM(LTRIM(@DB_Name)) + '.dbo.SysFiles
WHERE Name = ' + '''' + RTRIM(LTRIM(@DB_Name)) + '_Log' + '''' + ' )'

EXECUTE sp_executesql @SQL_Command,
-- list of variables passed to the sp_executesql
N'@DB_Name VarChar(100),@TLogFileName VarChar(600) OUTPUT',
-- variables
@DB_Name,
@TLogFileName OUTPUT

UPDATE #DBs_To_Report

SET [DBTR_DB_Path] = @DBFileName,
[DBTR_TLog_Path] = @TLogFileName
WHERE [DBTR_Key] = @Record_Count

END
SET @Record_Count = @Record_Count + 1
END


Thanks

J. Kusch

 

[ClaireHCM]

Hey Jay,

I was playing for a while, and I always have my bf look into it. And we both couldnt find a way for this. Do you consider it might be a bug in SQL Server?

Let me know if you find the way out

 

[fluteplr]

Why not just define the table with the identity key to begin with? When you do the insert you will have to define the insert fields and exclude the identity but that should not be an big deal.

 

[JayKusch]

ClairHsu ... I'm begining to wonder about that too.

Fluteplr ... Taking the example above, what would be the code to insert the records in the table via a SELECT statement as shown???



Thanks

J. Kusch

 

[ClaireHCM]

CREATE TABLE #DBs_To_Report (
[DBTR_DB_Name] [VarChar] (400) NULL,
[DBTR_DB_Total_Size] [Int] NULL,
[DBTR_DB_Used_Size] [Int] NULL,
[DBTR_DB_Path] [VarChar] (400) NULL,
[DBTR_TLog_Total_Size] [Int] NULL,
[DBTR_TLog_Used_Size] [Int] NULL,
[DBTR_TLog_Path] [VarChar] (400) NULL,
[DBTR_Backup_Mode] [VarChar] (20) NULL,
[DBTR_Last_Complete_Backup] [DateTime] NULL,
[DBTR_Last_Log_Backup] [DateTime] NULL,
DBTR_Key [Int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]


INSERT INTO #DBs_To_Report(
[DBTR_DB_Name],
[DBTR_DB_Total_Size],
[DBTR_DB_Used_Size],
[DBTR_DB_Path],
[DBTR_TLog_Total_Size],
[DBTR_TLog_Used_Size],
[DBTR_TLog_Path],
[DBTR_Backup_Mode],
[DBTR_Last_Complete_Backup],
[DBTR_Last_Log_Backup])

SELECT
DatabaseName, -- as 'Database Name',
TotalSize, -- as 'Datafile Size (MB)',
UsedSize, -- as 'Datafile Used (MB)',
NULL, -- DB Path
LogSize, -- as 'LogFile Size (MB)',
LogUsed, -- as 'LogFile Used (MB)',
NULL, -- TLog Path
BackupMode, -- as 'Backup Mode',
CASE LastCompleteBackup
WHEN '1900-01-01 00:00:00.000' THEN CreationDate
ELSE LastCompleteBackup
END,
CASE LastLogBackup
WHEN '1900-01-01 00:00:00.000' THEN CreationDate
ELSE LastLogBackup
END
FROM CDMSMasterDatabase..DatabaseTable WITH(NOLOCK)
WHERE DatabaseName NOT IN ('Model', 'MSDB', 'Northwind', 'Pubs', 'TempDb')
ORDER BY ServerName,
DatabaseName