Solved

Integrate TSQL code with Powershell

Posted on 2011-09-14
10
468 Views
Last Modified: 2012-05-12
Hi,

Before doing any activity (Example :Applying Patches) on prod servers, we are MANUALLY doing pre/post verifications on servers. The verifications checklist  includes following items:
1. SQL INSTANCE NAME
2. NO OF DATABASES
3. DATABASE STATUS (ONLINE/OFFLINE)
4. Online Physical Server Name
5. SQL START TIME
6. SERVER UP TIME
7. SQL PRODUCT VERSION/PRODUCT LEVEL/PRODUCT EDITION

Now, we want to do  this activity as automated (With Centralized) through Poweshell scripting & TSQL CODE and results/output should be in the form of CSV file.

I have created stored proc with TSQL and we have to integrate this code with powershell.

I am totally new to Powershell technology, so need help from Powershell Exprts.

Please give your suggestions on above requirement (how to integrate attached stored proc with powershell).

Thanks in Advance.
Pre-Post-Verification-USP.txt
0
Comment
Question by:sg05121983
  • 4
  • 4
10 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 36540889
Hi sg05121983,

This might help:

How do I call an Sql Server stored procedure from Powershell?
http://stackoverflow.com/questions/83410/how-do-i-call-an-sql-server-stored-procedure-from-powershell
0
 

Author Comment

by:sg05121983
ID: 36541007
In above link, the CSV details (how to generate output in the form CSV) are not given.

Also, we want to make this activity as centralized not for specific instance.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 36556497
Do you want to
run the existing stored procedure on each instance - requires that you create and modify it on each instance;
prefer to have the PS code define the stored procedure each time;
run the included T-SQL code instead directly from PS (not creating or using the stored procedure)
?
If you look at what the stored procedure gives for results, you will see each part is different – retrieving a single value and column, or retrieving more than one column, with different column names (of course), and so on. Long story short, the results are heterogenous, and cannot be stored in a single CSV file that way as rows. Think of the CSV as being like a database table; you would have to use distinct columns for distinct meanings.

The only correct way to handle the output is to create one "record" per server, having all infos in the appropriate "columns". Is that what you are after?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sg05121983
ID: 36559212
Hi Olemo,

Thanks for your reply.

1. My main requirement is to produce the output for single instance.

For Ex:
Server Name is : A1 : When i am running script on this server, i need output of A1 server in CSV format and output should be stored in any drive (A Server drive) or i have to receive O/P through mail (mail configuration).

The procedure (TSQL) is same for all instances.

OR

2. Second requirement (Optional):

Make this task as centralized.

For example, I need results of A10 instance and i am executing PS (integrated with stored proc) on A2 server.

Please help me on this requirement (integrate TSQL stored proc with PS)

Thanks in Advance.

0
 
LVL 69

Expert Comment

by:Qlemo
ID: 36568436
As I said already, combining the output into a CSV the way the values are returned doesn't make sense. In particular since there is one query returning more than one row (the installed databases) - how should that be processed? Would you want to have all other data repeated for each DB? That would look similar to:
Server1 Version1 DBCount1 Db1.1
Server1 Version1 DBCount1 DB1.2
Server2 Version2 DBCount2 DB2.1
Server2 Version2 DBCount2 DB2.2
Server2 Version2 DBCount2 DB2.3

Open in new window

where DB1.1 and DB1.2 are located on the same server/instance.

If not, I would stick to a text-only output, without further formatting, as it results from the selects.
0
 

Author Comment

by:sg05121983
ID: 36577959
Yes, you are right.

Please share the code  (PS integrated with attached stored proc - output in the form of text-only option).

I will try in my local machine/server and update you the results.
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 36578824
I'm preparing a piece of PS code which contains the complete T-SQL code - I would favour that instead of the Stored Procedure, since the output of each select needs to be processed seperately anyway in PS. Alternatively you could write the Stored Procedure in a way it already provides the deserialized result, that is one row per DB, each row containing all data related to the server (according to the example output I showed). Example for doing that:
select d1.*, d2.*
from (select ...) d1,
(select ...) d2,
...

Open in new window

0
 

Author Closing Comment

by:sg05121983
ID: 36907706
--
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 36908642
Sorry I didn't manage to return to this question with detailed code. Has my comment been sufficient to get you started with an own solution (then it is ok the way the question has been closed), or did you simply want to get rid of the question? Should you not have something reasonable out of this question, we should reopen (I, as Zone Advisor, can do that) and continue.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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