SQL
JDBC AND STORED PROCEDURES
	SQL Persistent Stored Modules, such as stored procedures, can be executed through JDBC. To do this, the CallableStatement class must be used. An example is shown below, where the getProductName stored procedure is executed: 1) Prepare the call of the stored procedure: CallableStatement callState = conn.prepareCall("{? = call getCustName(?)}"); 2) Register the output parameter that will contain the name: callState.registerOutParameter(1,Types.Varchar); 3) Set up the input variable that will be passed to the stored procedure using an already defined and set variable for the customer number: callState.setInt(2,vCustNumber); 4) Execute the stored procedure: callState.executeQuery(); 5) The output string can be retrieved by moving the following to a string variable: callState.getString(1); As you can see, the advantages of database-stored procedures can be maintained when performing JDBC database access.
SQL SERVER MAGAZINE UPDATE TIP OF THE WEEK : MOVING AND RESTORING DTS PACKAGES (contributed by Richard Waymire, rwaymi@microsoft.com) Q. To copy and move or back up and restore Data Transformation Services (DTS) packages in SQL Server 7.0, I opened each DTS package in the DTS Designer and saved it to a new server or file. This method worked for a few relatively static packages. However, as my team develops more DTS packages, it's becoming cumbersome to transfer them from the development machine to the test machine and production. Can you recommend an easier, more automated way to back up and restore DTS packages or transfer them between servers? A. When you save DTS packages in SQL Server, a table in the Microsoft database (msdb) called sysdtspackages contains the DTS package definitions. To move these definitions, you can create a package and simply copy the contents of msdb..sysdtspackages from one server to the other. Keep in mind that you still need to register any appropriate COM components on the other server, and because sysdtspackages is a system table, you might need to change the Allow Updates configuration option setting on your import server to make the transfer work.
SORT ORDERS IN DATABASES
	Q. I'm working with different programming languages and sort orders in databases. Do I need to reinstall SQL Server 7.0 with the new sort order every time I import and export data? A. Yes, you need to reinstall SQL Server to rebuild user databases in SQL Server 7.0. You need to rebuild the Master database after you export data to ASCII text files with bulk copy program (bcp) or Data Transformation Services (DTS). Then you import the databases with the new sort order to the server with bcp, BULK INSERT, or DTS. (contributed by Richard Waymire, rwaymi@microsoft.com)
SQL SERVER MAGAZINE UPDATE TIP OF THE WEEK: RECOVERING FAILED DATA WITHOUT THE TRANSACTION LOG (contributed by Richard Waymire, rwaymi@microsoft.com)
	Q. I'm trying different solutions to protect my data and minimize costs. First, I used a RAID 5 solution with three disks on which I kept data and transaction log files, with a backup every 2 hours on a separate device (disk or tape) that a separate disk controller manages. But if the RAID 5 controller fails, I lose the transactions of the past 2 hours. Then I tried keeping the transaction log on a separate disk (three RAID 5 disks for data and one physical disk for the log--not managed by a RAID 5 controller), which lets me recover all my transactions from the backup and the log. However, if the log disk fails, the database isn't accessible (it's marked as suspect) and I get a 9001 error. For example, I create a simple database with the transaction log on the F drive, stop the SQL Server, drop the F partition, and restart SQL Server. At this point, SQL Server marks the database as suspect, and the database is inaccessible. Here's the setup: three physical disks in RAID 5 (with C, D partitions), managed by a SCSI controller one physical disk, (with F partition), managed by an IDE controller The test database looks like this: test_data.MDF on the D: partition test_log.LDF on the F: partition I've tried unsuccessfully to reset the suspect bit by using the sp_resetstatus procedure, by using a bulk copy program (bcp) to export the data, and by trying to perform a backup of the database at that point. Can I recover my data without the transaction log? A. No. If you lose your transaction log, you need to restore from your backup media, so protecting your transaction log is essential. Put the log on a mirrored device under a separate controller or use Windows NT mirroring to two single disks on two separate controllers. This approach costs more than using a single disk controller, but you can't afford to lose your transaction log. RAID 5 is a good choice for the data, but protecting your transaction log is your primary concern (protecting your log is more important than protecting your data). Also consider the effect of RAID 5 on restoration times: It slows restoration considerably compared to mirroring because with mirroring there's no need to calculate the parity bits when adding data. Using a separate file location (device) for your backups is also good practice.