Input in Stored Procedure from Text File

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

General SQL Server Forums

New to SQL Server Programming

Input in Stored Procedure from Text File

Author

Topic

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-31 : 13:01:33

I have a text file with 30 entries in it.
I also have another text file with a 2 entries(the start date in mm/dd/yyyy and end date in same format)

I have execute only access to a stored procedure on another database, which i execute and direct the output into a table i created in my local database.
This is the 2 line script i execute:

insert into Database.tablename
exec Servername.DatabaseName..storedprocedurename 'devicename', 'date'

I have 30 device names and 5 days to collect datas for.
So i keep changing the devices and dates to collect them all.

Now i want to automate the entire process.

So, i came up with the 2 text files i mentioned above.
What i want to do is, a stored procedure should read the input from the device text file one by one and execute the above 2 insert and exec command and insert into table and repeat the process for 5 days and for all 30 devices.

Hence I have a skeleton of a stored procedure which i am not sure is correct.

Here it is.

create procedure testAutomate
@devicename varchar(50),
@startdate datetime,
@enddate datetime

AS
CREATE TABLE [dbo].[TestAutoAug31](
[systName] [varchar](50) NULL,
[InterfaceName] [varchar](151) NULL,
[SlotDateTime] [datetime] NULL,
[InterfaceSpeed] [float] NULL,
[LoadPctIn] [float] NULL,
[LoadPctOut] [float] NULL,
[deltaTime] [int] NULL,
[InterfacesName] [varchar](151) NULL
)

begin
while @enddate <= '08/31/2009'
insert into Database.tablename
exec Servername.DatabaseName..storedprocedurename 'devicename', '@startdate'
end

Will this work?
Here @ device name is the name it should fetch from the text file and date is the date it should fetch from the date text file.

How will it fetch the data and input in this stored procedure.

I have been reading many articles in vain.

Please guide me.

Thank you,
Padhma

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-31 : 14:20:05

any specific reason you want to use a text file? otherwise have a table with devicename and another with date, create a cursor and loop it a number of times to call the stored procedure.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-31 : 14:29:19

Hi Rohit,

The reason is to edit the entries in the text file, without having to access the database often.
But, as you said, I am trying to do that as an alternative.
Tbl Test1 has one column with 30 entiries.
Tbl Test2 has 1 column, 2 rows(2 dates)
Tbl Test3 has 6 columns to insert the output of the stored procedure from
exec ServerName.DBName..spname '@devicename', '@startdate'

This is what i have...but it gives me rror saying the cursor is open or it does not exist, or does not exist.
Please advice.

declare @devicename varchar(50)
declare @daterange datetime

declare auto1 cursor for
select systName from Test1

declare auto2 cursor for
select Daterange from Test2

open auto1
fetch next from auto1 into @devicename
while @@fetch_status = 0
begin
open auto2
fetch next from auto2 into @daterange
while @@fetch_status = 0

begin
insert into PadhmaUtilizData_Interface_PVZ.dbo.Test3
exec FTGBOSDASH2WIN.Network..spGetInterfaceData '@devicename', '@daterange'

fetch next from auto2 into @daterange
end
close auto2
deallocate auto2

fetch next from auto1 into @devicename
end
close auto1
deallocate auto1

Thank you,
Padhma

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-31 : 16:05:30

Any assistance here would be helpful.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-31 : 16:32:01

see below for cursor example
http://www.mssqltips.com/tip.asp?tip=1599

and you need just 1 cursor, use this select query

SELECT systName, Daterange FROM Test1, Test2

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-31 : 16:37:20

Hi Rohit,

I followed that article and came up withthe cursor i wrote.
Below is the modification and i get an error.

error:
Msg 8114, Level 16, State 5, Procedure spGetInterfaceData, Line 0
Error converting data type varchar to smalldatetime.

Cursor:

declare @devicename varchar(50)
declare @daterange varchar(50)

declare auto1 cursor for select systName, Daterange from Test1, Test2

open auto1
fetch next from auto1 into @devicename, @daterange
while @@fetch_status = 0
begin
insert into UtilizationData_PVZ.dbo.Test3
exec FTGBOSDASH2WIN.Network..spGetInterfaceData '@devicename', '@daterange'
fetch next from auto1 into @devicename, @daterange
end
close auto1
deallocate auto1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-31 : 16:43:08

make this select
select systName, Daterange from Test1, Test2
to see where the bad data is.

No, you're never too old to Yak'n'Roll if you're too young to die.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-31 : 16:45:55

I do not see any bad data. I am seeing the exepcted 2 columns in the right format, DeviceName, 08/29/2009

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-31 : 16:47:09

if i execute the 2 sattement:

insert into UtilizationData_PVZ.dbo.Test3
exec FTGBOSDASH2WIN.Network..spGetInterfaceData 'DeviceName', '08/29/2009'

It works fine. But when in a cursor it gives me error.
error:
Msg 8114, Level 16, State 5, Procedure spGetInterfaceData, Line 0
Error converting data type varchar to smalldatetime.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-31 : 16:50:56

remove the quotes

exec FTGBOSDASH2WIN.Network..spGetInterfaceData @devicename, @daterange

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-31 : 16:53:05

hm...
please try
set language english
before your open cursor statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-31 : 16:54:17