I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.
How can I use xp_send mail using a dabase other than Master?? Please Help.
hi and thanks for your help, I created the following store procedure to run and send query result via sql mail. how can I run the xp_sendmail.... from a store procedure if I cannot use user Master in a store procedure or trigger. ?
Thanks
Ali
---------------------------
create procedure p_ci_notification as
if exists(
select customer_invoice_id,invoice_total,convert(varchar( 10),date_of_service,101) as date_of_service
from ci_master
where convert(varchar(10),date_of_service+60,101) > convert(varchar(10),getdate(),101)
and invoice_total >5000
and customer_invoice_id not in (select customer_invoice_id from ci_notification )
)
BEGIN
insert ci_notification(customer_invoice_id,invoice_total, date_of_service)
select customer_invoice_id,invoice_total ,date_of_service
from ci_master
where convert(varchar(10),date_of_service+60,101) > convert(varchar(10),getdate(),101)
and invoice_total >5000
and customer_invoice_id not in (select customer_invoice_id from ci_notification )
-- send the email
use master
EXECUTE xp_sendmail @recipients = xzy@aol.com;abc@yahoo.com'
,@message = 'Erick,
how to send a query result via sql mail'
,@query ='select customer_invoice_id,invoice_total,convert varchar (10),date_of_service,101)as date_of_service
from cesweb.cesi_intranet.dbo.ci_notification
WHERE notified =0'
-- update the flag
--select customer_invoice_id,invoice_total,convert(varchar( 10),date_of_service,101) as date_of_service,notified
--from ci_notification
-- truncate table ci_notification
update server.dbname.dbo.ci_notification set notified = 1 WHERE notified =0
END
------------------------------------------------------------------------
ERROR message that I got:---------------------
Server: Msg 154, Level 15, State 1, Procedure p_ci_notification, Line 20
a USE database statement is not allowed in a procedure or trigger.
I've got a job that sends out the results of a stored procedure in anemail via xp_sendmail. The job code is as follows:DECLARE @rlist varchar(1000)Declare @Q varchar(100)Select @Q = 'EXEC impact_exec..TLD_Reconciliation'Declare @Sub nvarchar(60)Select @Sub = 'TLD Reconciliation - Condensed Report'SELECT @rlist = 'jmiller@wbhq.com'exec master..xp_sendmail @recipients=@rlist,@query=@Q, @subject=@SubThe error message I get when running this in Query Analyzer is:ODBC error 7410 (42000) Remote access not allowed for Windows NT useractivated by SETUSER.In Enterprise Manager the owner of the stored procedure in question isdbo. Our SQL guru here suggested I change the owner of the storedprocedure from dbo to myself.I did that and got a warning message that changing the owner willbreak connections. (I'm paraphrasing here because I don't rememberthe exact wording of the warning.) Anyhoo, after changing the owner,and then trying to run the code in Query Analyzer I got the errormessage that the stored procedure could not be found.I'm not sure what to do here. I've never seen the error messagebefore. This same query worked just fine a couple of days ago.Any ideas?Thanks,Jennifer
View 1 Replies View RelatedHaving serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.
View 2 Replies View RelatedHi,
I will give someone a script that creates a database using :
create database mydatabase
my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.
The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.
The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.
Please let me know if there are any (BAD) chances for the following script to fail.
create database mosaikDB737
go
use mosaikDB737
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileListInput](
[FileName] [char](50) NULL
) ON [PRIMARY]
END
use master
select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb')
select * from mosaikDB737.dbo.DBlistOutput
OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.
I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this.
If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.
Query1
SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems
FROM Table1 Inner join on table1.assettag = table2.assettag
where Status = "Scrubbed" or Status = "Initial"
Query2
Update Query1
SET query1.ScheduledItems = True
Where query1.Status = Scrubbed
thank you for any information or help.
Hi,
I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??
Sondra
Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click
Dim myReader As Data.SqlClient.SqlDataReader
Dim mySqlConnection As Data.SqlClient.SqlConnection
Dim mySqlCommand As Data.SqlClient.SqlCommand
'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"
mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
Try
mySqlConnection.Open()
myReader = mySqlCommand.ExecuteReader()
If (myReader.HasRows) Then
'Read in the first row to initialize the DataReader; we will on read the first row.
myReader.Read()
Dim content As ContentPlaceHolder
content = Page.Master.FindControl("main")
Dim lbl As New Label()
lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
content.Controls.Add(lbl)
End If
Catch ex As Exception
Console.WriteLine(ex.ToString())
Finally
If Not (myReader Is Nothing) Then
myReader.Close()
End If
If (mySqlConnection.State = Data.ConnectionState.Open) Then
mySqlConnection.Close()
End If
End Try
End Sub
I have to send mail with HTML format and attaching multiple files dynamically via send mail task.
View 10 Replies View RelatedI've set up DB mail and sent a test e-mail and that comes through fine.
I set up an Operator with email Name: DWhelpton@k-and-s.com;MWeaver@k-and-s.com
I created a job and set up the notifications to e-mail the operator on failure.
When the job runs and fails, I do not get an e-mail and I get the following exception in the db mail log:
Date 2/2/2007 8:35:00 AM
Log Database Mail (Database Mail Log)
Log ID 402
Process ID 3936
Last Modified 2/2/2007 8:35:00 AM
Last Modified By NT AUTHORITYSYSTEM
Message
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Could not retrieve item from the queue.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Controller.ICommand CreateSendMailCommand(Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DBSession)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
What step am I missing?
I am using nt4.0 sp5 with sql7.0 sp3 and exchange client.
Does anyone know how to keep xp_sendamil form putting a copy of the mail message in the sent mail folder or an automated process for deleting all mail in the sent folder? As it stands now I must manually delete all messages from the sent mail folder on a daily basis.
Thanks,
Ed.
Lokendra writes "I have configured the Database mail profile and account in Sql Server 2005 but the mail is not sending and showing the following error message:
Error,235,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-03-05T15:16:07). Exception Message: Cannot send mails to mail server. (Mailbox name not allowed. The server response was: Sorry<c/> that domain isn't in my list of allowed rcpthosts.).
),3000,90,,3/5/2007 3:16:07 PM,sa
but while in the same mail set up in previous instance of sql server 2005 the message was sending very well. After installing new instance of sql server 2005 the problem is arising.
Anybody can tell me that what I can do so that i can send mail using the SMTP databasemail account."
Dear all,
I have switched off the firewall settings on my system and as suggested im entering the minimal information and data to send the mail.
but still the Mail Task is failing..
plz suggest.
Hi Everyone,
Hopefully someone out there will have an idea as this is driving me nuts.
I've setup a task to email on success/failure and keep receiving the following message when executed:
Progress: The SendMail task is initiated. - 0 percent complete
[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.".
Progress: The SendMail task is completed. - 100 percent complete
Task Send Mail Task failed
When I configure Outlook Express on the same machine with the same settings it works.
On the SMTP Connection Manager I have left the default name, tested with both an IP address and Server Name, and no authentication or SSL.
On the Send Mail Task, it uses the above connection. The To: , From: , Subject fields are populated. Message SourceType is DirectInput, MessageSource is Test, Priority is Normal and no attachments or expressions etc.
Nothing useful is logged in the Event Viewer even with full logging turned on.
Any suggestions appreciated,
Thanks.
I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance!
View 3 Replies View RelatedI have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
Hello,
I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).
The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is
successful, my return value is coming back correctly (to whatever i set it)....but if there is an error
such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP.
It comes back blank. (The literature I've read says that processing should continue in the SP, so you
can perform error processing...is that right?)
I set the return var in my ASP as:
objCommand.Parameters.Append objCommand.CreateParameter("return",_
adInteger,adParamReturnValue,4)
and read it back as:
strReturn = objCommand.Parameters("return").Value
In my SP I simply do;
INSERT blah blah
if @@error = 0
return(100)
else
return(200)
(Idon't ever get back "200")
Any ideas???
Thanks for your help.
Hi
I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody
My goal:
I have been written a GPS program that has an database.
The database will be replicated to an central web server.
That will say one web server and x numbers of laptops that has same GPS program. :)
All laptops uses internet to replicate.
Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:
Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.
Problaby I need some kind of an database on client and subscription to make the replication. :confused:
please help me, I'am almost finished with my project only replication part is over my head :(
if someone can point me to right direction in this issue. I would be greateful. :cool:
Thanks
KK
I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.
How can I use xp_sendmail using a dabase other than Master?? Please Help.
I am using this query to create a single transactions from data that is distributed over several databases. So essentially i have created several variable tables and now I have to join them together.
So what I wanted to have happen was display all rows from temptalbel and then join the other tables to create one transaction row. The problem that occurs is within the where statement and I dont understand why. In some cases, you can have two instances of x but y will be different. In that case the joins work perfectly. In the event that there are only a single instance of x associated with a single instance of y this join does not work. Can anyone help me understand why this is happening?
select somedata, somedata, somedata, somedata
From kpi..temptablel l
left outer join @temps s on l.x = s.x
left outer join @tempf f on l.x = f.x
left outer join kpi..temptablee e on l.x = e.x
left outer join @tempn n on l.x = n.x
where l.y = s.y and l.y = f.y and l.y = e.y and l.y = n.y
The Yak Village Idiot
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')
also is it wise to use Group by and having in sub-queries?
Hi
I have an slq Express mdf at path X and I copy it to path Y. When I open it up (from the Y path) using sql mgmt studio, it shows that it's from path X.
Why? How can I get sql mgmt studio to recognize it as a separate mdf, distinct from the one at path X?
TIA
rank beginner
I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.
View 5 Replies View RelatedHi there,
i need a database for my Windows CE application which i can update from a desktop application.
I tried the SqlCeConnection. This works good on the device, but i found out, that i need a sql server on the desktop or someone else to get access to the device server. This is a problem for me, because i cannot insall such a sever on the desktop.
So i searched and searched....I found infos about the ole connection, but i cant find the namespace?!?
Can anyone give me a hint what the best solution could be?
Im using Visual Studio 2005 and a CE device.
Thanks a lot
I'm trying to get up-to-speed on developing new websites using Visual Web Designer and SQL Server Express.
I have previously installed various Microsoft web development components (Visual Studio 2005, .NET Framework 1/2/3 and SQL Server 2005). I've also tried out the new Web Expressions Beta (and Design and Blends, altho the install keys for the latter two never worked and I never received an answer as to what to do for that in the appropriate forums).
TODAY, I'm just trying to get Visual Web Developer and SQL Server Express installed so that I can start down the path of "Connecting to an Existing Database" as outlined in the (downloaded) "Microsoft Visual Web Developer 2005 Express Edition - Build a Web Site Now!" PDF gude (page 138: "Start Visual Web Developer and display the Database Explorer window.").
HOWEVER, when I attempt to install "SQLEXPR32.exe" I get the following message after all the unpacking seems to complete:
"SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Franework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme."
NOW, my first confusion is this: is "SQLEXPR32.exe" Server 2005 or SQL Server Express -- and what's the difference? Following this bit of unhelpful error message, I searched through the SQL Server Express pages and found a live link to a "uninstall tool" that hinted that it could solve my left-over garbage problems. However, then I run that I get:
"The setup has encountered an unexpected error in datastore. The action is Write_CommitFlag .... blah-blah-blah"
so it sounds like the automatic uninstall tool has gotten lost.
ODDLY, the codish window which follows contains the following:
"
Reference to undefined entity 'nbsp'. Error processing resource 'file:///C:/DOCUME~1/Kevin/LOCALS~1/Temp/IXP000.TMP/failed....
<!-- div id="RPCreated" style="display:none">
-----------------------------^
" which very peculiarly looks like something has gotten lost and confused over a non-breakable space? (" "). Huh?
ME? I'm totally lost! Is there no step-by-step cookbook that I can use to just start all over and go through the painful (DSL) downloads and get my show on the road?
Sign me ... "depressed on the garden isle of Kauai ..."
What is SQL?
After running a disk cleanup and defrag, I now have this icon and it tells me SQL is not connected. What is it? I am on a campus which supplies "high speed" internet connection... Please, help! I'm not savvy at any of this!
There are two properties in using CheckpointFile: CheckpointUsage and SaveCheckpoints:
It says: SaveCheckpoints indicates whether the package saves checkpoints while CheckpointUsage indicates whether the package uses checkpoints.
The confusion is: If CheckpointUsage is set to NEVER, and SaveCheckpoints is set to be YES, is there any checkpoint file saved on disk even if a filename is specified? It is easy to find out through a simple testing, but the teminologies here are kind of confusion.
comments please ?
I need to transform the following layout by pivoting, but am confused ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.
I need to go from this...
PKcol1 PKcol2 PKcol3 col4 col5 col6 col7
A 2007 1 Y N N N
A 2007 2 Y Y N N
A 2007 3 N N N Y
into this....
A 2007 1 col4 Y
A 2007 1 col5 N
A 2007 1 col6 N
A 2007 1 col7 N
A 2007 2 col4 Y
A 2007 2 col5 Y
A 2007 2 col6 N
A 2007 2 col7 N
A 2007 3 col4 N
A 2007 3 col5 N
A 2007 3 col6 N
A 2007 3 col7 Y
Can I do this using PIVOT or should I just do 4 inserts (one for each col40col7) into a temp table? Any suggestions?
the field which has drilldown option is always showed minimum width after delopying to website
I attempted to set all properties of this fields as i can,such as "width","can grow" etc, but all do not work
any idea about this?
thanks
Hello,
The Database Mail feature is already enabled on the server, also I have a mail account on the other server.
The problem I faced is that I need to send mail from my SQL Server using a created email account on the other server. How should I configure my email to do that ?
Should I use a sysmail_add_account procedure to enable account, also set profile and profile account ? Does this way creates server mail account that is binded with other email account?
The mail should be sent from my SQL Server.
Thank You.
Hi all....
Our company use yahoo business mail server for our corporate mails. I know that I can configure database mail with SMTP. But when I try to configure database mail account with yahoo bizmail, I cannot do that. It gets configured but when I test it it doesn't send any mails. Do I need to have any special condiguration for this. SMTP address is smtp.bizmail.yahoo.com. Also I have specified the Authentication using my user name and password. Please help
Thanks
Rajesh
I keep getting a generic "Error Sending Mail" error. For testing purposes I am just trying to send using my own email account. What goes in the SMTP Server box in the connection manager? I have tried:
A - the exchange server address (SOMETHING.us.company.com)
B - The SMTP properties I see when I look at the properties of my email address: (my.name@abcd.efgh.company.com)
C - Just the end portion of the SMTP properties: (abcd.efgh.company.com)
D - My email address (my.name@company.com)
I don't know what to enter, or what is giving me such a generic error message.
Hello
I have got a script which gives the mail to the dba mail box when database backup fails.
In the script I want to make a change so that I get the particular database name , on what ever database i implement.
Can you tell me some suggestions.
The script I am using is :
use master
go
alter PROCEDURE dbo.SendMail
@to VARCHAR(255),
@subject VARCHAR(255),
@message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@rv INT,
@from VARCHAR(64),
@server VARCHAR(255);
SELECT
@from = 'testsql2000@is.depaul.edu',
@server = 'smtp.depaul.edu';
select @message = @message + char(13) + Char(13) + @@servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)
EXEC @rv = dbo.xp_smtp_sendmail
@to = @to,
@from = @from,
@message = @message,
@subject = @subject,
@server = @server;
END
GO
--- After the above script is run the following should be given in the 2nd step when
--- the backup jobs are scheduled ------
exec master.dbo.sendmail
@to = 'dvaddi@depaul.edu',
@subject =' Test sqlserver 2000',
@message = '' ;
Thanks
I know you can specify additional recipients in the To column by sepperating them with a semicolon. But whats the easiest way to send to several users, when the email address must be retrieved from a table with a query like this:
select email from problem_subscribers
where project = 'project1'
and statusmail = 'OnError'
So when the eventhandler gets an OnError i want the mail task to be sent to each problemsubscriber.