Old school html and ASP.NET are not friends

I have not built a webpage in quite a few months but I wanted to sketch out a process flow so I could make sure the database components were positioned to support it.  Easy enough, built the screens but then I thought it'd be nifty to actually make a quick demo video showing how they would look to the end user.  I have Windows 7 with me and thanks to Twitter support, I fired up the Problem Step Recorder (psr.exe).  Edited my html and set the action to be the next page in the sequence.  Page 1 looks good, click the button to go to the next page and 

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that <machineKey> configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.

So what's the story?  No web farm or cluster here, just the simple visual studio IIS-lite process running.  I started trying things like setting EnableViewState to false everywhere but to no avail.  

My ultimate problem was I was mixing pure HTML and ASP.NET.  My form looked like

<form id="form1" runat="server" action="publishcomplete.aspx"><asp:Button ID="ButtonPublish" runat="server" Text="Publish data" /></form>

Google failed me and I was too lazy to look at previous solutions to see how I'd done it before.  Emasculated, I asked for help and my coworker, being the awesome one he is, gave me the appropriate level of grief along with the solution.  The action is old school and doesn't work mixed with ASP.NET controls.  Instead, move the action to the appropriate button and set the PostBackUrl property.

<form id="form1" runat="server"><asp:Button ID="ButtonPublish" runat="server" Text="Publish data" PostBackUrl="~/PublishComplete.aspx"/></form>

Problem solved and now on to better things

 

Execute SQL Task, ADO.NET provider, parameters and result set - a how not to

Talk about a needle in a haystack. I began working on my own version of Andy Leonard's SSIS Instrumentation framework and in particular, I was trying to load some values into a recordset using a simple execute sql task with an ADO.NET connection type and some basic parameters. It shouldn't have been this hard and I should have looked at documentation sooner. Let this blog post serve as the quick reference guide to all the stupid mistakes you can make and how to resolve them.

The initial failure message was
[Execute SQL Task] Error: Executing the query "SELECT..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Failed to convert parameter value from a DateTime to a Int32.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


Seems easy enough, I was trying to punch System::StartTime in as a parameter. I stripped my parameterized query down to just integer types (a variety of row counts)
[Execute SQL Task] Error: Executing the query "SELECT..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Incorrect syntax near ','.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Well that was silly, I had an invalid query "SELECT , @param1" isn't valid SQL. I corrected the syntax of the query and ran it with great success. Next step, start adding back the variables in the System namespace. First up, TaskName. Care to guess what happens when you forget to map a value to a parameter placeholder? If you said
[Execute SQL Task] Error: Executing the query "SELECT..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Must declare the scalar variable "@TaskName".'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

you'd be absosmurfly correct. By the way, the converse is not an issue. The ADO.NET provider has no qualms about being passed parameters it doesn't use or have a placeholder for.

What was the root cause of my initial problem? Don't forget to set the value in the "Data Type" column. That did explain why SSIS thought it needed to convert a datetime to an int32.

You can't get ye flask (or performance counters)

I'm finally getting my feet wet with SQL Server 2008 and working
through some SSIS stuff. 2008 is running on my Windows 7 VM so new on
the new. Ran my first package and "[SSIS.Pipeline] Warning: Warning:
Could not open global shared memory to communicate with performance
DLL; data flow performance counters are not available. To resolve,
run this package as an administrator, or on the system's console."

I assume that's a Windows 7 thing so even though I'm an admin, I have
to explicitly right click visual studio and say run as admin to get
that to go away. It'd be nice if I could just sudo my way into admin
rights within the current context instead of having to shut Visual
Studio down and relaunching it.

References:
http://msdn.microsoft.com/en-us/library/ms345164.aspx
http://www.homestarrunner.com/sbemail94.html

CVS, as seen on TV!!!

I was attempting to resolve a CVS issue and none of the examples I found on the web would work.  Thinking perhaps the command-line version of cvs had a defect, I installed the latest and greatest version of cvsnt.  I should have known something was amiss with it when the download page kept pushing me to try the Suite.  No, I really just want the client.

Fast-forward to this week.  I'm reviewing some code I had committed since the upgrade and there's my commit message and some advertising stored there in my fracking commit!

5153:  Changes to meet requirements F2A.1 (enable sorting based on activation date and highlighting based on participation in program)
Committed on the Free edition of March Hare Software CVSNT Server.
Upgrade to CVS Suite for more features and support:
http://i-am-not-giving-you-free-advertising.com/

This moves a product from free-ware to nag-ware in my mind.  Looks like I'll be rolling back to the cvsnt-2.5.03.2382.msi which just committed code.

</rant>

Instead Of

I learned something nifty today about triggers. @dyfhid had a scenario where they wanted to make a unique constraint for only a subset of the data. Not running SQL Server 2008 on the VM I already had spun up, I didn't get to play with the nifty filtered index solutions others proposed. Below is my quick and dirty solution to allow the text to be as duplicated as they want but to keep the serialnum column unique for numeric values.

create table dbo.production3
(
    production3_id int identity(1,1) NOT NULL PRIMARY KEY
,   serialnum varchar(50) NOT NULL
)

GO

INSERT INTO
    dbo.production3
SELECT
    'Hi mom Pack with NO SN' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    '01234567890' AS serialnum
UNION ALL
SELECT
    '12345678901' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum

GO
-- Return 1 if the item is unique enough
-- return 0 if it's not unique enough (numbers only)
CREATE FUNCTION dbo.MostlyUnique
(
    @serialNum varchar(50)
)
RETURNS bit
BEGIN
    DECLARE @retval bit
    SET @retval = 1
    -- We only care if it looks like a number
    if isnumeric(@serialNum) = 1 AND EXISTS (select serialnum from production3 where SerialNum = @serialnum)
    BEGIN
        SET @retval = 0
    END

    RETURN @retval
END

GO


CREATE TRIGGER DBO.MostlyUniqueTrigger
ON dbo.production3
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO
        dbo.production3
    SELECT
        I.serialnum
    FROM
        INSERTED I
    WHERE
        -- this probably sucks for performance
        -- but it keeps the dupes out
        dbo.MostlyUnique(I.serialnum) = 1
END

GO


-- readd the original set, should only see the NO SN stuff

INSERT INTO
    dbo.production3
SELECT
    'Hi mom Pack with NO SN' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    '01234567890' AS serialnum
UNION ALL
SELECT
    '12345678901' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    '23456789012' AS serialnum


SELECT * FROM production3


--production3_id  serialnum
--1   Hi mom Pack with NO SN
--2   Cactus Pack with NO SN
--3   Cactus Pack with NO SN
--4   01234567890
--5   12345678901
--6   Cactus Pack with NO SN
--7   Hi mom Pack with NO SN
--8   Cactus Pack with NO SN
--9   Cactus Pack with NO SN
--10  Cactus Pack with NO SN
--11  23456789012

Alternate solution (there are many):
http://markvsql.com/index.php/2009/05/enforcing-uniqueness-on-a-nullable-column/

References:
http://msdn.microsoft.com/en-us/library/ms189799.aspx

CVS hell: I accidentally the binary mode

I am old.  I grew up with a command line and I'm comfortable there.  Command line args to programs are not foreign to me.  Today however I was desperate for a  pretty GUI tool to fix a problem with CVS.  CVS is our version control system here at work.  It works, we have change history and can tag our releases.  All is well and good.  

A contractor created a stored procedure in Unicode format and committed it.  CVS happily accepted it, as binary.  CVS doesn't handle binary diffs nor does it expand macros which of course makes sense for binary data.  The trouble I ran into was how the hell do I get it back to Text/ASCII mode.  It was a simple matter of resaving the file under ANSI/Windows 1252 code page.  With a text file in hand, the logical answer would be "cvs -kt MyFile.sql"

Logic does not factor into this!  "cvs update: invalid RCS keyword expansion mode"  

I thought perhaps my client version was causing issues "Concurrent Versions System (CVSNT) 2.5.03 (Scorpio) Build 2382 (client/server)" so I upgraded to the latest and greatest "Concurrent Versions System (CVSNT) 2.5.04 (Zen) Build 3510 () (client/server)" but that didn't resolve the issue.  It looks like our server version is mostly up to date "Concurrent Versions System (CVS) 1.12.12 (client/server)"  It's a point release behind and I see nothing in the patch notes to indicate there was a problem or fix relative to binaries.

After much futzing around via blogs, newsgroups and general google searches, I went with the old school solution "nuke it from orbit!  It's the only way to be sure"  Which amounted to ssh to our cvs server, remove the MyFile.sql,v and re-add it.  I'm all ears if someone finds a solution but for me, I'm done worrying about it.

References

http://www.cvsnt.org/manual/html/Substitution-modes.html

http://www.cvsnt.org/pipermail/cvsnt/2004-November/016036.html

http://www.cvsnt.org/pipermail/cvsnt/2006-August/026511.html