Accesing to a web page | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Accesing to a web page

I’m trying to execute a http from a DTS package.
Is it possible? Can anybody help me? Marcela
From Books on Line:<br /><br />Executing SQL Statements Using HTTP<br />Microsoft® SQL Serverâ„¢ 2000 can be accessed directly by queries executed at the URL (if the allow URL queries option was selected when the virtual root was registered). Clients can make requests using HTTP methods GET and POST. For more information about the URL syntax that is support by the SQL ISAPI extension, see URL Access.<br /><br />Examples<br />In the following examples, nwind is a virtual directory used to access the Northwind database. For more information about creating the nwind virtual directory, see Creating the nwind Virtual Directory.<br /><br />In the example queries, if a query returns more than one element at the root of the document, the root element can be added by including either of these: <br /><br />A SELECT &lt;ROOT&gt; in the query.<br /><br /><br />Passing a root keyword as a parameter to the query with a value ROOT (this value can be anything). <br />A. Specify a simple query<br />The following statement returns all of the customer data in the Customers table in the Northwind database. In this query, the XML mode is set to RAW.<br /><br /<a target="_blank" href=http://IISServer/Nwind?sql=SELECT+top+2+CustomerID>http://IISServer/Nwind?sql=SELECT+top+2+CustomerID</a>,+ContactName+FROM+Customers+FOR+XML+RAW&root=ROOT<br /><br />Here is the result set:<br /><br />&lt;ROOT&gt;<br /> &lt;Customers CustomerID="ALFKI" ContactName="Maria Andears" /&gt; <br /> &lt;Customers CustomerID="ANATR" ContactName="Ana Trujillo" /&gt; <br />&lt;/ROOT&gt;<br /><br />B. Specify a query on multiple tables<br />In this example, the SELECT statement returns information from the Customers and Orders tables in the Northwind database. The XML mode is set to AUTO.<br /><br /<a target="_blank" href=http://IISServer/nwind?sql=SELECT+top+2+Customers.CustomerID>http://IISServer/nwind?sql=SELECT+top+2+Customers.CustomerID</a>,OrderID,OrderDate+FROM+Customers,+Orders+WHERE+Customers.CustomerID=Orders.CustomerID+Order+by+Customers.CustomerID,OrderID+FOR+XML+AUTO&root=ROOT<br /><br />This is the partial result:<br /><br />&lt;ROOT&gt;<br /> &lt;Customers CustomerID="ALFKI"&gt;<br /> &lt;Orders OrderID="10643" OrderDate="1997-08-25T00:00:00" /&gt; <br /> &lt;Orders OrderID="10692" OrderDate="1997-10-03T00:00:00" /&gt; <br /> &lt;/Customers&gt;<br /> &lt;Customers CustomerID="ANATR"&gt;<br /> &lt;Orders OrderID="10308" OrderDate="1996-09-18T00:00:00" /&gt; <br /> &lt;/Customers&gt;<br />&lt;/ROOT&gt;<br /><br />C. Specify special characters in the query<br />The following query returns all distinct contact titles starting with Sa from the Customers table in the Northwind database. The example uses the LIKE clause and the special character % to search for the contact titles. In the LIKE clause, the special character % is specified as %25.<br /><br /<a target="_blank" href=http://IISServer/nwind?sql=SELECT+DISTINCT+ContactTitle+FROM+Customers+WHERE+ContactTitle+LIKE+’Sa%25’+ORDER+BY+ContactTitle+FOR+XML+AUTO&root=root>http://IISServer/nwind?sql=SELECT+DISTINCT+ContactTitle+FROM+Customers+WHERE+ContactTitle+LIKE+’Sa%25’+ORDER+BY+ContactTitle+FOR+XML+AUTO&root=root</a><br /><br />Here is the result set:<br /><br />&lt;ROOT&gt;<br /> &lt;Customers ContactTitle="Sales Agent" /&gt; <br /> &lt;Customers ContactTitle="Sales Associate" /&gt; <br /> &lt;Customers ContactTitle="Sales Manager" /&gt; <br /> &lt;Customers ContactTitle="Sales Representative" /&gt; <br />&lt;/ROOT&gt;<br /><br />In the following example, order and order detail information is retrieved from the Orders and Order Details tables. <br /><br /<a target="_blank" href=http://IISServer/nwind?sql=SELECT+’&lt;ROOT&gt;’+SELECT+Orders.OrderID>http://IISServer/nwind?sql=SELECT+’&lt;ROOT&gt;’+SELECT+Orders.OrderID</a>,+[Order+Details].OrderID,[Order+Details].ProductID,[Order+Details].UnitPrice+FROM+Orders,+[Order+Details]+WHERE+Orders.OrderID=[Order+Details].OrderID+ORDER+BY+Orders.OrderID+FOR+XML+AUTO;SELECT+’&lt;/ROOT&gt;'<br /><br />This is the partial result:<br /><br />&lt;ROOT&gt;<br />&lt;Orders OrderID="10248"&gt;<br /> &lt;Order_x0020_Details OrderID="10248" ProductID="11" UnitPrice="14.00" /&gt; <br /> &lt;Order_x0020_Details OrderID="10248" ProductID="42" UnitPrice="9.80" /&gt; <br />&lt;/Orders&gt;<br />&lt;Orders OrderID="10249"&gt;<br /> &lt;Order_x0020_Details OrderID="10249" ProductID="14" UnitPrice="18.60" /&gt; <br />&lt;/Orders&gt;<br />&lt;/ROOT&gt;<br /><br />D. Specify a query without the FOR XML clause<br />You can specify SQL queries without the FOR XML clause. The result is returned as a stream. In the query, you can specify only one column because streaming is not supported over multiple column results. In this example, the query returns the first name of employees from the Employees table in the Northwind database. The result is returned as a concatenated string of first names.<br /><br /<a target="_blank" href=http://IISServer/nwind?sql=SELECT+FirstName+FROM+Employees>http://IISServer/nwind?sql=SELECT+FirstName+FROM+Employees</a><br /><br />E. Specify the contenttype keyword<br />The contenttype keyword specifies the content-type of the document returned. text/XML is the default content-type of the document except when xsl is specified in the URL. When xsl is specified in the URL and contenttype is not specified, then contenttype defaults to text/html. <br /><br />In this example, the query returns a picture of an employee from the Employees table in the Northwind database. FOR XML mode is not specified because the returned data is compatible with the receiving application (that is, the browser can handle the returned data).<br /><br /<a target="_blank" href=http://IISServer/nwind?sql=SELECT+Photo+FROM+Employees+WHERE+EmployeeID=1>http://IISServer/nwind?sql=SELECT+Photo+FROM+Employees+WHERE+EmployeeID=1</a><br /><br />In retrieving images, contenttype is generally specified. If contenttype is specified, the ISAPI extension does not search for and remove any Access header information. Therefore, to retrieve any images that have the Access header information, contenttype should not be specified as shown in the previous example. In all other cases, contenttype should be specified as shown in this example:<br /><br /<a target="_blank" href=http://iisserver/virtualroot?sql=SELECT+Picture+FROM+TableName+WHERE+SomeID=1&contenttype=image/jpeg>http://iisserver/virtualroot?sql=SELECT+Picture+FROM+TableName+WHERE+SomeID=1&contenttype=image/jpeg</a><br /><br />The images can also be brought into an HTML document. In the following example, an .htm file (File1.htm) is created with these contents:<br /><br />&lt;img src="http://IISServer/nwind?sql=select photo from Employees where EmployeeID=1"&gt;<br /><br />When this file is opened in the browser, an employee photo is displayed.<br /><br />F. Specify the xsl keyw ord<br />In this example, the query returns the first and last name of all employees in the Employee table in the Northwind database. employee.xsl processes the result set. <br /><br />When xsl is specified in the URL but contenttype is not specified in the URL and there is no content-type defined in the XSL style sheet, contenttype defaults to text/html. Therefore, the result is displayed in the form of a table with two columns (firstname, lastname).<br /><br /<a target="_blank" href=http://IISServer/nwind?sql=SELECT+FirstName>http://IISServer/nwind?sql=SELECT+FirstName</a>,LastName+FROM+Employees+FOR+XML+AUTO&xsl=employee.xsl&root=root<br /><br />The .xsl file is provided here. This file must exist in the virtual root directory or one of its subdirectories (in which case the file path specified is relative to the virtual root directory). In this example, the .xsl file is stored in the virtual root directory.<br /><br />&lt;?xml version=’1.0′ encoding=’UTF-8′?&gt; <br /> &lt;xsl<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tylesheet xmlns<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />sl="" version="1.0"&gt; <br /> &lt;xsl:template match = ‘*’&gt; <br /> &lt;xsl:apply-templates /&gt; <br /> &lt;/xsl:template&gt; <br /> &lt;xsl:template match = ‘Employees’&gt; <br /> &lt;TR&gt; <br /> &lt;TD&gt;&lt;xsl:value-of select = ‘@FirstName’ /&gt;&lt;/TD&gt; <br /> &lt;TD&gt;&lt;B&gt;&lt;xsl:value-of select = ‘@LastName’ /&gt;&lt;/B&gt;&lt;/TD&gt;<br /> &lt;/TR&gt; <br /> &lt;/xsl:template&gt;<br /> &lt;xsl:template match = ‘/’&gt; <br /> &lt;HTML&gt; <br /> &lt;HEAD&gt; <br /> &lt;STYLE&gt;th { background-color: #CCCCCC }&lt;/STYLE&gt; <br /> &lt;/HEAD&gt; <br /> &lt;BODY&gt; <br /> &lt;TABLE border=’1’ style=’width:300;’&gt; <br /> &lt;TR&gt;&lt;TH colspan=’2’&gt;Employees&lt;/TH&gt;&lt;/TR&gt; <br /> &lt;TR&gt;&lt;TH &gt;First name&lt;/TH&gt;&lt;TH&gt;Last name&lt;/TH&gt;&lt;/TR&gt; <br /> &lt;xsl:apply-templates select = ‘root’ /&gt; <br /> &lt;/TABLE&gt; <br /> &lt;/BODY&gt; <br /> &lt;/HTML&gt; <br /> &lt;/xsl:template&gt; <br />&lt;/xsl<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tylesheet&gt;<br /><br />Instead of specifying the contenttype in the URL, contenttype can also be specified as the value of the media-type attribute of the &lt;xsl<img src=’/community/emoticons/emotion-3.gif’ alt=’:eek:‘ />utput&gt; element. For example, &lt;xsl<img src=’/community/emoticons/emotion-3.gif’ alt=’:eek:‘ />utput media-type="text/html" /&gt; can be added after the namespace declaration in the preceding XSL file.<br /><br />G. Pass parameters to SQL statements<br />Parameters can be passed to SQL queries. In this example, employee information for a given employee ID is returned from the Employees table in the Northwind database. The value of EmployeeID is provided as input to the query. Note that the ? character, used for a parameter marker in the URL, is a special character and is encoded as %3F. For more information about special characters, see Special Characters.<br /><br /<a target="_blank" href=http://IISServer/nwind?sql=SELECT+FirstName>http://IISServer/nwind?sql=SELECT+FirstName</a>,LastName+FROM+Employees+WHERE+EmployeeID=%3F+FOR+XML+AUTO&EmployeeID=1&root=ROOT<br /><br />Here is the result set:<br /><br />&lt;ROOT&gt;<br /> &lt;Employees FirstName="Nancy" LastName="Davolio" /&gt; <br />&lt;/ROOT&gt;<br /><br />In this query, two parameter values are passed to the query:<br /><br /<a target="_blank" href=http://IISServer/nwind?sql=SELECT+’&lt;ROOT&gt;’;SELECT+EmployeeID>http://IISServer/nwind?sql=SELECT+’&lt;ROOT&gt;’;SELECT+EmployeeID</a>,Title+FROM+Employees+WHERE+LastName=%3F+and+FirstName=%3F+FOR+XML+AUTO;SELECT+’&lt;/ROOT&gt;’&LastName=Davolio&FirstName=Nancy<br /><br />Here is the result set:<br /><br />&lt;ROOT&gt;<br /> &lt;Employees EmployeeID="1" Title="Sales Representative" /&gt; <br />&lt;/ROOT&gt;<br /><br /><br />See Also<br /><br />Accessing SQL Server Using HTTP<br /><br />Retrieving XML Documents Using FOR XML<br /><br />Using IIS Virtual Directory Management for SQL Server Utility<br /><br /><br />You can write your queries and use DTS to execute it.<br /><br /><br />Luis Martin<br />Moderator<br /><br /><br /><font size="1">Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.<br />Leonardo Da Vinci<br /></font id="size1"><br /><font size="1"> Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
I think I made a mistake when I tried to explain the problem I have. I need to execute a http "service". It´s a program that corrects an address. I pass some arguments and execute it.
That is an example:…gentine&province=bs as&submit=Correct Address I need to execute it from SQL Server. (Sorry if my english isn´t very good [:I]) Thanks!!!
Not sure if this is what you need, but have a look at the Execute Process Task in a DTS package. —
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?

Thanks but I need an exe file to execute this and I haven´t got one
It´s a service that I use through a http address

Have you checked yet? —
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?

May check fyi. HTH Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks to all!![<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />I finally make a bat that calls the HTTP address, because I couldn´t find any proper answer to my question.<br /><br />Regards,<br />Marcela