Putting the pieces together - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Putting the pieces together

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-30 : 04:39:09

Hi there guys I wonder if someone could offer me some good advise here please ?
I have a cursor in a stored procedure that needs to run 43 times and then run the 2nd stored procedure, sound simple enough ?
Well everytime the first stored procedure runs it is not guarenteed to run 43 times thats why it needs to run about five times before giving up completely, only when it loops 43 times then the 2nd sp must run.

Would a counter variable be used here and what would the best way be to schedule this in sql 2000 please ?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 04:50:08

>> Would a counter variable be used here
Well sounds like you need to count up to 43 so a variable (or table value) would probably be needed (unless you want to address what you are doing).

>> what would the best way be to schedule this in sql 2000 please
Depends on the environment - probably the agent scheduling a stored procedure.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 05:06:08

Post the code and we'll have a look why it doesn't complete 43 times.

Peter Larsson
Helsingborg, Sweden

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-30 : 06:31:22

Cheers Peter
The code in red at the bottom is where I need to put in a command to run the whole procedure again and in green this is where I need to print a success string and execute another procedure
Here is what I am trying to achieve.. Excuse the cursors Nigel.

ALTER PROCEDURE InsertItemsales
as
SET NOCOUNT on
DECLARE @exists as varchar(300)
DECLARE @icount as int
DECLARE @result int
DECLARE @failedstores as int
DECLARE @date as varchar(100)
DECLARE @sql as varchar(200)
DECLARE @storeno as smallint
DECLARE storecursor CURSOR FOR
Select store_no From -- store number count is 43 required
retail2 Where store_no in (SELECT storenum FROM retailchoisestore WHERE storenum <> 300 )

DROP TABLE dbo.tmpisaleimport
CREATE TABLE dbo.tmpisaleimport ([salesdata] [char] (56))
DROP TABLE dbo.itemsales_stores_input_error
CREATE TABLE [itemsales_stores_input_error] (
[StoreNumber] [smallint] NULL ,
[Description] varchar(200),
[DateInserted] [datetime] NULL CONSTRAINT [DF__itemsales__DateI__43F17C88] DEFAULT (getdate())
) ON [PRIMARY]
OPEN storecursor
FETCH NEXT FROM storecursor INTO @storeno
WHILE @@FETCH_STATUS = 0

BEGIN
select
@date = cast(datediff(day,min(date),getdate()) as varchar(3))
from
dbo.DatesToPeriods_Virtual
where
period = 1
and week = 1
and finyear in
(select
finyear
from
dbo.DatesToPeriods_Virtual
where
date > DATEADD(day, -1, getdate()))

select @date = right('000' + convert(varchar(10), @date), 3)
--find if the current isale file exists in the itemsale folder
select @exists = '"' + 'DIR /B \\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@date+'"'
EXEC @result = master..xp_cmdshell @exists , no_output
-- if it does then bulk insert it into the tmpisaleimport table
IF (@result = 0)
begin
select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@date+''''
Set @sql='BULK INSERT [tmpisaleimport]FROM '+@date
Exec(@sql)
IF @@rowcount = 0
Begin
INSERT INTO itemsales_stores_input_error(StoreNumber, [Description])
VALUES (@storeno,'File does exist but has no data')
End
ELSE
Begin
--increment the counter variable
SELECT @icount = @icount + 1
End

end
ELSE
-- if it doesn't plug it into the the error table which shows all failed store imports
begin
INSERT INTO itemsales_stores_input_error(StoreNumber, [Description])
VALUES (@storeno,'File does not exist')
end

FETCH NEXT FROM storecursor INTO @storeno
END
CLOSE storecursor
DEALLOCATE storecursor

--check to see if there are 43 legitimate stores imported without blank files
IF @icount = 43
BEGIN

DECLARE @errordescrip as varchar(100)
DECLARE @rowc as int

DROP TABLE dbo.tmpoffersaleimport
CREATE TABLE dbo.tmpoffersaleimport ([salesdata] [varchar] (156))
DROP TABLE dbo.offersales_stores_input_error
CREATE TABLE dbo.[offersales_stores_input_error] (
[ErrorDescription] varchar(200) NULL ,
[DateInserted] [datetime] NULL CONSTRAINT [DF__offersales__DateI__43F17C88] DEFAULT (getdate())
) ON [PRIMARY]

SELECT @exists = '"' + 'DIR /B \\jy2003smem012\Data\Iris6\Data\HOSTOUT\OffrSale.'+@date+'"'
EXEC @result = master..xp_cmdshell @exists
-- if it does then bulk insert it into the tmpoffersaleimport table
IF (@result = 0)
begin
select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTOUT\OffrSale.'+@date+''''
Set @sql='BULK INSERT [tmpoffersaleimport]FROM '+@date
Exec(@sql) end
ELSE
-- if it doesn't plug it into the the error table which shows all failed store imports
begin
set @errordescrip = 'The offer sale file did not exist for the date specified'
INSERT INTO offersales_stores_input_error(ErrorDescription)
VALUES (@errordescrip)
set @rowc = @@rowcount
end
END

ELSE

begin
--repeat the codeagain from thetop
@icount value of 43
print @icount
end

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 06:44:21

This looks very familiar - haven't we been through this before?
You should imoprt the files - that's one process.
Then check to see how many files have been imported.
If they have then run the next process.

Importing files.
Can this be independent of everything else? Just import the file from the folder, move the file to an archive folder and log the result. It means making sure that only files that are to be imported get in the folder.

If not then get a directory listing of the folder, Get a list of the files to be imported and join the two to run the imports.

Check to see if all stores have been imported - You hard code 43 whereas I would have thought you should have a table of stores - join to that to check if all have been processed.
If you use the second option above then this will be when the files to be imported is empty.

As for the processing - You say run 5 times. I guess you are running this from the agent with a delay? Just run the schedule for a time period e.g. every 10 mins for half an hour.
First step - check to see if process complete - if not then import any files.
Second step - check to see if process complete - if not check if all files imported - if so then complete processing.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-30 : 07:11:50

We have Nigel good memory! this was initially supposed to be two seperate procedures but I need to automate this whole process everyday so if the first proc succeeds (count of 43 coming out of a predefined store table) then run the second procedure.

What do you think about the idea of creating this as a function and return either success of failure which then triggers the next job to run ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 07:59:05

Something like this? Much of the code is redundant if storeno is present in the SalesData row in every textfile.

CREATE PROCEDURE uspInsertItemsales
AS

SET NOCOUNT ON

DECLARE @StoreNo SMALLINT,
	@MaxStoreNo SMALLINT,
	@FileExt SMALLINT,
	@cmd VARCHAR(500),
	@File VARCHAR(500),
	@Result INT

DROP TABLE	dbo.tmpisaleimport 
CREATE TABLE	dbo.tmpisaleimport
		(
			SalesData char(56)
		)

DELETE
FROM	dbo.itemsales_stores_input_error

DECLARE	@Stores TABLE (StoreNo SMALLINT)

INSERT		@Stores
		(
			StoreNo
		)
SELECT		r2.store_no
FROM		retail2 r2
INNER JOIN	retailchoisestore rcs ON rcs.storenum = r2.store_no AND rcs.storenum <> 300

DECLARE	@Data TABLE (StoreNo SMALLINT, SalesData char(56))

SELECT	@StoreNo = MIN(StoreNo),
	@MaxStoreNo = MAX(StoreNo)
FROM	@Stores

WHILE @StoreNo <= @MaxStoreNo
	BEGIN
		DELETE
		FROM	dbo.tmpisaleimport

		SELECT	@FileExt = RIGHT('000' + CONVERT(VARCHAR, DATEDIFF(day, MIN(x.Date), GETDATE())), 3)
		FROM	dbo.DatesToPeriods_Virtual x
		WHERE	x.finyear = DATEPART(year, GETDATE())

		SELECT	@File = '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale' + CONVERT(CHAR(3), @StoreNo) + '.' + @FileExt

		SELECT	@cmd = '"' + 'DIR /B ' + @File + '"'
		EXEC	@Result = master..xp_cmdshell @cmd, no_output

		IF @Result = 0
			BEGIN
				SELECT	@cmd = 'BULK INSERT [tmpisaleimport] FROM ' + CHAR(39) + @File + CHAR(39)
				EXEC	(@cmd)

				IF EXISTS (SELECT * FROM tmpisaleimport)
					INSERT	@Data
						(
							StoreNo,
							SalesData
						)
					SELECT	@StoreNo,
						SalesData
					FROM	tmpisaleimport
				ELSE
					INSERT	itemsales_stores_input_error
						(
							StoreNumber,
							[Description]
						)
					VALUES	(
							@StoreNo,
							'File does exist but has no data'
						)
			END
		ELSE
			INSERT	itemsales_stores_input_error
				(
					StoreNumber,
					[Description]
				)
			VALUES	(
					@StoreNo,
					'File does not exist'
				)

		SELECT	@StoreNo = MIN(StoreNo)
		FROM	@Stores
		WHERE	StoreNo > @StoreNo
	END

DROP TABLE	dbo.tmpisaleimport 

CREATE TABLE	dbo.tmpisaleimport
		(
			StoreNo,
			SalesData char(56)
		)

INSERT	tmpisaleimport
	(
		StoreNo,
		SalesData
	)
SELECT	StoreNo,
	SalesData
FROM	@Data

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 08:08:18

Separate the import and process proceudures.
The architecture I gave does this - it means that the import processes run completely independantly.
If you only have the files that need to be imported in the import folder then the imports don't need to know anything about the processing. You can just have a process that imports these files and logs the file name imported and moves it to the archive folder (in fact on my web site there is an sp to do exactly that - you just need to write the sp with the bulk insert statement).
You write that and test it separately.

Then you can concentrate on the processing bit and how to control it can go ahead. This would be a sepatate sp and again separate the control from the processing.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-30 : 08:27:45

Thank you guys for your valuable advise going on what you said Nigel I have come up with something like this which I have thoroughly tested and works well

ALTER PROCEDURE InsertItemsales
@status varchar (10) output
as
SET NOCOUNT on
DECLARE @exists as varchar(300)
DECLARE @icount as int
DECLARE @result int
DECLARE @failedstores as int
DECLARE @date as varchar(100)
DECLARE @sql as varchar(200)
DECLARE @storeno as smallint
DECLARE storecursor CURSOR FOR
Select store_no From -- store number count is 43 required
retail2 Where store_no in (SELECT storenum FROM retailchoisestore WHERE storenum <> 300 )

DROP TABLE dbo.tmpisaleimport
CREATE TABLE dbo.tmpisaleimport ([salesdata] [char] (56))
DROP TABLE dbo.itemsales_stores_input_error
CREATE TABLE [itemsales_stores_input_error] (
[StoreNumber] [smallint] NULL ,
[Description] varchar(200),
[DateInserted] [datetime] NULL CONSTRAINT [DF__itemsales__DateI__43F17C88] DEFAULT (getdate())
) ON [PRIMARY]

SELECT @icount = 0
OPEN storecursor
FETCH NEXT FROM storecursor INTO @storeno
WHILE @@FETCH_STATUS = 0

BEGIN
select
@date = cast(datediff(day,min(date),getdate()) as varchar(3))
from
dbo.DatesToPeriods_Virtual
where
period = 1
and week = 1
and finyear in
(select
finyear
from
dbo.DatesToPeriods_Virtual
where
date > DATEADD(day, -1, getdate()))

select @date = right('000' + convert(varchar(10), @date), 3)
--find if the current isale file exists in the itemsale folder
select @exists = '"' + 'DIR /B \\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@date+'"'
EXEC @result = master..xp_cmdshell @exists , no_output
-- if it does then bulk insert it into the tmpisaleimport table
IF (@result = 0)
begin
select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@date+''''
Set @sql='BULK INSERT [tmpisaleimport]FROM '+@date
Exec(@sql)
IF @@rowcount = 0
Begin
INSERT INTO itemsales_stores_input_error(StoreNumber, [Description])
VALUES (@storeno,'File does exist but has no data')
End
ELSE
Begin
--increment the counter variable
SELECT @icount = @icount + 1

End

end
ELSE
-- if it doesn't plug it into the the error table which shows all failed store imports
begin
INSERT INTO itemsales_stores_input_error(StoreNumber, [Description])
VALUES (@storeno,'File does not exist')
end

FETCH NEXT FROM storecursor INTO @storeno
END
CLOSE storecursor
DEALLOCATE storecursor

--check to see if there are 43 legitimate stores imported without blank files
IF @icount = 43
BEGIN

DECLARE @errordescrip as varchar(100)
DECLARE @rowc as int

DROP TABLE dbo.tmpoffersaleimport
CREATE TABLE dbo.tmpoffersaleimport ([salesdata] [varchar] (156))
DROP TABLE dbo.offersales_stores_input_error
CREATE TABLE dbo.[offersales_stores_input_error] (
[ErrorDescription] varchar(200) NULL ,
[DateInserted] [datetime] NULL CONSTRAINT [DF__offersales__DateI__43F17C88] DEFAULT (getdate())
) ON [PRIMARY]
select
@date = cast(datediff(day,min(date),getdate()) as varchar(3))
from
dbo.DatesToPeriods_Virtual
where
period = 1
and week = 1
and finyear in
(select
finyear
from
dbo.DatesToPeriods_Virtual
where
date > DATEADD(day, -1, getdate()))

select @date = right('000' + convert(varchar(10), @date), 3)
--find if the current offersale file exists in the itemsale folder

SELECT @exists = '"' + 'DIR /B \\jy2003smem012\Data\Iris6\Data\HOSTOUT\OffrSale.'+@date+'"'
EXEC @result = master..xp_cmdshell @exists
-- if it does then bulk insert it into the tmpoffersaleimport table
IF (@result = 0)
begin
select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTOUT\OffrSale.'+@date+''''
Set @sql='BULK INSERT [tmpoffersaleimport]FROM '+@date
Exec(@sql)
Set @rowc = @@rowcount
If @rowc >= 1
begin
SELECT @status = 'Success'
end
Else
begin
Select @status = 'Failure'
end
end
ELSE
-- if it doesn't plug it into the the error table which shows all failed store imports
begin
set @errordescrip = 'The offer sale file did not exist for the date specified'
INSERT INTO offersales_stores_input_error(ErrorDescription)
VALUES (@errordescrip)
SELECT @status = 'Failure'
end
END

ELSE

begin
--repeat the code again from the top until we get a @icount value of 43
Select @status = 'Failure'
end

I then call the above with something like this ?

DECLARE @stat varchar(10)

EXEC insertitemsales
@status = @stat output

SELECT @stat as message

I feel this code is pretty fail safe as I have tested it with the file not being available it containing no data and all 43 store files not being available and it works well.

Thank you Nigel especially for getting me on the right track here, thank you sincerely.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources