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

migration rejected from stackoverflow.com Jun 3 at 12:52

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Max Vernon, Paul White, dezso, RolandoMySQLDBA, RLF Jun 3 at 12:52

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Paul White, dezso, RolandoMySQLDBA, RLF
If this question can be reworded to fit the rules in the help center, please edit the question.

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
2  
Sounds like the column was dropped and re-added, and INFORMATION_SCHEMA wasn't kept in the loop (I've blogged about reasons to avoid these views; you've just given me another one). Are you sure that column name doesn't appear in the output just in a different position? Also do you need them to return in the same order in both environments? Anyway I would switch to sys.columns/column_id for more accurate results - about the ordering, you might not be able to rely on that... –  Aaron Bertrand May 2 '13 at 21:32