0

I am using R to run a stored SQL procedure:

    query.str = "EXEC [StoredProcedure].[Procedure1]"
    con <- odbcConnect("my_database")
    my_data  = sqlQuery(con, query.str)

This code works fine on my laptop. But when I try to run it on the server it gives an error:

42000 2812 [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure

I don't think this is a problem with the stored procedure itself, as I have encountered the same situation with multiple stored procedures (they work on my laptop but not the server).

Edit: I am sure the connection string works. When I use the same connection string for a non-stored-procedure, it works and data reads in just fine. The problem only occurs with stored procedures.

Thank you in advance!

HSpeckman
  • 51
  • 1
  • 7
  • Check the connection string to where your database is located. If it works locally, you may have a local copy of the DB but the SP's are not on the server? Or when you execute it other than laptop, the DB can not be seen (or does not have permissions) to execute the SP's from the application. – Brad Dec 31 '20 at 19:47
  • I am sure that the connection works and things run just fine for everything but stored procedures. – HSpeckman Dec 31 '20 at 19:52
  • What do you mean by *server*? Do you mean outside of R with an SQL Server interface like `sqlcmd` or `SSMS` IDE? Does stored procedure exist under `dbo` schema of specific database as your code assumes? Please run an [information schema query](https://stackoverflow.com/questions/219434/query-to-list-all-stored-procedures/219441) to locate stored procedure under db and schema names. – Parfait Dec 31 '20 at 21:35

2 Answers2

0

Solution found: going through the Window Odbc connector, changing the default database to be the desired database fixed the problem.

HSpeckman
  • 51
  • 1
  • 7
0

This error may raise due to three main issues:

  • Incorrect reference of object's encapsulation including schema or database.

    Every SQL Server object (table, stored procedure, function, etc.) resides in a schema and every schema resides in a database. Also, every object can be referenced by multi-part names. The default schema in SQL Server is dbo. Therefore by not specifying the database and schema in object reference, object is assumed to reside in connecting database and dbo schema. Consequently, below calls are equivalent:

    EXEC [myServer].[myConnectedDatabase].[dbo].[myStoredProcedure]
EXEC [myConnectedDatabase].[dbo].[myStoredProcedure]
EXEC [dbo].[myStoredProcedure]
EXEC [myStoredProcedure]

    If myStoredProcedure does not reside in either specified database or schema, this error would raise. If you do not know or remember where stored procedure resides, run queries on system sys views, INFORMATION_SCHEMA views, or system stored procedures, sp_*.

  • Incorrect spelling of stored procedure including not escaping special characters or reserved words.

    To escape spaces, special characters (non-alphanumeric and non-underscore), and reserved words, enclose object names in square brackets [...]. Even better avoid such names. Thankfully for you, by default SQL Server is not case sensitive regarding identifiers. In other RDBMS's, like Oracle and Postgres, case sensitivity is retained for mixed cases during CREATE TABLE stage and double quotes would be needed for mixed cases types (i.e., "myStoredProc" <> mystoredproc <> MYSTOREDPROC).

  • Non-existent object in database or schema either by deletion or transfer to a different database or schema.

Parfait
  • 104,375
  • 17
  • 94
  • 125