I found some problem using Union and Group By clause together, and I later realized that my technique of using these words together is not proper.
Most of the users are making the same mistakes when they deal with Union and Group By clause.
You can use like below:
SELECT x FROM (
SELECT x FROM table_A
UNION
SELECT x FROM table_B
)
GROUP BY x;
You can retrieve comma separated values from a database table using following type of SQL scripts
DECLARE @varList VARCHAR(1000)
SET @varList = SPACE(0)
SELECT @varList = @varList + ', ' + ContactName
FROM Customers
SELECT @varList
SELECT SUBSTRING(@varList, 3, 1000)
You can see that the statement “SELECT @varList” will return list like “ , Maria Anders, Ana Trujillo, Antonio Moreno, Thomas Hardy….etc ”
Here you can see the result will have extra comma at the start of the string result. To remove this I have added another select statement “SELECT SUBSTRING(@varList, 3, 1000)”. This will return string upto 1000 characters only, you can set this value as per your requirements.
After a very long time I am posting this stuff…
I have noticed that on day to day activity with MS SQL, one requires to split any string constant every now and then and so I have decided to make a post of it.
Please create below SQL function into your database and then you can use this to split any string constant with any separator you specified.
CREATE function [dbo].[fun_Split]
(
@String nvarchar(4000),
@Delimiter char(1)
) Returns @Results Table (Items nvarchar(4000))
As
Begin
Declare @Index int
Declare @Slice nvarchar(4000)
Select @Index = 1
If @String Is NULL Return
While @Index != 0
Begin
Select @Index = CharIndex(@Delimiter, @String)
If (@Index != 0)
Select @Slice = left(@String, @Index - 1)
else
Select @Slice = @String
Insert into @Results(Items) Values (@Slice)
Select @String = right(@String, Len(@String) - @Index)
If Len(@String) = 0 break
End
Return
End
You can use this function like example given below:
SELECT Items FROM dbo.fun_Split('1,2,3,4',',')
This will return a table with Column “Items” where you can find each comma separated values in different rows.
Happy Scripting!
I have two tables for e.g. tblStudents and tblSchools. Both are having different columns. For a reason I need to merge the two tables into one temporary table.
For example “tblStudents” contains the following design.
| StudentID | FirstName | LastName | Address | DOB |
Where as “tblSchools” contains the following design.
| SchoolID | Name | Address | IsSecodarySchool |
Now, you would like to merge this two table columns into one temporary table then you can use the following sql script.
SELECT * INTO #tbl_Temp
FROM tblStudent, tblSchool where 1>2
Here, I have mentioned the “where” clause because I wanted to copy only the columns and not the data. You can ignore this clause if you want to copy the entire data too.
You can add as many tables you want to merge by separating with comma (,) between the table names.
You can add specific columns from specific tables by using table alias and column name combination.
For example
SELECT tblStudent.FirstName,tblStudent.LastName,tblSchool.Name INTO #tbl_Temp
FROM tblStudent, tblSchool where 1>2
Happy Programming!
Today I came to know how to search for a specific keyword or any object like tables or columns of any tables used in a procedure. This is really useful when you have a bunch of procedures and you need to findout the procedure that have some specific tables, columns or even any keyword you wanted to search.
Here is the SQL Query that will be useful to get appropriate result set of stored procedures which contains your provided search keywords.
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%your keyword%'
I found this whole article from my email hosting provider as a solution, when I was stick with one issue of SQL Server of not sending any emails from stored procedures. I have submitted one ticket to the hosting provider pointing out this problem, and I received this below article link from the hosting side that I found very useful.
I have some stored procedures, from which I was sending emails to solve out my different purpose. However, I was unable to receive any emails though stored procedures were running successfully. Then I found that there were some settings that need to be done to allow stored procedure to send emails. I guess, you will also find this article useful.
The SQL Mail problems, that we faced in SQL Server 7.0 and 2000, are no more. SQL Server 2005 supports and uses SMTP email now and there is no longer a need to MAPI client to send email. In SQL Server 2005, the mail feature is called Database Mail. In this article, I am going to demonstrate step-by-step, with illustrations, how to configure Database Mail and send email from SQL Server.
Database Mail has four components.
1. Configuration Component
Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.
The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members of DatabaseMailUserRole in MSDB database can send email. For private profile, a set of users should be defined.
2. Messaging Component
Messaging component is basically all of the objects related to sending email stored in the MSDB database.
3. Database Mail Executable
Database Mail uses the DatabaseMail90.exe executable to send email.
4. Logging and Auditing component
Database Mail stores the log information on MSDB database and it can be queried using sysmail_event_log.
Step 1
Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. This can be done in two ways. The first method is to use Transact SQL to enable Database Mail. The second method is to use a GUI.
In the SQL Server Management Studio, execute the following statement.
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
Alternatively, you could use the SQL Server Surface area configuration. Refer Fig 1.0.
Fig 1.0
Step 2
The Configuration Component Database account can be enabled by using the sysmail_add_account procedure. In this article, we are going create the account, “MyMailAccount,” using mail.optonline.net as the mail server and
makclaire@optimumonline.net as the e-mail account.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'makclaire@optonline.net',
@display_name = 'MyAccount',
@username='makclaire@optonline.net',
@password='abc123',
@mailserver_name = 'mail.optonline.net'
Step 3
The second sub component of the configuration requires us to create a Mail profile.
In this article, we are going to create “MyMailProfile” using the sysmail_add_profile procedure to create a Database Mail profile.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile used for database mail'
Step 4
Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1
Step 5
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;
Step 6
Now let us send a test email from SQL Server.
Please execute the statement below.
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;
You will get the message shown in Fig 1.1.
Fig 1.1
Moreover, in a few moments you will receive the email message shown in Fig 1.2.
Fig 1.2
You may get the error message below, if you haven’t run the SQL statements from step 1.
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of
component ‘Database Mail XPs’ because this component is turned off as part of
the security configuration for this server. A system administrator can enable
the use of ‘Database Mail XPs’ by using sp_configure. For more information
about enabling ‘Database Mail XPs’, see “Surface Area Configuration”
in SQL Server Books Online.
You may see this in the database mail log if port 25 is blocked. Refer Fig 1.3.
Fig 1.3
Please make sure port 25 is not blocked by a firewall or antivirus software etc. Refer Fig 1.4.
Fig 1.4
Step 7
You can check the configuration of the Database Mail profile and account using SQL Server Management Studio by right clicking Database Mail [Refer Fig 1.5] and clicking the Configuration. [Refer Fig 1.6]
Fig 1.5
Fig 1.6
Step 8
The log related to Database Mail can be viewed by executing the statement below. Refer Fig 1.7.
SELECT * FROM msdb.dbo.sysmail_event_log
Fig 1.7
you can find this whole article and other all different kind of database related solutions on http://www.databasejournal.com/features/mssql/article.php/3626056
Please comment, if you found this article useful or not?
