Clearing Out Tempdb Faster?

Feb 21, 1999

Hello:

We have been testing a new vendor_purchased application and are now
running some month-end processes/jobs now. One of the processes that we
kicked off stopped when a 2gb tempdb filled up with only 5mb or so remaini
g. In reviewing what happened, I noticed that tempdb is in the process of
clearing itself out slowly when we restarted the process. The database has
80% of its disk space remaining and the transaction log has also 75%
remaining.

My questions are:

1) Although I don't think so, is there nay way of speeding up the process
of tempdb clearing out the data in it?

2) We will need to examine what sql code the vendor has used that caused
this to happen. Aside from group by and order by, if there are a lot of
'select into' code, what alternatives do we have?

Any information that can be provided will be fine. THanks in advance.


DAvid Spaisman

View 1 Replies


ADVERTISEMENT

Clearing Tempdb

Oct 7, 1999

We have a production sql server V6.5 sp5a (running on NT 4.0 sp3) that has a tempdb that is fulling up about every two weeks. (The log to tempdb is okay, it is not filling up). Tempdb is defined as 400 MB. I have been reading about "The two most common db errors"; 1) The db log ran out of space and 2) The db ran out of space. We are receiving "The db ran out of space." Once tempdb gets near full is there anyway to clear tempdb without rebooting the nt server? Is there a stored procedure that can be scheduled to free up tempdb?

My books tell me the tempdb is used for creating temporary tables or storing temporary information. For example, a temporary table in a stored procedure or sql may create a temporary work table as a result of a query with a 'group by' or 'order by' clause. If these tables are temporary why don't sql release them or free up the space used by tempdb?

Thanks in advance!

View 1 Replies View Related

Moving Tempdb Onto A Faster Drive

Feb 27, 2006

Hello all ...

is there a standard procedure or document that explains how to:

Expand tempdb onto a faster drive ... making it larger

then ...

Remove the small portion of tempdb from my c: drive to reduce contention?

Thanks!

Doug

View 3 Replies View Related

Clearing A Table?

Jun 28, 2006

Hello, and sorry if this is really obvious.
Is there a way to wipe all data from a table, besides the obvious manual way?

View 1 Replies View Related

Transaction Log Not Clearing

Jul 5, 1999

We have a SQL Server 6.5 with SP5a installed and are having problems with the Transaction log not clearing every night when a scheduled backup takes place.
Is there anyway to debug this?

The Scheduled task that is run is
"DUMP DATABASE DBSALES TO DBSALESBK with INIT
DUMP TRANSACTION DBSALES WITH TRUNCATE_ONLY
DUMP DATABASE DISTRIBUTION TO DSBK WITH INIT
DUMP TRANSACTION DISTRIBUTION WITH TRUNCATE_ONLY"

I have also run on it DBCC Checktable(Syslogs) but it all seems fine. Any help would be appreciated.

View 3 Replies View Related

Log File Not Clearing

Oct 5, 1998

When I truncate the transaction log the log space available does not change?

View 2 Replies View Related

Database Clearing?

May 9, 2008

Hello!

I'd like to ask you what means the term "database clearing", if such a concept exists in the Database Theory.
Maybe this is not directly related to SQL Server but any help would be very appreciated. So please anybody who have an idea of what this could mean is welcome to reply here

Thank you very much in advance.
With best regards,
Sorin

View 9 Replies View Related

Clearing A Table

Apr 8, 2007

I have entered some temporary values in a table located in an SQL database, to test a project to see if it was working. Now I want to remove the values so that later, the program I have created will be capable of using the table for it's true purpose. However, when I try to delete the values located there, I receive an error message. How can I clear the table?

View 2 Replies View Related

Clearing OutputBuffer

Feb 27, 2008



Hello All,

I am here again with a query.

In SQL SERVER 2000, there is a command DBCC OutputBuffer which returns the current output buffer in hexadecimal and ASCII format for the specified system process ID.


Is it so that everytime we execute a stored procedure the contents of the outputbuffer are automatically cleared so that the new contents are inserted into the outputbuffer ?

If it is not cleared then how can we clear this outputbuffer.

Thanks,
Kapadia Shalin P.

View 3 Replies View Related

Cache Clearing.

Dec 3, 2007

Hi all,

Few clarification needed.

1. Actually when does the cache gets cleared in SQL Server ?
2. Is clearing the cache in production environment recommended ?

3. What is the main purpose of DBCC DROPCLEANBUFFERS & DBCC FREEPROCCACHE commands ?

Thanks ,

DBLearner

View 9 Replies View Related

Tempdb Is Skipped. You Cannot Run A Query That Requires Tempdb

Jul 14, 2004

Has anyone seen the SQL Server error:

"tempdb is skipped. You cannot run a query that requires tempdb"?

We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks!
- Mike

View 11 Replies View Related

Clearing All Records In A Table

Apr 21, 2004

Hi

I have two questions:

1. Is there a way to clear all records in a table from within a store procedure? (Instead of having to go into the table and manually selecting all rows and deleting them that way.)

2. I want to sort a list of records so that the only one left are the ones with just single words in. So I have used


WHERE SearchText LIKE '% %'


to remove the cells with spaces in, but I need to be able to remove the ones with punctuation too. Is there an easy way to do this?

Thanking you in advance.

View 5 Replies View Related

Tables Not Clearing Appropriately...

Mar 5, 2007

Having issues with a table clearing out properly which is causing problems in the program.

By design there should only be one row in this table which updates with the most current record. However on this one system the table does not clear/update like it is supposed to, and instead starts adding multiple rows.

I have tried truncating the table but the problem does not resolve itself. Does anyone have any ideas as to why this might be happening?

Thanks!
Shawn

View 6 Replies View Related

Clearing Parameter Input

Sep 21, 2007

Hi everyone,
I have a question that I believe should be simple to answer yet I cannot find the answer anywhere. I am trying to make it possible for my report to clear the input box whenever the report is run or when anything in a dropdown list is selected. The reason why I want this is because my report has a dropdown list that inputs date ranges for "quick" report info. The other option is to manually type in the begin and end date. If anyone could help me out with this I would be very grateful.

Thanks,
Roy

View 8 Replies View Related

Inconsistent Clearing Of Connection Pool

Jan 15, 2008

I'm opening a large number of connections to a SQL server 2005 database using a SQL object based on the System.Data.SqlClient library. When I log out of the application, I call the ClearSQLConnectionPool method of the SQL object and this should clear the pool of these connections. This has worked in the past, but lately I have noticed some odd and very annoying behaviour.Very often I log out of the application and few, if any of the connections disappear. Just this morning I opened the application, logged out and all but one of the connections disappeared. Then I opened it again, logged out and none of the connections had gone. If I open the application again all of the past connections are still there plus the new ones.I know it works, its just very inconsistent. The problem may be with IIS, SQL or with library; I just can't tell which.Any ideas?Many thanks 

View 1 Replies View Related

Clearing Exception In Catch Block

Sep 3, 2004

Is there any way to clear out the exception from a previous Try/Catch block if I am nesting another Try/Catch block within it. I am executing a SQL Command and based on the Error number coming back decide whether or not to execute various other Sql commands. Now the Outer exception seems to be taking precedence over the Inner try block, and even though the code is stepped through for the inner try block it is never executed due to the Parent Exception. Is there any way to clear the exception received from the outer Try block? Here is a snippet of code:


Try

Cmd = New SqlCommand(Sql, Con)

Cmd.ExecuteNonQuery()

Catch t as SqlException

if t.Number = "2601" then

sql_upd = "<Text>"

Try

Cmd_upd = New SqlCommand(Sql_upd, Con)

Cmd_upd.ExecuteNonQuery()

Catch b as Exception

response.write("<Text>")

End Try

End If

End Try
Thanks,

View 3 Replies View Related

Clearing All Data From Tables In A Database

Jul 14, 2005

Hi,I want to clear all data from 200 tables in a database from a sql query. Some of the tables have got relationship as well. I want to clear data from there as well.What could be the best approach? Does anyone have an idea or script from where I could do the needful?Do let me know.Thanks

View 2 Replies View Related

Clearing Of Cache In Query Analyser

Apr 3, 2002

HI

May I know how to clear the cache area in the analyser as I need to
know the time taken for execution of an sql statement(query) before
and after creation of indexes.



tia
MK

View 1 Replies View Related

Clearing Multi-Value Parameter Choices

Oct 18, 2007



Hello,

I am searching for a resolution. I did a few searches on this site and could not gain the exact answer. I apologize is this thread is a duplicate.

I have written a report containing Multi-Values for Reporting Services. When a user selects the values for the report parameters, the checked values continue to be selected after the report is completed.

Is there a way to clear these parameters?

Some of our users run the same report more than once during a single web session.

Thank you for your attention!

View 3 Replies View Related

Target Conversation Endpoint Not Clearing Out

May 8, 2007

Hi There



I am ending conversations properly for a dialog, the end conversation at the target properly marks the conversation as closed in sys.conversation_endpoints and sends the EndDialog message to the initiator.

The initiator get the EndDialog message from the target and ends the conversation and it clears out of sys.conversation_endpoints.



I know that to avoid reply attacks that the conversation at the target will only clear out in 30 minutes.



However it has been a day now and the conversation is still sitting in sys.conversation_endpoints as CLOSED at the target.



How do i troubleshoot this ? Why is the conversation not clearing?



Thanx

View 5 Replies View Related

Clearing Execution Plan Cache

Sep 6, 2007



Hi,
We have an application which fetches data from a table which has approximately 1 million records.


1. Nearly 25 users will be using this application concurrently.
2. frequent updations will be done to the records in the geographyrolecurriculum table.
3. This table has 1 clustered index and 4 nonclustered index bounded to it.

Problem Statement:
1. Application runs smoothly for 15 - 20 days and after that all the screens throws timeout errors.
When i clear the sys.syscacheobjects its working fine again and screens get loaded quickly.
Please tell me how clearing the syscacheobjects makes the execution fast? and is this the correct way to solve the timeout issue or is there any other alternative?


2. Will the stored procs timeout if the tempdb is full ?

Thanks,
Arunprasad

View 5 Replies View Related

SQL Server 2012 :: Transaction Log Not Clearing At Checkpoint

Aug 14, 2015

I have a Database that is set to Simple Recovery mode.

It is not clearing the log and the transaction log is growing.

I thought that it would clear the log upon checkpoint?

How can I force the Transaction Log to clear since NO-TRUNCATE is no longer available?

View 9 Replies View Related

SQL Server 2012 :: Clearing Contents Of Temp Tables

Jun 6, 2015

I am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:

Msg 2714, Level 16, State 6, Line 6

There is already an object named '#mytemp2' in the database.

View 2 Replies View Related

Clearing All Messages From A Queue - For Automated Unit Test

Jun 4, 2007

Hi,

I am developing automated .Net Unit Tests, and as a prerequisite of each test, I would like to clear the service broker queues of any messages. Executing the

RECEIVE * FROM statement appears to only return a message at a time, and not all as I expected. Any ideas on how to make this happen, besides not having to delete the queues and then having to rebuild them?

Thanks,Eugen

View 3 Replies View Related

WARNING: Clearing Procedure Cache To Free Contiguous Memory.

Dec 6, 2000

We see the following message in our error log.
WARNING: Clearing procedure cache to free contiguous memory.
It is accomonpanied by fairly intensive CPU activity.
We get this roughly once per working day.

Anyone have any idea why, and what we can do to stop this?

Regads,

Jim Plant

View 2 Replies View Related

Reporting Services :: SSRS 2008 Report Invalid Parameter Warning Not Clearing Out

Jul 16, 2015

I am using SSRS 2008 and the reports we have use parameters of type Date/Time.  The reports work well when the parameter values are entered correctly.

When entering an invalid date format for one of the Date/Time parameters the following error is displayed "The value provided for the report '<parameter name>' is not valid for its type. (rsReportParameterTypeMismatch).  This seems to be working correctly as well.  However, when the correct date format is then entered for the report parameter for which the report threw an error, the error persists and the report doesn't run again.  Setting the parameter to "NULL" doesn't work either.

The only way to get the report to run again is to refresh the entire report.  Of course, if at that point one has entered a bunch of other parameter values, those values all disappear.

View 2 Replies View Related

Which Would Be Faster ?

Mar 28, 2007

hi all,    if i have a comma delimited string and want to insert each delimited substring into a table which of the following way is faster?pass the whole string into the a stored procedure and loop through the delimited string and pick out the substring and insert into the table orloop and pass the substring into a stored procedure and insert N times?or any other better ways someone could suggest me to do thanks! 

View 6 Replies View Related

Which Is Faster LIKE Or IN

Jul 27, 2001

i have a query that i can use either and get the same results. i just need to shave some time off... which is faster the LIKE or IN () ???

thanks

kim

View 2 Replies View Related

Can This Be Done Faster?

Jan 16, 2006

I was just wondering if this can be done any faster? code-wise that is...

Don't mind the converts, can't do without them, as the data discipline for the source table isn't always reliable, while I have to be absolutely sure the destination data ends in the required format.



UPDATE MATCH_basistabel
SET MATCH_basistabel.matchfelt = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.søgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n),
MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n2),
MATCH_basistabel.medlemsnummer = ALL_tbl_medlemsinfo.medlemsnummer,
MATCH_basistabel.nationalitet = convert(varchar(10), ALL_tbl_medlemsinfo.nationalitet),
MATCH_basistabel.organisationsnummer = convert(varchar(10),ALL_tbl_medlemsinfo.organisati onsnummer),
MATCH_basistabel.medlemskab = convert(varchar(20), ALL_tbl_medlemsinfo.medlemskab),
MATCH_basistabel.ipdn = ALL_tbl_medlemsinfo.ipdn,
MATCH_basistabel.ipdnroll = convert(varchar(20), ALL_tbl_medlemsinfo.ipdroll),
MATCH_basistabel.franavision = 1
FROM MATCH_basistabel, ALL_tbl_medlemsinfo
WHERE isnumeric(matchfelt) = 1
AND (convert(int, MATCH_basistabel.matchfelt) = convert(int, ALL_tbl_medlemsinfo.medlemsnummer)
AND MATCH_basistabel.franavision = 0)

View 14 Replies View Related

Run Faster

Feb 17, 2006

Hi Guys,

I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...

Many Thanks,

Regards,
Shaffiq

View 6 Replies View Related

NEED TO GO FASTER

Jun 18, 2007

View 2 Replies View Related

Which Is Faster?

Jul 20, 2005

I'm sonewhat new to MS SQL Server and I'm wondering about which of thefollowing two queries would be faster:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSELECT@ResidencesBuilt = SUM(CASE WHEN BuildingType = 0 THEN Built END),@BarracksBuilt = SUM(CASE WHEN BuildingType = 1 THEN Built END),@AirBaysBuilt = SUM(CASE WHEN BuildingType = 2 THEN Built END),@NuclearPlantsBuilt = SUM(CASE WHEN BuildingType = 3 THEN Built END),@FusionPlantsBuilt = SUM(CASE WHEN BuildingType = 4 THEN Built END),@StarMinesBuilt = SUM(CASE WHEN BuildingType = 5 THEN Built END),@TrainingCampsBuilt = SUM(CASE WHEN BuildingType = 6 THEN Built END),@FactoriesBuilt = SUM(CASE WHEN BuildingType = 7 THEN Built END)FROM BuildingsGROUP BY kdIDHAVING kdID = 2902Or:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSET @ResidencesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 0AND kdID = 2902)SET @BarracksBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 1 ANDkdID = 2902)SET @AirBaysBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 2 ANDkdID = 2902)SET @NuclearPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =3 AND kdID = 2902)SET @FusionPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 4AND kdID = 2902)SET @StarMinesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 5AND kdID = 2902)SET @TrainingCampsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =6 AND kdID = 2902)SET @FactoriesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 7AND kdID = 2902)The data source is:kdID BuildingType Built2902 6 02902 7 02902 4 02902 0 802902 2 02902 1 52902 3 402902 5 10Or:CREATE TABLE [dbo].[Buildings] ([kdID] [int],[BuildingType] [tinyint],[Built] [int])INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 0, 80)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 1, 5)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 2, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 3, 40)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 4, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 5, 10)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 6, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 7, 0)Analyzer says the first would be faster, but it has a lot of SUM()'s andwhatnot so I'm not too sure about this. There are also about 1000 rows inthe actual Buildings table. This will be a part of a stored procedure.

View 3 Replies View Related

Faster Way To Do This?

May 7, 2007

I want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds).



Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.



USE [TNS]

GO

/****** Object: StoredProcedure [dbo].[Unique_Login_IPs] Script Date: 05/07/2007 12:38:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[Unique_Login_IPs]

(

@year1 int,

@year2 int

)

AS

BEGIN

SET NOCOUNT OFF;



-- Define the years for testing purposes

set @year1 = 2006

set @year2 = 2007



SELECT month,[2006] as y2006,[2007] as y2007

FROM

(

SELECT @year1 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @year1 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 2)) as tmpy1_2

UNION

SELECT @year1 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3

UNION

SELECT @year1 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 4)) as tmpy1_4

UNION

SELECT @year1 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 5)) as tmpy1_5

UNION

SELECT @year1 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 6)) as tmpy1_6

UNION

SELECT @year1 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 7)) as tmpy1_7

UNION

SELECT @year1 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 8)) as tmpy1_8

UNION

SELECT @year1 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 9)) as tmpy1_9

UNION

SELECT @year1 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 10)) as tmpy1_10

UNION

SELECT @year1 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 11)) as tmpy1_11

UNION

SELECT @year1 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 12)) as tmpy1_12

UNION

SELECT @year2 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @year2 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 2)) as tmpy2_2

UNION

SELECT @year2 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 3)) as tmpy2_3

UNION

SELECT @year2 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 4)) as tmpy2_4

UNION

SELECT @year2 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 5)) as tmpy2_5

UNION

SELECT @year2 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 6)) as tmpy2_6

UNION

SELECT @year2 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 7)) as tmpy2_7

UNION

SELECT @year2 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 8)) as tmpy2_8

UNION

SELECT @year2 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 9)) as tmpy2_9

UNION

SELECT @year2 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 10)) as tmpy2_10

UNION

SELECT @year2 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 11)) as tmpy2_11

UNION

SELECT @year2 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 12)) as tmpy2_12

) piv

PIVOT

(

SUM(cnt)

FOR year IN

([2006],[2007])

) as child

END

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved