August 4, 2004 at 5:03 pm
Environment: SQL 2K, collation =case sensitive accent sensitive, .NET 2002, SQLXML 3.0
I get an exception [Invalid object name 'information_schema.columns']
when I try to bulk load XML into SQL using SQLXML bulk load facility. My code looks like this:
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
objBL.ConnectionString = "Provider=sqloledb;server=server;database=databaseName;integrated security=SSPI";
objBL.ErrorLogFile = "error.xml";
objBL.Execute ("schema.xml","data.xml");
When my schema has NO relationship annotations, it works fine. As soon as I add the table relationtionship annotations I get the exception. The only thing I can think of is that the SQLXML is hard coded with 'information_schema.columns' and SQL is expecting 'INFORMATION_SCHEMA.COLUMNS'. I cannot change the collation of the SQL instance. Does anyone have a solution?
for more info on bulk loading XML see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp
August 10, 2004 at 8:41 am
Are you using SQLXML 3.0 SP2? We had problems until we installed SP2... you can get it here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=4c8033a9-cf10-4e22-8004-477098a407ac&DisplayLang=en
Hope that helps.
-Mike
May 2, 2005 at 2:35 am
1. Create a user "information_schema" in the database where you are loading the data.
2. In the create user dialogue box check all the properties in the property window.
3. Cut and paste this code in query analyzer window and run it.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Displays columns accessable to the current user
create view information_schema.columns
as
select
db_name() as table_catalog
,user_name(obj.uid) as table_schema
,obj.name as table_name
,col.name as column_name
,col.colid as ordinal_position ,com.text as column_default
,case col.isnullable
when 1 then 'YES'
else 'No '
end as is_nullable
,spt_dtp.LOCAL_TYPE_NAME as data_type
,convert(int,
OdbcPrec(col.xtype, col.length, col.xprec)
+ spt_dtp.charbin) as character_maximum_length
,convert(int, spt_dtp.charbin +
case when spt_dtp.LOCAL_TYPE_NAME in ('nchar', 'nvarchar', 'ntext')
then 2*OdbcPrec(col.xtype, col.length, col.xprec)
else OdbcPrec(col.xtype, col.length, col.xprec)
end) as character_octet_length
,nullif(col.xprec, 0) as numeric_precision
,spt_dtp.RADIX as numeric_precision_radix
,col.scale as numeric_scale
,spt_dtp.SQL_DATETIME_SUB as datetime_precision
,convert(sysname, NULL) as character_set_catalog ,convert(sysname, NULL) as character_set_schema
,convert(sysname, case
when spt_dtp.LOCAL_TYPE_NAME in
('char', 'varchar', 'text')
then a_cha.name
when spt_dtp.LOCAL_TYPE_NAME in
('nchar', 'nvarchar', 'ntext')
then N'Unicode'
else NULL
end) as character_set_name
,convert(sysname, NULL) as collation_catalog
,convert(sysname, NULL) as collation_schema
,col.collation as collation_name
,convert(sysname, case when typ.xusertype > 256
then DB_NAME()
else NULL
end) as domain_catalog
,convert(sysname, case when typ.xusertype > 256
then USER_NAME(obj.uid)
else NULL
end) as domain_schema ,convert(sysname, case when typ.xusertype > 256
then typ.name
else NULL
end) as domain_name
FROM
sysobjects obj,
master.dbo.spt_datatype_info spt_dtp,
systypes typ,
syscolumns col
LEFT OUTER JOIN syscomments com on col.cdefault = com.id
AND com.colid = 1,
master.dbo.syscharsets a_cha --charset/1001, not sortorder.
WHERE
permissions(obj.id, col.name) != 0
AND obj.id = col.id
AND typ.xtype = spt_dtp.ss_dtype
AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
AND obj.xtype in ('U', 'V')
AND col.xusertype = typ.xusertype
AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)
AND a_cha.id = isnull(convert(tinyint, CollationPropertyFromID(col.collationid, 'sqlcharset')),
convert(tinyint, ServerProperty('sqlcharset'))) -- make sure there's one and only one row selected for each column
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
4. Run SQLXML Bulkload again.
This loads perfectly ..
Srivalli.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy