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.