Greetings!
I'm trying to use the SQLXML BulkLoad COM object from a .net multithreaded
application... I'm currently getting an error when I try to use this object
saying:
System.InvalidCastException: Unable to cast COM object of type
'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type
'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the
QueryInterface call on the COM component for the interface with IID
'{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error:
No such interface supported (Exception from HRESULT: 0x80004002
(E_NOINTERFACE)).
at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.set_ConnectionString(String
pbstrConnectionString)
at Olympus.Integrate.Client.SqlServerClient.SendMessage(String message)
in C:\Projects\prototype\Integrate\SqlServe
rClient\SqlServerClient.vb:line
62
at Olympus.Integrate.Core.Core.MonitorFlow(Object flowObj) in
C:\Projects\prototype\Integrate\Integrat
e\Core.vb:line 152
Now I'm guessing that this COM object does support multiple threads.
So the question is how to get around this. Would wrapping this function in a
singleton, for example, do the trick? Or maybe having a static function in
an object?
What would be the best approach?
thanks for your time.
Daniel."Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:OC2INWwwHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Greetings!
> I'm trying to use the SQLXML BulkLoad COM object from a .net multithreaded
> application... I'm currently getting an error when I try to use this
> object saying:
> System.InvalidCastException: Unable to cast COM object of type
> 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type
> 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the
> QueryInterface call on the COM component for the interface with IID
> '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following
> error: No such interface supported (Exception from HRESULT: 0x80004002
> (E_NOINTERFACE)).
> at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.set_ConnectionString(String
> pbstrConnectionString)
> at Olympus.Integrate.Client.SqlServerClient.SendMessage(String message)
> in C:\Projects\prototype\Integrate\SqlServe
rClient\SqlServerClient.vb:line
> 62
> at Olympus.Integrate.Core.Core.MonitorFlow(Object flowObj) in
> C:\Projects\prototype\Integrate\Integrat
e\Core.vb:line 152
>
> Now I'm guessing that this COM object does support multiple threads.
> So the question is how to get around this. Would wrapping this function in
> a singleton, for example, do the trick? Or maybe having a static function
> in an object?
> What would be the best approach?
> thanks for your time.
> Daniel.
Make sure your thread is initilaize to enter a STA before starting (see
SetApartmentState(ApartmentState.STA) in System.Threading).
Willy.
>|||Willy,
Thanks for your reply.
In my application I dynamically create multiple threads according to some
configuration. At this point in the application the knowledge doesn't exist
as to whether the thread that is created will use the COM object... Is it
recommended that I just mark each thread as STA?
For Each myObj As MyObjectType In myObjects
' launch a seperate thread for each configured message flow
Dim t As Thread = New Thread(AddressOf MyThreadStartFunction)
t.Name = myObj.myName
t.SetApartmentState(ApartmentState.STA) // <-- ' Is
this okay '
MyThreadHandler.Instance.AddThread(t)
t.Start(myParameter)
Next
Cheers.
Dan.|||"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:e1UxaY5wHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Willy,
> Thanks for your reply.
> In my application I dynamically create multiple threads according to some
> configuration. At this point in the application the knowledge doesn't
> exist as to whether the thread that is created will use the COM object...
> Is it recommended that I just mark each thread as STA?
> For Each myObj As MyObjectType In myObjects
> ' launch a seperate thread for each configured message flow
> Dim t As Thread = New Thread(AddressOf MyThreadStartFunction)
> t.Name = myObj.myName
> t.SetApartmentState(ApartmentState.STA) // <-- ' Is
> this okay '
> MyThreadHandler.Instance.AddThread(t)
> t.Start(myParameter)
> Next
> Cheers.
> Dan.
>
Dan, The apartment state is a COM only thing, it doesn't affect .NET. An STA
apartment is valid for single and apartment threaded COM object types, so in
your case it is safe to initialize your threads to enter an STA .
Willy.|||"Willy Denoyette [MVP]" <willy.denoyette@.telenet.be> wrote in message
news:uj0Wdq7wHHA.4592@.TK2MSFTNGP05.phx.gbl...
> "Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
> news:e1UxaY5wHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan, The apartment state is a COM only thing, it doesn't affect .NET. An
> STA apartment is valid for single and apartment threaded COM object types,
> so in your case it is safe to initialize your threads to enter an STA .
> Willy.
>
Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
like a bogus message. My process spends a lot of time looping around in
other parts of the code while polling which suggests the reason for this
error coming up.
Thanks for your help.
Dan.|||"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:OiEYjv7wHHA.3444@.TK2MSFTNGP05.phx.gbl...
> "Willy Denoyette [MVP]" <willy.denoyette@.telenet.be> wrote in message
> news:uj0Wdq7wHHA.4592@.TK2MSFTNGP05.phx.gbl...
> Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
> like a bogus message. My process spends a lot of time looping around in
> other parts of the code while polling which suggests the reason for this
> error coming up.
> Thanks for your help.
> Dan.
>
This is not necessarily bogus message, don't take this too lightly. One
possible reason for the MDA is that your thread fails to pump the message
queue, if that's the case you will have problems whenever the finalizer
needs to release the COM object references you are holding in this thread,
this will block the finalizer thread with as a result that finalizable
objects stay in memory, finally leading into OOM and other kind of failures.
Willy.|||>> Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
> This is not necessarily bogus message, don't take this too lightly. One
> possible reason for the MDA is that your thread fails to pump the message
> queue, if that's the case you will have problems whenever the finalizer
> needs to release the COM object references you are holding in this thread,
> this will block the finalizer thread with as a result that finalizable
> objects stay in memory, finally leading into OOM and other kind of
> failures.
> Willy.
>
I've kept the process running, keeping an eye on memory and handles, and it
appears to destory and release the COM object correctly as there's no
increase in the resources used.
How would I check whether I'm "failing to pump the message queue"? This is
not a GUI app, but a backend service. Is there any way in .Net to check the
COM reference count?
I do explicitly disgard the object after creating and using...
' clear up
System.Runtime.InteropServices.Marshal.ReleaseComObject(bulkLoadComObject)
bulkLoadComObject = Nothing
Thanks.
Daniel.
Showing posts with label sqlxml. Show all posts
Showing posts with label sqlxml. Show all posts
Sunday, March 11, 2012
COM ADO and SQLXML v3
Hi,
I'm in the process of updating an HTA hosted application to SQL Server 2005 Express, using javascript with ADO for database access. I have one function to which I pass an SQLXML query template or updategram for all database queries and updates:
function doSql(sXml) {
var cmd = new ActiveXObject('ADODB.Command');
var conn = new ActiveXObject('ADODB.Connection');
var strmIn = new ActiveXObject('ADODB.Stream');
var strmOut = new ActiveXObject('ADODB.Stream');
var xml = new ActiveXObject(sDOM);
xml.async = false;
try {
conn.Provider = "SQLOLEDB";
conn.Open("Provider=SQLOLEDB.1;Persist Security Info=True;"+
"Initial Catalog=CGIS;Server=(local)\\ocean;Integrated Security=SSPI;");
conn.Properties("SQLXML Version") = "SQLXML.3.0";
cmd.ActiveConnection = conn;
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}";
strmIn.Open();
strmIn.WriteText(sXml);
strmIn.Position = 0;
cmd.CommandStream = strmIn;
strmOut.Open();
cmd.Properties("Output Stream").Value = strmOut;
cmd.Properties("Output Encoding").Value = "UTF-16";
var iCount;
cmd.Execute(iCount, null, 0x400);
conn.Close();
xml.load(strmOut);
return xml;
} catch(e) { alert('A database error occured: \n'+e.description+'\n\nQuery:\n'+sXml); }
I understand that SQLServer 2005 has SQLXML built in, which can be accessed using ADO.Net. Can it also be accessed using COM ADO? When I changed the connection string to this:
conn.Open("Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=\\.\pipe\SQLLocal\SQLEXPRESS");
I get this error:
'A database error occured:
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].'
Sounds like a connection string error however this was the connection string I pulled out of a .udl file that connected successfully.
Any help/suggestions greatly appreciated!
Andrew
Try changing the provider to see if that's it. My bet is that it is something to do with the network connection settings, so look in Books Online in the Database Engine section to learn more about the settings and various issues they have. You can also check out this article:
http://support.microsoft.com/default.aspx/kb/914277
Buck Woody
COM ADO and SQLXML v3
Hi,
I'm in the process of updating an HTA hosted application to SQL Server 2005 Express, using javascript with ADO for database access. I have one function to which I pass an SQLXML query template or updategram for all database queries and updates:
function doSql(sXml) {
var cmd = new ActiveXObject('ADODB.Command');
var conn = new ActiveXObject('ADODB.Connection');
var strmIn = new ActiveXObject('ADODB.Stream');
var strmOut = new ActiveXObject('ADODB.Stream');
var xml = new ActiveXObject(sDOM);
xml.async = false;
try {
conn.Provider = "SQLOLEDB";
conn.Open("Provider=SQLOLEDB.1;Persist Security Info=True;"+
"Initial Catalog=CGIS;Server=(local)\\ocean;Integrated Security=SSPI;");
conn.Properties("SQLXML Version") = "SQLXML.3.0";
cmd.ActiveConnection = conn;
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}";
strmIn.Open();
strmIn.WriteText(sXml);
strmIn.Position = 0;
cmd.CommandStream = strmIn;
strmOut.Open();
cmd.Properties("Output Stream").Value = strmOut;
cmd.Properties("Output Encoding").Value = "UTF-16";
var iCount;
cmd.Execute(iCount, null, 0x400);
conn.Close();
xml.load(strmOut);
return xml;
} catch(e) { alert('A database error occured: \n'+e.description+'\n\nQuery:\n'+sXml); }
I understand that SQLServer 2005 has SQLXML built in, which can be accessed using ADO.Net. Can it also be accessed using COM ADO? When I changed the connection string to this:
conn.Open("Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=\\.\pipe\SQLLocal\SQLEXPRESS");
I get this error:
'A database error occured:
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].'
Sounds like a connection string error however this was the connection string I pulled out of a .udl file that connected successfully.
Any help/suggestions greatly appreciated!
Andrew
Try changing the provider to see if that's it. My bet is that it is something to do with the network connection settings, so look in Books Online in the Database Engine section to learn more about the settings and various issues they have. You can also check out this article:
http://support.microsoft.com/default.aspx/kb/914277
Buck Woody
Subscribe to:
Posts (Atom)