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;'

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.

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 

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...