I have a situation in which a trigger calls a stored procedure that creates a temp table within the BEGIN TRAN operation and drops the temp table before the transaction is committed. An error in SQL Server 7.0 appears, saying the DROP TABLE command is not allowed in this trigger statement. However, this approach works in SQL Server 6.5.
DROP TABLE is one command that isn't allowed in a trigger. You can drop a table any other time, but not inside a trigger. (For other commands that aren't allowed in a trigger, see SQL Server Books Online (BOL), Create Trigger in T-SQL.) According to the SQL Server 6.5 documentation, DROP TABLE shouldn't have worked in 6.5 and won't work in SQL Server 7.0.
I used the data migration attach and restore methods to add a database to a target server. I'm surprised to find out that in both methods, the user names in the new database's sysusers table don't exist in master.sysxlogins. If I add these user names through the Security, New Login option, the system reports that the names already exist in the current database and that the names are updated to sysxlogins. Is this how SQL Server 7.0 handles the restore method? What's the best way to match sysxlogins and sysusers?
Run the stored procedure sp_change _users_login after the attach or restore (if the restore was from another server) to clean up the login mismatches you describe. Login mismatches always exist unless you use only Windows NT domain accounts and restore or attach the database to another server within the same domain trust structure (so that the NT SIDs are still recognized). SQL Server SIDs for SQL security logins are globally unique IDs (GUIDs), so they're unique each time they're created on each server. Thus, the SIDs won't be the same when you create a name on Server A and then create a name again on Server B. Local NT accounts have a similar problem except that their SIDs come from NT.
I need support for the Euro symbol, which is not available in code page CP850 in SQL Server 6.5. If I install SQL Server 7.0 with code page ISO1252, does the SQL Server Upgrade Wizard convert extended characters in SQL Server 6.5 to the same characters in 7.0 during the upgrade process?
The version upgrade does not convert data; it converts meta data, which is scripted into Unicode files and then passed in. The upgrade bulk-moves data but doesn't translate it. So, use Data Transformation Services (DTS) or a bulk copy program (bcp) to preserve your extended character data if you change code pages.
Should I back up the SQL Server 6.5 Registry before upgrading to SQL Server 7.0? And can I restore SQL Server 6.5 if I have the SQL Server directories, devices, and a Windows NT Registry?
You don't need to manually back up the SQL Server 6.5 Registry before upgrading, because SQL Server 7.0 backs up the SQL Server 6.5 Registry for you. To restore SQL Server 6.5, SQL Server 7.0 includes a menu item to switch back to SQL Server 6.5.
To be very careful, back up HKLM/Software /Microsoft/MSSQLServer, HKLM/System/Current ControlSet/Services/MSSQLServer, SQLExecutive, and MSDTC. And, back up the \mssql directory and all data files.
I recently upgraded from SQL Server 6.5 to 7.0. My system includes a shared log and a 15GB database. When I use the default options in the Upgrade Wizard, the SQL Server 7.0 log file is 15GB, but the data is only about 3GB. Can I shrink or recreate the log file to a manageable size?
Yes, you can use the Database Consistency Checker (DBCC) shrinkfile command to shrink a file. The DBCC shrinks a file to a specified level. You might also want to use the DBCC shrink database command, which has a slightly different functionality. Also, look up DBCC in Books Online (BOL) for more information on how to shrink files to a manageable size.
Using ADO to access SQL Server from Visual Basic (VB) 6.0, I created one connection and recordset, and command objects. I want to execute two stored procedures inside a loop by passing parameter values. Both stored procedures return records, but when I set the second stored procedure, the parameter collection is missing and the following error appears:
3265 - ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application.
I don't use two command objects on the same connection because this approach generates an error. How can I execute these stored procedures with one connection and one command object?
You can use the ADO command object that Listing 1 shows to execute two stored procedures. To do so, change the value of the CmdText property, delete the parameters for the first stored procedure, then append new parameters for the second stored procedure. You can use one ADO connection object as the active connection for more than one command object. Also, you can use two command objects as an alternative to changing the CmdText and parameters of a single command object.
