I need to create a linked server in SQL Server 2012. What parameters would I use connecting to the demo database suing SAOLEDB.16?

asked 05 Jul '14, 22:43

mysorian's gravatar image

mysorian
1189913
accept rate: 0%

edited 07 Jul '14, 05:22

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819


Have you had a look at these pages in the v16 doc? They contain a sample setup for the SA 16 demo database:

They relate to MS SQL 2005/2008 but I guess the setup should work with MS SQL 2012 as well. Note: Though you may use the generic MSDASQL driver it's recommended to use the builtin SAOLEDB driver.

In case your setup does not work yet feel free to tell what connection strings and properties you have used so far...

permanent link

answered 07 Jul '14, 03:55

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

The link you provided is perfect. There is a slight error in the stored procedure adding a linked server that needs a fix. Using MSDASQL is not an option as there is no x64 bit version for Windows 7. There was another suggestion here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/46dc0747-4006-429a-85f0-fd976d75bae9/is-there-a-msdasql-64-provider-on-windows-7-ultimate-x64-bit-os?forum=sqldatabaseengine But it is not appropriate form my set up.

(07 Jul '14, 22:41) mysorian
Replies hidden
1

That seems a strange contrast to the quote from the TechNet article "Data Access Technologies Road Map" cited by Breck:

MSDASQL: The Microsoft OLE DB Provider for ODBC (MSDASQL) is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. MSDASQL is an OLEDB provider that connects to ODBC, instead of a database. MSDASQL ships with the Windows operating system, and Windows Server 2008 and Vista SP1 were the first Windows releases to include a 64-bit version of the technology.

On my Windows 7 SP1 Professional 64-bit version, there certainly IS a 64-bit MSDASQL driver.

(08 Jul '14, 03:53) Volker Barth
1

There is a slight error in the stored procedure adding a linked server that needs a fix.

So what is that error? - Note that you can comment on the documentation immediately in DCX so you may leave your suggestion there...

(08 Jul '14, 03:57) Volker Barth

I find only one odbc.dll in my Windows directory but that is odbc32.dll in C:\Windows\SysWOW64\odbc32.dll. I am working on a Windows 7(x64bit) Ultimate laptop. I have had a discussion going on in MSDN forum as well and at least one of them confirmed. I am writing a post to my blog http://hodentekMSSS.blogspot.com

(09 Jul '14, 00:14) mysorian

The code shown in Download Via Linked Server is for SQL Anywhere 11, but it may give you some ideas.

permanent link

answered 06 Jul '14, 09:37

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

Hi Breck,

Thanks. I did try this. It does not seem to work. On a Windows 7 (x64bit) machine there is no Microsoft OLEDB for ODBC (MSDASQL). There are samples from Sybase There are two providers on SQL Server 2012 that can be used for creating linked servers: MSDASQL SAOLEDB.16

The first choice will not work. There isn't enough documentation for using SAOLEDB.16. It is not super urgent for me, plain academic interest.

(06 Jul '14, 20:58) mysorian
Replies hidden

> The first choice will not work.

Be wary of bold statements :)

Microsoft lists MSDASQL as the "Microsoft OLE DB Provider for ODBC" in the Help topic for SQL Server 2014 sp_addlinkedserver.

According to Microsoft's Data Access Technologies Road Map "MSDASQL ships with the Windows operating system".

Check this location on Windows 7...

C:\Program Files\Common Files\System\Ole DB\msdasql.dll

(07 Jul '14, 08:13) Breck Carter

FWIW, on 64-bit systems, there should also be a msdasql.dll in the 32-bit program files directory, i.e.

C:\Program Files (x86)\Common Files\System\Ole DB\msdasql.dll

Nevertheless, since SQL Server 2012 only comes as 64-bit software AFAIK, you will have to use the 64-bit OLEDB drivers for linked servers, no matter whether you will use the MSDASQL or SAOLEDB drivers...

(07 Jul '14, 08:46) Volker Barth

SAOLEDB is perfect for this. There is no MSDASQL(x64) on Windows 7 OS.

(07 Jul '14, 22:43) mysorian
Replies hidden
1

> There is no MSDASQL(x64) on Windows 7 OS

That is not true... please stop saying it.

It is located here...

C:\Program Files\Common Files\System\Ole DB\msdasql.dll

alt text

(09 Jul '14, 14:36) Breck Carter
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:

×41
×10

question asked: 05 Jul '14, 22:43

question was seen: 8,889 times

last updated: 09 Jul '14, 14:37