ASP.NET SQL Server: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

In an ASP.NET / SQL-Server web app I hit this error in a web form after modifying a the query of a TableAdapter in a .xsd file using VWD:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

The underlying SQL Query was a fairly simple UNION ALL query looking for a value (in this case an email address) across several different tables. I had two new tables to search in, so I simply added two more UNION ALL sections to the query for these new tables.

Then I hit the above error. There were some slight differences to the column definitions between the new tables and the existing ones. I tried all sorts of things including CASTing variables and checking for NULLs, all to no avail. Eventually I twigged that it was hanging on to something in the .xsd file behind the scenes.

Solution: Copy the SQL from the table adapter, delete the table adapter, then re-create it and paste in the old SQL. Runs perfectly. (Well, it did after I had two goes at getting the table adapter name exactly the same as before!)

Oh the joys...!

How to use NEWID() in SQL Server User Defined Functions (UDF)

Using NEWID() in an SQL Server user defined function returns an error message:
Msg 443, Level 16, State 1, ...
Invalid use of a side-effecting operator 'newid' within a function.

Here is a trick to get round this. Create a simple view that returns one row containing one column:
CREATE VIEW dbo.vu_newid
AS
    SELECT    NEWID() as mynewid


Test this out to prove it is working and returns a different NEWID each time.

Then in your function, use the view to return a NEWID or set of NEWIDs as follows:

To populate a variable:
DECLARE @mynewid uniqueidentifier
...
SELECT @mynewid = mynewid FROM dbo.vu_newid

To use in ORDER BY:
SELECT mt.*, vid.mynewid as mynewid
FROM   mytable mt
  CROSS JOIN dbo.vu_newid vid
ORDER BY vid.mynewid

To use in ROW_NUMBER():
SELECT ROW_NUMBER() OVER(ORDER BY vid.mynewid) as rn,
       mt.*
FROM   mytable mt
  CROSS JOIN dbo.vu_newid vid

Happy coding!

SSIS + MS-Sql-Server parameterized queries in "Execute SQL Task"

Struggled a bit with getting parameters to work with hand-crafted SQL in an "Execute SQL Task" that used an OLE DB Connector to the MS-SQL-Server database.

Eventually, after some playing around and some googling I realized that the parameters (which are marked by ? in the query) need to be mapped (in "Parameter Mapping") to numbers (0-based) in the "Parameter Name" column.

Here is the summary of what I found:

Connector	Parameter Names
OLE DB	0,1,2...
ODBC	0,1,2...
Excel	0,1,2...
ADO	1,2,3...
ADO.NET	@namedParameters...

Special thanks to: this answer on stackoverflow by Teju MB and christiandev

SSIS expression to generate date usable in file names

I finally cracked how to generate the date in an SSIS package in the ISO format of YYYYMMDDHHMM. By including this in file names, e.g. when archiving, it keeps them in logical order when sorted.

Here's the magic expression part:

SUBSTRING( REPLACE( REPLACE( REPLACE( (DT_WSTR, 30)GETDATE(), "-", ""), ":", ""), " ", ""), 1, 12 )

This will produce 201208131830 at 18:30 on 13/Aug/2012. The final 12 in the expression is used to chop it down to the minute. Use 14 if you need it down to the second.

SSIS SMTP Connection Manager setup

After fighting with setting up an SMTP Connection Manager  connection, testing various domain names in the SMTP Server property, it suddenly dawned on me:

localhost

is all you need! Plus ticking the Use Windows Authentication in my case.

MS Sql Server Queries: Grouping with ROLLUP or CUBE

Working on a GROUP BY query I decided I needed ROLLUP. That bit is pretty easy:
SELECT X, Y, COUNT(*) AS vol 
FROM Tbl 
GROUP BY X, Y WITH ROLLUP
The sub-total lines contain NULL in the X and Y columns. This all applies to CUBE as well.

However, in this case, X and Y contained NULLs, and that makes the output confusing. I remembered there was a way to change the X and Y values in the sub-total lines and had to google for it. Here is the syntax as a reminder to self!

SELECT   CASE WHEN (GROUPING(X) = 1) THEN 'ALL' ELSE X END AS X,
         CASE WHEN (GROUPING(Y) = 1) THEN 'ALL' ELSE Y END AS Y,
         COUNT(*) AS vol
FROM     Tbl
GROUP BY X, Y WITH ROLLUP

The GROUPING(colname) function returns 1 when on a sub-total line and 0 when on a data line.

One little caveat! Where I have put the value 'ALL' - that needs to be the same datatype as the column you are testing. Alternatively, convert/cast the column as a string.

A more standards-compliant syntax for the GROUP BY ... ROLLUP clause, which is supported by later versions of MS SQL Server, is:

GROUP BY ROLLUP(X, Y)

Reminder:

ROLLUP gives you sub-totals and a grand-total based on the GROUP BY hierarchy.

CUBE gives you sub-totals and a grand-total based on every possible combination in the GROUP BY list.