Solved

Get value from Openquery statement

Posted on 2011-09-21
11
913 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
11 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
Unfortunately you cannot use variables with OPENQUERY(), which may explain why the author is resorting to using Dynamic SQL.
0
 

Author Comment

by:kdeutsch
Comment Utility
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
 
LVL 9

Expert Comment

by:DrewKjell
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:kdeutsch
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
No problem. I was just curious.
0
 

Accepted Solution

by:
kdeutsch earned 0 total points
Comment Utility
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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
Comment Utility
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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
Comment Utility
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
Comment Utility
Thanks for suggestions
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now