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

asked 05 Jan '14, 20:47

Scott%20Baldwin's gravatar image

Scott Baldwin
1567914
accept rate: 0%

edited 11 Jan '14, 07:59

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109


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

answered 07 Jan '14, 16:09

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

1

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

(07 Jan '14, 16:58) Breck Carter
Replies hidden

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

(08 Jan '14, 15:01) Karim Khamis

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

(08 Jan '14, 16:46) Breck Carter

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.

(09 Jan '14, 08:46) Karim Khamis

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:)

(09 Jan '14, 09:01) Volker Barth

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.

permanent link

answered 05 Jan '14, 22:03

Mark%20Culp's gravatar image

Mark Culp
23.0k9129270
accept rate: 40%

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.

(05 Jan '14, 23:12) Scott Baldwin
Replies hidden

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

The {sql_driver} is in the wrong place.

Plus, see Volker's answer.

(06 Jan '14, 07:45) 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 
permanent link

answered 07 Jan '14, 09:45

Breck%20Carter's gravatar image

Breck Carter
25.7k427586846
accept rate: 20%

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

permanent link

answered 06 Jan '14, 03:50

Volker%20Barth's gravatar image

Volker Barth
30.3k301453660
accept rate: 32%

edited 06 Jan '14, 03:56

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.

(06 Jan '14, 17:46) Scott Baldwin
Replies hidden

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?

(07 Jan '14, 14:36) Volker Barth

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

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

(08 Jan '14, 03:37) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×124
×62
×28
×26

question asked: 05 Jan '14, 20:47

question was seen: 2,034 times

last updated: 11 Jan '14, 07:59