locked
Powershell to sql connector RRS feed

  • Question

  • Hi ,

    I need to run a sql query from powershell to obtain data. below are the server and db details. Can any one help me with a powershell script to get the query run and retrive neccessary output.

    select Distinct a.EmailAddr from eRCoorCont a ,eRMembers b where a.UserRole like 'Coordinator' and b.RoomCount>0 and b.IsDeactivated like 'False' and b.ServerName like 'sqlserver%' and a.EmailAddr=b.EmailAddr and a.Server= b.ServerName and a.EmailAddr like 'a%mydomain.com'"

    Server Details:

        SQL Server IP =195.33.63.21;
        SQL Server Host Name : sqlserver.mydomain.com(DMZ Domain Server)
        Database Name=colossus_eRoom;


    Ahmed Ali

    Tuesday, February 5, 2013 4:49 AM

Answers

  • Hi Ahmed,

    1. Copy this entire script into a PS file ( say C:\Test\sample.ps1)

    2. Please edit you Server,DB, and query details

    3. Open powershell window and execute C:\Test\sample.ps1

    $ServerInstance = "Manish"
    $Database = "MASTER"
    $ConnectionTimeout = 30
    $Query = "SELECT getdate()"
    $QueryTimeout = 120
    
    $conn=new-object System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
    $conn.ConnectionString=$ConnectionString
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    $ds.Tables


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Daniel.Sommer Tuesday, February 5, 2013 12:45 PM
    • Marked as answer by AhmedShaik Friday, February 8, 2013 8:05 AM
    Tuesday, February 5, 2013 8:31 AM
  • Hi Manish,

    Sorry for the delayed reply. Please find the ps1 content

    Just a Query - do i need to give username and password of the sql admin as i am executing the ps script remotely.

    Hi Ahmed,

     As you mentioned you are trying to execute PS script remotely, I will like to suggest following things

    1. Open the Power shell window by RIGHT Click and Run as Administrator and execute the script.
    2. If still there is same error then instead of executing the script remotely, try to execute on the server only.

    It suspect it could be any firewall issue, or MCafee or any access rights issue, lets try as above and see what happens.


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by AhmedShaik Friday, February 8, 2013 8:05 AM
    Wednesday, February 6, 2013 1:58 PM

All replies

  • Hi Ahmed,

    1. Copy this entire script into a PS file ( say C:\Test\sample.ps1)

    2. Please edit you Server,DB, and query details

    3. Open powershell window and execute C:\Test\sample.ps1

    $ServerInstance = "Manish"
    $Database = "MASTER"
    $ConnectionTimeout = 30
    $Query = "SELECT getdate()"
    $QueryTimeout = 120
    
    $conn=new-object System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
    $conn.ConnectionString=$ConnectionString
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    $ds.Tables


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Daniel.Sommer Tuesday, February 5, 2013 12:45 PM
    • Marked as answer by AhmedShaik Friday, February 8, 2013 8:05 AM
    Tuesday, February 5, 2013 8:31 AM
  • Thanks for the perfect answer.

    I will execute this and get back to you in case of any errors.


    Ahmed Ali

    Tuesday, February 5, 2013 8:32 AM
  • Hi Manish,

    When executed the same with my server,db details getting the below error.

     
    PS C:\Script> .\SQLtest.ps1
    Exception calling "Open" with "0" argument(s): "Cannot initialize SSPI package."
    At C:\Script\SQLtest.ps1:10 char:11
    + $conn.Open <<<< ()
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException


    Ahmed Ali

    Wednesday, February 6, 2013 3:18 AM
  • Ahmed,

    Could you please try to execute your ps1 file as follows in your PowerShell window.

    C:\Script>c:\script\SQLtest.ps1


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, February 6, 2013 3:55 AM
  • Still the same error Manish.


    Ahmed Ali

    Wednesday, February 6, 2013 4:03 AM
  • Ahmed,

    Could you please show the contents of your ps1 file, I understand the server details shall be confidential, I would suggest to encrypt it before displaying here.



    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, February 6, 2013 4:33 AM
  • Hi Manish,

    Sorry for the delayed reply. Please find the ps1 content

    Just a Query - do i need to give username and password of the sql admin as i am executing the ps script remotely.

    $ServerInstance = "192.132.32.41"
    $Database = "colloasd"
    $ConnectionTimeout = 30
    $Query = "SELECT getdate()"
    $QueryTimeout = 120
    $conn=new-object System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
    $conn.ConnectionString=$ConnectionString
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    $ds.Tables


    Ahmed Ali

    Wednesday, February 6, 2013 8:26 AM
  • Hi Manish,

    Sorry for the delayed reply. Please find the ps1 content

    Just a Query - do i need to give username and password of the sql admin as i am executing the ps script remotely.

    Hi Ahmed,

     As you mentioned you are trying to execute PS script remotely, I will like to suggest following things

    1. Open the Power shell window by RIGHT Click and Run as Administrator and execute the script.
    2. If still there is same error then instead of executing the script remotely, try to execute on the server only.

    It suspect it could be any firewall issue, or MCafee or any access rights issue, lets try as above and see what happens.


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by AhmedShaik Friday, February 8, 2013 8:05 AM
    Wednesday, February 6, 2013 1:58 PM
  • Manish you are correct.

    I ran in the same sql server and it works for me.

    Is there any way to give user name and pwd in the same script, because for few databases the script is returning invalid credentials.

    .


    Ahmed Ali

    Friday, February 8, 2013 8:05 AM
  • Ahmed,

    Mostly I used to execute this kind of PS scripts in server only, but I will try to edit the script to work remotely. BTW your SQL Server is having window authentication or mixed authentication.


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, February 8, 2013 8:46 AM