Friday, March 30, 2012

linked server query

Hi,
I have added a linked server. Now , how can I query the tables and also I
only see one of the few databases only on the linked server. How can I see
all the databases of linked server.
Thanks,Via the linked server you will see your 'default database' objects on that
target database. Although you cannot graphically browse the multiple
databases that you have access to on the target linked server, you will be
able to access those other databases and tables via querying.
When querying the databases on the linked server, make sure to set your ANSI
settings and issue the queries using the 4-part object context:
ex:
USE ANSI_NULLS ON
GO
USE ANSI_WARNINGS ON
GO
SELECT name FROM <LINKEDSERVERNAME>.Northwind.dbo.Customers
Replace <LINKEDSERVERNAME> with the name of the linked server
"dotnettester" wrote:

> Hi,
> I have added a linked server. Now , how can I query the tables and also I
> only see one of the few databases only on the linked server. How can I see
> all the databases of linked server.
> Thanks,
>|||Hi,
Before u query to linked server table see in linked server property
that rpc option are checked.
use the select * from [linked
servername].databasename.username.tablename
hope this help
from
killer

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Help? I am an Oracle DBA struggling to support SQL Server.
Using EM I added another SQL Server database to Linked Servers with Windows
authentication. In Query Analyzer I attempt a simple SQL query and receive...
Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
I read thru BOL and Robert Vieira's book to setup the distributed query, and
I believe I am doing everything correctly. Scanning Google and MSKB, it
seems I hit a bug with SQL Server, but this is so simple I have a hard time
believing that.
Is my only recourse to call support? Is there anything I can check?
Chris
It depends...the problem can be caused by a ton of
different things which is why it's often recommended to open
up a case with support.
What version of SQL Server? Are you on the latest service
packs?
Does this occur with any server and any database or just one
specific server and one database?
Make sure the servers, databases involved are running
correctly - check both servers server names with
@.@.Servername. Make sure the database you are accessing does
not have any consistency problems - run a dbcc on the
database you are accessing.
You may also want to check your mdac installation on the
servers. You can use the mdac component checker to do this.
It's available from:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Fri, 12 Nov 2004 09:54:03 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:

>Help? I am an Oracle DBA struggling to support SQL Server.
>Using EM I added another SQL Server database to Linked Servers with Windows
>authentication. In Query Analyzer I attempt a simple SQL query and receive...
>Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
>I read thru BOL and Robert Vieira's book to setup the distributed query, and
>I believe I am doing everything correctly. Scanning Google and MSKB, it
>seems I hit a bug with SQL Server, but this is so simple I have a hard time
>believing that.
>Is my only recourse to call support? Is there anything I can check?

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Help? I am an Oracle DBA struggling to support SQL Server.
Using EM I added another SQL Server database to Linked Servers with Windows
authentication. In Query Analyzer I attempt a simple SQL query and receive...
Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
I read thru BOL and Robert Vieira's book to setup the distributed query, and
I believe I am doing everything correctly. Scanning Google and MSKB, it
seems I hit a bug with SQL Server, but this is so simple I have a hard time
believing that.
Is my only recourse to call support' Is there anything I can check'
--
ChrisIt depends...the problem can be caused by a ton of
different things which is why it's often recommended to open
up a case with support.
What version of SQL Server? Are you on the latest service
packs?
Does this occur with any server and any database or just one
specific server and one database?
Make sure the servers, databases involved are running
correctly - check both servers server names with
@.@.Servername. Make sure the database you are accessing does
not have any consistency problems - run a dbcc on the
database you are accessing.
You may also want to check your mdac installation on the
servers. You can use the mdac component checker to do this.
It's available from:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Fri, 12 Nov 2004 09:54:03 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:
>Help? I am an Oracle DBA struggling to support SQL Server.
>Using EM I added another SQL Server database to Linked Servers with Windows
>authentication. In Query Analyzer I attempt a simple SQL query and receive...
>Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
>I read thru BOL and Robert Vieira's book to setup the distributed query, and
>I believe I am doing everything correctly. Scanning Google and MSKB, it
>seems I hit a bug with SQL Server, but this is so simple I have a hard time
>believing that.
>Is my only recourse to call support' Is there anything I can check'|||Duh. I should have posted that.
We are currently running SQL Server 2000 with SP3a in Windows 2000 w SP4.
This occurs when I try to query tables on another instance on the same server.
We have a maintenance plan that performs the dbcc checks on a regular basis.
So no problem there.
Basically, I need to be able to compare the tables in the current (dev or
test) databases with a "baseline" database. So I decided to try the
distributed query from the baseline database against the dev database and it
works. But it fails the other way Or when I try to compare dev database
against test database it fails. So for now I'm ok. Later, I will
troubleshoot why I cant go from dev or test database to the baseline database.
Thanks Sue!!
"Sue Hoegemeier" wrote:
> It depends...the problem can be caused by a ton of
> different things which is why it's often recommended to open
> up a case with support.
> What version of SQL Server? Are you on the latest service
> packs?
> Does this occur with any server and any database or just one
> specific server and one database?
> Make sure the servers, databases involved are running
> correctly - check both servers server names with
> @.@.Servername. Make sure the database you are accessing does
> not have any consistency problems - run a dbcc on the
> database you are accessing.
> You may also want to check your mdac installation on the
> servers. You can use the mdac component checker to do this.
> It's available from:
> http://msdn.microsoft.com/data/mdac/default.aspx
> -Sue
> On Fri, 12 Nov 2004 09:54:03 -0800, "Chris"
> <Chris@.discussions.microsoft.com> wrote:
> >Help? I am an Oracle DBA struggling to support SQL Server.
> >
> >Using EM I added another SQL Server database to Linked Servers with Windows
> >authentication. In Query Analyzer I attempt a simple SQL query and receive...
> >
> >Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
> >
> >I read thru BOL and Robert Vieira's book to setup the distributed query, and
> >I believe I am doing everything correctly. Scanning Google and MSKB, it
> >seems I hit a bug with SQL Server, but this is so simple I have a hard time
> >believing that.
> >
> >Is my only recourse to call support' Is there anything I can check'
>

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Help? I am an Oracle DBA struggling to support SQL Server.
Using EM I added another SQL Server database to Linked Servers with Windows
authentication. In Query Analyzer I attempt a simple SQL query and receive.
.
Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
I read thru BOL and Robert Vieira's book to setup the distributed query, and
I believe I am doing everything correctly. Scanning Google and MSKB, it
seems I hit a bug with SQL Server, but this is so simple I have a hard time
believing that.
Is my only recourse to call support' Is there anything I can check'
ChrisIt depends...the problem can be caused by a ton of
different things which is why it's often recommended to open
up a case with support.
What version of SQL Server? Are you on the latest service
packs?
Does this occur with any server and any database or just one
specific server and one database?
Make sure the servers, databases involved are running
correctly - check both servers server names with
@.@.Servername. Make sure the database you are accessing does
not have any consistency problems - run a dbcc on the
database you are accessing.
You may also want to check your mdac installation on the
servers. You can use the mdac component checker to do this.
It's available from:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Fri, 12 Nov 2004 09:54:03 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:

>Help? I am an Oracle DBA struggling to support SQL Server.
>Using EM I added another SQL Server database to Linked Servers with Windows
>authentication. In Query Analyzer I attempt a simple SQL query and receive
..
>Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
>I read thru BOL and Robert Vieira's book to setup the distributed query, an
d
>I believe I am doing everything correctly. Scanning Google and MSKB, it
>seems I hit a bug with SQL Server, but this is so simple I have a hard time
>believing that.
>Is my only recourse to call support' Is there anything I can check'

Linked Server Provider

Hi All
I am trying to create a linked server to MYSQL and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:
>Hi All
>I am trying to create a linked server to MYSQL and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- Davidsql

Linked Server Provider

Hi All
I am trying to create a linked server to mysql and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:

>Hi All
>I am trying to create a linked server to mysql and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- David

Linked Server Properties - Security - SQL Server 2000

I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group ar
e
connected. I have been forced to specify each group memebr individually. I
s
there a way to make the domain groupt option work?I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>|||The login connecting to my sqlserver has no credentials on the remote server
.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with sa
y
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was lookin
g
for a better option.
"Russell Fields" wrote:

> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server o
r
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of t
he
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>
>