Re: Really tough ADO Stored Procedure Question. Please Help!!!
From: Ian (ian_at_NoWhere.com)
Date: 08/19/04
- Next message: David G.: "Re: Occasional delay in processing incoming packets"
- Previous message: David Browne: "Re: General network error - SqlInternalConnection.OpenAndLogin"
- In reply to: Mary Chipman: "Re: Really tough ADO Stored Procedure Question. Please Help!!!"
- Next in thread: Al: "RE: Really tough ADO Stored Procedure Question. Please Help!!!"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Aug 2004 15:40:32 +0100
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_Scenario_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_Scenario_With_Excel_Import ScenarioID_From,
ScenarioID_To ,InsertAfter, NoOfTimes, ExcelFilePath
-- EXEC sp_CopyPast_Instructions_Between_Scenario_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_Scenario_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:
>
> >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
> >
> >
>
- Next message: David G.: "Re: Occasional delay in processing incoming packets"
- Previous message: David Browne: "Re: General network error - SqlInternalConnection.OpenAndLogin"
- In reply to: Mary Chipman: "Re: Really tough ADO Stored Procedure Question. Please Help!!!"
- Next in thread: Al: "RE: Really tough ADO Stored Procedure Question. Please Help!!!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
