Configuring SQL XML support in IIS

• zahi antoine

  SSC Enthusiast

  Points: 127

  More actions

  June 22, 2005 at 3:37 am

  #88747

  Hi,

  I used "Creating the nwind Virtual Directory" from SQL Server Books Online.

  All the steps worked fine. But when i try to test the virtual directory in the browser , i type:

  "/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root" target=_blank>http://<IISServer>/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root" and i press ENTER.

  I got an error on the browser "The URL could not be retrieved""

  I did help about on my SQL Query Analyser. The Version is SQL 8.00.760

  Please help.

  Thanks&Regards.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 22, 2005 at 12:33 pm

  #567615

  an url and it's arguments may not contain spaces or other reserved characters; the argument must be urlencoded:

  SELECT+%2A+FROM+Employees+FOR+XML+AUTO&root=root

  in .net you can use system.web.httputility.urlencode i think, in asp it's server.urlencode,

  if you set your form to METHOD="GET" and you have a <INPUT TYPE="text" NAME="sql"> t this would be done automatically as part of the form's submit functionality.

  if you need a function to do it here's a vb6 version you can adapt:

  Public Function URLEncode(sRawURL As String) As String

      On Error GoTo Catch

      Dim iLoop As Integer

      Dim sRtn As String

      Dim sTmp As String

      Const sValidChars = "1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz:/.?=_-$(){}~&"

      If Len(sRawURL) > 0 Then

          ' Loop through each char

          For iLoop = 1 To Len(sRawURL)

              sTmp = Mid(sRawURL, iLoop, 1)

              If InStr(1, sValidChars, sTmp, vbBinaryCompare) = 0 Then

                  ' If not ValidChar, convert to HEX and p

                  '     refix with %

                  sTmp = Hex(Asc(sTmp))

                  If sTmp = "20" Then

                      sTmp = "+"

                  ElseIf Len(sTmp) = 1 Then

                      sTmp = "%0" & sTmp

                  Else

                      sTmp = "%" & sTmp

                  End If

              End If

              sRtn = sRtn & sTmp

          Next iLoop

          URLEncode = sRtn

      End If

  Finally:

      Exit Function

  Catch:

      URLEncode = ""

      Resume Finally

  End Function

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!