Configurable connection string with Linq to SQL

When using Linq-to-SQL with the dbml designer, by default it generates an App.config file and puts the connection string to use in it. This is fine (well, not really, but at least it works) for applications, but when working with class libraries it's a problem – DLL's don't load the app.config, so changing the connection string in the app.config won't produce any change (the application will still try to use the connection string that was used at design time).

I found a pretty neat solution here by David Klein ( http://ddkonline.blogspot.com/2008/02/set-connection-string-in-linq-dbml-file.html ), which is based on Jon Gallant's solution ( http://blogs.msdn.com/jongallant/archive/2007/11/25/linq-and-web-application-connection-strings.aspx )

P.S: The problem is known by MSFT: http://msdn.microsoft.com/en-us/library/bb386996.aspx . It doesn't seems to bother them though…

Can't this be simpler?

I'm trying to run a Linq query which, in SQL, would look like this:

select V.Date, SUM(F.Factor/V.Change) AS Denom
from AllVols V JOIN Factors F on F.Key = V.FId
group by V.Date

The only way I found looks like this:

var denoms = from v in allVols
             join f in factors on v.FId equals f.Key
             group new {v.Date, Factor = f.Value, v.Change} by v.Date
             into g
                 orderby g.Key.Date
                 select new {g.Key.Date, Denom = g.Sum(d => d.Factor/d.Change)};

Is there no better way ?!?!

SQL Server 2005 rantings - User Defined Aggregate Functions are nice, but not there yet...

1. Why can't there be UDA's in T-SQL? Granted, it's easy to write it in CLR, but sometimes it would be simpler (and more appropriate) to write it in SQL. It also took me a while to figure out that indeed there is such limitation...

2. UDA's must be serializable. Why? I don't know yet (still need to figure that one out), although I have some ideas, but anyway it's besides the point - it's a must and I assume there are good reasons for that. The problem is that whenever you're doing something slightly more complicated than just an average or Product, you need to accumulate all the values until you get to Terminate() (e.g. a variation on STDEV). This means that this list you've just accumulated could grow significantly. Now to the pitfall - when you use user-defined serialization (which you would have to in this case), you must specific the maximum size that the UDA structure could grow to. This maximum size is limited to 8000 bytes (*sounds familiar...). So in my case, I'm using a UDA over double values, and thus I'm limited to aggregating a little below 1000 records. IMHO this reduces the practical usage of UDA's to about 50%...

3. I tried to write a UDA for decimal data. No matter what I did, it constantly produced a function defined to return decimal(18,0). In other words - no decimal numbers to the right of the dot. In the end I didn't have the time to find out the KB article talking about it, but I suppose there is - I pretty much tried everything. In my particular case using double values was an acceptable compromise - it won't always be that way...

Parameterized SELECT TOP

Just a small tip:

Say you want to run a query that returns the first @X results of some query, such that @X is a parameter.

The simple "SELECT TOP @X ..." statement doesn't work. I found this weird, because I know for a fact that SQL 2005 supports using a parameter in the TOP clause (as opposed to SQL 2000 where you had to use either dynamic queries or the SET ROWCOUNT statement).

Solution - surround the parameter with parenthesis as so:

"SELECT TOP (@X) ..."

Something to remember...

Grrrr... I hate bad documentation! or: "An INSERT EXEC statement cannot be nested"

I recently had to extensively use a feature in SQL I seldom used before - inserting the results of an EXEC statement directly into a table. Sounds a reasonable thing to do, right? Accidentally, I had a bunch of stored procedures calling each other, using temporary tables, and finally filling a "final" table. I finished writing the whole thing, and then - BABOOM! - it doesn't work, because "An INSERT EXEC statement cannot be nested". Huh? What?
Why? Why didn't you say so before?

Following is a quote from SQL Server Books Online for "INSERT (Transact-SQL)" :

execute_statement

Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements. The SELECT statement cannot contain a CTE.

If execute_statement is used with INSERT, each result set must be compatible with the columns in the table or in column_list.

execute_statement can be used to execute stored procedures on the same server or a remote server. The procedure in the remote server is executed, and the result sets are returned to the local server and loaded into the table in the local server.

If execute_statement returns data with the READTEXT statement, each READTEXT statement can return a maximum of 1 MB (1024 KB) of data. execute_statement can also be used with extended procedures. execute_statement inserts the data returned by the main thread of the extended procedure; however, output from threads other than the main thread are not inserted.

Did you see any clear mention of this limitation???

SQL Table Variables - Limitations

I've come to really like table variables in SQL. They are a nice and natural development of database programming. And, when used smartly, can greatly improve performance compared to using temporary tables.

Today I've discovered that table variables cannot be truncated. I had no idea...

Anyway, I've found a cool source of more information about table variables, including some stuff that is not or not well documented in Books Online. Check here and here.

When you think of it - it makes sense. As far as I know, table variables are memory constructs whereas temporary tables are kept in tempdb. Truncating a table simply removes the reference of the table's page from the database. However, if the table is only kept in memory and in no actual database - it has no meaning...

WITH keyword in SQL

A feature I didn't know in SQL 2005: WITH can be used to create ad-hoc table-like entities within a query (they call it CTE for Common Table Expression). Check it out here and here.

Joke By Code

I haven't blogged much lately, I know, sorry. Between work, kids, buying a house (!!!!!) and a million other things, I just couldn't find a minute.

Anyway, today I was searching for some good books about SQL Server 2005 programming. I have a lot of experience with SQL 2000, but it's about time I get to know the little baby (who's way past the 'baby' stage and would better be called a teenager) a little more.

So I came accross Murach's SQL Server 2005 for Developers and like everyone, I started skimming through the 6 customer reviews. All reviews were really good, except the last one, which seems to have been written by particularly dissapointed customer. So I decided to have a look at the 3 comments to his review. Here is what I found (quote):

SELECT OneHalfBrain
FROM Name
WHERE LName = `Husain'

results: NULL

It made me laugh (the reviewer is called Munawer Husain, so I assume it was meant personally against him, and not in some stupid racist direction).