bcp.exe installation

(This was originally in the sqlserver.programming group but I moved it to a
more appropriate place)
I'm using the bcp utility to create a text file from a view. The text file
is used by a 3rd party program for other processing.
bcp installs on the server machine with sql 2k (or in my case MSDE) just
fine but of course it's not automatically on any of the client machines. But
that's where I need it because those are the users that create and make use
of this text file.
My qustion is how to install bcp on the client machines without installing
MSDE. I thought it would work to just copy the file to an appropriate
location but apparently it needs to be installed. How do I go about that?
Thanks,
Keith
Never mind. I found a very nice inexpensive 3rd party control for use with
Delphi that will export datasources to all sorts of output formats. This is
a better solution since now I have no need at all for bcp.
Keith

BCP,CTE,NTILE

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTEs are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

BCP,CTE,NTILE

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTEs are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

BCP,CTE and NTILE problem?

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTE is only visible within a batch in a connection. When you execute BCP using xp_cmdshell, you are actually invoking an external process (bcp in this case) which makes a new connection to SQLServer. So it will not have any context about the declared CTE. Also, the way you are using the CTE doesn't make sense also. You declare a CTE but then you update the base table directly. Even though it is legal I am not sure what you are doing. CTE is also not a peristent object like view or table-valued function.

You could do one of the following:

1. Query from the base table directly

2. Specify CTE definition in the queryout parameter itself

3. Dump the results of the table into a global temporary table and export from there (this will prevent the BCP from being executed simultaneously by different connections though)

bcp -x and "Invalid Field index."

Hey, I am using bcp to try to create my xml file formate off from a table
that has about 477 fields. I am getting the following error:
F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
FNIApplication_X
MLFormatFile.xml
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
When I remove the -x to create the non xml file format no issues arise.
Idea's? I have not been able to find anything on web about this.
bcp Yea, you know me. (bcp Yea, you know me.@.discussions.microsoft.com)
writes:
> Hey, I am using bcp to try to create my xml file formate off from a table
> that has about 477 fields. I am getting the following error:
> F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
> FNIApplication_X
> MLFormatFile.xml
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
>
> When I remove the -x to create the non xml file format no issues arise.
> Idea's? I have not been able to find anything on web about this.
Sounds like a bug to me. I suggest that you file one on
http://connect.microsoft.com
Personally, I have not been able to whip up any enthusiams over the XML
format file. I have not been able to find that it buys me anything that
the old format does not. (Which, true, is fairly obscure. But I know
it by now.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

bcp with format file

I am trying to copy the data in excel file into a table using the bcp
and this is the code that I have. However the bcp utility does not
seem to create a format file, which I thought it should do. I am
probably going about this all wrong so any help would be useful.

exec master..xp_cmdshell '(FOR %i IN ("E:\WUTemp\*") DO (bcp
#ProspectImportTest in "%i" -fE:\WUTemp\Prospect.fmt)'
bulk insert #ProspectImportTest from 'E:\WUTemp\*."' with (formatfile =
'E:\WUTemp\Prospect.fmt')

Thanks.

KRKR (kraman@.bastyr.edu) writes:
> I am trying to copy the data in excel file into a table using the bcp
> and this is the code that I have. However the bcp utility does not
> seem to create a format file, which I thought it should do. I am
> probably going about this all wrong so any help would be useful.
> exec master..xp_cmdshell '(FOR %i IN ("E:\WUTemp\*") DO (bcp
> #ProspectImportTest in "%i" -fE:\WUTemp\Prospect.fmt)'
> bulk insert #ProspectImportTest from 'E:\WUTemp\*."' with (formatfile =
> 'E:\WUTemp\Prospect.fmt')

To have BCP to create a format file, you should specify "format" for
the direction parameter, not "in".

Furthermore, BCP cannot access the temp table #ProspectImportTest, as it
as local to your connection. You could try a global temp table,
##ProspectImportTest.

And BULK INSERT does not, as far as I know, accept file specifications with
wildcards in them.

As for the format file , I would not expect that format file be very useful.
You said Excel file, but you did not specify what format of Excel. BULK
INSERT cannot read an xls file, as that is a binary file. It can read
a CSV file or a tab-delimited file, but you don't need a format file for
that, just specify FIELDTERMINATOR with the BULK INSERT command. (Unless you
have a CSV file with strings quoted. Then you need a format file.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Guess I have a lot to learn yet - and you have given me good points. I
am new to this kind of thing.

Anyway, by the time I saw your reply, I had tried to go in another
direction where I was trying to do the same thing but I am working with
a csv file this time.

Thanks

bcp utility

I am trying to create a flat file from SQL server and here is
what my query looks like:
bcp "select intakeid from tblintake" queryout d:\out.txt -c
I got this error: "Line 1: Incorrect syntax near 'queryout'."
when I run it in query analyzer. Can anyone please tell me what
I did wrong? I
Thanks.
MLPMLP,
> I am trying to create a flat file from SQL server and here is
> what my query looks like:
> bcp "select intakeid from tblintake" queryout d:\out.txt -c
> I got this error: "Line 1: Incorrect syntax near 'queryout'."
> when I run it in query analyzer. Can anyone please tell me what
> I did wrong? I
Bcp is a command line utility. You need to run it from a command
prompt (aka DOS window). It cannot be run in Query Analyzer.
Linda|||If you want to run BCP from QA, you need to use
xp_cmdshell.
>--Original Message--
>I am trying to create a flat file from SQL server and
here is
>what my query looks like:
>bcp "select intakeid from tblintake" queryout
d:\out.txt -c
>I got this error: "Line 1: Incorrect syntax
near 'queryout'."
>when I run it in query analyzer. Can anyone please tell
me what
>I did wrong? I
>Thanks.
>MLP
>.
>

BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP Usage with SQL Server 2005 Clustered Server

How can I use BCP to create an XML format file on a SQL Server 2005
cluster that has multiple instances of SQL Server running? Is there
some sample syntax, etc.?
Thx.
-ak
This syntax "appears" to work...
bcp [sqlcls1dev\inst2005dev].[BusinessContacts].[Branch] format -x -T
-fc:\temp\myfile.xml
However, I get a connection timeout error that also states that the SQL
Server 2005 by default doesn't allow remote connections. But, I see
that this server does allow remote connections in the options. I am
logged into the box using Win authent and my Win acct has sysadmin
privileges on the sql server instance. Any information would be
appreciated.
-ak
Ayron wrote:
> How can I use BCP to create an XML format file on a SQL Server 2005
> cluster that has multiple instances of SQL Server running? Is there
> some sample syntax, etc.?
> Thx.
> -ak

BCP Usage with SQL Server 2005 Clustered Server

How can I use BCP to create an XML format file on a SQL Server 2005
cluster that has multiple instances of SQL Server running? Is there
some sample syntax, etc.?
Thx.
-akThis syntax "appears" to work...
bcp [sqlcls1dev\inst2005dev].[BusinessContacts].[Branch] format -x -T
-fc:\temp\myfile.xml
However, I get a connection timeout error that also states that the SQL
Server 2005 by default doesn't allow remote connections. But, I see
that this server does allow remote connections in the options. I am
logged into the box using Win authent and my Win acct has symin
privileges on the sql server instance. Any information would be
appreciated.
-ak
Ayron wrote:
> How can I use BCP to create an XML format file on a SQL Server 2005
> cluster that has multiple instances of SQL Server running? Is there
> some sample syntax, etc.?
> Thx.
> -ak

BCP Temporary Tables

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

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advancebcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||It worked just fine for me.
1. Make sure that the bcp command is in one line.
2. Print the contents of the @.sql variable and post it here.
3. Also provide error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

BCP Out each result of a query in a saperate file

hi,

i am trying to BCP out the result of a query to a seperate file. i am trying to create a seperate file for each record. could somebody please help me out?

Pankaj

Do you really mean a seperate file for each row?
Or a separate file for each table?

If it's for each row, then how many rows are there?
..and why?

/Kenneth

|||

yes,

seperate file for each row. just trying to send file as per required by customer. by the way i am trying with CURSORS.

Pankaj

|||

Sorry for the late answer, it's busy times ;o)

It seems a very odd request from your customer...
In a query, how many rows are you expecting to do this on?

/Kenneth

bcp into view with derived column

Hi,

I have a view that looks something like this -

CREATE VIEW myview AS SELECT
myudf(col1) as col1, col2, col3
FROM mytable

The view has an 'INSTEAD OF' trigger on it to do the correct thing on
insert. When I run the bcp, it runs successfully, but the value
inserted into col1 is always NULL. I modified my trigger to get a
better idea of what was happening, and when I look at the values of
INSERTED.col1, they are all null. It appears that bcp is setting the
column value to null, presumable because the view definintion for this
column is a derived column.

Does any one know a way around this?

Thanks!On 10 Jan 2006 11:28:32 -0800, bsandell@.gmail.com wrote:

>Hi,
>I have a view that looks something like this -
>CREATE VIEW myview AS SELECT
> myudf(col1) as col1, col2, col3
>FROM mytable
>The view has an 'INSTEAD OF' trigger on it to do the correct thing on
>insert. When I run the bcp, it runs successfully, but the value
>inserted into col1 is always NULL. I modified my trigger to get a
>better idea of what was happening, and when I look at the values of
>INSERTED.col1, they are all null. It appears that bcp is setting the
>column value to null, presumable because the view definintion for this
>column is a derived column.
>Does any one know a way around this?
>Thanks!

Hi bsandell,

By default, triggers are not fired for bulk copy statements.

To override this default, add the option
-h "FIRE_TRIGGERS"

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
being executed. I have added the following line to my trigger code -

SELECT inserted.* into pubs..dummy from inserted

My input file looks like this -
aaa,aaa,aaa
bbb,bbb,bbb
ccc,ccc,ccc

but when the code above runs in my trigger, the output is -
NULL,aaa,aaa
NULL,bbb,bbb
NULL,ccc,ccc

so it looks like bcp has somehow determined that the first column in my
view does not have a 1-to-1 mapping with a column on the database
(because it's based on a udf) and set it to null? I'd like to find a
way to work around this if possible, without changing the view
definition or the underlying table.

Any ideas?

Thanks,
Bruce|||(bsandell@.gmail.com) writes:
> Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
> being executed. I have added the following line to my trigger code -
> SELECT inserted.* into pubs..dummy from inserted
> My input file looks like this -
> aaa,aaa,aaa
> bbb,bbb,bbb
> ccc,ccc,ccc
> but when the code above runs in my trigger, the output is -
> NULL,aaa,aaa
> NULL,bbb,bbb
> NULL,ccc,ccc
> so it looks like bcp has somehow determined that the first column in my
> view does not have a 1-to-1 mapping with a column on the database
> (because it's based on a udf) and set it to null? I'd like to find a
> way to work around this if possible, without changing the view
> definition or the underlying table.

Smells bug to me. To wit, it works with BULK INSERT, which uses OLE DB
in difference of BCP which uses ODBC. The behaviour is the same in
SQL 2000 and SQL 2005, so I've field a bug for it,
http://lab.msdn.microsoft.com/Produ...ackId=FDBK43711
You can vote for it, if you like.

The repro below is also in the bug report:

USE tempdb
go
CREATE TABLE sandell (col1 int NULL,
col2 int NULL,
col3 int NULL)
go
CREATE FUNCTION myudf(@.c int) RETURNS int AS
BEGIN
RETURN (SELECT 80 - @.c)
END
go
CREATE VIEW sandell_vy AS
SELECT col1 = dbo.myudf(col1), col2, col3
FROM sandell
go
CREATE TRIGGER sandell_tri ON sandell_vy INSTEAD OF INSERT AS
INSERT sandell(col1, col2, col3)
SELECT 80 - col1, col2 + 20, col3 + 20 FROM inserted
go
INSERT sandell_vy (col1, col2, col3)
SELECT 12, 98, 23
go
SELECT * FROM sandell_vy
go
EXEC master..xp_cmdshell 'ECHO 9, 2, 98 > C:\TEMP\bulk.csv', 'no_output'
EXEC master..xp_cmdshell 'ECHO 76, 23, 87 >> C:\TEMP\bulk.csv', 'no_output'
go
BULK INSERT sandell_vy FROM 'C:\temp\bulk.csv'
WITH (FIELDTERMINATOR = ',',
DATAFILETYPE = 'char',
FIRE_TRIGGERS)
go
DECLARE @.bcp nvarchar(4000)
SELECT @.bcp = 'bcp tempdb..sandell_vy in C:\temp\bulk.csv -T -c -t, -h "FIRE_TRIGGERS" -S ' + @.@.servername
EXEC master..xp_cmdshell @.bcp
go
SELECT * FROM sandell_vy
go
DROP VIEW sandell_vy
DROP FUNCTION myudf
DROP TABLE sandell
EXEC master..xp_cmdshell 'DEL C:\temp\bulk.csv', 'no_output'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

As always, I really appreciate your help. Any guesses on the liklihood
that this would get fixed? I didn't think there was much going on with
bcp support these days. Thanks again for you time and effort.

Bruce|||(bsandell@.gmail.com) writes:
> As always, I really appreciate your help. Any guesses on the liklihood
> that this would get fixed? I didn't think there was much going on with
> bcp support these days. Thanks again for you time and effort.

I would guess that it is more likely that it will be fixed in SQL 2005 than
in SQL 2000. Whether the fix will come in SP1 for SQL 2005, I don't want to
speculate in.

If this is critical for you, and you need a hotfix, you will have to open
a case with Microsoft, and try to convince them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP IN: create table on-the-fly

Hello!
I have a BCP file (.BCP) and I want to do a BCP IN but I don't have a
table associated with it. (Lets say table got dropped). I am curious if
there is a way to create a table on-the-fly while doing BCP IN? Any neat
solution?
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***Whats wrong with CREATE TABLE xxx?
Andrew J. Kelly SQL MVP
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:eTq4h7FGGHA.3080@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I have a BCP file (.BCP) and I want to do a BCP IN but I don't have a
> table associated with it. (Lets say table got dropped). I am curious if
> there is a way to create a table on-the-fly while doing BCP IN? Any neat
> solution?
> Thanks!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Lets say ..I dont know the table structure. All I have found is a .BCP
file which is a binary file and can not be opened in text editor.
*** Sent via Developersdex http://www.examnotes.net ***|||Test Test (farooqhs_2000@.yahoo.com) writes:
> Lets say ..I dont know the table structure. All I have found is a .BCP
> file which is a binary file and can not be opened in text editor.
You now have a challenge. You see, there is not one possible table for
this file - but many. Some of them, makes more sense than others.
The file itself contains records only - there is no metadata. So all
you can to is to analyse combinations of bytes. Hm, here is a long
sequence of bytes with the ASCII codes of letter. Probably a character
string. And here is a sequence where every second byte is 0. Might
be Unicode.
In short, this will keep you busy a couple of days!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to what Erland suggested, I'd start looking for the format file
-
if there ever was one, or some documentation to what this BCP file was used
for.
ML
http://milambda.blogspot.com/

BCP fmt and Create Table

Hello,
I am being sent a fmt file and a data file without headers which I
would like to import into my sql 2000 server. However the data files
have couple of 100's of columns, how could I avoid manually creating a
table. Can I create a table using a fmt file thru bcp/bulk insert?

Thanks
sd<danths@.gmail.com> wrote in message
news:1117041460.483784.193120@.g47g2000cwa.googlegr oups.com...
> Hello,
> I am being sent a fmt file and a data file without headers which I
> would like to import into my sql 2000 server. However the data files
> have couple of 100's of columns, how could I avoid manually creating a
> table. Can I create a table using a fmt file thru bcp/bulk insert?
> Thanks
> sd

I don't believe there's an automatic way to create a table using only a
format file, but why not just ask the person sending the data to include a
CREATE TABLE script?

If that's not possible (which would be strange), one solution would be to
write a script to parse format files and produce the corresponding CREATE
TABLE statement - if you'll need to do this often, and if the data will
always be in different formats, it could be worth it.

Alternatively, for a one-off task, it might be easier to use DTS via the
Import and Export Data Wizard. That will give you the option to create the
destination table, although depending on what's in the format file, you
might have to spend a lot of time rearranging column order and so on.

But getting the table script is probably the best option, otherwise there's
no way to know what the exact data types are, what the key columns are,
which columns are NULL/NOT NULL, if there are any CHECK constraints etc.

Simon

BCP Error

Hi I need to create a trigger on a table which bcps the table out to a flat fine. I am getting the error "Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"
Although if I run the bcp command separately I do not get this error and the command runs successfully. I am using the Exec Master..xp_cmdshell command.

Please advice.

Thanks,
PankajError = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"

this error occurs when you give the output path as a directory which does not exist. i.e. you give the path as C:\enigma\enigma.txt even though the directory enigma does not exist on your PC. Create the directory and reissue the command. The BCP command only creates a data file ... not the directory

BCP and Copy permissions

Hi,
I'm creating a number of CSV files in a stored procedure and I want to
copy them to a mapped network drive. I can create the CSV files
locally without any problem but I'm having trouble copying them to the
network drive. While I try to write to the network drive directly
using BCP I get the following error:
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host
data-file
Any when I try to use a batch file (which is trigger by the query) to
copy the files locally to the network I get the following error:
The system cannot find the drive specified.
If I run either through the command prompt they both work fine.
I've looked at the permissions on the destination folder and set that
to all full control for everyone and I've also changed the user
account SQL Agent run under without any success.
Any help on this would be much appreciated.
Thanks
SimonOn Oct 11, 12:35 pm, accyboy1981 <accyboy1...@.gmail.com> wrote:
> Hi,
> I'm creating a number of CSV files in a stored procedure and I want to
> copy them to a mapped network drive. I can create the CSV files
> locally without any problem but I'm having trouble copying them to the
> network drive. While I try to write to the network drive directly
> using BCP I get the following error:
> Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host
> data-file
> Any when I try to use a batch file (which is trigger by the query) to
> copy the files locally to the network I get the following error:
> The system cannot find the drive specified.
> If I run either through the command prompt they both work fine.
> I've looked at the permissions on the destination folder and set that
> to all full control for everyone and I've also changed the user
> account SQL Agent run under without any success.
> Any help on this would be much appreciated.
> Thanks
> Simon
I'm not positive about this, but aren't network drives mapped on a per-
user basis? Try using the UNC path instead of a mapped drive letter.
Sandy Barnabas

BCN service questions

Hi

some questions about BCN service :

- WE have several remote clients and we don’t know their addresses.

- We create a BCN Service for dynamic routes configuration.

- Each time we send a message to a specific user message is sent to BCN asking for a route

- Messages for a specific user can have specific conversation group id

( Question : if we send several message to the client with no route does it mean that there will be several messages to BCN ?.

My guess that “missing route” message is sent to BCN for every new conversation but not for several messages inside one conversation. )

- BCN service doesn’t answer immediately because it doesn’t know the address.

- There will be an event in the system ( client is connected and provide an address ) when we know his address and only then BCN will answer to the message asking for the route for this client.

( Question : if we have several messages ( probably several conversations ) asking route for a specific client does it mean we need to answer to all these messages/conversations? ).

Will this work?

Leonid.

When the service broker service begins a dialog with the BCN service and sends a missing route message, it will not send any more messages until the BCN service ends the dialog. When the dialog is ended, broker will try to classify dialogs targetting the missing service again. If no routes are found, the messages remain in DELAYED state for 10 minutes. Then service broker service will begin a new conversation with the BCN service to re-request the missing route.

|||

So if I have several clients with no routes information then BCN is not a solution ...

I mean the scenario I have presented in my question won;t work?

|||

What do you mean by several client? If you mean target services, Service Broker will send a request to BCN for each service with a missing route. So I don't see why this shouldn't work.