AutoGenerate

Hi
I have set a field "MessageId" as primary in a Messages table. What I want is that whenever user inserts a message through my site, the MsSql should automatically generate MessageId for the new message inserted, but this is not happening. Any suggestions, advice are highly appreciated. Thank YouIs the column set as an IDENTITY column? If not, that explains the problem. In Enterprise Manager, go into Design mode for the table, and make sure in the properties window, Identity is True (or Yes, do not recall which is used).|||You've got to create a table in SQL with something like the below. As long as there is input in the column named "Message" then the MessageID will automatically increase.

CREATE TABLE Message
(
MessageID int IDENTITY(1,1) PRIMARY KEY,
Messagevarchar (2000)NOT NULL
)

Good luck!
-Gabian-|||Thanks to both of you gentleman.

One more thing, what enum of SqlDbtype should i keep for my actual Message(thats being recorded by the user) : "text" or "varChar" ?|||What are your needs:

Varchar will allow a maximum of 8000 bytes
Text will allow very large values ~ 2gb

Varchar will give you much more flexibility for searching and manipulating data though and if it is sufficient would be my recomendation.|||Depends on the size...

I usually use varchar (^_^)

Auto Process Messages in Target Queue

I am little confused with this statement, can please some one clarify!

Create QUEUE ReceiverQueue WITH STATUS = ON,

ACTIVATION ( PROCEDURE_NAME = dbo.process, MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo' )

Does this mean when a message reaches to ReceiverQueue, it will automatically processed by dbo.Process procedure

Create PROC dbo.process As

Begin

DECLARE @.conversationHandle UNIQUEIDENTIFIER

DECLARE @.message_body varchar(100)

DECLARE @.rid int

Begin Transaction;

While (1=1)

Begin

RECEIVE TOP(1)

@.rid = Cast(Convert(Nvarchar(max),message_body) as int)

From ReceiverQueue

If (@.@.RowCount = 0 Or @.@.ERROR <> 0 ) Break

Exec PriceChange.Extract_AS400Data @.rid

End

Commit Transaction;

End

Or do I have to put WAITFOR statement in my stored proc (dbo.Process) to run it continously.

Please guide

Thanks

By WAITFOR I assume you mean WAITFOR (RECEIVE...), not the WAITFOR DELAY.

Both ways are correct, but a small WAITFOR timeout added to the RECEIVE in the loop is more efficient. If you have a small timeout (e.g. 10 seconds) the procedure wil linger around for up to 10 seconds, so new incoming messages within 10 seconds will find it already activated.

You need to receive also the message_type_name columns and check what message type you RECEIVED. There are two built-in message types you procedure should be ready to handle: EndDialog and Error. As it is now, when will receive an EndDialog or Error it will actually call the PriceChange.Extract_AS400Data procedure with the wrong data. The usual way to reac to these two message types is to issue and END CONVERSATION. For this you need to RECEIVE also the conversation_handle column.

Also, you need to think who will END the conversations you use first and when. How are those pricechange mesages sent? Each change is sent as BEGIN DIALOG/SEND, or there is one long lived dialog that sends all changes?

HTH,
~ Remus

|||

Thanks for the detailed reply. My whole intent is to extract AS400 data using SQL server asynchronously.

Users from front end application (ASP.NET 2) sends a request to download certain batches of data. When users submits a request, request is saved into a database table and a requestid is generated. Generated id is sent as message to sender queue which in truns go to receiver queue. The stored proc associated with the receiver queue will execute an try to download data from as400 by reading the request table based on sent id.

Mostly it will be one message and conversation is closed. Then later time another message and conversation is closed.

Please Guide me for the above scanario.

Thanks

|||

1> How will the stored proc download data from AS400? Do you plan to use a CLR assembly with external access permission? If so, note that your outbound connections will be from the sqlservr.exe process which is running as the SQL Server User Account (eg> LOCALSYSTEM, NETWORKSERVICE, etc). Also, you will be using up a thread in the database engine waiting for the data to be fetched from AS400. And you also need to be careful with handling error semantics (what if AS400 is unavailable or there is a network failure while downloading the data).

One solution to the problem of executing in the context of SQL Server database engine is not to use internal activation. You can use external activation as described here (http://rushi.desai.name/Blog/tabid/54/EntryID/7/Default.aspx) so that your services can be written as standalone EXEs that could run as whatever principal you wish and also on a separate machine if desired.

The other solution, is to use a different SQL Server instance for your outbound access. This instance could even be a copy of the free SQL Server Express.

2> "Mostly it will be one message and conversation is closed". If the initiator service sends the message and closes the conversation in the same transaction (i.e. fire-and-forget) you cannot achieve application-level reliability. In order to get reliability, the app must get back some acknowledgment from the target that the message was received and processed. This response message could be as simple as EndDialog. So it is always better for the target to close the conversation first, after it has successfully received the message.

|||

Thanks for the informatin. Data from AS400 is extracted using the LinkedServer feature of SQL2005.

Is it possible to give some example or url reference to achive what's mentioned in the point 2.

Thnaks

Shafiq