CREATE SERVER problem with braces

[Former Member]

I am trying to create a DSN-less connection to SQL Server from SQL Anywhere so I can create a proxy table. I want to use a specific SQL Server driver, but it seems to come unstuck.

The CREATE SERVER statement shown below executes without error:

CREATE SERVER mss
CLASS 'MSSODBC'
USING 'ODBC;Driver={SQL Server Native Client 10.0};Server=<server>;Database=<database>;Trusted_Connection=yes;'
READ ONLY

but when I attempt to create the proxy table like so

CREATE EXISTING TABLE proxy_items
AT 'mss.<database>.dbo.DecodedItem'

I get the error

Could not execute statement. Variable 'SQL Server Native Client 10.0' not found SQLCODE=-260, ODBC 3 State="42000" Line 1, column 1

CREATE EXISTING TABLE proxy_items AT 'mss.database.dbo.DecodedItem'

Am I correct in assuming that open brace '{' is a special character that specifies a variable? If so is there some way of escaping it?

Note: I have tried '{{' and '@{', neither of which worked.

Also note, I can happily connect to this server using a DSN connection, but ideally I would like to connect in a DSN-less fashion.

Thanks

[Former Member]

You should not be providing the curly braces in the USING clause nor should you provide the ODBC keyword. Please try:

'Driver=SQL Server Native Client 10.0;Server=<server>;Database=<database>;Trusted_Connection=yes;'

[Breck_Carter]

Welllll, the {braces} seem to work fine if you want to use variable substitution; see my reply.

[Former Member]

Agreed. But I do not believe the initial intent was to use variable substitution, but rather to ensure that spaces are handled correctly.

[Breck_Carter]

FWIW spaces are not mentioned anywhere else in this discussion, entitled "CREATE SERVER problem with braces".

[Former Member]

My apologies for not being clear. When I talk about the "initial intent" above, I am not referring to the initial intent of providing variable substitution support in USING and AT clauses. Instead, I was referring to the initial intent of this particular forum question. I believe Scott's initial reason for putting braces around "SQL Server Native Client 10.0" was because the driver name has spaces and he may have wanted to make sure the spaces were handled correctly.

[VolkerBarth]

In my understanding, Scott's (here inappropriate) usage of braces has been triggered by the sample page he has mentioned in one of his comments...

but many of them (like this one(http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer) do use braces in the actual driver string.

Nevertheless, we still don't know why his tests do fail, and whether he really wants to parametrize the driver name or simply wants to use a DNS-free entry - and now it would be fine if he could give these answers instead of our wild guesses:)

[Breck_Carter]

The following test worked OK using SQL Anywhere 16.0.0.1691 and SQL Server SQL Server 2008 (SP2...

---------------------------------------------------------- 
-- SQL Server

1> SELECT @@VERSION
2> GO
 Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
        Sep 16 2010 19:43:16
        Co
        pyright (c) 1988-2008 Microsoft Corporation
        Express Edition (64-bit) on
         Windows NT 6.1 <x64> (Build 7601: Service Pack 1)

CREATE TABLE t  
   ( pkey INTEGER NOT NULL PRIMARY KEY,
     data INTEGER NOT NULL )
GO
INSERT t VALUES ( 1, 1 )
GO
SELECT * FROM t
GO

pkey        data
 ----------- -----------
           1           1

---------------------------------------------------------- 
-- SQL Anywhere

SELECT @@VERSION;

@@VERSION                                                                                                                        
-----------
16.0.0.1691

CREATE OR REPLACE VARIABLE @driver VARCHAR ( 100 ) = 'SQL Server Native Client 10.0';

CREATE SERVER mss 
   CLASS 'MSSODBC' 
   USING 'Driver={@driver};Server=Envy;Database=test;Trusted_Connection=yes;';

CREATE EXISTING TABLE proxy_t AT 'mss.test.dbo.t';

SELECT * FROM proxy_t;

pkey        data 
----------- ----------- 
          1           1 

[MarkCulp]

Yes, braces are special in a OMNI/CIS/Proxy Server definition - see the Parameters section (and last example) on the CREATE SERVER documentation page.

Off the top of my head I cannot remember how you escape the braces - you could try using a backslash?

But failing that you can put the value in a variable and use the variable replacement within the CREATE SERVER statement. Example:

begin
  declare @driver long varchar;
  set @driver = '{SQL Server Native Client 10.0}';

  CREATE SERVER mss
   CLASS 'MSSODBC'
   USING 'ODBC;Driver={@driver};Server=<server>;Database=<database>;Trusted_Connection=yes;'
    READ ONLY;
end;

But having said all of the above... I'm not sure why you are wanting to put braces in your DSN name - does your DNS really start with '{' and end with '}'. I suspect you really should be using:

CREATE SERVER mss
CLASS 'MSSODBC'
USING 'Driver=SQL Server Native Client 10.0;Server=<server>;Database=<database>;Trusted_Connection=yes;'
READ ONLY;

(and fill in <server> and <database> with the real names of your server and database.) You likely also need to specify a UID and PWD in your connection? See the examples in the documentation.

HTH

ps. BTW: Please also specify the version and build number that you are using as this information is often required to properly find a solution.

[Former Member]

Mark, thanks for your suggestion, unfortunately, it still doesn't want to work. When I try the following

BEGIN

BEGIN

declare @driver long VARCHAR; 
set @driver = '{SQL Server Native Client 10.0}';

CREATE SERVER mss
CLASS 'MSSODBC'
USING 'ODBC;Driver=;Server={@driver};<server>;Database=<database>;Trusted_Connection=yes;'
READ ONLY;

END;

as suggested in your answer, the server is 'created', but I get the following error message when I attempt to create a proxy table

Could not execute statement. Variable '@driver' not found SQLCODE=-260, ODBC 3 State="42000" Line 1, column 1

If I use the CREATE VARIABLE syntax as the example in the documentation demonstrates

BEGIN

CREATE VARIABLE sql_driver VARCHAR(256); 
set sql_driver = '{SQL Server Native Client 10.0}';

CREATE SERVER mss
CLASS 'MSSODBC'
USING 'ODBC;Driver=;Server={sql_driver};<server>;Database=<database>;Trusted_Connection=yes;'
READ ONLY;

END;

again, the server is 'created', but when I attempt to create a proxy table I get

Could not execute statement. Unable to connect to server 'mss': [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified SQLCODE=-656, ODBC 3 State="HY000" Line 2, column 1

For your reference, I am using SQL Anywhere version 12.0.1.3967

UID and PWD are not necessary as this is a Trusted_Connection (i.e. uses integrated security to connect to SQL Server). As I said, this all works perfectly fine (including the Trusted_Connection bit) if I set up a DNS using the ODBC Administrator, I would really just prefer to connect using a DSN-less connection, as I wish to programatically automate everything.

PS, of course, I am replacing <server> and <database> with appropriate values, I just didn't want to muddy the water with these environment specific details.

[Breck_Carter]

USING 'ODBC;Driver=;Server={sql_driver};<server>;Database=...

The {sql_driver} is in the wrong place.

Plus, see Volker's answer.

[VolkerBarth]

Hm, in my understanding, the syntax is not correct:

• Within a DNS-less connection, the DRIVER name is used without curly braces, so the SQL variable should not contain braces, either. The curly braces are only needed around the variable's name in the connection info string.

So in Mark's sample, I would think the SET statement should be

set @driver = 'SQL Server Native Client 10.0';

Or, in case you do not want to specify the driver's name by a variable (i.e. in case you will always use a MS SQL Server database here), you can specify the driver's name in the USING clause directly - again without curly braces.

---

Note, AFAIK, the CREATE SERVER statement does not try to establish any connection to the remote server - it's a common experience that incorrect server specs will only be noticed when trying to create proxy tables (or when using the FORWARD TO statement). So not getting an error on the CREATE SERVER statement doesn't mean it is correct in itself...

[Former Member]

I have literally tried every possible combination of SQL Server, SQL Native Client, SQL Server Native Client 10.0,

with, and without braces, using the syntax Marc suggested, as well as the CREATE VARIABLE syntax that the CREATE SERVER documentation suggests. Nothing works.

I have looked at a heap of websites that talk about DSN-less connections, and every one of them has slightly different syntax, but many of them (like this one http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer) do use braces in the actual driver string. Not really sure how to solve this.

[VolkerBarth]

Have you made sure the driver name is identical to that one shown in the ODBC admin? And that the driver of the required bitness is installed on the SQL Anywhere box?

[VolkerBarth]

Just to add a link to a bitness-related proxy table question:

Are always 32bit ODBC drivers used for remote data access ?.