bcp with table name

HI,
i want to export data from a sql table to a csv file with a the name of each colunms in the csv file
i use this script

bcp [databasename.owner.table ]out [path.csv] -t, -c -Uuser -Ppass -Sservername

i want to put each column name in my csv file.

Sorry for my english, i'm not fluency in english

Thanks,
PatrickUse OSQL instead.|||Use DTS, you can export the data to a CSV file and I believe there is an option to include headers (column headers) into the CSV file.

BCP with ASCII

People,
can I export data using BCP to ASCII file?
I'm looking for but I did not found...
ThanksYou ought to get character data if you use the -c (must be lower case) BCP switch.

-PatP|||I did a BCP test with -c parameter but It isn't correct. How I discovery the caracter table of the file?

thanks,sql

bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.
Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:

> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.
sql

bcp utility

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

Thanks allYou can use the extended stored procedure xp_cmdshell to execute bcp from a
stored procedure. Please read in SQL Server Books Online the security
implications. Assuming you do not want to run it under an account that is
member of sysadmin, you may want to set up a proxy account via
sp_xp_cmdshell_proxy.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Mike (mckeyes@.gmail.com) writes:

Quote:

Originally Posted by

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

As Plamen said, you can use xp_cmdshell, but xp_cmdshell is a security
risk and for this reason it is disabled by default. It may be better
to write a small application VBscript or whatever you fancy to run
the export.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:
> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.sql

BCP truncation error on few tables?

Hi,
I'm using BCP to copy some data between 2 servers.
I'm using the -N option to export the tables into the Native Unicode format
this works fine
except for few tables where I receive the truncation error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
this process has been used for few month without any issue and now I start
to see this error.
any idea?
thanks for your quick guides!
jerome.
Hi
-N is keep non-text native and -n is native so you may want to try that
instead.
John
"Jeje" wrote:

> Hi,
> I'm using BCP to copy some data between 2 servers.
> I'm using the -N option to export the tables into the Native Unicode format
> this works fine
> except for few tables where I receive the truncation error:
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> this process has been used for few month without any issue and now I start
> to see this error.
> any idea?
> thanks for your quick guides!
> jerome.
>
|||the BOL says:
-N is native unicode format:
http://msdn2.microsoft.com/en-us/library/ms189941.aspx
the varchar and char are exported in Unicode and others are in their native
format.
we have solve the issue for few tables by using the -w instead of -N
but some tables continues to suffer the same issue!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...[vbcol=seagreen]
> Hi
> -N is keep non-text native and -n is native so you may want to try that
> instead.
> John
> "Jeje" wrote:
|||I would still try -n
John
"Jeje" wrote:
[vbcol=seagreen]
> the BOL says:
> -N is native unicode format:
> http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> the varchar and char are exported in Unicode and others are in their native
> format.
> we have solve the issue for few tables by using the -w instead of -N
> but some tables continues to suffer the same issue!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
|||I am having a similar problem.
When I use -N, I get this error:
SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
Client]String data, right truncation
When I use -n I get this error:
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
Client]Unexpected EOF encountered in BCP data-file
Any help will be appreciated.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> I would still try -n
> John
> "Jeje" wrote:
|||yes, same error here.
"Agho" <Agho@.discussions.microsoft.com> wrote in message
news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...[vbcol=seagreen]
> I am having a similar problem.
> When I use -N, I get this error:
> SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> Client]String data, right truncation
> When I use -n I get this error:
> SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> Client]Unexpected EOF encountered in BCP data-file
> Any help will be appreciated.
> Thanks
>
> "John Bell" wrote:
|||Hi
This issue could be cause be either a row or field terminator being present
withing your data or possibly a missing row terminator at the end of the
file. The latter should be easy to identify as only the last row may not be
imported. The easiest way to correct the former is to generate the data file
with specify terminators that you will know are not going to occur in the
data, then use the -r and -t option to specify the appropriate character when
importing the data with BCP.
HTH
John
"Jeje" wrote:
[vbcol=seagreen]
> yes, same error here.
>
> "Agho" <Agho@.discussions.microsoft.com> wrote in message
> news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
|||Can you please give an example of whay you want done? I don't quite
understand it. For now, I am using OPENDATASOURCE to insert the data in to
the desired table. This is taking about 3 minutes compared to 15 seconds when
I use BCP.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This issue could be cause be either a row or field terminator being present
> withing your data or possibly a missing row terminator at the end of the
> file. The latter should be easy to identify as only the last row may not be
> imported. The easiest way to correct the former is to generate the data file
> with specify terminators that you will know are not going to occur in the
> data, then use the -r and -t option to specify the appropriate character when
> importing the data with BCP.
> HTH
> John
>
> "Jeje" wrote:
|||Hi
Can you post DDL and (a small set of) example data that will fail.
John
"Agho" wrote:
[vbcol=seagreen]
> Can you please give an example of whay you want done? I don't quite
> understand it. For now, I am using OPENDATASOURCE to insert the data in to
> the desired table. This is taking about 3 minutes compared to 15 seconds when
> I use BCP.
>
> Thanks
>
> "John Bell" wrote:

BCP truncation error on few tables?

Hi,
I'm using BCP to copy some data between 2 servers.
I'm using the -N option to export the tables into the Native Unicode format
this works fine
except for few tables where I receive the truncation error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
this process has been used for few month without any issue and now I start
to see this error.
any idea?
thanks for your quick guides!
jerome.Hi
-N is keep non-text native and -n is native so you may want to try that
instead.
John
"Jeje" wrote:
> Hi,
> I'm using BCP to copy some data between 2 servers.
> I'm using the -N option to export the tables into the Native Unicode format
> this works fine
> except for few tables where I receive the truncation error:
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> this process has been used for few month without any issue and now I start
> to see this error.
> any idea?
> thanks for your quick guides!
> jerome.
>|||the BOL says:
-N is native unicode format:
http://msdn2.microsoft.com/en-us/library/ms189941.aspx
the varchar and char are exported in Unicode and others are in their native
format.
we have solve the issue for few tables by using the -w instead of -N
but some tables continues to suffer the same issue!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> Hi
> -N is keep non-text native and -n is native so you may want to try that
> instead.
> John
> "Jeje" wrote:
>> Hi,
>> I'm using BCP to copy some data between 2 servers.
>> I'm using the -N option to export the tables into the Native Unicode
>> format
>> this works fine
>> except for few tables where I receive the truncation error:
>> SQLState = 22001, NativeError = 0
>> Error = [Microsoft][SQL Native Client]String data, right truncation
>> this process has been used for few month without any issue and now I
>> start
>> to see this error.
>> any idea?
>> thanks for your quick guides!
>> jerome.
>>|||I would still try -n
John
"Jeje" wrote:
> the BOL says:
> -N is native unicode format:
> http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> the varchar and char are exported in Unicode and others are in their native
> format.
> we have solve the issue for few tables by using the -w instead of -N
> but some tables continues to suffer the same issue!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > Hi
> >
> > -N is keep non-text native and -n is native so you may want to try that
> > instead.
> >
> > John
> >
> > "Jeje" wrote:
> >
> >> Hi,
> >>
> >> I'm using BCP to copy some data between 2 servers.
> >>
> >> I'm using the -N option to export the tables into the Native Unicode
> >> format
> >> this works fine
> >> except for few tables where I receive the truncation error:
> >> SQLState = 22001, NativeError = 0
> >> Error = [Microsoft][SQL Native Client]String data, right truncation
> >>
> >> this process has been used for few month without any issue and now I
> >> start
> >> to see this error.
> >>
> >> any idea?
> >> thanks for your quick guides!
> >>
> >> jerome.
> >>
> >>|||I am having a similar problem.
When I use -N, I get this error:
SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
Client]String data, right truncation
When I use -n I get this error:
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
Client]Unexpected EOF encountered in BCP data-file
Any help will be appreciated.
Thanks
"John Bell" wrote:
> I would still try -n
> John
> "Jeje" wrote:
> > the BOL says:
> > -N is native unicode format:
> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> >
> > the varchar and char are exported in Unicode and others are in their native
> > format.
> >
> > we have solve the issue for few tables by using the -w instead of -N
> > but some tables continues to suffer the same issue!
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > > Hi
> > >
> > > -N is keep non-text native and -n is native so you may want to try that
> > > instead.
> > >
> > > John
> > >
> > > "Jeje" wrote:
> > >
> > >> Hi,
> > >>
> > >> I'm using BCP to copy some data between 2 servers.
> > >>
> > >> I'm using the -N option to export the tables into the Native Unicode
> > >> format
> > >> this works fine
> > >> except for few tables where I receive the truncation error:
> > >> SQLState = 22001, NativeError = 0
> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > >>
> > >> this process has been used for few month without any issue and now I
> > >> start
> > >> to see this error.
> > >>
> > >> any idea?
> > >> thanks for your quick guides!
> > >>
> > >> jerome.
> > >>
> > >>|||yes, same error here.
"Agho" <Agho@.discussions.microsoft.com> wrote in message
news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> I am having a similar problem.
> When I use -N, I get this error:
> SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> Client]String data, right truncation
> When I use -n I get this error:
> SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> Client]Unexpected EOF encountered in BCP data-file
> Any help will be appreciated.
> Thanks
>
> "John Bell" wrote:
>> I would still try -n
>> John
>> "Jeje" wrote:
>> > the BOL says:
>> > -N is native unicode format:
>> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
>> >
>> > the varchar and char are exported in Unicode and others are in their
>> > native
>> > format.
>> >
>> > we have solve the issue for few tables by using the -w instead of -N
>> > but some tables continues to suffer the same issue!
>> >
>> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
>> > > Hi
>> > >
>> > > -N is keep non-text native and -n is native so you may want to try
>> > > that
>> > > instead.
>> > >
>> > > John
>> > >
>> > > "Jeje" wrote:
>> > >
>> > >> Hi,
>> > >>
>> > >> I'm using BCP to copy some data between 2 servers.
>> > >>
>> > >> I'm using the -N option to export the tables into the Native Unicode
>> > >> format
>> > >> this works fine
>> > >> except for few tables where I receive the truncation error:
>> > >> SQLState = 22001, NativeError = 0
>> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
>> > >>
>> > >> this process has been used for few month without any issue and now I
>> > >> start
>> > >> to see this error.
>> > >>
>> > >> any idea?
>> > >> thanks for your quick guides!
>> > >>
>> > >> jerome.
>> > >>
>> > >>|||Hi
This issue could be cause be either a row or field terminator being present
withing your data or possibly a missing row terminator at the end of the
file. The latter should be easy to identify as only the last row may not be
imported. The easiest way to correct the former is to generate the data file
with specify terminators that you will know are not going to occur in the
data, then use the -r and -t option to specify the appropriate character when
importing the data with BCP.
HTH
John
"Jeje" wrote:
> yes, same error here.
>
> "Agho" <Agho@.discussions.microsoft.com> wrote in message
> news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > I am having a similar problem.
> > When I use -N, I get this error:
> > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > Client]String data, right truncation
> >
> > When I use -n I get this error:
> > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > Client]Unexpected EOF encountered in BCP data-file
> >
> > Any help will be appreciated.
> >
> > Thanks
> >
> >
> > "John Bell" wrote:
> >
> >> I would still try -n
> >>
> >> John
> >>
> >> "Jeje" wrote:
> >>
> >> > the BOL says:
> >> > -N is native unicode format:
> >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> >> >
> >> > the varchar and char are exported in Unicode and others are in their
> >> > native
> >> > format.
> >> >
> >> > we have solve the issue for few tables by using the -w instead of -N
> >> > but some tables continues to suffer the same issue!
> >> >
> >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> >> > > Hi
> >> > >
> >> > > -N is keep non-text native and -n is native so you may want to try
> >> > > that
> >> > > instead.
> >> > >
> >> > > John
> >> > >
> >> > > "Jeje" wrote:
> >> > >
> >> > >> Hi,
> >> > >>
> >> > >> I'm using BCP to copy some data between 2 servers.
> >> > >>
> >> > >> I'm using the -N option to export the tables into the Native Unicode
> >> > >> format
> >> > >> this works fine
> >> > >> except for few tables where I receive the truncation error:
> >> > >> SQLState = 22001, NativeError = 0
> >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> >> > >>
> >> > >> this process has been used for few month without any issue and now I
> >> > >> start
> >> > >> to see this error.
> >> > >>
> >> > >> any idea?
> >> > >> thanks for your quick guides!
> >> > >>
> >> > >> jerome.
> >> > >>
> >> > >>|||Can you please give an example of whay you want done? I don't quite
understand it. For now, I am using OPENDATASOURCE to insert the data in to
the desired table. This is taking about 3 minutes compared to 15 seconds when
I use BCP.
Thanks
"John Bell" wrote:
> Hi
> This issue could be cause be either a row or field terminator being present
> withing your data or possibly a missing row terminator at the end of the
> file. The latter should be easy to identify as only the last row may not be
> imported. The easiest way to correct the former is to generate the data file
> with specify terminators that you will know are not going to occur in the
> data, then use the -r and -t option to specify the appropriate character when
> importing the data with BCP.
> HTH
> John
>
> "Jeje" wrote:
> > yes, same error here.
> >
> >
> > "Agho" <Agho@.discussions.microsoft.com> wrote in message
> > news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > > I am having a similar problem.
> > > When I use -N, I get this error:
> > > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > > Client]String data, right truncation
> > >
> > > When I use -n I get this error:
> > > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > > Client]Unexpected EOF encountered in BCP data-file
> > >
> > > Any help will be appreciated.
> > >
> > > Thanks
> > >
> > >
> > > "John Bell" wrote:
> > >
> > >> I would still try -n
> > >>
> > >> John
> > >>
> > >> "Jeje" wrote:
> > >>
> > >> > the BOL says:
> > >> > -N is native unicode format:
> > >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> > >> >
> > >> > the varchar and char are exported in Unicode and others are in their
> > >> > native
> > >> > format.
> > >> >
> > >> > we have solve the issue for few tables by using the -w instead of -N
> > >> > but some tables continues to suffer the same issue!
> > >> >
> > >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > >> > > Hi
> > >> > >
> > >> > > -N is keep non-text native and -n is native so you may want to try
> > >> > > that
> > >> > > instead.
> > >> > >
> > >> > > John
> > >> > >
> > >> > > "Jeje" wrote:
> > >> > >
> > >> > >> Hi,
> > >> > >>
> > >> > >> I'm using BCP to copy some data between 2 servers.
> > >> > >>
> > >> > >> I'm using the -N option to export the tables into the Native Unicode
> > >> > >> format
> > >> > >> this works fine
> > >> > >> except for few tables where I receive the truncation error:
> > >> > >> SQLState = 22001, NativeError = 0
> > >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > >> > >>
> > >> > >> this process has been used for few month without any issue and now I
> > >> > >> start
> > >> > >> to see this error.
> > >> > >>
> > >> > >> any idea?
> > >> > >> thanks for your quick guides!
> > >> > >>
> > >> > >> jerome.
> > >> > >>
> > >> > >>|||Hi
Can you post DDL and (a small set of) example data that will fail.
John
"Agho" wrote:
> Can you please give an example of whay you want done? I don't quite
> understand it. For now, I am using OPENDATASOURCE to insert the data in to
> the desired table. This is taking about 3 minutes compared to 15 seconds when
> I use BCP.
>
> Thanks
>
> "John Bell" wrote:
> > Hi
> >
> > This issue could be cause be either a row or field terminator being present
> > withing your data or possibly a missing row terminator at the end of the
> > file. The latter should be easy to identify as only the last row may not be
> > imported. The easiest way to correct the former is to generate the data file
> > with specify terminators that you will know are not going to occur in the
> > data, then use the -r and -t option to specify the appropriate character when
> > importing the data with BCP.
> >
> > HTH
> >
> > John
> >
> >
> > "Jeje" wrote:
> >
> > > yes, same error here.
> > >
> > >
> > > "Agho" <Agho@.discussions.microsoft.com> wrote in message
> > > news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > > > I am having a similar problem.
> > > > When I use -N, I get this error:
> > > > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > > > Client]String data, right truncation
> > > >
> > > > When I use -n I get this error:
> > > > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > > > Client]Unexpected EOF encountered in BCP data-file
> > > >
> > > > Any help will be appreciated.
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > >> I would still try -n
> > > >>
> > > >> John
> > > >>
> > > >> "Jeje" wrote:
> > > >>
> > > >> > the BOL says:
> > > >> > -N is native unicode format:
> > > >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> > > >> >
> > > >> > the varchar and char are exported in Unicode and others are in their
> > > >> > native
> > > >> > format.
> > > >> >
> > > >> > we have solve the issue for few tables by using the -w instead of -N
> > > >> > but some tables continues to suffer the same issue!
> > > >> >
> > > >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > > >> > > Hi
> > > >> > >
> > > >> > > -N is keep non-text native and -n is native so you may want to try
> > > >> > > that
> > > >> > > instead.
> > > >> > >
> > > >> > > John
> > > >> > >
> > > >> > > "Jeje" wrote:
> > > >> > >
> > > >> > >> Hi,
> > > >> > >>
> > > >> > >> I'm using BCP to copy some data between 2 servers.
> > > >> > >>
> > > >> > >> I'm using the -N option to export the tables into the Native Unicode
> > > >> > >> format
> > > >> > >> this works fine
> > > >> > >> except for few tables where I receive the truncation error:
> > > >> > >> SQLState = 22001, NativeError = 0
> > > >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > > >> > >>
> > > >> > >> this process has been used for few month without any issue and now I
> > > >> > >> start
> > > >> > >> to see this error.
> > > >> > >>
> > > >> > >> any idea?
> > > >> > >> thanks for your quick guides!
> > > >> > >>
> > > >> > >> jerome.
> > > >> > >>
> > > >> > >>sql

BCP truncation error on few tables?

Hi,
I'm using BCP to copy some data between 2 servers.
I'm using the -N option to export the tables into the Native Unicode format
this works fine
except for few tables where I receive the truncation error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
this process has been used for few month without any issue and now I start
to see this error.
any idea?
thanks for your quick guides!
jerome.Hi
-N is keep non-text native and -n is native so you may want to try that
instead.
John
"Jeje" wrote:

> Hi,
> I'm using BCP to copy some data between 2 servers.
> I'm using the -N option to export the tables into the Native Unicode forma
t
> this works fine
> except for few tables where I receive the truncation error:
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncatio
n
> this process has been used for few month without any issue and now I start
> to see this error.
> any idea?
> thanks for your quick guides!
> jerome.
>|||the BOL says:
-N is native unicode format:
http://msdn2.microsoft.com/en-us/library/ms189941.aspx
the varchar and char are exported in Unicode and others are in their native
format.
we have solve the issue for few tables by using the -w instead of -N
but some tables continues to suffer the same issue!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...[vbcol=seagreen]
> Hi
> -N is keep non-text native and -n is native so you may want to try that
> instead.
> John
> "Jeje" wrote:
>|||I would still try -n
John
"Jeje" wrote:
[vbcol=seagreen]
> the BOL says:
> -N is native unicode format:
> http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> the varchar and char are exported in Unicode and others are in their nativ
e
> format.
> we have solve the issue for few tables by using the -w instead of -N
> but some tables continues to suffer the same issue!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...|||I am having a similar problem.
When I use -N, I get this error:
SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
Client]String data, right truncation
When I use -n I get this error:
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
Client]Unexpected EOF encountered in BCP data-file
Any help will be appreciated.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> I would still try -n
> John
> "Jeje" wrote:
>|||yes, same error here.
"Agho" <Agho@.discussions.microsoft.com> wrote in message
news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...[vbcol=seagreen]
> I am having a similar problem.
> When I use -N, I get this error:
> SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> Client]String data, right truncation
> When I use -n I get this error:
> SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> Client]Unexpected EOF encountered in BCP data-file
> Any help will be appreciated.
> Thanks
>
> "John Bell" wrote:
>|||Hi
This issue could be cause be either a row or field terminator being present
withing your data or possibly a missing row terminator at the end of the
file. The latter should be easy to identify as only the last row may not be
imported. The easiest way to correct the former is to generate the data file
with specify terminators that you will know are not going to occur in the
data, then use the -r and -t option to specify the appropriate character whe
n
importing the data with BCP.
HTH
John
"Jeje" wrote:
[vbcol=seagreen]
> yes, same error here.
>
> "Agho" <Agho@.discussions.microsoft.com> wrote in message
> news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...|||Can you please give an example of whay you want done? I don't quite
understand it. For now, I am using OPENDATASOURCE to insert the data in to
the desired table. This is taking about 3 minutes compared to 15 seconds whe
n
I use BCP.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This issue could be cause be either a row or field terminator being presen
t
> withing your data or possibly a missing row terminator at the end of the
> file. The latter should be easy to identify as only the last row may not b
e
> imported. The easiest way to correct the former is to generate the data fi
le
> with specify terminators that you will know are not going to occur in the
> data, then use the -r and -t option to specify the appropriate character w
hen
> importing the data with BCP.
> HTH
> John
>
> "Jeje" wrote:
>|||Hi
Can you post DDL and (a small set of) example data that will fail.
John
"Agho" wrote:
[vbcol=seagreen]
> Can you please give an example of whay you want done? I don't quite
> understand it. For now, I am using OPENDATASOURCE to insert the data in to
> the desired table. This is taking about 3 minutes compared to 15 seconds w
hen
> I use BCP.
>
> Thanks
>
> "John Bell" wrote:
>

bcp to xml

can someone please advise me on the correct syntax to bcp export executing a
stored procedures results to xml
I know it begins
bcp "exe sp_procedure" queryout
but unser on the rest of the syntax
many thanks
bcp "SELECT * FROM TABLE FOR XML RAW" queryout
c:\table.xml -Sserver -Uusername -Ppassword -c -r -t
"Matthew Butler" <matthew.butler@.srjdebt.co.uk> wrote in message
news:KuZ%i.24820$6v.5502@.newsfe2-gui.ntli.net...
> can someone please advise me on the correct syntax to bcp export executing
> a stored procedures results to xml
> I know it begins
> bcp "exe sp_procedure" queryout
> but unser on the rest of the syntax
> many thanks
>

bcp to xml

can someone please advise me on the correct syntax to bcp export executing a
stored procedures results to xml
I know it begins
bcp "exe sp_procedure" queryout
but unser on the rest of the syntax
many thanksbcp "SELECT * FROM TABLE FOR XML RAW" queryout
c:\table.xml -Sserver -Uusername -Ppassword -c -r -t
"Matthew Butler" <matthew.butler@.srjdebt.co.uk> wrote in message
news:KuZ%i.24820$6v.5502@.newsfe2-gui.ntli.net...
> can someone please advise me on the correct syntax to bcp export executing
> a stored procedures results to xml
> I know it begins
> bcp "exe sp_procedure" queryout
> but unser on the rest of the syntax
> many thanks
>sql

BCP to export an SP which uses Temporary Tables not working - SQL Server 2005

Hi

I am trying to export the data from a stored procedure via bcp export. The SP uses temporary tables although the actual data in the temp tables is not the data being exported they are just used to help get the final data.

When running the BCP Export I get an error message that the Object does not exist however if I change the SP to use real tables as opposed to temporary then it runs fine.

I have read that there is no problem exporting from temporary tables with BCP but I am not exporting the data in the temporary tables is this what is causing the problem, it seems a but strange that you can only use them if the data contained is what is being exported.

Can anyone explain?

Thanks

Paul

Maybe you can post some code.

I'm somewhat confused. You say it's throwing an error about the temporary tables, but you say you're exporting from the temporary tables.

|||

Paul:

If the temp tables are created inside the stored procedures then what you are doing is not going to be possible because the temp tables will go out of scope when you exit the stored procedure. This is also logically consistent with the fact that if you use permanent tables instead of temporary tables that the BCP works.

It seems to me that the most likely scenario for you to get your stored procedure operate on a temp table and then have BCP also operate on the same temp table is if (1) your temp table is a global temp table -- that is, it starts with ## instead of # -- that is created before the the stored procedure is call by the connection that also calls the stored procedure and (2) the connection is maintained and the global temp table is not dropped. Under these circumstances you should be able to use BCP on the global temp table.

It would be simpler if you could convert your stored procedure to a function or a view.

|||

Hi Kent

Thanks for the reply I have tried using global temp tables and i get the same error but with ## before the object name so it would appear that it is not going to work with temp tables at all. This may not be a problem as I can always create them then drop them so in effect they are temporary.

Problem with converting to anything else is the whole routine is someone elses that they have been working on for many months I was just trying to help with the BCP aspect, it is a large amount of code and apart from the temp tables it does work we were just trying to understand why it wouldnt work so it can be documented or if possible we could have fixed it.

I can see the problem with the local temp tables thanks to your help but dont see why the global ones wouldnt work I even tried without dropping them at the end of the SP. Oh wait a moment are you saying that the global temp tables would need to be created before the SP is executed and therefore my order of events would be

1. Create Global Temp Tables

2. Execute SP in the BCP export Command

3. Once all is done and happy with the results send in another SQL statement to Drop the Global Tables

If so that should be a good enough reason for us to create real tables and drop them instead.

Thanks for your help

Paul

BCP to comma separated Quote surround text file.

I have a need to export (regularly) a large table. I need to export to a
comma delimited file with quote surrounds around text fields (or all fields
for all that matters).
I can export the data with no problem, I can't figure out how to get the
quote surrounds however.
Here is what my bcp statement looks like:
bcp SURVEY.dbo.vw_898002_results out e:\export\survey.dat -U ***** -P
***** -S VNET-SQL /c /t , > e:\export\out.txt
I used the bol and it looked like I should be able to do something with
the -t switch but that seems to be having no impact what-so-ever.
thanks.Quote surrounds can be made by specifying in a format file the delimiters
for each field, rather than using the -t operator. Read about format files,
they are not really that hard but many people choke on them too quickly. If
I remember correctly, you can define a column 0 that terminates with " if
you need a quote on the first column.
Think of it as a regular expression problem.
RLF
PS - Of course, it leaves me wondering what you are getting with /t. (FWIW,
I don't think it matters, but you are using /t and the doc is for -t.)
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:OJLZc7CQFHA.248@.TK2MSFTNGP15.phx.gbl...
>I have a need to export (regularly) a large table. I need to export to a
>comma delimited file with quote surrounds around text fields (or all fields
>for all that matters).
> I can export the data with no problem, I can't figure out how to get the
> quote surrounds however.
> Here is what my bcp statement looks like:
> bcp SURVEY.dbo.vw_898002_results out e:\export\survey.dat -U ***** -P
> ***** -S VNET-SQL /c /t , > e:\export\out.txt
> I used the bol and it looked like I should be able to do something with
> the -t switch but that seems to be having no impact what-so-ever.
> thanks.
>|||the /t is just one of the many versions I was trying out, I think it was
meant to be: -t \t (tab delimited)
in the end I managed to get the -t to work except for the very first record
on the very first row.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ubZrw8DQFHA.2000@.TK2MSFTNGP15.phx.gbl...
> Quote surrounds can be made by specifying in a format file the delimiters
> for each field, rather than using the -t operator. Read about format
> files, they are not really that hard but many people choke on them too
> quickly. If I remember correctly, you can define a column 0 that
> terminates with " if you need a quote on the first column.
> Think of it as a regular expression problem.
> RLF
> PS - Of course, it leaves me wondering what you are getting with /t.
> (FWIW, I don't think it matters, but you are using /t and the doc is
> for -t.)
>
> "Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
> news:OJLZc7CQFHA.248@.TK2MSFTNGP15.phx.gbl...
>

BCP table export and Shared Locks

Hi,
We use BCP commands like this:
BCP ACC.Dbo.[TableName] out CSV\ TableName.csv /c /k /t "|" /r
"\n" -Sservername -Uname -Ppass
to extract data from SQL Server into CSV files.
I want BCP don't put any lock, including shared lock, on table records. How
can I supply locking hint (NOLOCK) along with the table name?
We try not to use the actual query and just put the table name on the
command line.
Thank you,
AlanTry append -hnolock to the command.
Lucas
"Maxwell2006" wrote:
> Hi,
>
> We use BCP commands like this:
>
> BCP ACC.Dbo.[TableName] out CSV\ TableName.csv /c /k /t "|" /r
> "\n" -Sservername -Uname -Ppass
>
> to extract data from SQL Server into CSV files.
>
> I want BCP don't put any lock, including shared lock, on table records. How
> can I supply locking hint (NOLOCK) along with the table name?
>
> We try not to use the actual query and just put the table name on the
> command line.
>
> Thank you,
> Alan
>
>
>|||Hi Max,
Thank you for posting.
As for the SQL server bcp utility, currently there is only a "TABLOCK" hint
option whch can help switch the lock (when performing bulk
importing/exporting) between row level lock and table level lock.
Therefore, if we need to completely disable any lock when performing the
bulk exporting, we still have to use explicit T-SQL script to do it.
#bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
BTW, if you do not want to pass the T-SQL directly in command prompt, do
you think it possible that we use a batch/script file to programmatically
load such T-SQL script and launch the bcp utility command?
Anyway, please feel free to let me know if you have any other consideration.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thank you Steven.
I changed our export scripts to use the query "select * from tableName
(NOLOCK)".
Max
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:t8tbYX3jGHA.4528@.TK2MSFTNGXA01.phx.gbl...
> Hi Max,
> Thank you for posting.
> As for the SQL server bcp utility, currently there is only a "TABLOCK"
> hint
> option whch can help switch the lock (when performing bulk
> importing/exporting) between row level lock and table level lock.
> Therefore, if we need to completely disable any lock when performing the
> bulk exporting, we still have to use explicit T-SQL script to do it.
> #bcp Utility
> http://msdn2.microsoft.com/en-us/library/ms162802.aspx
> BTW, if you do not want to pass the T-SQL directly in command prompt, do
> you think it possible that we use a batch/script file to programmatically
> load such T-SQL script and launch the bcp utility command?
> Anyway, please feel free to let me know if you have any other
> consideration.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your followup Max,
Glad that you've got a solution to work on it. If there is anything else we
can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

BCP Queryout export to XML Question

Hey All,
Thought I'd take a break from Scalar Functions for a while and give Omnibuzz
a break from answering them.
I've searched thru the BOL, and this forum, as well as the help files in
SQL2005. I've gotten to the point where I am exporting the data
correctly..however I am looking for a format..
Here's what I'm attempting.. I need to take certain grabs of data using a
stored procedure.. dump it to an xml file / soap file, and then publish to
another service. for that to happen, i have a specific file format that the
y
have to be in.
So, here's my data...
strike price nominalDate
1000 0.000 2006-04-01
10000 2.850 2006-04-01
10050 0.000 2006-04-01
10150 0.000 2006-04-01
10200 0.000 2006-04-01
Here's the optionformat.xml
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="10"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="10"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="10"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="nominalDate" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="price" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="strike" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
and here's the real format it needs to be in.
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="0.75"/>
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="1.0"/>
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="3.0"/>
Of course, this doesn't include the headers at all, but I'm thinking I can
just add that to the file using the copy command to join the 3 files (copy
txt1.txt + optionformat.xml + txt2.txt uploadfile.xml)
The hardest part is getting it in that format...
Now to have the answer plunked right in front of me would be nice, but I
really need to learn how to do this, and understand the process... The only
other option that i have is to write a small vb program that calls from the
database, formats the data using the FileSystemObject.
If you know of a good resource that explains exporting into formatted xml,
that would be great.
If I'm totally going down the wrong road on this solution, let me know as
well.
Thanks!
~Dan Regalia
--
www.krushradio.com - Internet Radio for the rest of usDaniel Regalia (DanielRegalia@.discussions.microsoft.com) writes:
> Here's what I'm attempting.. I need to take certain grabs of data using
> a stored procedure.. dump it to an xml file / soap file, and then
> publish to another service. for that to happen, i have a specific file
> format that they have to be in.
>...
> Here's the optionformat.xml
><?xml version="1.0"?>
><BCPFORMAT
> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
>...
> and here's the real format it needs to be in.
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="0.75"/>
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="1.0"/>
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="3.0"/>
Ehum, you cannot use a BCP format file to specify an XML format for
the output.
You should probably look into using FOR XML EXPLICIT instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP Problems with ISO Latin-1 Characters past ASCII 192

I am trying to export data from a table with char and varchar fields
(Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
with the character past ASCII 192 the characters like , , , etc. My
export statement looks like this as I am using the -c flag.
bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
But I get the following:
MXICO| |MX|0|1
The data in the table looks like this
MXICO| |MX|0|1
-n (Native) did not seem to work.
-w (Unicode character) did work, geting it out, but I can not put it back
in...
Do I have some sort of code page/collation not set correct?
Thanks
Reeves
Hi
Have you tried -C RAW?
John
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>
|||Reeves,
We had a similar problem that I believe was fixed when we switched to
sing -C ACP. (RAW may do for you as well, try it out.) The problem is
that the default is -C OEM, which is basically the old DOS character set and
that is trashing some characters for you.
Russell Fields
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>

bcp out..

I need to provide a user with the ability to export the
data from 1 view into a .csv file at any time he needs to
see the data.
I have tried using a commmand line bcp out, but the data
does not come out in a format that is highly useable to
the user.
How can I make it so that bcp out exports to the .csv in
format that it exists in the database ie. distinct
columns and rows.
Also, what does the user need to bcp out data? Is select
permissions for his windows account enough?
Thanks,
MariaMaria
Ex
bcp "select * from northwind..orders" queryout c:\cust.xls -c -S<server> -Usa -
above command will create a file with extension XLS but, in fact its a tab delimited flat file since it has extension XLS by default it will get opened in EXCEL and tab character is considered as a column delimiter. I've put TAB as a column delimiter assuming this character is not used in any of the varchar/char field. because if you are including such a character which exists in the varchar/char datatype whole format will get disturbed. comma seperated file/CSV file is a good example for this. Also point to be noted that if any of the char/varchar field contains TAB character even this file's format will get disturbed.So basically you have to make sure that the varchar columns for which you are exporting the data should not contain the character that is being used as a field seperator otherwise it will lead to
you can also run above command line using xp_cmdshell and thus can be used in T-SQL script
E
exec master..xp_cmdshell 'bcp "select * from northwind..orders" queryout c:\cust.xls -c -S<server> -Usa -P
Also refer to following UR
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b30612
>Also, what does the user need to bcp out data? Is select
>permissions for his windows account enough
Yes,
- Visha|||You could also connect to the database via ODBC and an
Excel spreadsheet. Open Excel, go to data/get external
data/new dB query. connect using the ODBC setting and
enter the query needed. The user will then have the option
of refreshing the data at their leisure and can
automatically save it as an CSV file.
>--Original Message--
>I need to provide a user with the ability to export the
>data from 1 view into a .csv file at any time he needs to
>see the data.
>I have tried using a commmand line bcp out, but the data
>does not come out in a format that is highly useable to
>the user.
>How can I make it so that bcp out exports to the .csv in
>format that it exists in the database ie. distinct
>columns and rows.
>Also, what does the user need to bcp out data? Is select
>permissions for his windows account enough?
>Thanks,
>Maria
>.
>

bcp out with column name.

Hi,
I need make a scheduling job for bcp export data from databases' table to a
text file with column name. Does anyone know any method I can use?
Regards!
-ChenFirst I would try to create a stored procedure that uses xp_cmdshell to
execute a BCP? You could also create a linked server to an ODBC driver
for text, and insert records into that from a stored procedure. Either
method could then be scheduled. I'm sure there are other ways too.
You didn't mention which SQL Server you have, but I'm sure there is a
DTS solution and / or an Integration solution. Pick your poison!
Good luck...
-Rick|||Chen,
May be using osql.exe
Example:
c:\>osql -S(local) -E -Q"select orderid from northwind.dbo.orders"
-o"c:\temp\test.txt
AMB
"Chen" wrote:
> Hi,
> I need make a scheduling job for bcp export data from databases' table to a
> text file with column name. Does anyone know any method I can use?
> Regards!
> -Chensql

bcp out with column name.

Hi,
I need make a scheduling job for bcp export data from databases' table to a
text file with column name. Does anyone know any method I can use?
Regards!
-ChenFirst I would try to create a stored procedure that uses xp_cmdshell to
execute a BCP? You could also create a linked server to an ODBC driver
for text, and insert records into that from a stored procedure. Either
method could then be scheduled. I'm sure there are other ways too.
You didn't mention which SQL Server you have, but I'm sure there is a
DTS solution and / or an Integration solution. Pick your poison!
Good luck...
-Rick|||Chen,
May be using osql.exe
Example:
c:\>osql -S(local) -E -Q"select orderid from northwind.dbo.orders"
-o"c:\temp\test.txt
AMB
"Chen" wrote:

> Hi,
> I need make a scheduling job for bcp export data from databases' table to
a
> text file with column name. Does anyone know any method I can use?
> Regards!
> -Chen

bcp out empty column

I'm bcp-ing a table to a file. I've noticed that if a varchar column is empty, that both "bcp -c" and "bcp -c -k" export the empty value as #$00. Instead, I'd like to export the value as if it were null.

use monkey
go

create table tab1 (
myColumn varchar(10) null
)
go

insert into tab1 (myColumn) values ('')
go

exec master..xp_cmdshell 'bcp monkey..tab1 out D:\data.csv -c -T'
go

drop table tab1
go

I've been fiddling about with other switches but so far haven't come up with a solution other than writing out the full SELECT-statement or updating the original. Anyone with an easier solution?try changing the extn of file to '.txt' and check it, its working fine for me using the command prompt as well as query analyser.

try as
exec master..xp_cmdshell 'bcp <db-name>.<table-owner>.tab1 out D:\data.csv -c -T'
or
exec master..xp_cmdshell 'bcp <db-name>.<table-owner>.tab1 out D:\data.txt -c -T'

cheers,
pavan.|||Changing the file's extension didn't do it for me, perhaps it's an OS or SQLServer configuration issue? It's a Windows 2000 Server running SQL2000. I'm not sure where to start.

I don't have the issue with the analyzer either, only when exporting to a file.|||Mine is windows 2000 professional runnung sqlserver 2000.
I dont think thats the problem.

Try the same in others machine,
i had a similar problem for exports and Imports of oracle,they never worked in my colleagues PC and worked fine from my pc..

bcp out and bcp in don't give the same number of rows

Hi,
I'm trying to export and import one big table in sql
server 2000 using bcp. Original table has 7,416,000 rows,
and after I bcp out this table into file in native format,
and bcp in from this file, also in native format, table
gets 7,478,000 rows and the data takes significantly less
space than before!
This table has char, varchar, numeric, datetime, and text
data types.
Can anybody tell me why the difference?
Thanks,
OJ
Hi,
Significatly less space:-
This is because your source table is fragmented heavily. The fragmentation
can be viwed by
DBCC SHOWCONTIG (Reger books online for info) command. By using this command
see the scan density, if the scan density is
too low than execute a DBCC DBREINDEX (Refer books online for usage and
info) command to remove the fragmention.
Cause for the fragmentation is because of the DML commands (Insert, Update
and Delete).
Row Difference:-
This may be bause of the inconsistency in sysindexes table. Execute the
below command to correct the inconsistency
and give the correct rows in the table. (Use the source table_name)
USE <dbname>
go
sp_spaceused <table_name>,@.updateusage = 'TRUE'
Thanks
Hari
MCDBA
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1b69d01c45082$2712ca90$a301280a@.phx.gbl...
> Hi,
> I'm trying to export and import one big table in sql
> server 2000 using bcp. Original table has 7,416,000 rows,
> and after I bcp out this table into file in native format,
> and bcp in from this file, also in native format, table
> gets 7,478,000 rows and the data takes significantly less
> space than before!
> This table has char, varchar, numeric, datetime, and text
> data types.
> Can anybody tell me why the difference?
> Thanks,
> OJ
|||Table is not fragmented at all. It is rebuilt before bcp
out. Statistics are also updated because of rebuilding the
table (clustered index with fillfactor 100)...
Thanks anyway,
OJ
>--Original Message--
>Hi,
>Significatly less space:-
>--
>This is because your source table is fragmented heavily.
The fragmentation
>can be viwed by
>DBCC SHOWCONTIG (Reger books online for info) command. By
using this command
>see the scan density, if the scan density is
>too low than execute a DBCC DBREINDEX (Refer books online
for usage and
>info) command to remove the fragmention.
>Cause for the fragmentation is because of the DML
commands (Insert, Update
>and Delete).
>Row Difference:-
>--
>This may be bause of the inconsistency in sysindexes
table. Execute the
>below command to correct the inconsistency
>and give the correct rows in the table. (Use the source
table_name)
>USE <dbname>
>go
>sp_spaceused <table_name>,@.updateusage = 'TRUE'
>--
>Thanks
>Hari
>MCDBA
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1b69d01c45082$2712ca90$a301280a@.phx.gbl...
rows,[vbcol=seagreen]
format,[vbcol=seagreen]
less[vbcol=seagreen]
text
>
>.
>
|||OJ (anonymous@.discussions.microsoft.com) writes:
> Table is not fragmented at all. It is rebuilt before bcp
> out. Statistics are also updated because of rebuilding the
> table (clustered index with fillfactor 100)...
How was it rebuilt? I've seen that when I have run DBCC DBREINDEX, there
has been a lot of extra space around, as it to have room next time
DBREINDEX is run.
How did you conclude that the number of rows were different? Did you
actually run a SELECT COUNT(*)? Or did you look at sysindexes.rows?
To get accurate values in this column - and in the space column,
run DBCC UPDATEUSAGE on the table.
If you indeed get different results with SELECT COUNT(*) before and
after running BCP, something is fishy. One alternative is that there
was some other data in the target table before you started. Also
check that the schemas of the tables are entirely identical.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp