raise message from Update Trigger from External App

Hello,
I can raise this message from an Update Trigger in Query Analyzer when I
update the recordID field of my table:
RAISERROR ('this is a test message from SubDetail Update 7', 16, 10)
Is it possible to raise this message from an external app? How is this
achieved? Actually, I am sure this is possible because I remember doing it.
I just can't remember what I did because I did not document it.
Thanks,
RichWhat is this "external app"? An application that you write yourself? A TSQL
error (which is what you
raise using RAISERROR) is returned to the client application. The client app
lications is connected
to the database using an API, like ADO.NET. And, sure, you can have your dat
abase application
connect to SQL Server and issue a RAISERROR command, and have that error mes
sage be returned to the
same app, but that sounds a bit ... meaningless. If you give us more informa
tion, we can probably
give some suggestion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:54A80BEF-6285-4F6A-8B60-F06007C1BDEF@.microsoft.com...
> Hello,
> I can raise this message from an Update Trigger in Query Analyzer when I
> update the recordID field of my table:
> RAISERROR ('this is a test message from SubDetail Update 7', 16, 10)
> Is it possible to raise this message from an external app? How is this
> achieved? Actually, I am sure this is possible because I remember doing i
t.
> I just can't remember what I did because I did not document it.
> Thanks,
> Rich|||The external app in this case is an Access ADP. I had to modify a trigger a
few months ago, and I added a raiseerror message at the end to see my result
s
in QA - not error results - just checking what parameter was being used. I
accidentally left the raiseerror message in the trigger, and then I got a
call from an End User stating that this message was coming up all of a sudde
n
when she made updates to the table.
I found the table and reactivated the raiseerror message and I get it when I
updaet a field. The only thing I noticed is that the field I update in this
table (the master table) is not a key field. In the Detail table when I
update the RecordID field this action does not raise the message like in the
Master table. I guess my question is if this is something fundamental that
I
am missing or is it something that I need to dig around to see what is going
on?
"Tibor Karaszi" wrote:

> What is this "external app"? An application that you write yourself? A TSQ
L error (which is what you
> raise using RAISERROR) is returned to the client application. The client a
pplications is connected
> to the database using an API, like ADO.NET. And, sure, you can have your d
atabase application
> connect to SQL Server and issue a RAISERROR command, and have that error m
essage be returned to the
> same app, but that sounds a bit ... meaningless. If you give us more infor
mation, we can probably
> give some suggestion.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:54A80BEF-6285-4F6A-8B60-F06007C1BDEF@.microsoft.com...
>|||Well, I was able to raise that message if I physically update the RecordID -
meaning I go to the live table in the Access ADP which is the same thing tha
t
was going on with the Master table - where the End user was physically
writing to the table through a form. But if I update the table
programmatically from the ADP, then the message does not come up.
While I am at it, I want to alter/replace my raiseerror message. I used the
sp_addmessage sp. Since my message already exists as 50001, I don't want to
add another message. I want to alter this one. But I get an error message
in QA saying that I need to use REPLACE to alter the message for ID 50001.
I
don't know the syntax for this. I have tried variations such as:
USE master
EXEC sp_addmessage 50001, 16,
select replace('This is a test custome message', 'custome', 'custom')
and placing REplace in other locations with no success. Any suggestions how
to do this correctly would be greatly appreciated.
"Rich" wrote:
> The external app in this case is an Access ADP. I had to modify a trigger
a
> few months ago, and I added a raiseerror message at the end to see my resu
lts
> in QA - not error results - just checking what parameter was being used.
I
> accidentally left the raiseerror message in the trigger, and then I got a
> call from an End User stating that this message was coming up all of a sud
den
> when she made updates to the table.
> I found the table and reactivated the raiseerror message and I get it when
I
> updaet a field. The only thing I noticed is that the field I update in th
is
> table (the master table) is not a key field. In the Detail table when I
> update the RecordID field this action does not raise the message like in t
he
> Master table. I guess my question is if this is something fundamental tha
t I
> am missing or is it something that I need to dig around to see what is goi
ng
> on?
> "Tibor Karaszi" wrote:
>|||You more or less lost me on the logic part, but from a technical viewpoint:
If you see the error when executing a statement which will result in the tri
gger being called using
TSQL but not when using ADP, then probably ADP is masking that error and you
should check with an
Access group to see how you can rectify this behavior in Access. Use Profile
r to catch the TSQL
command being submitted from Access just to make sure of what is happening o
n the server level.
Here is how you replace a message text:
EXEC sp_addmessage 50001, 16,
N'Old message text'
GO
EXEC sp_addmessage 50001, 16,
N'NEW message text', @.replace = 'replace'
GO
RAISERROR(50001, -1, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:4B003260-9016-4FE1-B324-F48A24007CD7@.microsoft.com...
> Well, I was able to raise that message if I physically update the RecordID
-
> meaning I go to the live table in the Access ADP which is the same thing t
hat
> was going on with the Master table - where the End user was physically
> writing to the table through a form. But if I update the table
> programmatically from the ADP, then the message does not come up.
> While I am at it, I want to alter/replace my raiseerror message. I used t
he
> sp_addmessage sp. Since my message already exists as 50001, I don't want
to
> add another message. I want to alter this one. But I get an error messag
e
> in QA saying that I need to use REPLACE to alter the message for ID 50001.
I
> don't know the syntax for this. I have tried variations such as:
> USE master
> EXEC sp_addmessage 50001, 16,
> select replace('This is a test custome message', 'custome', 'custom')
> and placing REplace in other locations with no success. Any suggestions h
ow
> to do this correctly would be greatly appreciated.
>
> "Rich" wrote:
>|||Thank you for explaining how to replace a custome message.
"Tibor Karaszi" wrote:

> You more or less lost me on the logic part, but from a technical viewpoint
:
> If you see the error when executing a statement which will result in the t
rigger being called using
> TSQL but not when using ADP, then probably ADP is masking that error and y
ou should check with an
> Access group to see how you can rectify this behavior in Access. Use Profi
ler to catch the TSQL
> command being submitted from Access just to make sure of what is happening
on the server level.
> Here is how you replace a message text:
> EXEC sp_addmessage 50001, 16,
> N'Old message text'
> GO
> EXEC sp_addmessage 50001, 16,
> N'NEW message text', @.replace = 'replace'
> GO
> RAISERROR(50001, -1, 1)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4B003260-9016-4FE1-B324-F48A24007CD7@.microsoft.com...
>

Quoted Identifiers Don't Work with Linked Servers and Update

I believe this is a bug in how update SQL statements are parsed when
executing against a linked server. I execute the following SQL:
update [server].[database].[dbo].[table] set [my column] = 'new value'
Note the space in the column name 'my column'. And I get the following
errors:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'column'.
The syntax of that SQL statement is correct. In fact, I can go to the
linked server and execute it there and it works, like this:
update [table] set [my column] = 'new value'
If I change the schema so the column does not have a space in it, then
execute the following SQL statement from the original server with the server
link, it works:
update [server].[database].[dbo].[table] set [mycolumn] = 'new value'
This to me says that there is a parsing bug because, when performing an
update against a linked server, the quoted identifier for the column is not
respected! Note that this works:
select [my column] from [server].[database].[dbo].[table]
So it appears to be a problem parsing the update statement.
Can anyone shed some light on what is going on? Should I use a support
incident to get this fixed? Or is it something I am doing wrong? Thanks!
-CoreyYou don't say what version of sql server your using so it is hard to say but
there are numerous KB's with related subjects on this. Here is one that
seems to fit.
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
If that is not it then I would take a look at the other hits in the KB that
you can find from here:
http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
and enter "linked server identifier".
Andrew J. Kelly
SQL Server MVP
"Young, Corey" <Corey@.Youngspot.com> wrote in message
news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> I believe this is a bug in how update SQL statements are parsed when
> executing against a linked server. I execute the following SQL:
> update [server].[database].[dbo].[table] set [my column] = 'new value'
> Note the space in the column name 'my column'. And I get the following
> errors:
> Server: Msg 8180, Level 16, State 1, Line 1
> Statement(s) could not be prepared.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'column'.
> The syntax of that SQL statement is correct. In fact, I can go to the
> linked server and execute it there and it works, like this:
> update [table] set [my column] = 'new value'
> If I change the schema so the column does not have a space in it, then
> execute the following SQL statement from the original server with the
server
> link, it works:
> update [server].[database].[dbo].[table] set [mycolumn] = 'new value'
> This to me says that there is a parsing bug because, when performing an
> update against a linked server, the quoted identifier for the column is
not
> respected! Note that this works:
> select [my column] from [server].[database].[dbo].[table]
> So it appears to be a problem parsing the update statement.
> Can anyone shed some light on what is going on? Should I use a support
> incident to get this fixed? Or is it something I am doing wrong? Thanks!
> -Corey
>
>|||Thanks for the response! I'm using SQL Server 2000 with Service Pack 3.
The problem happens:
1. Using Query Analyzer
2. In my code, which uses the native SQL Server .NET data provider
I have seen and read a lot of articles and, while they discuss the problem,
and while Microsoft claims they have fixed it in other situations, they have
not fixed it in mine. I would be interested if anyone could reproduce the
problem, or could give me information that would lead to a solution of the
problem. Otherwise I'll be forced to use an MSDN support incident.
-Corey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You don't say what version of sql server your using so it is hard to say
but
> there are numerous KB's with related subjects on this. Here is one that
> seems to fit.
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> If that is not it then I would take a look at the other hits in the KB
that
> you can find from here:
> http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> and enter "linked server identifier".
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Young, Corey" <Corey@.Youngspot.com> wrote in message
> news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > I believe this is a bug in how update SQL statements are parsed when
> > executing against a linked server. I execute the following SQL:
> >
> > update [server].[database].[dbo].[table] set [my column] = 'new value'
> >
> > Note the space in the column name 'my column'. And I get the following
> > errors:
> >
> > Server: Msg 8180, Level 16, State 1, Line 1
> > Statement(s) could not be prepared.
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'column'.
> >
> > The syntax of that SQL statement is correct. In fact, I can go to the
> > linked server and execute it there and it works, like this:
> >
> > update [table] set [my column] = 'new value'
> >
> > If I change the schema so the column does not have a space in it, then
> > execute the following SQL statement from the original server with the
> server
> > link, it works:
> >
> > update [server].[database].[dbo].[table] set [mycolumn] = 'new value'
> >
> > This to me says that there is a parsing bug because, when performing an
> > update against a linked server, the quoted identifier for the column is
> not
> > respected! Note that this works:
> >
> > select [my column] from [server].[database].[dbo].[table]
> >
> > So it appears to be a problem parsing the update statement.
> >
> > Can anyone shed some light on what is going on? Should I use a support
> > incident to get this fixed? Or is it something I am doing wrong?
Thanks!
> >
> > -Corey
> >
> >
> >
>|||OK, it is best to post that extra info up front so that we don't have to
assume anything. I will post this on the private ng and see if anyone else
can confirm this. By the way does it work if you use double quotes instead
of [] ?
--
Andrew J. Kelly
SQL Server MVP
"Young, Corey" <Corey@.Youngspot.com> wrote in message
news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks for the response! I'm using SQL Server 2000 with Service Pack 3.
> The problem happens:
> 1. Using Query Analyzer
> 2. In my code, which uses the native SQL Server .NET data provider
> I have seen and read a lot of articles and, while they discuss the
problem,
> and while Microsoft claims they have fixed it in other situations, they
have
> not fixed it in mine. I would be interested if anyone could reproduce the
> problem, or could give me information that would lead to a solution of the
> problem. Otherwise I'll be forced to use an MSDN support incident.
> -Corey
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > You don't say what version of sql server your using so it is hard to say
> but
> > there are numerous KB's with related subjects on this. Here is one that
> > seems to fit.
> >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> >
> > If that is not it then I would take a look at the other hits in the KB
> that
> > you can find from here:
> > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> >
> > and enter "linked server identifier".
> >
> >
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > I believe this is a bug in how update SQL statements are parsed when
> > > executing against a linked server. I execute the following SQL:
> > >
> > > update [server].[database].[dbo].[table] set [my column] = 'new value'
> > >
> > > Note the space in the column name 'my column'. And I get the
following
> > > errors:
> > >
> > > Server: Msg 8180, Level 16, State 1, Line 1
> > > Statement(s) could not be prepared.
> > > Server: Msg 170, Level 15, State 1, Line 1
> > > Line 1: Incorrect syntax near 'column'.
> > >
> > > The syntax of that SQL statement is correct. In fact, I can go to the
> > > linked server and execute it there and it works, like this:
> > >
> > > update [table] set [my column] = 'new value'
> > >
> > > If I change the schema so the column does not have a space in it, then
> > > execute the following SQL statement from the original server with the
> > server
> > > link, it works:
> > >
> > > update [server].[database].[dbo].[table] set [mycolumn] = 'new value'
> > >
> > > This to me says that there is a parsing bug because, when performing
an
> > > update against a linked server, the quoted identifier for the column
is
> > not
> > > respected! Note that this works:
> > >
> > > select [my column] from [server].[database].[dbo].[table]
> > >
> > > So it appears to be a problem parsing the update statement.
> > >
> > > Can anyone shed some light on what is going on? Should I use a
support
> > > incident to get this fixed? Or is it something I am doing wrong?
> Thanks!
> > >
> > > -Corey
> > >
> > >
> > >
> >
> >
>|||Thanks!
The behavior is the same using either double-quotes or brackets.
-Corey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ucOjyfBnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> OK, it is best to post that extra info up front so that we don't have to
> assume anything. I will post this on the private ng and see if anyone
else
> can confirm this. By the way does it work if you use double quotes
instead
> of [] ?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Young, Corey" <Corey@.Youngspot.com> wrote in message
> news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Thanks for the response! I'm using SQL Server 2000 with Service Pack 3.
> > The problem happens:
> >
> > 1. Using Query Analyzer
> > 2. In my code, which uses the native SQL Server .NET data provider
> >
> > I have seen and read a lot of articles and, while they discuss the
> problem,
> > and while Microsoft claims they have fixed it in other situations, they
> have
> > not fixed it in mine. I would be interested if anyone could reproduce
the
> > problem, or could give me information that would lead to a solution of
the
> > problem. Otherwise I'll be forced to use an MSDN support incident.
> >
> > -Corey
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > You don't say what version of sql server your using so it is hard to
say
> > but
> > > there are numerous KB's with related subjects on this. Here is one
that
> > > seems to fit.
> > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> > >
> > > If that is not it then I would take a look at the other hits in the KB
> > that
> > > you can find from here:
> > > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> > >
> > > and enter "linked server identifier".
> > >
> > >
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > > I believe this is a bug in how update SQL statements are parsed when
> > > > executing against a linked server. I execute the following SQL:
> > > >
> > > > update [server].[database].[dbo].[table] set [my column] = 'new
value'
> > > >
> > > > Note the space in the column name 'my column'. And I get the
> following
> > > > errors:
> > > >
> > > > Server: Msg 8180, Level 16, State 1, Line 1
> > > > Statement(s) could not be prepared.
> > > > Server: Msg 170, Level 15, State 1, Line 1
> > > > Line 1: Incorrect syntax near 'column'.
> > > >
> > > > The syntax of that SQL statement is correct. In fact, I can go to
the
> > > > linked server and execute it there and it works, like this:
> > > >
> > > > update [table] set [my column] = 'new value'
> > > >
> > > > If I change the schema so the column does not have a space in it,
then
> > > > execute the following SQL statement from the original server with
the
> > > server
> > > > link, it works:
> > > >
> > > > update [server].[database].[dbo].[table] set [mycolumn] = 'new
value'
> > > >
> > > > This to me says that there is a parsing bug because, when performing
> an
> > > > update against a linked server, the quoted identifier for the column
> is
> > > not
> > > > respected! Note that this works:
> > > >
> > > > select [my column] from [server].[database].[dbo].[table]
> > > >
> > > > So it appears to be a problem parsing the update statement.
> > > >
> > > > Can anyone shed some light on what is going on? Should I use a
> support
> > > > incident to get this fixed? Or is it something I am doing wrong?
> > Thanks!
> > > >
> > > > -Corey
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Corey,
Here is a reply I got from another MVP and although I don't like the answer
I guess it makes sense as to what is going on.
> My understanding is that, this is a known limitation(?) of OLE DB provider
> for SQL Server. By default, SQL Server OLE DB provider supports two
> interfaces IRowsetUpdate & IRowsetChange whose properties determine
whether
> the underlying rowset can be updated or not. Delimited identifiers with
> certain characters (~ , - ,! ,{ ,% ,} ,^ ,' ,& ,. ,( ,\ , ) ,` ,space) can
> set these property bits to false thereby making the underlying rowset
> non-updateable. This causes any update/insert/delete queries including
> 4-part naming & pass-through against these datasets to fail.
> A workaround may be to avoid direct 4-part naming and pass thru queries
and
> try to do the update directly on the server, say using sp_executeSQL like:
> EXEC server.database.dbo.sp_ExecuteSQL N'
> UPDATE [table] SET [my column] = ''new value'''
>
Since I never use spaces I can't say I have run across this before. Another
option would be to use a stored proc that lives in the other server and call
it to do the update. Good luck. If you still aren't satisfied you can give
MS PSS a call.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Young, Corey" <Corey@.Youngspot.com> wrote in message
news:uzPNp%23BnDHA.1284@.TK2MSFTNGP09.phx.gbl...
> Thanks!
> The behavior is the same using either double-quotes or brackets.
> -Corey
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ucOjyfBnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > OK, it is best to post that extra info up front so that we don't have to
> > assume anything. I will post this on the private ng and see if anyone
> else
> > can confirm this. By the way does it work if you use double quotes
> instead
> > of [] ?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > Thanks for the response! I'm using SQL Server 2000 with Service Pack
3.
> > > The problem happens:
> > >
> > > 1. Using Query Analyzer
> > > 2. In my code, which uses the native SQL Server .NET data provider
> > >
> > > I have seen and read a lot of articles and, while they discuss the
> > problem,
> > > and while Microsoft claims they have fixed it in other situations,
they
> > have
> > > not fixed it in mine. I would be interested if anyone could reproduce
> the
> > > problem, or could give me information that would lead to a solution of
> the
> > > problem. Otherwise I'll be forced to use an MSDN support incident.
> > >
> > > -Corey
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > > You don't say what version of sql server your using so it is hard to
> say
> > > but
> > > > there are numerous KB's with related subjects on this. Here is one
> that
> > > > seems to fit.
> > > >
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> > > >
> > > > If that is not it then I would take a look at the other hits in the
KB
> > > that
> > > > you can find from here:
> > > > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> > > >
> > > > and enter "linked server identifier".
> > > >
> > > >
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > > > I believe this is a bug in how update SQL statements are parsed
when
> > > > > executing against a linked server. I execute the following SQL:
> > > > >
> > > > > update [server].[database].[dbo].[table] set [my column] = 'new
> value'
> > > > >
> > > > > Note the space in the column name 'my column'. And I get the
> > following
> > > > > errors:
> > > > >
> > > > > Server: Msg 8180, Level 16, State 1, Line 1
> > > > > Statement(s) could not be prepared.
> > > > > Server: Msg 170, Level 15, State 1, Line 1
> > > > > Line 1: Incorrect syntax near 'column'.
> > > > >
> > > > > The syntax of that SQL statement is correct. In fact, I can go to
> the
> > > > > linked server and execute it there and it works, like this:
> > > > >
> > > > > update [table] set [my column] = 'new value'
> > > > >
> > > > > If I change the schema so the column does not have a space in it,
> then
> > > > > execute the following SQL statement from the original server with
> the
> > > > server
> > > > > link, it works:
> > > > >
> > > > > update [server].[database].[dbo].[table] set [mycolumn] = 'new
> value'
> > > > >
> > > > > This to me says that there is a parsing bug because, when
performing
> > an
> > > > > update against a linked server, the quoted identifier for the
column
> > is
> > > > not
> > > > > respected! Note that this works:
> > > > >
> > > > > select [my column] from [server].[database].[dbo].[table]
> > > > >
> > > > > So it appears to be a problem parsing the update statement.
> > > > >
> > > > > Can anyone shed some light on what is going on? Should I use a
> > support
> > > > > incident to get this fixed? Or is it something I am doing wrong?
> > > Thanks!
> > > > >
> > > > > -Corey
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks for the help!
I have not seen 4-part naming to be a problem as I am able to do inserts and
updates to tables on the linked server when the table and column names don't
require bracketing or quoting.
The table and column names are highly dynamic, so having a stored procedure
on the linked server is probably not a good option for me. However, I had
not thought of the sp_executesql option, which I will try. What I had
planned on doing (until you came up with the sp_executesql option) was to
name my columns and tables such that they do not have spaces in them, but
rather have the '_' character in them, and then just changing the '_'
character to a space prior to display to the user. I'll try your idea
first.
Will there be any performance consequences vs. normal inserts and updates as
a result of using sp_executesql in the way you have described?
Again, thanks a lot for the help! I really appreciate it.
-Corey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Oo7xj4OnDHA.424@.TK2MSFTNGP10.phx.gbl...
> Corey,
> Here is a reply I got from another MVP and although I don't like the
answer
> I guess it makes sense as to what is going on.
> > My understanding is that, this is a known limitation(?) of OLE DB
provider
> > for SQL Server. By default, SQL Server OLE DB provider supports two
> > interfaces IRowsetUpdate & IRowsetChange whose properties determine
> whether
> > the underlying rowset can be updated or not. Delimited identifiers with
> > certain characters (~ , - ,! ,{ ,% ,} ,^ ,' ,& ,. ,( ,\ , ) ,` ,space)
can
> > set these property bits to false thereby making the underlying rowset
> > non-updateable. This causes any update/insert/delete queries including
> > 4-part naming & pass-through against these datasets to fail.
> >
> > A workaround may be to avoid direct 4-part naming and pass thru queries
> and
> > try to do the update directly on the server, say using sp_executeSQL
like:
> >
> > EXEC server.database.dbo.sp_ExecuteSQL N'
> > UPDATE [table] SET [my column] = ''new value'''
> >
>
> Since I never use spaces I can't say I have run across this before.
Another
> option would be to use a stored proc that lives in the other server and
call
> it to do the update. Good luck. If you still aren't satisfied you can
give
> MS PSS a call.
>
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Young, Corey" <Corey@.Youngspot.com> wrote in message
> news:uzPNp%23BnDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > Thanks!
> >
> > The behavior is the same using either double-quotes or brackets.
> >
> > -Corey
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:ucOjyfBnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > > OK, it is best to post that extra info up front so that we don't have
to
> > > assume anything. I will post this on the private ng and see if anyone
> > else
> > > can confirm this. By the way does it work if you use double quotes
> > instead
> > > of [] ?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > > Thanks for the response! I'm using SQL Server 2000 with Service
Pack
> 3.
> > > > The problem happens:
> > > >
> > > > 1. Using Query Analyzer
> > > > 2. In my code, which uses the native SQL Server .NET data provider
> > > >
> > > > I have seen and read a lot of articles and, while they discuss the
> > > problem,
> > > > and while Microsoft claims they have fixed it in other situations,
> they
> > > have
> > > > not fixed it in mine. I would be interested if anyone could
reproduce
> > the
> > > > problem, or could give me information that would lead to a solution
of
> > the
> > > > problem. Otherwise I'll be forced to use an MSDN support incident.
> > > >
> > > > -Corey
> > > >
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > > > You don't say what version of sql server your using so it is hard
to
> > say
> > > > but
> > > > > there are numerous KB's with related subjects on this. Here is
one
> > that
> > > > > seems to fit.
> > > > >
> > > > >
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> > > > >
> > > > > If that is not it then I would take a look at the other hits in
the
> KB
> > > > that
> > > > > you can find from here:
> > > > > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> > > > >
> > > > > and enter "linked server identifier".
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > > > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > > > > I believe this is a bug in how update SQL statements are parsed
> when
> > > > > > executing against a linked server. I execute the following SQL:
> > > > > >
> > > > > > update [server].[database].[dbo].[table] set [my column] = 'new
> > value'
> > > > > >
> > > > > > Note the space in the column name 'my column'. And I get the
> > > following
> > > > > > errors:
> > > > > >
> > > > > > Server: Msg 8180, Level 16, State 1, Line 1
> > > > > > Statement(s) could not be prepared.
> > > > > > Server: Msg 170, Level 15, State 1, Line 1
> > > > > > Line 1: Incorrect syntax near 'column'.
> > > > > >
> > > > > > The syntax of that SQL statement is correct. In fact, I can go
to
> > the
> > > > > > linked server and execute it there and it works, like this:
> > > > > >
> > > > > > update [table] set [my column] = 'new value'
> > > > > >
> > > > > > If I change the schema so the column does not have a space in
it,
> > then
> > > > > > execute the following SQL statement from the original server
with
> > the
> > > > > server
> > > > > > link, it works:
> > > > > >
> > > > > > update [server].[database].[dbo].[table] set [mycolumn] = 'new
> > value'
> > > > > >
> > > > > > This to me says that there is a parsing bug because, when
> performing
> > > an
> > > > > > update against a linked server, the quoted identifier for the
> column
> > > is
> > > > > not
> > > > > > respected! Note that this works:
> > > > > >
> > > > > > select [my column] from [server].[database].[dbo].[table]
> > > > > >
> > > > > > So it appears to be a problem parsing the update statement.
> > > > > >
> > > > > > Can anyone shed some light on what is going on? Should I use a
> > > support
> > > > > > incident to get this fixed? Or is it something I am doing
wrong?
> > > > Thanks!
> > > > > >
> > > > > > -Corey
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||sp_executesql can be just as effective as adhoc sql (or more so) if you can
properly parameterize the changing values. If you are calling multiple
tables or columns you will still be able to cache the plan and reuse it the
next time you call a similar update.
--
Andrew J. Kelly
SQL Server MVP
"Young, Corey" <Corey@.Youngspot.com> wrote in message
news:eD820LSnDHA.424@.TK2MSFTNGP10.phx.gbl...
> Thanks for the help!
> I have not seen 4-part naming to be a problem as I am able to do inserts
and
> updates to tables on the linked server when the table and column names
don't
> require bracketing or quoting.
> The table and column names are highly dynamic, so having a stored
procedure
> on the linked server is probably not a good option for me. However, I had
> not thought of the sp_executesql option, which I will try. What I had
> planned on doing (until you came up with the sp_executesql option) was to
> name my columns and tables such that they do not have spaces in them, but
> rather have the '_' character in them, and then just changing the '_'
> character to a space prior to display to the user. I'll try your idea
> first.
> Will there be any performance consequences vs. normal inserts and updates
as
> a result of using sp_executesql in the way you have described?
> Again, thanks a lot for the help! I really appreciate it.
> -Corey
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Oo7xj4OnDHA.424@.TK2MSFTNGP10.phx.gbl...
> > Corey,
> >
> > Here is a reply I got from another MVP and although I don't like the
> answer
> > I guess it makes sense as to what is going on.
> >
> > > My understanding is that, this is a known limitation(?) of OLE DB
> provider
> > > for SQL Server. By default, SQL Server OLE DB provider supports two
> > > interfaces IRowsetUpdate & IRowsetChange whose properties determine
> > whether
> > > the underlying rowset can be updated or not. Delimited identifiers
with
> > > certain characters (~ , - ,! ,{ ,% ,} ,^ ,' ,& ,. ,( ,\ , ) ,` ,space)
> can
> > > set these property bits to false thereby making the underlying rowset
> > > non-updateable. This causes any update/insert/delete queries including
> > > 4-part naming & pass-through against these datasets to fail.
> > >
> > > A workaround may be to avoid direct 4-part naming and pass thru
queries
> > and
> > > try to do the update directly on the server, say using sp_executeSQL
> like:
> > >
> > > EXEC server.database.dbo.sp_ExecuteSQL N'
> > > UPDATE [table] SET [my column] = ''new value'''
> > >
> >
> >
> > Since I never use spaces I can't say I have run across this before.
> Another
> > option would be to use a stored proc that lives in the other server and
> call
> > it to do the update. Good luck. If you still aren't satisfied you can
> give
> > MS PSS a call.
> >
> >
> > http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL
Support
> > http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > news:uzPNp%23BnDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > > Thanks!
> > >
> > > The behavior is the same using either double-quotes or brackets.
> > >
> > > -Corey
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:ucOjyfBnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > > > OK, it is best to post that extra info up front so that we don't
have
> to
> > > > assume anything. I will post this on the private ng and see if
anyone
> > > else
> > > > can confirm this. By the way does it work if you use double quotes
> > > instead
> > > > of [] ?
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > > news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > > > Thanks for the response! I'm using SQL Server 2000 with Service
> Pack
> > 3.
> > > > > The problem happens:
> > > > >
> > > > > 1. Using Query Analyzer
> > > > > 2. In my code, which uses the native SQL Server .NET data provider
> > > > >
> > > > > I have seen and read a lot of articles and, while they discuss the
> > > > problem,
> > > > > and while Microsoft claims they have fixed it in other situations,
> > they
> > > > have
> > > > > not fixed it in mine. I would be interested if anyone could
> reproduce
> > > the
> > > > > problem, or could give me information that would lead to a
solution
> of
> > > the
> > > > > problem. Otherwise I'll be forced to use an MSDN support
incident.
> > > > >
> > > > > -Corey
> > > > >
> > > > >
> > > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > > news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > > > > You don't say what version of sql server your using so it is
hard
> to
> > > say
> > > > > but
> > > > > > there are numerous KB's with related subjects on this. Here is
> one
> > > that
> > > > > > seems to fit.
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> > > > > >
> > > > > > If that is not it then I would take a look at the other hits in
> the
> > KB
> > > > > that
> > > > > > you can find from here:
> > > > > > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> > > > > >
> > > > > > and enter "linked server identifier".
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Andrew J. Kelly
> > > > > > SQL Server MVP
> > > > > >
> > > > > >
> > > > > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > > > > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > > > > > I believe this is a bug in how update SQL statements are
parsed
> > when
> > > > > > > executing against a linked server. I execute the following
SQL:
> > > > > > >
> > > > > > > update [server].[database].[dbo].[table] set [my column] ='new
> > > value'
> > > > > > >
> > > > > > > Note the space in the column name 'my column'. And I get the
> > > > following
> > > > > > > errors:
> > > > > > >
> > > > > > > Server: Msg 8180, Level 16, State 1, Line 1
> > > > > > > Statement(s) could not be prepared.
> > > > > > > Server: Msg 170, Level 15, State 1, Line 1
> > > > > > > Line 1: Incorrect syntax near 'column'.
> > > > > > >
> > > > > > > The syntax of that SQL statement is correct. In fact, I can
go
> to
> > > the
> > > > > > > linked server and execute it there and it works, like this:
> > > > > > >
> > > > > > > update [table] set [my column] = 'new value'
> > > > > > >
> > > > > > > If I change the schema so the column does not have a space in
> it,
> > > then
> > > > > > > execute the following SQL statement from the original server
> with
> > > the
> > > > > > server
> > > > > > > link, it works:
> > > > > > >
> > > > > > > update [server].[database].[dbo].[table] set [mycolumn] = 'new
> > > value'
> > > > > > >
> > > > > > > This to me says that there is a parsing bug because, when
> > performing
> > > > an
> > > > > > > update against a linked server, the quoted identifier for the
> > column
> > > > is
> > > > > > not
> > > > > > > respected! Note that this works:
> > > > > > >
> > > > > > > select [my column] from [server].[database].[dbo].[table]
> > > > > > >
> > > > > > > So it appears to be a problem parsing the update statement.
> > > > > > >
> > > > > > > Can anyone shed some light on what is going on? Should I use
a
> > > > support
> > > > > > > incident to get this fixed? Or is it something I am doing
> wrong?
> > > > > Thanks!
> > > > > > >
> > > > > > > -Corey
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Quote in input field yeilds error

Hello!

I have an input form that contains a textarea in which people can input the description of an item. They then click Insert or Update and the information is inserted or updated to a SQL Server database. Everything works fine unless someone includes a quote in the description. For example:

The item is Bob's computer.

The apostrophe in Bob creates a problem. I receive the following:

Incorrect syntax near 's'. Unclosed quotation mark before the character string '.

I understand the problem. How do I correct it?

Thanks!

PS I am using C#.Use parameters.

See here|||OF COURSE!! I knew I had done this at some time...thanks for JOGGING my brain!! :)|||I have the same problem, but I don't see how that tutorial would work for an imput text box. If the user types in something like "Mike's car" (without the quotes), it ruins the sql string. How can I code around, or get the server to accept single quotes or apostrophes?
Thanks,
Sean|||The previous link will in fact resolve the problem. Honest.

A poorer alternative is replacing all ' with two ' characters ('' - this is NOT a regular quote, but two single quotes). Doing this still allows SQL Injection attacks to occur.|||Sorry, but I don't see how to apply it to an update statement. Here's a piece of my code:

Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim strPurpose as string =txtPurpose.text
Dim MySQL as string = "Insert into tbl_ExpsReports (expsPurpose) values ('" & strPurpose & "')"
Dim myConn As New OLEDBConnection(configurationSettings.AppSettings("MSDBconn"))
Dim Cmd as New OleDbCommand(MySQL, MyConn)
MyConn.Open()
cmd.ExecuteNonQuery
MyConn.close()
End Sub

How do I allow the user to key in a single quote or apostrope into the txtPurpose text box? The tutorial seems to be geared towards a return rather than input statement.
Thanks,
Sean|||What you are doing is not that unusual:

Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim strPurpose as string =txtPurpose.text
Dim MySQL as string = "Insert into tbl_ExpsReports (expsPurpose) values (?)"

Dim myConn As New OLEDBConnection(configurationSettings.AppSettings("MSDBconn"))
Dim Cmd as New OleDbCommand(MySQL, MyConn)
Cmd.Parameters.Add("expsPurpose",strPurpose)

MyConn.Open()
cmd.ExecuteNonQuery
MyConn.close()
End Sub
</code>|||So, what you are saying is, if I use a perameterized insert statement, then the user can key in an apostrophe or single quote? Cool! ;^]|||Yes. And prevents SQL Injection.|||SQL injection... hmmmm... sounds bad.

quotation marks in xml

I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily.

this section shows the problem, i am loading xml from a file and trying to insert this into the sql database

XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));
reader.WhitespaceHandling = WhitespaceHandling.None;
XmlDocument xmlDocF = new XmlDocument();
xmlDocF.Load(reader);
this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;
this.SqlDataSource1.Update();
reader.Close();

pls help

Can you post some part of xml?

|||

<_n0011 HyperLink="undefined" Welcome="A'hneiv zipv meih" Language="undefined" country="undefined" x="1329" y="338"/>

this is an example.

there are about 400 similar tags with about 30 instances of the single quote mark. this may change in the future as i am trying to make the xml editable.

|||

I believe using Parameters will take care of the escaping automatically.

|||

I have tried the following syntax and got the same problems. I think this type of parameterising just parses the text into the string and produces effectively the same problem. Is there a different type of parameterising that I should be using.

this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] @.xmlDocF.InnerXml where [index] = 1" ;

I am a bit new to this subject and have looked for an article on this subject but not been able to find anything that deals with this specific requirement.

many thx for your assistance.

|||

what kind of parameter method did u have in mind?

|||

I created a DataSource with insert/update/delete commands and connected it to a GridView.

In edit mode, I entered this in the AVarCharField: Hello, "test" 'test'

When I clicked update, it worked.

The UpdateCommand looks like this:

UpdateCommand="UPDATE [TestTable] SET [CustomerName] = @.CustomerName, [Status] = @.Status, [AVarCharField] = @.AVarCharField WHERE [ID] = @.ID">

The Parameters look like this:

<UpdateParameters> <asp:Parameter Name="CustomerName" Type="String" /> <asp:Parameter Name="Status" Type="Single" /> <asp:Parameter Name="AVarCharField" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /></UpdateParameters>

I hope that helps.

Quicker Cursor or Table Variable

Hi
I have a large update batch to make on our database which will run overnight
when no users are logged in.
I always use a Table Variable instead of a Cursor to conserve resources, but
in this case resources are not a problem but speed is.
Which would be quicker: cursor or table variable, also would I get a
performance benefit from running the batch within a Stored Procedure rather
than Query Analyser.
Thanks
BHave you looked at the execution plan used in your batch update. This should
pinpoint where the problem is.
Use a binary approach to this. In your batch write print statements which
will display datediff statements throughout the batch. This way you will
know which portion takes the longest.
I think you will find that local table variables with indexes (primary key
constraint) offer the best performance. You will probably also find that
using one or more stored procedures offers better performance as well.
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
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:uz1niRiAGHA.916@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a large update batch to make on our database which will run
> overnight
> when no users are logged in.
> I always use a Table Variable instead of a Cursor to conserve resources,
> but
> in this case resources are not a problem but speed is.
> Which would be quicker: cursor or table variable, also would I get a
> performance benefit from running the batch within a Stored Procedure
> rather
> than Query Analyser.
> Thanks
> B
>|||Ben
I'd understan you if you ask what is a difference between a table variable
and a temporary table?
How does it relate to the cursors?
Try to avoid using cursors because it may hurt a performance , insead use
SET BASED process to update a table
If you show us what you are trying to accomplish , we van suggest something
more useful.
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:uz1niRiAGHA.916@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a large update batch to make on our database which will run
> overnight
> when no users are logged in.
> I always use a Table Variable instead of a Cursor to conserve resources,
> but
> in this case resources are not a problem but speed is.
> Which would be quicker: cursor or table variable, also would I get a
> performance benefit from running the batch within a Stored Procedure
> rather
> than Query Analyser.
> Thanks
> B
>

Quick Update/Insert TSQL?

Hi all!

I have a quick question...

'UPLOAD / INSERT EXISTING CLIENT DATA INTO SQL SERVER FROM CLIENT
INSERT INTO ProductLocal
(tblID,SQLkey, CreateDateTime, Alias, ProductNumber, ProductMfgID, ProductDesc, ProductAlt1Number, ProductAlt2Number, ProductCost,
ProductListPrice, ProductVendorID, ProductHier1, ProductHier2, ProductHier3, ProductHier4, ProductHier5, ProductHier6, ProductHier7, ProductHier8,
ProductHier9, ProductCategory, ProductSubCategory, ProductLocalAdd, ProductAddDesc, create_timestamp, update_timestamp, update_originator_id,
create_date)
SELECT tblID, SQLkey, CreateDateTime, Alias, ProductNumber, ProductMfgID, ProductDesc, ProductAlt1Number, ProductAlt2Number, ProductCost,
ProductListPrice, ProductVendorID, ProductHier1, ProductHier2, ProductHier3, ProductHier4, ProductHier5, ProductHier6, ProductHier7, ProductHier8,
ProductHier9, ProductCategory, ProductSubCategory, ProductLocalAdd, ProductAddDesc, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM Product
WHERE (Alias = 'me')

'DOWNLOAD / UPDATE-INSERT EXISTING LOCAL DB (PRODUCT TABLE) FROM SQL SERVER PRODUCTLOCAL TABLE

What would be the best and least expensive way to UPDATE/INSERT the local client db from the SQL Server?

-- LOCAL DB (PRODUCT TABLE) FROM SQL SERVER PRODUCTLOCAL TABLE

Any help would be appreciated.... thanks.

Kind regards,

billb

You can use the following approaches,

1. Linked Server,

Set up Linked Serer on your Local Server,

EXEC master.dbo.sp_addlinkedserver

@.server = N'<LinkedServerName>',

@.srvproduct=N'SQLOLEDB.1',

@.provider=N' SQLOLEDB.1',

@.datasrc=N'<YourServer>',

@.provstr=N'Provider=SQLOLEDB.1;Data Source=<YourServer>;Initial Catalog=<Database name>’,

@.catalog=N'<Database Name>'

GO

Exec sp_addlinkedsrvlogin

@.rmtsrvname = N'<LinkedServerName>',

@.useself = false,

@.locallogin = 'sa',

@.rmtuser = 'sa',

@.rmtpassword = '***************'

Now execute the following query..

INSERT INTO ProductLocal

SELECT *

FROM

<LinkedServerName>.<databasename>.<dbo>.Product

2. Ad-Hoc Distributed Quires

Insert Into ProductLocal

SELECT a.*

FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=<YourServer>; UID=sa; PWD=PASSWORD’, 'Select * from <databasename>.dbo.product') AS a;

3. SQL Server Replication

See, http://msdn2.microsoft.com/en-us/library/ms151198.aspx

|||

Was sort of going for just the TSQL Update/Insert though:

Insert Into ProductLocal

SELECT a.*

FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=<YourServer>; UID=sa; PWD=PASSWORD’, 'Select * from <databasename>.dbo.product') AS a;

Something like:

Update ProductLocal

SELECT a.*

FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=<YourServer>; UID=sa; PWD=PASSWORD’, 'Select * from <databasename>.dbo.product where alias = ' & me & ' & ') AS a;

Not sure how to do the above update tsql...

Thanks,

billb

Quick SQL question...

I'm trying to change every value of a certain column in a table by adding an
extra character to it:
UPDATE event_details
SET code_event = (SELECT code_event + '0' FROM event_details)
But I know I need some sort of join to do this but I'm not sure what. Can
any one advise please?
--
Cheers,
elzikoelziko
CREATE TABLE #Temp
(
Col VARCHAR(10)
)
GO
INSERT INTO #Temp VALUES ('A')
INSERT INTO #Temp VALUES ('B')
INSERT INTO #Temp VALUES ('C')
GO
SELECT * FROM #Temp
GO
UPDATE #Temp SET Col=Col+'0'
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:eQLPHRt#DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I'm trying to change every value of a certain column in a table by adding
an
> extra character to it:
> UPDATE event_details
> SET code_event = (SELECT code_event + '0' FROM event_details)
> But I know I need some sort of join to do this but I'm not sure what. Can
> any one advise please?
> --
> Cheers,
> elziko
>|||> CREATE TABLE #Temp
I need to do this without using CREATE TABLE
Any ideas?
--
Cheers,
elziko|||elziko
I was creating table for repro your question because you did not post DDL.
Just use UPDATE Tablename.............
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:#BHQ$tt#DHA.2184@.TK2MSFTNGP12.phx.gbl...
> > CREATE TABLE #Temp
> I need to do this without using CREATE TABLE
> Any ideas?
> --
> Cheers,
> elziko
>|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:eQLPHRt%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I'm trying to change every value of a certain column in a table by adding
an
> extra character to it:
> UPDATE event_details
> SET code_event = (SELECT code_event + '0' FROM event_details)
> But I know I need some sort of join to do this but I'm not sure what. Can
> any one advise please?
> --
> Cheers,
> elziko
>|||Thanks, but how would I only update the rows who are already only seven
characters long? Where would I put the where clause?
--
Cheers,
elziko|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details where LEN(code_event)
= 7
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter where LEN(code_event) =7
I am not sure exactly what you are trying to achieve. The above code will
add a '0' to every code_event that is 7 characters long. If your 7
character/digit code_events are different then I would suggest using a
cursor.
Cheers,
Andre
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:403b730c$0$21923$afc38c87@.news.easynet.co.uk...
> Thanks, but how would I only update the rows who are already only seven
> characters long? Where would I put the where clause?
> --
> Cheers,
> elziko
>|||> I am not sure exactly what you are trying to achieve. The above code will
> add a '0' to every code_event that is 7 characters long. If your 7
> character/digit code_events are different then I would suggest using a
> cursor.
Yeah thats exactly what I want to do and thats where I initially put the
WHERE clauses before replying to you! But I get the following error:
MED0017 0
Server: Msg 8152, Level 16, State 9, Line 13
String or binary data would be truncated.
The statement has been terminated.
When I do the print of the NewCharacter it seems to have a space in it but
the column I'm updating is only 8 chars long so it looks like this is the
problem? Where is that space coming from? Or is something else wrong here?
Thanks a lot for your help.
--
Cheers,
elziko

Quick SQL Question - update a single row with multiple rows

This is on SQL Server 2000 (if that matters, but I think this is just a
T-SQL issue).
I have two tables (tied together by a common key, in this example TableID),
the first of which has many rows (for a given ID), and the second has just
one row (for that ID).
I want to figure out a way to do an UPDATE query (without using a cursor)
that will allow me to build up the Descr(iption) field on the second table
with all of the values from the original table, concatenated.
For example, if the first table (which you'll see I create and populate in
the example below) contains:
TableID Counter
1 1
1 2
1 3
1 4
1 5
and the second table contains:
TableID Descr
1 Start:
I want to come up with an update query that will join the two tables, and
populate the Descr field of the single row of the second table (for TableID
1) with: "Start: 1, 2, 3, 4, 5".
And yet, I'm at a loss to figure out a way to do this (other that cursors,
that I need to avoid using).
Here's my code, for what it's worth:
DECLARE @.table1 table
(
TableId int,
Counter int
)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,1)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,2)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,3)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,4)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,5)
DECLARE @.Table2 table
(
TableID int,
Descr char(1024)
)
INSERT INTO @.Table2 (TableID, Descr)
VALUES (1, 'Start:')
UPDATE @.Table2
SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
FROM @.Table2 T2
INNER JOIN @.Table1 T1
ON T2.TableID = T1.TableID
select *
from @.Table2you want to create a udf to do concat...
e.g.
create function udf(@.id int)
returns varchar(1024)
as
begin
declare @.s varchar(1024)
select @.s=isnull(@.s+',','')+cast(@.counter as varchar)
from tb1
where id=@.id
return @.s
end
update tb2
set descr=udf(id)
-oj
"Scott M. Lyon" <scott.RED.lyon.WHITE@.rapistan.BLUE.com> wrote in message
news:ugO34pvPGHA.1556@.TK2MSFTNGP09.phx.gbl...
> This is on SQL Server 2000 (if that matters, but I think this is just a
> T-SQL issue).
>
> I have two tables (tied together by a common key, in this example
> TableID), the first of which has many rows (for a given ID), and the
> second has just one row (for that ID).
>
> I want to figure out a way to do an UPDATE query (without using a cursor)
> that will allow me to build up the Descr(iption) field on the second table
> with all of the values from the original table, concatenated.
>
> For example, if the first table (which you'll see I create and populate in
> the example below) contains:
> TableID Counter
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
>
> and the second table contains:
> TableID Descr
> 1 Start:
>
> I want to come up with an update query that will join the two tables, and
> populate the Descr field of the single row of the second table (for
> TableID 1) with: "Start: 1, 2, 3, 4, 5".
>
> And yet, I'm at a loss to figure out a way to do this (other that cursors,
> that I need to avoid using).
>
> Here's my code, for what it's worth:
> DECLARE @.table1 table
> (
> TableId int,
> Counter int
> )
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,1)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,2)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,3)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,4)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,5)
> DECLARE @.Table2 table
> (
> TableID int,
> Descr char(1024)
> )
> INSERT INTO @.Table2 (TableID, Descr)
> VALUES (1, 'Start:')
> UPDATE @.Table2
> SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
> FROM @.Table2 T2
> INNER JOIN @.Table1 T1
> ON T2.TableID = T1.TableID
> select *
> from @.Table2
>|||Scott M. Lyon wrote:
> This is on SQL Server 2000 (if that matters, but I think this is just a
> T-SQL issue).
>
> I have two tables (tied together by a common key, in this example TableID)
,
> the first of which has many rows (for a given ID), and the second has just
> one row (for that ID).
>
> I want to figure out a way to do an UPDATE query (without using a cursor)
> that will allow me to build up the Descr(iption) field on the second table
> with all of the values from the original table, concatenated.
>
> For example, if the first table (which you'll see I create and populate in
> the example below) contains:
> TableID Counter
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
>
> and the second table contains:
> TableID Descr
> 1 Start:
>
> I want to come up with an update query that will join the two tables, and
> populate the Descr field of the single row of the second table (for TableI
D
> 1) with: "Start: 1, 2, 3, 4, 5".
>
> And yet, I'm at a loss to figure out a way to do this (other that cursors,
> that I need to avoid using).
>
> Here's my code, for what it's worth:
> DECLARE @.table1 table
> (
> TableId int,
> Counter int
> )
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,1)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,2)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,3)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,4)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,5)
> DECLARE @.Table2 table
> (
> TableID int,
> Descr char(1024)
> )
> INSERT INTO @.Table2 (TableID, Descr)
> VALUES (1, 'Start:')
> UPDATE @.Table2
> SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
> FROM @.Table2 T2
> INNER JOIN @.Table1 T1
> ON T2.TableID = T1.TableID
> select *
> from @.Table2
Don't store the data in both forms in permanent tables. For one thing
you are creating redundancy. For another, "descr" looks like a
non-atomic value, which is a bad idea in principle. So assuming this is
just a one-off exercise a cursor may even be the most feasible
solution.
Assuming your data will be unchanging while you update Table2, take a
look at this example for one possible solution:
http://groups.google.co.uk/group/mi...5888972df4b3291
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1141418003.533985.216310@.e56g2000cwe.googlegroups.com...
> Don't store the data in both forms in permanent tables. For one thing
> you are creating redundancy. For another, "descr" looks like a
> non-atomic value, which is a bad idea in principle. So assuming this is
> just a one-off exercise a cursor may even be the most feasible
> solution.
> Assuming your data will be unchanging while you update Table2, take a
> look at this example for one possible solution:
> http://groups.google.co.uk/group/mi...5888972df4b3291
> --
> David Portas, SQL Server MVP
>
This was actually just an overly simplified example, so I could figure out
how to do this, and then apply that to the real problem. The real issue is
that the source tables are actually a combination of three or four permanent
tables, and the destination (that I'm doing the update on) is a temp table,
just used for generating data for reporting.|||>> The real issue is that the source tables are actually a combination of th
ree or four permanent tables, and the destination (that I'm doing the update
on) is a temp table,just used for generating data for reporting. <<
In a tiered architecture, is done in the front end and not in the
database. It sounds likeyou want to have VIEW that collects the report
data and then you can arrange it anyway you wish with the front end.
Update a temp table from several base tables, one at a time, is an
awful way to write SQL. We prefer to have things happen "all at once"
and in procedural steps.

Quick Question... How to use Default Values (after allowing NULL)

Hello,

I have a BIT column which accepts NULL values.

What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).

Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?

Thanks,Look up CREATE TRIGGER in Books Online, and pay special attention to the INSERTED and DELETED virtual table concepts. Then within your trigger:

update YourTable
set YourValue = 0
from YourTable
inner join INSERTED on YourTable.PKEY = INSERTED.PKEY
where YourValue is null

But really, you should be doing your inserts through a stored procedure which uses ISNULL([NewValue], 0)|||Thx for the quick response.

I'll give it a shot.

Quick question on Cascade Delete

Is cascade delete and cascade update new to 2000 or did it exist in 7?
Thank you,
MikeNew for 2000
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:ORKfTeV$DHA.3804@.TK2MSFTNGP09.phx.gbl...
> Is cascade delete and cascade update new to 2000 or did it exist in 7?
> Thank you,
> Mike
>|||Thank you.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Jc8kqV$DHA.712@.tk2msftngp13.phx.gbl...
> New for 2000
> "Mike Hildner" <mhildner@.afweb.com> wrote in message
> news:ORKfTeV$DHA.3804@.TK2MSFTNGP09.phx.gbl...
>

Quick question on Cascade Delete

Is cascade delete and cascade update new to 2000 or did it exist in 7?
Thank you,
MikeNew for 2000
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:ORKfTeV$DHA.3804@.TK2MSFTNGP09.phx.gbl...
> Is cascade delete and cascade update new to 2000 or did it exist in 7?
> Thank you,
> Mike
>|||Thank you.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Jc8kqV$DHA.712@.tk2msftngp13.phx.gbl...
> New for 2000
> "Mike Hildner" <mhildner@.afweb.com> wrote in message
> news:ORKfTeV$DHA.3804@.TK2MSFTNGP09.phx.gbl...
> > Is cascade delete and cascade update new to 2000 or did it exist in 7?
> >
> > Thank you,
> > Mike
> >
> >
>

Quick and easy question, Sql update method . . .

I am new to Sql, so I think this is a really easy question.

But I am working on a 2005 MSSQL database.

When I add columns to an existing application with data and tables already being used, and the new column will be set to Database Null.

What is an easy way to quickly add the data to all the rows in the table.

For example if I am adding a checkbox, I've been doing it manually, add the column, change all the rows to False, one by one.

And then I can change it to Disallow DBNull.

As I get more and more users this could be a very time consuming process.

So the name of the Table is classifeds_Ads and let's say the column I want to add is Bonus and it needs to be filled with False.

How do I do this?

Thank you in advance

Daniel Meis

You can create a new query and then just run this:

UPDATE classifieds_Ads Set Bonus = 0

Or an easier approach is to use the Column Properties pane to set the initial properties for the column - Allow Nulls: No, Default Value or Binding: 0

|||

Works great, Thank you.

Queued update

Dear friends

I have a simple doubt.

1)What is the difference between Queued Updating & Immediate Updating in Transactional Relplication.

2)What is the difference between Meged Replication & Peer to Peer replication.Because in both each node will Act as Publisher/Subscriber.So load balancing is possible both type replication na?

3) how to replicate views,stored procedure & functions.Beacuse when applying intial snapshot the copy is getting in subscriber.But afterwards whatever changes occurring in view,procedure not propagating from publisher to subscriber.what to do in this case

Filson

1. Queue can handle offline changes, immediate updating works only when publisher/subscriber are connected, otherwise change at the subscriber will fail.

2. In a nutshell, P2P is used mostly for server-server environment, Merge is for server-client environment. Read BOL topics (cited below) for more information.

3. In sp_addpublication/sp_addmergepublication, set @.replicate_ddl = 1. For more information about schema changes, see "Making Schema Changes on Publication Databases", http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

For more information, please see Books Online topic "SQL Server Replication", http://msdn2.microsoft.com/en-us/library/ms151198.aspx.

Queue Reader Agent Failed

Hi,
We have set replication between two servers.In subscriber if i update any
row, the transaction is in Msreplication_queue Table.But when it try to push
to publisher it throws a error called "Invalid cursor state". I don't know
where it is going wrong.
what could be the cause of this error.
Thanks in Advance,
Rajesh
Do you have sp4 installed? There was a bug prior to sp4 which gave this
message (http://support.microsoft.com/kb/831997/EN-US/).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi
Thanks, Currently we have installed SP3.We have to install it.
"Paul Ibison" wrote:

> Do you have sp4 installed? There was a bug prior to sp4 which gave this
> message (http://support.microsoft.com/kb/831997/EN-US/).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Questions on triggers and when they fire...

I have a stored proc that does something like 'Update table1 set a =
'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:

>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you cant. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
Hugo Kornelis, SQL Server MVP

Questions on triggers and when they fire...

I have a stored proc that does something like 'Update table1 set a = 'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:
>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you can´t. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
--
Hugo Kornelis, SQL Server MVP

Questions on triggers and when they fire...

I have a stored proc that does something like 'Update table1 set a =
'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris
"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.
|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:

>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you cant. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
Hugo Kornelis, SQL Server MVP