As I posted in the previous issue, you will not be able to proceed until you get some privs for bulk insert and maybe some DDL privs. Then, use Bulk Insert.

Your DBA may not give you the necessary privs on a production box and I don't blame him/her... production is production. So, ask the DBA if there's a test area (test DB, most people have a separate box where developers have "SA" or "dbo" privs) that you can write and unit test your code in. If there isn't one, ask if they would mind making one. They'll likely love you for those questions. When it's fully debugged, send it to the DBA for review and implementation.

And, remember, just because it works, doesn't mean the DBA will approve it. Nicely formatted code with good comments as to what the code does and a nice header will help A LOT. Even then, the DBA still doesn't have to approve the code if he/she thinks it will be dangerous to the DB or the data.

Instead of "reopening" issues on the forum (we've already explained most of the methods available), why don't you talk with the DBA in a kind and courteous manner about how he/she would prefer it to be done? IS it ok to use Bulk Insert? WOULD it be ok to make a BCP job? WOULD it be possible to use a perm linked server to a given file so you could parse it? WHERE is the test area so you can make sure it works (the DBA's LOVE that question)? ANd, make sure you understand the ramifications of each option before you do and be prepared to explain every line of code even if it IS well commented. Good DBA's are (rightfully so) very careful about granting privs in the production environment. "You must comply or your code won't fly" is the mantra of most good DBA's.

I can't stress this enough... if you don't spend any time making your code LOOK like quality code (header, comments, correctly indented, correct capitalization, etc, etc), the DBA will figure "I'm not letting this idiot anywhere near my DB... he won't even format his code."

If it just sounded like I was lecturing or even a bit Draconian... I was :D It's always worked for me to work very closely with the DBA's. It's their database and they are charged with protecting it... if you help, your code will fly the quickest.

--Jeff Moden

Hi,
Suppose if i get bulkadmin or sysadmin access, will that solve my problem?????
I am asking u this because,
I need to fetch the fields based on their column position, not by the delimiter. There is no Delimiter in the file.
If so, can u give the link for the syntax????

praveen KN

Yes... it will solve your problem. Bulk Insert will handle fixed field records quite nicely and very quickly using a BCP format file. And, if the DBA won't let you store the format file anywhere, you can use Bulk Insert the file into a 1 column staging table and parse it using SUBSTRING.

Send your file spec and the first 10 lines from the file and I'll show you how to do it both ways...

--Jeff Moden

Hi,
     I have only 3 line of data in the file right now. Here i am sending the file specification.
I want the following fields to be fetched,
Document id (column 17 to 28),
Received Date (column 35 to 42),
Verifier (column 51 to 55),
Memberid (column 899 to 908)
The contents of the file is as follows,


H00061003000030106276100011 COBOCR2006100320061004CSUCH390427198 00000000000000000000000000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000N 20060908000000000000000993113-07373944015 $$ T99961003000030106276100011 000000000 0000000000 000000000 000 $$
H00061003000030206276100012 COBOCR2006100320061004CSUCH000993113 00000000000000000000000000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000N 20060908000000000000000993113-07395468826 $$ T99961003000030206276100012 000000000 0000000000 000000000 000 $$
H00061003000030206276100013 COBOCR2006100320061004CSUCH000993113 00000000000000000000000000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000 000000000N 20060908000000000000000993113-07397600221 $$ T99961003000030206276100013 000000000 0000000000 000000000 000 $$


And I need one more clarification,
Can we use BULK INSERT option for "Table" Data type?


praveen KN

Couldn't do anything but Quick Reply... something wrong with thread... started new one with same title.

--Jeff Moden