Network Connects to Wrong SQL Instance

I have a server running Windows Server 2003 with two instances of SQL Server
2000 - a default instance and a named instance I shall call "INSTANCE_A".
Locally, SQL Server works correctly. Query Analyzer connects to both
instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
network, when I connect to default instance SERVER it actually connects to
SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is unchange
d
and works correctly. Port numbers are set correctly to 1433 and 1226
respectively. The server has been rebooted several times - no change. All
network clients are behaving the same - they all see SERVER\INSTANCE_A when
pointed to SERVER. Any ideas on what's gone wrong?"Jonno" <Jonno@.discussions.microsoft.com> wrote in message
news:57B7981D-0750-43F9-8BB5-F39B4A581570@.microsoft.com...
>I have a server running Windows Server 2003 with two instances of SQL
>Server
> 2000 - a default instance and a named instance I shall call "INSTANCE_A".
> Locally, SQL Server works correctly. Query Analyzer connects to both
> instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
> network, when I connect to default instance SERVER it actually connects to
> SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is
> unchanged
> and works correctly. Port numbers are set correctly to 1433 and 1226
> respectively. The server has been rebooted several times - no change. All
> network clients are behaving the same - they all see SERVER\INSTANCE_A
> when
> pointed to SERVER. Any ideas on what's gone wrong?
Validate that the remote clients are connecting over TCP/IP.
Validate the ports the instances are using by looking into the log file for
each intance.
David|||Thanks David
Both instances have TCP/IP only.
Log files show that both instances are listening on TCP, with default
instance listening on port 1433, and INSTANCE_A listening on port 1226.
Is it possible the publishing of the default instance has become corrupted?
I've tried re-registering from the client side, but the problem persists.
"David Browne" wrote:

> "Jonno" <Jonno@.discussions.microsoft.com> wrote in message
> news:57B7981D-0750-43F9-8BB5-F39B4A581570@.microsoft.com...
>
> Validate that the remote clients are connecting over TCP/IP.
> Validate the ports the instances are using by looking into the log file fo
r
> each intance.
>
> David
>
>

Network Connects to Wrong SQL Instance

I have a server running Windows Server 2003 with two instances of SQL Server
2000 - a default instance and a named instance I shall call "INSTANCE_A".
Locally, SQL Server works correctly. Query Analyzer connects to both
instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
network, when I connect to default instance SERVER it actually connects to
SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is unchanged
and works correctly. Port numbers are set correctly to 1433 and 1226
respectively. The server has been rebooted several times - no change. All
network clients are behaving the same - they all see SERVER\INSTANCE_A when
pointed to SERVER. Any ideas on what's gone wrong?"Jonno" <Jonno@.discussions.microsoft.com> wrote in message
news:57B7981D-0750-43F9-8BB5-F39B4A581570@.microsoft.com...
>I have a server running Windows Server 2003 with two instances of SQL
>Server
> 2000 - a default instance and a named instance I shall call "INSTANCE_A".
> Locally, SQL Server works correctly. Query Analyzer connects to both
> instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
> network, when I connect to default instance SERVER it actually connects to
> SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is
> unchanged
> and works correctly. Port numbers are set correctly to 1433 and 1226
> respectively. The server has been rebooted several times - no change. All
> network clients are behaving the same - they all see SERVER\INSTANCE_A
> when
> pointed to SERVER. Any ideas on what's gone wrong?
Validate that the remote clients are connecting over TCP/IP.
Validate the ports the instances are using by looking into the log file for
each intance.
David|||Thanks David
Both instances have TCP/IP only.
Log files show that both instances are listening on TCP, with default
instance listening on port 1433, and INSTANCE_A listening on port 1226.
Is it possible the publishing of the default instance has become corrupted?
I've tried re-registering from the client side, but the problem persists.
"David Browne" wrote:
> "Jonno" <Jonno@.discussions.microsoft.com> wrote in message
> news:57B7981D-0750-43F9-8BB5-F39B4A581570@.microsoft.com...
> >I have a server running Windows Server 2003 with two instances of SQL
> >Server
> > 2000 - a default instance and a named instance I shall call "INSTANCE_A".
> > Locally, SQL Server works correctly. Query Analyzer connects to both
> > instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
> > network, when I connect to default instance SERVER it actually connects to
> > SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is
> > unchanged
> > and works correctly. Port numbers are set correctly to 1433 and 1226
> > respectively. The server has been rebooted several times - no change. All
> > network clients are behaving the same - they all see SERVER\INSTANCE_A
> > when
> > pointed to SERVER. Any ideas on what's gone wrong?
>
> Validate that the remote clients are connecting over TCP/IP.
> Validate the ports the instances are using by looking into the log file for
> each intance.
>
> David
>
>

Nested Transactions

Hello! Sorry if I choose wrong forum for this post.
I have next scenario:

Transaction1

Transaction2

Commit Transaction2
Transaction3

Commit Transaction3

Commit Transaction1 I wanna implement it in C# code (.NET 1.1, MS SQL 2000):

IDbConnection connection = new OleDbConnection(connectionString);

IDbTransaction transaction = null;

connection.Open();

/* NOTE: I can't use something like this:

* transaction outter = connection.BeginTransaction();

* transacrion inner = connection.BeginTransaction();

* // Here I'm getting an error: OleDB doesn't support parallel transactions,

* // though I wanna create nested one.

*/

// So, I decided to turn implicit transactions mode on in hope it should help:

IDbCommand bt = connection.CreateCommand();

bt.CommandText = " SET IMPLICIT_TRANSACTIONS ON; BEGIN TRANSACTION;";

bt.ExecuteNonQuery();

transaction = connection.BeginTransaction();

IDbCommand command = connection.CreateCommand();

command.Transaction = transaction;

command.CommandType = CommandType.Text;

command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description');";

command.ExecuteNonQuery();

command.CommandText = "SELECT @.@.TRANCOUNT;";

int transCount = (int)command.ExecuteScalar(); // It's equal to 2 here, seems to be OK.

transaction.Commit();

// Let's start the second "nested" transaction

IDbTransaction transaction1 = connection.BeginTransaction();

IDbCommand command1 = connection.CreateCommand();

command1.Transaction = transaction1;

command1.CommandType = CommandType.Text;

command1.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description');";

command1.ExecuteNonQuery();

command1.CommandText = " SELECT @.@.TRANCOUNT; ";

transCount = (int)command1.ExecuteScalar(); // WOW! Now it's already equal to 1 here.

transaction1.Commit();

// Well, here I wanna close outter transaction, but... I'll get exception: There is nothing to commit here

bt = connection.CreateCommand();

bt.CommandText = "Commit TRANSACTION";

bt.ExecuteNonQuery();

Well, I know that SQL Server has no support for nested transactions. Nesting of transactions only increments @.@.TRANCOUNT and it is the final commit that has control over the outcome of the entire transaction. And I can't use the new TransactionScope class in .NET Framework 2.0 which has promotable transactions concept.

Please help me: How can I implement required operations?

You could try using a ServicedComponent and using COM+ functionalities to perform that type of transactions. I don't know if it will work well with OleDB - you just have to try it.|||

Thank you Miguelb for reply. I found solution much simpler :). If somebody find it helpful this is it:

public static void InitiateTransactionsChain(string connectionString)
{
using (IDbConnection connection = new OleDbConnection(connectionString))
{
IDbCommand mostOutter = null;
connection.Open();
try
{
connection.Open();

mostOutter = connection.CreateCommand();
mostOutter.CommandText = "SET IMPLICIT_TRANSACTIONS ON;"; // There is no BEGIN TRANSACTION here
mostOutter.ExecuteNonQuery();
ExecuteNestedTrans(connection, 100, "help me"); // see below
ExecuteNestedTrans(connection, 101, "hope it is OK"); // see below
// If any exception occurs previous transactions will be rolled back!
// ExecuteNestedTrans(connection, 102, null); // see below
mostOutter = connection.CreateCommand();
mostOutter.CommandText = "Commit TRANSACTION";
mostOutter.ExecuteNonQuery();
}
catch (Exception ex)
{
// if something goes wrong, we can easy roll back everything :
if (mostOutter == null)
return;
mostOutter = connection.CreateCommand();
mostOutter.CommandText = "ROLLBACK TRANSACTION";
mostOutter.ExecuteScalar(); // That's all...
}
}
}
// Here is ExecuteNestedTrans:
public static void ExecuteNestedTrans(IDbConnection connection, int value, string description)
{
IDbCommand fakeTransaction = null;
// Let's cheat here: Increase the counter (@.@.TRANCOUNT):
fakeTransaction = connection.CreateCommand();
fakeTransaction.CommandText = "BEGIN TRANSACTION;";
fakeTransaction.ExecuteNonQuery();

IDbTransaction innerTransaction = null;
try
{
innerTransaction = connection.BeginTransaction();
IDbCommand command = connection.CreateCommand();
// Somewhere here transcount somehow will be decremented by 1
// Strange, isn't it?
command.Transaction = innerTransaction;
command.CommandType = CommandType.Text;
command.CommandText = String.Format("Insert into Region (RegionID, RegionDescription) VALUES ({0}, '{1}');", value, description);
command.ExecuteNonQuery();
innerTransaction.Commit();
}
catch (Exception exInner)
{
if (innerTransaction != null)
{
innerTransaction.Rollback();
throw exInner;
}
}
// NOTE: There is no need to commit fakeTransaction.
// It will be commited by something somewhere deep in .NET
// That is why code below is commented
//fakeTransaction = connection.CreateCommand();
//fakeTransaction.CommandText = "COMMIT TRANSACTION;";
//fakeTransaction.ExecuteNonQuery();
if (description == null)
throw new Exception("He-he!");
}

That's all :). Thanks for the time You spent for me.

Nested Transaction

Hi,
I wanted to know why the nested transaction is used.
If I am not wrong, what I understand by the below SQL is that the
"commit tran outer1"
should be run to commit the entire transaction and to release the locks
hold by transaction.
and if the rollback happens anywhere, the entire transaction is rolled
back.
As per my current understanding I don't see any use for nested
transaction.
And also what is the significance of using savepoint in transaction?
Begin tran outer1
Update table1 set column1 = 45 where column2 = 56
begin tran outer2
Update table2 set column1 = 45 where column2 = 56
commit tran outer2
Update table3 set column1 = 45 where column2 = 56
commit tran outer1
ThanksHi,
If you use a save point you can rollbackup or commit based on the method you
do the save transaction. The savepoint will define a location
to which a transaction can return if the part of transaction is cancelled.
But in your example you have not Save point for that you have to use
SAVE TRAN <Tran Name>
See details for Begin tran, Commit Tran, Rollback and Save Tran in books
online.
In your case nested tran is not required. see the below example form books
online for nested trans.
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
Thanks
Hari
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156943181.580372.44710@.i42g2000cwa.googlegroups.com...
> Hi,
> I wanted to know why the nested transaction is used.
> If I am not wrong, what I understand by the below SQL is that the
> "commit tran outer1"
> should be run to commit the entire transaction and to release the locks
> hold by transaction.
> and if the rollback happens anywhere, the entire transaction is rolled
> back.
> As per my current understanding I don't see any use for nested
> transaction.
> And also what is the significance of using savepoint in transaction?
> Begin tran outer1
> Update table1 set column1 = 45 where column2 = 56
> begin tran outer2
> Update table2 set column1 = 45 where column2 = 56
> commit tran outer2
> Update table3 set column1 = 45 where column2 = 56
> commit tran outer1
>
> Thanks
>

Nested Transaction

Hi,
I wanted to know why the nested transaction is used.
If I am not wrong, what I understand by the below SQL is that the
"commit tran outer1"
should be run to commit the entire transaction and to release the locks
hold by transaction.
and if the rollback happens anywhere, the entire transaction is rolled
back.
As per my current understanding I don't see any use for nested
transaction.
And also what is the significance of using savepoint in transaction?
Begin tran outer1
Update table1 set column1 = 45 where column2 = 56
begin tran outer2
Update table2 set column1 = 45 where column2 = 56
commit tran outer2
Update table3 set column1 = 45 where column2 = 56
commit tran outer1
ThanksHi,
If you use a save point you can rollbackup or commit based on the method you
do the save transaction. The savepoint will define a location
to which a transaction can return if the part of transaction is cancelled.
But in your example you have not Save point for that you have to use
SAVE TRAN <Tran Name>
See details for Begin tran, Commit Tran, Rollback and Save Tran in books
online.
In your case nested tran is not required. see the below example form books
online for nested trans.
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
Thanks
Hari
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156943181.580372.44710@.i42g2000cwa.googlegroups.com...
> Hi,
> I wanted to know why the nested transaction is used.
> If I am not wrong, what I understand by the below SQL is that the
> "commit tran outer1"
> should be run to commit the entire transaction and to release the locks
> hold by transaction.
> and if the rollback happens anywhere, the entire transaction is rolled
> back.
> As per my current understanding I don't see any use for nested
> transaction.
> And also what is the significance of using savepoint in transaction?
> Begin tran outer1
> Update table1 set column1 = 45 where column2 = 56
> begin tran outer2
> Update table2 set column1 = 45 where column2 = 56
> commit tran outer2
> Update table3 set column1 = 45 where column2 = 56
> commit tran outer1
>
> Thanks
>sql

Nested Select - Help

I dont have a clue what i'm doing wrong.

SELECT Tbl_Region.REGION, [NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], [NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], [TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], [TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM (((Tbl_Region LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg, Count(qry_New_Members_HMO_All_Regions_1.CONTRACT_N UM) AS [# of New Members]
FROM (SELECT tbl_hmo.Reg, tbl_hmo.CONTRACT_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo.reg, tbl_hmo.CONTRACT_NUM

) AS qry_New_Members_HMO_All_Regions_1

GROUP BY qry_New_Members_HMO_All_Regions_1.reg
) AS NEW_HMO_CONTRACTS ON Tbl_Region.REGION = [NEW_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_New_Members_HMO_All_Regions_1.reg, Count(qry_New_Members_HMO_All_Regions_1.MEMBER_NUM ) AS [# of New Members]
FROM (SELECT tbl_hmo.reg, tbl_hmo.MEMBER_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo_History.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo.Aff_Area, tbl_hmo.MEMBER_NUM

) AS qry_New_Members_HMO_All_Regions_1
GROUP BY qry_New_Members_HMO_All_Regions_1.reg) AS 4_NEW_HMO_MEMBERS ON Tbl_Region.REGION = [4_NEW_HMO_MEMBERS].reg) LEFT JOIN (SELECT qry_Termed_Contracts_HMO_All_Regions_1.reg, Count(qry_Termed_Contracts_HMO_All_Regions_1.CONTR ACT_NUM) AS [# of Termed Contracts]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
) AS qry_Termed_Contracts_HMO_All_Regions_1
GROUP BY qry_Termed_Contracts_HMO_All_Regions_1.reg) AS TERMED_HMO_CONTRACTS ON Tbl_Region.REGION = [TERMED_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_Termed_Members_HMO_All_Regions_1.reg, Count(qry_Termed_Members_HMO_All_Regions_1.MEMBER_ NUM) AS [# of Termed Members]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
) AS qry_Termed_Members_HMO_All_Regions_1
GROUP BY qry_Termed_Members_HMO_All_Regions_1.reg)
AS TERMED_HMO_MEMBERS ON Tbl_Region.REGION = [TERMED_HMO_MEMBERS].reg;

error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'AS'.I haven't a clue what you are doing right. Are you getting paid by the parenthesis?

"WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))"?

Isn't this:

"WHERE tbl_hmo_History.CONTRACT_NUM Is Null"

...simpler and easier to read?

And I suspect this may be throwing your first error:

"LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg,..."

The square brackets denote a database object. SELECT is a statement, not an object.

Clean up your code, format it well with indents, and try running the individual components separately before putting them all together. That is the best way to debug.|||it was working in access then i brought it over to sql and then MESS...

Are you getting paid by the parenthesis? -- haha i wish.

i will work through it again tomorrow.

thanks for looking at it.|||THAT was working in ACCESS?!

...but that explains the square brackets. I've had Access throw those into free SQL querys before, and then the query won't work until you take them out again. A bug, for sure.

Was it a single free SQL statement, or were the subqueries manifested as independent views?|||I got it to work.
PLEASE let me know if you see anything wrong with what i did.

SELECT dbo.Tbl_Region.REGION_NAME, [4_NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS],
[4_NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS],
[5_TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS],
[5_TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM dbo.Tbl_Region LEFT OUTER JOIN
(SELECT qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.CONTR ACT_NUM)
AS [# of New Members]
FROM (SELECT tbl_HMO.Region, tbl_HMO.CONTRACT_NUM
FROM tbl_HMO LEFT JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.CONTRACT_NUM = tbl_HMO_History.CONTRACT_NUM
WHERE tbl_HMO_History.CONTRACT_NUM IS NULL
GROUP BY tbl_HMO.Region, tbl_HMO.CONTRACT_NUM) AS qry_New_Members_HMO_All_Region_Names_1
GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_CONTRACTS] ON
dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_CONTRACTS].Region LEFT OUTER JOIN
(SELECT qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.MEMBE R_NUM)
AS [# of New Members]
FROM (SELECT tbl_HMO.Region, tbl_HMO.MEMBER_NUM
FROM tbl_HMO Left JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
WHERE tbl_HMO_History.CONTRACT_NUM IS NULL
GROUP BY tbl_HMO.Region, tbl_HMO.MEMBER_NUM) AS qry_New_Members_HMO_All_Region_Names_1
GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_MEMBERS] ON
dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_MEMBERS].Region Left OUTER JOIN
(SELECT qry_Termed_Contracts_HMO_All_Region_Names_1.Region ,
COUNT(qry_Termed_Contracts_HMO_All_Region_Names_1. CONTRACT_NUM) AS [# of Termed Contracts]
FROM (SELECT dbo.tbl_HMO_History.Region, dbo.tbl_HMO_History.Contract_Num
FROM dbo.tbl_HMO RIGHT OUTER JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
dbo.tbl_HMO.CONTRACT_NUM = dbo.tbl_HMO_History.Contract_Num
GROUP BY dbo.tbl_HMO_History.Region, dbo.tbl_HMO.CONTRACT_NUM, dbo.tbl_HMO_History.Contract_Num
HAVING (dbo.tbl_HMO.CONTRACT_NUM IS NULL)) AS qry_Termed_Contracts_HMO_All_Region_Names_1
GROUP BY qry_Termed_Contracts_HMO_All_Region_Names_1.Region ) [5_TERMED_HMO_CONTRACTS] ON
dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_CONTRACTS].Region Left OUTER JOIN
(SELECT qry_Termed_Members_HMO_All_Region_Names_1.Region, COUNT(qry_Termed_Members_HMO_All_Region_Names_1.ME MBER_NUM)
AS [# of Termed Members]
FROM (SELECT tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM
FROM tbl_HMO RIGHT JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
WHERE tbl_HMO.MEMBER_NUM IS NULL
GROUP BY tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM) AS qry_Termed_Members_HMO_All_Region_Names_1
GROUP BY qry_Termed_Members_HMO_All_Region_Names_1.Region) [5_TERMED_HMO_MEMBERS] ON
dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_MEMBERS].Region

Nested select

Can you tell me whats wrong with this query?
select count(*) as CountedOrders
from
(
select distinct [order]
from [OrdersTable]
where
[Customer]='100000' and
[Order Date] between '01/01/2005' and '31/12/2005'
)
It runs perfect in MS Access but not in MS SQL-Server.
I need to create a stored procedure that returns the number of orders from a
specific customer on a specific period.
Thanks in advance for your help.
"John" <John@.discussions.microsoft.com> wrote in message
news:E00259AC-FF4E-44D6-8730-5800CFDAC22A@.microsoft.com...
> Can you tell me whats wrong with this query?
> select count(*) as CountedOrders
> from
> (
> select distinct [order]
> from [OrdersTable]
> where
> [Customer]='100000' and
> [Order Date] between '01/01/2005' and '31/12/2005'
> )
> It runs perfect in MS Access but not in MS SQL-Server.
> I need to create a stored procedure that returns the number of orders from
> a
> specific customer on a specific period.
> Thanks in advance for your help.
SQL requires an alias for the derived table. Also ORDER has to be delimited
because it's a keyword (and therefore not a good choice for a column name).
It's also good practice to use a locale-independent date format like I have
done below.
SELECT COUNT(*) AS countedorders
FROM
(SELECT DISTINCT [order]
FROM OrdersTable
WHERE customer='100000'
AND [order date] BETWEEN '20050101' AND '20051231'
) AS T ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
(snip)
>SELECT COUNT(*) AS countedorders
> FROM
> (SELECT DISTINCT [order]
> FROM OrdersTable
> WHERE customer='100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> ) AS T ;
Hi David (& John),
Or even shorter:
SELECT COUNT(DISTINCT [order])
FROM OrdersTable
WHERE customer = '100000'
AND [order date] BETWEEN '20050101' AND '20051231'
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
|||Ο χρ?στη? "Hugo Kornelis" Xγγραψε:

> On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
> (snip)
> Hi David (& John),
> Or even shorter:
> SELECT COUNT(DISTINCT [order])
> FROM OrdersTable
> WHERE customer = '100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
>
Thanks you both David & Hugo!
Much appreciated!
John
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:316912561c41ogeut35basn6lq4h00n58j@.4ax.com...
> On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
> (snip)
> Hi David (& John),
> Or even shorter:
> SELECT COUNT(DISTINCT [order])
> FROM OrdersTable
> WHERE customer = '100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
I wonder if Order has any nulls?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Mon, 13 Mar 2006 21:02:57 -0000, David Portas wrote:
(snip)
>I wonder if Order has any nulls?
Hi David,
Good catch - I hadn't though of that.
(But in a table called "OrdersTable", I would really *hope* that the
Order column is NOT NULL...)
Hugo Kornelis, SQL Server MVP
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:jjvb12lparlmthnnllpsvrotgleknpm3ra@.4ax.com...
> (But in a table called "OrdersTable", I would really *hope* that the
> Order column is NOT NULL...)
>
Hugo, I would hope so too. But if the table is truly called "OrdersTable"
then I would *expect* that anything is possible. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Nested Loop in SQL Query

Hi,
I'm probably missing something obvious (either that or doing this totally wrong).
I'm trying to use a nested loop to generate the following results:
Unit Day1 Day2 Day3 Day4 Day5
Name1 25 45 89 54 76
Name2 48 54 81 74 98
What I have so far is this:
WHILE @.FCount < @.TotalFoodUnits
BEGIN
SELECT
(SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@.FCount + 1)) AS Unit

WHILE @.FDCount < @.Days
BEGIN
SELECT
(SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @.FDCount, @.pdStartDate)) AS Rev
SET @.FDCount = @.FDCount + 1
END
SET @.FCount = @.FCount + 1
END
Any suggestions pleaseThis won't do what you want. It looks like you need to do a pivot-table type query that aggregates your data. With loops, you're just going to generate a whole bunch of separate result sets. Doing a pivot type table is difficult if you don't know how many columns you'll have. One thing I've done is create a dynamic SQL statement with cursors that pivots them all together. When the statement is built, it looks like it was basically hardcoded, but it is correct for the data as it is when it is run. The cursor(s) you need depend on what the varying numbers of rows or columns are that you have. Build your SQL statement by looping through the cursors as needed. See if you can hardcode a statement that will work and then look to see what the repeating elements are and how would you get those repeating elements with a SQL statement. You may need to query schema tables to get field names or use DISTINCT to get what values you have. I know thi sisn't very clear, but if you hardcode it and then see how that statement repeats, you might see what I'm talking about.
|||Along the lines of what PDraigh has advised, you might find this post helpful:http://forums.asp.net/1041295/showpost.aspx
|||Hi,
Thanks for the info above, it actually led me to this page:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
This provides an alternative solution which pivots the data using .net rather than trying to force sql into doing it for you.

nested for loop with xquery

What is wrong with this snippet of XQuery like this
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after itThe problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegroups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The problem is that you have too many {}... {} are used in XQuery to switc
h
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES
TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.0499
90.46950@.l53g2000cwa.googlegroups.com...
>
>
>|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType> )
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterTyp
e)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

nested for loop with xquery

What is wrong with this snippet of XQuery like this
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after it
The problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegro ups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>
|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> The problem is that you have too many {}... {} are used in XQuery to switch
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.049990.46950@.l53g2000cwa.go oglegroups.com...
>
|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType>)
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:[vbcol=seagreen]
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>