Hi,
I'm making a transactional replication.
I get the following error:
Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
(Source: SQL4 (Data source); Error number: 4819)
Function sequence error
(Source: ODBC Driver Manager (ODBC); Error number: S1010)
Anyone that knows how to solve the problem?
Thanks in advance
Anders
your collation on your publication database is different from your collation
on the subscriber database.
To solve your problem use the same collation on both databases.
"Anders Johansson" <anders.johansson@.nospam.se> wrote in message
news:OQ9CtRuJEHA.1096@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm making a transactional replication.
> I get the following error:
> Could not bulk insert. Bulk data stream was incorrectly specified as
sorted.
> (Source: SQL4 (Data source); Error number: 4819)
> ----
--
> --
> Function sequence error
> (Source: ODBC Driver Manager (ODBC); Error number: S1010)
> ----
--
> --
> Anyone that knows how to solve the problem?
> Thanks in advance
> Anders
>
|||Hi Hilary!
The publication database had a different collation than the subscriber.
But I didnt change the whole subscriber collation - just the article that
went wrong. (There was an alternative in the Publication properties).
Thanks!
"Hilary Cotter" <hilaryk@.att.net> skrev i meddelandet
news:%23tQRaguJEHA.3944@.tk2msftngp13.phx.gbl...
> your collation on your publication database is different from your
collation
> on the subscriber database.
> To solve your problem use the same collation on both databases.
> "Anders Johansson" <anders.johansson@.nospam.se> wrote in message
> news:OQ9CtRuJEHA.1096@.TK2MSFTNGP10.phx.gbl...
> sorted.
> ----
> --
> ----
> --
>
|||damn, why didn't I think of that. Good idea!
"Anders Johansson" <anders.johansson@.nospam.se> wrote in message
news:uYQGUquJEHA.3380@.TK2MSFTNGP09.phx.gbl...
> Hi Hilary!
> The publication database had a different collation than the subscriber.
> But I didnt change the whole subscriber collation - just the article that
> went wrong. (There was an alternative in the Publication properties).
> Thanks!
> "Hilary Cotter" <hilaryk@.att.net> skrev i meddelandet
> news:%23tQRaguJEHA.3944@.tk2msftngp13.phx.gbl...
> collation
> ----
> ----
>
Showing posts with label bulk. Show all posts
Showing posts with label bulk. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
error when executing sql xml bulk load
I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
my problem. I get a bulk load error stating I need a sql:relationship for
any of my nested complex types but the elements in my nested complex type is
not going to another table but to the same table as my first complex type.
How do I create nested complex types that bulk load the elements to the same
table?
In the example below how do I represent the "Phone" as a complex type in the
xsd and have the bulk upload load the elements to the same table as my root
complex type?
Ex.
<Root>
<ContactInfo>
<FirstName></FirstName>
<LastName></LastName>
<Phone>
<Home></Home>
<Cell></Cell>
</Phone>
</ContactInfo>
</Root>
So how do i layout the xsd for the "Phone" and either not have to provide
the relationship if the data goes to the same sql table or how do I provide
the sql:relationship so I don't get the error?
Hello Semloh,
I have notice that you have send the same issue on Feb 27th which I have
replied. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
Based on my research, you should follow the article to create 2 table to
get the XML file uploaded to the database.
XML Bulk Load Examples (SQLXML 4.0)
http://msdn2.microsoft.com/en-us/ms171806.aspx
In this article you could see that the XSD schema include a
sql:relationship attribute.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="Order"
sql:relation="CustOrder"
sql:relationship="CustCustOrder" >
<xsd:complexType>
<xsd:attribute name="OrderID" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
If you want to upload all the element into one table, you could use a XSLT
to transform the original XML file to up the level of your Phone element.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi,
If the Phone element is just a wrapper for Home and Cell (whch will be
mapped to fileds in the database) that you can use sql:is-constant="true" on
the Phone element and you don't need relationship.
I hope this helps.
Thanks,
Monica Frintu
"semloh" wrote:
> I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
> my problem. I get a bulk load error stating I need a sql:relationship for
> any of my nested complex types but the elements in my nested complex type is
> not going to another table but to the same table as my first complex type.
> How do I create nested complex types that bulk load the elements to the same
> table?
> In the example below how do I represent the "Phone" as a complex type in the
> xsd and have the bulk upload load the elements to the same table as my root
> complex type?
> Ex.
> <Root>
> <ContactInfo>
> <FirstName></FirstName>
> <LastName></LastName>
> <Phone>
> <Home></Home>
> <Cell></Cell>
> </Phone>
> </ContactInfo>
> </Root>
>
> So how do i layout the xsd for the "Phone" and either not have to provide
> the relationship if the data goes to the same sql table or how do I provide
> the sql:relationship so I don't get the error?
>
my problem. I get a bulk load error stating I need a sql:relationship for
any of my nested complex types but the elements in my nested complex type is
not going to another table but to the same table as my first complex type.
How do I create nested complex types that bulk load the elements to the same
table?
In the example below how do I represent the "Phone" as a complex type in the
xsd and have the bulk upload load the elements to the same table as my root
complex type?
Ex.
<Root>
<ContactInfo>
<FirstName></FirstName>
<LastName></LastName>
<Phone>
<Home></Home>
<Cell></Cell>
</Phone>
</ContactInfo>
</Root>
So how do i layout the xsd for the "Phone" and either not have to provide
the relationship if the data goes to the same sql table or how do I provide
the sql:relationship so I don't get the error?
Hello Semloh,
I have notice that you have send the same issue on Feb 27th which I have
replied. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
Based on my research, you should follow the article to create 2 table to
get the XML file uploaded to the database.
XML Bulk Load Examples (SQLXML 4.0)
http://msdn2.microsoft.com/en-us/ms171806.aspx
In this article you could see that the XSD schema include a
sql:relationship attribute.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="Order"
sql:relation="CustOrder"
sql:relationship="CustCustOrder" >
<xsd:complexType>
<xsd:attribute name="OrderID" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
If you want to upload all the element into one table, you could use a XSLT
to transform the original XML file to up the level of your Phone element.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi,
If the Phone element is just a wrapper for Home and Cell (whch will be
mapped to fileds in the database) that you can use sql:is-constant="true" on
the Phone element and you don't need relationship.
I hope this helps.
Thanks,
Monica Frintu
"semloh" wrote:
> I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
> my problem. I get a bulk load error stating I need a sql:relationship for
> any of my nested complex types but the elements in my nested complex type is
> not going to another table but to the same table as my first complex type.
> How do I create nested complex types that bulk load the elements to the same
> table?
> In the example below how do I represent the "Phone" as a complex type in the
> xsd and have the bulk upload load the elements to the same table as my root
> complex type?
> Ex.
> <Root>
> <ContactInfo>
> <FirstName></FirstName>
> <LastName></LastName>
> <Phone>
> <Home></Home>
> <Cell></Cell>
> </Phone>
> </ContactInfo>
> </Root>
>
> So how do i layout the xsd for the "Phone" and either not have to provide
> the relationship if the data goes to the same sql table or how do I provide
> the sql:relationship so I don't get the error?
>
error when executing sql xml bulk load
I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
my problem. I get a bulk load error stating I need a sql:relationship for
any of my nested complex types but the elements in my nested complex type is
not going to another table but to the same table as my first complex type.
How do I create nested complex types that bulk load the elements to the same
table?
In the example below how do I represent the "Phone" as a complex type in the
xsd and have the bulk upload load the elements to the same table as my root
complex type?
Ex.
<Root>
<ContactInfo>
<FirstName></FirstName>
<LastName></LastName>
<Phone>
<Home></Home>
<Cell></Cell>
</Phone>
</ContactInfo>
</Root>
So how do i layout the xsd for the "Phone" and either not have to provide
the relationship if the data goes to the same sql table or how do I provide
the sql:relationship so I don't get the error?Hello Semloh,
I have notice that you have send the same issue on Feb 27th which I have
replied. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
---
Based on my research, you should follow the article to create 2 table to
get the XML file uploaded to the database.
XML Bulk Load Examples (SQLXML 4.0)
http://msdn2.microsoft.com/en-us/ms171806.aspx
In this article you could see that the XSD schema include a
sql:relationship attribute.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="Order"
sql:relation="CustOrder"
sql:relationship="CustCustOrder" >
<xsd:complexType>
<xsd:attribute name="OrderID" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
If you want to upload all the element into one table, you could use a XSLT
to transform the original XML file to up the level of your Phone element.
---
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi,
If the Phone element is just a wrapper for Home and Cell (whch will be
mapped to fileds in the database) that you can use sql:is-constant="true" on
the Phone element and you don't need relationship.
I hope this helps.
Thanks,
--
Monica Frintu
"semloh" wrote:
> I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
> my problem. I get a bulk load error stating I need a sql:relationship for
> any of my nested complex types but the elements in my nested complex type
is
> not going to another table but to the same table as my first complex type.
> How do I create nested complex types that bulk load the elements to the sa
me
> table?
> In the example below how do I represent the "Phone" as a complex type in t
he
> xsd and have the bulk upload load the elements to the same table as my roo
t
> complex type?
> Ex.
> <Root>
> <ContactInfo>
> <FirstName></FirstName>
> <LastName></LastName>
> <Phone>
> <Home></Home>
> <Cell></Cell>
> </Phone>
> </ContactInfo>
> </Root>
>
> So how do i layout the xsd for the "Phone" and either not have to provide
> the relationship if the data goes to the same sql table or how do I provid
e
> the sql:relationship so I don't get the error?
>
my problem. I get a bulk load error stating I need a sql:relationship for
any of my nested complex types but the elements in my nested complex type is
not going to another table but to the same table as my first complex type.
How do I create nested complex types that bulk load the elements to the same
table?
In the example below how do I represent the "Phone" as a complex type in the
xsd and have the bulk upload load the elements to the same table as my root
complex type?
Ex.
<Root>
<ContactInfo>
<FirstName></FirstName>
<LastName></LastName>
<Phone>
<Home></Home>
<Cell></Cell>
</Phone>
</ContactInfo>
</Root>
So how do i layout the xsd for the "Phone" and either not have to provide
the relationship if the data goes to the same sql table or how do I provide
the sql:relationship so I don't get the error?Hello Semloh,
I have notice that you have send the same issue on Feb 27th which I have
replied. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
---
Based on my research, you should follow the article to create 2 table to
get the XML file uploaded to the database.
XML Bulk Load Examples (SQLXML 4.0)
http://msdn2.microsoft.com/en-us/ms171806.aspx
In this article you could see that the XSD schema include a
sql:relationship attribute.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="Order"
sql:relation="CustOrder"
sql:relationship="CustCustOrder" >
<xsd:complexType>
<xsd:attribute name="OrderID" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
If you want to upload all the element into one table, you could use a XSLT
to transform the original XML file to up the level of your Phone element.
---
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi,
If the Phone element is just a wrapper for Home and Cell (whch will be
mapped to fileds in the database) that you can use sql:is-constant="true" on
the Phone element and you don't need relationship.
I hope this helps.
Thanks,
--
Monica Frintu
"semloh" wrote:
> I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
> my problem. I get a bulk load error stating I need a sql:relationship for
> any of my nested complex types but the elements in my nested complex type
is
> not going to another table but to the same table as my first complex type.
> How do I create nested complex types that bulk load the elements to the sa
me
> table?
> In the example below how do I represent the "Phone" as a complex type in t
he
> xsd and have the bulk upload load the elements to the same table as my roo
t
> complex type?
> Ex.
> <Root>
> <ContactInfo>
> <FirstName></FirstName>
> <LastName></LastName>
> <Phone>
> <Home></Home>
> <Cell></Cell>
> </Phone>
> </ContactInfo>
> </Root>
>
> So how do i layout the xsd for the "Phone" and either not have to provide
> the relationship if the data goes to the same sql table or how do I provid
e
> the sql:relationship so I don't get the error?
>
Error when deploying on Server 2003
I built a utility that grabs large XML string from a database and then uses XML bulk load to shread the data in to 4 seperate tables. Everything works great on my box, but when I tried to move it to the dev server I started getting errors.
The first error I got was: Exception: Retrieving the COM class factory for component with CLSID {19AA7334-069C-437F-BFE4-CC9894DB8504} failed due to the following error: 80040154.
I evetually figured out that the XML bulkload dll 'xblkld4.dll' was not registered. I had a hard time getting the DLL registered as it was dependant on a lot of other dll's. Finally I copied all of the dll's from C:\Program Files\Common Files\System\Ole DB that had XML in them and I was able to register.
Now I am getting a new error that I can't find any information on. When the XML bulk load executes, I get the exception: Error creating SAX reader.
Is there a sax reader dll that I need to copy to the dev server and register? Does anybody have any ideas?
~Jason
I think this article may help you deploy your solution and fix your problems.
http://msdn2.microsoft.com/en-us/library/ms171744.aspx
Error when deploying on Server 2003
I built a utility that grabs large XML string from a database and then uses XML bulk load to shread the data in to 4 seperate tables. Everything works great on my box, but when I tried to move it to the dev server I started getting errors.
The first error I got was: Exception: Retrieving the COM class factory for component with CLSID {19AA7334-069C-437F-BFE4-CC9894DB8504} failed due to the following error: 80040154.
I evetually figured out that the XML bulkload dll 'xblkld4.dll' was not registered. I had a hard time getting the DLL registered as it was dependant on a lot of other dll's. Finally I copied all of the dll's from C:\Program Files\Common Files\System\Ole DB that had XML in them and I was able to register.
Now I am getting a new error that I can't find any information on. When the XML bulk load executes, I get the exception: Error creating SAX reader.
Is there a sax reader dll that I need to copy to the dev server and register? Does anybody have any ideas?
~Jason
I think this article may help you deploy your solution and fix your problems.
http://msdn2.microsoft.com/en-us/library/ms171744.aspx
Sunday, February 26, 2012
ERROR USING BULK INSERT
Hi
I'm executing
declare @.ARCHIVOidx varchar(300)
set @.ARCHIVOidx = (Select top 1 MyFile from task)
declare @.sentencia varchar(300)
set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
exec (@.sentencia)
and the SQL says:
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 51
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
The event viewer says
18052 : Error: 3624, Severity: 20, State: 1.
THE machine has (SQL DEV):
WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
SQL WITH SP3
What can I do?
Thanks a lot> exec (@.sentencia)
Change this to PRINT @.sentencia and see if what it yields makes sense, also
try executing this manually.|||ok
The output is:
bulk insert a from 'C:\input\bankAA.idx' with (formatfile
='c:\input\bcpfmt.txt',batchsize=100)
"Aaron Bertrand [SQL Server MVP]" wrote:
> Change this to PRINT @.sentencia and see if what it yields makes sense, als
o
> try executing this manually.
>
>|||The Sintax is OK.
The server inserts 600.000 records and generate error, however
using a smaller file it does not generate error. The original file has 2
million registries, the second 600,000 registries (with errors).
The Tsql generates error with the greatest archives
"Macisu" wrote:
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Okay, now, what happened when you ran this command manually?
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> The Sintax is OK.
I understand that. Which is why I suggested RUNNING the code, not just
looking at it.
> The server inserts 600.000 records and generate error, however
> using a smaller file it does not generate error.
So have you considered using the MAXERRORS parameter? Or cleaning the file
before builk inserting it? Obviously the one with errors in it is failing
because there are rows with errors.
By "smaller file" do you really mean "a file that does not happen to include
any rows that cause an error"?|||The command ran ok, the command generates error 6 mins after executing itsel
f,
when the table has 600.000 records aprox.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Okay, now, what happened when you ran this command manually?
>
>
> "Macisu" <Macisu@.discussions.microsoft.com> wrote in message
> news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
>
>|||> The command ran ok, the command generates error
Uh, could you be more specific?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, could you be more specific?
>
>
the window output says
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 53
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Query batch completed with errors|||This may be a bug, but I have a couple of questions:
Can you post the format file?
Can you try MAXERRORS = 1 and also MAXERRORS = 100, and see if the
problem moves to a different place than around 600000 rows? (By
default, MAXERRORS is 10.)
Can you watch the computer's memory and virtual memory use to see if it
is going up and up to the max before the crash?
Steve Kass
Drew University
Macisu wrote:
>Hi
>I'm executing
>declare @.ARCHIVOidx varchar(300)
>set @.ARCHIVOidx = (Select top 1 MyFile from task)
>declare @.sentencia varchar(300)
>set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
>(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
>exec (@.sentencia)
>and the SQL says:
>Server: Msg 3624, Level 20, State 1, Line 1
>Location: record.cpp:825
>Expression: newColLen >= WideTextPointer::OVERHEAD
>SPID: 51
>Process ID: 3420
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>
>The event viewer says
>18052 : Error: 3624, Severity: 20, State: 1.
>THE machine has (SQL DEV):
>WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
>SQL WITH SP3
>What can I do?
>Thanks a lot
>
>
>
>
I'm executing
declare @.ARCHIVOidx varchar(300)
set @.ARCHIVOidx = (Select top 1 MyFile from task)
declare @.sentencia varchar(300)
set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
exec (@.sentencia)
and the SQL says:
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 51
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
The event viewer says
18052 : Error: 3624, Severity: 20, State: 1.
THE machine has (SQL DEV):
WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
SQL WITH SP3
What can I do?
Thanks a lot> exec (@.sentencia)
Change this to PRINT @.sentencia and see if what it yields makes sense, also
try executing this manually.|||ok
The output is:
bulk insert a from 'C:\input\bankAA.idx' with (formatfile
='c:\input\bcpfmt.txt',batchsize=100)
"Aaron Bertrand [SQL Server MVP]" wrote:
> Change this to PRINT @.sentencia and see if what it yields makes sense, als
o
> try executing this manually.
>
>|||The Sintax is OK.
The server inserts 600.000 records and generate error, however
using a smaller file it does not generate error. The original file has 2
million registries, the second 600,000 registries (with errors).
The Tsql generates error with the greatest archives
"Macisu" wrote:
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Okay, now, what happened when you ran this command manually?
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> The Sintax is OK.
I understand that. Which is why I suggested RUNNING the code, not just
looking at it.
> The server inserts 600.000 records and generate error, however
> using a smaller file it does not generate error.
So have you considered using the MAXERRORS parameter? Or cleaning the file
before builk inserting it? Obviously the one with errors in it is failing
because there are rows with errors.
By "smaller file" do you really mean "a file that does not happen to include
any rows that cause an error"?|||The command ran ok, the command generates error 6 mins after executing itsel
f,
when the table has 600.000 records aprox.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Okay, now, what happened when you ran this command manually?
>
>
> "Macisu" <Macisu@.discussions.microsoft.com> wrote in message
> news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
>
>|||> The command ran ok, the command generates error
Uh, could you be more specific?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, could you be more specific?
>
>
the window output says
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 53
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Query batch completed with errors|||This may be a bug, but I have a couple of questions:
Can you post the format file?
Can you try MAXERRORS = 1 and also MAXERRORS = 100, and see if the
problem moves to a different place than around 600000 rows? (By
default, MAXERRORS is 10.)
Can you watch the computer's memory and virtual memory use to see if it
is going up and up to the max before the crash?
Steve Kass
Drew University
Macisu wrote:
>Hi
>I'm executing
>declare @.ARCHIVOidx varchar(300)
>set @.ARCHIVOidx = (Select top 1 MyFile from task)
>declare @.sentencia varchar(300)
>set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
>(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
>exec (@.sentencia)
>and the SQL says:
>Server: Msg 3624, Level 20, State 1, Line 1
>Location: record.cpp:825
>Expression: newColLen >= WideTextPointer::OVERHEAD
>SPID: 51
>Process ID: 3420
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>
>The event viewer says
>18052 : Error: 3624, Severity: 20, State: 1.
>THE machine has (SQL DEV):
>WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
>SQL WITH SP3
>What can I do?
>Thanks a lot
>
>
>
>
Subscribe to:
Posts (Atom)