Really tough ADO Stored Procedure Question. Please Help!

Hi
I was hopping some one can give me an explanation for this.
Stored Procedure: (All this is in one stored procedure but this is the
basis
of what it does)
Step 1: I have a stored procedure that selects some records from
tblTableOne
into #Temp1.
Step 2: It then goes to and excel workbook and gets some data and holds
that
in a #Temp2.
Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
Step 4: Once this is done I then Insert the data from #Temp1 back into
tblTableOne as new records.
Scenario:
When running in debug mode in Query Analyser the stored procedure works
perfectly.
When I then check the data it has all been accurately updated and inserted
back into tblTableOne.
When I run the Stored procedure from using the "exec sp_Name" it works
perfectly also.
BUT.
When I run the Stored procedure from my VB 6 application at first glance it
seems to work fine in the fact that there is no errors and when the data is
checked it is there.
However the data has not been changed to the Data in #Temp2. So something
is
going wrong with Step 2 or 3.
So I then added an output parameter to the SP to check that there was data
being imported form Excel and there were the correct no of records. and
there are.
So that has now narrowed it down to Step 3. Since it works fine from Query
Analyser and no errors are being thorn by the stored procedure. I have also
added Error handling just after the UPDATE statement and there is no error
in @.@.ERROR.
I have been battling with this for about 12 hours now.
Please any thing that I could try would be welcome.
I have been trying different database option but no luck. Maybe I am
setting
them incorrectly or in the wrong place.
My guess is that I think that just that one UPDATE statement is rolling
back.
It doesn't sound possible I know, but I am watching it.
I have tried creating a transaction inside the SP and I have also tried
creating a transaction for the database connection using ADO. and still no
joy.
I know it is a tough one.
I am willing to email the SP and sample data it your up for a challenge.
Thanks
IanIs SET NOCOUNT ON the first line in the sproc?
--Mary
On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:

>Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
>basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
>tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
>that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2
.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance i
t
> seems to work fine in the fact that there is no errors and when the data i
s
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
>is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have als
o
> added Error handling just after the UPDATE statement and there is no error
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
>setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>|||Hi Mary
Yeah it is. Here is the Stored Procedure.
--START--
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ----
---
-- Name:
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import
--
-- Summary: This will Copy a Selection of instructions Change there
Instruction Order Number to keep them
-- In Order and allow them to go between 2 instructions and then inserts
them back into the table.
--
-- Example calls : EXEC
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import 17,
96, 1, 1, 'C:\temp\ImportData.xls'
--
-- Version: 001
-- History: Created on 11/08/2004
--
-- Author: Ian Killoran
--
-- Version Date Modifier Description
--
--
--
--
--
CREATE PROCEDURE
sp_CopyPast_Instructions_Between_Scenari
o_With_Excel_Import(
@.lScenarioID_CopyFrom int,
@.lScenarioID_PastTo int,
@.lInsertAfterInstruction int,
@.lNoOfTimes int = 1,
@.ImportFilePath varchar(255),
@.Test int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET IMPLICIT_TRANSACTIONS ON
--SET REMOTE_PROC_TRANSACTIONS ON
--Strings
DECLARE @.ErrMSG varchar(400)--This is the max msg size
DECLARE @.sSQL varchar(8000)
DECLARE @.sFieldListSQL varchar(8000)
DECLARE @.sSelectSQL varchar(8000)
DECLARE @.sCreateTableSQL varchar(8000)
DECLARE @.sFieldName varchar(200)
DECLARE @.sDataType varchar(200)
DECLARE @.AddField varchar(100)
DECLARE @.ImportDataName varchar(100)
DECLARE @.ImportFileName varchar(300)
--Int
DECLARE @.lNewRowCount int
DECLARE @.lLength int
DECLARE @.lRowCountHolder int
DECLARE @.lRowCountCurrent int
DECLARE @.iLoopControl int
DECLARE @.lFirstComma int
DECLARE @.lRecordCount int
--Decimal
DECLARE @.dOrderIncrement decimal(28,20)
DECLARE @.fNewOrderIncrement float(30)
--Prep Variables
SET @.ErrMSG = ''
SET @.sSQL = ''
SET @.sSelectSQL = ''
SET @.sCreateTableSQL = ''
SET @.sFieldName = ''
SET @.sDataType = ''
SET @.ImportDataName = ''
SET @.iLoopControl = 1
SET @.lLength = 0
SET @.lNewRowCount = 0
SET @.fNewOrderIncrement = @.lInsertAfterInstruction
SET @.lFirstComma = 0
--BEGIN TRANSACTION
BEGIN TRANSACTION
--FIRST THERE IS THE USER INPUT VALIDATION
IF @.lScenarioID_CopyFrom IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy from.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lScenarioID_PastTo IS Null
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a scenario to copy to.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lInsertAfterInstruction < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The insert after value must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.lNoOfTimes < 1
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'The No of inserts must be greater than one.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
IF @.ImportFilePath = ''
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There must be a valid import File Path.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--END OF INPUT VALIDATION
-- WORK OUT THE INCREMENT TO KEEP THE RECORDS IN ORDER
SELECT @.lRecordCount = count(InstructionID) FROM tbl_BTP_Instructions WHERE
ScenarioID = @.lScenarioID_CopyFrom
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem getting the record count'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.dOrderIncrement = 1/((cast(@.lRecordCount as float) + @.lNoOfTimes)
* @.lNoOfTimes)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem Calculating the new increment value.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--This is a fix for if they only want to copy one record
IF @.dOrderIncrement = 1
BEGIN
SELECT @.dOrderIncrement = 0.5
END
-- CREATE THE DYNAMIC FIELD LIST
STRINGS ---
-- #FieldData to hold the data for later use
Create Table #FieldData (FieldName varchar(200), DataType varchar(200),
Length int, lRowCount int)
--Fill #FieldData with data
SET @.sSQL = 'INSERT INTO #FieldData SELECT syscolumns.name AS FieldName,
systypes.name AS DataType, syscolumns.length, 0' +
' FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN' +
' systypes ON syscolumns.xusertype = systypes.xusertype' +
' WHERE (sysobjects.name = N' + char(39) + 'tbl_BTP_Instructions' +
char(39) + ')' +
' ORDER BY syscolumns.colid'
EXEC(@.sSQL)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the field date into the Temp
table.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Fill up the count field for us in the loop
UPDATE #FieldData
SET @.lNewRowCount = lRowCount = (@.lNewRowCount + 1)
--get the first recorde
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
--Fill the variables with the first reccords data
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
WHILE @.iLoopControl = 1
--Now loop round creating the strings for later use in Dynamic SQL statment
BEGIN
IF @.sFieldName <> 'InstructionID' --No need for this field it is and
IDENTITY
BEGIN
SET @.sSelectSQL = @.sSelectSQL + @.sFieldName + ', '
END
IF @.sDataType = 'int' OR @.sDataType = 'tinyint' --No need to specify size
for these
IF @.sFieldName = 'ScenarioID' OR @.sFieldName =
'InstructionID' --InstructionID is IDENTITY and ScenarioID is created
manually
BEGIN
-- This is really just doing nothing so that it misses out the ScenarioID
and Instruction ID
SET @.sCreateTableSQL = @.sCreateTableSQL
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + '
' + @.sDataType + ', '
END
ELSE
IF @.sFieldName = 'InstructionOrder' --This one needs to be bigger
than the actual table
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName +
' ' + @.sDataType + '(30), '
END
ELSE
BEGIN
SET @.sCreateTableSQL = @.sCreateTableSQL + ' ADD ' + @.sFieldName + ' ' +
@.sDataType + '('+ cast(@.lLength as varchar(100)) + '), '
END
-- Reset looping variables.
SELECT @.lRowCountHolder = NULL
-- get the next @.lRowCountHolder
SELECT @.lRowCountHolder = MIN(lRowCount)
FROM #FieldData
WHERE lRowCount > @.lRowCountCurrent
-- did we get a valid next @.lRowCountHolder?
IF ISNULL(@.lRowCountHolder,0) = 0
BEGIN
--This will happen at table EOF
BREAK
END
--Else get the next row.
SELECT @.sFieldName=FieldName, @.sDataType=DataType, @.lLength=Length,
@.lRowCountCurrent = lRowCount
FROM #FieldData
WHERE lRowCount = @.lRowCountHolder
END
--Remove the last Comma ,
Select @.sSelectSQL = LEFT(@.sSelectSQL,Len(@.sSelectSQL)-1)
--Print @.sSelectSQL
--Print @.sCreateTableSQL
DROP TABLE #FieldData
SET @.sSQL = ''
-- END SECTION ----
--CREATE TWO TABLES ONE FOR REORDEING CALLED #ReOrder AND ANOTHER CALLED
#tblBatchDataHolder
--TO HOLD EACH BATCH OF DATA FOR EDITING BEFORE BEING MOVED TO THE REORDER
TABLE
--Create the two temp tables so that it may be Altered
CREATE TABLE #ReOrder(ScenarioID int)
CREATE TABLE #tblBatchDataHolder(InstructionID int, ScenarioID int)
--Get the first alter
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
WHILE @.lFirstComma > 0 --Loop round Adding fields to the temp table
BEGIN
SET @.AddField = LTRIM(RTRIM(substring(@.sCreateTableSQL,1
,
charindex(',',@.sCreateTableSQL) -1)))
--print 'ALTER TABLE #ReOrder ' + @.AddField
EXEC ('ALTER TABLE #ReOrder ' + @.AddField)
EXEC ('ALTER TABLE #tblBatchDataHolder ' + @.AddField)
--Truncate @.sCreateTableSQL and get the next add
SET @.sCreateTableSQL = substring(@.sCreateTableSQL,@.lFirstComma + 1,
Datalength(@.sCreateTableSQL))
SELECT @.lFirstComma = charindex(',', @.sCreateTableSQL)
END
--SELECT * FROM #ReOrder --This is for testing
--SELECT * FROM #tblBatchDataHolder --This is for testing
-- Now Create the Third temp table so that we can import data from Excel
into it
CREATE TABLE #tblNewData (
InstructionID int NULL,
PlainText varchar (2000) NULL,
PlainText2 varchar (2000) NULL)
--SELECT * FROM #tblNewData --This is for testing
-- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE
-- This is how many times that insert will occure
SET @.iLoopControl = 1
SET @.ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @.ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)
WHILE @.iLoopControl <= @.lNoOfTimes --Loop round inserting
BEGIN
-- Create the Data table name to use for the import
SET @.ImportDataName = 'ImportData' + CAST(@.iLoopControl as
varchar(10))
-- Import the new data from excel into it Table.
-- OPENROWSET does not alow variables so the entire string needs to be
built into a batch and run
-- This is because of optimising.
SET @.sSQL = 'INSERT INTO #tblNewData ' +
'SELECT InstructionID, PlainText, PlainText2' +
' FROM OPENROWSET(' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39) +
',' + @.ImportFileName + ',' + @.ImportDataName + ')'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reading the data from the Excel work
book.' + CHAR(13) + @.ImportFileName + CHAR(13) + 'In to #tblNewData'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SELECT @.Test = count(InstructionID) from #tblNewData
SET @.sSQL = ''
Select * From #tblNewData --This is for testing
-- Create the @.sSQL to get the data from the the table
SET @.sSQL = 'INSERT INTO #tblBatchDataHolder SELECT InstructionID,' +
@.sSelectSQL +
' FROM tbl_BTP_Instructions WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the records into
#tblBatchDataHolder for editing.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
SET @.sSQL = ''
Select * From #tblBatchDataHolder --This is for testing
--This now changes the data
UPDATE #tblBatchDataHolder
SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
FROM #tblNewData INNER JOIN #tblBatchDataHolder
ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem updating the data in
#tblBatchDataHolder with the Excel data in #tblNewData.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
Select * From #tblBatchDataHolder --This is for testing
-- And now we move the new data into the update table ready for reordering
-- Create the @.sSQL for inserting
SET @.sSQL = 'INSERT INTO #ReOrder SELECT ' + @.sSelectSQL +
' FROM #tblBatchDataHolder WHERE (ScenarioID = ' +
cast(@.lScenarioID_CopyFrom as varchar(500)) + ')'
+ ' ORDER BY InstructionOrder'
--PRINT @.sSQL
EXEC(@.sSQL) --This will run the insert
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the updated data into the
#ReOrder from #tblBatchDataHolder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * From #ReOrder
SET @.sSQL = ''
DELETE FROM #tblBatchDataHolder
DELETE FROM #tblNewData --This clears out the old data ready for the
new data
SELECT @.iLoopControl = @.iLoopControl + 1
END
Select * From #ReOrder --This is for testing only
-- Do The ReOrdering
UPDATE #ReOrder
SET @.fNewOrderIncrement = InstructionOrder = @.fNewOrderIncrement +
@.dOrderIncrement
, ScenarioID = @.lScenarioID_PastTo
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem reordering the data in the #ReOrder.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
-- END
SECTION ----
--
--Select * From #ReOrder --This is for testing only
-- Insert The new Copied updated Records
INSERT INTO tbl_BTP_Instructions SELECT * FROM #ReOrder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem inserting the reorderd data from
#ReOrder into tbl_BTP_Instructions.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
--Select * from #ReOrder
--Clear the temp table
DROP TABLE #ReOrder
DROP TABLE #tblNewData
DROP TABLE #tblBatchDataHolder
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.ErrMSG = 'There was a problem dropping the Temp tables.'
RAISERROR (@.ErrMSG,16,1)
RETURN (1)
END
ELSE
BEGIN
COMMIT TRANSACTION
--RETURN (0)
END
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
SET IMPLICIT_TRANSACTIONS OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--END--
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:cvc9i0ttjvimrfsgbil9ec5smev8t71mod@.
4ax.com...
> Is SET NOCOUNT ON the first line in the sproc?
> --Mary
> On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@.NoWhere.com> wrote:
>
#Temp2.[vbcol=seagreen]
inserted[vbcol=seagreen]
it[vbcol=seagreen]
is[vbcol=seagreen]
something[vbcol=seagreen]
data[vbcol=seagreen]
Query[vbcol=seagreen]
also[vbcol=seagreen]
error[vbcol=seagreen]
no[vbcol=seagreen]
>|||Hi,
I'm more PowerBuilder and/or ADO .Net than straight ADO, but I have 1
possible cause for you.
Is you ADO DB Connection / VB6 application starting a transaction ?
Because, if you have start 2 transaction, effectively nesting them, then you
will need to issue 2 Commits.
Now, if the SQL Server is configured for implicit transactions, it might be
that when you close the connection, ADO automatically rolls back the open
transaction. And the SET before the CREATE of the Stored Procedure will not
have any effect on the caller, because they would have started the
transaction before calling the SP.
To check out what's going on, use SQL Profiler. There is a SQLTransaction
event and DTCTransaction event. You will need the Event Sub Class column to
see what type of action is carried out (begin, rollback, commit). The lookup
is in BOL.
Al
"Ian" wrote:

> Hi
> I was hopping some one can give me an explanation for this.
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp
2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
> Scenario:
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserte
d
> back into tblTableOne.
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
> BUT.
> When I run the Stored procedure from my VB 6 application at first glance
it
> seems to work fine in the fact that there is no errors and when the data
is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
> So I then added an output parameter to the SP to check that there was dat
a
> being imported form Excel and there were the correct no of records. and
> there are.
> So that has now narrowed it down to Step 3. Since it works fine from Quer
y
> Analyser and no errors are being thorn by the stored procedure. I have al
so
> added Error handling just after the UPDATE statement and there is no erro
r
> in @.@.ERROR.
> I have been battling with this for about 12 hours now.
> Please any thing that I could try would be welcome.
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still n
o
> joy.
> I know it is a tough one.
> I am willing to email the SP and sample data it your up for a challenge.
> Thanks
> Ian
>
>