Solved

Powershell Query temp temp tables

Posted on 2015-01-03
2
291 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 68

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Synchronize a new Active Directory domain with an existing Office 365 tenant
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now