Hi,
I am new to SQL Server 2000.
I have to write a stored procedure that fetches some of the fields from two flat files.
Here is my requirement....
There is a flat file by name "QN1004pf.hck", Here i have to Fetch all the Document ids (first 20 characters) from this file.
Then join this file using trim(Document id) with the below file’s Document id (column 17 to 28) to get the following fields:
"YYJJJD0#.mis" - fetch the following fields (by position)
Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55)

Join the above result set with the following condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the following fields:
(where result set is the result obtained by joining the flat files)
Sent (loistatus.loisentdate)
Unit (eligibilityorg.univid)
where Enrollkeys, loistatus, eligibilityorg are the tables in the datase..

Please help me in solving this.......
Thanks in advance for ur solutions.....



praveen KN

__________________
praveen KN

Bulk Insert seems like it would be the perfect solution for this... See "Bulk Insert" and "BCP Format Files" in "Books Online" to get familiar with the commands and the concepts.

In your description of the problem, you have the following...

"YYJJJD0#.mis" - fetch the following fields (by position)
Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55)

...but you never use the received date or the verifier anywhere... why do you need to import them if you are not going to use them?

--Jeff Moden

But i need to fetch the fields(Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55) from "YYJJJD0#.mis" file) in to stored procedure, and i want to display these field on the report(Microsoft Report Service)


praveen KN

Ok... was just checking because you didn't say anything about that in the first post.

Have you looked up and studied [BULK INSERT] and [bcp utility, format files] in Books Online, yet?

--Jeff Moden

yeah i've gone thru the Bulk insert option, but it is not a feasible solution for me. Because Only members of the "sysadmin" and "bulkadmin" fixed server roles can execute BULK INSERT.
i don't have admin rights, i don't have rights to create table. and also i don't have access to insert records into any of the existing tables. i have rights only to execute.

praveen KN

I'm thinking that you're pretty well screwed then... you don't need SysAdmin privs for Bulk Insert but, as you state, you will need BulkAdmin privs whether you use DTS or BulkInsert. Can you ask your DBA if you can get BulkAdmin privs?

--Jeff Moden

Just thought of something else... if you don't have DDL privs, you won't even be able to truncate a staging table. If they want you to build an import routine, they're going to have to trust you a bit.

For a slower method, you might try OPENROWSET (not sure what privs they require, though)... the only example I have is from an unknown source and I've never tried it... here's the example...

select * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp;Extended properties=''ColNameHeader=True;Format=CSVDelimite d;''','select * from Test.csv')

If you want more formating you'll need to use a schema.ini file

http://msdn.microsoft.com/library/de...a_ini_file.asp

schema.ini (same folder as Test.csv file!)

[test.csv]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=ANSI
Col1=COL1 Char Width 255
Col2=COL2 Char Width 255

--Jeff Moden

Thanks a lot Jeff Moden for ur reply.

Now i am able to solve the problem.

Actually i used the "Import and Export Data" facility of the SQL Server.
I didn't use any DTS package.

Thanks to P2P site...

praveen KN

Thank you for the feedback but I thought you said you couldn't even write to a table. "Import and Export Data" facility does write to tables...

--Jeff Moden

Hi all,
I am reopening the issue.

Actually my DBA is not allowing me to use DTS package.
He is telling me to Try using Stored Procedure to access the flat files and getting the values.
So how can i proceed..
Please reply ASAP..


praveen KN