How to use wildcards to search TEXT columns in SQL Server?

A colleague using the Informix database needs to use wildcards to search for the TEXT column. Although Informix supports wildcards in the LIKE and MATCH declarations, this does not include TEXT columns. The solution for outputting data to SQL Server obviously supports this search.

To complete the search, my colleague must have Informix output useful data to a text file. Then, an SQL Server DTS package inputs TEXT files into a local instance of SQL Server, and then processes the TEXT column with wildcards.

Unfortunately, there are two other problems: 1) the connection is slow and the connection is often interrupted before the input is complete. 2) if you decide to cancel the operation halfway, SQL Server will discard all actions that have been processed. He must come up with a way to submit every n records and continue the operation.

In order to find a solution for him, I will wander around in the DTS wizard. The solution has been built in DTS.

To illustrate how to operate, I create a DTS package and output the Northwind MERs database to a text file. Then, I created a new package to input the text file to a Northwind copy called Northwind_New. In the DTS wizard, The result 1 is displayed. Now, right-click the pipe and select properties. The "convert data task attributes" dialog box is displayed. The tag to be used is option Options), as shown in figure 2.

The options to be used are "always submit the last batch" and "insert batch size ". The first option does not need to be described; the last option controls the number of rows inserted into the batch. You can adjust it to meet your requirements and application situations. The default value is 0, which is what my colleagues experienced. If a fault occurs, the entire batch is lost. Set 1 to force commit after each insert. If 100 or 1000 is set, the request is submitted every 100 or 1000 rows.

Now my colleague has solved the problem. He is happy to search the TEXT bar with wildcards.

Related Articles]

• How to iterate SQL Server data tables and databases

• SQL Server automatically analyzes access history logs and provides optimization policies

• SQL Server 2005 XML support and Exception Handling