Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: What are the alternatives to Bulk Insert?

    I am trying to do this in a stored procedure and I am lacking permissions. Not sure if they will allow me permission so is there an alternative? I want to do it in a procedure and prefer not to use DTS.

    BULK INSERT tablename
    FROM 'C:\filename.txt'
    WITH
    (
    ROWTERMINATOR = '\t'
    )

    ddave

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    osql from a windows batch script can also be used.

    I'm not sure if that gets you anywhere though, because you still are going to be doing essentially a bulk load/insert.

    perhaps more importantly, if you don't have permissions on the table for inserts, chances are that you don't have permissions on the table for inserts.

    One way or the other, you will need permissions that allow you to insert to the table.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by TallCowboy0614
    perhaps more importantly, if you don't have permissions on the table for inserts, chances are that you don't have permissions on the table for inserts.
    that's certainly a true statement. not sure how much meaning it conveys though.

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    Ok, they gave me permissions to do the BULK INSERT. Now I am not using the correct syntax for the file path unless there's something else I am missing.

    Looking at this site from MSDN, http://msdn2.microsoft.com/en-US/library/ms175915.aspx, I see that the file path format I need is: \\Servername\Sharename\Path\Filename. I got the servername fine but what is a "Sharename"?

    The error I am getting is below. I have replaced the actual server name w/ this file path for security reasons.

    Server: Msg 4861, Level 16, State 1, Line 1
    Could not bulk insert because file '\\Servername\Sharename\Path\Filename.txt' could not be opened. Operating system error code 53(The network path was not found.).

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that's just a UNC path. do you know what a UNC path is?

    also it will work with a local path of the c:\dir\file.txt variety provided the file is local to the SQL Server machine.

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    I only know of UNC what I have briefly read about it. This is the first time I have tried this. So what is the Sharename?

    I tried it locally but go this error now:

    Server: Msg 4861, Level 16, State 1, Line 1
    Could not bulk insert because file 'C:\_Dave_Uyemura\RosterEnrollmentProject\_Current \FTPFAME.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).

    Any ideas? Is it an issue w/ permissions?

    ddave

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by jezemine
    that's certainly a true statement. not sure how much meaning it conveys though.
    hence the additional note "One way or the other, you will need permissions that allow you to insert to the table."

    just adept at stating the obvious
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by dolfandave
    I only know of UNC what I have briefly read about it. This is the first time I have tried this. So what is the Sharename?

    I tried it locally but go this error now:

    Server: Msg 4861, Level 16, State 1, Line 1
    Could not bulk insert because file 'C:\_Dave_Uyemura\RosterEnrollmentProject\_Current \FTPFAME.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).

    Any ideas? Is it an issue w/ permissions?

    ddave
    seems a likely culprit to me...keep in mind that the bulk load is happening from the perspective of the server running SQL. If the SQL Server is a different machine than your desktop, and the file is on your desktop, then you have to make the location of the file visible to the SQL Server. That means setting up an UNC path to your local file from the server, or sharing your local directory/file so that the server can see it and access it.

    Start with getting on the server's console (as one option) and using the console's windows explorer to find the file you are looking for. At minimum, if you can't get at the target file from the console, chances are the bulk load won't find it either.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by TallCowboy0614
    hence the additional note "One way or the other, you will need permissions that allow you to insert to the table."

    just adept at stating the obvious


    Just giving you a hard time because that sentence I quoted from you maps to "if you don't have X, chances are you don't have X." which I thought was amusing.

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by jezemine


    Just giving you a hard time because that sentence I quoted from you maps to "if you don't have X, chances are you don't have X." which I thought was amusing.
    "hard time"???? That all you got??? *LOL* Just funnin' back.

    "if you don't have X, chances are you don't have X." is exactly what I meant to say. Unfortunately I forgot to add the facial expression and body language that would have been present in person. *shrug*
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •