Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

VB6: How to call SQLServer 2000 system procedure sp_fkeys

This is a frustrating one.  If I use "sp_fkeys 'tblControl'" in Query Analzyer, I get the information I'm after (relationships the table is participating in).  HOW do I call this stored procedure from VB6?? It's driving me nuts!

I've tried the code below (which I got off the web somewhere) and passed in "sp_fkeys" and "tblControl".  I don't get any errors, but the recordset has no records in it.  When I run it through Query Analyser, 3 records return (which is correct, since the table is participating in three relationships):

Public Function CallStoredProc(sProcName As String, sParameter As String) As Integer
    'TH 22/01/01 call a stored procedure from vb
    Dim cmd As New Command
    Dim prm As New ADODB.Parameter
    Dim rs As New ADODB.Recordset
    'create new stored proc object
    Set cmd = New ADODB.Command
    'add a paramter with value of "sParameter"
    Set prm = cmd.CreateParameter("param1", adBSTR, adParamInput, 1, sParameter)

    With cmd
        .Parameters.Append prm
        .ActiveConnection = gcn
        .CommandType = adCmdStoredProc
        .CommandText = sProcName
    End With
   
    Set rs = cmd.Execute
   
    If Not rs.State = adStateOpen Then
        rs.Open
    End If
    If gcn.Errors.Count > 0 Then
        MsgBox gcn.Errors.Item(0)
    End If
    'Set frmMain.list1.DataSource = rs
    MsgBox rs.RecordCount
End Function
0
MushyPeas
Asked:
MushyPeas
1 Solution
 
leonstrykerCommented:
Hmm, try this:

Public Function CallStoredProc(sProcName As String, sParameter As String) As Integer
Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "Exec " & sProcName & "'" & sParameter & "'"
    MsgBox rs.RecordCount
    CallStoredProc = rs.RecordCount
End Function


Leon
0
 
MushyPeasAuthor Commented:
Thank you!!!  Can you explain this to me - why "Exec".  Is this not the same as saying cmd.execute (which I also tried)

Is this an acceptable/efficient/safe method to use?

Thanks

M
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now