Salakhetdinov Shamil
mcp2004 at mail.ru
Wed May 6 09:16:44 CDT 2009
Hi Borge,
It's not easy to answer your question.
Can you compile and run the following C# test on your problematic PC?
If it will run OK then something is probably wrong with MDAC, if it will fail then - ????
This is a test SP:
CREATE PROCEDURE sp_test
@prm1 int,
@prm2 varchar(80),
@prm3 varchar(120) out
AS
BEGIN
SET NOCOUNT ON
SET @prm3 =
'@prm1 = ' + Cast(@prm1 as varchar(20)) + ', ' +
'@prm2 = ' + @prm2
return 503
END
GO
This is a test code (change connection string):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace TestConsoleApplication1
{
class Program3
{
private const string _connectionString =
@"Data Source=HAMBURG\SQL2005;Initial Catalog=NorthwindNET;Integrated Security=True";
static void Main(string[] args)
{
try
{
using (SqlConnection cnn = new SqlConnection(_connectionString))
{
cnn.Open();
string sql = "sp_test";
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter prm = new SqlParameter("@prm1", 25);
cmd.Parameters.Add(prm);
prm = new SqlParameter("@prm2", "TEST");
cmd.Parameters.Add(prm);
prm = new SqlParameter("@prm3", SqlDbType.VarChar, 120);
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm);
prm = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
prm.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(prm);
int ret = cmd.ExecuteNonQuery();
Console.WriteLine("ret = {0}", cmd.Parameters["@RETURN_VALUE"].Value.ToString());
Console.WriteLine("@prm3 = '{0}'", cmd.Parameters["@prm3"].Value.ToString());
//ret = 503
//@prm3 = '@prm1 = 25, @prm2 = TEST'
}
}
catch(Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
}
}
}
--
Shamil
-----Original Message-----
From: Borge Hansen <pcs.accessd at gmail.com>
To: accessd at databaseadvisors.com
Date: Wed, 6 May 2009 19:52:26 +1000
Subject: [AccessD] MSADO28.tlb and MDAC
> Shamil,
> Thanks for your reply.
> Yes, MDAC files are now considered part of the operating system, and you
> can't readily downgrade or upgrade the version ....
>
> I still have a mysterious problem.
> Please excuse if this is a bit long.
> This is causing me great frustration.
> I hope someone can be of help!
>
> My problem relates to following setup:
> Access2003 Frontend (FE) on Win2003 Sp2 Server
> The FE has been modified to work against a SQL2005 Db Backend.
> Some functionality uses linked ODBC tables, some functionality uses ADODB
> record sets.
> And some uses ADO for executing Stored Procedures (SP)s in the SQL Db.
> I have developed code (functions) that send a parameterized SP request to
> the SQL Db.
> The last parameter is an output parameter - the value of which after the SP
> has run is returned to the calling code in the Access aplication / VBA .
> This is working without any problems on several systems ... all with the ADO
> 2.8 version referenced in the VBA
>
> The application has been tested to run with no problems in this setting:
>
> SQL 2005 Express on Vista Ultimate laptop as server in a workgroup setup
> Frontend tested on the Vista Ultimate laptop; on a Windows XP laptop
> upgraded to WindowsXP SP3 in April 2008; on a Vista Home laptop, and on
> another WindowsXP
> All works fine!
>
> However, on the client's site - it does not work.
>
> The SQL2005Express has been set up in the same way on my Vista Ultimate
> laptop and the client's Windows Server 2003 SP2:
>
> TCP/IP protocol enabled and set to listen on port 9999 rather than dynamic
> ports
>
> I can create ODBC DNS using this port and establish linked tables to the SQL
> Db - both on the Windows2003 Server and on the Vista Ultimate ....
>
> I can run the particular code that returns the output parameter form the
> Stored Procedure from any of my workstations against the SQL Db on the Vista
> Ultimate laptop (computers in workgroup no domain)
>
> With the same application running either from a workstation in the
> Windows2003 SP2 Server workgroup setup, or directly on the Windows2003 SP
> Server machine itself, I have verified that the code issuing the SP request
> receives back the output parameter intermittently. Where I would expect "0 -
> bla bla" or "1 - bla bla" I get nothing,i.e.: ""
>
> I believe it may have something to do with the MDAC version installed on the
> client's site - on the Windows2003 SP2
>
> The SQL2005 Express installed on the Windows2003 SP2 reports the following
> propeties:
> Microsoft SQL Server Management Studio Express
> 9.00.4035.00
> Microsoft Data Access Components (MDAC)
> 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
> Microsoft MSXML 2.6 3.0 5.0 6.0
> Microsoft Internet Explorer 7.0.5730.13
> Microsoft .NET Framework 2.0.50727.42
> Operating System 5.2.3790
>
> On the Vista Ultimate laptop, the SQL2005 Express reports:
> Microsoft SQL Server Management Studio Express
> 9.00.4035.00
> Microsoft Data Access Components (MDAC)
> 6.0.6000.16386 (vista_rtm.061101-2205)
> Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
> Microsoft Internet Explorer 7.0.6000.16575
> Microsoft .NET Framework 2.0.50727.1433
> Operating System 6.0.6000
>
> On all machines the ADO DLLs and .tlb files sits in: C:\Program Files\Common
> Files\System\ado
>
> The VBA Code requires a reference to ADO2.8 in order for the return of an
> output parameter from a Stored Procedure in SQL Db to work.
>
> With the Frontend Access2003 application (FE) placed on various machines we
> have the following ADO references:
>
> FE on Vista Ultimate or on a Vista Home workstation laptop:
> - ADO2.8 is referenced to a file called msado28.tlb (!!)
> - The msado15.dll is versioned as 6.0.6000.16386
> - Function call works OK!
>
> FE on WindowsXP SP3 against the SQL2005Express on Vista:
> - ADO2.8 is referenced to the file called msado15.dll which is versioned as
> 2.81.1132.0 (xpsp.080413-0852).
> - The ado folder has no msado28.tlb file !!
> - Function call works OK
>
> FE on the Windows2003 Server SP2 against SQL2005Express on same machine:
> - ADO2.8 is referenced as msado15.dll which is versioned 2.82.3959.0
> - No msado28.tlb file
> - Function call does NOT work...
>
> So, my questions to the list is:
> Is it MDAC related?
> If not what else could it be?
> How can I debug this further?
> Is it the MDAC on the machine where SQL2005Express resides that is important
> here or the MDAC on the workstation or both?
> What is the latest SP of Windows Server 2003? and what are the version of
> the MDAC files on the latest version, especially msado15.dll ??
> ??
>
> Regards
> borge
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>