Hello,
I've just taken over a project and there's an issue with null values in some fields.
The XML file is loaded into SQL Server 2005 with the SQLXMLBulkLoad
The XML file is about 60mb.
Anyone any ideas, the fields in particular that seem to be causing the issue are of type ntext and allow nulls.
thanks.
With my Sql server I install SQLxml3.0 and run this with problem.
DECLARE @ObjectError INT
, @Object INT
, @ErrMsg VARCHAR(50)
EXEC @ObjectError = sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload', @Object OUT,4I plan to go with the Sql server 2005 but this code give me a error...:eek: Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
My user right not change. And I dont see some "SQLXML" in the "Surface Area Configuration"???
Can someone can give my suggestion?
Hi,
Just curious, is there any alternative to SQLXMLBULKLOAD for shredding and loading very large (800 megs) XML files ? Due to the nature of the XML data sent to me (which I have no control over)I am having great difficulty loading data into tables. More specifically, I can load parent data but not the child data beneath it despite using sql:relationships.
Thanks,
Jeff
Hi,
I have a process running SQLXMLBulkload.3.0 against an XML data file which is completing without issue. I cloned the same process for another XML data file but am getting an error I can't seem to resolve. I've checked the working process vs. the one not working (vbscripts, xml data files, xml schema files,etc.) and see no differences that might cause this error. The error when I run SQLXMLBulkload.3.0 is this:
"Microsoft OLE DB Provider for SQL Server: All bound columns are read-only"
Has anyone run across this before. We are running under sql server 2000.
Thank you.
J.
Hi, Am I right in saying that by default, an element of complex typein an XSD annotated schema maps to the table with the same name in thespecified database. If so does anyone have any idea why my app runswith no errors, but doesn't add anything to the table in the DB. I washoping to find the value 'GDS-2392265' in the table.Any Ideas?Thanks in advance.Richvb.net code-----------------Dim objBLobjBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")objBL.ConnectionString = "provider=SQLOLEDB.1;data source=xxxSQLEXPRESS;database=xxx;uid=sa;pwd=xxxxx"objBL.ErrorLogFile = "c:error.log"objBL.KeepIdentity = FalseobjBL.Execute("c:SCHEMA.XML", "c:INPUT.XML")objBL = NothingMe.Enabled = TrueDB Table Def-------------------CREATE TABLE [dbo].[FeatureCollection]([fid] [nchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]Schema File------------------<?xml version="1.0" ?><xsd:schema xmlns="urn:schemas-microsoft-com:xml-data"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sql="urn:schemas-microsoft-com:mapping-schema"xmlns:osgb="http://www.ordnancesurvey.co.uk/xml/namespaces/osgb"xmlns:gml="http://www.opengis.net/gml"xmlns:xlink="http://www.w3.org/1999/xlink"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><xsd:element name="FeatureCollection"><xsd:complexType><xsd:attribute name="fid" type="xsd:string"/></xsd:complexType></xsd:element></xsd:schema>XML File------------<?xml version='1.0' encoding='UTF-8'?><osgb:FeatureCollectionxmlns:osgb='http://www.ordnancesurvey.co.uk/xml/namespaces/osgb'xmlns:gml='http://www.opengis.net/gml'xmlns:xlink='http://www.w3.org/1999/xlink'xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'xsi:schemaLocation='http://www.ordnancesurvey.co.uk/xml/namespaces/osgb http://www.ordnancesurvey.co.uk/xml/schema/v5/OSDNFFeatures.xsd'fid='GDS-2392265'></osgb:FeatureCollection>
View 1 Replies View RelatedHow to instanciate SQLXMLBulkLoad object in C#?
I have SQL Server 2000 installed and added Microsoft.Data.SqlXml reference to the project, but I can't find any SQLXMLBulkLoad object in it.
What have to be installed to have SQLXMLBulkLoad object?
i have attached XML and XSD file
i bulkload xml file into 2 tables .ManifestID is the Relationship between 2 tables
CREATE TABLE [dbo].[BIOS_DataLoader_LDR_Manifest_Detail] (
[manifest_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_num] [bigint] NULL ,
[track_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[box_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[box_type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[scan_time] [datetime] NULL ,
[scac_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BIOS_DataLoader_LDR_Manifest_Header] (
[trailer_id] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trailer_close_date] [datetime] NULL ,
[manifest_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[manifest_qty] [int] NULL ,
[origin_facility] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[destination_facility] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
When the first TrackCodeid is NULL
all remaining NULL TrackCodeid is entered as null in the database table
but if the first/prev trackcodeID is not NULL then following null trackCODEID is populated with the prev trackcodeID and not as null in database
<TrackCodeId>ABCDEFG</TrackCodeId>
All Null TrackCOdeID is populated as ABCDEFG
if we remove ABCDEFG and then bulkload all null values are populated
if a null trackCODEID is to be inserted the prev trackCODEID must be null and must not contain any value
Hi.
I'm using SQLXMLBulkLoad to insert data into the SQL database. It worked with the test database on my PC but when I changed it into a VPS it throws an error: Invalid Connection String. This connection string that I used worked in my program before the addition of 'provider = sqloledb'.
Here's the connection string:
@"Provider = sqloledb; Data Source=CRM;Initial Catalog=Works;Persist Security Info=True;User ID=userid;Password=password;Asynchronous Processing=True";
Anyone?
heres the error code:
-2147217887
I want to import the contents of a remote database every morning via a Web service to populate a data mart. I am looking for the most efficient way to do this. Step 1 is to use the Web Service Task in SSIS to grab the data and save to an XML file (since the content is far too large to store in the String data type).
For step 2, I can either use the SQLXMLBulkLoad object in VB Script to read in the file and populate the tables. Or I can using the XML Source object in Integration Services. It is clear that SQLXMLBulkLoad is the most efficient way to load data, but is the XML Source object in SSIS just a graphical representation of the SQLXMLBulkLoad object, or is it something else entirely that is inefficient because it requires loading of the XML content entirely into RAM before it can process the XML?
Any other suggestions are welcome.
SQL Server 2005 SP2.
Thank you,
Mike Chabot