Sunday, October 09, 2005

Generating SP scripts with VS.NET 2003 ,SQL Server 2000

1)It seems that the dependencies for stored procedures relies on the sysdepends table which doesn't always have the proper relational keys. If the source code for the stored procedure exceeds the maximum allowed bytes in the syscomments table, SQL Server spreads the source code across multiple records and uses sp_helptext to combine the code back into one long string again. This is dealt with and explained by an MVP with sample script where he has also specified what to custom edit at http://www.eggheadcafe.com/articles/20030609.asp

2)By the way, SQL-DMO uses the Microsoft® SQL Server ODBC driver to connect to and communicate with instances of SQL Server. If you have installed SQL Server Enterprise Manager then you should have the SQL DMO installed on your system.I found the following article that takes a preview into one such capability of DMO to script out table description from SQL Server. The following code helps you script the table definitions from the database you choose. You have an option to script out Indexes and Triggers associated with the tables.

http://www.codeproject.com/database/sqldmo.asp

3 a)You might also find the following utility interesting .

Interactive SQL Tool (using ADO)
http://www.codeproject.com/database/isqlado.asp

Description
Query Tool (using ADO) is an Universal Data Access tool that allows you to query OLE DB data sources, author SQL scripts and queries, return query results to a grid or free-form text, retrieve provider properties, execute multiple SQL scripts or stored procedures simultaneously, retrieve multiple result sets depending on the data source, etc.

3 b) Stored Procedure Class Wizard (SPCW)
http://www.codeproject.com/database/spcw.asp

SPCW is a development tool that can be used exclusively to generate class files (.cpp and .h) derived from MFC’s CRecordset for stored procedures.

4)You might also find it interesting that ,If you have made an application that uses an SQL database that needs to be located on the client server, rather than go for a InstallShield or another product you can use the Installer class ,and make a class derived from System.Configuration.Install ,then add two Embedded Resources named install.txt & uninstall.txt to the solution. The install.txt will contain the SQL script for your database and uninstall.txt the drop script.

More info on the Install class from MSDN here

PS: SQL2000 permits an XML file to be passed as the argument of a stored procedure. and u can check out an example of that install class mentioned above ,at http://www.codeguru.com/Csharp/Csharp/cs_misc/sampleprograms/article.php/c9601/

No comments: