Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server


SuperbHosting.net have generously sponsored dedicated servers to ensure a reliable and scalable dedicated hosting solution for BigResource.com.





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


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 Complete Forum Thread with Replies

Related Forum Messages:
Error 209 Ambiguous Column Name ...Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK ....
i'm getting following exception when i try to execute stored procedure.{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting"Transaction count after EXECUTE indicates that......" In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN/* Its failing at    MemberID = tTempResult.MemberID   bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID    */   here is my stored procCREATE PROCEDURE dbo.ExportFile   @intMonth INT,   @intYear INT,   @dtFirstDayOfMonth DATETIMEAS  BEGINBEGIN TRANSACTION  /*   I have some logic here that will select rows into temporary table   #TEMPRESULT  */    UPDATE    dbo.RebateInstanceItem   SET     ResubmitCreated = @dtmNewCreated  FROM #TEMPRESULT tTempResult  WHERE     MemberID = tTempResult.MemberID       AND RebateInstanceItem.IsResubmit = 'Y'    AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated   OR RebateInstanceItem.ResubmitCreated IS NULL)      IF @@ERROR<>0    GOTO ERR_HANDLER // when error it will goto error_handler that will rollback  DROP TABLE #TEMPRESULT  IF @@ERROR<>0   GOTO ERR_HANDLERCOMMIT TRANSACTIONRETURN 0ENDERR_HANDLER: ROLLBACK TRANSACTION RETURN 1GO

View Replies !
Error: COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Why?
Hello:
I am implimenting the creation of sequence numbers .I use an insert proc on a table that generates the numbers using an identity field:
procedure usp_createidentity

begin transaction

  insert into [tblOrderNumber] with  default values

rollback  ' done so no records in this table

select @OrderNumber = scope_identity()
 
I call this from another proc that inserts values into my order table:

 
procedure usp_Insert  @OrderNumber int
as

SET XACT_ABORT ON;

BEGIN TRY

  BEGIN TRANSACTION
 

     EXEC usp_GetNewOrderNumber @OrderNumber = @OrderNumber output
      INSERT INTO [dbo].[tblOrder] ([OrderNumber]) values (@orderNumber) ' inserts value from other stored proc

  
COMMIT TRANSACTION

END TRY

BEGIN CATCH

 if (XACT_STATE() = -1)

ROLLBACK TRANSACTION

else

if (XACT_STATE() = 1)

COMMIT TRANSACTION
 END CATCH
 
Here is the problem.   When I run  usp_Insert I get the following:  Error 266  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
 
This refers to the  usp_GetNewOrderNumber  that is called inside the other proc as shown above.
 
The problem does not happen if I put each statement in usp_Insert in its own try/catch. transaction statements.
 
Maybe it has something to do with the rollback call in the usp_getneworder. 
 
What do I need to do to get rid of this. problem and still run these within one try/catch trans statement set.
 
Thanks

View Replies !
Error 209 Ambiguous Column ..Transaction Count After EXECUTE Indicates .....
i'm getting following exception when i try to execute stored procedure.

{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }

I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting
"Transaction count after EXECUTE indicates that......"

In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN

/* Its failing at    MemberID = tTempResult.MemberID   bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID    */   

here is my stored proc

CREATE PROCEDURE dbo.ExportFile
   @intMonth INT,
   @intYear INT,
   @dtFirstDayOfMonth DATETIME

AS 

BEGIN

BEGIN TRANSACTION


  /*
   I have some logic here that will select rows into temporary table
   #TEMPRESULT
  */
 
   UPDATE
   dbo.RebateInstanceItem
  SET  
   ResubmitCreated = @dtmNewCreated
  FROM #TEMPRESULT tTempResult
  WHERE 
   MemberID = tTempResult.MemberID   
    AND RebateInstanceItem.IsResubmit = 'Y'
    AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated
   OR RebateInstanceItem.ResubmitCreated IS NULL)    

  IF @@ERROR<>0
    GOTO ERR_HANDLER // when error it will goto error_handler that will rollback

  DROP TABLE #TEMPRESULT
  IF @@ERROR<>0
   GOTO ERR_HANDLER

COMMIT TRANSACTION
RETURN 0
END

ERR_HANDLER:
 ROLLBACK TRANSACTION
 RETURN 1
GO

View Replies !
Transaction Count/day
Is anyone currently totalling their transaction count per day? I'm wondering how to do this.
I figure I can stick perfmon tran/sec in a table and total it that way, just wondering about other ways. Thanks.

Pete Karhatsu

View Replies !
Transaction Count
How do you find out how many transactions sql server is processing? I want to be able to monitor peak and average transaction processing counts over time.

View Replies !
Implicit Transaction Count
Hello, everyone:

I need total transaction count. @@TRANCOUNT returens the number of explicit transaction. How about implicit transaction? Thanks a lot.

ZYT

View Replies !
SQL Server 7.0 Transaction Log Count
Hello,
We just migrated one of our databases from SQL Server 6.5 to 7.0. One of our applications (2-tier) used to run this query against the database: "Select rows from sysindexes where name = 'syslogs'" to check the number of rows.

There is no table 'syslogs' in the master anymore. There is no name "syslogs" in the sysindexes. Do you know why don't we have this table and how can I get the number of rows?

Thank you,
Anastasia.

View Replies !
Transaction Count --help Very Urgent!!
Hi,

I would like to capture number of the total transactions made on the server during a day. ie begin tran - commit tran
How can I collect this information?
Is it possible?

Please help.

View Replies !
Usage Of Count() Function Inside Sql Transaction
Please find my second post in this thread.

Can anyone help me in sorting out the problem and let me know what might be the reason.

Thanks & Regards

Pradeep M V

View Replies !
Altering Table Issues (total Row Count Vs Exported Row Count)
Please help me...
I had a table with x number of fields...
I had this data source view and model ...
if I selected some information about the table everything was ok...

yesterday I altered the table adding two more fields.
I create a new data source, data source view and model
to get the new database fields included...

now I filter using those fields... and the report is telling me...

81 records... but I exported the data to csv... and I only see 58 records...
I do a manual query thru query analyzer... and got the 81 records...
some records did not appear...

I have no filters... no relations, just one table just one field selected in the report I'm building.

any ideas ???

kindest regards.
elias.

View Replies !
Error 266 - I'm Confused, Only One Transaction With COMMIT And ROLLBACK
I have a transaction that calls one other sproc and also executes another set of queries, but for some reason I'm getting error 266: "Msg 266, Level 16, State 2, Procedure AddUserHaveTag, Line 26.  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.  Previous count = 0, current count = 1."There is NO transaction in the sproc AddTag.  It is also included below.Here is the sproc with the transaction:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON
3 go4
5 ALTER PROCEDURE [dbo].[AddUserHaveTag] 6 (7 @UserHaveID int,8 @Tag varchar(24),9 @UserHaveTagExists bit OUTPUT
10 )11 AS12 SET NOCOUNT OFF13 DECLARE @ErrorCode int14 DECLARE @TagID int15 DECLARE @TagExists bit16
17 BEGIN TRAN
18 19 -- Call proc to add tag to Tags table
20 EXEC AddTag @Tag, @TagID OUTPUT, @TagExists OUTPUT
21
22 -- Check for errors
23 IF @ErrorCode <> 0 GOTO ERROR24
25 -- Check for existing record, otherwise insert
26 IF EXISTS (SELECT 1 FROM UserHaveTags WHERE UserHaveID = @UserHaveID AND TagID = @TagID)27 BEGIN28 SET @UserHaveTagExists = 129 RETURN 030 END31 ELSE32 BEGIN33 INSERT INTO UserHaveTags (UserHaveID, TagID) VALUES (@UserHaveID, @TagID)34 SET @UserHaveTagExists = 035 END
36
37 -- Check for errors
38 IF @ErrorCode <> 0 GOTO ERROR39
40 COMMIT TRAN
41
42 ERROR:43 IF (@ErrorCode <> 0)44 BEGIN45 PRINT 'Unexpected error occurred!'
46 ROLLBACK TRAN47 END
  Here is the AddTag sproc:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON
3 go4
5 ALTER PROCEDURE [dbo].[AddTag] 6 (7 @Tag varchar(24),8 @TagID int OUTPUT,9 @TagExists bit OUTPUT
10 )11 AS12 SET NOCOUNT OFF13 14 IF EXISTS (SELECT 1 FROM Tags WHERE Tag = @Tag)15 BEGIN16 SELECT @TagID = TagID FROM Tags WHERE Tag = @Tag17 SET @TagExists = 118 RETURN 019 END20 ELSE21 BEGIN22 INSERT INTO Tags (Tag) VALUES (@Tag)23 SET @TagID = SCOPE_IDENTITY()24 SET @TagExists = 025 ENDAny advice?Also if you see any glaring errors or things I could be doing better, I'm open to suggestions. I'm fairly new to sprocs and transactions. Thanks,Travis  

View Replies !
How To Create A Transaction Commit/Rollback For Oracle DB In SSIS
 I'm able to connect to the Oracle database to insert the data into multiple tables using OLEDB connection via Oracle Provider for OLEDB. However, i wish to create a transaction so that i'm able to rollback all the data in the case where the insertion fails in one of the table. May i know where should i start from?

View Replies !
Distributed Transaction Coordinator UPDATE Then COMMIT Then ROLLBACK
Hello,
 
I am running the following from New Query in SQL Server 2005 management studio in an SQL Server 2005 instance on a separate machine.
 
I am updating a SQL Server 2000 database on a separate machine.  I execute each statement one at a time.
 


SET XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION
 

SELECT @@TRANCOUNT
 

SELECT RIGHT(StudentID,LEN(StudentID)-LEN('TE2_'))

FROM [HQ-A4].TI_excentonlineDD.dbo.Student
 

SELECT * FROM [HQ-A4].TI_excentonlineDD.dbo.Student
 

UPDATE [HQ-A4].TI_excentonlineDD.dbo.Student

SET StudentID = RIGHT(StudentID,LEN(StudentID)-LEN('TE2_'))

WHERE StudentID LIKE 'TE2_%'
 

SELECT * FROM [HQ-A4].TI_excentonlineDD.dbo.Student
 

SELECT @@TRANCOUNT
 

COMMIT TRANSACTION

SET XACT_ABORT OFF
 
The update statement, updates an SQL Server 2000 database.
 
The query will run for 30 seconds.
I test the results with SELECT * FROM [HQ-A4].TI_excentonlineDD.dbo.Student
then COMMIT TRANSACTION.  All is good.
 
After the  COMMIT TRANSACTION,  I check my new expected dataset expected results by SELECT * FROM [HQ-A4].TI_excentonlineDD.dbo.Student. 
 
Next, I open SQL Server 2000 query analyzer (another session (should show only COMMITED data) and connect to the
target SQL Server 2000 database.
 
I run SELECT * FROM TI_excentonlineDD.  I get my new expected dataset expected results.  All is good.
I close Query Analyzer.
 
I close the SQL Server 2005 Management Studio.
 
The weird problem
-------------------------
If I wait 5 minutes or so, open a new New Query or Query Analyzer, then, next, run SELECT * FROM [HQ-A4].TI_excentonlineDD.dbo.Student or run SELECT * FROM  TI_excentonlineDD.dbo.Student, I get back my "old dataset re-appears" (my pre-COMMITED data).  This is bad.
 
I have repeated that same scenaro with both BEGIN DISTRIBUTED TRANSACTION and just BEGIN TRANSACTION
After a while, the "old dataset re-appears" problem still happens.   This is all bad.
 
Is some sort of internal-crash or timeout failure occurring?
Any ideas where I could start looking to resolve this problem?
 
Thanks.
 
AIMDBA
 
 
 

 

View Replies !
Rollback Transaction To Previous Stored Procedure?
Hi all, I have a program that needs to delete records, then re-insert new records to a table. But I need to rollback the transaction IF the insert is not success (error occured). The delete and insert are in 2 difference stored procedure (which have rollback transaction) that calling from 1 stored procedure. My problem is that if Insert is not successful, but the records already deleted previously. How can we rollback the delete transaction when insert is not successful?
Note: if possible, I don't want to delete the records AFTER the insert is successful, or create a temp table to stored the deleted records 
======================================= 
create stored procedure combine_sp
as
begin
       call delete_sp -- have rollback transaction in the delete_sp
      -- what to do if following has error occured, but we already deleted the records above?
       call insert_sp -- have rollback transaction in the insert_sp
end
go
=======================================
Thanks a lot.

View Replies !
In SQL 2000 Can I Use Count() To Count A Column?
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 Replies !
Table Row Count + Index Row Count
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 Replies !
DTSDestination.Count And DTSSource.Count
I'm trying to create a VB Script that gets the # of columns from a source table and the # of columns from the destination table and creates a transformation based on the number of columns in the source table.

I can write the actual transformation, but does anyone have an example of how I can get the DTSSource.Count property to return an integer?

Much Thanks,

Dan O'Malley
dan_o@leaseteam.com

View Replies !
Properties Row Count Not = Select Count(*)
I have a SQL2000 table, and when I display Properties, it says the row count = 927, but when I do select count(*), I get 924. I did a refresh on everything (since refresh is often needed), finally exited SQL Ent Mgr, went back in with the same result. I believe 924 is the correct count ..... Is that table corrupted somehow ? Can I trust the count in "Properties" for other tables ?

View Replies !
Obtain Unit Percent With Unit Count Divided By Total Count In Query
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
 
Here is a test code snippet:
 

--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'
 

select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc

 
Thanks. Dan

View Replies !
PROBABLY SIMPLE QUESTION!! How To Count Products In The Same Category As The Current Product
hi there. I'd like to list out all products in the adventure works catalogue, and for each product show how many other products there are in the same category.  could someone help me with this please.  please note, I don't want to include the actual category in the output, just 2 columns: product name and count of other products in same category.

 

with

member [measures].[samecat]

as 0 //WHAT SHOULD I PUT IN MY CALCULATED EXPRESSION.

select {[measures].[samecat]} on columns,

[Product].[Model Name].children on rows

from [Adventure Works]


All-Purpose Bike Stand 0
Bike Wash 0
Cable Lock 0
Chain 0
Classic Vest 0
Cycling Cap 0
Fender Set - Mountain 0
Front Brakes 0
Front Derailleur 0
Full-Finger Gloves 0
Half-Finger Gloves 0
Headlights - Dual-Beam 0
Headlights - Weatherproof 0
Hitch Rack - 4-Bike 0
HL Bottom Bracket 0
HL Crankset 0
HL Fork 0
HL Headset 0
HL Mountain Frame 0
HL Mountain Front Wheel 0
HL Mountain Handlebars 0
HL Mountain Pedal 0
HL Mountain Rear Wheel 0
HL Mountain Seat/Saddle 2 0
HL Mountain Tire 0
HL Road Frame 0
HL Road Front Wheel 0
HL Road Handlebars 0
HL Road Pedal 0
HL Road Rear Wheel 0
HL Road Seat/Saddle 2 0
HL Road Tire 0
HL Touring Frame 0
HL Touring Handlebars 0
HL Touring Seat/Saddle 0
Hydration Pack 0
LL Bottom Bracket 0
LL Crankset 0
LL Fork 0
LL Headset 0
LL Mountain Frame 0
LL Mountain Front Wheel 0
LL Mountain Handlebars 0
LL Mountain Pedal 0
LL Mountain Rear Wheel 0
LL Mountain Seat/Saddle 2 0
LL Mountain Tire 0
LL Road Frame 0
LL Road Front Wheel 0
LL Road Handlebars 0
LL Road Pedal 0
LL Road Rear Wheel 0
LL Road Seat/Saddle 1 0
LL Road Tire 0
LL Touring Frame 0
LL Touring Handlebars 0
LL Touring Seat/Saddle 0
Long-Sleeve Logo Jersey 0
Men's Bib-Shorts 0
Men's Sports Shorts 0
Minipump 0
ML Bottom Bracket 0
ML Crankset 0
ML Fork 0
ML Headset 0
ML Mountain Frame 0
ML Mountain Frame-W 0
ML Mountain Front Wheel 0
ML Mountain Handlebars 0
ML Mountain Pedal 0
ML Mountain Rear Wheel 0
ML Mountain Seat/Saddle 2 0
ML Mountain Tire 0
ML Road Frame 0
ML Road Frame-W 0
ML Road Front Wheel 0
ML Road Handlebars 0
ML Road Pedal 0
ML Road Rear Wheel 0
ML Road Seat/Saddle 2 0
ML Road Tire 0
ML Touring Seat/Saddle 0
Mountain Bike Socks 0
Mountain Bottle Cage 0
Mountain Pump 0
Mountain Tire Tube 0
Mountain-100 0
Mountain-200 0
Mountain-300 0
Mountain-400-W 0
Mountain-500 0
Patch kit 0
Racing Socks 0
Rear Brakes 0
Rear Derailleur 0
Road Bottle Cage 0
Road Tire Tube 0
Road-150 0
Road-250 0
Road-350-W 0
Road-450 0
Road-550-W 0
Road-650 0
Road-750 0
Short-Sleeve Classic Jersey 0
Sport-100 0
Taillight 0
Touring Front Wheel 0
Touring Pedal 0
Touring Rear Wheel 0
Touring Tire 0
Touring Tire Tube 0
Touring-1000 0
Touring-2000 0
Touring-3000 0
Touring-Panniers 0
Water Bottle 0
Women's Mountain Shorts 0
Women's Tights 0

View Replies !
Inserted Rows Count From SSIS Not Like Table Rows Count
Hi all

 

i using lookup error output  to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................

 

any ideas

 

View Replies !
SQL COUNT Statement
I am trying to count records in SQL Server. I have this Stored procedure but I am getting SQL errors.


Alter Procedure usp_rptQualityReport3 As

SELECT * FROM viewQualityReport

SELECT COUNT([FailureReason]) AS FC

WHERE
(((viewQualityReport.FailureReason) <> N'NONE'))

ORDER BY
FC


I am trying to count records that have like FailureReasons. I am selecting all the records from the view I created and then trying to count the records in the second Select statement. Basically what I want to do is counf them so I can then rank them starting with failure reasons that happen the most. I don't know what I am doing wrong.

View Replies !
Please Help With The SQL COUNT Statement!
I'm trying to run this SQL statement in my ASP code, sql="SELECT COUNT(*) FROM order_list WHERE ref_index='"&ref_index&"'"

The problem is, should i create a variable to store the returned integer? I tried rs_itemcount = conn.execute(sql) and then response.write(rs_itemcount.value)

But it returns with an error... any ideas how to store the integer and use it?!

thx!

View Replies !
Where Statement Again A Count
I can insert into a temp table and get this to work fine but it am unable to pass my sdate and endate parameter from reporting services.
 
This Works Fine I Just want to throw in a where statement. How do you get this to work? I tried [] () ''
 
WHERE Cola=Count(*) = Colb=Count(DidNotAttend)
 

Use Consumer

Select TripsId.ID#, Cola=Count(*), Colb=Count(DidNotAttend)

FROM Trips INNER JOIN

TripsId ON Trips.Rec# = TripsId.TripId#

Group by TripsId.ID#

View Replies !
Getting The Count Of Rows For 3 Tables In Single Execute SQL Task
hi frnds,

im very new to SSIS package .my package consists of Single Exceute task.

In Single Exceute SQL task i had 3 seperate queries to get the count of rows of 3 tables.

the Query goes like dis ...

select count(*) AS precheckcount1 from new_main_dts where cust_nbr like '875%'

like dis for another 2 tables i had written with the alias name precheckcount2 and 3

i mapped the variable user::precheckcount1 to precheckcount1 in the resultSet and for other two alias name i did the same.

while executing the package the error is thrown :  [Execute SQL Task] Error: An error occurred while assigning a value to variable "precheckcount2": "Unable to find column precheckcount2 in the result set.".

please help me its very urgent

View Replies !
Trying To Set Output Variable To Row Count Result Of SQL Execute Task
I am building this as an expression, but it is not working.  I am trying to return the row count into a variable to use later in an update statement.  What am I doing wrong?  I am using a single row result set.  I have one variable defined in my result set.  I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".  Any help is appreciated!
 
SELECT count(*) FROM hsi.itemdata a
 JOIN hsi.keyitem105 b on a.itemnum = b.itemnum
 JOIN hsi.keyitem106 c on a.itemnum = c.itemnum
 JOIN hsi.keyitem108 d on a.itemnum = d.itemnum
WHERE a.itemtypegroupnum = 102
AND a.itemtypenum = 108
AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + "
AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + "
AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")

View Replies !
Count(*) Vs Count(columnname)
 Is there a difference in performance when using count(*) or count(columnname)?

View Replies !
Using A Count If Within A Group By SQL Statement?
I have the following SQL Statement:
SELECT     CONVERT(char(10), FixtureDate, 101) AS Date, COUNT(*) AS 'NumberOfRecords'FROM         tblFixturesGROUP BY CONVERT(char(10), FixtureDate, 101)
I want to add a new column called "need results".
This column needs to be count if a certain cell is NULL.
Count If HomeScore IS NULL
as well as grouping by date and counting the number of records. So the third column needs to count the number of records where homescore IS NULL

View Replies !
Trying To Get A Count In A Select Statement
When I try and execute this query I get the belwo error.
I want to get the ItemName and the Count as one column.
How can this be done? 
SELECT itemName, itemName +' - '+ COUNT(itemName) AS itemNameCount FROM tblItems GROUP BY itemName
ERROR: Conversion failed when converting the nvarchar value 'Spark Plug - ' to data type int.

View Replies !
Help With COUNT In SELECT Statement
Could someone assist with getting the count function working correctlyin this example please. I know the count function will return all rowsthat do not have null values, but in this case I want to count all therows except those with a zero sale price, (which are unsold).The table shows works offered for sale by an artist, with a positivefigure under SalePrice indicating a sale, and I want to count thenumber sold by each auction house, and sum the sale price by auctionhouse. The table is as follows:NameSalePriceAuctionDowling12000ChristiesDowling 0ChristiesDowling10000ChristiesDowling 0ChristiesDowling 0ChristiesDowling 6000SothebysDowling 0SothebysDowling 0SothebysDowling 8000SothebysDowling 0SothebysDowling 0SothebysDowling 0SothebysWhen I run this query:SELECT MyTable.Name, Count(MyTable.Name) AS [Number],Sum(MyTable.SalePrice) AS TotalSales, MyTable.AuctionFROM MyTableGROUP BY MyTable.Name, MyTable.AuctionHAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));The results are:NameNumberTotalSalesAuctionDowling 5 22000 ChristiesDowling 7 14000 SothebysThe TotalSales is correct, but the Number (Count) is incorrect, as therows with zero were also included. The results should be:NameNumberTotalSalesAuctionDowling 2 22000 ChristiesDowling 2 14000 SothebysHow do I prevent the unsolds (zeros) being counted?Thanks in advance,John Furphy

View Replies !
Increment A Count I Sql Statement
How can I add an incremented counter to a select statement.
Such as select last_name,date,Count? from table, so I would
get

miles,1/1/2003,1
long,1/3/2003,2
smith,1/3/2003,3

View Replies !
Count(*) And Select In The Same WITH Statement
Hi,
 
I have a query:
 
-- main select
WITH Orders AS
(
SELECT
 ROW_Number() OVER(MyDate ASC) RowNo,
** rest o the query ***
)
SELECT *
FROM Orders
WHERE RowNo BETWEEN 100 AND 200
ORDER BY RowNo

--count of records
DECLARE @COUNT INT
SELECT @COUNT = COUNT(*)
FROM ** the same query as above ***
RETURN @COUNT

 
In this case it can happen that when counting records there will be different number of records that it was at time of paging. Also server has to execute this query twice and the query is quite complicated means that takes time.
 
Is there any better way to get number of rows in the same part of query with paging ?

 
Thanks for help
Przemo
 

View Replies !
Trying To Count A Case Statement?
I need to get a total count of leads and then separate the counts by either Retail or Wholesale -
 
 
Here's my table schema -

CREATE TABLE [dbo].[Sent] (
 [IdentID] [int] IDENTITY (1, 1) NOT NULL ,
 [LeadID] [bigint] NOT NULL ,
 [AffiliateID] [bigint] NULL ,
 [PartnerID] [int] NULL ,
 [FranchiseID] [bigint] NULL ,
 [FirstName] [t_Name] NULL ,
 [LastName] [t_LastName] NULL ,
 [Address] [t_Address] NULL ,
 [Zip] [t_ZipCode] NULL ,
 [Make] [t_Make] NULL ,
 [Model] [t_Model] NULL ,
 [DateIn] [datetime] NULL ,

Here's my query - Since I'm grouping by the partnerid
 

select distinct make, count(leadid) as TotalCount,     case
            when PartnerID = 1 then 'retail'
            else 'wholesale' end
            as disposition
from leads_sent (nolock)where datein between '2007-09-01' and '2007-09-30'
group by make, partnerid
order by make
 
Here's a sample my current output -
 
Acura
1 wholesale Acura
2 wholesale Acura
4 wholesale Acura
5 wholesale Acura
21 wholesale Acura
34 wholesale Acura
37 wholesale Acura
56 wholesale Acura
57 wholesale Acura
72 wholesale Acura
510 retail Audi
1 wholesale Audi
3 wholesale Audi
7 wholesale Audi
12 wholesale Audi
16 wholesale Audi
18 wholesale Audi
23 wholesale 
 
 
Here's the output I need









Make
Total Count
RetailCount
WSCount

Acura
798
510
288

Audi
256
75
181

View Replies !
Using A Count W/in A Sub Select Statement
I'm trying to create a DTS package that uses CDO to send users an email. I need to create a sql query that counts two columns. I also need to create aliases for these two columns and then reference this in the sendEmail function. I have something that looks like this but I'm getting a DTS error. I think that it's because I'm not using an alias to reference Valid and Invalid. Can someone tell me how to alias the subselect columns correctly?? thanks :)

select advertiseremail, accountnumber from miamiherald where AdvertiserEmail is not null (select Valid = (select count (*) from miamiherald where validad = 1), Invalid = (select count (*) as Invalid from miamiherald where validad = 0))

View Replies !
How Do I Make Use Of Begin Transaction And Commit Transaction In SSIS.
Hi

 

How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.

 

its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.

 

Thanks,

Prashant

View Replies !
Need A Count Of '0', Even If Criteria Is Not Met In A Select SQL Statement
I have the following Select SQL Statement in which I get the count of the 'Code' column based upon a criteria and Group By clause:BEGIN
SELECT Code, COUNT(Code)as exprCount1a
FROM dbo.[Test]
WHERE Section = '1' and Item = 'a' GROUP BY Code ORDER BY Code
END
The results of the statement:
Code | exprCount1a 
1              22              44              1
I would like the following results:
Code | exprCount1a 
1              22              4
3              04              1
Note: Code ' 3 ' doesn't have any rows that meet the select count statement criteria but I still need to populate ' 0 ' in the results.
Thank you in advance

View Replies !
SQL Statement Count NULL Values
Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !
SProc - Ad Hoc Sql Statement With COUNT For Exec(@SQL) ??
Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith

View Replies !
Count # Of Results From SELECT Statement
Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)

Looking to run a query that will retrieve the number of results returned from a select statement...

Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:

SELECT * FROM Software WHERE LicensesID = 2

Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?

Thanks all!

View Replies !
Help With Query (count With Case Statement)
Hi,

I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.

select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages

Basically now, it returns the "OutBoundMessages" column

I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )

I determine if the column is "checked" or "unChecked" by a column in
tblMessage

For example

tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")


any help much appreciated..

thanks!
mike123

View Replies !
TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.&&"
I'm receiving the below error when trying to implement Execute SQL Task.

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'

I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.

Anyone know of the reason?

View Replies !
Using Begin Transaction,End Transaction, Return,Rollback, Break,Continue
Hi, is there someone who can demonstrate the usgae of these codes in this simple process. I have a table that has (id int, Balance money)
I want to write codes to utilize the Subject coding in this process.
1.Every time I withdraw money from a client, I want to verrify that he/she has enough money if not I want to roll back transaction.
2.Using Begin Tran , end Tran..
3. Using Return ,Break,continue

I really appreciate your help. Althought I read about this from a book, but it really confused me and I want to see real world example using client and withdraw from there accounts...:)

regards
Ali

View Replies !
TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,

View Replies !
Count The Number Of Rows In A UNION ALL Statement
Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark

View Replies !
SQL Statement, Adding Two COUNT/CASE Statements
 

SELECT     COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors], 
                      COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors]
FROM         content_hits_tbl
WHERE     (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
 
=======================
 
How do I add up both COUNT/CASE columns?  Would it be:
SUM([New Visitors] + [Returning Visitors]) AS Total

 
I tried this and it doesn't work. I get invalid column names error for both.
 
I have even tried:
SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total
 
You would think that there would be some gui functionality in VS08 that would do this...

 
Thoughts are greatly appreciated!

TT

View Replies !
Zero Count Values Not Appearing In SELECT Statement
Hi all

I have the following tables:
 



Code Snippet
CREATE TABLE #Lkp_Circle
(
ID INT ,
Abbreviation varchar(50)
)
GO
CREATE TABLE #Lkp_OtherCircles
(
Circle varchar(50)
)
GO
CREATE TABLE #Tbl_User
(
ID INT,
Name VARCHAR(50),
IsActive bit
)
GO
CREATE TABLE #Tbl_UserDetails
(
AssociateID INT,
CircleID INT
)
GO
INSERT INTO #Lkp_Circle VALUES (1,'C1')
INSERT INTO #Lkp_Circle VALUES (2,'C2')
INSERT INTO #Lkp_Circle VALUES (3,'C3')
INSERT INTO #Lkp_Circle VALUES (4,'C4')
INSERT INTO #Lkp_Circle VALUES (5,'C5')
INSERT INTO #Lkp_Circle VALUES (6,'C6')
INSERT INTO #Lkp_Circle VALUES (7,'C7')
GO
INSERT INTO #Lkp_OtherCircles VALUES ('C3')
INSERT INTO #Lkp_OtherCircles VALUES ('C4')
INSERT INTO #Lkp_OtherCircles VALUES ('C5')
INSERT INTO #Lkp_OtherCircles VALUES ('C6')
GO
INSERT INTO #Tbl_User VALUES ( 101,'U 1','True')
INSERT INTO #Tbl_User VALUES ( 102,'U 2','True')
INSERT INTO #Tbl_User VALUES ( 103,'U 3','True')
INSERT INTO #Tbl_User VALUES ( 104,'U 4','True')
INSERT INTO #Tbl_User VALUES ( 105,'U 5','True')
GO
INSERT INTO #Tbl_UserDetails VALUES(101,3)
INSERT INTO #Tbl_UserDetails VALUES(102,4)
INSERT INTO #Tbl_UserDetails VALUES(103,5)
INSERT INTO #Tbl_UserDetails VALUES(104,5)
INSERT INTO #Tbl_UserDetails VALUES(105,3)
GO
 
 
 
SELECT ISNULL(Circle,'Total') Circle, ISNULL(COUNT([HeadCount]),SUM(1)) AS [Total]
FROM
(
SELECT DISTINCT 'Circle' = CASE
WHEN #Lkp_Circle.Abbreviation IN (SELECT Circle FROM #Lkp_OtherCircles) THEN #Lkp_Circle.Abbreviation
WHEN #Lkp_Circle.Abbreviation NOT IN (SELECT Circle FROM #Lkp_OtherCircles) THEN 'Others'
ELSE 'Total' END,ISNULL(#Tbl_UserDetails.AssociateID,0) AS 'HeadCount'
FROM #Tbl_User INNER JOIN #Tbl_UserDetails ON #Tbl_User.ID = #Tbl_UserDetails.AssociateID INNER JOIN
#Lkp_Circle ON #Tbl_UserDetails.CircleID = #Lkp_Circle.ID
WHERE #Tbl_User.IsActive='True' AND #Tbl_User.ID>0 AND #Tbl_UserDetails.AssociateID>0
) AS PivotTable
GROUP BY Circle
WITH Cube
 
DROP TABLE #Tbl_User,#Tbl_UserDetails,#Lkp_Circle,#Lkp_OtherCircles
 
----EXPECTED RESULT
--Circle HeadCount
--C3           2
--C4           1
--C5           2
--C6           0
--Others     0
--Total       5
--
----ACTUAL RESULT
--Circle HeadCount
--C3          2
--C4          1
--C5          2
--Total       5
 
 

The criteria for Others is that those circles which are not part of #Lkp_OtherCircles i.e. C1,C2,C3 and C7 clubbed together. I have tried checking for the condition ISNULL when for that circle there is no user but the end result is same. Can someone tell me where I am going wrong and how to correct it?
 
 

View Replies !
Usage Of Begin Transaction.... Commit Transaction??
Hi, anyone can help,I am trying to learn how to use begin tran..commit tran .. rollback tran by doing this exercise... unfortunatelly,it did not work...anyone can help...
I have a table named tbl_balance(investor_id int,amount money)
I want to insert/update balance for each investor. I wrote a procedure but it didnot work:::

declare @investorid int, @amount money,@error_status int
select @investorid=1
select @amount = 500
/* to check if investor exist in the tbl_balance table, if so execute the codes */
IF exists(select investorid from tbl_bal where investorid=@investorid )
begin tran
begin
update tbl_balance
set amount=amount+@amount
where investorid =@investorid

if (@error_status<> 0)
begin
ROLLBACK tran
select 'you did not update'
end
else
begin
COMMIT tran
select ' YOu did update '
end
end

IF not exists(select investorid from tbl_bal where investorid=@investorid )
begin tran
begin
insert into tbl_balance
values(@investorid,@amount)

if (@error_status<> 0)
begin
ROLLBACK tran
select 'you did not update'
end
else
begin
COMMIT tran
select ' YOu did update '
end
end

View Replies !
Cannot Construct A SELECT DISTINT COUNT... Statement In SSCE 3.1
Hi everyone,

sorry to b a pest again!  Before I made the decision to change the DB used in my app from SQL Server Express to SSCE, I had no problems with constructing a SELECT statement as laid out in the Title.

Basically, I have 2 tables with a one-many relationship between them.  In the Parent table, I had a SQL Statement as follows:

SELECT DeptID, DeptName,

(SELECT DISTINCT COUNT(Active) FROM Documents WHERE (Documents.DeptID = Dept.DeptID) AND
(Documents.Active = 'True') AS CountOfActive
FROM Dept


Now in SSCE 3.1, I get an "Unable to parse query" error message when I construct the same SQL statement in my dataset designer.

Any thoughts on how I may solve this?

Much thanx!
Shalan

View Replies !

Copyright © 2005-08 www.BigResource.com, All rights reserved