August 1, 2014 at 10:39 am
I ran the following query:
DECLARE @tsql VARCHAR(8000)
SET @tsql = '
SELECT
E.UCELL_ID As LocalCellID
,RNC As RNC
,Cast( Cast(YEAR_ID As varchar(4)) + ''-'' + Cast(MONTH_ID As varchar(2)) + ''-'' + Cast(DAY_ID As varchar(2)) + '' '' + Cast(HOUR_ID As varchar(2)) + '':00:00'' As datetime) As Start_Time
,Cast( Cast(YEAR_ID As varchar(4)) + ''-'' + Cast(MONTH_ID As varchar(2)) + ''-'' + Cast(DAY_ID As varchar(2)) + '' '' + ''00:00:00'' As datetime) As Start_Date
,RBS As RbsUtranId
,H.Sector As Sector
,DCVECTOR_INDEX As DCVECTOR_INDEX
,MIN(H.DC_RELEASE) as DC_RELEASE
,SUM(pmAverageRssi) As pmAverageRssi
,SUM(pmTransmittedCarrierPower) As pmTransmittedCarrierPower
FROM DWHDB.dc.DC_E_RBS_CARRIER_V_RAW H
INNER Join ( -- e
SELECT DISTINCT
RBS_ID,
Ucell_ID,
RNC_ID,
LOCALCellID
FROM DWHDB.DC.DIM_E_RAN_UCELL
WHERE STATUS = ''ACTIVE''
) e
ON H.RBS = E.RBS_ID
And H.RNC = E.RNC_ID
And E.LOCALCellID = H.Sector
WHERE ROWSTATUS = ''LOADED''
AND DATE_ID = CAST(DATEADD(D, -1, GETDATE()) AS DATE)
GROUP BY e.UCELL_ID,RBS,H.Sector,DCVECTOR_INDEX,YEAR_ID,MONTH_ID,DAY_ID,HOUR_ID,H.DC_RELEASE,RNC'
PRINT @tsql
SELECT Top 100 * FROM OPENQUERY (ENIQSQLSERVER, '@tsql') Hourly
which gave me the following error:
OLE DB provider "SAOLEDB.11" for linked server "ENIQSQLSERVER" returned message "Procedure '@tsql' not found".
Msg 7321, Level 16, State 2, Line 37
An error occurred while preparing the query "@tsql" for execution against OLE DB provider "SAOLEDB.11" for linked server "ENIQSQLSERVER".
August 1, 2014 at 10:45 am
Take the @Tsql variable out of single quotes.
SELECT Top 100 * FROM OPENQUERY (ENIQSQLSERVER, @tsql) Hourly
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 1, 2014 at 10:59 am
OPENQUERY does not allow variables, only literal/static queries.
Try code below instead.
--temporarily add "TOP (100)" to the query for testing purposes
SET @tsql = STUFF(@tsql, CHARINDEX('SELECT', @tsql) + 6, 0, ' TOP (100) ')
PRINT @tsql
EXEC(@tsql) AT [ENIQSQLSERVER]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 4, 2014 at 1:50 am
ScottPletcher (8/1/2014)
OPENQUERY does not allow variables, only literal/static queries.Try code below instead.
--temporarily add "TOP (100)" to the query for testing purposes
SET @tsql = STUFF(@tsql, CHARINDEX('SELECT', @tsql) + 6, 0, ' TOP (100) ')
PRINT @tsql
EXEC(@tsql) AT [ENIQSQLSERVER]
Not my day, thank you for the reminder, Scott.
SELECT Top 100 *
FROM OPENQUERY (ENIQSQLSERVER, '
SELECT
E.UCELL_ID As LocalCellID
,RNC As RNC
,Cast( Cast(YEAR_ID As varchar(4)) + ''-'' + Cast(MONTH_ID As varchar(2)) + ''-'' + Cast(DAY_ID As varchar(2)) + '' '' + Cast(HOUR_ID As varchar(2)) + '':00:00'' As datetime) As Start_Time
,Cast( Cast(YEAR_ID As varchar(4)) + ''-'' + Cast(MONTH_ID As varchar(2)) + ''-'' + Cast(DAY_ID As varchar(2)) + '' '' + ''00:00:00'' As datetime) As Start_Date
,RBS As RbsUtranId
,H.Sector As Sector
,DCVECTOR_INDEX As DCVECTOR_INDEX
,MIN(H.DC_RELEASE) as DC_RELEASE
,SUM(pmAverageRssi) As pmAverageRssi
,SUM(pmTransmittedCarrierPower) As pmTransmittedCarrierPower
FROM DWHDB.dc.DC_E_RBS_CARRIER_V_RAW H
INNER Join ( -- e
SELECT DISTINCT
RBS_ID,
Ucell_ID,
RNC_ID,
LOCALCellID
FROM DWHDB.DC.DIM_E_RAN_UCELL
WHERE STATUS = ''ACTIVE''
) e
ON H.RBS = E.RBS_ID
AND H.RNC = E.RNC_ID
AND E.LOCALCellID = H.Sector
WHERE ROWSTATUS = ''LOADED''
AND DATE_ID = CAST(DATEADD(D, -1, GETDATE()) AS DATE)
GROUP BY e.UCELL_ID,RBS,H.Sector,DCVECTOR_INDEX,YEAR_ID,MONTH_ID,DAY_ID,HOUR_ID,H.DC_RELEASE,RNC'
) Hourly
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 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply