Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part:

ALTER PROCEDURE [dbo].[TBL_GET_COLUMNS](@TBL1 varchar(255),  @BADCOLLIST varchar(255), @OUT_COL_LIST varchar(4096) OUTPUT) as 
BEGIN
DECLARE  @COL_LIST varchar(4096)
DECLARE @COL_NAME varchar(255)
DECLARE @SEP varchar(5)
DECLARE @TBL1_ID int
DECLARE @SKIP int
select @TBL1_ID =  OBJECT_ID(@TBL1)

select @COL_LIST = ''
select @SEP = ''

/* select   @TBL1_ID, @TBL2_ID */


DECLARE Tbl_Cols Cursor For
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBL1  and COLUMN_NAME not  in (@BADCOLLIST) 

Open Tbl_Cols 
FETCH  Tbl_Cols INTO @COL_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @SKIP =  CHARINDEX(@COL_NAME, @BADCOLLIST)
 if @SKIP = 0 
 BEGIN
  select @COL_LIST = @COL_LIST + @SEP + @COL_NAME
  select @SEP = ','
END
 FETCH  Tbl_Cols INTO @COL_NAME
END

Close Tbl_Cols
Deallocate Tbl_Cols

/* select @COL_LIST
select  len(@COL_LIST) */
select @OUT_COL_LIST = @COL_LIST
END

Now on our Testing Database, everything runs just fine. In our Production Database, one of the rows doesn't get copied over to the new table. The schemas are identical (as far as I can tell). The only thing that I can tell to spot the difference between Prod and Test is the results of this query:

Given this SQL Query:

SELECT COL_NAME(969262708, ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Pac_Parcels'

I'm getting these results on Production:

**SNIP**
ContractId
ContractSuffix
TaxLineSeq    
SysNote
Leinholder
NULL
AssessorName
SitusAddress1
SitusAddress2
SitusCity
SitusState
SitusZip
**SNIP**

And these results on our Test DB:

**SNIP**
ContractId
ContractSuffix
TaxLineSeq    
SysNote
Leinholder
PropertyLostDate
AssessorName
SitusAddress1
SitusAddress2
SitusCity
SitusState
SitusZip
**SNIP**

What's the deal with that NULL value? PropertyLostDate is the column that isn't getting copied over. We've got a work-around in place, but I wanna know what's goin' on. Any ideas on where I should look?

UPDATE:

When I look at sys.columns I get this on Production:

SELECT OBJECT_ID, name, column_id from sys.columns where name = 'PropertyLostDate' and object_id = 969262708
969262708   PropertyLostDate    90

And this on Testing:

SELECT OBJECT_ID, name, column_id, * from sys.columns where name = 'PropertyLostDate' and object_id = 492177149
492177149   PropertyLostDate    89

The OBJECT_ID is different, but that's okay because it's a differnt table. BUT the column_id is off by 1.

When I do:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pac_parcels'

The ORDINAL_POSITION of the offending column (PropertyLostDate) is 89 in both Production and Testing

share|improve this question

migrated from stackoverflow.com May 3 '13 at 6:10

This question came from our site for professional and enthusiast programmers.

1  
sqlfiddle.com is your/our friend –  mattedgod May 2 '13 at 20:21
1  
We can still be friends. :) –  MayorAwesome May 2 '13 at 20:33
6  
Could this be permissions related? BOL states that COL_NAME can return NULL if the caller doesn't have permission to view the object. Are any column-level permissions in place? Is the query running under an equivalent security context in test and production? –  Ed Harper May 2 '13 at 20:49
2  
Also I'd test to see if the same bad information comes back from sys.columns. –  Aaron Bertrand May 2 '13 at 20:50
1  
You can also check to see if INFORMATION_SCHEMA.COLUMNS is reporting the same ordinal position in both places (for the same column name of course). It's possible the column was added in different ways in the two environments (or dropped and re-added). Compare those rows to sys.columns.column_id... –  Aaron Bertrand May 2 '13 at 20:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.