Re: Insert SQL does not work in stored procedure.
From: oj (nospam_ojngo_at_home.com)
Date: 01/17/05
- Next message: oj: "Re: 2 servers"
- Previous message: Mike G: "Insert SQL does not work in stored procedure."
- In reply to: Mike G: "Insert SQL does not work in stored procedure."
- Next in thread: Mike G: "Re: Insert SQL does not work in stored procedure."
- Reply: Mike G: "Re: Insert SQL does not work in stored procedure."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 17 Jan 2005 14:36:40 -0800
You're experiencing 'implicit transaction' mode.
http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_7mur.asp
"Mike G" <MikeG@discussions.microsoft.com> wrote in message
news:9A3B99A5-C119-489B-8670-C58C2FBD19CB@microsoft.com...
> 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: 2 servers"
- Previous message: Mike G: "Insert SQL does not work in stored procedure."
- In reply to: Mike G: "Insert SQL does not work in stored procedure."
- Next in thread: Mike G: "Re: Insert SQL does not work in stored procedure."
- Reply: Mike G: "Re: Insert SQL does not work in stored procedure."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
