Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 647
  • Last Modified:

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.


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
Leo Torres
Asked:
Leo Torres
1 Solution
 
QlemoC++ DeveloperCommented:
Cannot find an issue, so it should work if you call it this way:
  .\script.ps SqlSrv1\instance
  .\script.ps SqlSrv2
0
 
Leo TorresSQL DeveloperAuthor Commented:
Yes worked
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now