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
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Friday, March 9, 2012
Sunday, February 26, 2012
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:
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
Friday, February 17, 2012
Error
This is a multi-part message in MIME format.
--=_NextPart_000_0026_01C360BE.A39C7B60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
When using VB with SQL Server we get the following errors at various = times: Connection is busy with results of another command Cannot create new connection because in manual or = distributed transaction mode Is there a way to fix this problem without changing code. I am = using static cursor type while retrieving the data.
I am using Visual basic 6.0 with SQL Server 2000 and the operating = system is Win 2000 professional.
TIA,
Gary
--=_NextPart_000_0026_01C360BE.A39C7B60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
When using VB with SQL Server we get = the following errors at various times: Connection is busy with = results of another command Cannot create new connection = because in manual or distributed transaction mode Is there a way to fix = this problem without changing code. I am using static cursor type while = retrieving the data.
I am using Visual basic 6.0 with = SQL Server 2000 and the operating system is Win 2000 professional.
TIA,
Gary
--=_NextPart_000_0026_01C360BE.A39C7B60--This is a multi-part message in MIME format.
--=_NextPart_000_002A_01C360AD.DA018FD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The connection busy issue is because you haven't closed the recordset =before trying to execute another query on the same database connection =object - try setting the recordset object to nothing or use another =database connection object.
Not sure about the second - what are you doing?
-- Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=3D21
(Create your own groups, Forum, FAQ's and a ton more)
--=_NextPart_000_002A_01C360AD.DA018FD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The connection busy issue is because =you haven't closed the recordset before trying to execute another query on the same =database connection object - try setting the recordset object to nothing or use =another database connection object.
Not sure about the second - what are =you doing?
-- Tony RogersonSQL Server =MVPhttp://www.sqlserverfaq.com?mbr=3D21">http://www.sqlserverfaq.com=?mbr=3D21(Create your own groups, Forum, FAQ's and a ton =more)
--=_NextPart_000_002A_01C360AD.DA018FD0--
--=_NextPart_000_0026_01C360BE.A39C7B60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
When using VB with SQL Server we get the following errors at various = times: Connection is busy with results of another command Cannot create new connection because in manual or = distributed transaction mode Is there a way to fix this problem without changing code. I am = using static cursor type while retrieving the data.
I am using Visual basic 6.0 with SQL Server 2000 and the operating = system is Win 2000 professional.
TIA,
Gary
--=_NextPart_000_0026_01C360BE.A39C7B60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
When using VB with SQL Server we get = the following errors at various times: Connection is busy with = results of another command Cannot create new connection = because in manual or distributed transaction mode Is there a way to fix = this problem without changing code. I am using static cursor type while = retrieving the data.
I am using Visual basic 6.0 with = SQL Server 2000 and the operating system is Win 2000 professional.
TIA,
Gary
--=_NextPart_000_0026_01C360BE.A39C7B60--This is a multi-part message in MIME format.
--=_NextPart_000_002A_01C360AD.DA018FD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The connection busy issue is because you haven't closed the recordset =before trying to execute another query on the same database connection =object - try setting the recordset object to nothing or use another =database connection object.
Not sure about the second - what are you doing?
-- Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=3D21
(Create your own groups, Forum, FAQ's and a ton more)
--=_NextPart_000_002A_01C360AD.DA018FD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The connection busy issue is because =you haven't closed the recordset before trying to execute another query on the same =database connection object - try setting the recordset object to nothing or use =another database connection object.
Not sure about the second - what are =you doing?
-- Tony RogersonSQL Server =MVPhttp://www.sqlserverfaq.com?mbr=3D21">http://www.sqlserverfaq.com=?mbr=3D21(Create your own groups, Forum, FAQ's and a ton =more)
--=_NextPart_000_002A_01C360AD.DA018FD0--
Error
I have full text catalog on my table.
SELECT TOP 10 * FROM MyTable My(NOLOCK)
WHERE CONTAINS My.*, '"a.b.c.d*"')
query execute error;
"Msg 9937, Level 16, State 5, Line 1
Too many full-text columns or the full-text query is too complex to be
executed."
otherwise another query which doesn't have --"--
SELECT TOP 10 * FROM MyTable My(NOLOCK)
WHERE CONTAINS My.*, 'a.b.c.d*')
returns data normally.
what is the differences between them?
Is anyone can help me?
The wildcard is applied to each term in your query, so its a* and b* and c*
and d*. That is why you are getting the error. I can't see a good solution
to this.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Deniz Emeci" <Deniz Emeci@.discussions.microsoft.com> wrote in message
news:176E6C5D-D092-42B7-A7AD-F501304CA6D7@.microsoft.com...
>I have full text catalog on my table.
> SELECT TOP 10 * FROM MyTable My(NOLOCK)
> WHERE CONTAINS My.*, '"a.b.c.d*"')
> query execute error;
> "Msg 9937, Level 16, State 5, Line 1
> Too many full-text columns or the full-text query is too complex to be
> executed."
>
> otherwise another query which doesn't have --"--
> SELECT TOP 10 * FROM MyTable My(NOLOCK)
> WHERE CONTAINS My.*, 'a.b.c.d*')
> returns data normally.
> what is the differences between them?
> Is anyone can help me?
|||a.b.c.d is one term like u.s.a.(united states of america)
so i dont want to divide my term.
"Hilary Cotter" wrote:
> The wildcard is applied to each term in your query, so its a* and b* and c*
> and d*. That is why you are getting the error. I can't see a good solution
> to this.
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> 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
> "Deniz Emeci" <Deniz Emeci@.discussions.microsoft.com> wrote in message
> news:176E6C5D-D092-42B7-A7AD-F501304CA6D7@.microsoft.com...
>
>
SELECT TOP 10 * FROM MyTable My(NOLOCK)
WHERE CONTAINS My.*, '"a.b.c.d*"')
query execute error;
"Msg 9937, Level 16, State 5, Line 1
Too many full-text columns or the full-text query is too complex to be
executed."
otherwise another query which doesn't have --"--
SELECT TOP 10 * FROM MyTable My(NOLOCK)
WHERE CONTAINS My.*, 'a.b.c.d*')
returns data normally.
what is the differences between them?
Is anyone can help me?
The wildcard is applied to each term in your query, so its a* and b* and c*
and d*. That is why you are getting the error. I can't see a good solution
to this.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Deniz Emeci" <Deniz Emeci@.discussions.microsoft.com> wrote in message
news:176E6C5D-D092-42B7-A7AD-F501304CA6D7@.microsoft.com...
>I have full text catalog on my table.
> SELECT TOP 10 * FROM MyTable My(NOLOCK)
> WHERE CONTAINS My.*, '"a.b.c.d*"')
> query execute error;
> "Msg 9937, Level 16, State 5, Line 1
> Too many full-text columns or the full-text query is too complex to be
> executed."
>
> otherwise another query which doesn't have --"--
> SELECT TOP 10 * FROM MyTable My(NOLOCK)
> WHERE CONTAINS My.*, 'a.b.c.d*')
> returns data normally.
> what is the differences between them?
> Is anyone can help me?
|||a.b.c.d is one term like u.s.a.(united states of america)
so i dont want to divide my term.
"Hilary Cotter" wrote:
> The wildcard is applied to each term in your query, so its a* and b* and c*
> and d*. That is why you are getting the error. I can't see a good solution
> to this.
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> 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
> "Deniz Emeci" <Deniz Emeci@.discussions.microsoft.com> wrote in message
> news:176E6C5D-D092-42B7-A7AD-F501304CA6D7@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)