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, 1
Cmp02, Word, 97, 2/4/1999, 1
Cmp01, Word, 2000, 8/9/1999, 2
Cmp01, Word, 2000, 10/9/1999, 1
the result should be:
Cmp01, Word, 2000, 10/9/1999, 1
Cmp02, 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.
Prev. page
[1]
2
next page 
