Error in bcp of trace file

Hi, i've created a trace file which shows columns EventClass, TextData, SPID
and ServerName.
I can save this to a sql table by using profiler, save as, trace table optio
n.
If however i try and create a bcp process to do this automatically, it keeps
on failing with:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP
data-file
I create the table with the same definitions as the profiler has created
them ie
CREATE TABLE [dbo].[test] (
[RowNumber] [int] NULL ,
[EventClass] [int] NULL ,
[TextData] [ntext] NULL ,
[SPID] [int] NULL ,
[ServerName] [nvarchar] (128) NULL
) ON [PRIMARY]
GO
execute master.dbo.xp_cmdshell 'bcp db1.dbo.test in c:\temp\test.trc -Usa -P
-S -c'
Any suggestions?Use function fn_trace_gettable to accomplish what you want.
Example:
select * from ::fn_trace_gettable('c:\temp\test.trc', default)
go
AMB
"billu" wrote:

> Hi, i've created a trace file which shows columns EventClass, TextData, SP
ID
> and ServerName.
> I can save this to a sql table by using profiler, save as, trace table opt
ion.
> If however i try and create a bcp process to do this automatically, it kee
ps
> on failing with:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP
> data-file
> I create the table with the same definitions as the profiler has created
> them ie
> CREATE TABLE [dbo].[test] (
> [RowNumber] [int] NULL ,
> [EventClass] [int] NULL ,
> [TextData] [ntext] NULL ,
> [SPID] [int] NULL ,
> [ServerName] [nvarchar] (128) NULL
> ) ON [PRIMARY]
> GO
> execute master.dbo.xp_cmdshell 'bcp db1.dbo.test in c:\temp\test.trc -Usa
-P
> -S -c'
> Any suggestions?

error in --> EXEC xp_cmdshell ''bcp "SELECT * FROM tbl a where a.flag=N" queryout

some one can tell if i can make a "where" clause inside the BCP , when i say : select * FROM tbl where flag=N , usually the where flag='N' works with the ' ' , but it gives no error when save the store procedure without it ' ' saves nicely the SP,

but

like this it saves well "SELECT * FROM tbl where flag=N" and

when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."

i dont now , any help

PS: perhaps at the end of the BCP > -c -T , must have some more or less

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

|||

Manivannan.D.Sekaran wrote:

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

yes i tryed both ways with 'N' and "N" and it says the same error message

with this

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt="N"" queryout "'

Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'N'.

with this says

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt= 'N' " queryout "'

Msg 102, Level 15, State 1, Procedure spDELTA_P1_RANGEL_STOCKS, Line 59

Incorrect syntax near ' " queryout "'.

|||

Please copy and paste the following code,

Instead of 2 single quotes you are trying with 1 double quote.

Code Snippet

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''" queryout F.txt -c -T'

|||

THANK YOU A LOT

and it was only a little thing like a '