August 2, 2002 at 6:24 pm
I am using sp_makewebtask in combination with a "for xml explicit" query to generate static xml files.
Problem is that my sp_makewebtask output files contain 2 unwanted CR/LF every 2033 characters. The output is fine other than that.
Does anyone know why this happens and how I can stop it?
Thanks,
Jim Bennett
August 2, 2002 at 7:59 pm
There is a trace flag that will do "pretty print" in QA, maybe you could use that. Its #257. See the following article about trace flags in general:
http://qa.sqlservercentral.com/columnists/RDyess/traceflags.asp
Andy
October 18, 2002 at 1:14 am
Have the same problem myself.
Trace Flags didn't work either...
Microsoft state that this is a restriction due to ODBC links:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q275583
They suggest using OLEDB or rewriting your ADO app to break the query into seperate components and concatenating them at the end.
However, how does one implement this solely through SQL?
October 18, 2002 at 4:32 am
By creating a DTS package or a JOB using active script to do it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 22, 2002 at 7:43 am
Do the CR/LF's effect the xml in any other way besides visually? They shouldn't effect the parsing of the xml?
October 22, 2002 at 8:56 am
The CR/LF cause the XML to be invalid so it can't be processed by an XSLT processor.
I finally just used a global search and replace program to fix the XML files after they are output.
Do the CR/LF's effect the xml in any other way besides visually? They shouldn't effect the parsing of the xml?
October 22, 2002 at 9:03 am
I think also if you use the ADO stream object, you wont have the CR/LF problem. I believe it will allow strings longer than 2033 characters.
October 22, 2002 at 9:07 am
Try something similar to this function: (ignore the comment marks)
'Function ExecuteSqlForXMLCommandWithParamsReturnXML(sCnStr, sSqlCommand, ParamAry)
'Dim Cmd
'Dim J
'Dim objCn
'Dim Str
'Dim sXML
'Dim L
'Dim U
'Set objCn = OpenConnection(sCnStr, True)
'Set Str = CreateObject("ADODB.Stream")
'Str.Type = adTypeText
'Str.Open
'Set Cmd = CreateObject("ADODB.Command")
'With Cmd
'.ActiveConnection = objCn
'.CommandText = sSqlCommand
'.CommandType = adCmdStoredProc
'.Properties("Output Stream") = str
'L = lbound(ParamAry)
'U = ubound(ParamAry)
'For J = L to U
'.Parameters.Append(.CreateParameter(ParamAry(J,0) ,ParamAry(J, 2),,ParamAry(J, 3),ParamAry(J,1)))
'Next
'End With
'Cmd.Execute , , adExecuteStream
'add parent node
'sXML = "<cnx.net>" & Str.ReadText(adReadAll) & "</cnx.net>"
'Str.Close
'Set Str = Nothing
'Set Cmd = Nothing
'Call CloseConnection(objCn)
'ExecuteSqlForXMLCommandWithParamsReturnXML = sXML
'End Function
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply