I have a database that tracks events as they occur. The events are used to track the completion of a series of tasks. The events have a event type id and a result. The type id is used to track a specific task, and there is a result for it.
so the table looks something like:
DATETIME, EVENT_ID, RESULT_CODE
Since the same task can be performed many times a day, I am only interested in getting a snapshot of all event types at 11:59:59 PM. If an Task of type A had a sucess event then failed event on the same day, I would count it as failed.
Additionally, I if an Task succeeded on say 5/18, an the same task did not occur on 5/19, I would want to count it as a success because that was the last known state from the 18th.
I need to produce a report showing our progress over time (I would guess plotting a line between 0 and 100%), around tasks that have passed, failed, and have not been completed. (no completion record exists)
As far as I can tell, I need to get the last known result, per event type, per day.
I know how to do this in TSQL, but my team is making a big bet in Analysis Server and I'm at a loss as to how I should tackle this.
I have a table of status_Master where the details are maintained by street name with status but I need the result of total count by each status against the street name.
Status_Master
Sl NoStreetNameStatus 1       Delhi        Pending 2       Delhi        Working 3       Delhi        Working 4       Mumbai        Pending 5       Mumbai        Pending 6       Delhi        Working 7       Delhi        Problem 8       Mumbai        Working 9       Mumbai        Problem 50       .        . 100       .       . 200       .       .
Result as below
StreetNameWorkingPendingProblem
Delhi              3           1           1 Mumbai          1           2           1
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
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
 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
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
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.
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
Unless I grant a developer "system admin" role, they are unable to see whether or not any job *not* owned by themselves is running?
EX: Developer A can't see whether Developer B's job is executing? Why? I've granted them both db_owner in msdb, and all permissions possible in msdb. Is there an extended sp I can look for?
I have a table with columns c1, c2, c3, c4.if all nulls or blanks. Status = 0if c1 assigned but no c2, c3, and c4, then status = 1if c2 assigned but no c3 and c4, then status = 2if c3 .. then ..if c4 .. then ..I want to have one SQL to get the status like (ignored checking forblanks here for demo)SELECT Status = (if not c4 is null then 4else not c3 is null then 3else not c2 is null then 2else not c1 is null then 1else 0)FROM mytable.Thought of using CASE ... WHEN ... but it is only on one colum.Any better idea.ThanksJohn
In SQL 2000, go to Management, than Jobs, you will see a list of all jobs and their running status. I would like to programmatically display the same on my web page. I can call sysjobs table to get the jobs and use sysjobschedules to determine whether it's been scheduled or not. Then use sysjobhistory to obtain their running history. But how and where do I get the Next Run Date information? Is this calculated or stored somewhere?Can anyone give me some help on how to work with sysjobs, sysjobschedules, and sysjobhistory tables? All I am trying to do is to build a web-based app that does the same thing as the Enterprise Manager is doing - create and schedule a job, and monitor the job status.Thanks a lot!Penn
Hi all - am having trouble working out what would be the best way to do this (most efficient)I have written an order, despatch and accounts system. To get a list of orders ready for despatch, I have written a query which calculates the amount of items ordered against the amount of items despatched from that order. If there are any outstanding items, then this order is in the list of orders which are outstanding for despatch. I am having trouble working out how to go about creating a list of orders ready do be invoiced... Orders that are ready to be invoiced can be completely despatched orders or part despatched orders. That is to say that if an order is only half despatched, my client still wants to be able to raise an invoice against that part of the order. I envisage creating a query which checks my orderline table to see if the item has been invoiced and creating the list from there. But as the application is used more and more, this query will take longer to run? ANy ideas? Thanks. Steve.
I need to know the meaning of the status bit in sysdatabases because BOL only reports some of them. For SQL 7.0 SP1 I have some database with a status bit equal to 0 (zero) and equal to 12. For SQL 2000 SP2 I have some database with a status bit equal to 0 (zero),24,20 and 1073741840. So I would really appreciate if someone can explain the meaning of these status bits. Thank you very much. Franco
I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.
Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.
I have a question I hope someone can help me with.
My situation: I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.
The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.
My problem: On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).
Is anyone able to shed some light on this?
I assume that the job will only return a completed status after: (a) both A and B have completed execution, and (b) the BACKUP operation has completed
Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?
My boss and I have recently started designing a new database, and we've got into a bit of a debate about creating status tables (e.g. order statuses).
It was my intial instinct to create a separate table for each kind of status (e.g. an OrderStatuses table, a JobStatuses table, etc), however my boss wanted to create one table containing all the statuses, with an extra field for defining what kind of status it is.
I was just wondering what others thought was the best approach.
While iam connecting to servers through management studio iam facing iam seeing like few servers are connected with Green colour (like>)and few are connected with circle as white.Frm these how can i decide the server status.
I need to know how can i see the replication status. I have 4 servers with ms sql 200 standard server and hundreds of commnad to replicate. Sometime is important to know how mush of this commnads is replicated. Thank You.
T-SQL for checking which jobs are currently running ? And what happen if index rebuild job overlap with shrink log file job Is database wont allow to insert or update for other application
Using SQL Server 2005 w/ SP2 I have a simple select query with a parameterized WHERE clause that in Management Studio runs nearly instantly, everytime. Using the same login, when I run the same query using SQL RS the results (only 6 rows) takes upwards of 5 minutes. If I look at the Activity Monitor, it shows the process as suspended and indicates a CXPACKET. Everything I have read on the forums about this indicate it is an issue with not having SP2 , which I do. Or a 64 bit version, I have 32. Or for large bulk updates, I am doing a very simple select. Does anyone have any clue on this?
Hi everyone, while executing sp_who sub directory, I see a column which makes me totally confused. This column is status column. I understood nothing about its usage. So would you please inform me ?
This probably isn€™t a T-SQL question but couldn€™t find an appropriate place in the forum for it.
I€™m building a statistics page for our customers and i want to show how long the SQL server has been up since the last restart, I€™ve got the windows uptime from elsewhere but wondering if there€™s a way to get the SQL Server uptime.
I need a way to determine if a query is still running on the database server. Is there a way to do this? If the query is still running after 2 hours I need to send a message. How can I query the database for the status of a query?
