Configure SQL XML Support in IIS available?

Discussion in 'Databases' started by charle, Jul 16, 2005.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. According to MS-SQL Online Books, to configure MS-SQL to accept queries by HTTP, I must create a virtual directory using a utility called IIS Virtual Directory Management for SQL Server. Can I do this in my DiscountASP account with MS-SQL addon?
     
  2. Bruce

    Bruce DiscountASP.NET Staff

    SQLXML is installed on the server, i do not believe that you need to create any VD to use it.

    Bruce

    DiscountASP.NET
    www.DiscountASP.NET
     
  3. Bruce,


    The following is pasted from MS-SQL Books Online. Note that SQL requests via HTTP arrive at the server and are handled first by IIS. The VD stuff tells IIS how to send the request to SQL Server.
    Using IIS Virtual Directory Management for SQL Server Utility


    Before accessing a Microsoft® SQL Server? 2000 database using HTTP, you must set up an appropriate virtual directory. Use the IIS Virtual Directory Management for SQL Server utility (click Configure SQL XML Support in IIS in the SQL Server Tools program group) to define and register a new virtual directory, also known as the virtual root, on the computer running Microsoft Internet Information Services (IIS). This utility instructs IIS to create an association between the new virtual directory and an instance of Microsoft SQL Server. For information about the user interface for this utility, see IIS Virtual Directory Management Utility.


    The name of the IIS server and the virtual directory must be specified as part of the URL. The information in the virtual directory (including login, password, and access permissions) is used to establish a connection to a specific database and execute the query.


    The URL can be specified to:
    <UL type=disc>
    [*]Directly access the database objects, such as tables.
    In this case, the URL would include a virtual name of dbobject type.
    [*]Execute template files.
    A template is a valid XML document consisting of one or more SQL statements. When a template file is specified at the URL, the SQL commands stored in the template file are executed. SQL queries can be directly specified at the URL, but this is not recommended for security reasons.
    [*]Execute XPath queries.
    The XPath queries are executed against an annotated mapping schema file specified as part of the URL.[/list]
    Virtual Names


    To allow a template file, mapping schema file, or a database object (such as a table or view) as part of the URL, virtual names of type template, schema, and dbobject must be created. The virtual name is specified as part of the URL to execute a template file, an XPath query against a mapping schema file, or to access a database object directly.


    The type (template, schema, dbobject) of the virtual name specified at the URL is also used to determine the file type specified at the URL (template file or a mapping schema file). For example, this URL accesses a SQL Server database using a template:
    Code:
    [url]http://IISServer/nwind/TemplateVirtualName/Template.xml[/url]
    

    TemplateVirtualName is a virtual name of template type, which identifies that the specified file (Template.xml) is a template file.<!--RELATEDTOPICSLIST-->

    See Also





    Accessing SQL Server Using HTTP<!--/RELATEDTOPICSLIST--><!--END-->
     
  4. Bruce

    Bruce DiscountASP.NET Staff

    i think you mistaken this, SQLXML is installed on the web server.

    You cannot talk directly to the SQL server because it is not exposed to the Internet.

    See SQL XML in action in a test site

    http://www.iis6hosting.com/asp/sqlxml/sqlxml.asp

    Code as follow

    <%

    Response.ContentType = 'text/xml'
    Dim oCmd, sSQL

    sSQL = '<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>' & _
    'SELECT * FROM tblContact FOR XML AUTO </sql:query></root>'

    Set oCmd = Server.CreateObject('ADODB.Command')
    oCmd.ActiveConnection = ('Provider=sqlxmloledb.3.0;data provider=sqloledb;Data Source=mssql01;user id=xxxx;password=xxxx;Initial Catalog=db_1234')

    oCmd.CommandText = sSQL
    oCmd.Dialect = '{5D531CB2-E6Ed-11D2-B252-00C04F681B71}'

    oCmd.Properties('Output Stream') = Response
    oCmd.Execute , , 1024

    Set oCmd = Nothing

    %>

    Bruce

    DiscountASP.NET
    www.DiscountASP.NET
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page