When I execute the SQL Statement in the SQL Query Analyzer, I get the result
s
returned in 4 seconds but when I use the same query from a stored procedure
and execute it from SQL Analyzer, it is taking too long about 50-55 seconds.
Am I missing something? I have a join between external and internal table.
Thanks!!
Vibs
SELECT
AAPSUser.[ID]
, AAPSUser.First_Name
, AAPSUser.Last_Name
, AAPSUser.Second_Sections,
[UserExtraInfo].[title],
[UserExtraInfo].[profile1_text],
[UserExtraInfo].[profile2_text],
[UserExtraInfo].[profile3_text]
FROM
AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
WHERE
AAPSUser.[ID] = 003479Are you running the query and the proc containg the query locally i.e, same
SQL Server? (Same security creditials as well?) I.e, log into QA and run
the query then directly below (same window) exectue the proc (same query in
proc right?). You can also compare the execution plans as well for any
visable differences.
HTH
Jerry
"vibs" <vibs@.discussions.microsoft.com> wrote in message
news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@.microsoft.com...
> When I execute the SQL Statement in the SQL Query Analyzer, I get the
> results
> returned in 4 seconds but when I use the same query from a stored
> procedure
> and execute it from SQL Analyzer, it is taking too long about 50-55
> seconds.
> Am I missing something? I have a join between external and internal table.
> Thanks!!
> Vibs
> SELECT
> AAPSUser.[ID]
> , AAPSUser.First_Name
> , AAPSUser.Last_Name
> , AAPSUser.Second_Sections,
> [UserExtraInfo].[title],
> [UserExtraInfo].[profile1_text],
> [UserExtraInfo].[profile2_text],
> [UserExtraInfo].[profile3_text]
> FROM
> AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
> INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
> WHERE
> AAPSUser.[ID] = 003479|||Yes the query and the proc is running from the same query analyzer window on
the same SQL Server. I'll look into the execution plans
Thanks
"Jerry Spivey" wrote:
> Are you running the query and the proc containg the query locally i.e, sam
e
> SQL Server? (Same security creditials as well?) I.e, log into QA and run
> the query then directly below (same window) exectue the proc (same query i
n
> proc right?). You can also compare the execution plans as well for any
> visable differences.
> HTH
> Jerry
> "vibs" <vibs@.discussions.microsoft.com> wrote in message
> news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@.microsoft.com...
>
>
Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts
Friday, March 30, 2012
Linked Server query
I have set up a link server to the AS400and I can see the tables in the Enterprise Manager, How do I query it so I can use the data in my SQL Server database.
*****************************************
* This message was posted via http://sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://sqlmonster.com/Uwe/Abuse.aspx...b79e73dd024921
*****************************************
Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQLMonster.c om...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> *****************************************
> * This message was posted via http://sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://sqlmonster.com/Uwe/Abuse.aspx...b79e73dd024921
> *****************************************
sql
*****************************************
* This message was posted via http://sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://sqlmonster.com/Uwe/Abuse.aspx...b79e73dd024921
*****************************************
Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQLMonster.c om...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> *****************************************
> * This message was posted via http://sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://sqlmonster.com/Uwe/Abuse.aspx...b79e73dd024921
> *****************************************
sql
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
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 query
can anybody please tell me how to query a table from a linked server?
thanks
Sure, thats quite easy, use the four part notation:
SELECT <columnlist> FROM LinkedServerName.DatabaseName.Owner.Objectname
HTH, Jens Suessmeyer.
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
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 query
I have set up a link server to the AS400and I can see the tables in the Enterprise Manager, How do I query it so I can use the data in my SQL Server database.
*****************************************
* This message was posted via http://sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://sqlmonster.com/Uwe/Abuse.aspx?aid=66558ca34dd7468082b79e73dd024921
*****************************************Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQLMonster.com...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> *****************************************
> * This message was posted via http://sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://sqlmonster.com/Uwe/Abuse.aspx?aid=66558ca34dd7468082b79e73dd024921
> *****************************************
*****************************************
* This message was posted via http://sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://sqlmonster.com/Uwe/Abuse.aspx?aid=66558ca34dd7468082b79e73dd024921
*****************************************Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQLMonster.com...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> *****************************************
> * This message was posted via http://sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://sqlmonster.com/Uwe/Abuse.aspx?aid=66558ca34dd7468082b79e73dd024921
> *****************************************
Linked Server query
I have set up a link server to the AS400and I can see the tables in the Ent
erprise Manager, How do I query it so I can use the data in my SQL Server da
tabase.
****************************************
*
* This message was posted via http://droptable.com
*
* Report spam or abuse by clicking the following URL:
* http://droptable.com/Uwe/Abuse.asp...2b79e73dd024921
****************************************
*Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via droptable.com" <forum@.droptable.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQ
droptable.com...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> ****************************************
*
> * This message was posted via http://droptable.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://droptable.com/Uwe/Abuse.asp...2b79e73dd024921
> ****************************************
*sql
erprise Manager, How do I query it so I can use the data in my SQL Server da
tabase.
****************************************
*
* This message was posted via http://droptable.com
*
* Report spam or abuse by clicking the following URL:
* http://droptable.com/Uwe/Abuse.asp...2b79e73dd024921
****************************************
*Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via droptable.com" <forum@.droptable.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQ
droptable.com...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> ****************************************
*
> * This message was posted via http://droptable.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://droptable.com/Uwe/Abuse.asp...2b79e73dd024921
> ****************************************
*sql
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
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?
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'
>
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'
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
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
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...
>
>
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...
>
>
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 are
connected. I have been forced to specify each group memebr individually. Is
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 say
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 looking
for a better option.
"Russell Fields" wrote:
> 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...
>
>
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?
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 say
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 looking
for a better option.
"Russell Fields" wrote:
> 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...
>
>
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 are
connected. I have been forced to specify each group memebr individually. Is
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...
>>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?
>|||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 say
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 looking
for a better option.
"Russell Fields" wrote:
> 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...
> >>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?
> >
>
>
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?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...
>>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?
>|||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 say
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 looking
for a better option.
"Russell Fields" wrote:
> 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...
> >>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?
> >
>
>
Linked Server properties
If I make changes to the linked server properties in the
Security tab, do I need to reboot for them to take affect?
What is the best practice for this?
This is on a SQL Server 2000 instance.
Thanks.They take effect immediately.(or at leat the next time the linked server =is used) if a query happened to be running while youmade the change =isuspect it would not pick up the change, but the next query certainly =should.
Reboot - No
Mike John
"phil" <pjconrad@.icontrans.com> wrote in message =news:09e901c3941f$a54d9d10$a001280a@.phx.gbl...
> If I make changes to the linked server properties in the > Security tab, do I need to reboot for them to take affect?
> What is the best practice for this?
> > This is on a SQL Server 2000 instance. > > Thanks.sql
Security tab, do I need to reboot for them to take affect?
What is the best practice for this?
This is on a SQL Server 2000 instance.
Thanks.They take effect immediately.(or at leat the next time the linked server =is used) if a query happened to be running while youmade the change =isuspect it would not pick up the change, but the next query certainly =should.
Reboot - No
Mike John
"phil" <pjconrad@.icontrans.com> wrote in message =news:09e901c3941f$a54d9d10$a001280a@.phx.gbl...
> If I make changes to the linked server properties in the > Security tab, do I need to reboot for them to take affect?
> What is the best practice for this?
> > This is on a SQL Server 2000 instance. > > Thanks.sql
Linked Server problems with Visual Foxpro
My company is using SQL 2000 and a Visual Foxpro
database. In order to perform joint queries against the
two data sources, we set up a linked server definition to
the VFP data source. Everything was working perfectly
until the other day, when I noticed that the server had
not been updated in quite some time. I installed SQL 2000
SP3a, which worked just fine. However, after that, when I
submit a query that uses the VFP linked server, it does
not work like it did. If the VFP part of the query is
going against a local view defined in the VFP database,
sometimes it works, sometimes it doesn't. There are no
error messages, nothing to indicate there is a problem.
It just doesn't return any rows. This is true if I do the
query through EM, Query Analyzer, or via ODBC connection
to the server. This is a critical situation. I even went
so far as to remove SQL 2000 and re-install and NOT
upgrade to SP3a. No change. Any assitance in this area
would be greatly appreciated. Thank youJohn,
It's never a good idea to just apply service packs without testing first.
Have a look here and see if this addresses your issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;810474&Product=sql2k
--
Andrew J. Kelly
SQL Server MVP
"John" <kcmo_710@.yahoo.com> wrote in message
news:00f001c3afbb$07f640f0$7d02280a@.phx.gbl...
> My company is using SQL 2000 and a Visual Foxpro
> database. In order to perform joint queries against the
> two data sources, we set up a linked server definition to
> the VFP data source. Everything was working perfectly
> until the other day, when I noticed that the server had
> not been updated in quite some time. I installed SQL 2000
> SP3a, which worked just fine. However, after that, when I
> submit a query that uses the VFP linked server, it does
> not work like it did. If the VFP part of the query is
> going against a local view defined in the VFP database,
> sometimes it works, sometimes it doesn't. There are no
> error messages, nothing to indicate there is a problem.
> It just doesn't return any rows. This is true if I do the
> query through EM, Query Analyzer, or via ODBC connection
> to the server. This is a critical situation. I even went
> so far as to remove SQL 2000 and re-install and NOT
> upgrade to SP3a. No change. Any assitance in this area
> would be greatly appreciated. Thank you
database. In order to perform joint queries against the
two data sources, we set up a linked server definition to
the VFP data source. Everything was working perfectly
until the other day, when I noticed that the server had
not been updated in quite some time. I installed SQL 2000
SP3a, which worked just fine. However, after that, when I
submit a query that uses the VFP linked server, it does
not work like it did. If the VFP part of the query is
going against a local view defined in the VFP database,
sometimes it works, sometimes it doesn't. There are no
error messages, nothing to indicate there is a problem.
It just doesn't return any rows. This is true if I do the
query through EM, Query Analyzer, or via ODBC connection
to the server. This is a critical situation. I even went
so far as to remove SQL 2000 and re-install and NOT
upgrade to SP3a. No change. Any assitance in this area
would be greatly appreciated. Thank youJohn,
It's never a good idea to just apply service packs without testing first.
Have a look here and see if this addresses your issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;810474&Product=sql2k
--
Andrew J. Kelly
SQL Server MVP
"John" <kcmo_710@.yahoo.com> wrote in message
news:00f001c3afbb$07f640f0$7d02280a@.phx.gbl...
> My company is using SQL 2000 and a Visual Foxpro
> database. In order to perform joint queries against the
> two data sources, we set up a linked server definition to
> the VFP data source. Everything was working perfectly
> until the other day, when I noticed that the server had
> not been updated in quite some time. I installed SQL 2000
> SP3a, which worked just fine. However, after that, when I
> submit a query that uses the VFP linked server, it does
> not work like it did. If the VFP part of the query is
> going against a local view defined in the VFP database,
> sometimes it works, sometimes it doesn't. There are no
> error messages, nothing to indicate there is a problem.
> It just doesn't return any rows. This is true if I do the
> query through EM, Query Analyzer, or via ODBC connection
> to the server. This is a critical situation. I even went
> so far as to remove SQL 2000 and re-install and NOT
> upgrade to SP3a. No change. Any assitance in this area
> would be greatly appreciated. Thank you
Linked Server Problems with all providers
Im having problems with linked servers on one particular computer and am out
of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a linke
d
server of any type, clicking on "tables" or "views" in the enterprise manage
r
list, gives the following error:
Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initialize
returned 0x80004005: ].
This particular LSN is pointing to another SQL server machine. Regardless
of what provider i choose, i get the same error (the error message changes
the provider based on the provider of the LSN).
I am an administrator on both boxes, have disabled virus protection, the
windows firewall on the XP box is disabled (by a group policy on the domain)
,
and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
LSN's created on other computers work fine. Any ideas?You can get this error with timeout issues. Refer to the
following:
Error message when you execute a linked server query in SQL
Server: "Timeout Expired"
http://support.microsoft.com/?id=314530
You can also double check the security mappings for the
linked servers.
You will also want to check the connectivity - try pinging
the other servers from the box you are having problems with.
Ping by server name and IP.
-Sue
On Wed, 15 Mar 2006 06:56:28 -0800, John Kurtis <John
Kurtis@.discussions.microsoft.com> wrote:
>Im having problems with linked servers on one particular computer and am ou
t
>of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a link
ed
>server of any type, clicking on "tables" or "views" in the enterprise manag
er
>list, gives the following error:
>Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initializ
e
>returned 0x80004005: ].
>This particular LSN is pointing to another SQL server machine. Regardless
>of what provider i choose, i get the same error (the error message changes
>the provider based on the provider of the LSN).
>I am an administrator on both boxes, have disabled virus protection, the
>windows firewall on the XP box is disabled (by a group policy on the domain
),
>and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
>LSN's created on other computers work fine. Any ideas?|||Thanks for the reply. However im 99% sure that its not a timeout issue. I
get the error immediately after i click on "tables" or "views" in enterprise
manager.
Pings from the box that is having problems come back fine, both with IP as
well as name.
of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a linke
d
server of any type, clicking on "tables" or "views" in the enterprise manage
r
list, gives the following error:
Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initialize
returned 0x80004005: ].
This particular LSN is pointing to another SQL server machine. Regardless
of what provider i choose, i get the same error (the error message changes
the provider based on the provider of the LSN).
I am an administrator on both boxes, have disabled virus protection, the
windows firewall on the XP box is disabled (by a group policy on the domain)
,
and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
LSN's created on other computers work fine. Any ideas?You can get this error with timeout issues. Refer to the
following:
Error message when you execute a linked server query in SQL
Server: "Timeout Expired"
http://support.microsoft.com/?id=314530
You can also double check the security mappings for the
linked servers.
You will also want to check the connectivity - try pinging
the other servers from the box you are having problems with.
Ping by server name and IP.
-Sue
On Wed, 15 Mar 2006 06:56:28 -0800, John Kurtis <John
Kurtis@.discussions.microsoft.com> wrote:
>Im having problems with linked servers on one particular computer and am ou
t
>of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a link
ed
>server of any type, clicking on "tables" or "views" in the enterprise manag
er
>list, gives the following error:
>Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initializ
e
>returned 0x80004005: ].
>This particular LSN is pointing to another SQL server machine. Regardless
>of what provider i choose, i get the same error (the error message changes
>the provider based on the provider of the LSN).
>I am an administrator on both boxes, have disabled virus protection, the
>windows firewall on the XP box is disabled (by a group policy on the domain
),
>and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
>LSN's created on other computers work fine. Any ideas?|||Thanks for the reply. However im 99% sure that its not a timeout issue. I
get the error immediately after i click on "tables" or "views" in enterprise
manager.
Pings from the box that is having problems come back fine, both with IP as
well as name.
Linked Server Problems HELP HELP HELP !
having a LOT of problems with SQL server linked servers.
I have a server running Windows Server 2003. (Server A)
I want to run a query from a different server (Server B) using query
analyser.
E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
keep getting the following message
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
looked through various pages/groups etc.
my authentication mode is set to mixed.
still getting the message.
starting to annoy me - a LOT.!!
Help please!!!
THanks in advance.Paul
Can you post a script for set up linked server?
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||how?|||This example of set up linekd server to Access (Lookup these SP in the BOL)
USE master
GO
EXEC sp_addlinkedserver
@.server = 'test_Access1',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\WINDOWS\Desktop\uritennis.mdb'
GO
sp_linkedservers
sp_dropserver 'test_Access1', 'droplogins'
USE master
GO
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'test_Access1',
@.useself = false,
@.locallogin = null,
@.rmtuser ='admin',
@.rmtpassword =null
USE master
GO
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
<paul_30s@.hotmail.com> wrote in message
news:1140522241.310199.137970@.g47g2000cwa.googlegroups.com...
> how?
>|||done all this stuff. still same problem.
is there anything on the server that i need to check ?|||Have you ran it on linked server?
exec sp_serveroption 'SERVER','data access','true'
<paul_30s@.hotmail.com> wrote in message
news:1140523365.016551.291200@.g44g2000cwa.googlegroups.com...
> done all this stuff. still same problem.
> is there anything on the server that i need to check ?
>|||still the same.|||It seems like YOUR server is using mixed authorization but LINKED server is
not (trusted connections only)
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||both servers are using mixed authentication.|||paul_30s@.hotmail.com wrote:
> still the same.
>
Have you tried using a SQL user instead of an Windows/NT user account?
If you want to use integrated security (windows user) you have to ensure
that security account delegation is enabled (and that's something that
has to be set up in your Active Directory).
You can read more about security account delegation in Books On Line -
look for "Security Account Delegation".
Regards
Steen
I have a server running Windows Server 2003. (Server A)
I want to run a query from a different server (Server B) using query
analyser.
E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
keep getting the following message
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
looked through various pages/groups etc.
my authentication mode is set to mixed.
still getting the message.
starting to annoy me - a LOT.!!
Help please!!!
THanks in advance.Paul
Can you post a script for set up linked server?
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||how?|||This example of set up linekd server to Access (Lookup these SP in the BOL)
USE master
GO
EXEC sp_addlinkedserver
@.server = 'test_Access1',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\WINDOWS\Desktop\uritennis.mdb'
GO
sp_linkedservers
sp_dropserver 'test_Access1', 'droplogins'
USE master
GO
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'test_Access1',
@.useself = false,
@.locallogin = null,
@.rmtuser ='admin',
@.rmtpassword =null
USE master
GO
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
<paul_30s@.hotmail.com> wrote in message
news:1140522241.310199.137970@.g47g2000cwa.googlegroups.com...
> how?
>|||done all this stuff. still same problem.
is there anything on the server that i need to check ?|||Have you ran it on linked server?
exec sp_serveroption 'SERVER','data access','true'
<paul_30s@.hotmail.com> wrote in message
news:1140523365.016551.291200@.g44g2000cwa.googlegroups.com...
> done all this stuff. still same problem.
> is there anything on the server that i need to check ?
>|||still the same.|||It seems like YOUR server is using mixed authorization but LINKED server is
not (trusted connections only)
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||both servers are using mixed authentication.|||paul_30s@.hotmail.com wrote:
> still the same.
>
Have you tried using a SQL user instead of an Windows/NT user account?
If you want to use integrated security (windows user) you have to ensure
that security account delegation is enabled (and that's something that
has to be set up in your Active Directory).
You can read more about security account delegation in Books On Line -
look for "Security Account Delegation".
Regards
Steen
Subscribe to:
Posts (Atom)