archana.kulkarni
2nd March 2010, 04:40 PM
For whatever reason Microsoft chose to make this a default behaviour (probably because there is no two-phase commit), there are times when running a report against Sql Server 2000 and Sql Server 2005 can cause page locks in the database (even though the JDBC connection properties are set to read-only).
BI Plus allows you to get around this design flaw in Sql Server.
http://www.seemoredata.com/en/../wiki/_media/tips:lockds1.gif (http://www.seemoredata.com/en/../wiki/_detail/tips:lockds1.gif?id=tips%3Asql_server_locking)
Edit the Datasource connection properties to force an override of the transaction isolation level and force Sql Server to issue queries automatically in NO COMMIT mode. This would be the same as writing a query like: Select …… from …… where CONDITION WITH (NOLOCK), without having to understand the reasons for doing so, or issuing every query to this database in this way.
Click the expand (…) button on the Properties section of the Datasource configuration.
A dialog opens that allows you to add parameters to the datasource connection.
Add 2 parameters here, of type SQL, so that upon datasource connection, these are executed by BI+.
http://www.seemoredata.com/en/../wiki/_media/tips:lockds2.gif (http://www.seemoredata.com/en/../wiki/_detail/tips:lockds2.gif?id=tips%3Asql_server_locking) Select SQL form the dropdown list of Property Parameter Types.
In the NAME sub-property, enter: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Create a 2nd parameter, of type SQL with the property of SET NOCOUNT ON
http://www.seemoredata.com/en/../wiki/_media/tips:lockds3.gif (http://www.seemoredata.com/en/../wiki/_detail/tips:lockds3.gif?id=tips%3Asql_server_locking)
Now click OK and then test the datasource connection. If you have mistyped the parameter, Sql Server will return an error, telling you it does not understand the parameter value. If you get Datasource Connection is Successful then you have succeeded.
A quick look at the server log after making the datasource connection test reveals that the commands were executed in serial, successfully.
http://www.seemoredata.com/en/../wiki/_media/tips:lockds4.gif (http://www.seemoredata.com/en/../wiki/_detail/tips:lockds4.gif?id=tips%3Asql_server_locking)
BI Plus allows you to get around this design flaw in Sql Server.
http://www.seemoredata.com/en/../wiki/_media/tips:lockds1.gif (http://www.seemoredata.com/en/../wiki/_detail/tips:lockds1.gif?id=tips%3Asql_server_locking)
Edit the Datasource connection properties to force an override of the transaction isolation level and force Sql Server to issue queries automatically in NO COMMIT mode. This would be the same as writing a query like: Select …… from …… where CONDITION WITH (NOLOCK), without having to understand the reasons for doing so, or issuing every query to this database in this way.
Click the expand (…) button on the Properties section of the Datasource configuration.
A dialog opens that allows you to add parameters to the datasource connection.
Add 2 parameters here, of type SQL, so that upon datasource connection, these are executed by BI+.
http://www.seemoredata.com/en/../wiki/_media/tips:lockds2.gif (http://www.seemoredata.com/en/../wiki/_detail/tips:lockds2.gif?id=tips%3Asql_server_locking) Select SQL form the dropdown list of Property Parameter Types.
In the NAME sub-property, enter: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Create a 2nd parameter, of type SQL with the property of SET NOCOUNT ON
http://www.seemoredata.com/en/../wiki/_media/tips:lockds3.gif (http://www.seemoredata.com/en/../wiki/_detail/tips:lockds3.gif?id=tips%3Asql_server_locking)
Now click OK and then test the datasource connection. If you have mistyped the parameter, Sql Server will return an error, telling you it does not understand the parameter value. If you get Datasource Connection is Successful then you have succeeded.
A quick look at the server log after making the datasource connection test reveals that the commands were executed in serial, successfully.
http://www.seemoredata.com/en/../wiki/_media/tips:lockds4.gif (http://www.seemoredata.com/en/../wiki/_detail/tips:lockds4.gif?id=tips%3Asql_server_locking)