Locking

I've got the following stored procedure I'm working on:
-- =============================================
-- Create procedure postCash
-- =============================================
USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?
Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================
> -- Create procedure postCash
> -- =============================================
> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>
|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.

> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.

>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.

Lock Resource

I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?
It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?

Lock 'Childreen' Table

Hi!

I'm new in SQL and I′ve been the problem bellow:

I lock a record in one foreign table and automaticaly SQL lock the record that matches on primary table.

But this occours just in some primaries tables and not in all. I need that just the table that are of SELECT are lock. How can I do this?

Example:

** At open of the invoice:

SET ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

SELECT * FROM INVOICE WITH (ROWLOCK UPLOCK) WHERE ( ID = 15 )

******* at the end of invoice:

INSERT INTO INVOICE ........

COMMIT TRANSACTION

END

******* The relationship are:

INVOICE <>> ITENS_INVOICE

ITENS_INVOICE <>> PRODUCTS

CUSTOMER <>> INVOICE

VENDORS <>> INVOICE

Just the INVOICE and PRODUCTS record′s involved in Transaction are lock. ( The CUSTOMER and VENDORS are not locked for example )

But I need that just INVOICE record be locked.

Thank′s for all and sorry my English.

Igor Sane

S?o Paulo - Brazil

PS: This doesn′t occours in SQL Express, just in SQL Server 2005....

location of ODBC drivers for SQL 2k5 x64 in Win Server 2003 x64 ST

Hi,
I've got Win Server 2003 Standard 64bit edition trying to work with SQL 2005
standard 64bit edition however there is a problem.
I need to locate the ODBC driver in Win Ser.
I know where they are in the 32bit edition but not in the 64bit version.
can anyone help?
Thanks
Olgun.Ucurel@.ccsmedia.com
Is there a 64 bit ODBC Driver? I didnt think that there was. You might have
to compile your application in x86 or if you are using SSIS or DTS exec you
can use this post...
http://forums.microsoft.com/MSDN/Sho...03814&SiteID=1
Can you use the OLE DB Driver?
C:\WINDOWS\SysWOW64\odbcad32.exe
uses...
odbc32.dll
and
C:\WINDOWS\System32\odbcad32.exe
uses
odbc32.dll
I am sure others will have some thoughts as well.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Olgun" <Olgun@.discussions.microsoft.com> wrote in message
news:9BA59FAC-D013-4BF4-9700-A97D71D65545@.microsoft.com...
> Hi,
> I've got Win Server 2003 Standard 64bit edition trying to work with SQL
> 2005
> standard 64bit edition however there is a problem.
> I need to locate the ODBC driver in Win Ser.
> I know where they are in the 32bit edition but not in the 64bit version.
> can anyone help?
> Thanks
> Olgun.Ucurel@.ccsmedia.com
|||Olgun wrote:
> Hi,
> I've got Win Server 2003 Standard 64bit edition trying to work with SQL 2005
> standard 64bit edition however there is a problem.
> I need to locate the ODBC driver in Win Ser.
> I know where they are in the 32bit edition but not in the 64bit version.
> can anyone help?
> Thanks
> Olgun.Ucurel@.ccsmedia.com
As far as I know the ODBC driver is not working on 64 bits Windows. You
could use OLEDB instead, which is working fine.
Regards,
lucm

localhost, local, . question

Do you know the difference between
(a)localhost
(b) local
(c) .
I've heard that they use different network layers but can't get any info on it.
TIA,
FS
This is my understanding:
Localhost is resolved by the IP stack.
(local) is resolved by the netlib, which means that it can use any network protocol (NetBEUI, IP, IPX etc)
I recall someone mentioning that . will only be resolved through Named Pipes. Not sure about this, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C182FD96-9B7D-42F8-9274-CBA2D56464A7@.microsoft.com...
> Do you know the difference between
> (a)localhost
> (b) local
> (c) .
> I've heard that they use different network layers but can't get any info on it.
> TIA,
> FS
>
|||localhost uses TCPIP. It just resolves to the loop back address
(127.0.0.1). Try "ping localhost" from the command prompt.
(local) and . use a protocol called shared memory and does not use any
network protocols.
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C182FD96-9B7D-42F8-9274-CBA2D56464A7@.microsoft.com...
> Do you know the difference between
> (a)localhost
> (b) local
> (c) .
> I've heard that they use different network layers but can't get any info
on it.
> TIA,
> FS
>

local or sql tables?

I have Access XP and SQL 2000. I'm doing tests for my
upsizing to SQL. I've read that I should have tables in
my Access FE for data that don't change much. Like
tblCity, tblEmployee and such. However, I do have a
question on tables that don't get changed much, but are
linked to other tables.
For instance, tblEmployee only change if there is someone
new to the company (not often do I change that).
tblProvider is similar to tblEmployee where I only add if
we get a new Provider (which can happen once a yr or two).
These two tables do have one-many relationships to other
tables. tblProvider has a one-many relationship to two
tables. tblEmployee has a link (not a one-many) to the
tblIncident that house incidents for an employee.
Should these two tables be local because they dont get
changed much or be SQL linked tables because they have a
relationship?
Being a SQL Server guy and wanting to make sure that ALL the data is
properly maintained, primary/foreign keys are in place so that all the data
that we expect to be there exists, the appropriate security exists, and
proper database backups are taken....
I would put everything in SQL Server.
Keith
"ngan" <anonymous@.discussions.microsoft.com> wrote in message
news:494f01c4a180$6b0804f0$a501280a@.phx.gbl...
> I have Access XP and SQL 2000. I'm doing tests for my
> upsizing to SQL. I've read that I should have tables in
> my Access FE for data that don't change much. Like
> tblCity, tblEmployee and such. However, I do have a
> question on tables that don't get changed much, but are
> linked to other tables.
> For instance, tblEmployee only change if there is someone
> new to the company (not often do I change that).
> tblProvider is similar to tblEmployee where I only add if
> we get a new Provider (which can happen once a yr or two).
> These two tables do have one-many relationships to other
> tables. tblProvider has a one-many relationship to two
> tables. tblEmployee has a link (not a one-many) to the
> tblIncident that house incidents for an employee.
> Should these two tables be local because they dont get
> changed much or be SQL linked tables because they have a
> relationship?
|||is there any time you want to have local tables? like
temp tables?
Thanks for your input.
One more question:
When I did the upsizing, it made triggers for the
relationships, instead of DRI. Is it just best to
recreate the SQL tables and re-do the upsizing to use
DRI? Or is there a way to change the trigger to DRI on
the existing tables?
Ngan
>--Original Message--
>Being a SQL Server guy and wanting to make sure that ALL
the data is
>properly maintained, primary/foreign keys are in place so
that all the data
>that we expect to be there exists, the appropriate
security exists, and
>proper database backups are taken....
>I would put everything in SQL Server.
>--
>Keith
>
>"ngan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:494f01c4a180$6b0804f0$a501280a@.phx.gbl...
someone[vbcol=seagreen]
if[vbcol=seagreen]
two).
>.
>
|||What do you mean by "temp" tables? Temp tables are a concept within SQL
Server...these tables go away when the command that created them goes away.
I suspect that you mean temp working table...but I am not sure
how/why/where they would be used so I do not feel that I should say one way
or the other.
Yes, I would go for the PK/FK approach using DRI instead of the trigger
approach.
Keith
"Ngan" <anonymous@.discussions.microsoft.com> wrote in message
news:278701c4a196$ffbded60$a601280a@.phx.gbl...[vbcol=seagreen]
> is there any time you want to have local tables? like
> temp tables?
> Thanks for your input.
> One more question:
> When I did the upsizing, it made triggers for the
> relationships, instead of DRI. Is it just best to
> recreate the SQL tables and re-do the upsizing to use
> DRI? Or is there a way to change the trigger to DRI on
> the existing tables?
> Ngan
> the data is
> that all the data
> security exists, and
> message
> someone
> if
> two).
|||I was just trying to figure out when you would store a
table within the FE file. In any case, i have moved all
the tables to SQL like you suggested because of the
security and integrity issue...also because if I create a
website for this, I will need those other tables. so in
the end SQL will have to have all the tables.
Thanks for your help.
Ngan

>--Original Message--
>What do you mean by "temp" tables? Temp tables are a
concept within SQL
>Server...these tables go away when the command that
created them goes away.
>I suspect that you mean temp working table...but I am
not sure
>how/why/where they would be used so I do not feel that I
should say one way
>or the other.
>Yes, I would go for the PK/FK approach using DRI instead
of the trigger
>approach.
>--
>Keith
>
>"Ngan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:278701c4a196$ffbded60$a601280a@.phx.gbl...
ALL[vbcol=seagreen]
so[vbcol=seagreen]
my[vbcol=seagreen]
tables in[vbcol=seagreen]
are[vbcol=seagreen]
add[vbcol=seagreen]
other[vbcol=seagreen]
two[vbcol=seagreen]
the[vbcol=seagreen]
get[vbcol=seagreen]
have a
>.
>
|||Having done some Access/SQL Server development, I'll throw in my 2 cents.
If you have split your Access application, so all forms, reports and code
live on the client workstation. Then use either code and/or linked tables
pointing back to the data on SQL Server. It is perfectly acceptable to use
local tables in Access to store more static data. Typically you need to code
routines, that on application startup determine if the local table data
needs to be refreshed.
Steve
"Ngan" <anonymous@.discussions.microsoft.com> wrote in message
news:297001c4a1b1$8cc6d220$a601280a@.phx.gbl...[vbcol=seagreen]
> I was just trying to figure out when you would store a
> table within the FE file. In any case, i have moved all
> the tables to SQL like you suggested because of the
> security and integrity issue...also because if I create a
> website for this, I will need those other tables. so in
> the end SQL will have to have all the tables.
> Thanks for your help.
> Ngan
> concept within SQL
> created them goes away.
> not sure
> should say one way
> of the trigger
> message
> ALL
> so
> my
> tables in
> are
> add
> other
> two
> the
> get
> have a

Local Database Issue with SQL 2000 Install

I've got a user with a new HP desktop requesting SQL 2000 Developer Server
and Client Tools to be installed. When I install the software along with SQL
SP4, the local database shows up in Enterprise Manager. When the user logs
on to the same machine, the local database is missing in Enterprise Manager.
I had another user with the same type of permissions log on to the machine
and they get the local database to show up as well.
Why can't this particular person get the local database to show up in
Enterprise Manger when they log on? Is there a registry edit that's needed?
Any help is greatly appreciated.
Colette
Hi Colette,
Perhaps it's been removed. Have they tried just registering the server in
Enterprise Manager? (ie: right-click the SQL Servers group in the main tree
and choose the option to register a server)
HTH,
Greg
"Colette" <Colette@.discussions.microsoft.com> wrote in message
news:084583C7-EA4C-4BC5-8EA1-1D0F448FDA32@.microsoft.com...
> I've got a user with a new HP desktop requesting SQL 2000 Developer Server
> and Client Tools to be installed. When I install the software along with
> SQL
> SP4, the local database shows up in Enterprise Manager. When the user
> logs
> on to the same machine, the local database is missing in Enterprise
> Manager.
> I had another user with the same type of permissions log on to the machine
> and they get the local database to show up as well.
> Why can't this particular person get the local database to show up in
> Enterprise Manger when they log on? Is there a registry edit that's
> needed?
> Any help is greatly appreciated.
> Colette

Local and Remote Index Servers returning different resultsets

I have a project that uses a linked server in SQL Server 2000 to query a
local Index Server. This works fine.
I've been experimenting with moving the catalog to a remote Index Server and
querying it via Index Server running on the SQL Server machine (using the
four-part SCOPE syntax). I've followed plenty of other pointers relating to
syntax and security and seem to be getting there, but have noticed some
strange behaviour.
I seem to be getting a different number of results returned from the local
and remote index servers when I query them through Query Analyzer. My test
queries (which run over a catalog of about 12,000 documents) are very
simple:
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope()')
This returns 12,012 results from NTS-1 (where the Index Server actually is)
and only 9,915 results when run from NTS-2 (where the SQL Server instance
and linked server are set up).
As soon as I add a CONTAINS clause:
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope() where contains(''microsoft'')')
...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the search
term makes no difference - no results (or errors) are returned from NTS-2 as
soon as I add the CONTAINS clause.
The two machines are setup very similarly - two Win2K Servers with SQL
Server 2000 Ent. Ed. Could the database and collation settings of the linked
server be acting up? Does anyone know what this strangeness is due to? I
seem to be passed all the permissions problems but this one is weird.
Thanks,
Alan
NZ! I used to live in New Plymouth!
results are trimmed by security and scope. You have to verify that
1) you are using the same catalogs
2) the account you are querying with in SQL Server is present on the remote
server and on both servers they have identical rights
3) the content is the same on both servers
4) the default scope (\) is the same on both servers. To verify this open up
ciadv.msc and expand your default catalog. expand the directories folder and
ensure the directories which show up there are the same and they are both
physical, ie don't have globes on them
5) when you are not doing a contains, you are basically enumerating the file
system on the local and remote servers. If AllowEnumeration is turned off,
this will account for this behavior
6) try something like this
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope(''shallow traversal of ""c:\""'')')
thats scope ( ' ' deep traversal of " " c:\ " " ' ' ) ' )
I think your problems are due to different content and probably that the
index is not finished building on one or more servers.
BTW - are all IS servers on the same version? IE is one on IS 2 (NT 4.0) and
the other on IS 3.x?
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:%23iKyiIYaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> I have a project that uses a linked server in SQL Server 2000 to query a
> local Index Server. This works fine.
> I've been experimenting with moving the catalog to a remote Index Server
and
> querying it via Index Server running on the SQL Server machine (using the
> four-part SCOPE syntax). I've followed plenty of other pointers relating
to
> syntax and security and seem to be getting there, but have noticed some
> strange behaviour.
> I seem to be getting a different number of results returned from the local
> and remote index servers when I query them through Query Analyzer. My test
> queries (which run over a catalog of about 12,000 documents) are very
> simple:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope()')
> This returns 12,012 results from NTS-1 (where the Index Server actually
is)
> and only 9,915 results when run from NTS-2 (where the SQL Server instance
> and linked server are set up).
> As soon as I add a CONTAINS clause:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope() where contains(''microsoft'')')
> ...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the
search
> term makes no difference - no results (or errors) are returned from NTS-2
as
> soon as I add the CONTAINS clause.
> The two machines are setup very similarly - two Win2K Servers with SQL
> Server 2000 Ent. Ed. Could the database and collation settings of the
linked
> server be acting up? Does anyone know what this strangeness is due to? I
> seem to be passed all the permissions problems but this one is weird.
> Thanks,
> Alan
>
|||Hi Alan,
can you share the "..plenty of other pointers relating to syntax and security.."
to set up the configuration you used: Index Server and SQLServer on different
machines ?
Many guys (including me..) posted in this group questions on the argument, but
got no aswer..
Many THX,
Max
"Alan Howard" wrote:

> I have a project that uses a linked server in SQL Server 2000 to query a
> local Index Server. This works fine.
> I've been experimenting with moving the catalog to a remote Index Server and
> querying it via Index Server running on the SQL Server machine (using the
> four-part SCOPE syntax). I've followed plenty of other pointers relating to
> syntax and security and seem to be getting there, but have noticed some
> strange behaviour.
> I seem to be getting a different number of results returned from the local
> and remote index servers when I query them through Query Analyzer. My test
> queries (which run over a catalog of about 12,000 documents) are very
> simple:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope()')
> This returns 12,012 results from NTS-1 (where the Index Server actually is)
> and only 9,915 results when run from NTS-2 (where the SQL Server instance
> and linked server are set up).
> As soon as I add a CONTAINS clause:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope() where contains(''microsoft'')')
> ...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the search
> term makes no difference - no results (or errors) are returned from NTS-2 as
> soon as I add the CONTAINS clause.
> The two machines are setup very similarly - two Win2K Servers with SQL
> Server 2000 Ent. Ed. Could the database and collation settings of the linked
> server be acting up? Does anyone know what this strangeness is due to? I
> seem to be passed all the permissions problems but this one is weird.
> Thanks,
> Alan
>
>