Dear Group,
I have got some XML code which I need to send from my SQL Server
database to some remote application server via POST method. The
application server will return XML code as well and this returned XML
will be processed on SQL Server afterwards.
Because of the fact that it all can't be done via T-SQL I was advised
on using CLR. This means that I should write some DLL in VB.NET or C#,
place this DLL on SQL Server, register it and write a CLR stored
procedure with the reference to that DLL. Then, when I want to
communicate with the application server, I will execute the CLR SP (I
will pass the proper XML code to it as input parameter), this CLR SP
will call the DLL, the DLL will send the XML to the application server
(via POST method), the application server will execute it and send the
XML answer back to my CLR SP. This XML I will process and save into
tables afterwards. I hope I understand it good...
The problem is that I have no idea how to write that DLL. I have never
programmed in Visual Studio. Can you pls advise me how to make the DLL
which receives XML code, sends it (using .Method="POST") to the
application server, receives the XML answer from that server and
passes it to the calling entity (CLR SP)?
Many thanks for your answers!
MikeX 5 1423
You've got a couple of different ways you can go.
Let me give one.
You have a source1.xml.
You don't control the formatting of source1.xml.
You can write a stored procedure (uspImportMyXml ) to handle source1.xml.
However, if you ever get into the situation where you need to handle a
different xml (source2.xml), you'll have to write another uspImportMyXml2.
I don't like that scenario.
You can write a strong dataset , which you will take your source1.xml, and
turn it into a dataset. You can use the dataset(.GetXml) method and send
that to the uspImportMyXml.
The bonus for you is that you can also return dataset.GetXml() without
pulling it back out of the database.
You also may want to write a WebService, instead of relying on the POST
method.
Your html page ( import.aspx ) can piggyback off the webservice if you'd
like.
Your other issue is performance. How much xml are you talking about?
Some articles:
Xml to Xml (or to DataSet) conversion:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!148.entry
A decent template for a VS2005 project:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry
How to pass Xml into a stored procedure: http://www.sqlservercentral.com/colu...terproblem.asp
(not an exact match, but you can find the OPENXML usage there)
This again is a way to pass Xml into a stored procedure for processing.
There are some performance issues with OPENXML you might consider. But I've
found using this method for bulk data is better than running the same stored
procedure over and over in a loop for multiple records.
What you find there is a derivative of this MSKB http://support.microsoft.com/kb/315968
Again, this is ` of a few ways you can go. Look at other ideas also.
But that's how I would approach it.
<ac*@centrum.czwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...
Dear Group,
I have got some XML code which I need to send from my SQL Server
database to some remote application server via POST method. The
application server will return XML code as well and this returned XML
will be processed on SQL Server afterwards.
Because of the fact that it all can't be done via T-SQL I was advised
on using CLR. This means that I should write some DLL in VB.NET or C#,
place this DLL on SQL Server, register it and write a CLR stored
procedure with the reference to that DLL. Then, when I want to
communicate with the application server, I will execute the CLR SP (I
will pass the proper XML code to it as input parameter), this CLR SP
will call the DLL, the DLL will send the XML to the application server
(via POST method), the application server will execute it and send the
XML answer back to my CLR SP. This XML I will process and save into
tables afterwards. I hope I understand it good...
The problem is that I have no idea how to write that DLL. I have never
programmed in Visual Studio. Can you pls advise me how to make the DLL
which receives XML code, sends it (using .Method="POST") to the
application server, receives the XML answer from that server and
passes it to the calling entity (CLR SP)?
Many thanks for your answers!
MikeX
No idea why u need a DLL, you can write an app to do it. If you must have
the SQL's DTS start the process, your dll need to be an ActiveX dll.
Anyway, just make the function returning the stream.
--
cheers,
RL
<ac*@centrum.czwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...
Dear Group,
I have got some XML code which I need to send from my SQL Server
database to some remote application server via POST method. The
application server will return XML code as well and this returned XML
will be processed on SQL Server afterwards.
Because of the fact that it all can't be done via T-SQL I was advised
on using CLR. This means that I should write some DLL in VB.NET or C#,
place this DLL on SQL Server, register it and write a CLR stored
procedure with the reference to that DLL. Then, when I want to
communicate with the application server, I will execute the CLR SP (I
will pass the proper XML code to it as input parameter), this CLR SP
will call the DLL, the DLL will send the XML to the application server
(via POST method), the application server will execute it and send the
XML answer back to my CLR SP. This XML I will process and save into
tables afterwards. I hope I understand it good...
The problem is that I have no idea how to write that DLL. I have never
programmed in Visual Studio. Can you pls advise me how to make the DLL
which receives XML code, sends it (using .Method="POST") to the
application server, receives the XML answer from that server and
passes it to the calling entity (CLR SP)?
Many thanks for your answers!
MikeX
Mike,
The first thing you are going to want to read up on is the section of
the MSDN documentation titled "CLR Stored Procedures", located at: http://msdn2.microsoft.com/en-us/library/ms131094.aspx
You will also want to take a look at the CREATE ASSEMBLY documentation
which you will need in order to register the assembly once you have coded it
up: http://msdn2.microsoft.com/en-us/library/ms189524.aspx
Take notice of the WITH PERMISSION_SET option, as you will have to set
it to EXTERNAL_ACCESS in order to allow your CLR stored procedure network
access.
If the request is a simple one, then take a look at the WebClient class: http://msdn2.microsoft.com/en-us/lib...webclient.aspx
The methods you are most likely to use are the UploadFile, UploadString,
or UploadData methods.
If you need more fine-grained control over the request that you are
sending, use the HttpWebRequest class: http://msdn2.microsoft.com/en-us/lib...ebrequest.aspx
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
<ac*@centrum.czwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...
Dear Group,
I have got some XML code which I need to send from my SQL Server
database to some remote application server via POST method. The
application server will return XML code as well and this returned XML
will be processed on SQL Server afterwards.
Because of the fact that it all can't be done via T-SQL I was advised
on using CLR. This means that I should write some DLL in VB.NET or C#,
place this DLL on SQL Server, register it and write a CLR stored
procedure with the reference to that DLL. Then, when I want to
communicate with the application server, I will execute the CLR SP (I
will pass the proper XML code to it as input parameter), this CLR SP
will call the DLL, the DLL will send the XML to the application server
(via POST method), the application server will execute it and send the
XML answer back to my CLR SP. This XML I will process and save into
tables afterwards. I hope I understand it good...
The problem is that I have no idea how to write that DLL. I have never
programmed in Visual Studio. Can you pls advise me how to make the DLL
which receives XML code, sends it (using .Method="POST") to the
application server, receives the XML answer from that server and
passes it to the calling entity (CLR SP)?
Many thanks for your answers!
MikeX
That's just a bad idea all around. Using the CLR in SQL Server is much
safer (in comparison to using a COM object), and more efficient than using a
COM object to write an extended stored procedure which will be triggered
from within SQL Server.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Egghead" <robertlo@NO_SHAW.CAwrote in message
news:OM****************@TK2MSFTNGP06.phx.gbl...
No idea why u need a DLL, you can write an app to do it. If you must have
the SQL's DTS start the process, your dll need to be an ActiveX dll.
Anyway, just make the function returning the stream.
--
cheers,
RL
<ac*@centrum.czwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...
>Dear Group,
I have got some XML code which I need to send from my SQL Server database to some remote application server via POST method. The application server will return XML code as well and this returned XML will be processed on SQL Server afterwards.
Because of the fact that it all can't be done via T-SQL I was advised on using CLR. This means that I should write some DLL in VB.NET or C#, place this DLL on SQL Server, register it and write a CLR stored procedure with the reference to that DLL. Then, when I want to communicate with the application server, I will execute the CLR SP (I will pass the proper XML code to it as input parameter), this CLR SP will call the DLL, the DLL will send the XML to the application server (via POST method), the application server will execute it and send the XML answer back to my CLR SP. This XML I will process and save into tables afterwards. I hope I understand it good...
The problem is that I have no idea how to write that DLL. I have never programmed in Visual Studio. Can you pls advise me how to make the DLL which receives XML code, sends it (using .Method="POST") to the application server, receives the XML answer from that server and passes it to the calling entity (CLR SP)?
Many thanks for your answers!
MikeX
You are right, it is not that safe with ActiveX;
I completely miss the CLR in SQL 2k5 part.
--
cheers,
RL
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.comwrote in
message news:O8****************@TK2MSFTNGP06.phx.gbl...
That's just a bad idea all around. Using the CLR in SQL Server is much
safer (in comparison to using a COM object), and more efficient than using
a COM object to write an extended stored procedure which will be triggered
from within SQL Server.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Egghead" <robertlo@NO_SHAW.CAwrote in message
news:OM****************@TK2MSFTNGP06.phx.gbl...
>No idea why u need a DLL, you can write an app to do it. If you must have the SQL's DTS start the process, your dll need to be an ActiveX dll. Anyway, just make the function returning the stream. -- cheers, RL <ac*@centrum.czwrote in message news:11**********************@q75g2000hsh.googleg roups.com...
>>Dear Group,
I have got some XML code which I need to send from my SQL Server database to some remote application server via POST method. The application server will return XML code as well and this returned XML will be processed on SQL Server afterwards.
Because of the fact that it all can't be done via T-SQL I was advised on using CLR. This means that I should write some DLL in VB.NET or C#, place this DLL on SQL Server, register it and write a CLR stored procedure with the reference to that DLL. Then, when I want to communicate with the application server, I will execute the CLR SP (I will pass the proper XML code to it as input parameter), this CLR SP will call the DLL, the DLL will send the XML to the application server (via POST method), the application server will execute it and send the XML answer back to my CLR SP. This XML I will process and save into tables afterwards. I hope I understand it good...
The problem is that I have no idea how to write that DLL. I have never programmed in Visual Studio. Can you pls advise me how to make the DLL which receives XML code, sends it (using .Method="POST") to the application server, receives the XML answer from that server and passes it to the calling entity (CLR SP)?
Many thanks for your answers!
MikeX This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Simon |
last post by:
Hi,
I am having a little problem with my PHP - MySQl code, I have two tables
(shown below) and I am trying populate a template page with data from both.
<disclaimer>Now I would like to say my...
|
by: BijuThomas |
last post by:
Complicated - ASP/Security/data transfer/XML doubt
In our company Head office we are hosting an intranet
server in IIS (Windows 2000) , ASP and Sqlserver back
end.
We are maintaining our branch...
|
by: Dave |
last post by:
We are trying to migrate a MS SQL server app to DB2 8.1 Linux platform.
Our database has got about 300+tables with total size - 150 GB
We are using MS SQL's BCP utility to extract data from...
|
by: Fatih BOY |
last post by:
Hi,
I want to send a report from a windows application to a web page like
'report.asp'
Currently i can send it via post method with a context like
local=En&Username=fatih&UserId=45&Firm=none...
|
by: Mark Waser |
last post by:
Hi all,
I'm trying to post multipart/form-data to a web page but seem to have
run into a wall. I'm familiar with RFC 1867 and have done this before (with
AOLServer and Tcl) but just can't seem...
|
by: E.T. Grey |
last post by:
Hi,
I have an interesting problem. I have a (LARGE) set of historical data
that I want to keep on a central server, as several separate files. I
want a client process to be able to request the...
|
by: David Veeneman |
last post by:
I am creating a form on the server that I want to POST to a URL that's being
called with Server.Transfer(). What's the simplest way to do that?
A little background: I'm programming buttons that...
|
by: Donald Adams |
last post by:
Hi,
I will have both web and win clients and would like to page my data. I
could not find out how the datagrid control does it's paging though I did
find some sample code that says they do it...
|
by: =?Utf-8?B?YzY3NjIyOA==?= |
last post by:
Hi all,
Our asp programs seem to have different behaviors when users use IE and
firefox.
One of most annoying things is the data disappearing problem in IE but not
in firefox.(Note: Sometimes a...
|
by: Andrew Jackson |
last post by:
I am writing a newsgroup client. I have the protocol figured out. But I
get slow transfer speeds off any of the network objects read the data from
For example one of the commands for a news...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |