Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Database Locking Problem with MS SQL Server 6.5

5 posts in PB Infomaker Report Writer Last posting was on 1999-03-25 23:08:52.0Z
Hannah Wong Posted on 1999-03-23 19:17:08.0Z
Newsgroups: sybase.public.infomaker.general
From: "Hannah Wong" <hwong@bdo.com>
Subject: Database Locking Problem with MS SQL Server 6.5
Date: Tue, 23 Mar 1999 11:17:08 -0800
Lines: 17
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <348_bXtMDDWd#GA.115@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6475
Article PK: 241459

Using Infomaker 6 with MSSQL Server 6.5:

When using stored procedures that create temp tables, the compiled
executable locks the syscolumns table of tempdb and does not release it.
This causes any other system procedures requiring access to tempdb to be
blocked. The locks are not released until the compiled executable is closed.
This does not occur when using IM6.0.

Has anyone else encountered this?
Has anyone else resolved this?

Thanks.

Hannah Wong


Terry Dykstra Posted on 1999-03-23 19:46:30.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra" <tdykstra@cfol.ab.ca>
Subject: Re: Database Locking Problem with MS SQL Server 6.5
Date: Tue, 23 Mar 1999 12:46:30 -0700
Lines: 32
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_uRiK6VWd#GA.290@forums.sybase.com>
References: <348_bXtMDDWd#GA.115@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6472
Article PK: 241457

To determine the difference between IM6 and an executable, turn on tracing.
This will tell you what IM is sending to the database. In the DB-profile
there is a check box to generate a trace file.
In the executable you add the word trace to the ini file in the [database]
section:
DBMS=trace ODBC (or what driver you are using).
I'm not sure we can change the behavior of the executable, but at least we
know what the difference is.
--
Terry Dykstra (TeamPS)
Canadian Forest Oil Ltd.

Hannah Wong wrote in message ...
>Using Infomaker 6 with MSSQL Server 6.5:
>
>When using stored procedures that create temp tables, the compiled
>executable locks the syscolumns table of tempdb and does not release it.
>This causes any other system procedures requiring access to tempdb to be
>blocked. The locks are not released until the compiled executable is
closed.
>This does not occur when using IM6.0.
>
>Has anyone else encountered this?
>Has anyone else resolved this?
>
>Thanks.
>
>Hannah Wong
>
>
>


Terry Dykstra Posted on 1999-03-23 23:57:38.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra" <tdykstra@cfol.ab.ca>
Subject: Re: Database Locking Problem with MS SQL Server 6.5
Date: Tue, 23 Mar 1999 16:57:38 -0700
Lines: 43
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_R5bpOiYd#GA.218@forums.sybase.com>
References: <348_bXtMDDWd#GA.115@forums.sybase.com> <348_uRiK6VWd#GA.290@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6469
Article PK: 241452

It might be caused by the autocommit dbparm. By default it is set to false,
which effectively results in a begin transaction when connecting and a
commit when disconnecting. I would set it to true and see if it makes a
difference.

--
Terry Dykstra (TeamPS)
Canadian Forest Oil Ltd.

Terry Dykstra wrote in message ...
>To determine the difference between IM6 and an executable, turn on tracing.
>This will tell you what IM is sending to the database. In the DB-profile
>there is a check box to generate a trace file.
>In the executable you add the word trace to the ini file in the [database]
>section:
>DBMS=trace ODBC (or what driver you are using).
>I'm not sure we can change the behavior of the executable, but at least we
>know what the difference is.
>--
>Terry Dykstra (TeamPS)
>Canadian Forest Oil Ltd.
>Hannah Wong wrote in message ...
>>Using Infomaker 6 with MSSQL Server 6.5:
>>
>>When using stored procedures that create temp tables, the compiled
>>executable locks the syscolumns table of tempdb and does not release it.
>>This causes any other system procedures requiring access to tempdb to be
>>blocked. The locks are not released until the compiled executable is
>closed.
>>This does not occur when using IM6.0.
>>
>>Has anyone else encountered this?
>>Has anyone else resolved this?
>>
>>Thanks.
>>
>>Hannah Wong
>>
>>
>>
>
>


Matt Gililand Posted on 1999-03-23 22:12:50.0Z
Newsgroups: sybase.public.infomaker.general
Date: Tue, 23 Mar 1999 17:12:50 -0500
From: Matt Gililand <matt.gilliland@sympatico.ca>
Reply-To: matt.gilliland@sympatico.ca
Organization: home
X-Mailer: Mozilla 4.5 [en]C-SYMPA (Win95; U)
X-Accept-Language: en,fr-CA
MIME-Version: 1.0
Subject: Re: Database Locking Problem with MS SQL Server 6.5
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 28
Message-ID: <348_36F811E2.39BDAF@sympatico.ca>
References: <348_bXtMDDWd#GA.115@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6471
Article PK: 241460

I am currently working on a project for a company that is experiencing similar
problems. However they are using PB5.004 and they are experiencing large
blocking problems when using temp tables. I have never seem this problem before
with other DBs. I am convinced that MSSQL Server 6.5 can't be that poor of a
database to cause these system wide blocks. I would believe that this could be
a a configuration issue of some type. But I would be very interested to hear a
Microsoft opinion on why temporary tables can lock an entire database

Hannah Wong wrote:

> Using Infomaker 6 with MSSQL Server 6.5:
>
> When using stored procedures that create temp tables, the compiled
> executable locks the syscolumns table of tempdb and does not release it.
> This causes any other system procedures requiring access to tempdb to be
> blocked. The locks are not released until the compiled executable is closed.
> This does not occur when using IM6.0.
>
> Has anyone else encountered this?
> Has anyone else resolved this?
>
> Thanks.
>
> Hannah Wong


Hannah Wong Posted on 1999-03-25 23:08:52.0Z
Newsgroups: sybase.public.infomaker.general
From: "Hannah Wong" <hwong@bdo.com>
Subject: Re: Database Locking Problem with MS SQL Server 6.5
Date: Thu, 25 Mar 1999 15:08:52 -0800
Lines: 9
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <348_d0gNyNxd#GA.148@forums.sybase.com>
References: <348_bXtMDDWd#GA.115@forums.sybase.com> <348_36F811E2.39BDAF@sympatico.ca>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6457
Article PK: 241443

I was able to determine that the executable was beginning a transaction upon
connection. Since I did not have AutoCommit set to True, the executable was
not releasing locks on tempdb until the connection was closed.

Setting AutoCommit to True solved the lock problem.

Hannah Wong