INVERT vs UPDATE

Hi
why does an UPDATE triggers itself at the same time as the INSERT trigger?
thanksHow about a bit more information here - at least the code for the triggers.
TheSQLGuru
President
Indicium Resources, Inc.
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:S8dXh.43563$q97.244799@.wagner.videotron.net...
> Hi
> why does an UPDATE triggers itself at the same time as the INSERT trigger?
> thanks
>sql

INVERT vs UPDATE

Hi
why does an UPDATE triggers itself at the same time as the INSERT trigger?
thanksHow about a bit more information here - at least the code for the triggers.
TheSQLGuru
President
Indicium Resources, Inc.
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:S8dXh.43563$q97.244799@.wagner.videotron.net...
> Hi
> why does an UPDATE triggers itself at the same time as the INSERT trigger?
> thanks
>

INVERT vs UPDATE

Hi
why does an UPDATE triggers itself at the same time as the INSERT trigger?
thanks
How about a bit more information here - at least the code for the triggers.
TheSQLGuru
President
Indicium Resources, Inc.
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:S8dXh.43563$q97.244799@.wagner.videotron.net.. .
> Hi
> why does an UPDATE triggers itself at the same time as the INSERT trigger?
> thanks
>

Inventory update problem

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

Quote:

Originally Posted by Kliot

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

I think it is not possible in SQL Server. But it will work in MS Access.

You have to fetch record and then update it|||hi

i have gone through ur query, but if possible just send me 1 or two records of each table and tell me exactily what u want|||Here is an example,

Invoice table

Code|||Here is an example,

Invoice table

Code Quantity
DM01 2
LG02 2
DM01 3
QP76 1

The update query will update the Inventory table quantity for DM01 by 2 not 5, the second DM01 is not updated

I can get around this by doing a sum query inside the select but it's not ideal.

UPDATE Inventory.Inventory
set RQty = Inventory.Inventory.RQty - od.Quantity
FROM (SELECT Code, SUM(Quantity) AS Quantity FROM Retail.OrderDetails WHERE Invoice = 207022101
GROUP BY Code) as od WHERE(Inventory.inventory.code = od.code)

Invalide object name 'INSERTED'

In an after insert/update trigger I have the following:
IF UPDATE(CustName)
BEGIN
SET @.iCustID = (SELECT CustID FROM INSERTED)
..
END
It compiles but when I run it, I get a message: "Invalide object name
'INSERTED'"
But if I take the SET out of the IF like this, it works fine:
SET @.iCustID = (SELECT CustID FROM INSERTED)
IF UPDATE(CustName)
BEGIN
..
END
Can someone explain why?
Thanks,
KeithSorry. My mistake. It doesn't work either way. What does work is if I do
this (I mean it runs without errors):
SELECT CustID FROM INSERTED
IF UPDATE(CustName)
BEGIN
..
END
I need to get CustID into a variable so that I can pass it to a stored
procedure as follows:
Keith
IF UPDATE(CustName)
BEGIN
SET @.iCustID = (SELECT CustID FROM INSERTED)
EXEC @.bSomeVar = spTest @.iCustID
..
END|||Strange, never had that. I would have suggested that the problem was case
sensitivity (BOL lists the table as 'inserted', not 'INSERTED') but you say
it works when you move the SET out of the IF block
Even if this worked, you would have a problem anyway if more than 1 row was
updated in one go, because you'd be trying to set a numbers of rows to a
scalar variable.
Dan
Keith wrote on Wed, 26 Apr 2006 11:33:59 -0400:

> In an after insert/update trigger I have the following:
> IF UPDATE(CustName)
> BEGIN
> SET @.iCustID = (SELECT CustID FROM INSERTED)
> ...
> END
> It compiles but when I run it, I get a message: "Invalide object name
> 'INSERTED'"
> But if I take the SET out of the IF like this, it works fine:
> SET @.iCustID = (SELECT CustID FROM INSERTED)
> IF UPDATE(CustName)
> BEGIN
> ...
> END
> Can someone explain why?
> Thanks,
> Keith
>|||Geeze. Never mind. Not enough sleep last night. I moved some code from the
trigger to a stored procedure and didnt' change "INSERTED" to the actual
table name in the stored procedure. The error was there, not in the trigger.
Keithsql

Invalid Udate SQL statement DOES NOT cause error... Does anyone know why?

Here's my update statement:

UPDATE Item1
SET reviewloop = 1, currentreviewstate=5
WHERE itemid in
(SELECT itemid FROM Item2)

The thing is: the table Item2 DOES NOT HAVE a field called itemid.
So, I should receive an error, right? Not so.Instead, every single
record in Item1 was updated.

Does anyone know why SQL Serverr does not trown an error?

Thanks guys,

-Silvio SouzaBecause the sub query can reference fields from the update. itemid in this
case will be retrieved from Item1.|||The rule for subqueries is that a column name that can't be resolved to
column within the subquery is assumed to reference a column in the outer
query. If in doubt, use the two-part column name including the table
name/alias.

--
David Portas
SQL Server MVP
--|||"no spam" <chuck@.sheckmedia.com> wrote in message news:<vCWhc.71068$Lh2.5553@.bignews1.bellsouth.net>...
> Because the sub query can reference fields from the update. itemid in this
> case will be retrieved from Item1.

I don't think so. SQL certainly doesn't say to itself "Since I can't
find that value in Item2 I'll assume that they must mean the value in
Item1" - that would be catastrophic.

I've just tried this myself, and whilst it didn't give any error, it
didn't update any rows in Item1 either. This makes sense, because
the subquery is simply evaluating to FALSE, so 0 rows are updated in
the main query.|||> I don't think so. SQL certainly doesn't say to itself "Since I can't
> find that value in Item2 I'll assume that they must mean the value in
> Item1" - that would be catastrophic.

The problem isn't to do with *values* it's to do with resolution of *column
names*. Substitute the word "column" for "value" and your statement
describes exactly what SQL does.

Assuming the column Itemid doesn't exist in Item2, the UPDATE statement you
posted is equivalent to:

UPDATE Item1
SET reviewloop = 1, currentreviewstate=5
WHERE Item1.itemid IN
(SELECT Item1.itemid FROM Item2)

As long as there is at least one row in Item2, every row in Item1 should get
updated.

--
David Portas
SQL Server MVP
--|||Any field reference in a sub query will always look for the field internally
first and if not found it will look in the outer query. The reason for this
behaviour is that the sub query can use values from the outer query as
selection criteria, in case statements etc.

This is not a bug, it is by design. By always using table qualifiers in all
sql it will never cause a problem even if the developer mistypes a field
name.
Sloppy SQL (without proper table qualifiers etc) may behave funny as in the
example provided by the OP.

Also, if you look at the execution plan for this and similar queries it will
be more clear why. The optimizer usually turn sub queries like this into
joins.

Invalid syntax near nvarchar

Im trying to update and modify data in a grid view in my sql data base. I keep getting this error..What is the solution to this. Sorry, I have no programing experience. All help will be greatly appreciated. thank you
jdslim

Incorrect syntax near 'nvarchar'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244
 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915
 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179
 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140
 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +835
 System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +162
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
 System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +118
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +107
 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +175
 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +244
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840

It would help if you posted your code.

|||

Here is the new stack trace and the code that goes with it. I started a new page that just contains the gridview and the sql data source.

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +392 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +638 System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +71 System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +933 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1152 System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +191 System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +172 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919

<%

@.PageLanguage="VB" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div> <asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [Trials] WHERE [Trial#] = @.original_column1 AND [Wet Date] = @.original_Wet_Date AND [Check Date] = @.original_Check_Date AND [Comments] = @.original_Comments"InsertCommand="INSERT INTO [Trials] ([Trial#], [Wet Date], [Check Date], [Comments]) VALUES (@.column1, @.Wet_Date, @.Check_Date, @.Comments)"OldValuesParameterFormatString="original_{0}"SelectCommand="SELECT * FROM [Trials]"UpdateCommand="UPDATE [Trials] SET [Wet Date] = @.Wet_Date, [Check Date] = @.Check_Date, [Comments] = @.Comments WHERE [Trial#] = @.original_column1 AND [Wet Date] = @.original_Wet_Date AND [Check Date] = @.original_Check_Date AND [Comments] = @.original_Comments"><DeleteParameters><asp:ParameterName="original_column1"Type="String"/><asp:ParameterName="original_Wet_Date"Type="DateTime"/><asp:ParameterName="original_Check_Date"Type="DateTime"/><asp:ParameterName="original_Comments"Type="String"/></DeleteParameters><UpdateParameters><asp:ParameterName="Wet_Date"Type="DateTime"/><asp:ParameterName="Check_Date"Type="DateTime"/><asp:ParameterName="Comments"Type="String"/><asp:ParameterName="original_column1"Type="String"/><asp:ParameterName="original_Wet_Date"Type="DateTime"/><asp:ParameterName="original_Check_Date"Type="DateTime"/><asp:ParameterName="original_Comments"Type="String"/></UpdateParameters><InsertParameters><asp:ParameterName="column1"Type="String"/><asp:ParameterName="Wet_Date"Type="DateTime"/><asp:ParameterName="Check_Date"Type="DateTime"/><asp:ParameterName="Comments"Type="String"/></InsertParameters></asp:SqlDataSource></div><asp:GridViewID="GridView1"runat="server"AllowSorting="True"AutoGenerateColumns="False"DataKeyNames="Trial#"DataSourceID="SqlDataSource1"Style="z-index: 100; left: 538px; position: absolute; top: 391px"><Columns><asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/><asp:BoundFieldDataField="Trial#"HeaderText="Trial#"ReadOnly="True"SortExpression="Trial#"/><asp:BoundFieldDataField="Wet Date"HeaderText="Wet Date"SortExpression="Wet Date"/><asp:BoundFieldDataField="Check Date"HeaderText="Check Date"SortExpression="Check Date"/><asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/></Columns></asp:GridView></form>

</

body>

</

html>|||

Hi

I just want to check if you ever solved the problem? I'm sitting with the same thing, but nothing will work...

I've tried the suggestions made, but it still doesn't work.

|||

Hi

I just want to check if you ever solved the problem? I'm sitting with the same thing, but nothing will work...

I've tried the suggestions made, but it still doesn't work.

|||you are better off starting a new thread and post your codes there

Invalid object name 'dbo.MSreplication_queue'.

I am attemption to update a record in a SQL 2005 table that is trans.
replicated with updates and I am receiving the following message...
Msg 208, Level 16, State 1, Procedure sp_MSsendtosqlqueue, Line 40
Invalid object name 'dbo.MSreplication_queue'.
Any ideas?
AHIA,
Larry...
Does the table dbo.MSreplication_queue exist in the subscribing database ?
In updatable scription scenerio, SQL Server fires ins/upd/del triggers which
will insert information into that table when a DML command is executed on the
replicated tables. So this table is one of the critical tables for the queue
replication to work.
I can think of only 2 reasons why you might be running into this error :
1. The table dbo.MSreplication_queue has been deleted. To see if the table
was dropped, take a look at the default trace report for the database "Schema
Changes Histroy" in the Management Studio.
or
2. The snapshot did not get applied to the subscriber successfully. Check
the distribution agents histroy to see if there has been any failures.
"LPR-3rd" wrote:

> I am attemption to update a record in a SQL 2005 table that is trans.
> replicated with updates and I am receiving the following message...
> Msg 208, Level 16, State 1, Procedure sp_MSsendtosqlqueue, Line 40
> Invalid object name 'dbo.MSreplication_queue'.
>
> Any ideas?
> AHIA,
> Larry...
>
|||What is the compatibility level of this database?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168541651.977581.286190@.i56g2000hsf.googlegr oups.com...
>I am attemption to update a record in a SQL 2005 table that is trans.
> replicated with updates and I am receiving the following message...
> Msg 208, Level 16, State 1, Procedure sp_MSsendtosqlqueue, Line 40
> Invalid object name 'dbo.MSreplication_queue'.
>
> Any ideas?
> AHIA,
> Larry...
>

Invalid object name 'ctsv_F9F22D34FACE4A1BA4D2B061ADBEE5C1'

Hi,
I've a replication in a SQL Server 2000 to syncronize with a SQL CE 2.0.
When I try to update a row, I get this error: Invalid object name
'ctsv_F9F22D34FACE4A1BA4D2B061ADBEE5C1'
I get this error if I try to modify a row from enterprise manager too!
Can you help me please?
Thanks!
your merge replication metadata is out of sync. Script out your publication,
drop your publication, and then recreate it from script.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andrea Grandi" <andrea@.nospam.com> wrote in message
news:uytKvf$TFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've a replication in a SQL Server 2000 to syncronize with a SQL CE 2.0.
> When I try to update a row, I get this error: Invalid object name
> 'ctsv_F9F22D34FACE4A1BA4D2B061ADBEE5C1'
> I get this error if I try to modify a row from enterprise manager too!
> Can you help me please?
> Thanks!
|||Hi,

> your merge replication metadata is out of sync. Script out your publication,
> drop your publication, and then recreate it from script.
wich script? Can I drop/create from Enterprise Manager?
Anyway, now I've another problem... I exported only the 4 tables I was
interested in to another instance. I dropped the whole database and
re-imported the 4 tables. Now I cannot write on those tables
I tried to remove the rowguid column but I get this error "rowguid
column is not valid". I know it's not valid, I removed it!
Is there a way to fix my database?
Thanks!
|||Most probably some artefacts are left on you tables, such as merge replication triggers. They try to write to replication views, but views are not existing..
If triggers are not there, then we should think about something else..
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Andrea Grandi" <andrea@.nospam.com> wrote in message news:%23EqCibBUFHA.3076@.TK2MSFTNGP12.phx.gbl...
Hi,

> your merge replication metadata is out of sync. Script out your publication,
> drop your publication, and then recreate it from script.
wich script? Can I drop/create from Enterprise Manager?
Anyway, now I've another problem... I exported only the 4 tables I was
interested in to another instance. I dropped the whole database and
re-imported the 4 tables. Now I cannot write on those tables
I tried to remove the rowguid column but I get this error "rowguid
column is not valid". I know it's not valid, I removed it!
Is there a way to fix my database?
Thanks!

Invalid Object Name ctsv_####...

DB Gurus,

I have a VB 6 utility that is using ADO to connect to replicated tables and I get the following error when I try to update the recordset:

Invalid Object Name 'ctsv_18C3929C22...'

This code worked great until the team that owns one of our SQL servers decided to turn of Replication. After getting replication back up and running, we set up a push subscription rather than a pull as before.

The util is running against the subscriber's tables...not the source tables.

What the heck have I gotten into here?

What does ctsv mean?

Thanks,
GregGood question, what the heck is it? Also make sure you fully qualify table names with schema owner. Maybe that's what it's barking at...

Invalid cursor state @ Distribution Agent

Hi! When i create a Transactional Replication between two SQL Server 2000 (with no additional Service Pack or Update) with the Enterprise Manager, i get this error (also when i restart the agent):
Invalid cursor state
(Source: ODBC Driver Manager [ODBC]; Error number: 24000)
I think it the MDAC is not the problem, because i never updated the SQL Server 2000 and there the error is from the ODBC SQL Driver. But here it′s from the Driver Manager.
Please help me!! I really despair!
Thanks
Does this apply to your case:
http://support.microsoft.com/default.aspx?kbid=831997?
HTH,
Paul Ibison

invalid column error when updating a new column that was added by alter table

i am getting an error "Invalid Column name <column name>" message when runni
ng an update statement which updates a new column which was just added by an
alter table statement. If i run the alter statement and update statement se
perately, i don't get any e
rrors and it works. I only get this when they run together in a script or st
ored proc.
Is this a bug and what are the alternatives. Thanks.This is the way that the parser work. As the column doesn't (yet) exists at
parse time, the update will
generate the error. One way around it is to do the update with dynamic SQL.
OTOH, I'd re-consider why you have
to add a column dynamically in the first place (if possible).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jsin" <anonymous@.discussions.microsoft.com> wrote in message
news:6A977D73-49D1-4229-92B1-B90B8E054540@.microsoft.com...
> i am getting an error "Invalid Column name <column name>" message when running an
update statement which
updates a new column which was just added by an alter table statement. If i
run the alter statement and update
statement seperately, i don't get any errors and it works. I only get this w
hen they run together in a script
or stored proc.
> Is this a bug and what are the alternatives. Thanks.

invalid column error when updating a new column that was added by alter table

i am getting an error "Invalid Column name <column name>" message when running an update statement which updates a new column which was just added by an alter table statement. If i run the alter statement and update statement seperately, i don't get any e
rrors and it works. I only get this when they run together in a script or stored proc.
Is this a bug and what are the alternatives. Thanks.
This is the way that the parser work. As the column doesn't (yet) exists at parse time, the update will
generate the error. One way around it is to do the update with dynamic SQL. OTOH, I'd re-consider why you have
to add a column dynamically in the first place (if possible).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jsin" <anonymous@.discussions.microsoft.com> wrote in message
news:6A977D73-49D1-4229-92B1-B90B8E054540@.microsoft.com...
> i am getting an error "Invalid Column name <column name>" message when running an update statement which
updates a new column which was just added by an alter table statement. If i run the alter statement and update
statement seperately, i don't get any errors and it works. I only get this when they run together in a script
or stored proc.
> Is this a bug and what are the alternatives. Thanks.

interview question?

How many tables can be updated with a single update ?I would answer only one
but I'm curious of the exact answer|||even i support one ,but it is correct|||One|||I think one|||zero (where clause finds no rows)

one (duh)

many (updated row cascades to related tables)|||Cascading updates ... hmm ... why didnt I think of that ...

Well .. seems like I lost any chance of getting this job|||What does n mean ... Mr BK|||brett probably means m, not n

m is n + 1

:cool: :cool: :cool: :cool: :cool: :cool: :cool:|||I thought n meant any number...

m to me means many, but infers a relationship as in 1-m

either way...

However, here's a poll type question...how many people use cascade...

I've never...always wanted more control...prefer to delete and insert...

(And keep history)|||Never !!!|||when updates are performed, if you're writing audit records or something, those triggers should fire no matter whether the table being updated is the target table or a related table, right?

oh, and to me, m implies 0 to many, not 1 to many

my answer to the question was "zero, one, or many"

;)|||Originally posted by r937
when updates are performed, if you're writing audit records or something, those triggers should fire no matter whether the table being updated is the target table or a related table, right?

oh, and to me, m implies 0 to many, not 1 to many

my answer to the question was "zero, one, or many"

;)

fair enough...but now we trapse down the logical data modeling path...

things are not always 0-m...could be required to be 1-m...

EDIT: And you didn't answer the cascade question...|||sorry, i assumed my answer to the cascade question would be obvious

i use as much RI (http://evolt.org/RI) as the database supports

unless i'm doing consulting work at a cllient site where there's a DBA with veto on changes or exceptions to his database guidelines...

but that's politics ;)|||That's a great link...love the VW...

And yes, I get the cascade answer...you must be a big IDENTITY kind of guy...

I never thought of cascading as a function of RI though...

but it seems related...

no pun intended|||I use cascading for RI whenever possible. There have been a few times where I have modeled relationships with multiple update paths, and then Cascading fails and I had to resort to triggers.|||Originally posted by Brett Kaiser
That's a great link...love the VW... thanks

actually, when it comes to the natural versus surrogate primary key debate, i tend to favour natural keys, insofar as any candidate key can be called "natural"

however, i have been know to use IDENTITY and its cousins AUTONUMBER, AUTO_INCREMENT, and SEQUENCE from time to time

one of the best articles on the subject, long but very worth the time to read, is Key Points About Surrogate Keys (http://www.rationalcommerce.com/resources/surrogates.htm)|||i think guid is coming up ;)|||You know...|||why I outta...Moe, Larry build a database...

interview question

when does sql use update lock?>>> is the salary worth it ?

see BOL for that answer

Internet Explorer 7 Release Candidate 1

After downloading and installing this update I find that java script doesn't work (e.g. I cannot now access news videos). I have checked in Tools and find that Scripting is enabled.

Can anyone assist me please as I rely on the Internet for news, not having a TV?

This forum is for SQL Server Reporting Services, and in general the MSDN forums target developer related questions, but you could try the Internet Explorer forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=923&SiteID=1

You may also want to enable "Display a notification about every script error" on the Advanced tab of Tools->Options and provide the exact script error in your post.