473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

XML output serialized for remote query

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
5 3370

"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
[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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: johkar | last post by:
What is the proper doctype syntax for XHTML transitional??? Strict? How come XSLT doesn't preserve XHTML when it is compiled (Xalan)? Meaning, <br /> tags become <br> <input /> tags become...
5
by: ajholme | last post by:
We have a problem in our application: if a user clicks a link to load another page before the previous ASPX has finished executing, the Sub New() constructor of the second page executes...
10
by: Fabuloussites | last post by:
I'm considering deploying an application that will us an IP address locaiton database provided by Ip2location.com... http://www.ip2location.net/ip2location-dotnet-component.aspx their .net...
0
by: rlaemmler | last post by:
Hi, I just migrated my web app to .NET 2.0. Part of the app creates some business objects from a MySQL query which is returned by a web service. Some of those objects contain DateTime...
4
by: Bob Bedford | last post by:
Hello, I've a file wich must answer a request. For this my file must generate an XML output. That's work fine but sometimes I get a wrong mysql query. In this case, I've to manage the output...
16
by: Zenon | last post by:
I have an application where I create a Plink process to communicate with an HP Unix box. The problem I am having is that while reading the redirected output, it seems to try to continue past the...
2
by: thecoolone | last post by:
I am trying to implement the yahoo search api using php. I prefer to use PHP serialized to get the result of the search displayed. Right now i get the output like: Array ( =Array ( =web ...
1
by: Joris van Lier | last post by:
Hi, im trying to validate objects before they are sent over the wire to a webservice, the schema embedded into WSDL is not sufficient so i took that schema and extended it with additional...
9
by: PI | last post by:
Hi Guys, I need some assistance with the following scenario please. I know this might be more of a MySQL post than a PHP one, but it is likely some developer has been here before and would be...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.