Re: SQL Native Client 10 ODBC + " delimiter + ;1 results in 'Could not find stored procedure'

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Try replacing the double quote with []: call [dbo].[stproc;1]

There is an option on SQL-Server to activate or deactive the use of " as the
object delimiter and it's called the QUOTED_IDENTIFIER setting. When
QUOTED_IDENTIFIER is ON (and it's better to have it always ON) then " will
be used as the object delimiter; when it's OFF then " will be seen as a
string delimiter instead.

Even it should be ON, by default, on many databases, it's OFF but many
providers will automatically set it ON when opening a connection. Honestly,
knowing and understanding it's actual configuration can be a real labyrinth.

Personally, I try to set it to ON wherever I can and I never use " as the
object delimiter or as a string delimiter, only the [ ] for objects and the
single quote ' for the strings.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"Michael Burstin" <mburstin@xxxxxxxxxxxxxxxxx> wrote in message
news:Of9tc16tJHA.4324@xxxxxxxxxxxxxxxxxxxxxxx
I think that this has to be a bug in the SQL Native Client 10 version
because this used to work against SQL Server 2005 using its SQL Native
Client as well as if I use v9 of the SQL Native client against SQL Server
2008.

I have a generic tool which uses SQLProcedures catalog calls to determine
the names of stored procedures to call. For example, lets say that the
procedure is named:

dbo.stproc;1

The tool then uses the " " delimiters and calls the stored procedure as:

{call "dbo"."stproc;1"}

The call to SQLPrepare is successful (looking at the Driver Manager Log)
and everything appears to be fine. Now, when I go to SQLExecute the
stored procedure, I get an error:

DIAG [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Could
not find stored procedure 'dbo.stproc;1'. (2812)

If I use a tool like ODBCTest from MDAC, I have determined that the
following syntax works:

{call "dbo"."stproc";1}
{call dbo.stproc;1}
{call "dbo".stproc;1}
{call "dbo"."stproc"}

The problem is, if I want to use what SQLProcedures returns ("stproc;1")
and use delimiters, I get an error. The above cases just show that some
flavors of syntax works, but the one provided by the catalog calls fail!

I believe this is a bug. Can someone verify and tell me what to do about
it?


.

Relevant Pages

  • Re: INSERT Query problem with Quotes & Apostrophes
    ... I've got front-ends that go against both Jet and SQL Server databases. ... InputText As String, _ ... >> Delimiter, Delimiter & Delimiter) ... dDateTime, ...
    (microsoft.public.access.modulesdaovba)
  • Re: INSERT Query problem with Quotes & Apostrophes
    ... SQL Server compatible so that, if we want to, we can move from an Access ... > InputText As String, _ ... > Dim strTemp As String ... > Delimiter, Delimiter & Delimiter) ...
    (microsoft.public.access.modulesdaovba)
  • Re: "DataSet" input parametes to Stored Procedures
    ... a @delimiter nvarcharparameter to denote the parameter to use. ... Create a stored procedure like the following to set product stock level: ... Create a SQL Command to update the record?? ... > database what to update (filter) and how to update. ...
    (microsoft.public.sqlserver.programming)
  • Re: format files for use with bulk insert
    ... And this fails is you have that delimiter in the text. ... point with the quotes is to cover the case that the delimiter appears ... It's possible that SQL Server Integration Services can handle this ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Incomplete File Format Info for Import
    ... is you use Cr+Lf or a normal printable character then you will ... If you use Cr or Lf or another non-printable character it looks like this- ... Note the missing Row Delimiter line, so just add it back, e.g. ... I have basically nothing in SQL Server: View, Tables, DTS-wizard-created packages, T-SQL, BCP. ...
    (microsoft.public.sqlserver.dts)