Solved

Get value from Openquery statement

Posted on 2011-09-21
11
946 Views
Last Modified: 2012-05-12
I figured out how to pass a value to an openquery statement but now the problem I have is getting the results of the statment.  I decalred the @TSql but then when i try to print the @Req it gets nothing, no value, when I print the @TSql it print the whole sql statement.  how do I get the value from this.  I have many statments that are part of an Sp that does calcualtions  I need to get the @Req and @Asgn but it does not seemt o get them.


Select @TSql = 'Select @Req = AUTH From OPENQUERY(SIDPERS, ''Select SUM(AUTH) AUTH from MNLOCAL_ACN_AUTH where UPC =  ''''' + @Unit + ''''''')'


Select @TSql = 'Select @Asgn = ASGN From OPENQUERY(SIDPERS, ''Select SUM(COUNT_SSN) ASGN from MNLOCAL_ACN_ASGN a INNER JOIN MNLOCAL_ACN_AUTH aa on aa.upc = a.upc and aa.para = a.para and aa.line = a.line where aa.OS IS NULL AND a.UPC = ''''' + @Unit + ''''''')'

Open in new window

0
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36578052
You should be able to do this without dynamic SQL:

Select @Req = AUTH
From OPENQUERY(SIDPERS, 'Select SUM(AUTH) AUTH from MNLOCAL_ACN_AUTH where UPC =  ''' + @Unit + '''');

If you do use dynamic SQL, then you should use sp_executesql, passing in @Unit as an input variable and @Req as an output one. http://msdn.microsoft.com/en-us/library/ms188001.aspx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36580200
Unfortunately you cannot use variables with OPENQUERY(), which may explain why the author is resorting to using Dynamic SQL.
0
 

Author Comment

by:kdeutsch
ID: 36580445
All,
The above statement does not work as OPENQUERY does not accept variables.  In the end I used the following work around to get it to work but there must be a better way as I have to write 15 of these with the different counts.

Create table #tmpR(VAL float)
            SET @TSQL = 'Select * From OPENQUERY(SIDPERS, ''Select SUM(AUTH) AUTH from MNLOCAL_ACN_AUTH where UPC =  ''''' + @Unit + ''''''')'
            truncate table #tmpR
            Insert into #tmpR
            EXEC (@TSQL)
            Select @Req = VAL from #tmpR
            drop table #tmpR
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 9

Expert Comment

by:DrewKjell
ID: 36580505
You could also try not utilizing OPEN Query and just referencing the FQ name of the table on the linked server:

IE:

SELECT @Req = SUM(AUTH)
FROM SIDPERS.<DBNAME>.<SCHEMA>.MNLOCAL_ACN_AUTH
WHERE UPC = @Unit

SELECT @Req
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36580595
Thanks, Anthony! I never tried to use OPENQUERY with a variable before, so I was not aware of that restriction. Anyway, why did you not like the sp_executesql part of my suggestion, which would let you get the output variable w/o a temp table?

Additionally, how much overhead was there in doing a GROUP BY UPC in the open query, returning UPC as a column, and then filtering UPC in SQL?
0
 

Author Comment

by:kdeutsch
ID: 36580724
DrewKjell:

If I run witht the FQ server names it takes just one statement 12 seconds to run, openquery does it in a second, so add oon antoher 15 of these statements * running it in a loop for 150 different @Unit that need to be counted and it runs for quite a long time versus openquery statements.  If I hard code the variable it takes 8 seconds to run the whole thing in openquery.

mwvisa1:
I have approx 150 UPC that I cycle through to get the counts with 15 different statements that count and get percentages, Grouping it was a harder solution than trying to do what I am doing.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36580793
No problem. I was just curious.
0
 

Accepted Solution

by:
kdeutsch earned 0 total points
ID: 36580836
FYI here is the whole thing of what I am trying to do and gettting this value each time is a real pain but it still runs way faster than the FQ server name method.

Alter Proc sp_ReadinessReport
as
BEGIN

Declare @Unit as varchar(5), @UName as Varchar(100), @RSC as varchar(9), @Req as float, @Asgn as Float, @Pasgn as Float, @OS as Float, @OSA as float, 
		@POS as float, @TAsgn as Float, @PTAsgn as Float, @DMOSQ as Float, @PDOMSQ as Float, @Deploy as Float, @PDeploy as Float, @Dental as Float, 
		@Medical as Float, @Admin as Float, @DTReport as datetime, @TSQL as Varchar(max)

--Set date for all reports to same
Set @DTReport = GETDATE()

--Get all the units
Declare Strength CURSOR FOR Select UPC, UNAME, RPT_SEQ_CODE 
							from OPENQUERY(SIDPERS, 'Select UPC, UNAME, RPT_SEQ_CODE from PERS_UNIT_TBL where 
													 Length(RPT_SEQ_CODE) >= ''5'' AND OESTS IN (''N'', ''Q'')
													 AND Substr(UPC,1,3) <> ''8SF'' AND Substr(UPC,1,2) <> ''8V'' 
													 AND Substr(UPC,1,2) <> ''8N'' AND UPC NOT IN (''8AE02'', ''8AE03'', 
													 ''8AE04'', ''8AE07'')  Order by UPC')

	OPEN Strength

	FETCH NEXT FROM Strength INTO @Unit,@UName,@RSC
	 
	while @@FETCH_STATUS = 0 

	begin

		--Code to count Unit Readiness Issues.

		--1. Count to get the Auth Positions in Sidpers (REQ Field)		
		--Select * from OPENQUERY(SIDPERS, 'Select SUM(EAUTH) + SUM(OAUTH) + SUM(WOAUTH_STR) from PERS_UNIT_TBL where UPC = ''PJPT0''')
		Create table #tmpR(VAL float)
		SET @TSQL = 'Select * From OPENQUERY(SIDPERS, ''Select SUM(AUTH) AUTH from MNLOCAL_ACN_AUTH where UPC =  ''''' + @Unit + ''''''')'
		truncate table #tmpR
		Insert into #tmpR
		EXEC (@TSQL)
		Select @Req = VAL from #tmpR
		drop table #tmpR
		
		--2. Count the personnel in a unit in Sidpers (ASGN)
		--Select * from OPENQUERY(SIDPERS, 'Select SUM(ASGN_STR) from PERS_AUTH_STR_TBL where UPC = ''PJPT0'' and LENGTH(AUTH_LINE_DSG) = 2')
		Create table #tmpA(VAL float)
		SET @TSQL = 'Select Case When ASGN IS NULL then ''0'' else ASGN END From OPENQUERY(SIDPERS, ''Select SUM(COUNT_SSN) ASGN from MNLOCAL_ACN_ASGN a INNER JOIN MNLOCAL_ACN_AUTH aa on aa.upc = a.upc 
													 and aa.para = a.para and aa.line = a.line where aa.OS IS NULL AND a.UPC = ''''' + @Unit + ''''''')'
		truncate table #tmpA
		Insert into #tmpA
		EXEC (@TSQL)
		Select @Asgn = VAL from #tmpA
		drop table #tmpA


		--3. Find the percent of the ASGN via sql
		--Take the second query divided by the first query Times by 100
		If @Asgn > 0 AND @Req > 0
			SET @PAsgn = Ceiling((@Asgn / @Req) * 100)
		ELSE IF @Req = 0
			Set @PAsgn = 0

		--4. Count to get the OverStrength Positions in Sidpers  (OS)
		--Select * from OPENQUERY(SIDPERS, 'Select Count(AUTH_LINE_DSG) from PERS_AUTH_STR_TBL where UPC = ''PJPT0'' and LENGTH(AUTH_LINE_DSG) = 3')
		Create table #tmpO(VAL float)
		SET @TSQL = 'Select OS From OPENQUERY(SIDPERS, ''Select SUM(OS) OS from MNLOCAL_ACN_AUTH where UPC = ''''' + @Unit + ''''''')'
		truncate table #tmpO
		Insert into #tmpO
		EXEC (@TSQL)
		Select @OS = VAL from #tmpO
		drop table #tmpO


		--5. Count to get the overstrength of a unit in Sidpers (OS ASGN)
		--Select * from OPENQUERY(SIDPERS, 'Select SUM(ASGN_STR) from PERS_AUTH_STR_TBL where UPC = ''PJPT0'' and LENGTH(AUTH_LINE_DSG) = 3')
		Create table #tmpOS(VAL float)
		SET @TSQL =  'Select OSA From OPENQUERY(SIDPERS, ''Select SUM(COUNT_SSN) OSA from MNLOCAL_ACN_ASGN a INNER JOIN MNLOCAL_ACN_AUTH aa on aa.upc = a.upc 
													and aa.para = a.para and aa.line = a.line where aa.OS IS NOT NULL AND a.UPC = ''''' + @Unit + ''''''')'
		truncate table #tmpOS
		Insert into #tmpOS
		EXEC (@TSQL)
		Select @OSA = VAL from #tmpOS
		drop table #tmpOS
		
		--6. Find the percent of the OS via sql
		--Take the second query divided by the first query Times by 100
		If @OSA > 0
			SET @POS = Ceiling((@OSA / @OS) * 100)
		ELSE IF @OSA = 0
			Set @POS = 0
			
		--7. Find the Total Asgn of all Unit
		--Take the 2nd query  +  4th query
		SET @TAsgn = @Asgn + @OSA
		
		--8. Get the Total Percent Assigned
		IF @TAsgn > 0 
			SET @PTAsgn = Ceiling(@TAsgn / @Req * 100)
		ELse If @TAsgn = 0
			Set @PTAsgn = 0
		
		--9. Find the DMOSQ of Unit
		Select @DMOSQ = DMOSQ from OPENQUERY(SIDPERS, 'Select	Count(DY_POSN_QUAL) DMOSQ
											from	PERS_SVCMBR_TBL st LEFT JOIN
													PERS_SVCMBR_ATCHMT_TBL at on at.MPC = st.MPC AND at.ASG_SEQ_NBR = st.ASG_SEQ_NBR
											where	(st.UPC = ''@Unit'' or at.UPC = ''@Unit'') and 
													st.DY_POSN_QUAL = ''Q'' AND 
													REC_PREC = ''99999999''')
												
		 --10. Find the percentage of the qualified
		 --Take the DMOSQ Count and divide by the total asigned to unit.
		If @TAsgn > 0
			SET @PDOMSQ = Ceiling((@DMOSQ / @TAsgn) * 100)
		ELSE IF @TAsgn = 0
			Set @PDOMSQ = 0


		--11. Count all personnel whom have a critial task in ReadinessTracker
		 Select @Deploy = COUNT(pt.strSSN) from tblPermTask pt LEFT JOIN
									tblSRPQuestion q on q.intQuestionId = pt.intQuestionId LEFT JOIN
									MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = pt.strSSN
							Where	(mn.strUIC = @Unit or mn.strAttchUIC = @Unit) AND q.intPriorityID = 1
												
						
		--12. Find the Percent deployable from above query
		-- Take the number from critical tasks and divide by total # Assigned of personnel
		If @Deploy > 0
			SET @PDeploy = Ceiling((@Deploy / @TAsgn) * 100)
		ELSE IF @Deploy = 0
			Set @PDeploy = 0
		
		--13. Find the # Of Dental Tasks For a Unit
		Select	@Dental = COUNT(pt.intQuestionID) from tblPermTask pt INNER JOIN tblSRPQuestion q on q.intQuestionId = pt.intQuestionId INNER JOIN
			    MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = pt.strSSN where q.intCategoryID = 1 AND (mn.strUIC = @Unit or mn.strAttchUIC = @Unit)
		
		--14. Find the # Of Medical Tasks For a Unit
		Select	@Medical = COUNT(pt.intQuestionID) from tblPermTask pt INNER JOIN tblSRPQuestion q on q.intQuestionId = pt.intQuestionId INNER JOIN
			    MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = pt.strSSN where q.intCategoryID = 2 AND (mn.strUIC = @Unit or mn.strAttchUIC = @Unit)
		
		--15. Find the # Of Admin Tasks For a Unit
		Select	@Admin = COUNT(pt.intQuestionID) from tblPermTask pt INNER JOIN tblSRPQuestion q on q.intQuestionId = pt.intQuestionId INNER JOIN
			    MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = pt.strSSN where q.intCategoryID = 3 AND (mn.strUIC = @Unit or mn.strAttchUIC = @Unit)
	
		--Insert Into Table
		Insert tblReadinessUnitReport (strUIC, strUName, strRPTCode, intReq, intAsgn, intPAsgn, intOS, intOSA, intPOSA)--, intTAsgn, intPTAsgn, intDMOSQ, 
									   --intPDMOSQ, intDeploy, intPDeploy, intDental, intMedical, intAdmin, dtReport)
									    VALUES
									   (@Unit, @UName, @RSC, @Req, @Asgn, @Pasgn, @OS, @OSA, @POS)--, @TAsgn, @PTAsgn, @DMOSQ, @PDOMSQ, @Deploy, @PDeploy, 
										--@Dental, @Medical, @Admin, @DTReport)
		Set @Req = ''
		Set @Asgn = ''
		Set @Pasgn = ''
	FETCH NEXT FROM Strength INTO @Unit,@UName,@RSC
	End
Close Strength
Deallocate Strength

END

Open in new window

0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 36581605
A few of those (if not all) you can combine into one call to SPIDERS.

For example:
Select SUM(AUTH) AUTH from MNLOCAL_ACN_AUTH ...
Select SUM(OS) OS from MNLOCAL_ACN_AUTH ...

Can be written as:
Select SUM(AUTH) AUTH, SUM(OS) OS From MNLOCAL_ACN_AUTH ...

Apply that principal to some of the others and you may save some time. Good luck!
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 36581628
By the way, sp_executesql may still come in handy for you over plain exec[ute]...if you condense this down, then you can get all the values in one swoop.
0
 

Author Closing Comment

by:kdeutsch
ID: 36895879
Thanks for suggestions
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question