current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm using sqlQuery for submitting an SQL query to an ODBC database, and try to retrieve the results

The following R script works

x = "'2013-12-19'"

sqlQuery(channel,paste("USE [JWFX]

DELETE FROM Currency$
WHERE [Date] = ",x,";

DELETE FROM MktIndex$
WHERE [Date] = ",x))

While the following R scritp, whose query properly works on SQL Server does not work when called in R:

sqlQuery(channel,
"USE [JWFX] GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1 GO
INSERT INTO Currency$
SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database= C:/Users/admin/Dropbox/SQL Server/DatabaseInput/BBGdata.xlsx;','SELECT * FROM [Currency$] WHERE Date = 41627');")

and returns the following error:

[1] "42000 102 [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'."                                                                                                                                                                                                                                                                                                                                                                                
[2] "42000 102 [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'."                                                                                                                                                                                                                                                                                                                                                                                
[3] "[RODBC] ERROR: Could not SQLExecDirect 'USE [JWFX] GO\nEXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 GO\nEXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1 GO\nINSERT INTO Currency$\nSELECT * FROM \nOPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database= C:/Users/admin/Dropbox/SQL Server/DatabaseInput/BBGdata.xlsx;','SELECT * FROM [Currency$] WHERE Date = 41627');'"

Why this happen? Any hint?

share|improve this question
    
RODBC isn't likely to support complex stuff like that. I would only count on the basics. –  joran Dec 20 '13 at 15:13
    
@joran: I doubt this has anything to do with R. It's likely a limitation of the SQL Server and/or Excel ODBC drivers. –  Joshua Ulrich Dec 20 '13 at 15:20
    
That's sort of what I meant, but I wasn't very clear about the distinction between RODBC and the driver itself. –  joran Dec 20 '13 at 15:32
    
perhaps you just need to add \n. DELETE FROM Currency$ \n WHERE [Date] = ",x,"; Or just write the code in one line. –  Martín Bel Dec 20 '13 at 20:01

2 Answers 2

up vote 2 down vote accepted

You cannot include GO in an SQL string submitted via ODBC, it is only used within SQL Server management Studio to separate batches.

In this case the first line USE [JWFX] GO is not needed, provided that the connection string point to database JWFX.

Separate the remaining into three different batches and remove the GO statement between them.

share|improve this answer
up vote 0 down vote

By omitting the first part:

USE [JWFX] GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1 GO

the query properly works and correcly insert into the database the rows we have selected through SELECT ...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.