One of my all time favorite features of MSSQL 2005+ is being able to create table variables on the fly from SELECT statements. This isn’t a lesson in what table variables are, but here is an easy sample in case this is a new concept.
Running this query
SELECT * INTO #myTableVar FROM YourTable
Gives you a new table variable named myTableVar. Table variables are scoped to the active connection, so running this will work.
// make table var
SELECT * INTO #myTableVar FROM YourTable
// show me the data
SELECT * FROM #myTableVar
// you can drop it if you wish
DROP TABLE #myTableVar
However, let’s say you have an aspx page or a sproc that runs this query.
SELECT * INTO #myTableVar FROM YourTable
You can not access myTableVar in a separate connection to the database because as soon as the first query’s connection closes, myTableVar gets dropped. Here are a few other scenarios that also demonstrate the scoping of a table variable.
-- FAILS
EXEC ('SELECT * INTO #tmp FROM MyTable;');
-- #tmp does not exist
SELECT * FROM #tmp
Table variable #tmp lives inside of EXEC
Here we see that the table variable #tmp only lives for the life of the statement inside of EXEC. The second SELECT * calls is outside of the EXEC statement.
-- #tmp2 works inside of EXEC statement
EXEC ('SELECT * INTO #tmp2 FROM MyTable; SELECT * FROM #tmp2');
What happens in EXEC, stays in EXEC
Here #tmp2 works because it’s being used inside of the EXEC statement. This is worth knowing if you work with dynamic sql statements and exec.
-- works!
SELECT * INTO #tmp FROM MyTable;
-- #tmp exists
SELECT * FROM #tmp
typical sample of using mssql table variable
This is a typical example that you may use inside a sproc, trigger, script, etc. Both sql calls live in the same space, so #tmp exists.
Tags: exec, mssql, mssql2000, mssql2005, scope, SELECT INTO, table variable, temp table, tsql
Posted in database, development, random, tsql | No Comments »
Wanted to share this since it gave me so much trouble figuring out. It’s a simple SQL query ported to LINQ to SQL that joins two tables to return a filtered listed of data.
Here are the tables from my schema
Here is a basic SQL statement I could fire to retrieve my user videos.
select *
from video v, user_videos uv
where v.vid = uv.vid
and uv.uid = 2
Here is how you would run the same query using .net’s LINQ to SQL.
// create DB connection
var db = new DBCONN();
// run query
List<video> uvids = (
from c in db.video
join o in db.user_videos
on c.vid equals o.vid
where o.uid == 2
select c
).ToList();
This query differs slightly from the screenshot below because I used it in a WCF Service.
The variable DBCONN is my database connection that I established when mapping my DB. If you are not familiar with how to set this up, use the Visual Studio’s “Add the ADO.NET Entity Data Model” wizard. With your .net project open, right click your project, left click on “Add the ADO.NET Entity Data Model”. This wizard will walk you through setting up everything you need to setup your DB model file ( edmx ), as well as setting up your database connection and saving it in web.config.
Jesse Liberty did a simple tutorial that uses this wizard in a WCF service application.
I hope this helps somebody out.
Tags: asp.net, join, linq, linq to sql, linqtosql, query, sql
Posted in .net, C#, database, development, microsoft, sql, tips and tricks | No Comments »
Chat is @ Thursday Feb 11th, 11:30am MST
Jun Heider and myself will be talking to the 360|Flex guys tomorrow about our session. It’s actually going to be a back-to-back mega session comparing the latest and greatest on both the Flash Platform and the Silverlight Platform.
Come check it out and feel free to ask questions…although for the good stuff you’ll have to wait until our talks. 
Here’s the full details: http://www.360flex.com/blog/2010/02/360flex-speaker-chat-eric-fickes-and-jun-heider/
I hope to see you online tomorrow or at 360 Flex in March.
I'm speaking at 360 Flex 2010
Tags: 360flex, adobe, adobeconnect, chat, conference, flex, jun heider, speaker
Posted in .net, C#, adobe, coldfusion, database, development, flex, flexbuilder | No Comments »
EDIT :
After finishing this post I ran into all sorts of other strange issues and restarted using a Web Appliction instead of a plain old Website. Between IntelliSense not showing any classes, to project reference issues, I couldn’t figured it out in time. I’m sure there’s a way, I just had to move on. So maybe this ramble below will be helpful for someone.
EF
Just ran into something quirky with Visual Studio 2008’s new ADO.NET Entity Data Model wizard. While working on an ASP.NET 3.5 website ( not a codebehind web application ) I was trying to get the ADO.NET Entity Data model wizard to work with MySQL and ran into a probable Visual Studio bug. To sum up the issue, if you are going to add a new edmx to your project, do NOT save it to the App_Code folder initially. Put it in your root folder, compile your project, then move the edmx where you’d like.
Assuming you’ve already created your ASP.NET Website project, here’s how you reproduce this issue.
Right click your project and left click ‘Add New Item’
Add New Item
Select ADO.NET Entity Data Model, name it, select your language of preference
ADO.NET Entity Data Model
Click Yes to the ‘Store in App_Code’ prompt
Place file in 'App_Code' folder
Complete the new Entity Data Model wizard
See this tutorial if you have not done this before
Compile project after completing wizard
Invalid token 'void' in class, struct, or interface member declaration
At this point your project should have a new.edmx file located inside of the App_Code folder, but the project won’t build without failing. If you are stuck in this predicament, follow this workaround.
Move .edmx to root folder and rebuild
WORKAROUND : move edmx to root folder, then recompile
After moving your edmx file to the root folder you should be able to compile without problem. Assuming this solves your problem, you should be able to move your edmx file to the App_Code folder without problem. Seems like an initial compile problem.
Tags: ADO.NET, ADO.NET Entity Data Model, edmx, error, mysql, Visual Studio, visual studio 2008, void
Posted in .net, C#, Visual Studio, development, hack, microsoft, mysql, tips and tricks, windows | 2 Comments »
