Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sggy
Starting Member
8 Posts |
 Posted - 2007-05-29 : 11:30:50
|
| Hi, Does anyone know if it is possible to execute a stored procedure with input parameter that contains < or > from OSQL? ex. 'OSQL -SLocalhost -E -dTestDB -Q"EXEC GenXML <RootNode/>" -h-1 -w250'Thank you, Natalya |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-29 : 11:39:02
|
| Should you enclose parameter to SP in single quotes?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
sggy
Starting Member
8 Posts |
Posted - 2007-05-30 : 13:03:09
|
| I tried. It actually works from OSQL, but the problem is that i invoke OSQL as a command from master..xp_cmdshell. I have an SP: accepts a parameter and returns XML. My goal is to trap XML inside SQL. I call xp_cmdshell to execute the command (run my SP) and trap the output XML into a temp table.INSERT INTO #TempXML (XMLData1) EXEC master.dbo.xp_cmdshell 'OSQL -SLocalHost -E -dTestDB -Q"EXEC GenXML <Guy/>" -h-1 -w250'SELECT * FROM #TempXML Error: incorrect syntax near '<'. This works from cmd using OSQL. |
|
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-05-30 : 13:13:00
|
| Try enclosing the parameter you pass to your SP in single-quotes:EXEC master.dbo.xp_cmdshell 'OSQL -SLocalHost -E -dTestDB -Q"EXEC GenXML ''<Guy/>''" -h-1 -w250'SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2007-05-31 : 04:52:16
|
Try^<AndyBeauty is in the eyes of the beerholder  |
|
|
|
sggy
Starting Member
8 Posts |
Posted - 2007-06-05 : 12:07:25
|
| Thanks everyone for your help. I made it work. The solution was to enclose the input xml string into two single quotes and for every attribute value inside the xml preceed " with Ex. EXEC master.dbo.xp_cmdshell 'OSQL -SLocalHost -E -dTestDB -Q"EXEC GenXML ''<Root Room=\"URI\"/>''" -h-1 -w250'I have a minor problem now. if output XML is long it is broken down into several rows and there is no way to control the number of rows. I tried to play with -w parameter, but it works only for small numbers (<100), after 100 it does not matter how long is -w, xml is broken down anyway. And for some of the SPs -h-1 has no effect, header is displayed whether -h-1 is present or not.I am not so concerned about the output spread between multiple rows, but header in the return table really annoys me. Please let me know if you have any ideas how to fix it. Thank you in advance. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-05 : 12:34:18
|
maybe try using bcp.exe instead of osql.exe
elsasoft.org |
|
|
|
sggy
Starting Member
8 Posts |
Posted - 2007-06-05 : 12:57:11
|
| as i understand, bcp is for moving in/out files , but i am not moving a file content into the table, but the result of the SP |
|
|
|
|
|
|
|
|
SQL input parameter with <