I have the following code to send out an email if the row count of a table exceeds a certain number. But it seems to fire an email alert no matter what. condition check part of the code?
use msdb
go
create table ##resultset (
total int)
[code]...
I have one table which contines 3 columns they are
1.emp
2.monthyear
3.amount
in that table i am having 6 rows like below.
emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000
i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.
out put should be like this
1 2000
2 1000
I have a table exam_setup
CREATE TABLE exam_setup
(
setup_id INT,
sub_id INT,
assignment decimal(4,1),
[Code] ....
I have to fetch the values like
assignment = 25
attendance = 15.5
INT_1 =0
INT_2 =0
if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?
[Code] ....
Following is a working code
declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')
[Code] ...
I am getting the output correctly. My requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id
I want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
When I tried this code I am getting error.
I've a variable in my SP that I'd like to use to change the format of my output. Try this!
select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Then this:
select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):
select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2
Work fine.
We are trying to create some alerts in our SQL Server 2014 BI edition.Issue is that, after I chose "Type" as "SQL Server performance condition alert" nothing is listed in the "Object" list box.SQL Server event alerts are working. Issue is only with "SQL Server performance condition alert".
View 3 Replies View RelatedI want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?
**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.
example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia
example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
5 USA America
Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N
I am trying to learn how to store a web form password and than check it when the user log in. So far none of the code I can find works.
Why the following test does not work and what the correct code should be?
Insert Into [user]
values ('name', 'email', HashBytes('SHA1', 'bob'))
GO
Why does the following produce no rows?
SELECT *
From [user]
Where HashBytes('SHA1', password) = HashBytes('SHA1', 'bob')
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case.
hi
as i am a beginner to ssis. i have tried a lot to solce a logicbut it did not work.the situation is as i have a view which is updated dynamically whenever thebase table gets updated. then from view i hvae to select the new inserted row with the order id as identity column. there is a column in a table as salespersonid i have to check if the value is null or not.if its null then i have to assign a sales personid from the table havind the sales person detail. if its already in the row then i have to create a xml file of that data and send file so,e location. i have created a stored procedure to work on the creation of a xml file. but i hve tried a lot about how to check the value of the salepersonid which may be null or anu number and deceide to do one of the above operation. i have to do it in ssis.
i am stuck on it.
can any one have any idea.on proceeding it.
thanks in advance.
I've been running the Ola Hallengren maintenance script for the last five months without missing a beat. Today I find an error stating the UserDatabase Integrity check job failed last night. This is running on SQL Server 2014 BI edition w/64 Gigs.
I ran a DBCC CHECKDB on each database manually and all worked until I tried it on the biggest one that is about 18 gbs. It just keeps running and I eventually stopped it so I'm guessing it is memory, but doesn't make sense considering it has 64 gbs. I have it set to 64/4 max / min. Again, this was never an issue until last night.I've been looking up all morning, but not seeing much on this error "The operating system returned error 1453"?
usual way to check if file exists
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT
print @File_Exists
1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupul', @File_Exists OUT
print @File_Exists
0
If use xp_subdirs like:
EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists, Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
how to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
I want the count of orders of a particular table on weekly basis i.e if date given to me is 10/3/2014 then my output should be count of orders from date 10/3/2014 to 09/3/2014(one week) then count of orders from 2/3/2014 to 08/3/2014(another week) and then from 24/2/2014 to 01/3/2014(another week).....
View 5 Replies View RelatedHow can I select data from a table and row counts from multiple tables in a view. For example:
Select * from Settings -- it gets 1 row only
Select count(*) from NewApps where Status = 'False'
Select count(*) from myUsers where Status = 'Pending'
I just want to get them all in 1 view...
I have two table in two different database. I have to found count for both table and put into other table.
example : Database --> A , table is T1
Database --> B , table is T2
I want count of both table and put into T3 table in database A or B .
Hi,
I have a data-flow-task that imports data to sqlserver.
Now I want to check, if a special column of an imported record is null.
If yes, I have to wait 10 minutes and jump to the data-flow-task again. (Cjeck and wait).
How can I do this with the integration services?
Thanks
Gerd
Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...
View 2 Replies View RelatedI have the following setup:
- An MSSQL 2014 Standard server that houses multiple small databases (in excess of a hundred).
- These databases are frequently dropped and restored by an application that uses this SQL Server.
- There is a business need for this setup at this time, so I can't get away from it. Therefore answers like "don't have so many small databases that are frequently dropped and restored" would be somewhat unuseful
This is the problem I have:
- When I connect SSMS 2014 to the server and expand the "Databases" node, it takes forever to display. In comparison, SSMS 2008 connected to SQL 2008R2 server with the same number of databases displays the Databases tree very quickly.
I ran a trace to see what exactly SSMS 2014 is doing. When the "Databases" node is expanded, it runs a query that checks each database for Memory-Optimized Tables (new and wonderful feature of SQL 2014 for sure, but I'm not using it, at least yet). Naturally, when you have to loop through over a hundred DBs, it takes time. Worse yet, if one of these DBs is in process of being restored, the query sits and waits to time out before proceeding to the next DB. Sometimes this causes outright timeouts. Here is the query:
use [MyDatabase]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]
To be sure, this is NOT a SQL Server performance issue. This server processes a rather heavy workload and has been doing so for over a month, and the workload completes within expected time limits or better. Even so I've done some basic performance measuring, and the server itself is quite all right.
Moreover, if I connect SSMS 2008 to it, I get an error message (Index out of bounds or somesuch), but SSMS 2008 does connect, and displays the Databases tree much faster than SSMS 2014.
I'd like to turn off the option to check for Memory Optimized Objects altogether, as I'm not using the feature.
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
hi guys
I need to do a count on a column in my table but i have to check for a condition first.
Here is my sample data
Providerdate
Region
DHB
DHBName
PHO
PHOName
Practice
Practicename
PracticeAddress
Practice_Startdate
Practice_Enddate
Practitioner
PractitionerName
Practitioner_Startdate
Practitioner_Enddate
Locum
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003
29201
Geraldine Victoria MacKenzie Jordan
8/06/2005
31/10/2005
no
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003
22079
Glenn Morton Colquhoun
1/03/2006
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003
14121
Meaburn Charles Staniland
1/07/2003
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003
9877
Philip White
1/07/2003
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003
11678
Susan Prudence Wilson
1/07/2003
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000045
Horatepai
15 Epiha Street, Paraparaumu
1/07/2003
3881
Wilfred Travis Wilson
5/01/2002
yes
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003
22742
Adrian Howard Beaumont Gilliland
13/01/2005
28/10/2005
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003
18188
Amanda Mary Clarke
1/07/2003
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003
32909
Andre Honda Garib
29/12/2005
no
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003
13042
Christopher Alan Fawcett
1/07/2003
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003
16556
Jennifer Maree O'Donnell
1/07/2003
22/12/2005
YES
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000052
Paraparaumu Medical Centre
92-94 Kapiti Road, Paraparaumu
1/07/2003
13650
Timothy Stapleton Smith
1/07/2003
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000046
Paul Norton Surgery
51 Kapiti Road, Paraparaumu
1/07/2003
14621
Paul Stephen Norton
1/07/2003
NO
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000053
Raumati Road Surgery
23 Raumati Road, Raumati Beach, Paraparaumu
1/07/2003
11202
Katherine Dana Stone
3/10/2005
7/10/2005
yes
1/01/2006
Central
091
Capital & Coast DHB
585171
Kapiti PHO-585171
585171_0000053
Raumati Road Surgery
23 Raumati Road, Raumati Beach, Paraparaumu
1/07/2003
13048
Robyn Lynette Crow
1/07/2003
NO
now i have to add two new columns as LocumYes and LocumNo
In Locumyes column I have to count the number of 'yes' in the locum column grouped by practitioner and in locumno i have to count the number of 'no' for that practitioner.
The situation is a practitioner can work on more than one practice and he can work as a 'yes' locum in one,'no' locum' in another or vice versa.It can be of any sort of combination.
so I have to do a query which should group by practitioner and count the number of yes and no for each practitioner..
Thanks
Mita
select '$ '+ CONVERT(varchar,CONVERT(decimal(10,0),CONVERT(money, Amt_Value)),1) as [Amount]
from Products
How can I sum this column values and need to set a validation like the column has null values it has to return zero.
Hi,
It seems like For Loop Container works like do-while loop in C++. I have set its EvalExpresstion to @[User::SyncStats]==0 , I have an Execute SQL Task before this container that sets the variable to 1 but Container still executes atleast one time.
Anybody else been through this ?
Hi, i need help please!
I want to show a count of the following:
Field values = "CFG1" , "CFG2"
So i want to count the number of "CFG2" values there are.
I tried something like this but does not work.
=count(Fields!Task.Value="CFG2")
Please Assist!
Regards
Is it possible to use a IIF IsNothing expression and check another condition? For all the values that are blank, I want it to check another column if "Disposition" = 2 then I want to show "Inactive"
=iif(isnothing(Fields!Value)," ","MS " & Fields!Value)
Not sure how to do this but here is example of what I have
Table A
ID data1 data2 data3 data4
1 535 452 213 554
2 325 651 321 554
3 654 846 096 355
4 765 658 321 422
I want to have a select that will pull the following information out with count = the number of rows that have matching data in data4
ID data1 count
1 535 2
2 325 2
3 654 1
4 765 1
Right now I am using a VB script to loop thru get the current data4 value then using SELECT COUNT(data1) AS count FROM tbl_toolerrors WHERE data4 = {data4 value currently looking at}
Of course this take a bunch of trips to database and I think there should be a way to do it. I was thinking of a nested SQL querry like
Select data1, data2, ID, data3, (select count ...) Order by data1
can anyone help?
I am using SQL Server 2008.Each stock item will have default 4 document type (1, 5, 6, 7) and each will have 3 zone's (1, 2, 3) to qualify. Each zone will be updated to 1 for that document type if the item successfully pass through it. If all zone are NULL means no transaction. How to retrieve only the failed rows which means not all zone are 1 or NULL.In the image GJ-00064 has one row failed. So how to get the count of failed rows for each item
Expected result:
Uniid <-> Stockcode <-> FailedRows
1670 <-> GJ-00064 <-> 1
I have created a package that use SSIS check pointing for failure-retry mechanism. I knew that when this package fails, on restarting the package it starts from the task where it got failed.
Is it possible for me to override this on a custom condition and start the package at an earlier task where it executed successfully?
Ex:
Check pointing is enabled.
FTP task è Write to Staging Table è Write to Target table.
Assuming I am downloading XML file through FTP and writing to a table.
FTP Download is successful.
Read from XML file and write to Staging table task failed because of downloaded file is not a well formed XML.
Here, FTP task is completed successfully; it failed only in the second task. When I re-run the package; it starts from the second [Read from XML file and write to staging table] task because of check pointing.
Is it possible for me to restart from the FTP task on a custom condition where it executed successfully?
I want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).
My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null
But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.
How do I check the douplicate attribute ?
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
=CALCULATE(COUNTROWS(incident),
apparatus[Incident Response Time] >-1 ||
apparatus[Incident Response Time] <320,
uv_901APP_TYPE[Description]="Engine",
uv_901INCIDENT[Top_Category]="Fire"
[code]....
We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"),
IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")