Sending SQL 2005 Dbmail When Using Application Role
I am attempting to use dbmail from an application that logs in to my database using an application role. Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it: I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights.
Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail. But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission." When I run my spSendEmail stored procedure from the calling database, I get the same error.
View Complete Forum Thread with Replies
Related Forum Messages:
Application Role And SQL Express (2005)
Hello, Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)? Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005 Thanks John
View Replies !
Web Application And Role Management In Sql Server 2005
Hi every body I'm developing a web application and i like to use the sql server 2005 role management features istead of developing a role management package in my program, I can do it on my tables and othe database items but I have no idea about using database access rights in my web pages to permit some one viewing or updating a web form... Is there any system table or system stored procedure showing access rights in my data base? or is there another idea to do this?? by Thanks Javaneh
View Replies !
SQL Server 2005 Application Role's Problem
Hi all. I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005. My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables: 1) Table A 2) Table B 3) Table C The USER login is a restricted user which has only SELECT permission to Table A, B and C. I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime". Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A. I have tried for almost 2 days but am still clueless. Any ideas? Thank you so much.
View Replies !
SQL Server 2000 And 2005 - Application Role - Sp_setapprole
Hi All, Is there any limitation for setting password to an approle (like it should be 8 character long, should not start with numbers) ? If so, what are those limitations ? Does the same is applicable for sp_setapprole which uses the same password ? please confirm, for both SQL Server 2000 and 2005 versions. thanks in advance, Regards, Kailai
View Replies !
Uploading SQL Express To SQL Server 2005 - Role's Causes Application Error With SSE Provider
I am very frustrated. Everything works on the local host but when I upload to server I can login to the admin role I created, but when I try to access pages that have role priveleges I get the following error: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. The ASPNETDB.MDF database was uploaded using the Database Publishing Wizard. Please help!
View Replies !
Application Role, App Role
Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) for the given connection string Dim connstring As String = "Data Source=Northwind;Initial Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;" Dim cmd As SqlCommand Dim param As SqlParameter Dim cookie As Byte() Dim cn As New SqlConnection(connstring) If (cn.State = ConnectionState.Closed) Then cn.Open() End If Please help.. Thanking you, Nirmala
View Replies !
Sql 2005 Trigger Insert And Dbmail
I have a stored proc that inserts records into a table, gets the identity, and uses that identity on a .net webpage. The page retrieves that identity key and processes some stuff on the page. Everything was working fine until I tried to enable sql mail with triggers. I wanted to double-check some stuff, so I requested an sql email be sent whenever an insert occurs on the table above. Here's what I think is happening --- please correct and/or help me out with a work around? I use the stored proc to insert a record. I select the @@identity. The trigger fires but uses select to retrieve the latest insert - thereby replacing the @@identity number returned to the page? If this is true, could I do something like the following in the original stored proc? Is this a good idea or bad idea? BEGIN Declare @myID as int, @myBody1 as varChar(200) Set @myID=0 INSERT INTO table (fields) VALUES (@PID, more stuff); Set @myID = SELECT @@IDENTITY As [Identity]; If @myID<>0 Begin Set @body1='<br />pid=' + more stuff..... Exec msdb.dbo.sp_send_dbmail @profile_name='profileName', @recipients='email@email.com', @subject='Temp History Insert', @body=@body1, @body_format= 'HTML' ; End END
View Replies !
Application Role
I am confused . What is considered an application and how SQL would know ? If I have a web site accessing SQL VIA IIS will SQL Server treat it as an application ? How about MS Excel ? Also , If I was to use the application of Power builder , using app role , how do control which user can use the app ? Thanks
View Replies !
Application Role
With reference to Mr. Eric Charron's article about Implementing Application Roles in SQL7, I have used this tips in my application. But I am facing the following problems. I am looking for some solution from this User's Group. Problem: I am using application (VB6/SQL7) role. I have number of reports. To display reports I follow the following steps. 1) Open Connection 2) Set application role 3) Execute procedure for the report 4) Close the connection Since I have no. of reports, I go through the aboue steps no. of times. For first 2 times it works fine but 3rd time, it gives error ie. application role not set properly. RM Joseph
View Replies !
Application Role
hi all ! first time i created application role with read and write permissions , once application accessing automatically it assigns rd and wr permissions to that users or previously wht permissions are there in logins will it be reflected to that application role. once we create the appliation role , is there any changes we have to do in coding part pls clear my doubt. regards manoj
View Replies !
Troubleshooting A New Application Role
Hi all, This one is a real X FIle, just without Mulder, Scully or the Lone Gunmen! I have a database, to which access must be restricted via a sole application. So, I have to use an application role. I go in the database and run these statements to add and activate the roles, respectively; Exec sp_addapprole 'Sirius', 'password' (The system confirms the role is created.) Exec sp_setapprole 'Sirius', 'password' 'odbc' (The system confirms the role is activated.) Right, now I should not be able to connect using anything but this role, agreed? But here's where things go wrong. I can then successfully connect from another computer by using MS query from Excel, from a login that is not even a member of the Public Role! I tried again, started and stopped the Server/DTS/Agent services and dropped the old role after each successful login before recreating it. I've checked my syntax exhaustively. I must be doing something wrong, or overlooking something, otherwise MS has a major security problem! (Just hope the Cancer Man doesn't find out!!) Thanks in advance everyone, Jaishel.
View Replies !
Security For Application Role
We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data. I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database? Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator? Thanks
View Replies !
Application Role And SSRS
Hi dear reader I made an application that uses a Sql Server 2005 Express DataBase. In the database I made a application role. When the user logs into my application I run this procedure: If Not sqlConnectionCR Is Nothing Then If Not sqlConnectionCR.State = ConnectionState.Open Then sqlConnectionCR.Open() SqlConnection.ClearAllPools() ConsultasSqlCommand = New SqlCommand ConsultasSqlCommand.CommandType = CommandType.Text ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap" ConsultasSqlCommand.Connection = sqlConnectionCR ConsultasSqlCommand.ExecuteNonQuery() End If Else.... I understand that this procedure connects to my sqlserver database as my application role Ok, so far no problems in reading and manipulating data. The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access.... I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role). So, my cuestion is: How can I tell Report Server to use the application role to display reports? Thank you for your time and help. Giber
View Replies !
BULK INSERT And APPLICATION ROLE
I want to add bulkadmin permission to my applicatio role. Is it aposible.My windows account havo only public permission on database.I'm using application roleEXEC sp_approlepassword 'MyRole', 'password';Therefore I want to BULK some data with BULK INSERT command.Error is:The current user is not the database or object owner of table'tablename'. Cannot perform SET operation.Thanks in advance.
View Replies !
Use SQL 7.0 Application Role With Crystal 8.0 Crpe32.dll
using vb6 and crystal global32.bas to make function calls to the crpe32.dll and there doesn't seem to be any calls to pass the application role id with password. ap roles must be passed at the ad hoc level, which means i have to execute the system stored procedure for the ap role thru crystals connection to the sql server. HELP....seagate has tried to help, but want me to use odbc dsn, which is exactly what i'm trying to avoid. oledb provider works like a champ, except for app roles with crystal.
View Replies !
Connecting To A Database From VB Using An Application Role
I know how to create an Application role in SQL server 7. Now how do I connect to the database from VB using that Application Role? I can't find anything about this topic anywhere. Is this the purpose of an Application role or am I way off? Thanks for the help Steven Abt StevenA@grsgroup.com
View Replies !
Create Database With Application Role...
Hi, I want my application to create database and I do the following things: 1)Create application role 2)Grant create database to application role 3)Activate application role 4)Create database and I get the answer: CREATE DATABASE permission denied in database 'master'.
View Replies !
Application Role Losing Connection?
Hi, I'm testing the use of application roles for security. The customer I work for has still a lot of ASP intranet applications running. We're migrating the databases to a SQL Server 2005 server. I've changed the connection string to a user without any permissions but to log on. After that I use an application role for permission to select different tables and to execute Stored Procedures. The first queries do execute but after that I get "Permission denied", like I haven't got the application role anymore. Any ideas? Adrian
View Replies !
Get Sql Err Message 15422 When Activating Application Role.
Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX). Developing a desktop application which calls a local instance of ".sqlexpress". This app needs to set data base options and add/del various table columns. When activating the application role, I get the following message: HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level. Anyone know what this message means? I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious. Thanks Gary
View Replies !
Login Failed For 'user' (Application Role)
I've created a database in SQL Express and I have a Windows form attempting to connect to it through SQL Authentication. Connection string: private string connString = @"Data Source=.sqlexpress;Initial Catalog=SQLTestDatabase;User ID=SearchAppRole; Password=password;"; The role I have added to the database is an Application Role. It has been added to the Database permissions with Grant checked for "Select" and "Authenticate". If I test this with query analyzer, it returns expected results (if I remove Grant from 'Select', it fails) sp_setapprole 'SearchAppRole', 'password' select * from recipe If I edit my connection string (for testing purposes) to use the sa account, the application can connect and run the Select statement: private string connString = @"Data Source=.sqlexpress;Initial Catalog=SQLTestDatabase;User ID=sa; Password=sa_password;"; However, I cannot get the application to successfully logon and run the select statement when using the user id and password of the Application Role. I get error: System.Data.SqlClient.SqlException: Login failed for user 'SearchAppRole'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj I can't find much information on Application Role...I just want one basic permission for the application as a whole. Any help is appreciated. Thanks.
View Replies !
Application Role How Query A View Whose Reference Table Is In Another Db?
Hi everybody. I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1 I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error: The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated. Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania
View Replies !
Can't Make Database Role A Member Of Another Database Role In 2005.
In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
View Replies !
One Distributor Is Re-sending Transactions Another Stops Sending Altogether.
I'm running SQL Server 2000 SP2 transactional replication.Periodically replication fails due to primary key errors. Oninvestigation I find that the offending transaction is attempting toinsert a duplicate of a recently replicated row (identical dataincluding the rowtimestamp, which is automatically generated in thepublishing database). My conclusion is that the distributor issending the same transaction twice. There is usually a large block oftransactions that are duplicates. Replication has been removed andreinstalled with no change. Any suggestions on what might be causingthis problem?Another distributor/publisher replicating to the same subscriber ishaving the opposite problem. When replication is first set up,everything works fine. Then, a few hours or days later, I notice thatno new transactions have been replicated for some time. Thedistribution agent appears to be running normally with no errors andno transactions are queued in the distribution database. Thesubscription and publication are still active but new data stored tothe published articles that should be replicated are ignored. Againany suggestions on how to proceed would be greatly appreciated.We have many other systems replicating with no problems, some to thesame subbscriber and built from the same image. I plan to re-installSQL Server on both of these systems when operations allow, but I wouldreally like to find the root cause of this problem.ThanksWalt Smith
View Replies !
DBMail
Is it possible to embed a image datatype into a EMail message using sp_Send_DBMail? For example, my query would select a saved print screen image held in a SQL table as datatype image. I would prefer not to attach this image but rather have it print in the message section. Thanks in advance.
View Replies !
DBMail
Hi, I am using SQL Server 2005 and need to mail. I used sp_send_dbmail for mailing but I am able send to one @recipeint and one @copy_recipient. How can I mail to many email addresses and/or group? Any help is appreciated. Thank you,
View Replies !
Sending Mails Through SQL Server 2005
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
View Replies !
Sending Data From SQL 2005 To And From C# Code
Hi Background: I am a post-graduate student developing a model of the way HIV/AIDS spreads in communities. The data sets I am using are large (eg census data - 50m records) and the manipulation is complex (eg deciding which individuals will begin a sexual partnership, and with whom). Problem: I need to process a table of data in code written in C# that will insert the results which are also a table of data into a table in the database. In the example set up below, a table with rows comprising an Integer and a Real is the input. The output is a table where each row also comprises an Integer and a Real. Each row of input, say (Inti, Reali) leads to Inti rows of output. The example below gives a simple example of this. The T-SQL code below sets up the problem, and includes simplest possible T-SQL that would lead to the requisite Output, so that you could compare your solution's code's with it: -- Set up tables to use in example CREATE TABLE InputTable ( Int1 INT NOT NULL, Real1 REAL NOT NULL ) GO CREATE TABLE OutputTable ( Int1 INT NOT NULL, Real1 REAL NOT NULL ) GO --Initialise the Input Table INSERT INTO InputTable (Int1, Real1) VALUES (3, 0.5) INSERT INTO InputTable (Int1, Real1) VALUES (5, 0.99) --Manually put Data into Output table --I do know how to code this in T-SQL using cursors and loops --I wish to code this part in CLR INSERT INTO OutputTable (Int1, Real1) VALUES (1, 0.5) INSERT INTO OutputTable (Int1, Real1) VALUES (2, 0.5) INSERT INTO OutputTable (Int1, Real1) VALUES (3, 0.5) INSERT INTO OutputTable (Int1, Real1) VALUES (1, 0.99) INSERT INTO OutputTable (Int1, Real1) VALUES (2, 0.99) INSERT INTO OutputTable (Int1, Real1) VALUES (3, 0.99) INSERT INTO OutputTable (Int1, Real1) VALUES (4, 0.99) INSERT INTO OutputTable (Int1, Real1) VALUES (5, 0.99) --This should show the output SELECT * FROM InputTable SELECT * FROM OutputTable --The output should be Int1Real1 3 0.5 5 0.99 Int1Real1 1 0.5 2 0.5 3 0.5 1 0.99 2 0.99 3 0.99 4 0.99 5 0.99 Additional Information: I would be grateful if your solution could indicate * whether the code to be written is a class, stored procedure or user-defined function * the answer could be included in entirity begining with "using system..." * example T-SQL that can be used to execute the code Current knowledge:I can write T-Sql. I have written C# user defined functions and stored procedures, assembled them and run them from within SQL-Server 2005. Thanks in advance. Greg
View Replies !
Sending Message With Sql Server 2005
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.
View Replies !
Dbmail Failing
W23K 64 bit SQL 2005 SP1 64 bit (clustered).Whenever I start mail with sysmail_start_sp it starts and then stopswith the following entries in sysmail_log.The read on the database failed. Reason: The error description is'Whitespace is not allowed at this location.'.It was working. This started happening after I dropped all my mailprofiles and accounts and recreated them with a different email address.I've now deleted everything using sysmail_delete_mailitems_sp. It stillfalls over straight away.Any guidance would be appreciated.*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
HTML And DBmail
Is anyone using a SP or Trigger to Create a HTML formated e-mail and send it useing DBmail? If so could I see a sample of the code? Jim Users <> Logic
View Replies !
Sending Sql 2005 Table Back To DB2 AS400
I am trying to send a file from SQL 2005 back to our AS400 DB2 db. First time trying to get this to work in SSIS. I have a couple fields that are datetime in SQL Server and see SQL server sees datetime fields from DB2 as string so I am converting them to Strings using derived columns. That part seems to work fine. Get no errors on that part. The conversions seem like they are working but it is faling when it tries to send it to as/400. I get the following error: any ideas on what is wrong? [OLE DB Destination [1569]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5029: Data mapping error on member DSSCNTL. ". An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5035: Data mapping error on member DSSCNTL. ". An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5035: Data mapping error on member DSSCNTL. ". I even created a sql table with same field sizes as one I am sending to AS400 and its sucessful there. So seem like it mght have somthing to do with sending to AS400 but not sure what. Any ideas? Stacy
View Replies !
Customizing The From Address When Sending Emails From SQL 2005
We have the need to be able to send emails from our SQL 2005 server with the from address field determined at run time from a table. What are my options? We are running a clustered SQL 2005 x64 standard edition on Win 2003 Enterprise x64. 1. Database Mail - with all the hoopla about how nice it is to have a proper SMTP mailer in SQL, it still seems to lack this functionailty. IMHO a HUGE miss. 2. sp_send_cdosysmail - We used this when we had a SQL 2000 on W2K and it worked just fine. On our current environment, the stored procedure works for a period of time and then suddenly and out of no where we see this message in its log files: CDO.Configuration.1 - A dynamic link library (DLL) initialization routine failed. sp_OAGetErrorInfo for sp_OASetPropery sendusing After which no emails are sent until I restart the SQL Server service. We have tried both the sendusing method of 1 (local pickup) as well as 2 (remote SMTP server) and both are exhibiting the same behavior. What other options do we have? Alex.
View Replies !
DBMail Permission Error
I am using DBMail to send an email notification at the completion of service broker process and have been getting the following security error: Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'. I have given the userID used to run the Service Broker, databasemailuserrole in msdb as per instruction in the msdb. I have also given it execute permission on the sp_send_dbmail but keep getting the error. The service broker is run using a sql login and the dbmail profile is set as public profile. Any suggestions would be much appreciated.
View Replies !
Config DBmail To Use MX Records?
We are having trouble configuring SQL Server 2005 to use MX records. Basically, we want to use DBmail to utilize multiple mail relays. The three mail relays are contained within internal DNS MX records. smtphost.xxx.com. IN MX 10 mailhost1.xxx.com. smtphost.xxx.com. IN MX 10 mailhost2.xxx.com. smtphost.xxx.com. IN MX 10 mailhost3.xxx.com. It doesn't seem to like using MX records? Any ideas? Thanks KraftR Tim.Kraft@Dowjones.com
View Replies !
SQL DBMAIL Performance Issues
Hi, I'd like to have some feedback from the experts on SQL 2005. The situations is the following. I have an application .net/SQL server 2000 that send notification mails using the SQL mail feature. It works fine for years. Sooner or later SQL 2000 will be out of service, so I decide to move my database to SQL 2005. When I'm trying to put in production with my web hosting provider www.webhost4life.com, they said that are not supporting SQL DBMAIL on any SQL 2005 32/64 bits because the extra workload will slow down SQL 2005 performance. My question is if somebody has experience of this situation in other installations or maybe is that provider who has some problems tuning the SQL servers.
View Replies !
Trigger Insert And Dbmail
I have a stored proc that inserts records into a table, gets the identity, and uses that identity on a .net webpage. The page retrieves that identity key and processes some stuff on the page. Everything was working fine until I tried to enable sql mail with triggers. I wanted to double-check some stuff, so I requested an sql email be sent whenever an insert occurs on the table above. Here's what I think is happening --- please correct and/or help me out with a work around? I use the stored proc to insert a record. I select the @@identity. The trigger fires but uses select to retrieve the latest insert - thereby replacing the @@identity number returned to the page? If this is true, could I do something like the following in the original stored proc? Is this a good idea or bad idea? BEGIN Declare @myID as int, @myBody1 as varChar(200) Set @myID=0 INSERT INTO table (fields) VALUES (@PID, more stuff); Set @myID = SELECT @@IDENTITY As [Identity]; If @myID<>0 Begin Set @body1='<br />pid=' + more stuff..... Exec msdb.dbo.sp_send_dbmail @profile_name='profileName', @recipients='email@email.com', @subject='Temp History Insert', @body=@body1, @body_format= 'HTML' ; End END
View Replies !
Sending Emails Throught DTS Packages In Sql Server 2005
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
View Replies !
Sending MIME Messages Using Database Mail In Sql 2005
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? Thanks in advance for any help
View Replies !
Problem With Service Broker And DBMail
Hi! I try to use Service Broker and DBMail together, but have some trouble with that. I need to create the queue with activation. And the stored procedure activated on this queue must send e-mail using DBmail. It's looks simple, but it doesn't work. There is my script to create objects, but don't forget create dbmail profile before use it. PS And replace my email by yours
View Replies !
Need Help With Permissions Error On Dbmail Trigger
Good morning, I'm stumped on this trigger error and I'm hoping someone can help. Here's the background: BACKGROUND Running SQLServer2005 on WinServer 2003 RC2. We use a SQL-based business application that has it's own alert system that uses database mail sucessfully, meaning the built-in emailing functions of the application work and can communicate with the database mail profile and send mail without error. PROBLEM I'm trying to write a trigger that will automatically send out an email alert after certain actions are performed in the business application. When I enable the trigger and it tries to run it fails with the following error: SQLDBCode: 229Alerts error: SQLSTATE = 4200Microsoft OLE DB Provider for SQL ServerEXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'... TROUBLESHOOTING I've seen several articles about DatabaseMailUserRole permissions and have made sure that all users, admin and even guest (for testing) are members of this role. Other than that our setup is pretty vanilla so I'm not sure what else to do. We only have one large dbase for the app and the fact that the internal emailing fuction works makes me think that the permissions for the msdb are already correct (although I could be wrong) This problem is driving me crazy so I thank you in advance for any suggestions!
View Replies !
SQL Server 2005 / Securityadmin Role / SSMS
Experiencing a little weirdness with SQL Server Management Studio...Have created a login, that has fixed server role "securityadmin".The login can create/drop logins, add/remove them as users indatabases, etc.The login can change another login's password, using SQLfrom a query window (e.g. alter login bob with password='abc')However, the login *cannot* change another login's password,using SSMS GUI (e.g. "Security", "Logins", double-click "Bob",change password, say "OK"). The error text is:Change password failed for Login 'Bob'. (Microsoft.SqlServer.Smo)Additional information:An exception occurred while executing a Transact-SQL statement orbatch.(Microsoft.SqlServer.ConnectionInfo)Cannot alter the login 'Bob', because it does not exist or you donot have permission.(Microsoft SQL Server, Error: 15151)Has anybody else experienced this?
View Replies !
|
TRACKER
