Ryan Garaygay on Code, Creativity, and Community

Tag: sql

  • MCTS SQL Server 2008 beta exam 71-432 taken

    I just got off from taking exam 71-432: TS: Microsoft SQL Server 2008, Implementation and Maintenance – which will become 70-432 when it goes live and is the successor of 70-431 (for SQL 2005)and here are just a few thoughts [more] This is sort of "SQL Server 2008" foundation exam and required to move on…

  • Get Ancestor and Descendant IDs or info using SQL 2005 Common Table Expressions

    Need some recursive processing in TSQL (SQL Server 2005)? Needed some TSQL code again to retrieve descendant and ancestor entries in a self-referencing table. I've done this a couple of times already and although I can write on top of my mind, sometimes you just want to make life easier and have a script you…

  • Link: SQL Server 2008: new data types and .Net 2 with and without SP1

    Here's an interesting I came across in the recent Simple Talk Newsletter (by Red Gate Software). It talks about issues with new SQL 2008 data types (date/time related) and it's effect on .NET 2.0 datetime data types when SP1 is installed or not. Something to be aware of and one of those times that you're…

  • Link: Testing SQL Stored Procedures Using LINQ

    Just ran into this interesting article on MSDN Magazine about using LINQ to test stored procedures. It would be a bad idea to test stored procedures using other stored procedures too (eg. insert SP then verify presence of inserted value using select/load SP) so traditionally we do it using ADO.NET object (conn, commands) to build…

  • sp_executesql error with DDL statements (preventing SQL injection)

    One of the requirement for an application I'm currently working on is for the end user of a web application/site to be able to create objects in the database. There are a number of objects that must be created or manipulated but for the sake of simplicity let's take for example creating a table with…

  • TSQL SubQuery requires a Table Alias

    Subqueries are essentially a query (or queries) inside another query. That is for example a SELECT statement inside another SELECT statement. There is already enough information available in the internet and written materials about subqueries including their common uses, when and how to use them (see bottom of post) and I will not elaborate on…

  • TSQL : Concatenate values from a datatable column into a delimited string

    I think it's common to come across a requirement where you have to retrieve values from a column in a database table. For this example however, we would use a table variable instead.   DECLARE @EmailTable TABLE (   Email VARCHAR(64) )   — then we insert sample values  INSERT INTO @EmailTable VALUES ('test@ryangaraygay.com') INSERT…

  • Cannot create Scheduled Job on SQL 2005

    Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo) I ran into this error for the first time a few hours ago [more]and I have no idea what's causing it since I've been creating SQL Jobs for some time except for the past few days. Maybe some patch or update or an installation…

  • Unit Testing Data Access with .NET TransactionScope

    I was curious how to maintain my database at a consistent state while unit testing (technically integration testing already – but since it uses unit testing framework as they say the lines are terminologies are getting blurry) so I looked around for "database unit testing" write ups. It turns out that [more]mbUnit has [Rollback] which…

  • Generate SQL Job Schedule Description

    I was actually calling the system stored procedure "msdb.dbo.sp_help_jobschedule" in my previous post about modifying column list from a called stored procedure, While working on that I noticed that [more]the description (schedule_description) being returned by stored procedure (if you set @include_description = 1) returns date and times as integer values (rather than date/time format). While…