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

Batch Jobs

Is there a forum, documentation, link, or anything that talks about batch jobs, what they are, how to create them, run them, etc? Basically, everything I need to know about batch jobs?

thx,

Kat

What do you mean by batch jobs ? Do you mean Jobs that are executed by SQL Server Agent ? If so, I would recommend to get a book about SQL Server administration which dives deeper into the sense and the functionality of SQL Server Agent.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens,

I've been working with version 2000. Running a batch job to me is different for instance than running a proc (I think)... typically a batch job runs overnight because it takes a long time. I'm not sure what the definition of a batch job is. I believe it is a script that is run that takes a long time. Any ideas?

Kat

|||

Hi there - Whenever most DBAs talk about "batch" they usually just mean a set of SQL statements. If you save these in a file, they are also called a "script". If you run these scripts automatically, either using the SQL Server Agent (in what is called a "job") it's called a "batch job". To be even more confusing, some DBAs refer to a transaction (which is one statement alone or separated with a GO) to mean "batch".

Tell me what you're having issues with and I'll try to help.

Buck Woody

http://www.buckwoody.com

|||

Hi Buck,

This was just a question of definition and you explained it perfectly, thanks much!

Kat

|||If you don't need anything else on this thread, feel free to mark the answer as complete.

Batch Jobs

Is there a forum, documentation, link, or anything that talks about batch jobs, what they are, how to create them, run them, etc? Basically, everything I need to know about batch jobs?

thx,

Kat

What do you mean by batch jobs ? Do you mean Jobs that are executed by SQL Server Agent ? If so, I would recommend to get a book about SQL Server administration which dives deeper into the sense and the functionality of SQL Server Agent.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens,

I've been working with version 2000. Running a batch job to me is different for instance than running a proc (I think)... typically a batch job runs overnight because it takes a long time. I'm not sure what the definition of a batch job is. I believe it is a script that is run that takes a long time. Any ideas?

Kat

|||

Hi there - Whenever most DBAs talk about "batch" they usually just mean a set of SQL statements. If you save these in a file, they are also called a "script". If you run these scripts automatically, either using the SQL Server Agent (in what is called a "job") it's called a "batch job". To be even more confusing, some DBAs refer to a transaction (which is one statement alone or separated with a GO) to mean "batch".

Tell me what you're having issues with and I'll try to help.

Buck Woody

http://www.buckwoody.com

|||

Hi Buck,

This was just a question of definition and you explained it perfectly, thanks much!

Kat

|||If you don't need anything else on this thread, feel free to mark the answer as complete.

basic timestamp question.

Hello I have a very basic timestamp question. There is a data type in SQl Servertimestamp.

what I basically want is that when someone Inserts a data the timestamp column puts a timestamp there...how can i achieve that?...I thought that the timestamp type automatically does that

Yes you cannot insert into a timestamp column. SQL Server does that for you.

|||

If it puts a time stamp then instead of seeing some date when I open the table and look under the timestamp column I see something like: 0x00000000E4EBF66E

what does that mean? why it is not putting date there?

|||

Thats the hex value of the date/time value. Its very specific. I forget what measure of time it measures to, but its very specific.

Tim

|||

sahajMarg:

If it puts a time stamp then instead of seeing some date when I open the table and look under the timestamp column I see something like: 0x00000000E4EBF66E

what does that mean? why it is not putting date there?

Yes its a binary number assigned by SQL Server. you should read up books online for timestamp columns to understand where/how it can be used.

|||

sahajMarg:

If it puts a time stamp then instead of seeing some date when I open the table and look under the timestamp column I see something like: 0x00000000E4EBF66E

what does that mean? why it is not putting date there?

Yes its a binary number assigned by SQL Server. you should read up books online for timestamp columns to understand where/how it can be used.

|||

Just read it the same post the it is the Hex value of date and time..is that correct?