Showing posts with label call. Show all posts
Showing posts with label call. Show all posts

Monday, March 26, 2012

how to use UDFs with asp.net

Hi there, i have a user defined function in my sql server so i can use from different pages. the question is, how do i call it from asp.net? can some one show me some code? and if u can, can u show me how to use it with data grid?
thanks.What does your UDF do? Does it return a Table value? You would treat the resultset the same way you would with any other table/stored procedure resultset.

I think more information is needed here.

Terri|||I am not aware that you can call a UDF from ADO.NET directly. You can call a stored procedure, and so perhaps you will need to wrap your UDF call in a stored procedure. More information about what the UDF does (and what it is returning) will help.|||my UDF returns a table, i have been reading some tutorials and books, and they say that "one of the advantages of using UDF is that it is possible to use it from many applications." however, i have been going nuts trying to find some help on how to code this.
if i cant use UDF, can i use stored procedures to retrive a table? i read that you can only update data with stored procedures, and that u get tables with UDF's...
thanks for your help guys.|||You can absolutely return tables from a stored procedure, and you can certainly call a stored procedure from ADO.NET on an ASP.NET page.

This link should help explain how to do it.|||thanks!|||And you can absolutely also call UDFs from ADO.NET on an ASP.NET page.


SELECT * FROM dbo.fnMyFunctionThatReturnsATable

But personally I wouldn't call the UDF directly. I'd make a stored procedure which calls the UDF.

Terri|||True, true. I have never used UDF's that way, but no reason you cannot. There is just not a CommandType.UserDefinedFunction...

Thanks!|||Hi All,

1. Here is an example of ASP.NET calling UDF returns a TABLE


http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=265546
Re: Calling a SQL 2k UDF from .aspx page

-- See Posted: 07-02-2003 02:11 AM

2. As for using it, you make a judgement call.

3. You can use sp to wrap your UDF: There is an example

-CREATE FUNCTION dbo.udf_Txt_SplitTAB
-CREATE PROCEDURE sp_SELECT_IN
to wrap UDF.
-private void ASP_NET_SELECT_IN (string IN_List_Values)
to call sp.


http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=280390
sql

How to use transaction in a SSIS Package

I try ton use a transaction in a SSIS package. When running i have an error :

[source [1]] Error: The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009.

[Connection manager "myconnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "Le partenaire du gestionnaire de transactions a dsactiv la prise en charge des transactions à distance/par rseau.".

Can someone help ?

thanks

Where is that 'myconnection' connection manager pointing to?

Make sure you open it an test its connection.

|||The connection is pointing an oracle database and is correct. When i run tha package whitout the transaction it works correctly. This error appears only when i implement the transaction.|||I believe SSIS uses Microsoft's Distributed Transaction Coordinator - Oracle needs to have support for that enabled explicitly (or at least it did last time I used it).|||I try to do the same thing using SQL SERVER 2005 databases for source and destination. And i have the same error.|||

Make sure you have Distributed Transaction Coordinator running on both the source and destination machines.

Brian Knight has a good video about it on Jumpstart TV - Using Transactions in SSIS

|||

Try to use checkpoints too!

Regards

|||

PedroCGD wrote:

Try to use checkpoints too!

Regards

Not the same as transactions though...|||

I know is not the same..

but could help... some people dont know checkpoint and it features.

How to use transaction in a SSIS Package

I try ton use a transaction in a SSIS package. When running i have an error :

[source [1]] Error: The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009.

[Connection manager "myconnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "Le partenaire du gestionnaire de transactions a dsactiv la prise en charge des transactions à distance/par rseau.".

Can someone help ?

thanks

Where is that 'myconnection' connection manager pointing to?

Make sure you open it an test its connection.

|||The connection is pointing an oracle database and is correct. When i run tha package whitout the transaction it works correctly. This error appears only when i implement the transaction.|||I believe SSIS uses Microsoft's Distributed Transaction Coordinator - Oracle needs to have support for that enabled explicitly (or at least it did last time I used it).|||I try to do the same thing using SQL SERVER 2005 databases for source and destination. And i have the same error.|||

Make sure you have Distributed Transaction Coordinator running on both the source and destination machines.

Brian Knight has a good video about it on Jumpstart TV - Using Transactions in SSIS

|||

Try to use checkpoints too!

Regards

|||

PedroCGD wrote:

Try to use checkpoints too!

Regards

Not the same as transactions though...|||

I know is not the same..

but could help... some people dont know checkpoint and it features.

Sunday, February 19, 2012

How to use a dialog retry timer for doing a web service call

Hi Remus,

I just started looking into SB about a week ago, so my question is likely to be pretty lame. However, that's not going to stop me from asking it :-)

We're trying to do something similar to what you're describing here. How do you "reset" a dialog timer? Why would you need to deal with resetting timers (or with timers at all for that matter) in the event of a success? Couldn't you rewrite the logic to be as follows?


begin transaction
receive message
if message is web request
save state of request (http address, caller etc)
else if message is retry timer message
load state of request
endif
commit
do the web request (no transaction open)
if success
begin transaction
send back response
end conversation
commit
else
set a retry timer on the dialog (say 1 minute) using BEGIN DIALOG TIMER
endif

Also, when you set the retry timer you have to associate it with the saved request state, right? Otherwise, how will the service know which request to load on receipt of the timer message?

TIA.

Rushi has a whole sample exemplifying how to do this. Look at http://blogs.msdn.com/rushidesai/archive/2006/04/19/746827.aspx

The trick is to receive, set the timer and commit, then do the web call. This way you don't hold the transaction open for a long time:

begin transaction
receive message
if message is web request
save state of request (http address, caller etc)
else if message is retry timer message
load state of request
endif
set a retry timer based on the policy
commit
do the web request (no transaction open)
if success
begin transaction
send back response
end conversation -- this will reset the timer implicitly. If conversation would not be ended, you'd have to reset the timer (begin conversation timer .. timeout 0)
commit
endif

Why this is a better pattern is because the timer is set during the original transaction, thus given transactional integrity to the dequeue and retry. If the shole server crashes during the web call, the timer is already commited and will pop. If a mirrorirng or cluster failover occurs, the timer will pop on the new principal/active host.

HTH,
~ Remus

|||

Thank you for your reply! That helps explain some of it. May I have you comment on this scenario, below?

Messages "A" and "B" are sent to a destination queue on a single dialog.

Message "A" is received from the queue.

Set a retry timer for 60 (e.g.) seconds. The retry time interval should be longer than the web request timeout, right?

Do the web request for message A (e.g., hit a web service).

It fails.

Receive another message. It might be message "B", which has a lifecycle of its own. but we won't worry about message B for now. So let's instead assume we receive the conversation timer message.

Reload state for message A.

Set a retry timer for 60 seconds again.

Do the web request for message A again.

It succeeds this time. We send back the response, but we don't end the conversation b/c message B still needs to be processed. Instead we reset the timer and commit.

Message B is now received and processed (it arrived after the timer b/c it was stuck in traffic).

This scenario is pretty easy. But what about the case where B is received before the retry timer message? If B succeeds, then it resets the timer (I'm looking at the logic right after the response is sent). Won't this kill the timer so that the timer set for A never gets called? Aren't timers specific to dialogs? I'm really confused trying to figure out dialogs, conversations, conversation groups, and the whole lot of it right now, but that's another topic in and of itself :-)

Another thing. What if both A and B fail, and what if they have different retry intervals? You can't have 2 separate timers going, can you?

In the meantime, I'll try to understand the examples on the link.

Thanks again for your help, Remus.

|||

Timers are per dialog. Why are messages A and B sent on the same dialog in the first place? Is there some correlation between them you left out of the explanation?

HTH,
~ Remus

|||

I'm trying to recycle dialogs on an account-by-account basis in a banking app. This is to benefit from dialog reuse (benefits discussed elsewhere) and to get the locking we desire (e.g., we want to minimize the chances of multiple threads hitting the same account at the same time). The app posts financial transactions from multiple channels (home banking, teller line, batch, etc). The app must communicate changes to account balances to other integration partners. So, when account A receives a payment, I need to let credit bureau XYZ know about it in "real time". Continuing with the example, I was trying to have the payment posting logic drop a message into a queue. Then the activated service(s) would send that message to all subscribers via web service calls (for example).

Am I making things too hard by trying to go about it this way?

|||

The dialogs benefits you quote are real, and they apply to one side of your process: the transmission of messages (ordering, locking etc).
The benefits of timers are just as real, but they apply to a different side of the process: the retry of individual web calls.

What that spells is that you probably could separate the dialogs on which you receive the messages from the dialogs on which you set the timers. W/o knowing more details about the process and application, I can't say for sure if this is a viable solution or how to implement it. I'm speculating, but some quick ideas would be:
- one 'timer' dialog for each message pending processing
- one 'timer' dialog for each thread doing the processing
- one 'timer' dialog for each instance of the state machine driving the retry logic

A 'timer' dialog would be a dialog begun by your application, perhaps from the target service to itself, on which you never actualy send any message, you just set a timer. Maybe you can relate it to the original conversation that receives the messages (BEGIN CONVERSATION .... WITH RELATED_CONVERSATION_HANDLE ...) to obtain better locking semantics.

HTH,
~ Remus