Solved

Powershell Query temp temp tables

Posted on 2015-01-03
2
367 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
2 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VS 2017 18 57
Invoke command powershell 4 27
ADModify LDAP Query Help 7 13
Need to create Multiple folders by importing CSV file  using powershell. 13 17
Utilizing an array to gracefully append to a list of EmailAddresses
Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

821 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