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 insode triggers problems?

Dear all

I am trying to use linked server objects inside a trigger and have some major problems.

Just to explain what i am trying to achieve:

My server A is SQL 2000. When ever a row is added to a table on server A i would like to send some of the inserted values to server B which is a physically different computer and has SQL 2005.

To do that i created linked server object on the SQL 2000 side.

To test the linked server object i go:

Select * ServerB.Database.dbo.Table

This works perfectly and i get the results. I then test the same inside a stored procedure but i do some parameterised queries.

Select id from ServerB.Database.dbo.Table where id=@.myId

This also works perfectly inside the stored procedure.

BUT NOW IT COMES THE FUN PART.

As soon as i place this inside the trigger it just doesnt work. My trigger has nothing else pretty much except for that. Here is a sample:

BEGIN
SET NOCOUNT ON
SET xact_abort ON

DECLARE @.myValue nvarchar(50)

SET @.myValue = '6357'

SELECT * from ServerB.Database.dbo.Table
Where id = @.myValue

END

It just gives me a timeout error. But what is even worse is that after this the whole database is crashed and i have to restart the database service to make it work.

I checked both servers and they have the service DTS for the distributed transaction on. No proxies, no firewals. Also i checked the servers configuration and they have RPC,RPC OUT and Data Access enabled.

I have tried everything over the last week and nothing has worked for me.

Any advice would be much much apreciated.

Sincerely
Dan

When you use 4-part name, the entire table is pulled from the remote server to the local before any filtering is applied. It's never a good idea to fetch & return results from a trigger.

Because trigger, by default, participates in the current transaction, it's best to keep the transaction as short as possible. In the case, you implicitly promote the transaction to a distributed transaction. That means both parties must play nice and DTC (which has a timeout) definitely is needed.

We need a bit more info as to what the timeout value refers to. Do enable 7300 trace flag and post the error here.

e.g.

dbcc traceon (-1,7300,3604)

go

|||

Dan_Dan wrote:

My server A is SQL 2000. When ever a row is added to a table on server A i would like to send some of the inserted values to server B which is a physically different computer and has SQL 2005.

Trigger is bad idea to do this task, you can utilize the Replication feature here.

See more on, http://msdn2.microsoft.com/en-us/library/ms151198.aspx

|||

You have violated a couple of 'no-nos' with TRIGGERS, and as you have discovered, that violation can have severe consequences.

First, a TRIGGER should NOT return data to the client. Use a Stored Procedure or UDF to return data to the client. TRIGGERs were not designed to be part of the client data interface, but were designed for internal operations only.

Second, a TRIGGER should NOT 'reach' outside the current processing thread. When you access data on a remote server, your client, your TRIGGER, and your local server, are at the mercy of the remote server. Anything disruptive in the connection, and/or the remote server will negatively effect your local operation -as you noticed. (This second factor can be worked around by using some form of 'store and forward' service, such as Service Broker. But again, not to retrieve data from a remote server.)

While you can, and will see these 'no-nos' violated, it just isn't a good idea.|||

This is the line from the sql logs.

I got this from C:\Program Files\Microsoft SQL\MSQL\LOGS

2007-08-09 12:19:40.60 spid8 This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 1 queries and performance may be adversely affected.

Before i enabled the dbcc traceon option as suggested in the post. But it does not give me any usable information to figure out as to why this is happening.

What i cant really understand is as to why the query would run inside a stored procedure or if ran just independently in the query analyser but it not in the trigger.

|||

You're running on MSDE, a free version of sql2k which has workload governor set. This is by design so you must upgrade to a paid version if you want to rid of the error.

http://msdn2.microsoft.com/en-us/library/aa258279(sql.80).aspx

As to the problem with the trigger, it could be authentication or network problem. We would need more info to nail it down. (A full error log would be helpful here).

sql

Linked server error in SSMS

Hi all
I have installed Sql Server 2005 + Sp2 developer edition. Once trying to add
a linked server in SSMS Server Objects => Linked Servers => New Linked
Server I'm getting the error immediately saying " Cannot show requested
dialog. Cannot find table 0. (System.Data)". Additionally Providers tree
under Linked servers is also empty. However I could add a linked server
successfully using t-sql sp_addlinkedserver. I got the feeling that some
files are corrupt on my PC, but I don't know which. I tried to uninstall and
put the server back again and it did not help either. I installed Sql Server
from the same disk onto the other machine and it works fine. Has anybody got
an idea how to resolve my problem?
Many thanks
DariusDarius
Hmm, I tried succefully create a linked server throu SSMS to SQL Server
2000. Pehaps something got corrupted during the installation of SSMS...
"Darius" <nospam@.mail.com> wrote in message
news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
> Hi all
> I have installed Sql Server 2005 + Sp2 developer edition. Once trying to
> add a linked server in SSMS Server Objects => Linked Servers => New Linked
> Server I'm getting the error immediately saying " Cannot show requested
> dialog. Cannot find table 0. (System.Data)". Additionally Providers tree
> under Linked servers is also empty. However I could add a linked server
> successfully using t-sql sp_addlinkedserver. I got the feeling that some
> files are corrupt on my PC, but I don't know which. I tried to uninstall
> and put the server back again and it did not help either. I installed Sql
> Server from the same disk onto the other machine and it works fine. Has
> anybody got an idea how to resolve my problem?
> Many thanks
> Darius
>
>|||Hi Uri
I believe something is corrupted on my PC (drivers, etc.), I successfully
can create linked server on the other machines. The error appears straight
after I click on "New Linked Server", it does not even show any dialog box..
I reinstalled the Sql Server from the scratch but there were no difference.
Just don't know where to start looking at...
Darius
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
> Darius
> Hmm, I tried succefully create a linked server throu SSMS to SQL Server
> 2000. Pehaps something got corrupted during the installation of SSMS...
>
> "Darius" <nospam@.mail.com> wrote in message
> news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
>|||Hi
Have you tried to search on "Google" for this error message?
"Darius" <nospam@.mail.com> wrote in message
news:eK2yKolgHHA.3852@.TK2MSFTNGP04.phx.gbl...
> Hi Uri
> I believe something is corrupted on my PC (drivers, etc.), I successfully
> can create linked server on the other machines. The error appears straight
> after I click on "New Linked Server", it does not even show any dialog
> box.. I reinstalled the Sql Server from the scratch but there were no
> difference. Just don't know where to start looking at...
> Darius
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
>|||yep, this is the first thing I do before posting to newsgroups. Anyway, will
post the solution when/if I manage to solve that problem.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxr7xplgHHA.2640@.TK2MSFTNGP06.phx.gbl...
> Hi
> Have you tried to search on "Google" for this error message?
>
> "Darius" <nospam@.mail.com> wrote in message
> news:eK2yKolgHHA.3852@.TK2MSFTNGP04.phx.gbl...
>

Linked Server error

Hi, All
I have one default SQL Server 2005 "LIANG" and "LIANG\SQLEXPRESS".
After I set up a linked server "LING" in "LIANG\SQLEXPRESS"., I try to run a
query
select * from LIANG.Northwind.dbo.Customers
it gives me the following error:
OLE DB provider "SQLNCLI" for linked server "LIANG" returned message
"Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LIANG" reported an error.
Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
linked server "LIANG".
Please help
KaiHi
Have you added a linked server login? Can you connect to the remote server
directly with this login?
John
"kai" wrote:

> Hi, All
> I have one default SQL Server 2005 "LIANG" and "LIANG\SQLEXPRESS".
> After I set up a linked server "LING" in "LIANG\SQLEXPRESS"., I try to run
a
> query
> select * from LIANG.Northwind.dbo.Customers
> it gives me the following error:
> OLE DB provider "SQLNCLI" for linked server "LIANG" returned message
> "Invalid authorization specification".
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LIANG" reported an error.
> Authentication failed.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
> linked server "LIANG".
>
> Please help
>
> Kai
>
>

Linked Server error

Hi, All
I have one default SQL Server 2005 "LIANG" and "LIANG\SQLEXPRESS".
After I set up a linked server "LING" in "LIANG\SQLEXPRESS"., I try to run a
query
select * from LIANG.Northwind.dbo.Customers
it gives me the following error:
OLE DB provider "SQLNCLI" for linked server "LIANG" returned message
"Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LIANG" reported an error.
Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
linked server "LIANG".
Please help
Kai
Hi
Have you added a linked server login? Can you connect to the remote server
directly with this login?
John
"kai" wrote:

> Hi, All
> I have one default SQL Server 2005 "LIANG" and "LIANG\SQLEXPRESS".
> After I set up a linked server "LING" in "LIANG\SQLEXPRESS"., I try to run a
> query
> select * from LIANG.Northwind.dbo.Customers
> it gives me the following error:
> OLE DB provider "SQLNCLI" for linked server "LIANG" returned message
> "Invalid authorization specification".
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LIANG" reported an error.
> Authentication failed.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
> linked server "LIANG".
>
> Please help
>
> Kai
>
>

Linked Server (Secure Access Database) Problem

hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
Ansari
Could you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Linked Server (Secure Access Database) Problem

hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
Ansari"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:uOnO$LPWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I m facing problem in setting linked server to Secure Access Database.. I
> have created a system DSN name "Costing" and set the user name and
password
> also. Then I created linked server using enterprise manager with
following
> property values
> Linked Server = CostingServer
> Provide name = Micorsoft OLEDB Provider for ODB Drivers
> Data Source = Costing
> when I press ok and click on "tables" icon it shows all the tables of
secure
> database... it means that connection successfull..
> but when I try to access tables using following query
> SELECT * FROM COSTINGSERVER...MaterialGroup
> it gives the following error
> "Invalid schema or catalog specified for provider 'MSDASQL'."
> but if remove password from the database all work is going fine without
any
> error...
> Any suggestion, help would be higly appreiciated in this regard..
Yes, don't bother with an Access database password. They are not only
insecure, a quick internet search will turn up a few mdb password crackers.
If you need your data secure, migrate it to SQL Server and use appropriate
security.
Steve

Linked Server (Secure Access Database) Problem

hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
AnsariCould you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.
]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>