July 12, 2011 at 1:09 pm
I have the following openquery I need to set a variable for to process.
THis below returns the value
select * FROM OPENQUERY(SDW, 'SELECT count(*) FROM PHARMADM.PHARM_HRXS where POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')')
GO
I tried
declare @cnt int
SEt @cnt = select * FROM OPENQUERY(SDW, 'SELECT count(*) FROM PHARMADM.PHARM_HRXS where POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')')
GO
July 12, 2011 at 1:18 pm
This should do what your looking for:
declare @cnt int
SELECT @cnt = COUNT(*) FROM SDW.PHARMADM.PHARM_HRXS where POSTED > to_date('07/11/2011' ,'mm/dd/yyyy'))
select @cnt
July 12, 2011 at 1:33 pm
can't do that have to use openquery or it will ignore indexes
July 12, 2011 at 1:36 pm
Then what is the purpose for the variable? A little more info in what your trying to do would help.
July 12, 2011 at 1:42 pm
Then try this should work for OpenQuery:
declare @cnt int
select @cnt = cnt FROM OPENQUERY(SDW, 'SELECT count(*) as cnt FROM PHARMADM.PHARM_HRXS where POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')')
select @cnt
July 12, 2011 at 1:43 pm
Try this form (changes to your original query bolded):
DECLARE @cnt INT
SELECT @cnt = cnt
FROM OPENQUERY(SDW, 'SELECT count(*) as cnt
FROM PHARMADM.PHARM_HRXS
where POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')')
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 1:47 pm
opc
Its deja vu.
July 12, 2011 at 2:00 pm
That's scary, we even picked the same alias and variable names :blink:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 19, 2015 at 8:56 am
Does anyone have any idea how to do this when the remote query contains an unknown (i.e. a variable) for the "to_date" instead of "07/11/2011" (which I presume is 7th Nov 2011 !)?
You would need to execute the whole query as a string and that puts the local variable out of scope.
The only thing I can think of is to create a temporary table and populate it using sp_executesql where the string contains the OPENQUERY query.
March 20, 2015 at 3:13 am
Kelvin Lush (3/19/2015)
Does anyone have any idea how to do this when the remote query contains an unknown (i.e. a variable) for the "to_date" instead of "07/11/2011" (which I presume is 7th Nov 2011 !)?You would need to execute the whole query as a string and that puts the local variable out of scope.
The only thing I can think of is to create a temporary table and populate it using sp_executesql where the string contains the OPENQUERY query.
Using the previous example code as the starting point, reworking yourself into a pattern like this might help you and should keep you working in declared-SQL:
DECLARE @results TABLE (cnt INT);
DECLARE @sql NVARCHAR(MAX) =
N'SELECT count(*) as cnt
FROM PHARMADM.PHARM_HRXS
WHERE POSTED > to_date(''07/11/2011'' ,''mm/dd/yyyy'')';
INSERT INTO @results (cnt)
EXEC(@sql) AT [SDW];
* Note this code is untested because I do not have an environment available at the moment where I can use a Linked Server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 20, 2015 at 5:17 am
-- Tested and working
DECLARE @Statement VARCHAR(1000), @Year SMALLINT, @AgencyCode VARCHAR(5)
SELECT @Year = 2014, @AgencyCode = '57LHS'
SET @Statement = '
SELECT
[Complaint Number] = QNUMBM,
[Complaint Date] = QCMPDM,
[Complaint Reason] = QCRSNM
FROM OPENQUERY(
[AS400_DB2],
''SELECT QNUMBM, QCMPDM, QCRSNM
FROM QMPLNT
WHERE QCMPDM BETWEEN ' + CAST(@Year*10000 AS CHAR(8)) + ' AND ' + CAST((@Year+1)*10000 AS CHAR(8)) + '
AND QAGYCD = ''''' + @AgencyCode + '''''
AND QCSQNM = 1''
)';
--PRINT @Statement -- visual syntax check
IF OBJECT_ID ('tempdb..#Complaints') IS NOT NULL DROP TABLE #Complaints;
CREATE TABLE #Complaints ([Complaint Number] INT, [Complaint Date] INT, [Complaint Reason] VARCHAR(6));
INSERT INTO #Complaints ([Complaint Number], [Complaint Date], [Complaint Reason])
EXEC(@Statement);
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy