September 24, 2008 at 9:28 am
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
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