Stored procedure, Invalid column name 1

[MdotButler]

I have a stored procedure which executes the following sequence of command.

DROP TABLE wk_file
CREATE TABLE [dbo].[wk_file] ([wk_data] char(1000) NULL)

DECLARE @bulk_insert varchar (500)
SET @bulk_insert='BULK INSERT wk_file FROM "C:\provlist.seq" WITH (TABLOCK)'
EXEC (@bulk_insert)

ALTER TABLE wk_file ADD wk_provid char(11) NULL DEFAULT ' '
ALTER TABLE wk_file ADD wk_lstupd char(08) NULL DEFAULT ' '

The above code is raising the follwing error which I can't seem to get around.

Server: Msg 207, Level 16, State 1, Procedure mxw_provlist, Line 40
Invalid column name 'wk_provid'.

If I cut the alter statements out of the code and execute them in QA with the table already defined it works as expected. Can anyone shed some light on why I am getting the error in the procedure?

TIA
Mark

 

[gmmastros]

I notice that you are creating a dbo.wk_file but altering wk_file (without the owner). I suggest you specify the owner in EVERY occurrance of the table name.

DROP TABLE dbo.wk_file
CREATE TABLE [dbo].[wk_file] ([wk_data] char(1000) NULL)

DECLARE @bulk_insert varchar (500)
SET @bulk_insert='BULK INSERT dbo.wk_file FROM "C:\provlist.seq" WITH (TABLOCK)'
EXEC (@bulk_insert)

ALTER TABLE dbo.wk_file ADD wk_provid char(11) NULL DEFAULT ' '
ALTER TABLE dbo.wk_file ADD wk_lstupd char(08) NULL DEFAULT ' '

This may not be your problem, though.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MdotButler]

Thanx for the response.

I originally had the owner and took it out while trying to figure this out. I did put it back and it made no difference. Something that may come into play is the code that follows (don't laugh).

UPDATE wk_file SET wk_provid = SUBSTRING(wk_file.wk_data,64,11), wk_lstupd = SUBSTRING(wk_file.wk_data,766,8)
CREATE INDEX [wk_provid] ON [dbo].[wk_file]([wk_provid]) ON [PRIMARY]

Even though I drop the table before creating it, am I running into a name conflict with the index maybe?

 

[gmmastros]

What are you using the wk_file table for? Do you want this to be a *temporary* table that is ONLY used for importing data?

The reason I ask is because you can use a temp table to accomplish the same thing.

CREATE TABLE #Temp([wk_data] char(1000) NULL)

BULK INSERT #Temp FROM "C:\provlist.seq" WITH (TABLOCK)

Select top 20 * from #Temp

[green]-- Add code here to parse the fields from the data[/green]

Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Please, let me clarify a little more.

Generally, the process would be...

1. Bulk Insert the data in to a temp table.
2. Since you are currently droping the table, you could change this to Truncate table.
3. Parse the data in to the appropriate fields of the real table

CREATE TABLE #Temp([wk_data] char(1000) NULL)

BULK INSERT #Temp FROM "C:\provlist.seq" WITH (TABLOCK)

Truncate Table wk_file

Insert 
Into   wk_file(wk_provid, wk_data)
Select SUBSTRING(wk_data,64,11), 
       SUBSTRING(wk_data,766,8)
From   #Temp

Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MdotButler]

Thanx George for the suggestion. Can't say I figured out the reason for the original error but I did implement a version usiing a temporary table as you suggested and it worked like a champ.

Bottom line, results are everything.

Thanx again, ya get a star from me...