Archive for March, 2012



Back in January of last year i wrote a post on how to write a query on how to write a MS SQL query that would output the ID of the row that you are deleting without having to write 2 seperate queries. Now this does not only work on delete, but it also works on updates and inserts.

Before SQL 2005 you would need to use @@IDENTITY,SCOPE_IDENTITY, or IDENT_CURRENT to select out your inserted ID. The problem that can plague some of these is when you have several queries on the same page that are performing this same operation, you can have adverse results.

From SQL 2005 to current you can now use OUTPUT on your statement to output a Deleted, Updated, or Inserted id without running a seperate query before the action to determine the ID. I have seen many developers fall prey to running 2 queries to determine the ID being changed and then changing it. The problem with this is that you force your application to prepare and create 2 seperate threads for a transaction that you can do with 1 statement. We all like to trim our code to be efficient, so SQL has helped with this by allowing you to OUTPUT your modified rows id in the same statement as the one doing the modifications.

So a few days ago i thought, well I know how to do that but what if I wanted to insert a row in 1 table and then use that id to update a row in another table. Not only how to do that but how do you do all that within 1 statement so as to keep my ColdFusion application from having to prepare and send 2 separate statements. so here is the example.


 <cfquery name="qinsertRec" datasource="#datasource#">
	DECLARE @retId TABLE(id int);

        INSERT INTO testtable1
		(source,
		 dest,
		 TransDate,
		 username,
		 backupKey,
		 filename,
		 [action])
	OUTPUT INSERTED.id INTO @retId
	VALUES
		(<cfqueryparam cfsqltype="cf_sql_varchar" value="#copyFrom#">,
		 <cfqueryparam cfsqltype="cf_sql_varchar" value="#copyto#">,
		 <cfqueryparam cfsqltype="cf_sql_date" value="#dateTime#">,
		 <cfqueryparam cfsqltype="cf_sql_integer" value="1234">,
		 <cfqueryparam cfsqltype="cf_sql_bigint" value="14">,
		 <cfqueryparam cfsqltype="cf_sql_varchar" value="test.txt">,
		 <cfqueryparam cfsqltype="cf_sql_varchar" value="Restore">);

	UPDATE testtable2
	SET    trans_id_rest = (SELECT id
	                        FROM @retId),
	       status = 'Restored'
	WHERE  id = <cfqueryparam cfsqltype="cf_sql_integer" value="14">;
 </cfquery>

DECLARE

First we declare a temp table to output our ID into.

INSERT

Then we run our insert statement, remember to put your output between your column definitions and your values. Now instead of just writing OUTPUT INSERTED.id, we are going to put OUTPUT INSERTED.id INTO @retId. This will insert the id into our temp table so that we can select it back out when we run our UPDATE statement. Of course always remember to use your cfqueryparam’s to prevent for injections of people that want to do malicious things to your site.

UPDATE

Now we can do our update to the other table and select out the id from the declared temp table.

Your Ideas


Hello all… I know there are not many out there that have read this blog. I hope to make this interesting and intriguing enough to keep you coming back here. I am not always good with ideas of what to talk about, so leave comments and drop a question or even a suggestion. I hope to be challenged on some of my ideas and also to help all those out there in search for an answer.

The internet is a wealth of information, however every so often I run across an issue that I cannot find an answer to or I find one that is close but not exact. I like to think there is no such thing as too much information. Plus I like to be concise. I am not good with words alot but the words I do have, I try to get to the point.

So let me know what you think. This site is for everyone out there, so I am looking to you for friendly criticism on what you want to see. Take care and God Bless.


Middle of the night on sunday, i get an alert on my phone telling me that the brand new EVA 6300 that i had purchased was experiencing a power supply failure. You can imagine that my heart sank. Shocked about this i logged into my command view VM that monitors my EVA and uses phone home to send it’s alerts to HP and myself.

To my surprise my EVA was telling me that all was clear.  All systems including the power supplies had passed diagnostics. Within a few minutes HP had responded to the error stating that this false positive is a well known issue with the 6300 and will be fixed in the next firmware release.

So for anyone out there experiencing this in the middle of the night… don’t fret it is all ok. If your command view is saying the power supply is ok, but you are receiving  messages stating otherwise, you are probably in the clear.


I was unable to find online information about this, so i thought i would offer up information about this problem in hopes that maybe it will help someone else. During a build update of VMWare along with HBA upgrade and some other hardware enhancements i began to receive random errors from VMWare on one of my hosts regarding an error with the CPU Scheduler. This error racked my brain because there was no loss of functionality to the environment, but i was unwilling to just ignore the random error as some had suggested. the error seemed to stop for a short period of time after a reboot, however nothing ultimately fixed it like a good reinstall does. So as you can imagine where i am going with this, i ended up re installing VMWare on the host, then reapplying the build upgrades and then baam! all has been good since.

So if there is any one out there that is suffering the same fate, fair warning you will need to re install VMWare on the host. This process is only about 20 minutes in time and is actually very easy. Just make sure ALL of your VM’s are off the host because you will want to do a fresh install not a install over the top. Installing over the top of an already corrupt install sometimes turns out worse then expected, so i recommend just start from scratch.

%d bloggers like this: