How to check for active transactions
Debugging an issue today at work, and needed to find if there was any uncommitted transactions or active transactions.
There’s a couple of ways to do this:
1
Thanks Pinal Dave – more from his blog post here.
Also you can use dbcc opentran Or SELECT @@TRANSCOUNT
So, if you’re like me and what to see this in action… Continue reading
How to find the name of a sql server
Fast way to get a sql server name, great if you’re dealing with multiple sql servers.
1
This will return the Windows computer name on which the sql server instance is currently running on.
It will return NULL if there is an error.
1
This will return the instance & computer name
[code… Continue reading
how to select columns from a stored proc
Okay so recently I’ve had to select only certain columns from a large stored proc that may return like 4 columns. How to do this?
DECLARE @tableVar table (Surname varchar (100), FirstName varchar(255), Email varchar(100), Age int)
INSERT INTO @tableVar EXEC dbo.usp_getSomeData
SELECT Surname, Email FROM @tableVar… Continue reading
Passing a nullable/blank field to a WHERE statement
I’ve used this piece of code in many places lately to check if a field is nullable or blank – used mainly as a default value.
AND
(
@ResultType = ” OR
@ResultType ” AND ResultType IN(@ResultType)
)… Continue reading
grant execute for a stored proc to application role
simple script incase you forget:
grant exec on to… Continue reading
Using DELETE FROM with INNER JOIN
Ever needed to delete a table? Oh wait it’s joined to another. Well here’s some how you do it… the right way!
For MSSQL
DELETE Users
FROM Users u
INNER JOIN
Profiles p on u.UserID = p.UserID
WHERE u.UserIsArchived = 1
For MySQL
DELETE u, p
FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID
WHERE u.UserIsArchived = 1… Continue reading
Example script for create table in h2
Just figured I’d add one of these scripts incase I forget! Haha… but just if you’re curious, it not different to MySQL.
DROP TABLE IF EXISTS Players;
CREATE TABLE Players
(
PlayerID int identity,
FirstName varchar(255),
LastName varchar(255)
);
INSERT INTO Players
(FirstName, LastName)
VALUES
(‘Joey’, ‘Daly’);
Fusegrid SDK
Been using Fusegrid SDK, it’s fantastic for people who just want to start coding Coldfusion with zero time wasted on setup.
Normally you’ll need to setup a web server, then download ColdFusion and then work out what database you want… painful!
Fusegrid comes bundled with Railo + H2 Database, and CFWheels 
Get it here: http://www.fusegrid.com/sdk/… Continue reading
Concat Rows into a string
I had this problem a while ago where I needed to do a really big UPDATE sql statement using the IN clause. So rather than coping all the ids out and then adding a comma at the end of it you can concat the id rows into a string – thanks A-ton!… Continue reading
Downloading an image from database
So assuming you have a table called Assets, and all the right fields. There’s two options, using cfhttp or cfimage (cf8+).
Email Subscription
Latest Tweets
@GameSpy Barb, it was my very first character in Diablo 2 and I can't wait to play it again - posted on 15/05/2012 07:32:57- #diablo3 - posted on 15/05/2012 05:24:55
- Well done reds!! Liverpool v Chelsea 4-1 full time RT @empireofthekop: All the goals here http://t.co/UQt2VA90 - posted on 09/05/2012 07:06:58
- hump day, you could not have come sooner #win - posted on 09/05/2012 06:40:19
- @DHughesy @nova100 196 retweets from hughesy last night, great work! http://t.co/zYAeXL8N - posted on 09/05/2012 06:16:34
Recent Comments
- Omar on iOS5 create custom ringtone for iPhone
- amr on iOS5 create custom ringtone for iPhone
- Dan on DNF Photography
- Edu on I/O Error: Cannot run program “javac”: CreateProcess error=2
- MaryD on iOS5 create custom ringtone for iPhone
Tags
Advertising
Archives
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- January 2011
- December 2010
- November 2010
- October 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- February 2010
- January 2010
- September 2009
- July 2009
- May 2009