Answers from Microsoft - 22 Mar 2000

I'm testing SQL Server 7.0's merge replication feature with ODBC, but where I use the SQL statement SELECT *, I get the error message Invalid procedure called with no error number displayed. When I remove the asterisk and specify the column names, the merge replication works perfectly. I don't have problems using SELECT * from a table through the Query Analyzer. However, I have problems when SELECT * is part of the view in which I use unions for reporting or when SELECT * is part of the INSERT statement called through a stored procedure.

Most likely, the SELECT INTO statement that is happening behind the scenes is causing your problem. For example, let's say that you start with two identical tables, T1 and T2, each of which has three columns. When you publish table T1, that table gets one extra column: rowguid. When you subsequently do an INSERT T2 SELECT * FROM T1, the action will fail because you have incompatible column definitions in the SELECT statement (i.e., too many columns in T1). You need to explicitly list the columns in your SELECT statement to avoid this problem.

How can I write a query that will list all the software installed on 10 PCs, including the latest version and the latest date (dd/mm/yyyy) for each computer name? The columns involved are Computer, Software, Version, Datetime, and ErrorMessageNo. For example, with this data:

Cmp01, Word, 97, 1/3/1999, 1Cmp02, Word, 97, 2/4/1999, 1Cmp01, Word, 2000, 8/9/1999, 2Cmp01, Word, 2000, 10/9/1999, 1

the result should be:

Cmp01, Word, 2000, 10/9/1999, 1Cmp02, Word, 97, 2/4/1999, 1

The code in Listing 1 (or a variation) will work.

I use a SQL script with a third-party application to extract data from a SQL Server database. The script extracts the data, places it into an outfile (text), then FTPs it to a physical file on an AS/400. But when a user presses Enter to start a new line in an entry field, an end-of-record marker is put into the SQL Server database. When I extract the data and place it in a text file for transfer, the script has split the record into two lines. How can I find and replace these end-of-record characters before I FTP the file? Can I program a search for the carriage return/line feeds (CRLFs) in SQL Server 6.5, or do I need to use a Data Transformation Services (DTS) transformation? What characters am I searching for?

CRLFs are a carriage return (Char(13)) plus a line feed (Char(10)). You can use a DTS transformation to find and replace these characters, but you'll have to programmatically search for the CRLFs and replace them with either a space or a blank character (' '). Why not use DTS instead of SQL scripts? DTS can get the data out of SQL and into text, perform operations on the data, then FTP the data. (For information about customizing DTS, see "Constructing DTS Custom Tasks," September 1999.) You can perform this task with T-SQL, but a DTS transformation is easier, and any search-and-replace program will work.

Every day, I transfer four tables from a remote SQL Server in Arizona to a local SQL Server in New Mexico. Sometimes, the tables from the remote SQL Server system don't contain data, so I check the record count before I transfer the tables. Managing these tables on a local SQL Server is easier than using another connection object in my Active Server Pages (ASP) code, and I'd like to automate this transfer instead of doing it manually each day. Can I use a stored procedure to do this job?

You can use linked servers to set up a stored procedure to automate the transfer. Here's the code:

USE PubsCREATE PROC mytransferprocASIF (SELECT COUNT(*) FROM NewMexico.pubs.dbo.authors) > 1 INSERT myLocalAuthors SELECT * FROM NewMexico.pubs.dbo .authors...RETURN

Even better, you can create a DTS package to perform the transfer. Use the Export/Import Wizard to build a template, then use the DTS designer environment to add any validation checking you want. After that, simply schedule the transfer with the SQL Server Agent service. Setting up the DTS automation takes a little more time, but this method saves you time in the long run.

In SQL Server 6.5, I'm trying to create a table with several records that have only two columns: iincidentid and worknotetext. I'm populating this table with data from another table by selecting incidentid into iincidentid and vchWorknote1..vchworknote20 (varchar(255)) into worknotetext(text). I've followed examples from SQL Server Books Online (BOL) and other resources, but they updated only the last record.

You can use updatetext to update all the records. For example, Listing 2 contains a combination of code and pseudocode that loops through the old table, then loops through the 20 columns of the new table, adding each one with updatetext. The code isn't complete, but it will get you started.

If you run the following query in SQL Server 7.0

SELECT Len(' ')

the result is 0 (zero), but if you run the same query in SQL Server 6.5, the result is 1. Which answer is correct, and why does this happen?

The len function returns the length of a string, excluding trailing blanks. Because all blanks in this string are trailing, SQL Server eliminates them (per the SQL Server 7.0 documentation for this function—Microsoft didn't document or support len in SQL Server 6.5) and correctly returns the length as 0. BOL says len "returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks." Note that SQL Server 7.0 properly supports empty strings, whereas SQL Server 6.5 typically references empty strings as a single blank character.