Insert SQL does not work in stored procedure.
From: Mike G (MikeG_at_discussions.microsoft.com)
Date: 01/17/05
- Next message: oj: "Re: Insert SQL does not work in stored procedure."
- Previous message: oj: "Re: Challenging Query"
- Next in thread: oj: "Re: Insert SQL does not work in stored procedure."
- Reply: oj: "Re: Insert SQL does not work in stored procedure."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 17 Jan 2005 14:29:03 -0800
On one of our development servers I've run into a problem with Insert
command. The stored procedure works on other servers and used to work on
this server. I've reduced the actual code to a trivial set that still
exhibits the behavior.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER procedure sp_wr_xmldoc3_ex @doc_passed text -- uniqueidentifier
as
begin tran
------------------------------------------------------------------------------------
-- DEBUG!
insert into Logical_Tag_Data
( uopLogicalTagId, uopTimeStart, uopTimeEnd, uopFltValue, uopStrValue )
values
(
'3AF75E96-F354-49E2-97AC-DC9779EE7B5F',
'2005-01-11 00:00:00.000',
'2005-01-11 23:00:00.000',
723.75238037109398,
'723.75238037109398'
)
select * from Logical_tag_data where
uopLogicalTagId='3AF75E96-F354-49E2-97AC-DC9779EE7B5F'
commit tran
return 1
------------------------------------------------------------------------------------
Here is the output:
uopLogicalTagId uopTimeStart
uopTimeEnd
uopFltValue uopStrValue
uopPercentGood
------------------------------------
------------------------------------------------------
------------------------------------------------------
-----------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------
3AF75E96-F354-49E2-97AC-DC9779EE7B5F 2005-01-11 00:00:00.000
2005-01-11 23:00:00.000
723.75238037109386 723.75238037109398
NULL
@RETURN_VALUE = 1
After this if I run a simple query (select * from Logical_tag_data where
uopLogicalTagId='3AF75E96-F354-49E2-97AC-DC9779EE7B5F') I don't see the
record I just added. And if I run this stored procedure a second time, again
only 1 record is being displayed. However, if I run the following commands
with the exact same insert everything works fine:
BEGIN TRANSACTION
insert into Logical_Tag_Data
( uopLogicalTagId, uopTimeStart, uopTimeEnd, uopFltValue, uopStrValue )
values
(
'3AF75E96-F354-49E2-97AC-DC9779EE7B5F',
'2005-01-11 00:00:00.000',
'2005-01-11 23:00:00.000',
723.75238037109398,
'723.75238037109398'
)
COMMIT
select * from Logical_tag_data where
uopLogicalTagId='3AF75E96-F354-49E2-97AC-DC9779EE7B5F'
Every time I run it I get an additional record (as expected).
I've tried running the database maintenance to make sure that the database
is not corrupt. And I've tried making a brand new table, but the stored
procudure insert still does not work.
Help!
Thanks,
Mike
- Next message: oj: "Re: Insert SQL does not work in stored procedure."
- Previous message: oj: "Re: Challenging Query"
- Next in thread: oj: "Re: Insert SQL does not work in stored procedure."
- Reply: oj: "Re: Insert SQL does not work in stored procedure."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
