The running of my bcp (with queryout) is aborting, with the message below. A
t
first I identified that it happened only with column with NULL value, but no
w
I realize this occurrence happened in other field without NULL value.
Anyone has suggestions ? Thanks a lot
My format file has the following contents : 8.0 (version) 2 (number of
columns)
1 SQLCHAR 0 20 "\t" 1 numeroaj Latin1_General_CI_AS
2 SQLCHAR 0 54 "\r\n" 2 acervoespecializada Latin1_General_CI_AS
My bad run :
D:\Users\sql>bcp "SELECT numeroaj, acervoespecializada from
Siga.dbo.vwHerancaJa
cente" queryout C:\dts\hjacente.txt -f d:\users\sql\pgm3.fmt -Smyserver
-Umyuser -Pmypwd
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Erro de E/S ao ler o arquivo no
forma
to BCP ==> Translating... Error of I/O when read the format filewhere do you run this bcp statement, on the client or on the server itself.
"d:\" must be local to where ever you run the bcp statement.
-oj
"Adalberto Andrade" <Adalberto Andrade@.discussions.microsoft.com> wrote in
message news:014C3C25-592D-4A88-A320-325C52D49D2A@.microsoft.com...
> The running of my bcp (with queryout) is aborting, with the message below.
> At
> first I identified that it happened only with column with NULL value, but
> now
> I realize this occurrence happened in other field without NULL value.
> Anyone has suggestions ? Thanks a lot
> My format file has the following contents : 8.0 (version) 2 (number of
> columns)
> 1 SQLCHAR 0 20 "\t" 1 numeroaj Latin1_General_CI_AS
> 2 SQLCHAR 0 54 "\r\n" 2 acervoespecializada
> Latin1_General_CI_AS
> My bad run :
> D:\Users\sql>bcp "SELECT numeroaj, acervoespecializada from
> Siga.dbo.vwHerancaJa
> cente" queryout C:\dts\hjacente.txt -f d:\users\sql\pgm3.fmt -Smyserver
> -Umyuser -Pmypwd
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Erro de E/S ao ler o arquivo no
> forma
> to BCP ==> Translating... Error of I/O when read the format file|||Hi oj,
On the client. And "d:\" is a local drive (in my machine). With
others columns it worked OK.
Thanks
Adalberto Andrade
"oj" wrote:
> where do you run this bcp statement, on the client or on the server itself
.
> "d:\" must be local to where ever you run the bcp statement.
> --
> -oj
>
> "Adalberto Andrade" <Adalberto Andrade@.discussions.microsoft.com> wrote in
> message news:014C3C25-592D-4A88-A320-325C52D49D2A@.microsoft.com...
>
>|||Adalberto,
Please check to see if there is a carriage return at the
end of your format file and that there are no extra tabs
or anything else in it. Also be sure the format file
is not open in an editor when you run the command.
The error message mentions the format file, not the
data file, so I think the problem is with the format file.
Steve Kass
Drew University
Adalberto Andrade wrote:
>Hi oj,
> On the client. And "d:\" is a local drive (in my machine). With
>others columns it worked OK.
>
> Thanks
> Adalberto Andrade
>
>
>"oj" wrote:
>
>|||Steve,
I made a complete revision of all components of my environment.
Things like : cr (carriage return), opened format file and others wrongs
caracters inside of format file are not the problem. I am thankful for yours
suggestions,but the problem continue. Now I substituted the third column of
my output file and the bcp utility started the copy, but the generated file
has mixed data with stranger caracters. This new column has valids datas and
also null values in some registers and it is the great difference between th
e
others columns (first e second ones). Without this third column everything
work 100% OK. I already try to change the value of prefix length (field of
the format file) to -1 or 2 with the hope to solve it, but the output
generated file continue with stranger and mixed caracters (like ASCII
caracters). I really don't have any idea of what I can do to put it to work.
I am not sure, but perhaps I will need to make other configuration in my
format file, but exactly what ? Do you have other help for me ?
Thanks again
Adalberto Andrade
"Steve Kass" wrote:
> Adalberto,
> Please check to see if there is a carriage return at the
> end of your format file and that there are no extra tabs
> or anything else in it. Also be sure the format file
> is not open in an editor when you run the command.
> The error message mentions the format file, not the
> data file, so I think the problem is with the format file.
> Steve Kass
> Drew University
> Adalberto Andrade wrote:
>
>|||Adalberto,
I am not sure what the problem is, but here are three separate
suggestions.
1. Try to use bcp without a format file, since TAB and NEWLINE
are the defaults for bcp. If this creates a Unicode file, you will need
to change the format file to say SQLNCHAR instead of SQLCHAR,
and you will also need to put the Unicode two-byte signature into
the beginning of the file yourself, since bcp does not do this for you.
2. Be sure the format file is saved as ASCII, not Unicode, then try
again with the format file.
3. Verify the data lengths and types of the output,
3A. Run this and provide the output.
select top 1
numeroaj, acervoespecializada
into CheckTypesTable
from Siga.dbo.vwHerancaJacente
select * from CheckTypesTable
3B. In Query Analyzer, refresh the current database
and for [CheckTypesTable] choose "Script Table To
New Window" to verify the data types of these columns
and provide the output.
3C. After doing this, you can DROP the table CheckTypesTable.
It might help if you post the definition of Siga.dbo.vwHerancaJa.
(If it is a view, also post CREATE TABLE statements from the
tables it uses for the columns numeroaj and acervoespecializada
(and the third column, since at one point you mention three columns.
Also, when you have three columns, what is your query?)
SK
Adalberto Andrade wrote:
>Steve,
> I made a complete revision of all components of my environment.
>Things like : cr (carriage return), opened format file and others wrongs
>caracters inside of format file are not the problem. I am thankful for your
s
>suggestions,but the problem continue. Now I substituted the third column of
>my output file and the bcp utility started the copy, but the generated file
>has mixed data with stranger caracters. This new column has valids datas an
d
>also null values in some registers and it is the great difference between t
he
>others columns (first e second ones). Without this third column everything
>work 100% OK. I already try to change the value of prefix length (field of
>the format file) to -1 or 2 with the hope to solve it, but the output
>generated file continue with stranger and mixed caracters (like ASCII
>caracters). I really don't have any idea of what I can do to put it to work
.
>I am not sure, but perhaps I will need to make other configuration in my
>format file, but exactly what ? Do you have other help for me ?
>
> Thanks again
>Adalberto Andrade
>"Steve Kass" wrote:
>
>|||Steve,
Forgive me for delay in my reply. I was very busy. Let 's go. Really
you are correct. When I ran the bcp utility in the prompt without my format
file, it show the message explaining that happened a truncate. In true there
was a difference between the data length of one column and your value define
d
for this size in the format file. Summarizing, the problem is over and your
suggesntions 1 and 3 were very helpful.
Thanks a lot
Adalberto
Andrade
Rio de Janeiro's
City Hall
"Steve Kass" wrote:
> Adalberto,
> I am not sure what the problem is, but here are three separate
> suggestions.
> 1. Try to use bcp without a format file, since TAB and NEWLINE
> are the defaults for bcp. If this creates a Unicode file, you will need
> to change the format file to say SQLNCHAR instead of SQLCHAR,
> and you will also need to put the Unicode two-byte signature into
> the beginning of the file yourself, since bcp does not do this for you.
> 2. Be sure the format file is saved as ASCII, not Unicode, then try
> again with the format file.
> 3. Verify the data lengths and types of the output,
> 3A. Run this and provide the output.
> select top 1
> numeroaj, acervoespecializada
> into CheckTypesTable
> from Siga.dbo.vwHerancaJacente
> select * from CheckTypesTable
> 3B. In Query Analyzer, refresh the current database
> and for [CheckTypesTable] choose "Script Table To
> New Window" to verify the data types of these columns
> and provide the output.
> 3C. After doing this, you can DROP the table CheckTypesTable.
>
> It might help if you post the definition of Siga.dbo.vwHerancaJa.
> (If it is a view, also post CREATE TABLE statements from the
> tables it uses for the columns numeroaj and acervoespecializada
> (and the third column, since at one point you mention three columns.
> Also, when you have three columns, what is your query?)
> SK
> Adalberto Andrade wrote:
>
>
Showing posts with label bcp. Show all posts
Showing posts with label bcp. Show all posts
Wednesday, March 28, 2012
Problem with BCP in trigger, how to do it?
Hello all,
I′m pretty new to T-SQL, so please bear with me.
I′m trying to output the temp "inserted" table avalible in the trigger to a text file.
When this trigger executes, the server seems to enter a never ending query.
If i comment the last three lines (declare... select... exec...) the trigger works fine,
so it seems to be a problem with the BCP part of the trigger.
What is wrong here? If you have better suggestions on how to
accoplish the same thing, please feel free to share!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [Skapa_Transfil]
ON [dbo].[Products]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into dbo.TempProducts (ProdNo, CountryOfOrigin)
select prodno, CountryOfOrigin
from inserted
declare @.sql varchar(8000)
select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'
exec master..xp_cmdshell @.sql
END
I've managed to replicate this behaviour. The cause seems to be that the TRIGGER is taking out an exclusive lock on the rows that are being inserted into TempProducts and therefore the BCP statement is unable to obtain a shared and so cannot read the data. I can't see a way around this unfortunately.
Would you be able to handle this logic in a stored procedure,similar to the following:
Code Snippet
create procedure insertandexport
@.int1 int, @.int2 int
as
insert into tempproducts
values (@.int1, @.int2)
if @.@.rowcount > 0
begin
declare @.sql varchar(8000)
select @.sql = 'bcp tempdb..TempProducts out c:\fil.txt -c -t, -U"User" -P"password" -S"YouServer"'
exec master..xp_cmdshell @.sql
end
The trigger runs inside a transaction, so the "insert into" statement is also inside that transaction and could be blocking the table or index. The execution of bcp is out of that transaction and has to wait till the blocking has gone if it is running in "read committed" isolation level.
AMB
|||Since it would be acceptable with some delay of the updates from tempProducts to fil.txt, would it be a good idea to do something like this?
The trigger keeps updating the tempProducts table whenever the trigger fires, like so:
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [Skapa_Transfil]
ON [dbo].[Products]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into dbo.TempProducts (ProdNo, CountryOfOrigin)
select prodno, CountryOfOrigin
from inserted
END
Then I schedule the following sql script to run using "sqlcmd -i exportfromtempproducts" once a minute or so.
Code Snippet
begin transaction
declare @.sql varchar(8000)
select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'
exec master..xp_cmdshell @.sql
go
use avk
go
delete
from tempProducts
go
commit transaction
I tried this and to me it seems to work. Since I run the bcp and delete in one transaction, the trigger would never be able to insert data into tempProducts between the bcp and the delete?
Thoughts someone?
|||I think the key to this is setting your TRANSATION ISOLATION LEVEL to SNAPSHOT. This will guarentee that the you will only be working with the rows as they were at the start of the transaction. Otherwise, no exclusive locks will be put on the tempProducts table and so you could get rows inserted after the bcp statement which would then be deleted.
Simulate this behaviour by executing the stages in 2 separate query windows, step by step (ie being tran, run the bcp statement only, update more rows in products in the other window, come back and then run the delete statement etc).
Check Books Online for a more thorough explanation of isolation levels.
As an aside, will you be overwriting fil.txt every minute? Does that matter?
Let us know how you get on!
|||I made some changes to the query in order to get different file names for each execution,
naming the file with date and time.
How would you go about to execute the query in "separate stages" as you mention above?
I′m used to working with break-points from VB, but I can′t seem to find any similar feature for T-SQL 
EDIT: For clarification, I also did the
ALTER DATABASE AVK
SET ALLOW_SNAPSHOT_ISOLATION
Code Snippet
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
DECLARE @.date char(8)
DECLARE @.time char(8)
DECLARE @.sql VARCHAR(8000)
SELECT @.date = CONVERT(char(8), getdate(),112)
SELECT @.time = CONVERT(char(8), getdate(),108)
SELECT @.time = REPLACE(@.time,':','')
SELECT @.time
DECLARE @.dt char(14)
SELECT @.dt = @.date + '_' + @.time
SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'
EXEC master..xp_cmdshell @.sql
GO
USE AVK
GO
DELETE
FROM tempProducts
GO
COMMIT TRANSACTION
|||For testing purposes, i did the following:
Paste the whole block into a query window in SSMS and then just highlght and execute the individual sections:
ie hightlight this and execute
Code Snippet
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
DECLARE @.date char(8)
DECLARE @.time char(8)
DECLARE @.sql VARCHAR(8000)
SELECT @.date = CONVERT(char(8), getdate(),112)
SELECT @.time = CONVERT(char(8), getdate(),108)
SELECT @.time = REPLACE(@.time,':','')
SELECT @.time
DECLARE @.dt char(14)
SELECT @.dt = @.date + '_' + @.time
SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'
EXEC master..xp_cmdshell @.sql
GO
Then go into your another query window (which is a separate transaction) and execute an update statement on products
Then return to the main window, highlight and execute the final section
Code Snippet
USE AVK
GO
DELETE
FROM tempProducts
GO
COMMIT TRANSACTION
This will give you the behaviour as if an update to your products tabla occured while your second transaction was running and will allow you to view how the different isolation levels affect your results.
As for debugging a la VB, i think you are able to use this facility for stored procedures in Visual Studio but not SSMS.
HTH!
WOHO!
Works like a charm!
Tried updating 3 records, then running the BCP-part of the query. 3 rows out as expected.
The updated 3 more records, did a select * on tempProducts, which now contained 6 records.
Finally ran the delete part of the query, checked tempProducts again. And yep, my last three updates where still there!
Thanks a bunch for all the help!
Problem with BCP in trigger, how to do it?
Hello all,
I′m pretty new to T-SQL, so please bear with me.
I′m trying to output the temp "inserted" table avalible in the trigger to a text file.
When this trigger executes, the server seems to enter a never ending query.
If i comment the last three lines (declare... select... exec...) the trigger works fine,
so it seems to be a problem with the BCP part of the trigger.
What is wrong here? If you have better suggestions on how to
accoplish the same thing, please feel free to share!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [Skapa_Transfil]
ON [dbo].[Products]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into dbo.TempProducts (ProdNo, CountryOfOrigin)
select prodno, CountryOfOrigin
from inserted
declare @.sql varchar(8000)
select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'
exec master..xp_cmdshell @.sql
END
I've managed to replicate this behaviour. The cause seems to be that the TRIGGER is taking out an exclusive lock on the rows that are being inserted into TempProducts and therefore the BCP statement is unable to obtain a shared and so cannot read the data. I can't see a way around this unfortunately.
Would you be able to handle this logic in a stored procedure,similar to the following:
Code Snippet
create procedure insertandexport
@.int1 int, @.int2 int
as
insert into tempproducts
values (@.int1, @.int2)
if @.@.rowcount > 0
begin
declare @.sql varchar(8000)
select @.sql = 'bcp tempdb..TempProducts out c:\fil.txt -c -t, -U"User" -P"password" -S"YouServer"'
exec master..xp_cmdshell @.sql
end
The trigger runs inside a transaction, so the "insert into" statement is also inside that transaction and could be blocking the table or index. The execution of bcp is out of that transaction and has to wait till the blocking has gone if it is running in "read committed" isolation level.
AMB
|||Since it would be acceptable with some delay of the updates from tempProducts to fil.txt, would it be a good idea to do something like this?
The trigger keeps updating the tempProducts table whenever the trigger fires, like so:
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [Skapa_Transfil]
ON [dbo].[Products]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert into dbo.TempProducts (ProdNo, CountryOfOrigin)
select prodno, CountryOfOrigin
from inserted
END
Then I schedule the following sql script to run using "sqlcmd -i exportfromtempproducts" once a minute or so.
Code Snippet
begin transaction
declare @.sql varchar(8000)
select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'
exec master..xp_cmdshell @.sql
go
use avk
go
delete
from tempProducts
go
commit transaction
I tried this and to me it seems to work. Since I run the bcp and delete in one transaction, the trigger would never be able to insert data into tempProducts between the bcp and the delete?
Thoughts someone?
|||I think the key to this is setting your TRANSATION ISOLATION LEVEL to SNAPSHOT. This will guarentee that the you will only be working with the rows as they were at the start of the transaction. Otherwise, no exclusive locks will be put on the tempProducts table and so you could get rows inserted after the bcp statement which would then be deleted.
Simulate this behaviour by executing the stages in 2 separate query windows, step by step (ie being tran, run the bcp statement only, update more rows in products in the other window, come back and then run the delete statement etc).
Check Books Online for a more thorough explanation of isolation levels.
As an aside, will you be overwriting fil.txt every minute? Does that matter?
Let us know how you get on!
|||I made some changes to the query in order to get different file names for each execution,
naming the file with date and time.
How would you go about to execute the query in "separate stages" as you mention above?
I′m used to working with break-points from VB, but I can′t seem to find any similar feature for T-SQL
EDIT: For clarification, I also did the
ALTER DATABASE AVK
SET ALLOW_SNAPSHOT_ISOLATION
Code Snippet
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
DECLARE @.date char(8)
DECLARE @.time char(8)
DECLARE @.sql VARCHAR(8000)
SELECT @.date = CONVERT(char(8), getdate(),112)
SELECT @.time = CONVERT(char(8), getdate(),108)
SELECT @.time = REPLACE(@.time,':','')
SELECT @.time
DECLARE @.dt char(14)
SELECT @.dt = @.date + '_' + @.time
SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'
EXEC master..xp_cmdshell @.sql
GO
USE AVK
GO
DELETE
FROM tempProducts
GO
COMMIT TRANSACTION
|||For testing purposes, i did the following:
Paste the whole block into a query window in SSMS and then just highlght and execute the individual sections:
ie hightlight this and execute
Code Snippet
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
DECLARE @.date char(8)
DECLARE @.time char(8)
DECLARE @.sql VARCHAR(8000)
SELECT @.date = CONVERT(char(8), getdate(),112)
SELECT @.time = CONVERT(char(8), getdate(),108)
SELECT @.time = REPLACE(@.time,':','')
SELECT @.time
DECLARE @.dt char(14)
SELECT @.dt = @.date + '_' + @.time
SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'
EXEC master..xp_cmdshell @.sql
GO
Then go into your another query window (which is a separate transaction) and execute an update statement on products
Then return to the main window, highlight and execute the final section
Code Snippet
USE AVK
GO
DELETE
FROM tempProducts
GO
COMMIT TRANSACTION
This will give you the behaviour as if an update to your products tabla occured while your second transaction was running and will allow you to view how the different isolation levels affect your results.
As for debugging a la VB, i think you are able to use this facility for stored procedures in Visual Studio but not SSMS.
HTH!
WOHO!
Works like a charm!
Tried updating 3 records, then running the BCP-part of the query. 3 rows out as expected.
The updated 3 more records, did a select * on tempProducts, which now contained 6 records.
Finally ran the delete part of the query, checked tempProducts again. And yep, my last three updates where still there!
Thanks a bunch for all the help!
Problem with BCP command
I am trying to execute following command using xp_cmdshell
EXEC master..xp_cmdshell 'bcp "select nc_value from fileade.dbo.nms_command (nolock) order by nc_pk desc" queryout \\filw\ExternalTools\NMS3\reports\NMS3_20070525_051507.txt -c -S"FILW2K" -Uabc -Pabc'
but the error message returned is
Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.
usage: bcp [[database_name.]owner.]table_name[
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I interfaces_file] [-S server]
[-a display_charset] [-q datafile_charset] [-z language] [-v]
[-A packet size] [-J client character set]
[-T text or image size] [-E] [-g id_start_value] [-N] [-X]
[-M LabelName LabelValue] [-labeled]
[-K keytab_file] [-R remote_server_principal]
[-V [security_options]] [-Z security_mechanism] [-Q]
NULL
Further the same command is running successfully in my DEV environment.
That sounds like you have a SQL 7 bcp.exe in your path. Run "bcp -v" on both machines and make sure the versions match.|||Wrong forum. Moving to Transact-SQL.|||
When the path for the output file contains spaces or other 'non-acceptable' characters, you 'should' enclose it in double quotes.
The Server name does not need to be in double quotes.
|||Hi Tom,
I have checked the version on all the three plateform
DEV : 8.00.382
PROD : 8.00.382
TestPROD : 8.00.382
and version are same.
|||Hi Arnie,
The command is giving problem on Production environment only while in DEV & staging server working perfectly wheather to export file in local drive or in a network drive.
|||On the production server, does the SQL Agent account have permissions for the file locations?|||Not Sure how to check this,
BUt we have checkred that other scheduled JOBS which also export some file from PROD DB server to other server are working OK, Further the BCP command is executed via windows service
Windows Service
Batch File
Stored Procedure
BCP Command.
|||Somewhere you have an old bcp.exe which is being picked up. The error message you posted above is from the SQL 7 bcp.exe program, not 8.00.382. The "queryout" option was added in SQL 2000 bcp.exe.Search your hard drive for bcp.exe and remove anything not in C:\Program Files\Microsoft SQL Server\90 (or 80)\tools\binn.
|||
Hi Tom,
Your check point really help me to found the the problem although Production Hard Drive was not having the bcp.exe of version 7 instead the server was having Sybase BCP.EXE also , so whenever the window service try to execute the BCP command instead of picking up the SQL Server BCP path it was picking the Syabse BCP due to which the error was coming.
Thanks again for your help.
|||Good. I am glad you found it.Problem with BCP
Hi,
I execute this command in CMD:
bcp pubs..authors out c:\test.dat -T -n
I get the following error:
Code page 720 is not supported by SQL Server.
Unable to resolve column level collation.
I'm using Personal Edition on Win XP Prof. I have not modified anything in
pubs database.
Any help would be greatly appreciated.
LeilaHi
You may want to try the -C option to specify 720 or RAW. Alternatively a
format file specifying the collation may work.
http://msdn.microsoft.com/library/d...>
bcp_1y43.asp
John
"Leila" <leilas@.hotpop.com> wrote in message
news:eUUw97aTFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> I execute this command in CMD:
> bcp pubs..authors out c:\test.dat -T -n
> I get the following error:
> Code page 720 is not supported by SQL Server.
> Unable to resolve column level collation.
> I'm using Personal Edition on Win XP Prof. I have not modified anything in
> pubs database.
> Any help would be greatly appreciated.
> Leila
>
>|||Thanks John!
RAW worked :-)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:#gQu8whTFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi
> You may want to try the -C option to specify 720 or RAW. Alternatively a
> format file specifying the collation may work.
>
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_impt_bcp_1y43.asp
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eUUw97aTFHA.2172@.tk2msftngp13.phx.gbl...
in
>
I execute this command in CMD:
bcp pubs..authors out c:\test.dat -T -n
I get the following error:
Code page 720 is not supported by SQL Server.
Unable to resolve column level collation.
I'm using Personal Edition on Win XP Prof. I have not modified anything in
pubs database.
Any help would be greatly appreciated.
LeilaHi
You may want to try the -C option to specify 720 or RAW. Alternatively a
format file specifying the collation may work.
http://msdn.microsoft.com/library/d...>
bcp_1y43.asp
John
"Leila" <leilas@.hotpop.com> wrote in message
news:eUUw97aTFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> I execute this command in CMD:
> bcp pubs..authors out c:\test.dat -T -n
> I get the following error:
> Code page 720 is not supported by SQL Server.
> Unable to resolve column level collation.
> I'm using Personal Edition on Win XP Prof. I have not modified anything in
> pubs database.
> Any help would be greatly appreciated.
> Leila
>
>|||Thanks John!
RAW worked :-)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:#gQu8whTFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi
> You may want to try the -C option to specify 720 or RAW. Alternatively a
> format file specifying the collation may work.
>
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_impt_bcp_1y43.asp
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eUUw97aTFHA.2172@.tk2msftngp13.phx.gbl...
in
>
problem with BCP
Dear all,
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Dollerhi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values '
-U -P
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> command working from a command prompt and then fit that into the xp_cmdshell
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> {password}'
> John
>
> "doller" wrote:
> > Dear all,
> >
> > I created a BCP file from SQL Server2000 with .BCP Extension.
> > Now i am trying to load data in the SQL Server2005 Database table.
> > First was getting the error of remote connection which i solved .
> > Now i am getting this below error
> >
> > SQLState = HY000, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > NULL
> >
> > I am executing this code
> > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> >
> > Pls help
> >
> > from
> > Doller
> >
> >|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online.
> John
> "John Bell" wrote:
> > Hi
> >
> > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > command working from a command prompt and then fit that into the xp_cmdshell
> > call.
> >
> > Try the following adding the correct loginid and password:
> >
> > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > {password}'
> >
> > John
> >
> >
> > "doller" wrote:
> >
> > > Dear all,
> > >
> > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > Now i am trying to load data in the SQL Server2005 Database table.
> > > First was getting the error of remote connection which i solved .
> > > Now i am getting this below error
> > >
> > > SQLState = HY000, NativeError = 0
> > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > NULL
> > >
> > > I am executing this code
> > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > >
> > > Pls help
> > >
> > > from
> > > Doller
> > >
> > >|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
--
current location: alicante (es)
"doller" wrote:
> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
> > Hi
> >
> > This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> > please let us know what it is supposed to be!
> >
> > You can check the format and parameters of the BCP utility in books online.
> >
> > John
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > > command working from a command prompt and then fit that into the xp_cmdshell
> > > call.
> > >
> > > Try the following adding the correct loginid and password:
> > >
> > > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > > {password}'
> > >
> > > John
> > >
> > >
> > > "doller" wrote:
> > >
> > > > Dear all,
> > > >
> > > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > > Now i am trying to load data in the SQL Server2005 Database table.
> > > > First was getting the error of remote connection which i solved .
> > > > Now i am getting this below error
> > > >
> > > > SQLState = HY000, NativeError = 0
> > > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > > NULL
> > > >
> > > > I am executing this code
> > > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > > >
> > > > Pls help
> > > >
> > > > from
> > > > Doller
> > > >
> > > >
>|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:
> > Hi all,
> >
> > EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> >
> > i resolved the connection issue but now i am getting diffrent type of
> > error
> > SQLState = HY000, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > NULL
> >
> > pls help
> >
> > from
> > doller
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> > > please let us know what it is supposed to be!
> > >
> > > You can check the format and parameters of the BCP utility in books online.
> > >
> > > John
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > > > command working from a command prompt and then fit that into the xp_cmdshell
> > > > call.
> > > >
> > > > Try the following adding the correct loginid and password:
> > > >
> > > > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > > > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > > > {password}'
> > > >
> > > > John
> > > >
> > > >
> > > > "doller" wrote:
> > > >
> > > > > Dear all,
> > > > >
> > > > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > > > Now i am trying to load data in the SQL Server2005 Database table.
> > > > > First was getting the error of remote connection which i solved .
> > > > > Now i am getting this below error
> > > > >
> > > > > SQLState = HY000, NativeError = 0
> > > > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > > > NULL
> > > > >
> > > > > I am executing this code
> > > > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > > > >
> > > > > Pls help
> > > > >
> > > > > from
> > > > > Doller
> > > > >
> > > > >
> >
> >
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Dollerhi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values '
-U -P
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> command working from a command prompt and then fit that into the xp_cmdshell
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> {password}'
> John
>
> "doller" wrote:
> > Dear all,
> >
> > I created a BCP file from SQL Server2000 with .BCP Extension.
> > Now i am trying to load data in the SQL Server2005 Database table.
> > First was getting the error of remote connection which i solved .
> > Now i am getting this below error
> >
> > SQLState = HY000, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > NULL
> >
> > I am executing this code
> > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> >
> > Pls help
> >
> > from
> > Doller
> >
> >|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online.
> John
> "John Bell" wrote:
> > Hi
> >
> > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > command working from a command prompt and then fit that into the xp_cmdshell
> > call.
> >
> > Try the following adding the correct loginid and password:
> >
> > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > {password}'
> >
> > John
> >
> >
> > "doller" wrote:
> >
> > > Dear all,
> > >
> > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > Now i am trying to load data in the SQL Server2005 Database table.
> > > First was getting the error of remote connection which i solved .
> > > Now i am getting this below error
> > >
> > > SQLState = HY000, NativeError = 0
> > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > NULL
> > >
> > > I am executing this code
> > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > >
> > > Pls help
> > >
> > > from
> > > Doller
> > >
> > >|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
--
current location: alicante (es)
"doller" wrote:
> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
> > Hi
> >
> > This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> > please let us know what it is supposed to be!
> >
> > You can check the format and parameters of the BCP utility in books online.
> >
> > John
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > > command working from a command prompt and then fit that into the xp_cmdshell
> > > call.
> > >
> > > Try the following adding the correct loginid and password:
> > >
> > > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > > {password}'
> > >
> > > John
> > >
> > >
> > > "doller" wrote:
> > >
> > > > Dear all,
> > > >
> > > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > > Now i am trying to load data in the SQL Server2005 Database table.
> > > > First was getting the error of remote connection which i solved .
> > > > Now i am getting this below error
> > > >
> > > > SQLState = HY000, NativeError = 0
> > > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > > NULL
> > > >
> > > > I am executing this code
> > > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > > >
> > > > Pls help
> > > >
> > > > from
> > > > Doller
> > > >
> > > >
>|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:
> > Hi all,
> >
> > EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> >
> > i resolved the connection issue but now i am getting diffrent type of
> > error
> > SQLState = HY000, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > NULL
> >
> > pls help
> >
> > from
> > doller
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> > > please let us know what it is supposed to be!
> > >
> > > You can check the format and parameters of the BCP utility in books online.
> > >
> > > John
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > > > command working from a command prompt and then fit that into the xp_cmdshell
> > > > call.
> > > >
> > > > Try the following adding the correct loginid and password:
> > > >
> > > > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > > > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > > > {password}'
> > > >
> > > > John
> > > >
> > > >
> > > > "doller" wrote:
> > > >
> > > > > Dear all,
> > > > >
> > > > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > > > Now i am trying to load data in the SQL Server2005 Database table.
> > > > > First was getting the error of remote connection which i solved .
> > > > > Now i am getting this below error
> > > > >
> > > > > SQLState = HY000, NativeError = 0
> > > > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > > > NULL
> > > > >
> > > > > I am executing this code
> > > > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > > > >
> > > > > Pls help
> > > > >
> > > > > from
> > > > > Doller
> > > > >
> > > > >
> >
> >
Problem with bcp
Hi all,
I'm using bcp from the command line to backup and
restore the data in a table.
To backup, I run a command like this:
bcp TestDatabase.dbo.tblTest out C:\test.txt -n
-S TestServer -U sa -P pwd
and this works fine. However, when I try to restore
the data that I have backed up by doing this
bcp TestDatabase.dbo.tblTest in C:\test.txt -n
-S TestServer -U sa -P pwd
I get the following error:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near 'varchar'.
Does anyone have any idea what the problem might
be?
TIA,
--
Akin
aknak at aksoto dot idps dot co dot ukuse the -n switch so you get a native dump.
Then, you'll have no trouble restoring as long as the schema is the same.
Otherwise, you've got to use format files.
(BTW, as a side not, BCP is not a backup utility).
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> Hi all,
> I'm using bcp from the command line to backup and
> restore the data in a table.
> To backup, I run a command like this:
> bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> -S TestServer -U sa -P pwd
> and this works fine. However, when I try to restore
> the data that I have backed up by doing this
> bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> -S TestServer -U sa -P pwd
> I get the following error:
> SQLState = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> syntax near 'varchar'.
> Does anyone have any idea what the problem might
> be?
> TIA,
>
> --
> Akin
> aknak at aksoto dot idps dot co dot uk
>
>|||Hello James,
Thanks for your reply.
If you see the command line I typed out, you will see that I
*am* using the -n switch, but the error still occurs.
Any other ideas?
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> use the -n switch so you get a native dump.
> Then, you'll have no trouble restoring as long as the schema is the same.
> Otherwise, you've got to use format files.
> (BTW, as a side not, BCP is not a backup utility).
> James Hokes
>
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > Hi all,
> >
> > I'm using bcp from the command line to backup and
> > restore the data in a table.
> >
> > To backup, I run a command like this:
> >
> > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > and this works fine. However, when I try to restore
> > the data that I have backed up by doing this
> >
> > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > I get the following error:
> >
> > SQLState = 37000, NativeError = 170
> > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> > syntax near 'varchar'.
> >
> > Does anyone have any idea what the problem might
> > be?
> >
> > TIA,
> >
> >
> > --
> > Akin
> >
> > aknak at aksoto dot idps dot co dot uk
> >
> >
> >
>|||Doh!
Thanks for not flaming - chalk one up to 'reading too fast, eh?'
Now I'm well and fully stumped.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> Hello James,
> Thanks for your reply.
> If you see the command line I typed out, you will see that I
> *am* using the -n switch, but the error still occurs.
> Any other ideas?
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > use the -n switch so you get a native dump.
> > Then, you'll have no trouble restoring as long as the schema is the
same.
> > Otherwise, you've got to use format files.
> >
> > (BTW, as a side not, BCP is not a backup utility).
> >
> > James Hokes
> >
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > Hi all,
> > >
> > > I'm using bcp from the command line to backup and
> > > restore the data in a table.
> > >
> > > To backup, I run a command like this:
> > >
> > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > and this works fine. However, when I try to restore
> > > the data that I have backed up by doing this
> > >
> > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > I get the following error:
> > >
> > > SQLState = 37000, NativeError = 170
> > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect
> > > syntax near 'varchar'.
> > >
> > > Does anyone have any idea what the problem might
> > > be?
> > >
> > > TIA,
> > >
> > >
> > > --
> > > Akin
> > >
> > > aknak at aksoto dot idps dot co dot uk
> > >
> > >
> > >
> >
> >
>|||OK James, I figured it out. The problem was that I had
some fields in the table which had spaces between their
names, like '[First Name]' and I wasn't using the quoted
identifier switch (-q). I thought I only needed to use
this when the name of the *table* or *database* whose
data I wanted to import had a space, but this seems to
apply to fields to.
Cheers,
Akin
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Doh!
> Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> Now I'm well and fully stumped.
> James Hokes
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > Hello James,
> >
> > Thanks for your reply.
> >
> > If you see the command line I typed out, you will see that I
> > *am* using the -n switch, but the error still occurs.
> >
> > Any other ideas?
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > use the -n switch so you get a native dump.
> > > Then, you'll have no trouble restoring as long as the schema is the
> same.
> > > Otherwise, you've got to use format files.
> > >
> > > (BTW, as a side not, BCP is not a backup utility).
> > >
> > > James Hokes
> > >
> > >
> > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > Hi all,
> > > >
> > > > I'm using bcp from the command line to backup and
> > > > restore the data in a table.
> > > >
> > > > To backup, I run a command like this:
> > > >
> > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > and this works fine. However, when I try to restore
> > > > the data that I have backed up by doing this
> > > >
> > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > I get the following error:
> > > >
> > > > SQLState = 37000, NativeError = 170
> > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect
> > > > syntax near 'varchar'.
> > > >
> > > > Does anyone have any idea what the problem might
> > > > be?
> > > >
> > > > TIA,
> > > >
> > > >
> > > > --
> > > > Akin
> > > >
> > > > aknak at aksoto dot idps dot co dot uk
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sky Fly,
Hey, way to go! That was one that I had not thought of, but now,thanks to
you, I'll keep it in my bag of tricks.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs14nd$8dlel$1@.ID-18325.news.uni-berlin.de...
> OK James, I figured it out. The problem was that I had
> some fields in the table which had spaces between their
> names, like '[First Name]' and I wasn't using the quoted
> identifier switch (-q). I thought I only needed to use
> this when the name of the *table* or *database* whose
> data I wanted to import had a space, but this seems to
> apply to fields to.
> Cheers,
> Akin
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Doh!
> >
> > Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> >
> > Now I'm well and fully stumped.
> >
> > James Hokes
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > > Hello James,
> > >
> > > Thanks for your reply.
> > >
> > > If you see the command line I typed out, you will see that I
> > > *am* using the -n switch, but the error still occurs.
> > >
> > > Any other ideas?
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > > use the -n switch so you get a native dump.
> > > > Then, you'll have no trouble restoring as long as the schema is the
> > same.
> > > > Otherwise, you've got to use format files.
> > > >
> > > > (BTW, as a side not, BCP is not a backup utility).
> > > >
> > > > James Hokes
> > > >
> > > >
> > > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > > Hi all,
> > > > >
> > > > > I'm using bcp from the command line to backup and
> > > > > restore the data in a table.
> > > > >
> > > > > To backup, I run a command like this:
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > and this works fine. However, when I try to restore
> > > > > the data that I have backed up by doing this
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > I get the following error:
> > > > >
> > > > > SQLState = 37000, NativeError = 170
> > > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > Incorrect
> > > > > syntax near 'varchar'.
> > > > >
> > > > > Does anyone have any idea what the problem might
> > > > > be?
> > > > >
> > > > > TIA,
> > > > >
> > > > >
> > > > > --
> > > > > Akin
> > > > >
> > > > > aknak at aksoto dot idps dot co dot uk
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
I'm using bcp from the command line to backup and
restore the data in a table.
To backup, I run a command like this:
bcp TestDatabase.dbo.tblTest out C:\test.txt -n
-S TestServer -U sa -P pwd
and this works fine. However, when I try to restore
the data that I have backed up by doing this
bcp TestDatabase.dbo.tblTest in C:\test.txt -n
-S TestServer -U sa -P pwd
I get the following error:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near 'varchar'.
Does anyone have any idea what the problem might
be?
TIA,
--
Akin
aknak at aksoto dot idps dot co dot ukuse the -n switch so you get a native dump.
Then, you'll have no trouble restoring as long as the schema is the same.
Otherwise, you've got to use format files.
(BTW, as a side not, BCP is not a backup utility).
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> Hi all,
> I'm using bcp from the command line to backup and
> restore the data in a table.
> To backup, I run a command like this:
> bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> -S TestServer -U sa -P pwd
> and this works fine. However, when I try to restore
> the data that I have backed up by doing this
> bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> -S TestServer -U sa -P pwd
> I get the following error:
> SQLState = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> syntax near 'varchar'.
> Does anyone have any idea what the problem might
> be?
> TIA,
>
> --
> Akin
> aknak at aksoto dot idps dot co dot uk
>
>|||Hello James,
Thanks for your reply.
If you see the command line I typed out, you will see that I
*am* using the -n switch, but the error still occurs.
Any other ideas?
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> use the -n switch so you get a native dump.
> Then, you'll have no trouble restoring as long as the schema is the same.
> Otherwise, you've got to use format files.
> (BTW, as a side not, BCP is not a backup utility).
> James Hokes
>
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > Hi all,
> >
> > I'm using bcp from the command line to backup and
> > restore the data in a table.
> >
> > To backup, I run a command like this:
> >
> > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > and this works fine. However, when I try to restore
> > the data that I have backed up by doing this
> >
> > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > I get the following error:
> >
> > SQLState = 37000, NativeError = 170
> > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> > syntax near 'varchar'.
> >
> > Does anyone have any idea what the problem might
> > be?
> >
> > TIA,
> >
> >
> > --
> > Akin
> >
> > aknak at aksoto dot idps dot co dot uk
> >
> >
> >
>|||Doh!
Thanks for not flaming - chalk one up to 'reading too fast, eh?'
Now I'm well and fully stumped.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> Hello James,
> Thanks for your reply.
> If you see the command line I typed out, you will see that I
> *am* using the -n switch, but the error still occurs.
> Any other ideas?
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > use the -n switch so you get a native dump.
> > Then, you'll have no trouble restoring as long as the schema is the
same.
> > Otherwise, you've got to use format files.
> >
> > (BTW, as a side not, BCP is not a backup utility).
> >
> > James Hokes
> >
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > Hi all,
> > >
> > > I'm using bcp from the command line to backup and
> > > restore the data in a table.
> > >
> > > To backup, I run a command like this:
> > >
> > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > and this works fine. However, when I try to restore
> > > the data that I have backed up by doing this
> > >
> > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > I get the following error:
> > >
> > > SQLState = 37000, NativeError = 170
> > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect
> > > syntax near 'varchar'.
> > >
> > > Does anyone have any idea what the problem might
> > > be?
> > >
> > > TIA,
> > >
> > >
> > > --
> > > Akin
> > >
> > > aknak at aksoto dot idps dot co dot uk
> > >
> > >
> > >
> >
> >
>|||OK James, I figured it out. The problem was that I had
some fields in the table which had spaces between their
names, like '[First Name]' and I wasn't using the quoted
identifier switch (-q). I thought I only needed to use
this when the name of the *table* or *database* whose
data I wanted to import had a space, but this seems to
apply to fields to.
Cheers,
Akin
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Doh!
> Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> Now I'm well and fully stumped.
> James Hokes
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > Hello James,
> >
> > Thanks for your reply.
> >
> > If you see the command line I typed out, you will see that I
> > *am* using the -n switch, but the error still occurs.
> >
> > Any other ideas?
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > use the -n switch so you get a native dump.
> > > Then, you'll have no trouble restoring as long as the schema is the
> same.
> > > Otherwise, you've got to use format files.
> > >
> > > (BTW, as a side not, BCP is not a backup utility).
> > >
> > > James Hokes
> > >
> > >
> > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > Hi all,
> > > >
> > > > I'm using bcp from the command line to backup and
> > > > restore the data in a table.
> > > >
> > > > To backup, I run a command like this:
> > > >
> > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > and this works fine. However, when I try to restore
> > > > the data that I have backed up by doing this
> > > >
> > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > I get the following error:
> > > >
> > > > SQLState = 37000, NativeError = 170
> > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect
> > > > syntax near 'varchar'.
> > > >
> > > > Does anyone have any idea what the problem might
> > > > be?
> > > >
> > > > TIA,
> > > >
> > > >
> > > > --
> > > > Akin
> > > >
> > > > aknak at aksoto dot idps dot co dot uk
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sky Fly,
Hey, way to go! That was one that I had not thought of, but now,thanks to
you, I'll keep it in my bag of tricks.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs14nd$8dlel$1@.ID-18325.news.uni-berlin.de...
> OK James, I figured it out. The problem was that I had
> some fields in the table which had spaces between their
> names, like '[First Name]' and I wasn't using the quoted
> identifier switch (-q). I thought I only needed to use
> this when the name of the *table* or *database* whose
> data I wanted to import had a space, but this seems to
> apply to fields to.
> Cheers,
> Akin
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Doh!
> >
> > Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> >
> > Now I'm well and fully stumped.
> >
> > James Hokes
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > > Hello James,
> > >
> > > Thanks for your reply.
> > >
> > > If you see the command line I typed out, you will see that I
> > > *am* using the -n switch, but the error still occurs.
> > >
> > > Any other ideas?
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > > use the -n switch so you get a native dump.
> > > > Then, you'll have no trouble restoring as long as the schema is the
> > same.
> > > > Otherwise, you've got to use format files.
> > > >
> > > > (BTW, as a side not, BCP is not a backup utility).
> > > >
> > > > James Hokes
> > > >
> > > >
> > > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > > Hi all,
> > > > >
> > > > > I'm using bcp from the command line to backup and
> > > > > restore the data in a table.
> > > > >
> > > > > To backup, I run a command like this:
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > and this works fine. However, when I try to restore
> > > > > the data that I have backed up by doing this
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > I get the following error:
> > > > >
> > > > > SQLState = 37000, NativeError = 170
> > > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > Incorrect
> > > > > syntax near 'varchar'.
> > > > >
> > > > > Does anyone have any idea what the problem might
> > > > > be?
> > > > >
> > > > > TIA,
> > > > >
> > > > >
> > > > > --
> > > > > Akin
> > > > >
> > > > > aknak at aksoto dot idps dot co dot uk
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
problem with BCP
Dear all,
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Dollerhi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values '
-U -P
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get th
e
> command working from a command prompt and then fit that into the xp_cmdshe
ll
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid}
-P
> {password}'
> John
>
> "doller" wrote:
>|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:[vbcol=seagreen]
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online
.
> John
> "John Bell" wrote:
>|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
--
current location: alicante (es)
"doller" wrote:
> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
>|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:[vbcol=seagreen]
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:
>
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Dollerhi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values '
-U -P
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get th
e
> command working from a command prompt and then fit that into the xp_cmdshe
ll
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid}
-P
> {password}'
> John
>
> "doller" wrote:
>|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:[vbcol=seagreen]
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online
.
> John
> "John Bell" wrote:
>|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
--
current location: alicante (es)
"doller" wrote:
> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
>|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:[vbcol=seagreen]
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:
>
PROBLEM WITH bcp
Hello!!
I have a problem with bcp in Query Analyzer.
The sentence is:
bcp SELECT id_categoria from categorias QUERYOUT 'c:\salida.txt' -c
-Ulogin -Ppassword
With this sentence, the error is:
Line 1: Incorrect syntax near 'c:\SADD.txt'.
PLEASE, I NEED HELP.
THANKSthis command works fine..
bcp "SELECT * from pubs..authors" QUERYOUT "C:\salida.txt" -c -S<server name> -Usa -P<password>
database name, server name and quotes around the query are missing from ur BCP command also the file path should be in full.|||hmmm, doesn't work for me, has a problem around QUERYOUT
strange.
i'll keep looking|||This is true!!, the error now is:
Line 1: Incorrect syntax near 'queryout'.
bcp "SELECT id_categoria from categorias" queryout "C:\salida.txt" -c -S<server_name> -U<login_id> -P<password>|||It works via the OS command line.|||master..xp_cmdshell 'bcp "SELECT * from pubs..authors" QUERYOUT "C:\salida.txt" -c -S<server name> -Usa -P<password>'sql
I have a problem with bcp in Query Analyzer.
The sentence is:
bcp SELECT id_categoria from categorias QUERYOUT 'c:\salida.txt' -c
-Ulogin -Ppassword
With this sentence, the error is:
Line 1: Incorrect syntax near 'c:\SADD.txt'.
PLEASE, I NEED HELP.
THANKSthis command works fine..
bcp "SELECT * from pubs..authors" QUERYOUT "C:\salida.txt" -c -S<server name> -Usa -P<password>
database name, server name and quotes around the query are missing from ur BCP command also the file path should be in full.|||hmmm, doesn't work for me, has a problem around QUERYOUT
strange.
i'll keep looking|||This is true!!, the error now is:
Line 1: Incorrect syntax near 'queryout'.
bcp "SELECT id_categoria from categorias" queryout "C:\salida.txt" -c -S<server_name> -U<login_id> -P<password>|||It works via the OS command line.|||master..xp_cmdshell 'bcp "SELECT * from pubs..authors" QUERYOUT "C:\salida.txt" -c -S<server name> -Usa -P<password>'sql
problem with BCP
Dear all,
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Doller
hi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values ?
-U -P
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>
|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>
|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> command working from a command prompt and then fit that into the xp_cmdshell
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> {password}'
> John
>
> "doller" wrote:
|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:[vbcol=seagreen]
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online.
> John
> "John Bell" wrote:
|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
current location: alicante (es)
"doller" wrote:
> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
>
|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:[vbcol=seagreen]
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Doller
hi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values ?
-U -P
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>
|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>
|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> command working from a command prompt and then fit that into the xp_cmdshell
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> {password}'
> John
>
> "doller" wrote:
|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:[vbcol=seagreen]
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online.
> John
> "John Bell" wrote:
|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
current location: alicante (es)
"doller" wrote:
> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
>
|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:[vbcol=seagreen]
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:
Monday, February 20, 2012
Problem using bcp with SQL MSDE
I successfully tested a bcp command similar to that below on a Small Business Manager database using SQL Enterprise Edition:
bcp "SELECT EMPLOYID FROM TWO..UPR00100" queryout testfile.txt -U login -P -C
I then tried using the same basic command for another Small Business Manager installation using SQL Desktop Edition. But I get the following:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not
exist or access denied.
The only other difference I'm aware of aside from the SQL edition is that an
'sa' password exists for the second environment. But I put the password in
the bcp command.
Does anyone have an idea why the bcp command works in the one
environment but not the other?
Thanks,
Tom
hi Tom,
"Tom Glasser" <Tom Glasser@.discussions.microsoft.com> ha scritto nel
messaggio news:3076FF6F-2708-4908-9149-6B0E148E6087@.microsoft.com...
> I successfully tested a bcp command similar to that below on a Small
Business Manager database using SQL Enterprise Edition:
> bcp "SELECT EMPLOYID FROM TWO..UPR00100" queryout testfile.txt -U
login -P -C
> I then tried using the same basic command for another Small Business
Manager installation using SQL Desktop Edition. But I get the following:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not
> exist or access denied.
> The only other difference I'm aware of aside from the SQL edition is that
an
> 'sa' password exists for the second environment. But I put the password
in
> the bcp command.
> Does anyone have an idea why the bcp command works in the one
> environment but not the other?
>
please have a look at
http://support.microsoft.com/default...B;EN-US;328306
it's possible that your 2nd environment does not accept SQL Server
authenticated connection... if this is the case, please have a look at
http://support.microsoft.com/default...b;en-us;285097 for further
info on how to hack the Windows Registry in order to allow SQL Server
quthenticated connections..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
bcp "SELECT EMPLOYID FROM TWO..UPR00100" queryout testfile.txt -U login -P -C
I then tried using the same basic command for another Small Business Manager installation using SQL Desktop Edition. But I get the following:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not
exist or access denied.
The only other difference I'm aware of aside from the SQL edition is that an
'sa' password exists for the second environment. But I put the password in
the bcp command.
Does anyone have an idea why the bcp command works in the one
environment but not the other?
Thanks,
Tom
hi Tom,
"Tom Glasser" <Tom Glasser@.discussions.microsoft.com> ha scritto nel
messaggio news:3076FF6F-2708-4908-9149-6B0E148E6087@.microsoft.com...
> I successfully tested a bcp command similar to that below on a Small
Business Manager database using SQL Enterprise Edition:
> bcp "SELECT EMPLOYID FROM TWO..UPR00100" queryout testfile.txt -U
login -P -C
> I then tried using the same basic command for another Small Business
Manager installation using SQL Desktop Edition. But I get the following:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not
> exist or access denied.
> The only other difference I'm aware of aside from the SQL edition is that
an
> 'sa' password exists for the second environment. But I put the password
in
> the bcp command.
> Does anyone have an idea why the bcp command works in the one
> environment but not the other?
>
please have a look at
http://support.microsoft.com/default...B;EN-US;328306
it's possible that your 2nd environment does not accept SQL Server
authenticated connection... if this is the case, please have a look at
http://support.microsoft.com/default...b;en-us;285097 for further
info on how to hack the Windows Registry in order to allow SQL Server
quthenticated connections..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Problem using bcp with SQL MSDE
I successfully tested a bcp command similar to that below on a Small Business Manager database using SQL Enterprise Edition:
bcp "SELECT EMPLOYID FROM TWO..UPR00100" queryout testfile.txt -U login -P -C
I then tried using the same basic command for another Small Business Manager installation using SQL Desktop Edition. But I get the following:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not
exist or access denied.
The only other difference I'm aware of aside from the SQL edition is that an
'sa' password exists for the second environment. But I put the password in
the bcp command.
Does anyone have an idea why the bcp command works in the one
environment but not the other?
Thanks,
Tom
hi Tom,
"Tom Glasser" <Tom Glasser@.discussions.microsoft.com> ha scritto nel
messaggio news:3076FF6F-2708-4908-9149-6B0E148E6087@.microsoft.com...
> I successfully tested a bcp command similar to that below on a Small
Business Manager database using SQL Enterprise Edition:
> bcp "SELECT EMPLOYID FROM TWO..UPR00100" queryout testfile.txt -U
login -P -C
> I then tried using the same basic command for another Small Business
Manager installation using SQL Desktop Edition. But I get the following:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not
> exist or access denied.
> The only other difference I'm aware of aside from the SQL edition is that
an
> 'sa' password exists for the second environment. But I put the password
in
> the bcp command.
> Does anyone have an idea why the bcp command works in the one
> environment but not the other?
>
please have a look at
http://support.microsoft.com/default...B;EN-US;328306
it's possible that your 2nd environment does not accept SQL Server
authenticated connection... if this is the case, please have a look at
http://support.microsoft.com/default...b;en-us;285097 for further
info on how to hack the Windows Registry in order to allow SQL Server
quthenticated connections..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
bcp "SELECT EMPLOYID FROM TWO..UPR00100" queryout testfile.txt -U login -P -C
I then tried using the same basic command for another Small Business Manager installation using SQL Desktop Edition. But I get the following:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not
exist or access denied.
The only other difference I'm aware of aside from the SQL edition is that an
'sa' password exists for the second environment. But I put the password in
the bcp command.
Does anyone have an idea why the bcp command works in the one
environment but not the other?
Thanks,
Tom
hi Tom,
"Tom Glasser" <Tom Glasser@.discussions.microsoft.com> ha scritto nel
messaggio news:3076FF6F-2708-4908-9149-6B0E148E6087@.microsoft.com...
> I successfully tested a bcp command similar to that below on a Small
Business Manager database using SQL Enterprise Edition:
> bcp "SELECT EMPLOYID FROM TWO..UPR00100" queryout testfile.txt -U
login -P -C
> I then tried using the same basic command for another Small Business
Manager installation using SQL Desktop Edition. But I get the following:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not
> exist or access denied.
> The only other difference I'm aware of aside from the SQL edition is that
an
> 'sa' password exists for the second environment. But I put the password
in
> the bcp command.
> Does anyone have an idea why the bcp command works in the one
> environment but not the other?
>
please have a look at
http://support.microsoft.com/default...B;EN-US;328306
it's possible that your 2nd environment does not accept SQL Server
authenticated connection... if this is the case, please have a look at
http://support.microsoft.com/default...b;en-us;285097 for further
info on how to hack the Windows Registry in order to allow SQL Server
quthenticated connections..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Problem using BCP
Hello,
I have the following scenario: I need to migrate several SQL Server tables
to mysql 5. The tables have aprox. 2'000.000 so i need to use the SQL Server
BCP program because is the fastest.
The problem is with the Null handling. With BCP the Null is a '' empty
string. In my case i'm separating the fields by commas so ",," this would
represent a NULL value.
But with the "Load data infile" in mysql the NULL value is represented by
this "\N"...
Has anyone resolved this incompatibility? Is there a way to configure BCP to
write the null values as \N ?
How can I handle this NULL incompatibility without editing the resulting
file, because it is several MB in size.
Thank you for your help.
Eduardo Sicouretsince you can use a table, view, or query as your bcp source, you may
be able to specify a SQL statement for your bulk export where you use
isNull(columnName, '\N') on all the columns with potential null values.
Along the same lines, you could also create a view that does the NULL
replacement and use the view for your bcp source
Try the bcp utility entry in BOL|||Thank you for answering...
This works fine for string datatypes. but what could I do with numeric and
date datatypes?
Eduardo
"KenJ" <kenjohnson@.hotmail.com> escribi en el mensaje
news:1138326809.037866.75220@.g47g2000cwa.googlegroups.com...
> since you can use a table, view, or query as your bcp source, you may
> be able to specify a SQL statement for your bulk export where you use
> isNull(columnName, '\N') on all the columns with potential null values.
> Along the same lines, you could also create a view that does the NULL
> replacement and use the view for your bcp source
> Try the bcp utility entry in BOL
>|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')
I have the following scenario: I need to migrate several SQL Server tables
to mysql 5. The tables have aprox. 2'000.000 so i need to use the SQL Server
BCP program because is the fastest.
The problem is with the Null handling. With BCP the Null is a '' empty
string. In my case i'm separating the fields by commas so ",," this would
represent a NULL value.
But with the "Load data infile" in mysql the NULL value is represented by
this "\N"...
Has anyone resolved this incompatibility? Is there a way to configure BCP to
write the null values as \N ?
How can I handle this NULL incompatibility without editing the resulting
file, because it is several MB in size.
Thank you for your help.
Eduardo Sicouretsince you can use a table, view, or query as your bcp source, you may
be able to specify a SQL statement for your bulk export where you use
isNull(columnName, '\N') on all the columns with potential null values.
Along the same lines, you could also create a view that does the NULL
replacement and use the view for your bcp source
Try the bcp utility entry in BOL|||Thank you for answering...
This works fine for string datatypes. but what could I do with numeric and
date datatypes?
Eduardo
"KenJ" <kenjohnson@.hotmail.com> escribi en el mensaje
news:1138326809.037866.75220@.g47g2000cwa.googlegroups.com...
> since you can use a table, view, or query as your bcp source, you may
> be able to specify a SQL statement for your bulk export where you use
> isNull(columnName, '\N') on all the columns with potential null values.
> Along the same lines, you could also create a view that does the NULL
> replacement and use the view for your bcp source
> Try the bcp utility entry in BOL
>|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')
Problem using BCP
Hello,
I have the following scenario: I need to migrate several SQL Server tables
to MySQL 5. The tables have aprox. 2'000.000 so i need to use the SQL Server
BCP program because is the fastest.
The problem is with the Null handling. With BCP the Null is a '' empty
string. In my case i'm separating the fields by commas so ",," this would
represent a NULL value.
But with the "Load data infile" in MySQL the NULL value is represented by
this "\N"...
Has anyone resolved this incompatibility? Is there a way to configure BCP to
write the null values as \N ?
How can I handle this NULL incompatibility without editing the resulting
file, because it is several MB in size.
Thank you for your help.
Eduardo Sicouret
since you can use a table, view, or query as your bcp source, you may
be able to specify a SQL statement for your bulk export where you use
isNull(columnName, '\N') on all the columns with potential null values.
Along the same lines, you could also create a view that does the NULL
replacement and use the view for your bcp source
Try the bcp utility entry in BOL
|||Thank you for answering...
This works fine for string datatypes. but what could I do with numeric and
date datatypes?
Eduardo
"KenJ" <kenjohnson@.hotmail.com> escribi en el mensaje
news:1138326809.037866.75220@.g47g2000cwa.googlegro ups.com...
> since you can use a table, view, or query as your bcp source, you may
> be able to specify a SQL statement for your bulk export where you use
> isNull(columnName, '\N') on all the columns with potential null values.
> Along the same lines, you could also create a view that does the NULL
> replacement and use the view for your bcp source
> Try the bcp utility entry in BOL
>
|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')
|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')
I have the following scenario: I need to migrate several SQL Server tables
to MySQL 5. The tables have aprox. 2'000.000 so i need to use the SQL Server
BCP program because is the fastest.
The problem is with the Null handling. With BCP the Null is a '' empty
string. In my case i'm separating the fields by commas so ",," this would
represent a NULL value.
But with the "Load data infile" in MySQL the NULL value is represented by
this "\N"...
Has anyone resolved this incompatibility? Is there a way to configure BCP to
write the null values as \N ?
How can I handle this NULL incompatibility without editing the resulting
file, because it is several MB in size.
Thank you for your help.
Eduardo Sicouret
since you can use a table, view, or query as your bcp source, you may
be able to specify a SQL statement for your bulk export where you use
isNull(columnName, '\N') on all the columns with potential null values.
Along the same lines, you could also create a view that does the NULL
replacement and use the view for your bcp source
Try the bcp utility entry in BOL
|||Thank you for answering...
This works fine for string datatypes. but what could I do with numeric and
date datatypes?
Eduardo
"KenJ" <kenjohnson@.hotmail.com> escribi en el mensaje
news:1138326809.037866.75220@.g47g2000cwa.googlegro ups.com...
> since you can use a table, view, or query as your bcp source, you may
> be able to specify a SQL statement for your bulk export where you use
> isNull(columnName, '\N') on all the columns with potential null values.
> Along the same lines, you could also create a view that does the NULL
> replacement and use the view for your bcp source
> Try the bcp utility entry in BOL
>
|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')
|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')
Problem using BCP
Hello,
I have the following scenario: I need to migrate several SQL Server tables
to MySQL 5. The tables have aprox. 2'000.000 so i need to use the SQL Server
BCP program because is the fastest.
The problem is with the Null handling. With BCP the Null is a '' empty
string. In my case i'm separating the fields by commas so ",," this would
represent a NULL value.
But with the "Load data infile" in MySQL the NULL value is represented by
this "\N"...
Has anyone resolved this incompatibility? Is there a way to configure BCP to
write the null values as \N ?
How can I handle this NULL incompatibility without editing the resulting
file, because it is several MB in size.
Thank you for your help.
Eduardo Sicouretsince you can use a table, view, or query as your bcp source, you may
be able to specify a SQL statement for your bulk export where you use
isNull(columnName, '\N') on all the columns with potential null values.
Along the same lines, you could also create a view that does the NULL
replacement and use the view for your bcp source
Try the bcp utility entry in BOL|||Thank you for answering...
This works fine for string datatypes. but what could I do with numeric and
date datatypes?
Eduardo
"KenJ" <kenjohnson@.hotmail.com> escribió en el mensaje
news:1138326809.037866.75220@.g47g2000cwa.googlegroups.com...
> since you can use a table, view, or query as your bcp source, you may
> be able to specify a SQL statement for your bulk export where you use
> isNull(columnName, '\N') on all the columns with potential null values.
> Along the same lines, you could also create a view that does the NULL
> replacement and use the view for your bcp source
> Try the bcp utility entry in BOL
>|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')
I have the following scenario: I need to migrate several SQL Server tables
to MySQL 5. The tables have aprox. 2'000.000 so i need to use the SQL Server
BCP program because is the fastest.
The problem is with the Null handling. With BCP the Null is a '' empty
string. In my case i'm separating the fields by commas so ",," this would
represent a NULL value.
But with the "Load data infile" in MySQL the NULL value is represented by
this "\N"...
Has anyone resolved this incompatibility? Is there a way to configure BCP to
write the null values as \N ?
How can I handle this NULL incompatibility without editing the resulting
file, because it is several MB in size.
Thank you for your help.
Eduardo Sicouretsince you can use a table, view, or query as your bcp source, you may
be able to specify a SQL statement for your bulk export where you use
isNull(columnName, '\N') on all the columns with potential null values.
Along the same lines, you could also create a view that does the NULL
replacement and use the view for your bcp source
Try the bcp utility entry in BOL|||Thank you for answering...
This works fine for string datatypes. but what could I do with numeric and
date datatypes?
Eduardo
"KenJ" <kenjohnson@.hotmail.com> escribió en el mensaje
news:1138326809.037866.75220@.g47g2000cwa.googlegroups.com...
> since you can use a table, view, or query as your bcp source, you may
> be able to specify a SQL statement for your bulk export where you use
> isNull(columnName, '\N') on all the columns with potential null values.
> Along the same lines, you could also create a view that does the NULL
> replacement and use the view for your bcp source
> Try the bcp utility entry in BOL
>|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')|||Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...
SELECT Isnull(Cast(@.int AS varchar(500)),'\N')
Subscribe to:
Posts (Atom)