Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    13

    Unanswered: getting results of sp_pkeys in a recordset

    Please tell me how to achieve this.

    rsPrimaryKey.Open "exec sp_pkeys 'dbo.tmpQuestionnaireDumpReport'"

    doesn't work

    rsPrimaryKey.Open "sp_pkeys 'dbo.tmpQuestionnaireDumpReport'"

    doesn't work either.

    I know in Query Analyzer, sp_pkeys 'dbo.tmpQuestionnaireDumpReport'
    works.

    Please help.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check this one:

    <% LANGUAGE="VBScript" %>
    <% Response.Expires =0 %>
    <HTML>
    <HEAD>
    <TITLE>
    </TITLE>
    </HEAD>
    <BODY>
    <%

    Session("db")="DSN=homebudget;UID=sa;PWD=;"

    Set Session("dbcon") = Server.CreateObject("ADODB.Connection")

    Session("dbcon").Open Session("db")

    set execommand = Server.CreateObject("ADODB.Command")

    execommand.CommandText = "sp_pkeys"
    execommand.ActiveConnection = Session("dbcon")
    execommand.CommandType = &H0004

    execommand.Parameters.Append execommand.CreateParameter("table",200,&H0001,8000 )

    execommand("table")="test"

    set rs=execommand.execute

    response.write rs(0)+"<br>"
    response.write rs(1)

    rs.close
    set rs=nothing


    %>

    </BODY>
    </HTML>

  3. #3
    Join Date
    Sep 2003
    Posts
    13
    Thank you Snail, but I tried that already. The command object doesn't work with sp_pkeys in VB. It would work with any other stored procedure that you define yourself.
    This is what worked for me:

    select Column_Name
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    ON t.Constraint_Name = c.Constraint_Name
    where t.table_name = 'lkuDataAudit'
    AND t.Constraint_Type = 'PRIMARY KEY'

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by archnam
    Thank you Snail, but I tried that already. The command object doesn't work with sp_pkeys in VB. It would work with any other stored procedure that you define yourself.
    This is what worked for me:

    select Column_Name
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    ON t.Constraint_Name = c.Constraint_Name
    where t.table_name = 'lkuDataAudit'
    AND t.Constraint_Type = 'PRIMARY KEY'
    It works in ASP it has to work in VB (I did not test it in VB).
    You have to run command as stored procedure for sp_pkeys.

Posting Permissions

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