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

[Prof Brian Ripley]

On Fri, 7 Nov 2008, Sean O'Riordain wrote:

> 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

No, that command is being read by SQL Server, not by R.

However, character strings such as file paths in SQL need to be quoted 
under quite a few circumstances, including here.  The lack of quoting is 
the syntax error.

This was quite inappropriate for R-devel and Sean has correctly moved it 
to R-sig-db.  But even here, the issue is about the SQL syntax needed for 
the DBMS and not about R (nor any package that may have be used without 
giving due credit).

>
> 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
>>
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595