Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 3rd, 2006, 09:00 AM
Authorized User
 
Join Date: Nov 2006
Location: mysore, karnataka, India.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Fetching fields from Flat File

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
Reply With Quote
  #2 (permalink)  
Old November 3rd, 2006, 09:13 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old November 3rd, 2006, 09:26 AM
Authorized User
 
Join Date: Nov 2006
Location: mysore, karnataka, India.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old November 3rd, 2006, 08:12 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old November 4th, 2006, 12:52 AM
Authorized User
 
Join Date: Nov 2006
Location: mysore, karnataka, India.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old November 4th, 2006, 09:30 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old November 4th, 2006, 09:43 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old November 6th, 2006, 06:53 AM
Authorized User
 
Join Date: Nov 2006
Location: mysore, karnataka, India.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old November 6th, 2006, 09:13 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old November 20th, 2006, 01:52 AM
Authorized User
 
Join Date: Nov 2006
Location: mysore, karnataka, India.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Masking flat file longs Linux 0 August 2nd, 2007 04:50 PM
problem fetching remote xml file method RSS and Atom 0 February 16th, 2007 07:50 PM
Fetching fields from flat file Jeff Moden SQL Server 2000 2 November 22nd, 2006 08:30 AM
How to open up a flat file daniel_arney Beginning VB 6 1 September 27th, 2006 02:40 PM
Need help with my Flat File Schema cmiller66 Biztalk 0 August 21st, 2006 10:48 PM



All times are GMT -4. The time now is 01:54 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.