'Error 14274' - Cannot add, update, or delete a job (or its steps or schedules) that ori

http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
I followed the tip above but it didn't work. anyone know of other workaround
?
I restore a copy of my Production DB on a TestDB, the scheduled jobs get
copied over as well. Cannot removed these job frm the Test DB because of
error 14274.
I logon the Test DB on workgroup, rename to original server name (on
workgroup because the Production still exist on the network), tried to
delete the jobs, SQL doesn't allow also. At the moment, I stopped the SQL
Agent Service to prevent the jobs frm running.
How to remove the jobs in the Test DB for good ?Don't bother with the rename stuff. Simply update the originating_server
column to reflect the new server name.
Have a look at http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23GEUVi%23bEHA.1764@.TK2MSFTNGP10.phx.gbl...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
>
> I followed the tip above but it didn't work. anyone know of other
workaround
> ?
> I restore a copy of my Production DB on a TestDB, the scheduled jobs get
> copied over as well. Cannot removed these job frm the Test DB because of
> error 14274.
> I logon the Test DB on workgroup, rename to original server name (on
> workgroup because the Production still exist on the network), tried to
> delete the jobs, SQL doesn't allow also. At the moment, I stopped the SQL
> Agent Service to prevent the jobs frm running.
> How to remove the jobs in the Test DB for good ?
>
>

error 14274

After I change a job and want to save, it gives me error as following:
error 14274: Cannot add, update or delete a job (or its steps or schedules)
that originates from an MSX server. The job was not save.
Can anyone please tell me how to fix this error? Thanks.
Hi!

> error 14274: Cannot add, update or delete a job (or its steps or
> schedules) that originates from an MSX server. The job was not save.
I guess this should help:
http://support.microsoft.com/default...;en-us;281642.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
|||Actually, I read this article. But I cannot down the SQL service and rename
the DB instance name as it said. What is other solution? Thanks.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23ZnjkyOQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> Hi!
>
> I guess this should help:
> http://support.microsoft.com/default...;en-us;281642.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>
|||Can anyone please help? This is urgent. Thanks.
"00KobeBrian" <a@.b.com> wrote in message
news:uGdduPPQGHA.312@.TK2MSFTNGP12.phx.gbl...
> Actually, I read this article. But I cannot down the SQL service and
> rename the DB instance name as it said. What is other solution? Thanks.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
> message news:%23ZnjkyOQGHA.5400@.TK2MSFTNGP09.phx.gbl...
>
|||Check the following article:
http://www.karaszi.com/sqlserver/inf...erver_name.asp
-Sue
On Tue, 7 Mar 2006 08:04:50 +0800, "00KobeBrian" <a@.b.com>
wrote:

>Can anyone please help? This is urgent. Thanks.
>"00KobeBrian" <a@.b.com> wrote in message
>news:uGdduPPQGHA.312@.TK2MSFTNGP12.phx.gbl...
>

error 14274

After I change a job and want to save, it gives me error as following:
error 14274: Cannot add, update or delete a job (or its steps or schedules)
that originates from an MSX server. The job was not save.
Can anyone please tell me how to fix this error? Thanks.Hi!

> error 14274: Cannot add, update or delete a job (or its steps or
> schedules) that originates from an MSX server. The job was not save.
I guess this should help:
http://support.microsoft.com/defaul...b;en-us;281642.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Actually, I read this article. But I cannot down the SQL service and rename
the DB instance name as it said. What is other solution? Thanks.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZnjkyOQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> Hi!
>
> I guess this should help:
> http://support.microsoft.com/defaul...b;en-us;281642.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>|||Can anyone please help? This is urgent. Thanks.
"00KobeBrian" <a@.b.com> wrote in message
news:uGdduPPQGHA.312@.TK2MSFTNGP12.phx.gbl...
> Actually, I read this article. But I cannot down the SQL service and
> rename the DB instance name as it said. What is other solution? Thanks.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:%23ZnjkyOQGHA.5400@.TK2MSFTNGP09.phx.gbl...
>|||Check the following article:
http://www.karaszi.com/sqlserver/in...server_name.asp
-Sue
On Tue, 7 Mar 2006 08:04:50 +0800, "00KobeBrian" <a@.b.com>
wrote:

>Can anyone please help? This is urgent. Thanks.
>"00KobeBrian" <a@.b.com> wrote in message
>news:uGdduPPQGHA.312@.TK2MSFTNGP12.phx.gbl...
>sql

error 14274

After I change a job and want to save, it gives me error as following:
error 14274: Cannot add, update or delete a job (or its steps or schedules)
that originates from an MSX server. The job was not save.
Can anyone please tell me how to fix this error? Thanks.Hi!
> error 14274: Cannot add, update or delete a job (or its steps or
> schedules) that originates from an MSX server. The job was not save.
I guess this should help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Actually, I read this article. But I cannot down the SQL service and rename
the DB instance name as it said. What is other solution? Thanks.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZnjkyOQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> Hi!
>> error 14274: Cannot add, update or delete a job (or its steps or
>> schedules) that originates from an MSX server. The job was not save.
> I guess this should help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;281642.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>|||Can anyone please help? This is urgent. Thanks.
"00KobeBrian" <a@.b.com> wrote in message
news:uGdduPPQGHA.312@.TK2MSFTNGP12.phx.gbl...
> Actually, I read this article. But I cannot down the SQL service and
> rename the DB instance name as it said. What is other solution? Thanks.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:%23ZnjkyOQGHA.5400@.TK2MSFTNGP09.phx.gbl...
>> Hi!
>> error 14274: Cannot add, update or delete a job (or its steps or
>> schedules) that originates from an MSX server. The job was not save.
>> I guess this should help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;281642.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor, www.SolidQualityLearning.com
>> Anything written in this message represents solely the point of view of
>> the sender.
>> This message does not imply endorsement from Solid Quality Learning, and
>> it does not represent the point of view of Solid Quality Learning or any
>> other person, company or institution mentioned in this message
>>
>|||Check the following article:
http://www.karaszi.com/sqlserver/info_change_server_name.asp
-Sue
On Tue, 7 Mar 2006 08:04:50 +0800, "00KobeBrian" <a@.b.com>
wrote:
>Can anyone please help? This is urgent. Thanks.
>"00KobeBrian" <a@.b.com> wrote in message
>news:uGdduPPQGHA.312@.TK2MSFTNGP12.phx.gbl...
>> Actually, I read this article. But I cannot down the SQL service and
>> rename the DB instance name as it said. What is other solution? Thanks.
>>
>> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
>> message news:%23ZnjkyOQGHA.5400@.TK2MSFTNGP09.phx.gbl...
>> Hi!
>> error 14274: Cannot add, update or delete a job (or its steps or
>> schedules) that originates from an MSX server. The job was not save.
>> I guess this should help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;281642.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor, www.SolidQualityLearning.com
>> Anything written in this message represents solely the point of view of
>> the sender.
>> This message does not imply endorsement from Solid Quality Learning, and
>> it does not represent the point of view of Solid Quality Learning or any
>> other person, company or institution mentioned in this message
>>
>>
>

Error 0x80040E31

Hi.

Somebody can tell how to deal with this error. When I try to connect from an
asp page to update a record wich has a ntext field it give me this error...

I tried with sp_configure 'remote query timeout',0 but no works...

What can I do ??
thanksDaviso (dhernande7@.alumno.uned.es) writes:

Quote:

Originally Posted by

Somebody can tell how to deal with this error. When I try to connect
from an asp page to update a record wich has a ntext field it give me
this error...
>
I tried with sp_configure 'remote query timeout',0 but no works...

Do you get any text with the error message, or just the error code?

If I am to guess from your fiddling with "remote query timeout", the
error is "Timeout Expired". "remote query timeout" is not going to help
you on that one, since that that is the time SQL Server uses when connecting
to linked servers.

Timeouts are always defined on client level, and from an ASP page, you would
set the CommandTimeout on the Connection object. Set it to 0 to wait
indefintely. By default the timeout is 30 seconds.

Another issue why your update takes more than 30 seconds. It could be a
blocking issue, and in that case it does not help if you change the timeout.
But with no information about your code and your system it's impossible to
tell.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ERROR : FOR UPDATE cannot be specified on a READ ONLY ...

Hi All,
I am using SQL Server 2000 + JDeveloper 10g with ADF.
When i try to make changes to data and do COMMIT, I receive ERROR, I
have tried both JDBC Driver from Microsoft and jTDS, both FAILS.
Here is the error :
With Microsoft JDBC Driver :
(oracle.jbo.DMLException) JBO-26080: Error while selecting entity for
Departments
-- LEVEL 1: DETAIL 0 --
(java.sql.SQLException) [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer] FOR UPDATE cannot be specified on a READ ONLY cursor.
With jTDS JDBC Driver :
(oracle.jbo.DMLException) JBO-26080: Error while selecting entity for
Departments
-- LEVEL 1: DETAIL 0 --
(java.sql.SQLException) Line 1: FOR UPDATE clause allowed only for
DECLARE CURSOR.
Pls any body help...
Thank you,
Krist
Quite simple, really. SQL Server does not support FOR UPDATE; at least
not in the way that Oracle does. It's not standard SQL.
Alin,
The jTDS Project.
|||Hi ,
Thank you for your reply, it works now if I use JDBC driver from
Microsoft.
But if I use jTDS JDBC Driver, I get this error :
(oracle.jbo.DMLException) JBO-26041: Failed to post data to database
during "Update": SQL Statement
"UPDATE dbo.DEPARTMENTS SET DEPARTMENT_NAME=? WHERE DEPARTMENT_ID=?".
-- LEVEL 1: DETAIL 0 --
(java.sql.SQLException) Invalid parameter index 1.
Is there any options /setting on jDTS diver that I miss ?
Thank you for your help,
Krist
|||xtanto@.hotmail.com wrote:
> Hi ,
> Thank you for your reply, it works now if I use JDBC driver from
> Microsoft.
> But if I use jTDS JDBC Driver, I get this error :
> (oracle.jbo.DMLException) JBO-26041: Failed to post data to database
> during "Update": SQL Statement
> "UPDATE dbo.DEPARTMENTS SET DEPARTMENT_NAME=? WHERE DEPARTMENT_ID=?".
> -- LEVEL 1: DETAIL 0 --
> (java.sql.SQLException) Invalid parameter index 1.
> Is there any options /setting on jDTS diver that I miss ?
What does this have to do with FOR UPDATE?
Alin.
|||Hi Alin,
I am sorry, yes FOR UPDATE problem already solved after I upgrade to
JDev 10.1.2 and Oracle fix the problem by generating the correct sql
syntax for sqlserver.
However I still have problem with jTDS (described above).
I also post this in jTDS forum.
Thank you,
Krist
|||Could you enable DriverManager logging just before executing the code
that generates the exception and post the resulting log dump?
Alin.

Error "System.NullReferenceException" on Update

Hello Everyone,

I am getting this error "System.NullReferenceException: Object reference not set to an instance of an object." when i try to run the update query as in my following code.

When the new record condition is true then the code runs absolutely fine and inserts a new record to the database. However, when it goes in the edit mode to update the record it gives an error at '## ERROR POINT FOR THE UPDATE QUERY'. Why is this happening. i have attached the portion of my code below for refernce. Can someone please help me with this? Where am i going wrong. Seems like update query has a problem.

'-------------------------------
If mode = "edit" Then

insertSql = "update [Customer Info] set [Company Name]=@.CompanyName, [Business Type]=@.BusinessSector, [Address Line 1]=@.AddressLine1, [Address Line 2]=@.AddressLine2, [Address Line 3] = @.AddressLine3, [Town] = @.Town, [County]= @.County, [Post Code]=@.PostCode, [Country]=@.Country, [Telephone]=@.Telephone, [Fax]=@.Fax, [Email Address]=@.Email, [Contact Person]=@.ContactPerson, [Job Title]=@.JobTitle, [Website Address]=@.WebsiteAddress Where [Customer ID] = " & Request.QueryString("custid")

else

insertSql = "insert into [Customer Info] ([Customer ID], [Company Name], [Business Type], [Address Line 1], [Address Line 2], [Address Line 3], [Town], [County], [Post Code], [Country], [Telephone], [Fax], [Email Address], [Contact Person], [Job Title],[Website Address])"
insertSql = insertSql & " values (@.CustomerIDSq, @.CompanyName, @.BusinessSector, @.AddressLine1, @.AddressLine2, @.AddressLine3, @.Town, @.County, @.PostCode, @.Country, @.Telephone, @.Fax, @.Email, @.ContactPerson, @.JobTitle, @.WebsiteAddress)"

end if

objConn.open()
objCommand.CommandText = insertSql '## ERROR POINT FOR THE UPDATE QUERY
objCommand.Connection = objConn

objCommand.Parameters.Add("@.CustomerIDSq", SqlDbType.Int).Value = CustomerID
objCommand.Parameters.Add("@.CompanyName", SqlDbType.VarChar).Value = Trim(txtCompanyName.Text)
objCommand.Parameters.Add("@.BusinessSector", SqlDbType.Int).Value = BusinessSector
objCommand.Parameters.Add("@.AddressLine1", SqlDbType.VarChar).Value = Trim(txtAddressLine1.Text)
objCommand.Parameters.Add("@.AddressLine2", SqlDbType.VarChar).Value = Trim(txtAddressLine2.Text)
objCommand.Parameters.Add("@.AddressLine3", SqlDbType.VarChar).Value = Trim(txtAddressLine3.Text)
objCommand.Parameters.Add("@.Town", SqlDbType.VarChar).Value = Trim(txtTown.Text)
objCommand.Parameters.Add("@.County", SqlDbType.VarChar).Value = Trim(txtCounty.Text)
objCommand.Parameters.Add("@.PostCode", SqlDbType.VarChar).Value = Trim(txtPostCode.Text)
objCommand.Parameters.Add("@.Country", SqlDbType.VarChar).Value = Trim(txtCountry.Text)
objCommand.Parameters.Add("@.Telephone", SqlDbType.VarChar).Value = Trim(txtTelephone.Text)
objCommand.Parameters.Add("@.Fax", SqlDbType.VarChar).Value = Trim(txtFax.Text)
objCommand.Parameters.Add("@.Email", SqlDbType.VarChar).Value = Trim(txtEmail.Text)
objCommand.Parameters.Add("@.ContactPerson", SqlDbType.VarChar).Value = Trim(txtContactPerson.Text)
objCommand.Parameters.Add("@.JobTitle", SqlDbType.VarChar).Value = Trim(txtJobTitle.Text)
objCommand.Parameters.Add("@.WebsiteAddress", SqlDbType.VarChar).Value = Trim(txtWebsiteAddress.Text)

objCommand.executeNonQuery()
objConn.close()

'-------------------------------

Thanks,
SimShow us where you instantiate (new) the objCommand. That is likely the problem.|||Yea, that was the problem. i was not instantiating the command object. Now i did it and it is running very fine.

Thanks You very much, how these small things gets me.. i dunno.. :o.
Sim

Error "Incorrect syntax near (." when doing Update() from code, VB

Hi all

My error is as follows:

Incorrect syntax near '('.
Line 27: acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)
Line 28: acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)
Line 29: acceptOrDeclineFriendship.Update()
Line 30:
Line 31: End Sub

Bear with me... I have a page where i use a repeater control to list users who have requested to be friends with the currently online user. The 'getFriendRequests' query looks like this:

SelectCommand="SELECT * FROM Friends, UserDetails WHERE (Friends.UserID = UserDetails.UserID) AND (FriendID = @.UserID) AND (ApprovedByFriend = 'False') ORDER BY Friends.Requested DESC"
This works.

Within each repeater template, there are 2 buttons, 'Accept' or 'Decline', like this:

 <asp:Repeater ID="Repeater1" runat="server" DataSourceID="getFriendRequests"> <ItemTemplate> (other stuff like avatar and username etc) <asp:Button ID="accept" runat="server" Text="Accept" commandName="Accept" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/> <asp:Button ID="decline" runat="server" Text="Decline" commandName="Decline" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/> </ItemTemplate> </asp:Repeater>

The code-behind (VB) which deals with this is as follows:

Protected Sub Accept_Decline_Friends(ByVal senderAs Object,ByVal eAs CommandEventArgs)'retrieve id of requestee and the answer accept/declineDim friend_idAs String = e.CommandArgument.ToStringDim answerAs String = e.CommandName.ToString'add the parameters acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer) acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id) acceptOrDeclineFriendship.Update()End Sub

Since the buttons are being created dynamically, this is how i track 1. the response from the currently logged in user 'Accept/Decline' and 2. who they are responding to (by their uniqueid)

This relates to a sqlDataSource on my .aspx page like this:

<!-- update query when user has accepted the friendship --> <asp:SqlDataSource ID="acceptOrDeclineFriendship" runat="server" ConnectionString="<%$ xxx%>" UpdateCommand="UPDATE Friends SET (ApprovedByFriend = @.Response) WHERE (FriendID = @.UserID) AND (UserID = @.FriendID)"> <UpdateParameters> <asp:ControlParameter Name="UserID" ControlID="userIdValue" /> </UpdateParameters> </asp:SqlDataSource>

Which is meant to update my 'Friends' table to show that 'ApprovedByFriend' (the logged in user) is either 'Accept' or 'Decline', and record who's request was responded to.

I hope this is clear, just trying to suppy all of the information! The error appears to be saying that I have an issue with my code-behind, where i am telling the sqlDataSource above to UPDATE. What I can say is that for each button in the repeater, the 2 variables 'friend_id' and 'answer' are picking up the correct values.

Can anyone see any obvious problems here? Any help is very much appreciated as i am well and truley stuck!

Hi all

after all that I figured out it was a simple syntax issue.

UpdateCommand="UPDATE Friends SET ApprovedByFriend = @.Response WHERE (FriendID = @.UserID) AND (UserID = @.FriendID)">

I had some brackets around the SET condition

Error - updating tables row using SqlDataSource

I have such a problem:

i try to update a row in my table using:

protected void selectButton_Click(object sender, EventArgs e)
{
String taskID = projectsGridView.SelectedRow.Cells[0].Text;
usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@.task where [UserID]=1";
usersSqlDataSource.UpdateParameters.Add("task", taskID);
usersSqlDataSource.Update();
}

And i receive error on usersSqlDataSource.Update():

You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is empty

What have i done wrong? Parameter are not set?

Check ConflictDetection attribute in your DataSource declaration.

Based on your usage it should be

ConflictDetection="OverwriteChanges"

Refer to the following link to better understand the implications of this setting

http://msdn2.microsoft.com/en-US/library/system.web.ui.webcontrols.sqldatasource.conflictdetection.aspx

|||

One way to solve this is to change the ConflictDetection property of your datasource to OverWriteChanges. At the moment it is set at CompareAllValues. If you want to keep the CompareAllValues setting, you need to add the old Values for each parameter to the UpdateParameters collection too.http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.oldvaluesparameterformatstring(vs.80).aspx

|||

Yes, it works fine now, thanks!

ERR_SYSERR(104):PRM:SQLExecDirect failed (trigger)

The trigger fire correctly with insert new comment, but when user update a
comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDirect
failed
I create a trigger as following:
DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
UPDATE(comment4)
--Get Acct, Sub number
SELECT @.acct = memb.acct, @.sub = prof.no
FROM memb
JOIN prof ON memb.rowno = prof.rowno
--Get Comments
EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTPUT
--Update or insert into other server with linkserver
EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
Please help,
CulamI run an update statement in Query Analyzer, it works fine, but not in the
application.
"culam" wrote:

> The trigger fire correctly with insert new comment, but when user update a
> comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDire
ct
> failed
> I create a trigger as following:
> DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
> IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
> UPDATE(comment4)
> --Get Acct, Sub number
> SELECT @.acct = memb.acct, @.sub = prof.no
> FROM memb
> JOIN prof ON memb.rowno = prof.rowno
> --Get Comments
> EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTP
UT
> --Update or insert into other server with linkserver
> EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
> Please help,
> Culam|||On Mon, 28 Mar 2005 16:21:03 -0800, culam wrote:

>The trigger fire correctly with insert new comment, but when user update a
>comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDirec
t
>failed
>I create a trigger as following:
>DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
>IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
>UPDATE(comment4)
>--Get Acct, Sub number
>SELECT @.acct = memb.acct, @.sub = prof.no
>FROM memb
>JOIN prof ON memb.rowno = prof.rowno
>--Get Comments
>EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTPU
T
> --Update or insert into other server with linkserver
>EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
Hi Culam,
First, I'd like to point out two errors in your trigger code that are
probably not the cause of the error message you report (at least not
directly), but that are important nonetheless.
First: triggers should refer to the inserted and/or deleted pseudotables
to find out which rows were affected by the operation that fired the
trigger. If you refer to the base tables only, you're working on the
entire set of rows in the table, whether updated or not.
Second: triggers fire once per executed statement, not once per row
affected. Your code should ensure that it will also work properly if the
inserted and/or deleted pseudotables contain no rows or more than one
row. If you just use SELECT @.xxx = yyy FROM inserted to copy the new
data in a variable, SQL Server will pick (randomly) one of the new rows,
ignoring all others. It won't even raise an error or warning condition!!
Another (potential - I don't really know your requirements) flaw in your
code is the lack of BEGIN and END after the IF. Are you aware that you
need BEGIN and END if you want more than one statement to be executed
conditionally? Your current code is equivalent to
IF UPDATE(..) OR UPDATE(..) ...
BEGIN
-- Get Acct, Sub number
SELECT @.acct = ..., @.sub = ...
FROM ...
END
-- Get Comments
EXEC usp_LMA_CUBE_trig_comments ...
-- update or insert ...
EXEC usp_LMA_CUBE_upd_comments ...
If an update is executed but none of the columns comment1 through
comment4 is in the SET list, then the stored procedures will be executed
with @.acct and @.sub equal to NULL.
Now we get to your actual error message. I've never seen this message
before; in fact, I've never seen an error message on SQL Server that
uses this format. I suspect that your client software has done some
reformatting of the error message. Could you check the error message you
get when you run the same update statement from Query Analyzer?
I'm quite sure that the error is not raised in the code you posted. That
leaves the two stored procedures as possible offenders. But since you
didn't post the code of these procedures, I can't say any more than
that. Maybe the error will spontaneoously disappear once you fix the
other issues I've mentioned. If not, then I suggest you code the updated
trigger code plus the code of both stored procedures. Posting the DDL
(as CREATE TABLE statements) for all tables involved, plus some rows of
sample data (as INSERT statements) might help as well.
Oh, and by the way - have you considered normalizing your data, storing
the comments in a seperate table where they probably belong? Names like
comment1, comment2, comment3, comment4 r of a repeating group...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)