Are you dealing with published datasources or your datasource is embedded in the workbook?
The data source is embedded in the workbook.
How does your Initial SQL look like? I don't remember seeing any issues with not properly embedding Initial (or Custom) SQL into workbooks...
Do you mind sharing the text of your Initial SQL?
Sure, it's the same structure as this:
WHERE Account in
AND BizDate >= DATEADD(MONTH,-37,DATEADD(DAY,(DATEPART(DD,CONVERT(DATE,GETDATE()))-1)*-1,CONVERT(DATE,GETDATE())))
I cannot reproduce this locally but I suspect that something in that Initial SQL is not handled/encoded in the XML properly.
Can you download the workbook from the Server and open it with Desktop?
Next, if I'd have access to your environment, I'd look at:
a) Fiddler traces and HTTP traffic to see what [workbook] XML is getting sent over
b) Server logs to see at what step does it actually fail - parsing the workbook or running Initial SQL statement.
Maybe type in a simpler "SELECT TOP 10 * INTO #TEMP1 FROM sales" type of query to rule out problems with weird/hidden characters (sometimes happens during copy/paste)?
I started getting connection timeout errors even with the initial sql even before the XML errors. I decided to take it to the next level to make sure I got the connection first and then started the process I needed. In the initial sql I added a "SELECT TOP 1 * INTO #TEMP FROM sales" first step.
This got my connection set up to the server with a dummy temp table that I'll never use, and then go on to the sql I need. I was able to publish the workbook with this strategy without the XML error and I have closed the workbook several times and reopened with the process working.
Really strange error but it seems I have conquered it. Thanks for the help Dmitry!