Use of OPENDATASOURCE and NOLOCK

[glthornton]

Hi everyone,
Configuration: MS SQL 2000 using Server 2003. I am trying to design a query that will allow me to access different databases using the OPENDATASOURCE command but also using the WITH (NOLOCK) command as well. I know that using NOLOCK is using a 'dirty read', but it's okay with what I'm doing. My query looks like this:

SELECT DISTINCT PatID
FROM OPENDATASOURCE('SQLOLEDB','Data Source=VTMAIM;User ID=pcadmin;Password=letmein').scandb.dbo.ScanDocs WITH (NOLOCK)
WHERE DocType = 'Consults - Eye' and ScanDate >= DateAdd(day, DateDiff(Day, 0, GetDate()-1), 0) AND ScanDate < DateAdd(day, DateDiff(Day, 0, GetDate()), 0)

When I try and check/parse the query in query analyzer, I get an error stating: 'incorrect syntax near the keyword WITH'.

I want to use both commands, but it does not seem to be working. Does anyone have any suggestions?

Glenn

 

[macleod1021]

You can't use lock hints with opendatasource. You can however use openquery if it's a sql server your connecting to.

Something like:

SELECT *
FROM OPENQUERY(SELECT DISTINCT PatID FROM scandb.dbo.ScanDocs WITH (NOLOCK)
WHERE DocType = 'Consults - Eye' and ScanDate >= DateAdd(day, DateDiff(Day, 0, GetDate()-1), 0) AND ScanDate < DateAdd(day, DateDiff(Day, 0, GetDate()), 0))

HTH