ADODB.Command error 800a0cb3

I have posted various questions on the microsoft ng trying to identify the cause of this error.

ADODB.Command error '800a0cb3'
Object or provider is not capable of performing requested operation.

I have MDAC 2.8 installed locally on my machine.

Via IIS I created a virtual directory via IIS that points to my ASP files on c:

Via the SQL Server IIS for XML configuration utility I created a virtual directory with a different names that points to the same directory as that created via IIS.

The SQL database is on a different machine and I connect via the OLEDB DSNless connection string.

I used a ADODB.Stream to transform the XML against the XSL but I couldnt get it to work. To simplify things and work towards a solution I inserted the code into my ASP from the MS KB article Q272266 (see below). I amended the ms code to change the connection code and call a stored procedure that exists on the database. The ms code gives the same error as my original code.

I tried changing the CursorLocation to server and client but the results were the same.

I put a SQL trace on the DB to determine if the stored procedure gets ran, it does not.

If I run the following in the URL it works:

If I run http://localhost/p2/?sql=SELECT+*+FROM+tblStatus+FOR+XML+AUTO&root=root&xsl=tblStatusDesc.xsl it works.
If I run the xml template it works: http://localhost/p2/xml/test.xml

The two lines above run. My IIS server uses a virtual directory called dev, so when I run the ASP I type http://localhost/DEV/secure/aframes.asp the IIS virtual directory creted by sql server is called p2 but has the same source code directory.

Here is the MS code amended as described above that does not work.

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblStatus for XML Auto</sql:query></ROOT>"
'*************************************************

Dim txtResults ' String for results
dim CmdStream ' as ADODB.Stream


sConn = "Provider=SQLOLEDB;Data Source=[name of sql server];UId=sa; Pwd=xxxxx; Initial Catalog=[DB Name]"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")

adoConn.ConnectionString = sConn
adoConn.Open

Set adoCmd = CreateObject("ADODB.Command")
set adoCmd.ActiveConnection = adoConn

adoConn.CursorLocation = adUseClient

Set adoCmd.ActiveConnection = adoConn

adoStreamQuery.Open ' Open the command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input command stream's text with the query string
adoStreamQuery.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS

Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query.
Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream
outStrm.Open
adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened
adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.

Response.Endtry this type of connection string

driver={SQL Server};server=ServerName;uid=sa;pwd=xxxx;Database =DBName|||Thanks for your response.

I think this is progress but it seems to have broken the command object and it doesn't do the execute, it fails on the line:

adoCmd.Properties("Output Stream") = response

and gives an error of:

Item cannot be found in the collection corresponding to the requested name or ordinal.|||the maybe try this provider woth your first connectionstring

Provider=SQLOLEDB.1;|||No, sorry, this gives the error;

Object or provider is not capable of performing requested operation.

On the line with the execute statement.

What I am thinking of doing is to install sql server locally and have the db on my c: and try again, perhaps it is something to do with win 2k permissions and accessing the sql server across the network. Or perhaps its cause we use host headers on the web server?

Code now:

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblcStatusDesc for XML Auto</sql:query></ROOT>"

'sql = "<dataroot xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
'sql = sql & "<sql:header><sql:param name='number'>" & memberNo & "</sql:param></sql:header>" & sqlQuery & "</dataroot>"

'dim sQuery
'sQuery = sql
dim sXSLfile
sXSLfile = strXSL

'sConn = "Provider=SQLOLEDB;Data Source=LON09WEB;UId=sa; Pwd=develop; Initial Catalog=NMRA_INETSOURCE"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")
set objError = CreateObject("ADODB.Error")
Set adoCmd = CreateObject("ADODB.Command")

sConn = "Provider=SQLOLEDB.1;Data Source=LON09WEB;UId=sa; Pwd=develop; Initial Catalog=NMRA_INETSOURCE"
'sConn="Provider=SQLOLEDB.1;driver={SQL Server};server=LON09WEB;uid=sa;pwd=develop;Databas e=NMRA_INETSOURCE"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")

adoConn.ConnectionString = sConn
adoConn.Open

Set adoCmd = CreateObject("ADODB.Command")
set adoCmd.ActiveConnection = adoConn

adoConn.CursorLocation = adUseClient

Set adoCmd.ActiveConnection = adoConn

adoStreamQuery.Open ' Open the command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input command stream's text with the query string
adoStreamQuery.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS

Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query.
Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream
outStrm.Open
adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened
adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.

Set adoConn = NOTHING
Set adoStreamQuery = NOTHING
set objError = NOTHING
Set outStrm = NOTHING|||Why are you using the Stream property ?|||I'm not really too sure. I've been copying code samples from different places and ended up with the stream property in the code. My aim is to transform the xml against xsl. I've managed to get the xml bit working all I need now is to add the xsl file name.

Dim oCmd, sSQL

Set adoConn = CreateObject("ADODB.Connection")

sConn = "Provider=[Server];Data Source=xxx;UId=xxx; Pwd=xxx; Initial Catalog=xxx"

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = sConn

oCmd.CommandText = sQuery
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("base") =
oCmd.Properties("xsl") = "tblcStatusDesc.xsl"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024|||I could give you VB code to use your db

> open/close connections
> open/commit/rollback transactions
> select data
> execute command queries

I going home soon

I'll be back only on monday (loooong WE comming up !)
if this interestes you, mail me monday|||Thanks for your help, have a great weekend.

ado "sql authentication" connections affected by loss of domain controller

I have observed that a temporary loss of a domain controller can cause
problems creating new ado connections between a client machine running
ado and a separate sql server machine that are members of the domain.
I understand why this happens when creating connections with windows
authentication. What is a mystery is that it also sometimes effects
new connections that use "sql authentication". Below is a description
of my test scenario.
a. Setup 3 machines.
i. one domain controller machine. windows 2000 based. I have tried
both a regular domain setup and a domain setup in compatibility mode.
ii. one sql server machine that is a member of the domain. windows
2000.
iii. one client machine running an ado test program that communicates
with the sql server machine. I have tried both 2000 and xp.
b. Start your test ado program and create a connection.
c. Fire a query.
d. It should work.
e. Unplug the network cord on the domain controller.
f. Create a new connection and fire a new connection about a minute or
so after.
g. It should work. Apparently the client caches account information
from the domain controller for a certain amount of time. The time
seems to be shorter by default in xp than 2000.
h. Wait 20 minutes or more.
i. Create another new connection. You will notice a timeout error.
If your using windows authentication the timeout will happen 100% of
the time. That is to be expected. If your using sql authentication
the timeout seems to happen about 50% of the time. I can't explain it
other than some strange Microsoft bug. I speculate that it may be
some bug with the way ado caches connections. Perhaps a previously
setup windows authentication connection gets reused by a request for a
sql authentication connection. However I haven't been able to prove
it.
Any insight you can offer would be appreciated. I also have a test
program you can use to reproduce this behavior if you are interested.
Thanks,
Frank"Frank" <frank@.policecentral.com> wrote in message
news:c148e27c.0401120604.7d0b3c0f@.posting.google.com...
> I have observed that a temporary loss of a domain controller can cause
> problems creating new ado connections between a client machine running
> ado and a separate sql server machine that are members of the domain.
> I understand why this happens when creating connections with windows
> authentication. What is a mystery is that it also sometimes effects
> new connections that use "sql authentication". Below is a description
> of my test scenario.
> a. Setup 3 machines.
> i. one domain controller machine. windows 2000 based. I have tried
> both a regular domain setup and a domain setup in compatibility mode.
> ii. one sql server machine that is a member of the domain. windows
> 2000.
> iii. one client machine running an ado test program that communicates
> with the sql server machine. I have tried both 2000 and xp.
> b. Start your test ado program and create a connection.
> c. Fire a query.
> d. It should work.
> e. Unplug the network cord on the domain controller.
> f. Create a new connection and fire a new connection about a minute or
> so after.
> g. It should work. Apparently the client caches account information
> from the domain controller for a certain amount of time. The time
> seems to be shorter by default in xp than 2000.
> h. Wait 20 minutes or more.
> i. Create another new connection. You will notice a timeout error.
> If your using windows authentication the timeout will happen 100% of
> the time. That is to be expected. If your using sql authentication
> the timeout seems to happen about 50% of the time. I can't explain it
> other than some strange Microsoft bug. I speculate that it may be
> some bug with the way ado caches connections. Perhaps a previously
> setup windows authentication connection gets reused by a request for a
> sql authentication connection. However I haven't been able to prove
> it.
> Any insight you can offer would be appreciated. I also have a test
> program you can use to reproduce this behavior if you are interested.
> Thanks,
> Frank
This is a complete guess, and may be an overly obvious question, but could
the 50% failure rate be due to unsuccessful name resolution, and not an
MSSQL login issue? So in the cases where the SQL authenticated connection
fails, can you still resolve the name of the SQL server? Certainly in
theory, losing the domain controller shouldn't affect SQL logins, provided
that the client can actually find the server to begin with.
Simon|||Which network library are you using to establish the
connection? Named pipes will require an underlying
NetBIOS connection to the SQL Server, which may result in
the "cache-ing" behavior you are seeing. NetBIOS
connections will require NT user account authentication
and the use of a domain controller.
If you refer to the SQL Server by IP Address in your
connectino string, you can force the client to connect
using TCP/IP which does not require an underlying Named
Pipe connection.
I don't know if this will help, but it is one possible
explanation.
Matthew Bando
BandoM@.CSCTGI(remove this).com
>--Original Message--
>I have observed that a temporary loss of a domain
controller can cause
>problems creating new ado connections between a client
machine running
>ado and a separate sql server machine that are members
of the domain.
>I understand why this happens when creating connections
with windows
>authentication. What is a mystery is that it also
sometimes effects
>new connections that use "sql authentication". Below is
a description
>of my test scenario.
>a. Setup 3 machines.
> i. one domain controller machine. windows 2000
based. I have tried
>both a regular domain setup and a domain setup in
compatibility mode.
> ii. one sql server machine that is a member of
the domain. windows
>2000.
> iii. one client machine running an ado test
program that communicates
>with the sql server machine. I have tried both 2000 and
xp.
>b. Start your test ado program and create a connection.
>c. Fire a query.
>d. It should work.
>e. Unplug the network cord on the domain controller.
>f. Create a new connection and fire a new connection
about a minute or
>so after.
>g. It should work. Apparently the client caches account
information
>from the domain controller for a certain amount of
time. The time
>seems to be shorter by default in xp than 2000.
>h. Wait 20 minutes or more.
>i. Create another new connection. You will notice a
timeout error.
>If your using windows authentication the timeout will
happen 100% of
>the time. That is to be expected. If your using sql
authentication
>the timeout seems to happen about 50% of the time. I
can't explain it
>other than some strange Microsoft bug. I speculate that
it may be
>some bug with the way ado caches connections. Perhaps a
previously
>setup windows authentication connection gets reused by a
request for a
>sql authentication connection. However I haven't been
able to prove
>it.
>Any insight you can offer would be appreciated. I also
have a test
>program you can use to reproduce this behavior if you
are interested.
>Thanks,
>Frank
>.
>|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4002fd9c$1_1@.news.bluewin.ch>...
> "Frank" <frank@.policecentral.com> wrote in message
> news:c148e27c.0401120604.7d0b3c0f@.posting.google.com...
> > I have observed that a temporary loss of a domain controller can cause
> > problems creating new ado connections between a client machine running
> > ado and a separate sql server machine that are members of the domain.
> > I understand why this happens when creating connections with windows
> > authentication. What is a mystery is that it also sometimes effects
> > new connections that use "sql authentication". Below is a description
> > of my test scenario.
> >
> > a. Setup 3 machines.
> > i. one domain controller machine. windows 2000 based. I have tried
> > both a regular domain setup and a domain setup in compatibility mode.
> > ii. one sql server machine that is a member of the domain. windows
> > 2000.
> > iii. one client machine running an ado test program that communicates
> > with the sql server machine. I have tried both 2000 and xp.
> >
> > b. Start your test ado program and create a connection.
> > c. Fire a query.
> > d. It should work.
> > e. Unplug the network cord on the domain controller.
> > f. Create a new connection and fire a new connection about a minute or
> > so after.
> > g. It should work. Apparently the client caches account information
> > from the domain controller for a certain amount of time. The time
> > seems to be shorter by default in xp than 2000.
> > h. Wait 20 minutes or more.
> > i. Create another new connection. You will notice a timeout error.
> > If your using windows authentication the timeout will happen 100% of
> > the time. That is to be expected. If your using sql authentication
> > the timeout seems to happen about 50% of the time. I can't explain it
> > other than some strange Microsoft bug. I speculate that it may be
> > some bug with the way ado caches connections. Perhaps a previously
> > setup windows authentication connection gets reused by a request for a
> > sql authentication connection. However I haven't been able to prove
> > it.
> >
> > Any insight you can offer would be appreciated. I also have a test
> > program you can use to reproduce this behavior if you are interested.
> >
> > Thanks,
> > Frank
> This is a complete guess, and may be an overly obvious question, but could
> the 50% failure rate be due to unsuccessful name resolution, and not an
> MSSQL login issue? So in the cases where the SQL authenticated connection
> fails, can you still resolve the name of the SQL server? Certainly in
> theory, losing the domain controller shouldn't affect SQL logins, provided
> that the client can actually find the server to begin with.
> Simon
I thought of that and tried doing a ping by name after it failed. It
worked ok. However given the 50% nature of the problem I still don't
totally rule it out.|||I considered that possibility. I did bring up the "Client Network
Utility" on the client and made tcp/ip the only choice. The behavior
did not change. However I am referencing the machine by name and not
ip. I will try the ip technique and report back.
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message news:<062d01c3d9ec$f7e783c0$a001280a@.phx.gbl>...
> Which network library are you using to establish the
> connection? Named pipes will require an underlying
> NetBIOS connection to the SQL Server, which may result in
> the "cache-ing" behavior you are seeing. NetBIOS
> connections will require NT user account authentication
> and the use of a domain controller.
> If you refer to the SQL Server by IP Address in your
> connectino string, you can force the client to connect
> using TCP/IP which does not require an underlying Named
> Pipe connection.
> I don't know if this will help, but it is one possible
> explanation.
> Matthew Bando
> BandoM@.CSCTGI(remove this).com
>
> >--Original Message--
> >I have observed that a temporary loss of a domain
> controller can cause
> >problems creating new ado connections between a client
> machine running
> >ado and a separate sql server machine that are members
> of the domain.
> >I understand why this happens when creating connections
> with windows
> >authentication. What is a mystery is that it also
> sometimes effects
> >new connections that use "sql authentication". Below is
> a description
> >of my test scenario.
> >
> >a. Setup 3 machines.
> > i. one domain controller machine. windows 2000
> based. I have tried
> >both a regular domain setup and a domain setup in
> compatibility mode.
> > ii. one sql server machine that is a member of
> the domain. windows
> >2000.
> > iii. one client machine running an ado test
> program that communicates
> >with the sql server machine. I have tried both 2000 and
> xp.
> >
> >b. Start your test ado program and create a connection.
> >c. Fire a query.
> >d. It should work.
> >e. Unplug the network cord on the domain controller.
> >f. Create a new connection and fire a new connection
> about a minute or
> >so after.
> >g. It should work. Apparently the client caches account
> information
> >from the domain controller for a certain amount of
> time. The time
> >seems to be shorter by default in xp than 2000.
> >h. Wait 20 minutes or more.
> >i. Create another new connection. You will notice a
> timeout error.
> >If your using windows authentication the timeout will
> happen 100% of
> >the time. That is to be expected. If your using sql
> authentication
> >the timeout seems to happen about 50% of the time. I
> can't explain it
> >other than some strange Microsoft bug. I speculate that
> it may be
> >some bug with the way ado caches connections. Perhaps a
> previously
> >setup windows authentication connection gets reused by a
> request for a
> >sql authentication connection. However I haven't been
> able to prove
> >it.
> >
> >Any insight you can offer would be appreciated. I also
> have a test
> >program you can use to reproduce this behavior if you
> are interested.
> >
> >Thanks,
> >Frank
> >.
> >|||Thanks for the help. The mystery has been solved. I ended up opening
a ms support incident and they helped me figure it out. For those
that guessed dns server that was the right idea. However what made it
difficult to pin down is the following.
A. I tried as one person suggested connecting by ip address. I
thought this would eliminate dns issues from the equation. However as
the ms support guy informed me ado does a gethostbyaddr early in the
connection if you pass in an ip address. Therefore if the computer
can't do that your out of business.
B. I also thought that dns was not a factor because I could ping by
name after a connection failure and it would work. However the reason
it worked is because all the machines were on the same subnet and the
broadcast was what made it work. Once the dns server drops out the
first ping response was very slow. if you would do a ping immediately
after the response the 2nd ping would be fast. That behavior was what
was causing the randomness of the connection working. The first time
the connection would time out. However the attempt woke things up and
the second attempt would succeed.
Now that I know this I can route the important machines to a more
isolated and protected dns server or manually edit the hosts files to
eliminate the domain controller / dns controller that I can't control
from the equation.
frank@.policecentral.com (Frank) wrote in message news:<c148e27c.0401131411.32f26332@.posting.google.com>...
> I considered that possibility. I did bring up the "Client Network
> Utility" on the client and made tcp/ip the only choice. The behavior
> did not change. However I am referencing the machine by name and not
> ip. I will try the ip technique and report back.
> "Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message news:<062d01c3d9ec$f7e783c0$a001280a@.phx.gbl>...
> > Which network library are you using to establish the
> > connection? Named pipes will require an underlying
> > NetBIOS connection to the SQL Server, which may result in
> > the "cache-ing" behavior you are seeing. NetBIOS
> > connections will require NT user account authentication
> > and the use of a domain controller.
> >
> > If you refer to the SQL Server by IP Address in your
> > connectino string, you can force the client to connect
> > using TCP/IP which does not require an underlying Named
> > Pipe connection.
> >
> > I don't know if this will help, but it is one possible
> > explanation.
> >
> > Matthew Bando
> > BandoM@.CSCTGI(remove this).com
> >
> >
> >
> > >--Original Message--
> > >I have observed that a temporary loss of a domain
> controller can cause
> > >problems creating new ado connections between a client
> machine running
> > >ado and a separate sql server machine that are members
> of the domain.
> > >I understand why this happens when creating connections
> with windows
> > >authentication. What is a mystery is that it also
> sometimes effects
> > >new connections that use "sql authentication". Below is
> a description
> > >of my test scenario.
> > >
> > >a. Setup 3 machines.
> > > i. one domain controller machine. windows 2000
> based. I have tried
> > >both a regular domain setup and a domain setup in
> compatibility mode.
> > > ii. one sql server machine that is a member of
> the domain. windows
> > >2000.
> > > iii. one client machine running an ado test
> program that communicates
> > >with the sql server machine. I have tried both 2000 and
> xp.
> > >
> > >b. Start your test ado program and create a connection.
> > >c. Fire a query.
> > >d. It should work.
> > >e. Unplug the network cord on the domain controller.
> > >f. Create a new connection and fire a new connection
> about a minute or
> > >so after.
> > >g. It should work. Apparently the client caches account
> information
> > >from the domain controller for a certain amount of
> time. The time
> > >seems to be shorter by default in xp than 2000.
> > >h. Wait 20 minutes or more.
> > >i. Create another new connection. You will notice a
> timeout error.
> > >If your using windows authentication the timeout will
> happen 100% of
> > >the time. That is to be expected. If your using sql
> authentication
> > >the timeout seems to happen about 50% of the time. I
> can't explain it
> > >other than some strange Microsoft bug. I speculate that
> it may be
> > >some bug with the way ado caches connections. Perhaps a
> previously
> > >setup windows authentication connection gets reused by a
> request for a
> > >sql authentication connection. However I haven't been
> able to prove
> > >it.
> > >
> > >Any insight you can offer would be appreciated. I also
> have a test
> > >program you can use to reproduce this behavior if you
> are interested.
> > >
> > >Thanks,
> > >Frank
> > >.
> > >