InsertCommand using data from a second SqlDataSource - ASP.NET 2.0

I have a process that inserts a new record using the InsertCommand of aSqlDataSource. As part of the process, I need to insert data the is available in a different SqlDataSource. I was trying this with the Insert Parameter:

<asp:FormParameterName="Change_Title"FormField="Change_Title"/>

where Change_Title is available on screen. Doesn't work. Is this possible?

HI

Can you see if this post helps or gives you some idea of how to achieve it.

http://forums.asp.net/p/1124558/1766373.aspx#1766373

The post though gives a way to avoid the need for two SQLDatasources but use one to handle both level updates.

Hope this helps.

VJ

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
>

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

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
>

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
>

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

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
>