I'm using and Execute Sql Task to get a count of the record in the table: How can I make the workflow to stop if it doesn;t meet the count requirement and continue if it does to the next flow. I'm looking at expression...but a bit comfused about using it.
thanks
Hello All,
Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.
The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...
Code Snippet
=Count(Fields!ResponseTime.Value > "500")
However, this appears to just return the count of all rows and ignores the "500" part.
Am I missing something? If someone could post a alternate code snippet, that would be great.
Thanks in advance,
Clint
I have a table with kind of data as below
PhNo Address DP AutoID Name
0123 RH 001 4577 J
0123 RH 004 4567 JD
0124 RH 010 4467 KK
0121 HN 007 4667 RK
012 SVN 009 4787 RKK
012 SVN 009 4787 RKR
011 SVN 009 4787 KKR
I need distinct phone numbers >1 for a address and if the same phNo has 2 different names I want to skip the second one.I want the output like below
PhNo Address DP AutoID Name
0123 RH 001 4577 J
0124 RH 010 4467 KK
0121 SVN 009 4787 RKK
012 SVN 009 4787 RKR
011 SVN 009 4787 KKR
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 have a stored proc. in that proc i need to select a value based on which one is greater.
Here is a non working example
select
name,
if (truck1.age > truck2.age, truck1.age, truck2.age)
from person
left join truck truck1
on truck1.make = person.make
left join truck truck2
on truck2.make = person.make
Thanks in advance for any help with this
I'm trying to sniff or view a fairly large query that get's passed to my sql server.
The problem I'm having is that the dbcc inputbuffer only shows the first 255 chars of query and I need to see the whole thing.
Is there another method or a way to increase the size of this so I can see the whole query?
Thanks
- jeff
Hi Guys
I have a table with id, date column in it. How do i list all records which has date greater than 30 day?
Thanks
Is this Correct?
SELECT * FROM TABLE WHERE DATEDIFF(DAY, DATE, GETDATE()) > 30
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.
How do I query just the changes?
Example dataset:
idrating
15
25
35
45
56
66
[code]....
There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
idrating
45
56
97
118
189
I use Microsoft SQL 2008
I was comparing diffrent columns and within those columns there are specific values. I want to get the greter values using SQL 2000
i want something like these but i think there was an error in scripting
CASE
WHEN a > b,c,d,e THEN a
WHEN b > a,c,d,e THEN b
WHEN c > a,b,d,e THEN c
WHEN d > a,b,c,e THEN d
WHEN e > a,b,c,d THEN e
END
Please help. Is there any possible way to implement this? Thank you.
Hello,
I would like to create a job that runs ones a day to delete records in a table that are older than one day. Any ideas?
Thanks,
Nina Melhem
The length of my query string is greater than 255 characters. SQL Server is truncating the query string when I assign it to a Local variable.
What should i do....???
Here is the first part of a query for MySQL that I am trying to get working on MSSQL:
Code:
SELECT n.*,
round((n.rgt-n.lft-1)/2,0) AS childs,
count(*)+(n.lft>1) AS level,
((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
(( (n.lft-max(p.lft)>1) )) AS upper
FROM table n
...
But, I get this error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
Is there a way to convert this? Thanks
I created this unique codes and I need all [FRMDAT] field set to "12/31/2014" in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > "12/31/2014"
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
im supposed to output the companies that have commission rates highter than company "Industrial Appparatus".
is there some whay to modify this code so that it will work?
commissionrate > ALL(Select commissionRate From salescompanydomestic Where companyName = 'Industrial Appparatus')
I have a query pulling all records with a disconnect date and a transaction date. However, I would like to retrieve any records that have a transaction date greater than 30 days from the disconnect date. I have been unable to figure out the correct formula to use. I think I need to use the datediff function in SQL, but I've never really used this function before.
View 2 Replies View RelatedProblem: I want to set compatibility_level only when it is greater than 110.
Solution: Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110
ISSUE Irrespective of IF Exist statement the alter database statement is executed all the time.
Here is the sql statement
IF EXISTS (
SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'
)
BEGIN
ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
END
What is that I am missing here ?
I have a column of varchar(2000) but when I use it in a select statement I only get the first 255 characters displayed. (all the data is there as I can see different parts using substring) How do I get the complete column to display?
View 2 Replies View RelatedI think I'm trying to do a simple query on maximum date.
I've got 100 tools that have been used over the past three years.
Some of the tools are used almost every day. Other tools haven't been used for a month, while other tools haven't been used for a year or more.
Ultimately I'm trying to just find the list of tools whose latest date of use was a year ago.
I have a list of tools and a list of times each tool was used.
I think I'm going to have to do a search that for each tool what was the times it was used. That I can do.
What I'm not sure of is how to then pull only the latest date for each tool.
Once I get that I can then do a query off that result to pull the "oldest latest" date of use.
finding the first value to cross a specific threshold in SQL Server
LoadTemp1 LoadTemp2 LoadTemp3 TimeStamp
300 350 200 12-10-2013 13:30
177 238 333 12-10-2013 13:05
240 190 170 12-10-2013 12:45
How do I design query to find the first LoadTemp* that cross a threshold of 340 degrees per TimeStamp.
When I use the following SQL with the variable @CutOFFTID the second select statement query takes several minutes. If i run it replacing the value in the where clause with the acutal value of the variable it runs instantly.
DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROMdbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)
DECLARE @MinPeriod int
SELECT @MinPeriod = Min(period)
FROM agr.dbo.atrans
WHERE agrtid >@CutOffTid
Greetings,
Is there anything new on returning max of two values in SQL 05? There seems to be nothing I have searched everywhere. Only option might be to create my own UDF or do a CASE statement, but I could not believe my eyes there is no such thing?
Thanks
I want SQL to look at a date field and sort the data by todays date and greater. Even though there may be some data older than today. I've tried something like this but not working
order by SSD_SED >= GETDATE()
Where the date field is SSD_SED.
I am trying to select all the data which are not associated with values older than a specific value.. For example I have:
paola -- 2
paola -- 3
paola -- 10
jessica -- 3
jessica -- 12
james -- 3
james -- 2
james -- 5
I want to query only those names, that are associated with numbers greater than 3. So, for this example, the result would be only Jessica. the others should be discarded.
I have a 300MG DB and Query Analyzer gives me the "... DB larger than configured..." error when I try to connect to it...
What is the work around?
Thanks in advance
JEK
I created this unique codes and
I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > ‘12/31/2014’
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
I have a created a table and entered data into the table as follows:
CREATE TABLE t ( id INT , txtcol varchar(1000) )
INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'ATXR_SOURCE_ID,CDDL_AG_PRICE,CDDL_ALLOW,CDDL_ALTDP_EXCD_ID,CDDL_CAP_IND,CDDL_CHG_AMT,CDDL_COINS_AMT,CDDL_CONSIDER_CHG,CDDL_COPAY_AMT,CDDL_DED_AC_NO,CDDL_DED_AMT,CDDL_DIS_PA_LIAB,CDDL_DISALL_AMT,CDDL_DISALL_EXCD,CDDL_DISC_AMT,CDDL_DP_PRICE,CDDL_FROM_DT,CDDL_PAID_AMT,CDDL_PF_PRICE,CDDL_PR_PYMT_AMT,CDDL_PRICE_IND,CDDL_REF_IND,CDDL_RISK_WH_AMT,CDDL_SB_PYMT_AMT,CDDL_SURF,CDDL_TOOTH_BEG,CDDL_TOOTH_END,CDDL_TOOTH_NO,CDDL_TOT_PA_LIAB,CDDL_UNITS,CDDL_UNITS_ALLOW,CGCG_ID,CGCG_RULE,DPCG_DP_ID_ALT,DPDP_ID,DPTC_CD,PDVC_LOBD_PTR,PSDC_ID,UTUT_CD' )
Now if i select data using the query below the txtcol field displays only 255 characters :
SELECT * FROM t
Y is this happening?
hi all, I'm after a way to produce a single-lined output of a char/varchar string replicated over 8000 times.
basically I've been given a task to create a stored procedure which can accept any integer to replicate a string X times.
From what I've read the replicate() fn will covert to varchar of max 8000 bytes.
I've thought about creating a UDF to accept the varchar and int and run a loop to keep outputting but 'print' will pass an endline to the buffer which is no good for my loop.
Any help would be great on this. Thanks.
Hello,
I am running a stored procedure thru' isql.
This returns only one field.
The sp is executed fine but if the data has length greater than 255 the data is truncated.
How do u capture the full output ?
Thanks In Advance
Ashutosh
:rolleyes:
I need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
I want to know how to make StartDate and EndDate should be greater than current date....
View 4 Replies View RelatedWe have Windows Server 2008R2 installed on VM Server.On that we have three SQL instances running. From few past months we are observing physical memory is going high. Earlier we observed it was at 86-88%, now it is 96-97%.We have 16RAM & 8 CPU cores on VM. what is the best and ideal configuration so that we can rectify the high physical memory issue.
View 13 Replies View RelatedWe have a requirement to call a Web Service method from SSIS where the method accepts a single parameter that is derived from a database recordset and can exceed 4k characters. From the little we know about the Web Service task it is limited to only accepting direct input and variables. Does anyone have any suggestions to overcoming this issue? Any detailed information in possibly calling the Web Service from script or using an object variable would be extremely appreciated.
Thank you in advance for your help.
What I want to see is how to show PO's who's due dates are > three
Select * from mytable
where myorderstatus = 'onorder'
This is my duedate format and what I want is any that past that date over 3 days
2014-08-11 00:00:00.000