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 Complete Forum Thread with Replies
Related Forum Messages:
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 Replies !
Usage Of Global Variables Inside SQL Task
I've been looking around but haven't yet found the syntax for usage of global variables in an SQL Task. I've set the global variable Id (see code below): if (select field from table where id = @[User::id]) is null select top 1 1 as response from table else select top 1 0 as response from table My objective with it is to set another global variable (@isNull). Supposably, when the selection returns null, I should set the variable to null, I did it by using the selections and mapping the response to that variable (is ther a better way to do so?). When I try to execute this, it says the variable has not been defined. Here is the error: Error: Must declare the variable '@'. I've also tryed it withou the brackets and the User:: thing in the beggining, (@id directly) and here is the response: Error: Must declare the variable '@id'. How should I access the global variables in the SQL code? (BTW, I've checked the field in execution time and it is set to 23, the correct Id, so the block that preceedes this one is working properly) Thanks,
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 !
How To Count The Tasks Inside A Package Programmatically?
Hi there, I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package? Thanks in advance for any help you can give me. Sincerely, Ricardo
View Replies !
How To Count # Of Occurrences Of A Character Inside A String?
I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is: NNNNNRJKSURNNNEJNNNN Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?
View Replies !
Transaction Inside Sp_executesql
Hi to all,Probably I'm just doing something stupid, but I would like you to tellme that (if it is so), and point the solution.There ist the thing:I' having a sp, where I call other sp inside.The only problem is, the name of this inside sp is builded variously,and executed over sp_executesql:create pprocedure major_sp@prm_outer_1 varchar(1),@prm_outer_2 varchar(2)assome codingset @nvar_stmtStr = N'exec @int_exRetCode = test_sp_' + @prm_outer_1 +@prm_outer_2set @nvar_stmtStr = @nvar_stmtStr + ' @prm_1, @prm_2, @prm_3, @prm_4output'set @nvar_prmStr = N'@prm_1nvarchar(128), ' +N'@prm_2nvarchar(128), ' +N'@prm_3nvarchar(4000), ' +N'@int_exRetCodeint output, ' +N'@prm_4varchar(64) output'exec sp_executesql @nvar_stmtStr,@nvar_prmStr,@prm_1,@prm_2,@prm_3,@int_exRetCode = @int_exRetCode output,@prm_4 = @prm_4 outputNow the issue is, I've transactions inside test_sp_11 lets say wherethe 11 is @prm_outer_1 + @prm_outer_2.These procedures are existing inside database, but are called dynamiclydepending of the parameters.The problem is, when I call the specified sp directly, the rollbacktransaction is working without any problem.Inside this procedures test_sp_xx, is a call of another sp (lets sayinside_sp).There is a transaction included.When it is called over major_sp, then the rollback is not performedbecause of error:Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54Cannot roll back transactio_bubu. No transaction or savepoint of thatname was found.The funniest way is, if there is no error inside, the commit is workingwithout any problem!The question is majory (because I'm almost sure, that this is anissue): is it possible, to have atransaction inside dynamicly called sp over sp_executesql?If ok to do that?Thank's in advanceMatik
View Replies !
Using Sp_droprolemember Inside A Transaction
I have a trigger which requires dropping a member from a role and includes user transactions. However you cannot call sp_droprolemember from within a user transaction. Looking at the code for the procedure gives me a line quote: exec %%Owner(Name = @membername).SetRoleMember(RoleID = @roluid, IsMember = 0) I can find no documentation on SetRoleMember or %%owner and attempts to run this line as an exec statement fails with "syntax error near '%'" Nor can I find any way of dropping a member from a role using T-SQL or DDL constructs. How do I drop a member from a role using T-SQL code and avoiding sp_droprolemember? And where do I find information about the %%Owner construct?
View Replies !
Function Inside The Procedure
i have created the table empid,empname,dob,salary for the insert i need to create procedure of insert empid,empname,dob,salary i got the function for autogenerated number for empid mmy question how to put function inside the procedure? my function below,pls guide me its very urgent. create function NextCustomerNumber() returns char(5) as begin declare @lastval char(5) set @lastval = (select max(customerNumber) from Customers) if @lastval is null set @lastval = 'C0001' declare @i int set @i = right(@lastval,4) + 1 return 'C' + right('000' + convert(varchar(10),@i),4) end Desikankannan
View Replies !
Usage Of E.g. Max(Date) Aggregat Function
Hi, I´ve searching for an reuseable SQL-Statement for follwoingsituation:Having e.g. a table tblAdress with some fields like persons full name(as Name), City, Birthdate, Street and so on.Now I will find e.g. all oldest persons for every City. No problemwith:select name, city, max(birthdate) group by name, cityBut if I also want to have the streetname for the oldest person, Ihave a problem cause I can´t include the street into the selectstatement like this:select name, streetname, country, max(birthdate)group by name, countryThis statement is not correct cause streetname is not included ingroup by. If I include the street, I´ve got the oldest persons percity and street. This is not what I want to have. Remember I want theoldest person in City and after that therefore the street ... and thisin a single sql statement. It´s only an example, but I think it´s astandard sql statement to retrieve information like this one forincluded aggregate functions. I spend hours on internet searching andmany self made test ... without any solution. Does anyone has astandard solution for this situation ?regardsHenri
View Replies !
Problem With Transaction Inside Loop
Hi, When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8. Create Table BPTest(id int) Declare @Id Int Set @Id = 0 While (@Id < 10) Begin begin tran Insert into BPTest values (@id) if(@Id > 5) begin if(@Id % 2 = 0) begin print 'true' print @Id commit tran end else begin print 'false' print @Id rollback tran end end Set @Id = @Id + 1 End Select * from BPTest drop table BPTest Please let me know the reason for this. Thanks in advance Regards, K. Manivannan
View Replies !
Dynamic Sql Inside Function And Return Value
Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working... Thanks ______________________________________________________________________ set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey from rt_'+@platform+'_CallLegs where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5)) exec @sql return @CallLegKey
View Replies !
Problem Retrieving @@Identity When Inside A Transaction.
I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ). I do an Insert into my User table, and then subsequently use the generated identity ( via @@identity ) to make an insert into another table. The foreign key relationship is dependant on the generated identity. For some reason with transactions, I can't retrieve the insert identity (it always returns as 1). However, I need the inserts to be under a transaction so that one cannot succeed while the other fails. Does anyone know a way around this problem? So heres a simplefied explanation of what I'm trying to do: Begin Transaction Insert into User Table Retrieve Inserted Identity (userID) via @@Identity Insert into UserContact table, using userID as the foreign key. Commit Transaction. Because of the transaction, userID is 1, therefore an insert cannot be made into the UserContact table because of the foreign key constraint. I need this to be a transaction in case one or the other fails. Any ideas??
View Replies !
Transaction Inside A Data Flow Task
We are designing an ETL solution for a BI project using SSIS. We need to load a dimension table from a source DB into the DW; inside the DW there are two tables for each source dimension table: a current dimension table and a storical dimension table. The source table includes technical columns that indicate if each record was processed correctly by ETL procedures. Each row in the source table can be a new record, or an exisisting one. If it's a new record, a corresponding new record should be inserted into the current dimension table of the DW; if it's an exisisting one, a new record should be inserted in the storical dimension table and the existing record in the current dimension table should be updated. Furthermore for each record we need to update the source table with an error code. If the record was processed with succes the code is zero, otherwise it contains an error code. I try to use a single data flow task, using the 'ole db command trasformation' task for managing update and 'ole db destination' task for managing insert. The problem is that some insert and update should be executed inside a single transaction, for each record; for example if the source contains a new record I should insert the record in the current dimension table and update the source record with error code zero if every think goes fine. There is some way to group task in the data flow pane in a single transaction ? There is a better way to do that ? Cosimo
View Replies !
Slow Execution Of Queries Inside Transaction
I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout. I have checked sp_lock myprocessid and I've noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks. I even tried to set the isolation level to Read UNCOMMITED and still have the same problem. As I said, once I execute my queries without being in a transaction everything works great. Can you help me to find out the problem? Thanks, Laura
View Replies !
Problems With ABS() Function Inside Sub Query (within WHERE-Clause)
Hello, when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003: SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in (SELECT Panel_Position FROM tblMeasurement_Results WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ? GROUP BY Panel_Position) i get this error returned: 0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported. I don't know where inside the sql string a conversion is necessary/fails. Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS: SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in (SELECT Panel_Position FROM tblMeasurement_Results WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ? GROUP BY Panel_Position) The only difference between the 2 statements is the ABS() function inside the sub query. More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake. I would appreciate it very much if someone out there knows an answer or a hint and could tell me. With kind regards, Andre
View Replies !
Transaction Inside A Transaction
Hi, I'm doing some unit testing using a .NET interface. how I'm doing this is to create a transaction whenever I begin a test, and to roll it back whenever I finish with it. That way i can do whatever I want to the database, and it will get reverted. The only problem I have is... some of my methods already implement transactions because they may need to call multiple queries or stored procedures. Im unsure of how transactions work, but is it possible to make a transaction inside a transaction? So the test itself would be transaction, (which gets rolled back after the test finishes) and then there will also be one or more transaction inside the test itself? Thanks
View Replies !
How To Read Data From Remote Server Inside A Transaction?
Hello, everyone: I have a local transaction, BEGIN TRAN INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE COMMIT View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query: Server: Msg 8501, Level 16, State 1, Line 12 MSDTC on server 'PROD' is unavailable. Server: Msg 7391, Level 16, State 1, Line 12 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c]. It looks like remote server is not available inside the local transaction. How to handle that? Thanks ZYT
View Replies !
CREATE FULLTEXT CATALOG Inside A User Transaction.
hi: I try to create full text for new created tables. Since all new created tables will have same columns with different table name. After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger. But I got error like this: CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction. Any one has idea how to deal with it? Thanks
View Replies !
Newbe Question: Calling Function Inside Select
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View Replies !
Permission-problem On Simple Query Inside CLR Function
I have created an assembly with permission set safe, and a function inside the assembly. The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read. However when I execute my CLR function I get an error saying something like: "The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed" I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error. And my .dll is also signed. Has this something to do with writing something a config file? I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
View Replies !
Help With Solution For Use Order BY Inside Function And VIEW On The Sql Server
help with solution for use order BY inside function and VIEW on the sql server i explain the problem i am a web developer in asp page i BUY an DATA grid object for my web site the problem in this object i can use only ONE field to use ORDER BY can i store in the sql server function and VIEW that change the ORDER of the result inside the sql server ? so whan i do SELECT * FROM dbo.tb_pivot_big the sql server ORDER for ME the result and run this (inside the sql server ) Code Snippet SELECT * FROM dbo.tb_pivot_big ORDER BY new_unit, Fname ASC, val_orginal desc TNX for help
View Replies !
Problem Returning A Timestamp Column Inside An TSQL Transaction
I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId 2 3 if @TimestampCheck is null 4 begin 5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist. 6 return -1 7 end 8 else if @TimestampCheck <> @LastChanged 9 begin 10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1) 11 return -1 12 end 13 14 15 Begin Tran Address 16 17 Update ADD_Address 18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate 19 where AddressId= @AddressId 20 21 select @error_code = @@ERROR, @AddressId= scope_identity() 22 23 if @error_code = 0 24 begin 25 commit tran Address 26 27 select @LastChanged = LastChanged 28 from ADD_Address 29 where AddressId = @AddressId 30 31 if @LastChanged is null 32 begin 33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1) 34 return -1 35 end 36 if @LastChanged = @TimestampCheck 37 begin 38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1) 39 return -1 40 end 41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
View Replies !
Setting The Transaction Inside A Script Task In A Child Package
I have what I would consider an odd situation that I really need to be able to solve and can't find any information. I have a parent package that is executing a child package, now in that child package I have a script task that is updating some records among other things. I need to know how to tie the updates that are happening in that script task to the transaction in the parent package so that if anything goes wrong anywhere in the package those updates are also rolled back. Can anyone help? Thanks, Jason
View Replies !
DATEDIFF Function Dosen't Return Anything If Used Inside ASPX Page!!
Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??
View Replies !
Calling Managed CLR Procedure From Inside User Defined Function -- How To ?
I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value. I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement. I did read that you could use an external stored procedure and/or managed CLR procedures inside a function. I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine. However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement. Any advice on how to do this? Thanks, Bruce
View Replies !
How To Determine, Inside A Function, If A Linked-server-query Returned Results
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database. I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple... One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this: SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')' EXEC sp_executesql @SQL (CAT_ASA is the linked-server's name) Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed). Does somebody know how to make what i want?? I prefer to avoid using temporary tables. Thanks!
View Replies !
Pre-Execute AcquireConnection Method Call Fails Inside Sequence Container (transaction Required)
I've created an SSIS package that contains a Sequence Container with TransactionOption = Required. Within the container, there are a number of Execute Package Task components running in a serial fashion which are responsible for performing "Upserts" to dimension and fact tables on our production server. The destination db configuration is loaded into each of these packages using an XML configuration file. The structure of these "Upsert" packages are nearly identical, while some execute correctly and others fail. Those that fail all provide the same error messages. These messages appear during Pre-Execute [Insert new dimension record [1627]] Error: The AcquireConnection method call to the connection manager "DW" failed with error code 0xC0202009. [DTS.Pipeline] Error: component "Insert new dimension record" (1627) failed the pre-execute phase and returned error code 0xC020801C. ... which are followed by [Connection manager "DW"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.". [Connection manager "DW"] Error: An OLE DB error has occurred. Error code: 0x8004D00A. While still in debug mode, I can check the properties of the "DW" connection and successfully test the connection within the packages that fail. The same packages run successfully when tested outside the container (i.e. no transaction) or when the configuration file is modified to point the "DW" connection to a development version of the db which is running on the same server as the source database. I have successfully used DTCtester to verify that transactions from source to destination server are working correctly. Also tried setting DelayValidation = True with no change. I have opened a case with Microsoft and am awaiting a reply so I thought I'd throw a post out here to see if anyone else has encountered this and might have a resolution. Here's some more on the environment: Source Server: Windows Server 2003 Enterprise Edition SP1 SQL Server 2005 Enterprise Edition SP0 Destination Server: Windows Server 2003 Enterprise Edition SP1 SQL Server 2000 Enterprise Edition SP3 (clustered) Thank you in advance for any feedback you might be able to provide. KS
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 !
SUM A Count Function
I need to perform a summation on the results of a COUNT but I'm not sure what's the best way to do it. Example: TableA --------------- CaseID (int) FollowupCorrespondence (int) (not guaranteed to be sequential) CustomerName (varchar) /**Get the total number of followups for each customer case, and display the customer's name.*/ select T.caseid, sum(T.cnt), T.CustomerName, T.FollowupCorrespondence from (select CaseID, count(FollowupCorrespondence) as cnt, CustomerName, FollowupCorrespondence from tableA group by caseID, CustomerName, FollowupCorrespondence; ) as T group by caseid, customername, followupcorrespondence I get the following output, but what I need is to get '4' in each row of the SumCases column. CASEID SUMCases CUSTOMERNAME FOLLOWUPCORRESPONDENCE ---------------------------------------------------------------------------------------------------------------- 1.................1.............John Doe................1 1.................1.............John Doe................2 1.................1.............John Doe................3 1.................1.............John Doe................4
View Replies !
Need Help With Count Function
Hi, I am new to sql language so bear with me. Suppose I have a relation table Student(department, name, gender): department, name, gender Bio Elisa F Physics Jeanne F Physics Rocky M Math Andy M Math John M and I want to find the number of male students in each department, how should I do it? this is what I got so far: select department, count(*) from student where gender like "M" group by department; but the problem with this query is I will get this table Department Count(*) Physics 1 Math 2 What I want is Department Count(*) Bio 0 Physics 1 Math 2 Can someone help with this? Thank you
View Replies !
How To Use Count() Function?
Dear all, I have a table with the the following columns: ProductCode varchar(50) PK, ProductType varchar(50), ProductCategory varchar(50), InStock (bit), Locked (bit) What I want to do is to query this table to return me a list of product type and category, and the total number of products in each category, and the count of number of instock, and locked. i.e. Type, Category, TotalCount, NumberInStock, NumberNoStock, NumberLocked, NumberUnlocked. How do I do this in a query? Any help is very much appreciated. Eugene
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 !
Multiple Count Function?
OK I am having problems trying to figure out or if it is even possible to do this in MSSQL Here is my records 1=New 2=Open 3=Closed Table stats ID, Status 1 1 2 1 3 1 4 2 5 2 6 3 So I am trying to count each of the status So Total records should be 6, New 3, Open 2, Closed 1 records. SELECT COUNT(*) FROM Stats Thats about all I know on this, will give me the total. but is there a way to count the total and count individuals.
View Replies !
Can't Divide A Count Function?
I am trying to divide 2 funcitons by each other and I am coming back with 0 as my result in one of them while I am getting the correct answer in another. The two columns I am trying to pull with functions are: SELECT sum(D.PrincipalBal)/sum(L.PrincipalBal)*100 as DelqRatioByBal , count(D.LoanID)/count(L.LoanID)*100 as DelqRatioByCnt FROM Loan L INNER JOIN DelqINFO D on D.LoanID = L.LoanID I get the correct result back for 'DelqRatioByBal' but I get 0 back for 'DelqRatioByCnt' Any suggestons?
View Replies !
COunt Function In Report
I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far. I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial. So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field I also want to print the the same fields in the report total area. Any ideas ?? =Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PAID") =Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "UNPAID") =Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PARTIAL")
View Replies !
Add Count Function To My Query
Hello, I have created the following query... and need to get the total records display for my report. I have tried adding in the count(*) function to my select list, but I get errors. Any help is appreciated. SELECT A.ParentSubjectName, A.ParentSubject, A.SubjectId, B.CreatedOn FROM dbo.Subject A INNER JOIN dbo.Incident B ON A.SubjectId = B.SubjectId WHERE A.ParentSubjectName LIKE 'ACDelco Products%' AND (B.CreatedOn >= '2007-01-01' AND B.CreatedOn <= '2007-11-30') AND A.SubjectId IN ( 'C44ADE3E-527B-DC11-8A2D-00170857BDE7', 'F8758E52-527B-DC11-8A2D-00170857BDE7', '7E65F458-527B-DC11-8A2D-00170857BDE7', '7F65F458-527B-DC11-8A2D-00170857BDE7', '2BE35262-527B-DC11-8A2D-00170857BDE7', '2AE35262-527B-DC11-8A2D-00170857BDE7', 'A2002127-527B-DC11-8A2D-00170857BDE7', '41A8A66F-527B-DC11-8A2D-00170857BDE7', 'A3002127-527B-DC11-8A2D-00170857BDE7', 'D6C08B45-527B-DC11-8A2D-00170857BDE7', 'C439FB4B-527B-DC11-8A2D-00170857BDE7' ) ORDER BY B.CreatedOn[/blue]
View Replies !
Count Function To Filter
Guys, I'm using the following code and I just want display all glcodes that have a count less than 2: select glcode, sum(abs(sysvalue)) 'Movement', count(glcode) 'Occurances' from jet group by glcode order by occurances The following sytax works i know i need to use the where function. Does anyone know the syntax? Cheers Michael
View Replies !
Count/sum Function Query
create function mytotalcount (@audit varchar(50), @startdate datetime, @enddate datetime) returns table as return ( select t.value,sum(t.countvalue) as totalcount from ( select sm.value,count(sm.value) as countvalue from subjectbase s join stringmap sm on s.organizationid = sm.organizationid inner join audit a on s.subjectid=a.subjectid inner join incidentbase i on i.subjectid=s.subjectid where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and sm.attributename = 'contractservicelevelcode' and sm.ObjectTypeCode = 112 group by sm.value ) t group by t.value ) value totalcount ------------------ NHLBI Employee329 NIH Employee329 Public329 VIP329 instead of different values i m getting same... there is something wrong in joins..can anyone help me? thanks.
View Replies !
Need Help With Count Function And Temporary Tables
I have data like this in a two column temporary table -ID Age23586 323586 323586 223586 223586 123586 123586 123586 123586 1I need to create a temporary table that look like this:ID Age1 Age2 Age3 Age423586 5 2 2 0However, what I get is this:23586 5 NULL NULL NULL23586 NULL 2 NULL NULL23586 NULL NULL 2 NULLHere is the query that I am using...select managed_object_id, (select count(Age) where Age = 1) As Age1,(select count(Age) where Age = 2) as Age2,(select count(Age) where Age = 3) as Age3,(select count(Age) where Age = 4) as Age4into #enhancementCount from #enhancementsgroup by managed_object_id, AgeWhere's my mistake?Thanks-Danielle
View Replies !
|
TRACKER
