Help with Calculated member

I'm trying to create a calculate member in SQL Server 2005 that will return a distinct count of clients year to date for all dates within my time dimension. I have figured out that if I create a measure that returns a distinct count then I can use the aggregate function and YTD function to get the distinct count YTD for any date. However, the aggregate function requires a slicer dimension (i think) and every time I put the mdx into the expression box, it does not like the syntax.

I have tried this:

With member [Authorization Service Date].[Calendar].[BlaBla] as

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

and I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

returns error: Error 1 MdxScript(DSS) (67, 4) The function can be called only on a named set. 0 0

I don't know wht this means

Any help would be deeply appreciated

I think you are looking to use the CURRENTMEMBER function instead of CURRENT.

I think this will simply take you to your next challenge. If you run this query, you'll likely get NULL for your result.

I think something like this will better serve your needs:

Code Snippet

With member [Measures].[x] as

COUNT(

EXISTS(

[Customer].[Customer].[Customer].Members,

YTD([Delivery Date].[Calendar].CurrentMember),

'Internet Sales'

)

)

select

[Measures].[x] on columns

from [Adventure Works]

where [Delivery Date].[Date].[June 1, 2002]

;

|||

Bryan,

I appreciate your taking your time to assist me with this. Unfortunately, it did not work. As translated, your mdx on my system looks like

with member [Measures].[x] as

COUNT(

EXISTS(

[CCE Members].[MemberId].Members,

YTD([Authorization Service Date].[Calendar].CurrentMember),

'Authorization Service Days Count'

)

)

SELECT [Measures].[x] ON 0

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

I get xError

Just for your info however, I know that all of the underlying attributes and metrics join up properly becuase when I execute:

SELECT [Measures].[Authorization Service Days Count] ON 0,

[CCE Members].[MemberId].Members ON 1

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

it works.

Again, your assistance is trully appreciated.

Thanks,

Michael

|||

Two things I noticed in the EXISTS function. Try changing the set to include just the leaf level members:

[CCE Members].[MemberId].Members --> [CCE Members].[MemberId].[MemberId].Members

Also, is 'Authorization Service Days Count' the name of the measure or the measure group? You need to identify the measure group.

B.

sql

Help with addition to Missing date query

Hi Everyone,
I hope someone can help out some. I have a report that displays the days our
clients missed there sessions. That was working great, and I've got some new
requirements now for the report. They needed to store the number of days the
client is suppose to attend, so I created a field called ClientSchedule that
stores a value 1-7 (days as week). The thing that has got me now is that they
also want the report to represent this new field. So the report needs to not
include the client in the report if they have attended for there scheduled
days for that week. But the kicker is that the report is based on a date
range and that range is variable, it could be set to one week, two weeks, 1
1/2 weeks, ect.. I have a query that partially works, but I could not figure
out how to work out the missed days within a week with a variable date range.
Somehow it needs to compare the number of days the client is scheduled
(ClientSchedule) with the number of days attended for a week, if days
attended is lower than ClientSchedule for that week, include the missed days
in the report(including all weeks that may be part of the Date range the user
selected).
The base query I have is as follows:
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.CounselorId int,
@.FromDate smalldatetime,
@.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
Delete from RangeTest
if @.PatientId = -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate
Print 'First Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
Print 'Second Set'
end
end
if @.PatientId > -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate and PatientId = @.PatientId
Print 'Third Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
and PatientId = @.PatientId
Print 'Last Set'
end
end
Delete from Range
if @.PatientId = -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON 1
= 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.PatientId > -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
Patientid = @.PatientId) Test ON 1 = 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.CounselorId = -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID = dbo.nf_GetLastDayVisited.PatientId
where PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
if @.CounselorId <> -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID = dbo.nf_GetLastDayVisited.PatientId
Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
GO
I have worked on it for several hours and not sure where to take it from
here. Do I have to start to use a curser, or is there a way to do this with
just a modification to the query I have so far. Thanks for any suggestions.
MichaelHi Michael
Have you thought of using a calendar table for this ?
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
John
"Michael" wrote:
> Hi Everyone,
> I hope someone can help out some. I have a report that displays the days our
> clients missed there sessions. That was working great, and I've got some new
> requirements now for the report. They needed to store the number of days the
> client is suppose to attend, so I created a field called ClientSchedule that
> stores a value 1-7 (days as week). The thing that has got me now is that they
> also want the report to represent this new field. So the report needs to not
> include the client in the report if they have attended for there scheduled
> days for that week. But the kicker is that the report is based on a date
> range and that range is variable, it could be set to one week, two weeks, 1
> 1/2 weeks, ect.. I have a query that partially works, but I could not figure
> out how to work out the missed days within a week with a variable date range.
> Somehow it needs to compare the number of days the client is scheduled
> (ClientSchedule) with the number of days attended for a week, if days
> attended is lower than ClientSchedule for that week, include the missed days
> in the report(including all weeks that may be part of the Date range the user
> selected).
> The base query I have is as follows:
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.CounselorId int,
> @.FromDate smalldatetime,
> @.ToDate smalldatetime
> AS
> SET NOCOUNT ON
> DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
> Delete from RangeTest
> if @.PatientId = -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate
> Print 'First Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> Print 'Second Set'
> end
> end
> if @.PatientId > -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate and PatientId = @.PatientId
> Print 'Third Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> and PatientId = @.PatientId
> Print 'Last Set'
> end
> end
>
> Delete from Range
> if @.PatientId = -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON 1
> = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.PatientId > -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
> Patientid = @.PatientId) Test ON 1 = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.CounselorId = -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID => dbo.nf_GetLastDayVisited.PatientId
> where PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> if @.CounselorId <> -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID => dbo.nf_GetLastDayVisited.PatientId
> Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> GO
> I have worked on it for several hours and not sure where to take it from
> here. Do I have to start to use a curser, or is there a way to do this with
> just a modification to the query I have so far. Thanks for any suggestions.
> Michael
>

Help with a Query

A simple sales activity tracking system. We collect information about
Clients, and sales Activity related to the clients.
Here's some simplified DDL and some insert statements
CREATE TABLE [Client] (
Id [int] NOT NULL ,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
Id
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
CREATE TABLE [Activity] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ClientId] [int] NOT NULL ,
[UserIdEnteredBy] [int] NOT NULL ,
[DateofActivity] [datetime] NOT NULL ,
CONSTRAINT [PK_Activity] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Activity_Client] FOREIGN KEY
(
[ClientId]
) REFERENCES [Client] (
[Id]
)
) ON [PRIMARY]
END
GO
INSERT INTO Client (Id) VALUES (1)
INSERT INTO Client (Id) VALUES (2)
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 1, '01/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 2, '02/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 2, '03/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 1, '04/01/2005')
I want a list of all Clients, and the LastContacted Date, and who the
user who LastContacted them was.
Here's what I have so far:
SELECT
C.Id,
A.[UserIdEnteredBy] AS LastContactedBy,
A.[DateofActivity] AS LastContacted
FROM
Client C
LEFT OUTER JOIN Activity A ON C.Id = A.[ClientId]
JOIN (SELECT ClientId, Max(Id) AS MaxActivityId From Activity GROUP BY
ClientId) L
ON A.Id = L.MaxActivityId AND A.ClientId = L.ClientId
This gives me:
Id LastContactedBy LastContacted
---
1 1 2005-04-01 00:00:00.000
However, I want to return:
Id LastContactedBy LastContacted
---
1 1 2005-04-01 00:00:00.000
2 NULL NULL
This has got to be something with my joins, but I can't figure it out.
Thanks!Hi,
Try this instead...
Select Client.ID,
Activity.UserIDEnteredBy,
Activity.DateOfActivity

>From Client
Left Outer Join Activity on Client.ID = Activity.ClientID
Where Activity.DateOfActivity = (select Max(Activity2.dateofactivity)
from Activity Activity2
where Activity2.ClientID = Client.ID)
or Activity.UserIDEnteredBy IS Null
HTH
Barry|||Try
select c.*,tmp.* from client c left join (SELECT ClientId, Max(Id) AS
MaxActivityId From Activity GROUP BY
ClientId) tmp on c.id=tmp.clientid
Cheers,
JP
----
A program is a device used to convert,
data into error messages
----
<george.durzi@.gmail.com> wrote in message
news:1126291592.558676.297110@.g49g2000cwa.googlegroups.com...
>A simple sales activity tracking system. We collect information about
> Clients, and sales Activity related to the clients.
> Here's some simplified DDL and some insert statements
> CREATE TABLE [Client] (
> Id [int] NOT NULL ,
> CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
> (
> Id
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
> CREATE TABLE [Activity] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [ClientId] [int] NOT NULL ,
> [UserIdEnteredBy] [int] NOT NULL ,
> [DateofActivity] [datetime] NOT NULL ,
> CONSTRAINT [PK_Activity] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Activity_Client] FOREIGN KEY
> (
> [ClientId]
> ) REFERENCES [Client] (
> [Id]
> )
> ) ON [PRIMARY]
> END
> GO
> INSERT INTO Client (Id) VALUES (1)
> INSERT INTO Client (Id) VALUES (2)
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 1, '01/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 2, '02/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 2, '03/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 1, '04/01/2005')
> I want a list of all Clients, and the LastContacted Date, and who the
> user who LastContacted them was.
> Here's what I have so far:
> SELECT
> C.Id,
> A.[UserIdEnteredBy] AS LastContactedBy,
> A.[DateofActivity] AS LastContacted
> FROM
> Client C
> LEFT OUTER JOIN Activity A ON C.Id = A.[ClientId]
> JOIN (SELECT ClientId, Max(Id) AS MaxActivityId From Activity GROUP BY
> ClientId) L
> ON A.Id = L.MaxActivityId AND A.ClientId = L.ClientId
> This gives me:
> Id LastContactedBy LastContacted
> ---
> 1 1 2005-04-01 00:00:00.000
> However, I want to return:
> Id LastContactedBy LastContacted
> ---
> 1 1 2005-04-01 00:00:00.000
> 2 NULL NULL
> This has got to be something with my joins, but I can't figure it out.
> Thanks!
>|||Thank you Barry, I'll check this out.|||Why did the client's id change names from table to table? Why did you
avoid real keys in favor proprietary, non-relational record numbering?
Is this what you meant, if you had a proper data model and had followed
ISO-11179 namign conventions? .
CREATE TABLE Clients
(client_id INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE ClientActivity
(client_id INTEGER NOT NULL
REFERENCES Clients (client_id)
ON UPDATE CASCADE,
contact_user_id INTEGER NOT NULL,
REFERENCES Users (user_id)
ON UPDATE CASCADE,
contact_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (client_id, contact_user_id, activity_date));
SELECT client_id, contact_user_id, activity_date
FROM ClientActivity AS A1
WHERE A1.contact_date
= (SELECT MAX(A2.contact_date)
FROM ClientActivity AS A
WHERE A1.client_id = A2.client_id);
I am assuming that a client has to have had at least one contact. If
nothing else, to sign them as a client! There is no need for the
outer join or the Clients table.

HELP sql server 2005 express connection hangs up

Hi

I got an access 2002 application front end with a sql server 2005 express back end. Some of my clients are having some difficulties. After using the application for a while, some of the users are finding that the system just hangs up. It usually happens after the front end application has been running for about an hour (sometimes sooner and sometimes later). There are perhaps 1 to 5 concurrent users and I have checked to see if there are any firewalls stalling it (I think I check all of them)- Is there any way that SQL Server 2005 express could be caused to just stall- This even occurs with the odd laptop. All the appropriate protocols are enabled as well. These databses are not very large.

ANY HELP WOULD BE GREATLY APPRETIATED!!!

Thanks

Frank Srebot

Moved thread to the SQL Server Express forum.|||

hi Frank,

what do you mean by "just hangs up"? does it completely stalls requiring a reboot, or it's "sleeeping" for just a while and then restarts working "as expected" or the like?

to start, few things to consider..

SQLExpress sets the "autoclose" property of it's created databases to true, and this causes the dbs to be shut down when not in use, meaning that tyey will be closed if no active connection references them.. this involves a little overhead at next re-use as the dbs must be re-open, but I do not think this is your problem... anyway, the eventual related "problem" can be workaround modifying the relative database property via sp_dboption database's system stored procedure call...

"autoshrink" database property is even set to true, and this causes, at engine scheduled time frames, the eventual shrinking of the involved databases, so that when lots of insert/delete operations are performed (actually lots of deletes), the engine wakes up a thread to shrink (when necessary) the databases, requiring some time to execute..

if the autogrowth property of the database's datafiles and logfiles is set to true and the engine states new file space is required, the engine enlarges the files (when needed) and this will obviously involve some time as well...

other non SQL Server related issues includes OS's scheduled tasks requiring lots of CPU and/or I/O..

but it's hard to solve this way

regards

|||

Thanks for the great and quick response.

To clarify, sql server just hangs up meaning that the application displays an hourglass and eventually the sql connection is lost and an error message is given. I was doing some research and I was wondering if the problem could be in the connection pooling configuration- currently the setting are that pool connections are enabled by default in the ODBC config settings- the databases which I am dealing are quite small- would any one have any ideas perhaps along these lines?

Thanks

Frank srebot

|||

Hi

This is an update to my connection Problem with Access 2002 to SQL SERVER EXPRESS 2005.

We are having random disconnects on the client side with Access putting up a "Connection Failure" dialog box even when the user is actively entering records into the system. Have any of you ever encountered this situation? We have disabled all TCP offloading engine technology on the machine thinking this was causing a problem with SQL Server as well as changing network cards to a whole different brand. We've pretty much ruled out the physical network at this point because we have changed cables and moved to another port on a different switch to no avail.

Is there some timeout setting or connection pooling setting that I am unaware of at the SQL Server level that has a problem interacting with Windows Server 2003 or Windows XP? I have checked and double checked all of the server settings between the old machine and the new and they are identical.

I did read that the connection pooling may be stressed and the pool of connections are 'Leaking'. This might be due to a bad cable or connection, but thats all I have found out.

Any info would be greatly appreciated.

Thanks

Frank