• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Troubleshooting

Locks on SQL Server System Tables

Q1:I get an “Errors from Server” message similar to that shown in Figure 22.32 at the beginning of the upsizing process.

Figure 22.32. This error message indicates that someone or something has locked the model database, which SQL Server uses as a template for all new databases you create.


A1: Errors that report the inability to obtain an exclusive lock on the table usually mean that you or another user has the required database open. For example, the error message of Figure 22.32 resulted from the model database being opened in SQL Server Enterprise Manager. Clicking OK opens the upsizing report, but no objects have upsized.

Make sure that you or others don't have the model database open, and then try starting the upsizing process again. If you receive the same or a similar error, reboot the machine running SQL Server to delete spurious locks. Spurious locks aren't common, but they do occur occasionally.

Stored Procedures Don't Return the Expected Recordset

Q1:My SELECT * FROM TableName statement at the end of a stored procedure that includes INSERT [INTO], UPDATE, or DELETE statements doesn't return any rows.
A1: You forgot to add SET NOCOUNT ON as the first statement of your procedure. T-SQL and VBA can handle combinations of multiple return values and Recordsets from a stored procedure, but ADP can't. If you don't add SET NOCOUNT ON, the server returns the number of records affected for each operation, which generates an unneeded data transfer from the server to the client. In the case of the spCategoriesCT procedure, the server returns 10 values before sending the Recordset. Unless you're using VBA code to execute the query and need the RecordsAffected values returned after execution, always make SET NOCOUNT ON the first statement of your stored procedures.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint