Answers from Microsoft - 21 Sep 2000

Editor's Note: Send your SQL Server questions to Richard Waymire, program manager for SQL Server development at Microsoft, at [email protected]

I have SQL Server 6.5 Enterprise Edition. I loaded it onto my Windows NT Server 4.0 system and received a message that I need the NT Enterprise Edition. Is that true?

Yes. The Enterprise Edition of SQL Server 7.0 and SQL Server 6.5 require either NT 4.0 Enterprise Edition, Windows 2000 Advanced Server, or Win2K Datacenter Server to run.

The SQL Server 6.5 documentation states that a Win16 version of the software is available as a development version. I carefully followed the documentation directions to load the software on my laptop, but I keep getting a DB-Library error, Unable to connect to server or does not exist. I want to run this program on a laptop running Windows 98 that is not connected to a network but has a PCMCIA network card installed.

This error message means that the software couldn't find the server name on the network. If you're using Win98 without a network connection, make sure that you've installed the client for Microsoft networks in the Control Panel network and that you've installed at least one network protocol. Also, make sure that the default network library in the SQL Server Client Configuration Utility is set to TCP/IP sockets. In some cases, the default is set to named pipes, which won't work for SQL Server installed on a Win98 computer.

I back up my SQL Server 7.0 database's transaction log to the hard disk on my Windows NT server. The 200MB disk partition is getting full. The backup device name is trlog.bak, and I plan to do an NT native backup of the file trlog.bak to a tape, then delete the backup device file to make space for the transaction log backup. Will this backup approach pose any problems if I want to restore from the previous set of transactions?

This seems like a very reasonable plan, as long as you can get to the backup later if you need it.

I have about 80 tables that extensively use datetime fields. I want to change the datetime format for all these fields from the default MDY to DMY. I know that I can use the set dateformat function to set the date to a specified format when executing insert queries. How can I set the dateformat for the existing fields in these 80 tables in my database?

You need to set the format per session or per login by changing the dateformat in the syslanguages table. For information about changing the dateformat, see Kalen Delaney, Inside SQL Server, "Solving the Datetime Mystery," September 2000.

Will changing my existing SQL Server 6.5 databases to a SQL Server 7.0 compatibility level cause any data-integrity problems in my existing applications?

No. Changing the database compatibility level affects only how the T-SQL and associated queries behave; the change doesn't affect the underlying data storage or the database's data integrity. The reason the SQL Server development team built compatibility levels was to ensure that upgrades won't affect existing applications, so your applications should be fine when you run them in the previous version's compatibility level.

When I want to add an Oracle server to a local machine on which SQL Server is installed, the action fails. Here are the parameters for my machine:

server:\\bao\cjf(\\domain\machine name)
product name:oracle
data source:msdaora

local login:cjf(sa)
remote login:internal/oracle

Doesn't SQL Server support a linked server on a local machine?

As SQL Server Books Online (BOL) states, you can use the Microsoft OLE DB Provider for Oracle to query data in Oracle databases. BOL specifies several requirements for this provider to work. Review these guidelines to make sure you've met them. For example, the OLE DB Provider for Oracle requires Oracle Client Software Support File version or later and SQL*Net version BOL tells you how to create an SQL*Net alias name and create mappings from SQL Server logins to Oracle logins. BOL also gives you some guidelines for how to reference Oracle database instances and how to reference tables in an Oracle linked server. According to these guidelines, to set up your Oracle linked server, you'd run

exec sp_addlinkedserver 'localOracle', 'Oracle', 'MSDAORA', 'proview'  
--Thus, SQL Server would call the server localOracle and
  SQL*NET would reference the server as proview.
exec sp_addlinkedsrvlogin 'localOracle', false, 'sa', 'internal', 'oracle'
--Log in to SQL Server as sa, which will match to an internal account on Oracle.

Then, log in to SQL Server as the sa user, and run a distributed query that references

SELECT * FROM localOracle..Schema.Table

What's the best way to migrate a database from SQL Server 6.5 to SQL Server 7.0?

I recommend using the SQL Server Upgrade Wizard. You can also use Data Transformation Services (DTS), but that method won't bring across the entire environment, such as your settings in the master and msdb databases. You can retain these settings by using the Upgrade Wizard.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.