These timesaving techniques belong in your bag of tricks
I'm looking for some timesaving SQL Query Analyzer tricks. Do you know of any?
Here's a simple way to manage tabs and comments while you're working from Query Analyzer. Select the T-SQL code you're working with and choose Edit, Advanced from Query Analyzer's menu bar. If you're working with SQL Server 2000, you'll see the following options (the first two of which are also available in SQL Server 7.0):
You can use these options (or the associated keystrokes) to quickly clean up code selections.
Here's another tip: Have you ever wasted time searching for a syntax error within a long stored procedure or batch? Simply double-click the error message that appears in Query Analyzer's lower pane, and your cursor will move to the statement that generated the error.
How can I use T-SQL to programmatically determine how many processors my SQL Server machine has?
The simple answer is to use the following command:
However, I can't possibly stop with the simple answer.
I didn't remember this command when I first saw the reader's question. But I remembered that the Processor tab of SQL Server Enterprise Manager's SQL Server Properties dialog box displays the information the reader wants. And I know that Enterprise Manager gets 99.9 percent of its information by running simple T-SQL commands against the server—and that I can run SQL Server Profiler to view Enterprise Managergenerated commands. Knowing all this, I used Profiler to isolate—in about 3 minutes—Enterprise Manager's calls to xp_msver.
I'm having trouble running sqlagent.exe from the command line for a named instance of SQL Server. The command won't start the correct sqlagent associated with the instance. Do you have any suggestions?
This reader ultimately resolved the situation with the help of Microsoft Product Support Services (PSS), which pointed him to an undocumented sqlagent.exe command-line switch.
The command-line executable sqlagent.exe starts SQL Server Agent from the command line—something you'd typically never do. (Generally, you start and stop SQL Server Agent from the SQL Server Service Manager, but starting SQL Server Agent from the command line is helpful in certain testing and troubleshooting situations.) Unfortunately, SQL Server Books Online (BOL) documents only two options for starting sqlagent.exe from the command line, and neither switch can specify a named instance of SQL Server Agent. Running sqlagent.exe on a machine with only a named instance can generate an error similar to the one that Figure 1 shows. The reader learned from PSS that this error occurs because SQL Server places the SQLServerAgent registry subkey in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server subkey, not in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\InstanceName\MSSQLServer subkey, when you install SQL Server as a named instance.
When starting a named instance of SQL Server Agent from the command line, you need to use the undocumented -i (for instance, I assume) parameter. This parameter takes only the instance name rather than the usual server\instance configuration. You must also use the correct version of sqlagent.exe, which is in the "binn" directory of the pertinent instance.
