Linked Server in SQL 2008 on Windows Server 2008

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??
p-platerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TempDBACommented:
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
David ToddSenior Database AdministratorCommented:
Hi TempDBA,

How and where do you enable openrowset and opendatasource support?

Regards
  David
0
TempDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

David ToddSenior Database AdministratorCommented:
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
p-platerAuthor Commented:
Look at tonight
0
TempDBACommented:
@David,
        Thanks, I forgot about the removal of surface area configuration in 2008. Thanks again for correcting me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.