5 Replies Latest reply: May 15, 2014 10:09 AM by Andy Hewitt RSS

    Connect to SQL Server Express with script

      Using Monarch Pro 10.5, Data Pump 10.5, Windows server 2008, XP workstation

      I have a post process script that is attempting to execute a Data Pump process.  I modeled my script after Bill Watson’s post, “Working Solution: use PumpAPI.DLL in Post Process Script”.  When I developed the following code it kept calling for a connection string so I’m attempting to give it one.

      Try

                  Dim PumpAPI As DwchServer.PumpAPI

                  Dim strTrackingID As String

                  Dim strConn As String = "Data Source=servername.xxx.net;Initial Catalog=DWCHServer;User Id=xxx;Password=xxx;"

                  PumpAPI = New DwchServer.PumpAPI(strConn)

       

                  strTrackingID = PumpAPI.StartProcess("Dummy For Testing")

                  Dim status As Integer = PumpAPI.GetStatus(strTrackingID)

       

              Catch myex As Exception

                  'it failed send email.

                  Dim Errormsg As String

                  Errormsg = myex.Message()

                  Dim email As New System.Net.Mail.SmtpClient

                  email.Host = "xxx.xxx.xxx"

                  email.Send("ServerConnection@xxx.org", "xxx@xxx.org", "Problem server connection example", "Error is: " & Errormsg)

              End Try

      I keep getting the following error:  “Cannot connect to DwchServer database”.  We are using SQL Server Express for the Data Pump process database.  Our server administrator has taken the steps necessary to configure SQL Express for remote connections.  I know our ID and password work because I can get to the database through SQL management studio.  Does anyone have a working connection string to a SQL Server Express database that works in a Data Pump process?  Other ideas or suggestions are welcome.  Thanks.

        • Connect to SQL Server Express with script
          Bill Watson

          John

           

          What is it you are trying to do exactly? Do you simply want to run a single seperate process from your current post process OR are you trying to read the SQL database. As I understand it these are two seperate tasks which would be used for different purposes.

           

          A. Running another process using pumpapi[/B]

           

          I think you are almost there, however you are attempting to use the pumpapi object in the wrong way. I have tweaked my code you referenced and hopefully it should give you a better start. I am on holiday next week, but will check back if I can.

           

          Remember to add the following assembly and import

           

          Assembly Details:

          Name: DwchServer.PumpAPI Assembly: DwchServer.PumpAPI.DLL

           

          Imports:

          DwchServer.PumpAPI

           

           

          Dim PumpAPI As DwchServer.PumpAPI

          Dim strTrackingID As String

          Dim strProcess as string

          Dim iStatus as integer

          dim blStatus as boolean

          Dim sError As String

          Dim email As New System.Net.Mail.SmtpClient

           

          PumpAPI = New DwchServer.PumpAPI

           

          'define process name here

          strProcess = "Dummy For Testing"

           

          blStatus = true 'default process success to true

          strtrackingid = "" 'reset tracking id

           

          'attempt to start process defined above

          Try

               strTrackingID = PumpAPI.StartProcess(strProcess)

               Log.AddEvent("Process: " + strProcess + "   Status: Execution Begun")

               Log.AddEvent("Process: " + strProcess + "   ID: " + strTrackingID)

                

          Catch ex As Exception

               sError = "Process: " + strProcess + "   Status: Failed " + ex.Message

               Log.AddEvent(sError)

               blstatus = false

          end try

           

          'if the process started above verify that it is running and when it has completed

          if blStatus = true then

               'keep checking status until process is running

               do

                    System.Windows.Forms.Application.DoEvents()

                    iStatus = pumpapi.getstatus(strTrackingID)

               loop until iStatus > 0

                     

               'when process is running check for completion/errors

               if iStatus > 0 then

                    'loop while process is running

                    do

                         System.Windows.Forms.Application.DoEvents()

                         iStatus = pumpapi.getstatus(strTrackingID)

                    loop until iStatus <> 1

           

                    'when process halts, determine why

                    select case iStatus

                    case 2

                         Log.AddEvent("Process: " + strProcess + "   Status: Completed OK")

                    case 3

                         sError = "Process: " + strProcess + "   Status: Completed With Errors"

                         Log.AddEvent(sError)

                         blstatus = false

                    case else

                         sError = "Process: " + strProcess + "   Status: Unknown Value ("intrep.tostring()")"              

                         Log.AddEvent(sError)

                         blstatus = false

                    end select

               end if

          end if

           

          'if false stop running further processes and send email alert

          if blstatus = false then     

               email.Host = "xxx.xxx.xxx"

               email.Send("ServerConnection@xxx.org", "xxx@xxx.org", "Post Process Error:" + strProcess, "Error is: " & sError)

               return false

          end if

          /code

           

           

           

           

          B. Connecting to SQL Server Database[/B]

           

          You would use this method to perhaps query the database directly to see which processes are currently running (I think Gareth posted some more detailed info on doing this elsewhere in the forums)

           

          You need to add the following namespaces to Imports:

           

          System.Data

          System.Data.SqlClient

           

          The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.

           

          Declare and instantiate your SQLConnection object as shown below in your post process

           

          Dim con As New SqlConnection

           

          SQLConnection class represents an open connection to a SQL Server database.

           

          Pass the SQL connection string to ConnectionString property of your SqlConnection object.

           

          con.ConnectionString = "Data Source=yourservername;Initial Catalog=DWCHServer;Persist Security Info=True;User ID=sa;Password=12345678"

           

          The connectionstring value usually contains the following :

          Data Source - physical server hostname

          Initial Catalog - your database name

          User ID - SQL username use to connect to the server

          Password - SQL username's password

           

          On this sample, I am using an SQL Server 2005. For the connectionstring for other SQL version, you can get it from here http://www.connectionstrings.com/[/url].

           

          Last step is to invoke the Open method of the connection object

           

          con.Open()

           

          The complete sample sourcecode:

           

          Dim con As New SqlConnection

          Dim cmd As New SqlCommand

           

          con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

          con.Open()

           

          To capture if the connection was successful or not, just tweak the above code:

           

          Dim con As New SqlConnection

          Dim cmd As New SqlCommand

          Try

          con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

          con.Open()

          Catch ex As Exception

          log.addevent("Error while connecting to SQL Server." & ex.Message) Finally

          con.Close() 'Whether there is error or not. Close the connection.

          End Try

           

          Once the connection is made you would then add a query to get the data you wish to examine.

           

          Hope that clarifies things for you.

          • Connect to SQL Server Express with script
            Gareth Horton

            John,

             

            Try using the string "Default" as the connection parameter.

             

            If this is not the problem, one of the most common issues is not specifically referring to the instance name of SQLEXPRESS in the connection string.

             

            Here's a snippet of what a connection string referring to a local SQL Express would look like:

             

            Initial Catalog=DWCHServer;Data Source=.\sqlexpress;

             

             

            Gareth

             

            Using Monarch Pro 10.5, Data Pump 10.5, Windows server 2008, XP workstation

            I have a post process script that is attempting to execute a Data Pump process.  I modeled my script after Bill Watson’s post, “Working Solution: use PumpAPI.DLL in Post Process Script”.  When I developed the following code it kept calling for a connection string so I’m attempting to give it one.

            Try

                        Dim PumpAPI As DwchServer.PumpAPI

                        Dim strTrackingID As String

                        Dim strConn As String = "Data Source=servername.xxx.net;Initial Catalog=DWCHServer;User Id=xxx;Password=xxx;"

                        PumpAPI = New DwchServer.PumpAPI(strConn)

             

                        strTrackingID = PumpAPI.StartProcess("Dummy For Testing")

                        Dim status As Integer = PumpAPI.GetStatus(strTrackingID)

             

                    Catch myex As Exception

                        'it failed send email.

                        Dim Errormsg As String

                        Errormsg = myex.Message()

                        Dim email As New System.Net.Mail.SmtpClient

                        email.Host = "xxx.xxx.xxx"

                        email.Send("ServerConnection@xxx.org", "xxx@xxx.org", "Problem server connection example", "Error is: " & Errormsg)

                    End Try

            I keep getting the following error:  “Cannot connect to DwchServer database”.  We are using SQL Server Express for the Data Pump process database.  Our server administrator has taken the steps necessary to configure SQL Express for remote connections.  I know our ID and password work because I can get to the database through SQL management studio.  Does anyone have a working connection string to a SQL Server Express database that works in a Data Pump process?  Other ideas or suggestions are welcome.  Thanks.[/QUOTE]

              • Connect to SQL Server Express with script

                Bill,

                I finally got the time to get back to this project.  Thanks for the update on the code.  What I am attempting to do for this project is to run another process using pumpapi.  Connecting to the server came about because the compiler comes back with the following error when I attempt to create an instance of PumpAPI = New DwchServer.PumpAPI:

                “Compiler error:#30455 of severity 0 on line 42: Argument not specified for parameter 'strConnectionName' of 'Public Sub New(strConnectionName As String)”

                So, connecting to the server was an attempt to satisfy the compiler, not something I need for this project.  Once I provided a connection string the pumpapi object was happy and then I started having the problem of not being able to connect to the server.

                 

                I updated my code with the part of the new version you provided that starts a process (I did not add any connection string code) and I still get the compiler error.  I checked again and I have the assembly reference and import.   I kept it simple and just attempted to start a process:

                Try

                  Dim PumpAPI As DwchServer.PumpAPI

                  Dim strTrackingID As String

                  Dim strProcess as string

                  Dim iStatus as integer

                  dim blStatus as boolean

                  Dim sError As String

                  Dim email As New System.Net.Mail.SmtpClient

                   

                  PumpAPI = New DwchServer.PumpAPI

                 

                  'define process name here

                  strProcess = "Dummy For Testing"

                 

                  blStatus = true 'default process success to true

                  strtrackingid = "" 'reset tracking id

                 

                  'attempt to start process defined above

                  strTrackingID = PumpAPI.StartProcess(strProcess)

                 

                Catch myex As Exception

                  'it failed send email.

                  Dim Errormsg As String

                  Errormsg = myex.Message()

                  Dim email As New System.Net.Mail.SmtpClient

                  email.Host = "xxx.xxx.xxx"

                  email.Send("ServerConnection@xxx.org", "xxx@xxx.org", "Problem server connection example", "Error is: " & Errormsg)

                End Try

                /code

                 

                Do you have any idea what is causing the object to require a connection string parameter in my environment and not yours?

                  • Connect to SQL Server Express with script
                    Bill Watson

                    can't see any reason why it would through up an error on your server and not on mine.

                     

                    are you sure the issue is with starting the process and not with the email connection you have?

                     

                    what i would do to error check this is to comment out everything relating to the email and test it. do you still get the error?

                     

                    if you do, then check you have all your assemblies and imports correct. Other than that as Gareth suggested try amending the line that initiates pump to PumpAPI = New DwchServer.PumpAPI("default")

                      • Connect to SQL Server Express with script
                        Andy Hewitt

                        Hello everyone.  I found this thread trying to research an issue I'm having.

                        I'm trying to make connections to a SQL databases.

                        I beleive I have all of the imports in the right place but i keep getting the error message below.

                         

                        All I'm doing is one line  "Dim con As New SqlConnection "

                         

                        The actual error message is.....

                         

                        Compiler error: BC30002 on line 23:  Type 'SqlConnection' is not defined

                         

                         

                        ' DwchServer default Imports...

                        Imports System

                        Imports System.Collections.Specialized

                        Imports System.Diagnostics

                        Imports System.Windows.Forms

                        Imports System.Xml

                        Imports Microsoft.VisualBasic

                        Imports DwchServer

                        ' Imports from the environment...

                        Imports System.Data

                        Imports System.Data.SqlClient

                        Imports System.Data.OleDb

                        Imports System.Data.Odbc

                        Imports System.Data.Common

                        Imports Microsoft.Data.Odbc

                         

                        Module Script

                         

                          Function PreProcess(Log as JobLog) as Boolean

                            Log.AddEvent("In PreProcess")

                            PreProcess = True

                            ' PreProcess steps from the project...

                         

                            Dim con As New SqlConnection

                         

                        End Function

                         

                        End Module

                         

                         

                        Any ideas?

                         

                        thanks