"Mark Findlay" <mfindlay@speakeasy.org> wrote in message
news:ugvU0oSeFHA.3700@TK2MSFTNGP10.phx.gbl...
>I am invoking a stored procedure from a web page, and the stored procedure
>correctly inserts a couple records. The calling web page then attempts to
>retreive a variable created by the stored procedure (called @@GUID) but
>this fails.
>
> The mystery is that the same stored procedure, invoked from Query Analyzer
> works fine and returns the @@GUID. (I display the query that my web page
> invokes the SP with so as to ensure the Analyzer is executing the query
> with the exact same values).

"Mark Findlay" <mfindlay@speakeasy.org> wrote in message
news:ugvU0oSeFHA.3700@TK2MSFTNGP10.phx.gbl...
>I am invoking a stored procedure from a web page, and the stored procedure
>correctly inserts a couple records. The calling web page then attempts to
>retreive a variable created by the stored procedure (called @@GUID) but
>this fails.
>
> The mystery is that the same stored procedure, invoked from Query Analyzer
> works fine and returns the @@GUID. (I display the query that my web page
> invokes the SP with so as to ensure the Analyzer is executing the query
> with the exact same values).
>
> Below is the execution string from the web page, and the stored proc
> source code. I can't figure out why it would work ok under Query Analyzer,
> and not my web page. Any ideas? Could the TRANSACTION have any bearing on
> the ability to return the @@GUID to the web page?
>
> Thanks!
> Mark
>
> =============== EXECUTION CODE from WEB PAGE ==========
> Here is the stored proc execution string displayed from the web page (ASP)
>
> sp_insertreservationrecord
> 1004,'{6DC81E6B-4361-41AF-BBCB-3C81B128B35C}','{00000000-0000-0000-0000-000000000000}','06/01/2005
> 00:00:00','06/02/2005 23:59:00',0,1,11,11,11,'My
> Company','Mark','a','Findlay','My
> Address','','Seattle','WA','98117','US','206-123-1234','','','mfindlay@mydomain.org','',0,1,1
>
> Here is the stored procedure: (the @@GUID is returned at the end)
>
> ============== STORED PROCEDURE =================
>
> CREATE PROCEDURE [dbo].[sp_insertreservationrecord]
>
> @s_reservationnumber decimal,
> @s_propertyguid uniqueidentifier,
> @s_userguid uniqueidentifier,
> @s_startdate smalldatetime,
> @s_enddate smalldatetime,
> @s_type int,
> @s_status int,
> @s_subtotal smallmoney,
> @s_subtotal_adjustment smallmoney,
> @s_amt_received smallmoney,
> @s_company nvarchar(255)=N'',
> @s_firstname nvarchar(100),
> @s_initial nchar(1)=N'',
> @s_lastname nvarchar(100),
> @s_address1 nvarchar(255),
> @s_address2 nvarchar(255)=N'',
> @s_city nvarchar(100),
> @s_state nvarchar(100),
> @s_zip nvarchar(10),
> @s_country nvarchar(50),
> @s_phone nvarchar(25),
> @s_fax nvarchar(25),
> @s_cell nvarchar(25),
> @s_email nvarchar(25),
> @s_comments ntext=N'',
> @s_maintdays_before int=0,
> @s_maintdays_after int=0,
> @s_mainttype int
>
> AS
>
> SET NOCOUNT ON
>
> /* We will use this for the reservation and for any maintenance
> reservation records we create so that
> they will all be deleted when we delete the reservation by guidid */
> DECLARE @s_reservationguid UniqueIdentifier
>
> /* generate a new reservation guid */
> set @s_reservationguid = NewID()
>
> /* Begin the transaction. Only if all criteria and insertions succeed will
> we commit the trans. Otherwise we rollback*/
> BEGIN TRANSACTION
>
> /* Execute the lookup that will ensure that the desired dates are
> available */
> SELECT startdate FROM _reservations WITH (TABLOCKX)
> WHERE (propertyguid = @s_propertyguid AND (startdate <= DATEADD([day],
> @s_maintdays_after, CONVERT(DATETIME, @s_enddate, 102)))
> AND (enddate >= DATEADD([day], -@s_maintdays_before,
> CONVERT(DATETIME, @s_startdate, 102))))
>
>
> IF (@@ERROR = 0) AND (@@ROWCOUNT > 0)
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Reservation dates already taken', 16, 1)
> RETURN -1
> END
>
> /* the dates were available, so insert user's new reservation*/
> INSERT INTO _reservations
> (reservationnumber, propertyguid, userguid, startdate, enddate, type,
> status, subtotal, subtotal_adjustment, amt_received, company, firstname,
> initial, lastname, address1, address2, city, state, zip, country, phone,
> fax, cell, email, comments)
> values (@s_reservationnumber,
> @s_propertyguid,@s_userguid,@s_startdate,@s_enddate,@s_type,@s_status,@s_subtotal,@s_subtotal_adjustment,
> @s_amt_received, @s_company, @s_firstname, @s_initial, @s_lastname,
> @s_address1, @s_address2, @s_city, @s_state, @s_zip, @s_country, @s_phone,
> @s_fax, @s_cell, @s_email, @s_comments)
>
> IF (@@ERROR <> 0) OR (@@ROWCOUNT <> 1)
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Error executing initial insert statement', 16, 1)
> RETURN -2
> END
>
> /* BEGIN MAINTENANCE CHECK =====================================
> if a maintenance record also needs to be created for this reservation,
> create it using the same values as the
> original reservation, except the dates, and also the TYPE will be set to
> 'maintenance' */
> IF @s_maintdays_before > 0 OR @s_maintdays_after > 0
> BEGIN
> /* Set the maintenance type*/
> set @s_type = @s_mainttype
>
> /* Set the maintenance date */
> IF @s_maintdays_before > 0
> BEGIN
> set @s_startdate = DATEADD([day], -@s_maintdays_before, CONVERT(DATETIME,
> @s_startdate, 102))
> END
> ELSE
> BEGIN
> set @s_startdate = DATEADD([day], @s_maintdays_after, CONVERT(DATETIME,
> @s_enddate, 102))
> END
> /* set the end date to the same date as the start date for now. If ever
> pass the number of days required, then
> we will add those days to the start date to calculate the available days
> and the maint days*/
> set @s_enddate = @s_startdate
>
> /* Insert the maintencen record */
> INSERT INTO _reservations
> (reservationnumber, propertyguid, userguid, startdate, enddate, type,
> status, subtotal, subtotal_adjustment, amt_received, company, firstname,
> initial, lastname, address1, address2, city, state, zip, country, phone,
> fax, cell, email, comments)
> values (@s_reservationnumber,
> @s_propertyguid,@s_userguid,@s_startdate,@s_enddate,@s_type,@s_status,@s_subtotal,@s_subtotal_adjustment,
> @s_amt_received, @s_company, @s_firstname, @s_initial, @s_lastname,
> @s_address1, @s_address2, @s_city, @s_state, @s_zip, @s_country, @s_phone,
> @s_fax, @s_cell, @s_email, @s_comments)
>
> /* failed? */
> IF (@@ERROR <> 0) OR (@@ROWCOUNT <> 1)
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Error executing maintenance insert statement', 16, 1)
> RETURN -3
> END
>
> END /* END MAINTENANCE CHECK ===========================================
> */
>
> /* SUCCESS:
> At this point, we're all done. Return successful guid to the caller and
> unlock the table*/
> COMMIT TRANSACTION
>
> /* This GUID will be returned to the client as a recordset */
> SELECT @s_reservationguid AS '@@GUID'
> RETURN 0
> GO
>