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

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 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
>--
>
>.
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
ReshmaHi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/d...r />
_2z4x.asp
http://www.microsoft.com/sql/evalua...es/distpart.asp
http://msdn.microsoft.com/library/d...r />
_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
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/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
ReshmaHi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/d...r />
_2z4x.asp
http://www.microsoft.com/sql/evalua...es/distpart.asp
http://msdn.microsoft.com/library/d...r />
_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
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/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
Reshma
Hi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/de...qd_10_2z4x.asp
http://www.microsoft.com/sql/evaluat...s/distpart.asp
http://msdn.microsoft.com/library/de...qd_12_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
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/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

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 w/ character data

Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:

> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql

DISTINCT values from a table

Hi,

I am trying to output a list of data from a table, showing only one record of each TypeID.

So, for instance, I have a simple SQL query that says:

SELECT DISTINCT AlbumTypeIDFROM AlbumORDER BY AlbumTypeIDDESC

This works correctly, and gives a list of 1,2,3. But I need more information than that, I want the Description field output with the ID, but how can I do this without assigning that to be Distinc also?

When I try: SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESC

The output is completely wrong.

Many thanks

My guess is that Description doesn't belong to AlbumTypeID, but to something like AlbumID. If you have an AblumType table with an AlbumTypeID and a Description, change you query to run against that table instead:

SELECT DISTINCT AlbumTypeID, Description FROM AlbumType ORDER BY AlbumTypeID DESC

|||

SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESC

Presumably you have lots of album types and lots of descriptions, so this query will only return results where the combination of the two fields is different to all the other results. For example, two AlbumTypeId of 1 records with a description of "Cars" would result in a single record.

What did you want to get back?

|||

Try

SELECT DISTINCT dbo.GetFirstAlbumDescription(AlbumTypeId), AlbumTypeId FROM Album
where
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetFirstAlbumDescription
(
@.AlbumTypeId INT
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @.RETURN VARCHAR(100)
SELECT @.RETURN = DESCRIPTION FROM Album WHERE Id = (SELECT Min(Id) FROM Album WHERE AlbumTypeId = @.AlbumTypeId)
RETURN @.RETURN
END
GO|||When I ran it I got
AlbumTypeId
-------- ----
Album 1 of 1 1
Album 1 of 2 2
Album 1 of 3 3

(3 row(s) affected) [excess apces deleted]|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Using the additional function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[GetFirstAlbumId]
(
@.AlbumTypeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.RETURN INT
SELECT @.RETURN = Id FROM Album WHERE Id = (SELECT Min(Id) FROM Album WHERE AlbumTypeId = @.AlbumTypeId)
RETURN @.RETURN
END

SELECT DISTINCT dbo.GetFirstAlbumId(AlbumTypeId) as a, dbo.GetFirstAlbumDescription(AlbumTypeId) as B, AlbumTypeId FROM Album

gave

a B AlbumTypeId
---- ---------- ----
1 Album 1 of 1 1
3 Album 1 of 2 2
6 Album 1 of 3 3

|||Can you provide some more sample data with same AlbumId and different AlbumTypeId's..with expected output.|||

SELECT *

FROM Albumns a

JOIN (

SELECT AlbumnTypeID,MIN(AlbumnID) AS LowestAlbumnID

FROM Albumns

GROUP BY AlbumnTypeID) t1 ON a.AlbumnID=t1.LowestAlbumnID

DISTINCT Values

I am trying to run a query to one of two delete duplicates records. The
process I normally use is use
1) SELECT DISTINCT from the table into a second table
2) Delete all duplicate values in original table
3) Copy the disctinct values from the second table back into the original
table
Unfortunately, this time, my table has ntext fields in it. SELECT DISTINCT
does not work with ntext fields.
Does anyone have an alternative solution?
Thank you,
JLFlemingHave you thought about declaring a PRIMARY KEY?|||I have thought about it. I cannot declare a primary key if there are alread
y
duplicates in the table. Once I get rid of duplicates, I can put a primary
key in.
"--CELKO--" wrote:

> Have you thought about declaring a PRIMARY KEY?
>|||http://www.aspfaq.com/2431
http://www.aspfaq.com/2509
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:FAF44802-6129-435D-B7AE-EBBC0C042ECD@.microsoft.com...
> I am trying to run a query to one of two delete duplicates records. The
> process I normally use is use
> 1) SELECT DISTINCT from the table into a second table
> 2) Delete all duplicate values in original table
> 3) Copy the disctinct values from the second table back into the original
> table
> Unfortunately, this time, my table has ntext fields in it. SELECT
> DISTINCT
> does not work with ntext fields.
> Does anyone have an alternative solution?
> Thank you,
> JLFleming

distinct row count in a table.

Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.|||Try this...

SELECT COUNT(*)
FROM
(SELECT DISTINCT * FROM ch1)ch1|||Or
SELECT COUNT(DISTINCT *) AS Distinct_Rows FROM ch1|||:shocked:
select sum(case when count(*)>1 then 1 else 1 end)
from ch1 group by a,b,c,d|||threads merged

ramshree, please do not post the same question into multiple forums|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1I think that you're "close, but no banana" on this... The code that you posted will actually count the non-distinct rows (how many rows have at least one duplicated row elsewhere).

-PatP

Distinct Report Parameter Values

How to display only distinct values/labels in a report parameter drop down?
The values are generated from the main query of the report. Thx. JLYou should have a dataset that is specifically for your report parameter. As
a matter of fact, you have it a little reversed. The report parameters
should be used to limit the query. If you are getting the data and then
using the report parameters to filter the report, you should re-evaluate. In
most cases you should limit the data coming over using query parameters
mapped to report parameters. If you filter the data and the data is of any
significant size you will have performance problems.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CCDA1621-CE1A-4790-8BF7-447456ABB794@.microsoft.com...
> How to display only distinct values/labels in a report parameter drop
> down?
> The values are generated from the main query of the report. Thx. JL|||It's very helpful. That really enlightens me. Now I think I have a lot of
changes to make. Thx. JL
"Bruce L-C [MVP]" wrote:
> You should have a dataset that is specifically for your report parameter. As
> a matter of fact, you have it a little reversed. The report parameters
> should be used to limit the query. If you are getting the data and then
> using the report parameters to filter the report, you should re-evaluate. In
> most cases you should limit the data coming over using query parameters
> mapped to report parameters. If you filter the data and the data is of any
> significant size you will have performance problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "JL" <JL@.discussions.microsoft.com> wrote in message
> news:CCDA1621-CE1A-4790-8BF7-447456ABB794@.microsoft.com...
> > How to display only distinct values/labels in a report parameter drop
> > down?
> > The values are generated from the main query of the report. Thx. JL
>
>

Distinct Records

Hi All,
i want to Write the query which will give me the distinct top 1 records.
see i have my table and data in that table is in this way.
ReqNo Name Other
1 chirag
1 xyz
1 ABC
2 ZZZ
2 YYY
2 QQQ
3 NNN
i want the output as following
1 Chirag
2 ZZZ
3 NNN
the first records of each and every Reqno. i am trying this from a long time
but not able to get the same if you guys has any solution then please help m
e
out.Try this
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from MyTest group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hi
It should be
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from TableName
group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hello Chirag
You can do it as Madhivanan said if you have not created any views based on
this table. If you have any views based on this table then alterting the
table may affect the views. Views become invalid. In that case you can do
like this.
Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
Insert into #Temp Select * From TableName
Select * from #Temp where Slno in (Select min(Slno) from #Temp
group by ReqNo)
#Temp is a temporary table. So it will be deleted automatically.
Thank you
Baiju
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
> Hi
> It should be
> Alter table TableName add sno int identity
> go
> Select * from TableName where sno in (Select min(sno) from TableName
> group
> by ReqNo)
> go
> Alter table TableName drop column sno
>
> Madhivanan
>|||I would suggest you to create a running number and have it permantently.
creating a temp table, inserting data and doing a select on that, deleting
temp table..will be a workable solution..
but if your table has large number of records, this query takes its own
time..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Baiju" <baiju@.indus-systems.com> wrote in message
news:O#r6yd8HFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello Chirag
> You can do it as Madhivanan said if you have not created any views based
on
> this table. If you have any views based on this table then alterting the
> table may affect the views. Views become invalid. In that case you can do
> like this.
> Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
> Insert into #Temp Select * From TableName
> Select * from #Temp where Slno in (Select min(Slno) from #Temp
> group by ReqNo)
> #Temp is a temporary table. So it will be deleted automatically.
> Thank you
> Baiju
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
>|||try
select ReqNo, Max(Name) from ... group by ReqNo
"Chirag" wrote:

> Hi All,
> i want to Write the query which will give me the distinct top 1 records.
> see i have my table and data in that table is in this way.
> ReqNo Name Other
> 1 chirag
> 1 xyz
> 1 ABC
> 2 ZZZ
> 2 YYY
> 2 QQQ
> 3 NNN
> i want the output as following
> 1 Chirag
> 2 ZZZ
> 3 NNN
>
> the first records of each and every Reqno. i am trying this from a long ti
me
> but not able to get the same if you guys has any solution then please help
me
> out.|||If those are the only columns then there's no such thing as the "first"
for each ReqNo. You haven't identified "first" in your table and tables
have no fixed concept of order. The best you can do is probably with an
aggregate function:
SELECT reqno, MIN(name)
FROM YourTable
GROUP BY reqno
David Portas
SQL Server MVP
--