Skip to main content

Posts

How to grant sendmail permission to sql server user?

Create a mail profile first using 1. Management - > Database Mail object 2. Create the desired user under Security -> Logins object Run the following permission script use msdb exec sp_addrolemember 'DatabaseMailUserRole' , 'sqlUser' EXECUTE msdb . dbo . sysmail_add_principalprofile_sp @ principal_name = 'sqlUser' , @ profile_name = 'sqlMailProfileName' , @ is_default = 0
Recent posts

Script SQL Mail Profiles when migrating databases

The following script obtains all the mail profiles, which could be used to recreate the settings on another server or test server. . USE msdb GO Declare @TheResults varchar(max),         @vbCrLf CHAR(2) SET @vbCrLf = CHAR(13) + CHAR(10) SET @TheResults = ' use master go sp_configure ''show advanced options'',1 go reconfigure with override go sp_configure ''Database Mail XPs'',1 --go --sp_configure ''SQL Mail XPs'',0 go reconfigure go ' SELECT @TheResults = @TheResults  + ' --################################################################################################# -- BEGIN Mail Settings ' + p.name + ' --################################################################################################# IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')   BEGIN     --CREATE Profile [' + p.name + 

Creating a Link Server to MYSQL from MSSQL

There might be situations where you need to apply CRUD  operations ( Create,   Read,   Update,   Delete ) on data residing in a MYSQL server database from SQL server. In order to do this you will need to create a link service from SQL server to MYSQL. The Steps are outlined below: Steps: 1. Install the MYSQL ODBC driver on the server with the MSSQL server installation. 2. Enter details for the MYSQL ODBC settings. 3. Create a link service to MYSQL using SSMS ( SQL Server Management Studio) 4. You will need an user account on the mysql server which can perform CRUD operations  along with enable remote connections to the MYSQL installation. (See Troubleshooting steps below) 4. Test the connection. Install MYSQL ODBC Driver  Download   (http://dev.mysql.com/downloads/connector/odbc/) and install the latest MYSQL ODBC driver from the MYSQL server site. Select the version which corresponds to your installation of MSSQL usually the 64bit version. Note This needs to be done on the server wi

How to pass a data table into a stored procedure as a Table Value Parameter (TVP) in SQL server 2008 and above.

In some instances there might be a need to manipulate an entire data set rather than passing individual values as variables into a database. To do this I explain how to start by creating the proc with the parameters then obtaining the inserted identity values from the insert statement to update a related table. Note the special table inserted which is an SQL table which stores the values of an insert just before this hits your table. Refer MSDN on inserted and deleted Tables 1.       To start with define the table value parameter under your database ->programmability->Types-> User-Defined Table Types 2.       Right click on the User Defined Table Types Create a data table definition of the set needed to be passed. Clicking on CTRL-Shift-M would bring up the Dialog to create them visually. The script should look like the following -- ================================ -- Create User-defined Table Type -- ================================ USE AdventureWorks G

Growing Log file issues - How to shrink a SQL server Log and data file.

Sometimes it is necessary to do this via a script as the UI does not work for reasons such as the DB is being in used. This method sets the recover mode and then shrinks the log and data file. Before doing this backup your databases using Management studio Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.) Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.) Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed: Use [Mydatabase] ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE DBCC SHRINKFILE(<log_file_name_Log>) ALTER DATABASE [mydatabase] SET RECOVERY FULL   The following screen shows the log file before the shrink. Log file after shrink Points of interest Transaction logs

Troubleshooting Database Mail: General steps

SQL Server 2008 R2 Other Versions SQL Server "Denali" SQL Server 2008 SQL Server 2005 Troubleshooting Database Mail involves checking the following general areas of the Database Mail system. These procedures are presented in a logical order, but can be evaluated in any order. To determine if Database Mail is enabled In SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code: Copy sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO In the results pane, confirm that the run_value for Database Mail XPs is set to 1 . If the run_value is not 1 , Database Mail is not enabled. Database Mail is not automatically enabled to reduce the number of features available for attack by a malicious user. For more information, see Understanding Surface Area Configuration . If you decide that it is appropriate to enable Database Mail, execute the fo

Method of grouping strings with XML

The following are some fine articles I found on  Brads blog page. http://bradsruminations.blogspot.com.au/2009/10/making-list-and-checking-it-twice.html In my previous blog entry , I described in detail how you could use the FOR XML clause to create a column with a comma-separated list of entries acquired from rows in a table somewhere. In this article, we will do the opposite… We will take a comma-separated list and “shred” the individual items into rows. Let’s start by creating and populating a test table: create table #CSVLists ( ID int , Description varchar ( 30 ) , CSVList varchar ( 200 ) ) insert #CSVLists select 1 , 'Stooges' , 'Moe,Larry,Curly' union all select 2 , 'Castaways' , 'Gilligan,Skipper,Thurston,Lovey,' + 'Ginger,Professor,MaryAnne' union all select 3 , 'Monkees' , 'Davy,Micky,Peter,Mike' (Okay, I know you Gilligan’s Island