[R-sig-DB] [Rd] problems executing a bulk load with SQL server

[Sean O'Riordain]

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 using 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]]
>
> ______________________________________________
> R-devel using r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-devel
>