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

x
?
Solved

Powershell Query temp temp tables

Posted on 2015-01-03
2
Medium Priority
?
579 Views
Last Modified: 2015-01-08
I can run this query from SQL and it will display result against a particular server. But if I pass that server as a parameter. It returns nothing. I believe that this script may be a but to complex for  "Invoke-Sqlcmd" command. Am I right? or is there a way to run this where it produces results.

I need to run this script against a list of servers so putting it a procedure wont really work. I need to run this and out put result set to file for further processing.


Param($SQLInstance = "ServerNAME") 

#####Add all the SQL goodies (including Invoke-Sqlcmd)#####
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls 

$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "Declare @searchSTR varchar(4000), @database varchar(255), @sql nvarchar(4000),@sql2 nvarchar(4000)

IF OBJECT_ID('tempdb..#TempList') IS NOT NULL
	Drop Table #TempList


IF OBJECT_ID('tempdb..#TempListDetails') IS NOT NULL
	Drop Table #TempListDetails

Create table #TempList(
[Database] varchar(255),
Counts  int
)

Create table #TempListDetails(
[Database] varchar(255)
,CNAME varchar(255)
,CDBNAME varchar(255)
,CTSINSTNAM varchar(255)
)


declare DB cursor fast_forward for 
SELECT name FROM master..sysdatabases (nolock)
Where name Like '%UTX%'
Union
SELECT name FROM master..sysdatabases (nolock)
Where name Like '%UEX%'

open DB
fetch next from DB into @database   

while @@fetch_status = 0
begin

Set @sql = 
N'Use ['+@database+']

Insert into #TempList
SELECT '''+@database+''',isNULL(Count(1),0) as counts 
FROM   dbo.ts_task 
WHERE  tasknextrun < dateadd(day,0,datediff(day,0,getdate()-1)) 
       AND taskfinished IS NULL
       AND taskstatus = 2
HAVING Count(1) > 0'
         

Print @sql

exec sp_executesql @sql

fetch next from DB into @database   
end 
close DB
deallocate DB

Select @@SERVERNAME as [Server],* from #TempList
Where Counts > 0"

$Packages | ForEach-Object { $_ }

Open in new window

0
Comment
Question by:Leo Torres
[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
2 Comments
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 40529001
Cannot find an issue, so it should work if you call it this way:
  .\script.ps SqlSrv1\instance
  .\script.ps SqlSrv2
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 40539571
Yes worked
0

Featured Post

Are You Ready for GDPR?

With the GDPR deadline set for May 25, 2018, many organizations are ill-prepared due to uncertainty about the criteria for compliance. According to a recent WatchGuard survey, a staggering 37% of respondents don't even know if their organization needs to comply with GDPR. Do you?

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the adminiā€¦
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

721 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