Linked Table Names

When linking tables from SQL 2K to Access 2K using ODBC, I
get the owner (usually dbo) as a qualifier. It appears
that this just started happening. If I do not disturb the
old link without a qualifir, I am usually OK, but if I
release a link and relink, the dbo_ comes in, and my
queries do not recognize the table. Can I get rid of the
qualifier, and why would this have started to occur?
Thanks.If you link the tables programmatically, you can set the
name of the linked table. You can find sample code in the
following article:
Relink ODBC tables from code
http://www.mvps.org/access/tables/tbl0010.htm
-Sue
On Thu, 5 Aug 2004 11:25:07 -0700, "W D Greene"
<anonymous@.discussions.microsoft.com> wrote:

>When linking tables from SQL 2K to Access 2K using ODBC, I
>get the owner (usually dbo) as a qualifier. It appears
>that this just started happening. If I do not disturb the
>old link without a qualifir, I am usually OK, but if I
>release a link and relink, the dbo_ comes in, and my
>queries do not recognize the table. Can I get rid of the
>qualifier, and why would this have started to occur?
>Thanks.

Linked Table Names

When linking tables from SQL 2K to Access 2K using ODBC, I
get the owner (usually dbo) as a qualifier. It appears
that this just started happening. If I do not disturb the
old link without a qualifir, I am usually OK, but if I
release a link and relink, the dbo_ comes in, and my
queries do not recognize the table. Can I get rid of the
qualifier, and why would this have started to occur?
Thanks.
If you link the tables programmatically, you can set the
name of the linked table. You can find sample code in the
following article:
Relink ODBC tables from code
http://www.mvps.org/access/tables/tbl0010.htm
-Sue
On Thu, 5 Aug 2004 11:25:07 -0700, "W D Greene"
<anonymous@.discussions.microsoft.com> wrote:

>When linking tables from SQL 2K to Access 2K using ODBC, I
>get the owner (usually dbo) as a qualifier. It appears
>that this just started happening. If I do not disturb the
>old link without a qualifir, I am usually OK, but if I
>release a link and relink, the dbo_ comes in, and my
>queries do not recognize the table. Can I get rid of the
>qualifier, and why would this have started to occur?
>Thanks.
sql

Linked table in Access shows wrong data

I am attempting to link two tables from SQL server to an access application. Both contain the same column names and the same fields as concatenated primary key but the tables are named differently, have different values and used differently. When these ta
bles are linked in access, one of the tables changes to a single primary key. When this table is opened, you see many duplicates (of what was the concatenated primary key) but the count of records is still equal to SQL Server. When I query in Access for d
uplicates, no results are returned. I drop the table and re-link it, but it still shows a single key. How can I get Access to accept the true table schema?
"bbitzer" <anonymous@.discussions.microsoft.com> wrote in message
news:0219C2D0-5ADB-48E7-86D0-EAC93D9D5422@.microsoft.com...
> I am attempting to link two tables from SQL server to an access
application. Both contain the same column names and the same fields as
concatenated primary key but the tables are named differently, have
different values and used differently. When these tables are linked in
access, one of the tables changes to a single primary key. When this table
is opened, you see many duplicates (of what was the concatenated primary
key) but the count of records is still equal to SQL Server. When I query in
Access for duplicates, no results are returned. I drop the table and re-link
it, but it still shows a single key. How can I get Access to accept the true
table schema?<
On the surface it sounds like a bug... make sure you update your client with
the latest MDAC and Jet updates. Also, on the tables you mention on SQL
Server, be sure that you have added an additional column of data type
timestamp.
Steve

servers Issues

I have a NAMES server and APPLICATION sever. I created a linked server
reference on APPPLICATION to point to NAMES. I did map the APPLICATION
user/password to that on NAMES
Issue : when I click on TABLES in the link server tree all I can see is
tables in the master database. The user/password I pass to the linked server
has rights to multiple databases on NAMES. The user in NAMES default databas
e
is master, but I would think I could see all db on NAMES that the user I
passed had access to.
The .NET query designer just tells me that the login failed when I try to
save the SP or on some occasions that it failed to start a distributed
transaction b/c login failed.
Ive tried reading up on linked servers on MS website and there seem to be so
many retractions and configurations that need to be made Im starting to get
. I thought this was going to be easy or am i misunderstanding. Also
the MSDN page for linked servers seems to imply that you can only do select
and updates from a linked server and not inserts. Is this true? Our NAMES
database is used by multiple applications and multiple databases. Because of
performance I realize that NAMES database would work better on its own serve
r.
JP
.NET Software DevelperJP,
Change the default database for the linkedserver login on the linked server
to database on which you'd like to see the tables listed. You can use EM or
sp_defaultdb.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:22FE02D9-8576-43E5-9EA7-AEB55C5E5704@.microsoft.com...
>I have a NAMES server and APPLICATION sever. I created a linked server
> reference on APPPLICATION to point to NAMES. I did map the APPLICATION
> user/password to that on NAMES
> Issue : when I click on TABLES in the link server tree all I can see is
> tables in the master database. The user/password I pass to the linked
> server
> has rights to multiple databases on NAMES. The user in NAMES default
> database
> is master, but I would think I could see all db on NAMES that the user I
> passed had access to.
>
> The .NET query designer just tells me that the login failed when I try to
> save the SP or on some occasions that it failed to start a distributed
> transaction b/c login failed.
> Ive tried reading up on linked servers on MS website and there seem to be
> so
> many retractions and configurations that need to be made Im starting to
> get
> . I thought this was going to be easy or am i misunderstanding.
> Also
> the MSDN page for linked servers seems to imply that you can only do
> select
> and updates from a linked server and not inserts. Is this true? Our NAMES
> database is used by multiple applications and multiple databases. Because
> of
> performance I realize that NAMES database would work better on its own
> server.
>
> --
> JP
> .NET Software Develper|||Is there no way to see multiple databases on the linked server? In addtion
can I also see views and tables and not SPs?
JP
.NET Software Develper
"Jerry Spivey" wrote:

> JP,
> Change the default database for the linkedserver login on the linked serve
r
> to database on which you'd like to see the tables listed. You can use EM
or
> sp_defaultdb.
> HTH
> Jerry
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:22FE02D9-8576-43E5-9EA7-AEB55C5E5704@.microsoft.com...
>
>|||JP,
The treeview in EM shows the tables and views for the default database of
the LS login on LS. EM is not customizable but you could use .NET and
SQL-DMO to create your own SQL admin/treeview application. Optionally, if
you're just trying to view the objects on the LS and the LS is a SQL Server,
why not just register it in EM. Outside of that you'd have to run T-SQL to
get what you're looking for qualifying the database in the OPENQUERY
statement or the 4-part name. i.e., you could use sp_stored_procedures or
some custom script to show the procs.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:3DF957D0-E428-48F2-ADC8-B756B8156E8C@.microsoft.com...
> Is there no way to see multiple databases on the linked server? In addtion
> can I also see views and tables and not SPs?
> --
> JP
> .NET Software Develper
>
> "Jerry Spivey" wrote:
>

server using default login credentials

I have to access data on a separate server, so I created a linked
server using this syntax (some names changed to be generic);
EXEC sp_addlinkedserver
@.server = N'LinkedServerName',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'MyServerName',
@.provstr = 'DRIVER={SQL
Server};SERVER=N'MyServerName';UID=MyAppUser;PWD=M yAppPW;',
@.catalog = N'MyTable'
This link is created, but when I try to do a simple select statement
on 'MyTable', it returns the error:
Login failed for user 'Domain\KirkH'
Therefore, it is trying to connect using my login credentials, INSTEAD
of the ones I specified when I created the linked server. Am I using
this incorrectly, or is my syntax wrong?
I would greatly appreciate any suggestions or comments.
Thank you!.
On Apr 16, 3:47Xpm, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Kirk" <lok...@.hotmail.com> wrote in message
> news:53688ba6-ce89-4f28-a02a-c486e8253a24@.2g2000hsn.googlegroups.com...
>
>
>
>
>
> Hi
> I would have expected your parameter to be
> @.provstr = 'DRIVER={SQL
> Server};SERVER=MyServerName;UID=MyAppUser;PWD=MyAp pPW;'
> i.e N'MyServerName' is not correct!
> Have you tried sp_addlinkedsrvlogin e.g.
> EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', NULL, 'MyAppUser',
> 'MyAppPW'
> John- Hide quoted text -
> - Show quoted text -
John,
You were correct - once I added the login using sp_addlinkedsrvlogin,
everything worked great.
Thank you very much for your help!

server using default login credentials

I have to access data on a separate server, so I created a linked
server using this syntax (some names changed to be generic);
EXEC sp_addlinkedserver
@.server = N'LinkedServerName',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'MyServerName',
@.provstr = 'DRIVER={SQL
Server};SERVER=N'MyServerName';UID=MyAppUser;PWD=MyAppPW;',
@.catalog = N'MyTable'
This link is created, but when I try to do a simple select statement
on 'MyTable', it returns the error:
Login failed for user 'Domain\KirkH'
Therefore, it is trying to connect using my login credentials, INSTEAD
of the ones I specified when I created the linked server. Am I using
this incorrectly, or is my syntax wrong?
I would greatly appreciate any suggestions or comments.
Thank you!."Kirk" <loki70@.hotmail.com> wrote in message
news:53688ba6-ce89-4f28-a02a-c486e8253a24@.2g2000hsn.googlegroups.com...
>I have to access data on a separate server, so I created a linked
> server using this syntax (some names changed to be generic);
> EXEC sp_addlinkedserver
> @.server = N'LinkedServerName',
> @.srvproduct = N' ',
> @.provider = N'SQLOLEDB',
> @.datasrc = N'MyServerName',
> @.provstr = 'DRIVER={SQL
> Server};SERVER=N'MyServerName';UID=MyAppUser;PWD=MyAppPW;',
> @.catalog = N'MyTable'
> This link is created, but when I try to do a simple select statement
> on 'MyTable', it returns the error:
> Login failed for user 'Domain\KirkH'
> Therefore, it is trying to connect using my login credentials, INSTEAD
> of the ones I specified when I created the linked server. Am I using
> this incorrectly, or is my syntax wrong?
> I would greatly appreciate any suggestions or comments.
> Thank you!.
Hi
I would have expected your parameter to be
@.provstr = 'DRIVER={SQL
Server};SERVER=MyServerName;UID=MyAppUser;PWD=MyAppPW;'
i.e N'MyServerName' is not correct!
Have you tried sp_addlinkedsrvlogin e.g.
EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', NULL, 'MyAppUser',
'MyAppPW'
John|||On Apr 16, 3:47=A0pm, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Kirk" <lok...@.hotmail.com> wrote in message
> news:53688ba6-ce89-4f28-a02a-c486e8253a24@.2g2000hsn.googlegroups.com...
>
>
> >I have to access data on a separate server, so I created a linked
> > server using this syntax (some names changed to be generic);
> > EXEC sp_addlinkedserver
> > @.server =3D N'LinkedServerName',
> > @.srvproduct =3D N' ',
> > @.provider =3D N'SQLOLEDB',
> > @.datasrc =3D N'MyServerName',
> > @.provstr =3D 'DRIVER=3D{SQL
> > Server};SERVER=3DN'MyServerName';UID=3DMyAppUser;PWD=3DMyAppPW;',
> > @.catalog =3D N'MyTable'
> > This link is created, but when I try to do a simple select statement
> > on 'MyTable', it returns the error:
> > =A0 Login failed for user 'Domain\KirkH'
> > Therefore, it is trying to connect using my login credentials, INSTEAD
> > of the ones I specified when I created the linked server. =A0Am I using
> > this incorrectly, or is my syntax wrong?
> > I would greatly appreciate any suggestions or comments.
> > Thank you!.
> Hi
> I would have expected your parameter to be
> @.provstr =3D 'DRIVER=3D{SQL
> Server};SERVER=3DMyServerName;UID=3DMyAppUser;PWD=3DMyAppPW;'
> i.e N'MyServerName' is not correct!
> Have you tried sp_addlinkedsrvlogin e.g.
> EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', NULL, 'MyAppUser',
> 'MyAppPW'
> John- Hide quoted text -
> - Show quoted text -
John,
You were correct - once I added the login using sp_addlinkedsrvlogin,
everything worked great.
Thank you very much for your help!