Post reply

BULK INSERT from share - impasse?

  • September 24, 2008 at 9:28 am

    #193196

    I need to do a nightly import data of a 350MB transaction file. It is to replace an intermediate table. I'm trying to use BULK INSERT such that it reads the data from machine "Mach_A" - W2K Server, running Citrix-hosted applications) to a SQL Server 2005 database hosted by "Server_B". The client does not want to give Mach_A access to a share on Server_B to be used as a staging area for the data.

    My approach so far has been that Mach_A has a folder E:\TestData which has a share defined "TestData" and "Everyone" has full control permissions (for now). On Server_B I have set up a mapped drive - T: mapping to \\Mach_A\TestData

    To confirm access, from Server_B console I have used NotePad to edit "T:TestData.txt" and saved the little file - no problem.

    I've tried this stored proc ---

    ALTER PROCEDURE [dbo].[Test_Bulk_Import]

    @filepath nvarchar(250)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL nvarchar(1000)

    SET @SQL = 'TRUNCATE TABLE dbo.dummy_test_table'

    EXEC (@SQL)

    DECLARE @file nvarchar(250)

    SET @file = @filepath + 'TestData.txt'

    SET @SQL = 'BULK INSERT dbo.dummy_test_table '

    + 'FROM ''' + @file + ''' '

    + ' WITH ( FIELDTERMINATOR = '';'', ROWTERMINATOR = '''' ) '

    EXEC (@SQL)

    END

    but it fails saying the OS can't find the file. I have tried passing in

    a) T:

    b) \\Mach_A\TestData\

    Neither worked.

    However, passing in D:\TestData\ where D: is on Server_B and has data, it rolls right in.

    I note that the stored proc shows my domainname\logonname as the "User" for the stored proc. I've also tried it logged on as sa - still no go.

    Is BULK INSERT restricted to only reading data that is on the same machine as SQL Server? is there some mechanism I am missing to allow BULK INSERT to reach out of the machine to a share?

    Thanks.

  • September 25, 2008 at 7:33 am

    #876990

    Update for the benefit of those to follow: If the server is running under a system account, it is restricted to local resources. If it is running under a Windows account, then it can access non-local resources using the account's credentials. For my situation, when presented with that information found on a Microsoft site, the client decided to open up a well-controlled share on the server's drive space.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply