dcsimg
Results 1 to 8 of 8

Thread: SQL jobs

  1. #1

    Thread Starter
    Member Dragokas's Avatar
    Join Date
    Aug 2015
    Location
    Ukraine
    Posts
    555

    SQL jobs

    Hi,

    https://docs.microsoft.com/en-us/sql...ql-server-2017
    https://docs.microsoft.com/en-us/sql...ql-server-2017

    Can somebody help me please to make up correctly a query to MS SQL database for:
    - creating job
    - read job
    - modify permissions
    - remove job

    as well as universal connection string that will work ok for most modern versions of MS SQL.

    I prefer PM. I will be very thankful.

    Thank you.

  2. #2

    Thread Starter
    Member Dragokas's Avatar
    Join Date
    Aug 2015
    Location
    Ukraine
    Posts
    555

    Re: SQL jobs

    Looks like I handle myself how to query the table I need.

    1 question remains: how correctly build universal connection string. One of these:
    https://www.connectionstrings.com/sql-server/

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,825

    Re: SQL jobs

    There is no Universal Connection String listed at that web site. What do you mean? I know there is probably a language barrier but you need to give use more information about what your trying to accomplish, don't just post Links. There are lots of people on this forum that will help you if they can understand what you want.

  4. #4

    Thread Starter
    Member Dragokas's Avatar
    Join Date
    Aug 2015
    Location
    Ukraine
    Posts
    555

    Re: SQL jobs

    Thanks for the answer.

    I have wide range of servers with MS SQL 2008 - 2017 Express, Standart, any... They have no common domain, subnet or so.
    I need a way to connect to them from my Visual Basic program. It will be run locally on each individual server.
    That's why I need:
    - to recognize what server it is and what connection string I should use in each case.
    - and check is MS SQL exist at all, before trying to do any connections (maybe, check service names?)

    The task is simple: connect to database (with unknown name), read concrete table.

    One guy already suggested me how to query databases names:
    Code:
    SELECT name FROM master.sys.databases
    Now, I just need correct connection string, or several versions.

    Also, do I need to specify login/pass in connection string? (if current user is admin and it has permission to access database => mixed auth type)

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: SQL jobs

    I would suggest that you follow the CodeBank link in my signature beow and check out my thread on Protected Configuration. That thread was created for other reasons but it will show you how to use the SqlDataSourceEnumerator class to determine what servers are available and the SqlConnection class to determine what databases are available on a server. You can use a SqlConnectionstringBuilder to create the connection string to connect to the server and then the ChangeDatabase method to access the desired database.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,380

    Re: SQL jobs

    I'm not sure what we are talking about here.
    Is this an SQL JOB? Is this a vb.net program? Do the servers have open domains to communicate? Do they run independently?
    If the are on the same domain you can use linked server and then use sp_addlinkedserver. Although this is very slow and can bloat your DB . So may want to take a look at https://docs.microsoft.com/en-us/sql...ql-server-2017
    or https://docs.microsoft.com/en-us/sql...ql-server-2017
    or mirroring.
    Another option is to pass files (p.e. xmlfiles) to the servers and have a job .net job that will add the data to SQL.

    At work we are using files passing ,mirroring and Linked servers (unfortunately) .
    Slow as hell.

  7. #7

    Thread Starter
    Member Dragokas's Avatar
    Join Date
    Aug 2015
    Location
    Ukraine
    Posts
    555

    Re: SQL jobs

    Hi, jmcilhinney! Thank you. It is not very helpful. It's easy done with vb.net. Not for vb6. Sorry, I didn't mention it.

    Hi, sapator! It is SQL job. It's vb6 tool. They run independently. No way to communicate.
    Last edited by Dragokas; Nov 12th, 2018 at 10:48 AM.

  8. #8
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,368

    Re: SQL jobs

    Hi,

    sounds a bit strange if you want to connect a Database you don't know.

    use ENUM to declare the DB_sytem

    look at the ..RebuildConnection.. Function

    Code:
    Option Explicit
    Private Const m_ClassName = "clsGenericOpen"
    Private Const vbObjectError = 513
    
    Private Const ERR_UPDATE_COLLISION = -2147217864
    Private Const ERR_CONFLICTS_FOUND = vbObjectError + 1
    
    Private strDBName As String
    Private strConnect As String
    Private strErrorInfo As String
    
    Private AdoConnect As ADODB.Connection
    Private LocRecset As ADODB.Recordset
    Private mvarCType As Integer
    Private mvarErrorNumber As Long
    
    
    Enum CType
        [ACCESS] = 0
        [SQLServer] = 1
        [ODBC] = 2
    'etc.....
    End Enum
    
    Private m_szConnection As String
    Private m_bChange As Boolean
    Private mvarPassword As String
    Private mvarUserID As String
    Private mvarServerName As String
    
    '**************************
    '****    Properties    ****
    '**************************
    Public Property Get ErrorInfo() As String
        ErrorInfo = strErrorInfo
    End Property
    
    Public Property Get ErrorNumber() As Long
        ErrorNumber = mvarErrorNumber
    End Property
    
    Public Property Let ServerName(ByVal vData As String)
        mvarServerName = vData
        m_bChange = True
    End Property
    
    Public Property Get ServerName() As String
        ServerName = mvarServerName
    End Property
    
    Public Property Let PASSWORD(ByVal vData As String)
        mvarPassword = vData
        m_bChange = True
    End Property
    
    Public Property Get PASSWORD() As String
        PASSWORD = mvarPassword
    End Property
    
    Public Property Let UserID(ByVal vData As String)
        mvarUserID = vData
        m_bChange = True
    End Property
    
    Public Property Get UserID() As String
        UserID = mvarUserID
    End Property
    
    Public Property Let Dbname(ByVal sData As String)
        strDBName = sData
        m_bChange = True
    End Property
    
    Public Property Get Dbname() As String
    Dbname = strDBName
    End Property
    
    
    
    '******************************
    '****    Methodes Public   ****
    '******************************
    
    
    Public Sub SetConnectType(ByVal vData As CType)
        mvarCType = vData
        m_bChange = True
    End Sub
    
    Public Function ReadOnlyRecord(ByVal strQuery As String, _
                                    ByRef RecSet As ADODB.Recordset) As Boolean
    On Error GoTo ErrorSection
    
    Dim bResult As Boolean
    '** Default No Error
    bResult = True
    ClearError
    ReConnect
    
    ' Open Recordset
    With LocRecset
        .ActiveConnection = AdoConnect
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Source = strQuery
        .Open
    End With
    
    Set LocRecset.ActiveConnection = Nothing
    '** Transfer The Recordset
    Set RecSet = LocRecset.Clone
    LocRecset.Close
    
    '********************
    'Exit Point
    '********************
    ExitPoint:
    ReadOnlyRecord = bResult
    Exit Function
    '********************
    'Error Section
    '********************
    ErrorSection:
    If AdoConnect.Errors.Count > 0 Then
        ShowError AdoConnect.Errors(0).Number, AdoConnect.Errors(0).Description, "ReadOnlyRecord", m_ClassName, vbLogEventTypeError
    Else
        Select Case Err.Number
            Case Else
            ShowError Err.Number, Err.Description, "ReadOnlyRecord", m_ClassName, vbLogEventTypeError
        End Select
    End If
    bResult = False
    Resume ExitPoint
    End Function
    
    Public Function RunSQL(ByVal strSql As String) As Boolean
    On Error GoTo ErrorSection
    Dim bResult As Boolean
    '** Default No Error
    bResult = True
    ClearError
    ReConnect
    
    AdoConnect.BeginTrans
    AdoConnect.Execute (strSql)
    AdoConnect.CommitTrans
    
    '********************
    ' B) Exit Point
    '********************
    ExitPoint:
    RunSQL = bResult
    Exit Function
    '********************
    ' C) Error Section
    '********************
    ErrorSection:
    If AdoConnect.Errors.Count > 0 Then
        ShowError AdoConnect.Errors(0).Number, AdoConnect.Errors(0).Description, "RunSQL", m_ClassName, vbLogEventTypeError
    Else
        Select Case Err.Number
            Case Else
            ShowError Err.Number, Err.Description, "RunSQL", m_ClassName, vbLogEventTypeError
        End Select
    End If
    bResult = False
    AdoConnect.RollbackTrans
    Resume ExitPoint
    
    End Function
    
    
    Public Function GetBatchRecord(ByVal strQuery As String, _
                                    ByRef RecSet As ADODB.Recordset) As Boolean
    On Error GoTo ErrorSection
    
    Dim bResult As Boolean
    '** Default No Error
    bResult = True
    ClearError
    ReConnect
    
    'Open Recordset for batch mode
    With LocRecset
        .ActiveConnection = AdoConnect
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset      'Need the RecordCount Property
        .LockType = adLockBatchOptimistic
        .Source = strQuery
        .Open
    End With
    
    Set LocRecset.ActiveConnection = Nothing
    '** Transfer The Recordset
    Set RecSet = LocRecset.Clone
    LocRecset.Close
    
    '********************
    'Exit Point
    '********************
    ExitPoint:
    GetBatchRecord = bResult
    Exit Function
    '********************
    'Error Section
    '********************
    ErrorSection:
    If AdoConnect.Errors.Count > 0 Then
        ShowError AdoConnect.Errors(0).Number, AdoConnect.Errors(0).Description, "GetBatchRecord", m_ClassName, vbLogEventTypeError
    Else
        Select Case Err.Number
            Case Else
            ShowError Err.Number, Err.Description, "GetBatchRecord", m_ClassName, vbLogEventTypeError
        End Select
    End If
    bResult = False
    Resume ExitPoint
    End Function
    
    Public Function GetRecord(ByVal strQuery As String, _
                              ByRef RecSet As ADODB.Recordset) As Boolean
    On Error GoTo ErrorSection
    
    Dim bResult As Boolean
    '** Default No Error
    bResult = True
    ClearError
    ReConnect
    
    ' Open Recordset
    With LocRecset
        .ActiveConnection = AdoConnect
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Source = strQuery
        .Open
    End With
    
    Set LocRecset.ActiveConnection = Nothing
    '** Transfer The Recordset
    Set RecSet = LocRecset.Clone
    LocRecset.Close
    
    '********************
    'Exit Point
    '********************
    ExitPoint:
    GetRecord = bResult
    Exit Function
    '********************
    'Error Section
    '********************
    ErrorSection:
    If AdoConnect.Errors.Count > 0 Then
        ShowError AdoConnect.Errors(0).Number, AdoConnect.Errors(0).Description, "GetRecord", m_ClassName, vbLogEventTypeError
    Else
        Select Case Err.Number
            Case Else
            ShowError Err.Number, Err.Description, "GetRecord", m_ClassName, vbLogEventTypeError
        End Select
    End If
    bResult = False
    Resume ExitPoint
    End Function
    
    Public Function SaveBatchRecord(ByVal AdoRS As ADODB.Recordset) As Boolean
    On Error GoTo ErrorSection
    
    Dim bResult As Boolean
    '** Default No Error
    bResult = True
    ClearError
    ReConnect
    
    With AdoRS
        .MarshalOptions = adMarshalModifiedOnly
        .ActiveConnection = AdoConnect
    End With
    
    '** We start transaction
    AdoConnect.BeginTrans
    AdoRS.UpdateBatch adAffectAll
    AdoRS.Filter = adFilterConflictingRecords
    '** If All records updated then recordcount = 0
    If AdoRS.RecordCount > 0 Then
        Err.Raise ERR_CONFLICTS_FOUND, , AdoRS.RecordCount & " could not be saved"
    End If
    AdoConnect.CommitTrans
    
    '********************
    'Exit Point
    '********************
    ExitPoint:
    Set AdoRS.ActiveConnection = Nothing
    SaveBatchRecord = bResult
    Exit Function
    '********************
    'Error Section
    '********************
    ErrorSection:
    Select Case Err.Number
        Case ERR_UPDATE_COLLISION
            '***Resync problem so try again
            Err.Clear
            With AdoRS
                .Resync adAffectAllChapters, adResyncUnderlyingValues
                .UpdateBatch
            End With
            Resume ExitPoint
        Case Else
        ShowError Err.Number, Err.Description, "SaveBatchRecord", m_ClassName, vbLogEventTypeError
    End Select
    bResult = False
    '** Error so Rollback
    AdoConnect.RollbackTrans
    Resume ExitPoint
    
    End Function
    
    Public Function SaveRecord(ByVal AdoRS As ADODB.Recordset) As Boolean
    On Error GoTo ErrorSection
    
    Dim bResult As Boolean
    '** Default No Error
    bResult = True
    ClearError
    ReConnect
    
    Set AdoRS.ActiveConnection = AdoConnect
    
    '** We start transaction
    AdoConnect.BeginTrans
    AdoRS.Update
    
    '** Commit the transaction
    AdoConnect.CommitTrans
    Set AdoRS.ActiveConnection = Nothing
    
    '********************
    'Exit Point
    '********************
    ExitPoint:
    SaveRecord = bResult
    Exit Function
    '********************
    'Error Section
    '********************
    ErrorSection:
    If AdoConnect.Errors.Count > 0 Then
        ShowError AdoConnect.Errors(0).Number, AdoConnect.Errors(0).Description, "SaveRecord", m_ClassName, vbLogEventTypeError
    Else
        Select Case Err.Number
            Case Else
            ShowError Err.Number, Err.Description, "SaveRecord", m_ClassName, vbLogEventTypeError
        End Select
    End If
    bResult = False
    AdoConnect.RollbackTrans
    Resume ExitPoint
    
    End Function
    
    
    
    '******************************
    '****    Methodes Private  ****
    '******************************
    
    Private Sub Class_Initialize()
    
        Set AdoConnect = New ADODB.Connection
        Set LocRecset = New ADODB.Recordset
        '** First the connection is to build again
        m_bChange = True
        '** No error
        mvarErrorNumber = 0
    End Sub
    
    Private Sub Class_Terminate()
    On Error GoTo ErrorSection
    
    '* Do we need to close recordset ?
    If LocRecset.State = adStateOpen Then
        LocRecset.Close
    End If
    Set LocRecset = Nothing
    
    '* Do we need to close the connection ?
    If Not AdoConnect Is Nothing Then
        If AdoConnect.State = adStateOpen Then
            AdoConnect.Close
        End If
        Set AdoConnect = Nothing
    End If
    
    '********************
    'Exit Point
    '********************
    ExitPoint:
    Exit Sub
    '********************
    'Error Section
    '********************
    ErrorSection:
    Select Case Err.Number
        Case Else
        ShowError Err.Number, Err.Description, "Class_Terminate", m_ClassName, vbLogEventTypeError
    End Select
    Resume ExitPoint
    
    End Sub
    
    Private Sub ClearError()
        mvarErrorNumber = 0
        strErrorInfo = ""
    End Sub
    
    Private Sub ReConnect()
    On Error GoTo ErrorSection
    
    '** Rebuild connection if necessary
    If m_bChange Then
        m_szConnection = RebuildConnection
        '** The Conection Change so we must Close it
        If AdoConnect.State = adStateOpen Then
            AdoConnect.Close
        End If
    End If
        
    '** If the connection is already Open we skip This
    If Not (AdoConnect.State = adStateOpen) Then
        '** Use Connection for rich Info on Error
        With AdoConnect
            .ConnectionString = m_szConnection
            .Open
        End With
    End If
    
    '********************
    'Exit Point
    '********************
    ExitPoint:
    Exit Sub
    '********************
    'Error Section
    '********************
    ErrorSection:
    Select Case Err.Number
        Case Else
        ShowError Err.Number, Err.Description, "ReConnect", m_ClassName, vbLogEventTypeError
    End Select
    Resume ExitPoint
    
    End Sub
    
    Private Function RebuildConnection() As String
    On Error GoTo ErrorSection
    
    Dim szLocalString As String
    
    Select Case mvarCType
        Case CType.ACCESS
            '** MDAC
            If Len(Me.PASSWORD) = 0 Then
                '** No Password Protection
                szLocalString = "Provider=Microsoft.jet.OLEDB.4.0;Data source=" & Dbname
            Else
                '** We have a Password
                szLocalString = "Provider=Microsoft.jet.OLEDB.4.0;" & _
                            "Data Source=" & Dbname & ";" & _
                            "Jet OLEDB:Database Password=" & PASSWORD & ";" & _
                            "admin"
            End If
        
        Case CType.ODBC
            szLocalString = "Provider=MSDASQL.1;Password=" & PASSWORD & ";Persist Security Info=True;User ID=" _
                            & UserID & ";Data Source=" & Dbname
        
        Case CType.SQLServer
            szLocalString = "Provider=sqloledb;Data Server=" & ServerName & ";User Id=" & UserID _
                            & ";Password=" & PASSWORD & ";database=" & Dbname
    End Select
    
    RebuildConnection = szLocalString
    m_bChange = False
    '********************
    'Exit Point
    '********************
    ExitPoint:
    Exit Function
    '********************
    'Error Section
    '********************
    ErrorSection:
    Select Case Err.Number
        Case Else
        ShowError Err.Number, Err.Description, "RebuildConnection", m_ClassName, vbLogEventTypeError
    End Select
    Resume ExitPoint
    
    End Function
    
    
    '*********************************
    '****    Error(s) Handling    ****
    '*********************************
    
    Private Sub ShowError(ErrorNumber As Long, ErrorMsg As String _
                          , ErrorModule As String, ErrorForm As String _
                         , LogEventType As Long, Optional ErrorInfo As Variant)
    On Error GoTo ErrorSection
    Dim ErrorTitle As String
    Dim ErrorMessage As String
    
    ErrorTitle = "ERROR - " & ErrorNumber & " - " & ErrorModule & " - " & ErrorForm
    ErrorMessage = "ERROR  " & ErrorNumber & " - " & ErrorMsg
    
    If Not IsMissing(ErrorInfo) Then
        ErrorMessage = ErrorMessage & vbCrLf & ErrorInfo
    End If
    
    strErrorInfo = ErrorMessage
    mvarErrorNumber = ErrorNumber
    App.LogEvent ErrorTitle & ": " & ErrorMessage, LogEventType
    
    ExitPoint:
    Exit Sub
    
    ErrorSection:
    Resume ExitPoint
    
    End Sub
    in the Form youwould use it like this
    Code:
    Private TestClass As clsGenericOpen
    Private recEmployee As ADODB.Recordset
    
    Private Sub Form_Load()
    Set TestClass = New clsGenericOpen
    Set recEmployee = New ADODB.Recordset
    With TestClass
        .Dbname = App.Path & "\Northwind.MDB"
        .SetConnectType ACCESS
    End With
    End Sub
    Last edited by ChrisE; Nov 14th, 2018 at 03:09 PM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width