InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 18:03:36 GMT20http://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxFortunately for me it was at Christmas and not too many people needed it. After a few days another programmer who read thru my comments and code and found my mistake, of course they did not let me forget for the longest. Tue, 05 Feb 2002 18:14:00 GMTAntares686http://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxThat was probably funny. Haven't done that before (knock on wood).Steve Jonessteve@dkranch.netTue, 05 Feb 2002 15:36:00 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxKeep in mind, never assume your first query for anything is optimal, and learn to understand the execution plan.Biggest OPPS, renamed a table on a major production site and forgot to update all procs, views and asp pages to match. Then left for vacation. Tue, 05 Feb 2002 14:59:00 GMTAntares686http://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxIt has always bugged me that the pop up menu on Enterprise Manager for the registered instance has the disconnect item next to the stop (stop MSSQLServer service) item.I am very careful, I tend to use the letter 'c' (underlined in disconnect) to complete the command.Some of our developers complain to me regularly about how SQL seems to stop spontaneously on their laptops. (Their local copy not the production server). Mon, 04 Feb 2002 22:46:00 GMTneiljacobsonhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxI agree on the upgrade the client tools. What are some of the impacts of using SQL200 client utilities to adminster SQL 7 servers. Especially in the DTS realm?? -JG Wed, 23 Jan 2002 09:15:00 GMTjGhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxnot in v7.Reason enough to upgrade your client tools.Steve Jonessteve@dkranch.netTue, 22 Jan 2002 13:22:00 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxYes I hightlight the line then perform the shortcut. Tue, 22 Jan 2002 12:02:00 GMTjGhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxNot sure. I'll get up from my desk and go check. Are you highlighting lines and then doing it?Steve Jonessteve@dkranch.netTue, 22 Jan 2002 10:43:00 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxSteve,Does the ctrl-shift-C work with 7.0 QA? I tried it but it did not comment the text. -JG Tue, 22 Jan 2002 09:31:00 GMTjGhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxActually I use the filesystem object to create a handle to a text file if I have a debug flag set. I store the debug flag, the filename and path in global varaibles. If the flag is set, then I create (and close ) the file. In many places i have a "if debugflag = 1 then write a line in the file".Steve Jonessteve@dkranch.netTue, 08 Jan 2002 16:01:00 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxCorrect, in my first job as a DBA we only had on SQL Server 7.0 and none of us had experience with it since I moved all our data from MS Access to SQL Server in the first month in my DBA position.When I first started in my current position we only had a development and production environment. Our testing environment was set up a few weeks later. As I look back now I don't see how we got by with just one database server.Robert MardaTue, 08 Jan 2002 15:39:00 GMTRobert W Mardahttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxI see from the first posted message that in the States you also have companies that have no development or quality environments. I thought that was a UK phenomenon. Certainly make life interesting when a question like "why have all our customers sold their vehicles in the last 15 minutes?" arise.My learning experience is still continuing. I get like an Octopus on Drugs. No mouse Shift-F6, CTL-B and then an accidental F5 (Query analyser) and Bob's you uncle, whole script is running again.Andy P Tue, 08 Jan 2002 15:16:00 GMTAndy Poolehttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxLearning experiences like this are not fun at the time they are happening and I certainly would never go out of my way to create them. However, problems like this (especially those that cause me to loose sleep) I never forget. I remember being at work with our DBA at 1 and 2 AM (both of us have very little experience and we had less a year ago when this occured) we were both in the server room trying to figure out how to get one of our production databases out of suspect mode (which as I recall I caused it to go into suspect mode). After we got it out of suspect mode (or it came out on its own I don't remember which) we restored the previous days version of the database and went home. Unfortunately, I don't remember how we got that database out of suspect mode. Does anyone have a quick way to get a database out of suspect mode?Robert MardaTue, 08 Jan 2002 12:18:00 GMTRobert W Mardahttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxHere is a recent oops for all to enjoy. My expense.. have fun.Upgrading a server to Windows 2000 and SQL Server 2000 and additionally expanding the drive sets for the data and log files.This was an old install prior to my arriving here (actually I am the first DBA on site and this install was done by non-database staff). So, the default databases were all D: and the newer databases were on E: and Logs on F: The only twist is that the TempDB was moved to E: and F: as space was constrained on D: and TempDB was growing. So, now that the stage is set.....Knowing that I had good backups, both SQL Dump to disk and BackUp Exec of Dumps and DB (hey, you never know), I decided to blow away the arrays once we did the OS upgrade and then upgrade SQL. Sounds good. OS upgrade flew. No problems. Go to start the SQL Server upgrade from 7.0 SP3 to SQL Server 2000 but, the service won't start. Hmmm. Realizing that I had blown away the TempDB, and also knowing that it will recreate itself when starting the service, I immediately suspected this was most likely the problem as all drives with any aspect of SQL Server were still intact. Verified I could write to the expanded and formatted drives E: and F: and all was o.k. Hmmm again. (Read the end to find out why it would not start.)Here goes the stupid part. After NOT thinking about this long enough, I figured that something MUST have been corrupted with the SQL install and decided that I would uninstall 7.0, install 2000 fresh and restore the 7.0 master and msdb to the newly installed 2000 installation. Much learned this night!!!After so happily uninstalling 7.0, installing 2000, verifying everything was working well I decided I would restore the 7.0 master database. Here comes the learning. Interesting to note that you CANNOT restore the 7.0 master database to a 2000 installation. No problem, I have a good script for re-creating accounts. I'll just restore the msdb and get my DTS packages and jobs back right?! Notice confidence was waning now. And of course, come to find out you can't restore an msdb 7.0 onto a 2000 installation either. Gee, good thing I script everything and save all my work. Well, here comes the kicker. The reason this all went wrong, is that I could not get the old 7.0 install to start and I assumed that it was something with the install being corrupted with the OS upgrade when in actuality it was because of the TempDB not getting recreated BECAUSE I NEGLECTED TO CREATE THE PATH (FOLDERS) TO WHERE THE FILES RESIDED. Much work to get everything back but, much learned also.DavidTue, 08 Jan 2002 12:04:00 GMTDavid Benoithttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxAre you talking about the log files that a DTS package will produce to tell you which task succeed and which fail. If so, I don't like them because the task names used are generic and I don't know of a way (I suspect there is no way) to change them in SQL Server 7.0. If not then what are these debug files? And how do you use msgboxes?Robert MardaTue, 08 Jan 2002 11:53:00 GMTRobert W Mardahttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxInteresting. I use msgboxes and debug files in DTS to write out execution to a text file.Steve Jonessteve@dkranch.netTue, 08 Jan 2002 11:33:00 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxI have used a similar technique in DTS packages. When testing a certain part of a DTS package and unable to monitor its progress I'll go to where I want the package to stop and put a line of code in a task that will fail to force the package to stop at just the right spot. This way I don't have to worry that it completes and imports data when the data is not ready or a transformation is not working right (and this has happened to me a time or two).Robert MardaTue, 08 Jan 2002 11:05:00 GMTRobert W Mardahttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxThere are some good tips here. One that has worked for me is to put a string of characters, I typically use ******, on the first line of the query analyzer window. If you accidently run the query without anything highlighted you will get an error instead of all the queries running. Tue, 08 Jan 2002 09:06:00 GMTmchapinhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxLonnie, try highlingting and using CTRL-Shift-C to comment a series of lines. Works great to prevent that accidental delete!Steve Jonessteve@dkranch.netMon, 07 Jan 2002 10:47:00 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxHere's a tip I find useful. Many times I will have several scripts in the same window of QA. To ensure that I don't accidently run a deletion that follows a select, etc., I precede all my delete and update actions with a comment -- tag. Hitting Alt-X accidently while in the script window could have disastrous affects :-) Mon, 07 Jan 2002 06:40:00 GMTLonnie Wimblehttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxaliased tables is a great idea. Never use that one. I'll give it a try. Thanks!!Steve Jonessteve@dkranch.netThu, 03 Jan 2002 11:34:00 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxOne technique I use to try to avoid deletes or updates when highlighting only a portion of a query is to use table aliases and always include a FROM clause (even though not required). This way if I only highlight the top line it will fail because SQL Server can't find the aliased table.For example:delete tblFROM tablename tbl WHERE UserID = 1I am going to have to look into using begin tran. I'm sure this will help me avoid some of the mistakes I make.Robert MardaThu, 03 Jan 2002 11:22:00 GMTRobert W Mardahttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxI extensively use the CTRL-SHIFT-C and CTRL-SHIFT-R extensively in QA to comment and uncomment a series of lines when making queries (as well as begin tran).Why?Too often I've typed the following:-------------select * from product where producttype = 2delete product where producttype = 2---------Then I highlight the first query and run it. check my results, then highlight the second query and run it.Of course, sometimes I highlight "delete product" without the where clause <img src=icon_smile_blackeye.gif border=0 align=middle>you can imagine what happens then....Since I may have a few updates/inserts/deletes, I decided to comment them out until I need them and I can just run the entire script without worrying about highlighting. Not the greatest, but it prevents some of my stupider mistakes from being repeated.Other goofs:-- Running and update on the wrong server (another reason to use begin tran) (quite a few times)-- not verifying the results of a query before telling someone I've done it (more often than I would like)-- ignoring a DBCC error (was busy). DB was down for a day while I worked with MS Support to fix it. (only once)-- making a change on production without testing in the QA environment (not too many times, but still to many)Steve Jonessteve@dkranch.netThu, 03 Jan 2002 11:04:00 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxOne of the things I can tack on to this is if I'm having to run queries manually to make changes to the data, I'll start a tran. After each step of the process, I'll verify that the data looks as it should. If it doesn't, I can roll everything back. Once I get to the end of the process, if everything is as it should be, I commit the tran. It should be noted that in cases like this I've been having to make changes to data in production (don't ask), and at the time the app is down (usually because the data is hosed). As a result, I'm not worried about blocking others from the resources... got to get things fixed that the app broke!K. Brian Kelleybk@warpdrivedesign.orghttp://www.sqlservercentral.com/columnists/bkelley/Thu, 03 Jan 2002 09:06:00 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxI forgot to post an invitation for others to add your own learning experiences here. Please add experiences you've had that taught you a valuable lesson about being a DBA (or SQL Programmer)Robert MardaSQL Programmerbigdough.comThu, 03 Jan 2002 08:49:00 GMTRobert W Mardahttp://www.sqlservercentral.com/Forums/Topic2074-61-1.aspxI am Robert Marda and am hoping to spark some constructive conversation here. This is the first of a few topic ideas I have.I have been working with SQL Server and SQL since March 1999. I was given my first DBA position in May 1999.Within my first month as a DBA I successfullydeleted all rows in our main table - Oops!(containing the location and serial numbers of all computer components used to produce ID cards at US military bases). The deletion was not intentional but happened due to my lack of SQL knowledge and the fact that we had no development nor testing environment (a very bad combination). Thankfully I had run a full backup recently and was able to restore from that.Because of this experience, I usually develop all queries as a SELECT to ensure I see only what I want to change before converting the SELECT to a DELETE or UPDATE statement. One of my philosophies is "What you see is what will be changed."Robert MardaSQL Programmerbigdough.comThu, 03 Jan 2002 08:42:00 GMTRobert W Marda