Professional Web Applications Themes

view a query's SQL in VB - Microsoft SQL / MS SQL Server

How can I use Visual Basic to view the SQL built into a query? I have two databases in SQL Server. One is on a SQL 7 production server, and the other is on a SQL 2000 test server. Both databases have tables and queries, stored in SQL Server. The tables and queries should be the same in both databases. I have a regression test suite in Visual Basic 6, which verifies that if I put certain sample data into the test database, the queries return certain results. It also verifies that the table names, query names, column names, and ...

  1. #1

    Default view a query's SQL in VB

    How can I use Visual Basic to view the SQL built into a
    query?

    I have two databases in SQL Server.
    One is on a SQL 7 production server,
    and the other is on a SQL 2000 test server.
    Both databases have tables and queries,
    stored in SQL Server. The tables and queries
    should be the same in both databases.

    I have a regression test suite in Visual Basic 6,
    which verifies that if I put certain sample data
    into the test database, the queries return
    certain results. It also verifies that
    the table names, query names, column names,
    and data types match. (This is just a matter of
    opening an empty recordset against corresponding
    tables or queries, and comparing the fields.)

    How can I write a regression test in Visual Basic
    to verify the query SQL? For example, if both
    databases have a qry_ExtremeValue query,
    how can I make sure that they both query
    "SELECT TOP 1 * FROM tbl_base ORDER BY id ASC",
    not
    "SELECT TOP 1 * FROM tbl_base ORDER BY id DESC"?

    (It does not do me much good to make sure the
    test database works fine, if the test database
    does not have the same structure as the
    production database.)

    Jasper Paulsen Guest

  2. #2

    Default Re: view a query's SQL in VB

    Jasper,
    If I read this right, the object you want to use is SQLDMO, you can look at
    the DDL for any object.

    You can also try and use my doentation tool .
    [url]http://www.ag-software.com/ags_scribe_index.asp[/url] the MDB it makes can be
    used simply to compare SQL text. 30 free trial

    Or try SQL Compare from Red-Gate [url]http://www.red-gate.com/[/url]

    --
    I hope this helps
    regards
    Greg O MCSD
    SQL Scribe Doentation Builder
    Doent any SQL server database in minutes
    Programmers love it, DBA dream of it
    AGS SQL Scribe download a 30 day trial today
    [url]http://www.ag-software.com/ags_scribe_index.asp[/url]

    "Jasper Paulsen" <jasper.paulsenpse.com> wrote in message
    news:071901c3459c$2caf3340$a101280aphx.gbl...
    > How can I use Visual Basic to view the SQL built into a
    > query?
    >
    > I have two databases in SQL Server.
    > One is on a SQL 7 production server,
    > and the other is on a SQL 2000 test server.
    > Both databases have tables and queries,
    > stored in SQL Server. The tables and queries
    > should be the same in both databases.
    >
    > I have a regression test suite in Visual Basic 6,
    > which verifies that if I put certain sample data
    > into the test database, the queries return
    > certain results. It also verifies that
    > the table names, query names, column names,
    > and data types match. (This is just a matter of
    > opening an empty recordset against corresponding
    > tables or queries, and comparing the fields.)
    >
    > How can I write a regression test in Visual Basic
    > to verify the query SQL? For example, if both
    > databases have a qry_ExtremeValue query,
    > how can I make sure that they both query
    > "SELECT TOP 1 * FROM tbl_base ORDER BY id ASC",
    > not
    > "SELECT TOP 1 * FROM tbl_base ORDER BY id DESC"?
    >
    > (It does not do me much good to make sure the
    > test database works fine, if the test database
    > does not have the same structure as the
    > production database.)
    >

    Greg Obleshchuk Guest

  3. #3

    Default view a query's SQL in VB

    [Reply forwarded from Devdex.com]

    Re: view a query's SQL in VB
    From: Greg Obleshchuk
    Date Posted: 7/8/2003 9:46:00 PM

    Jasper,
    If I read this right, the object you want to use is
    SQLDMO, you can look at
    the DDL for any object.

    You can also try and use my doentation tool .
    [url]http://www.ag-software.com/ags_scribe_index.asp[/url] the MDB it
    makes can be
    used simply to compare SQL text. 30 free trial

    Or try SQL Compare from Red-Gate [url]http://www.red-gate.com/[/url]

    --
    I hope this helps
    regards
    Greg O MCSD
    SQL Scribe Doentation Builder
    Doent any SQL server database in minutes
    Programmers love it, DBA dream of it
    AGS SQL Scribe download a 30 day trial today
    [url]http://www.ag-software.com/ags_scribe_index.asp[/url]
    > How can I write a regression test in Visual Basic
    > to verify the query SQL? For example, if both
    > databases have a qry_ExtremeValue query,
    > how can I make sure that they both query
    > "SELECT TOP 1 * FROM tbl_base ORDER BY id ASC",
    > not
    > "SELECT TOP 1 * FROM tbl_base ORDER BY id DESC"?
    Jasper Paulsen Guest

  4. #4

    Default view a query's SQL in VB

    Thanks, Greg.

    This code does the trick.
    It does some parsing the SQLDMO.View.Script,
    which might change in future versions
    of SQL Server:

    Function GetQuery(ByVal strServerName As String, _
    ByVal strLoginName As String, _
    ByVal strPassword As String, _
    ByVal strDatabaseName As String, _
    ByVal strQueryOwner As String, _
    ByVal strQueryName As String) As String

    Dim dmoSqlServer As SQLDMO.SQLServer
    Dim dmoDb As SQLDMO.Database
    Dim dmoView As SQLDMO.View
    Dim strScript As String

    Set dmoSqlServer = New SQLDMO.SQLServer
    Call dmoSqlServer.Connect(strServerName, strLoginName,
    strPassword)
    Set dmoDb = dmoSqlServer.Databases.Item
    (strDatabaseName)
    Set dmoView = dmoDb.Views(strQueryName, strQueryOwner)
    strScript = UCase(dmoView.Script)

    Dim strResult As String
    strResult = GetTextBetweenXandYinZ("CREATE VIEW ",
    vbCrLf & "GO" & vbCrLf, strScript)
    If strResult <> "" Then
    strResult = "CREATE VIEW " & strResult
    End If
    GetQuerySqlPerSqlServer = strResult
    End Function



    Private Function GetTextBetweenXandYinZ(ByVal strX As
    String, _
    ByVal strY As
    String, _
    ByVal strZ As
    String) As String

    Dim strRest As String
    Dim lngPosX As Long
    Dim lngPosY As Long
    strRest = strZ
    lngPosX = InStr(strRest, strX)
    If lngPosX = 0 Then
    GetTextBetweenXandYinZ = ""
    Exit Function
    End If
    strRest = Mid(strRest, lngPosX)
    strRest = Mid(strRest, Len(strX) + 1)

    lngPosY = InStr(strRest, strY)
    If lngPosY = 0 Then
    GetTextBetweenXandYinZ = ""
    Exit Function
    End If
    strRest = Left(strRest, lngPosY - 1)

    GetTextBetweenXandYinZ = strRest
    End Function

    Jasper Paulsen Guest

Similar Threads

  1. Query Drop View
    By ftroute in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 18th, 02:26 PM
  2. Query, view join question.
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 8
    Last Post: January 6th, 09:19 PM
  3. Query, view join question.
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 6th, 06:09 PM
  4. Using a query on a query (or view)
    By John Victor in forum PHP Development
    Replies: 4
    Last Post: August 31st, 09:51 AM
  5. Access query Sql View
    By rking in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 06:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139