Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-06 : 16:18:09
|
Joon writes "Hi there.
My Query is in two parts:
1.) I am trying to access an XML template via ADO, using the stream objects. This is fairly straightforward, and does work. So now, having harnessed the power of XML templates via ADO, I would like to send parameters into the query.
That is, I want to send parameters via ADO the way I would send them as part of the URL to a template from a web browser.
Q272266 from the MSDN online site said that this principle should work
My Template looks like this:
Real XML :
5
Select @AParm as 'Param' for XML Raw
Browser friendly version:
5
Select @AParm as 'Param' for XML Raw
The template is called XmlParmUsage.xml.
As you can see, it has a parameter called AParm, with a value of 5.
My code is written in VB, and looks as follows:
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cmdStream As New ADODB.Stream
Dim cmdOutput As New ADODB.Stream
' Force ADO to pass parm names as well as values
cmd.NamedParameters = True
' Open the stream
cmdStream.Open
' set the character set to ASCII
cmdStream.Charset = "ascii"
' set the command stream type to text, not binary
cmdStream.Type = adTypeText
' Load the SQL template
cmdStream.LoadFromFile App.Path & "\XmlParmUsage.xml"
' Activate the output stream
cmdOutput.Open
With cmd
.Parameters.Append .CreateParameter("AParm", adInteger, _
adParamInput, 4, 10)
End With
cn.Open DBConn
' set the command connection
Set cmd.ActiveConnection = cn
' set the command's command stream, thereby hooking the
' template query to the command you want to execute.
Set cmd.CommandStream = cmdStream
' set the command dialect to DBGUID_DEFAULT
cmd.Dialect = "{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}"
' set up the output stream that will receive the output of
' the command execute
cmd.Properties("Output Stream") = cmdOutput
' Set parameter information (This does not work, template is
' not receiving data)
cmd.Execute , , adExecuteStream
' Show the output of the query
MsgBox cmdOutput.ReadText
End Sub
This code is inside of a VB Project, and the project has the MS ADO library 2.6 included
in its references.
When this code runs, it returns XML in the messagebox with AParm = 5, as specified
in the template. The value I send in through the ADO parameter does not appear.
How can I send parameters to a template via ADO?
2.) Our installation of SQL Server 2000 has a problem with errors raised by the database
from inside templates. For some reason, the error description is not returned. We have tried
it on different machines, as well as with case sensitive and case insensitive installations
of SQL 2000.
When we get an error message, it looks as follows:
Real XML :
Browser friendly version:
Thank you for your time
Joon du Randt
Developer, Sense-I Management Systems" |
|
|
|
|
|
|
|
Parameterizing SQL Server 2000 XML Query templates using ADO 2.6