I have the following problem with bcp_int() call: same program that
works just fine on Windows 2000 fails silently on bcp_init() call on
Windows XP. All other conditions are the same: database, table name etc.
Note that program was compiled on Windows 2000. Has anybody else
encountered similar problem?
I had the same problem with XP and Win98.
I have installed MDAC 2.8 download on Microsoft site and it correct the
problem.
But it doesn't correct problem on Win98.
Let me know if this solution resolve your problem
"Yuriy Dudko" wrote:
> I have the following problem with bcp_int() call: same program that
> works just fine on Windows 2000 fails silently on bcp_init() call on
> Windows XP. All other conditions are the same: database, table name etc.
> Note that program was compiled on Windows 2000. Has anybody else
> encountered similar problem?
>
2012年3月29日星期四
bcp_init fails on Windows XP, works fine on Windows 2000
I have the following problem with bcp_int() call: same program that
works just fine on Windows 2000 fails silently on bcp_init() call on
Windows XP. All other conditions are the same: database, table name etc.
Note that program was compiled on Windows 2000. Has anybody else
encountered similar problem?I had the same problem with XP and Win98.
I have installed MDAC 2.8 download on Microsoft site and it correct the
problem.
But it doesn't correct problem on Win98.
Let me know if this solution resolve your problem
"Yuriy Dudko" wrote:
> I have the following problem with bcp_int() call: same program that
> works just fine on Windows 2000 fails silently on bcp_init() call on
> Windows XP. All other conditions are the same: database, table name etc.
> Note that program was compiled on Windows 2000. Has anybody else
> encountered similar problem?
>
works just fine on Windows 2000 fails silently on bcp_init() call on
Windows XP. All other conditions are the same: database, table name etc.
Note that program was compiled on Windows 2000. Has anybody else
encountered similar problem?I had the same problem with XP and Win98.
I have installed MDAC 2.8 download on Microsoft site and it correct the
problem.
But it doesn't correct problem on Win98.
Let me know if this solution resolve your problem
"Yuriy Dudko" wrote:
> I have the following problem with bcp_int() call: same program that
> works just fine on Windows 2000 fails silently on bcp_init() call on
> Windows XP. All other conditions are the same: database, table name etc.
> Note that program was compiled on Windows 2000. Has anybody else
> encountered similar problem?
>
bcp.exe connects to go.microsoft.com?
Hi!
We're using MSDE 1.0 and one customer informed us, that his Sygate
personal firewall spit out the following message:
bcp.exe is trying to connect to go.microsoft.com [207.46.197.43] using
port 80.
the exe's path is the path that we'd expect and bcp still does it's job,
namely saving the databases to files; so I doubt that it is a hijacked
version; but we'll have to double check that.
Can anyone explain why bcp would want to connect to a website?
Thanks,
Stefan
Check your event log. For many Microsoft Products, events that have errors
can connect to or provide a link to go.microsoft.com ... if you have error
reporting enabled that might be where the error goes...
I see this in my event log for various auditing events and errors:
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
http://www.aspfaq.com/
(Reverse address to reply.)
"Stefan M. Huber" <looseleaf@.gmx.net> wrote in message
news:opsb7imbzss9ddfw@.news.individual.de...
> Hi!
> We're using MSDE 1.0 and one customer informed us, that his Sygate
> personal firewall spit out the following message:
> bcp.exe is trying to connect to go.microsoft.com [207.46.197.43] using
> port 80.
> the exe's path is the path that we'd expect and bcp still does it's job,
> namely saving the databases to files; so I doubt that it is a hijacked
> version; but we'll have to double check that.
> Can anyone explain why bcp would want to connect to a website?
> Thanks,
> Stefan
|||On Wed, 4 Aug 2004 09:43:42 -0400, Aaron [SQL Server MVP]
<ten.xoc@.dnartreb.noraa> wrote:
> Check your event log. For many Microsoft Products, events that have
> errors
> can connect to or provide a link to go.microsoft.com ... if you have
> error
> reporting enabled that might be where the error goes...
Oh thank you. That is a very good hint.
Stefan
Pink Floyd of Borg: We don't need no assimilation!
We're using MSDE 1.0 and one customer informed us, that his Sygate
personal firewall spit out the following message:
bcp.exe is trying to connect to go.microsoft.com [207.46.197.43] using
port 80.
the exe's path is the path that we'd expect and bcp still does it's job,
namely saving the databases to files; so I doubt that it is a hijacked
version; but we'll have to double check that.
Can anyone explain why bcp would want to connect to a website?
Thanks,
Stefan
Check your event log. For many Microsoft Products, events that have errors
can connect to or provide a link to go.microsoft.com ... if you have error
reporting enabled that might be where the error goes...
I see this in my event log for various auditing events and errors:
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
http://www.aspfaq.com/
(Reverse address to reply.)
"Stefan M. Huber" <looseleaf@.gmx.net> wrote in message
news:opsb7imbzss9ddfw@.news.individual.de...
> Hi!
> We're using MSDE 1.0 and one customer informed us, that his Sygate
> personal firewall spit out the following message:
> bcp.exe is trying to connect to go.microsoft.com [207.46.197.43] using
> port 80.
> the exe's path is the path that we'd expect and bcp still does it's job,
> namely saving the databases to files; so I doubt that it is a hijacked
> version; but we'll have to double check that.
> Can anyone explain why bcp would want to connect to a website?
> Thanks,
> Stefan
|||On Wed, 4 Aug 2004 09:43:42 -0400, Aaron [SQL Server MVP]
<ten.xoc@.dnartreb.noraa> wrote:
> Check your event log. For many Microsoft Products, events that have
> errors
> can connect to or provide a link to go.microsoft.com ... if you have
> error
> reporting enabled that might be where the error goes...
Oh thank you. That is a very good hint.
Stefan
Pink Floyd of Borg: We don't need no assimilation!
bcp, queryout and pipe delimiter
When I execute the following string within a stored procedure:
(The string has been modified to mask company info)
bcp "Exec dbname.dbo.GetSalesCodesContracts" queryout d:\ftp\sfa\SalescodeContract-1.txt -m1 -c -t| -Uxxxxx -Pxxxxx -Sxxxxx
I get the following error: The name specified is not recognized as an
internal or external command, operable program or batch file.
But if I change -t| to -t, (comma) it works ok and produces a comma delimited file.
The pipe character seems to be the problem. The help file on delimiters says you can use the pipe.
This is running under SQL Server 7.0.
Any help would be appreciated.
RichardAre you using bulk insert or the bcp command line utility?
'|' is used for command line re-direction, most commonly to more, find and sort. It can also be used to re-direct the output of one application to another application. I suspect the command interpriter sees the '|' and tries to re-direct the result of your bcp command to the '-UXXXXX' application which as you have found out does not exist.
Hope this helped.|||Try using -t'|'|||Try using -t'|'
This did not work for me. Instead, I finally discovered, -t^| did the trick. (The DOS shell interpreter uses the ^ character to escape the | and interpret the whole thing as a literal to be passed to bcp, rather than as part of the DOS commandline itself.)|||I would do this with t-sql and xp_cmdshellsql
(The string has been modified to mask company info)
bcp "Exec dbname.dbo.GetSalesCodesContracts" queryout d:\ftp\sfa\SalescodeContract-1.txt -m1 -c -t| -Uxxxxx -Pxxxxx -Sxxxxx
I get the following error: The name specified is not recognized as an
internal or external command, operable program or batch file.
But if I change -t| to -t, (comma) it works ok and produces a comma delimited file.
The pipe character seems to be the problem. The help file on delimiters says you can use the pipe.
This is running under SQL Server 7.0.
Any help would be appreciated.
RichardAre you using bulk insert or the bcp command line utility?
'|' is used for command line re-direction, most commonly to more, find and sort. It can also be used to re-direct the output of one application to another application. I suspect the command interpriter sees the '|' and tries to re-direct the result of your bcp command to the '-UXXXXX' application which as you have found out does not exist.
Hope this helped.|||Try using -t'|'|||Try using -t'|'
This did not work for me. Instead, I finally discovered, -t^| did the trick. (The DOS shell interpreter uses the ^ character to escape the | and interpret the whole thing as a literal to be passed to bcp, rather than as part of the DOS commandline itself.)|||I would do this with t-sql and xp_cmdshellsql
bcp -x and "Invalid Field index."
Hey, I am using bcp to try to create my xml file formate off from a table
that has about 477 fields. I am getting the following error:
F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
FNIApplication_X
MLFormatFile.xml
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
When I remove the -x to create the non xml file format no issues arise.
Idea's? I have not been able to find anything on web about this.
bcp Yea, you know me. (bcp Yea, you know me.@.discussions.microsoft.com)
writes:
> Hey, I am using bcp to try to create my xml file formate off from a table
> that has about 477 fields. I am getting the following error:
> F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
> FNIApplication_X
> MLFormatFile.xml
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
>
> When I remove the -x to create the non xml file format no issues arise.
> Idea's? I have not been able to find anything on web about this.
Sounds like a bug to me. I suggest that you file one on
http://connect.microsoft.com
Personally, I have not been able to whip up any enthusiams over the XML
format file. I have not been able to find that it buys me anything that
the old format does not. (Which, true, is fairly obscure. But I know
it by now.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
that has about 477 fields. I am getting the following error:
F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
FNIApplication_X
MLFormatFile.xml
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
When I remove the -x to create the non xml file format no issues arise.
Idea's? I have not been able to find anything on web about this.
bcp Yea, you know me. (bcp Yea, you know me.@.discussions.microsoft.com)
writes:
> Hey, I am using bcp to try to create my xml file formate off from a table
> that has about 477 fields. I am getting the following error:
> F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
> FNIApplication_X
> MLFormatFile.xml
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
>
> When I remove the -x to create the non xml file format no issues arise.
> Idea's? I have not been able to find anything on web about this.
Sounds like a bug to me. I suggest that you file one on
http://connect.microsoft.com
Personally, I have not been able to whip up any enthusiams over the XML
format file. I have not been able to find that it buys me anything that
the old format does not. (Which, true, is fairly obscure. But I know
it by now.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
2012年3月25日星期日
BCP to txt file
I am trying the following but it only works on a local database I want to ru
n
this on a remote server and have the file sent to my PC.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'You have to create a shared folder in your pc and give write access to sql
agent services account. Then use that shared folder in the bcp command.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout
"\\your_pc_name\shared_folder\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'
AMB
"Lontae Jones" wrote:
> I am trying the following but it only works on a local database I want to
run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -
S
> server1 -U sa -P sapwd -c'
>|||It works only if your servers are linked.
Try using tabe name directly (instead of using query) it should work
bcp "TestDB_data..titles" OUT c:\test_08-25-05.txt ....
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:80C1C9DA-2C3F-4048-9B55-7F849FD6B662@.microsoft.com...
>I am trying the following but it only works on a local database I want to
>run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout
> "c:\test_08-25-05.txt" -S
> server1 -U sa -P sapwd -c'
>
n
this on a remote server and have the file sent to my PC.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'You have to create a shared folder in your pc and give write access to sql
agent services account. Then use that shared folder in the bcp command.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout
"\\your_pc_name\shared_folder\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'
AMB
"Lontae Jones" wrote:
> I am trying the following but it only works on a local database I want to
run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -
S
> server1 -U sa -P sapwd -c'
>|||It works only if your servers are linked.
Try using tabe name directly (instead of using query) it should work
bcp "TestDB_data..titles" OUT c:\test_08-25-05.txt ....
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:80C1C9DA-2C3F-4048-9B55-7F849FD6B662@.microsoft.com...
>I am trying the following but it only works on a local database I want to
>run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout
> "c:\test_08-25-05.txt" -S
> server1 -U sa -P sapwd -c'
>
bcp syntax
When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
Message posted via http://www.sqlmonster.com
Bcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
-oj
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQLMonster.c om...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide character
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row terminator]
> [-i inputfile] [-o outfile] [-a packetsize]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.sqlmonster.com
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
Message posted via http://www.sqlmonster.com
Bcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
-oj
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQLMonster.c om...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide character
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row terminator]
> [-i inputfile] [-o outfile] [-a packetsize]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.sqlmonster.com
2012年3月22日星期四
bcp syntax
When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
--
Message posted via http://www.sqlmonster.comBcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
--
-oj
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQLMonster.com...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide character
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row terminator]
> [-i inputfile] [-o outfile] [-a packetsize]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.sqlmonster.com
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
--
Message posted via http://www.sqlmonster.comBcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
--
-oj
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQLMonster.com...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide character
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row terminator]
> [-i inputfile] [-o outfile] [-a packetsize]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.sqlmonster.com
bcp syntax
When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafi
le
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide charact
er
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminat
or]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional ena
ble]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
Message posted via http://www.droptable.comBcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
-oj
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQ
droptable.com...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} data
file
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide chara
cter
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row termin
ator]
> [-i inputfile] [-o outfile] [-a packetsize
]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional e
nable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.droptable.comsql
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafi
le
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide charact
er
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminat
or]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional ena
ble]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
Message posted via http://www.droptable.comBcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
-oj
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQ
droptable.com...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} data
file
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide chara
cter
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row termin
ator]
> [-i inputfile] [-o outfile] [-a packetsize
]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional e
nable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.droptable.comsql
bcp resource file error
I am trying to run a bcp import and I am getting the following error:
Unable to Load BCP resource dll. BCP cannot continue.
Does anyone have any idea what might cause this error?
<d> schrieb im Newsbeitrag news:eWq8%23a82EHA.3120@.TK2MSFTNGP12.phx.gbl...
> I am trying to run a bcp import and I am getting the following error:
> Unable to Load BCP resource dll. BCP cannot continue.
> Does anyone have any idea what might cause this error?
You might have to change your PATH appropriately.
robert
Unable to Load BCP resource dll. BCP cannot continue.
Does anyone have any idea what might cause this error?
<d> schrieb im Newsbeitrag news:eWq8%23a82EHA.3120@.TK2MSFTNGP12.phx.gbl...
> I am trying to run a bcp import and I am getting the following error:
> Unable to Load BCP resource dll. BCP cannot continue.
> Does anyone have any idea what might cause this error?
You might have to change your PATH appropriately.
robert
BCP Problem with SQL Server 2000
Ive encountered the following problem when trying to bcp out data
from a table with text columns :
Starting copy...
SQLState = S1001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Memory allocation failure
Ive no idea on whats going on with it :cry:
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-BCP-P...ict245539.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=852097
terry_aibo,
Try increasing the size of the windows page file. Also, allow SQL Server
to take as much memory on the machine as possible, something like
(TotalRam-200MB). Try and switch off any unnecessary services, and quit
any running applications except SQL Server. Try and make the machine a
"pure" dedicated SQL Server. Ensure you have the latest service pack.
If this fails to cure it, I would raise a call with Microsoft, this
error is quite rare.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
terry_aibo wrote:
> Ive encountered the following problem when trying to bcp out data
> from a table with text columns :
> Starting copy...
> SQLState = S1001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Memory allocation failure
>
> Ive no idea on whats going on with it :cry:
>
from a table with text columns :
Starting copy...
SQLState = S1001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Memory allocation failure
Ive no idea on whats going on with it :cry:
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-BCP-P...ict245539.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=852097
terry_aibo,
Try increasing the size of the windows page file. Also, allow SQL Server
to take as much memory on the machine as possible, something like
(TotalRam-200MB). Try and switch off any unnecessary services, and quit
any running applications except SQL Server. Try and make the machine a
"pure" dedicated SQL Server. Ensure you have the latest service pack.
If this fails to cure it, I would raise a call with Microsoft, this
error is quite rare.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
terry_aibo wrote:
> Ive encountered the following problem when trying to bcp out data
> from a table with text columns :
> Starting copy...
> SQLState = S1001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Memory allocation failure
>
> Ive no idea on whats going on with it :cry:
>
BCP Problem with SQL Server 2000
Ive encountered the following problem when trying to bcp out data
from a table with text columns :
Starting copy...
SQLState = S1001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Memory allocation failure
Ive no idea on whats going on with it :cry:
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-BCP-...rm.php?p=852097terry_aibo,
Try increasing the size of the windows page file. Also, allow SQL Server
to take as much memory on the machine as possible, something like
(TotalRam-200MB). Try and switch off any unnecessary services, and quit
any running applications except SQL Server. Try and make the machine a
"pure" dedicated SQL Server. Ensure you have the latest service pack.
If this fails to cure it, I would raise a call with Microsoft, this
error is quite rare.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
terry_aibo wrote:
> Ive encountered the following problem when trying to bcp out data
> from a table with text columns :
> Starting copy...
> SQLState = S1001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Memory allocation failu
re
>
> Ive no idea on whats going on with it :cry:
>
from a table with text columns :
Starting copy...
SQLState = S1001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Memory allocation failure
Ive no idea on whats going on with it :cry:
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-BCP-...rm.php?p=852097terry_aibo,
Try increasing the size of the windows page file. Also, allow SQL Server
to take as much memory on the machine as possible, something like
(TotalRam-200MB). Try and switch off any unnecessary services, and quit
any running applications except SQL Server. Try and make the machine a
"pure" dedicated SQL Server. Ensure you have the latest service pack.
If this fails to cure it, I would raise a call with Microsoft, this
error is quite rare.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
terry_aibo wrote:
> Ive encountered the following problem when trying to bcp out data
> from a table with text columns :
> Starting copy...
> SQLState = S1001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Memory allocation failu
re
>
> Ive no idea on whats going on with it :cry:
>
2012年3月20日星期二
bcp problem
I'm not having a syntax problem, I don't think, but the following is telling 0 rows copied, but I do know the file contains data...anyone have any ideas?
bcp "Privacy_OHM.[dbo].[Dat_up_log]" in D:\DATA\OHM\T0000001.log -T -S<servername> -fD:\DATA\OHM\Dat_log_In.fmtwhat does the fmt file look like?
and the first few rows of your .log file?|||Perhaps toss in an error file to catch the rows that bomb out?|||it was the format file
The redirected out put is essentially 1 column
The table has 3 columns and I forgot to "zero out" the column in the format file
Thanks guys...sql
bcp "Privacy_OHM.[dbo].[Dat_up_log]" in D:\DATA\OHM\T0000001.log -T -S<servername> -fD:\DATA\OHM\Dat_log_In.fmtwhat does the fmt file look like?
and the first few rows of your .log file?|||Perhaps toss in an error file to catch the rows that bomb out?|||it was the format file
The redirected out put is essentially 1 column
The table has 3 columns and I forgot to "zero out" the column in the format file
Thanks guys...sql
bcp problem
Can any one why I am facing the following error..
bcp pubs..authors out 'c:\Testing.xls' -c -S{local} -Usa -PDbPass
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
Thanks
bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
Without the -S, it will try the default instance on the local host. It is
generally best to give a server name and instance to ensure you don't
connect to the wrong server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rogers" <naissani@.hotmail.com> wrote in message
news:u4Xd4U%234FHA.2616@.TK2MSFTNGP12.phx.gbl...
> Can any one why I am facing the following error..
> bcp pubs..authors out 'c:\Testing.xls' -c -S{local} -Usa -PDbPass
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> Thanks
>
|||I used the following query but it gave me an error..
bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:etsm8z%234FHA.2040@.TK2MSFTNGP14.phx.gbl...
> bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
> Without the -S, it will try the default instance on the local host. It is
> generally best to give a server name and instance to ensure you don't
> connect to the wrong server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:u4Xd4U%234FHA.2616@.TK2MSFTNGP12.phx.gbl...
>
bcp pubs..authors out 'c:\Testing.xls' -c -S{local} -Usa -PDbPass
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
Thanks
bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
Without the -S, it will try the default instance on the local host. It is
generally best to give a server name and instance to ensure you don't
connect to the wrong server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rogers" <naissani@.hotmail.com> wrote in message
news:u4Xd4U%234FHA.2616@.TK2MSFTNGP12.phx.gbl...
> Can any one why I am facing the following error..
> bcp pubs..authors out 'c:\Testing.xls' -c -S{local} -Usa -PDbPass
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> Thanks
>
|||I used the following query but it gave me an error..
bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:etsm8z%234FHA.2040@.TK2MSFTNGP14.phx.gbl...
> bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
> Without the -S, it will try the default instance on the local host. It is
> generally best to give a server name and instance to ensure you don't
> connect to the wrong server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:u4Xd4U%234FHA.2616@.TK2MSFTNGP12.phx.gbl...
>
BCP Out Slow?
I am running the following BCP to extract a table with 156641604 rows.
bcp TestDB..data out test3.bcp -T -b1000000 -a32000
When running this i notice that the disk read bytes\sec counter in performance monitor on the drive that has the database devices is only reading 30mb\sec. I am writing the bcp file to a different drive. Both drives are far more capable of achieving much higher IO. Is this a limitation with BCP or are there futher switches available that would speed this process up. Also the drives are both local so the bottle neck is not network. Any ideas?
Hi Andy,
Try not using the -a option, and reducing the batch size to 100000.
What is the purpose of extracting the data. Do you need it to be in char format.
If use -n for native, it will be a bit quicker.
Jag
|||Hi Jag
That does help with throughput. The thing I don't understand is that even if I run a Select * From TestDB..data i get low read IO on this table. I know the disk IO is far more capable and if I run a DBCC SHOWCONTIG command for instance, the disk read bytes\sec counter in performance monitor jumps to over 70mb\sec. My question is, why doesn't BCP or a Select statement achieve this IO?
|||That's because it might not need to. The DBCC is going to explicitly hit the disk drive. Doing a BCP or a SELECT is going to utilize the normal infrastructure. It is going to read it from memory and then go out to disk as needed. Since SQL Server has an intelligent read ahead capability, it might only need 30mb/sec to keep up with the write process out the other side.|||Ok i can see what you are saying but the write IO is hovering around 30mb\sec also when it can write much faster than that. Is this IO limitation possibly due to the row count being so high, or some other factor?|||The row count shouldn't have any effect on this. BCP is going to run just as fast, regardless of the number of rows. It simply starts at the beginning and is done when SQL Server doesn't send anything else to it. I would suggest comparing this to the performance you get using SSIS. From everything that I've seen thus far, SSIS is going to move stuff a lot faster than BCP. Has to do with the basic interface that is used to connect to and get the data.2012年3月19日星期一
bcp invalid date format
Hi,
I have problem in BCP utility while exporting data to sql server.
It works in my system and my friend's system , but it gives the
following when its run in production
server.
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
BCP copy in failed
there are about 365000 records in the data file...
the date values go like below;
5/30/2007 9:27:35 AM
5/30/2007 9:28:37 AM
5/30/2007 5:28:16 PM
5/30/2007 5:28:32 PM
5/30/2007 5:28:16 PM
please help me for this problem...
thanks,
ThiruHi
/*
Kelly,Reynold,kelly@.reynold.com,5/30/2007 9:27:35 AM
John,Smith,bill@.smith.com,5/30/2007 9:27:35 AM
Sara,Parker,sara@.parker.com,5/30/2007 9:27:35 AM
*/
Make a column for dates as VARCHAR(50) and try insert
CREATE TABLE Tmp
(
stFName varchar (50) NOT NULL,
stLName varchar (50) NOT NULL,
stEmail varchar (100) NOT NULL,
dt varchar (100)
)
BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
SELECT * FROM Tmp
Later on you can convert VARCHAR to DATETIME datatype
"Thiru.net" <thirudotnet@.gmail.com> wrote in message
news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
> Hi,
> I have problem in BCP utility while exporting data to sql server.
> It works in my system and my friend's system , but it gives the
> following when its run in production
> server.
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> BCP copy in failed
> there are about 365000 records in the data file...
> the date values go like below;
> 5/30/2007 9:27:35 AM
> 5/30/2007 9:28:37 AM
> 5/30/2007 5:28:16 PM
> 5/30/2007 5:28:32 PM
> 5/30/2007 5:28:16 PM
> please help me for this problem...
> thanks,
> Thiru
>|||On Nov 1, 4:47 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> /*
> Kelly,Reynold,ke...@.reynold.com,5/30/2007 9:27:35 AM
> John,Smith,b...@.smith.com,5/30/2007 9:27:35 AM
> Sara,Parker,s...@.parker.com,5/30/2007 9:27:35 AM
> */
> Make a column for dates as VARCHAR(50) and try insert
> CREATE TABLE Tmp
> (
> stFName varchar (50) NOT NULL,
> stLName varchar (50) NOT NULL,
> stEmail varchar (100) NOT NULL,
> dt varchar (100)
> )
> BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
> SELECT * FROM Tmp
> Later on you can convert VARCHAR to DATETIME datatype
> "Thiru.net" <thirudot...@.gmail.com> wrote in message
> news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have problem in BCP utility while exporting data to sql server.
> > It works in my system and my friend's system , but it gives the
> > following when its run in production
> > server.
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > BCP copy in failed
> > there are about 365000 records in the data file...
> > the date values go like below;
> > 5/30/2007 9:27:35 AM
> > 5/30/2007 9:28:37 AM
> > 5/30/2007 5:28:16 PM
> > 5/30/2007 5:28:32 PM
> > 5/30/2007 5:28:16 PM
> > please help me for this problem...
> > thanks,
> > Thiru- Hide quoted text -
> - Show quoted text -
Hi,
Thanks for your reply, actually I am using bcp command-lind utility
here. I cant change the datatype in the production server..
Thanks,
Thiru
I have problem in BCP utility while exporting data to sql server.
It works in my system and my friend's system , but it gives the
following when its run in production
server.
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
BCP copy in failed
there are about 365000 records in the data file...
the date values go like below;
5/30/2007 9:27:35 AM
5/30/2007 9:28:37 AM
5/30/2007 5:28:16 PM
5/30/2007 5:28:32 PM
5/30/2007 5:28:16 PM
please help me for this problem...
thanks,
ThiruHi
/*
Kelly,Reynold,kelly@.reynold.com,5/30/2007 9:27:35 AM
John,Smith,bill@.smith.com,5/30/2007 9:27:35 AM
Sara,Parker,sara@.parker.com,5/30/2007 9:27:35 AM
*/
Make a column for dates as VARCHAR(50) and try insert
CREATE TABLE Tmp
(
stFName varchar (50) NOT NULL,
stLName varchar (50) NOT NULL,
stEmail varchar (100) NOT NULL,
dt varchar (100)
)
BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
SELECT * FROM Tmp
Later on you can convert VARCHAR to DATETIME datatype
"Thiru.net" <thirudotnet@.gmail.com> wrote in message
news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
> Hi,
> I have problem in BCP utility while exporting data to sql server.
> It works in my system and my friend's system , but it gives the
> following when its run in production
> server.
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> BCP copy in failed
> there are about 365000 records in the data file...
> the date values go like below;
> 5/30/2007 9:27:35 AM
> 5/30/2007 9:28:37 AM
> 5/30/2007 5:28:16 PM
> 5/30/2007 5:28:32 PM
> 5/30/2007 5:28:16 PM
> please help me for this problem...
> thanks,
> Thiru
>|||On Nov 1, 4:47 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> /*
> Kelly,Reynold,ke...@.reynold.com,5/30/2007 9:27:35 AM
> John,Smith,b...@.smith.com,5/30/2007 9:27:35 AM
> Sara,Parker,s...@.parker.com,5/30/2007 9:27:35 AM
> */
> Make a column for dates as VARCHAR(50) and try insert
> CREATE TABLE Tmp
> (
> stFName varchar (50) NOT NULL,
> stLName varchar (50) NOT NULL,
> stEmail varchar (100) NOT NULL,
> dt varchar (100)
> )
> BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
> SELECT * FROM Tmp
> Later on you can convert VARCHAR to DATETIME datatype
> "Thiru.net" <thirudot...@.gmail.com> wrote in message
> news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have problem in BCP utility while exporting data to sql server.
> > It works in my system and my friend's system , but it gives the
> > following when its run in production
> > server.
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > BCP copy in failed
> > there are about 365000 records in the data file...
> > the date values go like below;
> > 5/30/2007 9:27:35 AM
> > 5/30/2007 9:28:37 AM
> > 5/30/2007 5:28:16 PM
> > 5/30/2007 5:28:32 PM
> > 5/30/2007 5:28:16 PM
> > please help me for this problem...
> > thanks,
> > Thiru- Hide quoted text -
> - Show quoted text -
Hi,
Thanks for your reply, actually I am using bcp command-lind utility
here. I cant change the datatype in the production server..
Thanks,
Thiru
BCP invalid date
Hi,
I have problem in BCP utility while exporting data to sql server.
It works in my system and my friend's system , but it gives the
following when its run in production
server.
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
BCP copy in failed
there are about 365000 records in the data file...
the date values go like below;
5/30/2007 9:27:35 AM
5/30/2007 9:28:37 AM
5/30/2007 5:28:16 PM
5/30/2007 5:28:32 PM
5/30/2007 5:28:16 PM
please help me for this problem...
thanks,
ThiruThiru,
This could be because the regional settings of the server are different, or
your login's language setting on SQL Server is different. The problem is
that going into your server it is probably interpreting 5/30/2007 as: Day 5
of Month 30, which is invalid, of course.
If that is the problem, create a login on your SQL Server with a
default_language that uses the proper regional settings. For example:
Create Login AmericanImport WITH Password '####',
default_language='us_english'
Grant the AmericanImport login enough rights to bcp in the data to your
database, then run bcp using the AmericanImport login, which is using a
language setting that will properly interpret dates in this format.
RLF
"Thiru.net" <thirudotnet@.gmail.com> wrote in message
news:1193915612.728350.46490@.i13g2000prf.googlegroups.com...
> Hi,
> I have problem in BCP utility while exporting data to sql server.
> It works in my system and my friend's system , but it gives the
> following when its run in production
> server.
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> BCP copy in failed
> there are about 365000 records in the data file...
> the date values go like below;
> 5/30/2007 9:27:35 AM
> 5/30/2007 9:28:37 AM
> 5/30/2007 5:28:16 PM
> 5/30/2007 5:28:32 PM
> 5/30/2007 5:28:16 PM
> please help me for this problem...
> thanks,
> Thiru
>
I have problem in BCP utility while exporting data to sql server.
It works in my system and my friend's system , but it gives the
following when its run in production
server.
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
BCP copy in failed
there are about 365000 records in the data file...
the date values go like below;
5/30/2007 9:27:35 AM
5/30/2007 9:28:37 AM
5/30/2007 5:28:16 PM
5/30/2007 5:28:32 PM
5/30/2007 5:28:16 PM
please help me for this problem...
thanks,
ThiruThiru,
This could be because the regional settings of the server are different, or
your login's language setting on SQL Server is different. The problem is
that going into your server it is probably interpreting 5/30/2007 as: Day 5
of Month 30, which is invalid, of course.
If that is the problem, create a login on your SQL Server with a
default_language that uses the proper regional settings. For example:
Create Login AmericanImport WITH Password '####',
default_language='us_english'
Grant the AmericanImport login enough rights to bcp in the data to your
database, then run bcp using the AmericanImport login, which is using a
language setting that will properly interpret dates in this format.
RLF
"Thiru.net" <thirudotnet@.gmail.com> wrote in message
news:1193915612.728350.46490@.i13g2000prf.googlegroups.com...
> Hi,
> I have problem in BCP utility while exporting data to sql server.
> It works in my system and my friend's system , but it gives the
> following when its run in production
> server.
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> BCP copy in failed
> there are about 365000 records in the data file...
> the date values go like below;
> 5/30/2007 9:27:35 AM
> 5/30/2007 9:28:37 AM
> 5/30/2007 5:28:16 PM
> 5/30/2007 5:28:32 PM
> 5/30/2007 5:28:16 PM
> please help me for this problem...
> thanks,
> Thiru
>
2012年3月11日星期日
BCP in stored procedure
Hi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.
SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE
I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Can someone help me out?
Regards,
Bharathram GHi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.
SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE
I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Can someone help me out?
Regards,
Bharathram G
It seems many things are missing,plz provide full code of the stored pro.
Joydeep|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)
SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = '"C:\'
SET @.L_OUT_FILENAME = 'out.txt'
SET @.QUERY = '"SELECT * FROM DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE"'
SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE
END|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
Hi ,
Try this ...
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)
SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = 'C:\'
SET @.L_OUT_FILENAME = 'out.txt'
SET @.QUERY = 'SELECT * FROM test.dbo.PACK_DELETION_LOG WHERE convert(varchar(12),delete_dt,101) =convert(varchar(12),getdate(),101)'
print @.query
SELECT * FROM PACK_DELETION_LOG WHERE day(DELETE_DT) = day(@.L_CURRENTDATE)
SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+' -c -P '
print @.query
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY
end
Joydeep
I am using the following statements in a atored procedure to be executed in SQL Server 2000.
SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE
I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Can someone help me out?
Regards,
Bharathram GHi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.
SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE
I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Can someone help me out?
Regards,
Bharathram G
It seems many things are missing,plz provide full code of the stored pro.
Joydeep|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)
SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = '"C:\'
SET @.L_OUT_FILENAME = 'out.txt'
SET @.QUERY = '"SELECT * FROM DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE"'
SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE
END|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
Hi ,
Try this ...
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)
SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = 'C:\'
SET @.L_OUT_FILENAME = 'out.txt'
SET @.QUERY = 'SELECT * FROM test.dbo.PACK_DELETION_LOG WHERE convert(varchar(12),delete_dt,101) =convert(varchar(12),getdate(),101)'
print @.query
SELECT * FROM PACK_DELETION_LOG WHERE day(DELETE_DT) = day(@.L_CURRENTDATE)
SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+' -c -P '
print @.query
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY
end
Joydeep
BCP import error
I BCP out a table and then import back. While BCP IN, i get the following error:
#@. Row 303, Column 5: Invalid character value for cast specification @.#
#@. Row 296, Column 4: String data, right truncation @.#
Help plz!I think the data you try to insert into the table does not match the the ddl of the table itself. Are there any strange characters on row 303? What does row 303 look like anyway? The bcp-command you execute: does it consider strange characters and how does it map the file to the table? What's the layout of the table?|||Hi, thanx.
I don't suspect if there's any column binding problem, coz
I create a txt file from the same table, then truncate that table and import the data back from this text file.
The problem is multiple commas ",,," in the note-content column text.
Here's an example:
CREATE TABLE mydbo (id int, notedate datetime, note_content varchar(900), note_user char(3))
go
INSERT MYTAB
SELECT 1 , '2003-12-24 00:00:00' , 'AS PERINSTRUCTION ENTRD SC#@.12/16/03...71', 'SF2'
UNION ALL
SELECT 2 , '2004-02-07 00:00:00', 'CHECKED INF SCA@.#02/06/04.............17', 'SF2'
UNION ALL
SELECT 3 , '2004-03-26 00:00:00', 'GO TO, #, as per data .', 'SF2'
When i BCP out this table and then BCP into same table, i get the error for line 3. (right trucation)
How do i handle this, i have a CSV file that i have to import into a table, but data in single field contains multiple commas.
Howdy!|||Would it be possible for you to choose a different field seperator such as ; or [tab] ?
#@. Row 303, Column 5: Invalid character value for cast specification @.#
#@. Row 296, Column 4: String data, right truncation @.#
Help plz!I think the data you try to insert into the table does not match the the ddl of the table itself. Are there any strange characters on row 303? What does row 303 look like anyway? The bcp-command you execute: does it consider strange characters and how does it map the file to the table? What's the layout of the table?|||Hi, thanx.
I don't suspect if there's any column binding problem, coz
I create a txt file from the same table, then truncate that table and import the data back from this text file.
The problem is multiple commas ",,," in the note-content column text.
Here's an example:
CREATE TABLE mydbo (id int, notedate datetime, note_content varchar(900), note_user char(3))
go
INSERT MYTAB
SELECT 1 , '2003-12-24 00:00:00' , 'AS PERINSTRUCTION ENTRD SC#@.12/16/03...71', 'SF2'
UNION ALL
SELECT 2 , '2004-02-07 00:00:00', 'CHECKED INF SCA@.#02/06/04.............17', 'SF2'
UNION ALL
SELECT 3 , '2004-03-26 00:00:00', 'GO TO, #, as per data .', 'SF2'
When i BCP out this table and then BCP into same table, i get the error for line 3. (right trucation)
How do i handle this, i have a CSV file that i have to import into a table, but data in single field contains multiple commas.
Howdy!|||Would it be possible for you to choose a different field seperator such as ; or [tab] ?
2012年3月8日星期四
BCP genric error
I am trying to BCP locally, first I created the format file using
"BCP...nul..." and then issued the following bcp phrase :
bcp myDB.dbo.WEBFARM_DailyBandWidth_temp in
D:\Tracing\Charts\Dailybandwidth_ex061212.txt -T -f DailybandwidthOut.fmt
I am getting the genric error
**********************************
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
************************************
The sql server and sqlserver agent are running under a domain account which
is not in the local admin group
I changed this and added him to the local admin group, I get the same error
Thanks for your help
Hi I figured out what is the problem. It was indicating the fullpath for the
format file.
But now I am getting nothing entered in the table, when I issue the bcp I get
*****************
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
********************
So what is missing?
> I am trying to BCP locally, first I created the format file using
> "BCP...nul..." and then issued the following bcp phrase :
> bcp myDB.dbo.WEBFARM_DailyBandWidth_temp in
> D:\Tracing\Charts\Dailybandwidth_ex061212.txt -T -f DailybandwidthOut.fmt
>
> I am getting the genric error
> **********************************
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
> ************************************
> The sql server and sqlserver agent are running under a domain account which
> is not in the local admin group
> I changed this and added him to the local admin group, I get the same error
> Thanks for your help
|||SalamElias (eliassal@.online.nospam) writes:
> Hi I figured out what is the problem. It was indicating the fullpath for
> the format file. But now I am getting nothing entered in the table, when
> I issue the bcp I get
> *****************
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.) Total : 1
> ********************
> So what is missing?
My interpretation is that the file is empty.
How does the format file look like? What sort file is it? A text file?
Binary?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi,
My understanding of your issue is that:
You used BCP to transfer data, however you found zero rows copied.
If I have misunderstood, please let me know.
I am afraid that the information here is not enough to judge what caused
this issue.
Please first check the database table type definition matches your format
file; then check if there are some special characters in your data file.
I would like to provide you the bcp usage articles for your reference:
bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx
Also, you may mail me (changliw@.microsoft.com) your table definition, the
data file and the format file so that I can reproduce your issue and find a
resolution.
Thanks for using Microsoft Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, I have sent you an email
Thanks
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> You used BCP to transfer data, however you found zero rows copied.
> If I have misunderstood, please let me know.
> I am afraid that the information here is not enough to judge what caused
> this issue.
> Please first check the database table type definition matches your format
> file; then check if there are some special characters in your data file.
> I would like to provide you the bcp usage articles for your reference:
> bcp Utility
> http://msdn2.microsoft.com/en-us/library/ms162802.aspx
> Creating a Format File
> http://msdn2.microsoft.com/en-us/library/ms191516.aspx
> Also, you may mail me (changliw@.microsoft.com) your table definition, the
> data file and the format file so that I can reproduce your issue and find a
> resolution.
> Thanks for using Microsoft Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
|||Hi,
I got it.
This issue was caused by your format file not matching your data file. I
correct it with the following:
9.0
4
1 SQLCHAR 0 10 "|" 1 Server_Name
French_CI_AS
2 SQLCHAR 0 10 "|" 2 Date_Day
French_CI_AS
3 SQLNUMERIC 0 18 "|" 3 Incoming_Traffic
""
4 SQLNUMERIC 0 18 "\r\n" 4 Outgoing_Traffic
""
Please try it and see if this issue persists at your side.
Thanks for using Microsoft Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||Hi Salam,
It's my bad. I am so sorry that I omit checking the table. It is indeed a
strange behavior that the last two fields are populated with zero.
I am afraid that I need to consult our SQL team on this and I will let you
know their response as soon as possible.
For now, per my test, you can use SQLCHAR for all the columns:
9.0
4
1 SQLCHAR 0 10 "|" 1 Server_Name
French_CI_AS
2 SQLCHAR 0 10 "|" 2 Date_Day
French_CI_AS
3 SQLCHAR 0 9 "|" 3 Incoming_Traffic
""
4 SQLCHAR 0 9 "\r\n" 4 Outgoing_Traffic
""
The type SQLCHAR will be automatically converted to the database data type
when the data are imported into the table.
I am sorry again for bringing you any inconvenience. Sincerely hope this
suggestion can help resolve your problem.
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of assistance.
Merry Christmas!
Charles Wang
Microsoft Online Community Support
|||Hi Elias,
I am sorry for this late response. I just got the confirmation that the
type SQLNUMERIC is only for SQL native data type.
For a plain text file, all data types in the BCP format file should be
SQLCHAR.
For the explanation, please see Cindy's response as following.
Please feel free to let me know if need further assistance on this issue.
It's always our pleasure to be of assistance.
Merry Christmas!
Charles Wang
Microsoft Online Community Support
"BCP...nul..." and then issued the following bcp phrase :
bcp myDB.dbo.WEBFARM_DailyBandWidth_temp in
D:\Tracing\Charts\Dailybandwidth_ex061212.txt -T -f DailybandwidthOut.fmt
I am getting the genric error
**********************************
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
************************************
The sql server and sqlserver agent are running under a domain account which
is not in the local admin group
I changed this and added him to the local admin group, I get the same error
Thanks for your help
Hi I figured out what is the problem. It was indicating the fullpath for the
format file.
But now I am getting nothing entered in the table, when I issue the bcp I get
*****************
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
********************
So what is missing?
> I am trying to BCP locally, first I created the format file using
> "BCP...nul..." and then issued the following bcp phrase :
> bcp myDB.dbo.WEBFARM_DailyBandWidth_temp in
> D:\Tracing\Charts\Dailybandwidth_ex061212.txt -T -f DailybandwidthOut.fmt
>
> I am getting the genric error
> **********************************
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
> ************************************
> The sql server and sqlserver agent are running under a domain account which
> is not in the local admin group
> I changed this and added him to the local admin group, I get the same error
> Thanks for your help
|||SalamElias (eliassal@.online.nospam) writes:
> Hi I figured out what is the problem. It was indicating the fullpath for
> the format file. But now I am getting nothing entered in the table, when
> I issue the bcp I get
> *****************
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.) Total : 1
> ********************
> So what is missing?
My interpretation is that the file is empty.
How does the format file look like? What sort file is it? A text file?
Binary?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi,
My understanding of your issue is that:
You used BCP to transfer data, however you found zero rows copied.
If I have misunderstood, please let me know.
I am afraid that the information here is not enough to judge what caused
this issue.
Please first check the database table type definition matches your format
file; then check if there are some special characters in your data file.
I would like to provide you the bcp usage articles for your reference:
bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx
Also, you may mail me (changliw@.microsoft.com) your table definition, the
data file and the format file so that I can reproduce your issue and find a
resolution.
Thanks for using Microsoft Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, I have sent you an email
Thanks
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> You used BCP to transfer data, however you found zero rows copied.
> If I have misunderstood, please let me know.
> I am afraid that the information here is not enough to judge what caused
> this issue.
> Please first check the database table type definition matches your format
> file; then check if there are some special characters in your data file.
> I would like to provide you the bcp usage articles for your reference:
> bcp Utility
> http://msdn2.microsoft.com/en-us/library/ms162802.aspx
> Creating a Format File
> http://msdn2.microsoft.com/en-us/library/ms191516.aspx
> Also, you may mail me (changliw@.microsoft.com) your table definition, the
> data file and the format file so that I can reproduce your issue and find a
> resolution.
> Thanks for using Microsoft Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
|||Hi,
I got it.
This issue was caused by your format file not matching your data file. I
correct it with the following:
9.0
4
1 SQLCHAR 0 10 "|" 1 Server_Name
French_CI_AS
2 SQLCHAR 0 10 "|" 2 Date_Day
French_CI_AS
3 SQLNUMERIC 0 18 "|" 3 Incoming_Traffic
""
4 SQLNUMERIC 0 18 "\r\n" 4 Outgoing_Traffic
""
Please try it and see if this issue persists at your side.
Thanks for using Microsoft Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||Hi Salam,
It's my bad. I am so sorry that I omit checking the table. It is indeed a
strange behavior that the last two fields are populated with zero.
I am afraid that I need to consult our SQL team on this and I will let you
know their response as soon as possible.
For now, per my test, you can use SQLCHAR for all the columns:
9.0
4
1 SQLCHAR 0 10 "|" 1 Server_Name
French_CI_AS
2 SQLCHAR 0 10 "|" 2 Date_Day
French_CI_AS
3 SQLCHAR 0 9 "|" 3 Incoming_Traffic
""
4 SQLCHAR 0 9 "\r\n" 4 Outgoing_Traffic
""
The type SQLCHAR will be automatically converted to the database data type
when the data are imported into the table.
I am sorry again for bringing you any inconvenience. Sincerely hope this
suggestion can help resolve your problem.
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of assistance.
Merry Christmas!
Charles Wang
Microsoft Online Community Support
|||Hi Elias,
I am sorry for this late response. I just got the confirmation that the
type SQLNUMERIC is only for SQL native data type.
For a plain text file, all data types in the BCP format file should be
SQLCHAR.
For the explanation, please see Cindy's response as following.
Please feel free to let me know if need further assistance on this issue.
It's always our pleasure to be of assistance.
Merry Christmas!
Charles Wang
Microsoft Online Community Support
订阅：
博文 (Atom)