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

[Grey Moran]

I tried Prof Ripley's advice (i.e. to add the extra quotes arourn the
path & filename) as follows:
sqlQuery(channel, createTable.q)  # successful
loadData.q <- paste("BULK INSERT ", newTableName, " FROM '", inputCSV,
"' WITH (FIRSTROW=2, LASTROW=10);", sep="")

> loadData.q
[1] "BULK INSERT US15Aug2008 FROM
'\\\\rhea\\users\\Risk\\Dump\\US_15Aug2008_50paths.csv' WITH
(FIRSTROW=2, LASTROW=10);"

sqlQuery(channel, loadData.q )
[1] "[RODBC] ERROR: Could not SQLExecDirect"
The error returned is very laconic - i.e. no clear error message is
given beyond 'could not execute'.

Sean, I do not have access to the iSQL command line.

Thank you both for the help offered. My questions are offered with
some trepidation for protocol and content naivete but so far the web
search and my dba have not managed to resolve this.

Grey

On Fri, Nov 7, 2008 at 3:12 AM, Prof Brian Ripley <ripley using stats.ox.ac.uk> wrote:
>
> 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