Dropping unnamed default constraints

Ron Talmage article on Default constraints explains why we should always name our default constraints. In case you created a table with unnamed default constraint then SQL server automatically assigns one for you, scripting to drop that column would be a headache as you don’t have the default constraint name to drop. The following code finds the drops the constraint.

DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname =
(
SELECT name
FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'H2O_TestMap' AND so.xtype = 'D'
AND sc.colid = (SELECT colid FROM syscolumns WHERE id = object_id('dbo.H2O_TestMap') AND name = 'AntimicrobialFlag')
)
SET @cmd = 'ALTER TABLE dbo.H2O_TestMap DROP CONSTRAINT ' + @defname
EXEC(@cmd)

Advertisements
Posted in Development | Tagged | Leave a comment

WCF service Hosted on IIS in Windows 2003 needs write access to windows temp directory

I migrated my WCF services to Prod windows 2003 box and started getting the following error.

Error: Cannot import wsdl:portTypeDetail: An exception was thrown while running a WSDL import extension: System.ServiceModel.Description.DataContractSerializerMessageContractImporterError: Schema with target namespace 'http://www.odh.ohio.gov/ELR2/contract' could not be found

After initially being perplexed at what could have gone wrong and frantically searching for resolution on google found this blog post.

Resolution to the bug, provide write access on C:\Windows\Temp to the App-Pool user.

Posted in Development | Tagged | Leave a comment

SQL 2005 drop unique constraint

I just realized that we can not use drop constraint to drop a unique constraint on a table. Since unique constraint is implemented as index we need to drop the index.


IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'unq_MessageDiseaseHold')
BEGIN
DROP INDEX unq_MessageDiseaseHold ON dbo.H2O_MessageDiseaseHold
END
GO

Posted in Development | Tagged | Leave a comment

Creating attributes in xslt

The order of elements matter when creating attributes in xslt. For example say we have xml document

<?xml version=1.0 encoding=utf-16 ?>

<testoutputDocument>

<test isTest=true>odrs</test>

</testoutputDocument>

and we want to generate a output that looks like

<?xml version=1.0 encoding=UTF-16 ?>

<testoutputDocument>

<test isTest=true>odrs</test>

</testoutputDocument>

we can use the following xsl:

<?xml version=1.0 encoding=utf-8?>

<xsl:stylesheet version=1.0 xmlns:xsl=http://www.w3.org/1999/XSL/Transform>

<xsl:template match=/>

<testoutputDocument>

<xsl:apply-templates select=testDocument/>

</testoutputDocument>

</xsl:template>

<xsl:template match=testDocument>

<xsl:apply-templates select=test/>

</xsl:template>

<xsl:template match=test>

<test>

<xsl:attribute name=isTest>

<xsl:value-of select=./@isTest/>

</xsl:attribute>

<xsl:value-of select=./>

</test>

</xsl:template>

</xsl:stylesheet>

only thing to remember here is that the attributes should come before elements.

Posted in Development | Tagged | 1 Comment

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Posted in Uncategorized | 1 Comment