Linked Server Provider

Hi All
I am trying to create a linked server to mysql and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:

>Hi All
>I am trying to create a linked server to mysql and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- David

Linked Server and Verify Connection

I am adding a linked server to another SQL server within a stored procedure.
I need to verify the link was made before I start running any more code. How
can I verify that I have connected successfully to the linked server?
Matt,
When you add a linked server, you do not make a connection, but
instead just provide the details needed so a connection can be made to
execute a query that refers to the linked server. Basically there is no
"open a connection for the code that follows" statement in SQL. I'm not
quite sure why you would want to add a linked server within a stored
procedure - do you drop the linked server in that procedure as well, so
that repeated calls to the procedure won't try to create an existing
linked server?
It would help if you could give more information about what you are
trying to accomplish.
Steve Kass
Drew University
Matt Tapia wrote:

>I am adding a linked server to another SQL server within a stored procedure.
>I need to verify the link was made before I start running any more code. How
>can I verify that I have connected successfully to the linked server?
>
>

Linked server 2000 to 2005: Error 17 sql server does not exist or access denined.

I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.I am also receving this code error, from a .Net 2003/C# to Crystal Report XI.
I get: Failled to Connection: [Vendor Error 17].
Is there a security permission that has been overlooked?
Here is the code I use to access CR XI

ConnectionInfo connectionInfo = new ConnectionInfo();
TableLogOnInfo tableLogOnInfo;
Database DB;
'CrystalDecisions.CrystalReports.Engine.Table' table;
Tables tables;

//Log in
connectionInfo.ServerName = "ServerName";
connectionInfo.DatabaseName = "Database Namet";
connectionInfo.UserID = "userid";
connectionInfo.Password = "password";

//Get Table inf from report
DB = reportDocument..Database;
tables = DB.Tables;

//Looping through all the tables in CR and apply connection info
for(int i = 0; i < tables.Count; i++)
{
table = tables[ i ];
tableLogOnInfo = table.LogOnInfo;
tableLogOnInfo.ConnectionInfo = connectionInfo;
table.ApplyLogOnInfo(tableLogOnInfo);

}

CrystalReportViewer.ReportSource = ReportDocument.ReportSource;
CrystalReportViewer.visible = true;

Thank you|||

Cacu,

Looks like your problem is not specific to linked server. Can you use other tools such as osql to connect to your SS2005 directly?

Certain SKUs of SQL 2005 do not allow remote connection by default. You can use SS2005 surface configuration manager to enable remote connection. If the server is a named instance, you also need to add firewall exception for sqlbrowser udp port 1434.

|||

Hi, cacu

I assume your server 2000 and 2005 are in different Box, say server 2000 is your client, and 2005 is your target server. So, first, try to find osql.exe on your client box, "c:\program files\microsoft sql server\80\tools\binn", osql.exe /S<servermachine> /E, or "osql.exe /S<servermachine>\<Instancename> /E"( the latter is for named instance). would you also get the same error? For remote connection specific you need following checklist:

A. Enable "File and printer sharing" in Firewall if your server is behind that.

B. Add program "sqlbrowser.exe" or udp port "1434" in your firewall exception list.

C. Add tcp port "1433" of default instance or <port number> of named instance in firewall, check you errorlog to figure out which port server is listening on. If you want your connection over TCP and make sure your server has TCP enabled.

D. Try ping your remote server machine, name and ipaddress.

E. Try to telnet <remoterservername> <portnumber>

F. Try to access a share of your remote server.

If above list all passed, you should be able to connect to linked server unless other problem exists, normally, you could see event log or server log to get clue.

Good Luck!

Ming.

Linked server 2000 to 2005: Error 17 sql server does not exist or access denined.

I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.I am also receving this code error, from a .Net 2003/C# to Crystal Report XI.
I get: Failled to Connection: [Vendor Error 17].
Is there a security permission that has been overlooked?
Here is the code I use to access CR XI

ConnectionInfo connectionInfo = new ConnectionInfo();
TableLogOnInfo tableLogOnInfo;
Database DB;
'CrystalDecisions.CrystalReports.Engine.Table' table;
Tables tables;

//Log in
connectionInfo.ServerName = "ServerName";
connectionInfo.DatabaseName = "Database Namet";
connectionInfo.UserID = "userid";
connectionInfo.Password = "password";

//Get Table inf from report
DB = reportDocument..Database;
tables = DB.Tables;

//Looping through all the tables in CR and apply connection info
for(int i = 0; i < tables.Count; i++)
{
table = tables[ i ];
tableLogOnInfo = table.LogOnInfo;
tableLogOnInfo.ConnectionInfo = connectionInfo;
table.ApplyLogOnInfo(tableLogOnInfo);

}

CrystalReportViewer.ReportSource = ReportDocument.ReportSource;
CrystalReportViewer.visible = true;

Thank you|||

Cacu,

Looks like your problem is not specific to linked server. Can you use other tools such as osql to connect to your SS2005 directly?

Certain SKUs of SQL 2005 do not allow remote connection by default. You can use SS2005 surface configuration manager to enable remote connection. If the server is a named instance, you also need to add firewall exception for sqlbrowser udp port 1434.

|||

Hi, cacu

I assume your server 2000 and 2005 are in different Box, say server 2000 is your client, and 2005 is your target server. So, first, try to find osql.exe on your client box, "c:\program files\microsoft sql server\80\tools\binn", osql.exe /S<servermachine> /E, or "osql.exe /S<servermachine>\<Instancename> /E"( the latter is for named instance). would you also get the same error? For remote connection specific you need following checklist:

A. Enable "File and printer sharing" in Firewall if your server is behind that.

B. Add program "sqlbrowser.exe" or udp port "1434" in your firewall exception list.

C. Add tcp port "1433" of default instance or <port number> of named instance in firewall, check you errorlog to figure out which port server is listening on. If you want your connection over TCP and make sure your server has TCP enabled.

D. Try ping your remote server machine, name and ipaddress.

E. Try to telnet <remoterservername> <portnumber>

F. Try to access a share of your remote server.

If above list all passed, you should be able to connect to linked server unless other problem exists, normally, you could see event log or server log to get clue.

Good Luck!

Ming.