Strange CREATE PROC error...

[nwm76]

Gurus,

I've got a rather garbled script that is giving me a major headache. Basically, I have an Excel sheet with multiple worksheets where the 1st worksheet - called "Contents" -contains a single column containing a list of all the remaining worksheet names that contain the data.

I want to cycle through this Excel file and load the contents of each data worksheet into a separate table in SQL Server 7. I've tried writing the sproc below to do this, but it keeps producing the following error when I try and actually create it which is caused by the line of code in bold:

"Invalid column name 'ID'".

For starters, I dont understand why the CREATE PROC statement should fall over because of an invalid column name cos I thought it used deferred name resolution and therefore doesnt care if objects exist or not at the time the sproc is created. As it is, this column definitely should exist, yet it still falls over.

Any ideas anyone??

Thanks,
Neil

----------------------

CREATE PROC cp_ExcelTDImport @xlsfile varchar(75)
AS

DECLARE @cmd varchar(100)
SELECT @cmd = 'copy "' + @xlsfile + '" "J:\Data\Neil\test.xls"'
EXEC master..xp_cmdshell @cmd


IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ContList')
DROP TABLE ContList

SELECT OperatorNames AS OperatorNames
INTO ContList
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;IMEX=1;Database=J:\Data\Neil\test.xls'
,'SELECT OperatorNames FROM [Contents$]')

ALTER TABLE ContList ADD ID int IDENTITY(2,1)


DECLARE @opcount int
SELECT @opcount = COUNT(*) from ContList
DECLARE @counter int
SELECT @counter = 1

DECLARE @opname varchar (30)
DECLARE @sql varchar(500)

--Create table for each operator containing raw TD data
WHILE @counter <= @opcount
BEGIN

SELECT @opname = OperatorNames FROM OpList WHERE ID = (@counter + 1)

SELECT @sql = 'SELECT * INTO '
+ '[' + 't_' + @opname + ']' +
+ ' FROM OPENROWSET(' + "'" + 'Microsoft.Jet.OLEDB.4.0' + "'" + ',' + "'"
+ 'Excel 8.0;IMEX=1;Database=' + @xlsfile + "'"
+ ', ' + "'" + 'SELECT * FROM [' + @opname + '$]' + "'" + ')'
EXEC (@sql)

SELECT @counter = @counter + 1
END

 

[nwm76]

Oops! Bold line should read:

SELECT @opname = OperatorNames FROM ContList WHERE ID = (@counter + 1)

Error still occurs as originally described.

 

[JamesLean]

Try creating the contlist table like this:

SELECT IDENTITY(2, 1) AS id, OperatorNames
INTO ContList
FROM OPENROWSET ...

--James

 

[nwm76]

Thanks, but afraid that doesnt work. Apparently you can't add an identity column using SELECT INTO.

Any more suggestions anybody?

Cheers
Neil

 

[vongrunt]

IDENTITY() used in SELECT... INTO singleton statement is actually a function call. It takes 3 arguments - datatype, seed and increment.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[nwm76]

That nailed it....thanks a million.