Receiving and sending a cursor with(in) a Stored Procedure

Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.

And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.

Thanks for help on this,

PeterCursors are a really poor choice in MS-SQL. It would appear that you are trying to use Oracle-like logic in SQL Server, which is a receipe for disaster.

Can you explain in a bit more detail what you are planning to do? I suspect that there is a much better way to do the job once we understand what you are trying to accomplish.

-PatP|||Hi Pat:

Maybe I should have said "result set" or "table" - I'm fairly new with SS.

Here's more detail:

A web app has a screen where the user enters order header information - name, address, etc. - this would be the tblOrder - 1 record. Then they enter the order detail - say 6 individual sales items - tblDetail - 6 records. All these entries are done on 1 screen in the web app.

Upon save, a stored procedure is called that will do 2 things.

1. It will save the tblOrder record and generate the PK for this record
2. It will save the 6 records into tblDetail including stuffing the PK from the tblOrder into a FK field within the tblDetail records.

1 and 2 would probably be wrapped in a transaction in case either fails it could Rollback. Otherwise, Commit.

The approach within the SP is basically what I'm after. Does that make sense?

Thanks,

Peter|||Write one stored procedure that saves your order record and returns the generated key value to your interface.

Write a second stored procedure that that save the detail records, including the order key returned by the first procedure.

Your interface should save the order, and then loop through the detail records calling an insert for each one.|||Blindman:

I was hoping for some code to show the passing of the detail record set from the view tier to the SP.

Secondly - I'd rather fail both the Order record creation and Part records creation if either fails.

Wouldn't it be better to have both processes in the same stored procedure? Otherwise, end up with an order and no detail?

Is there anything preventing me from doing both in 1 stored procedure? If not, how do I pass the needed data - that's the key to my question.

Thanks for helping,

Peter|||The detail recored set is just passed to the stored procedure as a set of parameters defined in the procedure's heading.|||Hi - a coded example of the stored procedure would be most helpful to this newbie. I understand that detailed record set is passed as a parameter. My original question would be to see the code of the Stored Procedure handling the receipt and processing of the detailed record set.

Thanks,

Peter|||Create Procedure DetailInsert(@.OrderID as int, @.DetailInfo as varchar(50))
as
begin
insert into DetailTable (OrderID, DetailInfo) values (@.OrderID, @.DetailInfo)
end|||So detailInfo is a record set? It looks like a single field. If it is a record set, are the values comma delimited or is it simply a reference to a record set that was established in the calling program?

This is where I'm confused. I can handle a SP that inserts a single record into a DB. I'm trying to understand a situation where multiple records are submitted at once.

Thanks,

peter|||I have no idea what @.DetailInfo is. That is up to you. It is a dummy parameter that represents all the values you need to submit to the procedure. Submit each value as a separate parameter.

Peter, have you even TRIED to look up how to write Stored Procedure in Books Online?|||Blindman:

I have looked up many things with books online. I thought this might be a place to zero in efficiently for a solution. As someone new, I'm struggling. I apologize that I didn't understand the purpose of this board and to have taken valuable time away from you.

I have numerous stored procedures that work to insert and update single records. I have some stored procedures to query and return a record set. What I haven't had success in is having a stored procedure receive a group of child records along with parent and save both. If either fails, I want to rollback - that's why I thought there would be a benefit to combining the operations into a single SP transaction.|||The answer here is easy.

This is not Oracle

You can not do what you are thinking unless you use bcp or bulk insert.

In either case that means you'd have to create a file, which I don't think is a good idea.

You need to iterate through your rs and make a stored procedure call for each record set.

Now you could put all of the data in to 1 string, pass it to a sproc, the "unstring" it in the sproc...but that would be overkill.

Use 2 sprocs like the blind dude said.

Pass the id as an output variable from the first sproc.

In the second sproc use error checking. If anything fails, perform a delete of the initial record.

Sorry|||You can't set up a stored procedure that will recieve a group of records. They can only accept input parameters.

I recommend that you either change your application design to one that submits new data one record at a time, or look into other options available through your development interface for handling recordset. I'm not much of an interface programmer, so I can't help you with that, but you might check one of the other sections of DB Forums.

Sorry if I was short with you. Been sick today. I readily answer question such as "How do I write a stored procedure to do X", but I don't answer questions like "How do I write a stored procedure."

If you have specific questions, please do post them on this forum and I or somebody else will assist you.|||Thank you Brett and Blindman. I now understand that the best way to handle is from the interface, not at SQL server.

I hope you are feeling better Blindman.

thanks,

Peter|||Sick?

Don't you listen to anything I say?

A bottle of tequila will take care of all those germs|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?Have you ever tried using a bottle of Tequila to smash the germs? I'd like the video rights, and would make you a very handsome deal.

Scalable? How about many cases of Tequila?

-PatP

receive top 20

HI

I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.

WAITFOR (
RECEIVE top (20) -- get batched so that we can process same listid once
message_type_name,
message_body, -- the message contents
conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.target
into @.PayloadData
), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting

thanks

P

RECEIVE can only return messages on one conversation group. Normally each conversation is its own conversation group. If you sent only one message on each conversation, RECEIVE cannot get more that one message at a time, even if there are more messages in the queue.|||

Hi

so, in your blog on T-SQL RECEIVE. Fast. : Set based Processing.

How are you able to receive the message in bulk? Is it because of the way you send the message in LoadQueueReceivePerfBlog?

P|||Yes. This is also the reason why I recommend reusing dialogs in my other entry at http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx|||

one follow up,

when I send the message using the same conversation handle, the receive top (20) statement waits until the previous batch is committed before it will start retrieving the next 20. I guess this is because of the its now part of the same conversation group and service broker need to guarantee process order?

This is what my proc looks like

BEGIN TRANSACTION

WAITFOR (

RECEIVE top (20) -- get batched so that we can process same listid once

message_type_name,

message_body, -- the message contents

conversation_handle -- the identifier of the dialog this message was received on

FROM dbo.target

into @.PayloadData

), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

-- do some processing of the records in @.PayloadData

COMMIT TRANSACTION

if "-- do some processing of the records in @.PayloadData" is taking a long time, its going to block the messages in the queue.

If I remove the begin and commit transaction block, it only wait for the 3 seconds I specified.

Question: is the transaction block necessary in the activated procedure.

thanks

Paul

Receive all messages on queue

Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back.

any help appreciated.

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [RMIS_COMMS_Queue]

INTO @.tableMessages), TIMEOUT 2000;

Each RECEIVE returns only messages belonging to conversation in one conversation group only. If each conversation is its own group (which is true unless you use RELATED_CONVERSATION clause in BEGIN DIALOG or you use MOVE CONVERSATION) then it means you can RECEIVE only one conversation in one call. The TOP clause applies to this resultset (one conversation).

HTH,
~ Remus

|||Thanks for that. Can i just refer to your article on batch processing messages (the example with the cursor) this is the method i am try ing to implement but my scenario is many shops with many tills, where each message/conversation is a complete sale from a till i.e many conversations with one message. Is it safe to say this is not the best method for trying to speed this up, what would be the best way to process these messages, bearing in mind sometimes a shop can lose comms and send down a batch of sales when it reconnects.|||

I'm using triggers to cause an update of a seperate database via service broker, in our activated stored procedure we are seeing similiar situations, Could you give an example of a script that a trigger might use to take advantage of the RELATED_CONVERSATION clause? I looked at the documentation and didn't see how I could use this in our situation. (trigger on update sends inserted table with for xml clause as message to queue)

Thanks,

Bill

Receive all messages on queue

Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back.

any help appreciated.

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [RMIS_COMMS_Queue]

INTO @.tableMessages), TIMEOUT 2000;

Each RECEIVE returns only messages belonging to conversation in one conversation group only. If each conversation is its own group (which is true unless you use RELATED_CONVERSATION clause in BEGIN DIALOG or you use MOVE CONVERSATION) then it means you can RECEIVE only one conversation in one call. The TOP clause applies to this resultset (one conversation).

HTH,
~ Remus

|||Thanks for that. Can i just refer to your article on batch processing messages (the example with the cursor) this is the method i am try ing to implement but my scenario is many shops with many tills, where each message/conversation is a complete sale from a till i.e many conversations with one message. Is it safe to say this is not the best method for trying to speed this up, what would be the best way to process these messages, bearing in mind sometimes a shop can lose comms and send down a batch of sales when it reconnects.|||

I'm using triggers to cause an update of a seperate database via service broker, in our activated stored procedure we are seeing similiar situations, Could you give an example of a script that a trigger might use to take advantage of the RELATED_CONVERSATION clause? I looked at the documentation and didn't see how I could use this in our situation. (trigger on update sends inserted table with for xml clause as message to queue)

Thanks,

Bill

Receive "Must declare the variable" When Upgrading to Reporting Services 2005

We are in the process of migrating our databases to SQL Server 2005 and our Reporting Services Reports to 2005. We have been doing this in a phased approach with excellent success.

However, I have a set of Reporting Services 2000 reports that are reading from a SQL Server 7.0 database. If possible, I would like to migrate the reports before we migrate the database (we're not ready to migrate the database yet).

When I converted the reports to Reporting Services 2005, I first received an error message regarding my data source. Basically the message says anything developed in Visual Studio 2005 using the Microsoft SQL Server connection type cannot connect to a database prior to Microsoft SQL Server 2000. So I switched the connection string to be a OLE DB type.

Well ... the reports contain parameters (i.e. @.plant, @.employee, etc). So when I attempt to run the query, I get a message saying "Must declare the variable '@.plant'". I have searched for a work around until we migrate the database but I am coming up empty.

Is there a way for me to run a report with parameters from Reporting Services 2005 to a SQL Server database that is prior to SQL Server 2000?

Thanks in advance.

OLE DB Parameters are not named. Instead of @.foo for parameters in the SELECT statement, you use ? I thought the managed provider should work, though. What is the exact error?|||

Thank you for replying. The exact error that is displayed is as follows:

An error occurred during the local report processing

An error has occurred during report processing

Query execution failed for data set 'Journal'

Must declare the variable '@.plant'.

So if OLE DB does not support named parameters, can I use multiple parameters in this report? The report contains seven different parameters.

|||

According to documentation...

The OLE DB provider for SQL Server does not support named variables. Use the question mark (?) character to specify a variable. Parameters passed to the OLE DB provider must be passed in the order they occur in the WHERE clause. For example, PM.Name LIKE ('%' + ? + '%').

http://msdn2.microsoft.com/en-us/library/aa337223.aspx

Other providers may support.

However, you should be able to use query expression & a Reporting Services parameter.

eg. ="Select value from table where value = " + Parameters!MyParam.Value

cheers,

Andrew

|||

Hi,

Did you get the solution to this error ?

I'm getting the same error when I try to pass a multi-list of values from SRS2005 to a storeprocedure.

Please let me know if your report in the dataset has a query or SP.

Thanks

Pepe

Receive "Must declare the variable" When Upgrading to Reporting Services 2005

We are in the process of migrating our databases to SQL Server 2005 and our Reporting Services Reports to 2005. We have been doing this in a phased approach with excellent success.

However, I have a set of Reporting Services 2000 reports that are reading from a SQL Server 7.0 database. If possible, I would like to migrate the reports before we migrate the database (we're not ready to migrate the database yet).

When I converted the reports to Reporting Services 2005, I first received an error message regarding my data source. Basically the message says anything developed in Visual Studio 2005 using the Microsoft SQL Server connection type cannot connect to a database prior to Microsoft SQL Server 2000. So I switched the connection string to be a OLE DB type.

Well ... the reports contain parameters (i.e. @.plant, @.employee, etc). So when I attempt to run the query, I get a message saying "Must declare the variable '@.plant'". I have searched for a work around until we migrate the database but I am coming up empty.

Is there a way for me to run a report with parameters from Reporting Services 2005 to a SQL Server database that is prior to SQL Server 2000?

Thanks in advance.

OLE DB Parameters are not named. Instead of @.foo for parameters in the SELECT statement, you use ? I thought the managed provider should work, though. What is the exact error?|||

Thank you for replying. The exact error that is displayed is as follows:

An error occurred during the local report processing

An error has occurred during report processing

Query execution failed for data set 'Journal'

Must declare the variable '@.plant'.

So if OLE DB does not support named parameters, can I use multiple parameters in this report? The report contains seven different parameters.

|||

According to documentation...

The OLE DB provider for SQL Server does not support named variables. Use the question mark (?) character to specify a variable. Parameters passed to the OLE DB provider must be passed in the order they occur in the WHERE clause. For example, PM.Name LIKE ('%' + ? + '%').

http://msdn2.microsoft.com/en-us/library/aa337223.aspx

Other providers may support.

However, you should be able to use query expression & a Reporting Services parameter.

eg. ="Select value from table where value = " + Parameters!MyParam.Value

cheers,

Andrew

|||

Hi,

Did you get the solution to this error ?

I'm getting the same error when I try to pass a multi-list of values from SRS2005 to a storeprocedure.

Please let me know if your report in the dataset has a query or SP.

Thanks

Pepe

sql

rebuildm can not start sql server

Hi All,
I ran the rebuildm.exe and it had been showing "server configuration progress..." for more than 10 minutes. So I stopped that process manually. Now I can not start sql server. why?
When I start it, the status first changes to "starting" and again "stopped".
Please help...
Hi!
Rebuildm.exe copies master database files from CD (search for mast*.mdf and
mast*.ldf). When copied from CD, files are marked with read-only attribute.
This might be the reason for the problem. Copy the files mnually, change the
attribute and rerun rebuildm with copied files.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"suresh" <anonymous@.discussions.microsoft.com> wrote in message
news:F694DCC2-0823-48EC-ACCC-92291C1A4E23@.microsoft.com...
> Hi All,
> I ran the rebuildm.exe and it had been showing "server configuration
progress..." for more than 10 minutes. So I stopped that process manually.
Now I can not start sql server. why?
> When I start it, the status first changes to "starting" and again
"stopped".
> Please help...
|||No, it does not work !
|||Any errors in Windows Event Log and SQL Server Error Log?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"suresh" <anonymous@.discussions.microsoft.com> wrote in message
news:B1271FD4-A0E9-401D-9E8E-91F0E4BC8105@.microsoft.com...
> No, it does not work !
sql

Real-Time Data Mining Discussion

I am about to prepare a paper concerning the field of real-time data mining. Real-time here means the process of incremental training of an existing model as soon as the data arrives.

There is a number of papers introducing algorithms for incremental association analysis, incremental clustering etc. Stream mining ís a field which is closely related to that. The main reason for the implementation of incremental algorithms is a) the large amount of data to be mined and b) the high rate of new data that is evolving every day.

Using classical batch mining algorithms, models that are outdated for some reason, would have to be re-trained, which could be very time consuming for billions of records. And once the training is completed, the training would have to be restarted once again because a bulk of new data has been arrived.

The question that I would like to discuss now is: For what real world applications would it be a meaningful or even essential to use real-time training of models?

Two main reasons could determine the answer to that question:

You just want to incorporate new data into existing models in order to increase the prediction accuracy of your model or

Your underlying data is subject to more or less massive changes (also refered to as concept drift) and you want to adapt your mining model continuously to that reality.

I'm looking for some examples or ideas where one of these cases apply and it would be a good idea to have incremental mining algorithms involved.

I'm looking forward to inspiring some discussion on that issue.

Whenever you model a control system (like validation edits for an application process), you get the ability to tune the controls to stop unwanted behavior. Users that are subject to the new controls will, over time, begin to understand the controls and start to look for weaknesses in the controls that makes their input tasks easier to accomplish. This may lead to new "unwanted" behaviors and it would be great to have control model that learns on the fly and adjusts when it identifies new unwanted behaviors.|||If you haven't already prepared the paper, here's another potential application. Let's say you are doing data mining on stock price movements. You're passing some sort of stock price history, as well as relating it to day of week, day of month, month, year of presidency, moon cycle, what have you. Statistics generally show, for example, that stock prices move up on Fridays more often than Mondays, and this is thought to be due to short sellers covering their positions so they aren't caught by unexpected events over the weekend.

Let's say you want to update these statistics daily, shortly after market close, to keep your trading strategies up-to-date with current market conditions. You don't want to retrain the model with 100+ years of stock data every day, so it'd be much faster to be able to do incremental updates. This becomes particularly important for options and futures trading (though there's not 100 years of data for that), as for every underlying security there are potentially dozens or even hundreds of contracts trading on the market.

Real-Time Data Mining Discussion

I am about to prepare a paper concerning the field of real-time data mining. Real-time here means the process of incremental training of an existing model as soon as the data arrives.

There is a number of papers introducing algorithms for incremental association analysis, incremental clustering etc. Stream mining ís a field which is closely related to that. The main reason for the implementation of incremental algorithms is a) the large amount of data to be mined and b) the high rate of new data that is evolving every day.

Using classical batch mining algorithms, models that are outdated for some reason, would have to be re-trained, which could be very time consuming for billions of records. And once the training is completed, the training would have to be restarted once again because a bulk of new data has been arrived.

The question that I would like to discuss now is: For what real world applications would it be a meaningful or even essential to use real-time training of models?

Two main reasons could determine the answer to that question:

You just want to incorporate new data into existing models in order to increase the prediction accuracy of your model or

Your underlying data is subject to more or less massive changes (also refered to as concept drift) and you want to adapt your mining model continuously to that reality.

I'm looking for some examples or ideas where one of these cases apply and it would be a good idea to have incremental mining algorithms involved.

I'm looking forward to inspiring some discussion on that issue.

Whenever you model a control system (like validation edits for an application process), you get the ability to tune the controls to stop unwanted behavior. Users that are subject to the new controls will, over time, begin to understand the controls and start to look for weaknesses in the controls that makes their input tasks easier to accomplish. This may lead to new "unwanted" behaviors and it would be great to have control model that learns on the fly and adjusts when it identifies new unwanted behaviors.|||If you haven't already prepared the paper, here's another potential application. Let's say you are doing data mining on stock price movements. You're passing some sort of stock price history, as well as relating it to day of week, day of month, month, year of presidency, moon cycle, what have you. Statistics generally show, for example, that stock prices move up on Fridays more often than Mondays, and this is thought to be due to short sellers covering their positions so they aren't caught by unexpected events over the weekend.

Let's say you want to update these statistics daily, shortly after market close, to keep your trading strategies up-to-date with current market conditions. You don't want to retrain the model with 100+ years of stock data every day, so it'd be much faster to be able to do incremental updates. This becomes particularly important for options and futures trading (though there's not 100 years of data for that), as for every underlying security there are potentially dozens or even hundreds of contracts trading on the market.

Real-Time Data Mining Discussion

I am about to prepare a paper concerning the field of real-time data mining. Real-time here means the process of incremental training of an existing model as soon as the data arrives.

There is a number of papers introducing algorithms for incremental association analysis, incremental clustering etc. Stream mining ís a field which is closely related to that. The main reason for the implementation of incremental algorithms is a) the large amount of data to be mined and b) the high rate of new data that is evolving every day.

Using classical batch mining algorithms, models that are outdated for some reason, would have to be re-trained, which could be very time consuming for billions of records. And once the training is completed, the training would have to be restarted once again because a bulk of new data has been arrived.

The question that I would like to discuss now is: For what real world applications would it be a meaningful or even essential to use real-time training of models?

Two main reasons could determine the answer to that question:

You just want to incorporate new data into existing models in order to increase the prediction accuracy of your model or

Your underlying data is subject to more or less massive changes (also refered to as concept drift) and you want to adapt your mining model continuously to that reality.

I'm looking for some examples or ideas where one of these cases apply and it would be a good idea to have incremental mining algorithms involved.

I'm looking forward to inspiring some discussion on that issue.

Whenever you model a control system (like validation edits for an application process), you get the ability to tune the controls to stop unwanted behavior. Users that are subject to the new controls will, over time, begin to understand the controls and start to look for weaknesses in the controls that makes their input tasks easier to accomplish. This may lead to new "unwanted" behaviors and it would be great to have control model that learns on the fly and adjusts when it identifies new unwanted behaviors.|||If you haven't already prepared the paper, here's another potential application. Let's say you are doing data mining on stock price movements. You're passing some sort of stock price history, as well as relating it to day of week, day of month, month, year of presidency, moon cycle, what have you. Statistics generally show, for example, that stock prices move up on Fridays more often than Mondays, and this is thought to be due to short sellers covering their positions so they aren't caught by unexpected events over the weekend.

Let's say you want to update these statistics daily, shortly after market close, to keep your trading strategies up-to-date with current market conditions. You don't want to retrain the model with 100+ years of stock data every day, so it'd be much faster to be able to do incremental updates. This becomes particularly important for options and futures trading (though there's not 100 years of data for that), as for every underlying security there are potentially dozens or even hundreds of contracts trading on the market.