Having trouble Creating Linked Server to Access database

I been having an issue trying to connect an Access database through SQL
Server as a Linked Server.
The crux of the problem is the Access Database (mdb file) has a Database
Password and this cannot be removed due to the database still being used by a
VB application. SO can someone Assist in a detailed outline that works, for
configuring a Linked Server connecting to a mdb with a Database Password set.
If the password is temperarily removed from the database I am able to create
a Linked Server using the "MS Jet 4.0 OLD DB Provider" However to complete
my objective I cannot remove the Database Password on our clients DB.
When the password is put back in, then once again I am unable to access the
DB. I have tried different configuration options and recieved different
errors. for instance on one try SQL Server was erroring and saying it needed
a mdw file that we do not have becaue that is for User Level security which
out MDB is not using.
I tried using a OpenROWSET command and providing a Provider Connection
String setting the Database Password in the String, However this Also errors
with a Could not find Installable ISAM.
Thanks
Tony
Hi Tony,
I understand that you would like to know how to open your
password-protected access database (.mdb) in SQL Server via linked server
or OPENROWSET/OPENDATASOURCE.
If I have misunderstood, please let me know.
I reproduced your issue at my side. It seems not possible to use linked
server or OPENROWSET to connect to a password-protected mdb file. However
per my test, you can use OPENDATASOURCE to query your MDB file with
specifying the parameter "Jet OLEDB:Database Password=******". For example:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="C:\Documents and Settings\charles\My Documents\db1.mdb";User
ID=Admin;Password=;Jet OLEDB:Database Password=myPassword!')...tblDT;
I am not sure why the parameter does not work for OPENROWSET and linked
server and I will try to consult the product team regarding the issue. If
there is any response, I will let you know. Anyway as a temporary
workaround, I recommend that you use OPENDATASOURCE to see if it helps.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Having trouble Creating Linked Server to Access database

I been having an issue trying to connect an Access database through SQL
Server as a Linked Server.
The crux of the problem is the Access Database (mdb file) has a Database
Password and this cannot be removed due to the database still being used by
a
VB application. SO can someone Assist in a detailed outline that works, fo
r
configuring a Linked Server connecting to a mdb with a Database Password set
.
If the password is temperarily removed from the database I am able to create
a Linked Server using the "MS Jet 4.0 OLD DB Provider" However to complete
my objective I cannot remove the Database Password on our clients DB.
When the password is put back in, then once again I am unable to access the
DB. I have tried different configuration options and recieved different
errors. for instance on one try SQL Server was erroring and saying it neede
d
a mdw file that we do not have becaue that is for User Level security which
out MDB is not using.
I tried using a OpenROWSET command and providing a Provider Connection
String setting the Database Password in the String, However this Also errors
with a Could not find Installable ISAM.
Thanks
TonyHi Tony,
I understand that you would like to know how to open your
password-protected access database (.mdb) in SQL Server via linked server
or OPENROWSET/OPENDATASOURCE.
If I have misunderstood, please let me know.
I reproduced your issue at my side. It seems not possible to use linked
server or OPENROWSET to connect to a password-protected mdb file. However
per my test, you can use OPENDATASOURCE to query your MDB file with
specifying the parameter "Jet OLEDB:Database Password=******". For example:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="C:\Documents and Settings\charles\My Documents\db1.mdb";User
ID=Admin;Password=;Jet OLEDB:Database Password=myPassword!')...tblDT;
I am not sure why the parameter does not work for OPENROWSET and linked
server and I will try to consult the product team regarding the issue. If
there is any response, I will let you know. Anyway as a temporary
workaround, I recommend that you use OPENDATASOURCE to see if it helps.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Tony,
Just notify that I have not got the product team's confirmation now. I will
try to follow up them. Since the process may need a long time, could you
please just leave me (changliw_at_microsoft_dot_com) an email response so
that I can timely update you when I got the confirmation?
Also could you please let me know whether or not the OPENDATASOURCE method
worked for you?
If you have any questions or concerns, please feel free to let me know. It
is my pleasure to be of your assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

Having Clause wont run on Linked Server

I am trying to find all affiliates that have more commissions from this
week to the prior week. The problem is in the having part where
"aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
trying to use "aff.affiliateid" to refer to the parent table.

The error I get is "Invalid column name 'Col1010'.", but ONLY on the
sql01 box; it runs correctly on the box where the affiliate database
is, sql02.

If I get rid of the alias of "aff" and use the full path, I get the
error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
more than the maximum number of prefixes. The maximum is 3."

Is this just a SQL bug, or is there a fix?

declare @.date datetime ; set @.date = '2/3/05'

select affiliateid
from sql02.affiliates.dbo.affiliates_sum aff
where day >= @.date - 7 and day < @.date
group by affiliateid
having sum(lead_commissions) > (
select sum(lead_commissions + sales_commissions) as total
from sql02.affiliates.dbo.affiliates_sum aff2
where day >= @.date - 14 and day < @.date - 7
and aff2.affiliateid = aff.affiliateid
group by affiliateid
)<scottelkin@.gmail.com> wrote in message
news:1108711888.673744.271790@.c13g2000cwb.googlegr oups.com...
>I am trying to find all affiliates that have more commissions from this
> week to the prior week. The problem is in the having part where
> "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
> trying to use "aff.affiliateid" to refer to the parent table.
> The error I get is "Invalid column name 'Col1010'.", but ONLY on the
> sql01 box; it runs correctly on the box where the affiliate database
> is, sql02.
> If I get rid of the alias of "aff" and use the full path, I get the
> error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
> more than the maximum number of prefixes. The maximum is 3."
> Is this just a SQL bug, or is there a fix?
>
> declare @.date datetime ; set @.date = '2/3/05'
> select affiliateid
> from sql02.affiliates.dbo.affiliates_sum aff
> where day >= @.date - 7 and day < @.date
> group by affiliateid
> having sum(lead_commissions) > (
> select sum(lead_commissions + sales_commissions) as total
> from sql02.affiliates.dbo.affiliates_sum aff2
> where day >= @.date - 14 and day < @.date - 7
> and aff2.affiliateid = aff.affiliateid
> group by affiliateid
> )

This KB article might be relevant:

http://support.microsoft.com/defaul...kb;en-us;825019

If that doesn't help, or if you need a workaround, you could try using
OPENQUERY() to pass the query through to sql02, although since you need to
include a parameter value, you would need dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

Simon|||(scottelkin@.gmail.com) writes:
> I am trying to find all affiliates that have more commissions from this
> week to the prior week. The problem is in the having part where
> "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
> trying to use "aff.affiliateid" to refer to the parent table.
> The error I get is "Invalid column name 'Col1010'.", but ONLY on the
> sql01 box; it runs correctly on the box where the affiliate database
> is, sql02.
> If I get rid of the alias of "aff" and use the full path, I get the
> error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
> more than the maximum number of prefixes. The maximum is 3."
> Is this just a SQL bug, or is there a fix?

Judging from the error message, this is a bug in either SQL Server
or SQLOLEDB.

I created a table from the query and then ran the query on my box
with a loopback linked server, and the query completed succesfully.
I am running the beta of SP4, so it could be because the issue have
been fixed. However, the problem may be related to a specific query
plan, so it is difficult to tell for sure.

Info about SP4 Beta is here: http://support.microsoft.com/kb/290211.

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

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