Editor's Note: Send your SQL Server questions to Richard Waymire, lead program manager for SQL Server development at Microsoft, at [email protected].
When I reference an incorrect object name in the middle of a long stored procedure (and the object I referenced therefore doesn't exist), the stored procedure's execution fails, and SQL Server generates an invalid object error message. Can I write a stored procedure that executes even though it can't find an object because I referenced it incorrectly?
You can wrap those calls in an IF statement that starts with an existence check. The files in your \mssql7\install directory show many examples of IF statements, such as
IF object_id('spt_committab','U') IS NOT NULL
BEGIN
PRINT 'drop table spt_committab ....'
DROP TABLE spt_committab
END
When we try to install SQL Server 7.0 Service Pack 2 (SP2), we receive the error message Error executing script 1-4 Sp1.sql. We have already applied SQL Server 7.0 SP1. Why are we getting this error message?
SP2 includes SP1, so the file sp1.sql has failed in some way. Look in the file sp1.out, which should be installed on your machine, to find the exact error. When you know what the error is, you might have enough information to fix the problem or to further pursue the diagnosis.
Why does a stored procedure in SQL Server 7.0 lose its security context while executing a piece of dynamic T-SQL? One of our stored procedures can execute static or dynamic T-SQL, depending on the user request. Both queries access the same tables with the same joins and values, but the dynamic T-SQL query returns multiple rows, depending on the number we request. The stored procedure always works when it executes the static T-SQL portion, but Permission denied error messages appear on the tables when the stored procedure executes the dynamic T-SQL portion. Security can't be the problem because our security measures require that users hold Exec rights only on the stored procedures and that we use Windows NT authentication. Moreover, the dynamic T-SQL syntax is working properly, because I can fix the problem simply by granting select permissions on the two tables involved in the join. However, I can't apply this makeshift solution indefinitely because it breaches our security regulations. What's going on?
Typically, a stored procedure executes under the security context of the caller. Within the stored procedure, ownership chain rules apply to static T-SQL. That is, if a Database Owner (DBO) owns the stored procedure and selects data from a table or view the DBO also owns, SQL Server 7.0 doesn't check security. However, dynamic T-SQL is by definition unknown ahead of time and might try to retrieve data the stored procedure owner doesn't want to reveal.
In the first example below, the dynamic T-SQL executes properly (as you expect).
CREATE PROC myproc (@tablename nvarchar(2000))
AS
EXEC ('SELECT * FROM ' + @tablename)
RETURN
EXEC myproc 'pubs..authors'
But something different happens in the second example.
EXEC myproc 'pubs..authors exec sp_addlogin "richard" exec sp_addsrvrolemember "richard", "sysadmin"'
In executing this statement, the caller not only retrieved the author data but also hacked into the system and created a new login with systems administrator (sa) rights. For this reason, SQL Server requires the dynamic T-SQL context to run inside the caller's security context to make sure the caller really has the right to run additional code. SQL Server doesn't trust that the dynamic SQL is necessarily called the way the author of the stored procedure intended, which the first example shows.
I need help identifying and troubleshooting a slow-performing query in SQL Server 6.5. Because I can use only SQL Server 6.5 Trace to create traces and log files, can I call upon SQL Server 7.0's Index Tuning Wizard to recommend missing indexes or find slow-performing queries? What other tools will do the job? And can I load a SQL Server 6.5 Trace file into SQL Server 7.0 Profiler, export that log file trace to a table, and sort by duration?
SQL Server 7.0's Index Tuning Wizard won't work because it uses specific calls into the Query Optimizer that don't exist in SQL Server 6.5. Instead, you can load a SQL Server 6.5 Trace file into SQL Server 7.0 Profiler, as you described. I highly recommend two resources: David Solomon and Ray Rankin, SQL Server 6.5 Unleashed (SAMS, 1998), and Microsoft's course 665, "Performance Tuning and Optimization of SQL Server 6.5." To view the course information, go to http://www.microsoft.com/trainingandservices/default.asp?pageid=training&name=course and choose Microsoft SQL Server, IT Professional, Instructor Led, Submit.
I've tried unsuccessfully to find technical information about SQL Server, as well as Windows 2000 and Windows NT 4.0, on the Web. I need details such as processor and memory capacity. Where can I find these specifications?
To find technical information online for SQL Server 2000 and 7.0, see SQL Server Books Online (BOL). Look under SQL Server Architecture, Implementation Details, Maximum Capacity Specifications. For Windows products, you get the same kind of information at http://www.microsoft.com/windows.
I rely on the ADO CommandTimeout property to detect failures of my catalog request server. When my server goes down, I want the Web user's request to fail over more quickly to the backup server. How can I reduce the timeout to 3 seconds or less?
The time you specify can be any positive number of seconds. However, the timeout developer tells me that the specified time isn't guaranteed; you have to find the time that works for you by trial and error.
I'm trying to upgrade a SQL Server 6.5 database to SQL Server 7.0, but I receive page link error messages. SQL Server 6.5 Books Online (BOL) says I should restore from the last known good backup, but no backups are available. Does a utility similar to DBCC CHECKDB (database, REPAIR_ALLOWDATALOSS) exist for SQL Server 6.5? What should I do?
If you don't have a backup, you need to contact Microsoft product support and hope that a support specialist can repair the database. SQL Server 6.5 has no automated repair tool.
I've placed a trigger on a table called WorkOrders. If a work order's status changes, the trigger fires and updates the work order with a date and puts a row into the events table. This trigger works fine if I update one work order. However, if I update multiple work orders with one update statement, the trigger fires only for the first work order. What am I doing wrong?
Your SQL Server trigger is working the way Microsoft designed it, so if you want a particular action to occur for each row, you need to open a cursor over the Inserted or Deleted tables and perform the appropriate action on the rows one at a time (or write your code to work on rowsets rather than on individual rows).
I know that an interprocess communication (IPC) is a Net-Library DLL. But are Named Pipes and TCP/IP categorized as protocols, or are they IPCs?
They're IPCs. SQL Server can talk between the client and server over virtually any network protocol. The exception is TCP/IP sockets, which requires the TCP/IP protocol underneath.
What is collation, and how does it relate to locale settings?
For your answer, take a look at the topic "SQL Server Collation Fundamentals" in SQL Server Books Online (BOL) under SQL Server Architecture, Database Architecture, Logical Database Components, Collations.
I use SQL Server 7.0's backup option to back up our databases and transaction logs every night. But I also need to back up Data Transformation Services (DTS) packages and SQLServerAgent jobs because I have to guarantee that we can restore our servers to another location. I also want to assure data recovery for users who accidentally make faulty changes to a DTS package or a SQLServerAgent job and want to restore to an earlier version. Can I also back up DTS packages and SQLServerAgent jobs?
If you back up SQL Server's msdb database, you automatically back up DTS packages and SQLServerAgent jobs because SQL Server stores them as table entries in msdb.
