Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    64

    Unanswered: SQL Query Headache

    Hallo

    I am interrogating the structure of SQL Server database looking for the occurence of a particular phrase in the object names/definitions. This is in preparation for decommission/replacement...

    When I run the script below in SQL Query Analyzer it returns about 200 results, but when I run the same query in my Access VBA code it returns much less!

    SQL Script:
    SELECT
    so.ID,
    so.name,
    so.type,
    CHARINDEX('insita',so.name) AS PosInObjName,
    CASE WHEN CHARINDEX('insita',so.name) > 0 THEN 1 ELSE 0 END AS FoundInObjName,
    CHARINDEX('insita',sc.text) AS PosInObjDef,
    CASE WHEN CHARINDEX('insita',sc.text) > 0 THEN 1 ELSE 0 END AS FoundInObjDef
    FROM
    sysobjects so INNER JOIN syscomments sc on so.id = sc.id
    ORDER BY [name]

    VB Code:
    Sub InterrogateDatabaseStructure(sServer As String, sDB As String, sSearch4 As String)
    On Error GoTo err_IDS
    Dim oCnn As ADODB.Connection
    Dim oCmd As ADODB.Command
    Dim oRs As ADODB.Recordset
    Dim sSQL As String

    Set oCnn = New ADODB.Connection
    oCnn.ConnectionString = "driver={SQL Server};server=" & sServer & ";uid=;pwd=;database=" & sDB & ";dsn=;"
    oCnn.Open

    CurrentDb.Execute "DELETE * FROM PhraseSearchResults"
    CurrentDb.Execute "DELETE * FROM PhraseInObjName"

    Set oCmd = New Command
    oCmd.ActiveConnection = oCnn
    oCmd.CommandType = adCmdText
    sSQL = "SELECT so.ID, so.name, so.type, "
    sSQL = sSQL & "CASE WHEN CHARINDEX('" & sSearch4 & "',so.name) > 0 THEN 1 ELSE 0 END AS FoundInObjName,"
    sSQL = sSQL & "CHARINDEX('" & sSearch4 & "',so.name) AS PosInObjName, "
    sSQL = sSQL & "CASE WHEN CHARINDEX('" & sSearch4 & "',sc.text) > 0 THEN 1 ELSE 0 END AS FoundInObjDef, "
    sSQL = sSQL & "CHARINDEX('" & sSearch4 & "',sc.text) AS PosInObjDef "
    sSQL = sSQL & "FROM " & sDB & ".dbo.sysobjects so INNER JOIN syscomments sc on so.id = sc.id"

    oCmd.CommandText = sSQL
    Set oRs = oCmd.Execute()

    With oRs
    If Not .EOF And Not .BOF Then
    .MoveFirst
    Do Until .EOF
    sSQL = "INSERT INTO PhraseSearchResults VALUES('"
    sSQL = sSQL & .Fields(0) & "','" & .Fields(1) & "','" & .Fields(2) & "',"
    sSQL = sSQL & .Fields(3) & "," & .Fields(4) & ")"
    CurrentDb.Execute sSQL
    .MoveNext
    Loop
    End If
    End With


    Set oCmd = Nothing
    Set oCnn = Nothing

    Exit Sub

    err_IDS:
    MsgBox "Error: " & Err.Description & vbCr & "Error Code: " & Err.Number
    Screen.MousePointer = 0
    End Sub

    Can anyone please advise what I am doing wrong? Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your VB code is missing the database name for the syscomments table.

    -PatP

Posting Permissions

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