error 0xC0202092 - what is it?

Pardon, can someone tell me how to resolve and "error 0xC0202092" ?
I am trying to import a text file, with "" as the field text qualifier.
Thanks,
`LeAlright, I have narrowed the error, but not the solution.

The problem occurs when importing a text file using TEXT DELIMITERS.

Specifically, imagine these two records (Text qualifier of "" and field delimeter of "|"):

"Row 1"|"Hello Nurse"|"Goodbye"
"Row 2"|"Hello Jenna"|"Goodbye"

In the 2nd row, there are several spaces after the word "Goodbye". These spaces are causing the import problem!

This error does not appear in SQl Server 2000!

Anyone have a solution?

`Le

Error 0x80040E31

Hi.

Somebody can tell how to deal with this error. When I try to connect from an
asp page to update a record wich has a ntext field it give me this error...

I tried with sp_configure 'remote query timeout',0 but no works...

What can I do ??
thanksDaviso (dhernande7@.alumno.uned.es) writes:

Quote:

Originally Posted by

Somebody can tell how to deal with this error. When I try to connect
from an asp page to update a record wich has a ntext field it give me
this error...
>
I tried with sp_configure 'remote query timeout',0 but no works...

Do you get any text with the error message, or just the error code?

If I am to guess from your fiddling with "remote query timeout", the
error is "Timeout Expired". "remote query timeout" is not going to help
you on that one, since that that is the time SQL Server uses when connecting
to linked servers.

Timeouts are always defined on client level, and from an ASP page, you would
set the CommandTimeout on the Connection object. Set it to 0 to wait
indefintely. By default the timeout is 30 seconds.

Another issue why your update takes more than 30 seconds. It could be a
blocking issue, and in that case it does not help if you change the timeout.
But with no information about your code and your system it's impossible to
tell.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error (8626) while inserting record into table with text field and which is the base for i

I have a problem with inserting records into table when an indexed view
is based on it.
Table has text field (without it there is no problem, but I need it).
Here is a sample code:

USE test
GO

CREATE TABLE dbo.aTable (
[id] INT NOT NULL
, [text] TEXT NOT NULL
)
GO

CREATE VIEW dbo.aView
WITH SCHEMABINDING AS
SELECT [id]
, CAST([text] AS VARCHAR(8000)) [text]
FROM dbo.aTable
GO

CREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERT
AS
BEGIN
INSERT INTO aTable
SELECT [id], [text]
FROM inserted
END
GO

Do the insert into aTable (also through aView).

INSERT INTO dbo.aTable VALUES (1, 'a')
INSERT INTO dbo.aView VALUES (2, 'b')

Still do not have any problem. But when I need index on view

CREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])
GO

I get following error while inserting record into aTable:

-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4
-- Only text pointers are allowed in work tables, never text, ntext, or
image columns. The query processor produced a query plan that required
a text, ntext, or image column in a work table.

Does anyone know what causes the error?ing42 (Inga.Korczowska@.gmail.com) writes:
> Do the insert into aTable (also through aView).
> INSERT INTO dbo.aTable VALUES (1, 'a')
> INSERT INTO dbo.aView VALUES (2, 'b')
> Still do not have any problem. But when I need index on view
> CREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])
> GO
> I get following error while inserting record into aTable:
> -- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4
> -- Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table.
> Does anyone know what causes the error?

Did you notice the warning when you created the index:

Warning: The optimizer cannot use the index because the select list of
the view contains a non-aggregate expression.

So the index is not of much use. I guess you have hit a restriction
in SQL Server, which does not report as such in a nice way. When I
run your code in SQL 2005, I get:

Msg 1942, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.aView'. It contains text, ntext,
image or xml columns.

Which is a more resolute message.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

error

hi i have one varchar field i m convarting that field to datetime in format of dd/mm/yyyy

but i m getting eror

select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,convert(varchar(10),cast(AdditionalField5 as datetime),103) 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

error is

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

change your bold area as follow as,

Convert(datetime, AdditionalField5,103) as 'ValDate'

|||

hi thanx for reply

now i m getting this error

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

|||

Ok.. Expected one.. Use the following query..

SET DATEFORMAT dmy

Select ....
Case When IsDate(AdditionalField5)=1 Then

Convert(datetime, AdditionalField5,103)

Else NULL END as 'ValDate'

... From ....|||

hi i tried this

SET DATEFORMAT dmy
select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,Case when IsDate(AdditionalField5)=1 Then Convert(datetime, AdditionalField5,103) Else NULL END as 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

now getting error

Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

|||Are you using any UNION on your query..?|||

no actuallu it is like this

if @.fileFormatId='83'
Begin

set @.s='select top '+cast( @.j as varchar(10)) +' CMS_Upload_Details_ID,Scheme_Code,DrCr,AdditionalField5 ''ValDate'',AdditionalField13 ''dept_slip'',AdditionalField14 ''dept_dt'',Cheque_No ''Instrm_No'',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 ''Drawer Name'' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status=''Pending'' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top '+ cast(@.i as varchar(10))+' ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = ''Pending '' and Format_ID ='+ convert(varchar(5),@.fileFormatId) +')and Compare_Status=''Pending'' and Format_ID ='+ convert(varchar(5),@.fileFormatId)
end

|||

Try the following query..

Code Snippet

SET DATEFORMAT dmy

if @.fileFormatId='83'
Begin

set @.s='select top '+cast( @.j as varchar(10)) +' CMS_Upload_Details_ID,Scheme_Code,DrCr,Case when IsDate(AdditionalField5)=1 Then Convert(datetime, AdditionalField5,103) Else NULL END as ''ValDate'',AdditionalField13 ''dept_slip'',AdditionalField14 ''dept_dt'',Cheque_No ''Instrm_No'',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 ''Drawer Name'' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status=''Pending'' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top '+ cast(@.i as varchar(10))+' ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = ''Pending '' and Format_ID ='+ convert(varchar(5),@.fileFormatId) +')and Compare_Status=''Pending'' and Format_ID ='+ convert(varchar(5),@.fileFormatId)
end

Exec(@.s)

|||

Hi

Can you just confirm the SQL you are now using for the bold section of your query, and also can we see a sample of the format of the data in AdditionalField5. Also can this field be null (or have non-date text in it) and if so what do you expect to happen in this case.

|||mani actually i tried this earlier and getting error ..see my 2nd previous post|||

mani after printing my query i m getting this one ..so i m running this query.

SET DATEFORMAT dmy
select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,Case when IsDate(AdditionalField5)=1 Then Convert(datetime,AdditionalField5,103) Else NULL END as 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

|||

AdditionalField5 is varchar field ...and i m getting my date in this format

12/21/2006--this is is mm/dd/yyyy format i want to change this dd/mm/yyyy format

|||

I am really wondering....

IsDate() function first parse & validate your input before it parse so there is no issue on your bolded area..

Can you remove other date to character conversions to validate the query..

|||i didn't get u what i have to do now?|||hi yes this field can be null (or have non-date text in it) and so in this case how to d this?

error

what is max size for a string field in latest crystal report?. can it get increased?.I don't think there is any limitation in the crystal. But there is a limitation in UFL's or Function if there return type is string then they can only return maximum 256 character.

Does any body else has idea about this ?

ERR_SYSERR(104):PRM:SQLExecDirect failed (trigger)

The trigger fire correctly with insert new comment, but when user update a
comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDirect
failed
I create a trigger as following:
DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
UPDATE(comment4)
--Get Acct, Sub number
SELECT @.acct = memb.acct, @.sub = prof.no
FROM memb
JOIN prof ON memb.rowno = prof.rowno
--Get Comments
EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTPUT
--Update or insert into other server with linkserver
EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
Please help,
CulamI run an update statement in Query Analyzer, it works fine, but not in the
application.
"culam" wrote:

> The trigger fire correctly with insert new comment, but when user update a
> comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDire
ct
> failed
> I create a trigger as following:
> DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
> IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
> UPDATE(comment4)
> --Get Acct, Sub number
> SELECT @.acct = memb.acct, @.sub = prof.no
> FROM memb
> JOIN prof ON memb.rowno = prof.rowno
> --Get Comments
> EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTP
UT
> --Update or insert into other server with linkserver
> EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
> Please help,
> Culam|||On Mon, 28 Mar 2005 16:21:03 -0800, culam wrote:

>The trigger fire correctly with insert new comment, but when user update a
>comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDirec
t
>failed
>I create a trigger as following:
>DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
>IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
>UPDATE(comment4)
>--Get Acct, Sub number
>SELECT @.acct = memb.acct, @.sub = prof.no
>FROM memb
>JOIN prof ON memb.rowno = prof.rowno
>--Get Comments
>EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTPU
T
> --Update or insert into other server with linkserver
>EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
Hi Culam,
First, I'd like to point out two errors in your trigger code that are
probably not the cause of the error message you report (at least not
directly), but that are important nonetheless.
First: triggers should refer to the inserted and/or deleted pseudotables
to find out which rows were affected by the operation that fired the
trigger. If you refer to the base tables only, you're working on the
entire set of rows in the table, whether updated or not.
Second: triggers fire once per executed statement, not once per row
affected. Your code should ensure that it will also work properly if the
inserted and/or deleted pseudotables contain no rows or more than one
row. If you just use SELECT @.xxx = yyy FROM inserted to copy the new
data in a variable, SQL Server will pick (randomly) one of the new rows,
ignoring all others. It won't even raise an error or warning condition!!
Another (potential - I don't really know your requirements) flaw in your
code is the lack of BEGIN and END after the IF. Are you aware that you
need BEGIN and END if you want more than one statement to be executed
conditionally? Your current code is equivalent to
IF UPDATE(..) OR UPDATE(..) ...
BEGIN
-- Get Acct, Sub number
SELECT @.acct = ..., @.sub = ...
FROM ...
END
-- Get Comments
EXEC usp_LMA_CUBE_trig_comments ...
-- update or insert ...
EXEC usp_LMA_CUBE_upd_comments ...
If an update is executed but none of the columns comment1 through
comment4 is in the SET list, then the stored procedures will be executed
with @.acct and @.sub equal to NULL.
Now we get to your actual error message. I've never seen this message
before; in fact, I've never seen an error message on SQL Server that
uses this format. I suspect that your client software has done some
reformatting of the error message. Could you check the error message you
get when you run the same update statement from Query Analyzer?
I'm quite sure that the error is not raised in the code you posted. That
leaves the two stored procedures as possible offenders. But since you
didn't post the code of these procedures, I can't say any more than
that. Maybe the error will spontaneoously disappear once you fix the
other issues I've mentioned. If not, then I suggest you code the updated
trigger code plus the code of both stored procedures. Posting the DDL
(as CREATE TABLE statements) for all tables involved, plus some rows of
sample data (as INSERT statements) might help as well.
Oh, and by the way - have you considered normalizing your data, storing
the comments in a seperate table where they probably belong? Names like
comment1, comment2, comment3, comment4 r of a repeating group...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)