Powershell: how to execute a T-SQL query without getting all the result records?

Hi Experts:

I wrote a Powershell script using Invoke-Sqlcmd to test some T-SQL queries in a SQL Server 2012 server. I only want to make the script check whether there is some record returned or not, but don't care how many record.

The code I currently uses is like:
$queryResult = Invoke-Sqlcmd -Query $q -ServerInstance $serverInstance -Database $dbName -Username $dbUser -Password $dbPassword -ErrorAction Stop

Open in new window


I encounter an issue that some query returns millions of records, such that it takes long time for the script to run and return them all to my workstation through the network. Can you help me to find a way to speed it up? I only need the script to check whether there is data return, and don't care how many.

I tried some ways but they didn't seem to work:
1. tried parameters in Invoke-Sqlcmd  (MaxBinaryLength and MaxCharLength) and hope they can limmit the return length, but doesn't work
2. try to add "SELECT TOP 1 * from () as x" around the original T-SQL query, but this does not work for SELECT statement having "WITH" clause

Thank you!
huangs3Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what's the value of $q ?
PortletPaulEE Topic AdvisorCommented:
You won't find a simple solution for "WITH" because "WITH" must precede all other SQL commands.

For those without a CTE your "select TOP(1) from ( ... ) x" would work

or perhaps; SELECT COUNT(*) from ( $q ) x

----

for those using "WITH" you need to modify the query AFTER the CTE has been defined, e.g.


    with myCTE as (select ...)
    select * from myCTE

would need to become

   with myCTE as (select ...)
select top(1) from (
    select * from myCTE
) x
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Does prepending
  set rowcount 1
work?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

huangs3Author Commented:
$q is a variable used in a loop, it can be any kind of "SELECT" statement including the ones with "WITH" clause
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Again, but more clearly: Does this work?
$queryResult = Invoke-Sqlcmd -Query "set rowcount 1;`n$q" -ServerInstance $serverInstance -Database $dbName -Username $dbUser -Password $dbPassword -ErrorAction Stop

Open in new window

PortletPaulEE Topic AdvisorCommented:
good thought!

https://msdn.microsoft.com/en-us/library/ms188774.asp
NB: works for SELECT, but not INSERT/UPDATE/DELETE
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Paul, the link is invalid.
huangs3Author Commented:
Qlemo:
the Invoke-Sqlcmd -Query  command will still take long time to execute because there is no error.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If it still lasts very long, there is no effective query execution plan even if restricting the number of rows to get. There is no way to get around that but to change the SQL itself to e.g. some "Select 1 where exists ( $q )", which might work much better (but does not allow for WITH, for the reasons mentioned by Paul).
huangs3Author Commented:
I found a way to do it now. In powershell I can use native .NET classes such as SqlCommand to return results record by record using reader class. I can close the reader after reading the first recorder and in experiment it is very quick even if the query can return millions of records.

http://blogs.technet.com/b/heyscriptingguy/archive/2012/02/11/why-use-net-framework-classes-from-within-powershell.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
For future reference as an answered question could you share a powershell snippet that uses the .Net SqlCommand and a WITH CTE?

 (that url above does not)
huangs3Author Commented:
I implemented this method and it works well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.