Messages In Transmission_queue With No Errors, Status Is Conversing.
Jan 29, 2008
I've got 2 service broker databases on remote servers. I've created my endpoints, my routes and have everything set up. But when i send a test message, the messages set in the transmission_queue. There is no transmission_status. And when i look in at the sys.conversation_endpoints view I see that the conversation status is conversing. One odd thing I wanted to point out though is that the far_broker_instance column of the sys.conversation_enpoints view is null. When i run a trace on both databases, I see activity on the Initiator with things like Started_OutBound and conversing but I don't see any messages such as acknowledgment or any errors. On the Targer side I see no activity at all. Does anyone know what the deal is. Why don't I get some kind of error message. Why are all my messages staying in the transmission_queue?
Thanks in advance.
View 2 Replies
ADVERTISEMENT
Oct 4, 2006
Once my target service start to process, how can I stop the rest from running?
right now I'm looping sys.conversation_endpoints and run "END CONVERSATION @dhValue"
Is there a simpler way to stop processing a service without interfering with other services?
View 3 Replies
View Related
Dec 19, 2007
We are having a problem with messages getting out of transmission_queue and into the queues themselves. The queues all are actviated and enabled, and our service broker is enabled at the database level. The dba detached & reattached the db yesterday, which I believe may be the cause of this problem. Everything seems to be in order (sp ownership, activation procs, etc), except when I run:
select [name], count(*)
from sys.dm_broker_queue_monitors qm
inner join sys.service_queues sq on qm.queue_id = sq.object_id
group by [name]
I get 2 entries for each queue. And the last_activated_time is the same date as the detach event. When we reattached we had to do an alter database set enable_broker in order to get it back up and running. When I run the above query on our dev and test environments, I get only one entry per queue.
Does anyone know why this would happen? Is this a valid state for SB?
And to get past it, if we can't figure out the real fix for it, we want to get a copy of all the messages in the transmission queue, do an alter database set new_broker, then replay them all into SB. We hope this fixes the root cause, but it's a guess.
Any ideas? Thanks in advance,
John
View 1 Replies
View Related
Aug 14, 2007
Hi,
At my company, we're trying to use service broker to create a client-server system where there is a head office machine and multiple outlets registered with that head office. My problem is that sometimes when a branch sends a message to the head office, it just seems to sit in the transmission queue and never gets sent. If I run a script that forcibly ends the conversations on the client machine (with cleanup), storing the message bodies and then resend them, they seem to get through fine.
The way that we send messages is by calling a t-sql stored procedure from a c# application using SqlCommand (don't know if this should make any difference).
If I monitor the Head Office machine and one of the Outlets while this is happening, on the HO I get three events in a row:
Broker: Message Classify (1 - Local)
Audit Broker Conversation (2 - No Certificate)
Broker: Message Undeliverable (1 - Sequenced Message)
The TextData contained in the third event is: This message could not be delivered because the security context could not be retrieved.
The RoleName of the server is Initiator, and the TargetUserName is the name of the service on the Outlet.
On the Outlet I get the following event repeatedly (presumably as it continues to try sending the message) - Broker: Remote Message Acknowledgement (1 - Message With Acknowledgement Sent).
On the client the RoleName also appears to be Initiator, and the TargetUserName is blank.
This would make me suspect that certificates were missing or something, except that if I remove messages from the queue and resend them they seem to get through, and also I've checked both databases and they have the correct certificates.
Any ideas?
Thanks in advance,
Adam
View 6 Replies
View Related
Jan 29, 2007
Sorry for the stupid question, but I can't seem to figure it out...
There are 119 messages that are stuck in the transmission queue, all for the same queue. When I check the status of the queue (via sys.service_queues), is_receive_enabled = 1, is_activation_enabled = 1, and max_readers = 3. When I check to see if there is an active queue monitor (via sys.dm_broker_queue_monitors) there is nobody watching this queue. What would cause this queue to be active, enabled but have nobody montioring it? Is there something internal that went wrong that made these (outbound) messages get stuck in the transmission queue, and is not showing up in the views? How can I get these messages "un-stuck" and flow through the system?
A problem I am seeing is the return message to this queue (to signify the target has consumed the message, and to end the conversation) are not being consumed, thus getting stuck in the "DI" state.
Any suggestions would be greatly appreciated.
Thanks in advance,
John Hennesey
View 7 Replies
View Related
Mar 26, 2007
For some reason the messages are stuck at sys.transmission_queue. The transmission status seems to be null. I verified all the Queues and they hae the values of (is_receive_enables = 1, is_enqueue_enabled = 1, is_activation_enabled = 0)
I have set up the service broker on different domain and have bidirectional ports open to receive messages.
I can send and receive messages, when I run the same service broker setup scripts at the machines that belongs to same domain .
any help appreciated.
View 3 Replies
View Related
Feb 28, 2007
I may have a misunderstanding of how SB works, but this seems like a problem.
If a queue is disable (i.e. status = off) and a message is sent to the queue the message is placed on the sys.transmission_queue. Once the queue is enabled I thought the messages were sent to the queue in the order they were placed on the sys.tranmission_queue? I have been troubleshooting a problem and this is not the case. Do I have a misunderstanding of how the sys.transmission_queue works?
The queue has retention turned off.
View 4 Replies
View Related
Jun 7, 2007
Hello,
I have a question..
I set-up my Service Broker comunication on the same SQL Server Instance beetween 2 different DBs.
One DB behave as Initiator and send messages to the SB service setup in the other DB.
I execute the SEND statement from the Initiator and if I count the messages on the sys.transmission_queue before to commit the transaction the count returns 0.
If I try to send a message not compliant with the message type, the count that runs after the SEND returns 1 - far enought.
I'm confused about the first behaviour because from what I understood the Acknolodgment and the removal of the message from the sys.trasmission queue should happen after the COMMIT.
Some expert can aswer to this direct quection?
Thank you
Marina B.
View 7 Replies
View Related
Sep 5, 2006
Hi
We have a VB6 app, with SQL2005 database, SQLOLEDB driver
We have it installed at many clients and normally works fine.
We've migrated one client from SQL2000 to SQL2005 and had to move the database to a new machine.
It's now running SQLExpress sp1, on a Win XP sp2 machine
The clients now frequently get either
a) SQL timeouts
or
b) Runtime Error€™2147467259 (80004005)
[DBNETLIB][Connection Read(recv()).]General network error. Check your network document
Errors
I suspect it is something to do with connections not be released (even though we explicitely free them in the app).
Where do I look ?
Thanks
Bruce
View 5 Replies
View Related
Nov 30, 2007
I am using execute sql task and my sql statement contains
EXEC test ?
procedure test has 2 parameters. I added parameters using parametermapping tab. When I run i get an error message
Multiple-step OLE DB operation generated errors. Check each OLE DB status value
View 1 Replies
View Related
Nov 9, 2007
Hi all,
We have Windows 2003 64 sp2 Xeon, 2005 EE SP2 64 bit...
Trying to do conversion from DTS sql 2000..One package use load from excel to sql..So I tried to create same thing by myself.. Hell, so many issues.. So I used wizard, package created, I changed Run64bit to False, tried to run package, once - completed in debug mode.. Now it's time to create deployment utility and deploy package..During execution of manifest file got error:
Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
In BIDS, open up solution and tried to rerun package again - no way,".. cannot acquire connection from connection manager blah blah blah.."
Even tried to fire package without debugging, it fires 32 bid execution utility, so no question about 64 bit mode.. package failed..
Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785}
Message: ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The
external request has completed.
Start Time: 2007-11-09 09:41:25
End Time: 2007-11-09 09:41:25
End Log
Error: 2007-11-09 09:41:25.95
Code: 0xC0202009
Source: Package_name loader Connection manager "SourceConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x80040E21 Description: "Multiple-step OLE DB operation generated error
s. Check each OLE DB status value, if available. No work was done.".
End Error
Log:
Name: OnError
Source Name: Data Flow Task
Source GUID: {2A373E56-8AAF-40E9-B9EF-4B2BB40175F0}
Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785}
Message: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
. The AcquireConnection method call to the connection manager "SourceConnection
Excel" failed with error code 0xC0202009. There may be error messages posted be
fore this with more information on why the AcquireConnection method call failed.
Any help..?
View 1 Replies
View Related
Aug 25, 2015
I have  data driven subscriptions which are working fine for some subscription and one i have newly developed which is used to send the missing punch report to employeesNow , when the subscription runs it shows that all have gone email in report server but some of they are not getting any email i have tested it on my id to send each ones report to me but i also get sometimes 2 of 15 , 4 of 15 or else but not all i also checked mailroot folder under my server's inetpub there are nothing stucks. How to check why is it not sending email to all employees ?
View 3 Replies
View Related
Jan 31, 2007
Hi,
I am using ATL COM library application. It is using sql data base for fetching the records. Some times, i get the following error. could you please let me know, why this happens? This is not reproduceble every time.
(Error! hr=80040e21, hrDesc=Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work
Here is the code to connect to database which i am using.
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
dbinit.AddProperty(DBPROP_INIT_CATALOG, (const char *)bDatabase);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, (const char *)bServer);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
dbinit.AddProperty(DBPROP_INIT_TIMEOUT, (short)150);
hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
if (FatalError(hr, "db.Open", buf))
{
*iErrorCode = hr;
return S_OK;
}
Any help, appreciated.
Thanks,
Satish
View 1 Replies
View Related
Oct 17, 2006
I have seen this buried deep with the questions on Service Broker, but I am looking for it again. How do you delete all records from your sys.Transmission_Queue. This is on a test server and I want to clean it before some more test.
View 5 Replies
View Related
Jul 25, 2007
hi all!
I have 2 instances that communicate via service broker.
The conversations are only one way from initiator server1 db to target server2 db.
I also reuse dialog id's in BEGIN DIALOG @dlgId
i save @dlgId from the first run into a table and then retreive it for each message send
since the messages are constant i don't close the dialog at the target for each message.
i'm just wodering why do both sys.transmission_queue and sys.conversation_endpoints get a row
for each message i send but the transmission_status in sys.transmission_queue is emtpy.
also each conversation_handle and conversation_id is different for each row and
only one row in each sys table has the same conversation_handle as my saved @dlgId.
just wondering what is going on.
this code is done on the initiator
DECLARE @dlgId UNIQUEIDENTIFIER
-- each database has one dialog id
SELECT @dlgId = DialogId
FROM dbo.Dialogs
WHERE DbId = DB_ID()
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @dlgId
FROM SERVICE [//DataSender] -- service on initiator server
TO SERVICE '//DataWriter', -- service on target server
-- Target's Service Broker Id
'83382A22-2830-4B25-B067-15AAC255EB03'
ON CONTRACT [//Contract1]
WITH ENCRYPTION = OFF;
-- Send data
;SEND ON CONVERSATION @dlgId
MESSAGE TYPE [//Message1] (@msg)
Thanx,
Mladen
View 7 Replies
View Related
Feb 15, 2006
Are lock hints propagated to the underlying tables of system catolog views? I ask because I often query sys.transmission_queue with nolock, and I wanted to know if this was honoured through out the underlying tables.
Secondly, is sys.transmission_queue indexed at all, providing a way to prevent table-scanning?
Thanks
View 1 Replies
View Related
Dec 21, 2006
Is there any thought going into moving these two tables to a file group that we can control? Putting this in Primary with the rest of my system tables is quite problematic, and hinders my ability to manage space usage on my files. Traditionally, we didn't have to consider a primary file group that could grow to large proportions, but now with these two tables it can. If a large volume of messages gets sent through and the system can't keep up, then these tables and my primary file group will grow sometimes enormously.
View 8 Replies
View Related
Jul 13, 2005
as Christopher Yager say in "Need distributed service broker sample", I also test sending messages between two SQL Server 2005 instances,and after I setup the test environment with instance1 and instance2,I find queue [q2] in ssb2 can't receive message from ssb1. when I query by "select * from sys.transmission_queue",I get some message records that transmission_status is "64(error not found)".
View 12 Replies
View Related
Jun 16, 2007
Hi was wondering whether it is possible to log somewhere outside SB that there are messages in the transmission_queue because the Target queue was disabled.
I was testing this scenario:
try to send messages on a disabled queue and log the problem.
But the transmission_status from the trasmission_queue is always empty.
This is the code that I tried to execute between the send and the commit and after the commit:
WHILE (1=1)
BEGIN
BEGIN DIALOG CONVERSATION .....
SEND ON CONVERSATION ......
if select count(*) from sys.transmission_queue <> 0
BEGIN
set @transmission_status = (select transmission_status from sys.transmission_queue where conversation_handle=@dialog_handle);
if @transmission_status = ''
--Successful send - Exit the LOOP
BEGIN
UPDATE Mytable set isReceivedSuccessfully = 1 where ID = @IDMessageXML;
BREAK;
END
ELSE
raiserror(@transmission_status,1,1) with log;
END
ELSE
BEGIN
UPDATE [dbo].[tblDumpMsg] set isReceivedSuccessfully = 1 where ID = @IDMessageXML;
BREAK;
END
END
COMMIT TRANSACTION;
As I wrote before the @transmission_status variable is always empty and I have the same result even if I put the code after the commit transaction!
Maybe what I'm trying to reach has no sense?
With the event notification I can notify when the queue is disable because the receive rollsback 5 times but what if by mistake the target queue is disabled outside the SB environment? I can I catch it and handle it properly?
Thank you!Marina B
View 3 Replies
View Related
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
Jul 1, 2015
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
View 1 Replies
View Related
Jul 31, 2006
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Thanks,
Lee
View 5 Replies
View Related
Apr 20, 2006
Starwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).
. . . .
. . . .
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"
View 1 Replies
View Related
Jan 7, 2008
Hi Experts,
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Can anyone suggest how to solve this issue.
View 11 Replies
View Related
May 3, 2005
Returning "completed" when status = 1 and "not completed when status = 0
View 3 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