free geoip SQL Server - Jayson's Blog - jaysonKnight.com
jaysonKnight.com
Welcome to my corner of the internet

SQL Server - Jayson's Blog

Sort by: Most Recent | Most Viewed | Most Commented

  • Your Database Administrator Is Your Best Friend

    Let's face it...as developers we're expected to know a lot of stuff about a lot of things: Database design, application design, numerous languages and constructs, testing, debugging, etc.  I don't know about you guys, but my head can only hold so much information before an implosion is imminent.  Do yourself a favor and make buddies with a DBA near you (in my case, a guy 2 cubes over).  More than likely they've devoted their entire careers to RBDMS of numerous varieties, and chances are even better that they know a few things that you don't about databases and how to squeeze out some extra performance from that slow running query you can't seem to get running quick enough, etc.

    I've always kind of envied the hard core database guys.  It's not a job I'd want on a daily basis...too much stress for the big dogs, but most of the ones I've worked with always have a certain air of calm and tranquility about them.  The ERM server could be on fire right down the hall, and they're as calm as Hindu cows.  As they should be...a company's database(s) are their heart and soul, their bread and butter.  An application is worthless without a decently running DB behind it.

    I've made friends with one of ours, and already I'm learning tons that either A) I didn't know or B) had forgotten after a couple of years of being on my own.  Another thing I've found with these guys is that it either is or is not...there isn't much gray area when it comes to why things should be done a certain way.  That kind of finality is a refreshing change coming from an environment where there's a dozen ways to do one thing, with most of them being no more right or wrong than the next option.

  • XSD To Relational Database Model Converter -- XSD2DB

    A requirement in a small project I've been working on recently came up for mapping an XSD directly to an RDBMS schema on the fly (i.e. an incoming XML schema needs to be generated into an RDBMS schema structure at runtime...I won't get into specifics as to why), so I started searching for a way to to do this via the BCL.  The new SQL Server SMO libraries looked promising, however they are vast and patience quickly lost out to a some good ol' one on one Google time.  I ended up stumbling across this tool; even though the last release was 2 years ago so far it has worked quite nicely and it does appear to still be in (somewhat) active development.

  • Yet Another SQL Server Management Studio Gripe

    I simply cannot believe all the issues I’m finding in SQL Server Management Studio (for other gripes see my SQL Server 2005 category).  The newest one is just plain dumb IMO (unless I’m doing something wrong).  Recently I needed to implement a quick and dirty trigger for a test on one of my SQL Server 2005 databases, so I fired up SSMS expecting there to be a context menu option when you right click a table (ala Enterprise Manager) to create it.  Nope, not there.  Alright fine, do it via raw DML statements in a query window…easy enough.  Execute the statement to create the trigger.  Now where the heck is it in object explorer?  Programmability/Database Triggers?  Not listed.  Server Objects/Triggers?  No way Jose, not there either.  The only way to find it is select * from sysobjects where xtype = ‘TR’, and even then there’s no way to modify it without explicitly dropping it/recreating it via DDL.  Perhaps my installation is mucked up somehow, but triggers are simply nowhere to be found in object explorer.  This is getting ridiculous.
    Filed under: ,

  • More SQL Server 2005 Management Studio Woes

    I posted earlier about some SQL Server 2005 Management Studio (SSMS) gripes I've come across; I recently came across another really big one that I really feel the need to air out:  It's next to impossible to export an entire database to a new database on the same machine via the SSMS GUI.  Sure, the option is there (right click a db, tasks, export data), and the wizard is pretty similar to the one we had via SQL Server 2000's Enterprise Manager, except for one glaring "feature":  The only objects you can export from the existing database are tables/views, and their associated data. 

    Want your sprocs/functions/roles exported as well?  Ain't happenin'...at least not with this wizard.  Copy database wizard?  Only supported for moving databases between instances, no intra-server copying allowed.  Script the entire database to a new query window, create a new blank db, run the original db's create script, and then try to import the data via the wizard?  Nope.  The wizard fails immediately stating that the table objects already exist in the target database (shouldn't it generate DROP/CREATE statements?  Apparently not.); this is because the script database wizard allows for either CREATE or DROP statements exclusively (it's right there in the options).  Ok, how about backing up the original database, then attempting to restore the backup set to the target?  Uh uh, the wizard states that the target database is in use.  Take the target database offline to attempt the backup restore again?  Again I got an error saying the target db was unavailable.  Wash, rinse, repeat a few times for good measure, throw your hands up in frustration, and simply walk away before someone gets hurt?  Roger that.

    This is a pretty major defect in this humble developers opinion...if this kind of functionality existed in previous versions of SQL Server management tools, why strip it out in the new version?  This is a very common task, especially in the development realm where database versioning is crucial.  Of course this can be done (somewhat) easily via raw SQL statements (which I will show at the end of this post), and all the hardcore OSQL/SQLCMD folks will probably shake their head at anyone using the GUI at all, but the point is that this was in Enterprise Manager and was taken out of SSMS.  A huge no-no.  The mere fact that apparently this can't be done from the GUI is pretty bad.  I'd love to be proven wrong though.

    Anywho, after some digging around through the documentation (which takes more than a minute to open up on my machine by the way), I found what I was looking for which does exactly what I wanted:  An exact copy of the original database bit for bit.  Here's the code:

    BACKUP DATABASE [original_db]
   TO DISK = '[pathToBackupLocation]\[original_db].bak'

RESTORE FILELISTONLY
   FROM DISK = '[pathToBackupLocation]\[original_db].bak'

RESTORE DATABASE [target_db]
   FROM DISK = '[pathToBackupLocation]\[original_db].bak'
   WITH MOVE '[original_db]_Data' TO '[pathToSqlServerDataLocation]\[target_db]_data.mdf',
   MOVE '[original_db]_Log' TO '[pathToSqlServerDataLocation]\[target_db]_log.ldf'
GO

     

    SSMS will even automatically attach the database as well, so you're off to the races immediately after it's done.  It is very much worth mentioning I'm not adverse to this approach at all; my gripe is that the import/export wizard in SSMS is largely a piece of junk...a database is more than just tables and data.  So Microsoft, if you're reading this, here's yet another thing to put on your list for service pack 1.  Who knows what I'll find next.

  • Free Microsoft SQL Server 2005 Training

    Huge ups to Mark for sending me an email linking to a ton of free SQL Server 2005 online training from Microsoft (though unfortunately it looks like the “virtual labs” part of the training won’t be available until early February of next year).  I usually just go out and buy a ton of books when new technology comes out, but given the price of tech books these days, I’ll take anything that’s free…especially if it’s straight from the source (in this case, Microsoft).  Good find Mark!

  • Looking for Some Definitive SQLCMD.EXE Resources

    Does anyone know of a good hard core SQLCMD (not the ADO.Net object, the OSQL replacement that ships with SQL Server 2005) reference?  The SQL Books Online documentation for this new tool is terrible, and Google isn’t turning up much more than basic primers.  I’m looking for a hard core top to bottom detailed article (either online or print is fine) discussing the usage of it given my disdain for the new Management Studio (MS) I griped about earlier.

    What little I have done with SQLCMD thusfar has been great; it’s a nice replacement for OSQL (quite a bit easier to use), and the performance is leaps and bounds beyond MS as it uses the native .Net SqlClient OleDb provider instead of ODBC.  I’m just surprised at the lack of information out in the wild on this tool.

    Here’s a nice (very high level) comparison of SQLCMD vs OSQL.  I’m definitely sold on it, I just need information people!

    Filed under:

  • SQL 2005 Management Studio is Pathetic

    Forget everything I said earlier about SQL Server 2005 Management Studio (herein referred to as MS from now on); it’s slow, bloated, and just all around pathetic.  It seemed great at first, but after having done some heavy lifting with it lately it just seems…well, geared for idiots.  It’s just really been dumbed down to the point looking like Access (no offense to the Access folks).  Getting anything done takes an insane amount of clicks, and it just feels soooo slow compared to Enterprise Manager/Query Analyzer…very simple tasks take forever to finish up (for example, backing up a database…MS makes Enterprise Manager seem like a Ferrari).  I never thought I’d say this, but I actually miss QA at this point.

    The latest thing that made me go wtf was the following:  I needed to load up some 40 SQL statements for execution earlier today…normally I would just load them into a new Visual Studio database project, select them all and run them…works beautifully.  Unfortunately VS2003 can’t connect/communicate with SQL2005 (Microsoft, if you’re reading this (and I know you do sometimes), please back port this into VS2003, not all of us will be using VS2005 strictly on our SQL2005 databases), so I had to recreate my database project in VS2005 and trying to add all the SQL files to the project threw an error (the generic “parameter not supported” error if anyone is keeping score) which was a real bummer.

    So I selected all of the .sql files and opened them…instead of getting one MS instance with 40 tabs, wouldn’t you know that I got…you guessed it, 40 instances of MS, and each instance of course challenged me for authorization credentials so I couldn’t just right click the grouping on the task bar and close the entire group…I had to go through one by one and click connect (or cancel), then close the window.  Next attempt:  Open a single instance of MS and open the files from within the instance.  Un-bloody-f’ing real…they do indeed open up in individual tabs now, but I again get prompted for authorization creds for each file, and only 6 tabs are displayed below the menu bar.  It took a few seconds to find the very uintuitively placed icon that lets you see the rest of the windows, albeit in list form and not tabs.  Come on guys…after opening up tons of tabs in VS, you are given the very pleasant option of scrolling to the left/right to see hidden tabs and just all around easier to use.

    So then after clicking the connect button 40 more times I start the task of running each statement.  First tab: I select the database I need from the dropdown, run the statements, go to the next tab…and the database selection is reset to “Master”…I have dozens of databases so doing this on each tab simply isn’t feasible, so I go off searching for a setting that allows me to specify the database to use for my MS session.  Oh wait…there isn’t one.  At this point I’m ready to fire up osql (where you can specify command switches which are session wide) and write my own batch file that does this for me.  Thinking that I’m probably not the only person on the planet who needs to run an entire folder of .sql files, I Googled around and found this great little utility that does it for me, which does exactly what I want it do on the first try.

    While I’m griping, why doesn’t double clicking most objects (tables/sprocs/etc) in MS object explorer do anything…like maybe open the object and let me do something with it?  If I double click a table, it should either open in design view, or show me the data in it…doubling clicking a sproc should show me the sql statements, right?  No, you have to right click the object and choose from one of the menu commands now…no defaults.  And also gone is the “TOP” option when you open a table…if it has a gazillion rows in it, you’re gonna get all gazillion of them (well, you can click stop at anytime, but that’s still pretty pathetic)…if I’m in Enterprise Manager/Mgmt Studio, chances are good I’m just needing some quick and dirty data to look at, so give me the option.  It is very much worth mentioning that I usually do stuff like this from raw SQL statements in QA, but if I just need something quickly I’ll use EM/MS.  At first I really liked the integration of QA/EM into one tool, but now I’m absolutely craving separation again.  QA was quick, easy to use, and just all around fantastic compared to the query windows in MS.

    Ok, enough ranting…but feel free to chime in, and if you know of any other SQL2005 rant posts out there please let me know as I’d very much like to see what other people have to say about it.  Cheers.

Copyright © :: JaysonKnight.com
External Content © :: Respective Authors

Terms of Service/Privacy Policy