Something like the following should be more efficient as the table will only be scanned once:

-- Test Data

DECLARE @t TABLE

(

 TID INT NOT NULL PRIMARY KEY

 ,TDate1 DATETIME NULL

 ,TDate2 DATETIME NULL

 ,TDate3 DATETIME...

In SQL2000, I do not think defaults are allowed to refer to other columns in the table.

You may be able to get away with this in SQL2005.

-- Test Data

DECLARE @t TABLE

(

 TID CHAR(5) COLLATE DATABASE_DEFAULT NOT NULL

 ,TYPE CHAR(3) COLLATE DATABASE_DEFAULT NOT NULL

)

INSERT @t

SELECT 'KL202', 'JPJ' UNION ALL

SELECT 'KL202', 'JPJ' UNION ALL

SELECT 'KL202', 'JPN' UNION ALL

SELECT 'KL202', 'JIM'...

I read up on this last night and think that UPDLOCK should work no matter how much contention there is. The UPDLOCK should ensure that no other...

On considering this again, it may be safer to use the UPDLOCK hint. eg:

UPDATE A1

SET

 Tagged = 1,

 WorkStart = GETDATE(),

 UserCode = @UserCode,

 @UseIt1 = A1.AcctNo

FROM _FinalFlat (UPDLOCK) A1

INNER JOIN

(

 SELECT TOP 1 A.AcctNo

 FROM...

You are probably using the default isolation level of Read Committed. Try either setting the isolation level to Repeatable Read or using the RepeatableRead locking hint on the table to...

Still do not really have enough information, but something like this should get you started:

UPDATE R

-- This will set all the users RoleID to 7

SET RoleID = 7

-- If there...

It is not clear what you are trying to achieve.

You will need to post:

1. the DDL (CREATE TABLE statements) for the LOCATION, USERS a USERROLES tables.

2. some sample data for...

You could try returning:- SUM(CAST(dom-amount * 100 AS INT)) AS dom-amount

from the remote query and then dividing by 100.0

You need to be careful with the second example as it is not portable to other db systems. (eg Oracle.)

You need to use the 3 part naming convention: - <database>.<owner>.<table>

Something like the following should work:

-- ANSI SQL

UPDATE db1.dbo.People

SET [password] =

 (SELECT P2.[password]

 FROM db2.dbo.People P2

 WHERE P2.people_id = db1.dbo.People.people_id)

WHERE EXISTS

 (SELECT *

 FROM db2.dbo.People...

It could be an issue with the linked server not being MS-SQL as the following fails as expected:

DECLARE @rc INT

EXEC @rc = sp_executesql N'select * from...

The ISO date format is probably best for this; so something like:

DECLARE @DiskStr NVARCHAR(255)

SET @DiskStr = N'C:\CollSoftware2_' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N'.bak'

BACKUP DATABASE [CollSoftware2]

TO  DISK = @DiskStr

WITH  INIT...

You will have to use dynamic SQL.

exec('SELECT xx FROM ' + @Variable)

Declare @Memberid as varchar (50)

Declare @ProcCode as Varchar (15)

set @memberid = '-1'

Set @ProcCode = 'G0202'

Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible

where Membid = COALESCE(NULLIF( @memberid, '-1'), Membid)

and ProcCode =...