inserted and deleted tables

ok i know the rows being affected are put in these temporary tables, but
if i do an insert with 5 rows does inserted have 5 rows in it?
if htats the case how do you check field values for every row
I was using
if (select newfield from #inserted) = this
begin
update #inserted set newfield = that
end
but that isnt going to work if inserted contains all 5 rows, i thought
inserted only had the current row and it passed through the instead of
trigger 5 times once for each row. if thats not the case how do you do
something like
for each newfield in #inserted do
if newfield is this
set it to this.
for example
say i have an insert with three fields
category, categoryid, name
and the 3 rows in my insert are
('Standard', 1, 'Toys')
('NonStandard, null, 'Games')
('Misc', null, 'Puzzles')
and in my instead of trigger i want to fill the nulls with the proper
number so in my instead of trigger i say
if (field2 is null)
begin
set field2 = (select rightnumber from mastertable where name = field2)
end
but it has to do it for each rowChris M wrote:
> ok i know the rows being affected are put in these temporary tables,
> but if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you
> do something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
Yes. The inserted and deleted logical tables contain all affected rows.
No. You cannot modify data in the inserted and deleted tables, so I'm
not quite sure how your code was even executing. The tables do not have
a '#' prefix. The are plainly 'inserted' and 'deleted'.
How would you get the "right number" from mastertable is the second
column is NULL. What are you joining on?
Personally, I would just throw up a RAISERROR. I don't really understand
your test scenario. If you could join up with mastertable, then it seems
you should be using a FK to that table rather than repeating data
values.
Could you provide the DDL for the tables in question.
David Gugick
Imceda Software
www.imceda.com|||It will do it for each row in inserted, just write the expression on the
right of the set newfield =...
so that it will be a different value for each row of inserted...
update Table set newfield =
Case newfield
When 'this' Then 'That'
When 'TheOther' Then 'OtherThat'
End
But what is #Inserted? a Temporary Table?
What are you trying to update in this trigger?
"Chris M" wrote:

> ok i know the rows being affected are put in these temporary tables, but
> if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you do
> something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Stop thinking procedure and start thinking sets. If there are 3 rows in
the inserted set and 2 have NULL in some column (as your example shows)
you can do an UPDATE like this:
UPDATE original_table
SET column_name = (select rightnumber
from mastertable m inner join inserted i
on m.<join cols> = i.<join cols> )
WHERE EXISTS (SELECT * FROM inserted
WHERE column_name IS NULL
AND inserted.ID = original_table.ID)
The "WHERE column_name IS NULL" in the UPDATE's WHERE clause subquery
will identify the rows in original_table that have the 'column_name' set
to the "rightnumber."
The <join cols> have to be a column, or columns, that uniquely identify
the rows in inserted that relate to rows in mastertable, so the
"rightnumber" can be retrieved. I would have to see the design of
mastertable and original_table to determine which columns those would
be. You could even do w/o the inserted set and just use something in
the mastertable that identifies which row in mastertable has the correct
data that is to be placed in the original_table. IOW, if you had a
Default value in mastertable that always goes in that column - data in
mastertable looks like this:
column_ rightnumber
-- --
Price 25
The SET subquery would look like this:
SET Price = (select rightnumber
from mastertable
where column_ = 'Price')
NB: By now you should realize that you can create a DEFAULT on the
column(s) in original_table instead of using a trigger like the above.
E.g.: CREATE TABLE T (col_1 int, col_a char(2) default ('zz'))
insert into t (col_1) values (2)
select * from t
col_1 col_a
-- --
2 zz
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmguqYechKqOuFEgEQJJSgCg8iAqLWvq7TwF
9BLQlhBbcY/uxx0AnRRi
DXmdayzhLIxU5WBk4wSL4x4R
=mDFy
--END PGP SIGNATURE--
Chris M wrote:
> ok i know the rows being affected are put in these temporary tables, but
> if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you do
> something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
>|||MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Stop thinking procedure and start thinking sets. If there are 3 rows in
> the inserted set and 2 have NULL in some column (as your example shows)
> you can do an UPDATE like this:
> UPDATE original_table
> SET column_name = (select rightnumber
> from mastertable m inner join inserted i
> on m.<join cols> = i.<join cols> )
> WHERE EXISTS (SELECT * FROM inserted
> WHERE column_name IS NULL
> AND inserted.ID = original_table.ID)
> The "WHERE column_name IS NULL" in the UPDATE's WHERE clause subquery
> will identify the rows in original_table that have the 'column_name' set
> to the "rightnumber."
> The <join cols> have to be a column, or columns, that uniquely identify
> the rows in inserted that relate to rows in mastertable, so the
> "rightnumber" can be retrieved. I would have to see the design of
> mastertable and original_table to determine which columns those would
> be. You could even do w/o the inserted set and just use something in
> the mastertable that identifies which row in mastertable has the correct
> data that is to be placed in the original_table. IOW, if you had a
> Default value in mastertable that always goes in that column - data in
> mastertable looks like this:
> column_ rightnumber
> -- --
> Price 25
> The SET subquery would look like this:
> SET Price = (select rightnumber
> from mastertable
> where column_ = 'Price')
> NB: By now you should realize that you can create a DEFAULT on the
> column(s) in original_table instead of using a trigger like the above.
> E.g.: CREATE TABLE T (col_1 int, col_a char(2) default ('zz'))
> insert into t (col_1) values (2)
> select * from t
> col_1 col_a
> -- --
> 2 zz
I can do that sometimes but if i want to set something like an id number
based on a column in another table that i cannot join on i can't do it
with a set
unless there is a command like
select * into #inserted from inserted
update #inserted
set keyfield = getNextValueFromTable()
insert into myTable select * from #inserted
but I cannot figure out how to get the getNextValueFromTable()
procedure since i cannot call stored procedures that way and UDF's
cannot access my table values
I am only doing this the way i'm doing it to maintain compatibility with
a program. If i was to design this myself I'd be doing it with
constraints, foreign keys, identities, etc|||Chris M wrote:
> MGFoster wrote:
>
< SNIP >
> I can do that sometimes[,] but if i want to set something like an id numbe
r
> based on a column in another table that i cannot join on i can't do it
> with a set
>
<SNIP >
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
How can you know which "id number[...]in another table" to use if you
cannot join on it? That implies that there is "some other" way of
determining the relationship between one table and another; and, that
that relationship is defined outside the database. This goes against
RDB design principles.
If the "id number [is] based on a column in another table" that means
there is a relationship between the 2 tables. If there is a
relationship between the 2 tables you can join them.
So, what's going on there? ;-)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmgyZYechKqOuFEgEQJd5QCfaDO4xXjYNt2S
oYbvwG9acyT4ncAAoPyO
BL03rAJrHegoe1ktC8L/pRBI
=8GJu
--END PGP SIGNATURE--|||What do you mean..
<snip> ...that i cannot join on ...</snip>
Why Not?
If the objective here is to insert some records into MyTable, then just do
that in the trigger
Insert MyTable
Select <Stuff>
From inserted
The <Stuff> above needs t oeb written as a set-based expression, (Set of
expressions), such that the values will be appropriate... But there's no way
for us to guess what that is until you tell us whjat you are trying to do
with getNextValueFromTable()...
again, if all you are tyrying to do is set the value based on the value in
the inserted table, then, as an example...
Insert MyTable
Select <OtherColumns>,
Case newField
When <ValueA> Then <outValueA>
When <ValueB> Then <outValueB>
When <ValueC> Then <outValueC>
When <ValueD> Then <outValueD>
Else <OutVAlueDefault> End
From inserted|||MGFoster wrote:
> Chris M wrote:
>
> < SNIP >
>
> <SNIP >
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> How can you know which "id number[...]in another table" to use if you
> cannot join on it? That implies that there is "some other" way of
> determining the relationship between one table and another; and, that
> that relationship is defined outside the database. This goes against
> RDB design principles.
> If the "id number [is] based on a column in another table" that means
> there is a relationship between the 2 tables. If there is a
> relationship between the 2 tables you can join them.
> So, what's going on there? ;-)
A table called generators
create table generators (
generator_name varchar(50),
generator_lastid integer
)
in my table say myTable if i want to get the next ID from generators i
have to
select generator_lastid from generators where generator_name =
'gen_id_mytable)
so i do not know how i can join on that, and I'm sure this does violate
some rule, but its meant to simulate the sequence/generator object of
oracle/interbase/firebird|||> if i do an insert with 5 rows does inserted have 5 rows in it?
Yes if the insert/update was done as a single statement.

> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
What is "this"? Is the idea to override the values being inserted/updated in
the
trigger? If that is the case, then you need an InsteadOf trigger not an Afte
r
trigger.

> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of tri
gger
> 5 times once for each row. if thats not the case how do you do something like[/co
lor]
No. That is not the case. Each *statement* fires the trigger once (ignoring
cascades for the moment). Thus, imagine the statement:
Insert Table(F1...Fn)
Select F1...FN
From Table
That might insert 1000 records with that once statement. That statement will
fire the trigger once and populate the "inserted" table with 1000 records. I
f it
is an update, then you will get 1000 records in the "inserted" table and 100
0
records in the "deleted" table.
> for each newfield in #inserted do
> if newfield is this
> set it to this.
Can't do that with an After trigger. You need to do that with an InsteadOf
trigger.

> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper numb
er
> so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
Create Table Stuff
(
Category VarChar(50) Not Null
, SomeNumber Int Null
, Description VarChar(50) Not Null
)
Create Table SomeOtherTable
(
SingleValue Int
)
Insert SomeOtherTable(SingleValue) Values(99)
Create Trigger trigStuff On dbo.Stuff
Instead Of Insert
As
Begin
Insert Stuff(Category, SomeNumber, Description)
Select Category
, (Select SingleValue From SomeOtherTable)
, Description
From inserted As I
End
Insert Stuff(Category, SomeNumber, Description) Values('Standard', 1, 'Toys'
)
Insert Stuff(Category, SomeNumber, Description) Values('NonStandard', Null,
'Games')
Insert Stuff(Category, SomeNumber, Description) Values('Misc', Null, 'Puzzle
s')
Select * From Stuff
HTH
Thomas|||Chris M wrote:
> MGFoster wrote:
>
>
> A table called generators
> create table generators (
> generator_name varchar(50),
> generator_lastid integer
> )
>
> in my table say myTable if i want to get the next ID from generators i
> have to
> select generator_lastid from generators where generator_name =
> 'gen_id_mytable)
> so i do not know how i can join on that, and I'm sure this does violate
> some rule, but its meant to simulate the sequence/generator object of
> oracle/interbase/firebird
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Ah... In that case you can just insert that value "generator_lastid"
into the NULL columns in the original table like this (this is in the
trigger):
UPDATE original_table
SET null_column = (SELECT generator_nextid FROM generators
WHERE generator_name = null_column_name)
WHERE id IN (SELECT id FROM inserted WHERE null_column IS NULL)
Substitute correct table/column names where appropriate.
Each row would get the same number. This won't work if you want
incrementing numbers in each row that had the NULL valued column. There
is no way to increment the nextid for the next call. A function can't
be used 'cuz ya can't run an UPDATE inside a function (to increment the
nextid). A procedure can't be used 'cuz ya can't use a procedure as a
recordsource, like ya can w/ a function.
Looks like (ugh!) a WHILE loop would have to be used to cycle thru all
the inserted rows that had NULL values in the column.
@.count = (select count(*) from inserted where column_name is null)
while @.count > 0 begin
-- do the update & generate new nextid
@.count = @.count - 1
end
Quite a problem.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmhsb4echKqOuFEgEQL6JwCguTf9eD2kFh7F
fZDJAtnUhIKvKWwAniqR
xD1fe46JZ8B2jXX11NQRQmtJ
=xD8y
--END PGP SIGNATURE--

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 TIME only in DateTime field

I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?That is how datetime fields work. You need to address this using formatting on the client side. DateTime has a number of useful ToString() overloads.|||Thanks for your help. It is too bad Access doesn't function in datatypes more similarly to SQL, especially since they are products of the same company. This migration would go alot easier. I am migrating some VBScript applications from Access to SQL, then from VBScript to VB.Net. It has been quite fun so far. Hehehe...

insert stored procedure result into temporary table ?

I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.

Here is an example:

-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END

-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc

-- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))

-- this call will succeed
INSERT INTO testTable EXEC testProchow about defining your temp table before inserting into it?|||I'd really prefer to not create a hard dependency on the exact columns returned from the procedure. For instance, if I add another column to the resultset of the procedure, then the 'INSERT INTO EXEC' call will fail unless the predefined table for it is also updated.
I'm basically making a passthough procedure which will call another procedure and return the results as XML. So the base procedure may be called directly or via the passthrough.|||It is considered poor programming practice to use "SELECT *", which is essentially what you are doing when you don't pre-define your table layout.

If you add a column to your stored procedure, then your statement SHOULD fail. That ensures that you review your code to catch any other problems that might arise from the schematic change, and that is what the "testing" phase of development is all about.|||I don't think you know enough about what I'm doing to make that statement blindman. I am making a procedure that is just a passthough call to another procedure. The base procedure that is being called is where all the work is done. The passthrough procedure only exists because some clients will be calling the procedure via HTTP and expecting XML results rather than calling the procedure 'directly' via ODBC or JDBC. If I need to make a change to the base procedure, I don't want to have to also make that change to the passthough version. That creates more dependencies and unnecessary maintenance.
Ideally, a better design might be to add a parameter to the base procedure to tell it whether the results should be returned as XML or not... But I need more restrictive control over the HTTP-XML version for security reasons and it would be cleaner to go the passthrough route.|||I don't think you know enough about what I'm doing to make that statement blindman. I am making a procedure that is just a passthough call to another procedure. The base procedure that is being called is where all the work is done. The passthrough procedure only exists because some clients will be calling the procedure via HTTP and expecting XML results rather than calling the procedure 'directly' via ODBC or JDBC.That's exactly what I thought you were doing. Good coding practice calls for enumerating your datasets.

Insert record into temporary table from a select statement

Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" ... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chuai believe you can do it in a simpler way. can you post some sample data and the output you are looking for?|||here is the sample data. i want to group them to be able to use in 3 record in this case.

weekgroup mark updatedate
Week 1 100.000000 2006-01-03 09:37:15.000
Week 1 100.000000 2006-01-06 12:18:09.000
Week 1 71.600000 2006-01-06 12:59:46.000
Week 1 100.000000 2006-01-06 13:03:52.000

Week 2 95.000000 2006-01-09 11:49:17.000
Week 2 100.000000 2006-01-09 12:19:19.000

Week 3 100.000000 2006-01-16 15:03:24.000
Week 3 71.600000 2006-01-16 15:05:31.000
Week 3 100.000000 2006-01-17 15:59:43.000
Week 3 100.000000 2006-01-17 16:57:38.000

--------
here is the code i did. i set the @.dtstart = 1st day of the month
@.dtWeekEnd = 1 week after the 1st day of the month
@.dtEnd = end of the month

i try to group by the weekgroup but it fail. and it only allow to group by the last update date(Answer.dtAnswer).

SELECT CASE
WHEN day(@.dtStart) <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) THEN 'Week 1'
WHEN day(@.dtStart)+7 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 7 THEN 'Week 2'
WHEN day(@.dtStart)+14 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 14 THEN 'Week 3'
WHEN day(@.dtStart)+21 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 21 THEN 'Week 4'
else 'Week 5'
END as weekgroup, AVG(Answer.bScore) AS tScore, dtAnswer -- CONVERT(INT, AnswerKey.bRowId) AS bRowId-- day(Answer.dtAnswer) as days
FROM Answer INNER JOIN
AnswerKey ON Answer.lAnswerId = AnswerKey.lAnswerId INNER JOIN
QuestionDef ON AnswerKey.iQuestionDefId = QuestionDef.iQuestionDefId INNER JOIN
QuestionAnswerDef ON QuestionDef.iQuestionDefId = QuestionAnswerDef.iQuestionDefId AND AnswerKey.bKeyId = QuestionAnswerDef.bKeyId
WHERE (Answer.iMinutes BETWEEN 570 AND 1020) and (Answer.dtAnswer >= @.dtStart) AND (Answer.dtAnswer < @.dtEnd) and (Answer.iTemplateId = 1)
GROUP BY Answer.iTemplateId, AnswerKey.bRowId, QuestionDef.sQuestion, Answer.fDiscard,
QuestionAnswerDef.sAnswer, Answer.fIncomplete, AnswerKey.bScore, AnswerKey.bRowId, dtAnswer--, WeekGroup--, NoOfWeek
having (Answer.fDiscard = 0) AND (Answer.fIncomplete = 0)
order by dtAnswer--);|||what i understand, from the long sql, is you have a table with a numeric col and a date col and you want to have a AVG of the numeric col on at an interval of every 7 days, staring from the first day of the month...

an example to do the above for 1st half and 2nd half of the month could be

select avg(<Numeric Col>),
case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end
from table2
group by case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end

here the first date of the month is '20060101'|||thank for your advice, i found a solution from your sample.

i dun know using the case also can put at group by so problem solve easily.

thanks a lot:D
regards
terence chua

Insert Query with stored procedure

Hi,

is it possible to create an "INSERT INTO .... "Select from stored
procedure" Query?

I want to create an temporary table. In this table I want to enter the data,
which I can get from an stored procedure.
But in the FROM-clause a stored procedure is not allowed?"Harald" <yixxu@.yahoo.de> wrote in message news:bfhh70$jt5$1@.online.de...
> Hi,
> is it possible to create an "INSERT INTO .... "Select from stored
> procedure" Query?
> I want to create an temporary table. In this table I want to enter the data,
> which I can get from an stored procedure.
> But in the FROM-clause a stored procedure is not allowed?

Use INSERT INTO/EXEC, e.g.,

INSERT INTO #TempTable (col1, col2, col3)
EXEC MyStoredProcedure 1, 2

Regards,
jag

insert query question

Hi I have two temporary tables in a query and need to combine them as
described below. Thanks.
Table 1-results from query 1 based on a start and end date
*********************************************
*day * location * type * cost * Name* color*weight*
*********************************************
*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
***********************************************
* 2/3/07* calif * food * .50 * candy * blue *.1lb *
***********************************************
Table 2 list of all names
***************************
* name * location * Cost * Type *
***************************
*cat * AZ * $2.00 * animal *
***************************
*hamer *Texas *$1.0 *tool *
***************************
*candy *calif *.50 * food *
****************************
table 2 lists all of the named items. I would like to insert records from
table2 into table 1 in a fashion that will leave table 2 with all of the
named items for each date, as shown below. It does not write over what is in
table one but inserts records so all named items show up for every day.
Table1 after updated
*********************************************
*day * location * type * cost * Name* color*weight*
*********************************************
*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
***********************************************
*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
table2
**********************************************
*2/1/07* calif * food * .50 * candy *NULL *NULL *from
table2
**********************************************
* 2/3/07* calif * food * .50 * candy * blue *.1lb *
***********************************************
* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
***********************************************
*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
***********************************************
--
Paul G
Software engineer.Without knowing really basic stuff like keys it is pure guesswork
trying to write a query.
Something like this might get you started. Or not. The general idea
is to use a CROSS JOIN of the dates against the names to get the set
of rows you want in the results, then join that result to the detail
to fill in the rest.
SELECT A.day, B.location, B.type, B.cost, B.Name,
C.color, C.weight
FROM (SELECT DISTINCT day FROM Tbl1) as A
CROSS JOIN
Tbl2 as B
LEFT OUTER
JOIN Tbl1 as C
ON A.day = C.day
AND A.name = C.name
Roy Harvey
Beacon Falls, CT
On Mon, 20 Aug 2007 13:36:00 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I have two temporary tables in a query and need to combine them as
>described below. Thanks.
>Table 1-results from query 1 based on a start and end date
>*********************************************
>*day * location * type * cost * Name* color*weight*
>*********************************************
>*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
>***********************************************
>* 2/3/07* calif * food * .50 * candy * blue *.1lb *
>***********************************************
>Table 2 list of all names
>***************************
>* name * location * Cost * Type *
>***************************
>*cat * AZ * $2.00 * animal *
>***************************
>*hamer *Texas *$1.0 *tool *
>***************************
>*candy *calif *.50 * food *
>****************************
>table 2 lists all of the named items. I would like to insert records from
>table2 into table 1 in a fashion that will leave table 2 with all of the
>named items for each date, as shown below. It does not write over what is in
>table one but inserts records so all named items show up for every day.
>Table1 after updated
>*********************************************
>*day * location * type * cost * Name* color*weight*
>*********************************************
>*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
>***********************************************
>*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
>table2
>**********************************************
>*2/1/07* calif * food * .50 * candy *NULL *NULL *from
>table2
>**********************************************
>* 2/3/07* calif * food * .50 * candy * blue *.1lb *
>***********************************************
>* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
>***********************************************
>*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
>***********************************************|||thanks for the response. I left off the key column. Table 1 it is
**************************************************
day_id prim key * day (datetime)* type,name cost are all (varchar(20))
and table2 is
***************************************************
name_id prim key * location cost type are all (varchar(20)).
--
I will try what you have provided.
Paul G
Software engineer.
"Roy Harvey" wrote:
> Without knowing really basic stuff like keys it is pure guesswork
> trying to write a query.
> Something like this might get you started. Or not. The general idea
> is to use a CROSS JOIN of the dates against the names to get the set
> of rows you want in the results, then join that result to the detail
> to fill in the rest.
> SELECT A.day, B.location, B.type, B.cost, B.Name,
> C.color, C.weight
> FROM (SELECT DISTINCT day FROM Tbl1) as A
> CROSS JOIN
> Tbl2 as B
> LEFT OUTER
> JOIN Tbl1 as C
> ON A.day = C.day
> AND A.name = C.name
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 20 Aug 2007 13:36:00 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I have two temporary tables in a query and need to combine them as
> >described below. Thanks.
> >
> >Table 1-results from query 1 based on a start and end date
> >
> >*********************************************
> >*day * location * type * cost * Name* color*weight*
> >*********************************************
> >*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
> >***********************************************
> >* 2/3/07* calif * food * .50 * candy * blue *.1lb *
> >***********************************************
> >Table 2 list of all names
> >***************************
> >* name * location * Cost * Type *
> >***************************
> >*cat * AZ * $2.00 * animal *
> >***************************
> >*hamer *Texas *$1.0 *tool *
> >***************************
> >*candy *calif *.50 * food *
> >****************************
> >table 2 lists all of the named items. I would like to insert records from
> >table2 into table 1 in a fashion that will leave table 2 with all of the
> >named items for each date, as shown below. It does not write over what is in
> >table one but inserts records so all named items show up for every day.
> >Table1 after updated
> >*********************************************
> >*day * location * type * cost * Name* color*weight*
> >*********************************************
> >*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
> >***********************************************
> >*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
> >table2
> >**********************************************
> >*2/1/07* calif * food * .50 * candy *NULL *NULL *from
> >table2
> >**********************************************
> >* 2/3/07* calif * food * .50 * candy * blue *.1lb *
> >***********************************************
> >* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
> >***********************************************
> >*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
> >
> >***********************************************
>