No Messages In The Queues.
Mar 10, 2006
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.
View 11 Replies
ADVERTISEMENT
Apr 2, 2008
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
View 2 Replies
View Related
Sep 22, 2006
Hi, 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.
View 3 Replies
View Related
Jul 27, 2006
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)
View 11 Replies
View Related
Apr 19, 2006
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
View 3 Replies
View Related
Jul 20, 2005
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 Related
Nov 13, 2006
Hi 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. :)
View 10 Replies
View Related
Aug 14, 2006
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
View 7 Replies
View Related
Jun 20, 2007
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
View 3 Replies
View Related
Jan 4, 2007
Hi,
I want to know that how is it possible to achieve mirroring for the service broker queues in the database?
View 1 Replies
View Related
Jan 4, 2007
Hi,
I want to know that how is it possible to achieve mirroring for the service broker queues in the database?
View 1 Replies
View Related
Oct 11, 2005
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 Replies
View Related
Sep 13, 2006
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
View 3 Replies
View Related
Jul 19, 2007
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
View 4 Replies
View Related
Dec 23, 2005
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.
View 6 Replies
View Related
Feb 21, 2006
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!
View 8 Replies
View Related
Feb 15, 1999
Trying to obtain a full list of SQL 6.5 error messages into a file. There are
2,215 messages present in master.sysmessages, but there is a total of 2,733
messages listed in the dialog box within enterprise manager. Message numbers
17026 thru 19020 don't seem to be stored in the sysmessages table.
Anyone know how I could obtain an extract to obtain a full list of messages ?
View 2 Replies
View Related
Apr 15, 2008
After sending a message the message is just gone.
Our transmissionand and out our transactionqueue is empty.
This is our CLIENT:
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
SERVER
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
Hopefull you have an idea?
View 1 Replies
View Related
Dec 4, 2007
I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :
1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.
2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.
3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.
I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :
CREATE PROCEDURE sp_hitMe@ntid varchar(10),@page varchar(50),@thisHit datetimeASSET NOCOUNT ON
DECLARE @tempDate datetimeDECLARE @yearCount intDECLARE @monthCount intDECLARE @inMonth varchar(20)DECLARE @monthColumn varchar(10)SET @inMonth = DATENAME(mm, @thisHit)SET @monthColumn = CASE WHEN @inMonth = 'January' THEN 'hitsInJan' WHEN @inMonth = 'February' THEN 'hitsInFeb' WHEN @inMonth = 'March' THEN 'hitsInMar' WHEN @inMonth = 'April' THEN 'hitsInApr' WHEN @inMonth = 'May' THEN 'hitsInMay' WHEN @inMonth = 'June' THEN 'hitsInJun' WHEN @inMonth = 'July' THEN 'hitsInJul' WHEN @inMonth = 'August' THEN 'hitsInAug' WHEN @inMonth = 'September' THEN 'hitsInSep' WHEN @inMonth = 'October' THEN 'hitsInOct' WHEN @inMonth = 'November' THEN 'hitsInNov' WHEN @inMonth = 'December' THEN 'hitsInDec' END DECLARE @insString varchar(500)DECLARE @updString varchar(500)SET @insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @monthColumn + ') VALUES (' + @ntid + ', ' + @page + ', ' + @thisHit + ', 1, 1)'
if exists(select * from tblTracking where ntid = @ntid and @page = page) begin if exists(select * from tblTracking where lastVisit = @thisHit) begin -- DO NOTHING! end else begin DECLARE @theColumn varchar (100) SET @theColumn = 'SELECT ' + @monthColumn + ' FROM tblTracking WHERE ntid = @ntid AND @page = page' SET @yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @ntid AND @page = page) + 1 SET @monthCount = (Exec @theColumn) SET @monthCount = @monthCount + 1 SET @updString = 'UPDATE tblTracking SET lastVisit = ' + @thisHit + ', hitsThisYear = ' + @yearCount + ', ' + @monthColumn + ' = ' + @monthCount + ' WHERE ntid = @ntid AND @page = page' Exec @updString end endelse begin Exec @insString endGO
And to my surprise, the only 3 errors I got were :
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39Incorrect syntax near the keyword 'end'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45Incorrect syntax near the keyword 'Exec'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50Incorrect syntax near the keyword 'end'.
However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?
View 9 Replies
View Related
Sep 26, 2005
i'm trying to make private messages like the one in this site, i didnt start programming yet, the problem is in the DB schema, i have 3 tables (Users, Messages, MessageDetails) i think the problem that the UserID is related to the other two Tables, so to know the sender and reciever, when i try to view all messages for specific user and show all users who sent it, it give nothing back, so any help in the DB, thanx 4 help.
View 13 Replies
View Related
Mar 9, 1999
Is there a way to return the SQL Native error to a Visual Basic program?
View 3 Replies
View Related
Mar 3, 1999
Hey everyone,
Here is what I'm doing:
exec xp_sendmail @recipients = 'ampx@hotmail.com',
@query = 'select * from information where recid <= 10',
@subject = 'Query test',
@message = 'hello',
@attach_results = 'TRUE',
@width = 250
I get this error:
Msg 2812, Level 16, State 4
Stored procedure 'xp_sendmail' not found.
Anyone know why? isn't xp_sendmail a function of SQL?
Thanks
View 1 Replies
View Related
Jul 18, 2006
I have a website with a DB table of messages sent by users.
I want to select the first 3 messages for each unique user.
I found a way to accomplish this by pulling all of the messages from the sql and filtering the results via ASP.net code but it seems to be really really slow.
I hope one of you guys can help me accomplish this via sql .
Messages Table
Mid Username SendDate
110016/07/06 13:00
210017/07/06 14:00
310117/07/06 14:30
410217/07/06 15:00
510318/07/06 16:00
610018/07/06 17:00
710218/07/06 18:01
810319/07/06 18:02
911019/07/06 19:03
1010219/07/06 19:04
1110021/07/06 20:00
1210321/07/06 20:00
1310221/07/06 20:00
Query result:
Mid
1
2
6
3
4
7
10
5
8
12
9
THANK you guys!
View 9 Replies
View Related
Apr 30, 2007
Hi,
I have a table that has 4 fields and has free format text messages. The Identification field contains the client id
MNo field has Message Number specific client id. A single messge is split down into multiple rows and Seq field gives the relation which text message belongs to which MNo
Message table:
IdentificationMNoSeqText
CLIENT-01980001001This is the header record for client ABS.
CLIENT-01426001001This is the Header Record for DAN client #1426
CLIENT-01327001001This is the Header record for Glaxo Client
CLIENT-01327002001Jen: pursuant to my conversation via phone with you and Mel on 9/20
CLIENT-01327002002regarding the Pete relocation, there is concern as to whether this
CLIENT-01327002003sale is good or not. It appears that the prospective buy
CLIENT-01327002004er will be in tommorrow if he does not produce a firm fina
CLIENT-01327002005ncing commitment by that date.
CLIENT-01327003001Message for client A
CLIENT-09970001001This is the header record for client Nestle
I need to create a query where i need to join all the text messages that belong to the same MNo and Identification into one field. The output should be like the following:
Output Message table:
IdentificationMNoSeqText
CLIENT-01980001001This is the header record for client ABS.
CLIENT-01426001001This is the Header Record for DAN client #1426
CLIENT-01327001001This is the Header record for Glaxo Client
CLIENT-01327002001Jen: pursuant to my conversation via phone with you and Mel on 9/20 regarding the Pete relocation, there is concern as to whether this sale is good or
or not. It appears that the prospective buyer will be in tommorrow if he does not produce a firm financing commitment by that date.
CLIENT-01327003001Message for client A
CLIENT-09970001001This is the header record for client Nestle
In the above output table for Identification = CLIENT-01327 and Mno = 002 the entire text message has been concantenated into one field.
Is there any way that i can write in a single query to obtain the output file.. Please help.
View 2 Replies
View Related
Jul 20, 2005
Hello,I'd like to know if I use DTS. If I use it immediately, I can see the errorin the dialog box, instead, if I use it with scheduling, where Can I checkthe error ?ThanksSaimon(Florence)
View 2 Replies
View Related
Aug 16, 2007
we've gotten this to work from sqlclr and understand the dependence wcf has on the framework but still feel compelled to ask if somehow t-sql can send a message to a wcf endpoint without getting sqlclr involved. I've moved this question over here from the t-sql forum.
View 1 Replies
View Related
Mar 22, 2006
Hi, after some tooling round, googling, hair-pulling etc i have some prototypes of a simple C#.NET 1.1 console app which can now post a message to my yukon SB service i set up. It calls an internal activation stored proc to store the message in a simple table.
The problem i had was in the connection string "... Asynchronous Processing=true". The run-time throws an exception moaning about the Asynchronous Processing=true bit. So i just omitted it completely... Seems to work fine and as expected. I can post a message, my app exits, and the dummy stored proc (which i deliberately made enter a loop updating the table thousands of times to slow it down and lock the table) completed several seconds later proving the asychronousity of it (i issued a "select count(*) from X" query to wait for the write lock to be released, so i knew when the stored proc had finished and released its lock).
So, i guess my question is, firstly what is the "Asynchronous Processing=true" bit for in the connection string if we can just leave it out? And can anyone see any issues with what ive done?
View 3 Replies
View Related
Jan 10, 2006
Hey guys
I have another weird looking problem
I have a queue with a certain number of messages waiting in it to be processed. When I try and receive a message from this queue, I get back an empty row; no message. However, when I try and select from this queue, I see all the messages in there. Even sp_spaceused reports that there are rows in this queue. The status of each unreceivable message is 2. What would be the reason why I cannot receive these message?
I should add that newer messages added to this queue are receivable, meaning that if I add another message to the queue, when I do a receive, I get this new message and not the older ones in the list.
Thanks
View 4 Replies
View Related
Jul 3, 2007
Hello,
I am trying to figure out how to properly read a single message(poll) at a time from ADO .Net in c# 2005.
I am assuming i need to write a sproc that does a receive, but I wanted to validate if there were already any examples of this already?
I have been reading quite alot but don't see this exactly as an example anywhere and it is probably easier then my brain is allowing it to be.
If you have an example or would help me i would appreciate it. I am "hoping" for more then just "write a sproc and call it" as I believe this is accurate but am hoping for more info.
Happy to read, happy to research, but I am atm, lost for where to go next. I am going to start doing some plain testing, but appreciate any help.
Thanks,
-Mike
View 1 Replies
View Related
Jan 15, 2007
[Reposted because no-one answered me before: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097115&SiteID=1]
Hi,
Quick one for anyone from Microsoft. Does there exist anywhere a definative list of all the new log messages that are being introduced in SP2?
Thanks
Jamie
View 2 Replies
View Related
Mar 10, 2008
Greetings
I'm learning SSIS and BIDS. I have extreme difficulty making sense of the error messages that come out.
First of all, what do the numbers mean? Each column, error, etc. is assigned a number that obviously means something yet I cannot relate them to anything. For example: The output column Name (713) on output Test (15) and Component (15) -- My table doesn't have 713 columns in it...
Then there are the error codes that obviously contain something useful. For example:
DTS Error: Microsoft.SqlServer.Dts.Runtime.TaskHost/QueueFuzzyName [33]SIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (46)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (46)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Where would I look up: DTS_E_INDUCEDTRANSFORMFAILUREONERROR and 0xC020907D? I understand that it tried to convert a value in something numbered 46 (no idea what that is) and the conversion failed. But that's it. How do I transmogrify 46 to something I can look at. I'm a little fuzzy on what a Destination Input is. Isn't an output a destination?
Or this one:
Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors"
Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
I have absolutely no idea what this means except that many things happened, one of them failed, I have an error of some sort and it's error code is 0x80040E21. It must be important, it's in there twice, but what does it mean?
I'm thinking that in the help somewhere all these error messages are listed and I can look them up, but I can't seem to find it anywhere. Am I supposed to be converting them to decimal first?
Any advice from you experts would be much appreciated.
View 5 Replies
View Related
Aug 10, 2006
Hi There
What happens to messages that go to error?
I know that they are returned to the queue of the service that sent them, but the message it the queue is the error message and description. Transmission queue is empty.
Where is the actual message that was sent ? In my case if the message went to error i must resend it.
But there is no error when you send the message, transmission queue is clear, the error just comes back to the queue but i need the original message so that i can resend it after the error is fixed but i cannot find it anywhere ?
Thanx
View 18 Replies
View Related
Sep 6, 2006
I'm using SqlBulkCopy. Does anyone know how I can output what row (its column names) are throwing a duplicate primary key message when I bulkCopy.WriteToServer(datatable1)?Thanks
View 1 Replies
View Related
