Hello
MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
I have certain procedure which can't be reproduced
using my common technique.
CREATE table #t (text nvarchar(4000))
insert into #t exec sp_helptext 'objectname'
SELECT * FROM #T
DROP TABLE #T
exec sp_helptext 'objectname'
Two output resultsets are DIFFERENT!
Row order in temporary table doesn't coincide with real
row order (two rows with numbers 119 and 120 inserted
into positions 68 and 69).
This is critical for me!
Does anyone know the cause of this or any workaround?
Thanks,
Serge ShakhovThere is no way to totally control how rows are stored in a table. The
order in which you insert them has little to do with how they are stored or
placed into the pages on disk. Even with a clustered index you are not
guaranteed for everything to be physically and logically in order. So if
you want to get them out of a table in a specific order then you need to
specify an ORDER BY clause.
--
Andrew J. Kelly
SQL Server MVP
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:48dgmb.5o3.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
> I have certain procedure which can't be reproduced
> using my common technique.
> CREATE table #t (text nvarchar(4000))
> insert into #t exec sp_helptext 'objectname'
> SELECT * FROM #T
> DROP TABLE #T
> exec sp_helptext 'objectname'
> Two output resultsets are DIFFERENT!
> Row order in temporary table doesn't coincide with real
> row order (two rows with numbers 119 and 120 inserted
> into positions 68 and 69).
> This is critical for me!
> Does anyone know the cause of this or any workaround?
> Thanks,
> Serge Shakhov
>|||Hello
> MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
> I have certain procedure which can't be reproduced
> using my common technique.
> CREATE table #t (text nvarchar(4000))
> insert into #t exec sp_helptext 'objectname'
> SELECT * FROM #T
> DROP TABLE #T
> exec sp_helptext 'objectname'
> Two output resultsets are DIFFERENT!
I still don't know the reason of this behavior
but now I know that changing nvarchar datatype
to varchar forces server to work properly.
This can be considered as workaround.
Serge Shakhovsql
Showing posts with label sp3. Show all posts
Showing posts with label sp3. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Insert with Index VS Insert without Index
Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP Job
This sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP Job
This sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>
Insert with Index VS Insert without Index
Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>sql
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>sql
Insert with Index VS Insert without Index
Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx
.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx
.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>
Wednesday, March 21, 2012
Insert to temporary table causes EXCEPTION_ACCESS_VIOLATION
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype = 'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
Why would your #temp table already exist, at the beginning of the procedure?
Have you ever actually come across this? Why does your procedure not have a
DROP TABLE #ChangeLogs at the end?
In any case, rather than perform a query directly against
tempdb..sysobjects, how about:
IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
DROP TABLE #ChangeLogs
Essentially, this does the same thing, but I believe the optimizer / query
engine might behave a little differently. Also, your check for xtype is
redundant. What other kind of object is going to be named #ChangeLogs and
stored in tempdb?
> Can anyone tell me what is happening and how to fix it?
I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
What version are you using (SELECT @.@.VERSION)?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Can you post the output of the following command, on this server?
SELECT @.@.VERSION
GO
I tried on SQL2K SP3 and it worked fine.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype ='u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L|||Aaron
Thanks for the reply, I will answer your questions in line, but I am
afraid that you are concentrating on the wrong portion of the code. The
CREATE TABLE works OK, it is the INSERT that dies. SELECT @.@.Version returns
the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23r%23QU1PZEHA.2260@.TK2MSFTNGP12.phx.gbl...
> > /* Create a temporary Change Log information table. If the *
> > * update is successful, this data will be copied to the *
> > * TSL change log table. */
> > IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> > ID=OBJECT_ID('tempdb..#ChangeLogs') AND
xtype
> => > 'u')
> > DROP Table #ChangeLogs
> Why would your #temp table already exist, at the beginning of the
procedure?
Just a safety measure to be sure that I don't attempt to recreate an
existing table and get an error from it
> Have you ever actually come across this?
I believe that we have seen this in the development phase while running the
SP from Query Analyzer (which keeps the connection open) if the SP dies
before the DROP TABLE
>Why does your procedure not have a DROP TABLE #ChangeLogs at the end?
OOPS!
> In any case, rather than perform a query directly against
> tempdb..sysobjects, how about:
> IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
> DROP TABLE #ChangeLogs
We simply copied code (that works) from the code you get when you script a
table in SQL
> Essentially, this does the same thing, but I believe the optimizer / query
> engine might behave a little differently. Also, your check for xtype is
> redundant. What other kind of object is going to be named #ChangeLogs and
> stored in tempdb?
> > Can anyone tell me what is happening and how to fix it?
> I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
> What version are you using (SELECT @.@.VERSION)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Narayana,
Thanks for the response. SELECT @.@.Version returns the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uBR8V4PZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Can you post the output of the following command, on this server?
> SELECT @.@.VERSION
> GO
> I tried on SQL2K SP3 and it worked fine.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ron L" <rlounsbury@.bogusAddress.com> wrote in message
> news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
>|||> afraid that you are concentrating on the wrong portion of the code. The
> CREATE TABLE works OK, it is the INSERT that dies.
I wasn't suggesting it to fix the problem with this procedure. I was
suggesting a better approach for all your procedures.
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
MSDE! Ah, okay, this may be important information, but I'm not sure. I
don't have a 760 MSDE around to test. If you were on a similar edition, but
a lower version other than the ones I tested on, I would have suggested
upgrading. Unfortunately, in this case, I can only suggest that you open a
case with PSS, unless someone with MSDE (@. 760) can reproduce this
problem...
Aaron|||I called MS on this problem. They had me download and install the MS03-031
patch. This brings SQL to version 8.00.818. This has fixed the problem,
although I haven't yet done a broad check to verify that it doesn't cause
any other problems.
Ron L
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype = 'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
Why would your #temp table already exist, at the beginning of the procedure?
Have you ever actually come across this? Why does your procedure not have a
DROP TABLE #ChangeLogs at the end?
In any case, rather than perform a query directly against
tempdb..sysobjects, how about:
IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
DROP TABLE #ChangeLogs
Essentially, this does the same thing, but I believe the optimizer / query
engine might behave a little differently. Also, your check for xtype is
redundant. What other kind of object is going to be named #ChangeLogs and
stored in tempdb?
> Can anyone tell me what is happening and how to fix it?
I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
What version are you using (SELECT @.@.VERSION)?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Can you post the output of the following command, on this server?
SELECT @.@.VERSION
GO
I tried on SQL2K SP3 and it worked fine.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype ='u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L|||Aaron
Thanks for the reply, I will answer your questions in line, but I am
afraid that you are concentrating on the wrong portion of the code. The
CREATE TABLE works OK, it is the INSERT that dies. SELECT @.@.Version returns
the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23r%23QU1PZEHA.2260@.TK2MSFTNGP12.phx.gbl...
> > /* Create a temporary Change Log information table. If the *
> > * update is successful, this data will be copied to the *
> > * TSL change log table. */
> > IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> > ID=OBJECT_ID('tempdb..#ChangeLogs') AND
xtype
> => > 'u')
> > DROP Table #ChangeLogs
> Why would your #temp table already exist, at the beginning of the
procedure?
Just a safety measure to be sure that I don't attempt to recreate an
existing table and get an error from it
> Have you ever actually come across this?
I believe that we have seen this in the development phase while running the
SP from Query Analyzer (which keeps the connection open) if the SP dies
before the DROP TABLE
>Why does your procedure not have a DROP TABLE #ChangeLogs at the end?
OOPS!
> In any case, rather than perform a query directly against
> tempdb..sysobjects, how about:
> IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
> DROP TABLE #ChangeLogs
We simply copied code (that works) from the code you get when you script a
table in SQL
> Essentially, this does the same thing, but I believe the optimizer / query
> engine might behave a little differently. Also, your check for xtype is
> redundant. What other kind of object is going to be named #ChangeLogs and
> stored in tempdb?
> > Can anyone tell me what is happening and how to fix it?
> I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
> What version are you using (SELECT @.@.VERSION)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Narayana,
Thanks for the response. SELECT @.@.Version returns the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uBR8V4PZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Can you post the output of the following command, on this server?
> SELECT @.@.VERSION
> GO
> I tried on SQL2K SP3 and it worked fine.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ron L" <rlounsbury@.bogusAddress.com> wrote in message
> news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
>|||> afraid that you are concentrating on the wrong portion of the code. The
> CREATE TABLE works OK, it is the INSERT that dies.
I wasn't suggesting it to fix the problem with this procedure. I was
suggesting a better approach for all your procedures.
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
MSDE! Ah, okay, this may be important information, but I'm not sure. I
don't have a 760 MSDE around to test. If you were on a similar edition, but
a lower version other than the ones I tested on, I would have suggested
upgrading. Unfortunately, in this case, I can only suggest that you open a
case with PSS, unless someone with MSDE (@. 760) can reproduce this
problem...
Aaron|||I called MS on this problem. They had me download and install the MS03-031
patch. This brings SQL to version 8.00.818. This has fixed the problem,
although I haven't yet done a broad check to verify that it doesn't cause
any other problems.
Ron L
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
Insert to temporary table causes EXCEPTION_ACCESS_VIOLATION
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype =
'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
Why would your #temp table already exist, at the beginning of the procedure?
Have you ever actually come across this? Why does your procedure not have a
DROP TABLE #ChangeLogs at the end?
In any case, rather than perform a query directly against
tempdb..sysobjects, how about :
IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
DROP TABLE #ChangeLogs
Essentially, this does the same thing, but I believe the optimizer / query
engine might behave a little differently. Also, your check for xtype is
redundant. What other kind of object is going to be named #ChangeLogs and
stored in tempdb?
> Can anyone tell me what is happening and how to fix it?
I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
What version are you using (SELECT @.@.VERSION)?
http://www.aspfaq.com/
(Reverse address to reply.)|||Can you post the output of the following command, on this server?
SELECT @.@.VERSION
GO
I tried on SQL2K SP3 and it worked fine.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype =
'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L|||Aaron
Thanks for the reply, I will answer your questions in line, but I am
afraid that you are concentrating on the wrong portion of the code. The
CREATE TABLE works OK, it is the INSERT that dies. SELECT @.@.Version returns
the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurit
yOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23r%23QU1PZEHA.2260@.TK2MSFTNGP12.phx.gbl...
xtype[vbcol=seagreen]
> =
> Why would your #temp table already exist, at the beginning of the
procedure?
Just a safety measure to be sure that I don't attempt to recreate an
existing table and get an error from it
> Have you ever actually come across this?
I believe that we have seen this in the development phase while running the
SP from Query Analyzer (which keeps the connection open) if the SP dies
before the DROP TABLE
>Why does your procedure not have a DROP TABLE #ChangeLogs at the end?
OOPS!
> In any case, rather than perform a query directly against
> tempdb..sysobjects, how about :
> IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
> DROP TABLE #ChangeLogs
We simply copied code (that works) from the code you get when you script a
table in SQL
> Essentially, this does the same thing, but I believe the optimizer / query
> engine might behave a little differently. Also, your check for xtype is
> redundant. What other kind of object is going to be named #ChangeLogs and
> stored in tempdb?
>
> I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
> What version are you using (SELECT @.@.VERSION)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Narayana,
Thanks for the response. SELECT @.@.Version returns the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurit
yOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uBR8V4PZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Can you post the output of the following command, on this server?
> SELECT @.@.VERSION
> GO
> I tried on SQL2K SP3 and it worked fine.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ron L" <rlounsbury@.bogusAddress.com> wrote in message
> news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data[/vbcol
]
that[vbcol=seagreen]
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
>|||> afraid that you are concentrating on the wrong portion of the code. The
> CREATE TABLE works OK, it is the INSERT that dies.
I wasn't suggesting it to fix the problem with this procedure. I was
suggesting a better approach for all your procedures.
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
MSDE! Ah, okay, this may be important information, but I'm not sure. I
don't have a 760 MSDE around to test. If you were on a similar edition, but
a lower version other than the ones I tested on, I would have suggested
upgrading. Unfortunately, in this case, I can only suggest that you open a
case with PSS, unless someone with MSDE (@. 760) can reproduce this
problem...
Aaron|||I called MS on this problem. They had me download and install the MS03-031
patch. This brings SQL to version 8.00.818. This has fixed the problem,
although I haven't yet done a broad check to verify that it doesn't cause
any other problems.
Ron L
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data[/vbcol
]
that[vbcol=seagreen]
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>sql
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype =
'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
Why would your #temp table already exist, at the beginning of the procedure?
Have you ever actually come across this? Why does your procedure not have a
DROP TABLE #ChangeLogs at the end?
In any case, rather than perform a query directly against
tempdb..sysobjects, how about :
IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
DROP TABLE #ChangeLogs
Essentially, this does the same thing, but I believe the optimizer / query
engine might behave a little differently. Also, your check for xtype is
redundant. What other kind of object is going to be named #ChangeLogs and
stored in tempdb?
> Can anyone tell me what is happening and how to fix it?
I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
What version are you using (SELECT @.@.VERSION)?
http://www.aspfaq.com/
(Reverse address to reply.)|||Can you post the output of the following command, on this server?
SELECT @.@.VERSION
GO
I tried on SQL2K SP3 and it worked fine.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype =
'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L|||Aaron
Thanks for the reply, I will answer your questions in line, but I am
afraid that you are concentrating on the wrong portion of the code. The
CREATE TABLE works OK, it is the INSERT that dies. SELECT @.@.Version returns
the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurit
yOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23r%23QU1PZEHA.2260@.TK2MSFTNGP12.phx.gbl...
xtype[vbcol=seagreen]
> =
> Why would your #temp table already exist, at the beginning of the
procedure?
Just a safety measure to be sure that I don't attempt to recreate an
existing table and get an error from it
> Have you ever actually come across this?
I believe that we have seen this in the development phase while running the
SP from Query Analyzer (which keeps the connection open) if the SP dies
before the DROP TABLE
>Why does your procedure not have a DROP TABLE #ChangeLogs at the end?
OOPS!
> In any case, rather than perform a query directly against
> tempdb..sysobjects, how about :
> IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
> DROP TABLE #ChangeLogs
We simply copied code (that works) from the code you get when you script a
table in SQL
> Essentially, this does the same thing, but I believe the optimizer / query
> engine might behave a little differently. Also, your check for xtype is
> redundant. What other kind of object is going to be named #ChangeLogs and
> stored in tempdb?
>
> I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
> What version are you using (SELECT @.@.VERSION)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Narayana,
Thanks for the response. SELECT @.@.Version returns the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurit
yOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uBR8V4PZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Can you post the output of the following command, on this server?
> SELECT @.@.VERSION
> GO
> I tried on SQL2K SP3 and it worked fine.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ron L" <rlounsbury@.bogusAddress.com> wrote in message
> news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data[/vbcol
]
that[vbcol=seagreen]
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
>|||> afraid that you are concentrating on the wrong portion of the code. The
> CREATE TABLE works OK, it is the INSERT that dies.
I wasn't suggesting it to fix the problem with this procedure. I was
suggesting a better approach for all your procedures.
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
MSDE! Ah, okay, this may be important information, but I'm not sure. I
don't have a 760 MSDE around to test. If you were on a similar edition, but
a lower version other than the ones I tested on, I would have suggested
upgrading. Unfortunately, in this case, I can only suggest that you open a
case with PSS, unless someone with MSDE (@. 760) can reproduce this
problem...
Aaron|||I called MS on this problem. They had me download and install the MS03-031
patch. This brings SQL to version 8.00.818. This has fixed the problem,
although I haven't yet done a broad check to verify that it doesn't cause
any other problems.
Ron L
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data[/vbcol
]
that[vbcol=seagreen]
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>sql
Insert to temporary table causes EXCEPTION_ACCESS_VIOLATION
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype =
'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
Why would your #temp table already exist, at the beginning of the procedure?
Have you ever actually come across this? Why does your procedure not have a
DROP TABLE #ChangeLogs at the end?
In any case, rather than perform a query directly against
tempdb..sysobjects, how about:
IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
DROP TABLE #ChangeLogs
Essentially, this does the same thing, but I believe the optimizer / query
engine might behave a little differently. Also, your check for xtype is
redundant. What other kind of object is going to be named #ChangeLogs and
stored in tempdb?
> Can anyone tell me what is happening and how to fix it?
I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
What version are you using (SELECT @.@.VERSION)?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Can you post the output of the following command, on this server?
SELECT @.@.VERSION
GO
I tried on SQL2K SP3 and it worked fine.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype =
'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L
|||Aaron
Thanks for the reply, I will answer your questions in line, but I am
afraid that you are concentrating on the wrong portion of the code. The
CREATE TABLE works OK, it is the INSERT that dies. SELECT @.@.Version returns
the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23r%23QU1PZEHA.2260@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
xtype
> =
> Why would your #temp table already exist, at the beginning of the
procedure?
Just a safety measure to be sure that I don't attempt to recreate an
existing table and get an error from it
> Have you ever actually come across this?
I believe that we have seen this in the development phase while running the
SP from Query Analyzer (which keeps the connection open) if the SP dies
before the DROP TABLE
>Why does your procedure not have a DROP TABLE #ChangeLogs at the end?
OOPS!
> In any case, rather than perform a query directly against
> tempdb..sysobjects, how about:
> IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
> DROP TABLE #ChangeLogs
We simply copied code (that works) from the code you get when you script a
table in SQL
> Essentially, this does the same thing, but I believe the optimizer / query
> engine might behave a little differently. Also, your check for xtype is
> redundant. What other kind of object is going to be named #ChangeLogs and
> stored in tempdb?
>
> I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
> What version are you using (SELECT @.@.VERSION)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||Narayana,
Thanks for the response. SELECT @.@.Version returns the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uBR8V4PZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Can you post the output of the following command, on this server?
> SELECT @.@.VERSION
> GO
> I tried on SQL2K SP3 and it worked fine.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ron L" <rlounsbury@.bogusAddress.com> wrote in message
> news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
>
|||> afraid that you are concentrating on the wrong portion of the code. The
> CREATE TABLE works OK, it is the INSERT that dies.
I wasn't suggesting it to fix the problem with this procedure. I was
suggesting a better approach for all your procedures.
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
MSDE! Ah, okay, this may be important information, but I'm not sure. I
don't have a 760 MSDE around to test. If you were on a similar edition, but
a lower version other than the ones I tested on, I would have suggested
upgrading. Unfortunately, in this case, I can only suggest that you open a
case with PSS, unless someone with MSDE (@. 760) can reproduce this
problem...
Aaron
|||I called MS on this problem. They had me download and install the MS03-031
patch. This brings SQL to version 8.00.818. This has fixed the problem,
although I haven't yet done a broad check to verify that it doesn't cause
any other problems.
Ron L
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype =
'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
Why would your #temp table already exist, at the beginning of the procedure?
Have you ever actually come across this? Why does your procedure not have a
DROP TABLE #ChangeLogs at the end?
In any case, rather than perform a query directly against
tempdb..sysobjects, how about:
IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
DROP TABLE #ChangeLogs
Essentially, this does the same thing, but I believe the optimizer / query
engine might behave a little differently. Also, your check for xtype is
redundant. What other kind of object is going to be named #ChangeLogs and
stored in tempdb?
> Can anyone tell me what is happening and how to fix it?
I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
What version are you using (SELECT @.@.VERSION)?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Can you post the output of the following command, on this server?
SELECT @.@.VERSION
GO
I tried on SQL2K SP3 and it worked fine.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype =
'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L
|||Aaron
Thanks for the reply, I will answer your questions in line, but I am
afraid that you are concentrating on the wrong portion of the code. The
CREATE TABLE works OK, it is the INSERT that dies. SELECT @.@.Version returns
the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23r%23QU1PZEHA.2260@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
xtype
> =
> Why would your #temp table already exist, at the beginning of the
procedure?
Just a safety measure to be sure that I don't attempt to recreate an
existing table and get an error from it
> Have you ever actually come across this?
I believe that we have seen this in the development phase while running the
SP from Query Analyzer (which keeps the connection open) if the SP dies
before the DROP TABLE
>Why does your procedure not have a DROP TABLE #ChangeLogs at the end?
OOPS!
> In any case, rather than perform a query directly against
> tempdb..sysobjects, how about:
> IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
> DROP TABLE #ChangeLogs
We simply copied code (that works) from the code you get when you script a
table in SQL
> Essentially, this does the same thing, but I believe the optimizer / query
> engine might behave a little differently. Also, your check for xtype is
> redundant. What other kind of object is going to be named #ChangeLogs and
> stored in tempdb?
>
> I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
> What version are you using (SELECT @.@.VERSION)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||Narayana,
Thanks for the response. SELECT @.@.Version returns the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uBR8V4PZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Can you post the output of the following command, on this server?
> SELECT @.@.VERSION
> GO
> I tried on SQL2K SP3 and it worked fine.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ron L" <rlounsbury@.bogusAddress.com> wrote in message
> news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
>
|||> afraid that you are concentrating on the wrong portion of the code. The
> CREATE TABLE works OK, it is the INSERT that dies.
I wasn't suggesting it to fix the problem with this procedure. I was
suggesting a better approach for all your procedures.
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
MSDE! Ah, okay, this may be important information, but I'm not sure. I
don't have a 760 MSDE around to test. If you were on a similar edition, but
a lower version other than the ones I tested on, I would have suggested
upgrading. Unfortunately, in this case, I can only suggest that you open a
case with PSS, unless someone with MSDE (@. 760) can reproduce this
problem...
Aaron
|||I called MS on this problem. They had me download and install the MS03-031
patch. This brings SQL to version 8.00.818. This has fixed the problem,
although I haven't yet done a broad check to verify that it doesn't cause
any other problems.
Ron L
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=
> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
Monday, March 19, 2012
Insert Syntax Error
In SQL 7.0 SP3 , I am receiving this message...
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ')'.
When I try to execute this code from SQL Query Analyzer...
DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"
OPEN DPPNumberCursor
Fetch Next From DPPNumberCursor
INTO @.dppnumbercursor
While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id)
Values (@.dppnumbercursor)
Bsically, I want to insert the number held in @.dppnumbercursor in the APQPSub_id field.
Any help would be appreciated.How about END at the end?|||...and DEALLOCATE/CLOSE would be very appropriate ;)|||OK, I'm an idiot...can you tell I'm a newbie? Thanks for not tearing me up too bad :-)
Thanks to both for your help....|||You're welcome...from both of us ;)|||Nobody is going to mention the double quotes?
Or the fact that s/he doesn't need a cursor at all?
INSERT INTO APQPSubformTable(apqpsub_id)
SELECT PPAP_ID
FROM ppap
WHERE ppap_cancel <> '1'
AND ppap_close <> '1'
AND projectonhold <> '1'|||I was thinking about it, but then decided not to...when he/she thanked "both" of us ;)|||It's been one of those days:-)
Just for my own edification, what about the double quotes?
The reason I used the cursor was because I needed to add two records into the APQPSubformtable for each open project in the ppap table. Here is the completed working code...
DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"
OPEN DPPNumberCursor
Fetch Next From DPPNumberCursor
INTO @.dppnumbercursor
While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"31","Start Of Production (SOP) [F4]","OP","0","G")
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"32","Stable Production (Review) [F5]","OP","0","G")
FETCH NEXT from DPPNumberCursor
INTO @.DPPNumberCursor
End
Close DPPNumberCursor|||Double-quotes are harmless as long as you know what your connection settings are when you compile your procedure/function/trigger/view (should be SET QUOTED_IDENTIFIER OFF).|||Just for my own edification, what about the double quotes?
It is a better practice to use single quotes to delimit strings. Double quotes can serve two purposes: delimit a string and delimit an identifyer (ie table name or column name). There is a QUOTED_IDENTIFYER property the determines which. Set to ON, everything within double quotes is supposed to be considered an Identifyer.
I've read in BOL that SLQ Server is 'not very rigorous' in enforcing this rule, depending on the length of the string in question. Small strings such as the ones you are using seem to work OK, but you'd be better off delimiting the strings correctly than relying on SQL Analyzer to interpret what it is you really mean.|||Don't Play with Settings...
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
SELECT @.dppnumbercursor,'31','Start Of Production (SOP) [F4]','OP','0','G'
UNION ALL
SELECT @.dppnumbercursor,'32','Stable Production (Review) [F5]','OP','0','G'
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ')'.
When I try to execute this code from SQL Query Analyzer...
DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"
OPEN DPPNumberCursor
Fetch Next From DPPNumberCursor
INTO @.dppnumbercursor
While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id)
Values (@.dppnumbercursor)
Bsically, I want to insert the number held in @.dppnumbercursor in the APQPSub_id field.
Any help would be appreciated.How about END at the end?|||...and DEALLOCATE/CLOSE would be very appropriate ;)|||OK, I'm an idiot...can you tell I'm a newbie? Thanks for not tearing me up too bad :-)
Thanks to both for your help....|||You're welcome...from both of us ;)|||Nobody is going to mention the double quotes?
Or the fact that s/he doesn't need a cursor at all?
INSERT INTO APQPSubformTable(apqpsub_id)
SELECT PPAP_ID
FROM ppap
WHERE ppap_cancel <> '1'
AND ppap_close <> '1'
AND projectonhold <> '1'|||I was thinking about it, but then decided not to...when he/she thanked "both" of us ;)|||It's been one of those days:-)
Just for my own edification, what about the double quotes?
The reason I used the cursor was because I needed to add two records into the APQPSubformtable for each open project in the ppap table. Here is the completed working code...
DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"
OPEN DPPNumberCursor
Fetch Next From DPPNumberCursor
INTO @.dppnumbercursor
While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"31","Start Of Production (SOP) [F4]","OP","0","G")
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"32","Stable Production (Review) [F5]","OP","0","G")
FETCH NEXT from DPPNumberCursor
INTO @.DPPNumberCursor
End
Close DPPNumberCursor|||Double-quotes are harmless as long as you know what your connection settings are when you compile your procedure/function/trigger/view (should be SET QUOTED_IDENTIFIER OFF).|||Just for my own edification, what about the double quotes?
It is a better practice to use single quotes to delimit strings. Double quotes can serve two purposes: delimit a string and delimit an identifyer (ie table name or column name). There is a QUOTED_IDENTIFYER property the determines which. Set to ON, everything within double quotes is supposed to be considered an Identifyer.
I've read in BOL that SLQ Server is 'not very rigorous' in enforcing this rule, depending on the length of the string in question. Small strings such as the ones you are using seem to work OK, but you'd be better off delimiting the strings correctly than relying on SQL Analyzer to interpret what it is you really mean.|||Don't Play with Settings...
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
SELECT @.dppnumbercursor,'31','Start Of Production (SOP) [F4]','OP','0','G'
UNION ALL
SELECT @.dppnumbercursor,'32','Stable Production (Review) [F5]','OP','0','G'
Friday, February 24, 2012
insert performance varies
hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
i have with sql server 2000 sp3. i am running a script within query analyzer.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background which
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
khA wild guess is that autogrow for the log file (or even database file) kicks in when it is slow.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
> i have with sql server 2000 sp3. i am running a script within query analyzer.
> the batch includes hundreds of deletes followed by hundreds of inserts, all
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background which
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
i have with sql server 2000 sp3. i am running a script within query analyzer.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background which
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
khA wild guess is that autogrow for the log file (or even database file) kicks in when it is slow.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
> i have with sql server 2000 sp3. i am running a script within query analyzer.
> the batch includes hundreds of deletes followed by hundreds of inserts, all
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background which
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
insert performance varies
hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
i have with sql server 2000 sp3. i am running a script within query analyzer.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background which
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
kh
A wild guess is that autogrow for the log file (or even database file) kicks in when it is slow.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
> i have with sql server 2000 sp3. i am running a script within query analyzer.
> the batch includes hundreds of deletes followed by hundreds of inserts, all
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background which
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>
|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
i have with sql server 2000 sp3. i am running a script within query analyzer.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background which
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
kh
A wild guess is that autogrow for the log file (or even database file) kicks in when it is slow.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
> i have with sql server 2000 sp3. i am running a script within query analyzer.
> the batch includes hundreds of deletes followed by hundreds of inserts, all
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background which
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>
|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
insert performance varies
hi. i'm no dba and wouldn't mind a few pointers for investigation of an issu
e
i have with sql server 2000 sp3. i am running a script within query analyzer
.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background whic
h
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
khA wild guess is that autogrow for the log file (or even database file) kicks
in when it is slow.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft
.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an is
sue
> i have with sql server 2000 sp3. i am running a script within query analyz
er.
> the batch includes hundreds of deletes followed by hundreds of inserts, al
l
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to
do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background wh
ich
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
e
i have with sql server 2000 sp3. i am running a script within query analyzer
.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background whic
h
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
khA wild guess is that autogrow for the log file (or even database file) kicks
in when it is slow.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft
.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an is
sue
> i have with sql server 2000 sp3. i am running a script within query analyz
er.
> the batch includes hundreds of deletes followed by hundreds of inserts, al
l
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to
do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background wh
ich
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
Subscribe to:
Posts (Atom)