Occasional "ConnectionWrite" error

[Stuartp]

I have written a program in VB to allow operators (up to 6) to add entries to a table in an SQL Server 2000 database. I am using an ADO connection, so each operator's copy of the program will create a connection to SQL Server, open the table and add the records. The recordset is then closed, but the connection is left open as the operators leave the program open to use as needed, and the connection is closed when the program is closed.

Most of the time this works OK, but sometimes a machine will produce the error "Run Time Error -2147467259 (80004005) [Microsoft] [ODBC SQL Server Driver] [Named Pipes] ConnectionWrite (Writefile)" when trying to save a record.

Does anyone know what the cause of this is or how to prevent it? We use TCP/IP to connect to SQL Server, not named pipes.

Also, from a program overhead point of view, is it better to create/open a connection as and when it is needed, or to open it at the start and leave it open until the program is closed? I've read somewhere that it is best left open, but other I'd be grateful for other opinions.

Many thanks.

Stuart

 

[CasperTFG]

What is your connection to the SQL server. ADO, DAO ? etc.

Also please include your connection string. Shoudl be something like PROVIDER=MSSQLOLEDB... etc.

Craig, mailto:sander@cogeco.ca

In the computer industry, there are three kinds of lies:
lies, damn lies, and benchmarks.

 

[Stuartp]

I am using ADO to connect to SQL. The connection string is "Provider=MSDASQL.1;Password=purchase;Persist Security Info=True;User ID=purchase;Data Source=Charisma".

Thanks

Stuart