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?
I was wondering if there are any potential issues/drawbacks of a service sending a message to itself. Basically I have a situation where I only require one queue to hold messages. So say for instance I had a service named S1 associated with a queue named Q1 then I can do this:
Code Snippet
Begin conversation dialog @dialog_handle
from S1
to 'S1'
on contract MyContract
with encryption = off; Send on conversation @dialog_handle message type MyMessage
Yesterday I made the HelloWorld_CLR sample work and figured today I would create a simple windows service to immulate its functionality. When this was not working I tried the sample again and it stopped working. I have tried several things and nothing has worked. Has anyone seen this before or have any ideas on how I can get it working again? I even tried removing SQL Server and its samples then reinstalling to give me a clean slate but that did not work. I did not make any changes to the software nor did I make any changes to the database however the sample did so I need to make sure I can fix this incase it happens in development not to mention I need to continue development.
The Client Queue is normally empty but sometimes has a request to two in it. The Server Queue has alot in it that are both Request and an unspecified type or has a URL for a type. I would paste it in but it takes too much space. Please let me know if you have any ideas because I am not sure what else to try.
Bellow is a sample of the output from running this sample.
Connecting to SQL Server instance Connected to SQL Server instance
Transaction 1 begun Dialog begun from service (HelloWorldClient) to service (HelloWorldService) Message sent of type 'Request' Transaction 1 committed
Transaction 2 begun Waiting for Response.... No message received - Ending dialog with Error Transaction 2 committed
We're looking to send messages to an SSB Queue (our 'Notification' queue) that contains multiple values.. like Message #1: 20,1 to indicate Record #20, send Notification #1.. the reason for this is we have lots of different types of records in our database, and based on certain conditions, we would like to send different notices regarding these various record types.
What is the best format to send mesages like this? We thought about sending TABLE vars.. what other options are there?
Hello, I've been banging my head trying to figure this one out so I appreciate any help you guys can provide.
I've setup and configured SQL 2005's Database mail and have sent a few plain and simple text emails and I receive it fine at the destination.
I now want to send mime messages but I'm not sure what I'm doing wrong. I took the following source from an email that linked-in sent me which was a mime message: "
Mime-Version: 1.0
Content-Type: multipart/alternative;
boundary='----=_Part_434343432'
------=_Part_434343432
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Plain Text Goes Here
------=_Part_434343432
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<html>
<body>
<b>HTML Goes here</b>
</body>
</html>
------=_Part_434343432--
" and put the above text in the @body parameter of the stored proc msdb.dbo.sp_send_dbmail and submitted it.
When I received the email, it didn't appear as html. Instead, I just got the mime message above as plain text in my email as if someone just typed it and sent it to me. What do I need to do for SQL Database Mail to send the above as a MIME message?
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
Thanks in advance... What I have to do is take orders and generate a report that gives the daily totals of the orders The dates will be on the vertical and the subclass titles on the horizontal: Output
date SO PS Total 1/1/08 5 4 9 1/2/08 3 2 5 1/3/08 5 4 9
mtd 13 10 23 ytd 13 10 23
The SO orders for example can have a diff status each day and I will need to count this . 1 - how would I pull the orders that I want. I have pulled all credit work orders in but I only want the ones from 2008. Then once I pull them in how do I get the daily info., ie what records fell into the categories for that day. Do I need a loop, cursor, case statement? I also need to count the # of any give group as above. I am new at this and really do not know where to proceed.
see table below CREATE TABLE #CREDIT_WO ( WO_Noint, WO_Status varchar (1), WO_Classvarchar (30), WO_Subclassvarchar (30), WO_Create_Datedatetime, WO_Sch_Datedatetime, WO_Post_Datedatetime, WO_Statevarchar (2) ) INSERT INTO #CREDIT_WO (WO_NO, WO_Status, WO_Subclass, WO_Create_Date, WO_Sch_Date, WO_Post_Date, WO_State) SELECT wowo_wono, wowo_status, wtyp_wo_class, wowo_wo_date, wowo_sch_date, wowo_post_date, wowo_state FROM WO_WO with(nolock) inner join wo_type with (nolock) on wowo_wo_type = wtyp_wo_type WHERE wo_class = 'Credit' and subclass in ('so', 'ps') and wowo_wo_date between @Start_date and @End_date
I have written a stored procedure which should send mail from the stored procedure in sql server 2005. Here is the SP set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo ALTER Procedure [dbo].[sp_SMTPMail] @SenderName varchar(100),@SenderAddress varchar(100),@RecipientName varchar(100),@RecipientAddress varchar(100),@Subject varchar(200),@Body varchar(8000),@MailServer varchar(100) = 'smtp.mail.tspl.com' AS SET nocount on declare @oMail int --Object referencedeclare @resultcode int EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT if @resultcode = 0BEGINEXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserverEXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderNameEXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @SubjectEXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL EXEC sp_OADestroy @oMailEND SET nocount offexec sp_smtpmail 'ramana','venkataramanaiah.uppalapati@tspl.com','rajan','ramana.u@gmail.com','test','This is a sample message' when i execute the SP by giving the following input parameters like exec <spname> 'rajan', 'rajan.crp@tspl.com', 'venkat', 'venkat@tspl.com', 'test', 'Test Message' It ran successfully but the recepient venkat@tspl.com did'nt receive any mail from rajan.crp@tspl.com I used the following commands for configuring OLE Automation Procedures exec sp_configure 'Show
Hi all, In my web application, I need to send reminders to a list of persons in that database to fill out a form , once in every month. Suppose a person have not filled the form, then agaian afetr 15 days need to send a reminder only to him . And if he doesnt fill it again I need to sned another reminder after 10 days . ... In one word .. i need to snooze that person who have not complted the form continously and the end of month, even if doesn't complete, I need to tell him tto contact the Admin. How should I do it in my application. ? How to schedule a task periodically??? Please Help. Your Help will be very much Appreciated. Thanks in advance.
I have developed a B2B Application which is successfully running, now the problem is that my client says that he need a process which sends report of total orders on daily bases.
I am using SQL Server and think that SQL Mail is a good idea, I can find many articles about which needs Outlook for that which is much difficult on the web server. Remember I need to send email only not to receive.
I am having a problem with SqlServer 7.0 sending mail when the sqlserver account is not logged into the machine and mail is open. If I log off of the box and an alert sends mail it just sits in the outbox of the mail client until I log in. Is this the way it is supposed to be or am I doing something wrong. I am using the Inbox as my client.
The following SP adds a record to a table. I want to then send an email from within this SP to notify someone that their login is blocked. I can get their email address easily with a simple select but how does the xp_sendmail procedure actually fit in ?? I've tried adding it in just before the raiserror part but it doesn't work ? The raiserror part is used to trigger an alert to notify me but i need to also notify the client. Can anyone see what i am doing wrong please. Many thanks.
I want to send an email to all the addresses that are contained in a table. I have tried using xp_sendmail, but I just get an error saying that xp_sendmail cannot be found. Any ideas???
Ok I am going to try and explain this best as possible. I have a report that gets everyones name who has not filled in attendance for a class. what they want is when the report is ran an email gets sent out. so I have written the sql to create a temp table that will get the distinct email.... so lets say the table consists of
My code below works fine when run from my pc (changed all the values forobvious reasons). The code is placed inside a DTS task via VBS scripting.But when I try to run directly from the server where sqlserver is installed,the script fails.I have SMTP running, but there is no outlook installed.Can someone please advise what I am missing.ThanksBobSet objEmail = CreateObject("CDO.Message")objEmail.From = "send@test.com"objEmail.To = "receive@test.com"objEmail.Subject = "TEST SUBJECT"objEmail.AddAttachment "\server est.csv"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "SERVER_NAME"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "userpwd"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25objEmail.Configuration.Fields.UpdateobjEmail.Sendset objEmail = nothing
I am using SQL Server 2005. I have configured my email on Database Mail. when I am trying to send email using xp_sendmail I am getting problem error as:
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 ?
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?
Hi I am using asp.net framwork 2 with sql 2005 express edition. Can anyone please provide me any sample of sending automated emails (like welcoming people when they register to my website, or if their personal details have been updated....). or any e-book/website ref will do. I am not very expert in sql/db and smtp (or other email) stuffs. So please help me. With thanks
Hello, Let's say I created some tables in SQL server using the designer. Tables -> Right click -> New Table ->... How would I send someone else the script for creating that table? Or how would I send somebody else the tables? Thanks.
Hello all, I've run into a situation which I'm not sure which option to use. I have a web-based app that needs to send an email to a user once an auction has ended (the user being the winning bidder). The backend of the app is SQL 2005. I'm trying to figure out how to get this to work but I'm not sure if I should be using Database Mail or if I should be using a windows service on the web server that pings the database to check if the auction has ended and if the user has been notified. Can anyone shed some light as to which way I should be doing this? Additionally, I'm planning on formatting the style of the mail message so it'll need to be in HTML format and not plain text. Any help is greatly appreciated. Regards, Axel
I want to send data to a excel spreadsheet from SQL server 7. I know that I can create a odbc dsn which points to a table or a view on SQL Server. That works well. Is there any way to point to a stored procedure. I want to pull data to a spreadsheet based on a stored procedure.
I need help regarding how to send mails through SQL server. Let me give you some more details: I am having a table where in all the e-mail id's are stored so i need to write a stored procedure that will pick up the email id from the table and it has to send the mail once you run the stored procedure. Any help in this regard will really help...pls help me out...
If SQL is restarted for any reason, I need SQL server to send emails to others letting them know that the server is has restarted once it comes back up.
Is there an Error # that I can build an Alert on? Ideas???
I have few DTS package schedule on my SQL Server 7. When the DTS fail, it's supposed to send an e-mail to me. But, for an unknown reason, the server send me some e-mail to report the failure of the DTS and suddenly...nothing. The server stop sending e-mail to report the failure of the DTS package. I decided to restart the Server and... i receive over 300 e-mails that i was supposed to receive 3 week ago about the failure of the DTS package. It's not the first time that this problem occur and everytime i need to restart the server, now i am tired of that.
Does anyone know how to send emails from within a trigger without the use of a mail server. I know that xp_sendmail can be used if a mail server, such as MS Exchange Server is available and SQL Server is set up as an e-mail client. However is there a way to send mail to the outside world without the use of a mail server? (Perhaps there is a third party product or extended stored procedure that can give me this ability?)
I would like to send email from Sqlserver 7.0, and I don't have a MS exchange server(I have a smtp server). Do I need to have MS exchange server and if not, how can I get around this? Can Outlook express be of any use?
I've created a "data warehouse" for the Access users in my area. I take data from SQL Server, and write it to a network access DB using DTS. I don't want to link to the tables, I really need to put the data in an access format. The problem I'm having is that I'm appending the data and not overwriting it. Is it possible to truncate those table before importing? Do I have to use ActiveX Script to accomplish this?
I'm new to programming and to databases so apologies if this sounds like a stupid question.
I have a html page with a simple javascript function that uses the href mailto tag to send an email to a normal email address. I was wondering if it would be posssible to send the email directly to an SQL Server database, without having to pass through an intermediate parsing app. It would mean assigning an email address to the database. Is this possible with SQL Server? I know there would still be problems with parsing the email content to the appropriate fields, but would the sending of the email to the database in itself be possible?
I haven't much experience using databases and I don't currently have access to the SQL Server Express database that will be used to store the email content, so I'm not sure how difficult an operation this would be. I've looked on the internet but I haven't been able to find anything. I was hoping someone here might have done something similar, either with SQL Server or with any other database.
DBMS = SQL server 2005Web programming language = ASP.NETHiI have database 1 sat on server 1.I would like to move selected tuples from specific tables into database 2 sat on server 2.What do i need to research to try and find a method of doing this?Is the correct approach to move the selected data into a 'recordset' and if so how can i send this record set to database 2?many thanksBil
