Hello, I have a table in SQL Server that is used to track appointments. Is
there some way to take this data and send an email to user with the
appointment, when the user clicks on the email it would create the event in
the user or public calendar.
Thanks
Hi,
I need a help from you all on Sending Email on some specific event from SQL SERVER 2005. Any Idea how Do I proceed?
Any help will be appreciated.....
Thanks,
Rahul Jha
Hi!
Just set up my first Service Broker test. Wanted to send an Event Notification from one instance to another. Finally, the message arrives but profiler shows that the message is still being continously resent (and rejected as being duplicate). What am I doing wrong?
With best regards,
Artus
--
/* Local Event Source.sql */
USE master
GO
CREATE DATABASE NotificationDB
GO
ALTER DATABASE NotificationDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5554
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO
USE NotificationDB
GO
CREATE EVENT NOTIFICATION TestEN
ON DATABASE
FOR CREATE_TABLE
TO SERVICE 'ExpressService', 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6'
GO
CREATE ROUTE ExpressRoute
WITH SERVICE_NAME = 'ExpressService' ,
BROKER_INSTANCE = 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6',
ADDRESS = 'TCP://localhost:5555'
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
USE master
GO
SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
GO
/* Remote Service.sql */
USE master
GO
CREATE DATABASE Test
GO
ALTER DATABASE Test SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5555
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO
USE Test
GO
CREATE QUEUE ExpressQueue
WITH STATUS = ON
GO
CREATE SERVICE ExpressService
ON QUEUE ExpressQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
GRANT SEND ON SERVICE::ExpressService
TO [public]
GO
CREATE ROUTE ExpressServiceRoute
WITH SERVICE_NAME = 'ExpressService',
BROKER_INSTANCE = 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6',
ADDRESS = 'LOCAL'
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
GO
SELECT * FROM sys.endpoints
GO
Recently we migrated our environment to 2012.
We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.
Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.
I have gone through so many websites but i never find.
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM
Log SQL Server (Current - 10/25/2011 10:55:00 AM)
Source spid
Message
Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file.
The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30
WHERE targetinstance isa 'CIM_DataFile'
AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message.
However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup)
We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers.
We were having
Event Type: Failure Audit
Event Source: Security
Event Category: Object AccessEvent ID: 560
Date: 5/23/2007
Time: 6:27:15 AM
User: domainuser
Computer: MACHINENAME
Description:
Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,1623975729}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: Domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: User
Client Domain: Domain
Client Logon ID: (0x0,0x6097C608)
Accesses: READ_CONTROL
Connect to service controller
Enumerate services
Query service database lock state
Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015
Applied the following fix
http://support.microsoft.com/kb/907460/
Now we are getting
Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: user
Client Domain: domain
Client Logon ID: (0x0,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
David
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...
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?)
Many thanks for any response.
Hi
How can i send sms to mobile phone from sql server 2005.
Is it possible!
Regards
Js.Reddy
Hi,
i want to send Mail from sql server.which means that when i execute an query a mail will send to mentioned e-mail.is it any predefined function is there?.if,yes,can u explain Detail.
Regards
Umapathy
HiTrying to send email from sql server seems a very hard task, can iteven be done without using exchange? all examples I can find relies onexchange but I would rather send it to any SMTP server since we do notuse exchange.rgdsMatt
View 2 Replies View RelatedHi,I am having some trouble with my ASP page sending a file to a SQL Server 2005 database running on another machine. An exception is generated as follows:A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) It seems to work ok with smaller files, but when I attempt to upload a 11MB file, it gives me the above exception. I upped the httpRuntime maxRequestLength in the web.config to 2048. Any Ideas? Here is my code:1 if (FileUploader.HasFile)
2 {
3
4 // call the stored proc
5 SqlConnection conn = new SqlConnection();
6 conn.ConnectionString = "Password=password;Persist Security Info=True;User ID=MyUser;Initial Catalog=MyDb;Data Source=MySqlServerMachine;Connect Timeout=300";
7 conn.Open();
8
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = conn;
11 cmd.CommandType = CommandType.StoredProcedure;
12 cmd.CommandText = "spAddTestBinary";
13
14 SqlParameter param = new SqlParameter();
15 param.ParameterName = "@binaryParam";
16 param.SqlDbType = SqlDbType.VarBinary;
17 param.Direction = ParameterDirection.Input;
18 param.Value = FileUploader.FileBytes;
19 cmd.Parameters.Add(param);
20
21
22 cmd.ExecuteNonQuery();
23
24
25 conn.Close();
26 }
Here is my table:1 BinaryTable(
2 [id] [int] IDENTITY(1,1) NOT NULL,
3 [myBinary] [varbinary](max) NULL,
4 CONSTRAINT [PK_BinaryTable] PRIMARY KEY CLUSTERED
5 (
6 [id] ASC
7 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
8 ) ON [PRIMARY]
My stored proc:1 ALTER PROCEDURE [dbo].[spAddTestBinary]
2 -- Add the parameters for the stored procedure here
3 @binaryParam varbinary(MAX)
4 AS
5 BEGIN
6 -- SET NOCOUNT ON added to prevent extra result sets from
7 -- interfering with SELECT statements.
8 SET NOCOUNT ON;
9
10 -- Insert statements for procedure here
11 Insert into BinaryTable
12 (myBinary)
13 Values
14 (
15 @binaryParam
16 )
17 END
Hello,Does anyone know how I can set SQL Server up so that after it hascompleted a backup sucessfully it will send the completion report toan e-mail address via SMTP.Unfortunatly we cannot install MS OUTLOOK Express or similar.Is this possible?Many thanks in advance,Allan Martin
View 2 Replies View RelatedHello Friends
I want to send mail using Stored Procedure using SQL Server 2000 using windows smtp component. I don't want third party component.
can any body send Stored Procedure.
I have a need to asynchronously send a short message to an application over
tcp/ip after the user makes a selection from a web browser.
We are developing a reservation system where the availability to users in
close physical proximity is indicated by lights at a kiosk. We want the user
to be able to make a reservation from a web browser over the internet, (which
checks the sql server db for availibility), and when the reservation is made,
the appropriate lights at the kiosk indicate it is unavailable.
I can think of three choices, but am unsure which is best:
1. Poll the server to look for the event. I don't like this since the
application will be connected over a dial up internet connection, and I want
to keep traffic to a minimum, although this seems the easiest to build.
2. Have the users web browser be redirected to the application's ip address
directly. Here I am not sure which message protocol to use. The kiosk
application will be a .net 2.0 windows form app, but not running on a server
per se, but windows xp pro.
3. Have the database relay a message to the kiosk. I like this way the best,
since the user does not need to know the ip address of the kiosk, and the
kiosk does not need to be open from all ip addresses for security reasons. I
am not which way to do this either. I have read a little about hosting web
services using http.sys, but we probably want a shared hosting plan due to
costs at this point, and they are restrictive about turning on the clr inside
the db. Is there a different way to send a short message?
I apologize in advance if this is the wrong forum.
Hi,
I have a need to shoot and automated mail at regular intervals to clients. I got the following code for sending email from SQL Server but get an error.
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) = 'localhost'
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
EXEC sp_OADestroy @oMail
END
SET nocount off
The error is
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Can any one help me please
Doe anyone know how to do this. I keep getting an error remote path missing "/" but it doesn't contain a "/".
Mike
First, yes I know that DB Library is depreciated. Unfortunately it isn't an option for us to make a huge change like that right now.
Now, on to the problem. We have a few different C applications that access SQL Server via db-lib. On SQL 6.5, and SQL2000 we had no issues to speak of. Since upgrading to SQL2005 (Enterprise Edition, 32 bit), we have had a horrible time with receiving an error that says "Error msg: Possible network error: Error in sending out-of-band data to SQL Server. General network error.". It appears to happen at somewhat random times during the applications run.
Search for information on "Error msg: Possible network error: Error in sending out-of-band data to SQL Server. General network error." has provided next to nothing, so I turn to you. Any ideas?
Thanks,
Matt
Hai,
I am working on the DTS Package in SQL Server 2005 , actaully the DTA Packages Use OutLook to Send the email to the Mailing List , now that Out Look Stuff is not working Fine , soo I came to know that we can use Sp_send_dbMail stored Procedure insteds of Outlook , i am trying to do that my Email contains the attaachment to the Text file from the shared Folder.
I am not Getting the Correct Help when i am trying .
So can some one Helpe Providind Some examples and suggetions .
Thanks
I have below code to send email in HTML table format with store procedure. from my
Database = "CreditControl"
Table = "Testing$"
and code as below
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)
[code]....
I have a report that gets a Customer_Number parameter and sends a mail with that customer's data.I want my users to be able to get this report's results by sending an email to a certain email address with a customer number in the topic.
View 1 Replies View RelatedHi,
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I am using a direct connection from my Gridview to perform a huge database search. the stored procedure will take 10- 60 seconds to execute the query.
What the problem i am facing is when the sp takes long time (more than 1 minutes), then when i try any other operation like calling another sp frm another page, i am getting the above specified error.
Anybody can tell why is it happening?
here my code snippet goes
<asp:GridView ID="searchGV" runat="server" AllowPaging="True" AllowSorting="True"
BorderColor="#999999" BorderStyle="None" CellPadding="3" Width="100%" AutoGenerateColumns="False" DataSourceID="searchObj" DataKeyNames="Recordnumber" OnDataBound="searchGV_DataBound" OnPageIndexChanged="searchGV_PageIndexChanged">
<RowStyle CssClass="gridRow" />
<PagerStyle HorizontalAlign="Center" BackColor="#999999" ForeColor="Black" />
<HeaderStyle CssClass="gridHeader" />
<AlternatingRowStyle CssClass="gridAlternateRow" />
<Columns>
<asp:TemplateField HeaderText="All">
<HeaderTemplate>
<asp:CheckBox ID="chkid" AutoPostBack="true" OnCheckedChanged ="OnCheckChangedEvent" runat="server" />
</HeaderTemplate>
<EditItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server"/>
</ItemTemplate>
<ControlStyle Height="18px" Width="15px" />
<ItemStyle Width="5px" />
</asp:TemplateField>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title">
<ItemStyle Width="150px" />
</asp:BoundField>
<asp:BoundField DataField="Artist" HeaderText="Artist(s)" SortExpression="Artist"/>
<asp:BoundField DataField="Album" HeaderText="Album" SortExpression="Album"/>
<asp:BoundField DataField="Writer" HeaderText="Writer(s)" SortExpression="Writer" ><ItemStyle Width="150px" /></asp:BoundField>
<asp:BoundField DataField="ISRC" HeaderText="ISRC" SortExpression="ISRC" />
<asp:BoundField DataField="UPC" HeaderText="UPC" SortExpression="UPC"/>
<asp:BoundField DataField="PLineLabel" HeaderText="P-Line Label" SortExpression="PLineLabel" ><ItemStyle Width="100px" /></asp:BoundField>
<asp:BoundField DataField="DistLabel" HeaderText="Distribution Label" SortExpression="DistLabel" >
<ItemStyle Width="100px" />
</asp:BoundField>
<asp:BoundField DataField="PublishInfo" HeaderText="Publisher(s)" SortExpression="PublishInfo" />
<asp:BoundField DataField="Recordnumber" HeaderText="Record Number" SortExpression="Recordnumber" />
<%-- <asp:BoundField DataField="UID" HeaderText="UID" Visible="false" />--%>
</Columns>
</asp:GridView>
with adapter
<asp:SqlDataSource ID="searchObj" runat="server" SelectCommand="gsp_titleSearchResult" SelectCommandType="StoredProcedure" ConnectionString="<%$ ConnectionStrings:ConnStr %>">
<SelectParameters>
<asp:ControlParameter ControlID="HuserID" PropertyName="Value" Name="Userid" Type="String" Size="50" DefaultValue="" />
<asp:ControlParameter ControlID="Htitle" PropertyName="Value" Name="title" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hartist" PropertyName="Value" Name="artist" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Halbum" PropertyName="Value" Name="album" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hwriter" PropertyName="Value" Name="writer" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hisrc" PropertyName="Value" Name="isrc" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hupc" PropertyName="Value" Name="upc" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hpline" PropertyName="Value" Name="pline" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hdist" PropertyName="Value" Name="distlabel" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hpublish" PropertyName="Value" Name="publisher" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="ReleaseID" PropertyName="Value" Name="Releaseid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="RecordLabelID" PropertyName="Value" Name="recordlabelid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="PublisherID" PropertyName="Value" Name="Publisherid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="ArtistID" PropertyName="Value" Name="Artistid" Type="Int32" Size="50" DefaultValue="0" />
</SelectParameters>
</asp:SqlDataSource>
Advance thanks
Anvar Sadath
More and more often, I am seeing this error message in RS subscriptions. I would assume it means the IIS server can't connect to the corporate exchange server. However I was wondering if there is some timeout setting/configuration in RS or the IIS server (or any other kind of setting) I can change to help alleviate this problem.
Has anyone else experienced this? Or know how to fix it?
Thanks,
Jeff
UPDATE: After looking closer at the subscriptions, I have noticed that subscriptions set up by a RS Admin (who is also a server admin) seem to be working fine. It is the subscriptions that are set up by ordinary users that are failing...
Hi all,
I am new to SQL 2005. I have one issue and it is as follows:
I write a simple query and run it; it works fine.
Now I break the network connection and run the query; in this case it runs fine.
But when I reconnect the network and run the query, then I get an error as follows:
"A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - An unexpected network error occurred.) "
Every thing is local here. So how the network issue comes here?
I've tried to search on the web for a solution for this error but i didn't find any working solutions for this problem. We have 2 servers. The first one is the server we use to develop our ASP.NET 2 application. On this server we don't have this error. On the other server (use by our client) we have this error sometime and i don't know why. Both servers have the same configuration and both application have the same web.config file.
I've tried to add a try-catch and retry the query when the error occured. This seems to be working but we don't want to have to change all our connections and since we don't have this problem on the other server we want to find the source of the problem.
Any idea ?
Thanks !
Hi, I want to do some data transfer from a network share and also runsome stored procedures.How can I get them to run at a designated time, automatically?
View 1 Replies View RelatedEvent Id: 8, Source: SQLCTR60
Type: error, Catagory: None,
DB-Library error - unexpected EOF from SQL server. Conection broken.
I am trying to set up the email notification system in SQL server 2000, but im having some problems. Mainly, im having problems with setting up an email account in the first place (there is a dedicated mail box, but the one im working with is a different box altogether).
If anyone could help fathom this out from start to finish i would be really grateful.
How to verify whether windows event log is enable in MSSQL 2000 or not through SQL Client (or sql query)
View 9 Replies View RelatedI am trying to create subscription on report manager.
Getting this error: "Failure sending mail: The transport failed to connect to the server. ".
how to resolve this..