Q: SQL Server 6.5 Service Pack 4 (SP4) won't install on my system. I downloaded it three times, so I know the problem is not a corrupt file. Do you have a solution?
Here's what Neil Pike, a Microsoft Valuable Professional (MVP), had to say about a similar problem: "SP4 wouldn't install on my main machine, so I hacked it in. If you connect to SQL Server using the computer name or no name (if you connect via ISQL), certain machines take 30 seconds to connect. This delay is what causes the service pack installation to fail. SP4 waits only 2 seconds before aborting the install. However, if you specify a period as your server name, you immediately connect. I'm 'fixing' the setup.inf file."
Until the setup.inf file is fixed, Neil offers this command as a workaround:
setup /t Local = Yes
To fix the problem at its source, look for
set !<server name> = $(!<computer name>)
in the setup.inf file and add the following lines after it:
ifstr(i) $(!Local) == "Yes" set !<server name> = "."
endif
Q: What is the best way to control or prevent runaway queries? Users can access my database using any Open Database Connectivity (ODBC)-compliant application. I'm concerned that a user will run a query that negatively affects server performance. How can I prevent users from running a query that takes an hour to complete?
We receive many questions like this one; unfortunately, there's not a good solution. You have to rely on tools and front-end applications that let you set limits. Report environments that don't have administrator modules with access control lists (ACLs) suffer from this potentially disruptive flaw. However, SQL Server 7.0 offers a query governor tool, which lets you set a cost threshold to prevent queries from running too long.
Q: We're using text fields on a new project, and SQL Server truncates the text strings at 255 characters as we insert them. During development, we inserted a Visual Basic (VB) application text string. We then used SQLTrace to verify that the system passed all the data from the VB application to SQL Server, so we know the problem isn't there. Do you know what's happening?
Most likely, the data you're passing to SQL Server is completely stored. The problem is that ISQL/w truncates text column values at 255 characters no matter what you set the TEXTSIZE parameter to. The easiest way to verify that SQL Server is storing all your data is to use the DATALENGTH function to return the length of the expression being stored. You can use DATALENGTH to determine the length of an expression of any data type, but the function is especially useful with varchar, varbinary, text, and image data types, which can store variable-length data. The DATALENGTH of any null data always returns NULL. Figure 1, page 198, shows an example of using the DATALENGTH function to determine the length of a text column. SQL Server 7.0 lets you use longer text data: variable-length, non-Unicode data with a maximum length of 231-1*(2,147,483,647) characters.
Q: How do I submit a date query and suppress the time value that the system outputs with the date query results?
SQL Server Books Online (BOL) states that the CONVERT function not only converts an expression of one data type to another, but also lets SQL Server use a variety of date formats to present date and time data. By default, SQL Server uses the date format of the users' language to display date and time values. For example, a SQL login using the default us_english results in the query
SELECT getdate()
and results in the system output
Oct 31 1998 4:27PM
However, you can change the display mask for date and time values. For example,
SELECT CONVERT(char(20), getdate(), 1)
results in the output
10/31/98
Date and time style 1 tells SQL Server to throw out the time portion of getdate() and display only the date portion. Table 1 shows display masks you can use with the CONVERT function.
Understand that the CONVERT function is not an ANSI standard keyword. ANSI uses the keyword CAST to convert data types, such as changing a character data type to an integer data type. SQL Server 7.0 supports the CAST keyword (as SQL-92 specifies), and we encourage you to use it for data type conversion because you should follow the ANSI standard whenever possible. However, CAST doesn't include a display mask capability for date and time presentation, so you'll still need to use the CONVERT function in certain situations. The syntax for these two commands differs. CAST's syntax is
CAST(expression AS data_type)
CONVERT's syntax is
CONVERT (data_type\[(length)\], expression \[, style\])
Q: How can I delete data in a SQL Server table that FOREIGN KEY constraints reference?
Usually you don't want to lose data in the master table if a FOREIGN KEY references that data. Thus, SQL Server's default behavior is to prevent truncation or other forms of deletion to avoid orphan data. However, during database operations, database administrators (DBAs) sometimes need to temporarily disable the referential integrity that FOREIGN KEYs maintain. As a result of this need, SQL Server lets you use the ALTER TABLE command to temporarily disable FOREIGN KEYs. Listing 1 shows an example command using simplified versions of the stores and sales tables from the PUBS database.
As Listing 1 shows, the sales table contains the stor_id column, which is a FOREIGN KEY to the stores table. You would expect the following command to fail under normal circumstances:
DELETE FROM stores
The DELETE statement conflicts with COLUMN REFERENCE constraint FK_stor_id. The conflict occurs in the tempdb database, sales table, and stor_id column. Thus, SQL Server aborts the command.
However, SQL Server lets the DELETE command run if you temporarily disable the FOREIGN KEY in the stor_id column in the sales table before issuing the DELETE command:
ALTER TABLE sales
NOCHECK CONSTRAINT FK_stor_id
During DBA data-cleansing tasks like these, you might want to use the TRUNCATE TABLE command. Using this command results in less logging and overhead than running a full DELETE command. However, SQL Server never lets you truncate a table's FOREIGN KEY constraints reference, even if you use NOCHECK to disable that constraint.
