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
Ian
Ian,
One thing you can try is session SETtings. Session settings overrides database options, and some API's
executes a number of SET commands when they initialize the connection. (Yes, this essentially makes the
corresponding database options useless.) I suggest you go through the SET settings and try adding the ones you
can imagine might affect this, in the beginning of your proc code. Note that one setting is handled
differently from the others: ANSI_NULLS. This has to be set at *creation time* of the procedure, from the
connection where you create the proc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian" <ian@.NoWhere.com> wrote in message news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...
> 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
> Ian
>
>
>
>
>
|||Hi Tibor
Ok i think that i have tried every combination of setting i can come up
with. Still not working
Here is the SP if you want to have a look.
--START--
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ----
-- Name:
sp_CopyPast_Instructions_Between_Scenario_With_Exc el_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_Scenario_With_Exc el_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenario_With_Exc el_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_Scenario_With_Exc el_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--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O9vnWWdhEHA.140@.TK2MSFTNGP12.phx.gbl...
> Ian,
> One thing you can try is session SETtings. Session settings overrides
database options, and some API's
> executes a number of SET commands when they initialize the connection.
(Yes, this essentially makes the
> corresponding database options useless.) I suggest you go through the SET
settings and try adding the ones you
> can imagine might affect this, in the beginning of your proc code. Note
that one setting is handled
> differently from the others: ANSI_NULLS. This has to be set at *creation
time* of the procedure, from the
> connection where you create the proc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ian" <ian@.NoWhere.com> wrote in message
news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
#Temp2.[vbcol=seagreen]
inserted[vbcol=seagreen]
glance it[vbcol=seagreen]
data is[vbcol=seagreen]
something[vbcol=seagreen]
data[vbcol=seagreen]
Query[vbcol=seagreen]
also[vbcol=seagreen]
error[vbcol=seagreen]
no[vbcol=seagreen]
challenge.
>
|||Check to be sure you are connecting to the database via VB using the SAME
LOGON id as what you connected via Sql Analyzer. If your logon/permissions
are different, you could be hitting an error related to permissions and not
know it. The best way to find out if that is happening is to use the
Connection Objectss "Errors CVollection" in your VB. I didnt see any
mention of that, so here's a snippet of code to help you with that if you
need...
function Get_Child_Recs_Tools (msg, key)
'
' This proc gets a row count from the
' T_EQP_Tools table where fab_id = the key
' of fab_id of the T_EQP_Tools row that is
' about to be deleted.
' Any row count greater than 0 results in failure.
on error resume next
Get_Child_Recs_Tools = "NotOk"
tbl_primkey = cint(key)
sql_get = " Select Count(*) counter from TBLFAB where fabname = "
sql_get = sql_get & tbl_primkey
set cn = Server.CreateObject("ADODB.Connection")
cn.open str_Connect
set rs = Server.CreateObject("ADODB.Recordset")
rs.open sql_get, cn, adOpenForwardOnly
rs.movefirst
txt_rowcount = rs("counter")
if cn.Errors.Count > 0 then
Rtnmsg = ""
For each Error in cn.errors
Rtnmsg = Rtnmsg & "Error # : " & Error.Number & ", "
Rtnmsg = Rtnmsg & "Error Description : " & Error.Description
Next
cn.Errors.Clear
else
if txt_rowcount = 0 then
Get_Child_Recs_Tools = "Ok"
end if
end if
end function
P.S. I snipped this from an ASP application, so you'll have to cut out the
"Server." keyword from the ADO Object references and maybe make a few other
touchups....
g'Luck
"Ian" <ian@.NoWhere.com> wrote in message
news:OVPudKdhEHA.3476@.tk2msftngp13.phx.gbl...
> 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
> Ian
>
>
>
>
>