MS SQL XML - Need help with SQLXMLBulkLoad 3.0 in .NET

Author

Need help with SQLXMLBulkLoad 3.0 in .NET

laimaj

2005-09-22, 3:23 am

Hi there, hope you can help.
I have a web service to bulk load data from xml to sql server - here’s
a code snippet:
--------------------------------------------------------------------
System.Threading.Thread.CurrentThread.ApartmentState =
System.Threading.ApartmentState.STA;
SQLXMLBULKLOADLib. SQLXMLBulkLoad3Class
objXBL = new
SQLXMLBULKLOADLib. SQLXMLBulkLoad3Class
();

objXBL.ConnectionString =
ConfigurationSetting
s.AppSettings["connString"];
string schemaFile = ConfigurationSetting
s.AppSettings["schemaFile"];

objXBL.BulkLoad = true;
objXBL.KeepIdentity = false;
objXBL.XMLFragment = true;
objXBL.Transaction = false;
objXBL.Execute(schemaFile, data); //Data is a System.IO.MemoryStream,
made of given xml
------------------------------------------------------------

I call this web service from external Windows Application. By saying
external I mean separate solution.

Everything works just fine when I add a reference to service’s dll and
call it like that:

-------------------------------------------------------------
[STAThread]
static void Main()
{
Application.Run(new Form1());
}

private void btnSubmit_Click(obje
ct sender, System.EventArgs e)
{
MyService.Service1 svrDataImport = new MyService.Service1();

svrDataImport.objUserInformation = new MyService.UserInformation();
svrDataImport.objUserInformation.UserName = "login";
svrDataImport.objUserInformation.Password = "pass";

string msg = svrDataImport. LoadDataString(txtDa
ta.Text);
txtResult.Text = msg;
}
-------------------------------------------------------

As I said, it works fine like that. But I need to make to work it by
adding NOT a reference to dll, but a Web reference. The code I use is
a little bit different in this case:

----------------------------------------------------------
[STAThread]
static void Main()
{
Application.Run(new Form1());
}

private void btnSubmit_Click(obje
ct sender, System.EventArgs e)
{
MyAppl.MyService.Service1 svrDataImport = new
MyAppl.MyService.Service1(); //MyService is a name of web reference

svrDataImport. UserInformationValue
= new
MyAppl.MyService.UserInformation();
svrDataImport. UserInformationValue
.UserName = "login";
svrDataImport. UserInformationValue
.Password = "pass";

string msg = svrDataImport. LoadDataString(txtDa
ta.Text);
txtResult.Text = msg;
}
----------------------------------------------------

The error occures on
objXBL.ConnectionString =
ConfigurationSetting
s.AppSettings["connString"];
in web service. Could anyone, please, advice me?

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/XML-help-SQ...pict257384.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=887902

Chandra Kalyanaraman [MSFT]

2005-09-22, 1:23 pm

Hi ,
To me it seems like this issue is not connected with Bulkload / SQLXML per
se.
Can you put a breakpoint in your web service on the following line :

objXBL.ConnectionString =
ConfigurationSetting
s.AppSettings["connString"];

and see what is the value of
" ConfigurationSetting
s.AppSettings["connString"];" ?

May be the AppSettings["connString"] is not valid when you add a
webreference instead of the regular assembly reference.

If that is correct,
do exactly same code, but replace

objXBL.ConnectionString = ConfigurationSetting
s.AppSettings["connString"];
with
objXBL.ConnectionString = "<actual connection string value that you get> "

and see if it works?

HTH,
Chandra

--

------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"laimaj" < UseLinkToEmail@dbFor
umz.com> wrote in message
news:4_887902_6e3471
9da1a6a7e134b5526e52
20f68b@dbforumz.com...
> Hi there, hope you can help.
> I have a web service to bulk load data from xml to sql server - here's
> a code snippet:
> --------------------------------------------------------------------
> System.Threading.Thread.CurrentThread.ApartmentState =
> System.Threading.ApartmentState.STA;
> SQLXMLBULKLOADLib. SQLXMLBulkLoad3Class
objXBL = new
> SQLXMLBULKLOADLib. SQLXMLBulkLoad3Class
();
>
> objXBL.ConnectionString =
> ConfigurationSetting
s.AppSettings["connString"];
> string schemaFile = ConfigurationSetting
s.AppSettings["schemaFile"];
>
> objXBL.BulkLoad = true;
> objXBL.KeepIdentity = false;
> objXBL.XMLFragment = true;
> objXBL.Transaction = false;
> objXBL.Execute(schemaFile, data); //Data is a System.IO.MemoryStream,
> made of given xml
> ------------------------------------------------------------
>
> I call this web service from external Windows Application. By saying
> external I mean separate solution.
>
> Everything works just fine when I add a reference to service's dll and
> call it like that:
>
> -------------------------------------------------------------
> [STAThread]
> static void Main()
> {
> Application.Run(new Form1());
> }
>
> private void btnSubmit_Click(obje
ct sender, System.EventArgs e)
> {
> MyService.Service1 svrDataImport = new MyService.Service1();
>
> svrDataImport.objUserInformation = new MyService.UserInformation();
> svrDataImport.objUserInformation.UserName = "login";
> svrDataImport.objUserInformation.Password = "pass";
>
> string msg = svrDataImport. LoadDataString(txtDa
ta.Text);
> txtResult.Text = msg;
> }
> -------------------------------------------------------
>
> As I said, it works fine like that. But I need to make to work it by
> adding NOT a reference to dll, but a Web reference. The code I use is
> a little bit different in this case:
>
> ----------------------------------------------------------
> [STAThread]
> static void Main()
> {
> Application.Run(new Form1());
> }
>
> private void btnSubmit_Click(obje
ct sender, System.EventArgs e)
> {
> MyAppl.MyService.Service1 svrDataImport = new
> MyAppl.MyService.Service1(); //MyService is a name of web reference
>
> svrDataImport. UserInformationValue
= new
> MyAppl.MyService.UserInformation();
> svrDataImport. UserInformationValue
.UserName = "login";
> svrDataImport. UserInformationValue
.Password = "pass";
>
> string msg = svrDataImport. LoadDataString(txtDa
ta.Text);
> txtResult.Text = msg;
> }
> ----------------------------------------------------
>
> The error occures on
> objXBL.ConnectionString =
> ConfigurationSetting
s.AppSettings["connString"];
> in web service. Could anyone, please, advice me?
>
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/XML-help-SQ...pict257384.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=887902

laimaj

2005-09-23, 7:23 am

Hello,

I entered:
objXBL.ConnectionString = " Provider=sqloledb;In
itial Catalog=dbtest;Data
Source=myserver;User
ID=dbsql;Password=te
stpasswd;";

It didn't solve my problem. Is this in any way could be related with the
security issues trying to connect to the server? I try to access this web
service fom another server. Well, I use this connection string in another
place in this same web service to retrieve data, and it works fine. I'm
confused.

"Chandra Kalyanaraman [MSFT]" wrote:

> Hi ,
> To me it seems like this issue is not connected with Bulkload / SQLXML per
> se.
> Can you put a breakpoint in your web service on the following line :
>
> objXBL.ConnectionString =
> ConfigurationSetting
s.AppSettings["connString"];
>
> and see what is the value of
> " ConfigurationSetting
s.AppSettings["connString"];" ?
>
> May be the AppSettings["connString"] is not valid when you add a
> webreference instead of the regular assembly reference.
>
> If that is correct,
> do exactly same code, but replace
>
> objXBL.ConnectionString = ConfigurationSetting
s.AppSettings["connString"];
> with
> objXBL.ConnectionString = "<actual connection string value that you get> "
>
> and see if it works?
>
> HTH,
> Chandra
>
> --
>
> ------------------------------------------------------
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
>
>
> "laimaj" < UseLinkToEmail@dbFor
umz.com> wrote in message
> news:4_887902_6e3471
9da1a6a7e134b5526e52
20f68b@dbforumz.com...
>
>
>

laimaj

2005-09-23, 7:23 am

Hello again,

It really seems like it's the problem with Bulkload/SQLXML, because the
error always occures on the first line where I try to set any of the objXBL
property, i.e. if I move objXBL.BulkLoad = true before
objXBL.ConnectionString = "", the error occures on objXBL.BulkLoad line.

I was thinking - could it be that there's something wrong with the
configuration? Do I have to do something extra besides installing SQLXML 3.0
to make Blukload work?

Thanks in advance for help.
"laimaj" wrote:
[color=darkred]
> Hello,
>
> I entered:
> objXBL.ConnectionString = " Provider=sqloledb;In
itial Catalog=dbtest;Data
> Source=myserver;User
ID=dbsql;Password=te
stpasswd;";
>
> It didn't solve my problem. Is this in any way could be related with the
> security issues trying to connect to the server? I try to access this web
> service fom another server. Well, I use this connection string in another
> place in this same web service to retrieve data, and it works fine. I'm
> confused.
>
>
> "Chandra Kalyanaraman [MSFT]" wrote:
>

laimaj

2005-09-28, 9:23 am

I've figured it out where is the problem, but don't know how to solve it. As
Bulkload works with STA thread mode, I use
bulkLoad = new Thread( new ThreadStart( MyBulkload ) );
bulkLoad.ApartmentState = ApartmentState.STA;
bulkLoad.Start();

When I debug and watch
System.Threading.Thread.CurrentThread.ApartmentState
I see that it's value is STA when I initialize objXBL.BulkLoad = true; and
couple of other objXBL settings, and right in the middle of bulkload code the
STA turns to MTA. It every time occures at different place in the code. So,
the bulkload catches the exception on the line, where STA turns to MTA. I've
never dealt with threads and have no idea what to do. Please, help!
Setting
System.Threading.Thread.CurrentThread.ApartmentState =
System.Threading.ApartmentState.STA;
doesn't help - it doesn't change
System.Threading.Thread.CurrentThread.ApartmentState - don't know why.

"laimaj" wrote:
[color=darkred]
> Hello again,
>
> It really seems like it's the problem with Bulkload/SQLXML, because the
> error always occures on the first line where I try to set any of the objXBL
> property, i.e. if I move objXBL.BulkLoad = true before
> objXBL.ConnectionString = "", the error occures on objXBL.BulkLoad line.
>
> I was thinking - could it be that there's something wrong with the
> configuration? Do I have to do something extra besides installing SQLXML 3.0
> to make Blukload work?
>
> Thanks in advance for help.
> "laimaj" wrote:
>

Sponsored Links