Lock Type

Hello there,

Example

I pass this statement to the database:

SELECT FunctionName,FunctionDescription FROM Functions WHERE FunctionID = 21

Nothing special here.

Now i check in the Enterprise Manager in the Managment->Current Activity->Locks/Process ID Map and see this for my process

Object: LISPROD
Lock Type: DB
Mode: S
Status: Grant
Owner Sess
Index:
Resource:

My question now, is the LockType. Isn't this a bit too much. A full database lock while the query is only on one table.
All locks in the database seem to have this behaviour.

Cheers Erik.The lock type u see in
Enterprise Manager in the
Managment->Current Activity->Locks/Process ID

is DB...means database level locking.

Its not bacause u have fired a query, but Quey analyzer locks it as soon as u select a database form the drop down combo...

When we generally fire a query DB lavel locking is not used but Table level locking is used.

u can change it to page lavel or row level locking if required...but that approach has its own implications.

Naveen Mehta.

Local VB.NET app connection to remote SQL server

I know this is strickly not a website question, but dunno where else to post...

To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting server

I have the following code:

Dim StrSQLUNAs String ="[UN]"Dim StrSQLPWAs String ="[PW]"Dim StrServerAs String ="[IP]\[INSTANCE]"Dim StrDBAs String ="[DB]"Dim strTimeOutAs String ="Connection Timeout=0;"Dim pStrSQLConnAs String ="Server=" & StrServer &";Database=" & StrDB &";User Id=" & StrSQLUN &";Password=" & StrSQLPW &";" & strTimeOutDim sqlConnAs New SqlClient.SqlConnection(pStrSQLConn)If sqlConn.State = ConnectionState.ClosedThen sqlConn.Open()

This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.

I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct?

Can they put restrictions in place for this specific sort of data access?

Does anyone have any suggestions on how to resolve this issue??

Thanks for any help

Adam.

does the new server allow external connections? A lot of hosts I've used in the past only allow you to connect to the SQL database from an Asp.Net app on their webservers.

|||

Yeah it does allow external access as I am using MSSQL Mgmt Studio.

Anyway I think I have found the cause of the problem - my Cisco router. I needed to put in IP Inspect rules in for the MSSQL. Thing that confuses me still is that I could use the Mgnt Studio and I thought it would be accessing it in the same way. Anyway - it still seems to work - albeit with random connection times (eg last night it was connecting in a second or to and now taking about 5mins!)

Anyway have experience in this area??

Thanks

Adam.

Local Variables in User Defined Functions

I'm having a problem declaring variables in UDFs. Are they allowed? Can someone send me some syntax to see what I am doing wrong?You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.|||I am using a table function with the following syntax:

create function dbo.TestFunction (@.InputVariable int)
returns table
with encryption
as

declare @.LocalVariable smalldatetime

select @.LocalVariable = ABCDate from ABCTable where rowid = @.InputVariable

return(
select * from XYZTable where XYZDate < @.LocalVariable
)

Any thoughts?

Originally posted by rdjabarov
You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.|||It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @.tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.|||gtocha...thanks

Originally posted by rdjabarov
It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @.tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.