BCP out and headers

I'm no dummy when it comes to this stuff but for the life of me I can't figure out how (if it is possible) to get column headers to go when exporting data. Here is the syntax I'm using:

BCP "select ATSCLAIMNUMBER, ALTERNATECLAIMNUMBER, LNAME , FNAME, MNAME, convert(varchar(10),LOSSDATE,101), convert(varchar(10),ERNOTIFIEDDATE,101), convert(varchar(10),CLOSINGDATE,101), STATUSCODE, INDPAID,MEDPAID,REHABPAID,EXPPAID,LEGALPAID,TOTALP AID,INDFUTURERES,MEDFUTURERES,REHABFUTURERES,EXPFU TURERES,LEGALFUTURERES,TOTALFUTURERES,EXCESSRECOVE RY1,EXCESSRECOVERY2,EXCESSRECOVERY3,EXCESSRECOVERY 4,EXCESSRECOVERY5,EXCESSRECOVERY,OTHERRECOVERY1,OT HERRECOVERY2,OTHERRECOVERY3,OTHERRECOVERY4,OTHERRE COVERY5,OTHERRECOVERY FROM ##MMAACTUARY" queryout C:\mma03182004.TXT /c /t, /r \n /U demo /P demo /S ATSDEV\ATS2K

It spits out the file fine. This is all built with dynamic SQL and needs to email the file which is does perfectly. My only problem is that there are no headers which I can't have. Any suggestions?How about using osql with a "-s," parameter? Might want to add a -n -w 5000 also.|||I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott|||It's not what I imagined but it does look like it would work. I really don't care at this point how it happens so long as it does! I'll try this and report back with the results.|||Originally posted by hmscott
I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott

Hey, thnaks fo rremebering...and you know what?

bcp -c

and, what's not elegant about it?

I use Union ALL btw

AND to make sure the rows come out correctly...

For fixed width:

SELECT Col1,Col2,Col3 FROM (
SELECT 'Heading1' AS Col1
, 'Heading2' AS Col2
, 'Heading3' AS Col3
, 1 AS RowOrder
UNION ALL
SELECT Col1
, CONVERT(varchar(25), myDatetimeCol99)
, CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

For Delimeted:

SELECT datarow FROM (
SELECT 'Heading1,Heading2,Heading3' AS Datarow
, 1 AS Roworder
UNION ALL
SELECT Col1+','
+','CONVERT(varchar(25), myDatetimeCol99)
+','CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

And usually with delimeted, I wrap all the data in quotes

''''+ Col1 + ''''
','+ ''''+ Col2 + ''''|||Hey, and dig it, you can even add a trailer with audit counts...

Just make it datarow 3 and do a select count(*)...you could even sum amount if you want to go crazy...

AND you could add a row "type" to each one, to make it easier for extraction of the non data row

WHERE SUBSTRING(datarow,1,1) IN ('H','T')|||Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott|||Originally posted by hmscott
Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott

IMNSHO, I wouldn't be to quick to give up DTS. I don't think there is much you can do with BPC that you can't do with DTS. There is a bunch you can do with DTS that you can't do with BCP. There isn't an "ELEGANT" solution here just not as ugly.|||It does work but the string got too long. So, what si did was create a seondary table and inserted the header values. This table only has one row so when using that select statement unioned with the real select statement it works perfectly. If any one wants a copy of the procedure email me jfogel3@.msn.com|||Originally posted by hmscott
BTW, I changed jobs and have been thrown into what seems to be a real lion's den.


Sorry to hear that...

I avoid DTS for any production related issues...seen to many thing I couldn't explain...

Mostly with connections and changing them...Seems like it's lookin at an earlier version sometimes...no thanks...

And I love Nigels sig...

Cursors are useful if you don't know SQL
DTS can be used in a similar manner
Beer is not cold and fizzy

Or a paraphrase...since it seems like sqlteam is down...again....

Oh, and no apologies please....

(otherwise I'll have to do it all the time...)|||ooopps...here's the thread

They're back up...

and it's...

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BCP Error

Hi,
I'm getting the following error when I use BCP to import a tab
seperated file exported from Excel.
I hope someone can help me figure it out.
I'm using the following command:
c:\>bcp myComm.dbo.cmrc_products in test2.txt -n -T -Sbiff\sqlexpress
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP
data-fil
e
I think it has something to do with the column widths and datatypes of
the import, but I don't know. I've been staring at a command line for
so long, I can't see straight.
Thanks for the help.
Tom
i figured this one out myself after many cups of coffee...
Basically my datafile was FUBAR and I wasn't using the -c switch...
bcp is VERY touchy and gives no breaks at all. Attention to detail is
the order of the day when using this little proggie.
|||i figured this one out myself after many cups of coffee...
Basically my datafile was FUBAR and I wasn't using the -c switch...
bcp is VERY touchy and gives no breaks at all. Attention to detail is
the order of the day when using this little proggie.

Batch file and use of isql command

Hi,
I'm trying to figure out how to be able to run a batch
file, go to the database, find the current application
version of our product and based on that result it
returns, run a specific script. We have a table that
holds all of the builds/releases of the application that
have been applied against the database. Based on the
information there (the max build/release number), I want
it to execute a specific SQL script that will upgrade them
to the next build/release.
For example, we have a table called DBVersion with one
column in it (AppsVersion). In this table, it has rows
that are as follows:
8.0.100.0
8.0.101.0
8.0.102.0
We have two upgrade paths that we will support. In the
example above, we can upgrade a customer who are in
8.0.100.0 or 8.0.102.0 to the new release number
8.0.103.0. We have two upgrade scripts...one that holds
all db changes from 8.0.100.0 to 8.0.103.0 and one that
holds all db changes from 8.0.102.0 to 8.0.103.0. I want
to create a batch file that finds the version they are on
and run a specific script to upgrade them to the new
release.
Thank you in advance,
Dee
Hi, this line of SQL maybe helps you
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
greatings, Cesar
"Dee" wrote:

> Hi,
> I'm trying to figure out how to be able to run a batch
> file, go to the database, find the current application
> version of our product and based on that result it
> returns, run a specific script. We have a table that
> holds all of the builds/releases of the application that
> have been applied against the database. Based on the
> information there (the max build/release number), I want
> it to execute a specific SQL script that will upgrade them
> to the next build/release.
> For example, we have a table called DBVersion with one
> column in it (AppsVersion). In this table, it has rows
> that are as follows:
> 8.0.100.0
> 8.0.101.0
> 8.0.102.0
> We have two upgrade paths that we will support. In the
> example above, we can upgrade a customer who are in
> 8.0.100.0 or 8.0.102.0 to the new release number
> 8.0.103.0. We have two upgrade scripts...one that holds
> all db changes from 8.0.100.0 to 8.0.103.0 and one that
> holds all db changes from 8.0.102.0 to 8.0.103.0. I want
> to create a batch file that finds the version they are on
> and run a specific script to upgrade them to the new
> release.
> Thank you in advance,
> Dee
>
|||Hi Cesar,
Thank you for your response. Actually, the version that I
was implying was our own product's application version and
not SQL Server's.
Thanks again,
Dee
>--Original Message--
>Hi, this line of SQL maybe helps you
>SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'),[vbcol=seagreen]
>SERVERPROPERTY ('edition')
>greatings, Cesar
>"Dee" wrote:
that[vbcol=seagreen]
want[vbcol=seagreen]
them[vbcol=seagreen]
holds[vbcol=seagreen]
want[vbcol=seagreen]
on
>.
>

Batch file and use of isql command

Hi,
I'm trying to figure out how to be able to run a batch
file, go to the database, find the current application
version of our product and based on that result it
returns, run a specific script. We have a table that
holds all of the builds/releases of the application that
have been applied against the database. Based on the
information there (the max build/release number), I want
it to execute a specific SQL script that will upgrade them
to the next build/release.
For example, we have a table called DBVersion with one
column in it (AppsVersion). In this table, it has rows
that are as follows:
8.0.100.0
8.0.101.0
8.0.102.0
We have two upgrade paths that we will support. In the
example above, we can upgrade a customer who are in
8.0.100.0 or 8.0.102.0 to the new release number
8.0.103.0. We have two upgrade scripts...one that holds
all db changes from 8.0.100.0 to 8.0.103.0 and one that
holds all db changes from 8.0.102.0 to 8.0.103.0. I want
to create a batch file that finds the version they are on
and run a specific script to upgrade them to the new
release.
Thank you in advance,
DeeHi, this line of SQL maybe helps you
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
greatings, Cesar
"Dee" wrote:

> Hi,
> I'm trying to figure out how to be able to run a batch
> file, go to the database, find the current application
> version of our product and based on that result it
> returns, run a specific script. We have a table that
> holds all of the builds/releases of the application that
> have been applied against the database. Based on the
> information there (the max build/release number), I want
> it to execute a specific SQL script that will upgrade them
> to the next build/release.
> For example, we have a table called DBVersion with one
> column in it (AppsVersion). In this table, it has rows
> that are as follows:
> 8.0.100.0
> 8.0.101.0
> 8.0.102.0
> We have two upgrade paths that we will support. In the
> example above, we can upgrade a customer who are in
> 8.0.100.0 or 8.0.102.0 to the new release number
> 8.0.103.0. We have two upgrade scripts...one that holds
> all db changes from 8.0.100.0 to 8.0.103.0 and one that
> holds all db changes from 8.0.102.0 to 8.0.103.0. I want
> to create a batch file that finds the version they are on
> and run a specific script to upgrade them to the new
> release.
> Thank you in advance,
> Dee
>|||Hi Cesar,
Thank you for your response. Actually, the version that I
was implying was our own product's application version and
not SQL Server's.
Thanks again,
Dee
>--Original Message--
>Hi, this line of SQL maybe helps you
>SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'),
>SERVERPROPERTY ('edition')
>greatings, Cesar
>"Dee" wrote:
>
that[vbcol=seagreen]
want[vbcol=seagreen]
them[vbcol=seagreen]
holds[vbcol=seagreen]
want[vbcol=seagreen]
on[vbcol=seagreen]
>.
>

Batch file and use of isql command

Hi,
I'm trying to figure out how to be able to run a batch
file, go to the database, find the current application
version of our product and based on that result it
returns, run a specific script. We have a table that
holds all of the builds/releases of the application that
have been applied against the database. Based on the
information there (the max build/release number), I want
it to execute a specific SQL script that will upgrade them
to the next build/release.
For example, we have a table called DBVersion with one
column in it (AppsVersion). In this table, it has rows
that are as follows:
8.0.100.0
8.0.101.0
8.0.102.0
We have two upgrade paths that we will support. In the
example above, we can upgrade a customer who are in
8.0.100.0 or 8.0.102.0 to the new release number
8.0.103.0. We have two upgrade scripts...one that holds
all db changes from 8.0.100.0 to 8.0.103.0 and one that
holds all db changes from 8.0.102.0 to 8.0.103.0. I want
to create a batch file that finds the version they are on
and run a specific script to upgrade them to the new
release.
Thank you in advance,
DeeHi, this line of SQL maybe helps you
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
greatings, Cesar
"Dee" wrote:
> Hi,
> I'm trying to figure out how to be able to run a batch
> file, go to the database, find the current application
> version of our product and based on that result it
> returns, run a specific script. We have a table that
> holds all of the builds/releases of the application that
> have been applied against the database. Based on the
> information there (the max build/release number), I want
> it to execute a specific SQL script that will upgrade them
> to the next build/release.
> For example, we have a table called DBVersion with one
> column in it (AppsVersion). In this table, it has rows
> that are as follows:
> 8.0.100.0
> 8.0.101.0
> 8.0.102.0
> We have two upgrade paths that we will support. In the
> example above, we can upgrade a customer who are in
> 8.0.100.0 or 8.0.102.0 to the new release number
> 8.0.103.0. We have two upgrade scripts...one that holds
> all db changes from 8.0.100.0 to 8.0.103.0 and one that
> holds all db changes from 8.0.102.0 to 8.0.103.0. I want
> to create a batch file that finds the version they are on
> and run a specific script to upgrade them to the new
> release.
> Thank you in advance,
> Dee
>|||Hi Cesar,
Thank you for your response. Actually, the version that I
was implying was our own product's application version and
not SQL Server's.
Thanks again,
Dee
>--Original Message--
>Hi, this line of SQL maybe helps you
>SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'),
>SERVERPROPERTY ('edition')
>greatings, Cesar
>"Dee" wrote:
>> Hi,
>> I'm trying to figure out how to be able to run a batch
>> file, go to the database, find the current application
>> version of our product and based on that result it
>> returns, run a specific script. We have a table that
>> holds all of the builds/releases of the application
that
>> have been applied against the database. Based on the
>> information there (the max build/release number), I
want
>> it to execute a specific SQL script that will upgrade
them
>> to the next build/release.
>> For example, we have a table called DBVersion with one
>> column in it (AppsVersion). In this table, it has rows
>> that are as follows:
>> 8.0.100.0
>> 8.0.101.0
>> 8.0.102.0
>> We have two upgrade paths that we will support. In the
>> example above, we can upgrade a customer who are in
>> 8.0.100.0 or 8.0.102.0 to the new release number
>> 8.0.103.0. We have two upgrade scripts...one that
holds
>> all db changes from 8.0.100.0 to 8.0.103.0 and one that
>> holds all db changes from 8.0.102.0 to 8.0.103.0. I
want
>> to create a batch file that finds the version they are
on
>> and run a specific script to upgrade them to the new
>> release.
>> Thank you in advance,
>> Dee
>.
>