December 6, 2011 at 12:35 pm
I just altered my table as follows:
ALTER TABLE dbo.EpisodePackage
ADD
WebTagBroadViewId varchar(50) NULL
, FormatSheetBroadViewId varchar(50) NULL
, OnAirOfferBroadViewId varchar(50) NULL
, FundingBedBroadViewId varchar(50) NULL
then I alterd my Stored procedure:
Update dbo.EpisodePackage
Set WebTagBroadViewId = '1'
where id = 586
I get an error: on the line for:
Set WebTagBroadViewId = '1'
Msg 207, Level 16, State 1 Invalid column name 'WebTagBroadViewId'.
How can I resolve it?
December 6, 2011 at 12:41 pm
Did you run the ALTER TABLE. Did it succeed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2011 at 12:41 pm
Did you commit the alter table statement ?
What's the @@opentran count for that session ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 6, 2011 at 12:50 pm
The Alter Table statement succedded well.
When I expand the table columns, I already see the added columns. Is there anything ellse I need to do for the stored procedure to see the cloumns?
December 6, 2011 at 4:32 pm
Faye Fouladi (12/6/2011)
The Alter Table statement succedded well.When I expand the table columns, I already see the added columns. Is there anything ellse I need to do for the stored procedure to see the cloumns?
There is nothing else you need to do. Check that you are not running the alter procedure statement on a different database. For example, run your code as one entire script:
ALTER TABLE dbo.EpisodePackage
ADD
WebTagBroadViewId VARCHAR(50) NULL
, FormatSheetBroadViewId VARCHAR(50) NULL
, OnAirOfferBroadViewId VARCHAR(50) NULL
, FundingBedBroadViewId VARCHAR(50) NULL
GO
ALTER PROCEDURE dbo.UpdatePackage
AS
UPDATE dbo.EpisodePackage
SET WebTagBroadViewId = '1'
WHERE id = 586
GO
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 6, 2011 at 7:20 pm
I have something like this in the stored proc:
-- table variable for the BroadView data transformed into PackagingForms format
DECLARE @BroadViewData TABLE
(
EpisodeId INT,
EpisodePackageFormatId INT,
EpisodePackageNumber VARCHAR(50),
WebTagBroadViewId VARCHAR(50),
FormatSheetBroadViewId VARCHAR(50),
OnAirOfferBroadViewId VARCHAR(50),
FundingBedBroadViewId VARCHAR(50),
FlattenedInd CHAR(1),
BroadViewId INT,
BroadViewHashCode VARBINARY(MAX),
RowId INT IDENTITY,
UNIQUE CLUSTERED (BroadViewId, RowId)
)
The data for updating EpisodePackage table and updating those new fields is coming from this table variable. I also added the fields to the table variable. Do you think I should do something different becuase there is table variable involved here?
December 6, 2011 at 7:51 pm
Is there trigger associated with this table?
"Often speak with code not with word,
A simple solution for a simple question"
December 7, 2011 at 7:37 am
Faye Fouladi (12/6/2011)
I have something like this in the stored proc:...
The data for updating EpisodePackage table and updating those new fields is coming from this table variable. I also added the fields to the table variable. Do you think I should do something different becuase there is table variable involved here?
The only thing I can think of is maybe your table aliases are mixed up in the update statement. Post your whole proc.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 7, 2011 at 7:46 am
Faye Fouladi (12/6/2011)
I have something like this in the stored proc
Have you tried recompiling the stored procedure?
exec sp_recompile 'your_proc_name_goes_here';
exec sp_refreshsqlmodule 'your_proc_name_goes_here';
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 7, 2011 at 7:53 am
One thing important to note: GO is not a SQL Server command, but a tool of SQL Management studio.
When you run that script directly, SQL MS executes it batches separated by the 'GO'. Therefore the new columns are committed/posted before update is ran.
GO does not work in a stored procedure. When the procedure is compiled, it simply does not recognize the alter table.
I have inherited a script with a similiar problem. I welcome a better solution than I have.
My terrible temporary workaround is to create the table outside the stored procedure so that it contains all the columns it will eventually contain inside the stored procedure. Then the stored procedure will compile.
If the table exists when the procedure is compiled it will work. Then my stored procedure will:
1. drops the table if it exists;
2. creates the new table with one field Junk(200)
3. Perform bulk insert into my table
4. alters my table and adds columns used to more precisely split up the data from the bulk insert
5. Performs updates and other commands
In my case using the #temp session tables improve the situation. The script can also be rewritten to extract data from the imported table to the correct columns or copy to a second table.
December 7, 2011 at 8:45 am
As far as I can tell, the OP is not trying to alter a table within a proc. OP has run the alter table and is now trying to alter a proc which is returning the column not found error. We need to see the proc.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 7, 2011 at 11:13 am
No triggers associated with this stored procedure.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply