 |
|
8000XXXX Errors ASP.NET 2.0 Classic ASP 1.0
Databases Access DB & ADO General SQL Server & Access Articles MySQL Other Articles Schema Tutorials Sql Server 2000 Sql Server 2005 General Concepts Search Engine Optimization (SEO)Search
ASP FAQ Tutorials :: Databases :: Other Articles :: Which tool should I use: Enterprise Manager or Query Analyzer?
Which tool should I use: Enterprise Manager or Query Analyzer?
The short answer: both.
Anything development-related, you'll want to do in Query Analyzer (QA). However, many management-related tasks are better handled by Enterprise Manager (EM). Let's look at the pros and cons of each tool from the standpoint of various administrative/programming tasks.
Ad Hoc Queries
Query Analyzer is the easiest place to run ad hoc queries against the database. You can just open a connection and type something like:
Press F5, and you have your results.
In Enterprise Manager, you have to open the tables list, right click tablename, select Open Table > Return All Rows, then click on the SQL button to switch to 'SQL View,' change the query that EM created, and then press Ctrl+R (Run). This is not exactly convenient. Especially when, with larger resultsets, 'return all rows' and even 'return top' views can annoy you with popup dialogs about clearing the results pane:
QA has no time limit on how long you can inspect results, and you cannot adjust the timeout in Enterprise Manager.
The SQL view also only supports one statement, and behaves funny when you try more than one.
The view will re-format the query, and ignore everything after the keyword 'ASC'...
If you have a long-running query, you can't adjust the timeout in either Query Designer or View Designer.
Columns with datatype TEXT almost always display as <LONG TEXT>. Columns with datatype SQL_VARIANT always display as <Binary>. QA can display up to 8,192 characters in each tuple of a result, and properly shows SQL_VARIANT data as character data.
If you enter a time only in Enterprise Manager, the date you will get is 1899-12-31. Enter the same type of data in an INSERT or UPDATE statement in Query Analyzer, and you will get 1900-01-01.
Simply put, the 'Return All Rows' view in Enterprise Manager is not a good place to modify data... from the unconventional use of CTRL+0 required to enter a NULL, to the limit of 1,024 characters, to the frequent errors about 'insufficient key column information' or 'transaction cannot start while in firehose mode' or 'data has changed', I would much rather run my INSERT / UPDATE statements in QA — or, better yet, in an application designed to intercept conflicts and ask you how you want to deal with them.
To run an explicit UPDATE statement or change the query slightly, you need to toggle into SQL View. This view does not support PRINT, SET and other commands you may be used to using in QA.
You also need to toggle into SQL View to change the order of the data you have returned into this view. Even though there are very Excel-esque "Sort Ascending" and "Sort Descending" buttons on the context-sensitive toolbar (I've just never seen them actually enabled):
In comparison, there are many usability features in Query Analyzer's Object Browser (F8). You can drag object names into your script, and you can even pull comma-separated lists of column names (for tables) or parameter names (for procedures and functions) by dragging the "Columns" and "Parameters" nodes respectively onto the query window. This can save you plenty of time typing out column names manually, and will help avoid typographical errors with more complex object names. When you're in Enterprise Manager's SQL view, you're on your own.
Another side-effect to certain parts of EM's GUI, such as the table designer and open table > return all rows, is that they use an ODBC link on top of the OLE-DB provider. This has two effects: (1) an extra later of indirection, making data access slightly slower; and (2) it makes it very difficult to deal with columns with datatypes like sql_variant, since ADO can't deal with those.
View Designer
If you right-click the Views node in Enterprise Manager, one option is "New View" which opens a graphical tool called the View Designer. This is what the interface looks like:
This might be a tempting way to create views, however there are several problems with it.
Many people complain that the view designer puts useless names like "Expr1"..."ExprN" in column aliases.
Also, if you open an existing view that has comments, it may combine and reformat them. Create this view in Query Analyzer, then right-click it in Enterprise Manager, choose Design View, and click Save:
(Many people complain that the comments disappear completely from the View Designer, so it's not immediately clear if you will lose them when you save.)
Now go to Query Analyzer, open the Object Browser, right-click the view and choose Edit. Gross. You can also do some experimenting with adding NEW comments in the view designer, and hitting save without running them. Also play around with how it treats -- and /**/ comments slightly differently...
KB #317009 says that if you have a database with period (".") in the name, column names will not be available... the only thing you will be able to choose is "All Columns." This is because Enterprise Manager knows the table exists, but can't resolve it properly because it doesn't know enough to add [] brackets around each name in the node, so the . is interpreted incorrectly. Don't use weird characters in object names!
Trying to save a view that references a linked server results in various errors. If you are using Oracle and the MSDAORA provider:
If you are using a SQL Server linked server:
(The reason for this is because, behind the scenes, Enterprise Manager wraps the CREATE VIEW statement in a transaction, and there are many limitations with using transactions against remote servers.)
If you use the CASE statement, you will get an annoying error when you try to run or save the view:
The View Designer only supports a limited subset of the T-SQL language; CASE is one of the expressions that comes up most often.
If you have complex queries, you might see one of these errors:
The View Designer uses ODBC, so it may have problems with complex queries, such as unions with sub-queries and/or multiple types of joins.
All of these things are additional reasons I tend to run all of my T-SQL code in Query Analyzer. If you're going to be creating views, I strongly suggest you familiarize yourself with the CREATE VIEW and ALTER VIEW DDL statements.
Other errors
You might see this error when trying to open or design a table:
This could be a basic network glitch, if you are connecting to SQL Server remotely. Also see KB #275025, which describes similar symptoms when the database is in 6.0 compatibility mode.
If you are trying to get rid of those annoying dt_ stored procedures from Visual Studio and/or the Diagrams node in Enterprise Manager, it won't let you because they are considered "system objects." See Article #2534 for more information and a workaround.
Another error you might receive when using Enterprise Manager:
Enterprise Manager is confused, deep down. This usually requires a reinstallation of the client tools; however, you can try this first:
KB #268600
(BUG: When You Open Table with a Large Number of Columns SEM Appears to Hang or Reports Unknown Error)
KB #273472
(FIX: Enterprise Manager Returns Unexpected Error on Table or View)
Editing Database Objects
QA is definitely better suited for modifying table structure, stored procedure code, views, triggers, functions, indexes, etc. Everything is presented to you in Data Definition Language (DDL). You press F8, get your list of objects from the Object Browser, choose one, right-click, and you can edit a stored procedure or script a table. You can also drag the names of other objects from the object list into the current window, which cuts down quite a bit on my typos, at least.
In EM, the actual details of the object are hidden from you by pretty GUIs, which is sometimes okay, and sometimes not. For example, when you create a stored procedure in EM, you have to right-click within the stored procedures list, and choose New > Stored Procedure. This produces a modal dialog, so you cannot reference any other objects within that instance of EM. Also, the stored procedure editor does not allow you to apply permissions through the GUI until after the object has been created... so you have to hit Apply, close the dialog, and then double-click the procedure again to apply permissions. This is cumbersome at best.
In QA, you simply append GRANT EXEC to the end of the script.
Similarly, the Enterprise Manager Design Table GUI does not allow you to specify the owner for the table (it simply falls under ownership of the user logged in). However, in Query Analyzer, given adequate permissions, you can easily create tables that specify any owner prefix. Since the latter is the preferred standard (all tables owned by dbo or a specific schema), EM once again does not seem to be a recommended place to perform such tasks.
Another confusing issue is that if you right-click a table in EM's tables view, there are several menu items, including "cut":
In this case, neither cut nor copy actually does anything with the table itself or the data, they merely send the table's definition to the Windows clipboard. Go ahead, try it... right-click a table, choose Cut or Copy, and then open Notepad or Query Analyzer and hit Ctrl+V (or Edit|Paste). As one user pointed out, these two commands are redundant, because they both perform the exact same function (cut doesn't actually delete anything, which is the behavior users are used to from that command).
The stored procedure editor in EM does not allow you to load .SQL, .TAB, .PRC and other files from disk, so if you wanted to use an existing script file to create a stored procedure, you would have to open the .PRC file, select all, copy, and then paste into a new stored procedure editor. Similarly, the editor does not allow you to save work-in-progress to disk—the best you can do is save the SP, hope it doesn't return warnings or errors, and then generate a SQL Script from it (or, as before, use the old Ctrl+C, Ctrl+V trick). So, many database developers have to be using QA at least part of the time to get their work done.
If you want to make changes to a table that participates in a foreign key, you get a very scary dialog message asking you if you want to save changes to all of the related tables (but doesn't give you a hint about what those changes are). For example, create the following tables:
Now go into Enterprise Manager, right-click the table y, choose "Design Table," change the datatype of the column a to SMALLINT, and hit the Save button. You get this:
What the heck does the datatype of y.a have to do with the table x? Who knows? Maybe EM is being clever—or maybe this is just another bug.
Any kind of script that applies settings to multiple objects, e.g. granting or revoking permissions to several tables, will be much quicker in QA than in EM. Pointing and clicking your way around EM's GUI for repetitive tasks like this can be both cumbersome and prone to mistakes. Using a loop or writing out multiple GRANT statements in QA will be much easier; in addition, you can quasi-automate many of these tasks using undocumented system procedures like sp_MSforeachtable. Here is a script you can run in QA, which will generate a script in the bottom that applies SELECT access to all tables to the user 'bob'... once you've generated the result, copy it from the bottom pane, paste it into the top, and voila. (This works best if QA is set to Results in Text.)
There are many other things you can do in T-SQL that you just can't do in Enterprise Manager; the simplest example I can think of is creating an index on a BIT column (see Article #2530 for workarounds).
Another problem you may have seen while modifying database objects in Enterprise Manager is 'Invalid cursor state:'
See Article #2515 for possible causes and workarounds.
Database Diagrams
QA does not have a way to generate a database diagram. These can be quite helpful in visually designing a new schema and reviewing / altering an existing schema (there is a wizard to easily display only a subset of tables from an existing database, and it can even automatically include all related / dependent tables).
In QA, you have to be a gearhead and write all the code from scratch in order to generate new tables. And if you want to include related tables, you'll have to figure this out yourself through sysdepends or other system objects.
Now, having said that, there are third party tools that will allow you to create database diagrams. See the new SQL Server 2005 article, What happened to Database Diagrams?, for a list of alternate tools.
Debugging and Testing
Ever tried debugging in Enterprise Manager? Didn't think so. Query Analyzer, however, has the ability to step through a stored procedure, allowing you to toggle breakpoints, inspect the callstack, and just about everything else you would expect from a debugger for T-SQL code. It also provides for automatic rollback, which is something you have to worry about yourself if you are manually "debugging" your procedures.
QA is also very well-suited for analyzing queries and execution plans, either for comparing side-by-side, or testing the effects of minor changes to T-SQL code on CPU, memory, and disk I/O.
Note, however, that when using QA's debugger, all clients must be up to SP3 (see KB #328151 for more information).
Source Control
Source control is always a hot area of conversation, and is not facilitated by either EM or QA. However, it is quite easy to open/save scripts from Query Analyzer once you've checked them out from SourceSafe. Of course, you can use any editor, such as notepad, TextPad or UltraEdit (see Article #2500 for a more exhaustive list of alternatives). Personally, I check objects in and out of SourceSafe manually using the VSS GUI, then edit them in Visual Studio.NET 2003 or Query Analyzer. For more information on implementing source control with SQL Server, see Article #2495.
Scheduled Tasks
Enterprise Manager is the easiest way to set up a job. Minimally, it takes two or three steps to set up a scheduled task to execute the code of your choice. First, go to the Management node, SQL Server Agent, and highlight jobs. Right-click, new job, and give it a name. Move to the steps tab, add a step called step 1 with T-SQL code that says WAITFOR DELAY '00:01:00' - which simply tells the job to wait for 1 minute.
You're done (we'll use this job later to demonstrate one of EM's minor weaknesses). Creating this job in QA, on the other hand, can be quite intense. I did not modify this output to exaggerate my case; it is exactly what Generate SQL Script produced.
I'm sure you wouldn't want to write that script by hand every time you wanted to create a job! We don't even have a schedule yet; this is a simple job, with one step, consisting of one line of code, with no notifications. I bet we have some jobs that would take several pages of code to manually script out. (Similarly, you wouldn't want to create database maintenance plans by hand.)
Another aspect of jobs is viewing current status and previous job runtimes. Without EM's jobs pane, you would have to write quite verbose code to obtain this information directly out of msdb's system tables / stored procedures. However, as I'll explain later, the jobs pane is one of EM's 'sticky' interfaces that does not update itself with new status.
Data Consistency
Index usage, row counts, and overall table size are elements that are rarely up to date. In QA, you can simply run something like this:
However, how do you run a DBCC statement in EM? You don't... and therefore you must rely on out-of-date statistics when viewing the tables in TaskPad view. EM's Taskpad view is sufficient for a general overview of space allocation, last backup, and table and index sizes. However, if you really want up-to-the-second data, you should go directly against the system, since EM is just not updated frequently enough to be relied upon. See Article #2428 for one potential workaround — that you can either run directly in QA, or save as your own stored procedure and call at will. Another problem with the taskpad view is that it seems to be prone to scripting errors, due to its reliance on advanced DHTML through mshtml.dll:
(The workaround isn't altogether convoluted, but it isn't very intuitive either -- see Article #2293.)
Another inconsistency in EM's object presentation is the sorting algorithm used. On a few occasions, I have seen lists appear to be ordered randomly when they were *supposed* to be sorted by date. This is not a doctored screen shot:
Data Import / Export
Of the two tools, EM is the only way to easily create Data Transformation Services (DTS) packages, which allow you to move objects and data between databases, servers and networks. The wizard guides you through the steps of choosing a source and destination, the object(s) you want to transfer, and several other options you'd be hard-pressed to emulate in traditional T-SQL code. When you're finished, you have several other options, for example you can run it immediately and/or schedule it as a job, or simply save it off in a variety of formats for later re-use.
Overall Usability
Too many object lists in Enterprise Manager require manual refreshes, and it isn't 100% consistent where the F5 button actually works. For example, if you create a job, then right-click it and choose start, the interface doesn't update to tell you it's running. If the job is highlighted, F5 does nothing. You need to either right-click the job and choose 'refresh job' to just refresh the status and other properties of that object alone, or click on a blank spot in the pane (assuming there is one) and then hit F5. Another case where the visual display is not always accurate is the icon for SQL Server Agent. Is this server's Agent running, or not?
Other interfaces that don't auto-refresh are the lists of tables, stored procedures, users, logins, even databases... this is understandable, to some degree, because the interface clearly wasn't meant to be constantly scanning the server for changes. However it would be feasible to poll against only the objects associated with the current view, every few minutes or so (and maybe this interval could be user-defined).
Because Enterprise Manager relies on SQL-DMO to display its lists, you may find that it becomes an unusable tool in databases with a large number of objects. I have colleagues with applications that have thousands of stored procedures, and because of the way DMO allocates memory for the pull *possible* name of an object (256 bytes), and doesn't release it, they can't even view their stored procedures using EM. For objects within a database, this number is probably about 1,000. For databases within a server, EM is only usable up to about 100... this is mainly because EM takes it upon itself to open each user database in order to determine whether you have access (using sp_MShasdbaccess). This is markedly unacceptable when your databases are set to auto-close. If you find that EM is very slow at expanding the "databases" tree, see Article #2469 for some potential solutions.
EM doesn't allow you to expand a database by default (this would be a nice feature). With QA, the connection automatically sets the context to your default database, so you can run a query against it immediately after opening the application. With EM, you have to open the server name, possibly answer a 'are you sure' dialog because the server is not known to be running, expand databases, find your database, expand that, and then you're in. Having said that, with EM, it's easy once it's open to connect to multiple servers -- since all your registered servers are in a list. With QA, you have to pop open a new connection dialog for each server/instance you want to connect to.
Now, while we're on the topic of registered servers, it is much easier to connect to servers ad hoc within QA than in EM. EM requires you to go through the register server wizard, and unless you remove it, it adds that server to your server list permanently. In QA, you can just click connect, enter 1 or 3 parameters (depending on authentication mode), and you're in. Another downside to EM is that it can try and start the service when connecting to a registered server; this caused failovers in clustered environments in SQL Server 7.0, but has been fixed in 2000. Another problem in Enterprise Manager is that, on Windows XP systems, the whole set of registered servers can disappear when you change your password (this was fixed in SP3; see KB #323280 for more information).
Changing the structure of a table is more flexible in EM than in QA. In QA, using ALTER TABLE to add a column only allows you to add to the end of the table; you can't specify the ordinal position of the column. You can do this within EM, by inserting a column at any point. This comes at a cost, however; this is a "heavy touch," which can lock your table. EM copies the data into a new table with the new column added, replaces any indexes and constraints, then drops the old table and renames the new one. So, this technique can be quite dangerous; however, it can't be solved by QA, either. You shouldn't care about order of columns; if you really need to have the columns in a specific order, then create a view, or be prepared for potential data loss. Another problem with altering a table in Enterprise Manager is that many such changes will trigger a drop and re-create of the table, which can cause major headaches if your application relies on a pre-existing OBJECT_ID() for the table... since that is likely to change. Using ALTER TABLE in QA can prevent this from happening.
A few other handy tasks in EM are managing logins and users, setting up replication, viewing current activity, configuring SQL Mail, running the Index Tuning Wizard, building database maintenance plans, creating linked servers, and dealing with full-text catalogs. Some configuration options are exposed to T-SQL (usually through system stored procedures), and some are possible through a slightly more object-oriented approach via SQL-DMO (database management objects) or SQL-NS (namespace). Probably more than half of SQL Server's configuration options (that aren't simple flags / toggles), especially those on the server properties page, are easier to verify and change in EM's GUIs than in QA with straight T-SQL code.
Summary
So, it sounds like I've really been ragging on Enterprise Manager. Yes, I much prefer Query Analyzer for most of the things I do with my databases. But really, there is a silver lining here... I really don't think I could perform my tasks on a daily basis if I had to choose only *one* of the two tools (let's leave 3rd party tools out of the equation for now).
Anything development-related, you'll want to do in Query Analyzer (QA). However, many management-related tasks are better handled by Enterprise Manager (EM). Let's look at the pros and cons of each tool from the standpoint of various administrative/programming tasks.
Ad Hoc Queries
Query Analyzer is the easiest place to run ad hoc queries against the database. You can just open a connection and type something like:
| SELECT * FROM tablename WHERE id=1 |
Press F5, and you have your results.
In Enterprise Manager, you have to open the tables list, right click tablename, select Open Table > Return All Rows, then click on the SQL button to switch to 'SQL View,' change the query that EM created, and then press Ctrl+R (Run). This is not exactly convenient. Especially when, with larger resultsets, 'return all rows' and even 'return top' views can annoy you with popup dialogs about clearing the results pane:
QA has no time limit on how long you can inspect results, and you cannot adjust the timeout in Enterprise Manager.
The SQL view also only supports one statement, and behaves funny when you try more than one.
| SELECT TOP 10 * FROM authors ORDER BY au_id ASC SELECT TOP 10 * FROM authors ORDER BY au_id DESC |
The view will re-format the query, and ignore everything after the keyword 'ASC'...
If you have a long-running query, you can't adjust the timeout in either Query Designer or View Designer.
Columns with datatype TEXT almost always display as <LONG TEXT>. Columns with datatype SQL_VARIANT always display as <Binary>. QA can display up to 8,192 characters in each tuple of a result, and properly shows SQL_VARIANT data as character data.
If you enter a time only in Enterprise Manager, the date you will get is 1899-12-31. Enter the same type of data in an INSERT or UPDATE statement in Query Analyzer, and you will get 1900-01-01.
Simply put, the 'Return All Rows' view in Enterprise Manager is not a good place to modify data... from the unconventional use of CTRL+0 required to enter a NULL, to the limit of 1,024 characters, to the frequent errors about 'insufficient key column information' or 'transaction cannot start while in firehose mode' or 'data has changed', I would much rather run my INSERT / UPDATE statements in QA — or, better yet, in an application designed to intercept conflicts and ask you how you want to deal with them.
To run an explicit UPDATE statement or change the query slightly, you need to toggle into SQL View. This view does not support PRINT, SET and other commands you may be used to using in QA.
You also need to toggle into SQL View to change the order of the data you have returned into this view. Even though there are very Excel-esque "Sort Ascending" and "Sort Descending" buttons on the context-sensitive toolbar (I've just never seen them actually enabled):
In comparison, there are many usability features in Query Analyzer's Object Browser (F8). You can drag object names into your script, and you can even pull comma-separated lists of column names (for tables) or parameter names (for procedures and functions) by dragging the "Columns" and "Parameters" nodes respectively onto the query window. This can save you plenty of time typing out column names manually, and will help avoid typographical errors with more complex object names. When you're in Enterprise Manager's SQL view, you're on your own.
Another side-effect to certain parts of EM's GUI, such as the table designer and open table > return all rows, is that they use an ODBC link on top of the OLE-DB provider. This has two effects: (1) an extra later of indirection, making data access slightly slower; and (2) it makes it very difficult to deal with columns with datatypes like sql_variant, since ADO can't deal with those.
View Designer
If you right-click the Views node in Enterprise Manager, one option is "New View" which opens a graphical tool called the View Designer. This is what the interface looks like:
This might be a tempting way to create views, however there are several problems with it.
Many people complain that the view designer puts useless names like "Expr1"..."ExprN" in column aliases.
Also, if you open an existing view that has comments, it may combine and reformat them. Create this view in Query Analyzer, then right-click it in Enterprise Manager, choose Design View, and click Save:
| CREATE VIEW dbo.foo AS -- hi, I'm a comment SELECT * FROM authors /* I'm a multi-line comment */ |
(Many people complain that the comments disappear completely from the View Designer, so it's not immediately clear if you will lose them when you save.)
Now go to Query Analyzer, open the Object Browser, right-click the view and choose Edit. Gross. You can also do some experimenting with adding NEW comments in the view designer, and hitting save without running them. Also play around with how it treats -- and /**/ comments slightly differently...
KB #317009 says that if you have a database with period (".") in the name, column names will not be available... the only thing you will be able to choose is "All Columns." This is because Enterprise Manager knows the table exists, but can't resolve it properly because it doesn't know enough to add [] brackets around each name in the node, so the . is interpreted incorrectly. Don't use weird characters in object names!
Trying to save a view that references a linked server results in various errors. If you are using Oracle and the MSDAORA provider:
| ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction. [Microsoft][ODBC SQL Server Driver][SQL Server] OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b]. |
If you are using a SQL Server linked server:
| ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] MSDTC on server 'LinkedServerName' is unavailable. |
(The reason for this is because, behind the scenes, Enterprise Manager wraps the CREATE VIEW statement in a transaction, and there are many limitations with using transactions against remote servers.)
If you use the CASE statement, you will get an annoying error when you try to run or save the view:
| The Query Designer does not support the CASE SQL construct. |
The View Designer only supports a limited subset of the T-SQL language; CASE is one of the expressions that comes up most often.
If you have complex queries, you might see one of these errors:
| Error in ORDER BY clause. Unable to parse query text. --or View definition includes no output columns or includes no items in the FROM clause. |
The View Designer uses ODBC, so it may have problems with complex queries, such as unions with sub-queries and/or multiple types of joins.
All of these things are additional reasons I tend to run all of my T-SQL code in Query Analyzer. If you're going to be creating views, I strongly suggest you familiarize yourself with the CREATE VIEW and ALTER VIEW DDL statements.
Other errors
You might see this error when trying to open or design a table:
| Table 'tableName' could not be loaded ODBC error: [Microsoft][ODBC SQL Server Driver]Communication link failure |
This could be a basic network glitch, if you are connecting to SQL Server remotely. Also see KB #275025, which describes similar symptoms when the database is in 6.0 compatibility mode.
If you are trying to get rid of those annoying dt_ stored procedures from Visual Studio and/or the Diagrams node in Enterprise Manager, it won't let you because they are considered "system objects." See Article #2534 for more information and a workaround.
Another error you might receive when using Enterprise Manager:
| --------------------------- Microsoft Visual C++ Runtime Library --------------------------- Runtime Error! Program: C:\WINNT\system32\mmc.exe R6025 - pure virtual function call --------------------------- OK --------------------------- |
Enterprise Manager is confused, deep down. This usually requires a reinstallation of the client tools; however, you can try this first:
- Stop all SQL Server services (MSSQLServer, SQLServerAgent, MSSEARCH)
- Open command prompt and go to the Program Files\Microsoft SQL Server\80\Tools\Binn directory
- Unregister the following .dll files (regsvr32 /u): dtsui.dll, sqlns.dll, sqldmo.dll, sqllex.dll, sqlmmc.dll
- Copy and paste the .dll files from a CD (under x86\Binn directory) to the Program Files\Microsoft SQL Server\80\Tools\Binn folder
- Register the .dll files (regsvr32 dtsui.dll)
KB #268600
(BUG: When You Open Table with a Large Number of Columns SEM Appears to Hang or Reports Unknown Error)
KB #273472
(FIX: Enterprise Manager Returns Unexpected Error on Table or View)
Editing Database Objects
QA is definitely better suited for modifying table structure, stored procedure code, views, triggers, functions, indexes, etc. Everything is presented to you in Data Definition Language (DDL). You press F8, get your list of objects from the Object Browser, choose one, right-click, and you can edit a stored procedure or script a table. You can also drag the names of other objects from the object list into the current window, which cuts down quite a bit on my typos, at least.
In EM, the actual details of the object are hidden from you by pretty GUIs, which is sometimes okay, and sometimes not. For example, when you create a stored procedure in EM, you have to right-click within the stored procedures list, and choose New > Stored Procedure. This produces a modal dialog, so you cannot reference any other objects within that instance of EM. Also, the stored procedure editor does not allow you to apply permissions through the GUI until after the object has been created... so you have to hit Apply, close the dialog, and then double-click the procedure again to apply permissions. This is cumbersome at best.
In QA, you simply append GRANT EXEC to the end of the script.
Similarly, the Enterprise Manager Design Table GUI does not allow you to specify the owner for the table (it simply falls under ownership of the user logged in). However, in Query Analyzer, given adequate permissions, you can easily create tables that specify any owner prefix. Since the latter is the preferred standard (all tables owned by dbo or a specific schema), EM once again does not seem to be a recommended place to perform such tasks.
Another confusing issue is that if you right-click a table in EM's tables view, there are several menu items, including "cut":
In this case, neither cut nor copy actually does anything with the table itself or the data, they merely send the table's definition to the Windows clipboard. Go ahead, try it... right-click a table, choose Cut or Copy, and then open Notepad or Query Analyzer and hit Ctrl+V (or Edit|Paste). As one user pointed out, these two commands are redundant, because they both perform the exact same function (cut doesn't actually delete anything, which is the behavior users are used to from that command).
The stored procedure editor in EM does not allow you to load .SQL, .TAB, .PRC and other files from disk, so if you wanted to use an existing script file to create a stored procedure, you would have to open the .PRC file, select all, copy, and then paste into a new stored procedure editor. Similarly, the editor does not allow you to save work-in-progress to disk—the best you can do is save the SP, hope it doesn't return warnings or errors, and then generate a SQL Script from it (or, as before, use the old Ctrl+C, Ctrl+V trick). So, many database developers have to be using QA at least part of the time to get their work done.
If you want to make changes to a table that participates in a foreign key, you get a very scary dialog message asking you if you want to save changes to all of the related tables (but doesn't give you a hint about what those changes are). For example, create the following tables:
| CREATE TABLE x ( i INT PRIMARY KEY ) GO CREATE TABLE y ( a INT, xi INT FOREIGN KEY REFERENCES x(i) ) GO |
Now go into Enterprise Manager, right-click the table y, choose "Design Table," change the datatype of the column a to SMALLINT, and hit the Save button. You get this:
What the heck does the datatype of y.a have to do with the table x? Who knows? Maybe EM is being clever—or maybe this is just another bug.
Any kind of script that applies settings to multiple objects, e.g. granting or revoking permissions to several tables, will be much quicker in QA than in EM. Pointing and clicking your way around EM's GUI for repetitive tasks like this can be both cumbersome and prone to mistakes. Using a loop or writing out multiple GRANT statements in QA will be much easier; in addition, you can quasi-automate many of these tasks using undocumented system procedures like sp_MSforeachtable. Here is a script you can run in QA, which will generate a script in the bottom that applies SELECT access to all tables to the user 'bob'... once you've generated the result, copy it from the bottom pane, paste it into the top, and voila. (This works best if QA is set to Results in Text.)
| EXEC sp_MSforeachtable 'SELECT ''GRANT EXEC ON ? TO [bob]''' |
There are many other things you can do in T-SQL that you just can't do in Enterprise Manager; the simplest example I can think of is creating an index on a BIT column (see Article #2530 for workarounds).
Another problem you may have seen while modifying database objects in Enterprise Manager is 'Invalid cursor state:'
See Article #2515 for possible causes and workarounds.
Database Diagrams
QA does not have a way to generate a database diagram. These can be quite helpful in visually designing a new schema and reviewing / altering an existing schema (there is a wizard to easily display only a subset of tables from an existing database, and it can even automatically include all related / dependent tables).
In QA, you have to be a gearhead and write all the code from scratch in order to generate new tables. And if you want to include related tables, you'll have to figure this out yourself through sysdepends or other system objects.
Now, having said that, there are third party tools that will allow you to create database diagrams. See the new SQL Server 2005 article, What happened to Database Diagrams?, for a list of alternate tools.
Debugging and Testing
Ever tried debugging in Enterprise Manager? Didn't think so. Query Analyzer, however, has the ability to step through a stored procedure, allowing you to toggle breakpoints, inspect the callstack, and just about everything else you would expect from a debugger for T-SQL code. It also provides for automatic rollback, which is something you have to worry about yourself if you are manually "debugging" your procedures.
QA is also very well-suited for analyzing queries and execution plans, either for comparing side-by-side, or testing the effects of minor changes to T-SQL code on CPU, memory, and disk I/O.
Note, however, that when using QA's debugger, all clients must be up to SP3 (see KB #328151 for more information).
Source Control
Source control is always a hot area of conversation, and is not facilitated by either EM or QA. However, it is quite easy to open/save scripts from Query Analyzer once you've checked them out from SourceSafe. Of course, you can use any editor, such as notepad, TextPad or UltraEdit (see Article #2500 for a more exhaustive list of alternatives). Personally, I check objects in and out of SourceSafe manually using the VSS GUI, then edit them in Visual Studio.NET 2003 or Query Analyzer. For more information on implementing source control with SQL Server, see Article #2495.
Scheduled Tasks
Enterprise Manager is the easiest way to set up a job. Minimally, it takes two or three steps to set up a scheduled task to execute the code of your choice. First, go to the Management node, SQL Server Agent, and highlight jobs. Right-click, new job, and give it a name. Move to the steps tab, add a step called step 1 with T-SQL code that says WAITFOR DELAY '00:01:00' - which simply tells the job to wait for 1 minute.
You're done (we'll use this job later to demonstrate one of EM's minor weaknesses). Creating this job in QA, on the other hand, can be quite intense. I did not modify this output to exaggerate my case; it is exactly what Generate SQL Script produced.
| -- Script generated on 2/16/2003 8:58 PM -- By: MONOLITHIC\Administrator -- Server: (local) BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Aaron''s jobs') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'Aaron''s jobs' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'foo job') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''foo job'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'foo job' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'foo job', @owner_login_name = N'MONOLITHIC\Administrator', @description = N'Some description', @category_name = N'Aaron''s jobs', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'step 1', @command = N'WAITFOR DELAY ''0:01:00''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: |
I'm sure you wouldn't want to write that script by hand every time you wanted to create a job! We don't even have a schedule yet; this is a simple job, with one step, consisting of one line of code, with no notifications. I bet we have some jobs that would take several pages of code to manually script out. (Similarly, you wouldn't want to create database maintenance plans by hand.)
Another aspect of jobs is viewing current status and previous job runtimes. Without EM's jobs pane, you would have to write quite verbose code to obtain this information directly out of msdb's system tables / stored procedures. However, as I'll explain later, the jobs pane is one of EM's 'sticky' interfaces that does not update itself with new status.
Data Consistency
Index usage, row counts, and overall table size are elements that are rarely up to date. In QA, you can simply run something like this:
| DBCC UPDATEUSAGE('database_name') EXEC sp_spaceused |
However, how do you run a DBCC statement in EM? You don't... and therefore you must rely on out-of-date statistics when viewing the tables in TaskPad view. EM's Taskpad view is sufficient for a general overview of space allocation, last backup, and table and index sizes. However, if you really want up-to-the-second data, you should go directly against the system, since EM is just not updated frequently enough to be relied upon. See Article #2428 for one potential workaround — that you can either run directly in QA, or save as your own stored procedure and call at will. Another problem with the taskpad view is that it seems to be prone to scripting errors, due to its reliance on advanced DHTML through mshtml.dll:
(The workaround isn't altogether convoluted, but it isn't very intuitive either -- see Article #2293.)
Another inconsistency in EM's object presentation is the sorting algorithm used. On a few occasions, I have seen lists appear to be ordered randomly when they were *supposed* to be sorted by date. This is not a doctored screen shot:
Data Import / Export
Of the two tools, EM is the only way to easily create Data Transformation Services (DTS) packages, which allow you to move objects and data between databases, servers and networks. The wizard guides you through the steps of choosing a source and destination, the object(s) you want to transfer, and several other options you'd be hard-pressed to emulate in traditional T-SQL code. When you're finished, you have several other options, for example you can run it immediately and/or schedule it as a job, or simply save it off in a variety of formats for later re-use.
Overall Usability
Too many object lists in Enterprise Manager require manual refreshes, and it isn't 100% consistent where the F5 button actually works. For example, if you create a job, then right-click it and choose start, the interface doesn't update to tell you it's running. If the job is highlighted, F5 does nothing. You need to either right-click the job and choose 'refresh job' to just refresh the status and other properties of that object alone, or click on a blank spot in the pane (assuming there is one) and then hit F5. Another case where the visual display is not always accurate is the icon for SQL Server Agent. Is this server's Agent running, or not?
Other interfaces that don't auto-refresh are the lists of tables, stored procedures, users, logins, even databases... this is understandable, to some degree, because the interface clearly wasn't meant to be constantly scanning the server for changes. However it would be feasible to poll against only the objects associated with the current view, every few minutes or so (and maybe this interval could be user-defined).
Because Enterprise Manager relies on SQL-DMO to display its lists, you may find that it becomes an unusable tool in databases with a large number of objects. I have colleagues with applications that have thousands of stored procedures, and because of the way DMO allocates memory for the pull *possible* name of an object (256 bytes), and doesn't release it, they can't even view their stored procedures using EM. For objects within a database, this number is probably about 1,000. For databases within a server, EM is only usable up to about 100... this is mainly because EM takes it upon itself to open each user database in order to determine whether you have access (using sp_MShasdbaccess). This is markedly unacceptable when your databases are set to auto-close. If you find that EM is very slow at expanding the "databases" tree, see Article #2469 for some potential solutions.
EM doesn't allow you to expand a database by default (this would be a nice feature). With QA, the connection automatically sets the context to your default database, so you can run a query against it immediately after opening the application. With EM, you have to open the server name, possibly answer a 'are you sure' dialog because the server is not known to be running, expand databases, find your database, expand that, and then you're in. Having said that, with EM, it's easy once it's open to connect to multiple servers -- since all your registered servers are in a list. With QA, you have to pop open a new connection dialog for each server/instance you want to connect to.
Now, while we're on the topic of registered servers, it is much easier to connect to servers ad hoc within QA than in EM. EM requires you to go through the register server wizard, and unless you remove it, it adds that server to your server list permanently. In QA, you can just click connect, enter 1 or 3 parameters (depending on authentication mode), and you're in. Another downside to EM is that it can try and start the service when connecting to a registered server; this caused failovers in clustered environments in SQL Server 7.0, but has been fixed in 2000. Another problem in Enterprise Manager is that, on Windows XP systems, the whole set of registered servers can disappear when you change your password (this was fixed in SP3; see KB #323280 for more information).
Changing the structure of a table is more flexible in EM than in QA. In QA, using ALTER TABLE to add a column only allows you to add to the end of the table; you can't specify the ordinal position of the column. You can do this within EM, by inserting a column at any point. This comes at a cost, however; this is a "heavy touch," which can lock your table. EM copies the data into a new table with the new column added, replaces any indexes and constraints, then drops the old table and renames the new one. So, this technique can be quite dangerous; however, it can't be solved by QA, either. You shouldn't care about order of columns; if you really need to have the columns in a specific order, then create a view, or be prepared for potential data loss. Another problem with altering a table in Enterprise Manager is that many such changes will trigger a drop and re-create of the table, which can cause major headaches if your application relies on a pre-existing OBJECT_ID() for the table... since that is likely to change. Using ALTER TABLE in QA can prevent this from happening.
A few other handy tasks in EM are managing logins and users, setting up replication, viewing current activity, configuring SQL Mail, running the Index Tuning Wizard, building database maintenance plans, creating linked servers, and dealing with full-text catalogs. Some configuration options are exposed to T-SQL (usually through system stored procedures), and some are possible through a slightly more object-oriented approach via SQL-DMO (database management objects) or SQL-NS (namespace). Probably more than half of SQL Server's configuration options (that aren't simple flags / toggles), especially those on the server properties page, are easier to verify and change in EM's GUIs than in QA with straight T-SQL code.
Summary
So, it sounds like I've really been ragging on Enterprise Manager. Yes, I much prefer Query Analyzer for most of the things I do with my databases. But really, there is a silver lining here... I really don't think I could perform my tasks on a daily basis if I had to choose only *one* of the two tools (let's leave 3rd party tools out of the equation for now).
Related Articles
How do I build a query with optional parameters? How do I calculate the median in a table? How do I create a store locator feature? How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns? How do I deal with multiple resultsets from a stored procedure? How do I debug my SQL statements? How do I determine if a column exists in a given table? How do I enable or disable connection pooling? How do I enumerate through the DSNs on a machine? How do I find a stored procedure containing <text>? How do I get a list of Access tables and their row counts? How do I get the latest version of the JET OLEDB drivers? How do I handle alphabetic paging? How do I handle BIT / BOOLEAN columns? How do I handle error checking in a stored procedure? How do I ignore common words in a search? How do I page through a recordset? How do I present one-to-many relationships in my ASP page? How do I prevent duplicates in a table? How do I prevent my ASP pages from waiting for backend activity? How do I prevent NULLs in my database from mucking up my HTML? How do I protect my Access database (MDB file)? How do I protect my stored procedure code? How do I protect myself against the W32.Slammer worm? How do I remove duplicates from a table? How do I rename a column? How do I retrieve a random record? How do I return row numbers with my query? How do I send a database query to a text file? How do I simulate an array inside a stored procedure? How do I solve 'Could not find installable ISAM' errors? How do I solve 'Operation must use an updateable query' errors? How do I temporarily disable a trigger? How do I use a SELECT list alias in the WHERE or GROUP BY clause? How do I use a variable in an ORDER BY clause? Should I index my database table(s), and if so, how? Should I store images in the database or the filesystem? Should I use a #temp table or a @table variable? Should I use a view, a stored procedure, or a user-defined function? Should I use recordset iteration, or GetRows(), or GetString()? What are all these dt_ stored procedures, and can I remove them? What are the limitations of MS Access? What are the limitations of MSDE? What are the valid styles for converting datetime to string? What datatype should I use for my character-based database columns? What datatype should I use for numeric columns? What does "ambiguous column name" mean? What is this 'Multiple-step OLE DB' error? What is wrong with 'SELECT *'? What naming convention should I use in my database? What should I choose for my primary key? What should my connection string look like? When should I use CreateObject to create my recordset objects? Where can I get this 'Books Online' documentation? Where do I get MSDE? Which database platform should I use for my ASP application? Why are there gaps in my IDENTITY / AUTOINCREMENT column? Why can I not 'open a database created with a previous version...'? Why can't I access a database or text file on another server? Why can't I use the TOP keyword? Why do I get 'Argument data type text is invalid for argument [...]'? Why do I get 'Not enough space on temporary disk' errors? Why does ASP give me ActiveX errors when connecting to a database? Should I use COALESCE() or ISNULL()? Where can I get basic info about using stored procedures?