Footer rows to skip: update

Showing posts with label update. Show all posts

Tuesday, March 27, 2012

field changing unexpectedly

This is very frightening to me, a bit field completely unmentioned in an
update query keeps changing. There are no triggers, or other queries being
run.
I have a table with a bit field called "tc_active" nonull, default to (1).
The problem started on just one machine when I started editing the table,
inserted two new bit fields above tc_active, and enterprise manager crashed
while saving the table. I've since re-imported it from another server and
repeated the changes with no crash.
I run a query that updates many other fields in the table, but makes no
mention of tc_active. After it runs, tc_active has been changed to 0. I wish
I could pin down the causality, because I've tried splitting the query in
two, and each half, run independantly, does not cause the behavior.
I've tried renaming tc_active to tc_activeOLD, making a new tc_active field,
saving the table, editing it again, delete the new field and name the old
field back to tc_active, run the same problematic query again, and the
problem goes away.
however, if i re-import the table again from another server (which, by the
way doesn't share the problem) the problem will come back again.
I'm very shaken right now by this loss of faith in the correct operations of
the database... and this is a VERY difficult problem to websearch information
for, so I beg for someone's insight please.
-gIt is highly unlikely that the db itself is causing this but if you want to
be sure and the data is worth that much you should 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 MVP
"evilme" <evilme@.discussions.microsoft.com> wrote in message
news:12DFE953-EEC1-40D4-A372-3E112B3221BA@.microsoft.com...
> This is very frightening to me, a bit field completely unmentioned in an
> update query keeps changing. There are no triggers, or other queries being
> run.
> I have a table with a bit field called "tc_active" nonull, default to (1).
> The problem started on just one machine when I started editing the table,
> inserted two new bit fields above tc_active, and enterprise manager
> crashed
> while saving the table. I've since re-imported it from another server and
> repeated the changes with no crash.
> I run a query that updates many other fields in the table, but makes no
> mention of tc_active. After it runs, tc_active has been changed to 0. I
> wish
> I could pin down the causality, because I've tried splitting the query in
> two, and each half, run independantly, does not cause the behavior.
> I've tried renaming tc_active to tc_activeOLD, making a new tc_active
> field,
> saving the table, editing it again, delete the new field and name the old
> field back to tc_active, run the same problematic query again, and the
> problem goes away.
> however, if i re-import the table again from another server (which, by the
> way doesn't share the problem) the problem will come back again.
> I'm very shaken right now by this loss of faith in the correct operations
> of
> the database... and this is a VERY difficult problem to websearch
> information
> for, so I beg for someone's insight please.
> -g
>|||Is your server up-to-date on service packs? What does
SELECT @.@.VERSION return?
At least one vaguely similar bug was fixed some time ago:
http://support.microsoft.com/kb/294872; though yours is
not quite the same, it could be related.
If that's not it, can you post the CREATE TABLE and index
statements and the query that is giving you trouble?
Steve Kass
Drew University
evilme wrote:
>This is very frightening to me, a bit field completely unmentioned in an
>update query keeps changing. There are no triggers, or other queries being
>run.
>I have a table with a bit field called "tc_active" nonull, default to (1).
>The problem started on just one machine when I started editing the table,
>inserted two new bit fields above tc_active, and enterprise manager crashed
>while saving the table. I've since re-imported it from another server and
>repeated the changes with no crash.
>I run a query that updates many other fields in the table, but makes no
>mention of tc_active. After it runs, tc_active has been changed to 0. I wish
>I could pin down the causality, because I've tried splitting the query in
>two, and each half, run independantly, does not cause the behavior.
>I've tried renaming tc_active to tc_activeOLD, making a new tc_active field,
>saving the table, editing it again, delete the new field and name the old
>field back to tc_active, run the same problematic query again, and the
>problem goes away.
>however, if i re-import the table again from another server (which, by the
>way doesn't share the problem) the problem will come back again.
>I'm very shaken right now by this loss of faith in the correct operations of
>the database... and this is a VERY difficult problem to websearch information
>for, so I beg for someone's insight please.
> -g
>
>|||"Steve Kass" wrote:
> Is your server up-to-date on service packs? What does
> SELECT @.@.VERSION return?
well, my server's kept up to date, but it's only my devbox that's having
this problem. I get the following:
"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)"
> At least one vaguely similar bug was fixed some time ago:
> http://support.microsoft.com/kb/294872; though yours is
> not quite the same, it could be related.
it does seem very similar, thanks for this tip.
> If that's not it, can you post the CREATE TABLE and index
> statements and the query that is giving you trouble?
CREATE TABLE [t_title_co2] (
[tc_titleCoId] [int] IDENTITY (1, 1) NOT NULL ,
[tc_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_name2] DEFAULT (''),
[tc_address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_address2] DEFAULT (''),
[tc_city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_city2] DEFAULT (''),
[tc_state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_state2] DEFAULT (''),
[tc_zip] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_zip2] DEFAULT (''),
[tc_zip4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_zip42] DEFAULT (''),
[tc_phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_phone2] DEFAULT (''),
[tc_fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_fax2] DEFAULT (''),
[tc_webURL] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_webURL2] DEFAULT (''),
[tc_status] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_status2] DEFAULT (''),
[tc_bankName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_bankName2] DEFAULT (''),
[tc_bankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_bankCity2] DEFAULT (''),
[tc_ABA] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_ABA2] DEFAULT (''),
[tc_accountNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [tc_accountNum2] DEFAULT (''),
[tc_creditTo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_creditTo2] DEFAULT (''),
[tc_furtherCredit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [tc_furtherCredit2] DEFAULT (''),
[tc_furtherCreditNum] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [tc_furtherCreditNum2] DEFAULT (''),
[tc_insCloseLtr] [int] NOT NULL CONSTRAINT [tc_insCloseLtr2] DEFAULT (0),
[tc_pcFirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [tc_pcFirstName2] DEFAULT (''),
[tc_pcLastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [tc_pcLastName2] DEFAULT (''),
[tc_isTitleCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isTitleCo2]
DEFAULT (0),
[tc_isEscrowCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isEscrowCo2]
DEFAULT (0),
[tc_active] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_active2] DEFAULT (1)
) ON [PRIMARY]
GO
and the offending query:
UPDATE t_title_co2
SET
tc_state = 'IL',
tc_zip = '11111',
tc_phone = '(111) 111-1111',
tc_fax = '(111) 111-1111',
tc_bankName = 'demonstration Community Bank',
tc_bankCity = 'chicago',
tc_ABA = '111111111',
tc_accountNum = '11111111',
tc_creditTo = 'A Company, Inc.',
tc_furtherCredit = '',
tc_furtherCreditNum = '',
tc_insCloseLtr = '1',
tc_isTitleCo =1,
tc_isEscrowCo =1,
tc_pcFirstName = 'joe',
tc_pcLastName = 'smith'
WHERE (tc_titleCoId = 360)
aaand an insert line just to get a row of data in there
SET IDENTITY_INSERT t_title_co2 ON
INSERT INTO [DATABASENAME].[dbo].[t_title_co2]([tc_titleCoId], [tc_name],
[tc_address], [tc_city], [tc_state], [tc_zip], [tc_zip4], [tc_phone],
[tc_fax], [tc_webURL], [tc_status], [tc_bankName], [tc_bankCity], [tc_ABA],
[tc_accountNum], [tc_creditTo], [tc_furtherCredit], [tc_furtherCreditNum],
[tc_insCloseLtr], [tc_pcFirstName], [tc_pcLastName], [tc_isTitleCo],
[tc_isEscrowCo], [tc_active])
VALUES(360, 'moo', '222', 'here', 'mn', '44444', '', '', '', '', '', '', '',
'', '', '', '', '', 0, '', '', 0, 0, 1)
SET IDENTITY_INSERT t_title_co2 OFF
the query doesn't have the problem if i remove the tc_state line, or both
the _isEscrowCo and _isTitleCo lines, or several other things that have no
rhyme or reason related to tc_active.
thanks again
-g|||You should install Service Pack 3a on your development machine.
Not only will this fix a number of bugs, it will protect you from
security vulnerabilities, including the SQL Slammer work, which
you are not protected against.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
If you still have the problem after upgrading, let us know.
When I run this on an up-to-date server, the value of tc_active
remains equal to 1 after the update.
SK
evilme wrote:
>"Steve Kass" wrote:
>
>>Is your server up-to-date on service packs? What does
>>SELECT @.@.VERSION return?
>>
>well, my server's kept up to date, but it's only my devbox that's having
>this problem. I get the following:
>"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
>Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
>NT 5.1 (Build 2600: Service Pack 1)"
>
>>At least one vaguely similar bug was fixed some time ago:
>>http://support.microsoft.com/kb/294872; though yours is
>>not quite the same, it could be related.
>>
>it does seem very similar, thanks for this tip.
>
>>If that's not it, can you post the CREATE TABLE and index
>>statements and the query that is giving you trouble?
>>
>CREATE TABLE [t_title_co2] (
> [tc_titleCoId] [int] IDENTITY (1, 1) NOT NULL ,
> [tc_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_name2] DEFAULT (''),
> [tc_address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_address2] DEFAULT (''),
> [tc_city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_city2] DEFAULT (''),
> [tc_state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_state2] DEFAULT (''),
> [tc_zip] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_zip2] DEFAULT (''),
> [tc_zip4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_zip42] DEFAULT (''),
> [tc_phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_phone2] DEFAULT (''),
> [tc_fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_fax2] DEFAULT (''),
> [tc_webURL] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_webURL2] DEFAULT (''),
> [tc_status] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_status2] DEFAULT (''),
> [tc_bankName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_bankName2] DEFAULT (''),
> [tc_bankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_bankCity2] DEFAULT (''),
> [tc_ABA] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_ABA2] DEFAULT (''),
> [tc_accountNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL CONSTRAINT [tc_accountNum2] DEFAULT (''),
> [tc_creditTo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_creditTo2] DEFAULT (''),
> [tc_furtherCredit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL CONSTRAINT [tc_furtherCredit2] DEFAULT (''),
> [tc_furtherCreditNum] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
>NOT NULL CONSTRAINT [tc_furtherCreditNum2] DEFAULT (''),
> [tc_insCloseLtr] [int] NOT NULL CONSTRAINT [tc_insCloseLtr2] DEFAULT (0),
> [tc_pcFirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL CONSTRAINT [tc_pcFirstName2] DEFAULT (''),
> [tc_pcLastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL CONSTRAINT [tc_pcLastName2] DEFAULT (''),
> [tc_isTitleCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isTitleCo2]
>DEFAULT (0),
> [tc_isEscrowCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isEscrowCo2]
>DEFAULT (0),
> [tc_active] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_active2] DEFAULT (1)
>) ON [PRIMARY]
>GO
>and the offending query:
>UPDATE t_title_co2
>SET
>tc_state = 'IL',
>tc_zip = '11111',
>tc_phone = '(111) 111-1111',
>tc_fax = '(111) 111-1111',
>tc_bankName = 'demonstration Community Bank',
>tc_bankCity = 'chicago',
>tc_ABA = '111111111',
>tc_accountNum = '11111111',
>tc_creditTo = 'A Company, Inc.',
>tc_furtherCredit = '',
>tc_furtherCreditNum = '',
>tc_insCloseLtr = '1',
>tc_isTitleCo =1,
>tc_isEscrowCo =1,
>tc_pcFirstName = 'joe',
>tc_pcLastName = 'smith'
>WHERE (tc_titleCoId = 360)
>aaand an insert line just to get a row of data in there
>SET IDENTITY_INSERT t_title_co2 ON
>INSERT INTO [DATABASENAME].[dbo].[t_title_co2]([tc_titleCoId], [tc_name],
>[tc_address], [tc_city], [tc_state], [tc_zip], [tc_zip4], [tc_phone],
>[tc_fax], [tc_webURL], [tc_status], [tc_bankName], [tc_bankCity], [tc_ABA],
>[tc_accountNum], [tc_creditTo], [tc_furtherCredit], [tc_furtherCreditNum],
>[tc_insCloseLtr], [tc_pcFirstName], [tc_pcLastName], [tc_isTitleCo],
>[tc_isEscrowCo], [tc_active])
>VALUES(360, 'moo', '222', 'here', 'mn', '44444', '', '', '', '', '', '', '',
>'', '', '', '', '', 0, '', '', 0, 0, 1)
>SET IDENTITY_INSERT t_title_co2 OFF
>
>the query doesn't have the problem if i remove the tc_state line, or both
>the _isEscrowCo and _isTitleCo lines, or several other things that have no
>rhyme or reason related to tc_active.
>thanks again
> -g
>|||> If you still have the problem after upgrading, let us know.
> When I run this on an up-to-date server, the value of tc_active
> remains equal to 1 after the update.
well, the service pack fixed the problem.
i suspected it would since my kept-updated server was fine, and my
devmachine had such odd behavior. I really should have done that sooner, even
if it is an offline devmachine.
ahh, it's nice to have faith in the database again.
thanks for the help--you're a peach ^^
-g

Field attribute question

If I want to store day of the week (i.e. Mon), do I use datetime field or char? Also if I update #25/12/2004# to a datetime field. What is the value is the time port of the field? Thx.I would calculate the day of the week from a datetime, if you were also saving the datetime. If you are saving ONLY a day of week, then I would use a number, etc.

With only a date stored in a DateTime, the time would be 00:00:00.000|||I created two fields in a sql table, one for storing date and one for time. In my VB, I populated them with #3/8/2005# and #10:00:00 PM# respectively. The program crashed with message "sqldattime overflow. Must be between 1/1/1753 12:00:00 AM ...." Why did the program crash? Should I combine two fields into one?|||Show some code.|||In my aspx form I created one text field and one drop down list. The value in the text field is pulled from MS calendar control so it is like 3/10/2005 and the ddl is filled with time in every 15 min. e.g. 10:00, 10:15.
In my vb code, I coded as following:


...
reservation.requesteddate=cdate(txtbkdate.text)
reservation.requestedtime=ddlRequestedTime.SelectedItem.Text
...
reservation.insert()

I also did some experiment in which I created a temp table and a smalldatetime field and I entered only 10:00. When I debugged, I found it actually stored a date and time and the date value is one day (can't remember exactly) before the valid range.

Should I combine two fields into only one? Thanks.|||DateTime fields ALWAYS contains a date and a time. You can use code to split them for independent display, if you wish. I certainly would combine them in a single field if they refer to the same event.|||Thanks, Doug. Yes, they do refer to the same event. What I intended to do is a field for booked date and another for booked time. I though by splitting into two fields, when I create tree view for booking for a particular date will be comparatively easier. But still, apart from such reason, if I do have a need to only store a time value in a datatime field, what is the proper way?|||The reality is, you ALWAYS store both, and then use client side (in this case, ASP.NET) formatting to show the data you want. Alternately, you can use SQL code to get just the date or time (see CONVERT() in SQL Server Books Online).|||Doug, here is another real situation I have, can I have your comments.
I have a table to store my restaurant's info and I need to store the business hours. I created two fields to store from and to. Before I create a form to maintain the info of this table, I simply input data from vs2003 and it did accept my entry (only 10:00 entered to a smalldatetime field). Now if I create a form for data entry, apparently it makes no sense to ask user to input date and time, so I have to do something in the vb code. But which is a more sensible way to do so? Should I just concatenate any date with the time entered by user and update it to sql table? How do other developers normally do? Since I'm developer from other platform, may be my though is totally unlogically...|||I would likely concatentate the date and time, then parse it into a single DateTime (I generally do not use SmallDateTime's). The question is, is the date and time a single attribute? Meaning, are you talking about 3/13/2005 10:15 AM as a single thing, so that at some point, you might want to find a row based upon that date/time combination.|||Sorry I may have confused you. Put it in a simply way, what I need to store is just only a time, the date is meaningless to me. So really I don't need to store a date to the database but according to what you said, the date portion is required when popuplating the database. It means that I have to put in a meaningless date even I don't need it. The way I see is if documentation is not done properly or the field is not named meaningfully then other programmers may be misled by the date when examining data. I hope this is clear for you to understand my point.

Monday, March 19, 2012

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA

Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
statements as an alternative to strict UPDATES to TEXT data type attributes.
Sincerely,
Anthony Thomas

"Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA

Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
|||this could be to do with the following
1) the collation on your database is/was different to the server collation
2) is the column in question text(this data type could cause the issue
mentioned) and not ntext
can you confirm that the above is not true?
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uyDxQIX7EHA.2572@.tk2msftngp13.phx.gbl...
> Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
> statements as an alternative to strict UPDATES to TEXT data type
attributes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
> news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
> Hi,
> I am receiving the following error when trying to use an 'instead of'
update
> trigger.
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> It looks like something to do with calling stored procedures with text
> fields. The following test case illustrates the problem:
> CREATE TABLE A
> (Field1 int,
> Field2 text)
> GO
>
> CREATE TRIGGER A_UpdateTrig
> ON A
> INSTEAD OF UPDATE
> AS
> UPDATE A
> SET Field1 = i.Field1,
> Field2 = i.Field2
> FROM
> A JOIN inserted i ON (A.Field1 = i.Field1)
>
> GO
>
> INSERT INTO A VALUES (1, 'aaa')
> INSERT INTO A VALUES (2, 'bbb')
> GO
> CREATE PROCEDURE UpdateA
> @.Field1 int,
> @.Field2 text
> AS
>
> UPDATE A
> SET Field2 = @.Field2
> WHERE
> Field1 = @.Field1
> return 1
> GO
>
> -- Error occurs here:
> exec UpdateA 2, N'cccc'
>
> -- No error occurs here:
> DROP TRIGGER A_UpdateTrig
> exec UpdateA 2, N'ddd'
> DROP TABLE A
> DROP PROCEDURE UpdateA
>
> --
> Running this produces the following result:
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
> Any ideas would be appreciated. BTW select @.@.version returns:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
|||Hi Michael,
I alredy apply the hotfix in my test machine but still I received the error :
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 91 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
What else can I do?
Thanks in advance :=)

Quote:

Originally posted by Michael Cheng [MSFT]
Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionChec
kForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/defaul...scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
statements as an alternative to strict UPDATES to TEXT data type attributes.
Sincerely,
Anthony Thomas
"Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionChec
kForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)|||this could be to do with the following
1) the collation on your database is/was different to the server collation
2) is the column in question text(this data type could cause the issue
mentioned) and not ntext
can you confirm that the above is not true?
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uyDxQIX7EHA.2572@.tk2msftngp13.phx.gbl...
> Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
> statements as an alternative to strict UPDATES to TEXT data type
attributes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
> news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
> Hi,
> I am receiving the following error when trying to use an 'instead of'
update
> trigger.
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> It looks like something to do with calling stored procedures with text
> fields. The following test case illustrates the problem:
> CREATE TABLE A
> (Field1 int,
> Field2 text)
> GO
>
> CREATE TRIGGER A_UpdateTrig
> ON A
> INSTEAD OF UPDATE
> AS
> UPDATE A
> SET Field1 = i.Field1,
> Field2 = i.Field2
> FROM
> A JOIN inserted i ON (A.Field1 = i.Field1)
>
> GO
>
> INSERT INTO A VALUES (1, 'aaa')
> INSERT INTO A VALUES (2, 'bbb')
> GO
> CREATE PROCEDURE UpdateA
> @.Field1 int,
> @.Field2 text
> AS
>
> UPDATE A
> SET Field2 = @.Field2
> WHERE
> Field1 = @.Field1
> return 1
> GO
>
> -- Error occurs here:
> exec UpdateA 2, N'cccc'
>
> -- No error occurs here:
> DROP TRIGGER A_UpdateTrig
> exec UpdateA 2, N'ddd'
> DROP TABLE A
> DROP PROCEDURE UpdateA
>
> --
> Running this produces the following result:
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCh
eckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
> Any ideas would be appreciated. BTW select @.@.version returns:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
--
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
statements as an alternative to strict UPDATES to TEXT data type attributes.
Sincerely,
Anthony Thomas
"Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)|||this could be to do with the following
1) the collation on your database is/was different to the server collation
2) is the column in question text(this data type could cause the issue
mentioned) and not ntext
can you confirm that the above is not true?
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uyDxQIX7EHA.2572@.tk2msftngp13.phx.gbl...
> Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
> statements as an alternative to strict UPDATES to TEXT data type
attributes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
> news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
> Hi,
> I am receiving the following error when trying to use an 'instead of'
update
> trigger.
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> It looks like something to do with calling stored procedures with text
> fields. The following test case illustrates the problem:
> CREATE TABLE A
> (Field1 int,
> Field2 text)
> GO
>
> CREATE TRIGGER A_UpdateTrig
> ON A
> INSTEAD OF UPDATE
> AS
> UPDATE A
> SET Field1 = i.Field1,
> Field2 = i.Field2
> FROM
> A JOIN inserted i ON (A.Field1 = i.Field1)
>
> GO
>
> INSERT INTO A VALUES (1, 'aaa')
> INSERT INTO A VALUES (2, 'bbb')
> GO
> CREATE PROCEDURE UpdateA
> @.Field1 int,
> @.Field2 text
> AS
>
> UPDATE A
> SET Field2 = @.Field2
> WHERE
> Field1 = @.Field1
> return 1
> GO
>
> -- Error occurs here:
> exec UpdateA 2, N'cccc'
>
> -- No error occurs here:
> DROP TRIGGER A_UpdateTrig
> exec UpdateA 2, N'ddd'
> DROP TABLE A
> DROP PROCEDURE UpdateA
>
> --
> Running this produces the following result:
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
> Any ideas would be appreciated. BTW select @.@.version returns:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not

exist" by any method other than checking to see if it exists first.

The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not exist" by any method other than checking to see if it exists first.
The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

|||

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

Fatal Error

Need help,

I am trying to update my server connection ( Microsoft.AnalysisServices.Server.update ( ) )and got the following error message:

Microsoft.AnalysisServices.OperationException: Fatal internal error.

at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadRespon
se(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfEr
ror)
at Microsoft.AnalysisServices.AnalysisServicesClient.Alter(IMajorObject obj,
ObjectExpansion expansion, ImpactDetailCollection impact, Boolean allowCreate)
at Microsoft.AnalysisServices.Server.Update(IMajorObject obj, UpdateOptions o
ptions, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection
impactResult)
at Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj, UpdateOptio
ns options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollect
ion impactResult)
at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options, Updat
eMode mode, XmlaWarningCollection warnings)
at Microsoft.AnalysisServices.MajorObject.Update()
at Test.Program.Main(String[] args) in E:\Programming\C#\Test\Test\Program.cs
:line 64

Please helpp ...

THanks ... :)

See if this is the same issue as reported in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=317697&SiteID=1

Another idea. Try and run SQL Profiler and monitor activity while your application is issuing the request to update. See if there is any error message.

One more thing to try: Execute the update statement with Database object and not the server object.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I've tried to update from database level and there was no error found. Thanks.

One more thing i would like to know is, what's the different between

DimensionAttribute.KeyColumns and DimensionAttribute.NameColumn. I've

searched from the internet and still not found any good answer that can

make me understand the difference. I also got no explanation from MSDN

( I don't know if there is update in MSDN that explain about it more

detail ).

Thanks in advance ....|||

To answer your DimensionAttribute.KeyColumn vs DimensionAttribute.NameColumn.

These allow you to load key and name for dimension member separately. Imagine dimension members can be translated to different languages, you need a key to be able to reference a sinlge memeber. That is only a single example, there are many cases where you need to have key and name separate.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Need Help ...
Why this error can occure?

Microsoft.AnalysisServices.OperationException: Errors in the metadata manager. T
he Branch dimension has either zero or multiple key attributes.

This is the part of my source code:

Server aServer = new Server ( );
Database aDatabase = null;
Cube aCube = null;
CubeDimension aCubeDim = null;
Dimension aDimension = null;
DimensionAttribute anAttribute = null;
MeasureGroup mg = null;
Measure aMeasure = null;
Hierarchy hier;
RegularMeasureGroupDimension regMgDim;
ManyToManyMeasureGroupDimension mmMgDim;
MeasureGroupAttribute mgAttr;

String strProvider = null;
String strDataSource = null;
String strSourceDSN = null;

String CubePath = "E:\\Programming\\C#\\Test\\Test\\TEHSuite.cub";

if ( File.Exists ( CubePath ) )
File.Delete ( CubePath );

strProvider = "PROVIDER=MSOLAP";
strDataSource = "Data Source=" + CubePath;
strSourceDSN = "SOURCE_DSN=TEHSuite";

try
{
aServer.Connect ( strProvider + ";" + strDataSource + ";" + strSourceDSN + ";" );

aDatabase = aServer.Databases.FindByName ( "TEHSuite" );

if ( aDatabase != null )
aDatabase.Drop ( );

aDatabase = aServer.Databases.Add ( "TEHSuite" );
aDatabase.ID = "TEHSuite";
aDatabase.Update ( );

aCube = aDatabase.Cubes.FindByName ( "Sales" );

if ( aCube != null )
aCube.Drop ( );

aCube = new Cube ( );
aCube.Name = "Sales";
aCube.StorageMode = StorageMode.Molap;

aDatabase.Cubes.Add ( aCube );

aDimension = aDatabase.Dimensions.Add ( "Branch" );
aDimension.UnknownMember = UnknownMemberBehavior.Hidden;
aDimension.AttributeAllMemberName = "All Branch";

anAttribute = new DimensionAttribute ( );
anAttribute.ID = "Branch Name";
anAttribute.Name = "Branch Name";
anAttribute.KeyColumns.Add ( new DataItem ( "DimBranch" , "BranchID" ) );
anAttribute.NameColumn = new DataItem ( "DimBranch" , "BranchName" );
aDimension.Attributes.Add ( anAttribute );

anAttribute = new DimensionAttribute ( );
anAttribute.ID = "City Name";
anAttribute.Name = "City Name";
anAttribute.KeyColumns.Add ( new DataItem ( "DimCity" , "CityID" ) );
anAttribute.NameColumn = new DataItem ( "DimCity" , "CityName" );
aDimension.Attributes.Add ( anAttribute );

anAttribute = new DimensionAttribute ( );
anAttribute.ID = "Country Name";
anAttribute.Name = "Country Name";
anAttribute.KeyColumns.Add ( new DataItem ( "DimCountry" , "CountryID" ) );
anAttribute.NameColumn = new DataItem ( "DimCountry" , "CountryName" );
aDimension.Attributes.Add ( anAttribute );

anAttribute = new DimensionAttribute ( );
anAttribute.ID = "Continent Name";
anAttribute.Name = "Continent Name";
anAttribute.KeyColumns.Add ( new DataItem ( "DimContinent" , "ContinentID" ) );
anAttribute.NameColumn = new DataItem ( "DimContinent" , "ContinentID" );
aDimension.Attributes.Add ( anAttribute );

hier = aDimension.Hierarchies.Add ( "Branch Categories" );
hier.AllMemberName = "All Products";
hier.Levels.Add ( "Branch Name" ).SourceAttributeID = "Branch Name";
hier.Levels.Add ( "City Name" ).SourceAttributeID = "City Name";
hier.Levels.Add ( "Country Name" ).SourceAttributeID = "Country Name";
hier.Levels.Add ( "Continent Name" ).SourceAttributeID = "Continent Name";

aDimension.Update ( );
aCube.Dimensions.Add ( aDimension.ID );

Thanks ...

Monday, March 12, 2012

fastest way to do large amounts of updates

I was wondering what is the fastest way to UPDATE lots of recods. I heard the fastest way to perform lots of inserts in to use SqlCeResultSet. Would this also be the fastest way to update already existing records? If so, is this the fastest way to do that:

1. Create a SqlCeCommand object.
2. Set the CommandText to select the datat I want to update
3. Call the command object's ExecuteResultSet method to create a SqlCeResultSet object
4. Call the result set object's Read method to advance to the next record
5. Use the result set object to update the values using the SqlCeResultSet.SetValue method and the Update method.
6. repeat steps 4 and 5

Also I was wondering do call the SqlCeResultSet.Update method once per row, or just once? Also would it be possible and faster to wrap all that in a transaction?

Would parameterized updates be faster?
Any help will be appreciated.
To answer some of my own questions, for an SqlCeResultSet object, you must call the Update function once per row. Also you can wrap it in a transaction, but that will probably slow the process down, although this may still be a good idea.

My main question still remains unanswerd: What is the fastest way to do large amounts of updates? I will be running some tests soon and will post my results here.
|||

Some things you can do to improve update performance:

1. make your update statement a parameterized query, prepare it, and reuse it for each update, changing only the parameter values

2. keep indexes on the table to a minimum (or even remove them in extreme cases - then readd them after the updates complete)

3. SqlCeResultSet is the fastest mechanism if ou are using CF2 and SQL Mobile - yes, you call update on each row

-Darren

Friday, March 9, 2012

Fastest way of updating a row

In relation to my last post, I have a question for the SQL-gurus.

I need to update 70k records, and mark all those updated in a special
column for further processing by another system.

So, if the record was

Key1, foo, foo, ""

it needs to become

Key1, fap, fap, "U"

iff and only iff the datavalues are actually different (as above, foo
becomes fap),

otherwise it must become

Key1, foo,foo, ""

Is it quicker to :
1) get the row of the destination table, inspect all values
programatically, and determine IF an update query is needed

2) just do a update on all rows, but adding
and (field1 <> value1 or field2<>value2) to the update query

that is
update myTable
set
field1 = "foo"
markField="u"
where key="mykey" and (field1 <> foo)

The first one will not generate new update queries if the record has
not changed, on account of doing a select, whereas the second version
always runs an update, but some of them will not affect any lines.

Will I need a full index on the second version?

Thanks in advance,
Asger Henriksen[posted and mailed, vnligen svara i nys]

Asger Jensen (akj@.tmnet.dk) writes:
> Is it quicker to :
> 1) get the row of the destination table, inspect all values
> programatically, and determine IF an update query is needed
> OR
> 2) just do a update on all rows, but adding
> and (field1 <> value1 or field2<>value2) to the update query
> that is
> update myTable
> set
> field1 = "foo"
> markField="u"
> where key="mykey" and (field1 <> foo)

I'm not sure that I follow, but it sounds to me that the in first
approach you would retrieve rows one by one.

In any case, the second approach leaves all the jub to the computer,
and there is a reason why we have computers, isn't there? :-)

The only catch is that with too many rows in the table there can be
a strain on the transaction log. But with only 70000 rows, this is
not worth worrying about.

Obviously there query will run faster if there is a clustered index
on the column "key".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I'm not sure that I follow, but it sounds to me that the in first
> approach you would retrieve rows one by one.

Yes, thats what it was. Nasty.
> In any case, the second approach leaves all the jub to the computer,
> and there is a reason why we have computers, isn't there? :-)
:-), yes, only in my scenario it took soooo long.

> The only catch is that with too many rows in the table there can be
> a strain on the transaction log. But with only 70000 rows, this is
> not worth worrying about.

ok,

> Obviously there query will run faster if there is a clustered index
> on the column "key".
There was, actually, but during the 70000 records it became slower and
slower. I solved it by adding a dynamically generated index on ALL
fields, not just "key", this sped things up considerably, so I went
from 1 hour to 10 minutes.

I guess it is due to the server needing to do a lookup/record on all
value fields to see if they have changed, and this can be done more
efficiently with a n index.

Regards
Asger

Fastest Update/Insert Method

There is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter

>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...[vbcol=seagreen]
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> to perform monthly
> test to check if the
> may either perform
> ways for the
> records in the current
> as an insert. This
> are any tricks or|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
>

Fastest Update/Insert Method

There is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter

>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>
The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...[vbcol=seagreen]
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> to perform monthly
> test to check if the
> may either perform
> ways for the
> records in the current
> as an insert. This
> are any tricks or
|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
>

Fastest Update/Insert Method

I have a large database of several hundred gig and I need to perform monthly
updates of about 4 gigs of that data. However I have to test to check if the
update records exists in the current database so that I may either perform
an update or an insert. I've tried all kind of different ways for the
update, but the fastest appears to be to delete all records in the current
database that exist in the update and then do everything as an insert. This
still takes 30 hours to complete. I need to know if there are any tricks or
tips for updating large databases more quickly.
TIAThere is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter
>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>|||The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> >--Original Message--
> >I have a large database of several hundred gig and I need
> to perform monthly
> >updates of about 4 gigs of that data. However I have to
> test to check if the
> >update records exists in the current database so that I
> may either perform
> >an update or an insert. I've tried all kind of different
> ways for the
> >update, but the fastest appears to be to delete all
> records in the current
> >database that exist in the update and then do everything
> as an insert. This
> >still takes 30 hours to complete. I need to know if there
> are any tricks or
> >tips for updating large databases more quickly.
> >
> >TIA
> >
> >
> >.
> >|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
--
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
> > There is probably 1001 tips, but here are the two I know.
> >
> > I Remove all foreign keys before performing the insert /
> > update, if you need to know why then replay, nb I have not
> > included Primary key as you will need it to check if
> > record exists ;)
> >
> > The second thing is to perform the update on the same
> > server, taking out the network. What I mean is this if 4gb
> > on server B is to copied onto 5gb on Server A, then it
> > will take less time if you write it to a file on server B,
> > compress it, send it Server A, uncompress it, load it into
> > a temporary table then perform the SQL.
> >
> > Ok I now have a stupid question, did you try the
> >
> > INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> > (SELECT PrimaryKey from TEMPTABLE) ?
> >
> > Hope this helps
> > Peter
> >
> >
> > >--Original Message--
> > >I have a large database of several hundred gig and I need
> > to perform monthly
> > >updates of about 4 gigs of that data. However I have to
> > test to check if the
> > >update records exists in the current database so that I
> > may either perform
> > >an update or an insert. I've tried all kind of different
> > ways for the
> > >update, but the fastest appears to be to delete all
> > records in the current
> > >database that exist in the update and then do everything
> > as an insert. This
> > >still takes 30 hours to complete. I need to know if there
> > are any tricks or
> > >tips for updating large databases more quickly.
> > >
> > >TIA
> > >
> > >
> > >.
> > >
>

faster page update using SQL Server data

Hi,

What is the fastest way to get informations from a SQL BD (using stored procedure) in a WEB Page ?

This web page need to be updated EVERY SECONDE !
Javacript / OleDB / SQLConnection / ... ?

I plan to use a a usercontrol containing the informations, am I right ?

thank you for your help,You can get HTML code directly using the Web Tasks functionality in SQL Server ... I think this is the easiest and the fastest way to get web pages directly from SQL Server ...

Refer to SQL Server BOL for further info ...

Wednesday, March 7, 2012

Fast updates in SQL Server

Hi
In Oracle there is a concept that allows one to perform an update without
using the rollback logs so you can try to improve the performance of an
update. Does such functionality exist in SQL Server 2000? I cannot seem to
find anything on it in BOL
Thanks
NHi
In SQL Server every DML operations are logged. If you explain us a little
bit more about your requiremnts we will be able to help you .
How much data are you going to update?
Do you have any indexes defined on the table?
"Nesaar" <nesaarATprescientdotcodotza> wrote in message
news:%23GNz2I$HGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi
> In Oracle there is a concept that allows one to perform an update without
> using the rollback logs so you can try to improve the performance of an
> update. Does such functionality exist in SQL Server 2000? I cannot seem to
> find anything on it in BOL
> Thanks
> N
>
>|||Look at this thread there, for some operations there is a minimized
transaction protocol, like TRUNCATE. Other details were discussed in
here:
http://www.mcse.ms/archive89-2005-2-1441624.html
HTH, jens Suessmeyer.|||He has a temp table that has about 25 million rows in it. This is joined to
a transaction table with about 110 million rows on it. The transaction table
has a clustered index and a few other indexes as well.
I have just told the developer he should create an index on his temporary
table for the columns he is joining on as a first step to try and increase
performance.
Thanks
N
I've created a temp table to store transactions (+-25mill rows)
then it joins from there onto our transaction table (110 mill rows; lots of
indexes) to update.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uhRnAM$HGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi
> In SQL Server every DML operations are logged. If you explain us a little
> bit more about your requiremnts we will be able to help you .
> How much data are you going to update?
> Do you have any indexes defined on the table?
>
>
> "Nesaar" <nesaarATprescientdotcodotza> wrote in message
> news:%23GNz2I$HGHA.3936@.TK2MSFTNGP12.phx.gbl...
without
to
>

Sunday, February 26, 2012

faily to update record

update arinvchg set chgcode =
(select pTable.chgcode from pTable ,arinvchg where pTable.oldchgcode =
arinvchg.chgcode)
As i process the above statment, I got an error about " ld{^_?h_@.
?C?ld{b =B!=B<B<=B>B>= ZAΤld{Χ@.}?A?GO
?C
?yw?C"
[In English, it said , (it return more than one subquery ... etc) _How about
I assume that chgcode is an unique
update arinvchg set chgcode =
(select chgcode from pTable
where pTable.oldchgcode = arinvchg.chgcode)
where exists (select * from pTable where
pTable.oldchgcode = arinvchg.chgcode)
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:uC2gcq9SGHA.4340@.tk2msftngp13.phx.gbl...
> update arinvchg set chgcode =
> (select pTable.chgcode from pTable ,arinvchg where pTable.oldchgcode =
> arinvchg.chgcode)
> As i process the above statment, I got an error about "
> ld{^_?h_@.?C?ld{b =B!=B<B<=B>B>=
> ZAΤld{Χ@.}?A?GO?C
> ?yw?C"
> [In English, it said , (it return more than one subquery ... etc) _
>
>|||Hi,
As you didn=B4t explain where the new value should come from, this below
is one of the possible solutions (as your new value for the rest of the
id would be static)
UPDATE SomeTable
SET nameid =3D
YOurnewValue + RIGHT(nameid,18)
HTH, jens Suessmeyer.
http://www.sqlserver2005.de|||On Mon, 20 Mar 2006 12:56:12 +0800, Agnes wrote:

>update arinvchg set chgcode =
>(select pTable.chgcode from pTable ,arinvchg where pTable.oldchgcode =
>arinvchg.chgcode)
>As i process the above statment, I got an error about " ld{^_?h_
@.?C?ld{b =B!=B<B<=B>B>= ZAΤld{Χ@.?}?A?GO
?C
>?yw?C"
>[In English, it said , (it return more than one subquery ... etc) _
>
Hi Agnes,
Hard to say what you should change, since you didn;t post anything about
your tables, data and expected results. Please check www.aspfaq.com/5006
to learn a better way to ask for help in this group.
Anyway, here's a completely wild guess at what might possibly be your
solution:
UPDATE arinvchg
SET chgcode = (SELECT pTable.chgcode
FROM pTable
WHERE pTable.oldchgcode = arinvchg.chgcode)
Hugo Kornelis, SQL Server MVP

Friday, February 24, 2012

Failure with transactional replication with immediate updating subscriptions

I have set up a transactional replication with immediate updating
subscriptions,
Distributor and Subscriber on diff servers.
When I try to update rows in the subscriber I have the following error:
[Microsoft][ODBC SQL Server Driver][SQLServer] Login failed for user 'sa'
(Both servers have the same 'sa' password)
Does anybody know how can I do that ?
Thank you
Hernn Rado
review this kb article:
http://support.microsoft.com/default...b;en-us;320773
"Hernn Rado" <hernan_radovitzki@.hotmail.com> wrote in message
news:eAt7HgE6EHA.2592@.TK2MSFTNGP09.phx.gbl...
> I have set up a transactional replication with immediate updating
> subscriptions,
> Distributor and Subscriber on diff servers.
>
> When I try to update rows in the subscriber I have the following error:
> [Microsoft][ODBC SQL Server Driver][SQLServer] Login failed for user 'sa'
> (Both servers have the same 'sa' password)
> Does anybody know how can I do that ?
> Thank you
> Hernn Rado
>
|||Thanks Hilary !
I changed the SA password to a blank one and its works ! Later, I changed
again both password (to the original) and ran the sp_link_publication on the
subscriber (specifying the SA psw) but its doesnt work !!
What can I do?
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:O5fVNsE6EHA.248@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> review this kb article:
> http://support.microsoft.com/default...b;en-us;320773
> "Hernn Rado" <hernan_radovitzki@.hotmail.com> wrote in message
> news:eAt7HgE6EHA.2592@.TK2MSFTNGP09.phx.gbl...
'sa'
>

Failure when installing SQL 2005 SP1 on Windows 2003 Server

Hello.

When I installed SQL 2005 SP1 on Windows 2003 Server, I received the following message:

"A recently applied update, KB913090, failed to install."

At the end, "Database Services" was marked as "Failure".

I have tried everything I could find in the knowledge base and on the forums, but still

was not able to install SP1.

For example:

http://support.microsoft.com/default.aspx?scid=kb;en-us;918357

Any insights would be greatly appreciated.

Can you search your hotfix logs (%WINDIR%/hotfix directory) for the text string "value 3" and include the 10-20 lines above it? That should give us a more descriptive error.

Thanks,
Sam Lester (MSFT)|||

MSI (s) (B0!C0) [22:08:08:355]: Note: 1: 2262 2: _sqlAction 3: -2147287038
MSI (s) (B0!C0) [22:08:08:355]: Transforming table _sqlAction.

MSI (s) (B0!C0) [22:08:08:355]: Note: 1: 2262 2: _sqlAction 3: -2147287038
<Func Name='SetCAContext'>
<EndFunc Name='SetCAContext' Return='T' GetLastError='0'>
Doing Action: CommitSqlUpgrade
PerfTime Start: CommitSqlUpgrade : Sat Sep 23 22:08:08 2006
<Func Name='ComponentUpgrade'>
There was a failure during installation search up in this log file for this message:
SQL Server Setup failed to parse the SQL script "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Upgrade\procsyst.sql". The error code is The system cannot find the file specified.
. To continue, correct the problem, and then run SQL Server Setup again.
<EndFunc Name='ComponentUpgrade' Return='2' GetLastError='0'>
PerfTime Stop: CommitSqlUpgrade : Sat Sep 23 22:08:08 2006
Gathering darwin properties for failure handling.
<EndFunc Name='LaunchFunction' Return='2' GetLastError='0'>
MSI (s) (B0:A8) [22:08:09:370]: Transforming table InstallExecuteSequence.

MSI (s) (B0:A8) [22:08:09:370]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (B0:A8) [22:08:09:370]: Transforming table InstallExecuteSequence.

MSI (s) (B0:A8) [22:08:09:370]: Transforming table InstallExecuteSequence.

MSI (s) (B0:A8) [22:08:09:370]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (B0:A8) [22:08:09:370]: Transforming table InstallExecuteSequence.

MSI (s) (B0:A8) [22:08:09:370]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
Action ended 22:08:09: CommitSqlUpgrade.D20239D7_E87C_40C9_9837_E70B8D4882C2. Return value 3.

|||

It seams there was a failure during previous upgrade. At this point I would suggest that you go to AddRemove Programs select MS SQL Server 2005 and Change. Then you select the SQL instance that failed and Database Engine. Proceed to Change or Remove Instance dialog and I assume there is an option Complete the suspended installation so select that one.

It is possible that setup needs access to the original installation media so if you installed from CD insert it before launching the setup.

|||

That did the trick!

Many thanks for the help.

|||

I ran into this issue and resolved it by changing the registry. Under: Software\Policies\Microsoft\Windows\Installer, I found that DisableMSI was not set to 0. For some reason it was set to 2. After changing this to 0, SP1 installed fine.

What's strange is there is nothing that should've set this to registry entry to 2.

Tuesday, March 27, 2012

field changing unexpectedly

Field attribute question

Monday, March 19, 2012

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Fatal Error Handling

Fatal Error Handling

Fatal Error

Monday, March 12, 2012

fastest way to do large amounts of updates

Friday, March 9, 2012

Fastest way of updating a row

Fastest Update/Insert Method

Fastest Update/Insert Method

Fastest Update/Insert Method

faster page update using SQL Server data

Wednesday, March 7, 2012

Fast updates in SQL Server

Sunday, February 26, 2012

faily to update record

Friday, February 24, 2012

Failure with transactional replication with immediate updating subscriptions

Failure when installing SQL 2005 SP1 on Windows 2003 Server

Footer rows to skip

Blog Archive

About Me