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
>

INSERT statement conflicted with COLUMN CHECK constraint.

Hi,
I'm attempting to insert a new row into an SQL table using ADO written
with c# and stored procedures.
The ADO code is running OK, and i know it should work as i have used
equivilent code succeffully for other tables. However i am getting the
following error:
{"INSERT statement conflicted with COLUMN CHECK constraint 'CK
tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
table 'tblPatient', column 'pntStage'.\r\nThe statement has been
terminated." }
pntStage has data type NVarChar, and maximum length 8. The values i am
attempting to input do not violate these criteria. I have deleted the
complete row and added it again incase there was some hidden input
mask, this has not solved the problem.
Any ideas what the problem might be? Here's my stored procedure if
taht's any help.
CREATE PROCEDURE proc_InsertPatient
(@.patientNo int output,
@.pntUnitID nvarchar(15),
@.pntTitle nvarchar(4),
@.pntFName nvarchar(20),
@.pntLName nvarchar(30),
@.pntDOB nvarchar(8),
@.pntSex nvarchar(1),
@.pntAddress1 nvarchar(150),
@.pntAddress2 nvarchar(150),
@.pntAddress3 nvarchar(150),
@.pntCountryNo int output,
@.pntPostcode nvarchar (10),
@.pntHPhone nvarchar (14),
@.pntWPhone nvarchar (14),
@.pntMobPhone nvarchar (14),
@.pntEmail nvarchar (50),
@.pntStage nvarchar (8),
@.pntT tinyint,
@.pntN tinyint,
@.pntM tinyint,
@.pntPreviousTreatments char (1000),
@.pntFurtherNotes char (1000)
)
AS
INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
pntDOB, pntSex, pntAddress1,
pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
pntWPhone,
pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
pntPreviousTreatments, pntFurtherNotes)
VALUES
(@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
@.pntAddress1,
@.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
@.pntWPhone,
@.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
@.pntPreviousTreatments, @.pntFurtherNotes)
SELECT @.patientNo=@.@.IDENTITY
GO
Thanks.Hi
pntStage might have a check constraint that specifics that the values can
only be in a certain range or of a certain patters. Look at the Column's
check constrains though EM to see what has been setup.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122817569.583719.19100@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm attempting to insert a new row into an SQL table using ADO written
> with c# and stored procedures.
> The ADO code is running OK, and i know it should work as i have used
> equivilent code succeffully for other tables. However i am getting the
> following error:
> {"INSERT statement conflicted with COLUMN CHECK constraint 'CK
> tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
> table 'tblPatient', column 'pntStage'.\r\nThe statement has been
> terminated." }
> pntStage has data type NVarChar, and maximum length 8. The values i am
> attempting to input do not violate these criteria. I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
> Any ideas what the problem might be? Here's my stored procedure if
> taht's any help.
> CREATE PROCEDURE proc_InsertPatient
> (@.patientNo int output,
> @.pntUnitID nvarchar(15),
> @.pntTitle nvarchar(4),
> @.pntFName nvarchar(20),
> @.pntLName nvarchar(30),
> @.pntDOB nvarchar(8),
> @.pntSex nvarchar(1),
> @.pntAddress1 nvarchar(150),
> @.pntAddress2 nvarchar(150),
> @.pntAddress3 nvarchar(150),
> @.pntCountryNo int output,
> @.pntPostcode nvarchar (10),
> @.pntHPhone nvarchar (14),
> @.pntWPhone nvarchar (14),
> @.pntMobPhone nvarchar (14),
> @.pntEmail nvarchar (50),
> @.pntStage nvarchar (8),
> @.pntT tinyint,
> @.pntN tinyint,
> @.pntM tinyint,
> @.pntPreviousTreatments char (1000),
> @.pntFurtherNotes char (1000)
> )
> AS
> INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
> pntDOB, pntSex, pntAddress1,
> pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
> pntWPhone,
> pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
> pntPreviousTreatments, pntFurtherNotes)
> VALUES
> (@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
> @.pntAddress1,
> @.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
> @.pntWPhone,
> @.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
> @.pntPreviousTreatments, @.pntFurtherNotes)
> SELECT @.patientNo=@.@.IDENTITY
> GO
>
> Thanks.
>|||Please post the table DDL (including the CHECK constraint) and the @.pntStage
value you are trying to insert. This will help us identify the cause of
your problem.

> I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
I'm not sure I understand what you mean by 'deleted the complete row'.
Since the insert failed, I wouldn't expect you would find the row in
tblPatient.
Hope this helps.
Dan Guzman
SQL Server MVP
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122817569.583719.19100@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm attempting to insert a new row into an SQL table using ADO written
> with c# and stored procedures.
> The ADO code is running OK, and i know it should work as i have used
> equivilent code succeffully for other tables. However i am getting the
> following error:
> {"INSERT statement conflicted with COLUMN CHECK constraint 'CK
> tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
> table 'tblPatient', column 'pntStage'.\r\nThe statement has been
> terminated." }
> pntStage has data type NVarChar, and maximum length 8. The values i am
> attempting to input do not violate these criteria. I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
> Any ideas what the problem might be? Here's my stored procedure if
> taht's any help.
> CREATE PROCEDURE proc_InsertPatient
> (@.patientNo int output,
> @.pntUnitID nvarchar(15),
> @.pntTitle nvarchar(4),
> @.pntFName nvarchar(20),
> @.pntLName nvarchar(30),
> @.pntDOB nvarchar(8),
> @.pntSex nvarchar(1),
> @.pntAddress1 nvarchar(150),
> @.pntAddress2 nvarchar(150),
> @.pntAddress3 nvarchar(150),
> @.pntCountryNo int output,
> @.pntPostcode nvarchar (10),
> @.pntHPhone nvarchar (14),
> @.pntWPhone nvarchar (14),
> @.pntMobPhone nvarchar (14),
> @.pntEmail nvarchar (50),
> @.pntStage nvarchar (8),
> @.pntT tinyint,
> @.pntN tinyint,
> @.pntM tinyint,
> @.pntPreviousTreatments char (1000),
> @.pntFurtherNotes char (1000)
> )
> AS
> INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
> pntDOB, pntSex, pntAddress1,
> pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
> pntWPhone,
> pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
> pntPreviousTreatments, pntFurtherNotes)
> VALUES
> (@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
> @.pntAddress1,
> @.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
> @.pntWPhone,
> @.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
> @.pntPreviousTreatments, @.pntFurtherNotes)
> SELECT @.patientNo=@.@.IDENTITY
> GO
>
> Thanks.
>|||Mike,
Thanks for the quick reply.
I've looked in EM at the tblPatient table. Within it's properties i can
only see row Name (pntStage), Data Type (nvarchar), Size (8), Nulls
(not allowed), Default (blank).
Is this what you mean? Is there another way to check more detailed
constraints?
Thanks again.|||Hi
In EM, when you are in the Table Edit screen, top left next to the save
button is the Properties button. On the check constraints tab, you can see
what columns have constraints and what they are.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122818859.232265.305760@.g14g2000cwa.googlegroups.com...
> Mike,
> Thanks for the quick reply.
> I've looked in EM at the tblPatient table. Within it's properties i can
> only see row Name (pntStage), Data Type (nvarchar), Size (8), Nulls
> (not allowed), Default (blank).
> Is this what you mean? Is there another way to check more detailed
> constraints?
> Thanks again.
>|||Dan,
Excuse my ignorance, but where do i find the table DDL?
As regards the input value, i have tried a few "Unknown", "I", "II",
"Ia", to name a few.
By the deleting row comment, i was just meaning that it didn't work, so
i completely removed that particular row from the table, then recreated
it. I thought perhaps i might have put some sort of input mask
constraint or something on it that i had forgotten about. By doing this
it would remove that possibility.
Thanks.|||Mike,
I've just done that, there were some constraints on it. not sure how
they got there, but i've altered them, problem solved! :)
Thank you very much!|||One method to generate the table DDL is to navigate to the table using the
Query Analyzer Object Browser and then right-click on the table and select
script to clipboard as create. You can then paste into your post.
Hope this helps.
Dan Guzman
SQL Server MVP
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122819272.357946.52430@.f14g2000cwb.googlegroups.com...
> Dan,
> Excuse my ignorance, but where do i find the table DDL?
> As regards the input value, i have tried a few "Unknown", "I", "II",
> "Ia", to name a few.
> By the deleting row comment, i was just meaning that it didn't work, so
> i completely removed that particular row from the table, then recreated
> it. I thought perhaps i might have put some sort of input mask
> constraint or something on it that i had forgotten about. By doing this
> it would remove that possibility.
> Thanks.
>|||You might want to find out WHY someone put constraints on the data.
Having a default of a blank on what should be a code is a sign that
someone did not do much design work. Of course we know that when we
saw the "tbl;" and "pnt-" prefixes that violation basic rules for
naming data elements. The "tbl-" prefix is silly in a language with
one data structure; the "pnt-" tells us the location f one occurence of
a data element, not what it is. When you wrote "pntSex" did you mean
"sex_code", "sex_frequency", "sex_preference", "sex_total"? Again,
name it for what it is, never for where it is.
And the use of NVARCHAR(n) in codes is usually a sign the nobody
designed the encodings; we prefer CHAR(n) so can add constraints and do
validation. Do you really use a lot of Chinese characters? If you
allow it, it will come.

Insert running slow

Are inserts really slow in 2005 or am I doing something stupid?
Here's the table:
CREATE TABLE [dbo].[Tickets](
[ticket] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[data] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[added] [datetime] NOT NULL CONSTRAINT [DF_Tickets_added] DEFAULT
(getdate()),
[lastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED
(
[ticket] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I wrote this stored procedure:
ALTER PROCEDURE [dbo].[CreateTicket]
@.ticket varchar(20) OUTPUT,
@.data varchar(2000)
AS
DECLARE @.key varchar(20)
DECLARE @.len int
DECLARE @.added bit
DECLARE @.cypher varchar(52)
SET NOCOUNT ON;
SET @.added=0
SET @.cypher='abcdefghijklmnopqrstuvwxyzABCDE
FGHIJKLMNOPQRSTUVWXYZ0123456789'
WHILE @.added=0
BEGIN
SELECT @.key='', @.len=20
WHILE @.len>0
BEGIN
-- The following line is the SLOW one!!!
SET @.key = @.key + SUBSTRING(@.cypher, CAST(FLOOR(RAND()*52) AS int)+1,1)
SET @.len = @.len -1
END
IF NOT EXISTS(SELECT 1 FROM Tickets WHERE ticket=@.key)
BEGIN
INSERT INTO Tickets (ticket, data,lastupdated) VALUES(@.key, @.data,GETDATE())
SET @.ticket = @.key
SET @.added = 1
END
END
And then used this to test it's speed:
DECLARE @.ticket varchar(20)
DECLARE @.sec datetime
DECLARE @.cnt int
TRUNCATE TABLE Tickets
SET @.cnt = 0
SET @.sec = DATEADD(second, 1, GETDATE())
WHILE GETDATE()<@.sec
BEGIN
EXEC CreateTicket @.ticket, 'this is a test'
SET @.cnt=@.cnt + 1
END
PRINT @.cnt
When I run this on SQL 2000, I get roughly 3000 records a second. When I
run it against 2005 I get roughly 160 records per second! The statement tha
t
is taking all the time in 2005 is the insert statement!
On 2005 if I comment it out I can execute 8,600ish loops per second. If it
isn't commented out I run 160ish.
On 2000 if I comment it out I can execute 5,900 loops per second, If it
isn't commented out I run 3,000ish.
Is inserting really that expensive or am I missing some knob I forgot to tur
n?Never mind. It appears there's something wrong with the server I was testin
g
on. Testing on another server I got 5,600ish inserts per second. What's
really weird though is the box that's performing slowing is a faster box tha
n
either of the other two with faster disks. Guess it's time to reinstall :)|||Before reinstalling, I would check perfmon and profiler and see what is
actually taking so long. It might be something easy to fix (or it might be
that reinstalling would cause the same performance problems.) A reinstall
might be in order, but unless that is really easy to do for you, it is
probably just something in how something is set up.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Larry Charlton" <LarryCharlton@.discussions.microsoft.com> wrote in message
news:A378F5F2-63BC-4FE0-AD6D-DFBB2952BB06@.microsoft.com...
> Never mind. It appears there's something wrong with the server I was
> testing
> on. Testing on another server I got 5,600ish inserts per second. What's
> really weird though is the box that's performing slowing is a faster box
> than
> either of the other two with faster disks. Guess it's time to reinstall
> :)
>

INSERT query using linked servers is slow.

Good day everyone,
I am setting up a simple data mart on a server running SQL 2005. It gets
updated nightly from another server with SQL 2000. Originally both database
s
were on the same server under 2000. Now that they are on different servers,
some of the INSERT queries seem to be running for an abnormally long time (s
o
long that I end up having to kill them).
All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
from [Local-Tables]". Most only join together 3 or 4 tables, using key
fields. Only a couple of them are running long, the others complete in abou
t
the same time as before. The problem tables are not at all large compared t
o
the ones that work fine, and in some cases even have less activity (new
records).
I am pretty sure the two servers are linked correctly, as the majority of
these SQL commands still work fine. The remote query timeout parameter in
sp_configure has been set to 0, since the default of 600 was causing
problems. Other than that, no changes have been made on either server.
Any suggestions for other things I might check? Thanks in advance for your
help.Couple of things:
Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TRAN
on linked servers.
Have you checked all the usual rules which apply to large INSERTs? eg
- presumably there is no-one else logged in to the database when you are
doing these large inserts ie no danger of locking.
- are there a lot of indexes on the table you're inserting to; this will
slow things down
- are there any triggers firing? Think about disabling them
- is there any other audit stuff going on, traces etc?
- make sure all table names are fully qualified eg
server01.northwind.dbo.authors (presumably you have to do this anyway)
- if you've got IDENTITY columns, particularly as primary keys on the target
table, I believe these _can_ cause hotspots, although these are supposed to
be a minor concern on modern hardware
- think about breaking up your inserts, say 10,000 rows at a time so you can
keep track of their progress. I've seen techniques for doing this in loops
on the web using either SET ROWCOUNT or TOP
Hope that helps.
Let me know how you get on.
Damien
"Chris F" wrote:

> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both databa
ses
> were on the same server under 2000. Now that they are on different server
s,
> some of the INSERT queries seem to be running for an abnormally long time
(so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in ab
out
> the same time as before. The problem tables are not at all large compared
to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for you
r
> help.
>|||Chris
How much data do you insert?
Consider script out all indexes (remove them) and run the INSERT ,now that
after inserting re-create all indexes
"Chris F" <ChrisF@.discussions.microsoft.com> wrote in message
news:18089707-C1D6-4296-B937-C17D34DCAE65@.microsoft.com...
> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both
> databases
> were on the same server under 2000. Now that they are on different
> servers,
> some of the INSERT queries seem to be running for an abnormally long time
> (so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in
> about
> the same time as before. The problem tables are not at all large compared
> to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for
> your
> help.
>|||Thanks for your help.
Actually I am not using any form of BEGIN TRAN, since I keep getting a 7391
error in all cases. I am running a stored procedure consisting of several
delete and insert statements. Now that they are through the backlog (from
not having run for several days) all but one of the procedures have
acceptable run times.
Going down your list of checks, there are no other users, the only index is
the PK, no triggers, no audits/traces, no identity columns. I have been
fully qualifying the table on the remote server but not the local one where
the stored proc kicks off, I can try this and see if it helps. Will also
look at breaking up the query (I need to wait until the current run finishes
,
I found out over the wend that it does eventually). The only table we ar
e
still having problems with is the largest in the DB.
"Damien" wrote:
> Couple of things:
> Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TR
AN
> on linked servers.
> Have you checked all the usual rules which apply to large INSERTs? eg
> - presumably there is no-one else logged in to the database when you are
> doing these large inserts ie no danger of locking.
> - are there a lot of indexes on the table you're inserting to; this will
> slow things down
> - are there any triggers firing? Think about disabling them
> - is there any other audit stuff going on, traces etc?
> - make sure all table names are fully qualified eg
> server01.northwind.dbo.authors (presumably you have to do this anyway)
> - if you've got IDENTITY columns, particularly as primary keys on the targ
et
> table, I believe these _can_ cause hotspots, although these are supposed t
o
> be a minor concern on modern hardware
> - think about breaking up your inserts, say 10,000 rows at a time so you c
an
> keep track of their progress. I've seen techniques for doing this in loop
s
> on the web using either SET ROWCOUNT or TOP
>
> Hope that helps.
> Let me know how you get on.
>
> Damien
> "Chris F" wrote:
>

Insert produces error

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table

Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@.AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@.AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

ToddHi,

Found the answer elsewhere but thought I'd share it here in case someone
else has this problem.

Access's upsizing wizard created a trigger on tblAddrTypes which (evidently)
was meant to emulate Access's autonumber functionality. Once I deleted that
trigger, everything worked fine.

Todd
"Todd" <infoNOSPAM@.MAPSONgroomery.biz> wrote in message
news:T1j5e.11405$FN4.303@.newssvr21.news.prodigy.co m...
> Hi,
> Using SQL Server 2000 with Windows 2000 Adv Server
> &
> Microsoft Access linked table (running stored procedure using ADO as
> follows:
> ************************************************** ********
> Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)
> Dim cnn As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim prm As ADODB.Parameter
> Dim msg As String
> On Error GoTo Err_AddrType_NotInList
> 'Exit the procedure if the combo box was cleared
> If Trim(NewData) = "" Then Exit Sub
> 'Confirm that the user wants to add AddrType
> msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
> msg = msg & "Do you want to add it?"
> If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
> 'If the user chose not to add AddrType, set the response
> 'argument to supress an error message and undo changes.
> Response = acDataErrContinue
> MsgBox "No record added.", vbOKOnly, "Action Cancelled"
> Else
> 'If the user chose to add AddrType, open a recordset
> 'using the AddrType table
>
> Set cmd = New ADODB.Command
> Set cnn = New ADODB.Connection
> cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
> Catalog=groomery;Integrated Security=SSPI;"
> cmd.ActiveConnection = cnn
> cmd.CommandText = "spInsertAddrType"
> cmd.CommandType = adCmdStoredProc
> Set prm = cmd.CreateParameter("AddrType", adVarChar,
> adParamInput, , Trim(NewData))
> cmd.Execute Parameters:=prm
> 'Set Response argument to indicate that new data is being added
> Response = acDataErrAdded
> cnn.Close
> Set cnn = Nothing
> End If
> Exit_AddrType_NotInList:
> Exit Sub
> Err_AddrType_NotInList:
> MsgBox Err.Description
> Response = acDataErrContinue
> ************************************************** ********
> "NewData" is a text string - in this case "Test"
> The stored procedure referenced in the code is:
> ************************************
> CREATE PROCEDURE [spInsertAddrType]
> (@.AddrType [nvarchar](50))
> AS
> INSERT INTO [groomery].[dbo].[tblAddrTypes]
> ([fldAddrType])
> VALUES
> (@.AddrType)
> GO
> *************************************
> When I execute this code, I receive the following error
> "Cannot update identity column 'fldAddrTypeID'."
> fldAddrTypeID is configured as follows:
> ***************************
> Data Type = int
> Identity = Yes
> Identity Seed = 1
> Identity Increment = 1
> ***************************
> The documentation I've found online concerning this error says that it is
> produced when you try to supply a value for an identity field without SET
> IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
> figure why I'm getting this error.
> Thanks for any help you can offer.
> Todd

Insert problem with linked server

Both servers running SQL 2000

I have set up on our local SQL server (using Enterprise Manager) a linked
server running on our ISP. Just did new linked server and added remote
password and login.

The following three queries work:

insert into LinkedServer.dbname.dbo.Table2
select *
from LinkedServer.dbname.dbo.Table1

select *
into LocalTable
from LinkedServer.dbname.dbo.Table1

insert into LocalTable
select *
from LinkedServer.dbname.dbo.Table1

This query, which is what we really want to do, does not work:

insert into LinkedServer.dbname.dbo.Table1
select *
from LocalTable

and returns the error: 'The cursor does not include the table being modified
or the table is not updatable through the cursor.'

I am new to all this and would welcome some help.

AdrianI believe I have now resolved this

In fact the example below would work

> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable

I was trying to insert into a table on the linked server that was not owned
by the dbo but by the remote username. It seems that providing the owner of
the table is dbo it will be OK.

Adrian.

"Adrian" <NoSpam@.hotmail.com> wrote in message
news:ANReb.6576$8_4.54623402@.news-text.cableinet.net...
> Both servers running SQL 2000
> I have set up on our local SQL server (using Enterprise Manager) a linked
> server running on our ISP. Just did new linked server and added remote
> password and login.
> The following three queries work:
> insert into LinkedServer.dbname.dbo.Table2
> select *
> from LinkedServer.dbname.dbo.Table1
> select *
> into LocalTable
> from LinkedServer.dbname.dbo.Table1
> insert into LocalTable
> select *
> from LinkedServer.dbname.dbo.Table1
>
> This query, which is what we really want to do, does not work:
> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable
> and returns the error: 'The cursor does not include the table being
modified
> or the table is not updatable through the cursor.'
> I am new to all this and would welcome some help.
> Adrian

INSERT Problem

I'm having an issue inserting records into an SQL server. We're using Access 2003 as the front end with an SBS2003 box running sql with all the latest patches.

Everything works fine with one computer accessing but when we have multiple computers (5) we experience problems. Each order has multiple details rows that are being inserted into a table using ADO w/SQL commands. The rows for each order are entered into the table and the appear correct. Then the rows for the specific order appear to get removed and then all added again (with the same time stamp) but with some of the data missing.

Any help would be appreaciated.

Cheers,
Jon_got code? see Brett's sticky at the top of the page. It sounds like you have an application bug dealing with concurrency.|||got code? see Brett's sticky at the top of the page. It sounds like you have an application bug dealing with concurrency.

Exactly the problem! We found a bug in the code and it was a concurrency issue.

Thanks for your help!

Cheers,
Jon_|||I am awesome at blind chess but not chess with the blindman|||Well, I read the title and I was thinking that some sort of initimate relationship manual might come in handy

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.
======================================================

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.
================================================== ====

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.
========================================
==============