problems executing a bulk load with SQL server

Good morning Grey,

Can you execute that query from the isql command line?

Just a wild guess but ry mixing up the forward backward slashes...
\\\\rhea/users/Risk/Dump/
this used to be needed in older versions of R for network drives

Regards,
Sean

On Thu, Nov 6, 2008 at 11:53 PM, Grey Moran Tzamouranis
<grey.moran at gmail.com> wrote:

Hello,

New to this forum so I hope the content is appropriate...

I was building a some code to maniputlate some data.  Given the bulk of it
(the csv is about a GB), I opted to use a database.
The setup, which may be part of the issue is that the SQL is one remote
machine (call it 'A'), the disk that contains the data is on some other
remote drive - call it 'B' and the server on which I program is a third
machine with access to both A and B.  SQL Server is 2003, I believe, and the
environment is XP.

Commands such as "CREATE TABLE", "SELECT * FROM", "ALTER TABLE" and "INSERT"
data work well.
But, given the size of the data to be loaded, I had to opt for a bulk load
like this:
sqlQuery(channel, "BULK INSERT mytable FROM
\\\\rhea\\users\\Risk\\Dump\\myfile.csv WITH (FIRSTROW=2, LASTROW=10);")
The response is
[1] "[RODBC] ERROR: Could not
SQLExecDirect"
[2] "42000 170 [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near '\\'."
Similar errors are produced by using the "/" slashes instead.   Therefore,
the first question would be: "Where do I go wrong with my file definition?"

A stored procedure the dba created to get around this problem also produces
an error but without much content.

The stored procedure definition would be something like:

create procedure LoadFile ( @TableName varchar(256), @FileName varchar(256)
)

as

begin

declare @FilePath varchar(1024);

declare @Command varchar(1024);

set @FilePath = '*\\rhea\users\Risk\Dump\* <file://rhea/users/Risk/Dump/>' +
@FileName;

set @Command = 'BULK INSERT ' + @TableName + ' FROM ' + Char(39) + @FilePath
+ Char(39) + ' WITH (FIRSTROW=2, LASTROW=10)';

exec (@Command);

end;

When I issue the command:

sqlQuery(channel, "exec LoadFile 'US15Aug2008',
'US_15Aug2008_50paths.csv';")

 I get the cryptic message:

[1] "[RODBC] ERROR: Could not SQLExecDirect"
 Any ideas?  Anything would be highly appreciated!

       [[alternative HTML version deleted]]