Oct 9, 2008 at 7:44 PM
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?
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
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
Thank you very much!!!
Its working perfectly! My failure was to select a CELLSEt and not a COMMAND.
Now everything is fine - fantastic!