HELP Access to sql server 2005 express connection FAILS UPDATE

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

Help ! Lost clients sites

"HELP !! We've lost about 25 client's websites. The databases were backed up along with all the actual files contained within each CSK….in addition, all the original databases are intact & can be reattached to the new SQL server…..the problem that exists where the original CSK files do not recognize the original database once it is reattached to the new SQL server. Any help would be most appreciated.

This is the error……

Login failed for user 'DARRYL1\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'DARRYL1\ASPNET'.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Login failed for user 'DARRYL1\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44 System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38 ASPNET.StarterKit.Communities.CommunityUtility.GetAllCommunitiesFromDB() +93 ASPNET.StarterKit.Communities.CommunityUtility.GetAllCommunities() +58 ASPNET.StarterKit.Communities.CommunityUtility.GetCommunityInfo() +327 ASPNET.StarterKit.Communities.CommunitiesModule.Application_BeginRequest(Object source, EventArgs e) +221 System.Web.SyncEventExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute() +60 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +87

It looks like you either haven't set up the ASPNET account as a user inSQL Server, or given it permissions on those databases. It's aneasy enough fix though. From Enterprise Manager, under theSecurity node for that SQL Server instance, make sure that the ASPNETaccount is listed as a login, and if not add it. Once it'sadded, on the "Database Access" tab of the properties sheet for thatacocunt, you can give it the appropriate access to each of thosedatabases, and you'll be back up and running.

HELP - Replication Error

We have about 52 clients on SQL Server 2005 using anonymous pull
subscriptions (2 subs each).
One of these clients had an issue with disk space so we had to recreate the
subscriptions for the 2 databases. One subscription went fine but the second
is getting the following error:
The specified subscription type is invalid. Verify that the
-SubscriptionType parameter for the Merge Agent has been correctly specified.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201020
The -SubscriptionType parameter is set to 2. Nothing is different from the
other 100+ subscriptions we have created. I compared the working
subscription's step to the failing sub and they are identical except for the
db and pub name.
I have been unsuccesful in finding anything on the Internet. Does anybody
have a solution?
Thanks in advance!
can you try a subscriptiontype of 1?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RichardD" <RichardD@.discussions.microsoft.com> wrote in message
news:6733A022-8948-4D15-A116-58A5C81CCA94@.microsoft.com...
> We have about 52 clients on SQL Server 2005 using anonymous pull
> subscriptions (2 subs each).
> One of these clients had an issue with disk space so we had to recreate
> the
> subscriptions for the 2 databases. One subscription went fine but the
> second
> is getting the following error:
> The specified subscription type is invalid. Verify that the
> -SubscriptionType parameter for the Merge Agent has been correctly
> specified.
> (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201020
> The -SubscriptionType parameter is set to 2. Nothing is different from the
> other 100+ subscriptions we have created. I compared the working
> subscription's step to the failing sub and they are identical except for
> the
> db and pub name.
> I have been unsuccesful in finding anything on the Internet. Does anybody
> have a solution?
> Thanks in advance!

Help

I need some help...
SELECT
'YEAR' = year(entrydate)
,'MONTH'=month(entrydate)
,'Clients' = adversesurname
,COUNT(adversesurname) AS Numduplicate
FROM dbo.atlas_mast
where year(entrydate) = '2004' and month(entrydate) = '10'
GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
COUNT (adversesurname) > 1 )
compute count(adversesurname)
result:
year month name numduplicate
2004 10 ALBERTA 2
2004 10 ANDERSON 2
2004 10 BOISSONNAULT 3
2004 10 BROWN 2
2004 10 CALGARY 12
2004 10 CAMPBELL 3
2004 10 CANADA 2
2004 10 CITY 2
2004 10 COSCO 2
2004 10 CROWN 12
2004 10 EDMONTON 13
2004 10 EVERGREEN 2
2004 10 FRANCIS 2
2004 10 GRANDE PRAIRIE 2
2004 10 GREAT 2
2004 10 LAFONTAINE 2
2004 10 MACDONALD 2
2004 10 MARTIN 2
2004 10 MARTINSON 2
2004 10 MORRIS 2
2004 10 NA 3
2004 10 NONE 13
2004 10 PUBLIC 2
2004 10 RCMP 5
2004 10 REMILLARD 2
2004 10 ROYAL 2
2004 10 RUFF 2
2004 10 SCHMIDT 2
2004 10 SOHAIL 2
2004 10 SPENCER 2
2004 10 TD 2
2004 10 THE 2
2004 10 THOMPSON 2
2004 10 WHITECOURT 2
2004 10 WORKERS 2
cnt
35
Can anyone tell me how can i make the where line to go through months
without hardcoding...to count each row for month october, november,
december and just show the totals without showing the names
this is how i want it to show.
2004 10 35
2004 11 100
2004 12 333
2005 01 1230
2005 02 1233
etc.
can anyone help me?What about:
SELECT
'YEAR' = year(entrydate)
,'MONTH'=month(entrydate)
,COUNT(adversesurname) AS Numduplicate
FROM dbo.atlas_mast
GROUP BY year(entrydate), month(entrydate)
HAVING COUNT (adversesurname) > 1
HTH, jens Suessmeyer.|||DROP TABLE Result
CREATE TABLE Result
(
entrydate datetime,
adversesurname char(50),
numduplicate int
)
Go
INSERT Result VALUES('10/01/2004','NAME1',1)
INSERT Result VALUES('10/01/2004','NAME1',15)
INSERT Result VALUES('10/01/2004','NAME2',20)
INSERT Result VALUES('11/01/2004','NAME3',50)
INSERT Result VALUES('11/01/2004','NAME4',50)
INSERT Result VALUES('12/01/2004','NAME5',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('01/01/2005','NAME6',800)
INSERT Result VALUES('01/01/2005','NAME6',200)
INSERT Result VALUES('01/01/2005','NAME6',230)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('02/01/2005','NAME6',412)
INSERT Result VALUES('02/01/2005','NAME6',411)
INSERT Result VALUES('02/01/2005','NAME6',410)
Go
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135275094.304489.196740@.z14g2000cwz.googlegroups.com...
>I need some help...
> SELECT
> 'YEAR' = year(entrydate)
> ,'MONTH'=month(entrydate)
> ,'Clients' = adversesurname
> ,COUNT(adversesurname) AS Numduplicate
> FROM dbo.atlas_mast
> where year(entrydate) = '2004' and month(entrydate) = '10'
> GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
> COUNT (adversesurname) > 1 )
> compute count(adversesurname)
> result:
> year month name numduplicate
> 2004 10 ALBERTA 2
> 2004 10 ANDERSON 2
> 2004 10 BOISSONNAULT 3
> 2004 10 BROWN 2
> 2004 10 CALGARY 12
> 2004 10 CAMPBELL 3
> 2004 10 CANADA 2
> 2004 10 CITY 2
> 2004 10 COSCO 2
> 2004 10 CROWN 12
> 2004 10 EDMONTON 13
> 2004 10 EVERGREEN 2
> 2004 10 FRANCIS 2
> 2004 10 GRANDE PRAIRIE 2
> 2004 10 GREAT 2
> 2004 10 LAFONTAINE 2
> 2004 10 MACDONALD 2
> 2004 10 MARTIN 2
> 2004 10 MARTINSON 2
> 2004 10 MORRIS 2
> 2004 10 NA 3
> 2004 10 NONE 13
> 2004 10 PUBLIC 2
> 2004 10 RCMP 5
> 2004 10 REMILLARD 2
> 2004 10 ROYAL 2
> 2004 10 RUFF 2
> 2004 10 SCHMIDT 2
> 2004 10 SOHAIL 2
> 2004 10 SPENCER 2
> 2004 10 TD 2
> 2004 10 THE 2
> 2004 10 THOMPSON 2
> 2004 10 WHITECOURT 2
> 2004 10 WORKERS 2
> cnt
> 35
> Can anyone tell me how can i make the where line to go through months
> without hardcoding...to count each row for month october, november,
> december and just show the totals without showing the names
> this is how i want it to show.
> 2004 10 35
> 2004 11 100
> 2004 12 333
> 2005 01 1230
> 2005 02 1233
> etc.
> can anyone help me?
>|||Guess the op just wanted to count the rows rather than summing up, uh ?
-Jens.|||i tried that but it will only give me the total count of clients per
year, month
what i want to get is the count of duplicates from the total of clients
per month and year.
also i need to order them by year, month
it would show like this
Year month total/names Total multiple Total not mult % of mult
2004 10 450 50 400 12.50
2004 11 300 60 240 25
the above code will only give me the total/names which is good but
where i run into problems is when i get to calculate the total multiple
and single per month.|||this not what you are looking for ?
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:eVNuidyBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE Result
> CREATE TABLE Result
> (
> entrydate datetime,
> adversesurname char(50),
> numduplicate int
> )
> Go
> INSERT Result VALUES('10/01/2004','NAME1',1)
> INSERT Result VALUES('10/01/2004','NAME1',15)
> INSERT Result VALUES('10/01/2004','NAME2',20)
> INSERT Result VALUES('11/01/2004','NAME3',50)
> INSERT Result VALUES('11/01/2004','NAME4',50)
> INSERT Result VALUES('12/01/2004','NAME5',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('01/01/2005','NAME6',800)
> INSERT Result VALUES('01/01/2005','NAME6',200)
> INSERT Result VALUES('01/01/2005','NAME6',230)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('02/01/2005','NAME6',412)
> INSERT Result VALUES('02/01/2005','NAME6',411)
> INSERT Result VALUES('02/01/2005','NAME6',410)
> Go
> SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
> SUM(numduplicate) FROM Result
> WHERE numduplicate > 1
> GROUP BY MONTH(entrydate), YEAR(entrydate)
>
> "melindam" <cargowise_melindam@.yahoo.com> wrote in message
> news:1135275094.304489.196740@.z14g2000cwz.googlegroups.com...
>|||no thats not what i was looking for, thank you though.
David J. Cartwright
Dec 22, 11:41 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David J. Cartwright" <davidcartwri...@.hotmail.com> - Find
messages by this author
Date: Thu, 22 Dec 2005 13:41:54 -0500
Local: Thurs, Dec 22 2005 11:41 am
Subject: Re: Help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
DROP TABLE Result
CREATE TABLE Result
(
entrydate datetime,
adversesurname char(50),
numduplicate int
)
Go
INSERT Result VALUES('10/01/2004','NAME1',1)
INSERT Result VALUES('10/01/2004','NAME1',15)
INSERT Result VALUES('10/01/2004','NAME2',20)
INSERT Result VALUES('11/01/2004','NAME3',50)
INSERT Result VALUES('11/01/2004','NAME4',50)
INSERT Result VALUES('12/01/2004','NAME5',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('01/01/2005','NAME6',800)
INSERT Result VALUES('01/01/2005','NAME6',200)
INSERT Result VALUES('01/01/2005','NAME6',230)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('02/01/2005','NAME6',412)
INSERT Result VALUES('02/01/2005','NAME6',411)
INSERT Result VALUES('02/01/2005','NAME6',410)
Go
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)|||what did it not do?
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135278730.580717.269820@.o13g2000cwo.googlegroups.com...
> no thats not what i was looking for, thank you though.
> David J. Cartwright
> Dec 22, 11:41 am show options
> Newsgroups: microsoft.public.sqlserver.server
> From: "David J. Cartwright" <davidcartwri...@.hotmail.com> - Find
> messages by this author
> Date: Thu, 22 Dec 2005 13:41:54 -0500
> Local: Thurs, Dec 22 2005 11:41 am
> Subject: Re: Help
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse
> DROP TABLE Result
> CREATE TABLE Result
> (
>
> entrydate datetime,
> adversesurname char(50),
> numduplicate int
> )
> Go
> INSERT Result VALUES('10/01/2004','NAME1',1)
> INSERT Result VALUES('10/01/2004','NAME1',15)
> INSERT Result VALUES('10/01/2004','NAME2',20)
> INSERT Result VALUES('11/01/2004','NAME3',50)
> INSERT Result VALUES('11/01/2004','NAME4',50)
> INSERT Result VALUES('12/01/2004','NAME5',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('01/01/2005','NAME6',800)
> INSERT Result VALUES('01/01/2005','NAME6',200)
> INSERT Result VALUES('01/01/2005','NAME6',230)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('02/01/2005','NAME6',412)
> INSERT Result VALUES('02/01/2005','NAME6',411)
> INSERT Result VALUES('02/01/2005','NAME6',410)
> Go
> SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
> SUM(numduplicate) FROM Result
> WHERE numduplicate > 1
> GROUP BY MONTH(entrydate), YEAR(entrydate)
>|||I dont need to drop the table and create a table. I already have a
table an existing table where im extracting the information.
and i dont want it to hardcode the dates...the dates will be selected
from the table.|||um...review my post again...i provided the add create as i used it to write
the select statement
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135282541.079930.285980@.z14g2000cwz.googlegroups.com...
>I dont need to drop the table and create a table. I already have a
> table an existing table where im extracting the information.
> and i dont want it to hardcode the dates...the dates will be selected
> from the table.
>

Help

I need some help...
SELECT
'YEAR' = year(entrydate)
,'MONTH'=month(entrydate)
,'Clients' = adversesurname
,COUNT(adversesurname) AS Numduplicate
FROM dbo.atlas_mast
where year(entrydate) = '2004' and month(entrydate) = '10'
GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
COUNT (adversesurname) > 1 )
compute count(adversesurname)
result:
year month name numduplicate
200410ALBERTA2
200410ANDERSON2
200410BOISSONNAULT3
200410BROWN2
200410CALGARY12
200410CAMPBELL3
200410CANADA2
200410CITY2
200410COSCO2
200410CROWN12
200410EDMONTON13
200410EVERGREEN2
200410FRANCIS2
200410GRANDE PRAIRIE2
200410GREAT2
200410LAFONTAINE2
200410MACDONALD2
200410MARTIN2
200410MARTINSON2
200410MORRIS2
200410NA3
200410NONE13
200410PUBLIC2
200410RCMP5
200410REMILLARD2
200410ROYAL2
200410RUFF2
200410SCHMIDT2
200410SOHAIL2
200410SPENCER2
200410TD2
200410THE2
200410THOMPSON2
200410WHITECOURT2
200410WORKERS2
cnt
35
Can anyone tell me how can i make the where line to go through months
without hardcoding...to count each row for month october, november,
december and just show the totals without showing the names
this is how i want it to show.
2004 10 35
2004 11 100
2004 12 333
2005 01 1230
2005 02 1233
etc.
can anyone help me?
What about:
SELECT
'YEAR' = year(entrydate)
,'MONTH'=month(entrydate)
,COUNT(adversesurname) AS Numduplicate
FROM dbo.atlas_mast
GROUP BY year(entrydate), month(entrydate)
HAVING COUNT (adversesurname) > 1
HTH, jens Suessmeyer.
|||DROP TABLE Result
CREATE TABLE Result
(
entrydate datetime,
adversesurname char(50),
numduplicate int
)
Go
INSERT Result VALUES('10/01/2004','NAME1',1)
INSERT Result VALUES('10/01/2004','NAME1',15)
INSERT Result VALUES('10/01/2004','NAME2',20)
INSERT Result VALUES('11/01/2004','NAME3',50)
INSERT Result VALUES('11/01/2004','NAME4',50)
INSERT Result VALUES('12/01/2004','NAME5',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('01/01/2005','NAME6',800)
INSERT Result VALUES('01/01/2005','NAME6',200)
INSERT Result VALUES('01/01/2005','NAME6',230)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('02/01/2005','NAME6',412)
INSERT Result VALUES('02/01/2005','NAME6',411)
INSERT Result VALUES('02/01/2005','NAME6',410)
Go
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135275094.304489.196740@.z14g2000cwz.googlegr oups.com...
>I need some help...
> SELECT
> 'YEAR' = year(entrydate)
> ,'MONTH'=month(entrydate)
> ,'Clients' = adversesurname
> ,COUNT(adversesurname) AS Numduplicate
> FROM dbo.atlas_mast
> where year(entrydate) = '2004' and month(entrydate) = '10'
> GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
> COUNT (adversesurname) > 1 )
> compute count(adversesurname)
> result:
> year month name numduplicate
> 2004 10 ALBERTA 2
> 2004 10 ANDERSON 2
> 2004 10 BOISSONNAULT 3
> 2004 10 BROWN 2
> 2004 10 CALGARY 12
> 2004 10 CAMPBELL 3
> 2004 10 CANADA 2
> 2004 10 CITY 2
> 2004 10 COSCO 2
> 2004 10 CROWN 12
> 2004 10 EDMONTON 13
> 2004 10 EVERGREEN 2
> 2004 10 FRANCIS 2
> 2004 10 GRANDE PRAIRIE 2
> 2004 10 GREAT 2
> 2004 10 LAFONTAINE 2
> 2004 10 MACDONALD 2
> 2004 10 MARTIN 2
> 2004 10 MARTINSON 2
> 2004 10 MORRIS 2
> 2004 10 NA 3
> 2004 10 NONE 13
> 2004 10 PUBLIC 2
> 2004 10 RCMP 5
> 2004 10 REMILLARD 2
> 2004 10 ROYAL 2
> 2004 10 RUFF 2
> 2004 10 SCHMIDT 2
> 2004 10 SOHAIL 2
> 2004 10 SPENCER 2
> 2004 10 TD 2
> 2004 10 THE 2
> 2004 10 THOMPSON 2
> 2004 10 WHITECOURT 2
> 2004 10 WORKERS 2
> cnt
> 35
> Can anyone tell me how can i make the where line to go through months
> without hardcoding...to count each row for month october, november,
> december and just show the totals without showing the names
> this is how i want it to show.
> 2004 10 35
> 2004 11 100
> 2004 12 333
> 2005 01 1230
> 2005 02 1233
> etc.
> can anyone help me?
>
|||Guess the op just wanted to count the rows rather than summing up, uh ?
-Jens.
|||i tried that but it will only give me the total count of clients per
year, month
what i want to get is the count of duplicates from the total of clients
per month and year.
also i need to order them by year, month
it would show like this
Year month total/names Total multiple Total not mult % of mult
2004 10 450 50 400 12.50
2004 11 300 60 240 25
the above code will only give me the total/names which is good but
where i run into problems is when i get to calculate the total multiple
and single per month.
|||this not what you are looking for ?
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:eVNuidyBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE Result
> CREATE TABLE Result
> (
> entrydate datetime,
> adversesurname char(50),
> numduplicate int
> )
> Go
> INSERT Result VALUES('10/01/2004','NAME1',1)
> INSERT Result VALUES('10/01/2004','NAME1',15)
> INSERT Result VALUES('10/01/2004','NAME2',20)
> INSERT Result VALUES('11/01/2004','NAME3',50)
> INSERT Result VALUES('11/01/2004','NAME4',50)
> INSERT Result VALUES('12/01/2004','NAME5',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('01/01/2005','NAME6',800)
> INSERT Result VALUES('01/01/2005','NAME6',200)
> INSERT Result VALUES('01/01/2005','NAME6',230)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('02/01/2005','NAME6',412)
> INSERT Result VALUES('02/01/2005','NAME6',411)
> INSERT Result VALUES('02/01/2005','NAME6',410)
> Go
> SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
> SUM(numduplicate) FROM Result
> WHERE numduplicate > 1
> GROUP BY MONTH(entrydate), YEAR(entrydate)
>
> "melindam" <cargowise_melindam@.yahoo.com> wrote in message
> news:1135275094.304489.196740@.z14g2000cwz.googlegr oups.com...
>
|||no thats not what i was looking for, thank you though.
David J. Cartwright
Dec 22, 11:41 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David J. Cartwright" <davidcartwri...@.hotmail.com> - Find
messages by this author
Date: Thu, 22 Dec 2005 13:41:54 -0500
Local: Thurs, Dec 22 2005 11:41 am
Subject: Re: Help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
DROP TABLE Result
CREATE TABLE Result
(
entrydate datetime,
adversesurname char(50),
numduplicate int
)
Go
INSERT Result VALUES('10/01/2004','NAME1',1)
INSERT Result VALUES('10/01/2004','NAME1',15)
INSERT Result VALUES('10/01/2004','NAME2',20)
INSERT Result VALUES('11/01/2004','NAME3',50)
INSERT Result VALUES('11/01/2004','NAME4',50)
INSERT Result VALUES('12/01/2004','NAME5',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('01/01/2005','NAME6',800)
INSERT Result VALUES('01/01/2005','NAME6',200)
INSERT Result VALUES('01/01/2005','NAME6',230)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('02/01/2005','NAME6',412)
INSERT Result VALUES('02/01/2005','NAME6',411)
INSERT Result VALUES('02/01/2005','NAME6',410)
Go
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
|||what did it not do?
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135278730.580717.269820@.o13g2000cwo.googlegr oups.com...
> no thats not what i was looking for, thank you though.
> David J. Cartwright
> Dec 22, 11:41 am show options
> Newsgroups: microsoft.public.sqlserver.server
> From: "David J. Cartwright" <davidcartwri...@.hotmail.com> - Find
> messages by this author
> Date: Thu, 22 Dec 2005 13:41:54 -0500
> Local: Thurs, Dec 22 2005 11:41 am
> Subject: Re: Help
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse
> DROP TABLE Result
> CREATE TABLE Result
> (
>
> entrydate datetime,
> adversesurname char(50),
> numduplicate int
> )
> Go
> INSERT Result VALUES('10/01/2004','NAME1',1)
> INSERT Result VALUES('10/01/2004','NAME1',15)
> INSERT Result VALUES('10/01/2004','NAME2',20)
> INSERT Result VALUES('11/01/2004','NAME3',50)
> INSERT Result VALUES('11/01/2004','NAME4',50)
> INSERT Result VALUES('12/01/2004','NAME5',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('01/01/2005','NAME6',800)
> INSERT Result VALUES('01/01/2005','NAME6',200)
> INSERT Result VALUES('01/01/2005','NAME6',230)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('02/01/2005','NAME6',412)
> INSERT Result VALUES('02/01/2005','NAME6',411)
> INSERT Result VALUES('02/01/2005','NAME6',410)
> Go
> SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
> SUM(numduplicate) FROM Result
> WHERE numduplicate > 1
> GROUP BY MONTH(entrydate), YEAR(entrydate)
>
|||I dont need to drop the table and create a table. I already have a
table an existing table where im extracting the information.
and i dont want it to hardcode the dates...the dates will be selected
from the table.
|||um...review my post again...i provided the add create as i used it to write
the select statement
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135282541.079930.285980@.z14g2000cwz.googlegr oups.com...
>I dont need to drop the table and create a table. I already have a
> table an existing table where im extracting the information.
> and i dont want it to hardcode the dates...the dates will be selected
> from the table.
>

Help

I need some help...
SELECT
'YEAR' = year(entrydate)
,'MONTH'=month(entrydate)
,'Clients' = adversesurname
,COUNT(adversesurname) AS Numduplicate
FROM dbo.atlas_mast
where year(entrydate) = '2004' and month(entrydate) = '10'
GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
COUNT (adversesurname) > 1 )
compute count(adversesurname)
result:
year month name numduplicate
2004 10 ALBERTA 2
2004 10 ANDERSON 2
2004 10 BOISSONNAULT 3
2004 10 BROWN 2
2004 10 CALGARY 12
2004 10 CAMPBELL 3
2004 10 CANADA 2
2004 10 CITY 2
2004 10 COSCO 2
2004 10 CROWN 12
2004 10 EDMONTON 13
2004 10 EVERGREEN 2
2004 10 FRANCIS 2
2004 10 GRANDE PRAIRIE 2
2004 10 GREAT 2
2004 10 LAFONTAINE 2
2004 10 MACDONALD 2
2004 10 MARTIN 2
2004 10 MARTINSON 2
2004 10 MORRIS 2
2004 10 NA 3
2004 10 NONE 13
2004 10 PUBLIC 2
2004 10 RCMP 5
2004 10 REMILLARD 2
2004 10 ROYAL 2
2004 10 RUFF 2
2004 10 SCHMIDT 2
2004 10 SOHAIL 2
2004 10 SPENCER 2
2004 10 TD 2
2004 10 THE 2
2004 10 THOMPSON 2
2004 10 WHITECOURT 2
2004 10 WORKERS 2
cnt
35
Can anyone tell me how can i make the where line to go through months
without hardcoding...to count each row for month october, november,
december and just show the totals without showing the names
this is how i want it to show.
2004 10 35
2004 11 100
2004 12 333
2005 01 1230
2005 02 1233
etc.
can anyone help me?What about:
SELECT
'YEAR' = year(entrydate)
,'MONTH'=month(entrydate)
,COUNT(adversesurname) AS Numduplicate
FROM dbo.atlas_mast
GROUP BY year(entrydate), month(entrydate)
HAVING COUNT (adversesurname) > 1
HTH, jens Suessmeyer.|||DROP TABLE Result
CREATE TABLE Result
(
entrydate datetime,
adversesurname char(50),
numduplicate int
)
Go
INSERT Result VALUES('10/01/2004','NAME1',1)
INSERT Result VALUES('10/01/2004','NAME1',15)
INSERT Result VALUES('10/01/2004','NAME2',20)
INSERT Result VALUES('11/01/2004','NAME3',50)
INSERT Result VALUES('11/01/2004','NAME4',50)
INSERT Result VALUES('12/01/2004','NAME5',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('01/01/2005','NAME6',800)
INSERT Result VALUES('01/01/2005','NAME6',200)
INSERT Result VALUES('01/01/2005','NAME6',230)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('02/01/2005','NAME6',412)
INSERT Result VALUES('02/01/2005','NAME6',411)
INSERT Result VALUES('02/01/2005','NAME6',410)
Go
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135275094.304489.196740@.z14g2000cwz.googlegroups.com...
>I need some help...
> SELECT
> 'YEAR' = year(entrydate)
> ,'MONTH'=month(entrydate)
> ,'Clients' = adversesurname
> ,COUNT(adversesurname) AS Numduplicate
> FROM dbo.atlas_mast
> where year(entrydate) = '2004' and month(entrydate) = '10'
> GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
> COUNT (adversesurname) > 1 )
> compute count(adversesurname)
> result:
> year month name numduplicate
> 2004 10 ALBERTA 2
> 2004 10 ANDERSON 2
> 2004 10 BOISSONNAULT 3
> 2004 10 BROWN 2
> 2004 10 CALGARY 12
> 2004 10 CAMPBELL 3
> 2004 10 CANADA 2
> 2004 10 CITY 2
> 2004 10 COSCO 2
> 2004 10 CROWN 12
> 2004 10 EDMONTON 13
> 2004 10 EVERGREEN 2
> 2004 10 FRANCIS 2
> 2004 10 GRANDE PRAIRIE 2
> 2004 10 GREAT 2
> 2004 10 LAFONTAINE 2
> 2004 10 MACDONALD 2
> 2004 10 MARTIN 2
> 2004 10 MARTINSON 2
> 2004 10 MORRIS 2
> 2004 10 NA 3
> 2004 10 NONE 13
> 2004 10 PUBLIC 2
> 2004 10 RCMP 5
> 2004 10 REMILLARD 2
> 2004 10 ROYAL 2
> 2004 10 RUFF 2
> 2004 10 SCHMIDT 2
> 2004 10 SOHAIL 2
> 2004 10 SPENCER 2
> 2004 10 TD 2
> 2004 10 THE 2
> 2004 10 THOMPSON 2
> 2004 10 WHITECOURT 2
> 2004 10 WORKERS 2
> cnt
> 35
> Can anyone tell me how can i make the where line to go through months
> without hardcoding...to count each row for month october, november,
> december and just show the totals without showing the names
> this is how i want it to show.
> 2004 10 35
> 2004 11 100
> 2004 12 333
> 2005 01 1230
> 2005 02 1233
> etc.
> can anyone help me?
>|||Guess the op just wanted to count the rows rather than summing up, uh ?
-Jens.|||i tried that but it will only give me the total count of clients per
year, month
what i want to get is the count of duplicates from the total of clients
per month and year.
also i need to order them by year, month
it would show like this
Year month total/names Total multiple Total not mult % of mult
2004 10 450 50 400 12.50
2004 11 300 60 240 25
the above code will only give me the total/names which is good but
where i run into problems is when i get to calculate the total multiple
and single per month.|||this not what you are looking for ?
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:eVNuidyBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE Result
> CREATE TABLE Result
> (
> entrydate datetime,
> adversesurname char(50),
> numduplicate int
> )
> Go
> INSERT Result VALUES('10/01/2004','NAME1',1)
> INSERT Result VALUES('10/01/2004','NAME1',15)
> INSERT Result VALUES('10/01/2004','NAME2',20)
> INSERT Result VALUES('11/01/2004','NAME3',50)
> INSERT Result VALUES('11/01/2004','NAME4',50)
> INSERT Result VALUES('12/01/2004','NAME5',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('01/01/2005','NAME6',800)
> INSERT Result VALUES('01/01/2005','NAME6',200)
> INSERT Result VALUES('01/01/2005','NAME6',230)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('02/01/2005','NAME6',412)
> INSERT Result VALUES('02/01/2005','NAME6',411)
> INSERT Result VALUES('02/01/2005','NAME6',410)
> Go
> SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
> SUM(numduplicate) FROM Result
> WHERE numduplicate > 1
> GROUP BY MONTH(entrydate), YEAR(entrydate)
>
> "melindam" <cargowise_melindam@.yahoo.com> wrote in message
> news:1135275094.304489.196740@.z14g2000cwz.googlegroups.com...
>>I need some help...
>> SELECT
>> 'YEAR' = year(entrydate)
>> ,'MONTH'=month(entrydate)
>> ,'Clients' = adversesurname
>> ,COUNT(adversesurname) AS Numduplicate
>> FROM dbo.atlas_mast
>> where year(entrydate) = '2004' and month(entrydate) = '10'
>> GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
>> COUNT (adversesurname) > 1 )
>> compute count(adversesurname)
>> result:
>> year month name numduplicate
>> 2004 10 ALBERTA 2
>> 2004 10 ANDERSON 2
>> 2004 10 BOISSONNAULT 3
>> 2004 10 BROWN 2
>> 2004 10 CALGARY 12
>> 2004 10 CAMPBELL 3
>> 2004 10 CANADA 2
>> 2004 10 CITY 2
>> 2004 10 COSCO 2
>> 2004 10 CROWN 12
>> 2004 10 EDMONTON 13
>> 2004 10 EVERGREEN 2
>> 2004 10 FRANCIS 2
>> 2004 10 GRANDE PRAIRIE 2
>> 2004 10 GREAT 2
>> 2004 10 LAFONTAINE 2
>> 2004 10 MACDONALD 2
>> 2004 10 MARTIN 2
>> 2004 10 MARTINSON 2
>> 2004 10 MORRIS 2
>> 2004 10 NA 3
>> 2004 10 NONE 13
>> 2004 10 PUBLIC 2
>> 2004 10 RCMP 5
>> 2004 10 REMILLARD 2
>> 2004 10 ROYAL 2
>> 2004 10 RUFF 2
>> 2004 10 SCHMIDT 2
>> 2004 10 SOHAIL 2
>> 2004 10 SPENCER 2
>> 2004 10 TD 2
>> 2004 10 THE 2
>> 2004 10 THOMPSON 2
>> 2004 10 WHITECOURT 2
>> 2004 10 WORKERS 2
>> cnt
>> 35
>> Can anyone tell me how can i make the where line to go through months
>> without hardcoding...to count each row for month october, november,
>> december and just show the totals without showing the names
>> this is how i want it to show.
>> 2004 10 35
>> 2004 11 100
>> 2004 12 333
>> 2005 01 1230
>> 2005 02 1233
>> etc.
>> can anyone help me?
>|||no thats not what i was looking for, thank you though.
David J. Cartwright
Dec 22, 11:41 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David J. Cartwright" <davidcartwri...@.hotmail.com> - Find
messages by this author
Date: Thu, 22 Dec 2005 13:41:54 -0500
Local: Thurs, Dec 22 2005 11:41 am
Subject: Re: Help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
DROP TABLE Result
CREATE TABLE Result
(
entrydate datetime,
adversesurname char(50),
numduplicate int
)
Go
INSERT Result VALUES('10/01/2004','NAME1',1)
INSERT Result VALUES('10/01/2004','NAME1',15)
INSERT Result VALUES('10/01/2004','NAME2',20)
INSERT Result VALUES('11/01/2004','NAME3',50)
INSERT Result VALUES('11/01/2004','NAME4',50)
INSERT Result VALUES('12/01/2004','NAME5',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('01/01/2005','NAME6',800)
INSERT Result VALUES('01/01/2005','NAME6',200)
INSERT Result VALUES('01/01/2005','NAME6',230)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('02/01/2005','NAME6',412)
INSERT Result VALUES('02/01/2005','NAME6',411)
INSERT Result VALUES('02/01/2005','NAME6',410)
Go
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)|||what did it not do?
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135278730.580717.269820@.o13g2000cwo.googlegroups.com...
> no thats not what i was looking for, thank you though.
> David J. Cartwright
> Dec 22, 11:41 am show options
> Newsgroups: microsoft.public.sqlserver.server
> From: "David J. Cartwright" <davidcartwri...@.hotmail.com> - Find
> messages by this author
> Date: Thu, 22 Dec 2005 13:41:54 -0500
> Local: Thurs, Dec 22 2005 11:41 am
> Subject: Re: Help
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse
> DROP TABLE Result
> CREATE TABLE Result
> (
>
> entrydate datetime,
> adversesurname char(50),
> numduplicate int
> )
> Go
> INSERT Result VALUES('10/01/2004','NAME1',1)
> INSERT Result VALUES('10/01/2004','NAME1',15)
> INSERT Result VALUES('10/01/2004','NAME2',20)
> INSERT Result VALUES('11/01/2004','NAME3',50)
> INSERT Result VALUES('11/01/2004','NAME4',50)
> INSERT Result VALUES('12/01/2004','NAME5',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('01/01/2005','NAME6',800)
> INSERT Result VALUES('01/01/2005','NAME6',200)
> INSERT Result VALUES('01/01/2005','NAME6',230)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('02/01/2005','NAME6',412)
> INSERT Result VALUES('02/01/2005','NAME6',411)
> INSERT Result VALUES('02/01/2005','NAME6',410)
> Go
> SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
> SUM(numduplicate) FROM Result
> WHERE numduplicate > 1
> GROUP BY MONTH(entrydate), YEAR(entrydate)
>|||I dont need to drop the table and create a table. I already have a
table an existing table where im extracting the information.
and i dont want it to hardcode the dates...the dates will be selected
from the table.|||um...review my post again...i provided the add create as i used it to write
the select statement
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135282541.079930.285980@.z14g2000cwz.googlegroups.com...
>I dont need to drop the table and create a table. I already have a
> table an existing table where im extracting the information.
> and i dont want it to hardcode the dates...the dates will be selected
> from the table.
>|||I dont understand what is RESULT?
i want to count the total callers by surname and then calculate in a
column how many surnames are duplicate and another column how many
surnames apear only once and then column with % of duplicates from the
total.
I dont know how i can create the counts and print in columns and show
the total count of multiple for each month. Like i showed it above.
Can you please clarify your logic? thank you.|||I dont have permision to create a table or drop a table from my
database.
I only have the table that is called atlas_mast and using fields
entrydate and adversesurname. To count the total of callers.