Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.
The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure to
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server access
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure t
o
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server acces
s
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed query against loopback linked server is not supported

I receive the following error message when I run a distributed query against
a loopback linked server in SQL Server 2005:
"The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
the distributed transaction."
To resolve this problem, I was told that running a distributed query against
a loopback linked server is not supported in SQL Server 2005. And I am
suggested to use a remote server definition (sp_addserver) instead of a
linked server definition to resolve this problem. (Although this is only a
temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition.
I receive the following error message:
"Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerName\SQL2005' because '' is not defined
as a remote login at the server. Verify that you have specified the correct
login name."
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my
resolution that generates the second error message)
====== Reproduce steps for the first error message
======
1. On the ComputerA\InstanceA instance, run the following statement to
create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
2. On the ComputerB\InstanceB instance, run the following statement to
create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
3. On the ComputerA\InstanceA instance, create a linked server that links to
the ComputerB\InstanceB instance. Assume the name of the linked server is
LNK_ServerB.
4. On the ComputerB\InstanceB instance, create a linked server that links to
the ComputerA\InstanceA instance. Assume the name of the linked server is
LNK_ServerA.
5. On the ComputerB\InstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
6. On the ComputerA\InstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
======= My resolution that generates the second error message
======= 1. On the ComputerB\InstanceB instance, run the following statement:
sp_addserver 'ComputerA\InstanceA'
GO
sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
END
GO
2. On the ComputerA\InstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.Hi
Have you checked the security tab on the linked server properties in SSMS?
You may be using "be made without using a security context"
John
"Felix_Jiang" wrote:
> I receive the following error message when I run a distributed query against
> a loopback linked server in SQL Server 2005:
> "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> the distributed transaction."
> To resolve this problem, I was told that running a distributed query against
> a loopback linked server is not supported in SQL Server 2005. And I am
> suggested to use a remote server definition (sp_addserver) instead of a
> linked server definition to resolve this problem. (Although this is only a
> temporary resolution, which will deprecate in Katmai)
> However, I run into another problem when I use the remote server definition.
> I receive the following error message:
> "Msg 18483, Level 14, State 1, Line 1
> Could not connect to server 'ServerName\SQL2005' because '' is not defined
> as a remote login at the server. Verify that you have specified the correct
> login name."
> Could anyone please help me out?
> (I include the reproduce steps for the first error message, followed by my
> resolution that generates the second error message)
> ======> Reproduce steps for the first error message
> ======> 1. On the ComputerA\InstanceA instance, run the following statement to
> create a database and a table:
> CREATE DATABASE DatabaseA
> GO
> USE DatabaseA
> GO
> CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> GO
> INSERT INTO TestTable VALUES (1, 'Hello World')
> GO
> 2. On the ComputerB\InstanceB instance, run the following statement to
> create a database and a table:
> CREATE DATABASE DatabaseB
> GO
> USE DatabaseB
> GO
> CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> GO
> 3. On the ComputerA\InstanceA instance, create a linked server that links to
> the ComputerB\InstanceB instance. Assume the name of the linked server is
> LNK_ServerB.
> 4. On the ComputerB\InstanceB instance, create a linked server that links to
> the ComputerA\InstanceA instance. Assume the name of the linked server is
> LNK_ServerA.
> 5. On the ComputerB\InstanceB instance, run the following statement:
> USE DatabaseB
> GO
> CREATE PROCEDURE InsertA AS
> BEGIN
> SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> END
> GO
> 6. On the ComputerA\InstanceA instance, run the following statement:
> USE DatabaseA
> GO
> INSERT INTO TestTable
> EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> GO
> Then I receive the first error message.
> =======> My resolution that generates the second error message
> =======> 1. On the ComputerB\InstanceB instance, run the following statement:
> sp_addserver 'ComputerA\InstanceA'
> GO
> sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> GO
> USE DatabaseB
> GO
> CREATE PROCEDURE InsertA AS
> BEGIN
> SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> END
> GO
> 2. On the ComputerA\InstanceA instance, run the following statement:
> USE DatabaseA
> GO
> INSERT INTO TestTable
> EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> GO
> Then I receive the second error message.|||John, thanks so much for your reply.
Yes, I use my Windows user logon in the security context. Additionally, both
servers are logged on using the Administrator account.
If I would like to use the remote server definition, could you please shed
any light on the second error message? Thanks!
"John Bell" wrote:
> Hi
> Have you checked the security tab on the linked server properties in SSMS?
> You may be using "be made without using a security context"
> John
> "Felix_Jiang" wrote:
> > I receive the following error message when I run a distributed query against
> > a loopback linked server in SQL Server 2005:
> > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > the distributed transaction."
> >
> > To resolve this problem, I was told that running a distributed query against
> > a loopback linked server is not supported in SQL Server 2005. And I am
> > suggested to use a remote server definition (sp_addserver) instead of a
> > linked server definition to resolve this problem. (Although this is only a
> > temporary resolution, which will deprecate in Katmai)
> >
> > However, I run into another problem when I use the remote server definition.
> > I receive the following error message:
> > "Msg 18483, Level 14, State 1, Line 1
> > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > as a remote login at the server. Verify that you have specified the correct
> > login name."
> >
> > Could anyone please help me out?
> > (I include the reproduce steps for the first error message, followed by my
> > resolution that generates the second error message)
> >
> > ======> > Reproduce steps for the first error message
> > ======> >
> > 1. On the ComputerA\InstanceA instance, run the following statement to
> > create a database and a table:
> > CREATE DATABASE DatabaseA
> > GO
> > USE DatabaseA
> > GO
> > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > GO
> > INSERT INTO TestTable VALUES (1, 'Hello World')
> > GO
> >
> > 2. On the ComputerB\InstanceB instance, run the following statement to
> > create a database and a table:
> > CREATE DATABASE DatabaseB
> > GO
> > USE DatabaseB
> > GO
> > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > GO
> >
> > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > LNK_ServerB.
> >
> > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > LNK_ServerA.
> >
> > 5. On the ComputerB\InstanceB instance, run the following statement:
> > USE DatabaseB
> > GO
> > CREATE PROCEDURE InsertA AS
> > BEGIN
> > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > END
> > GO
> >
> > 6. On the ComputerA\InstanceA instance, run the following statement:
> > USE DatabaseA
> > GO
> > INSERT INTO TestTable
> > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > GO
> >
> > Then I receive the first error message.
> >
> > =======> > My resolution that generates the second error message
> > =======> > 1. On the ComputerB\InstanceB instance, run the following statement:
> > sp_addserver 'ComputerA\InstanceA'
> > GO
> > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > GO
> > USE DatabaseB
> > GO
> > CREATE PROCEDURE InsertA AS
> > BEGIN
> > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > END
> > GO
> >
> > 2. On the ComputerA\InstanceA instance, run the following statement:
> > USE DatabaseA
> > GO
> > INSERT INTO TestTable
> > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > GO
> >
> > Then I receive the second error message.|||Hi
For the first try I would not expect the error you have, from BOL:
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within a
distributed transaction causes an error:
Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.
Therefore the error message you are getting for the loopback server is not
the one I would expect.
For the second error http://support.microsoft.com/kb/818334 indicates that
there is possible wrong with the server names. I assume that both servers are
in the same domains?
Try using
INSERT INTO TestTable
SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
GO
John
"Felix_Jiang" wrote:
> John, thanks so much for your reply.
> Yes, I use my Windows user logon in the security context. Additionally, both
> servers are logged on using the Administrator account.
> If I would like to use the remote server definition, could you please shed
> any light on the second error message? Thanks!
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the security tab on the linked server properties in SSMS?
> >
> > You may be using "be made without using a security context"
> >
> > John
> >
> > "Felix_Jiang" wrote:
> >
> > > I receive the following error message when I run a distributed query against
> > > a loopback linked server in SQL Server 2005:
> > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > the distributed transaction."
> > >
> > > To resolve this problem, I was told that running a distributed query against
> > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > suggested to use a remote server definition (sp_addserver) instead of a
> > > linked server definition to resolve this problem. (Although this is only a
> > > temporary resolution, which will deprecate in Katmai)
> > >
> > > However, I run into another problem when I use the remote server definition.
> > > I receive the following error message:
> > > "Msg 18483, Level 14, State 1, Line 1
> > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > as a remote login at the server. Verify that you have specified the correct
> > > login name."
> > >
> > > Could anyone please help me out?
> > > (I include the reproduce steps for the first error message, followed by my
> > > resolution that generates the second error message)
> > >
> > > ======> > > Reproduce steps for the first error message
> > > ======> > >
> > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > create a database and a table:
> > > CREATE DATABASE DatabaseA
> > > GO
> > > USE DatabaseA
> > > GO
> > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > GO
> > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > GO
> > >
> > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > create a database and a table:
> > > CREATE DATABASE DatabaseB
> > > GO
> > > USE DatabaseB
> > > GO
> > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > GO
> > >
> > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > LNK_ServerB.
> > >
> > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > LNK_ServerA.
> > >
> > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > USE DatabaseB
> > > GO
> > > CREATE PROCEDURE InsertA AS
> > > BEGIN
> > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > END
> > > GO
> > >
> > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > USE DatabaseA
> > > GO
> > > INSERT INTO TestTable
> > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > GO
> > >
> > > Then I receive the first error message.
> > >
> > > =======> > > My resolution that generates the second error message
> > > =======> > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > sp_addserver 'ComputerA\InstanceA'
> > > GO
> > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > GO
> > > USE DatabaseB
> > > GO
> > > CREATE PROCEDURE InsertA AS
> > > BEGIN
> > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > END
> > > GO
> > >
> > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > USE DatabaseA
> > > GO
> > > INSERT INTO TestTable
> > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > GO
> > >
> > > Then I receive the second error message.|||Hi John
Thank you for your reply. Your suggestion really works. I notice that the
reason for the second error message is that ComputerA and ComputerB are in
the same domain. When I use SQL Server Login instead to run the statement, I
didn't receive the second error message.
Many many thanks!
One remaining question, do you think the "reproduce" section qualifies a
loopback linked server?
Chen
"John Bell" wrote:
> Hi
> For the first try I would not expect the error you have, from BOL:
> Loopback linked servers cannot be used in a distributed transaction.
> Attempting a distributed query against a loopback linked server from within a
> distributed transaction causes an error:
> Msg: 3910 Level: 16 State: 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
> another session.
> Therefore the error message you are getting for the loopback server is not
> the one I would expect.
> For the second error http://support.microsoft.com/kb/818334 indicates that
> there is possible wrong with the server names. I assume that both servers are
> in the same domains?
> Try using
> INSERT INTO TestTable
> SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
> GO
> John
> "Felix_Jiang" wrote:
> > John, thanks so much for your reply.
> >
> > Yes, I use my Windows user logon in the security context. Additionally, both
> > servers are logged on using the Administrator account.
> >
> > If I would like to use the remote server definition, could you please shed
> > any light on the second error message? Thanks!
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Have you checked the security tab on the linked server properties in SSMS?
> > >
> > > You may be using "be made without using a security context"
> > >
> > > John
> > >
> > > "Felix_Jiang" wrote:
> > >
> > > > I receive the following error message when I run a distributed query against
> > > > a loopback linked server in SQL Server 2005:
> > > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > > the distributed transaction."
> > > >
> > > > To resolve this problem, I was told that running a distributed query against
> > > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > > suggested to use a remote server definition (sp_addserver) instead of a
> > > > linked server definition to resolve this problem. (Although this is only a
> > > > temporary resolution, which will deprecate in Katmai)
> > > >
> > > > However, I run into another problem when I use the remote server definition.
> > > > I receive the following error message:
> > > > "Msg 18483, Level 14, State 1, Line 1
> > > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > > as a remote login at the server. Verify that you have specified the correct
> > > > login name."
> > > >
> > > > Could anyone please help me out?
> > > > (I include the reproduce steps for the first error message, followed by my
> > > > resolution that generates the second error message)
> > > >
> > > > ======> > > > Reproduce steps for the first error message
> > > > ======> > > >
> > > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > > create a database and a table:
> > > > CREATE DATABASE DatabaseA
> > > > GO
> > > > USE DatabaseA
> > > > GO
> > > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > > GO
> > > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > > GO
> > > >
> > > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > > create a database and a table:
> > > > CREATE DATABASE DatabaseB
> > > > GO
> > > > USE DatabaseB
> > > > GO
> > > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > > GO
> > > >
> > > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > > LNK_ServerB.
> > > >
> > > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > > LNK_ServerA.
> > > >
> > > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > > USE DatabaseB
> > > > GO
> > > > CREATE PROCEDURE InsertA AS
> > > > BEGIN
> > > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > > END
> > > > GO
> > > >
> > > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > > USE DatabaseA
> > > > GO
> > > > INSERT INTO TestTable
> > > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > > GO
> > > >
> > > > Then I receive the first error message.
> > > >
> > > > =======> > > > My resolution that generates the second error message
> > > > =======> > > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > > sp_addserver 'ComputerA\InstanceA'
> > > > GO
> > > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > > GO
> > > > USE DatabaseB
> > > > GO
> > > > CREATE PROCEDURE InsertA AS
> > > > BEGIN
> > > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > > END
> > > > GO
> > > >
> > > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > > USE DatabaseA
> > > > GO
> > > > INSERT INTO TestTable
> > > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > > GO
> > > >
> > > > Then I receive the second error message.|||Hi
It would be useful to have the linked server scripted e.g.
EXEC sp_addlinkedserver
@.server='LoopBack',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='(LOCAL)'
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'LoopBack',@.useself=N'True',@.locallogin=NULL,@.rmtuser=NULL,@.rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @.server=N'LoopBack', @.optname=N'rpc out',
@.optvalue=N'true'
GO
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LoopBack.DatabaseA.dbo.TestTable
END
GO
EXEC InsertA
GO
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LoopBack.DatabaseB.dbo.InsertA
GO
/*
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
*/
INSERT INTO TestTable
SELECT * FROM OPENQUERY(LoopBack,'EXEC DatabaseB.dbo.InsertA')
GO
John
"Felix_Jiang" wrote:
> Hi John
> Thank you for your reply. Your suggestion really works. I notice that the
> reason for the second error message is that ComputerA and ComputerB are in
> the same domain. When I use SQL Server Login instead to run the statement, I
> didn't receive the second error message.
> Many many thanks!
> One remaining question, do you think the "reproduce" section qualifies a
> loopback linked server?
> Chen
>
> "John Bell" wrote:
> > Hi
> >
> > For the first try I would not expect the error you have, from BOL:
> >
> > Loopback linked servers cannot be used in a distributed transaction.
> > Attempting a distributed query against a loopback linked server from within a
> > distributed transaction causes an error:
> >
> > Msg: 3910 Level: 16 State: 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
> > another session.
> >
> > Therefore the error message you are getting for the loopback server is not
> > the one I would expect.
> >
> > For the second error http://support.microsoft.com/kb/818334 indicates that
> > there is possible wrong with the server names. I assume that both servers are
> > in the same domains?
> >
> > Try using
> >
> > INSERT INTO TestTable
> > SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
> > GO
> >
> > John
> >
> > "Felix_Jiang" wrote:
> >
> > > John, thanks so much for your reply.
> > >
> > > Yes, I use my Windows user logon in the security context. Additionally, both
> > > servers are logged on using the Administrator account.
> > >
> > > If I would like to use the remote server definition, could you please shed
> > > any light on the second error message? Thanks!
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Have you checked the security tab on the linked server properties in SSMS?
> > > >
> > > > You may be using "be made without using a security context"
> > > >
> > > > John
> > > >
> > > > "Felix_Jiang" wrote:
> > > >
> > > > > I receive the following error message when I run a distributed query against
> > > > > a loopback linked server in SQL Server 2005:
> > > > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > > > the distributed transaction."
> > > > >
> > > > > To resolve this problem, I was told that running a distributed query against
> > > > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > > > suggested to use a remote server definition (sp_addserver) instead of a
> > > > > linked server definition to resolve this problem. (Although this is only a
> > > > > temporary resolution, which will deprecate in Katmai)
> > > > >
> > > > > However, I run into another problem when I use the remote server definition.
> > > > > I receive the following error message:
> > > > > "Msg 18483, Level 14, State 1, Line 1
> > > > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > > > as a remote login at the server. Verify that you have specified the correct
> > > > > login name."
> > > > >
> > > > > Could anyone please help me out?
> > > > > (I include the reproduce steps for the first error message, followed by my
> > > > > resolution that generates the second error message)
> > > > >
> > > > > ======> > > > > Reproduce steps for the first error message
> > > > > ======> > > > >
> > > > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > > > create a database and a table:
> > > > > CREATE DATABASE DatabaseA
> > > > > GO
> > > > > USE DatabaseA
> > > > > GO
> > > > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > > > GO
> > > > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > > > GO
> > > > >
> > > > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > > > create a database and a table:
> > > > > CREATE DATABASE DatabaseB
> > > > > GO
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > > > GO
> > > > >
> > > > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > > > LNK_ServerB.
> > > > >
> > > > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > > > LNK_ServerA.
> > > > >
> > > > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE PROCEDURE InsertA AS
> > > > > BEGIN
> > > > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > > > END
> > > > > GO
> > > > >
> > > > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > > > USE DatabaseA
> > > > > GO
> > > > > INSERT INTO TestTable
> > > > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > > > GO
> > > > >
> > > > > Then I receive the first error message.
> > > > >
> > > > > =======> > > > > My resolution that generates the second error message
> > > > > =======> > > > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > > > sp_addserver 'ComputerA\InstanceA'
> > > > > GO
> > > > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > > > GO
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE PROCEDURE InsertA AS
> > > > > BEGIN
> > > > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > > > END
> > > > > GO
> > > > >
> > > > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > > > USE DatabaseA
> > > > > GO
> > > > > INSERT INTO TestTable
> > > > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > > > GO
> > > > >
> > > > > Then I receive the second error message.sql

Distributed query & OLE DB provider

Hi,

I hope I'm in correct forum..

I'm working on simple OLE DB provider that should be usable in SQL server as a linked server. The provider should provide some support for SQL (certain SELECTs for now) and therefore I'd like the SQL server to delegate portions of query processing to the provider instead of fetching all rows and doing SELECT on its own.

Alas, I'm no OLE DB expert and my provider apparently doesn't implement enough things to actually make SQL server pass the query to it. My provider's session currently implements mandatory interfaces + couple of others (IDBCreateCommand, IDBSchemaRowset) and via provider properties, I 'claim' to have full support for SQL92. I'm testing the provider with SQL Server 2005 ('Express edition').

I'd like to know what am I missing in the implementation, or whether/how can I find out what makes SQL server decide between fetching full rowset and creating a command to let rthe query run in provider.

Thanks,

L.

In general, if you use 4-part name query, the processing is done at the local server. That means the remote data is pulled to the local server before work is done. However, if openquery/openrowset/opendatasource() is used the remote processing is done on the remote server. This is what we often call as pass-through query.

If you're creating a custom oledb provider, what would be more important is the distributed transaction implementation. I suggest you check msdn on this.

|||

I just begun reading on the transactions yesterday evening, so I certainly shall dig into it bit deeper as you suggest, thanks .

Regarding 4-part name query, you wrote 'in general'. Is the processing 'always' done locally, or is there a way to make even 4-part name queries run remotely?

|||

If you have a remote filter and your provider is delegateable (see http://msdn2.microsoft.com/en-us/library/ms180972.aspx) only the filtered rows are returned.

e.g.

Code Snippet

select a.name,b.name
from master..sysdatabases a join remotesrv.master.dbo.sysdatabases b on a.status=b.status
where b.name like 'D%'

What going on here is the local engine delegates the following work to the remote server

Code Snippet

select name from master.dbo.sysdatabases

where name like 'D%'

instead of asking the remote server to return the entire table then do join and filter locally.

Note that the join is still done locally!

|||

Well, according to description available at the link, my provider 'should be' delegateable as it claims to support SQL 92 and supports the command. I suppose there's nothing like a tool to tell me 'yes' or 'no, you fail here and here to be SQL command provider'..?

However, I guess I might have a problem with collation rules as I haven't cared about them at all. I'll check it later today.

Thanks for help!

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.edu
What account is SQL Server running under? The query is run in it's context.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>
|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>>I would like to perform Distributed Queries on Active Directory via views
>>in SQL Server. I have created a linked server by executing:
>> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
>> 'ADSDSOObject', 'adsdatasource'
>> However whenever I run even the simplest query:
>> SELECT *FROM OPENQUERY( ADSI,
>> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
>> I get the following error:
>> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
>> that the user did not have the permission to perform the operation.
>> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
>> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates
>> that the user did not have the permission to perform the operation.].
>> I am logged into the server as an Administrator and logging into query
>> analyzer as sa - so I should have all required permissions.
>> Any ideas? Any help or direction is really appreciated - Thanks in
>> advance,
>> J. Shane Kunkle
>> jkunkle@.vt.edu
>>
>

Distributed query

First question : what are the necessary step to allow remote access (linked
server) using Windows logging and no Active Directory available
I tried the following without success
EXEC sp_addlinkedsrvlogin 'sql2k5', 'false', 'Soline\myLogin',
'sql2k5\mylogin', 'remotepassword'
but when I map to sa remote server login it works fine, so my questions
A - Does that mean that we can not map windows login as far as delegation
is not possible becaus of non existance of AD?
B - what are the min requirements (privilges) to be given to login rather
then "SA" login on the server leveland on the DB level in order to be able t
o
run distributed query. what I mean by that is there any good step by step
procedure to follow to implment this using min priviliges on both sides as o
n
Lcris blog regarding "Cross database access"Hi,
If you would like to use Windows login without AD to access linked servers,
the Windows Authentication is NTLM then.
It is required to create a same user with same password on each machine.
For your two questions:
> A - Does that mean that we can not map windows login as far as delegation
is not possible becaus of non existance of AD?
If there is no AD, please manually create the Windows user with the same
password on both machine.

> B - what are the min requirements (privilges) to be given to login rather
then "SA" login on the server leveland on the DB level in order to be able
to run distributed query.
> what I mean by that is there any good step by step procedure to follow to
implment this using min priviliges on both sides as on Lcris blog regarding
"Cross database access"
If you just want to query the database, the min requirements is that the
user corresponed to the login needs db_datareader permission.
First create a login on the remote server, double click the login, switch
to the User Mapping, map the login to a user of the database, and assign
db_datareader permission;
Then on your local server, execute the SPs as following:
sp_addlinkedserver 'mySqlServer-03'
sp_addlinkedsrvlogin 'mySqlServer-03',false,NULL,'s1','s1'
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

distributed queries in win 2003 and win 2000

Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhiSaradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison

distributed queries in win 2003 and win 2000

Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhi
Saradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison

distributed queries in win 2003 and win 2000

Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhiSaradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";J et OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MD W";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
Massimo
After other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
sql

Distributed Queries

We are using MS SQL Server 2000 service pack 4.
On our production servers the following scenerio works, our LAN
department recently rebuilt one of our development servers and we are
having problems.
On our central development server I create a temp table CREATE TABLE
#Temp(dur_code char(1) NOT NULL, tmc_code char(1) NOT NULL)
-- Inserting into the temp table on our central server from our DEV2
server works when called this way:
INSERT #temp
SELECT dur_code, tmc_code
FROM dev1.sms.dbo.term_cds
--This dynamic statement works from the central server to dev2 server
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
EXEC (@.vcCmd)
-- Inserting to the same temp table on the central server from the same
DEV2 server does not work when it is called dynamically.
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
INSERT #temp EXEC (@.vcCmd)
I am running this code through SQL Query Analyzer. This code works on
our central server when we call our other development server remotely
(and on all of our production servers). The Distributed Transaction
Coordinator is running on both servers.
Any ideas on what we have done wrong?Karen (karen.jones@.tusd1.org) writes:
> -- Inserting to the same temp table on the central server from the same
> DEV2 server does not work when it is called dynamically.
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> INSERT #temp EXEC (@.vcCmd)
What "does not work" mean? If there is an error message, please post it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||CLARIFICATION:
We are inserting into a table on our central server and making a
dynamic call to the dev2 server.
Karen wrote:
> We are using MS SQL Server 2000 service pack 4.
> On our production servers the following scenerio works, our LAN
> department recently rebuilt one of our development servers and we are
> having problems.
> On our central development server I create a temp table CREATE TABLE
> #Temp(dur_code char(1) NOT NULL, tmc_code char(1) NOT NULL)
> -- Inserting into the temp table on our central server from our DEV2
> server works when called this way:
> INSERT #temp
> SELECT dur_code, tmc_code
> FROM dev1.sms.dbo.term_cds
>
> --This dynamic statement works from the central server to dev2 server
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> EXEC (@.vcCmd)
>
> -- Inserting to the same temp table on the central server from the same
> DEV2 server does not work when it is called dynamically.
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> INSERT #temp EXEC (@.vcCmd)
> I am running this code through SQL Query Analyzer. This code works on
> our central server when we call our other development server remotely
> (and on all of our production servers). The Distributed Transaction
> Coordinator is running on both servers.
> Any ideas on what we have done wrong?|||CLARIFICATION:
There is no error... it just hangs and never returns. It will not even
let you cancel the query, you have to end Query Analyzer through task
manager.
Erland Sommarskog wrote:
> Karen (karen.jones@.tusd1.org) writes:
> What "does not work" mean? If there is an error message, please post it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Karen,
Please help us out in clarifying a few points.
1. DEV1 = "Central Server"
2. DEV2 = "Development Server"
3. You have a temp table #Temp created on DEV1
4. INSERTS into #Temp succeed when executed on DEV1
5. INSERTS into #Temp FAIL when executed on DEV2
6. DEV2 does NOT have a #Temp table
#Temp tables are accessible only to the connection that created them. It is
logical that #Temp would be accessible and the INSERT would succeed when
executed from DEV1. And also that the INSERT would FAIL when you attempt to
insert into DEV1's #Temp table from code executed on DEV2.
Perhaps you need to investigate using a 'global' ##Temp table. (2 pound
signs) This form of ##Temp table is accessible by all connections and stays
in scope as long as there is any single connection open.
If I have properly understood the issue, let me know. Also you might check
in BOL about #Temp / ##Temp tables and scope.
Regards,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Karen" <karen.jones@.tusd1.org> wrote in message
news:1151102931.892749.87140@.p79g2000cwp.googlegroups.com...
> CLARIFICATION:
> We are inserting into a table on our central server and making a
> dynamic call to the dev2 server.
>
> Karen wrote:
>|||I am running the query on the central server through Query Analyzer.
Lets call that DEV1. The data is being added to the temp table on DEV1
from a table on DEV2.
A: The temp table exists on DEV1 and I am executing the statement on
DEV1
INSERT #temp EXEC ('SELECT x, y FROM DEV2.sms.dbo.table')
The above code works.
B:Using the same scenerio (A:) I execute the following code
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT x, y, FROM DEV2.sms.dbo.table'
INSERT #temp EXEC(@.vcCmd)
The above code does not work.
The same code as above (example B:) works on our production servers but
does not work on one (1) of our development servers, all other
development servers can run this code just fine.
Recently our LAN staff rebuilt the DEV2 server and this is when the
problem started.
Since example A: works we know it has nothing to do with the linked
server setup
and the DTC is running.
Arnie Rowland wrote:
> Karen,
> Please help us out in clarifying a few points.
> 1. DEV1 = "Central Server"
> 2. DEV2 = "Development Server"
> 3. You have a temp table #Temp created on DEV1
> 4. INSERTS into #Temp succeed when executed on DEV1
> 5. INSERTS into #Temp FAIL when executed on DEV2
> 6. DEV2 does NOT have a #Temp table
> #Temp tables are accessible only to the connection that created them. It i
s
> logical that #Temp would be accessible and the INSERT would succeed when
> executed from DEV1. And also that the INSERT would FAIL when you attempt t
o
> insert into DEV1's #Temp table from code executed on DEV2.
> Perhaps you need to investigate using a 'global' ##Temp table. (2 pound
> signs) This form of ##Temp table is accessible by all connections and stay
s
> in scope as long as there is any single connection open.
> If I have properly understood the issue, let me know. Also you might check
> in BOL about #Temp / ##Temp tables and scope.
> Regards,
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Karen" <karen.jones@.tusd1.org> wrote in message
> news:1151102931.892749.87140@.p79g2000cwp.googlegroups.com...|||Karen wrote:
> I am running the query on the central server through Query Analyzer.
> Lets call that DEV1. The data is being added to the temp table on DEV1
> from a table on DEV2.
> A: The temp table exists on DEV1 and I am executing the statement on
> DEV1
> INSERT #temp EXEC ('SELECT x, y FROM DEV2.sms.dbo.table')
> The above code works.
> B:Using the same scenerio (A:) I execute the following code
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT x, y, FROM DEV2.sms.dbo.table'
> INSERT #temp EXEC(@.vcCmd)
> The above code does not work.
> The same code as above (example B:) works on our production servers but
> does not work on one (1) of our development servers, all other
> development servers can run this code just fine.
> Recently our LAN staff rebuilt the DEV2 server and this is when the
> problem started.
> Since example A: works we know it has nothing to do with the linked
> server setup
> and the DTC is running.
>
Have you looked at sysprocesses on each server while this statement is
executing? Look there for any blocking, and also see what waittype is
specified for the spid that is running your statement.
Also look at perfmon on each machine, look at CPU and Average Disk Queue
length to see if either machine is CPU or I/O bound.|||No one is using these servers except myself. No other processes are
running on either server. The table I am accessing has 10 rows. I
believe this is a configuration problem.
Tracy McKibben wrote:
> Karen wrote:
> Have you looked at sysprocesses on each server while this statement is
> executing? Look there for any blocking, and also see what waittype is
> specified for the spid that is running your statement.
> Also look at perfmon on each machine, look at CPU and Average Disk Queue
> length to see if either machine is CPU or I/O bound.|||Karen (karen.jones@.tusd1.org) writes:
> CLARIFICATION:
> There is no error... it just hangs and never returns. It will not even
> let you cancel the query, you have to end Query Analyzer through task
> manager.
Ah, this behaviour. I've have one such situation at work as well. Alas,
I don't have a solution, as I've never cared about getting it fixed.
The only difference to your description is that for me it suffices to
to:
CREATE TABLE #dates (d datetime)
go
INSERT #dates (d)
EXEC ('SELECT * FROM NEMO.sec_93_stb.dbo.dates')
I don't have to put the SQL command in a variable. (And that should not
matter.)
What I have observed is that if I look in sysprocesses on NEMO, the
machice I connect to, there is an entry for a process with host name
JAMIE4K, the machine I connect from. So my conclusion is that the problem
occurs when NEMO tries to connect back to JAMIE4K, but never get a
response. As I've said, I have not dug into this, but our symin people
are very fond of running firewalls about everywhere, and they also lock
down the service accounts for SQL Server.
It is also worth noting that NEMO, the machine I connect to, runs
Windows 2003, whereas JAMIE4K where I connect from runs Windows 2000.
I guess you will have to talk with your LAN people, and ask them to
review what they did with the network.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Karen wrote:
> No one is using these servers except myself. No other processes are
> running on either server. The table I am accessing has 10 rows. I
> believe this is a configuration problem.
>
It very well could be a configuration problem, and looking at
sysprocesses to see what waittype, if any, the process is reporting, you
might well be able to figure out what is misconfigured. Perfmon might
also reveal some important clues. Suggestion is there, use it as you wish.

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work

distributed queries

Hi all,
Do you know that can we use 'distributed queries' in reporting services to
create rdl report or use linked server as datasource.
please advise if any direction, many thanks.
e.g.
SELECT s.sales_amount
FROM access_server...region AS r,
oracle_server..sales_owner.sale AS s,
sql_server.customer_db.dbo.customer AS c
where r.region_id=s.region_id
and s.customer_id=c.customer_id
and r.region_name='Southwest'
and c.customer_name='ABC Steel'Any legal queries can be used. To do something like this you need to switch
from graphical mode to non-graphical mode. There is a button to the right of
the ... that accomplishes this. Just hover your mouse over the buttons to
find the right one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> Do you know that can we use 'distributed queries' in reporting services to
> create rdl report or use linked server as datasource.
> please advise if any direction, many thanks.
> e.g.
> SELECT s.sales_amount
> FROM access_server...region AS r,
> oracle_server..sales_owner.sale AS s,
> sql_server.customer_db.dbo.customer AS c
> where r.region_id=s.region_id
> and s.customer_id=c.customer_id
> and r.region_name='Southwest'
> and c.customer_name='ABC Steel'
>|||Hi Bruce,
I am not sure I undeestand the step(s) to generate the report. do you know
any example or document from microsoft.
Any way, thanks a lot for your help.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Any legal queries can be used. To do something like this you need to
> switch from graphical mode to non-graphical mode. There is a button to the
> right of the ... that accomplishes this. Just hover your mouse over the
> buttons to find the right one.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>|||Your problem is that RS graphical editor does not support this sort of
thing.
What you need to do is two steps. First, you cannot use the Report Wizard.
You need to create an empty report. Right mouse click on Reports, Add -> New
Item. Select Report. You get a blank report.
Second, Click on the dataset tab. Add a new dataset (drop down
combobox),Switch to non-graphical mode. Put in your SQL code.
Go to the dataset tab in the report designer, then do as I mentioned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi Bruce,
> I am not sure I undeestand the step(s) to generate the report. do you know
> any example or document from microsoft.
> Any way, thanks a lot for your help.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>|||Ho Bruce,
Thanks for your direction, i have tried it, it real works ;-)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eNLHfnyFIHA.4140@.TK2MSFTNGP03.phx.gbl...
> Your problem is that RS graphical editor does not support this sort of
> thing.
> What you need to do is two steps. First, you cannot use the Report Wizard.
> You need to create an empty report. Right mouse click on Reports, Add ->
> New Item. Select Report. You get a blank report.
> Second, Click on the dataset tab. Add a new dataset (drop down
> combobox),Switch to non-graphical mode. Put in your SQL code.
> Go to the dataset tab in the report designer, then do as I mentioned.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Hi Bruce,
>> I am not sure I undeestand the step(s) to generate the report. do you
>> know any example or document from microsoft.
>> Any way, thanks a lot for your help.
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>>
>

Distributed processing "farm" using Broker for middleware.

In our current project, we are attempting to use Broker for the middleware for a "queuing" solution that helps in throttling & load-balancing incoming messages. These messages could potentially be either long-running jobs or simply an incoming flood of messages received from our integration web services that are called upon via BizTalk.

For the posting of messages onto the "to do" queue, we are implementing the "fire & forget" pattern that has been discussed here previously and on Remus' blog. This aspect of the design seems to work fine for us. Currently we have this setup where activation is occuring on the target queue, but we don't want to hold open a thread there on the server while waiting for our synchronous calls to various web-services on the processing "farm" to complete.

The next evolution of our design is to try and move activation from off of the primary SQL cluster itself (i.e. activation is currently happening on the clustered SQL boxes) onto the individual processing nodes. For this model, we are looking at using SQL Express on each of the nodes as has been suggested here on the forums for other similar scenarios.

For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.

To invoke the processing on each of the nodes, we need some form of mechanism to send a "wake up" and do the "reads" since no message is being sent to the node itself for any form of activation based on a queue that receives the actual "job". On the same hand, we are considering having a "wake up" queue on the nodes where a simple "wake up" message could be sent to all nodes/queues and then activation on those queues would then invoke the processing "loop" for each node.

My question is how to best establish this "wake up" call to each node. I think I've read about a queue that is internal to Broker itself that receives a message when new items are received in any queue. My initial thought is to put activation on that queue and have a procedure that sends the "wake up" to each of the nodes in our processing farm.

I am looking for any input where others have attempted to solve this type of problem with Broker.

Regards,

Bradley A. Hehe

Hi Brad!

Do you already had a look at the external activation mechanism available in Service Broker? With external activation you can subscribe to the event when a new message arrives at a queue. Then you can activate an external application (like a console application or a WinForms application) that processes the original message from the queue outside of SQL Server within his own process. Doing the processing of a message in an external process should solve your described problem.

You can find here (http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319) a sample (the ExternalActivator sample) which implements the whole infrastructure needed for this task and also some documentation about setting up this sample.

HTH

Klaus Aschenbrenner
www.csharp.at
www.sqljunkies.com/weblog/klaus.aschenbrenner

|||

Brad Hehe wrote:

For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.

I have a little trouble undertanding the reasoning here. What is the role of the SQL Express instance on each node? If you want to do reads from the node using TDS (i.e. ADO.Net or OLEDB), then why do you need a SQL Express instance on the node? If you want involve the SQL Express instances into linked servers (distributed queries) or other forms of DTC, then you will be way better to route the messages instead.

HTH,
~ Remus

|||

As for ExternalActivator, I'll go back and examine that one again. I was under the impression that I could only have 1 external "activator" listening to the events. In my case, I'd have a "farm" of "external" processes listening for events to process.

As for Remus's reply, I was looking to solve the problem without developing anything beyond a "procedure" for activation on the processing node itself. I was trying to avoid the creation of an "service" (i.e. Windows Service) or some similar mechanism polling the main database cluster for jobs. I was hopign to send some form of "wake up" or "heart beart" to the processing nodes to tell them to go out and find work & process through it..... Something more event driven than polling based.

|||Unfortunately, there's no way for a procedure to read a message from a queue on a remote machine. I think you really need to have your processing nodes open a connection to the database where the queue is and receive messages. This is actually much simplere than any of the solutions suggested so far. you just write a program that opens a connection, receives messages from the queue, processes then and then receives more messages. If you need more processing, just start up more jobs. If one of the processing nodes goes down, the connections will be broken, the transactions will roll back, the messages will appear on the queue again and another node can pick them up. This is absloutely the most efficient way to do load balancing because the processing nodes only pull as much work off the queue as they can handle. As soon as their done, they pull more, if there's no work left, they wait on a receive so no resources are wasted. If something goes wrong with a processing application or node, the transaction rolls back and another node or application picks it up. If instead you pull messages out into a local database on each processing node to process them, if one of the processing nodes goes down, it takes the messages with it and they don't get handled until the node comes back up. They aren't lost but they are delayed.|||

I follow you there Roger. Let me run this past you then.

The folks who preceded me on this effort had went about their selection of Broker to avoid going down the road of developing a "service" on each node that "polls" a simple table sitting on the server. Essentially, your proposed solution is doing the same, correct? It is "polling" a broker queue rather than a table with the benefits of conversation grouping, sequencing, etc. All of those are "gains" from the use of Broker over a table. Additionally there is this "WAITFOR" syntax being used. It is this "WAITFOR" syntax that I'm thinking justifies use of broker over the traditional polling approach.

From a resource friendliness view of the world, is Broker (A) the same in server resource utilization or (B) more efficient -- than the traditional approach of polling/updating a "jobs" table. Being that processing cycles / IO on your cluster are one of your most expensive resources to implement, we are looking to ensure we are using those resources wisely. The previous thought process was that polling would be hammering on any already very busy database.

|||Polling is efficient only if there's always something in the table to select each time. A select that returns nothing is a waste of resources so unless the incoming rate is at least as large as your maximum processing rate,polling is inefficient. WAITFOR aleviates this by not returning until there is something to return. Service Broker is optimized for pulling a message from the queue using the least possible resources. Because Service Broker is part of the database, it is able to do things directly that an external application can't.sql

Distributed processing "farm" using Broker for middleware.

In our current project, we are attempting to use Broker for the middleware for a "queuing" solution that helps in throttling & load-balancing incoming messages. These messages could potentially be either long-running jobs or simply an incoming flood of messages received from our integration web services that are called upon via BizTalk.

For the posting of messages onto the "to do" queue, we are implementing the "fire & forget" pattern that has been discussed here previously and on Remus' blog. This aspect of the design seems to work fine for us. Currently we have this setup where activation is occuring on the target queue, but we don't want to hold open a thread there on the server while waiting for our synchronous calls to various web-services on the processing "farm" to complete.

The next evolution of our design is to try and move activation from off of the primary SQL cluster itself (i.e. activation is currently happening on the clustered SQL boxes) onto the individual processing nodes. For this model, we are looking at using SQL Express on each of the nodes as has been suggested here on the forums for other similar scenarios.

For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.

To invoke the processing on each of the nodes, we need some form of mechanism to send a "wake up" and do the "reads" since no message is being sent to the node itself for any form of activation based on a queue that receives the actual "job". On the same hand, we are considering having a "wake up" queue on the nodes where a simple "wake up" message could be sent to all nodes/queues and then activation on those queues would then invoke the processing "loop" for each node.

My question is how to best establish this "wake up" call to each node. I think I've read about a queue that is internal to Broker itself that receives a message when new items are received in any queue. My initial thought is to put activation on that queue and have a procedure that sends the "wake up" to each of the nodes in our processing farm.

I am looking for any input where others have attempted to solve this type of problem with Broker.

Regards,

Bradley A. Hehe

Hi Brad!

Do you already had a look at the external activation mechanism available in Service Broker? With external activation you can subscribe to the event when a new message arrives at a queue. Then you can activate an external application (like a console application or a WinForms application) that processes the original message from the queue outside of SQL Server within his own process. Doing the processing of a message in an external process should solve your described problem.

You can find here (http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319) a sample (the ExternalActivator sample) which implements the whole infrastructure needed for this task and also some documentation about setting up this sample.

HTH

Klaus Aschenbrenner
www.csharp.at
www.sqljunkies.com/weblog/klaus.aschenbrenner

|||

Brad Hehe wrote:

For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.

I have a little trouble undertanding the reasoning here. What is the role of the SQL Express instance on each node? If you want to do reads from the node using TDS (i.e. ADO.Net or OLEDB), then why do you need a SQL Express instance on the node? If you want involve the SQL Express instances into linked servers (distributed queries) or other forms of DTC, then you will be way better to route the messages instead.

HTH,
~ Remus

|||

As for ExternalActivator, I'll go back and examine that one again. I was under the impression that I could only have 1 external "activator" listening to the events. In my case, I'd have a "farm" of "external" processes listening for events to process.

As for Remus's reply, I was looking to solve the problem without developing anything beyond a "procedure" for activation on the processing node itself. I was trying to avoid the creation of an "service" (i.e. Windows Service) or some similar mechanism polling the main database cluster for jobs. I was hopign to send some form of "wake up" or "heart beart" to the processing nodes to tell them to go out and find work & process through it..... Something more event driven than polling based.

|||Unfortunately, there's no way for a procedure to read a message from a queue on a remote machine. I think you really need to have your processing nodes open a connection to the database where the queue is and receive messages. This is actually much simplere than any of the solutions suggested so far. you just write a program that opens a connection, receives messages from the queue, processes then and then receives more messages. If you need more processing, just start up more jobs. If one of the processing nodes goes down, the connections will be broken, the transactions will roll back, the messages will appear on the queue again and another node can pick them up. This is absloutely the most efficient way to do load balancing because the processing nodes only pull as much work off the queue as they can handle. As soon as their done, they pull more, if there's no work left, they wait on a receive so no resources are wasted. If something goes wrong with a processing application or node, the transaction rolls back and another node or application picks it up. If instead you pull messages out into a local database on each processing node to process them, if one of the processing nodes goes down, it takes the messages with it and they don't get handled until the node comes back up. They aren't lost but they are delayed.|||

I follow you there Roger. Let me run this past you then.

The folks who preceded me on this effort had went about their selection of Broker to avoid going down the road of developing a "service" on each node that "polls" a simple table sitting on the server. Essentially, your proposed solution is doing the same, correct? It is "polling" a broker queue rather than a table with the benefits of conversation grouping, sequencing, etc. All of those are "gains" from the use of Broker over a table. Additionally there is this "WAITFOR" syntax being used. It is this "WAITFOR" syntax that I'm thinking justifies use of broker over the traditional polling approach.

From a resource friendliness view of the world, is Broker (A) the same in server resource utilization or (B) more efficient -- than the traditional approach of polling/updating a "jobs" table. Being that processing cycles / IO on your cluster are one of your most expensive resources to implement, we are looking to ensure we are using those resources wisely. The previous thought process was that polling would be hammering on any already very busy database.

|||Polling is efficient only if there's always something in the table to select each time. A select that returns nothing is a waste of resources so unless the incoming rate is at least as large as your maximum processing rate,polling is inefficient. WAITFOR aleviates this by not returning until there is something to return. Service Broker is optimized for pulling a message from the queue using the least possible resources. Because Service Broker is part of the database, it is able to do things directly that an external application can't.