Solved

Linked Server in SQL 2008 on Windows Server 2008

Posted on 2012-03-26
6
1,067 Views
Last Modified: 2012-06-27
Hello

I have Local MS SQL Server which I need to link to a remote MySql Server.
I have a Linked server set up on my local PC linking the Local SQL to the remote MySql server.

We have installed a Windows Server running the SQL Server and now I can not get the Linked Server to connect.

The Error I get is "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TESTWEB".
OLE DB provider "MSDASQL" for linked server "TESTWEB" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)"

When I test the System DSN under Data Sources it tests fine.

I have set up these linked servers EXACTLY the same but the one on the local PC will connect and the one on the Windows Server refuses to.

Any Ideas??
0
Comment
Question by:p-plater
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 37771661
You need TO change the PROPERTY OF the provider i.e. MSDASQL
Expand Providers -> Right Click MSDASQL -> Select Properties
Enable Nested queries
Enable Level zero only
Enable Allow inprocess
Enable Supports 'Like' operator


In SQL Server Surface Area Configuration:
Enable Local and Remote connections via TCP/IP and named pipes.Enable OPENROWSET and OPENDATASOURCE support.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 37772632
Hi TempDBA,

How and where do you enable openrowset and opendatasource support?

Regards
  David
0
 
LVL 25

Accepted Solution

by:
TempDBA earned 250 total points
ID: 37775561
From Surface Area Configuration Manager:
-> Click Surface Area Configuration For Features
-> Check in the checkbox that asks for enabling openrowset and opendatasource support.


From T-sql:
sp_configure “show advanced options”,1
go

reconfigure with override
go

sp_configure “Ad Hoc Distributed Queries”,1
go

reconfigure with override
go
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 37775832
Hi TempDBA,

Thanks for that.

But this unless I'm mistaken is a SQL 2008 question, and SQL 2008 doesn't have a Surface Area Configuration Manager.

Instead in SSMS, in Object Explor, right click on server and select Facets.

In Facet drop-down box, select Surface Area Configuration.

In Facet properties set AdHocRemoveQueriesEnaboled to true.

Is this right?

Regards
  David
0
 

Author Comment

by:p-plater
ID: 37895039
Look at tonight
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37895407
@David,
        Thanks, I forgot about the removal of surface area configuration in 2008. Thanks again for correcting me.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question