ADODB and Mirrored SQL-Server

I did setup a Mirrored Database. Connecting from it using ADO.NET works well. It goes to the Mirror if the Principal fails.

But ADODB does not work. I get the error following error:

80004005 Invalid connection string attribute

When trying to connect to the DB in case the principal failed and the mirror is active. (MyProductiveDB is in failover state)

What do I do wrong?

Here is the code:

ADOConn = New ADODB.Connection

ADOConn.Open(CS)

CS is my Connections-String:

"Provider=SQLNCLI.1;Data Source=MyProductiveDB;Failover Partner=MyMirror;Initial Catalog=MyCat;Persist Security Info=True;User ID=MyUser;Password=xxxxxx;Pooling=True;Connect Timeout=5;Application Name=MyApplic"

Remark: When I try to add "Network Library=dbmssocn" to the connection String, I get the same error, even if the Principal is active.

Your help is very much appreciated.

Beat

I contacted the client team and they pointed out that Pooling is not supported in SQLNCLI.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

Beat,

Do you mind if I ask for a sample of your ADO.NET code that works well if the principal fails?

Thank you very much.

|||?

ADODB and Mirrored SQL-Server

I did setup a Mirrored Database. Connecting from it using ADO.NET works well. It goes to the Mirror if the Principal fails.

But ADODB does not work. I get the error following error:

80004005 Invalid connection string attribute

When trying to connect to the DB in case the principal failed and the mirror is active. (MyProductiveDB is in failover state)

What do I do wrong?

Here is the code:

ADOConn = New ADODB.Connection

ADOConn.Open(CS)

CS is my Connections-String:

"Provider=SQLNCLI.1;Data Source=MyProductiveDB;Failover Partner=MyMirror;Initial Catalog=MyCat;Persist Security Info=True;User ID=MyUser;Password=xxxxxx;Pooling=True;Connect Timeout=5;Application Name=MyApplic"

Remark: When I try to add "Network Library=dbmssocn" to the connection String, I get the same error, even if the Principal is active.

Your help is very much appreciated.

Beat

I contacted the client team and they pointed out that Pooling is not supported in SQLNCLI.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

Beat,

Do you mind if I ask for a sample of your ADO.NET code that works well if the principal fails?

Thank you very much.

|||?

ADO.NET Transaction Fails to update Database after .Commit()

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives?? Anyone else ever heard of such a thing. Please help, before I loose my sanity.I may need to be more specific about the problem, so here goes. I am running an ADO.NET SQLTransaction that is calling 3 different stored procedures. The first proc Inserts data into a MailMessage Table and returns the @.@.IDENTITY of the Insert. The next set of stored procedures are called in a loop to Insert into a MailboxMessageAddressees table that has a foreign key of the MailMessages table and the ID of the Addressee. This loop can run 1-50+ times to insert all the Addressees. In SQL Profiler, I can trace the connection I made to a database and it starts the transaction, executes all stored procedures, and then COMMITS. I recieve no errors in my transaction and no errors occur on SQLServer. But the data is never inserted into the database. Its almost like the ADO.NET Transaction didn't take place, or SQL Server is rolling back the transaction on its own without throwing any errors that I can see.

Needless to say, I have closed the connection and disposed of the command and connection. Is there something else I need to send to SQL Server other than COMMIT to let it know that I have completed my transaction. This is really perplexing because I can manually execute all the stored procedures in Entrerprise Manager. I get every statement executed from the SQL Profiler when I trace the Transaction and put it in Query Analyzer and it works fine, and all the data shows up in SQL Server. Is there something I am missing?

Here is the actual code, maybe someone can clue me into what I might be missing.

/// <summary>
/// Mail_DAL.AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,Wargame_ID)
/// ->Executes a ExecuteNonQuery using the Stored Procedure Mail_DeleteGroupAddressees and an ExecuteNonQuery using stored
/// procedure Mail_AddGroupAddressee to insert all addressees not in the Group.
/// </summary>
/// <param name="Mailbox_ID">Int: The Mailbox associated with the MailGroup.</param>
/// <param name="subject">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="body">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="recipientTo">ArrayList: An ArrayList of the To addresses Mailbox_IDs for the group.</param>
/// <param name="recipientCc">ArrayList: An ArrayList of the CC addresses Mailbox_IDs for the group.</param>
/// <param name="groupTo">ArrayList: An ArrayList of the To group addresses Mailbox_IDs for the group.</param>
/// <param name="groupCc">ArrayList: An ArrayList of the CC group addresses Mailbox_IDs for the group.</param>
/// <param name="files">ArrayList: An ArrayList of Files if there are any.</param>
/// <param name="Wargame_ID">Int: The Wargame_ID of any file to be put in Files</param>
public void AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,int Wargame_ID)
{
//Get To Addresses from Groups
OpenProcCommand(Conn,"Mail_GetCurrentGroupAddressees");
SqlCommand cmd = ProcCommand;
param = new SqlParameter();
SqlDataReader dr;
Int32 holder = 0;
if(groupTo.Count > 0)
{
foreach(Object item in groupTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientTo.Contains(holder))
{
recipientTo.Add(holder);
}
}
dr.Close();
}
}

//Get CC Addresses from Groups
if(groupCc.Count > 0)
{
foreach(Object item in groupCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientCc.Contains(holder))
{
recipientCc.Add(holder);
}
}
dr.Close();
}
}

//Get BCC Recipients for this Maillbox
ArrayList recipientAll = new ArrayList();
recipientAll.Add(Convert.ToInt32(Mailbox_ID));
foreach(Object item in recipientTo)
{
recipientAll.Add(item);
}
foreach(Object item in recipientCc)
{
if(!recipientAll.Contains(item))
{
recipientAll.Add(item);
}
}
string allRecipients = "";
foreach(Object item in recipientAll)
{
allRecipients += item + ",";
}
allRecipients = allRecipients.Substring(0,allRecipients.Length-1);
ArrayList recipientBcc = new ArrayList();
cmd.CommandText = "Mail_GetBccAddressees";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Addressees",SqlDbType.VarChar,1000);
param.Value = allRecipients;
dr = cmd.ExecuteReader();
//Add any Bcc Recipients to Bcc Recipients ArrayList
while(dr.Read())
{
recipientBcc.Add(Convert.ToInt32(dr["Addressee"].ToString()));
}
dr.Close();

// Start a local transaction.
SqlTransaction myTrans = Conn.BeginTransaction();
// Enlist the command in the current transaction.
cmd.Transaction = myTrans;

try
{
//Insert Message into Database
cmd.CommandText = "Mail_AddNewMessage";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MsgFrom",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.MsgSubject",SqlDbType.VarChar,100);
param.Value = subject;
param = cmd.Parameters.Add("@.MsgBody",SqlDbType.Text);
param.Value = body;
int msg_ID = Convert.ToInt32(cmd.ExecuteScalar());

//Add To Recipients
cmd.CommandText = "Mail_AddMessageAddressees";
foreach(Object item in recipientTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 1;
cmd.ExecuteNonQuery();
}

//Add CC Recipients
foreach(Object item in recipientCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 2;
cmd.ExecuteNonQuery();
}

//Add BCC Recipients
foreach(Object item in recipientBcc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 3;
cmd.ExecuteNonQuery();
}

//Add message to senders Mailbox in SentItems
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 4;
cmd.ExecuteNonQuery();

//Add files if there are any.
foreach(HttpPostedFile myfile in files)
{
//Get the file name
string FileName = myfile.FileName;
int strLoc = FileName.LastIndexOf("\\");
FileName = FileName.Remove(0,strLoc+1);

// Get size of uploaded file
int nFileLen = myfile.ContentLength;

// Allocate a buffer for reading of the file
byte[] myData = new byte[nFileLen];

// Read uploaded file from the Stream
myfile.InputStream.Read(myData, 0, nFileLen);
File file = new File();
int file_ID = file.UploadFile(cmd,3,FileName,nFileLen,myfile.GetType().ToString(),Wargame_ID,myData);

//Add the file message association
cmd.Parameters.Clear();
cmd.CommandText = "Mail_AddMessageFile";
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.File_ID",SqlDbType.Int);
param.Value = file_ID;
cmd.ExecuteNonQuery();
}
//Commit Transaction
myTrans.Commit();
}
catch (SqlException sqlex)
{
// Specific catch for deadlock
if (sqlex.Number != 1205)
{
myTrans.Rollback();
}
throw(sqlex);
}
catch(InvalidOperationException ex1){
string ex2 = ex1.ToString();
}
catch (Exception ex)
{
myTrans.Rollback();
throw (ex);
}
finally
{
myTrans.Dispose();
Conn.Close();
Conn.Dispose();
}

}|||First try to run a simple transaction with one stored proc and minimal .net code. This will make it easier to pinpoint your problem. Could be memory problem on server or some other potential problem you are not aware of.

This may not be a problem but I have encountered this one before. Are you returning more than one id with the @.@.IDENTITY call (i.e more than one insert command in a single transaction)? If you are you need to reset the sql connection or the same id is returned each time you call it if my memory serves me correctly.

Also, you can check the error status within a stored proc and return it to .net. I have used this before to pinpoint a problem.

Cheers

Mo|||Thanks Mo for the insight, unfortunatly I still have the same problems. Funny thing is I created a single stored procedure to do all the inserts, thinking that would solve the problem, but alas nothing. Getting different results, but same outcome. The database executes the procedure, which inserts data into the database, but for some reason after about 3 min or so the rows are rolled back. Running sp_lock I can see that the process that executed the command has a lock on all the tables, but it never gives them up. After that timeout, they are all rolled back. Is there some reason anyone can think of where this would happen. I can execute the stored procedure on the database using query analyzer and the data goes in fine and stays in there. Another thing to note, after the web application executes the stored procedure I cannot view the data in the tables through Enterprise manager. I can't even view the current activity, it sits there for a while then returns a 1222 Error. This has become a nightmare. Thanks

Chris|||Could be a bug between sql server and .net ?? Are both upto date with service packs?

Have you looked on http://support.microsoft.com

Very good area for problem solving, used it many times.

ADO.NET connection to SQL fails

Hello,
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO and it is still rejected.
One last question; have you ever wanted to give up with computers and go back to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
Dale
DBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper? =20
>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale
|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/r...eleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:

>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO and it is still rejected.
>One last question; have you ever wanted to give up with computers and go back to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale

ADO.NET connection to SQL fails

Hello,
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm tryin
g to do an insert to can't validate my login. I've verified that the account
does have permissions to access the SQL DB and that everything is granted a
s far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and W
indows).
I've tried all the suggestions from the "connectionstrings.com" website and
I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Se
rver connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO an
d it is still rejected.
One last question; have you ever wanted to give up with computers and go bac
k to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
DaleDBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper? =20
>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/...ReleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:

>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do
an insert to can't validate my login. I've verified that the account does have perm
issions to access the SQL DB and that everything is granted as far as the INSERT, UP
DAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and
Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and
I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL S
erver connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO a
nd it is still rejected.
>One last question; have you ever wanted to give up with computers and go ba
ck to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale

ADO Stream from Stored Procedure with JScript fails mysteriously

Hi all, I've seen a couple of references to this problem, but only one
solution which had no appreciable effect.
I have a stored procedure which uses FOR XML EXPLICIT. The stored
procedure works quite happily and I can access the DB perfectly well
using VBScript.
Being of a religious bent, I'd rather not use VBScript.
I have the following *working* function, ported directly from the
broken JScript version to VBScript. The Jscript version fails on
Execute telling me that my procedure wasn't expecting any parameters.
Handy that, 'cos it doesn't get any.
I've tried using oCmd.Execute(0,0,1024),
oCmd.Execute(null,null,0x400), oCmd.Execute('', '', 0x404) without a
commandType, oCmd.Execute(N, N, 1024) where N is a null variable and
all points in between.
The error varies amusingly if I give it a nonsensical constant, but
provided I stick to something sensible, I get an 0x80040E21 error.
Finally, there are no differences between the two versions other than
the obvious ones.
Any idea how I can get this working in JScript?
******** begin code ********
function fetchXML()
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.CommandType = 4
oCmd.CommandText = "prcMenu_FetchMenu"
set stmOut = Server.CreateObject("ADODB.Stream")
stmOut.Open()
set cn = getConnection()
oCmd.ActiveConnection = cn
oCmd.Properties("Output Stream") = stmOut
oCmd.Execute , , 1024
set cn = nothing
set oCmd = nothing
sOut = "<menu>"+stmOut.ReadText()+"</menu>"
set stmOut = nothing
fetchXML = sOut
End Function
******** end code *******
Cheers,
-- Bob
0x80040E21 is DB_E_ERRORSOCCURRED. Its description is: "Multiple-step
operation generated errors. Check each status value. No work was done."
Check your stored procedure text.
This procedure works (MS SQL Server 2000, local, database pubs, Windows
security):
//----
var conn = new ActiveXObject("ADODB.Connection");
conn.Open("Provider=SQLOLEDB;Integrated Security=SSPI;Initial
catalog=pubs");
var cmd = new ActiveXObject("ADODB.Command");
cmd.ActiveConnection = conn;
cmd.CommandText = "SELECT * FROM authors FOR XML AUTO";
var stream = new ActiveXObject("ADODB.Stream");
stream.Open();
cmd.Properties("Output Stream") = stream;
cmd.Execute(null, null, 0x400);
WScript.Echo(stream.ReadText());
//----
//--
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE
"Bob Gregory" <bobgregory@.ppsltd.net> ??/?? ? ?? ??:
news:aba9c4c4.0407200859.7a64e94b@.posting.google.c om...
> Hi all, I've seen a couple of references to this problem, but only one
> solution which had no appreciable effect.
> I have a stored procedure which uses FOR XML EXPLICIT. The stored
> procedure works quite happily and I can access the DB perfectly well
> using VBScript.
> Being of a religious bent, I'd rather not use VBScript.
> I have the following *working* function, ported directly from the
> broken JScript version to VBScript. The Jscript version fails on
> Execute telling me that my procedure wasn't expecting any parameters.
> Handy that, 'cos it doesn't get any.
> I've tried using oCmd.Execute(0,0,1024),
> oCmd.Execute(null,null,0x400), oCmd.Execute('', '', 0x404) without a
> commandType, oCmd.Execute(N, N, 1024) where N is a null variable and
> all points in between.
> The error varies amusingly if I give it a nonsensical constant, but
> provided I stick to something sensible, I get an 0x80040E21 error.
> Finally, there are no differences between the two versions other than
> the obvious ones.
> Any idea how I can get this working in JScript?
> ******** begin code ********
> function fetchXML()
> set oCmd = Server.CreateObject("ADODB.Command")
> oCmd.CommandType = 4
> oCmd.CommandText = "prcMenu_FetchMenu"
> set stmOut = Server.CreateObject("ADODB.Stream")
> stmOut.Open()
> set cn = getConnection()
> oCmd.ActiveConnection = cn
> oCmd.Properties("Output Stream") = stmOut
> oCmd.Execute , , 1024
> set cn = nothing
> set oCmd = nothing
> sOut = "<menu>"+stmOut.ReadText()+"</menu>"
> set stmOut = nothing
> fetchXML = sOut
> End Function
> ******** end code *******
> Cheers,
> -- Bob
|||"Viatcheslav V. Vassiliev" <msnewsgroup@.www-sharp.com> wrote in message news:<O63yoqobEHA.4092@.TK2MSFTNGP10.phx.gbl>...
> 0x80040E21 is DB_E_ERRORSOCCURRED. Its description is: "Multiple-step
> operation generated errors. Check each status value. No work was done."
> Check your stored procedure text.
> This procedure works (MS SQL Server 2000, local, database pubs, Windows
> security):
<snip />
I submitted the system in VBScript thanks to deadline constraints so
this is now a purely academic issue. I know the stored proc is
absolutely fine, because I return useful results from VBScript with
the same procedure. The only difference is the language, and
presumably the underlying mechanics of speaking to COM therewith.
-- Bob

ADO initialization fails on W2003

OVERVIEW:
ADO initialization fails on W2003 Server, Standard Edition.
This is a new system.
This is the first time this app has been compiled or run on this system.
The app runs fine on all other servers I've tried it on.
What should I check?
Is anyone else able to run ADO routines against OLE 5.2?
Thanks,
Brad.
DETAILS:
OLE32.dll v.5.2.3790.250
OLEAUT32.dll v.5.2.3790.0
Tracing into the code, I get into
_WStrAsg("", "'ISAUTOINCREMENT'");
which calls SysReAllocStringLen. This fails with an access violation.
SysReAllocStringLen is a call into OLEAUT32.dll.
All the OLE32 dll look to be updated. They are newer than anything
on any of my XP systems.
I have a new test project that uses ADODB and it *doesn't* fail.
Yes, it does go through the same code just as nice as you could want.
The same app is deployed an running on dozens of other systems.
None are as new as this one.
So my guess is that it has to be something about the way the new
system is set up.
My next step is to start tearing down a copy of my project. But I'm
not terribly hopeful.
Thanks,
Brad.Long shot but is the bad project compiled on the same library as the working
one. Also, have you updated to the latest mdac + fixes.
-oj
"Brad White" <bwhite at inebraska . com> wrote in message
news:OQLP18vDFHA.2540@.TK2MSFTNGP09.phx.gbl...
> OVERVIEW:
> ADO initialization fails on W2003 Server, Standard Edition.
> This is a new system.
> This is the first time this app has been compiled or run on this system.
> The app runs fine on all other servers I've tried it on.
> What should I check?
> Is anyone else able to run ADO routines against OLE 5.2?
> Thanks,
> Brad.
> DETAILS:
> OLE32.dll v.5.2.3790.250
> OLEAUT32.dll v.5.2.3790.0
> Tracing into the code, I get into
> _WStrAsg("", "'ISAUTOINCREMENT'");
> which calls SysReAllocStringLen. This fails with an access violation.
> SysReAllocStringLen is a call into OLEAUT32.dll.
> All the OLE32 dll look to be updated. They are newer than anything
> on any of my XP systems.
> I have a new test project that uses ADODB and it *doesn't* fail.
> Yes, it does go through the same code just as nice as you could want.
> The same app is deployed an running on dozens of other systems.
> None are as new as this one.
> So my guess is that it has to be something about the way the new
> system is set up.
> My next step is to start tearing down a copy of my project. But I'm
> not terribly hopeful.
> --
> Thanks,
> Brad.
>
>
>|||Thanks for the reply.
The first thing I checked was MDAC, and yes it is 2.8.
I thought last night that perhaps it is a 2003 thing,
but no, we have had installations for months on 2003 Server
with no problems.
Same libraries. Tried the debug versions of the libraries in both exe's
with same results.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uMArKKwDFHA.2032@.tk2msftngp13.phx.gbl...
> Long shot but is the bad project compiled on the same library as the
working
> one. Also, have you updated to the latest mdac + fixes.
> --
> -oj
>
> "Brad White" <bwhite at inebraska . com> wrote in message
> news:OQLP18vDFHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||Brad,
Perhaps, reinstall the package?
-oj
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23LzC%23$3DFHA.3924@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply.
> The first thing I checked was MDAC, and yes it is 2.8.
> I thought last night that perhaps it is a 2003 thing,
> but no, we have had installations for months on 2003 Server
> with no problems.
> Same libraries. Tried the debug versions of the libraries in both exe's
> with same results.
> --
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uMArKKwDFHA.2032@.tk2msftngp13.phx.gbl...
> working
>|||"oj" <nospam_ojngo@.home.com> wrote in message
news:u34aoVAEFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Brad,
> Perhaps, reinstall the package?
>
Thanks for the reply.
The way it was acting reminded me of memory corruption,
but I couldn't see how that could be since no custom code
had run yet.
Turns out it was an unrelated dll improperly installed.
Reinstalling that to solve another problem, solved this problem.
It wasn't an ADO thing or WideStr thing at all.
--
Brad White

ado - stored procedure warnings

I am trying to run a stored procedure in an access front end using ado and the execute command. The process fails with the error "Warning: Null value is eliminated by an aggregate or other SET operation." - I get this warning when running the sp in query analyser but I know this is not a problem and it just continues. Is there a way to get ado to ignore these warnings and continue the code.

thanksOnce you establish a connection use <Connection Object>.Execute "SET ANSI_WARNINGS OFF", , adCmdText.

You might also want to issue a SET NOCOUNT ON.