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...!

"Let your Daemons rest", aka PHP Daemons not sleeping as expected

I was enhancing some inherited PHP code which used the so-called "Daemon Loop" design pattern to read jobs off a queue and hand them off for processing by a configurable number of child worker processes. Specifically, I was trying to get the daemon to wake up once every minute (instead of every 5s) to see it there were any jobs on the queue, so in the loop (in practice the value was read from a config file into a class-level variable), I had:
    sleep(60)

We were also capturing SIGCHLD for notification of when the child closed (in this instance after just a few seconds).

The problem was that the daemon was not waiting 60s to poll unless there was nothing to do. Turns out that SIGCHLD cancels the running sleep(60).

My inelegant solution? sleep(1) 60 times and then only 1s of sleep is lost!
while ($sec>0) {
    sleep(1);
    $sec--;
} 

Thanks to Stuporglue, on whose PHP daemon example code I built a testbed to find a solution.

PHP SOAP Fault: Error fetching http headers - tweak default_socket_timeout

Started getting the above error on some servers that communicate via soap. I control both ends, so I was pretty sure it wasn't a code issue. After searching round and experimenting I discovered that the query that produced the results that the soap server was being asked to supply could, under heavy load conditions, take longer than 60s, and the connection was dropping at the 60s mark.

Turns out this is the default for the PHP ini variable default_socket_timeout.

I reset it to zero - bad move. 0 = timeout after zero seconds.
    ini_set("default_socket_timeout",0);
I reset it to 3600 = an hour and all seems OK.
    ini_set("default_socket_timeout",3600);

Note that the SOAP method is being called by a crontab-scheduled batch script, so waiting a l-o-n-g time for results is perfectly OK in this case. Might not be if there was a person on the other end! ;)

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

Virgin mobile UK settings for MMS on Pre3

Struggled with getting MMS on my Pre3. Virgin Mobile helpline unhelpful :(

Anyways, by distilling the info from several sites, I sussed it out. Here it is:

• Open phone app
• Preferences & Accounts
• Scroll down to Network
• Switch Manual Settings on
• Tap Edit Network Settings
• Tap MMS APN
• Set MMS APN to goto.virginmobile.uk
• Set USERNAME to completely blank
• Set PASSWORD to completely blank
• Under MMS PROPERTIES 
• Set MMSC to http://mms.virginmobile.co.uk:8002
• Set MMS PROXY to 193.30.166.2:8080
• Leave MAX SIZE at 300
• Tap Change Settings

And that should be it!

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.