July 18, 2004 at 12:58 pm
Hi all. This is not really a SQL problem, but I am hoping one of you may have envoutered this problem before.
I have created a Visual Basic 6 ActiveX dll, and am using the code to save a recordset as an XML file (by the way, I am experimenting in ways of converting a query to a XML file and by pass the 'chopping off' problem.). The dll works fine, and I can call it from another VB program. The VB object browser can see the DLL and it's method. The code is listed below.
I use sp_addextendedproc to register my dll with SQL:
sp_addextendedproc xp_WriteXML, DoXMLv3
When I try to exec the sproc:
EXEC xp_WriteXML
I get this:
ODBC: Msg 0, Level 16, State 1
Cannot find the function xp_WriteXML in the library DoXMLv3. Reason: 127(The specified procedure could not be found.).
I have registered the DLL on the server (in winnt\system32).
BOL only has examples for C code, and I am not familiar with C. They say you are supposed to 'export' the function (xp_WriteXML), and I am doing that as far as I can understand. I am sure I am missing some simple step! Can anyone help me?
My VB code is here:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
Persistable = 0 'NotPersistable
DataBindingBehavior = 0 'vbNone
DataSourceBehavior = 0 'vbNone
MTSTransactionMode = 0 'NotAnMTSObject
END
Attribute VB_Name = "xp_WriteXML"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Public Function xp_WriteXML() As String
Dim sXMLFilename As String
Dim sQueryString As String
Dim objADORS As New ADODB.Recordset
sXMLFilename = "C:\XMLDoc.xml"
sQueryString = "SELECT CustnoID FROM Customer"
objADORS.Open sQueryString, _
"PROVIDER=SQLOLEDB.1;SERVER=server;uid=user;pwd=password;DATABASE=mydb;"
objADORS.Save sXMLFilename, adPersistXML
objADORS.Close
Set objADORS = Nothing
End Function
July 19, 2004 at 1:45 am
You can't write extended stored procedures with VB.
They require specific function calls that are not supported in VB.
The last time I checked C++ and Delphi were the main languages for writing extended stored procedures.
I am not sure what the long term career prospects are for C++ programmers in a Windows environment as everything seems to be VB.NET & C#.NET.
I was hoping the writing extended stored procedures would be part of the .NET framework and therefore a language neutral activity, however this doesn't seem to be the case.
I am very much in two minds as to whether making extended stored procedures easy to write it a good thing or bad.
From your example it looks like you should be looking at the built in SQL XML commands.
Don't forget you can use DTS to trigger an ACTIVEX DLL.
July 19, 2004 at 1:58 am
David: Thanks very much for confirming that! I was beginning to suspect the same!
We are using the FOR XML clause and sp_makewebtask, but have encountered that problem where if the SELECT is returning a large number of rows, the XML file gets chopped into multiple lines. If the line gets chopped in the middle of a tab, then IE errors out.
I have been asked to find a way round this. Do you have any suggestions?
Thanks! - Wayne
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply