By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

XML output serialized for remote query

P: n/a
Geo
Hi,

Im running into a strange problem here!
I have created a linked server on one of my SQL Servers (local server).
Lets call the linked server as LS1.

I have an sproc which resides on the remote server(LS1) and uses a FOR
XML query.
On running this sproc from my local server using linked server LS1, I
get a strange serialized data as output instead of the usual XML
output.

I run the proc as
exec [LS1].[DBName].[UserName].[SprocName] '12/1/2004','12/3/2004'

the proc accepts 2 parameters as dates.

The output generated is smth like:
0x44046E0061006D0065004404440061.......(long string of hex codes)
Looks like the ouput is getting serialized. How can I avoid
serialization OR How to de-serialize this data?
Please Help

Thanks,
Geo

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"Geo" <cy**********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hi,

Im running into a strange problem here!
I have created a linked server on one of my SQL Servers (local server).
Lets call the linked server as LS1.

I have an sproc which resides on the remote server(LS1) and uses a FOR
XML query.
On running this sproc from my local server using linked server LS1, I
get a strange serialized data as output instead of the usual XML
output.

I run the proc as
exec [LS1].[DBName].[UserName].[SprocName] '12/1/2004','12/3/2004'

the proc accepts 2 parameters as dates.

The output generated is smth like:
0x44046E0061006D0065004404440061.......(long string of hex codes)
Looks like the ouput is getting serialized. How can I avoid
serialization OR How to de-serialize this data?
Please Help

Thanks,
Geo


According to Books Online you can't use FOR XML "for any selections that do
not produce direct output to the client" (see "Guidelines for Using the FOR
XML Clause"), but I have no idea whether or not this includes using it
across a linked server. You might want to try posting in
microsoft.public.sqlserver.xml to get a better answer.

Simon
Jul 23 '05 #2

P: n/a
[posted and mailed, please reply in news]

Geo (cy**********@gmail.com) writes:
Im running into a strange problem here!
I have created a linked server on one of my SQL Servers (local server).
Lets call the linked server as LS1.

I have an sproc which resides on the remote server(LS1) and uses a FOR
XML query.
On running this sproc from my local server using linked server LS1, I
get a strange serialized data as output instead of the usual XML
output.

I run the proc as
exec [LS1].[DBName].[UserName].[SprocName] '12/1/2004','12/3/2004'

the proc accepts 2 parameters as dates.

The output generated is smth like:
0x44046E0061006D0065004404440061.......(long string of hex codes)
Looks like the ouput is getting serialized. How can I avoid
serialization OR How to de-serialize this data?


Normally, you should not use MSDASQL, the OLE DB over ODBC provider
to access SQL Server, but you should use SQLOLEDB, since that's one
less layer.

But there are always exceptions, and this is one! For some reason SQLOLEDB
receives FOR XML output as binary object, and that is not that useful.
But set up your linked server with MSDASQL instead:

sp_addlinkedserver LS1, '', 'MSDASQL', 'LS1'

The first LS1 is just an alias. The second LS1 is actually the name
of the server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

P: n/a
Geo
Thanks Simon and Erland.

Erland,
The linked server SQL statement u gave does not work for me
(InitProvider failed).
Seems like the Provider drivers are not installed on my server.

However, found a useful link:
http://www.awprofessional.com/articl...2307&seqNum=16

I am partly done with it. Need to find a way to overcome the limitation
of 8000 chars in XML. I tried making the XML output as text column, but
doesnt work. Still working on it...

BTW: Can anyone tell me how to get the complete output of a SQL query
(that returns long strings) in Query Analyzer. I tried all options
(Save to file, grid and text)
None helpful.

Thanks All,
Geo

Jul 23 '05 #4

P: n/a
Geo (cy**********@gmail.com) writes:
Erland,
The linked server SQL statement u gave does not work for me
(InitProvider failed).
Seems like the Provider drivers are not installed on my server.
Nah, the likelyhood that the MSDASQL provider is not installed on
your box is about nil, as MSDASQL comes with the MDAC.

It might be that you need to specify a more eloquent provider string
that I had in my example. Look at sp_addlinkedserver in Books Online
for details.

Then again, it appears that you get some unmotivated line breaks in
the output, so it may be better to pursue the tricks from Ken
Henderson that you found.
BTW: Can anyone tell me how to get the complete output of a SQL query
(that returns long strings) in Query Analyzer. I tried all options
(Save to file, grid and text)
None helpful.


You can set the maximum number of characters per column under
Tools->Options->Results, but it won't let you specify anything
bigger than 8192.

You could try the command-line tool OSQL. OSQL folds line when the
exceed the maximum, which is 80 by default, but you can change that
with the -w option. Don't know if you can say -w 100000 though.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

P: n/a
Geo
The XML stuff was taking too much time, so I dropped the idea for now.
thanks all...

Erland ! Thanks for isqlw tip.
Hmm... Tools->Options->Results
I should have thought abt that.

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.