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 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";

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

bcp copying,but no file

DECLARE @.query NVARCHAR(2000)
SELECT @.query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @.query

I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL

and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.

Could somebody please help meWhere are you looking for the file?