Showing posts with label following. Show all posts
Showing posts with label following. Show all posts

Thursday, March 29, 2012

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 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 partitioned view pblm

i have the following simple tables and partitioned views ...all is fine
until i try to i/u/d using the distributed part. view...tucker2 and jag are
sqlserver enterprise edition, windows 2k server...
i get ==> union all view 'customers' in not updateable because the defintion
contains
a disallowed construct (42000,4416)
what am i missing' thx, chester
-- On Server1 - tucker2
drop TABLE dbo.Customers_tucker2
CREATE TABLE Customers_tucker2
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 99999),
cust_name varchar(30) NOT NULL,
state_cd char(2) NOT NULL
)
insert into customers_tucker2 values (11101, 'chet1', 'tx')
insert into customers_tucker2 values (11102, 'chet2', 'xx')
insert into customers_tucker2 values (11103, 'chet3', 'tv')
insert into customers_tucker2 values (11104, 'chet4', 'vv')
insert into customers_tucker2 values (11105, 'chet5', 'zz')
-- On Server2 - jag
drop TABLE Customers_jag;
CREATE TABLE Customers_jag
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 100000 AND 999999),
cust_name varchar(30) NOT NULL,
state_cd char(2) NOT NULL
)
insert into customers_jag values (111001, 'chet41', 'tt')
insert into customers_jag values (111002, 'chet42', 'rr')
insert into customers_jag values (111003, 'chet43', 'dd')
insert into customers_jag values (111004, 'chet44', 'gg')
insert into customers_jag values (111005, 'chet45', 'ff')
/*************************************
run the following on both servers
************************************/
drop view customers
go
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
select top 2 * from jag.pubs.dbo.customers_jag
select top 2 * from tucker2.pubs.dbo.customers_tucker2
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
SET XACT_ABORT ON
go
create view customers
as
select customerid, cust_name, state_cd
from tucker2.pubs.dbo.customers_tucker2
union all
select customerid, cust_name, state_cd
from jag.pubs.dbo.customers_jag
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
select min(customerid), max(customerid) from customers
select * from customers
where customerid in (111005,11101)
/* the following 3 update/insert/del fail with msg:
union all view 'customers' in not updateable because the defintion contains
a disallowed construct (42000,4416)
*/
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
SET XACT_ABORT ON
update customers
set state_cd = 'la'
where customerid = 11101
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
insert into customers values ( 1122, 'chet', 'tx')
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
SET XACT_ABORT ON
delete from customers
where customerid = 11101Chet,
what for version of sql server are you using? Only the Developer and
Enterprise Editions of SQL Server 2000 allow INSERT, UPDATE, and DELETE
operations on partitioned views.
hth
Quentin
"chet gwin" <cgwin@.houston.rr.com> wrote in message
news:SyGVa.120630$XV.6674033@.twister.austin.rr.com...
> i have the following simple tables and partitioned views ...all is fine
> until i try to i/u/d using the distributed part. view...tucker2 and jag
are
> sqlserver enterprise edition, windows 2k server...
> i get ==> union all view 'customers' in not updateable because the
defintion
> contains
> a disallowed construct (42000,4416)
> what am i missing' thx, chester
>
> -- On Server1 - tucker2
> drop TABLE dbo.Customers_tucker2
> CREATE TABLE Customers_tucker2
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 1 AND 99999),
> cust_name varchar(30) NOT NULL,
> state_cd char(2) NOT NULL
> )
> insert into customers_tucker2 values (11101, 'chet1', 'tx')
> insert into customers_tucker2 values (11102, 'chet2', 'xx')
> insert into customers_tucker2 values (11103, 'chet3', 'tv')
> insert into customers_tucker2 values (11104, 'chet4', 'vv')
> insert into customers_tucker2 values (11105, 'chet5', 'zz')
>
> -- On Server2 - jag
> drop TABLE Customers_jag;
> CREATE TABLE Customers_jag
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 100000 AND 999999),
> cust_name varchar(30) NOT NULL,
> state_cd char(2) NOT NULL
> )
>
> insert into customers_jag values (111001, 'chet41', 'tt')
> insert into customers_jag values (111002, 'chet42', 'rr')
> insert into customers_jag values (111003, 'chet43', 'dd')
> insert into customers_jag values (111004, 'chet44', 'gg')
> insert into customers_jag values (111005, 'chet45', 'ff')
> /*************************************
> run the following on both servers
> ************************************/
> drop view customers
> go
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> select top 2 * from jag.pubs.dbo.customers_jag
> select top 2 * from tucker2.pubs.dbo.customers_tucker2
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> SET XACT_ABORT ON
> go
> create view customers
> as
> select customerid, cust_name, state_cd
> from tucker2.pubs.dbo.customers_tucker2
> union all
> select customerid, cust_name, state_cd
> from jag.pubs.dbo.customers_jag
>
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> select min(customerid), max(customerid) from customers
> select * from customers
> where customerid in (111005,11101)
> /* the following 3 update/insert/del fail with msg:
> union all view 'customers' in not updateable because the defintion
contains
> a disallowed construct (42000,4416)
> */
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> SET XACT_ABORT ON
> update customers
> set state_cd = 'la'
> where customerid = 11101
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> insert into customers values ( 1122, 'chet', 'tx')
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> SET XACT_ABORT ON
> delete from customers
> where customerid = 11101
>
>sql

Distributed FULL OUTER JOIN results in INNER join

Hi,
I am trying to perform a full outer join across two linked
servers - LINK1 and LINK2. If I put the following query in:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
- the query plan gives me a MERGE JOIN/FULL OUTER JOIN
which is what I expect. If, however, I add a WHERE clause:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
WHERE C1.r = 0 and C2.r = 0
- I get a MERGE JOIN/INNER JOIN, which gives me the wrong
result. The table definition is:
create table test (n char(8) not null, s int not null, r
tinyint not null)
go
alter table test add primary key (n,s)
go
I have obviously simplified the problem as much as
possible. Am I misunderstanding something about FULL OUTER
JOINS or is this a feature? I can obviously circumvent by
selecting into two temporary tables on the local server
and doing the join there.
regards
KenMany thanks
Ken
>--Original Message--
>The problem is nothing to do with the query being a
distributed one. Because
>your WHERE clause references both sides of the join, the
result set will
>include only the INNER joined rows - WHERE C1.r = 0 AND
C2.r = 0 means that
>both r columns must be non-NULL in the result. Perhaps
what you intended
>was:
>...
>WHERE COALESCE(C1.r, C2.r)=0 AND COALESCE(C2.r, C1.r)=0
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

Tuesday, March 27, 2012

distinct: removing dups

should be a simple resolution but I'm not familiar enough...

I have the following [simplified] query which generates duplicate rows that I need to get rid of.

SELECT MY_ID, DESCRIPTION, NAME
FROM MYTABLE

When I insert the distinct command, DB2 tells me:
SQL0134N Improper use of a string column, host variable, constant, or
function "DESCRIPTION". SQLSTATE=42907

The datatype of DESCRIPTION is LONG VARCHAR and that cannot change nor the need to query that column. It seems this is preventing distinct from working. It will work without DESCRIPTION being pulled, of course, but again - I need that column.

How do I use SQL to remove the duplicates I am getting since distinct seemingly cannot be used in this scenario ? Can a "WHERE" clause somehow help ?

ThanksCan you use:SELECT MY_ID, DESCRIPTION, NAME
FROM MYTABLE
GROUP BY MY_ID, DESCRIPTION, NAME-PatP|||looks like group by doesn't like DESCRIPTION either... same error.

SQL0134N Improper use of a string column, host variable, constant, or
function "DESCRIPTION". SQLSTATE=42907

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
ABC
1ax
2by
3c1
4nullnull
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1sql

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53B523BC4BB04@.SQLMonster.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1

Distinct Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.

Distinct Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.sql

Sunday, March 25, 2012

DISTINCT not working

I'm using the last version of JDBC driver, and the following query is
returning duplicate values:
SELECT DISTINCT COMPANY_ID C1 FROM COMPANY_WORKERS
By if i make de following query, it works:
SELECT A.C1 FROM (SELECT DISTINCT COMPANY_ID C1 FROM COMPANY_WORKERS ) A
Both queries work ok in the SQL Analyzer, but in the code only the second
one works as espected.
Thanks
Gon?alo
Gonalo,
I ran a quick test with the MS driver using SELECT DISTINCT and it worked.
Can you post the code that you are using? Perhaps there is something else
that is happening.
Thanks.
SuePurkis
DataDirect Technologies
"Gonalo" <Gonalo@.discussions.microsoft.com> wrote in message
news:B3FCD484-53E7-42E3-AD22-26236C9F3927@.microsoft.com...
> I'm using the last version of JDBC driver, and the following query is
> returning duplicate values:
> SELECT DISTINCT COMPANY_ID C1 FROM COMPANY_WORKERS
> By if i make de following query, it works:
> SELECT A.C1 FROM (SELECT DISTINCT COMPANY_ID C1 FROM COMPANY_WORKERS ) A
> Both queries work ok in the SQL Analyzer, but in the code only the second
> one works as espected.
> Thanks
> Gonalo
sql

DISTINCT not returning data in sorted order after specific no. of

Hi
I have the problem with the DISTINCT keyword. I used the following statement
to get the distinct values from the table:
SELECT DISTINCT trade_name FROM Customer
This works absolutely perfect getting the distinct values and getting in
sorted order. But my requirement needs the statement to be as :
SELECT DISTINCT RTRIM(trade_name) FROM Customer
With the above statement, it works fine only if the table contains few 100
of records. My table contains around 6000+ records and the above statement
returns DISTINCTINCT values but does not get them in sorted order.
The same statement works fine if the there are around say 500+ records. I
see that the results displayed are distinct and sorted as well.
The field in question (trade_name) of 30 chars (char(30))
Why is this happening.Without ORDER BY the sort order is undefined for any SQL query. Add
ORDER BY:
SELECT DISTINCT trade_name
FROM Customer
ORDER BY trade_name
SELECT DISTINCT RTRIM(trade_name)
FROM Customer
ORDER BY RTRIM(trade_name)
David Portas
SQL Server MVP
--

Distinct list

Let’s say we have two tables with the following values:
Table p1: Product Table p2: Product
-- --
A B
B C
C D
C D
Task #1 - In a single query, generate a unique list of values from both
tables (i.e. A, B, C, D).Hi Danlin,
Select Distinct Product From P1
Union
Select Distinct Product From P2
<Throw in an order by here if you want>
Order By
Product
That should do it.
Richard|||SELECT product
FROM P1
UNION
SELECT product
FROM P2
David Portas
SQL Server MVP
--|||Someone throw me this problem and he wants a single query solution not using
union. Sorry about the confusion. Thanks.
"danlin" wrote:

> Let’s say we have two tables with the following values:
>
> Table p1: Product Table p2: Product
> -- --
> A B
> B C
> C D
> C D
>
> Task #1 - In a single query, generate a unique list of values from both
> tables (i.e. A, B, C, D).
>|||SELECT DISTINCT
COALESCE(P1.product, P2.product)
FROM P1
FULL JOIN P2
ON P1.product = P2.product
--
David Portas
SQL Server MVP
--|||Thanks, you're graet David.
"David Portas" wrote:

> SELECT DISTINCT
> COALESCE(P1.product, P2.product)
> FROM P1
> FULL JOIN P2
> ON P1.product = P2.product
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Thursday, March 22, 2012

Distinct in Select Statement

Hey there, is there a way I can use command such as distinct in a select statement to do the following. Lets say I want to do a search of products based off their location and I want to list the companies that will have products in that area. I only want to list the company once, but if I'm searching by products in the area I might come up with 15 results for that company. I have not written the code yet for this, I'm just planning ahead.

I'm programming using VB so I guess I would do something like this.

State = Trim(Request.QueryString("State"))

SelectStatement = "Select * From Products Where State='" & _
State & "'"

This would of course give me hypothetically speaking a list as long as the amount of products in one given area. Is there a way to cut this down and only list the company once? Any help would be greatly appreciated. Thanks in advance.SELECT DISTINCT(CompanyName)
FROM Company
WHERE Product.State = stateVariable
AND Product.CompanyID = Company.CompanyID|||thanks, I'll give that a shot.

DISTINCT function does not remove the duplicate member

Hi All,

The following query returns 4 members ([Gross Sales], [Trade Sales], [Intercompany Sales], [Trade Sales]) while it should return only 3. DISTINCT function does not remove the duplicated member [Trade Sales].

SELECT

{[Measures].[Amount]}

ON AXIS(0),

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after))

ON AXIS(1)

FROM [ADVENTURE WORKS]

Any inputs will be appreciated.

David.

DISTINCT must follow SELECT:

Code Snippet

SELECT

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after)) ON AXIS(1),

{[Measures].[Amount]}

ON AXIS(0),

FROM [ADVENTURE WORKS]

Adamus

|||

Seems to be a bug, since if you statically list the members, Distinct() seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Distinct({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales],

[Account].[Accounts].[Intercompany Sales],

[Account].[Accounts].[Trade Sales]}) ON AXIS(1)

FROM [ADVENTURE WORKS]

Using Generate() instead of Distinct() also seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Generate(DESCENDANTS({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after),

{[Account].[Accounts].CurrentMember}) ON AXIS(1)

FROM [ADVENTURE WORKS]

|||

Thx, Deepak.

The question remains: is it just a bug or there is some hidden meaning to this behavior?

Curiously, if instead of DISTINCT you apply other functions that should remove duplicates,

the duplicates still remain, for example, UNION with an empty set:

UNION(DESCENDANTS({[Account].[Accounts].[Gross Sales], [Account].[Accounts].[Trade Sales]}, 0, self_and_after), {})

Distinct Count with Junk Dimension Attribute

Hi,

Suppose I have following Model Dimension which is formed based on Junk Dimension definition.

JunkID

ModelID

ModelCode

ModelTypeID

ModelTypeCode

ModelSpecificationID

ModelSpecificationCode

50

50

BAO

2

FFCVT

2

F1C1

53

53

BGO

1

FFAT

1

FW4A4

55

55

NBO

3

FRAT

9

V4A5

60

60

NCO

3

FRAT

9

V5A5

97

55

NBO

3

FRAT

11

V4A5

99

53

BGO

1

FFAT

13

F4A4

And In Fact Table Suppose there is data as below...

JunkID

Amount

50

5210983

53

9576

55

7994

60

7872

97

4306

99

6519

Now my requirement is to make Distinct Count on ModelID. If I makes Distinct Count on DimModel.ModelID then it gives me 5 on every combination in Cube. If I make Distinct Count on FactTable.JunkID then it gives me 6 in places of 5 as above seen data.

Please help, how can I make Distinct Count on ModelID in this situation?

Thanks in Advance

Bhudev

Are you excluding the ALL member? Take a look at the code sample below? It calculates a count 3 ways. In the first, I count the members of the dimension's attribute hierarchy. It includes the members from all levels including the ALL member because I didn't specify a level. In the next one, I specify the leaf level of the hierarchy which does not include the ALL member. The third example uses DISTINCTCOUNT.

Code Snippet

with

member measures.a as

COUNT([Geography].[Country].Members)

member measures.b as

COUNT([Geography].[Country].[Country].Members)

member measures.c as

DISTINCTCOUNT([Geography].[Country].Country.Members)

select

{[a],[b],[c]} on 0

from [Adventure Works]

;

Good luck,
Bryan

|||

Hi,

It works fine when we are making Calculated Members, but when Analysis Services 2005 has introduced Junk Dimension Concept. So I feel it should give facility to add Distinct Count Cube Measure based on its [Junk Dimension] associated Attribute Dimensions. As per my understanding if there is Junk Dimension and we have very important Attribute Dimension, in Junk Dimension, On which I have to add Distinct Count Measure which can be used across all combination, In that situation we can not, because I can add Distinct Count only on JunkID which will be wrong or not of my use, As I have explained above.

But we can add Several Calculated members as per our need.

Thanks Bryan C. Smith for your suggestion overall it was helpfull.

Bhudev

|||

Just to be clear, SSAS 2005 didn't add support for Junk dimensions. Junk dimensions are a concept from the Kimball group that are documented in their "The Data Warehouse Toolkit" and other books. They can be implemented in SSAS 2005 (and just about any other technology that supports regular old dimensions). :-)

B