SQL 2012 :: Any Way To Use People Distribution List With DBMail?
Mar 13, 2015
I would like to replace mymail@mail.com by mypdl@mail.com , where mypdl is basically a people distribution list with 2 emails: my own email and my coworker and fellow DBA.
IF EXISTS
(
SELECT TOP 1 [PercentUsed]
FROM [BackupSize]
WHERE PercentUsed>=70 AND [drivename]='D:' -- D is the internal drive
ORDER BY [MetricDate] DESC
[Code] ...
But I think this won't work by design. Maybe I need to create an operator?
When I am trying to insert to data from SQL ssis package to SharePoint list people or group column I am getting below error.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (25) failed with error code 0x80131500 while processing input "Component Input" (34). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Can you please let me know if it is possible to use a distribution list created in Outlook for sending reports via the automated email delivery system within SSRS? or do I have to set up distribution lists within SSRS?.
I am writing a stored procedure that sends mail to different distribution lists depending on different variables. I am having trouble passing in a local variable into the @recipients parameter for xp_sendmail. How can I do this?
Hi friends, Though we can send mails to Individual addresses, I wonder what would be the Syntax to specify in the script task or Format to specify in a "To" Property of Send Mail task that I use to Send a mail to my Personal Distribution List (PDL).
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
Is there a way to move the Distribution database (that is currently on the publisher server) to a different server without having to reync the subscriptions.
I have been asked to create a report for one of our clients. The report is pretty basic but I am concerned about the overheads with my planned approach.The report is at a table and field grain to include values for:
* Min column value * Max column value * Number of discrete values * Number of populated values (not NULL)
My current plan is to have a cursor over a limited view of sys.tables and sys.columns that will run a dynamic SQL query to import the results into a table that I can then output.There must be a better way of doing this and I don't have access to any DQS services.
I just finished installing SQL Server 2012 with the SQL Server Replication feature component checked. On configuring transaction replication, i notice the distribution database wasn't there. I ran the below query
EXEC sp_get_distributor And the value for distribution_db_installed is 0 as shown below
installed distribution server distribution db installed is distribution publisher has remote distribution publisher 0NULL000
On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
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.
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?
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
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.
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
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.
I have attached some test data for you that has two temp tables "#worker" and "#worker_rate".
The issue is to find all workers who are sharing SAME SET of rate_codes.
I'm able to get the output as "workers sharing same rate_codes", but unfortunately I could not get the list of workers sharing same SET of rate_codes. Also definition of SAME SET is not defined.
I have two tables, a dates table and a values table. They are joined on the date column.The date table has a range, say from today as far as 20 days from now, incrementing by 1 day each row.The values table may have a row for a day, and may not. If the day has a value I want to display that value.If the day does not have a value in the values table I want to display the last known value.
I think this can be done with windowing functions in a set based manner but have not been able to work it out. I have done it procedurally but im not happy with that at all, and really want to see if this is possible in a set based manner.Below is some simplified code to allow testing with sample data.
create table DimDate ( DateCol date ) create table TotalsData ( DateCol date
Supposing there are three Columns: Countries, Provinces and States.Then when the user enters “USA” for country, it will bring up a list of states, if they enter “Canada” for country, it will bring up a list of provinces, if they enter another country, it will not bring up any list?
SET NOCOUNT ON; DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10)) INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8' SELECT * FROM @items
-- table with categories
SET NOCOUNT ON; DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10)) INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE' SELECT * FROM @categories
--table where categories are assigned to master categories
SET NOCOUNT ON; DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT) INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107 SELECT * FROM @master_categories
-- items-categories assignment table
SET NOCOUNT ON; DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT) INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11
[code]....
So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.In the above example it would be only the ITEM 1 (id=10) that is returned:
1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally
2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally
3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1
3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2
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
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
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!
I was just given an excel file with a list of 300 Stores.
I need to find out if these stores are selling our products and if they are , how many products they are selling.
One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.
Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours.
Is there a command at the CLI or in PowerShell that will list the components installed for SQL? I'm looking for something like the Feature List you can get from running the discovery report from Tools in the SQL Server Installation Center program option.
I'm running SQL Server 2012 on the CORE version of Windows Server 2008 R2 Enterprise.
I have a Games table in a SQL Server 2012 database with game results from our league, including home and away scores, dates, and teams. How can I query to list streaks - that is, wins or losses of 2 more games in a row throughout a season?
1. A few months ago, I received a request to deny delete to a group (Windows AD login) against 3 databases. So I issued a DENY DELETE to the relevant Windows login.
2. Now, someone is having trouble updating data. The issue seems to be that a trigger fires during the update. In the trigger the code tries to delete from a table in the previously delete-denied databases. So the update fails.
-- In retrospect, perhaps I should have used REVOKE - not sure
-- Also, I can't seem to find a listing of the DENY permissions - or rather, how to list what permissions are denied. I guess maybe that is a hard thing to do if the result of a DENY is to leave certain permission columns NULL (vs adding 'DENY'). But it would be useful for me to see what the current permission state is.
-- Finally, I tried to undo the DENY without success. Here is my attempt.
a. I tried GRANT DELETE... - no luck b. Then I saw an article explaining to use REVOKE, so I tried 'REVOKE DELETE' followed by 'GRANT DELETE' - still no luck.
