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 1592
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: 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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |