Linking from SQL server to MySQL

Hi all, hoping someone can help.
I have created an ODBC connection to a remote MySQL database.
I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data.
The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables.
I am so stuck. Any help would be very much appreciated.My first guess would be a permissions issue in the MySQL login used within the DSN, or an IP permission problem in the MySQL login. I don't have any easy way to set up a test case this week, but those would be where I'd start looking.

-PatP|||Really don't know a lot about MySQL permissions so any more help would be great, so I can go back to are website designers with suggestions of what the problem might be on there end.|||The security and permissions for MySQL work a lot different than they do for SQL Server.

The IP address figures prominently into MySQL permissions. A login might have one set of permissions at one ip address or range (say inside the firewall), and drastically different permissions at others. If you ran your DTS package from a machine with an IP address that was "blessed", you could have one set of permissions, but when making a linked server connection on the SQL Server (which would have another address), the same MySQL id might have much less permission.

Another possibility is that the ODBC driver on the SQL Server might not be current. It has been relatively recent that the MySQL team has put any effort into making the ODBC drivers compatible with Windows 2003 Server.

The biggest problem at this point is that there are so many ways for the connection to go bad, and the MySQL ODBC drivers still aren't very good at giving usable diagnostics (as you've noticed!). The problem is almost certainly solvable, but without help from someone pretty technically knowledgable that understands both your NT and your MySQL configuration, it may take many tries to work out the problem and its solution.

-PatP

servers without MSDTC?

Simply put is it possible?

Will all transactions that involve a linked server connection by promoted to a Distributed transaction or can you set something somewhere that will keep the transaction 'normal'

Thanks in advance.

DaveTo ensure consistency across the server MSDTC will be needed and automatically involved.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

servers to Lotus Notes

Hello,

I have successfully linked a Lotus Notes server to our SQL Server
database using an ODBC connection.
This works fine when wanting to select records
eg openquery(LOTUSNOTES2, 'select * from Person' )

The problem I have is when I try to update the record I get an error
eg
update openquery(LOTUSNOTES, 'select * from Person where
MailAddress=''nick.bloor@.sandh-ltd.com''' )
set JobTitle='Test'

Produces the following error
==========
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
===========

I have tested the ODBC connection using an Access database. The link
tables facilities enable me to select what I think should be the
primary key. If I select a primary key then I can use Access to update
the lotus notes database, however if I don'k select a primary key, the
table is not updateable.

I believe that if I can somehow create an index on the linked table
within SQL Server, I should be able to update the Lotus Notes database
from SQL Server, but I cannot find a way of doing so.

As the Lotus Notes software is third party, I cannot actually change
anything on that server.

Thanks for any help.

Regards

Nick BloorNickBlooruk (nickblooruk@.yahoo.com) writes:
> The problem I have is when I try to update the record I get an error
> eg
> update openquery(LOTUSNOTES, 'select * from Person where
> MailAddress=''nick.bloor@.sandh-ltd.com''' )
> set JobTitle='Test'

I don't know Lotus Notes, nor do I have much experience of updating
through OPENQUERY. But what happens if you move the WHERE to the
SQL Server side of things?

> I have tested the ODBC connection using an Access database. The link
> tables facilities enable me to select what I think should be the
> primary key. If I select a primary key then I can use Access to update
> the lotus notes database, however if I don'k select a primary key, the
> table is not updateable.

Is Person.MailAddress the primary key? If it's not it seems like a
difficult case.

> I believe that if I can somehow create an index on the linked table
> within SQL Server, I should be able to update the Lotus Notes database
> from SQL Server, but I cannot find a way of doing so.

You cannot create indexes on linked tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,
Thanks for your response.

Strictly speaking Lotus Notes is not a relational database. The Person
table therefore does not have a primary key.
There are a few records will null values in the MailAddress within
Lotus notes.
However the data I am interested in is unique within Lotus notes.

Access 2000 allows me to say that the MailAddress field is unique (even
though it isn't strictly). This allows me to update the Lotus Notes
table using Access' Query.

I cannot find a way of doing the same with MS SQL SERVER 2000, so the
table is currently not updateable.

I have tried using the WHERE on the SQL SERVER side
eg:
update openquery(LOTUSNOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM
openquery(LOTUSNOTES2, 'select * from Person ' ) a
where a.MailAddress='NickBlooruk@.yahoo.com'

but the error is still the same.

Thanks for your advise.

Nick

Erland Sommarskog wrote:
> NickBlooruk (nickblooruk@.yahoo.com) writes:
> > The problem I have is when I try to update the record I get an
error
> > eg
> > update openquery(LOTUSNOTES, 'select * from Person where
> > MailAddress=''nick.bloor@.sandh-ltd.com''' )
> > set JobTitle='Test'
> I don't know Lotus Notes, nor do I have much experience of updating
> through OPENQUERY. But what happens if you move the WHERE to the
> SQL Server side of things?
> > I have tested the ODBC connection using an Access database. The
link
> > tables facilities enable me to select what I think should be the
> > primary key. If I select a primary key then I can use Access to
update
> > the lotus notes database, however if I don'k select a primary key,
the
> > table is not updateable.
> Is Person.MailAddress the primary key? If it's not it seems like a
> difficult case.
> > I believe that if I can somehow create an index on the linked table
> > within SQL Server, I should be able to update the Lotus Notes
database
> > from SQL Server, but I cannot find a way of doing so.
> You cannot create indexes on linked tables.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(nickblooruk@.yahoo.com) writes:
> Strictly speaking Lotus Notes is not a relational database. The Person
> table therefore does not have a primary key.

I kind of suspected that.

> Access 2000 allows me to say that the MailAddress field is unique (even
> though it isn't strictly). This allows me to update the Lotus Notes
> table using Access' Query.
> I cannot find a way of doing the same with MS SQL SERVER 2000, so the
> table is currently not updateable.

Access and SQL Server are not really the same sort of animals. Access
is a GUI and DBMS in once. SQL Server is only a DBMS.

Yet one more thing to try is something like:

UPDATE LOTUSNOTES2...Person
SET JobTitle = 'Test'
WHERE MailAdress = '...'

You would need to find out what to place between the dots in the four-
part notation, which is not always that easy.

> I have tried using the WHERE on the SQL SERVER side
> eg:
> update openquery(LOTUSNOTES2, 'select * from Person ' )
> set [JobTitle]='Test'
> FROM
> openquery(LOTUSNOTES2, 'select * from Person ' ) a
> where a.MailAddress='NickBlooruk@.yahoo.com'

I was more thinking of

update openquery(LOTUSNOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM where MailAddress='NickBlooruk@.yahoo.com'

But I would guess you've already tried that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland for your help.

I have discovered that there is a unique reference number in Lotus
Notes called NoteID. This has all the characteristics of a primary
key, however as it is not a primary key in the strict sense of the
work as there is no built in integrity checks, I cannot update using
the openquery statement.

I think I will post a note on the lotus notes forum and also try to
find a different solution.

Thank you very much though

Regards

Nick Bloor.|||NickBlooruk (nickblooruk@.yahoo.com) writes:
> I have discovered that there is a unique reference number in Lotus
> Notes called NoteID. This has all the characteristics of a primary
> key, however as it is not a primary key in the strict sense of the
> work as there is no built in integrity checks, I cannot update using
> the openquery statement.
> I think I will post a note on the lotus notes forum and also try to
> find a different solution.

Good luck! :-)

I can only say that I'm glad that I don't have fight that battle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

servers

I have a linked server connection on a SQL 2k server connected to a Sybase ASE 12.5 server via Sybase OLE DB connection. To this point everything has been working great for months. Now we're encountering a problem where new tables created on the Sybase db won't show up in the linked server connection. I've checked permissions and the object owners and everything is exactly the same as the pre-existing tables except the table's new.

Anyone have any ideas wht's up?Describe "won't show up" in a bit more detail. What exactly happens when you try to issue a SELECT statement against a new table?

-PatP|||When I say "won't show up" I mean you can't see the table in the table list in enterprise manager when I open the linked server. Here's the SQL and error message:

select i_con_contract from openquery([32tlsql2-dreamdb],
'Select i_con_contract From dbo.temp_client_3' )

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [Native Error code: 208]
[DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].|||What happens in Query Analyzer?

I suspect that this is another example of EM (SQL Enterprise Mangler) caching information, but never refreshing the cache.

-PatP|||The SQL and error message posted are from Query Analyzer.

I've even created a new linked server connection and the tables still won't show up. So I don't think it's a caching issue in EM.

Any other ideas? I appreciate the help.|||I was a bit corn-fused when you were talking about EM and posting SQL in the same message. I figured that somehow I must have "missed a meeting" in there somewhere.

Is there any chance that the Sybase objects are owned by a non-dbo user? Does the user being used by OPENQUERY have access to those objects when you use Sybase tools (like ISQL) to try to access them?

-PatP|||Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.|||Well, at least for now I'm stumped. I'm sure that come 03:30 I'll have a bright idea, but right now I'm fresh out. Sorry.

-PatP|||Originally posted by peterlemonjello
Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.

What's the linked server login?

Did you grant right to it on the new tables?|||Yep, the login the linked server is using has explicit permissions set on every table in the db. I've even changed to login to sa and still can't see the tables.|||I'm stuck...did you stop and restart EM?

Can you query them in QA?

Hey Pat, it's past 3:30...EST

Is there a way to see the linked server catalogs?|||Yep, I've rebooted the SQL Server. I can see the catalog using the sp_tables_ex proc and by looking in the sysremote_tables table in the master db. They're missing the new tables too. I have no freakin idea what's going on. I've turned the trace on in the OLEDB properties to try and isolate how SQL Server gets the table list from Sybase. Unfortuantely, when I refresh the tables in EM nothing appears in the OLEDB trace output.|||OK, let's get stupid (since I'm already there)

Can you create a new linked server with the same code?

Did you do it with code or through EM?|||I did it through EM. I have two SQL Server instances on seperate servers that are having the same problem. The only common denominator is the target Sybase server. I can't duplicate the problem against any other Sybase servers. I'm going to reboot the Sybase server tonight to see if that clears anything up. I'll let you know how it goes.|||Good Luck...

Time for a 'rita...

Later...|||Originally posted by Brett Kaiser
Hey Pat, it's past 3:30...EST

Is there a way to see the linked server catalogs? Not hardly, it was just past 15:30 EST when you posted! I really meant 03:30!

-PatP|||all righty then...

Hey Peter, did bouncing the box help?|||OK, I found the problem. There's a bug in Sybase's OLEDB provider. Luckily there's a patch out for the bug.

If you've setup more than one OLEDB profile in the Configuration Manager the first profile that's setup will apply to all additional profiles. No matter what properties are specified in the additional profiles settings.

For example create Profile1 that connects to 10.5.1.4 port 7682.
Cretae Profile2 and specify server 10.5.1.5 port 7680. Eventhough the properties are set correctly on Profile2 it will always connect to 10.5.1.4 port 7682. Also, any addiotional profiles will connect the profile that was created first in the Configuration Manager.

I couldn't see the new tables (in my test db) because my OLEDB connection was logging into a different server (production). Which happen to have the same schema except for the new tables.|||Wow! That's a pretty good one.

-PatP

server/VMWare Problem

I have two servers. Server #1 is a VMware virtual server that I
access using a Remote Desktop Connection (RDC). I acesssed Server #1
using RDC and brought in a linked server (Server #2). When I access
Server #2 as a linked server through RDC there's no problem. However,
if I access Server #1 using my local instance of SQL Server Management
Studio, I can't access the linked server. To make matters worse, if I
try to run views that select from one of the linked server's
databases, I get the following error:
SQL Execution Error
Executed SQL statement: SELECT AddressNumber . . .
Error Source: .Net SqlClient Data Provider
Error Message: TCP Provider. An existing connection was forcibly
closed by the remote host. Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
Has anyone come across this situation before? It would be nice to be
able to access the linked server without being on the virtual machine.
imani_technology_spam@.yahoo.com wrote:
> I have two servers. Server #1 is a VMware virtual server that I
> access using a Remote Desktop Connection (RDC). I acesssed Server #1
> using RDC and brought in a linked server (Server #2). When I access
> Server #2 as a linked server through RDC there's no problem. However,
> if I access Server #1 using my local instance of SQL Server Management
> Studio, I can't access the linked server. To make matters worse, if I
> try to run views that select from one of the linked server's
> databases, I get the following error:
> SQL Execution Error
> Executed SQL statement: SELECT AddressNumber . . .
> Error Source: .Net SqlClient Data Provider
> Error Message: TCP Provider. An existing connection was forcibly
> closed by the remote host. Login failed for user '(null)'.
> Reason: Not associated with a trusted SQL Server connection.
> Has anyone come across this situation before? It would be nice to be
> able to access the linked server without being on the virtual machine.
>
Hi,
How have you setup the Linked Servers? From the error message you get,
it sounds like it's a permission issue. Remember that if you want to use
your windows login as authenticated, you need to have the servers and
your account to be trusted for delegation in your AD. In most cases I
use a SQL server account for my linked servers. Then I always knows
which account I'm using for accessing the linked server and I don't need
to do any changes in the AD.
I don't think the problem has anything to do with the fact that the
servers are running on VMWare. I have a number of SQL servers running on
VMWare and they works just fine from a linked server point of view.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||How can I make this "trusted for delegation" happen?
On Jun 7, 1:12 am, "Steen Schlter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> imani_technology_s...@.yahoo.com wrote:
>
> Hi,
> How have you setup the Linked Servers? From the error message you get,
> it sounds like it's a permission issue. Remember that if you want to use
> your windows login as authenticated, you need to have the servers and
> your account to be trusted for delegation in your AD. In most cases I
> use a SQL server account for my linked servers. Then I always knows
> which account I'm using for accessing the linked server and I don't need
> to do any changes in the AD.
> I don't think the problem has anything to do with the fact that the
> servers are running on VMWare. I have a number of SQL servers running on
> VMWare and they works just fine from a linked server point of view.
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -
|||Hi,
This is done on the Server and/or User object in your AD. You can read
more about it in Books On Line - look for "Configuring Linked Servers
for Delegation".
Regards
Steen Schlter Persson
Database Administrator / System Administrator
imani_technology_spam@.yahoo.com wrote:
> How can I make this "trusted for delegation" happen?
>
> On Jun 7, 1:12 am, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>

server/VMWare Problem

I have two servers. Server #1 is a VMware virtual server that I
access using a Remote Desktop Connection (RDC). I acesssed Server #1
using RDC and brought in a linked server (Server #2). When I access
Server #2 as a linked server through RDC there's no problem. However,
if I access Server #1 using my local instance of SQL Server Management
Studio, I can't access the linked server. To make matters worse, if I
try to run views that select from one of the linked server's
databases, I get the following error:
SQL Execution Error
Executed SQL statement: SELECT AddressNumber . . .
Error Source: .Net SqlClient Data Provider
Error Message: TCP Provider. An existing connection was forcibly
closed by the remote host. Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
Has anyone come across this situation before? It would be nice to be
able to access the linked server without being on the virtual machine.imani_technology_spam@.yahoo.com wrote:
> I have two servers. Server #1 is a VMware virtual server that I
> access using a Remote Desktop Connection (RDC). I acesssed Server #1
> using RDC and brought in a linked server (Server #2). When I access
> Server #2 as a linked server through RDC there's no problem. However,
> if I access Server #1 using my local instance of SQL Server Management
> Studio, I can't access the linked server. To make matters worse, if I
> try to run views that select from one of the linked server's
> databases, I get the following error:
> SQL Execution Error
> Executed SQL statement: SELECT AddressNumber . . .
> Error Source: .Net SqlClient Data Provider
> Error Message: TCP Provider. An existing connection was forcibly
> closed by the remote host. Login failed for user '(null)'.
> Reason: Not associated with a trusted SQL Server connection.
> Has anyone come across this situation before? It would be nice to be
> able to access the linked server without being on the virtual machine.
>
Hi,
How have you setup the Linked Servers? From the error message you get,
it sounds like it's a permission issue. Remember that if you want to use
your windows login as authenticated, you need to have the servers and
your account to be trusted for delegation in your AD. In most cases I
use a SQL server account for my linked servers. Then I always knows
which account I'm using for accessing the linked server and I don't need
to do any changes in the AD.
I don't think the problem has anything to do with the fact that the
servers are running on VMWare. I have a number of SQL servers running on
VMWare and they works just fine from a linked server point of view.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||How can I make this "trusted for delegation" happen?
On Jun 7, 1:12 am, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> imani_technology_s...@.yahoo.com wrote:
> > I have two servers. Server #1 is a VMware virtual server that I
> > access using a Remote Desktop Connection (RDC). I acesssed Server #1
> > using RDC and brought in a linked server (Server #2). When I access
> > Server #2 as a linked server through RDC there's no problem. However,
> > if I access Server #1 using my local instance of SQL Server Management
> > Studio, I can't access the linked server. To make matters worse, if I
> > try to run views that select from one of the linked server's
> > databases, I get the following error:
> > SQL Execution Error
> > Executed SQL statement: SELECT AddressNumber . . .
> > Error Source: .Net SqlClient Data Provider
> > Error Message: TCP Provider. An existing connection was forcibly
> > closed by the remote host. Login failed for user '(null)'.
> > Reason: Not associated with a trusted SQL Server connection.
> > Has anyone come across this situation before? It would be nice to be
> > able to access the linked server without being on the virtual machine.
> Hi,
> How have you setup the Linked Servers? From the error message you get,
> it sounds like it's a permission issue. Remember that if you want to use
> your windows login as authenticated, you need to have the servers and
> your account to be trusted for delegation in your AD. In most cases I
> use a SQL server account for my linked servers. Then I always knows
> which account I'm using for accessing the linked server and I don't need
> to do any changes in the AD.
> I don't think the problem has anything to do with the fact that the
> servers are running on VMWare. I have a number of SQL servers running on
> VMWare and they works just fine from a linked server point of view.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -|||Hi,
This is done on the Server and/or User object in your AD. You can read
more about it in Books On Line - look for "Configuring Linked Servers
for Delegation".
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
imani_technology_spam@.yahoo.com wrote:
> How can I make this "trusted for delegation" happen?
>
> On Jun 7, 1:12 am, "Steen Schlüter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>> imani_technology_s...@.yahoo.com wrote:
>> I have two servers. Server #1 is a VMware virtual server that I
>> access using a Remote Desktop Connection (RDC). I acesssed Server #1
>> using RDC and brought in a linked server (Server #2). When I access
>> Server #2 as a linked server through RDC there's no problem. However,
>> if I access Server #1 using my local instance of SQL Server Management
>> Studio, I can't access the linked server. To make matters worse, if I
>> try to run views that select from one of the linked server's
>> databases, I get the following error:
>> SQL Execution Error
>> Executed SQL statement: SELECT AddressNumber . . .
>> Error Source: .Net SqlClient Data Provider
>> Error Message: TCP Provider. An existing connection was forcibly
>> closed by the remote host. Login failed for user '(null)'.
>> Reason: Not associated with a trusted SQL Server connection.
>> Has anyone come across this situation before? It would be nice to be
>> able to access the linked server without being on the virtual machine.
>> Hi,
>> How have you setup the Linked Servers? From the error message you get,
>> it sounds like it's a permission issue. Remember that if you want to use
>> your windows login as authenticated, you need to have the servers and
>> your account to be trusted for delegation in your AD. In most cases I
>> use a SQL server account for my linked servers. Then I always knows
>> which account I'm using for accessing the linked server and I don't need
>> to do any changes in the AD.
>> I don't think the problem has anything to do with the fact that the
>> servers are running on VMWare. I have a number of SQL servers running on
>> VMWare and they works just fine from a linked server point of view.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator- Hide quoted text -
>> - Show quoted text -
>

server/VMWare Problem

I have two servers. Server #1 is a VMware virtual server that I
access using a Remote Desktop Connection (RDC). I acesssed Server #1
using RDC and brought in a linked server (Server #2). When I access
Server #2 as a linked server through RDC there's no problem. However,
if I access Server #1 using my local instance of SQL Server Management
Studio, I can't access the linked server. To make matters worse, if I
try to run views that select from one of the linked server's
databases, I get the following error:
SQL Execution Error
Executed SQL statement: SELECT AddressNumber . . .
Error Source: .Net SqlClient Data Provider
Error Message: TCP Provider. An existing connection was forcibly
closed by the remote host. Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
Has anyone come across this situation before? It would be nice to be
able to access the linked server without being on the virtual machine.imani_technology_spam@.yahoo.com wrote:
> I have two servers. Server #1 is a VMware virtual server that I
> access using a Remote Desktop Connection (RDC). I acesssed Server #1
> using RDC and brought in a linked server (Server #2). When I access
> Server #2 as a linked server through RDC there's no problem. However,
> if I access Server #1 using my local instance of SQL Server Management
> Studio, I can't access the linked server. To make matters worse, if I
> try to run views that select from one of the linked server's
> databases, I get the following error:
> SQL Execution Error
> Executed SQL statement: SELECT AddressNumber . . .
> Error Source: .Net SqlClient Data Provider
> Error Message: TCP Provider. An existing connection was forcibly
> closed by the remote host. Login failed for user '(null)'.
> Reason: Not associated with a trusted SQL Server connection.
> Has anyone come across this situation before? It would be nice to be
> able to access the linked server without being on the virtual machine.
>
Hi,
How have you setup the Linked Servers? From the error message you get,
it sounds like it's a permission issue. Remember that if you want to use
your windows login as authenticated, you need to have the servers and
your account to be trusted for delegation in your AD. In most cases I
use a SQL server account for my linked servers. Then I always knows
which account I'm using for accessing the linked server and I don't need
to do any changes in the AD.
I don't think the problem has anything to do with the fact that the
servers are running on VMWare. I have a number of SQL servers running on
VMWare and they works just fine from a linked server point of view.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||How can I make this "trusted for delegation" happen?
On Jun 7, 1:12 am, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> imani_technology_s...@.yahoo.com wrote:
>
>
> Hi,
> How have you setup the Linked Servers? From the error message you get,
> it sounds like it's a permission issue. Remember that if you want to use
> your windows login as authenticated, you need to have the servers and
> your account to be trusted for delegation in your AD. In most cases I
> use a SQL server account for my linked servers. Then I always knows
> which account I'm using for accessing the linked server and I don't need
> to do any changes in the AD.
> I don't think the problem has anything to do with the fact that the
> servers are running on VMWare. I have a number of SQL servers running on
> VMWare and they works just fine from a linked server point of view.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -|||Hi,
This is done on the Server and/or User object in your AD. You can read
more about it in Books On Line - look for "Configuring Linked Servers
for Delegation".
Regards
Steen Schlter Persson
Database Administrator / System Administrator
imani_technology_spam@.yahoo.com wrote:
> How can I make this "trusted for delegation" happen?
>
> On Jun 7, 1:12 am, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>

server w/ text field gets Connection Broken error

We have a stored proc on Server B called:

my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL

this proc calls:

my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL

This second stored procedure just selects back an ID that is passed to it (to keep things simple).

If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.

It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.

Any ideas?If I change the linked server from SQL Server to ODBC I get this error message:

Server: Msg 0, Level 19, State 1, Line 15
SqlDumpExceptionHandler: Process 244 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.|||Here is the DML:

-- Run on DATABASE_1
CREATE PROCEDURE my_sp_server_a
@.the_id int, @.the_text TEXT = NULL
AS

SELECT @.the_id

GO

-- Run on DATABASE_2
CREATE PROC my_sp_server_b @.my_text TEXT = NULL
AS

EXEC MY_LINKED_SERVER.DATABASE_1.DBO.my_sp_server_a @.the_id = 1, @.the_text = @.my_text

GO

server via secure connection

I am trying to setup a linked server to a MySQL database over at my hosting company.
I would like the communication between the SQL server and the MySQL database to be via a secure connection. anyone know how to do this?
my DB is SQL 2000Do you just want to manage the remote MySQL from your local console, or you want your SQL Server to perform actions over the remote MySQL as part of its work?|||basically, i would like to do selects and updates to the MySQL server with data from the SQL server.|||First you need a crtificate server (could be internal) and have both machines obtain certificates from it. I know there is a Certificate Server included in the MSDN subscription but I don't know how exactly to set it up. Shouldn't be a big deal.

Then in the Client Network Utility on the SQL Server machine check the Force Protocol Encryption box.|||thanks, i will try that

server to Pervasive 8 - Connection Error

Hello,

I am currently migrating from SQL 7.0 to SQL2005. So far everything is going well except for one thing, my linked server to a Pervasive 8.6 server database. I've done the same configurations as with the SQL 7.0 server to create the linked server. I've created an Pervasive ODBC Client DSN system connection on the server called PervasiveDB pointing to the DataDB catalog, using TCP/IP as transport and the server name "PervasiveDB" as the server address. From this point, on the SQL2005 server, the DSN connection is successfull and I can see the different catalog names of the PervasiveDB server. When I create the Linked server on the SQL2005, I create exactly as done on the SQL7.0 which is :

SQL7.0 server : OS is Windows 2000 all service packs applied for both OS and SQL7.0

SQL2005 server : OS is Windows2003 standard edition all service packs applied for both OS and SQL2005.

Linked server name : TestPervasive

Provider : Microsoft OLE DB Provider for ODBC Drivers

Product name: Pervasive

Data source: PervasiveDB

Location: PervasiveDB

Catalog : DataDB

I get the following error when I try to query the data to test if the linked server I just created is successful:

query : SELECT * FROM testpervasive.DataDB..schedule

error message:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "TestPervasive" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TestPervasive".

Anybody can help me on this one? I would greatly appreciate it !!

Hello,

I am relatively new at posting at these forums, but I wanted to advise that the problem I experience above has been resolved. I re-installed the service pack for SQL2005 and re-booted and now works fine with the above configurations/settings.

server to Oracle via ODBC, return less records

I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.

I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.

The problem is: only 32 records returned.

May I ask if there is any problem to this linked server setup?

Thanks.

Does any one has any idea..please help.^^

|||

I forwarded this to a linked-server guru here at Microsoft and here's the reply he gave:

Usually provider bugs. Updating the client versions for Oracle's code often fixes this class of issue.

Note that Kagera isn't really fully supported anymore either.

Oracle's native OLE/DB provider is the usual choice instead of ODBC.

Hope this is helpful.

|||

Same problem here,

created a query within Oracle SQLplus that returns 344.743 records. When I use the same query within the SSIS package it only returns 60.000 records? I'm connected with OLE DB and installed the latest Oracle 9i client. When I add a count within the query in SSIS is also concludes that there are 344.743 records.

Urgent help needed because we are migrating from DTS to SSIS.

Any idea what the problem can be?

Thankz

Ron Killaars

IT Engineer Energieirect BV

server to Oracle via ODBC, return less records

I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.

I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.

The problem is: only 32 records returned.

May I ask if there is any problem to this linked server setup?

Thanks.

Does any one has any idea..please help.^^

|||

I forwarded this to a linked-server guru here at Microsoft and here's the reply he gave:

Usually provider bugs. Updating the client versions for Oracle's code often fixes this class of issue.

Note that Kagera isn't really fully supported anymore either.

Oracle's native OLE/DB provider is the usual choice instead of ODBC.

Hope this is helpful.

|||

Same problem here,

created a query within Oracle SQLplus that returns 344.743 records. When I use the same query within the SSIS package it only returns 60.000 records? I'm connected with OLE DB and installed the latest Oracle 9i client. When I add a count within the query in SSIS is also concludes that there are 344.743 records.

Urgent help needed because we are migrating from DTS to SSIS.

Any idea what the problem can be?

Thankz

Ron Killaars

IT Engineer Energieirect BV

server to Oracle on Unix

Hi all!
I have a SQL Server from which I need to query a view in a Oracle database.
I have created a linked server connection to the Oracle machine. Oracle,
version 9i, is running on Unix.
Now when I try to use the linked server connection I have some problems.
When I expand the linked server and try to see the tables and views I
sometimes get an error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Sometimes everything works just fine and sometimes I get the error message
above. When the developers are trying to query the Oracle view they
sometimes get the same error and sometimes the queries works just fine.
The thing is that I don't know anything about Unix so I don't know if there
is something I can do on that side.
Any help or suggestions would be greatly appreciated.A few things you can try:
When the developers are querying Oracle, they can turn on
trace flag 7300 to get a more detailed error message. In
Query Analyzer, execute the following:
Dbcc traceon (7300,3604)
and then try executing the queries against Oracle.
Also, pay attention to the time it takes to execute the
queries when they fail and when they are successful. You
could be experiencing remote query timeouts. You can try
increasing the setting using sp_configure. You can find more
information in books online on using sp_configure.
You will also want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 16 Nov 2004 12:18:31 +0100, "Jaana"
<jaana.lehtonen@.banverket.se> wrote:

>Hi all!
>I have a SQL Server from which I need to query a view in a Oracle database.
>I have created a linked server connection to the Oracle machine. Oracle,
>version 9i, is running on Unix.
>Now when I try to use the linked server connection I have some problems.
>When I expand the linked server and try to see the tables and views I
>sometimes get an error message.
>Error 7399: OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>Sometimes everything works just fine and sometimes I get the error message
>above. When the developers are trying to query the Oracle view they
>sometimes get the same error and sometimes the queries works just fine.
>The thing is that I don't know anything about Unix so I don't know if there
>is something I can do on that side.
>Any help or suggestions would be greatly appreciated.
>
>

server to Oracle on Unix

Hi all!
I have a SQL Server from which I need to query a view in a Oracle database.
I have created a linked server connection to the Oracle machine. Oracle,
version 9i, is running on Unix.
Now when I try to use the linked server connection I have some problems.
When I expand the linked server and try to see the tables and views I
sometimes get an error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Sometimes everything works just fine and sometimes I get the error message
above. When the developers are trying to query the Oracle view they
sometimes get the same error and sometimes the queries works just fine.
The thing is that I don't know anything about Unix so I don't know if there
is something I can do on that side.
Any help or suggestions would be greatly appreciated.
A few things you can try:
When the developers are querying Oracle, they can turn on
trace flag 7300 to get a more detailed error message. In
Query Analyzer, execute the following:
Dbcc traceon (7300,3604)
and then try executing the queries against Oracle.
Also, pay attention to the time it takes to execute the
queries when they fail and when they are successful. You
could be experiencing remote query timeouts. You can try
increasing the setting using sp_configure. You can find more
information in books online on using sp_configure.
You will also want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 16 Nov 2004 12:18:31 +0100, "Jaana"
<jaana.lehtonen@.banverket.se> wrote:

>Hi all!
>I have a SQL Server from which I need to query a view in a Oracle database.
>I have created a linked server connection to the Oracle machine. Oracle,
>version 9i, is running on Unix.
>Now when I try to use the linked server connection I have some problems.
>When I expand the linked server and try to see the tables and views I
>sometimes get an error message.
>Error 7399: OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>Sometimes everything works just fine and sometimes I get the error message
>above. When the developers are trying to query the Oracle view they
>sometimes get the same error and sometimes the queries works just fine.
>The thing is that I don't know anything about Unix so I don't know if there
>is something I can do on that side.
>Any help or suggestions would be greatly appreciated.
>
>