Leo Torres
asked on
Powershell Query temp temp tables
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.
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 { $_ }
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER