Answers from Microsoft - 28 Feb 2000

I've been trying to install SQL Server Desktop Edition on a Windows 98 machine, but I get errors such as Can't make a connection. Do I need a network adapter for SQL Server 7.0 Desktop to run? What networking protocol do I need to install?

You don't need a network adapter, but you do need to install the Client for Microsoft Networks under Control Panel, Network. I don't think it matters which protocol you install, but NetBEUI is easy because it doesn't require any configuration. On my Win98 machine at home, I have SQL Server 7.0 running with a dial-up adapter, the Client for Microsoft Networks, and TCP/IP.

In SQL Server 7.0, can I create user-defined functions, similar to the user-defined data types?

No. In SQL Server 7.0, the closest you can come to user-defined data types is to use stored procedures. However, Microsoft has added user-defined functions in SQL Server 2000.

I have SQL Server 7.0 and the SQL Server Agent running on Windows 95. When I try to create a file Data Source Name (DSN) in a Windows NT Server, I can't connect to this SQL database. Does SQL Server need to run on a server machine for other applications using DSNs to access it?

No. You probably still have Named Pipes as the default network library on your NT box. Make sure your machine is set to use TCP/IP Sockets as the default (configure in the Client Network Utility), or add a specific entry for your Win95 box as an alias.

What is the procedure to store images in SQL Server 7.0, and how do you retrieve the images?

You can use batch programs to load images or use the WRITETEXT and UPDATETEXT Transact-SQL (T-SQL) statements to insert them into SQL Server. SQL Server Books Online (BOL) includes code samples with the references for those statements.

How can I store and retrieve images in SQL Server by using Visual Basic (VB) as a front end with ADO as the methodology?

Put the images in a folder on Windows NT, then store the image name in SQL Server. The path to the folder is stored globally, so you can change it. You can also use DFS in NT 4.0 or Windows 2000 (Win2K) to share the images and store backups of them. To store an image in the database, use VB to read the image file bit by bit and store it. Then reverse the process to retrieve it. (For information about working with binary objects in ADO, see "Who's Afraid of the Big, Bad BLOB?" April 1999.)

I sometimes see the message Ec Iterator cleaned up a reference in the SQL Server error log. Microsoft product support says it's a bug that generally occurs when SQL Server uses the sysprocesses table. I searched all the stored procedures and triggers and changed some that use sysprocesses. But the message still appears. I looked in syscomments and didn't find anything that uses sysprocesses. Is there any other reason for that message? If not, how can I find out which objects (e.g., stored procedures, triggers) use sysprocesses?

This is a bug that Microsoft has fixed in SQL Server 2000. The error message means that an internal error occurred and SQL Server caught and corrected it. SQL Server uses sysprocesses frequently; it's a table representation of several internal data structures. So you won't be able to stop SQL Server from using this table, nor can you prevent this error message from appearing. For reference, this bug is documented in Microsoft Knowledge Base article Q198030.

How can I combine the text strings on two columns into one? I want the text of the two columns of a particular table to be joined in a view and to appear as one column in the result set.

The SQL command

SELECT cola + colb FROM table

will combine the text strings. Many tools have limits that you can configure for the maximum column size returned; make sure you modify that limit so that you can see the results from both columns together. You can also research the option Concat_Null_ Yields_Null in BOL; this option affects the results if one of these columns is null. If you know there will be nulls, you might want to modify the above query to look like this:

SELECT isnull(cola,") + isnull(colb,") FROM table

I want to direct query results to a file for later use in Web application static menus. Is there an easy way to direct SQL query output to a text file?

You can easily direct SQL query output to a text file by using the osql.exe program and specifying the -o parameter. For instance, to run SELECT * FROM authors and send the results to a text file, you could run:

osql -Q"SELECT * FROM authors" -o c:authors.txt -dpubs -E

Can a user turn case sensitivity off or on for one database, or does the case sensitivity apply for all the databases in the SQL Server installation? If the case sensitivity is serverwide, can you turn it on or off without reinstalling SQL Server?

Case sensitivity is set serverwide in SQL Server 7.0. However, you can specify collations down to the column level in SQL Server 2000. You can reset the sort order and character set of SQL Server with the Rebuild Master utility (rebuildm.exe), but then you need to rebuild your databases and reload all the data in all your databases. You can't simply do a restore from a backup.

I'm running SQL Server 7.0 on a Windows NT 4.0 network. I use Access 2000 as a user interface and I link tables across the network from SQL Server to Access through an ODBC connection. I used a shared file Data Source Name (DSN) to set up the drivers. Whenever I run an open table from Access, the table screen opens but the data doesn't come across, and then Access stops responding.

The problem is probably with Microsoft Data Access Components (MDAC) on the machine with Access 2000. Get the latest version of MDAC from http://www.microsoft.com/data, and apply it to the client computer.

In a data warehouse application, I have several DTS packages that use ExecSQL Tasks. They ignore any On Failure constraints (an ActiveX script executes if a ExecSQL Task fails). But even if the SQL task returns errors or uses raiserror, the ActiveX script doesn't execute. Can I use a return code (or set a global variable) to allow conditional execution of a subsequent task? I can't find any solution to this problem, short of executing all SQL tasks in an ActiveX task, which defeats the purpose of having an ExecSQL Task.

A bug in SQL Server 7.0 (RTM and Service Pack 1—SP1) makes SQL Server lose errors in ExecSQL Tasks, so if the first script that executes in the task works, then subsequent raiserrors will fail. You can call Microsoft product support for a hot-fix. This bug is fixed in SP2 (which was in beta at press time).

In SQL Server 6.5, when I run a query that converts the number -1 to a decimal and multiplies it by 0, why do I get -0 as an answer instead of 0?

When you run that particular query, a problem in SQL Server 6.5 and 7.0 causes the result to be returned with a negative sign preceding it. Microsoft fixed the problem in SQL Server 2000, so such queries will return 0 instead of -0.