I have a case sensitivity problem on a SQL Server 7.0 installation. It acts differently from the others. To define my problem in detail, to my knowledge SQL Server 6.5 and 7.0 is not case sensitive in a query such as:
select * from zipcode
However on the installation in question, I had to match the exact case for all table and field names. So to get any results, the query had to say:
select * from ZIPCODE
The table is defined as ZIPCODE. How do I fix this problem?
Run sp_helpsort on the server that's not working. One of the sort order options is case sensitivity. If it's enabled you have to reset the sort order. You need to rebuild your master database and reload all of your databases. Therefore, carefully choose character set and sort order when you begin installing SQL Server. Directions are in Books Online(BOL).
When I run distributed queries against a SQL 6.5 server I receive the error message: "Server: Msg 7353, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' supplied inconsistent meta data. An extra column was supplied during execution that was not found at compile time."
This error appears only when I directly reference the 6.5 object in the from clause of a query (e.g., select * from banjo.master.dbo.sysdatabases). However, I can remotely execute stored procedures from SQL Server 7.0 on SQL Server 6.5 without errors (e.g., execute banjo.master.dbo.sp_who).
I've upgraded the catalog stored procedures on SQL Server 6.5 using the mssql7\install\instcat.sql script recommended in BOL. Also, I’ve repeated the linked server setup using different OLE DB provider names with the same results each time. I'm running SQL Server 7.0 Enterprise Edition and have tried the process on SQL Server 6.5 ranging from Service Packs (SPs) 2 to 5a. What am I doing wrong?
I found your problem in a similar error message. And I suspect that because of SQL Server 6.5’s treatment of NULLable fixed-length columns, they were treated as variable length. If ANSI_PADDING was OFF when you created the table, you will see this behavior. The workaround is to create the table in SQL Server 6.5 with ANSI_PADDING ON.
I’m working on a project that lets users store text, Word documents, and JPEG files in a database. Can I use the image datatype to handle this and how do I initialize an image datatype field? I tried the different forms of the updatetext command, and they didn’t work. I know I can bulk copy program (bcp) data into image datatype fields, but can I export them? Also, can I replicate changes made to the image column from one database to another on the same server?
Yes, the image datatype is the appropriate type in SQL Server. For the first insert of an image datatype, use the writetext T-SQL statement. For an example VC++ import/export program, look at the textcopy program in BOL. Replication works with either transactional, snapshot, or merge. Replication won’t work when you immediately update subscribers. Text and image operations are not logged by default, so make sure you use the WITH LOG option if you want to use transactional replication. Transactional replication depends on the ability of the Log reader agent to reading the SQL Server log to find changes and replicate them.
I installed the evaluation copy of SQL Server 7.0 on my laptop, an IBM ThinkPad 770x running NT Server 4.0 (SP4). When I tried to upgrade my install to a full version of SQL Server 7.0 Standard Edition, I received the following message, "You have not selected any components to install. Please select the components you wish to install." I receive this message when I try to install any components as if the install routine is not detecting any components even though all the check boxes are checked.
When I try to uninstall the evaluation copy, I get the message, "unInstallShield has detected that 'D:\MSSQL7\BINN\sqlmanr.exe' is in use. Please close the application using this file and launch unInstallShield again." This message appears after I have stopped all the related SQL Server services I know of, and I am able to remove OLAP with no problem.
I tried manually deleting of the program (i.e., purging the application and services entries in the Registry and then deleting the program files). I can’t delete sqlmanr.exe because of an access violation. I haven’t found anything in TechNet that addresses this issue. What am I missing here?
The SQL Service Manager (the stoplight in your taskbar) is still running. Close the application by right-clicking it, select exit, then uninstall the evaluation copy. Install the full product. Then use sp_attachdb to attach the data files to your full product edition.
Our SQL Server 6.5 is set at sort order = 'nocasepref,' and character set = 'cp437’. When I install SQL Server 7.0, the program automatically uses the default setting for both the sort order and character set. Should I change the default setting to the above setting during the SQL Server 7.0 installation? Will the default setting change during the upgrade if I use the upgrade wizard, or can I convert the sort order and character set in SQL Server 6.5 to SQL Server 7.0’s default?
Set up SQL Server 7.0 using the same code page for the easiest upgrade. The wizard won't take care of any code page translations for you. However, it would be a good idea to change your code page and sort order to the defaults. Script out your DDL with SQL Server 6.5 Enterprise Manager (Object -> Generate SQL Scripts…), create all objects on the SQL Server 7.0 server, and then use DTS to move the data over. Test carefully before you put this solution into production if you are planning to do a data transfer.
When I used the Upgrade wizard to upgrade a SQL Server 6.5 (all objects) to SQL Server 7.0, everything seemed to go well. But when I use select TOP 10 * from users to query the first 10 records, error message, "Server: Msg 170, Level 15, State 1, Line 1: Incorrect syntax near '10'" appears. If I generate the create table script from the upgraded table and use insert into newtable select * from users to another new database, TOP 10 works. What's happening here?
After you upgrade a SQL Server 6.5 database to SQL Server 7.0, the database is still in 6.5 compatibility mode. Run sp_dbcmptlevel <dbname>,70 to put the database into 7.0mode so that you can use the SQL Server 7.0 syntax to run queries.
Our applications do not use BEGIN TRANSACTION and COMMIT TRANSACTION for DML statements. Will this situation affect SQL Server automatic recovery processing when the system fails? Does SQL Server use implicit BEGIN TRANSACTION and COMMIT TRANSACTION as the default? How does explicit BEGIN TRANSACTION and COMMIT TRANSACTION affect the transaction log?
All single operations are transactions, so your situation won’t affect recovery. Don't do an implicit BEGIN TRANSACTION by default, but you can turn that option on (set implicit_transactions on|off). Every statement within a transaction will be an autonomous unit when wrapped in an explicit transaction. For more information on transactions, look atthe transactions chapter in the book Sam's Teach Yourself MS SQL Server 7.0 in 21 Days by Richard Waymire.
What is the best method for upgrading SQL Server 6.5 to SQL Server 7.0 on a 7 x 24 production server?
Use the SQL Server Upgrade wizard to migrate from SQL Server 6.5 to 7.0 to be reasonably guaranteed of success. There's no way around taking the downtime. Please look at http://www.microsoft.com/ sql/70/whpprs/upgrade.htm for more information about upgrading from SQL Server 6.5 to SQL Server 7.0. Also see the migration white paper at http://www.microsoft.com/sql/70/ whpprs/upgrade.htm.
The SQL Server 7.0 Upgrade wizard features an Exhaustive Data Integrity Verification routine that uses checksum values to validate data. What is a checksum value? Is it calculated at run time, or is the value stored with the row information on each page? How is the checksum value calculated on SQL Server 6.5?
The checksum is calculated by taking each byte of data in a field and adding its byte value for each column and row of an entire table to come up with a single number that represents that field and a single value on a per-table basis. We build the Use the calculation \[Please say in whfor both the SQL Server 6.x and 7.0 sides. If these numbers match on both SQL Server 6.5 and 7.0, it is unlikely that corruption occurred during the transfer of data during the upgrade. However, this isn’t guaranteed.
I operate a 70GB database and two 20GB databases on SQL Server 7.0. Where can I find a backup hardware that will let me back up to tape and store the data offsite? My databases are mainly static and updated once a month.
See the whitepapers on the Microsoft Web site (http://www.microsoft.com/ sql/70/gen/ perform.htm#backup) with information about Compaq tapes, which have worked well. HP also has some tape drives that work nicely. So far Microsoft hasn’t found any tapes that run faster than we can drive them. For more information, see the whitepapers at http://www.microsoft.com/sql/ 70/whpprs/hpbenchmark.htm and http://www.microsoft.com/sql/ 70/whpprs/highperf.htm.
I'm analyzing the best way to upgrade from Access 97 to SQL Server 7.0. As a first step, my idea is to keep the front end in Access and migrate the minimal logic (and data via the upsizing tool) that I need to SQL Server 7.0 to decrease implementation time. Instead of going from Access 97 to SQL Server 7.0 will it be feasible or painless to upgrade from Access 97 to Access 2000 and then to SQL Server 7.0? Where can I find good information about dimensioning the project to upgrade from Access 97 to SQL Server 7.0?
Upgrade directly to SQL Server 7.0 if that's your goal anyway. Remember, you can still use Access (particularly Access 2000, which speaks OLD DB directly to SLQ Server) as a great front end to SQL Server. For more information, look at http://www.microsoft.com/sql/ 70/whpprs/migaccwp.htm.
