Editor's Note: Share your SQL Server tips and tricks with other SQL Server Magazine readers. Email your ideas (400 words or fewer) to r2r@sqlmag.com. If we print your submission, you'll get $50.
In a large database project, many programmers each write many stored procedures, views, and triggers, and the relationships among these objects are complex. Stored procedure A might use view A, and view A might use view B. At the same time, trigger A might use stored procedure A. If view A is based on the join of several tables and views and any table or view in the join changes, view A can fail. Such a failure will disable all the objects that need to use view A. When your database project includes hundreds or thousands of objects, you can get quite a headache trying to find out which objects the failure affects.
Another problem developers in my organization have encountered is that sometimes a complex view won't return a correct record set after the view has existed for a long time. This type of failure is hard to predict, but generally, the more complex a view is, the more likely it is to fail. For example, a view that has more than five hierarchy levels (e.g., view A is based on view B, view B is based on view C, and so on) might not return a correct record set because the underlying tables have changed—perhaps a new column has been added, a column data type has changed, or an underlying table has been dropped and recreated.
A good way to solve problems that changing objects cause is to recompile all the objects in the database that are associated with the problems. Recompiling reestablishes the links between objects and helps you find potential problems. In C and C++, the command make.exe recompiles all the source files in a project and reports any errors. For the environment I work in, I wrote VBScript code, which Listing 1 shows, that uses SQL-DMO and ADO to recompile views, stored procedures, and triggers. The script runs in SQL Server 2000 and 7.0 and uses Windows Script Host (WSH) on Windows 2000, Windows 98, or Windows 95. You can download WSH and Microsoft Windows Script from Microsoft's Web site. Go to http://msdn.microsoft.com/scripting and select Windows Script 5.6 in the navigation bar on the right side of the screen.
You use the Microsoft command syntax for this script:
cscript SQL_Refresh.vbs [-n<object_name>]
[-t<object_type>]
WHERE <object_name> can contain wild cards like vw_abc%
AND <object_type> = vw | sp | tr
To choose which objects to recompile, you can customize the cscript command in the command syntax. For example, in a command window, type one of the following lines:
cscript SQL_Refresh.vbs -tvw
--- Refresh all the views in your
--- database.
cscript SQL_Refresh.vbs -nsp_abc% -tsp
--- Refresh the stored procedures whose
--- names start with sp_abc (e.g.,
--- sp_abcDelete_Table1,
--- sp_abc_Update_Table2).
cscript SQL_Refresh.vbs
--- Refresh all the objects (i.e.,
--- stored procedures, views, and
--- triggers) in your database.
Note that you can omit the cscript command in the above syntax, but if you do, error messages will appear in a pop-up window that you'll have to close before the script can continue. Usually, error messages appear in the current command window, in which the script can run without interaction. Also note that you need to change the three constant values (cFilePath, cServerName, and cDatabaseName) to adapt the script to your environment.
After you run the script in Listing 1, any errors are saved in a file called Results.txt. So if you put the script in a SQL Server job and schedule it to run overnight, you can simply check Results.txt in the morning to see whether any errors occurred.
—Jeffrey Yao
jeffrey.yao@cognicase.com