|SSRS report to email subscription only when there is data|
The workaround we use for this problem is kind of silly, but very
Add a row count check at the beginning of your code like:
IF (SELECT COUNT(X) FROM TABLES)>0
RAISERROR ('No Rows to Report',2,1)
The error will halt execution of the subscription.
|most recent values entered for a column in SQL Server 2012|
try to use sql server IMPORT tool. that may help you do your
conversion with minimum work. You may find Imports in :
Right click on your DB in SQL Server/ Select Tasks/ Select Import Data
|Count block of time|
do a count on your case statements then groupy by whatever you need,
this will give you a count on sessions since you didnt really let us
know how to differenciate between visits and sessions
CASE WHEN DATEPART(hour, min(ApptStart)) BETWEEN 0 AND 12 THEN
count(distinct appointmentid) END AS 'MORNING_SESSION',
CASE WHEN DATEPART(hour, min(ApptStart)) BETWEEN 13 AND 17 THEN
|Full-text search does not find some prefixes|
Thank you for posting this question Mathias. I just wanted to point
out the suggestion from Microsoft (via
to switch to the format
SELECT * FROM [Profile].[DocumentView] WHERE FREETEXT(Content,
While this doesn't help Mathias, it was able to help me out and might
help the reader.
In my case, I had a pro
|Custom Data Service Provider (WCF Data Service) for a dynamic table in MS SQL Server|
I tried this about half a year ago (Web API OData 2). What I did was
to create the EDM model on the fly for every request and set it in a
custom OData Route constraint. In the controller I returned
EdmEntityObjects of the type corresponding to the current request. I
expected that returning an IQueryable would do all the magic of
filtering, but it failed saying that the operation is not supported.
|Insert statement conflicted with the Foreign key|
The first column in the insert list is Quantity but you select cost
price which is the 2nd column in the insert list. You are inserting
Quantity as Production_ID causing the FK violation.
insert into [pp
|Pentaho Spoon Tool Transformation Order|
Inserting/updating the same database in parallel, i am afraid will
throw you an error (like you have done). please try to separate these
transformations into multiple ktr files. I assume you will not face
the same issue again. Hope it helps:)
|BIDS 2008 - Lookup value in source against two columns in lookup table|
You can use a union as the source in the lookup transformation, then
use a single transform:
SELECT id, column_a FROM dbo.Table
UNION SELECT id, column_b from dbo.Table
Conversely, you can use two lookups with the first looking at column_a
and pass the no match output to a second lookup with column_b, then
union the results.
|Find duplicate values in SQL Server 2008|
There is a similar question here about address standardizations using
soundex in SQL Server
SQL address data is messy, how to clean it up in a query?
The relevant function is soundex()
|If an error occurs in my stored procedure, will a cursor local to that procedure get cleaned up?|
Based on the very useful comment from SubqueryCrunch, I've empirically
proved that the cursor gets cleaned up (without all the transaction
stuff), at least on SQL Server 2012 with the options we're using:
CREATE PROCEDURE tjtemp
DECLARE @foo TABLE
DECLARE @id INT
INSERT INTO @foo (id)
VALUES (1), (2), (3), (4), (
|FOR XML PATH - how to implement grouping?|
select '' as "OrderResponse-Lines/Line/Line-Item/ItemDescription",
'' as "OrderResponse-Lines/Line/Line-Item/ItemStatus",
'' as "OrderResponse-Lines/Line/Line-Item/ItemType"
for xml path(''), root('Document-OrderResponse'), type;
|IIF to Case statement|
IIf in JET SQL translates to CASE WHEN in SQL Server as follows:
IIf(condition, whenTrue, whenFalse)
translates directly to
CASE WHEN condition THEN whenTrue ELSE whenFalse END
For nested expressions
IIf(condition1, whenTrue1, IIf(condition2, whenTrue2, whenFalse))
you can either translate them directly:
CASE WHEN condition1
ELSE CASE WHEN condition2
|Ihow to merge two flat files have different data type in ssis|
Since your 2 sources have different columns, you could try to use
Derived Column transforms to create additional columns. This will make
the number of columns equal for all 2 sources. Next, you could use a
Union All transform to combine input from all sources, and then write
to your flat file.
|Join two tables on matching ID?|
Rewrite your query to this:
SELECT DENumber, AcquiredDate, ItemDescription, ItemName, LocationName
FROM dbo.Assets INNER JOIN dbo.Locations ON Assets.LocationId =
WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) >= 6
To clarify: In a JOIN, you must specify the names of both tables on
either side of the JOIN keyword. Then, you specify the join criteria
|SQL Pivot IF EXISTS|
WHEN value = 'a' THEN 'YES' else 'NO'
WHEN value = 'b' THEN 'YES' else 'NO'
WHEN value = 'c' THEN 'YES' Else 'NO'
GROUP BY CustID
|how to insert data to one table from 2 different tables?|
As I didnt get any common column in both table here the query insert
all rows from two table separately.
INSERT INTO NEWTABLE (patientUid,PracticeUid)
SELECT patientUid, NULL FROM PatientTable
SELECT NULL, PracticeUid FROM IndividualTable
If there exist a relation. Then we can use joins.
INSERT INTO NEWTABLE (patientUid,PracticeUid)
SELECT A.patientUid, B.PracticeUid
|Compare when one field is empty but concatenation is to be compared|
Use NOT EXISTS rather than NOT IN. It allows you to use more than one
field for comparison when doing the exclusion:
FROM tbl_Students AS s
WHERE NOT EXISTS
( SELECT 1
FROM tbl_Exclusion AS e
WHERE e.Class = s.Class
AND (e.Section IS NULL OR e.Section = s.Section)
CREATE TABLE #tbl_Students (Na
|SQL Server : trigger AFTER INSERT,UPDATE|
Try changing your sub-query to JOIN. Something like this.
SET idWork = b.idWork
FROM [HOURS] A
JOIN (SELECT U.idWork,
FROM [USER] U
JOIN inserted I
ON U.id = i.idUser) B
ON a.id = b.id
SET idWork = b.idWork
FROM [HOURS] A
JOIN inserted I
|Get Miliseconds and NanoSeconds in SQL Server|
You are trying to get result in milliseconds for a long period of date
and that caused the error.
If the return value is out of range for int (-2,147,483,648 to
+2,147,483,647), an error is returned. For millisecond, the maximum
difference between startdate and enddate is 24 days, 20 hours, 31
minutes and 23.647 seconds. For second, the maximum difference is 68
Source : http://msdn.micros
|SQL Query to Calculate the Rolling Difference by Date|
Try this. Use correlated sub-query to find rolling difference
CREATE TABLE #tem
(SKU INT,DataDate DATETIME,Web_qty INT)
VALUES( 2,'2014-11-17 00:00:00',404),
|SQL Script to replace all FK values based on the dependencies? Is it even possible?|
Yes, if you can first create a new user and after inserting , update
the reference based on condition.
declare @newUserid int , @olduserid
insert user values (colval1, colval2,..)
select @newUserid = @@identity
update tablename set userid = @newUserid where userid = @olduserid
if multiple user, then create a dynamic query for update statement.
Declare @query nvarchar(max)
Set @query =
|How to load data from oracle and sql server to HAWQ using Spring XD|
You need to integrate sqoop jobs with Spring XD. See link below for
sqoop jobs with springxd
|Execution Plan in SQL Server?|
Shitty statistics which simply do not match reality because they are
Complex queries where the math behind assumptions goes wrong.
Statistics entries are always simplified (A histogram) and some values
may not fit into it.
Both can have part in it.
|Null values stored in table via Stored Procedure|
Your code reads
SELECT @PasswordOld = Password,@Type=ClientType,@IP=IPAddress
WHERE Username = @userid
Wouldn't this mean that no row will be returned for a Username that
does not exist? So, the values for ClientType and IPAddress will not
get populated and will remain NULL, which would be the expected
However, if you want to store some value, or these fi
|how to do instr in tsql join|
Try something like this. But this is not a efficient code.
SELECT DISTINCT a.fullname,
FROM (select distinct fullname from huge_table) a
JOIN tiny_table b
ON a.fullname LIKE '%' + namepart1 + '%'
AND a.fullname LIKE '%' + case when len(namepart2)=0 then
' ' else namepart2 end + '%'
AND a.fullname LIKE '%' + namepart3 + '%'
|I cant figure out how to expand my code|
You can do it like this:
Dim stmt As String = "SELECT * FROM members WHERE members.User = '"
& txtUser.Text & "'"
if Not txtPC.text.trim.length = 0 then
stmt = stmt & " AND members.PC = '" & txtPC.Text & "'"
if Not txtIP.text.trim.length = 0 then
stmt = stmt & " AND members.IP = '" & txtIP.Text & "'"
if Not txtAsset.text.trim.length = 0 then
|SQL Error transfer one field to another database and table|
If you haven't already, backup your database(s) before trying this
code. It is untested:
set new_TarikhTamatPengajian = (
select TOP 1 try_convert(datetime,LEFT([CRSE_EDTE],
4)+'-'+RIGHT([CRSE_EDTE],2)+'-28',111) as new_CRSE_EDTE
where DMSTAG.dbo.TEMP_AKAUN_MARA.STUDENTN =
|SQL - Setting a Default Value from Trigger|
You have to handle the Update trigger instead of the delete trigger,
because you need to catch when dno field is being UPDATED.
Here is the code:
CREATE TRIGGER empdeptfk_update ON employee
INSTEAD OF UPDATE
DECLARE @DNO INT
SELECT @DNO = dno
IF @DNO IS NU
|Displaying results based on different selection in sql server stored procedure|
you are setting default value to empty string and comparing with NOT
NULL as empty string is NOT NULL, search is done for empty string.
First part to get results when only filters condition is met
SELECT Id,Name,Work,YEAR,Rep from #SalesRepVisit
WHERE ( @RepCode ='' OR Rep = @RepCode )
AND ( @Name ='' OR Name = @Name )
AND ( @Work ='' OR Work = @Work )
AND ( @Year ='
|Creating a new SQL Table with data from existing|
What this most likely means is that the original table has duplicates,
that is, titles.pub_id is currently not a unique list. That is, you're
trying to make it unique, and it is not.
To confirm this, run this query:
SELECT titles.pub_id, COUNT(*) As CountForPub
GROUP BY titles.pub_id
ORDER BY CountForPub DESC
If you see any pub_id's that have the count greater than 1, yo
|Best SELECT syntax performance with 5 parameter|
Second approach is better, you can simplify it with or condition based
on parameter value
SELECT * FROM Table1
WHERE (@param1 ='' or Column1 like '%'+ @param1 +'%' )
(@param2 ='' or Column2 like '%'+ @param2 +'%' )
|Removing duplicates based on systemID, primary key and email|
create table temp(
insert into temp
select 1,'email@example.com',null union all
select 2,'firstname.lastname@example.org','systemID2' union all
select 3,'email@example.com',null union all
select 4,'firstname.lastname@example.org',null union all
select 5,'email@example.com','systemID3' union all
;with cte as(
|change display of rows into column using PIVOT in sql server|
Use the PIVOT function:
WITH Data AS (
-- this is your actual query
--select d.disposition, count(a.serialnumber) AS SUBTOTAL
--FROM FADB a, FARepair b, Disposition d
--WHERE a.ID = b.ID AND b.DispositionID = d.DispositionID
--GROUP BY d.Disposition
-- this is a sample just to make up the example
VALUES ('a', 45), ('b', 100), ('c', 39)
|SQL Server : merge statement error|
SOURCE is a keyword used in MERGE. i.e. WHEN NOT MATCHED BY SOURCE.
The error is in this line
USING temp_table_inventory as SOURCE
Change the name to Src
|SSIS 2008 - Script Component - Microsoft.SqlServer.Dts.Pipeline.Wrapper - doesn't contain any public member or cannot be found|
check if all the refernces are there
use namespaces like this :
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
|MS SQL Server select statement: display all combinations from two tables|
If you don't have a departments table, you'll need to create a
subquery to get the distinct list of dept_ids to cross join on:
select emp_id, first_name, last_name, dept.dept_id, status
cross join (select distinct dept_id from empdept) dept
left join empdept on empl.emp_id = empdept.empt_id
and dept.dept_id = empdept.dept_id
SQL Fiddle Demo
|ms access linked table missing primary key|
The second question you asked about why TRUE is represented as -1 has
been asked before. It's the same convention as used in BASIC. TRUE
is equal to NOT FALSE. If FALSE is represented with a zero in 16
bits, and if NOT is the bitwise complement, then TRUE is represented
by 16 bits set to one.
16 bits set to one, in twos complement notation, is equal to minus
|SQL Server UniqueIdentifier as FK|
I do believe the following should do the trick (in the case that
you're wanting to set up CustomerUsers.CustomerUniqueID as a FK)
ALTER TABLE customerusers
ADD CONSTRAINT fk_customerusers_users FOREIGN KEY (customeruniqueid)
what I changed: inferred that error was related to column not
existing, noticed that you had already created a column which looked
|SQL Server 2012 INNER JOIN returning records that it shouldn't|
I would use EXISTS in this case:
from A2 T2
where T2.F2 not in (V1,V2,V3,V4)
and T2.F3 in (V5,V6,V7,V8)
select * from A1 T1 where T1.ID = T2.ID)
This is much simplere and will give you only those records in T2 that
have a matching record in T1.