BCP/DTS/cmdshell problem

Hi
Using 2000
I am writing a .cmd file for Bulk copying data(about 25 tables of 1 million
rows each). I need your help and advice on this
1)is dts faster than BCP converting to flatfiles and again copying to
destination tables.
2) if we write cmdshell and use BCP in that instead of directly using in
.cmd file, will performance be slower. I am new to .cmd so controlling and
error handling will be easier if I write in a sp which uses cmd shell to
extract bcp
3. any resource on writing .cmd files using osql and bcp( templates etc). I
googled but no use.
--
Thanks
DevaHi deva
I think you will get a better answer if you try the DTS discussion forum
"DEva" wrote:

> Hi
> Using 2000
> I am writing a .cmd file for Bulk copying data(about 25 tables of 1 millio
n
> rows each). I need your help and advice on this
> 1)is dts faster than BCP converting to flatfiles and again copying to
> destination tables.
> 2) if we write cmdshell and use BCP in that instead of directly using in
> .cmd file, will performance be slower. I am new to .cmd so controlling and
> error handling will be easier if I write in a sp which uses cmd shell to
> extract bcp
> 3. any resource on writing .cmd files using osql and bcp( templates etc).
I
> googled but no use.
> --
> Thanks
> Deva

Bcp utility with stored procedure

Guys,

I have stored proc sp_generate_insert which will generate insert scripts for the tables. When I run the stored Proc
from the management studio it runs fine. But when I run through stored proc as part of BCP utility I get this error.

'SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.'

Execute dev.dbo.sp_generate_inserts 'auth' runs fine from management studio and generates inserts for auth table.

When I run the same proc as part of the following stored proc with bcp utility I get the error.

alter PROCEDURE INSERTTEST2 ( @.FILEPATH NVARCHAR(50))
AS
DECLARE @.cmd varchar(2000)
BEGIN
set @.cmd = 'bcp.exe "EXEC dev.dbo.SP_GENERATE_INSERTS auth" '
+ 'QUERYOUT' + ' ' +@.filePath+ '.sql ' +'-S ' +
'NV-DEVSQL3' + ' -q ' + ' -c -T -e' + @.filePath+'.log -o '
+ @.filePath+ '_out.log'
select @.cmd -- + '...'
EXEC master.dbo.xp_cmdShell @.cmd
END

Any suggestions or inputs would help.

Thanks

The problem lies within the proc, so we need to see that code.

Though usually, this error comes from statements where the length parameter in SUBSTRING becomes negative.

If you're dynamically trying to set how large chunk substring should take, and that variable becomes negative, then this error happens.

Since the problem seems to occur or not depending on method of connecting, it may suggest that there are different settings that may be the root cause.. (ie ANSI DEFAULTS etc)

Could this be it perhaps?

/Kenneth

|||kenneth,

Thank you for you reply.

I dont know if the problem is setting defaults on the database or the connection, more so since the stored proc - sp_generate_scripts runs fine from the managment studio.

Anyways the code for stored proc is available at the following link

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Any suggestions/inputs would help

Thanks

|||

I played around a bit with the proc and found some 'interesting' stuff...

I think your problem may be that you don't use the -d parameter in your bcp command, so you're not ending up in the right db.

The reason this matters may be the same that I found, but didn't notice at first...

(I tried it on SQL Server 2000).
First when compiled, there was a msg about not finding sys.sp_MS_marksystemobject, but the proc compiled anyway, so I tried it out.

Got the same message as you a couple of times, but found that only if I was in a db other than master. Made a usertable in master, then it worked. =/

So, fixed the 'sys.sp_MS_marksystemobject' to 'sp_MS_marksystemobject' and recompiled (since the former doesn't exist in 2000, only in 2005) and tried again. Now all is smooth, and it works like it's supposed to.

Apparently, the proc needs to be marked as a systemobject, else you may get these 'db-scope' issues, so check out if this is the problem.

/Kenneth

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 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 Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table?
Thanks
Steffan
Temporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegr oups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table?
> Thanks
> Steffan
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid o
bject
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>

BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.
Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx
http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx
http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reload
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 300
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns)
> and reload the data. Dropping and recreating clustered index didn't reclaim
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BCP
> in, I can load them concurrently and also planning to use ORDER and TABLOCK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format.
> If you have any experience in unloading/reloading very large table from sql
> server, I would love to hear your comments/suggestion.
>
>
sql

BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/pr.../19/602142.aspx
http://www.microsoft.com/sql/techin...000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reloa
d
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 30
0
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns
)
> and reload the data. Dropping and recreating clustered index didn't reclai
m
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BC
P
> in, I can load them concurrently and also planning to use ORDER and TABLO
CK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if
I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format
.
> If you have any experience in unloading/reloading very large table from sq
l
> server, I would love to hear your comments/suggestion.
>
>

BCP out, extended chars when field is null (sometimes)

I want to "BCP out" some tables. The format is to be delimited with "\r\n"
row terminator.
Now for whatever reason, when I look at the exported text file some rows
have extended characters. The extended characters appear in fields that
happen to be null. The kicker is for the same column some rows with nulls
come through just fine, and a few rows (same column) come in with the
extended characters.
I am aware of the issue of there being no way to represent a null in the bcp
generated text data file. It is ok, if the source is a null and then it
comes back in as an empy string. I am ok with this.
In a sense, it sort of sounds like data corruption, but the field value is
null. The extended chars appear between the delimiters. So how does NULL
turn into extended characters.
Thanks.
I just ran into this too.
I created a little C# console guy that opens the text file and strips out
the null chars that were in my file.
you run it from a command line passing in the source file name and an output
file name.
FileScrubber.exe IckyFile.txt Cleanfile.txt
I've attached the cs file for the Class if you can use it great.
(i'm no C# Guru so please be kind with the review)
Greg Jackson
PDX, Oregon
begin 666 Class1.cs
M=7-I;F<@.4WES=&5M.PT*=7-I;F<@.4WES=&5M+DE/.PT*#0IN86UE<W!A8V4@.
M1FEL95-C<G5B8F5R#0I[#0H)+R\O(#QS=6UM87)Y/@.T*"2\O+R!3=6UM87)Y
M(&1E<V-R:7!T:6]N(&9O<B!#;&%S<S$N#0H)+R\O(#PO<W5M;6%R>3X-"@.EC
M;&%S<R!&:6QE4V-R=6)B97(-"@.E[#0H)"2\O+R \<W5M;6%R>3X-"@.D)+R\O
M(%1H92!M86EN(&5N=')Y('!O:6YT(&9O<B!T:&4@.87!P;&EC8 71I;VXN#0H)
M"2\O+R \+W-U;6UA<GD^#0H)"5M35$%4:')E861=#0H)"7-T871I8R!V;VED
M($UA:6XH<W1R:6YG6UT@.87)G<RD-"@.D)>PT*"0D)<W1R:6YG('-);G!U=$9I
M;&4@./2!A<F=S6S!=.PT*"0D)<W1R:6YG('-/=71P=71&:6QE(#T@.87)G<ULQ
M73L-"@.D)"6EN="!I;G1">71E.PT*"0D)8GET92!B=$)Y=&4[#0 H-"@.D)"49I
M;&53=')E86T@.<W1R;4]U='!U=" ](&YU;&P[#0H)"0E&:6QE4W1R96%M('-T
M<FU);G!U=" ](&YU;&P[#0H-"@.D)"71R>0T*"0D)>PT*"0D)"49I;&5);F9O
M(&]B:DEN1FEL92 ](&YE=R!&:6QE26YF;RAS26YP=71&:6QE*3L-"@.D)"0E&
M:6QE26YF;R!O8FI/=71&:6QE(#T@.;F5W($9I;&5);F9O*'-/=71P=71&:6QE
M*3L-"@.T*"0D)"7-T<FU);G!U=" ](&]B:DEN1FEL92Y/<&5N4F5A9"@.I.PT*
M"0D)"7-T<FU/=71P=70@./2!O8FI/=71&:6QE+D]P96Y7<FET92@.I.PT*#0H)
M"0D)9F]R*&EN="!I(#T@.,#MI/'-T<FU);G!U="Y,96YG=&@.[:2LK*0T*"0D)
M"7L-"@.D)"0D):6YT0GET92 ]('-T<FU);G!U="Y296%D0GET92@.I.PT*"0D)
M"0EB=$)Y=&4@./2 H8GET92EI;G1">71E.PT*#0H)"0D)"2\O:68@.:70@.:7,@.
M82!N=6QL(&-H87)A8W1E<BP@.=V4@.9V]T=&$@.<VMI<"!I="XN+BY'04H-"@.D)
M"0D):68H8G1">71E(#X@.,"D-"@.D)"0D)>PT*"0D)"0D)<W1R;4]U='!U="Y7
M<FET94)Y=&4H8G1">71E*3L-"@.D)"0D)?0T*"0D)"7T-"@.D)"7T-"@.D)"6-A
M=&-H*%-Y<W1E;2Y%>&-E<'1I;VX@.97AP*0T*"0D)>PT*"0D)"4-O;G-O;&4N
M5W)I=&5,:6YE*")%<G(@.(B K(&5X<"Y-97-S86=E*3L-"@.D)"7T-"@.D)"69I
M;F%L;'D-"@.D)"7L-"@.D)"0ES=')M3W5T<'5T+D-L;W-E*"D[#0H)"0D)<W1R
M;4EN<'5T+D-L;W-E*"D[#0H)"0E]#0H-"@.D)"4-O;G-O;&4N5W)I=&5,:6YE
M*")0<F]C97-S960@.26YP=70Z("(@.*R!S26YP=71&:6QE*3L-"@.D)"4-O;G-O
M;&4N5W)I=&5,:6YE*")'96YE<F%T960@.3W5T<'5T.B B("L@.<T]U='!U=$9I
M;&4I.PT*"0D)0V]N<V]L92Y7<FET94QI;F4H(B(I.PT*"0D)0V]N<V]L92Y7
D<FET94QI;F4H(D=I9&1Y(%5P(2(I.PT*"0E]#0H)?0T*?0T*
`
end

bcp out

hi all
I want to bcp out whole database (tables, sps, fns, triggers)
is this possible and is it possible to make it a job
tia
Zarko
BCP out is used only to export the data. You can use EM to script the table,
procedure and trigger etc. Or if you want to automate, look for sp_OACreate,
sp_OAMethod in BOL. There are several code snippets available in the internet.
Thanks
Chinna.
"Zarko Jovanovic" wrote:

> hi all
> I want to bcp out whole database (tables, sps, fns, triggers)
> is this possible and is it possible to make it a job
> tia
> Zarko
>
>

bcp out

hi all
I want to bcp out whole database (tables, sps, fns, triggers)
is this possible and is it possible to make it a job
tia
ZarkoBCP out is used only to export the data. You can use EM to script the table,
procedure and trigger etc. Or if you want to automate, look for sp_OACreate,
sp_OAMethod in BOL. There are several code snippets available in the internet.
Thanks
Chinna.
"Zarko Jovanovic" wrote:
> hi all
> I want to bcp out whole database (tables, sps, fns, triggers)
> is this possible and is it possible to make it a job
> tia
> Zarko
>
>

bcp or oledb/ado?

I have very big tables. And I need to some computation for each row. Which
one is the best/fast way to implement it?
1. bcp the tables to text file. C++ code parse the csv file row by row.
Write results to text files. Then bulk insert back to Sql server.
2. C++ code use oledb/ado to get the rows, write results to text file and
bulk insert back.
3. C++ code use oledb/ado for both getting and writting back operations.What type of computation do you need to do?
Why not just do it within SQL using T-SQL?
create table #foo (col1 int, col2 int, col3 decimal(5,2))
insert into #foo (col1, col2, col3) values (1,2,3)
insert into #foo (col1, col2, col3) values (10,20,30)
insert into #foo (col1, col2, col3) values (5,6, NULL)
go
select * from #foo
update #foo set col3 = col1 * col2
select * from #foo
select * from #foo
update #foo set col3 = col1 * col2
select * from #foo
update #foo set col3 = col3 / 4
select * from #foo
update #foo set col3 = (col3 / 2) / 1
select * from #foo
go
drop table #foo
If your tables are huge and you don't have enough disk space you might fill
up the transaction log. If that is a concern you could update batches of
data (based on your primary key).
Keith Kratochvil
"nick" <nick@.discussions.microsoft.com> wrote in message
news:89811B8B-D867-4163-A4F9-132EB4BC6D69@.microsoft.com...
>I have very big tables. And I need to some computation for each row. Which
> one is the best/fast way to implement it?
> 1. bcp the tables to text file. C++ code parse the csv file row by row.
> Write results to text files. Then bulk insert back to Sql server.
> 2. C++ code use oledb/ado to get the rows, write results to text file and
> bulk insert back.
> 3. C++ code use oledb/ado for both getting and writting back operations.
>|||The computation is complex math, matrix mutilple, etc. Very difficult to
write in TSQL. And the code is provided from other group and I cannot modify
it too. I will definitely rewrite it in TSQL if it's possbile.
"Keith Kratochvil" wrote:

> What type of computation do you need to do?
> Why not just do it within SQL using T-SQL?
> create table #foo (col1 int, col2 int, col3 decimal(5,2))
> insert into #foo (col1, col2, col3) values (1,2,3)
> insert into #foo (col1, col2, col3) values (10,20,30)
> insert into #foo (col1, col2, col3) values (5,6, NULL)
> go
> select * from #foo
> update #foo set col3 = col1 * col2
> select * from #foo
> select * from #foo
> update #foo set col3 = col1 * col2
> select * from #foo
>
> update #foo set col3 = col3 / 4
> select * from #foo
> update #foo set col3 = (col3 / 2) / 1
> select * from #foo
> go
> drop table #foo
>
> If your tables are huge and you don't have enough disk space you might fil
l
> up the transaction log. If that is a concern you could update batches of
> data (based on your primary key).
>
> --
> Keith Kratochvil
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:89811B8B-D867-4163-A4F9-132EB4BC6D69@.microsoft.com...
>
>|||you will have to define what "very big tables" means, but if they are over 1
0
million rows, then exporting them to a text file and running a well written
C
object against it will probably be faster. You should make sure you make
only one pass through the data making your computations, then use sql's bulk
insert to get the finished product back into the db.
"nick" wrote:

> I have very big tables. And I need to some computation for each row. Which
> one is the best/fast way to implement it?
> 1. bcp the tables to text file. C++ code parse the csv file row by row.
> Write results to text files. Then bulk insert back to Sql server.
> 2. C++ code use oledb/ado to get the rows, write results to text file and
> bulk insert back.
> 3. C++ code use oledb/ado for both getting and writting back operations.
>|||the big tables vary from several thousand rows to several tens million rows.
bcp should be faster then client side cursor, however it involved more I/O i
n
the whole process...
"Carl Henthorn" wrote:
> you will have to define what "very big tables" means, but if they are over
10
> million rows, then exporting them to a text file and running a well writte
n C
> object against it will probably be faster. You should make sure you make
> only one pass through the data making your computations, then use sql's bu
lk
> insert to get the finished product back into the db.
> "nick" wrote:
>|||nick (nick@.discussions.microsoft.com) writes:
> I have very big tables. And I need to some computation for each row. Which
> one is the best/fast way to implement it?
> 1. bcp the tables to text file. C++ code parse the csv file row by row.
> Write results to text files. Then bulk insert back to Sql server.
> 2. C++ code use oledb/ado to get the rows, write results to text file and
> bulk insert back.
> 3. C++ code use oledb/ado for both getting and writting back operations.
Now, C++ programming is not my main business, but my gut feelings says
that 1 is not a good solution. It takes time to read a file as well.
So to get the data into the client program, I would get one huge rowset,
or possibly batchwise. Note: not a server-side cursor, but client side.
For writing data back, I would use either bulk copy or send down an
XML document that I unpack in SQL Server with OPENXML. I would proably
not insert into the target table - I would prefer to update it. (Unless
the computations also removes and add rows.) To this OPENXML is maybe a
little simpler, but you can easily bulk copy into a staging table
you update from.
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|||Yes, bcp seems involve more I/O. But will it still be faster then client-sid
e
cursor? And I am trying to avoid client side oledb programming, ado is said
easier but slower. And I cannot use ado.net since it's not clr program.
"Erland Sommarskog" wrote:

> nick (nick@.discussions.microsoft.com) writes:
> Now, C++ programming is not my main business, but my gut feelings says
> that 1 is not a good solution. It takes time to read a file as well.
> So to get the data into the client program, I would get one huge rowset,
> or possibly batchwise. Note: not a server-side cursor, but client side.
> For writing data back, I would use either bulk copy or send down an
> XML document that I unpack in SQL Server with OPENXML. I would proably
> not insert into the target table - I would prefer to update it. (Unless
> the computations also removes and add rows.) To this OPENXML is maybe a
> little simpler, but you can easily bulk copy into a staging table
> you update from.
>
> --
> 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
>|||nick (nick@.discussions.microsoft.com) writes:
> Yes, bcp seems involve more I/O. But will it still be faster then
> client-side cursor? And I am trying to avoid client side oledb
> programming, ado is said easier but slower. And I cannot use ado.net
> since it's not clr program.
Admittedly, there is some overhead in a recordset/rowset. Maybe bulk
out to file, and then read the file into memory in one swoop?
The only way to find out is to benchmark - if you have the time.
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 in child before parent

Huh?

I've got good RI data...BUT..a developer loaded the tables in alpha table order...

Such that the child loaded BEFORE the parent...

Huh?

Got a test being set up now to mess with the child file to add a key that doesn't exist in the parent...

But Why is this allowed?

In DB2 you can specify

LOAD DATA REPLACE NO CHECK...

On the load card...you then need to run a check after to verify the data...

Is that what's going on? Is there such a utility in SQL Server to run a check post load?

I'm confused...

Any comments appreciated.

Thanks

Brett

8-)OK, -h option would allow you to check constraints

Otherwise it doesn't

So then if you use the default, How do you make sure the data is ok?|||d'oye....

DBCC CHECKCONSTRAINTS

What a maroon....

BCP import large text file to sql tables

hi,
I have a huge text file(about 10 GB) that needed to imported to SQL server2k
table.
I know BCP is the fastest way to do it. But if I use BPC, my log shipping to
a prod standby serve will not have BPC transactions.
What should I do to have fast importing process and log shipping synched as
well.
Thanks
How big is the entire db? If it is small compared to 10GB I would just
disable log shipping, import, index as appropriate then backup and restore
full db and restart log shipping. If the db size is very large compared to
10GB you will be better off letting log shipping do it's thing. If you bcp
with FULL recovery mode and use small batch sizes won't log shipping work
fine?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
> hi,
> I have a huge text file(about 10 GB) that needed to imported to SQL
> server2k
> table.
> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
> to
> a prod standby serve will not have BPC transactions.
> What should I do to have fast importing process and log shipping synched
> as
> well.
> Thanks
>
|||the size of Is it related to importing process? The db is 250gb
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k61tbvko3h51@.corp.supernews.com...
> How big is the entire db? If it is small compared to 10GB I would just
> disable log shipping, import, index as appropriate then backup and restore
> full db and restart log shipping. If the db size is very large compared
> to 10GB you will be better off letting log shipping do it's thing. If you
> bcp with FULL recovery mode and use small batch sizes won't log shipping
> work fine?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>
|||That is pretty big in relation to the 10GB to be loaded. I would consider
keeping log shipping online to avoid the 'downtime' required to completely
resync the database after the load. YMMV.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
> the size of Is it related to importing process? The db is 250gb
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k61tbvko3h51@.corp.supernews.com...
>
|||I have weekly large import -- 20GB text file(35 million records) need to
insert into 2 tables _ i need performance as well.
What is the best way to acomplish this with fullly log shipping synched.
Thanks
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k66ifi8esog81@.corp.supernews.com...
> That is pretty big in relation to the 10GB to be loaded. I would consider
> keeping log shipping online to avoid the 'downtime' required to completely
> resync the database after the load. YMMV.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>

BCP import large text file to sql tables

hi,
I have a huge text file(about 10 GB) that needed to imported to SQL server2k
table.
I know BCP is the fastest way to do it. But if I use BPC, my log shipping to
a prod standby serve will not have BPC transactions.
What should I do to have fast importing process and log shipping synched as
well.
ThanksHow big is the entire db? If it is small compared to 10GB I would just
disable log shipping, import, index as appropriate then backup and restore
full db and restart log shipping. If the db size is very large compared to
10GB you will be better off letting log shipping do it's thing. If you bcp
with FULL recovery mode and use small batch sizes won't log shipping work
fine?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
> hi,
> I have a huge text file(about 10 GB) that needed to imported to SQL
> server2k
> table.
> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
> to
> a prod standby serve will not have BPC transactions.
> What should I do to have fast importing process and log shipping synched
> as
> well.
> Thanks
>|||the size of Is it related to importing process? The db is 250gb
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k61tbvko3h51@.corp.supernews.com...
> How big is the entire db? If it is small compared to 10GB I would just
> disable log shipping, import, index as appropriate then backup and restore
> full db and restart log shipping. If the db size is very large compared
> to 10GB you will be better off letting log shipping do it's thing. If you
> bcp with FULL recovery mode and use small batch sizes won't log shipping
> work fine?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>|||That is pretty big in relation to the 10GB to be loaded. I would consider
keeping log shipping online to avoid the 'downtime' required to completely
resync the database after the load. YMMV.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
> the size of Is it related to importing process? The db is 250gb
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k61tbvko3h51@.corp.supernews.com...
>|||I have weekly large import -- 20GB text file(35 million records) need to
insert into 2 tables _ i need performance as well.
What is the best way to acomplish this with fullly log shipping synched.
Thanks
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k66ifi8esog81@.corp.supernews.com...
> That is pretty big in relation to the 10GB to be loaded. I would consider
> keeping log shipping online to avoid the 'downtime' required to completely
> resync the database after the load. YMMV.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>

BCP import large text file to sql tables

hi,
I have a huge text file(about 10 GB) that needed to imported to SQL server2k
table.
I know BCP is the fastest way to do it. But if I use BPC, my log shipping to
a prod standby serve will not have BPC transactions.
What should I do to have fast importing process and log shipping synched as
well.
ThanksHow big is the entire db? If it is small compared to 10GB I would just
disable log shipping, import, index as appropriate then backup and restore
full db and restart log shipping. If the db size is very large compared to
10GB you will be better off letting log shipping do it's thing. If you bcp
with FULL recovery mode and use small batch sizes won't log shipping work
fine?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
> hi,
> I have a huge text file(about 10 GB) that needed to imported to SQL
> server2k
> table.
> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
> to
> a prod standby serve will not have BPC transactions.
> What should I do to have fast importing process and log shipping synched
> as
> well.
> Thanks
>|||the size of Is it related to importing process? The db is 250gb
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k61tbvko3h51@.corp.supernews.com...
> How big is the entire db? If it is small compared to 10GB I would just
> disable log shipping, import, index as appropriate then backup and restore
> full db and restart log shipping. If the db size is very large compared
> to 10GB you will be better off letting log shipping do it's thing. If you
> bcp with FULL recovery mode and use small batch sizes won't log shipping
> work fine?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I have a huge text file(about 10 GB) that needed to imported to SQL
>> server2k
>> table.
>> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
>> to
>> a prod standby serve will not have BPC transactions.
>> What should I do to have fast importing process and log shipping synched
>> as
>> well.
>> Thanks
>>
>|||That is pretty big in relation to the 10GB to be loaded. I would consider
keeping log shipping online to avoid the 'downtime' required to completely
resync the database after the load. YMMV.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
> the size of Is it related to importing process? The db is 250gb
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k61tbvko3h51@.corp.supernews.com...
>> How big is the entire db? If it is small compared to 10GB I would just
>> disable log shipping, import, index as appropriate then backup and
>> restore full db and restart log shipping. If the db size is very large
>> compared to 10GB you will be better off letting log shipping do it's
>> thing. If you bcp with FULL recovery mode and use small batch sizes
>> won't log shipping work fine?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I have a huge text file(about 10 GB) that needed to imported to SQL
>> server2k
>> table.
>> I know BCP is the fastest way to do it. But if I use BPC, my log
>> shipping to
>> a prod standby serve will not have BPC transactions.
>> What should I do to have fast importing process and log shipping synched
>> as
>> well.
>> Thanks
>>
>>
>|||I have weekly large import -- 20GB text file(35 million records) need to
insert into 2 tables _ i need performance as well.
What is the best way to acomplish this with fullly log shipping synched.
Thanks
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k66ifi8esog81@.corp.supernews.com...
> That is pretty big in relation to the 10GB to be loaded. I would consider
> keeping log shipping online to avoid the 'downtime' required to completely
> resync the database after the load. YMMV.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> the size of Is it related to importing process? The db is 250gb
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13k61tbvko3h51@.corp.supernews.com...
>> How big is the entire db? If it is small compared to 10GB I would just
>> disable log shipping, import, index as appropriate then backup and
>> restore full db and restart log shipping. If the db size is very large
>> compared to 10GB you will be better off letting log shipping do it's
>> thing. If you bcp with FULL recovery mode and use small batch sizes
>> won't log shipping work fine?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I have a huge text file(about 10 GB) that needed to imported to SQL
>> server2k
>> table.
>> I know BCP is the fastest way to do it. But if I use BPC, my log
>> shipping to
>> a prod standby serve will not have BPC transactions.
>> What should I do to have fast importing process and log shipping
>> synched as
>> well.
>> Thanks
>>
>>
>>
>|||Depending on the network pipe your log shipping goes across you may need to
split the file into numerous smaller files to avoid flooding the log
shipping system.
I would also consider dropping and recreating indexes if the 35M rows is a
large fraction of the total rows in the tables. This should greatly speed
the data load and will give you cleaner indexes to boot.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u$Np9G6KIHA.3848@.TK2MSFTNGP05.phx.gbl...
>I have weekly large import -- 20GB text file(35 million records) need to
>insert into 2 tables _ i need performance as well.
> What is the best way to acomplish this with fullly log shipping synched.
> Thanks
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k66ifi8esog81@.corp.supernews.com...
>> That is pretty big in relation to the 10GB to be loaded. I would
>> consider keeping log shipping online to avoid the 'downtime' required to
>> completely resync the database after the load. YMMV.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> the size of Is it related to importing process? The db is 250gb
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13k61tbvko3h51@.corp.supernews.com...
>> How big is the entire db? If it is small compared to 10GB I would just
>> disable log shipping, import, index as appropriate then backup and
>> restore full db and restart log shipping. If the db size is very large
>> compared to 10GB you will be better off letting log shipping do it's
>> thing. If you bcp with FULL recovery mode and use small batch sizes
>> won't log shipping work fine?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I have a huge text file(about 10 GB) that needed to imported to SQL
>> server2k
>> table.
>> I know BCP is the fastest way to do it. But if I use BPC, my log
>> shipping to
>> a prod standby serve will not have BPC transactions.
>> What should I do to have fast importing process and log shipping
>> synched as
>> well.
>> Thanks
>>
>>
>>
>>
>

BCP Import Errors

I'm using a credit union software/database package (Symitar/Episys) to export the data tables for use with MS SQL 7.0. After I move the files onto my SQL Server 2000 box and then run the import batch file (there are 40 some odd tables to move) which has the correct destination table, import file and format file listed. While the batch is running in a command window, I spy three specific errors during the process. they are:

String Data, Right Truncation (I added spaces/changed to larger data type to the tables to possibly mitigate this to no avail)

Invalid Character Data For Cast Specification

Invalid Date Format (all my dates in the export file are in the format xxxx-xx-xx)

I have spent several hours on researching them and trying some of the fixes to no avail

The first export was a comma delimited file and had many errors, I then switched to a tab delimited export and changed the command line in the batch file to reflect that fact. I have fewer errors with the tab version but still have errors. I have tried to view the data in the export files at the probable location but can't see anything that will cause these errors.

Here are three lines in the batch file that I'm using:

bcp ACUTest..NAME in c:\DataXfer\EXTRACT.NAME -fc:\DataXfer\FMT.NAME -t \t -Satlas -Usa -Pwhatever -V70

bcp ACUTest..COMMENT in c:\DataXfer\EXTRACT.COMMENT -fc:\DataXfer\FMT.COMMENT -t \t -Satlas -Usa -Pwhatever -V70

bcp ACUTest..SAVINGSNAME in c:\DataXfer\EXTRACT.SAVINGSNAME -fc:\DataXfer\FMT.SAVINGSNAME -t \t -Satlas -Usa -Pwhatever -V70

If I've been to vague anywhere please let me know so I can provide more info.

Thank you all for your insight and valuable time.

Seems that you have the same problem like this poster here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=908084&SiteID=1

Did you checked the data types of the destination table ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de