A Common Query Question!

Hi Guys,

I have a query that gives me multiple rows for every customer, where as my requirement is to have one row for each customer. Below are the details:

Customers Table

Cust_ID int (PK)

Cust_Name varchar(255)

Subscription_Status int

Cust_Subscription_Audit Table

ID Identity(PK)

Cust_ID int

Old_Subscription_Status int

New_Subscription_Status int

Audit_Date DateTime

Logins Table (one customer may have multiple logins )

ID Identity (PK)

Cust_ID int

login name varchar(256)

email_ID varchar(256)

F_Name varchar(50)

L_name varchar(50)

role int

Last_Modified DateTime

INDEXES

Logins Table

PK__Clustered Ascending

No Index on Audit Table

Customers Table

PK__Clustered_ Ascending

Below is the query that is being used to extract the data

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID,MAX(CSA.Audit_Date)

FROM Customers C

INNERJOIN Cust_Subscription_Audit CSA ON C.Cust_ID= CSA.Cust_ID

INNERJOIN Logins L ON L.Cust_ID= C.Cust_ID

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUPBY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

This gives me multiple rows for a customer. What I am looking for is

1) One row for each customer.(Query 1)

2) If possible can pick the row of my choice Like log in that was added first or last based on login or Modified date in login table.(Query 2)

3) If possible can pick the row by row number i.e. if there are multiple logins for a customer then the 2nd login. (Query 3)

Your help will be appreciated.

Thanks

-Leo

Can you post some DDL, including constraints and indexes, sample data and expected result, please?

AMB

|||

Hi ,

Thanks for your prompt reply. Here is the Sample Data

Customers Table

Cust_ID Cust_Name SubsStatus

1 abc corp. 7

2 Adventure Works Inc 1

3 MicroCorn Inc 2

4 Fabrikam 7

5 Goleeeee Inc 5

Logins Table

ID Cust ID Login Email f_Name l_Name Role Date_Modified

1 1 rob1 rob1@.hotmail.com Rob Haany 1 2006-02-28 13:24
2 2 scot1 scot1@.yahoo.com Scot claudia 1 2006-05-24 12:30
3 3 scot2 scot2@.hotmail.com Scot Roy 1 2006-05-24 12:30
4 4 rob NULL RON HARTON 1 2006-08-22 15:26
5 4 qsir qsir@.hotmail.com Q Sir 1 2006-09-17 12:23
6 5 nzaar nzaar@.hotmail.com nad zaar 3 2006-09-17 12:23
7 5 jluk jluk@.hotmail.com Ron Richard 1 2006-09-17 12:23
8 2 drase draze@.hotmail.com Dino Mosoli 1 2006-08-22 15:26
9 2 dlins David Lin 3 2006-12-28 13:24
10 4 dmay NULL David May 1 2006-05-24 12:30

Role 1 = admin

Role 3 = Primary Contact

ID Cust_ID Old_Subscription_Status New_Subscription_Status Audit_Date

1 1 3 7 2006-09-24 12:30

2 2 0 1 2006-03-24 02:30

3 3 1 2 2006-08-24 10:30

4 4 1 7 2006-11-24 12:30

5 5 3 5 2006-09-24 12:30

6 4 3 1 2006-10-24 12:30

7 3 0 1 2006-06-24 12:30

8 1 0 1 2006-02-24 12:30

9 1 1 3 2006-07-24 12:30

10 5 1 3 2006-07-24 12:30

The required result is

Cust_ID Cust_Name F_name L_Name Email Audit Date

1 abc Corp Rob Haany rob1@.hotmail.com 2006-09-24 12:30

2 Adventure Works Inc Scot claudia scot1@.yahoo.com 2006-03-24 02:18

3 MicroCorn Inc Scot Roy scot2@.hotmail.com 2006-08-24 10:28

4 Fabrikam Q Sir qsir@.hotmail.com 2006-11-24 12:30

5 Goleeee Inc Ron Richard jluk@.hotmail.com 2006-09-24 12:30

Right now what is happening is threre are more than one records for Cust_ID = 2. The basic question is (Query 1) is how can we restrict the result set to only one row per Cust_ID. So my other questions (Query 2, Query3) were how can we choose one of these records based on some criteria e.g. since more than one records are there for Cust_ID = 2, and I wana choose the one that is with earliest Date_Modified or 2nd earliest Modified date.

Thanks,

-Leo.

|||

hi Leo,

If you are using SQL server 2005 Try this:

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

CROSS APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID ) L

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||

Thank you very much for this nice hint. I tweeked the query little bit and it woked for me here is the modified query.

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

OUTER APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID And [Role] = 1 ) L

WHERE C.Subscription_Status >=1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||do you need OUTER APPLU here? OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function (pretty much like OUTER JOIN in case of normal table). So if there is no match in table 'Logins' the value of L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID will be NULL.

A bad error!

hi friends!
i have a web service in .NET 2003 that contain below error and I cant handle it...
...

Anunhandled exception of type 'System.Web.Services.Protocols.SoapException'occurred in system.web.services.dll

Additionalinformation: System.Web.Services.Protocols.SoapException: Server was unable toprocess request. --> System.Data.SqlClient.SqlException: Login failed foruser 'MICROGOD3\ASPNET'.

atSystem.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,Boolean breakConnection)

atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehaviorrunBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

atSystem.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

atSystem.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnectionowningObject, SqlConnectionString connectionOptions, String newPassword,Boolean redirectedUserInstance)

atSystem.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentityidentity, SqlConnectionString connectionOptions, Object providerInfo, StringnewPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

atSystem.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptionsoptions, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnectionowning Connection)

atSystem.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionowning Connection, DbConnectionPool pool, DbConnectionOptions options)

atSystem.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionowningObject)

atSystem.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionowningObject)

atSystem.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnectionowningObject)

atSystem.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

atSystem.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnectionouterConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

atSystem.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[]datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommandcommand, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSetdataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommandcommand, CommandBehavior behavior)

atSystem.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

at LeitnerWebService.Service1.GetData() inc:\inetpub\wwwroot\leitnerwebservice\service1.asmx.cs:line 68

-- End of inner exception stack trace --

PLEASE HELP ME!The Windows user, the web service runs under on the server, user being MICROGOD3\ASPNET based on the error message, does not have permissions to log into the SQL Server you use. It should have a login in SQL Server and the login should be granted permissions to access the database in question.

64 bit 60 gb db with RAID 10 4 VOLUME

I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
error log file mentioned below, can you please help me
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
than 15 seconds to complete on file
[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11 ] in database
[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The offset
of the latest long I/O is: 0x0000032dac0000
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:926CD051-B1BF-481A-AA4D-BA06AB7DBA72@.microsoft.com...
>I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
> error log file mentioned below, can you please help me
> SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
> than 15 seconds to complete on file
> [E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11 ] in database
> [ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The
> offset
> of the latest long I/O is: 0x0000032dac0000
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
I'd look at the disk array first, make sure you have the latest drivers,
that there's no hardware issues, etc.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Generally indicates a HW problem.
Had this recently, first line of defense is to power-cycle the server,
second is to refresh RAID controller microcode and power-cycle. Doing
this seems to have "fixed" two of our servers where we were getting
this message - except at much, much higher volumes!
J.
On Tue, 27 Feb 2007 19:59:05 -0800, Jay
<Jay@.discussions.microsoft.com> wrote:

>I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
>error log file mentioned below, can you please help me
>SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
>than 15 seconds to complete on file
>[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC1 1] in database
>[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The offset
>of the latest long I/O is: 0x0000032dac0000
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.
|||very very Thanks to Greg and JXStern,
can you provide some links related this issues, so that i can show to my
boss.it will be more helpfull to me.
waiting for you alls reply.
regards
Jay
"JXStern" wrote:

> Generally indicates a HW problem.
> Had this recently, first line of defense is to power-cycle the server,
> second is to refresh RAID controller microcode and power-cycle. Doing
> this seems to have "fixed" two of our servers where we were getting
> this message - except at much, much higher volumes!
> J.
>
> On Tue, 27 Feb 2007 19:59:05 -0800, Jay
> <Jay@.discussions.microsoft.com> wrote:
>
>
|||Me, I don't have anything, just called the sysadmin guys (emailed,
actually), and they told me this story, then did it, and it worked!
Twice!
These are only fairly current HP servers, so the problem is probably
very, very, very common.
J.
On Wed, 28 Feb 2007 06:55:52 -0800, Jay
<Jay@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>very very Thanks to Greg and JXStern,
>can you provide some links related this issues, so that i can show to my
>boss.it will be more helpfull to me.
>waiting for you alls reply.
>regards
>Jay
>
>"JXStern" wrote:
|||thanks JXStern,
i am trying for a link to this issues, so that i can tell to my BOSS
thanks thanks very much
jay
"JXStern" wrote:

> Me, I don't have anything, just called the sysadmin guys (emailed,
> actually), and they told me this story, then did it, and it worked!
> Twice!
> These are only fairly current HP servers, so the problem is probably
> very, very, very common.
> J.
>
> On Wed, 28 Feb 2007 06:55:52 -0800, Jay
> <Jay@.discussions.microsoft.com> wrote:
>
>

64 bit 60 gb db with RAID 10 4 VOLUME

I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
error log file mentioned below, can you please help me
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
than 15 seconds to complete on file
[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The o
ffset
of the latest long I/O is: 0x0000032dac0000
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp."Jay" <Jay@.discussions.microsoft.com> wrote in message
news:926CD051-B1BF-481A-AA4D-BA06AB7DBA72@.microsoft.com...
>I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
> error log file mentioned below, can you please help me
> SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
> than 15 seconds to complete on file
> [E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
> [ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The
> offset
> of the latest long I/O is: 0x0000032dac0000
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
I'd look at the disk array first, make sure you have the latest drivers,
that there's no hardware issues, etc.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Generally indicates a HW problem.
Had this recently, first line of defense is to power-cycle the server,
second is to refresh RAID controller microcode and power-cycle. Doing
this seems to have "fixed" two of our servers where we were getting
this message - except at much, much higher volumes!
J.
On Tue, 27 Feb 2007 19:59:05 -0800, Jay
<Jay@.discussions.microsoft.com> wrote:

>I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
>error log file mentioned below, can you please help me
>SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
>than 15 seconds to complete on file
>[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
>[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The
offset
>of the latest long I/O is: 0x0000032dac0000
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.|||very very Thanks to Greg and JXStern,
can you provide some links related this issues, so that i can show to my
boss.it will be more helpfull to me.
waiting for you alls reply.
regards
Jay
"JXStern" wrote:

> Generally indicates a HW problem.
> Had this recently, first line of defense is to power-cycle the server,
> second is to refresh RAID controller microcode and power-cycle. Doing
> this seems to have "fixed" two of our servers where we were getting
> this message - except at much, much higher volumes!
> J.
>
> On Tue, 27 Feb 2007 19:59:05 -0800, Jay
> <Jay@.discussions.microsoft.com> wrote:
>
>|||Me, I don't have anything, just called the sysadmin guys (emailed,
actually), and they told me this story, then did it, and it worked!
Twice!
These are only fairly current HP servers, so the problem is probably
very, very, very common.
J.
On Wed, 28 Feb 2007 06:55:52 -0800, Jay
<Jay@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>very very Thanks to Greg and JXStern,
>can you provide some links related this issues, so that i can show to my
>boss.it will be more helpfull to me.
>waiting for you alls reply.
>regards
>Jay
>
>"JXStern" wrote:
>|||thanks JXStern,
i am trying for a link to this issues, so that i can tell to my BOSS
thanks thanks very much
jay
"JXStern" wrote:

> Me, I don't have anything, just called the sysadmin guys (emailed,
> actually), and they told me this story, then did it, and it worked!
> Twice!
> These are only fairly current HP servers, so the problem is probably
> very, very, very common.
> J.
>
> On Wed, 28 Feb 2007 06:55:52 -0800, Jay
> <Jay@.discussions.microsoft.com> wrote:
>
>

64 bit 60 gb db with RAID 10 4 VOLUME

I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
error log file mentioned below, can you please help me
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
than 15 seconds to complete on file
[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The offset
of the latest long I/O is: 0x0000032dac0000
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp."Jay" <Jay@.discussions.microsoft.com> wrote in message
news:926CD051-B1BF-481A-AA4D-BA06AB7DBA72@.microsoft.com...
>I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
> error log file mentioned below, can you please help me
> SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
> than 15 seconds to complete on file
> [E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
> [ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The
> offset
> of the latest long I/O is: 0x0000032dac0000
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
I'd look at the disk array first, make sure you have the latest drivers,
that there's no hardware issues, etc.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Generally indicates a HW problem.
Had this recently, first line of defense is to power-cycle the server,
second is to refresh RAID controller microcode and power-cycle. Doing
this seems to have "fixed" two of our servers where we were getting
this message - except at much, much higher volumes!
J.
On Tue, 27 Feb 2007 19:59:05 -0800, Jay
<Jay@.discussions.microsoft.com> wrote:
>I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
>error log file mentioned below, can you please help me
>SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
>than 15 seconds to complete on file
>[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
>[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The offset
>of the latest long I/O is: 0x0000032dac0000
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.|||very very Thanks to Greg and JXStern,
can you provide some links related this issues, so that i can show to my
boss.it will be more helpfull to me.
waiting for you alls reply.
regards
Jay
"JXStern" wrote:
> Generally indicates a HW problem.
> Had this recently, first line of defense is to power-cycle the server,
> second is to refresh RAID controller microcode and power-cycle. Doing
> this seems to have "fixed" two of our servers where we were getting
> this message - except at much, much higher volumes!
> J.
>
> On Tue, 27 Feb 2007 19:59:05 -0800, Jay
> <Jay@.discussions.microsoft.com> wrote:
> >I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
> >error log file mentioned below, can you please help me
> >
> >SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
> >than 15 seconds to complete on file
> >[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
> >[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The offset
> >of the latest long I/O is: 0x0000032dac0000
> >
> >For more information, see Help and Support Center at
> >http://go.microsoft.com/fwlink/events.asp.
>|||Me, I don't have anything, just called the sysadmin guys (emailed,
actually), and they told me this story, then did it, and it worked!
Twice!
These are only fairly current HP servers, so the problem is probably
very, very, very common.
J.
On Wed, 28 Feb 2007 06:55:52 -0800, Jay
<Jay@.discussions.microsoft.com> wrote:
>very very Thanks to Greg and JXStern,
>can you provide some links related this issues, so that i can show to my
>boss.it will be more helpfull to me.
>waiting for you alls reply.
>regards
>Jay
>
>"JXStern" wrote:
>> Generally indicates a HW problem.
>> Had this recently, first line of defense is to power-cycle the server,
>> second is to refresh RAID controller microcode and power-cycle. Doing
>> this seems to have "fixed" two of our servers where we were getting
>> this message - except at much, much higher volumes!
>> J.
>>
>> On Tue, 27 Feb 2007 19:59:05 -0800, Jay
>> <Jay@.discussions.microsoft.com> wrote:
>> >I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
>> >error log file mentioned below, can you please help me
>> >
>> >SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
>> >than 15 seconds to complete on file
>> >[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
>> >[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The offset
>> >of the latest long I/O is: 0x0000032dac0000
>> >
>> >For more information, see Help and Support Center at
>> >http://go.microsoft.com/fwlink/events.asp.
>>|||thanks JXStern,
i am trying for a link to this issues, so that i can tell to my BOSS
thanks thanks very much
jay
"JXStern" wrote:
> Me, I don't have anything, just called the sysadmin guys (emailed,
> actually), and they told me this story, then did it, and it worked!
> Twice!
> These are only fairly current HP servers, so the problem is probably
> very, very, very common.
> J.
>
> On Wed, 28 Feb 2007 06:55:52 -0800, Jay
> <Jay@.discussions.microsoft.com> wrote:
> >very very Thanks to Greg and JXStern,
> >
> >can you provide some links related this issues, so that i can show to my
> >boss.it will be more helpfull to me.
> >
> >waiting for you alls reply.
> >
> >regards
> >
> >Jay
> >
> >
> >
> >"JXStern" wrote:
> >
> >> Generally indicates a HW problem.
> >>
> >> Had this recently, first line of defense is to power-cycle the server,
> >> second is to refresh RAID controller microcode and power-cycle. Doing
> >> this seems to have "fixed" two of our servers where we were getting
> >> this message - except at much, much higher volumes!
> >>
> >> J.
> >>
> >>
> >> On Tue, 27 Feb 2007 19:59:05 -0800, Jay
> >> <Jay@.discussions.microsoft.com> wrote:
> >>
> >> >I am working with SQL 2005 64 bit sp1 , i am facing problem continues in
> >> >error log file mentioned below, can you please help me
> >> >
> >> >SQL Server has encountered 1 occurrence(s) of I/O requests taking longer
> >> >than 15 seconds to complete on file
> >> >[E:\ITS247DBFiles\ItSupport247DB1.mdf:MSSQL_DBCC11] in database
> >> >[ItSupport247DB] (11). The OS file handle is 0x0000000000007F5C. The offset
> >> >of the latest long I/O is: 0x0000032dac0000
> >> >
> >> >For more information, see Help and Support Center at
> >> >http://go.microsoft.com/fwlink/events.asp.
> >>
> >>
>

4GB string store limit: which file extensions?

The string store file has a 4GB size limit, as has been discussed previously (see the link below). I wanted to get some more info on that from one of the developers. Which file extensions are the ones we should be worrying about? Just .ksstore, .asstore, .bsstore, and .string.data?

I wanted to continue the thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1 but was unable to get a reply.

Got an answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1

4GB string store limit: which file extensions?

The string store file has a 4GB size limit, as has been discussed previously (see the link below). I wanted to get some more info on that from one of the developers. Which file extensions are the ones we should be worrying about? Just .ksstore, .asstore, .bsstore, and .string.data?

I wanted to continue the thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1 but was unable to get a reply.

Got an answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1

4 Table select statement?

Hello can anyone help me out with an sql statement.
I have an order table, which has 4 tables below it, which hold things that a
user can order.
Orders(table)
Then this order table above is linked to the 4 product tables.
ProductA(table)
ProductB(table)
ProductC(table)
ProductD(table)
each of the Product A to B tables have various different fields inside, but
they all share the following 3 fields: OrderID, ProductName, Pic
The product tables are linked by the OrderID field to the Orders Table.
I would like to to write a statement like this:
Select ProductName, Pic from ProductA, ProductB, ProductC, ProductD where
ProductA.OrderID = Orders.OrderID and
ProductB.OrderID = Orders.OrderID and
ProductC.OrderID = Orders.OrderID and
ProductD.OrderID = Orders.OrderID ;
Is the above SQL statement Possible? right now it gives me lots of errors,
and i can't seem to get the right output from that statement, i just wone
non-repeating unique records from all 4 tables , so if i had 8 products in
this order, 2 in each of the product tables, then i want to display 8 record
s
only, right now if i play around this statement i get lots and lots of
duplicate records.ARTMIC wrote:
> Hello can anyone help me out with an sql statement.
> I have an order table, which has 4 tables below it, which hold things that
a
> user can order.
> Orders(table)
> Then this order table above is linked to the 4 product tables.
> ProductA(table)
> ProductB(table)
> ProductC(table)
> ProductD(table)
>
> each of the Product A to B tables have various different fields inside, bu
t
> they all share the following 3 fields: OrderID, ProductName, Pic
> The product tables are linked by the OrderID field to the Orders Table.
> I would like to to write a statement like this:
> Select ProductName, Pic from ProductA, ProductB, ProductC, ProductD where
> ProductA.OrderID = Orders.OrderID and
> ProductB.OrderID = Orders.OrderID and
> ProductC.OrderID = Orders.OrderID and
> ProductD.OrderID = Orders.OrderID ;
> Is the above SQL statement Possible? right now it gives me lots of errors,
> and i can't seem to get the right output from that statement, i just wone
> non-repeating unique records from all 4 tables , so if i had 8 products in
> this order, 2 in each of the product tables, then i want to display 8 reco
rds
> only, right now if i play around this statement i get lots and lots of
> duplicate records.
It seems like you want a UNION rather than a join. Here's a guess:
SELECT orderid, productname, pic
FROM
(SELECT A.orderid, A.productname, A.pic
FROM producta AS A
UNION ALL
SELECT B.orderid, B.productname, B.pic
FROM productb AS B
UNION ALL
SELECT C.orderid, C.productname, C.pic
FROM productc AS C
UNION ALL
SELECT D.orderid, D.productname, D.pic
FROM productd AS D) AS T
WHERE ... /* ? not specified */
ORDER BY orderid ;
This looks more than a little strange. Why orderid in the products
tables? And why isn't there a common products table for the common
columns? Maybe I've misunderstood, in which case the best way for you
to get more help is to post DDL and sample data and show your required
end results. DDL and sample data means CREATE TABLE statements for your
tables and INSERT statements for some example data (just the essential
columns will do). Don't forget to include keys and constraints with
your DDL.
Hope this helps.
David Portas
SQL Server MVP
--|||Hello David, thank you for the answer,
i will play with the joins that you mentioned,
i think i didn't express my question the right way, hard to explain,
the 4 product tables, are really orderDetail tables. So in reality ProductA
is really DetailA, DetailB, DetailC, DetailD which is linked to the order
table.
the reason for the above is becuase the uesr will only purchase 4 types of
things... and each thing has their own data fields. ( this setup is for a
manufactuerer)
It is a master-detail type of situation with Order header, and order detail,
but in my case, i have 4 order Detail tables, and i want to display all the
details from the 4 tables in one sql statement, at least the common fields..
.
I woudl have gone with a flat table for the detail table, but iwould have
had like 250+ fields in the table, that is too much to manage...so i split
the order-detail table into 4 distinct productsDetail tables... now i am
paying for it :)
i will play with your sql statements and hopefully it will get me over my
proplem, i will reply if it worked or not,
Thanks again !
"David Portas" wrote:

> ARTMIC wrote:
>
> It seems like you want a UNION rather than a join. Here's a guess:
> SELECT orderid, productname, pic
> FROM
> (SELECT A.orderid, A.productname, A.pic
> FROM producta AS A
> UNION ALL
> SELECT B.orderid, B.productname, B.pic
> FROM productb AS B
> UNION ALL
> SELECT C.orderid, C.productname, C.pic
> FROM productc AS C
> UNION ALL
> SELECT D.orderid, D.productname, D.pic
> FROM productd AS D) AS T
> WHERE ... /* ? not specified */
> ORDER BY orderid ;
> This looks more than a little strange. Why orderid in the products
> tables? And why isn't there a common products table for the common
> columns? Maybe I've misunderstood, in which case the best way for you
> to get more help is to post DDL and sample data and show your required
> end results. DDL and sample data means CREATE TABLE statements for your
> tables and INSERT statements for some example data (just the essential
> columns will do). Don't forget to include keys and constraints with
> your DDL.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||ARTMIC wrote:
> I woudl have gone with a flat table for the detail table, but iwould have
> had like 250+ fields in the table, that is too much to manage...so i split
> the order-detail table into 4 distinct productsDetail tables... now i am
> paying for it :)
>
I would prefer 5 tables. 4 for each unique product type. 1 for the
attributes that are common to all types (name for example).
David Portas
SQL Server MVP
--|||Thank you David,
you are right, i might as well just move the common fields into an
OrderDetail table and then link to the 4 OrderDetailProd tables.
Thanks,
"David Portas" wrote:

> ARTMIC wrote:
> I would prefer 5 tables. 4 for each unique product type. 1 for the
> attributes that are common to all types (name for example).
> --
> David Portas
> SQL Server MVP
> --
>

4 Queries about DB clustering

Hi, Below are few queries:
1. Say for 2 nodes cluster, Do we need to have 2 copies of Databases( 1 on
each node)
2. Hows does the data Synchronization between different nodes of databases
takes place?
3. In Active/Passive mode, does the passive node database is exactly in the
same state (in terms of data) as that of Active?
4. Is 'Server Cluster' specifically for DB clustering ?
Thanks in advance,
Pankaj A. Chitriv
Answers Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
news:847A2BE3-74E1-4B9E-9D61-EEE40D117155@.microsoft.com...
> Hi, Below are few queries:
> 1. Say for 2 nodes cluster, Do we need to have 2 copies of Databases( 1 on
> each node)
No. Data is on shared storage and is controlled by one node at a time.
> 2. Hows does the data Synchronization between different nodes of databases
> takes place?
There is no synchronization. There is a single data store. Access to this
store is arbitrated by the cluster software.
> 3. In Active/Passive mode, does the passive node database is exactly in
the
> same state (in terms of data) as that of Active?
>
Clustering is failover not scaleout technology.

> 4. Is 'Server Cluster' specifically for DB clustering ?
I am not sure what you mean by "Server Cluster"?
> Thanks in advance,
> Pankaj A. Chitriv
>
|||Thanks Geoff.
"Server Cluster" is a inbuild clustering service provided by Windows 2003
Server.
Just wondering, whether that Single Data Store is a seperate windows system?
If so what if that system breaks down? Is that the case that we do DB
clustering only to distribute the processing capabilities?
What I understood by Single Data Store means is there will be a system on
which SQL DB will be created and there would be multiple servers accessing
the same database. But this cannot be called as DB clustering as we don't
have to configure any VIP to access the single data store. Can you clarify
this please?
Thanks a lot again for your answers.
Regards
Pankaj A. Chitriv
"Geoff N. Hiten" wrote:

> Answers Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
> news:847A2BE3-74E1-4B9E-9D61-EEE40D117155@.microsoft.com...
> No. Data is on shared storage and is controlled by one node at a time.
> There is no synchronization. There is a single data store. Access to this
> store is arbitrated by the cluster software.
> the
> Clustering is failover not scaleout technology.
> I am not sure what you mean by "Server Cluster"?
>
>
|||Server clustering is available on Windows Server 2003 Enterprise Edition.
"Single Data Store" is not a service, it is a description. All data resides
on a single logical device physically connected to all host nodes. This can
be a SCSI array, a Fiber array, or a SAN. Ownership of this device is
arbitrated by the cluster service. Microsoft SQL DB clustering exists as a
failover technology only and has no distributed computing capabilities. It
is not a scale-out technology. One server owns the entire database and no
other server can access the data without moving the entire SQL instance to
another host. Active-Passive and Active-Active are not accurate
descriptions of the current SQL clustering technology but are somewhat
misleading holdovers from an earlier version.
Again, clustering is for availability and does not allow multiple
simultaneous connections to the data store from different host nodes.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
news:4A51BB01-D028-4B3B-9244-12EFA8469987@.microsoft.com...
> Thanks Geoff.
> "Server Cluster" is a inbuild clustering service provided by Windows 2003
> Server.
> Just wondering, whether that Single Data Store is a seperate windows
system?[vbcol=seagreen]
> If so what if that system breaks down? Is that the case that we do DB
> clustering only to distribute the processing capabilities?
> What I understood by Single Data Store means is there will be a system on
> which SQL DB will be created and there would be multiple servers accessing
> the same database. But this cannot be called as DB clustering as we don't
> have to configure any VIP to access the single data store. Can you clarify
> this please?
> Thanks a lot again for your answers.
> Regards
> Pankaj A. Chitriv
> "Geoff N. Hiten" wrote:
Databases( 1 on[vbcol=seagreen]
databases[vbcol=seagreen]
this[vbcol=seagreen]
in[vbcol=seagreen]
|||Hi Geoff.
Thankyou very much for your help. It is just great. I really really
appreciate it.
Further, to summarise our discussion what I understood to implement the SQL
Db Cluster is:
1. I need to have SCSI array, a Fiber array, or a SAN as a shared device
"on which I'll install SQL Server Database".
2. Say in a 2 node cluster, both the nodes would be typically Windows 2k3
Server systems, with the SQL Server Instance reference (SQL Client) to the
shared device on which SQL Server is installed.
3. Say in Active / Passive mode, only 1 Win2k3 system will be utilized and
in case of faliure, the other win2k3 server node will be active.
4. I need to install SQL Client on both the nodes and Sql Server on shared
device.
5. Just wondering whether stored procedures of SQL DB will be executed on
the shared drive or on the Node. Does the shared drive have processing
capabilities?
With this queries clarified , I'm ready to shoot :-).
I really appreciate your help and request your comment on above queries.
Regards,
Pankaj A. Chitriv
"Geoff N. Hiten" wrote:

> Server clustering is available on Windows Server 2003 Enterprise Edition.
> "Single Data Store" is not a service, it is a description. All data resides
> on a single logical device physically connected to all host nodes. This can
> be a SCSI array, a Fiber array, or a SAN. Ownership of this device is
> arbitrated by the cluster service. Microsoft SQL DB clustering exists as a
> failover technology only and has no distributed computing capabilities. It
> is not a scale-out technology. One server owns the entire database and no
> other server can access the data without moving the entire SQL instance to
> another host. Active-Passive and Active-Active are not accurate
> descriptions of the current SQL clustering technology but are somewhat
> misleading holdovers from an earlier version.
> Again, clustering is for availability and does not allow multiple
> simultaneous connections to the data store from different host nodes.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
> news:4A51BB01-D028-4B3B-9244-12EFA8469987@.microsoft.com...
> system?
> Databases( 1 on
> databases
> this
> in
>
>
|||You are getting closer. More comments inline.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
news:1D8FA145-7AF2-4EBE-9FA1-FD9822233F81@.microsoft.com...
> Hi Geoff.
> Thankyou very much for your help. It is just great. I really really
> appreciate it.
> Further, to summarise our discussion what I understood to implement the
SQL
> Db Cluster is:
> 1. I need to have SCSI array, a Fiber array, or a SAN as a shared device
> "on which I'll install SQL Server Database".
The database(s) will live on the shared storage. SQL binaries will live on
the local disks of each cluster host.
> 2. Say in a 2 node cluster, both the nodes would be typically Windows 2k3
> Server systems, with the SQL Server Instance reference (SQL Client) to the
> shared device on which SQL Server is installed.
A basic two-node SQL cluster will have four systems visible. There are the
two physical host nodes. There is the cluster vrtual server itself. There
is the SQL Server virtual server. Each virtual system must have a minimum
of one unique IP address, one unique network name, and a physical disk
device located on the shared array. You access the SQL server by connecting
to the virtual server by name or IP address.
>

> 3. Say in Active / Passive mode, only 1 Win2k3 system will be utilized and
> in case of faliure, the other win2k3 server node will be active.
>
Correct. The current terms are single-instance and multi-instance. Each
new instance of SQL will have its own disk(s), IP address(es), and Network
name. Once an instance is installed, you can set the preferred host order
to determine the "normal" home host for that instance. You can have up to
16 instances on a single cluster.

> 4. I need to install SQL Client on both the nodes and Sql Server on shared
> device.
>
First you install MSCS (Clustering). Windows 2003 has an excellent
clustering wizard that makes it very difficult to create a non-working
cluster. Then you install a clustered instance of SQL server, running the
install CD from the node that currently owns the disk resource you wish to
use as the first SQL disk. You can assign additional disks after
installation. The installer writes the executable files to each host node
you choose as part of the installation. This includes server and client
components.

> 5. Just wondering whether stored procedures of SQL DB will be executed on
> the shared drive or on the Node. Does the shared drive have processing
> capabilities?
The binaries are on each host node. The memory and CPU resources come from
the host node. The data is stored on the shared array. SQL uses a "Shared
Nothing" model so after installation you can run any instance from any host
node, regardless of whether any other node or instance is running.
[vbcol=seagreen]
> With this queries clarified , I'm ready to shoot :-).
> I really appreciate your help and request your comment on above queries.
> Regards,
> Pankaj A. Chitriv
>
>
> "Geoff N. Hiten" wrote:
Edition.[vbcol=seagreen]
resides[vbcol=seagreen]
can[vbcol=seagreen]
as a[vbcol=seagreen]
It[vbcol=seagreen]
no[vbcol=seagreen]
to[vbcol=seagreen]
2003[vbcol=seagreen]
on[vbcol=seagreen]
accessing[vbcol=seagreen]
don't[vbcol=seagreen]
clarify[vbcol=seagreen]
time.[vbcol=seagreen]
to[vbcol=seagreen]
exactly[vbcol=seagreen]
|||I have a question for Geoff.
I have a similar situation where I plan to install/configure multi-instance
cluster.
My question is
a) as we plan to use/assign drive letters what care shoud be taken for
multi-instance cluster (3 active 1, passive/standby)?
Thanks.
Kamal.
"Geoff N. Hiten" wrote:

> You are getting closer. More comments inline.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
> news:1D8FA145-7AF2-4EBE-9FA1-FD9822233F81@.microsoft.com...
> SQL
> The database(s) will live on the shared storage. SQL binaries will live on
> the local disks of each cluster host.
> A basic two-node SQL cluster will have four systems visible. There are the
> two physical host nodes. There is the cluster vrtual server itself. There
> is the SQL Server virtual server. Each virtual system must have a minimum
> of one unique IP address, one unique network name, and a physical disk
> device located on the shared array. You access the SQL server by connecting
> to the virtual server by name or IP address.
> Correct. The current terms are single-instance and multi-instance. Each
> new instance of SQL will have its own disk(s), IP address(es), and Network
> name. Once an instance is installed, you can set the preferred host order
> to determine the "normal" home host for that instance. You can have up to
> 16 instances on a single cluster.
> First you install MSCS (Clustering). Windows 2003 has an excellent
> clustering wizard that makes it very difficult to create a non-working
> cluster. Then you install a clustered instance of SQL server, running the
> install CD from the node that currently owns the disk resource you wish to
> use as the first SQL disk. You can assign additional disks after
> installation. The installer writes the executable files to each host node
> you choose as part of the installation. This includes server and client
> components.
>
> The binaries are on each host node. The memory and CPU resources come from
> the host node. The data is stored on the shared array. SQL uses a "Shared
> Nothing" model so after installation you can run any instance from any host
> node, regardless of whether any other node or instance is running.
> Edition.
> resides
> can
> as a
> It
> no
> to
> 2003
> on
> accessing
> don't
> clarify
> time.
> to
> exactly
>
>
|||All drives will need to be visible to all hosts for failover. Drive letters
are assigned cluster-wide for shared resources. The easiest way is to
pretend you are building a multi-instance stand-alone system where SQL Data
and Logs from multiple instances cannot intersect on the same LUN. Also
remember to add a drive for MSDTC and for the Quorum. Don't forget to leave
room for expansion.
Example
C for local boot, Z for local CD-ROM (Old Novell habit)
Q for Quorum, M for MSDTC
K,L for SQLInstance1 data and logs respectively
R,S for SQLInstance2 data and logs respectively
lather, rinse, repeat.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kamal Hassan" <KamalHassan@.discussions.microsoft.com> wrote in message
news:5D8375C6-8269-46FC-B619-BD75E0F8A6CD@.microsoft.com...
> I have a question for Geoff.
> I have a similar situation where I plan to install/configure
multi-instance[vbcol=seagreen]
> cluster.
> My question is
> a) as we plan to use/assign drive letters what care shoud be taken for
> multi-instance cluster (3 active 1, passive/standby)?
> Thanks.
> Kamal.
>
>
> "Geoff N. Hiten" wrote:
the[vbcol=seagreen]
device[vbcol=seagreen]
on[vbcol=seagreen]
2k3[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
There[vbcol=seagreen]
minimum[vbcol=seagreen]
connecting[vbcol=seagreen]
and[vbcol=seagreen]
Each[vbcol=seagreen]
Network[vbcol=seagreen]
order[vbcol=seagreen]
to[vbcol=seagreen]
shared[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
node[vbcol=seagreen]
on[vbcol=seagreen]
processing[vbcol=seagreen]
from[vbcol=seagreen]
"Shared[vbcol=seagreen]
host[vbcol=seagreen]
queries.[vbcol=seagreen]
This[vbcol=seagreen]
is[vbcol=seagreen]
exists[vbcol=seagreen]
capabilities.[vbcol=seagreen]
and[vbcol=seagreen]
instance[vbcol=seagreen]
somewhat[vbcol=seagreen]
nodes.[vbcol=seagreen]
Windows[vbcol=seagreen]
windows[vbcol=seagreen]
DB[vbcol=seagreen]
system[vbcol=seagreen]
we[vbcol=seagreen]
message[vbcol=seagreen]
a[vbcol=seagreen]
of[vbcol=seagreen]
Access[vbcol=seagreen]
|||First, manythanks for quick reply.
In your suggestion is the SQLinstance1 and SQLInstance refer to as ACTIVE
nodes with StandyBy Server or ?
We are not using MSDTC do we still have to setup/configure? and what do you
mean don't forget to leave for expnasion?
Thanks a lot!
Kamal.
"Geoff N. Hiten" wrote:

> All drives will need to be visible to all hosts for failover. Drive letters
> are assigned cluster-wide for shared resources. The easiest way is to
> pretend you are building a multi-instance stand-alone system where SQL Data
> and Logs from multiple instances cannot intersect on the same LUN. Also
> remember to add a drive for MSDTC and for the Quorum. Don't forget to leave
> room for expansion.
> Example
> C for local boot, Z for local CD-ROM (Old Novell habit)
> Q for Quorum, M for MSDTC
> K,L for SQLInstance1 data and logs respectively
> R,S for SQLInstance2 data and logs respectively
> lather, rinse, repeat.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Kamal Hassan" <KamalHassan@.discussions.microsoft.com> wrote in message
> news:5D8375C6-8269-46FC-B619-BD75E0F8A6CD@.microsoft.com...
> multi-instance
> the
> device
> on
> 2k3
> the
> the
> There
> minimum
> connecting
> and
> Each
> Network
> order
> to
> shared
> the
> to
> node
> on
> processing
> from
> "Shared
> host
> queries.
> This
> is
> exists
> capabilities.
> and
> instance
> somewhat
> nodes.
> Windows
> windows
> DB
> system
> we
> message
> a
> of
> Access
>
>
|||Active\Active and Active\Passive are not accurate terms. Each SQL instance
is its own virtual server.
SQLInstance1 and 2 refer to the SQL virtual servers\instances. The host
nodes are all configured identically.
You will have to add disk storage sometime down the road. Leave empty drive
letters for adding new disk resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kamal Hassan" <KamalHassan@.discussions.microsoft.com> wrote in message
news:DD172480-C6FA-41A6-86CC-65A2C790D3C6@.microsoft.com...
> First, manythanks for quick reply.
> In your suggestion is the SQLinstance1 and SQLInstance refer to as ACTIVE
> nodes with StandyBy Server or ?
> We are not using MSDTC do we still have to setup/configure? and what do
you[vbcol=seagreen]
> mean don't forget to leave for expnasion?
> Thanks a lot!
> Kamal.
>
> "Geoff N. Hiten" wrote:
letters[vbcol=seagreen]
Data[vbcol=seagreen]
leave[vbcol=seagreen]
really[vbcol=seagreen]
implement[vbcol=seagreen]
live[vbcol=seagreen]
Windows[vbcol=seagreen]
Client) to[vbcol=seagreen]
are[vbcol=seagreen]
disk[vbcol=seagreen]
utilized[vbcol=seagreen]
up[vbcol=seagreen]
on[vbcol=seagreen]
non-working[vbcol=seagreen]
running[vbcol=seagreen]
wish[vbcol=seagreen]
host[vbcol=seagreen]
client[vbcol=seagreen]
executed[vbcol=seagreen]
come[vbcol=seagreen]
any[vbcol=seagreen]
data[vbcol=seagreen]
nodes.[vbcol=seagreen]
device[vbcol=seagreen]
database[vbcol=seagreen]
multiple[vbcol=seagreen]
message[vbcol=seagreen]
we do[vbcol=seagreen]
a[vbcol=seagreen]
servers[vbcol=seagreen]
as[vbcol=seagreen]
you[vbcol=seagreen]
at[vbcol=seagreen]
nodes[vbcol=seagreen]
is[vbcol=seagreen]

4 processors detected - but we only have two?

Each time our server is rebooted, and SQL Server restarts, it posts an event
log message like the one below. The thing is, we only have two processors.
Does this indicate a misconfiguration, a problem, or nothing to be concerned
about? This is a brand new Dell loaded with Windows 2003 Server and SQL
Server 2000 with all patches.
Thanks in advance.
Mark
It's behaviour or hyperthreading processors (each one seems like two).
J. Lejsek
"Mark" <Mark@.nowhere.com> pe v diskusnm pspvku
news:efaTLg8$EHA.4092@.TK2MSFTNGP09.phx.gbl...
> Each time our server is rebooted, and SQL Server restarts, it posts an
> event
> log message like the one below. The thing is, we only have two
> processors.
> Does this indicate a misconfiguration, a problem, or nothing to be
> concerned
> about? This is a brand new Dell loaded with Windows 2003 Server and SQL
> Server 2000 with all patches.
> Thanks in advance.
> Mark
>
|||Hyper-Threading
http://www.intel.com/technology/hyperthread/
"Mark" wrote:

> Each time our server is rebooted, and SQL Server restarts, it posts an event
> log message like the one below. The thing is, we only have two processors.
> Does this indicate a misconfiguration, a problem, or nothing to be concerned
> about? This is a brand new Dell loaded with Windows 2003 Server and SQL
> Server 2000 with all patches.
> Thanks in advance.
> Mark
>
>

4 processors detected - but we only have two?

Each time our server is rebooted, and SQL Server restarts, it posts an event
log message like the one below. The thing is, we only have two processors.
Does this indicate a misconfiguration, a problem, or nothing to be concerned
about? This is a brand new Dell loaded with Windows 2003 Server and SQL
Server 2000 with all patches.
Thanks in advance.
MarkIt's behaviour or hyperthreading processors (each one seems like two).
J. Lejsek
"Mark" <Mark@.nowhere.com> pe v diskusnm pspvku
news:efaTLg8$EHA.4092@.TK2MSFTNGP09.phx.gbl...
> Each time our server is rebooted, and SQL Server restarts, it posts an
> event
> log message like the one below. The thing is, we only have two
> processors.
> Does this indicate a misconfiguration, a problem, or nothing to be
> concerned
> about? This is a brand new Dell loaded with Windows 2003 Server and SQL
> Server 2000 with all patches.
> Thanks in advance.
> Mark
>|||Hyper-Threading
http://www.intel.com/technology/hyperthread/
"Mark" wrote:

> Each time our server is rebooted, and SQL Server restarts, it posts an eve
nt
> log message like the one below. The thing is, we only have two processors
.
> Does this indicate a misconfiguration, a problem, or nothing to be concern
ed
> about? This is a brand new Dell loaded with Windows 2003 Server and SQL
> Server 2000 with all patches.
> Thanks in advance.
> Mark
>
>

4 processors detected - but we only have two?

Each time our server is rebooted, and SQL Server restarts, it posts an event
log message like the one below. The thing is, we only have two processors.
Does this indicate a misconfiguration, a problem, or nothing to be concerned
about? This is a brand new Dell loaded with Windows 2003 Server and SQL
Server 2000 with all patches.
Thanks in advance.
MarkIt's behaviour or hyperthreading processors (each one seems like two).
J. Lejsek
"Mark" <Mark@.nowhere.com> pí¹e v diskusním pøíspìvku
news:efaTLg8$EHA.4092@.TK2MSFTNGP09.phx.gbl...
> Each time our server is rebooted, and SQL Server restarts, it posts an
> event
> log message like the one below. The thing is, we only have two
> processors.
> Does this indicate a misconfiguration, a problem, or nothing to be
> concerned
> about? This is a brand new Dell loaded with Windows 2003 Server and SQL
> Server 2000 with all patches.
> Thanks in advance.
> Mark
>|||Hyper-Threading
http://www.intel.com/technology/hyperthread/
"Mark" wrote:
> Each time our server is rebooted, and SQL Server restarts, it posts an event
> log message like the one below. The thing is, we only have two processors.
> Does this indicate a misconfiguration, a problem, or nothing to be concerned
> about? This is a brand new Dell loaded with Windows 2003 Server and SQL
> Server 2000 with all patches.
> Thanks in advance.
> Mark
>
>

4 CPU Utilization Imbalance

Got a strange thing happening, any comments are appreciated:

1) 4 CPU server
2) CPU's 0 & 2 are usually below 15% or so utilization (Perfmon, Task Manager)
3) CPU's 1 & 3 are usually nearly maxed out above 90%
4) Two SQL threads of over 80 threads in Perfmon show almost constant 90% Processor Time
5) Processor affinity in Server Properties is checked to use all four processors
6) Hyper-threading is not enabled
7) SQL Server priority is checked on HIGH
8) Dedicated SQL Server

Anybody know how to track down what those specific threads are executing? Any ideas, anybody? Thanks for the help!

SteveRefer to this link (http://www.sql-server-performance.com/dbcc_commands.asp) for useful DBCC commands and capture the events.|||Satya, thanks for the reply, and the link to the highly useful web site. I was able to trace the thread activity using Perfmon and sysprocesses; successfully killed a couple non-responsive threads and things returned to normal. I appreciate the help!

Steve|||True, keep in touch with the link provided for the tips and tricks on the performance.