Linked Server problem ?

Hi All,
Can anyone help me with the folllowing problem that I'm having, when I'm
using my linked server connection from a PC. Here's the overview:
I have setup a linked server to another sql Server successful and created a
view to a database on that remote server. everything works fine from the
server locally. However when I access the view remotely on my PC i get the
error message: "Login failed 'test'. Reason not associated with a trusted
server connection". I have tried many security configuration, but still no
joy. The only strange thing when I login into my machine as the
Administrator account on the nettwork, it works fine, even my own account has
full administration privileges on the network.
Any help would be great!.
thanks,
Liam.
Do you have with your account access on the SQL Server and on the DB Link/
DB
what kind of security use the link
"Liam Mac" <LiamMac@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> Hi All,
> Can anyone help me with the folllowing problem that I'm having, when I'm
> using my linked server connection from a PC. Here's the overview:
> I have setup a linked server to another sql Server successful and created
> a
> view to a database on that remote server. everything works fine from the
> server locally. However when I access the view remotely on my PC i get the
> error message: "Login failed 'test'. Reason not associated with a trusted
> server connection". I have tried many security configuration, but still
> no
> joy. The only strange thing when I login into my machine as the
> Administrator account on the nettwork, it works fine, even my own account
> has
> full administration privileges on the network.
> Any help would be great!.
> thanks,
> Liam.
>
>
|||Lian
Probably your remote server is configured with Windows Authentication .
Change it to Windows/SQL Server Authentication.
"Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> Hi All,
> Can anyone help me with the folllowing problem that I'm having, when I'm
> using my linked server connection from a PC. Here's the overview:
> I have setup a linked server to another sql Server successful and created
a
> view to a database on that remote server. everything works fine from the
> server locally. However when I access the view remotely on my PC i get the
> error message: "Login failed 'test'. Reason not associated with a trusted
> server connection". I have tried many security configuration, but still
no
> joy. The only strange thing when I login into my machine as the
> Administrator account on the nettwork, it works fine, even my own account
has
> full administration privileges on the network.
> Any help would be great!.
> thanks,
> Liam.
>
>
|||thanks guys, i got to the bottom of my problem. one of my boxes did not have
sql service pack 3a installed. once installed all ok.
liam
"Uri Dimant" wrote:

> Lian
> Probably your remote server is configured with Windows Authentication .
> Change it to Windows/SQL Server Authentication.
>
>
> "Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
> news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> a
> no
> has
>
>

Linked Server problem ?

Hi All,
Can anyone help me with the folllowing problem that I'm having, when I'm
using my linked server connection from a PC. Here's the overview:
I have setup a linked server to another sql Server successful and created a
view to a database on that remote server. everything works fine from the
server locally. However when I access the view remotely on my PC i get the
error message: "Login failed 'test'. Reason not associated with a trusted
server connection". I have tried many security configuration, but still no
joy. The only strange thing when I login into my machine as the
Administrator account on the nettwork, it works fine, even my own account ha
s
full administration privileges on the network.
Any help would be great!.
thanks,
Liam.Do you have with your account access on the SQL Server and on the DB Link/
DB
what kind of security use the link
"Liam Mac" <LiamMac@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> Hi All,
> Can anyone help me with the folllowing problem that I'm having, when I'm
> using my linked server connection from a PC. Here's the overview:
> I have setup a linked server to another sql Server successful and created
> a
> view to a database on that remote server. everything works fine from the
> server locally. However when I access the view remotely on my PC i get the
> error message: "Login failed 'test'. Reason not associated with a trusted
> server connection". I have tried many security configuration, but still
> no
> joy. The only strange thing when I login into my machine as the
> Administrator account on the nettwork, it works fine, even my own account
> has
> full administration privileges on the network.
> Any help would be great!.
> thanks,
> Liam.
>
>|||Lian
Probably your remote server is configured with Windows Authentication .
Change it to Windows/SQL Server Authentication.
"Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> Hi All,
> Can anyone help me with the folllowing problem that I'm having, when I'm
> using my linked server connection from a PC. Here's the overview:
> I have setup a linked server to another sql Server successful and created
a
> view to a database on that remote server. everything works fine from the
> server locally. However when I access the view remotely on my PC i get the
> error message: "Login failed 'test'. Reason not associated with a trusted
> server connection". I have tried many security configuration, but still
no
> joy. The only strange thing when I login into my machine as the
> Administrator account on the nettwork, it works fine, even my own account
has
> full administration privileges on the network.
> Any help would be great!.
> thanks,
> Liam.
>
>|||thanks guys, i got to the bottom of my problem. one of my boxes did not have
sql service pack 3a installed. once installed all ok.
liam
"Uri Dimant" wrote:

> Lian
> Probably your remote server is configured with Windows Authentication .
> Change it to Windows/SQL Server Authentication.
>
>
> "Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
> news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> a
> no
> has
>
>

Linked Server problem ?

Hi All,
Can anyone help me with the folllowing problem that I'm having, when I'm
using my linked server connection from a PC. Here's the overview:
I have setup a linked server to another sql Server successful and created a
view to a database on that remote server. everything works fine from the
server locally. However when I access the view remotely on my PC i get the
error message: "Login failed 'test'. Reason not associated with a trusted
server connection". I have tried many security configuration, but still no
joy. The only strange thing when I login into my machine as the
Administrator account on the nettwork, it works fine, even my own account has
full administration privileges on the network.
Any help would be great!.
thanks,
Liam.Do you have with your account access on the SQL Server and on the DB Link/
DB
what kind of security use the link
"Liam Mac" <LiamMac@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> Hi All,
> Can anyone help me with the folllowing problem that I'm having, when I'm
> using my linked server connection from a PC. Here's the overview:
> I have setup a linked server to another sql Server successful and created
> a
> view to a database on that remote server. everything works fine from the
> server locally. However when I access the view remotely on my PC i get the
> error message: "Login failed 'test'. Reason not associated with a trusted
> server connection". I have tried many security configuration, but still
> no
> joy. The only strange thing when I login into my machine as the
> Administrator account on the nettwork, it works fine, even my own account
> has
> full administration privileges on the network.
> Any help would be great!.
> thanks,
> Liam.
>
>|||Lian
Probably your remote server is configured with Windows Authentication .
Change it to Windows/SQL Server Authentication.
"Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> Hi All,
> Can anyone help me with the folllowing problem that I'm having, when I'm
> using my linked server connection from a PC. Here's the overview:
> I have setup a linked server to another sql Server successful and created
a
> view to a database on that remote server. everything works fine from the
> server locally. However when I access the view remotely on my PC i get the
> error message: "Login failed 'test'. Reason not associated with a trusted
> server connection". I have tried many security configuration, but still
no
> joy. The only strange thing when I login into my machine as the
> Administrator account on the nettwork, it works fine, even my own account
has
> full administration privileges on the network.
> Any help would be great!.
> thanks,
> Liam.
>
>|||thanks guys, i got to the bottom of my problem. one of my boxes did not have
sql service pack 3a installed. once installed all ok.
liam
"Uri Dimant" wrote:
> Lian
> Probably your remote server is configured with Windows Authentication .
> Change it to Windows/SQL Server Authentication.
>
>
> "Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
> news:B27A43FB-108A-48EB-8132-9388C210CB7F@.microsoft.com...
> > Hi All,
> >
> > Can anyone help me with the folllowing problem that I'm having, when I'm
> > using my linked server connection from a PC. Here's the overview:
> >
> > I have setup a linked server to another sql Server successful and created
> a
> > view to a database on that remote server. everything works fine from the
> > server locally. However when I access the view remotely on my PC i get the
> > error message: "Login failed 'test'. Reason not associated with a trusted
> > server connection". I have tried many security configuration, but still
> no
> > joy. The only strange thing when I login into my machine as the
> > Administrator account on the nettwork, it works fine, even my own account
> has
> > full administration privileges on the network.
> >
> > Any help would be great!.
> >
> > thanks,
> > Liam.
> >
> >
> >
>
>sql

linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.
It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:

>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>

linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:
>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>sql

linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:

>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>

Linked server issue

We are utilizing a linked server connection to a proprietary database server in SQL 2005. Connectivity is thru an ODBC driver written by the proprietary database server's vendor. This has been running successfully for several years utilizing SQL 2000, however we are experiencing problems with 2005. It appears that the proprietary database server can only answer queries single-threaded, or in other words, it must complete a query before it can begin another. Whenever the SQL 2005 server sends multiple queries to the linked server before it has a chance to respond, the connection freezes until the SQL 2005 server can be restarted. As support for the proprietary database server is limited, we are trying to identify if there is a way in SQL 2005 to queue the connections to the linked server to prevent this "traffic jam?" Adjusting connection timeouts only reduces the frequency of the "freezes" and we really need to find a solution to this.

Any ideas, short of writing a custom ODBC driver, would be appreciated.

Thanks in advance.

If you are operating on data within SQL server using data from this linked database, you could wrap that functionality in a transaction to enforce serialization and ensure that this strange single threaded server gets only one connection at a time. This would also provide you with transactional logging to ensure that you handle any other errors that come up without data corruption or loss.

Hope this helps,

John

|||This *could* work but I was hoping for a more global solution as the server is passed a lot of Adhoc queries via OLE and ODBC.|||

You could write a stored procedure if you have a common workload and implement your transactions within it to provide similar service across network stacks. Realistically, any approach to serializing an inherently distributed operation will seem nasty, and the real issue is that any operation involving that linked server is non-reentrant.

Typically, non-reentrant code is very dangerous to threaded code, in that global shared state within the non-reentrant function can nuke the results from another. You can skirt this by wrapping all operations that use that function or server in one big critical section, to avoid another connection coming in and stepping all over the shared state; however, this comes at a considerable performance cost. In the long run, this particular service (if it is used extensively and broadly enough) will become a major bottleneck to the performance of your pipeline. There really is no right answer to how to address this problem other than to either upgrade that driver to a multithreaded one, remove that service from your pipeline, or if possible -- replace real time communication with storage to a table in SQL Server with a batch job running nightly to send data across to synchronize the services. If SQL Server is the consumer of data in this relationship, rather than the producer, or if you have a need for real-time updates to and from the linked server then of course this would not address your issue.

I sympathize with your situation, and hope this helps.

John

Linked Server in SQL Server Express

I am just trying to setup a connection to an Oracl DB with a linked server i
n
SQL Server Express. It was successful to setup the linkedserver, since I
needed to login I tried a script like this:
USE [MyDB]
GO
EXEC sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'MylinkedServer'
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
GO
Now I got a message wrong syntax near MylinkedServer (message 102, level 15,
status 1, line 2) and I do not know what that means. Also I thought I would
be able to see my linkedserver somewhere in SQL Server Management Studio
Express, but unfortunately all articles only refer to Enterprise Manager
which I do not have with the Express Edition.Hi,
In your call to sp_addlinkedsrvlogin do you really have the square
brackets ([ and ]) around @.rmtsrvname? If you do, this is what is
causing the error. Also, you are missing a comma after the
'MyLinkedServer'. Try calling sp_addlinkedsrvlogin like:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'MylinkedServer',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
This doesn't generate any syntax errors on my SQL Server Express
instance so maybe it will work for you.
In SQL Server Management Studio Express you can see your linked servers
under the "Server Objects" node. Your object browser in Management
Studio Express should look like:
Instance Name
|
| <There's some stuff in the middle but don't worry about it>
|
|
-- Server Objects
|
|
-- Linked Servers
Hope that helps a bit|||Hi,
thanks I really forgot to remove the brackets. Sometimes you just don't see
the forest for the trees.
However in SQL Server Management Studio Express I still cannot find the
server. (Unfortunately) I do have a german version of it, by translating the
names I can find Serverobjects and something that might be a translation of
linked servers. There is only another submenu called contractor (with a lot
of entries) and nowhere is my linked server.
Since I am just reading through all that stuff, could you or anybody else
also tell me how to query tables on the linked server? I hoped to see them
somewhere under the linked server in the management studio and get a first
hint by that...|||Sorry I was to fast, got the answer for my last question about querying the
linked server here:
http://msdn.microsoft.com/library/d...br />
5xix.asp|||...and after restarting the management studio I can see my linked server!|||Hi Thomas,
Yeah, I find that sometimes a fresh pair of eyes to look at something
is good...

>From what you are describing I think you have found the linked servers
node. I believe the submenu (contractor) you are talking about would be
the "Provider" menu in the English version. Just for information, this
menu lists the provides (e.g. OLE DB, Native, Jet) that you can use to
connect to a linked server. No need to worry about this menu so much.
It's strange that you can't see your linked server in the GUI. Have you
tried adding a linked server via Management Studio Express. You can add
a linked server by clicking on the linked servers node and selecting
"New Linked Server..." (not sure what that would be in German)|||> It's strange that you can't see your linked server in the GUI. Have you
> tried adding a linked server via Management Studio Express. You can add
> a linked server by clicking on the linked servers node and selecting
> "New Linked Server..." (not sure what that would be in German)
I can see it now, it just needs a refresh sometimes. Do you have some
experience about its performance regarding queries? I linked an oracle serve
r
and when I query something it is relatively slow, even compared to Jet which
I used before.|||Not really, sorry. Are you running across a network? Maybe congestion
has something to do with it?
Also, I remember hearing that certain queries cause an entire result
set to be transmitted to your local server and not just the rows you
want. Don't remember any more than that, sorry.sql

Linked Server in SQL Server Express

I am just trying to setup a connection to an Oracl DB with a linked server in
SQL Server Express. It was successful to setup the linkedserver, since I
needed to login I tried a script like this:
USE [MyDB]
GO
EXEC sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'MylinkedServer'
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
GO
Now I got a message wrong syntax near MylinkedServer (message 102, level 15,
status 1, line 2) and I do not know what that means. Also I thought I would
be able to see my linkedserver somewhere in SQL Server Management Studio
Express, but unfortunately all articles only refer to Enterprise Manager
which I do not have with the Express Edition.Hi,
In your call to sp_addlinkedsrvlogin do you really have the square
brackets ([ and ]) around @.rmtsrvname? If you do, this is what is
causing the error. Also, you are missing a comma after the
'MyLinkedServer'. Try calling sp_addlinkedsrvlogin like:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'MylinkedServer',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
This doesn't generate any syntax errors on my SQL Server Express
instance so maybe it will work for you.
In SQL Server Management Studio Express you can see your linked servers
under the "Server Objects" node. Your object browser in Management
Studio Express should look like:
Instance Name
|
| <There's some stuff in the middle but don't worry about it>
|
|
-- Server Objects
|
|
-- Linked Servers
Hope that helps a bit|||Hi,
thanks I really forgot to remove the brackets. Sometimes you just don't see
the forest for the trees.
However in SQL Server Management Studio Express I still cannot find the
server. (Unfortunately) I do have a german version of it, by translating the
names I can find Serverobjects and something that might be a translation of
linked servers. There is only another submenu called contractor (with a lot
of entries) and nowhere is my linked server.
Since I am just reading through all that stuff, could you or anybody else
also tell me how to query tables on the linked server? I hoped to see them
somewhere under the linked server in the management studio and get a first
hint by that...|||Sorry I was to fast, got the answer for my last question about querying the
linked server here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp|||...and after restarting the management studio I can see my linked server!|||Hi Thomas,
Yeah, I find that sometimes a fresh pair of eyes to look at something
is good...
>From what you are describing I think you have found the linked servers
node. I believe the submenu (contractor) you are talking about would be
the "Provider" menu in the English version. Just for information, this
menu lists the provides (e.g. OLE DB, Native, Jet) that you can use to
connect to a linked server. No need to worry about this menu so much.
It's strange that you can't see your linked server in the GUI. Have you
tried adding a linked server via Management Studio Express. You can add
a linked server by clicking on the linked servers node and selecting
"New Linked Server..." (not sure what that would be in German)|||> It's strange that you can't see your linked server in the GUI. Have you
> tried adding a linked server via Management Studio Express. You can add
> a linked server by clicking on the linked servers node and selecting
> "New Linked Server..." (not sure what that would be in German)
I can see it now, it just needs a refresh sometimes. Do you have some
experience about its performance regarding queries? I linked an oracle server
and when I query something it is relatively slow, even compared to Jet which
I used before.|||Not really, sorry. Are you running across a network? Maybe congestion
has something to do with it?
Also, I remember hearing that certain queries cause an entire result
set to be transmitted to your local server and not just the rows you
want. Don't remember any more than that, sorry.

Linked server help: MAS90 URGENT!

Has anyone successfully created a linked server to a MAS90 database? I have
tried creating a UDL file and copying the connection string into the linked
server area, but Im running into errors when trying to access anything in
the database. I have also tried using OPENQUERY with the same connection
string from the UDL. I am able to create a linked table to MAS in access
just fine. I have emailed Sage, they gave me a sample but it didnt work.
select * from
openrowset( 'MSDASQL',
'DSN=SOTAMAS90;UID=7AB;PWD=;DESCRIPTION=MAS 90 32-Bit ODBC Driver;
Directory=\\MAS200\MAS90;Company=D94;SERVER=NotTheServer','select * from
GL5_DetailPosting' ) as A
This is the error message with OPENQUERY, which is more descriptive then
EM's error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an
unexpected catastrophic failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x8000ffff: The provider reported an unexpected catastrophic
failure.].I normally use udl file if I want to create a connection string in the
asp/.net file. rather than hand type the mumbo jumbo, I can just copy
and paste into the file. But never on creating a linked server.
I don't think you can use the connection string the udl file created
(at least not entirely) to create a linked server. Particularly you
hit a problem now, it is better to create it from scratch.
Here is the syntax of OPENROWSET.
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
For your case (with the information above), it should be:
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=NotTheServer;UID=7AB;PWD=xxx',
GL5_DetailsPosting) AS a
For the driver name, check your ODBC datasource Administrator panel
'drivers' tag.
Melsql

Linked server help: MAS90 URGENT!

Has anyone successfully created a linked server to a MAS90 database? I have
tried creating a UDL file and copying the connection string into the linked
server area, but Im running into errors when trying to access anything in
the database. I have also tried using OPENQUERY with the same connection
string from the UDL. I am able to create a linked table to MAS in access
just fine. I have emailed Sage, they gave me a sample but it didnt work.
select * from
openrowset( 'MSDASQL',
'DSN=SOTAMAS90;UID=7AB;PWD=;DESCRIPTION=
MAS 90 32-Bit ODBC Driver;
Directory=\\MAS200\MAS90;Company=D94;SER
VER=NotTheServer','select * from
GL5_DetailPosting' ) as A
This is the error message with OPENQUERY, which is more descriptive then
EM's error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an
unexpected catastrophic failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x8000ffff: The provider reported an unexpected catastrophic
failure.].I normally use udl file if I want to create a connection string in the
asp/.net file. rather than hand type the mumbo jumbo, I can just copy
and paste into the file. But never on creating a linked server.
I don't think you can use the connection string the udl file created
(at least not entirely) to create a linked server. Particularly you
hit a problem now, it is better to create it from scratch.
Here is the syntax of OPENROWSET.
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
For your case (with the information above), it should be:
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=NotTheServer;UID=7AB;PWD=
xxx',
GL5_DetailsPosting) AS a
For the driver name, check your ODBC datasource Administrator panel
'drivers' tag.
Mel

Linked Server Fails w/Error: 7399

I'm trying to connnect from a SQL Server 7.0 db to an
Oracle 8.i instance. Connection is failing with the
following error message: Error 7399 OLE DB
Provider 'MSDAOR' reported an error.
I used the following ddl to create my linked server &
linked server logon:
exec
sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
rd'
exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
Server logon', 'Oracle Server Logon', 'Oracle Server logon
password'
I can connect to the Oracle database from the box where
I'm running SQL Server.
When I issue: ping oracle server name from box running SQL
Server I can ping server.
When I issue tnsping oracle database name from box running
SQL server I can ping Oracle database.
When I issue sqlplus ORA logon/ORA password@.ORA dbname I
can logon to database so I know I can connect to the box
and I'm using the right logon & password.
Any help greatly appreciated. Thanks in advance.Check if these articles help:
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;220915
http://support.microsoft.com/defaul...kb;en-us;277002
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:6f5901c4061d$a857ef90$a601280a@.phx.gbl...
> I'm trying to connnect from a SQL Server 7.0 db to an
> Oracle 8.i instance. Connection is failing with the
> following error message: Error 7399 OLE DB
> Provider 'MSDAOR' reported an error.
> I used the following ddl to create my linked server &
> linked server logon:
> exec
> sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
> rd'
> exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
> Server logon', 'Oracle Server Logon', 'Oracle Server logon
> password'
> I can connect to the Oracle database from the box where
> I'm running SQL Server.
> When I issue: ping oracle server name from box running SQL
> Server I can ping server.
> When I issue tnsping oracle database name from box running
> SQL server I can ping Oracle database.
> When I issue sqlplus ORA logon/ORA password@.ORA dbname I
> can logon to database so I know I can connect to the box
> and I'm using the right logon & password.
> Any help greatly appreciated. Thanks in advance.|||I reviewed the Knowledge Base articles you suggested. This was a pre-existi
ng linked server definition that was working and has just failed. I've atte
mpted to redefine it. I'm not getting the message about the Oracle client a
nd networking comonents mis
sing so I'm ruling out 277002 & 220915. Article 280106 suggests that the ve
rsion of MDAC might be wrong (you need MDAC 2.5, or later, installed on SQL
Server computer). How do I check my version of MDAC to see if I'm at 2.5 or
later? TIA|||> How do I check my version of MDAC to see if I'm at 2.5 or later?
You can determine the installed MDAC version with the component checker
tool:
<http://www.microsoft.com/downloads/...a8df6-4a21-4b43
-bf53-14332ef092c9&displaylang=en>
Hope this helps.
Dan Guzman
SQL Server MVP
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:89454DC5-D415-4284-95FD-760FA7B0C8EC@.microsoft.com...
> I reviewed the Knowledge Base articles you suggested. This was a
pre-existing linked server definition that was working and has just failed.
I've attempted to redefine it. I'm not getting the message about the Oracle
client and networking comonents missing so I'm ruling out 277002 & 220915.
Article 280106 suggests that the version of MDAC might be wrong (you need
MDAC 2.5, or later, installed on SQL Server computer). How do I check my
version of MDAC to see if I'm at 2.5 or later? TIA|||I downloaded the component checker and verified I have MDAC 2.5 sp3 installe
d on my client (SQL Server) computer. I took another look at one of the Kno
wledge Base Articles, #280106, that Dejan suggested. We checked the registr
y entries for Oracle client
software. We found that the registry entries were pointing to Oracle Client
versions for Oracle Client 7.3 event though the dlls were not present on th
e machine, Oracle Client 8.i is loaded onto the machine. That's the version
we expected to see in the
registry. We suspected that the registry entries were somehow corrupted or
overlaid. When we checked the event log for the server it was full of error
s, the server had lost it's connection to the domain server. We scheduled a
n emergecy reboot of the s
erver (server hadn't been stopped & restarted in over 100 days) and when the
server came back up we were able to query our linked server.

Linked Server Error

Hello,
I have installed IBM AS/400 Client Access on my machine and then added a new
ODBC Data Source.
I have been working with this ODBC connection on several DTS packages and
everything has worked just fine.
Right now, I need to access some information on my AS/400 server from
several stored procedures on my SQL Server. I already configured a Linked
Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
Driver.
When I click the Tables folder under the new linked server, I can browse
through all the tables on the 400 Database. But when I execute any query
(using OPENQUERY or 4-part names in a normal query) I get the following erro
r:
****************************************
***+++
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32 bits)] Driver not capable.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned
0x80004005: ].
****************************************
**
Why is this happening? What is the error? Is there anything I am doing
wrong? I have been "fighting" with this problem for several hours.
Thanks a lot, in advance, for all the help you can give me.Gabriel --
See if this thread helps at all:
http://www.mcse.ms/message979460.html
same thread, different format:
http://www.newsarch.com/archive/new...
54.html
Gabriel Giraldo wrote:
> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a n
ew
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following er
ror:
> ****************************************
***+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returne
d
> 0x80004005: ].
> ****************************************
**
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>|||It has been awhile since I worked with the AS/400. Based on the error
message, I would look at the client software installed on the SQL end. The
second thing to look at is the syntax used to attempt to get data. As long a
s
the SQL is fully ANSI standard, you should be fine on both ends; if not, the
likelihood of error increases.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Gabriel Giraldo" wrote:

> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a n
ew
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following er
ror:
> ****************************************
***+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returne
d
> 0x80004005: ].
> ****************************************
**
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>

Linked Server Connection to Sybase

Has anyone had problems using an OLEDB linked server connection to Sybase ASE 12.5? I'm having major performance problems when I use string criteria in the where clause. Queries that return 1 row and execute in less than a second using a native Sybase connection take 40 seconds to run using the linked server OLEDB connection. If I use ints in the where clause performance is almost exactly the same between native and linked connections.

Any ideas?Did I mention I hate sybase...

Just had a project thrown at me...never touched it before...

I know there "cousins", but man is it quirky...

Saw a developer do

SELECT Col1, MAX(Col2), SUM(Col3)
FROM Table
WHERE Col1 = 'A'

AND IT RAN!

I couldn't believe it...and it gave back complete garbage...

Can you post your statement?

I'll try and link that server up and see what happens...

(No wonder they lost market share)|||Hi, I had a similar problem with ASE6, using a linked server in SQL server 2000.
The problem was I had a very poor performance (10 secs per query), and I had to make an update in 200,000 regs using that query for every record... also I had problems in my linked server too (it worked when it wanted, i never discovered the cause...)

My solution was very desesperate: instead of connecting SQL server to the ODBC linked to the ASE server, I made a program in Visual Basic with a connection to the ODBC. I wrote the update procedure with ADO.
The results: the whole update was finished in 15 secs (remember: in SQL server each query took 10 secs, multiplied by 200,000 records...).

I dont understand why this happens, a SQL server should be faster accessing data behind an ODBC than a VB program...|||Here's the SQL:

select *
from [32tlsql2-dreamdb].dreamdb.dbo.drt_contract
where i_con_contract = '000Q019999'
and c_company = 'P00'
and c_mkt_division = 'IPD'

Here's the table DDL (please don't make jokes about the crappy design of the table. Unfortunately I got stuck with it):

/****************** CREATE ORDER ******************/
create table dbo.drt_contract
(
i_client numeric(10,0) not null ,
i_con_contract char(10) not null ,
c_company char(3) not null ,
c_mkt_division char(3) not null ,
c_level numeric(2,0) Default 1 not null ,
c_interface_company char(6) null ,
c_form char(8) not null ,
c_con_contract_type char(2) not null ,
c_agent_type char(3) null ,
c_con_status char(2) null ,
d_final_disposition datetime null ,
d_lifecom_updated datetime null ,
c_lifecom_status char(1) null ,
d_con_terminate datetime null ,
d_con_da_report_terminate datetime null ,
d_con_signed datetime null ,
d_con_effective datetime null ,
c_con_line_of_business char(1) null ,
i_con_rsm char(10) null ,
c_con_publicity char(1) null ,
c_con_uw_eligiblity char(1) null ,
c_con_premium_remission char(1) null ,
q_con_years_experience char(1) null ,
c_con_pay char(2) null ,
c_con_report char(2) null ,
c_con_review char(1) null ,
c_con_additional_aa_trailer char(8) null ,
x_con_text varchar(250) null ,
c_con_terminate_reason char(2) null ,
f_con_assign_commissions char(1) null ,
n_con_assign_commissions varchar(50) null ,
i_con_financial_owner char(10) null ,
c_con_financial_owner_level char(1) null ,
i_con_address_owner char(10) null ,
c_con_address_owner_level char(1) null ,
c_con_mail char(1) null ,
c_con_hire_type char(2) null ,
c_con_change_type char(2) null ,
c_zipcode_processing char(1) null ,
n_con_sub_name varchar(30) null ,
d_con_nasd_hire datetime null ,
c_con_nasd_status char(2) null ,
i_con_alt_rep1 char(10) null ,
i_con_alt_rep2 char(10) null ,
i_con_alt_rep3 char(10) null ,
i_con_branch char(10) null ,
c_con_rep_class char(2) null ,
i_con_old_rep_nbr varchar(10) null ,
i_con_old_branch char(10) null ,
i_user_last_change char(8) null ,
h_last_change datetime null
)
Lock Datarows on "default"
go


/****************** ALTER ORDER CONSTRAINTS ******************/
alter table dbo.drt_contract
add constraint pk_contract primary key clustered ( i_con_contract asc ,c_company asc ,c_mkt_division asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_client on dbo.drt_contract ( i_client asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_con_status on dbo.drt_contract ( c_con_status asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_financial_owner on dbo.drt_contract ( i_con_financial_owner asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_address_owner on dbo.drt_contract ( i_con_address_owner asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_mkt_division on dbo.drt_contract ( c_mkt_division asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_alt_rep3 on dbo.drt_contract ( i_con_alt_rep3 asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_company on dbo.drt_contract ( c_company asc )
go|||You want crappy design?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26547

It looks like it should use the clustered index...but it still has to go to the datapage because of SELECT *

Which, in an application aint a good idea...do you need all of the columns?

What data do you actually need?|||Yep, you're right but I'm doing some quick testing so I didn't want to type all of the column names. The select * won't be used by any application or process. Anyways, I've specified the all of the column names and also only the 5-6 columns that I care to review and it still executes in the same amount of time. Oh yeah, Sybase nor Microsoft will provide support for OLEDB linked server connections to Sybase...SWEET|||I don't know if sybase has index intersection, but did you try and create a covered index with the 6 columns that you need

Sybase should at least use that index....

Or, can you create a sproc on sybase and call that?

My guess is that it's dragging way mopre data across than it needs to and sql server is figuring out the final results...

I'm not sure...

Gotta read up on how linked servers manage the data...|||The SQL statement only returns 1 row. So you think that a larger set of data is being sent to SQL Server and it has to narrow the set down to the final row? Hmm... remember I'm only encountering the problem when I use a string in the where clause. Integers work fine. I'll let you know how the covered index works.|||Here's the solution, use openquery() to execute the sql.

select * from openquery(linked server connection, sql stetament)|||Thanks for the info!

Linked server connection to SQL Server 2005

I have an error message of connecting to a SQL Server 2005 from SQL Server
7.0. I tried to set up a linked server connection from SQL Server 2000 and
it works fine. It not working on SQL Server 7.0. Is there a restriction of
connecting to SQL Server 2005 from 7.0? Thanks!
Error Message: Error 2: Access denied
Hi
Usually this message is because there is security issue. Have you run
sp_addlinkedsrvlogin?
John
"KTN" wrote:

> I have an error message of connecting to a SQL Server 2005 from SQL Server
> 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> it works fine. It not working on SQL Server 7.0. Is there a restriction of
> connecting to SQL Server 2005 from 7.0? Thanks!
> Error Message: Error 2: Access denied
|||I used both sp_addserver & sp_addlinkedsrvlogin. I tried these on SQL Server
2000 connecting to 2005 and it works. I then used the same script on SQL
Server 7.0 and it failed. Is this something Microsoft are not supporting on
version 7.0 with 2005? Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Usually this message is because there is security issue. Have you run
> sp_addlinkedsrvlogin?
> John
> "KTN" wrote:

Linked server connection to SQL Server 2005

I have an error message of connecting to a SQL Server 2005 from SQL Server
7.0. I tried to set up a linked server connection from SQL Server 2000 and
it works fine. It not working on SQL Server 7.0. Is there a restriction of
connecting to SQL Server 2005 from 7.0? Thanks!
Error Message: Error 2: Access deniedHi
Usually this message is because there is security issue. Have you run
sp_addlinkedsrvlogin?
John
"KTN" wrote:
> I have an error message of connecting to a SQL Server 2005 from SQL Server
> 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> it works fine. It not working on SQL Server 7.0. Is there a restriction of
> connecting to SQL Server 2005 from 7.0? Thanks!
> Error Message: Error 2: Access denied|||I used both sp_addserver & sp_addlinkedsrvlogin. I tried these on SQL Server
2000 connecting to 2005 and it works. I then used the same script on SQL
Server 7.0 and it failed. Is this something Microsoft are not supporting on
version 7.0 with 2005? Thanks!
"John Bell" wrote:
> Hi
> Usually this message is because there is security issue. Have you run
> sp_addlinkedsrvlogin?
> John
> "KTN" wrote:
> > I have an error message of connecting to a SQL Server 2005 from SQL Server
> > 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> > it works fine. It not working on SQL Server 7.0. Is there a restriction of
> > connecting to SQL Server 2005 from 7.0? Thanks!
> >
> > Error Message: Error 2: Access denied

Linked server connection to SQL Server 2005

I have an error message of connecting to a SQL Server 2005 from SQL Server
7.0. I tried to set up a linked server connection from SQL Server 2000 and
it works fine. It not working on SQL Server 7.0. Is there a restriction of
connecting to SQL Server 2005 from 7.0? Thanks!
Error Message: Error 2: Access deniedHi
Usually this message is because there is security issue. Have you run
sp_addlinkedsrvlogin?
John
"KTN" wrote:

> I have an error message of connecting to a SQL Server 2005 from SQL Server
> 7.0. I tried to set up a linked server connection from SQL Server 2000 an
d
> it works fine. It not working on SQL Server 7.0. Is there a restriction
of
> connecting to SQL Server 2005 from 7.0? Thanks!
> Error Message: Error 2: Access denied|||I used both sp_addserver & sp_addlinkedsrvlogin. I tried these on SQL Serve
r
2000 connecting to 2005 and it works. I then used the same script on SQL
Server 7.0 and it failed. Is this something Microsoft are not supporting on
version 7.0 with 2005? Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Usually this message is because there is security issue. Have you run
> sp_addlinkedsrvlogin?
> John
> "KTN" wrote:
>

Linked server connection to MS Access db across the network failed

I have attempted to create a linked server on Server1 to an Access db on
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.
As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:

> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same services
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine cannot
> open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
> exclusively by another user, or you need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I realize this an issue of permissions. I am just not sure where the failure
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is located
> on Server1 with SQL Server. However this is not the scenario I am trying to
> solution.
> Please advise.
> Thanks.

Linked server connection to MS Access db across the network failed

I have attempted to create a linked server on Server1 to an Access db on
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:

> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same service
s
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password
.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine c
annot
> open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
> exclusively by another user, or you need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I realize this an issue of permissions. I am just not sure where the failu
re
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is locat
ed
> on Server1 with SQL Server. However this is not the scenario I am trying t
o
> solution.
> Please advise.
> Thanks.

Linked Server connection Problem

SQLServer2000
Access 2000
Windows Server2003
1. I had a Linked Server to an Access 2000 database which had worked for two
months.
2. Hoever, now when I execute queries againt it in Query Anayler, e.g.
SELECT * FROM LINKEDSERVERNAME...TableName
The query Analyzer just says "Executing query batch" and stays there for
several minutes. No error messages. Pressing red button to end query puts
QA into 'Progran not responding mode'. No error messages in QA.
3. If I go to Linked Server in EM and click on Tables or Views, most of the
time I get same thing - i.e. just hourglass for minutes.
Somtimes in EM when I click on Linked Server Tables or Views, I get Error:
Error 0: SqlDumpExceptionHandler:Process 51 (or 55) generated fatal
exception 0000005 EXCEPTION_ACCESS_VIOLATION. SQLServer is terminating this
process.
4. Only one time, time in EM, when clicking on Linked Server Tables, I got
Error 7339:OLE_DB Provider Microsfot.Jet.OLEDB reported an error.
5. I tried finding answers to this in Google, but to no avail.
Any help would be appreciated. Thanks.
AlanHi Alan,
Have you verified that the connection is actually being made to the linked
server? Check the process info on the destination server to see if a
process ID has been generated by your request. If not, then check your
firewall logs to see if your request is not making it to its intended
target. Hope this helps! Let me know what your results are and we can dig
deeper if need be.
-Jose
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:uR2IxZk$EHA.3988@.TK2MSFTNGP11.phx.gbl...
> SQLServer2000
> Access 2000
> Windows Server2003
> 1. I had a Linked Server to an Access 2000 database which had worked for
> two
> months.
> 2. Hoever, now when I execute queries againt it in Query Anayler, e.g.
> SELECT * FROM LINKEDSERVERNAME...TableName
> The query Analyzer just says "Executing query batch" and stays there for
> several minutes. No error messages. Pressing red button to end query
> puts
> QA into 'Progran not responding mode'. No error messages in QA.
> 3. If I go to Linked Server in EM and click on Tables or Views, most of
> the
> time I get same thing - i.e. just hourglass for minutes.
> Somtimes in EM when I click on Linked Server Tables or Views, I get Error:
> Error 0: SqlDumpExceptionHandler:Process 51 (or 55) generated fatal
> exception 0000005 EXCEPTION_ACCESS_VIOLATION. SQLServer is terminating
> this
> process.
> 4. Only one time, time in EM, when clicking on Linked Server Tables, I got
> Error 7339:OLE_DB Provider Microsfot.Jet.OLEDB reported an error.
> 5. I tried finding answers to this in Google, but to no avail.
> Any help would be appreciated. Thanks.
> Alan
>|||Jose,
Thanks for your quick reply.
1. I looked in sysprocesses of SQLServer tryin to do the select on the
Access Linked Server, and there are indeed records with Pricess ID for those
attempts.
2. I'll have to check with network admin about firewall logs.
3. Note1: This used to work! Does this fact help narrow down anything?
4. Note2: From Access ADP, I can still import tables from the MDB.
5. Is it possible that something got trashed in system table?s I've tried
dropping and recreating the Linked Server. It gets created, but still same
problem.
6. I've tried both regular Windows path as well as UNC path. Access mdb is
on same machine as SQLServer.
Regards,
Alan
"Jose Molina" <rcmaniac23@.yahoo.com> wrote in message
news:eAzoPvk$EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi Alan,
> Have you verified that the connection is actually being made to the linked
> server? Check the process info on the destination server to see if a
> process ID has been generated by your request. If not, then check your
> firewall logs to see if your request is not making it to its intended
> target. Hope this helps! Let me know what your results are and we can dig
> deeper if need be.
> -Jose
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:uR2IxZk$EHA.3988@.TK2MSFTNGP11.phx.gbl...
Error:[vbcol=seagreen]
got[vbcol=seagreen]
>|||Hi Alan,
Have you tried dropping and recreating the linked server? Were there any
group policies implemented around the time you started experiencing issues?
I'm not sure where else to look. Try a post on the
microsoft.public.sqlserver.connect newsgroup to see if anybody else has
experienced this issue.
-Jose
PS Not to be condescending, but is the linked server set up correctly?
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23WGkpSl$EHA.3832@.TK2MSFTNGP10.phx.gbl...
> Jose,
> Thanks for your quick reply.
> 1. I looked in sysprocesses of SQLServer tryin to do the select on the
> Access Linked Server, and there are indeed records with Pricess ID for
> those
> attempts.
> 2. I'll have to check with network admin about firewall logs.
> 3. Note1: This used to work! Does this fact help narrow down anything?
> 4. Note2: From Access ADP, I can still import tables from the MDB.
> 5. Is it possible that something got trashed in system table?s I've tried
> dropping and recreating the Linked Server. It gets created, but still
> same
> problem.
> 6. I've tried both regular Windows path as well as UNC path. Access mdb
> is
> on same machine as SQLServer.
> Regards,
> Alan
>
>
> "Jose Molina" <rcmaniac23@.yahoo.com> wrote in message
> news:eAzoPvk$EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Error:
> got
>|||Jose,
Thanks again for your reply.
1. Yes it was set up OK, because it was working for two months. I don't mnd
your asking.
I had dropped and re-created several times.
2. The network administrator rebooted server last night, and now I can do
SELECT on linked server. in Query Analyzer.
3. Something still off though, because in EM when I click on Linked Server
tables or views in tree, I get
Error 7399 OLE_DB Provider Microsoft.Jet.OLEDB.4.0 reported an error.
IDB Initialize: Initialize returned 80004005.
4. I just found KB 818182
PRB: Error Message: "Unspecified error" Occurs When SQL Server Accesses a
Linked Server That Uses Microsoft OLE DB Provider for Jet 4.0
5. This says using wrong version of Jet 4.0.
I'll have to check this out. Still strange since was working for two
months!
If problem persists I will try Connection group. I thought Security was
right one since that's where Linked Servers are.
Thanks again for your help.
Regards,
Alan
"Jose Molina" <rcmaniac23@.yahoo.com> wrote in message
news:#r44dSy$EHA.600@.TK2MSFTNGP09.phx.gbl...
> Hi Alan,
> Have you tried dropping and recreating the linked server? Were there any
> group policies implemented around the time you started experiencing
issues?
> I'm not sure where else to look. Try a post on the
> microsoft.public.sqlserver.connect newsgroup to see if anybody else has
> experienced this issue.
> -Jose
> PS Not to be condescending, but is the linked server set up correctly?
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:%23WGkpSl$EHA.3832@.TK2MSFTNGP10.phx.gbl...
tried[vbcol=seagreen]
e.g.[vbcol=seagreen]
query[vbcol=seagreen]
of[vbcol=seagreen]
terminating[vbcol=seagreen]
I[vbcol=seagreen]
>|||Hi Alan,
I agree that the post should be here, but just in case I thought you might
be able to get some additional information there.
Here is a thought... were there any updates recently that changed MDAC in
any way? My experience with MDAC has been that it is VERY temperamental.
Go figure...
Have you tried connecting from EM on other machines? The server itself? If
another machine works fine, check the MDAC version of the machine working
and compare it the one that is currently not working. Try looking at:
HKLM\SOFTWARE\Microsoft\DataAccess. You can always rollback the MDAC
version if need be on the machine you are having issues with. (That is if
there is a saved rollback version)
Check here for instructions on rolling back your MDAC version:
http://whidbey.msdn.microsoft.com/l...mdacinstall.asp
I'll continue to check with you to make sure we have a resolution to this
issue.
Regards,
-Jose
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:Ong%236my$EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Jose,
> Thanks again for your reply.
> 1. Yes it was set up OK, because it was working for two months. I don't
> mnd
> your asking.
> I had dropped and re-created several times.
> 2. The network administrator rebooted server last night, and now I can do
> SELECT on linked server. in Query Analyzer.
> 3. Something still off though, because in EM when I click on Linked Server
> tables or views in tree, I get
> Error 7399 OLE_DB Provider Microsoft.Jet.OLEDB.4.0 reported an error.
> IDB Initialize: Initialize returned 80004005.
> 4. I just found KB 818182
> PRB: Error Message: "Unspecified error" Occurs When SQL Server Accesses a
> Linked Server That Uses Microsoft OLE DB Provider for Jet 4.0
>
> 5. This says using wrong version of Jet 4.0.
> I'll have to check this out. Still strange since was working for two
> months!
> If problem persists I will try Connection group. I thought Security was
> right one since that's where Linked Servers are.
> Thanks again for your help.
> Regards,
> Alan
> "Jose Molina" <rcmaniac23@.yahoo.com> wrote in message
> news:#r44dSy$EHA.600@.TK2MSFTNGP09.phx.gbl...
> issues?
> tried
> e.g.
> query
> of
> terminating
> I
>