One of the common SQL tasks is to return matched data only or all data if other table is empty. In other way it should work like INNER JOIN when both table have data or LEFT JOIN when one table is empty. Real world scenario could be like this:
- Return all properties if filter table is empty. If filter table has any data then return properties that matches to filter.
This is how we can create such query:
CREATE TABLE MyData(Id INT, Something VARCHAR(10), OwnerId INT);
CREATE TABLE OwnerFilter(OwnerId INT);
INSERT INTO MyData VALUES(1, 'AAAA', 1);
INSERT INTO MyData VALUES(2, 'BBBB', 1);
INSERT INTO MyData VALUES(3, 'CCCC', 1);
INSERT INTO MyData VALUES(4, 'AASS', 2);
-- Other table is empty. Query returns all rows.
SELECT *
FROM
(SELECT NULL AS Gr) AS Dummy
LEFT JOIN OwnerFilter F ON (1 = 1)
JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId);
-- Other table has data. Query returns only matched rows.
INSERT INTO OwnerFilter VALUES(2);
SELECT *
FROM
(SELECT NULL AS Gr) AS Dummy
LEFT JOIN OwnerFilter F ON (1 = 1)
JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId);
There is a bug in SSDT that causes database options to be generated for SQL Server 2012 even if the target platform is set to SQL Server 2008.
I observed it in a such scenario:
sqlpackage.exe was used to generate publish script to create new database. Sqlpackage target is set to a dacpacfile (just empty model without any tables).
The problem is that publish script has few options set which are available only in 2012. The options are:
But when I generate publish script directly from VisualStudio and specify target database as real 2008 database (not dacpac) then resulting publish script is correct. It doesn't contains above options.
I have made hundreds of reports using PowerBuilder datawindows. It is a great environment for designing complex reports with lots of controls and events. Nowadays I use SQL Server Reporting Services and I am disappointed how complex it is. Despite lots of properties it is missing some basic features like: expression validating in editor, data preview in design view, scrollbars, etc.
One of missing features is highlighting selected row. Highlighting selected row is very useful in master-detail reports. By clicking on a master row the detail report is being refreshed. User want to see which master row is currently selected. In this post I show how to highlight selected row on report in SSRS.
The final effect will look like on this screen-shot.
You might also watch a video how it works:
Highlighting selected row in Reporting Services report
I use Report Builder 3.0 for this tutorial. I assume that you have already created a master-detail report and you only need to add highlighting. If you don't have the master-detail report you can follow this tutorial: http://www.codeproject.com/Articles/270924/Master-Details-Report-in-SSRS Lets say you have a master report with dsMaster data set and a detail report with dsDetail data set. Also you have a parameter, lets say @masterRowId. This parameter is used to pass a value from the master report to a child report, so the child knows which records to display.
Now it is time to add highlighting selected row to the master report:
Right click on dsMaster data set and choose Dataset Properties
Click on Parameters tab
Add parameter - @masterRowId and assign it value of -1 (or any dumb value which is not present in your master table)
Click Ok to close the Properties
Select data row in the master report by right clicking on the row indicator
In Properties window locate BackgroundColor setting
Expand the list of possible values for the BackgroundColor setting and choose the last - Expression
Enter following formula:
Click Ok, save reports.
That is all. You have just added highlighting selected row feature to your SSRS report.
When you set up a WCF service to use Windows Authentication mode you might get such error message: 401 - Unauthorized: Access is denied due to invalid credentials.
I want to present a solution that works for me.
Editing web.config
First thing is a properly edited web.config file:
Just replace 'service - name' and 'endpoint - contract' attributes values (lines 26, 29) with your specific names.
Configuring service on IIS
Next step is to set Windows Authentication for service in IIS settings. On IIS7 it can be done in following way:
Go to: WebSite - Your service - Authentication:
Disable Anonumous and all others
Enable Windows Authentication
Next click on Windows Authentication and then Providers
Add Negotiate provider
An important thing is providers order
NTLM
Negotiate
Applying authentication in an application
At your client application add following code so it can authenticate:
