Sqlservr.exe Handle Count Of 3,306,263

Feb 13, 2008

Hi,

I have asked this question on the MSDN forums
with no response as yet. Maybe you guys can
shed some light on this:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2826915&SiteID=1

We have an apparent File Handle leak occurring in our SQL server.
Last weekend the server ran out of non-paged pool space:

Event ID 2019 -The server was unable to allocate from the system
nonpaged pool because the pool was empty.

In the process monitor the sqlservr.exe process had Mem Usage
of 503,528KB and a handle count of 3,306,263 (!). Using poolmon
the highest usage of the non-paged pool was for the Muta tag, with
211,610,768 Bytes. Restarting the sql service fixes this
temporarily, but already we are back to 427,000 handles for
sqlservr.exe and climbing steadily at about 200,000 handles
per day.

Is there a fix for this?

Cheers,
Geoff

PS
We have:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition
on Windows NT 5.2 (Build 3790: Service Pack 2)
(Meaning Windows Server 2003 R2 SP2 :-) )

View 20 Replies


ADVERTISEMENT

SQL Server Runs Out Of Resources Or Is It My Client Application. Handle Count = 96k

Jul 20, 2005

SServer PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAMSQL Server 2000 v 2000.8.00.76 (sp3)MS Office 2k3MSJet ms04-014 (latest ost sp8)MDAC v2.8 RTMADO 2.1vb6.exe / ADO 2.0I think this is a SQL Server/ADO problem as I have 2 applications withsame problem.My access database uses a timer based function to insert records intoSQL Server using ADO and stored procedures. Access also uses DAO ,Jet/ odbc to linked tables on SQL Server for many other tasks/forms.All is well when Access 1st run but after a few hours or so the Accessapp grinds to a halt.Upon checking the task manager the mem usuage upto 160MB and handlecount upto 86,000 ! (cpu process % is low).After the "Access Fail" if I stop/start access only, performance isnot returned, I have to stop/start SQL Server.It would seem that allconnections from this PC to SQL server are badly affected, it is nottied to the client application that had the problem.As I could not work out where the problem was I took the Accessfunctionality into a VB6 app, using ADO 2.0, thinking this shouldsimplify matters with Jet and ODBC out of the way.I now have the same problem with the number of handles increasing withevery new timer based function.* code snippet example *If Not OpenConnection Then 'we have not been able to open aconnection to SQL serverCall procLog("Connection failed to SQL server")Exit FunctionEnd If'gVar.cnnSQL is my public ADODB.ConnectionSet cmdSQL = New ADODB.CommandWith cmdSQL.ActiveConnection = gVar.cnnSQL.CommandText = "MyDB.dbo.insert_tblMyData".CommandType = adCmdStoredProc.Execute RecordsAffected:=lngRecs, _Parameters:=Array(lngID, dtDate,intCategory,strNationality,strNotes,strName)End With* code snippet *** After the "Access Fail" if I look at one of my clients, running thesame Access app on another PC, it seems normally responsive when usingone my bound forms to browse the data from same SQL Server **Any ideas anyone ?

View 3 Replies View Related

Sqlservr.exe And Cpu 100%

May 1, 2005

hi

when my site is being used, sqlservr.exe is using 100% of CPU..

why this happening ?
please help me..

View 14 Replies View Related

Sqlservr -c -f

Apr 5, 2004

hello,
We are having an issue with tempdb growth and even after restarting the server and the shrinkfile command the tempdb still holds on to nearly 15 GB of space.
I found this article on the microsoft website which recommended that we stop SQL server and retart it from the commad prompt using the sqlservr -c -f command. After this we go to SQL Analyzer and run the shrinkfile.

I gave the sqlservr command on a friday evening in our test enviroment and I come back on monday morning and it is still not completed and i had to close the window and start SQL Server from Windows.

Is there something that i am missing here that i should know about the sqlservr command. Is there anything else that someone could recommend regarding shrinking the tempdb.

thanks

View 5 Replies View Related

SQLservr.exe 100% CPU, Why?

Jan 7, 2008

Happy new year and merry xmas to everyone

I got SQL 2005 Express edition with Sharepoint Server 2007 installed and its running 100% CPU all the time

there are plenty threads like this out there but none have an answer

anyone got ideas?

View 11 Replies View Related

Sqlservr.exe Using Too Much Memory! Please Help!

Jun 12, 2002

SQL 7.0(SP3) on NT Server(SP5)

The sqlservr.exe process is using almost 800MB of memory in Task Manager. We have 1GB physical RAM. The swapfile is on C: with min=950/max=1050MB. Is this normal? I think there may be some kind of memory leak. Any ideas?? Please HELP!

Rob

View 2 Replies View Related

Sqlservr Service!

Jul 11, 2000

Help ! My sqlservr service is running at 100% and my server is dog slow. I don see anything unusual when looking at current activity or sp_who2. I have restarted it a few times.. ANy suggestions? I only have one database in production on this server and there are only about 10 users hitting it..

View 4 Replies View Related

Page 2 - Sqlservr.exe And Cpu 100%

May 2, 2005

Arghhh, login to sqlserver and issue this query:

Code:

SELECT @@VERSION


and tell us what comes out.
Seev here for reference.

View 12 Replies View Related

Error Log Peppered With --&&> 'The Conversation Handle Is Missing. Specify A Conversation Handle.'

Dec 3, 2007

Hi

I'm using service broker and keep getting errors in the log even though everythig is working as expected

SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.

I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.

In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'

error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'

I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?

i can provide code of the stored procs if that helps.

thanks.

View 10 Replies View Related

Sqlservr.exe Increasing In Size!!

Feb 25, 2000

Hello all,

Can I know somedetail about why the Sqlservr.exe app increasing in size drastically. Even I check all parameter of the server and I check the process running on server.

I feel server is not releasing the queues and It is occupying the memory. I any one suggest what could be the cause ?

Thanks,

View 1 Replies View Related

SQLservr.exe Taking Over Computer

Aug 25, 2004

I have been dealing with an intermittent problem for several months that manifests itself on my computer as well as a customers computer. It is happening so often, upon booting the computer, that I just open and then minimize the Task Manager so that it will be in the Tool Tray and the bargraph will be visible.

From time to time the processor bargraph will "Max out" and when I open Task Manager and click on CPU in the Processes Tab, SQLServr.exe is using 99% of the CPU.

In Enterprise Manager I have set maximum Memory to 25% of the available system memory. I have tried this in both Fixed mode as well as Dynamic mode, no change.

I was told that there was a SQL Server version that was susceptible to a WORM that caused this. I have since upgraded to SQL ver. 8.0.194. I'm not sure of the version that I replaced, but I thought that the previous version was the one that was susceptible to the worm.

Has anyone fought this battle and if so can you offer any experience or advice?

Thanks very much for your help,
Doc

View 4 Replies View Related

High Mem Usage Sqlservr.exe

Jun 11, 2008

Hi all,

I have a problem with sqlservr.exe (version 2005). It use alot of memory. I check on taskbar manager sqlservr.exe usage (CPU 10 - 20%, Mem usage - 1,493,688/2GB Ram). I dont know how can I fix it. Some body could help me please.

Thanks
Doan Jung

View 1 Replies View Related

High Mem Usage Sqlservr.exe

Jun 11, 2008

Hi all,

I have a problem with sqlservr.exe (version 2005). It use alot of memory. I check on taskbar manager sqlservr.exe usage (CPU 10 - 20%, Mem usage - 1,493,688/2GB Ram). I dont know how can I fix it. Some body could help me please.

Thanks
Doan Jung

View 14 Replies View Related

Sqlservr.exe Memory Usage

Feb 27, 2006

Hello All,On all of my SQL servers (2000 with SP3), when I go to task manager andlook at memory usage, sqlservr.exe is always at 1.7gigs. If I reducethe maximum to let's say 1 gig it will go down to 1 gig. But if it iseven at 2gigs or 3 gigs it will be showing 1.7 gigs. Why? Is is ok toreduce the memory usage of sqlservr.exe?Raziq.*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Sqlservr.exe Memory Steadily Climbing

Feb 22, 2002

I've a SQL 7 SP3 server that was running for more than 6 months without any problem and hence I never give much thought to it other than the monitoring the growth of the database size.

Just the other day, I was pretty shocked to see that SQLSERVR.EXE was using nearly 1 GB of RAM (and I have 1 GB of physical RAM)!!! My database size is only about 2 GB and there aren't many simultaneous users online. This database is used for a web application.

Today I have the opportunity to restart the SQLServer service (to apply the security hotfix). And I'm pretty dismay to see its memory usage climbing steadily at the rate of around 20 to 30 KB every few seconds. I guess it'd hit 1 GB in a matter of hours or days..

Now, I've a similar server on another machine.. but its SQLSERVR.EXE is only using around 20 MB and it doesnt grow much.

So..what should I do?? Ideas? Suggestions? Thanks!!

View 3 Replies View Related

Sqlservr.exe Memory Usage Up To 200 Megs?

Jul 20, 2000

Why does my Sqlservr.exe file take up 200 megs of my nt memory. When i control alt delete and go into task manager, sqlservr.exe is always at the top of the memory usage list.

Melissa

View 3 Replies View Related

Sqlservr.exe Exit Code Of 259 (0x103) - What Does It Mean?

Mar 20, 2007

Can someone please explain what exit code 259 means as it appears in the debug output below?

When running a C# Stored Procedure within the VS 2005 IDE I receive the following Debug Output:

Auto-attach to process '[1152] [SQL] enterprise' on machine 'enterprise' succeeded.

Debugging script from project script file.

The thread 'enterprisesqlexpress [56]' (0x150) has exited with code 0 (0x0).

The thread 'enterprisesqlexpress [56]' (0x1314) has exited with code 0 (0x0).

The thread 'enterprisesqlexpress [56]' (0x1314) has exited with code 0 (0x0).

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_32mscorlib2.0.0.0__b77a5c561934e089mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnSqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_MSILSystem2.0.0.0__b77a5c561934e089System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_32System.Transactions2.0.0.0__b77a5c561934e089System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_MSILSystem.Security2.0.0.0__b03f5f7f11d50a3aSystem.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

Auto-attach to process '[1152] sqlservr.exe' on machine 'enterprise' succeeded.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_MSILSystem.Xml2.0.0.0__b77a5c561934e089System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'SQL2005_Test', No symbols loaded.

'ENTERPRISE;.Net SqlClient Data Provider;4400' (Managed): Loaded 'C:WINDOWSassemblyGAC_MSILSystem.Configuration2.0.0.0__b03f5f7f11d50a3aSystem.Configuration.dll', No symbols loaded.

CurrencyCode Name ModifiedDate

------------ -------------------------------------------------- -----------------------

eee MyCurr4 3/20/2007 12:58:38 PM

(1 row(s) affected)

(1 row(s) returned)

Finished running sp_executesql.

The thread 'enterprisesqlexpress [56]' (0x1314) has exited with code 0 (0x0).

The program '[1152] [SQL] enterprise: enterprisesqlexpress' has exited with code 0 (0x0).

The program '[1152] sqlservr.exe: Managed' has exited with code 259 (0x103).



I would also appreciate hearing back from users as to why I only have two choices in my VS 2005 IDE Output window (Build and Debug). From what I've read, 'Database' should be another choice for 'Show output from:'.



My environment consists of the following:

Database:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I have verified the SQL Server connection has 'Allow SQL/CLR Debugging' enabled.

The Visual Studio 2005 IDE is:

8.0.50727.762 (SP.050727-7600)

Running Microsoft .Net Framework version 2.0.50727

View 4 Replies View Related

The Sqlservr.exe Service Could Not Release From Memory

Oct 3, 2007

Dear Friends,

I have developed my web application using ASP.Net with MS SQL SERVER Express Edition SP2 .
I have hosting it at some where which i can remote and see the service of sqlservr.exe .
The first start of this service take the memory space just only 25 MB but after this service run upto one week it increased up to 200MB .This service won't realease from the memory untill unless i restart the server or restart its service.

1.Could any one tell me why this service continue increasingly ?.
2.What can i configure in MS SQL SERVER 2005 EXPRESS Edition to let this release from the memory without restart the server or its service ?.


Best Regards,

Channarith Hun.

View 6 Replies View Related

Killing Sqlservr.exe From Sqlclr Code

May 4, 2006

I keep getting different answers from different people on regarding if you can or cannot kill the hosting sql server process with an unsafe assembly. Can you do this? If so could you please attach a sample demonstrating this?

Thanks,

Derek

View 7 Replies View Related

Please Explain This.., Why Is PasswordRecovery Creating Another Sqlservr Process....

Jul 3, 2006

Why is the PasswordRecovery control creating another sqlservr process after I successfully enter the user name,  and for a reason I have not found yet it keeps the database in a read only modem then when I answer my security question correctly it gives me the error:
Failed to update database "C:INETPUBWWWROOTVER1.0.0.1APP_DATAASPNETDB.MDF" because the database is read-only.
I read other post regarding the read only message and I don't if there doing the same thing, but why does this happen and what is the solution to this other not using the control?

View 1 Replies View Related

Windows Task Manager - Sqlservr.exe Mem Usage

Sep 12, 2007

Hello everyone,

I have my minimum server memory in SQL set to 4GB, but in my Windows Task Manager, sqlservr.exe only has about 100MB.

Does anyone have any idea why task manager doesn't show the full amount?

Thanks in advance.

Jarret

View 3 Replies View Related

Sqlservr.exe Memory Overflow And Performance Problem

Jul 5, 2007

Hello,



I'm havin a problem with my database server in the network, i'm running a windows 2003 server standard edition with sql server 2005 standard edition.

the problem is that the server get stock and the performance of the whole network is affected, when i use the tak manager to monitor the performance i can see that the sqlservr.exe proccess is using 1,397,928 k of memory usage, in the performance monitor the graphics get crazy and the cpu usage grows up untill 85%.



Can you please let me know if there is something that i can do to normalize the server performance in order to let the network user work with the applications feeded by this server.





Thanks in advance for your help.

View 6 Replies View Related

Conversation Handle Reuse And Conversation Handle XXX Not Found

Jan 18, 2008



We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?

Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?

I have attached an example of one of the queuing procs below:




Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);

SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)

END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;

View 2 Replies View Related

Facing Error Of Connection Failed , Check Sqlservr

Jan 29, 2008

Hi i have installed sql server 2000 on xp window and facing error of connection failed, check sql server registration what it mean pleas guide me.


webmaster http://www.GlobalGuideLine.com

View 1 Replies View Related

Task Manager Memory Show Wrong Number In Sqlservr.exe

May 28, 2008

Hi,

I've SQL Server 2005 Dev Edition and Windows Server 2003 ENT SP2.
in the task manager i see that the server use 5GB out of 6GB,but when i arrange the process to see whom takes all the memory i see that the sqlservr.exe takes 150MB(he is the biggest).
when i open Perfmon and look on the sqlservr.exe memory use, i see that he takes the 4.5G.
i've a problem that the server use alot of cpu time to run users queries,i see that pages/sec counter is very big average between 600-800.

is there any problem with my memory?
why the memory reading from the task manager is wrong?
if my pages/sec counter is so big do i have a memory leaks + pressure?

THX

View 1 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....

Select Distinct
sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

View 9 Replies View Related

Is There A Better Way To Handle This IF..ELSE IF?

Mar 30, 2004

Do I have other option beside using IF..ELSE IF? TIF



-- GET INFORMATION OF THE JOB
DECLARE @JOBIDAS Char(10)
DECLARE @VRUSERVICEHRSAS Decimal(18,2)
DECLARE @VRUSERVICEMINAS Decimal(18,2)
DECLARE @BILLEDFLATAS Decimal(18,2)
DECLARE @BILLREGRATEAS Decimal(18,2)
DECLARE @MIN_HRSAS Decimal(18,2)

DECLARE @COUNT_GREATER_MINTinyInt
DECLARE @COUNT_LESS_MINTinyInt

SET @VRUSERVICEMIN = 46
SET @BILLEDFLAT = 0
-- PROCESS ONLY RECORDS WHERE THERE IS NO FLAT FEE
IF @BILLEDFLAT = 0
BEGIN
IF @VRUSERVICEMIN BETWEEN 0 AND 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN = 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN BETWEEN 15 AND 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN = 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN BETWEEN 30 AND 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN = 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN > 45
BEGIN
SET @VRUSERVICEMIN = 1
END
END

PRINT @VRUSERVICEMIN

View 6 Replies View Related

In SQL 2000 Can I Use Count() To Count A Column?

Nov 26, 2007

I use SQL 2000
I have a Column named Bool , the value in this Column is  0ã€?0ã€?1ã€?1ã€?1
I no I can use Count() to count this column ,the result would be "5"
but what I need is  "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is  2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.  
thank you very much
 

View 5 Replies View Related

Table Row Count + Index Row Count

Jul 23, 2005

SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.

View 5 Replies View Related

How Can I Handle An Error

Apr 1, 2007

Is it possible to catch and error and then keep the process going in a stored procedure?
So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?

View 4 Replies View Related

How To Handle Particular Sql Error

Oct 21, 2007

Hi! I have some try .. catch block trying to insert some data into database. During its action duplicate key row insert error could raise, for example. The question is how could I know distinguish it from other sql errors? Object ex (Catch ex As Exception) has only message property '{"Cannot insert duplicate key row in object 'dbo.Group_Courses' with unique index 'IX_Group_Courses'.The statement has been terminated."}' and type System.Data.SqlClient.SqlException. Knowing the type of error is not enough, because there are different SqlExceptions. Even the message is not unique for this error, because now i deal with 'dbo.Group_Courses'  and then it could be other table. Is there something that unique identifies each error? For example error code. If it exists, where could I get it?
Thanks in  advance!
 

View 2 Replies View Related







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