Named Pipe Provider/Linked Server Connection Error

Hello, everyone!

I'm currently having an issue with a linked server. Here's the surrounding information:

A) I have a clustered SQL Server 2005 Instance (A) and a SQL Server 2000 instance (B).

B) There is a linked server on A to B. When I set it up, I did run the fix to ensure A could talk to B (There was an issue with communication between 2005 and 2000 servers). It has been there since I installed A, and has worked fine.. Until last week.

C) This linked server uses static credentials to connect to B.

D) Named Pipes are enabled on both servers to listen to both connections on both A and B in cliconfg. So A has a named pipe listening for B, and B has a named pipe listening for A.

E) A has a view that looks at a table on B - It's a table view, very simple, just pulls in all the data from the table on B.

So, I go to do a select statement from the view, connected as a user other then 'sa'. I then get the error:

Named Pipes Provider: Could not open a connection to SQL Server Linked Server

Error Source: ncli Client (Paraphrasing, didn't copy that down, but it was the ODBC connector)

Well, that's odd. It's been working fine for months now..

I then go and connect as sa on A to query B. It works! Mind you, absolutely NO QUERY from A to B will run again until I run SOME kind of query has been run as sa on A.

Mind you, this is not a credential issue. Every user who connects to the linked server to B uses a stored credential that is DBO to the database on B. The same User Name and Password exists on both A and B.

Also of note, I check the activity monitor. There's a process that is "dormant" every time I run a query against the view. The details of this connection are:

sp_reset_connection;1

My question is, why is it resetting the connection on B when being queried from A? Why is it "all of a sudden" a problem? Are there any changes that coudl ahve been made that would cause this?

Any help with this confusing issue would be appreciated.

Thanks!

No one has any idea?

|||we found this to help us out: data source = servername,1433 (the port used), we noticed that when using an alias as the Linked Servername (not the actual servername) it failed, yet if the servername was the LS name it worked, so we added ',1433' to the data source and it cleared up our problem.

Named Pipe Provider/Linked Server Connection Error

Hello, everyone!

I'm currently having an issue with a linked server. Here's the surrounding information:

A) I have a clustered SQL Server 2005 Instance (A) and a SQL Server 2000 instance (B).

B) There is a linked server on A to B. When I set it up, I did run the fix to ensure A could talk to B (There was an issue with communication between 2005 and 2000 servers). It has been there since I installed A, and has worked fine.. Until last week.

C) This linked server uses static credentials to connect to B.

D) Named Pipes are enabled on both servers to listen to both connections on both A and B in cliconfg. So A has a named pipe listening for B, and B has a named pipe listening for A.

E) A has a view that looks at a table on B - It's a table view, very simple, just pulls in all the data from the table on B.

So, I go to do a select statement from the view, connected as a user other then 'sa'. I then get the error:

Named Pipes Provider: Could not open a connection to SQL Server Linked Server

Error Source: ncli Client (Paraphrasing, didn't copy that down, but it was the ODBC connector)

Well, that's odd. It's been working fine for months now..

I then go and connect as sa on A to query B. It works! Mind you, absolutely NO QUERY from A to B will run again until I run SOME kind of query has been run as sa on A.

Mind you, this is not a credential issue. Every user who connects to the linked server to B uses a stored credential that is DBO to the database on B. The same User Name and Password exists on both A and B.

Also of note, I check the activity monitor. There's a process that is "dormant" every time I run a query against the view. The details of this connection are:

sp_reset_connection;1

My question is, why is it resetting the connection on B when being queried from A? Why is it "all of a sudden" a problem? Are there any changes that coudl ahve been made that would cause this?

Any help with this confusing issue would be appreciated.

Thanks!

No one has any idea?

Named Calculation, Referencing a field from a linked table

Is there a way to reference a field from another table in a named calculation.

Ex: Table movies has a link to table theatre, in the theatre there is a display code, the display-name of the movie should be comp.comp_displaycode + ' - ' + mov.mov_name.

I know i could do this in a view and than use the view instead of the movies table, but if it is possible in the Datasourceviews it would be easier.

Try replacing table in the DSV with named query joining 2 tables.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||This is very similar to defining views, i just hoped that there was a syntax available to do it.

MySQL Linked Server

I am trying to linka mysql database to SQL 2005, but I am not sure what I
need to do.
I have set up a DSN using the mysql driver. I get lost when I try and create
the linked server in SQL 2005. Can someone please outline the steps to creat
e
the linked server. Should the mysql driver show up as a provider in SQL 2005
?Select the Microsoft OLE DB Provider for ODBC Drivers. Form
there, enter the DSN name for the Data source.
-Sue
On Thu, 16 Mar 2006 17:07:27 -0800, Curtis
<Curtis@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am trying to linka mysql database to SQL 2005, but I am not sure what I
>need to do.
>I have set up a DSN using the mysql driver. I get lost when I try and creat
e
>the linked server in SQL 2005. Can someone please outline the steps to crea
te
>the linked server. Should the mysql driver show up as a provider in SQL 2005?[/vbco
l]

MySQL Linked Server

I am trying to set up mysql as linked server for SQL Server 2000.
These are the steps I already have taken.
1- Installation of MyODBC 3.51 driver
2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
'mysql' data source
Now what I need to have is a SQL commands to add it as a linked server?
OR
What do I specify for the following fields by using Enterprise Manager
a) Provider Name
b) Data source
c) Provider string
d) Location
e) Catalog
I am using default 'root' account without any password.Asaf,
Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
is what you have to use to create the link
Asaf wrote:
> I am trying to set up mysql as linked server for SQL Server 2000.
> These are the steps I already have taken.
> 1- Installation of MyODBC 3.51 driver
> 2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
> 'mysql' data source
> Now what I need to have is a SQL commands to add it as a linked server?
> OR
> What do I specify for the following fields by using Enterprise Manager
> a) Provider Name
> b) Data source
> c) Provider string
> d) Location
> e) Catalog
> I am using default 'root' account without any password.|||Thanks for your reply Dennis but my problem is still the same. Being novice
I
have no idea what I specify for all sp_addlinkedserver parameters such as
sp_addlinkedserver [ @.server = ] 'server'
[ , [ @.srvproduct = ] 'product_name' ]
[ , [ @.provider = ] 'provider_name' ]
[ , [ @.datasrc = ] 'data_source' ]
[ , [ @.location = ] 'location' ]
[ , [ @.provstr = ] 'provider_string' ]
[ , [ @.catalog = ] 'catalog' ]
I would appreciate if someone out there could provide me a complete
sp_addlinkedserver with all parameters for MySQL.
"Dennis Black" wrote:

> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>|||Dennis, this is what I have done
exec sp_addlinkedserver
@.server = 'DefaultMySQL'
, @.srvproduct = 'MySQLProv'
, @.provider = 'MSDASQL'
, @.datasrc = 'MySqlDSN'
, @.location = NULL
, @.provstr = NULL
, @.catalog = 'mysql'
This is the error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'MSDASQL' in sysservers.
Would you please help
"Dennis Black" wrote:

> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>|||Here is an example where I link a mysql database to SQL Server:
sp_addlinkedserver
@.server='YouServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
@.provstr='DATABASE=mydb;DSN=My
DSN;OPTION=0;PWD=password;SERVER=localho
st;UID=myuser'
I use a program called WinSQL (http://synametrics.com/winsql), which
creates the Connection string. I used this connection string for the
'provstr' parameter.
Asaf wrote:[vbcol=seagreen]
> Dennis, this is what I have done
> exec sp_addlinkedserver
> @.server = 'DefaultMySQL'
> , @.srvproduct = 'MySQLProv'
> , @.provider = 'MSDASQL'
> , @.datasrc = 'MySqlDSN'
> , @.location = NULL
> , @.provstr = NULL
> , @.catalog = 'mysql'
> This is the error message
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'MSDASQL' in sysservers.
> Would you please help
> "Dennis Black" wrote:
>|||Dear Dennis,
I must say you have been a great help. I have managed to successfully create
a linked server by using the following setting
exec sp_addlinkedserver
@.server='MySQLServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='MySqlODBC', -- This should be the ODBC System DSN.
@.provstr='DATABASE=mySql;DSN=MySqlODBC;O
PTION=0;PWD=;SERVER=localhost;UID=ro
ot'
There is only one more issue; I could query mysql by using e.g user Table
from mysql db.
Select * from OPENQUERY(MySQLServer, 'select * from user')
but usual sql server syntax
SELECT * FROM MySQLServer.mySQL.dbo.[user]
generates an error message
"Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema."
What is it that I am doing wrong ?
"Dennis Black" wrote:

> Here is an example where I link a mysql database to SQL Server:
> sp_addlinkedserver
> @.server='YouServer', -- This could be anything
> @.srvproduct='MySQL', -- This could be anything
> @.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
> @.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
> @.provstr='DATABASE=mydb;DSN=My
> DSN;OPTION=0;PWD=password;SERVER=localho
st;UID=myuser'
>
> I use a program called WinSQL (http://synametrics.com/winsql), which
> creates the Connection string. I used this connection string for the
> 'provstr' parameter.
>
>
>
> Asaf wrote:
>

MySQL Linked Server

I am trying to linka MySQL database to SQL 2005, but I am not sure what I
need to do.
I have set up a DSN using the MySQL driver. I get lost when I try and create
the linked server in SQL 2005. Can someone please outline the steps to create
the linked server. Should the MySQL driver show up as a provider in SQL 2005?
Select the Microsoft OLE DB Provider for ODBC Drivers. Form
there, enter the DSN name for the Data source.
-Sue
On Thu, 16 Mar 2006 17:07:27 -0800, Curtis
<Curtis@.discussions.microsoft.com> wrote:

>I am trying to linka MySQL database to SQL 2005, but I am not sure what I
>need to do.
>I have set up a DSN using the MySQL driver. I get lost when I try and create
>the linked server in SQL 2005. Can someone please outline the steps to create
>the linked server. Should the MySQL driver show up as a provider in SQL 2005?

MySQL Linked Server

I am trying to set up MySQL as linked server for SQL Server 2000.
These are the steps I already have taken.
1- Installation of MyODBC 3.51 driver
2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
'mysql' data source
Now what I need to have is a SQL commands to add it as a linked server?
OR
What do I specify for the following fields by using Enterprise Manager
a) Provider Name
b) Data source
c) Provider string
d) Location
e) Catalog
I am using default 'root' account without any password.
Asaf,
Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
is what you have to use to create the link
Asaf wrote:
> I am trying to set up MySQL as linked server for SQL Server 2000.
> These are the steps I already have taken.
> 1- Installation of MyODBC 3.51 driver
> 2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
> 'mysql' data source
> Now what I need to have is a SQL commands to add it as a linked server?
> OR
> What do I specify for the following fields by using Enterprise Manager
> a) Provider Name
> b) Data source
> c) Provider string
> d) Location
> e) Catalog
> I am using default 'root' account without any password.
|||Thanks for your reply Dennis but my problem is still the same. Being novice I
have no idea what I specify for all sp_addlinkedserver parameters such as
sp_addlinkedserver [ @.server = ] 'server'
[ , [ @.srvproduct = ] 'product_name' ]
[ , [ @.provider = ] 'provider_name' ]
[ , [ @.datasrc = ] 'data_source' ]
[ , [ @.location = ] 'location' ]
[ , [ @.provstr = ] 'provider_string' ]
[ , [ @.catalog = ] 'catalog' ]
I would appreciate if someone out there could provide me a complete
sp_addlinkedserver with all parameters for MySQL.
"Dennis Black" wrote:

> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>
|||Dennis, this is what I have done
exec sp_addlinkedserver
@.server = 'DefaultMySQL'
, @.srvproduct = 'MySQLProv'
, @.provider = 'MSDASQL'
, @.datasrc = 'MySqlDSN'
, @.location = NULL
, @.provstr = NULL
, @.catalog = 'mysql'
This is the error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'MSDASQL' in sysservers.
Would you please help
"Dennis Black" wrote:

> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>
|||Here is an example where I link a MySQL database to SQL Server:
sp_addlinkedserver
@.server='YouServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
@.provstr='DATABASE=mydb;DSN=My
DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myu ser'
I use a program called WinSQL (http://synametrics.com/winsql), which
creates the Connection string. I used this connection string for the
'provstr' parameter.
Asaf wrote:[vbcol=seagreen]
> Dennis, this is what I have done
> exec sp_addlinkedserver
> @.server = 'DefaultMySQL'
> , @.srvproduct = 'MySQLProv'
> , @.provider = 'MSDASQL'
> , @.datasrc = 'MySqlDSN'
> , @.location = NULL
> , @.provstr = NULL
> , @.catalog = 'mysql'
> This is the error message
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'MSDASQL' in sysservers.
> Would you please help
> "Dennis Black" wrote:
>
|||Dear Dennis,
I must say you have been a great help. I have managed to successfully create
a linked server by using the following setting
exec sp_addlinkedserver
@.server='MySQLServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='MySqlODBC', -- This should be the ODBC System DSN.
@.provstr='DATABASE=mySql;DSN=MySqlODBC;OPTION=0;PW D=;SERVER=localhost;UID=root'
There is only one more issue; I could query mySQL by using e.g user Table
from mySQL db.
Select * from OPENQUERY(MySQLServer, 'select * from user')
but usual sql server syntax
SELECT * FROM MySQLServer.mySQL.dbo.[user]
generates an error message
"Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema."
What is it that I am doing wrong ?
"Dennis Black" wrote:

> Here is an example where I link a MySQL database to SQL Server:
> sp_addlinkedserver
> @.server='YouServer', -- This could be anything
> @.srvproduct='MySQL', -- This could be anything
> @.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
> @.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
> @.provstr='DATABASE=mydb;DSN=My
> DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myu ser'
>
> I use a program called WinSQL (http://synametrics.com/winsql), which
> creates the Connection string. I used this connection string for the
> 'provstr' parameter.
>
>
>
> Asaf wrote:
>

MySQL linked in SQLServer

:confused: Hallo! I'm trying to link a MySql 's DB in SQLServer with ODBC: the operation was susseful but I'm not able to read and write/update the data.
HELP ME PLEASE!!!
silvia, ItalyI'm confused by "the operation was successful" followed by "I'm not able to read and write/update the data". I don't understand what you mean.

Check out sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp). If you tinker with the link a bit, you ought to be able to get it in Italian if that helps.

The quickest and easiest way is to use the ODBC link that uses a system DSN. That way if you change MySQL servers all you do is fix the DSN and your SQL Server link works.

A more complex but more powerful way is to use the ODBC with a connection string. It is kind of like a car with a manual transmission... You have more control, but you have to pay more attention to how you use it.

-PatP|||IF I RIGHT CLICK IN A TABLE IN THE REMOTE SERVER, I FIND ONLY "COPY" AND "?". IT'S CORRECT??

ciao e molte grazie!!!
silvia|||Without knowing a lot about your system(s), I can only guess. What you describe is one possible outcome of a linked server to MySQL that is actually fairly common.

There are two problems that commonly interfere with cross-server operations.

The most common problem is that the MySQL login used by the ODBC connection doesn't have sufficient permissions within MySQL. The only real solution for that problem is to get more permissions by either changing the MySQL login used by the ODBC connection to one that has more permissions, or by getting the MySQL administrator to grant the MySQL login used by the ODBC connection more permissions.

The next most common problem is that the MySQL ODBC driver on the SQL Server itself is old. The ODBC drivers have improved a lot over time. Older drivers (sometimes even a few months can make a real difference) are often a problem. Visit the MySQL site to see if you can get newer ODBC drivers.

-PatP|||I have set up a link server to MySQL 5

Sometimes mySQL do require a password. The following link server works for me.

EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;uid=userna me;pwd=password’

If MSDTC is running you can use the following to create a view without a link server.
SELECT * FROM OPENROWSET(’MSDASQL’,'Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;uid=userna me;pwd=password’, ‘SELECT * from table’)

If you get an error like
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].pensionNo'. The expected data length is 7, while the returned data length is 5.

You can resolve this using the option tag
'Driver={MySQL ODBC 3.51 Driver};DB=mysql_db;SERVER=mysql_server;option=512 ;uid=username;pwd=password'

Notice the option=512