Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Parameterizing SQL Server 2000 XML Query templates using ADO 2.6
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 12/06/2000 :  16:18:09  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 :


Select @AParm as 'Param' for XML Raw

Browser friendly version:


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
' 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

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"

Most Valuable Yak

15732 Posts

Posted - 12/06/2000 :  22:26:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
In your code, you specify a value of 10 for Aparm, not 5. Maybe that value doesn't return anything?

I don't know anything about XML templates (still using 6.5!) so I could be wrong.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000