Error: 4813 Expected the text length in data stream for bulk copy

Hi,
I am getting this error with Transactional replication for a table which has
column data type text, and has no data in it.
"Error: 4813,Expected the text length in data stream for bulk copy of text,
ntext, or image data."
Any idea why I am getting this error.
Thanks
Shan
Have a look at this
http://groups-beta.google.com/group/...e=source&hl=en
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:D8A5F5BC-9BFE-4CC0-A12F-1362DB8FAC77@.microsoft.com...
> Hi,
> I am getting this error with Transactional replication for a table which
has
> column data type text, and has no data in it.
> "Error: 4813,Expected the text length in data stream for bulk copy of
text,
> ntext, or image data."
> Any idea why I am getting this error.
> Thanks
> Shan

Error while executing a a query string using EXEC statement

Hi,

I have written a stored proc to bulk insert the data from a data file.

I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query

Code Snippet

DECLARE @.LastUpdate varchar(20)

SET @.LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category

SELECT MSISDN, @.LastUpdate FROM OPENROWSET( BULK '\\remotemachine\datafile.txt',

FORMATFILE = '\\remotemachine\FormatFile.fmt',

FIRSTROW = 2) AS a

To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below

Code Snippet

@.Category - Will be passed as a parameter to the stored proc

DECLARE @.vsBulkSQL VARCHAR(MAX)

DECLARE @.LastUpdate varchar(20)

SET @.LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @.vsBulkSQL ='INSERT INTO '+ @.Category + ' SELECT MSISDN, ''' + @.LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\\remotemachine\datafile.txt'+ ''''+ ' ,' +

+ ' FORMATFILE ' + '=' + ''''+ '\\remotemachine\FormatFile.fmt'+ ''''+ ',' +

' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @.vsBulkSQL - This prints the folliwing statement

INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\\remotemachine\DataFile.txt' , FORMATFILE ='\\remotemachine\FormatFile.fmt', FIRSTROW =2) AS a

Exec @.vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\\remotemachine\Second.txt' , FORMATFILE ='\\remotemachine\FormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.

Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

Does it work if you alias the @.LastUpdate value?

|||

I suspect you need to execute the query with

EXEC (@.vsBulkSQL)

You have left out the parentheses, and when you do that, EXEC expects the variable to hold a procedure name, not a query string.

Steve Kass

Drew University

www.stevekass.com

|||

Steve,

Good catch. Its working now if i have the string etween ( and ).

Thanks a lot

~Mohan