DMV from VBA

Topics: Developer Forum, User Forum
Oct 9, 2008 at 7:44 PM
TimB83 wrote  Today at 2:34 PM

I have got a question to your "ASStoredProcedures - Function DMV" Project:
I have added the ASSP.dll to my SQLServer2005 (Assemblys). Now when I am executing statements like "CALL ASSP.DMV("SELECT * FROM $System.MDSCHEMA_DIMENSIONS");

this is working very well! But I want to select from my vba-code. Therefore I open a connection and try to execute a normal SQL-SELECT-statement - this is working very well!
But when I execute the "CALL ASSP.DMV(...-statement" then there is a message that this could not be done. I thought the problem is that I need the ASSP.dll. So therefore I did the following from my vba-code:
1) open a connection (OleDB/ado)
2) then I sent my xml-assp-file to server -> no on the server the ASSAMBLY ASSP is created automatuically (and it works very well on manual tests).
3) now I sent a normal SQL-Satement from vba-code to SQLServer2005 -> this works perfectly.
4) now I sent the ASSP-statement CALL ASSP.DMV("SELECT * FROM $System.MDSCHEMA_DIMENSIONS") from vba-code to SQLServer2005 -> result is an error!

Why isn't it working? Normal sql-statements are working very well and the connectionstring is correct? Do you have a hint for me?

Best regards,

Oct 9, 2008 at 8:56 PM
What sort of error are you getting?

I ran the following VBA using only the ADODB library and it worked fine

Sub Test()
    Dim conn As New ADODB.Connection
    conn.Open "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Adventure Works DW"
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = "CALL ASSP.DMV(""SELECT * FROM $System.MDSCHEMA_DIMENSIONS"");"
    cmd.CommandType = adCmdText
    Dim rs As Recordset
    Set rs = cmd.Execute()
    MsgBox "records: " & rs.RecordCount, vbOKOnly Or vbInformation, "Discover Query"
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing
End Sub
Oct 10, 2008 at 10:29 AM
Thank you very much!!!
Its working perfectly! My failure was to select a CELLSEt and not a COMMAND.
Now everything is fine - fantastic!

Best regards,