Solved

XP_CMDSHELL ERROR (SQL SERVER 2005)

Posted on 2011-03-10
4
628 Views
Last Modified: 2012-05-11
Hi, Guys:

I have a need to execute a string from the command shell using xp_cmdshell (see below for script).

I am having problem getting the string in the correct “format”; I believe I need an escape character …????

I will appreciate your input, only if you can spare the time.

Thanks,


declare @sqlstr varchar(1000), @mlsnum varchar(16)
set @mlsnum = '11-512323'
--
set @sqlstr = (select top 1 '"C:\Program Files (x86)\Internet Explorer\iexplore.exe" http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251&save=false&mlsnum=' +  @mlsnum) --cast(1234 as sysname)

print @sqlstr
-- above returns the string below:
-- "C:\Program Files (x86)\Internet Explorer\iexplore.exe" http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251&save=true&mlsnum=11-512323
--
exec xp_cmdshell @sqlstr
--
-- above gives error below:
--'save' is not recognized as an internal or external command,
--operable program or batch file.
--'mlsnum' is not recognized as an internal or external command,
--operable program or batch file.
--NULL


-- NOTE:
--If I take the URL http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251&false=true&mlsnum=11-512323
--and paste in IE, it works fine.
0
Comment
Question by:dteshome
  • 3
4 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35101966

include the parameter in quotes


set @sqlstr = (select top 1 '"C:\Program Files (x86)\Internet Explorer\iexplore.exe"  "http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251&save=false&mlsnum=' +  @mlsnum + '"') --cast(1234 as sysname)

Open in new window

0
 

Author Comment

by:dteshome
ID: 35105799
It still gives the following error when the XP_... executes the string:

'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
'save' is not recognized as an internal or external command,
operable program or batch file.
'mlsnum' is not recognized as an internal or external command,
operable program or batch file.
NULL

However, when I took the string and run in the cmd shell it works.
0
 

Accepted Solution

by:
dteshome earned 0 total points
ID: 35159123
I figured it out!  The problem is with the "extended chars" - & etc.

Placing a ^ infront of the extended chars fixes the problem. Please see below

set @sqlstr = (select top 1 '"C:\Program Files (x86)\Internet Explorer\iexplore.exe" http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251^&save=true^&mlsnum=' +  @mlsnum) --cast(1234 as sysname)
0
 

Author Closing Comment

by:dteshome
ID: 35187401
None of the sol'n given earlier worked.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question