Ok, I'm stumped. I have a database which has a table that stores documents. Each document has a primary key. For years, I've been notifying an external program that a new document has arrived in the db by firing a trigger on the table. Trigger calls xp_cmdshell, which calls an external program which enqueues a message on a message queue.
View 6 RepliesHi, All!
Could you please help me in my problem?
I develop Service Broker applcation, SB has 20 queues, also I have one Activation Procedure which carries out following actions:
CREATE PROCEDURE proc_ms_Activation
AS
BEGIN
WAITFOR (RECEIVE TOP(1) message_type_name, message_body, conversation_handle, message_id FROM Queue1)
-- process message, save statisctisc and etc
END
Problem in that: when I execute command WAITFOR (RECEIVE TOP (1) message_type_name, message_body, conversation_handle, message_id FROM Queue1) I should specify a name concrete Queue, for example "... FROM Queue1 ". I would like to use one procedure for processing all Queue.
How I can design my application that one Activation Procedure processed messages from all Queues?
Thank a lot for help.
Hello, please help!!
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
kind regards,
David Weeden
Database Developer
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
I'm just getting around to understanding notification services in sql 2005 and I have been working through some examples. I'm curious as to why there are always two queues and two corresponding services being set up even when both queues/services exist in the same database. Here is my ultimate goal. I want to have triggers put messages on the queue for various stored procedures to handle asynchronously - i.e. table xyz is updated, and I need a stored proc to take the updated values and possibly generate or update rows in another table. When I tried to set up one queue and one service, nothing ever seems to get put on the queue:
Create Message Type TestMessageType Validation = Well_Formed_XML;
Create Contract TestContract (TestMessageType Sent By Any);
Create Queue TestQueue;
Create Service TestService ON Queue TestQueue(TestContract);
Begin Tran;
Declare @DialogHandle UNIQUEIDENTIFIER;
Begin Dialog Conversation @DialogHandle
From Service TestService
To Service 'TestService'
On Contract TestContract
Send On Conversation @DialogHandle
Message Type TestMessageType (N'<message>Hello World</message>');
End Conversation @DialogHandle;
Commit Tran;
Select * From dbo.TestQueue;
(nothing is ever returned)
Hello,
I'm trying to do a very simple example of sending a message from Initiator queue to Target queue. The result is no messages are delivered.
Here's the code:
DECLARE @conversationHandle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE GmiInitiatorService
TO SERVICE 'GmiTargetService'
ON CONTRACT GmiContract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @conversationHandle MESSAGE TYPE GmiMessage ('test');
END CONVERSATION @conversationHandle
All three queues are empty (Initiator, Transmission, Target). When I comment out the last line ("end conversation"), the messages get stuck in the Initiator queue.
Please help!!
Thank you.
Hi
We can create a queue on a filegroup for performance reasons however what about the sys.transmission_queue which could have messages backing up when the target is down.
What are your thoughts.
Cheers
HiGot a strange problem.For some reason our web client box times out occasionally. Maybe afew times a day.Nothing appears in the logs.What I do know is that the disk queue ramps up to <high>(think top ofthe graph), processor queue jumps up and the tps drops to 0 (naturallyenough!) along with reduced pagefile usage. Usually at this time thetps is between 20 and 300. Running a pair of mirrored 18gb scsi disksfor the whole server (yeah yeah) and a whopping 640mb memory. Oh anda single piii 1 gig. Sql2k standard vanilla, server2k.What server trace events is it worth my catching to try and get abetter Idea of what may be causing this? No major jobs (backups etcseem to be occuring at these times).Cheers
View 1 Replies View RelatedHi everyone! I have a very brief question... I have 10 queues in my database and each of them are sent equal number of messages... There are instances where they execute/activate the stored procedures very fast but there are times where they don't, does anyone have an idea why this happens?
Thank you very much for taking the time to read my post. :)
Hi There
In terms of scaling out Service Broker to hundreds of instances, would it be better from a performance perspective so have one queue with all the messages coming in(obviously with a high number for max_queue readers), or create a number of queues and spread the messages across them ? Or is there no significant difference.
The one reason i am leaning towords multiple queues is so that is poison messages are found or a something lese goes wrong with a queue not all messages are affected, however creating multipple queues makes it more complex and required more administration ?
Any general best practice when it comes to this ?
Thank You
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedGreetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
Hi
We have problems with duplicate messages in Service Broker queues. We have tried the "fire and forget" method.
Senario
Initiator doing SEND and END Conversation , target doing RECEIVE and End Conversation ,
Sql servar agent job runing every minute doing End conversation with cleanup in sys.conversations_endpionts queue.
We are runing 5000 - 10000 messages per minute.
When the clean up job is running we get som duplicte messages in the target queue.
Enviroment
Two separate machines runing Sql Server 2005 Standard Editon SP2
Initiator Machine
CREATE MESSAGE TYPE [TransactionStatisticsSend] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML
CREATE CONTRACT [TransactionStatistics] AUTHORIZATION [dbo] ([TransactionStatisticsSend] SENT BY ANY)
CREATE QUEUE [dbo].[TransactionStatisticsQueueActive] WITH STATUS = ON , RETENTION = OFF
ON [PRIMARY]
CREATE SERVICE [TransactionStatisticsServiceActive] AUTHORIZATION [dbo] ON QUEUE [dbo].[TransactionStatisticsQueueActive] ([TransactionStatistics])
CREATE ROUTE [Route::TransactionStatisticsServiceStat,0705DBB2-8CBA-43BC-A8FF-774A27F8ABC0] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'TransactionStatisticsServiceStat' , ADDRESS = N'tcp://EBSDBCONFIG1A:4022'
CREATE REMOTE SERVICE BINDING [RSB::TransactionStatisticsServiceStat] AUTHORIZATION [dbo] TO SERVICE N'TransactionStatisticsServiceStat' WITH USER = [Proxy::BtsDebitServiceStat] , ANONYMOUS = OFF
CREATE ENDPOINT [EBSDBCURR1A_BROKER]
AUTHORIZATION [BTSTULLENtsappl]
STATE=STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = CERTIFICATE [EBSDBCURR1A]
, ENCRYPTION = REQUIRED ALGORITHM RC4)
ALTER PROCEDURE [dbo].[Bts_SP_TransactionStatPrepare]
(@TransferID varchar(30))
AS
declare
@RowCount Int,
@ErrorSave Int,
@Msg xml,
@dialog_handle UNIQUEIDENTIFIER
Begin
BEGIN TRY
set @msg = (Select
Regtime,
SendPartShortName,
RecPartShortName,
MsgType,
From btslogactive.dbo.StatTransferlog Tl
where TransferID = @TransferID
FOR XML RAW)
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [TransactionStatisticsServiceActive]
TO SERVICE 'TransactionStatisticsServiceStat'
ON CONTRACT [TransactionStatistics]
WITH ENCRYPTION = OFF ;
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [TransactionStatisticsSend]
(@msg) ;
END CONVERSATION @dialog_handle
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
End
Target Machine
CREATE MESSAGE TYPE [TransactionStatisticsSend] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML
CREATE CONTRACT [TransactionStatistics] AUTHORIZATION [dbo] ([TransactionStatisticsSend] SENT BY ANY)
CREATE QUEUE [dbo].[TransactionStatisticsQueueStat] WITH STATUS = ON , RETENTION = OFF
, ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[Bts_SP_TransactionStatUpdateBroker] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo' ) ON [PRIMARY]
CREATE SERVICE [TransactionStatisticsServiceStat] AUTHORIZATION [dbo] ON QUEUE [dbo].[TransactionStatisticsQueueStat] ([TransactionStatistics])
CREATE ROUTE [Route::TransactionStatisticsServiceActive,D8A1A78B-CEAD-4C63-B3B3-3C986D2AB3AA] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'TransactionStatisticsServiceActive' , BROKER_INSTANCE = N'D8A1A78B-CEAD-4C63-B3B3-3C986D2AB3AA' , ADDRESS = N'tcp://EBSDBCURR1A:4022'
CREATE ENDPOINT [EBSDBCONFIG1A_BROKER]
AUTHORIZATION [BTSTULLENtsappl]
STATE=STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = CERTIFICATE [EBSDBCONFIG1A]
, ENCRYPTION = REQUIRED ALGORITHM RC4)
Activation procedure
ALTER PROCEDURE [dbo].[Bts_SP_TransactionStatUpdateBroker]
AS
DECLARE @MessageType sysname
DECLARE @ConversationHandle uniqueidentifier
DECLARE @MessageBody XML
DECLARE
@RegTime datetime,
@SendPartShortName varchar(30),
@RecPartShortName varchar(30),
@RC int
BEGIN TRANSACTION;
WHILE (1=1)
BEGIN
WAITFOR
(
RECEIVE TOP (1)
@MessageType = message_type_name,
@MessageBody = message_body,
@ConversationHandle = conversation_handle,
@SeqNo = message_sequence_number
FROM [TransactionStatisticsQueueStat]
), TIMEOUT 5000;
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
IF (@MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @ConversationHandle
BREAK
END
ELSE IF (@MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
END CONVERSATION @ConversationHandle
BREAK
END
ELSE IF (@MessageType = 'TransactionStatisticsSend')
BEGIN
Begin try
SET @RegTime = @MessageBody.value('(/row/@Regtime)[1]', 'datetime')
SET @SendPartShortName = @MessageBody.value('(/row/@SendPartShortName)[1]', 'varchar(30)')
SET @RecPartShortName = @MessageBody.value('(/row/@RecPartShortName)[1]', 'varchar(30)')
SET @MsgType = @MessageBody.value('(/row/@MsgType)[1]', 'varchar(30)')
.
End try
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CONVERSATION @ConversationHandle
BREAK
END CATCH
-- EXEC @RC = SP_XXXXX
END
END CONVERSATION @ConversationHandle
BREAK
END
END
COMMIT TRANSACTION
RETURN
Sql Server Agent job procedure
ALTER PROCEDURE [dbo].[Bts_SP_Del_Conversation_Endpoints]
AS
begin
DECLARE c_PurgeConversationEndpoints CURSOR FAST_FORWARD
FOR SELECT conversation_handle
FROM sys.conversation_endpoints
with (nolock)
WHERE is_system = 0
AND [State] = 'CD';
OPEN c_PurgeConversationEndpoints;
DECLARE @DialogHandle UNIQUEIDENTIFIER;
FETCH NEXT FROM c_PurgeConversationEndpoints
INTO @DialogHandle;
WHILE @@FETCH_STATUS = 0
BEGIN
END CONVERSATION @DialogHandle WITH CLEANUP;
FETCH NEXT FROM c_PurgeConversationEndpoints
INTO @DialogHandle;
END
CLOSE c_PurgeConversationEndpoints;
DEALLOCATE c_PurgeConversationEndpoints;
end
Hi,
I want to know that how is it possible to achieve mirroring for the service broker queues in the database?
Hi,
I want to know that how is it possible to achieve mirroring for the service broker queues in the database?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
I have read from a variety of sources that using the "WITH CLEANUP" option on
a "END CONVERSATION" statement is bad and unnecessary. (Question
#1) Is this true???
My code does not work properly if I don't
use the "WITH CLEANUP" option. My code leaves closed conversation records in the
queues if I leave out the "WITH CLEANUP" option. The "END CONVERSATION"
statement is executing properly and flagging the conversation record as closed
but the records don't get deleted. All the messages are going back and forth
properly too.
My code is based on the HelloWorld ServiceBroker sample
which does not use "WITH CLEANUP". When I run the sample scripts everything
works great and the conversation records are deleted. However, this sample does
not uses an activation stored procedure to receive messages and respond with the
results. When I copy and paste the receive messages sample code into an
activation stored procedure is when the problem comes up. It's the same code!
(Question #2) Why am I getting different results depending
how the code is executed/activated???
This is could be a tough one. I
just hope somebody else has seen it too and figured it out. Thanks for the
help!
Thanks,
Greg Van Mullem
Is there a way for a .NET application to receive a notification when a service broker queue has been updated with a new message? I tried using SqlDependency on an SB queue but I got an "invalid" error in my notification handler.
Such a notification would be much better than having to poll the queue every N seconds.
Thanks
Hello All:
I've been experimenting with the new SQL Server Service Broker, and I think I've discovered some interesting behavior. Service Broker relies on "Queues" to store messages that need to be processed. Service Broker operates by sending a message from one Queue (the INITIATOR Quque) to another Queue (the TARGET Queue). A Queue can have an "Activation Stored Procedure" associated with it. This procedure is what actually processes the messages in the Queue.
The first behavior I obeserved related to the setting of a Queue's RETENTION parameter. The RETENTION parameter indicates whether or not the Queue will retain a copy of the messages it receives. By default, the parameter's value is "OFF" (meaning it will not retain messages). In the Activation Stored Procedure of my TARGET Queue, I used "sp_send_dbmail" to send an e-mail message. I wanted to capture the "conversation_handle" (a uniqueidentifier that identifies a particular message) and include it in the body of the e-mail. I was unable to capture it, because the Queue's RETENTION parameter was "OFF". When I tried to capture the conversation_handle from the INITIATOR queue (whose RETENTION parameter was "ON"), I was successful. The moral of the story is you apparently need to have RETENTION = "ON" if you need to capture information from a Queue.
The second behavior I observed relates to the setting of a Queue's MAX_QUEUE_READERS setting. This setting allows you to automatically invoke multiple instances of the Activation Stored Procedure. As messages come into the Queue, the Queue creates an additional instance of the Activation Stored Procedure up to the number you specified in the MAX_QUEUE_READERS setting. This allows parallel processing of messages in the Queue. There is also a programming technique called a "RECEIVE LOOP" which is used for processing messages. In the RECEIVE LOOP, you have a parameter called WAITFOR which tells the Queue whether it should stay on constant alert for new messages or whether it should time out after a specified number of seconds.
If you have the Queue wait indefinitely (by not specifying a TIMEOUT value in the WAITFOR statement) and you have invoked multiple copies of the Activation Stored Procedure, the procedure will remain in memory indefinitely. Therefore, if you make a change to the code of the Activation Stored Procedure, the change will NOT be reflected in the Activation Stored Procedure until you change the STATUS of the Queue. I had changed my procedure so that it would not send an e-mail, but the e-mails kept coming. The e-mails did not stop until I executed an ALTER QUEUE statement. I ran "ALTER QUEUE queue_name WITH STATUS = OFF;" and then I ran "ALTER QUEUE queue_name WITH STATUS = ON;" After that, the changes were reflected in the procedure.
Be aware of this behavior as you design your Queues.
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Our current project involved into managing NTFS hierarchical folders structured by multi-level customers and associated projects for them. Essentially each folder level represents one level of customer hierarchy or project root. Both have different subfolders and user access rights for them based on generic XML templates. The folders resided on file servers across the country and should be accessible in ordinary way NTFS file shares are allowing. LAN/Intranet MS AD Win2003 / SQL2005 environment.
The folder management system basically have to keep the folder structure in tact with changes in underlying managing application logic. That involves such operations as creating a new folder with subfolders, copying a folder with its content into another folder branch (which may be on the same or another server and place), deleting a folder/content, renaming a folder, applying NTFS access rights to folders and subfolders for users according generic templates. As all these actions are unreliable and some tasks may take hours to complete, SSB approach seems to be the viable solution. Some tasks involved have to be done within 10 minutes, others (are prolonged) have to be scheduled for overdnight run. Windows service like EA sample may be used to start the actual NTFS related tasks. Content transfer involves WMI remoting with robocopy tool on target machine (for better network utilization), other related tasks make use of WMI APIs and probably do direct (i.e. synchronous) calls to the remote target file server.
At this stage, making atomary executable modules that do just one functionally isolated task like DirCopy, DirCreator, DirRename, DirDelete, DirUserAccess seems like somewhat logical choice.
The questions starting to arise from SSB queues planning to adoption of ExternalActivator sample to run these atomic executables. The problem is that if SSB messages contain atomic tasks for these executables, they needed to be syncronized in two ways - by execution precedence (create or copy dir first, then apply users' access rights) and transactionally (only when all related tasks succeed, the appropriate feedback action and event log writing can be taken.
I can imagine two implementation scenarios below.
Case A. Create common queue for directory creating, content transfer and access rights.
In that case ExternalActivator has to be either extended & re-designed or it has to activate another Activator-Coordinator (middleway) executable, that would actually read the queue and, based upon the message type, run the appropriate atomary executable. In that scenario, the queue processing would be stalled because if the previous task was content transfer and lasts, say access rights to them can't be started before the transfer task has been finished OK. That in turn probably will require running multiple instances of the atomary tasks and using conversation groups, wouldn't it? What would be the most reliable and simple way to achive that?
Case B. Dedicated queues for each atomary executable modules.
Neither EA changes nor extra Activator-Coordinator middleway executable required. But because the atomary task-oriented queues are not syncronized with each other the queues internal conversation groups wouldn't help much... That means if a directory not yet exists, userRights module have to wait. But, what if we are transferring directories from path X to Y, based on what the userRights module knows to wait? With zero asynchronous design experience I'm lost here...
Hope I described the app domain understandably, thanks for hints leading to the working solution!
I have a stored procedure which checks to see if a user's email address exists before it inserts a new record. If it does it should return a message that notifies the user they are already subscribed. If they are not a different message should be returned stating that a new subscription has been created. This procedure works and is shown below.
The problem I am having is with the first SELECT statement. How can I get the procedure to show just one message and not the results from the first SELECT statement too?
CREATE PROCEDURE sp_InsertSubscription
@SubscriberFirstName VARCHAR(50),
@SubscriberLastName VARCHAR(50),
@SubscriberEmailAddress VARCHAR(50),
@SubscriberZipCode INT,
@IsActive BIT,
@SubscriberOptIn BIT,
@AdditionalOffersOptIn BIT,
@Msg VARCHAR(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Check to see if email address exists first
SELECT @@ROWCOUNT FROM Subscriber WHERE SubscriberEmailAddress = @SubscriberEmailAddress
IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'This email address is already subscribed'
SELECT @Msg AS 'User'
RETURN
END
-- Insert statements for procedure here
INSERT INTO Subscriber (SubscriberFirstName,
SubscriberLastName,
SubscriberEmailAddress,
SubscriberZipCode,
IsActive,
SubscriberOptIn,
AdditionalOffersOptIn,
SubscriberSignUpDate)
VALUES (@SubscriberFirstName,
@SubscriberLastName,
@SubscriberEmailAddress,
@SubscriberZipCode,
@IsActive,
@SubscriberOptIn,
@AdditionalOffersOptIn,
GETDATE())
IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'New user subscription created'
END
SELECT @Msg AS 'User'
END
GO
If the user's email address does not exist I get
(No column name)
User
New user subscription created
If the user's email address does exist I get
(No column name)
0
User
This email address is already subscribed
I would like for the (No column name) to go away - I know this is coming from the first SELECT statement. How do I suppress that statement from being output, yet still get the @@ROWCOUNT variable set?
Hi
I setup a message between 2 database on a local instance. The message is send to the target and the target did send a reply. However, the reply is not picked up by the initiator. I did a try but there was nothing telling me why it did not pick up the reply. there is nothing in the textdata of the brokerclassify event. I query the queue on the initiator, the message is there, but the stored proc attached to the queue is not doing anything it seems. What else can I check here,
thanks
P
Hi
I have trouble to get this stored procedure running and I tried to figure out where I did a mistake but I'm just lost, so if somebody could help me... Thanks!
The Error Message is 102, Level 15, State 1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE InsertNewDocumentTypeAndAddSystemFields
@varDocumentTypeName nvarchar(254),
@varNEWDocumentTypeID integer = 0,
@varTempID integer
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Document_Type_LookUP
VALUES(@varDocumentTypeName);
@varNEWDocumentTypeID = IDENT_CURRENT('dbo.Document_Type_LookUP')
-- Name or Title
INSERT INTO dbo.Document_Field
SELECT 'Name', SQLDataTypeID FROM SQLDataType WHERE SQLDataTypeName = 'string'
@varTempID = IDENT_CURRENT('dbo.Document_Field')
INSERT INTO dbo.Document_Type_Field_Link
VALUES(@varNEWDocumentTypeID,@varTempID)
-- Filename
INSERT INTO dbo.Document_Field
SELECT 'Filename', SQLDataTypeID FROM SQLDataType WHERE SQLDataTypeName = 'string'
@varTempID = IDENT_CURRENT('dbo.Document_Field')
INSERT INTO dbo.Document_Type_Field_Link
VALUES(@varNEWDocumentTypeID,@varTempID)
END
GO
Thanks in advance!
I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).
In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.
Code SnippetIF @SQLCode <> 0
BEGIN
SELECT description = @ErrMsg
FROM master.dbo.sysmessages
WHERE error = @SQLCode
END
Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.
Thanks
Hello everybody,
I've encountered a strange thing using a CLR Stored procedure:
The procedure throws an exception with no message inside... value = {" "}
Basically the procedure has a string as argument which contains a SQL statement that changes according to the users selections...
The results of the query are saved into a dataset for further processing.
Those resultsets can sometimes be very big (ex: 15000 records...). (For the record the procedure works fine for smaller datasets ex 6000 records and running the same query on the application server returns the expected resultset )
By Debugging the procedure I could determine that the failing point was when the dataset is filled...
Anyone having any idea??
The only information I have from this exception is the stacktrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.CloseInternal(Boolean closeReader)
at System.Data.SqlClient.SqlDataReader.Close()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at DataAccess.runScalar(String strSQL, Boolean isStoredProcedure) in c:InetpubwwwrootStatistixApp_CodeDataAccess.cs:line 114
Thanks in advance
Alaindlk