Hi all,
I am trying to add an Access database as a linked server in SQL Server
2005.
For a "regular" database, everything works fine. BUT (there's a
but...) the database I'm trying to access is a "read-only" database
(i.e.: folder in which db is located is read-only). When I open it
from Access, it warns me that the db cannot be modified, bla bla bla.
I can access the data, but cannot make modifications to the structure
of the database (add queries, change tables, etc.).
So... my question is... Did I forget any parameter in the
sp_addlinkedserver procedure? Anytime I try to use the OpenQuery
method with this database I get the message
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "IVR_post"
returned message "The Microsoft Jet database engine cannot open the
file '\\CompletePathAndDatabaseName.mdb'
. It is already opened exclusively by another user, or you need
permission to view its data.".
However, like I said, the same db can be opened with access 2002 from
the same station, the same user, etc.
exec sp_addlinkedserver @.server='MyServer',
@.srvproduct='Access',
@.provider='Microsoft.Jet.OLEDB.4.0',
@.datasrc='\\CompletePathAndDatabaseName.mdb'
exec sp_addlinkedsrvlogin @.rmtsrvname='MyServer',
@.useself='false',
@.rmtuser='Admin',
@.rmtpassword=''
Regards,Hi,
I have replicated your problem and it works perfectly for me. Are you
sure that you don't have some issues with the Access lock file. If you
shut down all applications/clients/servers accessing this Access file,
does the .ldb file still show up?
Jonathan
fabien.turcotte@.fidomobile.ca wrote:
> Hi all,
> I am trying to add an Access database as a linked server in SQL Server
> 2005.
> For a "regular" database, everything works fine. BUT (there's a
> but...) the database I'm trying to access is a "read-only" database
> (i.e.: folder in which db is located is read-only). When I open it
> from Access, it warns me that the db cannot be modified, bla bla bla.
> I can access the data, but cannot make modifications to the structure
> of the database (add queries, change tables, etc.).
> So... my question is... Did I forget any parameter in the
> sp_addlinkedserver procedure? Anytime I try to use the OpenQuery
> method with this database I get the message
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "IVR_post"
> returned message "The Microsoft Jet database engine cannot open the
> file '\\CompletePathAndDatabaseName.mdb'
> . It is already opened exclusively by another user, or you need
> permission to view its data.".
> However, like I said, the same db can be opened with access 2002 from
> the same station, the same user, etc.
>
> exec sp_addlinkedserver @.server='MyServer',
> @.srvproduct='Access',
> @.provider='Microsoft.Jet.OLEDB.4.0',
> @.datasrc='\\CompletePathAndDatabaseName.mdb'
> exec sp_addlinkedsrvlogin @.rmtsrvname='MyServer',
> @.useself='false',
> @.rmtuser='Admin',
> @.rmtpassword=''
>
> Regards,
>sqlsql
Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts
Sunday, 25 March 2012
Connect To Oracle 64 Bit using SQL Server 2005 64 Bit
I'm using SQL Server 2005 64 Bit and having a problem
when I try to make a linked server to Oracle 10G 64 Bit (installed on the same
server) from management studio. I'm using "Oracle Provider for OLE DB" to
connect to Oracle 64 Bit database because it is the only provider but from what
I've read in Books Online that the provider doesn't support for Oracle 64 Bit.
When I’m using SSAS 2005 and try to connect to Oracle 64
Bit database, an error message appear when I’m using “Microsoft OLE DB Provider
for Oracle”:
Test connection
failed because of an error in initializing provider. Oracle client and
networking components were not found. These components are supplied by Oracle
Corporation and are part of the Oracle Version 7.3.3 or later client software
installation.
Provider is
unable to function until these components are
installed.
And also when I’m using “OracleClient Data Provider”, an
error message appear:
Test connection
failed because of an error in initializing provider. Attempt to load Oracle
client libraries threw BadImageFormatException. This problem will occur when
running in 64 bit mode with the 32 bit Oracle client components
installed.
Can you please tell me how to fix it ? Thanks in
advance.
Have you installed Oracle client? .NET Oracle provider wouldnt work without it.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Try contacting customer support and report your problem.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I remember similar problem came up in the past.
Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.
|||
Hi,
We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.
It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.
Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".
My query is running now.
Best Regards,
Uli
|||Dear All,I have found the solution for linked server to connect to Oracle 64 bit server:
If you want to setup a linked server from SQL 2005 64 bit to Oracle 10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get from Oracle.
Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL PROGRAMS -- Microsoft SQL Server 2005
- Provide the Server Name, Authentication, Username and Password to connect to your
SQL Server at the dialog box. This will authenticate you and log you into
Management Studio.
- Expand the Server Objects folder by clicking on the + sign
- Now, expand the Linked Servers folder and you will see Providers folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and
go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.
- Next, right click on the Linked Servers folder and choose New Linked Server
- In the New Linked Server Properties dialogue box, do the following:
-- Type in a Linked Server Name, for this example, we will call it 'Tiger'
-- Under Server Type, select Other Data Sources - for the provider name, select
Oracle Provider for OLEDB
-- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
-- In the Product Name field, type in "Oracle"
-- Leave Provider String blank.
- Now, click on the "Security option" on the left pane:
-- Select the radio button "Be made using this security context" at the bottom
of the box.
-- Type in the User ID (your Oracle login), ie. Scott in the "Remote login"
field and your Password, ie. Tiger in the "With Password" field.
- Go to the "Server Options" tab in the Linked Server Properties:
-- While leaving the default selections selected, also click on "Collation
Compatible". Select "true" from the drop down button.
-- Click OK. This will create your Linked Server named Tiger.
For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?
Thanks in advance.|||
Thanks!
I have done that and it works!
|||Thanks
The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.
Please help.
sqlsqlConnect To Oracle 64 Bit using SQL Server 2005 64 Bit
I'm using SQL Server 2005 64 Bit and having a problem
when I try to make a linked server to Oracle 10G 64 Bit (installed on the same
server) from management studio. I'm using "Oracle Provider for OLE DB" to
connect to Oracle 64 Bit database because it is the only provider but from what
I've read in Books Online that the provider doesn't support for Oracle 64 Bit.
When I’m using SSAS 2005 and try to connect to Oracle 64
Bit database, an error message appear when I’m using “Microsoft OLE DB Provider
for Oracle”:
Test connection
failed because of an error in initializing provider. Oracle client and
networking components were not found. These components are supplied by Oracle
Corporation and are part of the Oracle Version 7.3.3 or later client software
installation.
Provider is
unable to function until these components are
installed.
And also when I’m using “OracleClient Data Provider”, an
error message appear:
Test connection
failed because of an error in initializing provider. Attempt to load Oracle
client libraries threw BadImageFormatException. This problem will occur when
running in 64 bit mode with the 32 bit Oracle client components
installed.
Can you please tell me how to fix it ? Thanks in
advance.
Have you installed Oracle client? .NET Oracle provider wouldnt work without it.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Try contacting customer support and report your problem.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I remember similar problem came up in the past.
Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.
|||
Hi,
We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.
It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.
Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".
My query is running now.
Best Regards,
Uli
|||Dear All,I have found the solution for linked server to connect to Oracle 64 bit server:
If you want to setup a linked server from SQL 2005 64 bit to Oracle 10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get from Oracle.
Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL PROGRAMS -- Microsoft SQL Server 2005
- Provide the Server Name, Authentication, Username and Password to connect to your
SQL Server at the dialog box. This will authenticate you and log you into
Management Studio.
- Expand the Server Objects folder by clicking on the + sign
- Now, expand the Linked Servers folder and you will see Providers folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and
go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.
- Next, right click on the Linked Servers folder and choose New Linked Server
- In the New Linked Server Properties dialogue box, do the following:
-- Type in a Linked Server Name, for this example, we will call it 'Tiger'
-- Under Server Type, select Other Data Sources - for the provider name, select
Oracle Provider for OLEDB
-- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
-- In the Product Name field, type in "Oracle"
-- Leave Provider String blank.
- Now, click on the "Security option" on the left pane:
-- Select the radio button "Be made using this security context" at the bottom
of the box.
-- Type in the User ID (your Oracle login), ie. Scott in the "Remote login"
field and your Password, ie. Tiger in the "With Password" field.
- Go to the "Server Options" tab in the Linked Server Properties:
-- While leaving the default selections selected, also click on "Collation
Compatible". Select "true" from the drop down button.
-- Click OK. This will create your Linked Server named Tiger.
For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?
Thanks in advance.|||
Thanks!
I have done that and it works!
|||Thanks
The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.
Please help.
Connect To Oracle 64 Bit using SQL Server 2005 64 Bit
I'm using SQL Server 2005 64 Bit and having a problem when I try to make a linked server to Oracle 10G 64 Bit (installed on the same server) from management studio. I'm using "Oracle Provider for OLE DB" to connect to Oracle 64 Bit database because it is the only provider but from what I've read in Books Online that the provider doesn't support for Oracle 64 Bit.
When I’m using SSAS 2005 and try to connect to Oracle 64 Bit database, an error message appear when I’m using “Microsoft OLE DB Provider for Oracle”:
Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.
And also when I’m using “OracleClient Data Provider”, an error message appear:
Test connection failed because of an error in initializing provider. Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.
Can you please tell me how to fix it ? Thanks in advance.
Have you installed Oracle client? .NET Oracle provider wouldnt work without it.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Try contacting customer support and report your problem.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I remember similar problem came up in the past.
Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.
|||
Hi,
We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.
It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.
Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".
My query is running now.
Best Regards,
Uli
|||Dear All,I have found the solution for linked server to connect to Oracle 64 bit server:
If you want to setup a linked server from SQL 2005 64 bit to Oracle 10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get from Oracle.
Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL PROGRAMS -- Microsoft SQL Server 2005
- Provide the Server Name, Authentication, Username and Password to connect to your
SQL Server at the dialog box. This will authenticate you and log you into
Management Studio.
- Expand the Server Objects folder by clicking on the + sign
- Now, expand the Linked Servers folder and you will see Providers folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and
go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.
- Next, right click on the Linked Servers folder and choose New Linked Server
- In the New Linked Server Properties dialogue box, do the following:
-- Type in a Linked Server Name, for this example, we will call it 'Tiger'
-- Under Server Type, select Other Data Sources - for the provider name, select
Oracle Provider for OLEDB
-- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
-- In the Product Name field, type in "Oracle"
-- Leave Provider String blank.
- Now, click on the "Security option" on the left pane:
-- Select the radio button "Be made using this security context" at the bottom
of the box.
-- Type in the User ID (your Oracle login), ie. Scott in the "Remote login"
field and your Password, ie. Tiger in the "With Password" field.
- Go to the "Server Options" tab in the Linked Server Properties:
-- While leaving the default selections selected, also click on "Collation
Compatible". Select "true" from the drop down button.
-- Click OK. This will create your Linked Server named Tiger.
For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?
Thanks in advance.|||
Thanks!
I have done that and it works!
|||Thanks
The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.
Please help.
Connect To Oracle 64 Bit using SQL Server 2005 64 Bit
I'm using SQL Server 2005 64 Bit and having a problem
when I try to make a linked server to Oracle 10G 64 Bit (installed on the same
server) from management studio. I'm using "Oracle Provider for OLE DB" to
connect to Oracle 64 Bit database because it is the only provider but from what
I've read in Books Online that the provider doesn't support for Oracle 64 Bit.
When I’m using SSAS 2005 and try to connect to Oracle 64
Bit database, an error message appear when I’m using “Microsoft OLE DB Provider
for Oracle”:
Test connection
failed because of an error in initializing provider. Oracle client and
networking components were not found. These components are supplied by Oracle
Corporation and are part of the Oracle Version 7.3.3 or later client software
installation.
Provider is
unable to function until these components are
installed.
And also when I’m using “OracleClient Data Provider”, an
error message appear:
Test connection
failed because of an error in initializing provider. Attempt to load Oracle
client libraries threw BadImageFormatException. This problem will occur when
running in 64 bit mode with the 32 bit Oracle client components
installed.
Can you please tell me how to fix it ? Thanks in
advance.
Have you installed Oracle client? .NET Oracle provider wouldnt work without it.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Try contacting customer support and report your problem.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I remember similar problem came up in the past.
Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.
|||
Hi,
We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.
It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.
Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".
My query is running now.
Best Regards,
Uli
|||Dear All,I have found the solution for linked server to connect to Oracle 64 bit server:
If you want to setup a linked server from SQL 2005 64 bit to Oracle 10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get from Oracle.
Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL PROGRAMS -- Microsoft SQL Server 2005
- Provide the Server Name, Authentication, Username and Password to connect to your
SQL Server at the dialog box. This will authenticate you and log you into
Management Studio.
- Expand the Server Objects folder by clicking on the + sign
- Now, expand the Linked Servers folder and you will see Providers folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and
go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.
- Next, right click on the Linked Servers folder and choose New Linked Server
- In the New Linked Server Properties dialogue box, do the following:
-- Type in a Linked Server Name, for this example, we will call it 'Tiger'
-- Under Server Type, select Other Data Sources - for the provider name, select
Oracle Provider for OLEDB
-- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
-- In the Product Name field, type in "Oracle"
-- Leave Provider String blank.
- Now, click on the "Security option" on the left pane:
-- Select the radio button "Be made using this security context" at the bottom
of the box.
-- Type in the User ID (your Oracle login), ie. Scott in the "Remote login"
field and your Password, ie. Tiger in the "With Password" field.
- Go to the "Server Options" tab in the Linked Server Properties:
-- While leaving the default selections selected, also click on "Collation
Compatible". Select "true" from the drop down button.
-- Click OK. This will create your Linked Server named Tiger.
For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?
Thanks in advance.|||
Thanks!
I have done that and it works!
|||Thanks
The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.
Please help.
Thursday, 22 March 2012
Connect to MSAccess on server X from SQL on server Y
I would like to connect to MSAccess 2003 database from a SQL 2005 database.
I tried using Linked Server but it will not work as my MSAccess database is
on a different server.
I am also trying to use OpenDataSource but I am getting an error.
SELECT top 100 *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Server\MyDatabase.mdb";
User ID=Admin;Password=xxxxx'
)...MyTable
Is there a way to do this?
Thanks!You didn't mention what error you are getting so it's hard
to say. But...you mention that a linked server won't work as
the access database is on another server. A linked server
will work in this case and if a linked server won't work due
to this, Opendatasource won't either. Does the service
account that SQL Server is running under have access to the
share on the remote server? Make sure you are using UNC
paths as well (instead of mapped drives).
-Sue
On Thu, 10 Aug 2006 13:16:02 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:
>I would like to connect to MSAccess 2003 database from a SQL 2005 database.
>I tried using Linked Server but it will not work as my MSAccess database is
>on a different server.
>I am also trying to use OpenDataSource but I am getting an error.
>SELECT top 100 *
>FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
>'Data Source="\\Server\MyDatabase.mdb";
>User ID=Admin;Password=xxxxx'
> )...MyTable
>Is there a way to do this?
>Thanks!
I tried using Linked Server but it will not work as my MSAccess database is
on a different server.
I am also trying to use OpenDataSource but I am getting an error.
SELECT top 100 *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Server\MyDatabase.mdb";
User ID=Admin;Password=xxxxx'
)...MyTable
Is there a way to do this?
Thanks!You didn't mention what error you are getting so it's hard
to say. But...you mention that a linked server won't work as
the access database is on another server. A linked server
will work in this case and if a linked server won't work due
to this, Opendatasource won't either. Does the service
account that SQL Server is running under have access to the
share on the remote server? Make sure you are using UNC
paths as well (instead of mapped drives).
-Sue
On Thu, 10 Aug 2006 13:16:02 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:
>I would like to connect to MSAccess 2003 database from a SQL 2005 database.
>I tried using Linked Server but it will not work as my MSAccess database is
>on a different server.
>I am also trying to use OpenDataSource but I am getting an error.
>SELECT top 100 *
>FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
>'Data Source="\\Server\MyDatabase.mdb";
>User ID=Admin;Password=xxxxx'
> )...MyTable
>Is there a way to do this?
>Thanks!
Sunday, 11 March 2012
Connect from a SQL Server 2005 db to a SQL Server 2000 db, without linked server connection
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible?
Look in Books Online, Topic: OpenDatasource()
SELECT *
FROM OPENDATASOURCE( 'SQLOLEDB','Data Source=ServerName;
User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
Tuesday, 14 February 2012
Configuring Linked Server (mysql)
Hi,
I have setup a MYSQL server as a linked server in SQL SERVER 2000. I am able to select from a table using OPENQUERY as I understand that you can't use the 4 part qualifier to access an object from MYSQL as the ODBC driver does not expose the proper interface for this. The problem is that I cannot update,delete or insert into the OPENQUERY() table
eg.
UPDATE OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') SET id = 0
INSERT INTO OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') VALUES(0)
DELETE FROM OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') WHERE id = 0
This is the OLEDB PROVIDER FOR ODBC error I am getting...
The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].
I know there are brilliant Professionals on this forum. Please tell me that this is just an MDAC upgrade or something that is not configured correctly...Linked server query must be supported and contained a pass-thru query (OpenQuery, or OpenRowset with query a inside it).
SELECT/INSERT/UPDATE/DELETE statement against a 4-part name for which SQL Server generates a query against the provider for optimization.
And see whether this KBA http://support.microsoft.com/default.aspx?scid=KB;en-us;q270119 is any help.|||Thanks for the article reference. I had a look at it and it is definitely a different error that is explained in it. As I said above, you can't use 4 part names to access objects in mysql as the odbc driver does not allow it. Does anyone at all know why this error would be causes as MS does not document the reasons for it. Remember, I can select from the openquery but not update,insert,delete...
I have setup a MYSQL server as a linked server in SQL SERVER 2000. I am able to select from a table using OPENQUERY as I understand that you can't use the 4 part qualifier to access an object from MYSQL as the ODBC driver does not expose the proper interface for this. The problem is that I cannot update,delete or insert into the OPENQUERY() table
eg.
UPDATE OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') SET id = 0
INSERT INTO OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') VALUES(0)
DELETE FROM OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') WHERE id = 0
This is the OLEDB PROVIDER FOR ODBC error I am getting...
The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].
I know there are brilliant Professionals on this forum. Please tell me that this is just an MDAC upgrade or something that is not configured correctly...Linked server query must be supported and contained a pass-thru query (OpenQuery, or OpenRowset with query a inside it).
SELECT/INSERT/UPDATE/DELETE statement against a 4-part name for which SQL Server generates a query against the provider for optimization.
And see whether this KBA http://support.microsoft.com/default.aspx?scid=KB;en-us;q270119 is any help.|||Thanks for the article reference. I had a look at it and it is definitely a different error that is explained in it. As I said above, you can't use 4 part names to access objects in mysql as the odbc driver does not allow it. Does anyone at all know why this error would be causes as MS does not document the reasons for it. Remember, I can select from the openquery but not update,insert,delete...
Sunday, 12 February 2012
Configuring Active Directory as a Linked Server.
Hi, in a server running sql server 2000 sp3, as a new requirement for an
application. They need to have the active Directory configured as a
Linked Server.
Do somebody know the pros and cons of configuring this.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***Hi,
its their SQL Server ,SQL server can be integrated with Active Directory ,
please read *Active Directory* in BOL.
here are some articles FYI :
msdn.microsoft.com/library/en-us/architec/8_ar_cs_1gku.asp
msdn.microsoft.com/.../adsi/adsi/ creating_a_heterogeneous_join_between_sq
l_s
erver_and_active_directory.asp
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"MariaGuzman" wrote:
> Hi, in a server running sql server 2000 sp3, as a new requirement for an
> application. They need to have the active Directory configured as a
> Linked Server.
> Do somebody know the pros and cons of configuring this.
> Thanks a lot for your help.
> *** Sent via Developersdex http://www.codecomments.com ***
>
application. They need to have the active Directory configured as a
Linked Server.
Do somebody know the pros and cons of configuring this.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***Hi,
its their SQL Server ,SQL server can be integrated with Active Directory ,
please read *Active Directory* in BOL.
here are some articles FYI :
msdn.microsoft.com/library/en-us/architec/8_ar_cs_1gku.asp
msdn.microsoft.com/.../adsi/adsi/ creating_a_heterogeneous_join_between_sq
l_s
erver_and_active_directory.asp
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"MariaGuzman" wrote:
> Hi, in a server running sql server 2000 sp3, as a new requirement for an
> application. They need to have the active Directory configured as a
> Linked Server.
> Do somebody know the pros and cons of configuring this.
> Thanks a lot for your help.
> *** Sent via Developersdex http://www.codecomments.com ***
>
Subscribe to:
Posts (Atom)