Sp_send_dbmail Query Option
Sep 22, 2007Hello,
I'm using sp_send_dbmail with query option. Is there a way to not have "1 rows returned" in the email?
Thanks!
ADVERTISEMENT
Sp_send_dbmail. How Can I Use A Query For @recipients
Feb 5, 2008Hi everyone,
i'd like to assign "@recipients" for sp_send_dbmail derived from a query as follows:
Code SnippetDECLARE @mailist VARCHAR(2000)
SET @mailist=
'select email
from
server.db.dbo.table
where
lastname=' +''''+'aaa'+''''
exec msdb.dbo.sp_send_dbmail
@profile_name='myprofile',
@recipients=@mailist,
@subject='email address through a query',
@...
when i run it i dont get any error message but mails to dot arrive.
any suggestion why ?
Thanks a lot
Attach Query Results As Xml With SP_SEND_DBMAIL
Jun 10, 2008I'm trying to get sp_send_dbmail to attach the @query result set as an xml file.
The email is being sent and the attachment is present, but rather than ascii xml, it contains a chunk of nonsense like this
0x440352004F005700440470006B00650079004409660069007200730074006E0061006D00650044086C006100730074006E0061006D0065004410700068
I've tested the query with the first bit of code and sent the email with the second bit. I think I can jimmyjack it to write the xml to a temp file on the server and send that file using the @file_attachments parameter, but I'm hoping someone out there knows how to get this one to work.
Thanks in advance for any help you can give me.
Here's my code:
-- test that xml output is being generated
DECLARE @OUTPUT xml
SET @OUTPUT = (SELECT * FROM database..table FOR XML PATH('ROW'), root('ROOT'))
SELECT @OUTPUT
-- run the email proc
EXEC msdb.dbo.sp_send_dbmail
@QUERY= 'SET NOCOUNT ON SELECT * FROM database..table FOR XML PATH(''ROW''), root(''ROOT'')'
,@RECIPIENTS= 'userid@companyname.COM'
,@subject= 'Subject'
,@attach_query_result_as_file= 1
,@query_attachment_filename= 'ATTACHMENT.xml'
,@query_result_width= 32767
,@query_no_truncate= 1
,@append_query_error= 1
,@query_result_header= 1
Sp_send_dbmail @query Results Truncated
Sep 21, 2007
Hi, sending a mail using sp_send_dbmail (as below) with attachment works. However the characters in the attacheted file gets truncated to 256. The file is populated with the results from the @query string.
EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'SQLAdmin'
,@recipients = 'a@a.com'
,@subject = 'Test'
,@body = 'Test'
,@body_format = 'TEXT'
,@query = 'SELECT distinct column FROM Table'
,@execute_query_database = 'PULSE'
,@attach_query_result_as_file = 1
,@query_result_header = 0
,@query_result_width = 32767
,@query_attachment_filename = 'a.pdf'
,@exclude_query_output = 1
,@query_no_truncate = 1
The value of MaxFileSize is 1 Mb when executing sysmail_help_configure_sp. It seems as if the @query_no_truncate value is being ignored by sp_send_dbmail procedure.
Any help would be appreciated.
Sp_send_dbmail: Need Output To A Query Without The Messages.
Feb 27, 2008I am writing a stored procedure which finally has to send mail to the customer with a list of phones that have been added to his account. I am getting these phones from a SQL Table in the form of a query, and including the output of the query(list of phones) , in the body of the mail.
There are two issues that I am unable to resolve,
1. I just want the output with no messages.
eg. 8887775567
not
8887777767 ( 1 row(s) effected)
2. One phone number per row.
8887775567
8009978776
6679800077
NOT
8887775567 8009978776 6679800077
Thanks in advance.
Using Quotation Marks In A Msdb.dbo.sp_send_dbmail Query
Oct 31, 2007Hi There,
I'm having a little trouble with a large query I have written to be sent as an email.
I want to send it as a csv so I've added commas in between each value in the query.
This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ggsgn'
,@recipients = 'sgsdm'
,@subject = 'Nsgdday'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='
use sdgsgdgs
select
a1.c#,',', client,',', fr,',', tpe,',', dateReq as DateRequested,',', ... '
SQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option
Feb 10, 2015So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
Query Option In DTS
Apr 27, 2001Im trying to transfer data from SQL tbl to .xls file through a DTS package.
My problem is that DTS transfers all the data from the source tbl to .xls
file.
But i would like to have the selective data transfer based on some query like ;
select * from tbl where field ='val'
instead of complete data transfer.
Thanks in advance.
Regards
Siva
Using Query Option In Bcp
Mar 18, 2008
Hi,
I am trying to load data from sql server to sybase. SQL Server is our source databse and I want to do this task in unix server using isql and bcp options.
I want to limit the changes on source tables and also I cannot use DTS or SSIS since we dont own SQL server.
Does any have any idea how to use query option in bcp. It is not direct table load. I need to run select statements with Joins in it.
Any ideas on how to script run and save results in to file and then bcp in to our sybase server.
I am not suposed to craete any views on source db. So just need to run sql statements and take the results to text file.
Query Option In DTS - Urgent !!!
Apr 27, 2001Im trying to transfer data from SQL tbl to .xls file through a DTS package.
My problem is that DTS transfers all the data from the source tbl to .xls
file.
But i would like to have the selective data transfer based on some query like ;
select * from tbl where field ='val'
for the val i want to pass different values .
instead of complete data transfer.
Thanks in advance.
Regards
Anand
SQL Query Advanced Option
Apr 18, 2001I want to run a query overnight through the SQL agent using osql. However I need to turn off the print header option that you normally turn off through the advanced options tab in the Query Analyzer. Does anyone know if this is possible?? Many Thanks in advance..
View 1 Replies View RelatedI Need Sql Query Filtering Option
Mar 12, 2007My table have many column.........One for Message(column).My table updating every seconds......
But I need last Message.........
Ex
Message(Column) ..............
-------
firstmsg
nextmsg
middlemsg
jsjfsjmsg
jhsffmsg
lastmsg
In table i need lastmsg--- How to get
In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing
Apr 27, 2008Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies View Related'set' Query Option To Results In Text? Print Ss, Mis?
Jul 17, 2003In TSQL, is there a way to 'Set' the query option results in text as the head of my sql statement?
kind of tired of hit contol+t to get the text result.
also, when I
print '@dateStart--' + cast(@dateStart as varchar(30))
I don't get seconds and I even need mis.
Is there a simple way instead of a combination of datepart() to get ss, mis?
thanks
David
How To Disable Connect Option In Query Analyzer
Feb 28, 2004Does anyone know how to diable the File => Connect option in Query Analyzer?
Thanks
Clarification:
The situation is that I have several users on an application that controls their access to the DB. The user ID has full rights to the DB but the application stops them from making changes to data. Now I have a user that needs to be able to create and run SELECT statements in a “Query Analyser” type of environment. I can publish a Query Analyser session with locked credentials using Citrix, but I don’t want him to be able to use the File => Connect option to reconnect with one of the application Ids thus giving him full rights in an open environment.
Any ideas?
Thanks
T-SQL (SS2K8) :: New Query Option Is Not Visible In Server?
Mar 20, 2015I just inherited a few Servers that have SQL Server 2000.
I cant find the new query Icon?
[URL]
How To Make Query Select Option Results
Jul 12, 2014I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...
I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?
Here is the form code with query :
<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST">
<select name="ManagerID" id="manager" style="width:400px" class="form_textbox">
<?
$db_connect= mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $db_connect) || die(mysql_error());
$sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";
[code]....
SqlCeResultSet Looses Updatable Option If Query Has 2 Params
Feb 26, 2008i have result set from such query :
resultSetOptions = System.Data.SqlServerCe.ResultSetOptions.Scrollable | System.Data.SqlServerCe.ResultSetOptions.Updatable;
queryText =
SELECT agvb_id as ID
,agvb_cat_id as Category
,agv_brn_id as Brand
,agvb_brn_name as BrandName
,agv_brn_area as Area
,agv_brn_sku_qty as SkuQuantity
,agv_brn_qty as Quantity
,agv_orderliness as Orderliness
,agvb_importance as Importance
FROM AGENTS_VISITS_BRANDS
WHERE (AGENTS_VISITS_BRANDS.agvb_agv_id = @agv_id)
AND (AGENTS_VISITS_BRANDS.agvb_cat_id = @dad_id)
ORDER BY agvb_brn_name
if i open resultSet with such qury it looses its updatable option.
If i remove from where 1 param (no mater which) everything runs ok.
Turn On The Query Governor Cost Limit Option For 20 Minutes?
Sep 18, 2014I want to turn on the query governor cost limit option for 20 minutes so that queries do not run longer than 20 minutes but I could not find any info as if this option would also not allow database backup job or other maintenance jobs to run more than 20 minutes. We have backups (Via RedGate) run over 3.5 hours and others like rebuild indexes and integrity checks even more than 3.5 hours....
SQL Server 2012 SP2-CU1
Windows 2008 R2
Sp_send_dbmail
May 9, 2006When using "sp_send_dbmail", messages are queued through Service Broker. Other than the system views, "dbo.sysmail_...", in MSDB, is there another way to know if an email was sent successfully?
Also, in 2000 you did not need to supply the whole email address in order for xp_sendmail to work. For example, I could use my name, "RGioia" as the recipient and it would send email to rgioia@<my current domain>. Does anyone know of a work-around for this in 2005 or do you just have to ensure that full email addresses are used?
Thanks in advance!!
SP_SEND_DBMAIL
Aug 8, 2007I wrote a trigger as follows:
CREATE TRIGGER TR_ABC
ON TABLE_A
AFTER INSERT
AS
DECLARE @E_MAIL VARCHAR(255),
@MESSAGE VARCHAR(255)
SET @MESSAGE = 'A new call request has been logged'
SELECT @E_MAIL = E_MAIL
FROM TABLE_A AS A
LEFT OUTER JOIN TABLE_B AS B
ON B.SYSADMIN = A.SYSADMIN
WHERE SYS_ADMIN = 1
EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'SCINFO',
@recipients = @E_MAIL,
@body = @MESSAGE,
@subject = 'SCI Service Request';
The problem is everytime I insert data into the table I get an error stating that-:
'At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".
Mail queued.'
Please help.
Need Help W/ Sp_send_dbmail !
May 1, 2007SQL Server 2005Help. I have a problem:On a 2000 box, I am calling the sp_send_dbmail stored procedure on a2005 box (they are linked servers).This is how I'm calling the proc (from the 2000 box), which usessp_send_dbmail to send the mail.EXEC My2005Server.DatabaseName.dbo.SendNotification@recipients = 'me@yahoo.com',@subject = 'SENDING @TOTAL,@body = @text1 + @text2 + @text3 + @text4 + @text5@text1 - @text5 are varchar(8000).As you can see I'm trying to take advantage of the varchar(max) that@body allows.However, I get this error:Server: Msg 170, Level 15, State 1, Line 22Line 22: Incorrect syntax near '+'.I need to be able to concatenate these somehow... as I cannot put theminto one large variable from the 2000 server side.Can you see the problem here?Help.Thanks
View 1 Replies View RelatedSp_send_dbmail
Oct 31, 2007Hello all,
I'm new to this forum and have the same question on SQLTeam.com, where I'm a frequent forum user.
I'm trying to send the results of a table to a group of people by email, see code used below:
exec msdb.dbo.sp_send_dbmail @profile_name = 'DT', @recipients = 'isantos@foo.net;', @subject = 'QA Results', @body = 'Some Text', @query = 'select * from [db].[dbo].[table]', @execute_query_database = 'db', @exclude_query_output = 1, @append_query_error = 1;
Error message I get by email:
Msg 15404, Level 16, State 19, Server TAKKARA, Line 1 Could not obtain information about Windows NT group/user 'WECLICKisantos', error code 0x5.
If I remove the @query, @exclude_query_output and @append_query_error options, I can send the email without a problem, just whenever I try to add a query to it that it gives me this error message.
I'm a sysadmin, I can run that query, I have access to the DatabaseMailUserRole under msdb and the profile that I'm using is public... I'm kind of stuck, let me know if you can help.
Sp_send_dbmail Question
Apr 14, 2008I'm running the following test
declare @dbccdate varchar(60)
set @dbccDate='dbcc east' + convert(varchar(60),getdate(),10)
exec msdb.dbo.sp_send_dbmail
@recipeints='human@gmail.com',
@body = 'This is a test'
@subject= @dbccdate ,
@query='dbcc checkdb (msdb)',
@attach_query_result_as_file=1,
@query_attachment_filename='Dbcc_MSDB_Results.txt'
This works fine but, I'd really like the subject to contain the line
"Checkdb found 0 errors..." So mgmt doesn't have to open the txt file to view the results.
I've tried dumping the results to a txt file first. Then trying to read the text file for the line inquestion. I wrote a for loop to parse the results command line, but can't figure how to add that value to the subject.
The script is this:
for /f %i in ('findstr /B /I "CHECKDB" y:dbcclogmsdblog.txt') do echo %i
Is there anyway to do this?
Possible Issues With SP2 And Sp_send_dbmail?
Apr 25, 2007Have a funny one that I've not yet resolved, and was wondering if I am the only one.
SQL2005 SP1 server, Ent Ed, 64 bit, clustered machine.
SP2a installed over Easter weekend.
about 12 days after apparently succesful upgrade, the cluster was failed over. After that fail-over, an existing job started failing, with the following message:
quote:Error executing extended stored procedure: Invalid Parameter [SQLSTATE 42000] (Error 22050). The step failed.
All the job does is build a SQL query, then supply the query to sp_send_dbmail for execution:
DECLARE
@RetCodebit,
@Tovarchar(200),
@Subjectvarchar(100),
@CCvarchar(200),
@Queryvarchar(max),
@Debugsmallint
--cut section that has the query , but these are straight SQL
--queries - no SP or XP usage whatsoever
EXEC@RetCode = msdb.dbo.sp_send_dbmail
@profile_name= 'Email',
@recipients= @To,
@subject= @Subject,
@copy_recipients= @CC,
@query_result_header=0,
@Query=@Query
One 'funny' I noticed is that up until the failover, SSMS seemed to erport the version of the server as still the SP1 (2157). After failover, it now lists (3042). This may simply be a symptom of SSMS not auto-refreshing though. We have (I believe) failed back again since then, still no joy.
Any Ideas?
*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!
Sp_send_dbmail Problem
Feb 26, 2008I am trying to use the sp_send_dbmail sproc but I get the following error:
Msg 22051, Level 16, State 1, Line 0
Attachment file C: est.txt is invalid.
My code:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Server Mail Profile blah blah',
@recipients = 'myEmail@somewhere.com,
@body = 'Hello World',
@subject = 'Hello World',
@file_attachments = N'C: est.txt'
why would that be? The test.txt file does exist on my drive!
Please advise.
Sp_send_dbmail Without Using A Cursor?
Aug 22, 2007I have a table which contains a list of addresses to send an email to. I'd like to be able to do something like:
update emaillist
set sendcount = SendMyMessage( emailaddress )
where sendcount = 0
In this case SendMyMessage is a user defined function which calls the sp_send_dbmail stored procedure.
However, I am receiving an error saying "Only functions and extended stored procedures can be executed from within a function"
From what I've seen, it sounds like I am forced to use a cursor to go through my email list table. Is that the only way?
Under SQL 2000, using a user defined function to call CDONTS was a no brainer....
Thanks,
Chris.
Sp_send_dbmail && COM Errors.
Mar 23, 2006I just started getting these errors on a few (not all) servers the other day. These servers have been running fine for a few months now...
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Error initializing COM
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I can only find one thread regarding this error, specifically with dbmail and I am doing the same thing (executing a query and attaching the results).
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=248650
This thread points to a KB article http://support.microsoft.com/kb/910416 which is close except for the fact that these were not upgrades, but clean installs. I'm leary of applying the hotfix to my servers.
Is this a known issue internal to MS and is the referenced hotfix appropriate to install?
Sp_send_dbmail Possible Security Issue
May 28, 2008I have a stored procedure that sends an mail using sp_send_dbmail. My problem is, if I execute the stored procedure via my ASP.NET 2005 application, the email will not send. I know the stored procedure is being called and works because:
1. The stored procedure does 2 things, sets a status, then sends the email. The status is being changed, but the email is not being sent.
2. When I investigate with Sql Server Profiler, I see the stored proc being called and the values of the parameters are set as they should be.
What I don't understand is, I can copy the call in Sql Server Profler, and paste it in a Query Analyzer window and execute that same call, and everything works fine, status is changed, and the email is sent (to myself for testing and I receive it just fine).
Is there a reason why the email won't send via code? Am I suppose to setup some security that will allow my application to send emails? Any info would be GREATLY appreciated.
Problem With Sp_send_dbmail With Attachment
Aug 2, 2007Hi,
We have a DTS package in the old SS2000 that we are still using in SS2005. We change the old xp_sendmail to use the new sp_send_dbmail. This runs fine without attachment. But with attachment, I'm encountering an error when running the package.
The task reported failure on execution.
The client connection security context could not be impersonated. Attaching file requires an integrated client login.
Syntax error or access violation.
I cannot find much topic on the net about this error.
Thanks very much for any help.
Regards
Sql 2005, Sp_send_dbmail, @profile_name
Jan 23, 2008Hi everyone,
I'm trying to usie "sp_send_dbmail" and i'm required to provide value to: "@profile_name" argument. The trouble is i have no idea what it means..
How do i get to know what is my "profile_name" ?
Thanks.
Trigger Problem With Sp_send_dbmail
May 15, 2008Hallo
I want to write a trigger that sends me an email, when new records are added into a table.
For testing I have created one table called location1, which has simple data from AdventureWorks.Production.Location. And another table is called new_location, which stores the newly added records.
Here is the trigger:********************************
use [AdventureWorks]
if object_id ('location1') is not null
drop table location1
go
create table location1(LocationID int, LName varchar(50))
if object_id ('new_location') is not null
drop table new_location
go
create table new_location
(LocationID int, LName varchar(50))
if object_id ('change_ID', 'TR') is not null
drop trigger change_ID
go
create trigger change_ID on location1
for insert
as
delete new_location
insert into new_location
select * from inserted
exec sp_send_dbmail
@profile_name = 'Profile',
@recipients = 'email',
@subject = 'New Record(s) added.',
@query = 'select * from new_location order by LocationID';
go
********************************
After I insert statement, Management Studio runs the executing throughout.
insert into location1
select LocationID, Name
from Production.Location
order by LocationID
Can somebody help?
Msdb.dbo.sp_send_dbmail Error
Feb 28, 2007 I make PROCEDURE to send email useing db msdb and this PROCEDURE dbo.sp_send_dbmail like this
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'saly',
@recipients = @email,
@subject = @subject,
@body = @body;
Go
And when EXEC it gives me mail qeue
and mail don't arrive where it goes i don't know please tell me what error
thankx very much