Help getting linked server to work using multiprotocol!

I'm trying to get two servers to connect via Linked servers using multiprotocol instead of TCP/IP. I can make a Server registration fine using the multiprotocol alias I created in Client Network Utility. I'm not sure how to fill out the linked server properties to make this work with Multiprotocol. It works fine with TCP/IP. Can anyone help?I should add I'm trying to connect two sql 7 servers.|||anyone?

Help for 'EXCEPTION_ACCESS_VIOLATION'

Hi,
I have a linked server on a win2000 server SQL Server which is pointing to
Analyis Services server on another machine. We are using OPENQUERY to run
drillthroughs on the OLAP server from a stored procedure in the sql server.
At one time when several users were doing multiple drillthrough using the
linked server, each returning large amounts of data, we got an exception and
the sql server crashed. We badly need help for this as it happened in
production.
The dump that I got from the dba is pasted below:
Computer type is AT/AT COMPATIBLE.
Bios Version is IBM BIOS Ver 9.0
Current time is 10:45:47 11/07/03.
8 Intel x86 level 6, 899 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 3.
Memory
MemoryLoad = 99%
Total Physical = 7935 MB
Available Physical = 35 MB
Total Page File = 9841 MB
Available Page File = 1876 MB
Total Virtual = 3071 MB
Available Virtual = 6 MB
***Symbol load failed - Proceed to dump without any symbols
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0002.txt
*
****************************************************************************
*
**
*
* BEGIN STACK DUMP:
* 11/07/03 10:45:47 spid 101
*
* Exception Address = 12246557
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 000050AE
* s p B I M C o u 12 00 73 00 70 00 42 00 49 00 4d 00 43 00 6f 00 75 00
* n t O f C l a i m 6e 00 74 00 4f 00 66 00 43 00 6c 00 61 00 69 00 6d 00
* s Ð 4 S7 73 00 00 00 00 00 a7 0f 00 09 04 d0 00 34 07 00 53
37
* 0858S Ð Ð 4b D 30 38 35 38 53 00 00 a7 d0 07 09 04 d0 00 34 62 00
44
* RILLTHROUGH MAXROW 52 49 4c 4c 54 48 52 4f 55 47 48 20 4d 41 58 52 4f 57
* S 1000 Select From 53 20 31 30 30 30 20 53 65 6c 65 63 74 20 46 72 6f 6d
* [Procedure] Where 20 5b 50 72 6f 63 65 64 75 72 65 5d 20 57 68 65 72 65
* ([Provider Name]. 20 28 5b 50 72 6f 76 69 64 65 72 20 4e 61 6d 65 5d 2e
* [Provider Name].&[ 5b 50 72 6f 76 69 64 65 72 20 4e 61 6d 65 5d 2e 26 5b
* 27454]) ? Ð 4 32 37 34 35 34 5d 29 00 00 a7 80 00 09 04 d0 00 34
09
* Procedure Ð 00 50 72 6f 63 65 64 75 72 65 00 00 a7 14 00 09
04 d0
* 4 DrillThru 00 34 09 00 44 72 69 6c 6c 54 68 72 75
*
===================================================================== BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Bios Version is IBM BIOS Ver 9.0
Current time is 10:45:47 11/07/03.
8 Intel x86 level 6, 899 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 3.
Memory
MemoryLoad = 99%
Total Physical = 7935 MB
Available Physical = 35 MB
Total Page File = 9841 MB
Available Page File = 1876 MB
Total Virtual = 3071 MB
Available Virtual = 6 MB
***Symbol load failed - Proceed to dump without any symbols
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0002.txt
*
****************************************************************************
*
**
*
* BEGIN STACK DUMP:
* 11/07/03 10:45:47 spid 101
*
* Exception Address = 12246557
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 000050AE
* s p B I M C o u 12 00 73 00 70 00 42 00 49 00 4d 00 43 00 6f 00 75 00
* n t O f C l a i m 6e 00 74 00 4f 00 66 00 43 00 6c 00 61 00 69 00 6d 00
* s Ð 4 S7 73 00 00 00 00 00 a7 0f 00 09 04 d0 00 34 07 00 53
37
* 0858S Ð Ð 4b D 30 38 35 38 53 00 00 a7 d0 07 09 04 d0 00 34 62 00
44
* RILLTHROUGH MAXROW 52 49 4c 4c 54 48 52 4f 55 47 48 20 4d 41 58 52 4f 57
* S 1000 Select From 53 20 31 30 30 30 20 53 65 6c 65 63 74 20 46 72 6f 6d
* [Procedure] Where 20 5b 50 72 6f 63 65 64 75 72 65 5d 20 57 68 65 72 65
* ([Provider Name]. 20 28 5b 50 72 6f 76 69 64 65 72 20 4e 61 6d 65 5d 2e
* [Provider Name].&[ 5b 50 72 6f 76 69 64 65 72 20 4e 61 6d 65 5d 2e 26 5b
* 27454]) ? Ð 4 32 37 34 35 34 5d 29 00 00 a7 80 00 09 04 d0 00 34
09
* Procedure Ð 00 50 72 6f 63 65 64 75 72 65 00 00 a7 14 00 09
04 d0
* 4 DrillThru 00 34 09 00 44 72 69 6c 6c 54 68 72 75
*
*
* MODULE BASE END SIZE
*
* Edi: 12286A45: 2D6A1075 06E8006A 5E000003 000CC25D 6AEC8B55
0875F
F01
* Esi: 0C1CCF2C: 121A9488 00000001 133EBC20 0D1A6AD8 189D3EF8
00000
001
* Eax: 000050AA:
* Ebx: 000050AA:
* Ecx: 0000003B:
* Edx: 00107C2C: 00103EC8 FFFFFFFF 00000000 00000000 00000000
00000
FA0
* Eip: 12246557: 0443B70F 7508453B 0C458B0F 75164339 E045C707
00000
001
* Ebp: 0C1CCF0C: 0C1CCF88 12246D85 0000015F 00008D3E 12286A45
133EB
C20
* SegCs: 0000001B:
* EFlags: 00010206: 004E0049 0054004E 0073005C 00730079 00650074
00330
06D
* Esp: 0C1CCC94: 189D3EF8 00000001 00000001 0000FFFF 189D0000
00001
378
* SegSs: 00000023:
*
****************************************************************************
*
Thanks for your help,
JGPYour problem is significantly larger than what newsgroup help can usually
offer. I strongly suggest opening a case with Microsoft Product Support
Services. If you don't have an agreement with them, I believe it is $245
per incident. They have the tools to analyze your crash and make
recommendations on how to avoid such a condition
in the future.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Chumma Dede" <deja.removethis@.chothu.mailshell.com> wrote in message
news:O4%239ewGsDHA.1744@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a linked server on a win2000 server SQL Server which is pointing to
> Analyis Services server on another machine. We are using OPENQUERY to run
> drillthroughs on the OLAP server from a stored procedure in the sql
server.
> At one time when several users were doing multiple drillthrough using the
> linked server, each returning large amounts of data, we got an exception
and
> the sql server crashed. We badly need help for this as it happened in
> production.
>
> The dump that I got from the dba is pasted below:
> Computer type is AT/AT COMPATIBLE.
> Bios Version is IBM BIOS Ver 9.0
> Current time is 10:45:47 11/07/03.
> 8 Intel x86 level 6, 899 Mhz processor(s).
> Windows NT 5.0 Build 2195 CSD Service Pack 3.
>
> Memory
> MemoryLoad = 99%
> Total Physical = 7935 MB
> Available Physical = 35 MB
> Total Page File = 9841 MB
> Available Page File = 1876 MB
> Total Virtual = 3071 MB
> Available Virtual = 6 MB
> ***Symbol load failed - Proceed to dump without any symbols
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0002.txt
> *
>
****************************************************************************
> *
> **
> *
> * BEGIN STACK DUMP:
> * 11/07/03 10:45:47 spid 101
> *
> * Exception Address = 12246557
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 000050AE
> * s p B I M C o u 12 00 73 00 70 00 42 00 49 00 4d 00 43 00 6f 00 75
00
> * n t O f C l a i m 6e 00 74 00 4f 00 66 00 43 00 6c 00 61 00 69 00 6d
00
> * s Ð 4 S7 73 00 00 00 00 00 a7 0f 00 09 04 d0 00 34 07 00
53
> 37
> * 0858S Ð Ð 4b D 30 38 35 38 53 00 00 a7 d0 07 09 04 d0 00 34 62 00
> 44
> * RILLTHROUGH MAXROW 52 49 4c 4c 54 48 52 4f 55 47 48 20 4d 41 58 52 4f
57
> * S 1000 Select From 53 20 31 30 30 30 20 53 65 6c 65 63 74 20 46 72 6f
6d
> * [Procedure] Where 20 5b 50 72 6f 63 65 64 75 72 65 5d 20 57 68 65 72
65
> * ([Provider Name]. 20 28 5b 50 72 6f 76 69 64 65 72 20 4e 61 6d 65 5d
2e
> * [Provider Name].&[ 5b 50 72 6f 76 69 64 65 72 20 4e 61 6d 65 5d 2e 26
5b
> * 27454]) ? Ð 4 32 37 34 35 34 5d 29 00 00 a7 80 00 09 04 d0 00
34
> 09
> * Procedure Ð 00 50 72 6f 63 65 64 75 72 65 00 00 a7 14 00 09
> 04 d0
> * 4 DrillThru 00 34 09 00 44 72 69 6c 6c 54 68 72 75
> *
> =====================================================================> BugCheck Dump
> =====================================================================> This file is generated by Microsoft SQL Server 8.00.760
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
> Computer type is AT/AT COMPATIBLE.
> Bios Version is IBM BIOS Ver 9.0
> Current time is 10:45:47 11/07/03.
> 8 Intel x86 level 6, 899 Mhz processor(s).
> Windows NT 5.0 Build 2195 CSD Service Pack 3.
>
> Memory
> MemoryLoad = 99%
> Total Physical = 7935 MB
> Available Physical = 35 MB
> Total Page File = 9841 MB
> Available Page File = 1876 MB
> Total Virtual = 3071 MB
> Available Virtual = 6 MB
> ***Symbol load failed - Proceed to dump without any symbols
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0002.txt
> *
>
****************************************************************************
> *
> **
> *
> * BEGIN STACK DUMP:
> * 11/07/03 10:45:47 spid 101
> *
> * Exception Address = 12246557
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 000050AE
> * s p B I M C o u 12 00 73 00 70 00 42 00 49 00 4d 00 43 00 6f 00 75
00
> * n t O f C l a i m 6e 00 74 00 4f 00 66 00 43 00 6c 00 61 00 69 00 6d
00
> * s Ð 4 S7 73 00 00 00 00 00 a7 0f 00 09 04 d0 00 34 07 00
53
> 37
> * 0858S Ð Ð 4b D 30 38 35 38 53 00 00 a7 d0 07 09 04 d0 00 34 62 00
> 44
> * RILLTHROUGH MAXROW 52 49 4c 4c 54 48 52 4f 55 47 48 20 4d 41 58 52 4f
57
> * S 1000 Select From 53 20 31 30 30 30 20 53 65 6c 65 63 74 20 46 72 6f
6d
> * [Procedure] Where 20 5b 50 72 6f 63 65 64 75 72 65 5d 20 57 68 65 72
65
> * ([Provider Name]. 20 28 5b 50 72 6f 76 69 64 65 72 20 4e 61 6d 65 5d
2e
> * [Provider Name].&[ 5b 50 72 6f 76 69 64 65 72 20 4e 61 6d 65 5d 2e 26
5b
> * 27454]) ? Ð 4 32 37 34 35 34 5d 29 00 00 a7 80 00 09 04 d0 00
34
> 09
> * Procedure Ð 00 50 72 6f 63 65 64 75 72 65 00 00 a7 14 00 09
> 04 d0
> * 4 DrillThru 00 34 09 00 44 72 69 6c 6c 54 68 72 75
> *
> *
> * MODULE BASE END SIZE
> *
> * Edi: 12286A45: 2D6A1075 06E8006A 5E000003 000CC25D 6AEC8B55
> 0875F
> F01
> * Esi: 0C1CCF2C: 121A9488 00000001 133EBC20 0D1A6AD8 189D3EF8
> 00000
> 001
> * Eax: 000050AA:
> * Ebx: 000050AA:
> * Ecx: 0000003B:
> * Edx: 00107C2C: 00103EC8 FFFFFFFF 00000000 00000000 00000000
> 00000
> FA0
> * Eip: 12246557: 0443B70F 7508453B 0C458B0F 75164339 E045C707
> 00000
> 001
> * Ebp: 0C1CCF0C: 0C1CCF88 12246D85 0000015F 00008D3E 12286A45
> 133EB
> C20
> * SegCs: 0000001B:
> * EFlags: 00010206: 004E0049 0054004E 0073005C 00730079 00650074
> 00330
> 06D
> * Esp: 0C1CCC94: 189D3EF8 00000001 00000001 0000FFFF 189D0000
> 00001
> 378
> * SegSs: 00000023:
> *
>
****************************************************************************
> *
>
>
> Thanks for your help,
> JGP
>sql

help creating linked server to Unify ELS on SCO unix

hello,
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1
I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.
When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?
I'm new at this linked server stuff!
Product Name:
Data Source:
Provider String:
Location:
Catalog:
Thanks,
Oskar
Did you install the ODBC driver on the SQL Server box - not
just your PC - and create a system DSN on the SQL Server
box?
-Sue
On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
<nothing@.nothing.gone> wrote:

>hello,
>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>(very old, odbc is version 1) database on SCO unix.
>The odbc driver is old but it works fine when used by applications for
>creating reports.
>What I want to do is make a linked server from the SCO box to the win2k
>box. So, I'm using enterprise manager to create the linked server.
>The DSN of the unify database is CORE_OFFICE1
>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>Datasource name not found and no default driver specified.
>When I create the linked server I chose the OLE DB for ODBC drivers.
>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>security with the name of a unix account for login/password.
>What else should I have?
>I'm new at this linked server stuff!
>Product Name:
>Data Source:
>Provider String:
>Location:
>Catalog:
>Thanks,
>Oskar
|||Sue Hoegemeier wrote:
> Did you install the ODBC driver on the SQL Server box - not
> just your PC - and create a system DSN on the SQL Server
> box?
Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.
I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!
Thanks,
Oskar

> -Sue
> On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
> <nothing@.nothing.gone> wrote:
>
>
|||Yeah...it's closer. Your just stuck on the authentication from SQL to
Unify. You may want to try first by just setting the security context
so that anyone accessing the linked server uses the same login - that
will at least eliminate who you are logging in as for now.
As you are doing this in enterprise manger, on the security tab,
remove the stuff you put in. Then select the last option "Be made
using this security context" and then enter the login and password
needed to access Unify.
-Sue
On Mon, 16 Aug 2004 09:00:08 -0500, pheonix1t <nothing@.nothing.gone>
wrote:
[vbcol=seagreen]
>Sue Hoegemeier wrote:
>Yes, the odbc driver is installed on the sql server box. However, I had
>it as a User DSN instead of a System DSN. I changed that and now I get
>this error:
>Error 7399, authentication failed. Data source rejected connection attempt.
>I put the 'sa' user as the local account on the linked server security
>tab and I put the unix user/password as the remote account.
>I think this is getting closer, but it's still not there!
>Thanks,
>Oskar
|||update
I found out that the unify driver only works in the USER DSN, not the
SYSTEM DSN. So, after a bit of research I've found that it's possible
to do a kluge by using an Access MDB file to connect to the Unify
database via linked table manager. Then, make the linked server using
the Access driver on the SYSTEM DSN.
How does this sound to you? Kinda wild, huh?
Now, I'm in the process of trying to make it work.
Ah, job security!!
Oskar
ps. Any and all advice welcome

help creating linked server to Unify ELS on SCO unix

hello,
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1
I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.
When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?
I'm new at this linked server stuff!
Product Name:
Data Source:
Provider String:
Location:
Catalog:
Thanks,
OskarDid you install the ODBC driver on the SQL Server box - not
just your PC - and create a system DSN on the SQL Server
box?
-Sue
On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
<nothing@.nothing.gone> wrote:

>hello,
>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>(very old, odbc is version 1) database on SCO unix.
>The odbc driver is old but it works fine when used by applications for
>creating reports.
>What I want to do is make a linked server from the SCO box to the win2k
>box. So, I'm using enterprise manager to create the linked server.
>The DSN of the unify database is CORE_OFFICE1
>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>Datasource name not found and no default driver specified.
>When I create the linked server I chose the OLE DB for ODBC drivers.
>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>security with the name of a unix account for login/password.
>What else should I have?
>I'm new at this linked server stuff!
>Product Name:
>Data Source:
>Provider String:
>Location:
>Catalog:
>Thanks,
>Oskar|||Sue Hoegemeier wrote:
> Did you install the ODBC driver on the SQL Server box - not
> just your PC - and create a system DSN on the SQL Server
> box?
Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.
I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!
Thanks,
Oskar

> -Sue
> On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
> <nothing@.nothing.gone> wrote:
>
>|||Yeah...it's closer. Your just stuck on the authentication from SQL to
Unify. You may want to try first by just setting the security context
so that anyone accessing the linked server uses the same login - that
will at least eliminate who you are logging in as for now.
As you are doing this in enterprise manger, on the security tab,
remove the stuff you put in. Then select the last option "Be made
using this security context" and then enter the login and password
needed to access Unify.
-Sue
On Mon, 16 Aug 2004 09:00:08 -0500, pheonix1t <nothing@.nothing.gone>
wrote:
[vbcol=seagreen]
>Sue Hoegemeier wrote:
>Yes, the odbc driver is installed on the sql server box. However, I had
>it as a User DSN instead of a System DSN. I changed that and now I get
>this error:
>Error 7399, authentication failed. Data source rejected connection attempt
.
>I put the 'sa' user as the local account on the linked server security
>tab and I put the unix user/password as the remote account.
>I think this is getting closer, but it's still not there!
>Thanks,
>Oskar
>|||update
I found out that the unify driver only works in the USER DSN, not the
SYSTEM DSN. So, after a bit of research I've found that it's possible
to do a kluge by using an Access MDB file to connect to the Unify
database via linked table manager. Then, make the linked server using
the Access driver on the SYSTEM DSN.
How does this sound to you? Kinda wild, huh?
Now, I'm in the process of trying to make it work.
Ah, job security!!
Oskar
ps. Any and all advice welcome

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

HELP ! CoCreate of DSO for MSDAORA.1

I have configured an Linked Server from SQL Server to Oracle 9i
database.
With this link Server, i have create in a SQL Server Database a new
view who querying a view on this Linked Server (Oracle).
Also, i have an application that open a connection via ADODB with
a SQL Server database who have a view who refer to an object on Oracle
Linked Server. The connection to SQL Server is make via NT
Authentification.
When i'm connect as a domain administrator on the computer an i run
this application and i consult the view, i have no problem.
But when i'm connect with another user (who is not domain
administrator) and i run the same application i obtain this error
"[Microsoft][ODBC SQL ServerDriver][SQL Server]OLE DB error trace
[Non-interface error: CoCreate of DSO for MSDAORA.1 returned
0x80040154]"
I have search a lot on this problem but with not a lot of
solutions.
Thank !!
Jocelyn"Jocelyn Forest" <forj26@.yahoo.fr> wrote in message
news:ac34b055.0401150610.4efeb024@.posting.google.com...

quote:

---

> I have configured an Linked Server from SQL Server to Oracle 9i
> database.
> With this link Server, i have create in a SQL Server Database a new
> view who querying a view on this Linked Server (Oracle).
> Also, i have an application that open a connection via ADODB with
> a SQL Server database who have a view who refer to an object on Oracle
> Linked Server. The connection to SQL Server is make via NT
> Authentification.
> When i'm connect as a domain administrator on the computer an i run
> this application and i consult the view, i have no problem.
> But when i'm connect with another user (who is not domain
> administrator) and i run the same application i obtain this error
> "[Microsoft][ODBC SQL ServerDriver][SQL Server]OLE DB error trace
> [Non-interface error: CoCreate of DSO for MSDAORA.1 returned
> 0x80040154]"
> I have search a lot on this problem but with not a lot of
> solutions.
>

---

How is security set up for the linked server?
See sp_addlinkedsrvlogin an explanation of this topic.
David|||Hi !!
I've set Linked server security as "They will be mapped to" with
Oracle username and password (Remote user and Password).
This linked server is set with "Collation Compatible" (for
performance) and "Data Access".
Also, i mapped the same NT user ex. : "Domain\User" with the remote
user and password for Oracle.
Thanks !!
Jocelyn|||"Jocelyn Forest" <forj26@.yahoo.fr> wrote in message
news:ac34b055.0401151052.6281fecf@.posting.google.com...

quote:

---

> Hi !!
> I've set Linked server security as "They will be mapped to" with
> Oracle username and password (Remote user and Password).
> This linked server is set with "Collation Compatible" (for
> performance) and "Data Access".
> Also, i mapped the same NT user ex. : "Domain\User" with the remote
> user and password for Oracle.
> Thanks !!
> Jocelyn

---

It almost sounds like the server process is impersonating your domain user
when it makes the CoCreate call (that's the COM call that creates the Oracle
OleDb driver object).
Try doing this with a SQL login mapped to the same user and see if it makes
any difference.
The thing is this: The Oracle OleDb driver loads the OracleClient, which
needs certian file and registry permissions on the box. If you try to load
the OracleClient while impersonating a user who cannot do this, then you
might get the failure you noticed.
I do not know why the server would be impersonating the domain user when it
accesses the linked server, but it might be trying to pass the windows
identity on to the Oracle server.
David

HELP ! CoCreate of DSO for MSDAORA.1

I have configured an Linked Server from SQL Server to Oracle 9i
database.
With this link Server, i have create in a SQL Server Database a new
view who querying a view on this Linked Server (Oracle).
Also, i have an application that open a connection via ADODB with
a SQL Server database who have a view who refer to an object on Oracle
Linked Server. The connection to SQL Server is make via NT
Authentification.
When i'm connect as a domain administrator on the computer an i run
this application and i consult the view, i have no problem.
But when i'm connect with another user (who is not domain
administrator) and i run the same application i obtain this error
"[Microsoft][ODBC SQL ServerDriver][SQL Server]OLE DB error trace
[Non-interface error: CoCreate of DSO for MSDAORA.1 returned
0x80040154]"
I have search a lot on this problem but with not a lot of
solutions.
Thank !!
Jocelyn"Jocelyn Forest" <forj26@.yahoo.fr> wrote in message
news:ac34b055.0401150610.4efeb024@.posting.google.com...
> I have configured an Linked Server from SQL Server to Oracle 9i
> database.
> With this link Server, i have create in a SQL Server Database a new
> view who querying a view on this Linked Server (Oracle).
> Also, i have an application that open a connection via ADODB with
> a SQL Server database who have a view who refer to an object on Oracle
> Linked Server. The connection to SQL Server is make via NT
> Authentification.
> When i'm connect as a domain administrator on the computer an i run
> this application and i consult the view, i have no problem.
> But when i'm connect with another user (who is not domain
> administrator) and i run the same application i obtain this error
> "[Microsoft][ODBC SQL ServerDriver][SQL Server]OLE DB error trace
> [Non-interface error: CoCreate of DSO for MSDAORA.1 returned
> 0x80040154]"
> I have search a lot on this problem but with not a lot of
> solutions.
>
How is security set up for the linked server?
See sp_addlinkedsrvlogin an explanation of this topic.
David|||Hi !!
I've set Linked server security as "They will be mapped to" with
Oracle username and password (Remote user and Password).
This linked server is set with "Collation Compatible" (for
performance) and "Data Access".
Also, i mapped the same NT user ex. : "Domain\User" with the remote
user and password for Oracle.
Thanks !!
Jocelyn|||"Jocelyn Forest" <forj26@.yahoo.fr> wrote in message
news:ac34b055.0401151052.6281fecf@.posting.google.com...
> Hi !!
> I've set Linked server security as "They will be mapped to" with
> Oracle username and password (Remote user and Password).
> This linked server is set with "Collation Compatible" (for
> performance) and "Data Access".
> Also, i mapped the same NT user ex. : "Domain\User" with the remote
> user and password for Oracle.
> Thanks !!
> Jocelyn
It almost sounds like the server process is impersonating your domain user
when it makes the CoCreate call (that's the COM call that creates the Oracle
OleDb driver object).
Try doing this with a SQL login mapped to the same user and see if it makes
any difference.
The thing is this: The Oracle OleDb driver loads the OracleClient, which
needs certian file and registry permissions on the box. If you try to load
the OracleClient while impersonating a user who cannot do this, then you
might get the failure you noticed.
I do not know why the server would be impersonating the domain user when it
accesses the linked server, but it might be trying to pass the windows
identity on to the Oracle server.
David

Help - syncronizing forms in Access 2000

regular access db - usually use sub-forms or tabs - this time I want seperate forms - have linked fields - but can't final code line in "open form" button command. Any suggestions welcome. Denny :(but can't final code line in "open form" button command

Can you explain this a little better. You might want to bullet point exactly what you want. If you want to just populate two forms on the open, you need to just scroll down on properties of main form until you get to the open propety. You'll want to set the properties of the parent form first, then the second. I have no idea if this is what you are trying to do.

Help - how to execute an sp using linked server

When I run from Main server which has linked server connection - sp executes but the resultset does not get displayed. When I call the same proc from SSRS - I am getting 'an error occured when retrieving parameters for the query. sp does not exist'

Thanks

you will have a tab called 'Command Type' there you change it to Stored Procedure. Then try to execute also make sure you have declared all the parameters before executing this.

Hope it solves your problem.