显示标签为“varchar”的博文。显示所有博文
显示标签为“varchar”的博文。显示所有博文

2012年3月27日星期二

Bcp with QA : TABLOCK option

User <Brett Kaiser SQLTeam Scrub> a few days earlier
gave me the TRICK to launch BCP through the QA :

DECLARE @.cmd varchar(8000) ;

SET @.cmd = 'bcp ' + 'TablePerson'
+ ' out ' + 'C:\Program files\MyExportFile.dat'
+ ' -c -t ' + ''
+ ' -r ' + '\n'
+ ' -U ' + 'DBA' +' -P ' + 'SQL' +' -S ' + @.@.servername

EXEC master..xp_cmdshell @.cmd ;

It works GREAT but I'm trying to add the Tablock option at the end to have better performances

+ ' h ' + '"TABLOCK"'

But then I have this error :

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Impossible de trouver la procdure stocke 'TABLOCK'.Originally posted by Karolyn
<SQLTeam Scrub>

Hey...

You can't, and shouldn't

Why do you want to?|||Hem, ... to lock the table while loading the data (?!?)

I use it with the bulk insert and it cuts by 5 the time to load the data

Bulk Insert DB..Tbl
From 'MyPath\MyFile'
With (FieldTerminator = '',
RowTerminator = '\n',
CodePage = 'ACP',
TabLock) ;|||Are you trying to output the ss data to a file (which is what you are doing) ?|||Ok, now we've changed direction...

I would use BULK INSERT...and bcp out...

BOL:

TABLOCK

Specifies that a table-level lock is acquired for the duration of the bulk copy operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock only for the duration of the bulk copy operation reduces lock contention on the table, significantly improving performance.

Which I interprete to mean that you're using the default locking behavior...|||Hem, ... to lock the table while loading the data (?!?)

Change the "out" to "in" if you are loading the data into sql server.|||The BULK INSERT works fine with the TABLOCK option

but the BCP command doesn't recognize this option

OK >>> Bulk Insert ... With (Tablock)
KO >>> EXEC master..xp_cmdshell BCP ... -h "Tablock";

2012年3月25日星期日

bcp unicode files using format files


I have a problem with bcp and format files.

We changed our databases from varchar to nvarchar to support unicode. No

problems so fare with that. It is working fine.

But now I need a format file for the customer table and and it is not
working. It is working fine with the old DB with varchar, but with
nvarchar I'm not able to copy the data. The biggest problem is, that I
got no error message. BCP starts copying to table and finished without
error message.

This is my table:

CREATE TABLE [dbo].[Customer] (
[ID] [int] NOT NULL ,
[CreationTime] [datetime] NULL ,
[ModificationTime] [datetime] NULL ,
[DiscoveryTime] [datetime] NULL ,
[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Class] [int] NULL ,
[Subclass] [int] NULL ,
[Capabilities] [int] NULL ,
[SnapshotID] [int] NOT NULL ,
[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL ,
[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NOT NULL ,
[LocationID] [int] NULL ,
[MirrorID] [binary] (16) NULL ,
[DeleteFlag] [bit] NULL ,
[AdminStatus] [bit] NULL
) ON [PRIMARY]
GO

and this is the format file:

8.0
13
1 SQLINT 1 12 "#~@.~#" 1 ID ""
2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
SQL_Latin1_General_CP1_CI_AS
6 SQLINT 1 12 "#~@.~#" 6 Class ""
7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
SQL_Latin1_General_CP1_CI_AS
12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""

"#~@.~#" is the field terminator. We have a lot of text files with all
kind of charachers in it. So we think this is a set that will never
occur in our files.

Thanks for your help!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Bernd Lambertz <virus@.securiy.com> wrote in message news:<3fb38797$0$196$75868355@.news.frii.net>...
> I have a problem with bcp and format files.
> We changed our databases from varchar to nvarchar to support unicode. No
> problems so fare with that. It is working fine.
> But now I need a format file for the customer table and and it is not
> working. It is working fine with the old DB with varchar, but with
> nvarchar I'm not able to copy the data. The biggest problem is, that I
> got no error message. BCP starts copying to table and finished without
> error message.
> This is my table:
> CREATE TABLE [dbo].[Customer] (
> [ID] [int] NOT NULL ,
> [CreationTime] [datetime] NULL ,
> [ModificationTime] [datetime] NULL ,
> [DiscoveryTime] [datetime] NULL ,
> [Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [Class] [int] NULL ,
> [Subclass] [int] NULL ,
> [Capabilities] [int] NULL ,
> [SnapshotID] [int] NOT NULL ,
> [CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
> NULL ,
> [TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
> NOT NULL ,
> [LocationID] [int] NULL ,
> [MirrorID] [binary] (16) NULL ,
> [DeleteFlag] [bit] NULL ,
> [AdminStatus] [bit] NULL
> ) ON [PRIMARY]
> GO
> and this is the format file:
> 8.0
> 13
> 1 SQLINT 1 12 "#~@.~#" 1 ID ""
> 2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
> 3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
> 4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
> 5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLINT 1 12 "#~@.~#" 6 Class ""
> 7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
> 8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
> 9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
> 10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
> SQL_Latin1_General_CP1_CI_AS
> 11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
> SQL_Latin1_General_CP1_CI_AS
> 12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
> 13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""
> "#~@.~#" is the field terminator. We have a lot of text files with all
> kind of charachers in it. So we think this is a set that will never
> occur in our files.
> Thanks for your help!
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Your table has 15 columns, but the format file has 13 - according to
"Using a Data File with Fewer Fields" in Books Online you should still
have all the columns in the format file, but use 0 for the field
length and column number of the columns you don't want to load. You
should also not specify a terminator for those columns.

If doing that doesn't resolve the issue, perhaps you could post the
BCP command you're using, as well as the error and out files?

Simon|||I tried it with 15 columns but with the same result.

I'm wondering if you have a look at the Online Books page you mentioned they have only SQLCHAR typs for
everything. Okay it is a text file with char in it. But if I have a look at the description on the BCP
page the use SQLINT and SQLBINARY...

What is the right way here. I tried both without success.....

The bcp command is running as a batch job and I get no error. It just looks like a time out,
but no message. So I tried BULK COPY in the query analyzer now:
BULK INSERT unicode_db..customer FROM 'C:\inbox\csv_import\customer.csv'
WITH (FORMATFILE = 'C:\inbox\csv_import\formats\customer.fmt', DATAFILETYPE = 'widechar')

and this is the result:

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider
did not give any information about the error.].
The statement has been terminated.

By the way, I'm using the same stuff for NON-UNICODE with a formatfile with 13 columns all SQLCHAR and
the same table (just VARCHAR instead of NVARCHAR)
and it works fine......

Is that the problem that I have a .csv file saved as unicode and the .fmt file saved as ANSI?
Is the field terminator '#~@.~#' a problem and not supported?
BCP only except ANSI format files??

Simon Hayes wrote:

> Bernd Lambertz <virus@.securiy.com> wrote in message news:<3fb38797$0$196$75868355@.news.frii.net>...
> > I have a problem with bcp and format files.
> > We changed our databases from varchar to nvarchar to support unicode. No
> > problems so fare with that. It is working fine.
> > But now I need a format file for the customer table and and it is not
> > working. It is working fine with the old DB with varchar, but with
> > nvarchar I'm not able to copy the data. The biggest problem is, that I
> > got no error message. BCP starts copying to table and finished without
> > error message.
> > This is my table:
> > CREATE TABLE [dbo].[Customer] (
> > [ID] [int] NOT NULL ,
> > [CreationTime] [datetime] NULL ,
> > [ModificationTime] [datetime] NULL ,
> > [DiscoveryTime] [datetime] NULL ,
> > [Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [Class] [int] NULL ,
> > [Subclass] [int] NULL ,
> > [Capabilities] [int] NULL ,
> > [SnapshotID] [int] NOT NULL ,
> > [CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
> > NULL ,
> > [TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
> > NOT NULL ,
> > [LocationID] [int] NULL ,
> > [MirrorID] [binary] (16) NULL ,
> > [DeleteFlag] [bit] NULL ,
> > [AdminStatus] [bit] NULL
> > ) ON [PRIMARY]
> > GO
> > and this is the format file:
> > 8.0
> > 13
> > 1 SQLINT 1 12 "#~@.~#" 1 ID ""
> > 2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
> > 3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
> > 4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
> > 5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
> > SQL_Latin1_General_CP1_CI_AS
> > 6 SQLINT 1 12 "#~@.~#" 6 Class ""
> > 7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
> > 8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
> > 9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
> > 10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
> > SQL_Latin1_General_CP1_CI_AS
> > 11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
> > SQL_Latin1_General_CP1_CI_AS
> > 12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
> > 13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""
> > "#~@.~#" is the field terminator. We have a lot of text files with all
> > kind of charachers in it. So we think this is a set that will never
> > occur in our files.
> > Thanks for your help!
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> Your table has 15 columns, but the format file has 13 - according to
> "Using a Data File with Fewer Fields" in Books Online you should still
> have all the columns in the format file, but use 0 for the field
> length and column number of the columns you don't want to load. You
> should also not specify a terminator for those columns.
> If doing that doesn't resolve the issue, perhaps you could post the
> BCP command you're using, as well as the error and out files?
> Simonsql

BCP Temporary Tables

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

BCP Temporary Tables

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

BCP Temporary Tables

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

2012年3月22日星期四

BCP puts nulls in output text file

Hi,

I have been trying to output varchar fields from a table to a text file using BCP. When the field in the table is an empty string (where I have specifically set it to '') BCP places an ascii null in the output text file.

Is there a way I can tell it to just not place anything in the output for this field.

Other inportant information: I am trying to use comma as the field terminator (-t,). If I use the default tab terminator, then things seem to be ok.

Thanks for any help.
ScottWhat does your bcp command look like?

with -c it should be OK.

from
create table bcp (s varchar(10), t varchar(10) null, u varchar(10) not null, v varchar(10))
insert bcp select 'asd', 'asd', 'asd', 'asd'
insert bcp select 'asd', '', '', 'asd'
insert bcp select 'asd', 'asd', 'asd', 'asd'
exec master..xp_cmdshell 'bcp test..bcp out c:\bcpfile.txt -S(local) -T -t, -c'

I get
asd,asd,asd,asd
asd, , ,asd
asd,asd,asd,asd|||The command I used was very similar to yours and when I ran yours, I got the same results you did. However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

If I allow nulls in the table, then the output file contains a null (ASCII 0) in that spot between the commas.

Now, if I use DTS to output the file, then everything comes out as I want it to. I just wanted to avoid the use of DTS for something so simple.

Thanks for your help.
Scott|||However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

Actually the fields are NOT empty, they contained a zero length string. This is not the same as empty. The ASCII 0 or (, ,) is a zero length string, if you insert a NULL then your file will only contain (,,).sql

BCP Problems with ISO Latin-1 Characters past ASCII 192

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

2012年3月20日星期二

BCP out in UTF8 format

How to generate a file using BCP in UTF8 format. The table contains varchar,nvarchar and money as dataytpe for some columns. Will the –w switch in BCP for data file type would solve the problem because SQL server stores the data in UCS 2.0 format.
No, BCP will save it as UCS2
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:48BA7BC9-61DF-45FF-8D8F-586C21D0CF07@.microsoft.com...
> How to generate a file using BCP in UTF8 format. The table contains
> varchar,nvarchar and money as dataytpe for some columns. Will the -w
> switch in BCP for data file type would solve the problem because SQL
> server stores the data in UCS 2.0 format.
>

bcp out empty column

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

use monkey
go

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

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

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

drop table tab1
go

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

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

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

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

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

2012年3月8日星期四

BCP failure.

SQL 2005 SP1.

declare @.exec varchar(128)
set @.exec = 'bcp [boxName\mysql2k5].adventureWorks.sales.customer format nul -T -n -f c:\customer.fmt'
--print @.exec
exec master..xp_cmdshell @.exec

gives me:

SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL

(5 row(s) affected)

It's my local PC, that I am local admin on. I did the same thing (different DB of course) on a local 2000 instance and got the same result. It's been a few years since I used BCP, and this isn't ringing a bell. Can someone please assist?

TIA, cfri have observed (in SQL 2K) that bcp does not accept "[" & "]" in name qualifier. i suggest that u remove that part and use -S"boxName\mysql2k5" syntax instead.|||Check in the Client Network Configuration (should be in the SQL Server program group) to see if Shared Memory protocol is enabled.|||Doh!!!! :eek:

I just read BOL (which I obviously should have done first) and the server name isn't even part of the syntax (where I was trying to use it).

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
[-q] [-C code_page] [-t field_term] [-r row_term]
[-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]|||To further clarify, it would appear that the ServerName\Instance name MUST be used on a named instance.

/*Done on a default instance, this works fine with no server\instance name*/
declare @.exec varchar(128)
set @.exec = 'bcp aps_prod_071806.dbo.office format nul -T -n -f c:\office.fmt'
--print @.exec
exec master..xp_cmdshell @.exec

/*Done on a named instance, I can't get this to work UNLESS I specify the server\instance name*/
declare @.exec varchar(128)
set @.exec = 'bcp aps.dbo.office format -n -c -f c:\office.fmt -S"Server\InstanceName" -T'
--print @.exec
exec master..xp_cmdshell @.exec

2012年2月25日星期六

bcp command

if i use:
--code--
DECLARE @.CMD VARCHAR(8000)
SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"' +
@.@.servername + '" -T'
exec master..xp_cmdshell @.CMD
--end code--
how could i put the column headers in this spreadsheet?
or
is there a better way of getting a sql statement to save directly to excel?Have a look here:
76c9997" target="_blank">http://groups.google.de/group/micro...
76c9997
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"UNOTech" <UNOTech@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CDA8B4C3-3CB1-418A-A649-B928CB8FB275@.microsoft.com...
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
> +
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to
> excel?|||I'd advise to do it directly from Excel using the menu choices in Excel. go
to Data...Get Extranal Data...New Database Query... It is very clean. You
will need to set up an ODBC connection to do this.
"UNOTech" wrote:
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
+
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to excel?[/color
]

2012年2月12日星期日

Batch execution

Hello All,
I have a table TableX with one column SQL varchar(8000). This column
contains about 50 records (rows) like
insert into tablename1 select datetime,...From Tbl1 Where...
insert into tablename2 select datetime,...From Tbl2 Where...
insert into tablename3 select datetime,...From Tbl3 Where...
...
My question is, is there any way to run these dml statements from tableX all
at one time as a batch rather than looping through TableX and executing them
one by one.
Thanks
RickyHere goes:
set nocount on;
use tempdb;
go
drop table t1, tablex;
go
create table t1(col1 int);
go
create table tablex(sql varchar(8000) not null);
insert into tablex values('insert into t1 values(1);');
insert into tablex values('insert into t1 values(2);');
insert into tablex values('insert into t1 values(3);');
go
exec master..xp_execresultset
N'select sql from tablex;',
N'tempdb'
go
select * from t1;
-- Output:
col1
--
1
2
3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
> Hello All,
> I have a table TableX with one column SQL varchar(8000). This column
> contains about 50 records (rows) like
> insert into tablename1 select datetime,...From Tbl1 Where...
> insert into tablename2 select datetime,...From Tbl2 Where...
> insert into tablename3 select datetime,...From Tbl3 Where...
> ...
> My question is, is there any way to run these dml statements from tableX
> all
> at one time as a batch rather than looping through TableX and executing
> them
> one by one.
> Thanks
> Ricky|||Thank You Itzik Ben-Gan.
"Brilliant as always...!!"
"Itzik Ben-Gan" wrote:

> Here goes:
> set nocount on;
> use tempdb;
> go
> drop table t1, tablex;
> go
> create table t1(col1 int);
> go
> create table tablex(sql varchar(8000) not null);
> insert into tablex values('insert into t1 values(1);');
> insert into tablex values('insert into t1 values(2);');
> insert into tablex values('insert into t1 values(3);');
> go
> exec master..xp_execresultset
> N'select sql from tablex;',
> N'tempdb'
> go
> select * from t1;
> -- Output:
> col1
> --
> 1
> 2
> 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
>
>|||Anybody know how to get the value of an output parameter from a proc
executed with xp_ExecuteResultSet?
For Example:
set nocount on
use tempdb
go
create table T (t text default '')
go
create proc P (@.T1 text, @.Message varchar(255) Out)
as
begin
select @.T1
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
DECLARE @.ErrorMessage nvarchar(4000)
SET @.SQL = 'exec p ''This is a test.'', @.ErrorMessage'
exec master..xp_execresultset @.SQL, N'tempdb'
select @.ErrorMessage
go
drop table T
drop proc P
Message posted via http://www.webservertalk.com|||--It would go something like this.
set nocount on
use tempdb
go
Create Table ##ErrorMessage (F1 [varchar] (8000))
go
create proc P (@.T1 nvarchar(4000), @.Message varchar(255) Out)
as
begin
select
T1 = @.T1,
Message = @.Message
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
SET @.SQL = 'EXECUTE(''' + char(13) + char(10) +
'DECLARE @.ErrorMessage nvarchar(4000) ' + char(13) + char(10) +
'exec p ''''This is a test.'''', @.ErrorMessage OUT' + char(13) + char(10)
+
'insert ##ErrorMessage values(@.ErrorMessage)' + char(13) + char(10) +
''')'
--exec sp_executesql @.SQL
SET @.SQL = 'SELECT ''' + Replace(@.SQL, '''', ''') + ''''
exec master..xp_execresultset @.SQL, N'tempdb'
select Message = F1 from ##ErrorMessage
go
drop table ##ErrorMessage
drop proc P
Message posted via http://www.webservertalk.com