x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

January 2005 Blog Posts

But MOM! Do I have to use sp_executesql

OK, it seems often asked how to return values from dynamic sql.  My initial reastion was to do something like the following.  Which works, is flexible, is highly dynamic, and does take a bit of setup USE NorthwindGOSET NOCOUNT ONCREATE TABLE myCounts99(mySPID int, myCount int)GODECLARE @sql varchar(8000)SELECT @sql = 'DELETE FROM myCounts99 WHERE mySPID = ' + CONVERT(varchar(4),@@SPID) + CHAR(13)+ 'INSERT INTO myCounts99(mySPID, myCount) SELECT ' + CONVERT(varchar(4),@@SPID) + ', COUNT(*) FROM Orders'SELECT @sqlEXEC(@sql)DECLARE @x intSELECT @x = myCount FROM myCounts99 WHERE mySPID = @@SPIDSELECT @xGOSET NOCOUNT OFFDROP TABLE myCounts99GO But as Nigel http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578 points out in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45193 it can be much...

posted @ Thursday, January 27, 2005 10:13 AM | Feedback (6) | Filed Under [ SQL Server ]

Search SQL Server for a table in any databse

I've seen recently a lot of people with quite a proliferation of databases on a single server.  I'm not sure why this is, but it may be a “personalized” database per client, so each client has their own replica of a database for an application.  If that's the case, then I would say those are bad designs.  If it's not, then I don't know.  It would just be a maint. nightmare.  In any event, someone finally asked: “Anyone might have an idea or know how to find a specific table from various databases? I have about 20 database” In the following Post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44682 EDIT:...

posted @ Thursday, January 13, 2005 12:03 PM | Feedback (5) | Filed Under [ SQL Server ]

Fair or Fowl?

I think I'll have the Chardonay with that. But I don't know what to think when Fabian Pascal makes a reference to SQLTeam http://www.tdan.com/sms_issue31.htm And doesn't reference the responses in that thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42060 What would have been fun is if Fabian picked up on the thread where the posted wanted to make sure you could lock out the administrator for a database.  THAT was a fun thread....I have to find that and post it here. But, is it me, or does it seem overly self serving of Fabian?  

posted @ Wednesday, January 12, 2005 1:33 PM | Feedback (20) | Filed Under [ Disconnected Ramblings ]

Build a Comma Delemited String For All Rows

Seems to be a popular question people seem to look for.  I Still don't know why.  Kinda hard to perform set processing against data put in this form.  I forget the first day I saw this, or from whom, but I always reference this article on the great SQLTeam Site. http://www.sqlteam.com/item.asp?ItemID=2368 So, once they got the ability to do it for 1 ID, they'd like the Whole thing denormalized...so I'll post mine here so I don't have to retpe (lord knows I could use the practice though).  It's been done over and over, and I'm sure the original came from someone here...

posted @ Wednesday, January 05, 2005 2:22 PM | Feedback (4) | Filed Under [ SQL Server ]

Datatypes: CHAR, VARCHAR and VARCHAR2

HUH?  VARCHAR2? You gotta love Oracle...ANSI be damned.  Well I never specifically knew why we needed to used varchar2 during the last several projects we were on.  Mostly because I was told to...don't think the person who directed the effort really knew either. CHAR is simple, it retains any data you put in there AND space pads the values.  A pain for comparisons later on. VARCHAR is not supported and is reserved for future use  VARCHAR2's definition is a little more complicated since it's different between version (Hey, eve M$ tries to backward compatible) Brian Peasland at http://searchoracle.techtarget.com puts it best Quote: Actually, Oracle Corp. has changed the...

posted @ Wednesday, January 05, 2005 11:18 AM | Feedback (7) | Filed Under [ Oracle ]

Powered by:
Powered By Subtext Powered By ASP.NET