Borge Hansen
pcs.accessd at gmail.com
Wed May 6 06:36:24 CDT 2009
Stuart, Thanks... The Windows2003 Server only has one named instance of SQL 2005 Express installed : SQLExpress The Win2K3 Server is not a domain server. Both the Vista and Win2003 SQL Server instances are set to Mixed Mode I use SQL Server Authentication. The setup of the SQL Express on the Vista and Win2K3 are identical as far as I can tell - I installed both ... One difference may be that on the Vista only Shared Memory and TCP/IP are enabled, on the Win2K3 Named Pipes may have been enabled as well - but that should not make a difference... I did a clean install of SQL2005Express on the Win2K3 machine and restored the two databases that the application uses onto the SQL Server. To test the access application on the Win2K3 I created the ODBC DSN x 2 (one for each Db); copied the FE to a Folder on the C: Drive.... The application fires up, displays data on Forms (a mix of ODBC linked tables and ADODB recordset depending on the Form etc. etc.) All works fine. Except for when I issue a Stored Procedure request expecting the output parameter to be returned to the calling function.... Runing that part of the code/application invariably returns a "" instead of the expected values ..... Someone on another forum suggested to run SQL Server Profiler - I'd like to do that, but SQLExpress does not come with that - What could I do with that? Determine that the SP in question in fact did provide an output parameter - but somehow it did not come all the way back into the VBA environment?? If we assume that is the case, is it then a permission issue? What else could be the cause? borge On Wed, May 6, 2009 at 9:09 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote: > If you are using an FE on a workstation to an SQL Server BE on a server, > the MDAC on the > server is irrelevant, it's the workstation MDAC that does the work. > > I doubt that this is MDAC related. > > > I'd suspect a permission problem. > > Does the server have another instance of SQL Server running on it? > Is the Windows Server 2003 running in a domain? > Are the SQL Server instances on Vista and Win 2003 set up to used Windows > Authentication, SQL Server Authentication or "Mixed mode"? > > (I don't have access to Win Server 2003 at the moment so can't answer about > the latest file > versions.) > > > -- > Stuart > > On 6 May 2009 at 19:52, Borge Hansen wrote: > > > 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 > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >