SQL Server Variable Declaration Gotcha

Part of my new job is to work more with Transact-SQL than I've ever done before, so I've been making stored procedures and batches of SQL commands all over the place. Today I ran into a nice little "gotcha" that had me stumped

 

SQL Server 2005, "5(Access Denied)" Error

I got the following error this morning trying to restore a SQL Server backup file:

Additional information: System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\[my database name].mdf'. (Microsoft.SqlServer.Express.Smo)

And a little bit of Googling brought me to a page which set me straight; it turns out SQL Server was trying to put the files into the wrong directory. You can read more about it here, in the original post where I found the info.

 

[Macromedia][SQLServer JDBC Driver]Object has been closed.

I got this error for the first time today, while trying to get a query working in ColdFusion 8.01. I'd defined some custom functions in SQL Server, and tested them out through the SQL Server Management Studio, but the datasource user didn't have the right permissions to use the queries. I added the permissions, reloaded the page and got smacked with the following error:

[Macromedia][SQLServer JDBC Driver]Object has been closed.

I found the solution on House of Fusion: restart your ColdFusion services. Something about the lack of permissions seems to catch in CF, and wont be released until you restart.

 

IsDate() and SQL Server

I don't know about you, but my company uses the IsDate() function for a lot of simple validation of date inputs, and it works fairly well for the most part. The only problem I have with it is that when paired with SQL Server (2000 or 2005), it still can't capture all invalid dates.

The date 'January 5, 753' is a valid date, but attempting to insert it into a SQL Server datetime object throws an error because valid dates for SQL Server are between January 1, 1753 and December 31, 9999. Dates above the year 9999 are caught by IsDate(), but the lower bound is not.


<cffunction name="IsSQLServerDate" returntype="boolean" output="false">
    <cfargument name="date" type="date" required="true"/>
    <cfargument name="type" type="string" required="false" default="datetime"/>

    <cfswitch expression="#arguments.type#">
        <cfcase value="datetime">
            <cfreturn IsDate(arguments.date) AND Year(arguments.date) gte 1753 />
        </cfcase>
        <cfcase value="smalldatetime">
            <cfreturn IsDate(arguments.date) AND DateCompare(arguments.date, '1/1/1900') gte 0 AND DateCompare(arguments.date, '6/2/2079') lte 0 />
        </cfcase>
    </cfswitch>
</cffunction>

Nothing that someone else couldn't write, but useful. to save headaches.

 

Recursive CTE in SQL Server 2005

Recursive Common Table Expressions are solving a lot of problems for me at work today, so I thought I'd throw a note up on here about them. Its a common problem with databases that records often have a parent record in the same table, which can in turn have another parent and so forth up the chain. What happens is that at some point you need to find out the whole structure of the tree, which becomes much more difficult. Enter RCTE. While I've known that other servers had capabilities like this for a while (Oracle for example), I'd not seen a good way to do it in SQL Server.

Find out a little more at the bottom of this page.

And an official Microsoft page can be found here.

 
Post a job. Find one. authenticjobs.com