Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get value from Openquery statement

Posted on 2011-09-21
11
Medium Priority
?
958 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 2000 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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