bcp: starting copy...

hello - i have this problem:
i am able to use bulk insert script like
"bulk insert demodb..table1
from 'c:\filename.ext' with (formatfile = 'c:\bcp.fmt')"
to import a binary file into sql server.
table1 has 4 columns, noted column is number 2, type image, here's my format file:
8.0
1
1 SQLIMAGE 0 100184 "" 2 logo ""
i import only the binary file.
however when trying to do the same using bcp with the same format file, it just says
"starting copy..." and goes to sleep !
any idea what i should do?
i created a temp table with just an IMAGE column and then the bcp method works, but i rather need it with my current setup - table with 4 columns

thank youAny error or information on SQL error log for this behaviour.

Can take help of PROFILER to see the activity during this execution.

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 help

Hi, I'm hoping someone can help me

When trying to export from SQL to a file using the following:

------------
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)

SET @.FileName =
REPLACE('c:\tmp\tblComponentType_'+CONVERT(char(8) ,GETDATE(),1)+'.txt','/','
-')

SET @.bcpCommand = 'bcp "SELECT * FROM PNP..tblComponentType" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U pnpadmin -P admin -c'

EXEC master..xp_cmdshell @.bcpCommand
-------------

SQL Server gives me this error:

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

I've given the user datareader/writer permissions, also permission to SELECT
from that table. I get the feeling I need to qualify the SQL Server name
somehow.

Many thanks,
PaulDECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)

SET @.FileName =
REPLACE('c:\tmp\tblComponentType_'+CONVERT(char(8) ,GETDATE(),1)+'.txt','/','
-')

SET @.bcpCommand = 'bcp "SELECT * FROM PNP..tblComponentType" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U pnpadmin -P admin -c' +
'-S'+@.@.servername

EXEC master..xp_cmdshell @.bcpCommand

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"Paul Sampson" <psampson@.uecomm.com.au> wrote in message
news:1063168972.776810@.proxy.uecomm.net.au...
> Hi, I'm hoping someone can help me
> When trying to export from SQL to a file using the following:
> ------------
> DECLARE @.FileName varchar(50),
> @.bcpCommand varchar(2000)
> SET @.FileName =
REPLACE('c:\tmp\tblComponentType_'+CONVERT(char(8) ,GETDATE(),1)+'.txt','/','
> -')
> SET @.bcpCommand = 'bcp "SELECT * FROM PNP..tblComponentType" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U pnpadmin -P admin -c'
> EXEC master..xp_cmdshell @.bcpCommand
> -------------
> SQL Server gives me this error:
> ------------
> 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
> ------------
> I've given the user datareader/writer permissions, also permission to
SELECT
> from that table. I get the feeling I need to qualify the SQL Server name
> somehow.
> Many thanks,
> Paul|||Thanks!

"oj" <nospam_ojngo@.home.com> wrote in message
news:Hhy7b.18455$mp.11900@.rwcrnsc51.ops.asp.att.ne t...
> DECLARE @.FileName varchar(50),
> @.bcpCommand varchar(2000)
> SET @.FileName =
REPLACE('c:\tmp\tblComponentType_'+CONVERT(char(8) ,GETDATE(),1)+'.txt','/','
> -')
> SET @.bcpCommand = 'bcp "SELECT * FROM PNP..tblComponentType" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U pnpadmin -P admin -c' +
> '-S'+@.@.servername
> EXEC master..xp_cmdshell @.bcpCommand
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "Paul Sampson" <psampson@.uecomm.com.au> wrote in message
> news:1063168972.776810@.proxy.uecomm.net.au...
> > Hi, I'm hoping someone can help me
> > When trying to export from SQL to a file using the following:
> > ------------
> > DECLARE @.FileName varchar(50),
> > @.bcpCommand varchar(2000)
> > SET @.FileName =
REPLACE('c:\tmp\tblComponentType_'+CONVERT(char(8) ,GETDATE(),1)+'.txt','/','
> > -')
> > SET @.bcpCommand = 'bcp "SELECT * FROM PNP..tblComponentType" queryout "'
> > SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U pnpadmin -P admin -c'
> > EXEC master..xp_cmdshell @.bcpCommand
> > -------------
> > SQL Server gives me this error:
> > ------------
> > 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
> > ------------
> > I've given the user datareader/writer permissions, also permission to
> SELECT
> > from that table. I get the feeling I need to qualify the SQL Server name
> > somehow.
> > Many thanks,
> > Paul