BCP call to stored procedure - broke during upgrade from SQL 7.0 to 2000

I have this stored procedure that takes a few parameters like date and
merchant ID, and basically goes through a set of if-then statements to build
a SQL SELECT string.

When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
worked from Query Analyzer, but not in BCP. It used to work in BCP just
fine with 7.0. The error I get now is:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
at least one column

What's really strange is, if I instruct the stored procedure to simply print
the SELECT string, then cut-and-paste it into the end of stored procedure
code (assigning it to the variable that already contains the SELECT string),
then it works from BCP.

Any help would be greatly appreciated.

AstonAston (alau@.selera.com) writes:
> I have this stored procedure that takes a few parameters like date and
> merchant ID, and basically goes through a set of if-then statements to
> build a SQL SELECT string.
> When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
> worked from Query Analyzer, but not in BCP. It used to work in BCP just
> fine with 7.0. The error I get now is:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
> at least one column
> What's really strange is, if I instruct the stored procedure to simply
> print the SELECT string, then cut-and-paste it into the end of stored
> procedure code (assigning it to the variable that already contains the
> SELECT string), then it works from BCP.

If I understand this right you are doing something like:

bcp "exec some_db..some_sp" queryout datafile.bcp -c -T

To find out what columns there are in the query, bcp first submits the
query with SET FMTONLY ON. This command is causes SQL Server to not execute
the statements in the procedure, but return data about any result sets
it finds. However, if you produces a dynamic SQL string and executes it,
there not be anyting executed with FMTONLY ON, and BCP will not find any
result set.

Why this worked in SQL 7, I don't know. (I never worked much with SQL 7,
jumped direct to SQL 2000 from 6.5.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp