HELP Access to sql server 2005 express connection FAILS UPDATE

Hi

I got an access 2002 application front end with a sql server 2005 express back end. Some of my clients are having some difficulties. After using the application for a while, some of the users are finding that the system just hangs up. It usually happens after the front end application has been running for about an hour (sometimes sooner and sometimes later). There are perhaps 1 to 5 concurrent users and I have checked to see if there are any firewalls stalling it (I think I check all of them)- Is there any way that SQL Server 2005 express could be caused to just stall- This even occurs with the odd laptop. All the appropriate protocols are enabled as well. These databses are not very large.

ANY HELP WOULD BE GREATLY APPRETIATED!!!

Thanks

Frank Srebot

Moved thread to the SQL Server Express forum.|||

hi Frank,

what do you mean by "just hangs up"? does it completely stalls requiring a reboot, or it's "sleeeping" for just a while and then restarts working "as expected" or the like?

to start, few things to consider..

SQLExpress sets the "autoclose" property of it's created databases to true, and this causes the dbs to be shut down when not in use, meaning that tyey will be closed if no active connection references them.. this involves a little overhead at next re-use as the dbs must be re-open, but I do not think this is your problem... anyway, the eventual related "problem" can be workaround modifying the relative database property via sp_dboption database's system stored procedure call...

"autoshrink" database property is even set to true, and this causes, at engine scheduled time frames, the eventual shrinking of the involved databases, so that when lots of insert/delete operations are performed (actually lots of deletes), the engine wakes up a thread to shrink (when necessary) the databases, requiring some time to execute..

if the autogrowth property of the database's datafiles and logfiles is set to true and the engine states new file space is required, the engine enlarges the files (when needed) and this will obviously involve some time as well...

other non SQL Server related issues includes OS's scheduled tasks requiring lots of CPU and/or I/O..

but it's hard to solve this way

regards

|||

Thanks for the great and quick response.

To clarify, sql server just hangs up meaning that the application displays an hourglass and eventually the sql connection is lost and an error message is given. I was doing some research and I was wondering if the problem could be in the connection pooling configuration- currently the setting are that pool connections are enabled by default in the ODBC config settings- the databases which I am dealing are quite small- would any one have any ideas perhaps along these lines?

Thanks

Frank srebot

|||

Hi

This is an update to my connection Problem with Access 2002 to SQL SERVER EXPRESS 2005.

We are having random disconnects on the client side with Access putting up a "Connection Failure" dialog box even when the user is actively entering records into the system. Have any of you ever encountered this situation? We have disabled all TCP offloading engine technology on the machine thinking this was causing a problem with SQL Server as well as changing network cards to a whole different brand. We've pretty much ruled out the physical network at this point because we have changed cables and moved to another port on a different switch to no avail.

Is there some timeout setting or connection pooling setting that I am unaware of at the SQL Server level that has a problem interacting with Windows Server 2003 or Windows XP? I have checked and double checked all of the server settings between the old machine and the new and they are identical.

I did read that the connection pooling may be stressed and the pool of connections are 'Leaking'. This might be due to a bad cable or connection, but thats all I have found out.

Any info would be greatly appreciated.

Thanks

Frank

HELP - urgent situation

Hey all

I've got a DTS package with a script that does this:

Set xlApp = CreateObject("Excel.Application")

This fails though. Do I have to install MS Office on the machine the SQL Server is on? Or can I simply copy a few DLLs over and register them??

Thanks! I gotta get this solved within 5 hrs :(I am by no means an expert on this particular situation, but I would think that your 'best' (meaning most likely to work the first time) solution would be to install MS Office (just the Excel portion of it) on the server.

Regards,

hmscott|||Our in-house IT guy won't install Excel/Office on the server that our SQL Server is on.. something about opening up for potential problems and/or security issues.

It seems to me that if you CreateObject("Excel.Application"), that there's some kind of ActiveX .dll that could be registered on the server to allow the creation of these Excel COM objects. Anyone know which files they are??|||Where is the Package called from?

If client side app I would suggest installing Excel would work,

If a VB app adding the Excel distributables in the compile should work.

Create a reference to the Excel Object in your VB app.

mine is at
C:\Program Files\Microsoft Office 2000\Office\EXCEL9.OLB

When installed the required registry entries should follow.

Good Luck|||It's an ActiveX script within a DTS package on the server. I'd LIKE to have Office installed, but IT won't allow it.

Is there some way I could place the EXCEL9.OLB file on the server, and reference it somehow in my VBScript??|||All DTS Packages are run on the client unless scheduled by SQL Server regardless of where the code resides.

Your users will need Excel on their PC to use the output anyway, as well as DTS dlls (read SQL Server Client).

Is XML an option? or a structured text file which they can import into Excel. If so you could use the fileScripting object in Active script.|||Well.. actually this is a web page executing the DTS package.. so all the proper DTS dlls are installed on the web server now. And the DTS package has an ActiveX script that tries to create an instance of Excel.Application..but can't do so because Office is not installed on the web server (and will never be).

Nope.. XML is not an option. They gave us an Excel workbook they want populated.. there's no room to budge on this, unfortunately.|||If all you are doing is a data migration into a template try to map to the Excel spreadheet and se a dtsDataPump|||What if you pumped the data to a Comma Separated Value file (.csv), and let Excel convert it from wherever the client happens to open it from?|||OK here's the solution we went with.. MS Office Web Components.

Problem is, I can't find any thorough documentation on it. I want to know if it's possible to open an Excel file with it.

Set objOWC = CreateObject("OWC10.Spreadsheet")

That's how it's created. I have a few examples that tell you how to create a new Excel file from scratch and save it. But not how to open an existing one, modify it, then save to a new file.

Does anyone know, or can point me to some documentation? Thanks!