SQL Import Errors

[jdpatterson74]

Hello,

I am experiencing some validating errors when I am importing Excel 2003 table sheet. I exported the original table within the ablecommerce supplied database to use as a template table for my data. After adding the data, checking for open cells (blanks) and duplications errors, I used the import wizard on SQL Management Studios for my products table. These are the errors I received:

- Validating (Error)
Messages
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Name" with a length of 255 to database column "Name" with a length of 100.
(SQL Server Import and Export Wizard)

Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "ModelNumber" with a length of 255 to database column "ModelNumber" with a length of 40.
(SQL Server Import and Export Wizard)

Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "ThumbnailAltText" with a length of 255 to database column "ThumbnailAltText" with a length of 100.
(SQL Server Import and Export Wizard)

Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "ImageAltText" with a length of 255 to database column "ImageAltText" with a length of 100.
(SQL Server Import and Export Wizard)

Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "IconAltText" with a length of 255 to database column "IconAltText" with a length of 100.
(SQL Server Import and Export Wizard)

Error 0xc002f210: Preparation SQL Task: Executing the query "TRUNCATE TABLE [HFG_TEST].[dbo].[ac_Products]
" failed with the following error: "Cannot truncate table 'HFG_TEST.dbo.ac_Products' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)


I am learning as a go....thanks!

[jmestep]

The name field in the product table has a limitation of 100 characters and some of your names might have been longer. You can increase the size of that field- there was a recent post about it by sohaib telling how.
Your info for the fields might not be too long actually (like the image alt tag error) but possible something in your excel sheet is padding the cells to that length. You might try saving the excel as a .csv, then working to import that.
I'm not sure why you would be getting this error because you shouldn't be truncating that table unless it is trying to do it automatically after the import failed-- was that the only table you were working with?
If your data is in excel with the Able export used as a template, you should try to import it using the Dataport rather than SQL Manangement Studio, or did you try and it didn't work?

[jdpatterson74]

I will research your response, but here is a quick note about Dataport.

I was able to upload products (using dataport), however, my products have variants, options, and optionchoices tables. I have not looked into this yet, but Dataport only has a option for the Products Table, so I do not think I can use it for product choices.

[jmestep]

The dataport will import options for products now, but you would have to do it in xml format.

[jdpatterson74]

Hello, thanks for your response. I was able to check and change the size of the length for those type of errors now.

From the research I found about the following error is because I have to disable the foreign key constraints when I import data (deleting previous data). Otherwise, I get a duplicate key errors.

Error 0xc002f210: Preparation SQL Task: Executing the query "TRUNCATE TABLE [HFG_TEST].[dbo].[ac_Manufacturers]
" failed with the following error: "Cannot truncate table 'HFG_TEST.dbo.ac_Manufacturers' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have tried to figure out how to disable my keys and have not found information that is clear enough for a newbie.



I have tried your suggestion about Dataport, again a NEWBIE...lol and I download a template table. My first option when I try to open the exported table by Dataport XML file is:
1. Open file as an xml table
2. As a read-only workbook
3. Use the xml source task pane.

I choose, Open file as an xml table. (guessing) The next excel note says, "The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data."

Without making any changes to the table I tried to save it as either, XML Data or XML Spreadsheet 2003. Either one comes up with a error saying, " Cannot save or export XML data. The XML maps in this workbook are not exportable."


Any information for a newbie would be helpful to point me in the right direction. I am running blind....

Thanks, joshua

[jmestep]

"The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data."

This is why I asked Able for an xsd schema in a post on the dataport thread. I know I can create it manually, but was hoping they would release it. It would help, even though it might change in the future.

The restraints problem you are having can be overcome by using the dataport utility. There are several product-related tables that rely on the productid for a foreign key before you can insert the data via SQL Server. I had to fix one site manually by disabling all those constraints, disabling the auto-numbering in the tables, then enabling it all back when I was done. It was not fun and you can mess up your whole store if you aren't experienced enough with SQL Server to do it.
You might be able to do it in sql server if you import your products into the product table only without the data like manufacturers that depends on other tables. Also, you SQL process is trying to TRUNCATE TABLE [HFG_TEST].[dbo].[ac_Manufacturers] and is unable to because there must be products in your product table that are linked to that manufacturer, so SQL won't truncate ac_Manufacturers.

[jdpatterson74]

So you are saying that Dataport will not be useful for me because I will need a xml scheme?

And SQL Server is not useful because I will have a lot of adjustments for each table to disable my contraints?

What can I do? I have been asking questions and trying to figure this out (off and on) for a month.....argh

Thanks....

[jmestep]

About the only way you can do it is with the dataport utility unless you are experienced in SQL and able to change a lot then set it back. The site I did it on was one some one had worked on several times and it was a last ditch effort.
You can come up with an XML Schema if you are willing to spend some time- I just haven't had the time yet. You can download some products from the AC7 store, then write a schema based on that. There are some utilities out that will infer an xsd (schema) from an xml file, but I haven't experimented with them.
I found some by searching Google for xsd from xml. I hope to do it within the next couple of weeks, but that won't help you now.

[jmestep]

I was able to create a schema file for just products by using VS 2005. I just haven't found out anything that will work with it. Excel just doesn't seem to like relational data, which is really what the product/options set up is. I've tinkered around with it in Access a little and with XML Fox an XML converter from http://www.xmlfox.com/index.htm, but I've got to quit to work on a deadline job. Here is the schema- it needs to be saved with an .xsd extension.

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified">
  <xs:element name="AbleCommerceExport">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Products">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Product">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="ProductId" type="xs:unsignedShort" />
                    <xs:element name="StoreId" type="xs:unsignedByte" />
                    <xs:element name="Name" type="xs:string" />
                    <xs:element name="Price" type="xs:decimal" />
                    <xs:element name="CostOfGoods" type="xs:unsignedByte" />
                    <xs:element name="MSRP" type="xs:decimal" />
                    <xs:element name="Weight" type="xs:decimal" />
                    <xs:element name="Length" type="xs:unsignedByte" />
                    <xs:element name="Width" type="xs:unsignedByte" />
                    <xs:element name="Height" type="xs:unsignedByte" />
                    <xs:element name="ManufacturerId" type="xs:unsignedByte" />
                    <xs:element name="Manufacturer" type="xs:string" />
                    <xs:element name="Sku" type="xs:string" />
                    <xs:element name="ModelNumber" />
                    <xs:element name="DisplayPage" />
                    <xs:element name="TaxCodeId" type="xs:unsignedByte" />
                    <xs:element name="TaxCode" type="xs:string" />
                    <xs:element name="ShippableId" type="xs:unsignedByte" />
                    <xs:element name="Shippable" type="xs:string" />
                    <xs:element name="WarehouseId" type="xs:unsignedByte" />
                    <xs:element name="Warehouse" type="xs:string" />
                    <xs:element name="InventoryModeId" type="xs:unsignedByte" />
                    <xs:element name="InStock" type="xs:unsignedByte" />
                    <xs:element name="InStockWarningLevel" type="xs:unsignedByte" />
                    <xs:element name="ThumbnailUrl" type="xs:string" />
                    <xs:element name="ThumbnailAltText" type="xs:string" />
                    <xs:element name="ImageUrl" type="xs:string" />
                    <xs:element name="ImageAltText" type="xs:string" />
                    <xs:element name="Summary" />
                    <xs:element name="Description" type="xs:string" />
                    <xs:element name="VendorId" type="xs:unsignedByte" />
                    <xs:element name="CreatedDate" type="xs:dateTime" />
                    <xs:element name="LastModifiedDate" type="xs:dateTime" />
                    <xs:element name="ProductTemplateId" type="xs:unsignedByte" />
                    <xs:element name="IsFeatured" type="xs:boolean" />
                    <xs:element name="IsProhibited" type="xs:boolean" />
                    <xs:element name="AllowReviews" type="xs:boolean" />
                    <xs:element name="AllowBackorder" type="xs:boolean" />
                    <xs:element name="WrapGroupId" type="xs:unsignedByte" />
                    <xs:element name="ExcludeFromFeed" type="xs:boolean" />
                    <xs:element name="DisablePurchase" type="xs:boolean" />
                    <xs:element name="MinQuantity" type="xs:unsignedByte" />
                    <xs:element name="MaxQuantity" type="xs:unsignedByte" />
                    <xs:element name="VisibilityId" type="xs:unsignedByte" />
                    <xs:element name="Theme" />
                    <xs:element name="IconUrl" />
                    <xs:element name="IconAltText" type="xs:string" />
                    <xs:element name="IsGiftCertificate" type="xs:boolean" />
                    <xs:element name="CouponProduct" />
                    <xs:element name="ExtendedDescription" />
                    <xs:element name="HtmlHead" type="xs:string" />
                    <xs:element name="UseVariablePrice" type="xs:boolean" />
                    <xs:element name="MinimumPrice" type="xs:unsignedByte" />
                    <xs:element name="MaximumPrice" type="xs:unsignedByte" />
                    <xs:element name="SearchKeywords" />
                    <xs:element name="HidePrice" type="xs:boolean" />
                    <xs:element name="Assets" />
                    <xs:element name="CustomFields" />
                    <xs:element name="DigitalGoods" />
                    <xs:element name="Images" />
                    <xs:element name="ProductOptions">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="ProductOption">
                            <xs:complexType>
                              <xs:sequence>
                                <xs:element name="ProductId" type="xs:unsignedShort" />
                                <xs:element name="OptionId" type="xs:unsignedShort" />
                                <xs:element name="OrderBy" type="xs:unsignedByte" />
                                <xs:element name="Option">
                                  <xs:complexType>
                                    <xs:sequence>
                                      <xs:element name="OptionId" type="xs:unsignedShort" />
                                      <xs:element name="Name" type="xs:string" />
                                      <xs:element name="ShowThumbnails" type="xs:boolean" />
                                      <xs:element name="ThumbnailColumns" type="xs:unsignedByte" />
                                      <xs:element name="ThumbnailWidth" type="xs:unsignedByte" />
                                      <xs:element name="ThumbnailHeight" type="xs:unsignedByte" />
                                      <xs:element name="CreatedDate" type="xs:dateTime" />
                                      <xs:element name="Choices">
                                        <xs:complexType>
                                          <xs:sequence>
                                            <xs:element maxOccurs="unbounded" name="Choice">
                                              <xs:complexType>
                                                <xs:sequence>
                                                  <xs:element name="OptionChoiceId" type="xs:unsignedShort" />
                                                  <xs:element name="OptionId" type="xs:unsignedShort" />
                                                  <xs:element name="Name" type="xs:string" />
                                                  <xs:element name="ThumbnailUrl" />
                                                  <xs:element name="ImageUrl" />
                                                  <xs:element name="PriceModifier" type="xs:unsignedByte" />
                                                  <xs:element name="CogsModifier" type="xs:unsignedByte" />
                                                  <xs:element name="WeightModifier" type="xs:unsignedByte" />
                                                  <xs:element name="SkuModifier" type="xs:string" />
                                                  <xs:element name="OrderBy" type="xs:unsignedByte" />
                                                </xs:sequence>
                                              </xs:complexType>
                                            </xs:element>
                                          </xs:sequence>
                                        </xs:complexType>
                                      </xs:element>
                                    </xs:sequence>
                                  </xs:complexType>
                                </xs:element>
                              </xs:sequence>
                            </xs:complexType>
                          </xs:element>
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                    <xs:element name="ProductVolumeDiscounts" />
                    <xs:element name="Reviews" />
                    <xs:element name="TemplateFields" />
                    <xs:element name="Variants" />
                    <xs:element name="Specials" />
                    <xs:element name="Categories">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="Category">
                            <xs:complexType>
                              <xs:sequence>
                                <xs:element name="CategoryId" type="xs:unsignedShort" />
                                <xs:element name="Path" type="xs:string" />
                                <xs:element name="OrderBy" type="xs:byte" />
                              </xs:sequence>
                            </xs:complexType>
                          </xs:element>
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xsd:schema>