spidey73 (1/26/2011)

---

I've got two machines:

Server A: SQL 2008: contains the database on which the query is executed.

Server B: SQL 2005: the server from which the query is executed,

When I...

I would be interested in knowing why you need to do this. It...

To stop the race condition you will need to apply a key range lock on Jobs by making the select serializable.

(If this still gives problems add the update lock as...

There is a large section on Service Broker in BOL (Books Online).

You can get the same effect by creating a QUEUE and using Service Broker.

Maybe you want something like:

WHERE [state] =

CASE

WHEN @statefilter = 2

THEN 'CA'

WHEN @statefilter = 3 AND [state] = 'CA'

THEN 'ZZ' -- junk value

ELSE [state]

END

Could be network problems.

I would be inclined to to the backups onto a local share and then have them copied to the other server.

To pass a table variable you have to create a type and define the table as the type.

What you are trying to do will not work as table variables can...

You appear to selecting not merging.

Try something like:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.GetTblGUID

@Address nvarchar(255)

,@GUID uniqueidentifier OUTPUT

AS

DECLARE @GUIDs TABLE

(

[GUID] uniqueidentifier NOT NULL

);

INSERT INTO Tbl

OUTPUT inserted.[GUID] INTO @GUIDs

SELECT NEWID(), @Address

WHERE NOT EXISTS

(

SELECT...

You may be best to do it all in one statement to avoid potential concurrency problems.

Something like:

;WITH DivRows

AS

(

SELECT RowNo, Division, UserName

...

Add computed columns:

ALTER TABLE dbo.PCList

ADD PCNamePreFix

AS

(

CAST

(

CASE PATINDEX('%[0-9]%', PCName)

WHEN 1 THEN ''

WHEN 2 THEN LEFT(PCName, 1)

WHEN 3 THEN LEFT(PCName, 2)

ELSE LEFT(PCName, 3)

END

AS varchar(3)

)

) --PERSISTED

, PCNameNumber

AS

(

CAST

(

CASE PATINDEX('%[0-9]%', PCName)

WHEN 0 THEN...

You also have to apply a key range...

Try:

SELECT usernumber

FROM tblaccess

WHERE readernumber IN (1,2,3,4,5,6)

GROUP BY A.usernumber

HAVING

-- Max entry accessid (assuming accessid is an IDENTITY. If not use accessdatetime.

MAX(CASE WHEN readernumber IN (1,2,3) THEN A.accessid ELSE 0 END)

...

Assuming your DDL is something like:

CREATE TABLE tblaccess

(

accessid int IDENTITY NOT NULL

PRIMARY KEY

,accessdatetime datetime

,usernumber int NOT NULL

,readernumber int NOT NULL

)

CREATE TABLE readernumbers

(

readernumber int NOT NULL

PRIMARY KEY

,readerSet varchar(5)

CHECK (readerSet IN ('entry',...

If the table is heavily used, you might have problems in obtaining a schema lock to do the sp_rename.

You may have to look at doing an upsert and changing the...