November 23, 2015 at 3:19 am
Hi,
I have a query that I would like to send the result of it by mail to the customer,
This is my query that when I run it, it give me the result
SELECT 'some name' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup
FROM Users INNER JOIN
SecurityGroups ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N'Administrator')) order by login
but when try to send the result by mail with this query get this error:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile1',
@recipients = 'someone@microsoft.com',
@subject = 'Database Mail Test by T-SQL',
@body = 'This is a test e-mail sent from Database Mail on YUHONGLI5\KJINST.',
@query = 'SELECT 'DB name' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup
FROM Users
INNER JOIN SecurityGroups
ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N'Administrator'));',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'file_name.txt'
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'DB name'
Any help would be appreciate it.
Thanks
November 23, 2015 at 3:22 am
You need to escape the quotes inside the query: ' (single quote) has to be turned to '' (two single quotes, not double quote).
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile1',
@recipients = 'someone@microsoft.com',
@subject = 'Database Mail Test by T-SQL',
@body = 'This is a test e-mail sent from Database Mail on YUHONGLI5\KJINST.',
@query = 'SELECT ''DB name'' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup
FROM Users
INNER JOIN SecurityGroups
ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N''Administrator''));',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'file_name.txt'
-- Gianluca Sartori
November 23, 2015 at 3:29 am
Thank you for your replay,
I already tried that but when run that get this error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: ?Msg 208, Level 16, State 1, Server NTS60, Line 1
Invalid object name 'Users'.
November 23, 2015 at 3:48 am
You also need to specify the database where the query should run. It's one of the parameters.
As an alternative, you can qualify the object names in the query with three-part names: database.schema.table
-- Gianluca Sartori
November 23, 2015 at 3:49 am
You either need to supply a value for the @execute_query_database parameter, or qualify the Users table with the database name in the query.
John
November 23, 2015 at 4:07 am
Thans you so much,
you are right I did add the
@execute_query_database = DB name
now I can run the query and get a mail.
just one more question IF I may,
my query has 2 parts, I mean it should red the same info from 2 databases join them and then send the result
querntly the query is this:
use DB name 1
SELECT 'some name1' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup
FROM Users INNER JOIN
SecurityGroups ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N'Administrator'))
union
SELECT 'some name2' as Collectie,DataBase2..Users.Login,ABNamrokunst..Users.DisplayName, DataBase2..SecurityGroups.SecurityGroup
FROM ABNamrokunst..Users INNER JOIN
ABNamrokunst..SecurityGroups ON DataBase2..Users.SecurityGroupID = DataBase2..SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N'Administrator'))
order by login
And it give the result that it should, but my question is how can I sned the result of this compelet query by mail?
Thank you.
November 23, 2015 at 4:16 am
The same way you'd send the results of any other query. It only has one result set, as you mention, so I'm not sure what you're struggling with?
John
November 23, 2015 at 4:29 am
Thank you again,
I did follow your suggestion and run:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test profile',
@recipients = 'shahin3213@gmail.com',
@subject = 'Database Mail Test by T-SQL',
@execute_query_database = tms,
@body = 'This is a test e-mail sent from Database Mail on YUHONGLI5\KJINST.',
@query= 'SELECT ''some name'' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup
FROM Users INNER JOIN
SecurityGroups ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N''Administrator''))
Union
SELECT ''some name2'' as Collectie,loo..Users.Login,loo..Users.DisplayName, loo..SecurityGroups.SecurityGroup
FROM loo..Users INNER JOIN
loo..SecurityGroups ON loo..Users.SecurityGroupID = loo..SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N''Administrator''))
order by login;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'file_name.txt'
but get this error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: ?Msg 4104, Level 16, State 1, Server NTS60, Line 11
The multi-part identifier "Users.Login" could not be bound.
November 23, 2015 at 4:37 am
That's not the same query you posted earlier. What is "loo" - is it a linked server? If you're querying a remote server then the four-part naming convention is Server.Database.Schema.TableorView. If the other database is on the same instance you only need the last three parts. Don't omit the schema name (dbo, I take it?) - it's not good practice.
John
November 23, 2015 at 8:06 am
sorry to comeing back again,
when run the quey that should select data from the second datbase on the same server get this error:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = 'shahin@gmail.com',
@execute_query_database = databsemrohist,
@subject = 'Database Mail Test by T-SQL',
@body = 'This is a test e-mail sent from Database Mail on YUHONGLI5\KJINST.',
@query = 'SELECT ''ABNAMRO Historisch Archief'' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup
FROM Users
INNER JOIN SecurityGroups
ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N''Administrator''))
union
SELECT "database Kunstzaken" as Collectie,database Kunstzaken..Users.Login,database Kunstzaken..Users.DisplayName, database Kunstzaken..SecurityGroups.SecurityGroup
FROM database Kunstzaken..Users INNER JOIN
database Kunstzakent..SecurityGroups ON database Kunstzaken..Users.SecurityGroupID = database Kunstzaken..SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N"Administrator"));',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'file_name.txt'
Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.
Any idea?
November 23, 2015 at 8:26 am
You've got half a dozen "database" keywords in the second half of your query. What are they for? Run your query in a query window to check that it works before putting it in your sp_send_dbmail command - you'd have seen those syntax errors immediately if you'd done that. Having said that, I'm not sure that that is what's causing the particular error that you're getting.
John
November 23, 2015 at 8:59 am
sure If we run the query in the query windows it give me the result, so this realy streng why now it come up with this error.
if you like I can send you an screenshot of the result of the query.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply