I want to send email by sp_send_cdosysmail, but I got error,
Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'sp_send_cdosysmail'.
Even though I run the query under master database. Is it not available in SQL Server 2000?
This is my query:
declare @Body varchar(4000)
select @Body = 'This is a Test Message'
exec sp_send_cdosysmail 'someone@example.com','someone2@example.com','Test of CDOSYS',@Body
I am looking for any help or hints on how to pass a query into or run a query as sp_send_cdosysmail as the body.
I need some way other than using an attachment to return the results of a query from sp_send_cdosysmail as the body. Any good points in a direction would help!
I currently have a web form posting back to a SQL table using a StoredProcedure. Part of this SP is that it pulls data from another tableand inserts a new row into the registration table.I want to have a trigger on the registration table that will fire whenthe row is inserted which will use the sp_send_cdosysmail sproc to sendan e-mail to the user.However, I want to be able to include the value of one of the fieldswithin the BODY of the message. I can't find a way to includeparameters/variables within the Body of a message usingsp_send_cdosysmail and it's driving me nuts.Here's what I have in a sproc (not a trigger) that executessp_send_cdosysmail...I currently pass a parameter for the "To" e-mailaddress and that works fine._________________________________________________EXEC sp_send_cdosysmail'fromemailaddress@testcompany.com',@stremail, <--This is the Parameter passed for the "To" e-mail addy-->'Test Subject','Test Body,Additional TextAdditional Text<--THIS IS WHERE I WANT TO PUT THE PARAMETER-->Additional TextAdditional Text'__________________________________________________ _Is there any way to do this?The sp_send_cdosysmail I used is the standard MS one..Here it is forreference:Thanks for any help offered!ElliotCREATE PROCEDURE [dbo].[sp_send_cdosysmail]@From varchar(100) ,@To varchar(100) ,@Subject varchar(100)=" ",@Body varchar(4000)/************************************************** *******************This stored procedure takes the parameters and sends an e-mail.All the mail configurations are hard-coded in the stored procedure.Comments are added to the stored procedure where necessary.References to the CDOSYS objects are at the following MSDN Web site:http://msdn.microsoft.com/library/d...s_messaging.asp************************************************** *********************/ASDeclare @iMsg intDeclare @hr intDeclare @source varchar(255)Declare @description varchar(500)Declare @output varchar(1000)--************* Create the CDO.Message Object ************************EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT--***************Configuring the Message Object ******************-- This is to configure a remote SMTP server.--http://msdn.microsoft.com/library/d...n_sendusing.aspEXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','1'-- This is to configure the Server Name or IP address.-- Replace MailServerName by the name or IP of your SMTP Server.EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','SMTPServer'-- Save the configurations to the message object.EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null-- Set the e-mail parameters.EXEC @hr = sp_OASetProperty @iMsg, 'To', @ToEXEC @hr = sp_OASetProperty @iMsg, 'From', @FromEXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @BodyEXEC @hr = sp_OAMethod @iMsg, 'Send', NULL-- Sample error handling.IF @hr <>0select @hrBEGINEXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUTIF @hr = 0BEGINSELECT @output = ' Source: ' + @sourcePRINT @outputSELECT @output = ' Description: ' + @descriptionPRINT @outputENDELSEBEGINPRINT ' sp_OAGetErrorInfo failed.'RETURNENDEND-- Do some error handling after each step if you have to.-- Clean up the objects created.EXEC @hr = sp_OADestroy @iMsgGO
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
Hello! Please help me to find web page where guys who passed MS exams share their experience. I remember it should be like BenyenVine or very close... Thank you, Elena
Everytime I search for this text, it seems to pick up stuff beginning with 5, not dash 5. Does something need to be escaped and how would this be done?
Sometimes the simplest things are the most difficult... I'm creating a UDF as below, then executing it but all I get is that the object does not exist. I must be missing something very basic here...
CREATE function dbo.GetColumnLength(@vcTableName varchar(50), @vcColumnName varchar(50)) returns smallint as begin declare @intLength as smallint select @intLength=sysC.prec from syscolumns sysC, sysobjects sysO where sysC.Id = sysO.Id AND sysO.xtype ='U' And sysO.Name = @vcTableName AND sysC.Name = @vcColumnName return @intLength End GO select top 2 * from player, dbo.GetColumnLength('playerdetails','email')
Can someone help on this:I am just learning, and I'm connecting to the the northwindcs.mdf tables /open file is northwindcs.adp.This is the sample installed using msde, which is supposed to be mini sqlserver to learn.Please don't refer me elsewhere, here is what I'm trying to learn:If I want to hit a command button and do the following:1. Find a customerid2. if found, edit the record, if not found, add a new record.How would the below code need to look for this, I'm not even sure theconnection string is correct.I'm getting following error:run-time error 3219operation not allowed in this context.I get the y messagebox, but rst!ContactTitle = "The Owner" doesn't work.When I hit the debug, rst.close is highlighted.Also, how do you handle a no find situation here, I noticed a nomatchdoesn't work.I am real good at programming, but new to the server thing.And finally, is there a way to hit this command button, and do all from astored procedure instead of code? But in background, no user inteventiononce button is hit. Which is better, this code approach or a possiblestored procedure.Please help, if I get this down, I think I'll have the rest wipped. Theconnect string is one big thing confusing me along with handling record oncefound / not found. I'm used of DAO. If some one is willing to help, I canemail detailed real code from a database I'm really working on. I need tolearn this first to convert code.HERE IS SAMPLE CODEPrivate Sub Command16_Click()Dim cnn As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim mark As VariantDim count As Integercount = 0cnn.Open "DSN=NorthwindCS; Provider=SQLOLEDB;Data Source=OEMCOMPUTER;InitialCatalog=NorthwindCS; uid=sa; pwd=;"rst.Open "SELECT * FROM Customers", cnn, _adOpenDynamic, adLockOptimistic, adCmdText'rst.Open "SELECT CustomerID FROM Customers", cnn, _' adOpenDynamic, adLockReadOnly, adCmdText' The default parameters are sufficient to search forward' through a Recordset.rst.Find "CustomerID = 'CHOPS'"If rst!CustomerID = "CHOPS" ThenMsgBox "y"rst!ContactTitle = "The Owner"ElseMsgBox "n"End If' Skip the current record to avoid finding the same row repeatedly.' The bookmark is redundant because Find searches from the current' position.'Do While rst.EOF <> True 'Continue if last find succeeded.' Debug.Print "Title ID: "; rst!CustomerIDcount = count + 1 'Count the last title found.'mark = rst.Bookmark 'Note current position.'rst.Find "CustomerID = 'CHOPS'", 1, adSearchForward, mark'Exit Do'Looprst.Closecnn.CloseDebug.Print "The number of business titles is " & countEnd Sub
To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.
I have a visual studio 2005 app that has to match strings to something in a database. It works wonderfully until a string shows up with an & in it (so "this & that") The app wont find anything even though it should, the same query that is made in VS works just fine in enterprise manager I tried a replace on the & with the chr(), hex, and html reference for & and none of those work. We tried using different methods in VS to do the sql statement and commands. The statement surrounds the string with single quotes. If we take the & out, everything works fine. Turned off request valadation just in case. Any ideas?????
I am a novice.... Where would I start, if I wanted to search my SQL database for a record and have SQL return a true/false. I want this to be done with ASP.net.
Hello.I have a select that returns some SubCategories.SELECT SubCategoryID from SubCategories SCATINNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryIdWHERE SCAT.ParentCategoryId = X Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.What code i need to write toI think .. Select * from Items AS IT WHERE IT.Subcategory = ???I don't know, anyone can help meThanks
Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?
That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.
My ISP runs MS SQL server for me and does not have experience managing this type of database. For example, the database occassionally crashes and looses data. Our site is not live yet, but once we go live, this is unacceptable.
I need somebody to consult on the database configuration, tuning, security and maintenance plan. How would I find a person with the correct qualifications?
I have the following in my web.config<appSettings> <add key="strConn" value="server=10.100.1.2;uid=sa;pwd=;database=MyDB/></appSettings>and I am getting the following error message --"SQL Server cannot be found or access denied"if I replace the server IP address with server name, it works fine. Am I missing something here? Help!ThanksBugme
Where to look for the web assitant created jobs on the server?I created a web page using the web assistant which is suppossed to get updated each time a value changes in a particular table,but I can't find the task which I created ,where do i look for that?I didn't use this wizard previously.thanks for any help! Sheila.
Hi, All I have a problem with one table. This table is corrupted so I drop the table and recreate the table..(Of course I export data) After that I try to put the primary key to new table but it won't allow me to do it. Error message says " There are duplicated key existed" Therefore I open up the EM and take a look at that table. There is key in that table but not Primary key..(also from the query analyzer using sp_help) My question to U is how can I find this duplicated key and delete that info? I think somewhere in the system table contains this info but I don't know where:-(((
I'm doing a query using SQL Server. How can I get the max and min date from a database? I can't find any function related to it. Can you help me please ....
Hello! I have 80 tables in the database and I need to find in which table particular column exist. How can I do it shortly, without naming every table in my query? Thank you.
We have a number of databases on a server running NT 4.0(SP4) and SQL 6.5 (SP 5a). We run Enterprise Manager scheduled dumps of the databases to disk every night The dumps are spread over the clock from about 1730 to about 2330. The databases are stable and we regularly run various DBCC maintenence routines on them. We have been doing these dumps for a long time without problems. For the last two weeks we have been getting the following message in the SQL error log:
"[date & time] kernel DPDB_MAP: unable to find the OOS_MAP for page xxxx"
This is followed by an AV and stack dump of whatever dump happens to be running. This occurs at different times, and we can find no pattern or abnormality in the error logs or the event logs. So far, Microsoft has not been able to provide any information about this phenomenon. Can anyone help? What is the "OOS_MAP" and how do we fix this ?
Hi, We have surveys that gather data and we're trying to find the best way to query this data for reporting. It is not too late to change everything so we're open to any suggestions. Here is my current setup:
Results table from which each row represents a user that filled in the survey so it contains a unique identifier, a survey identifier and some other data which we use elsewhere.
Result_Answers table which holds the answers for each result. So it contains the result ID, the question ID and the answer ID
Questions and Answers table which hold the actual text for each identifier used in result_answers.
What we're trying to do is retrieve the count for each answers (thats easy) based on a set of conditional answers. For exemple:
Q1 - Are you male or female
Q2 - What is your age group
Q3 - What type of music do you listen to.
So lets say I want the count of each answers for each question but limited to the results that answered male on Q1 and 18-25 OR 25-35 on Q2. The only way I found to do this is create a cursor to loop trough each questions, select the result IDs that answered Q1 correctly and then remove from that temp table each result ID that didn't answer the subsequent questions correctly. This gives me a page long query with a cursor and multiple IN commands (for when there's more than one answer valid for a single question)
I'm not saying the results are abysmal, I'm still getting my results inside 2s with a bit over 1 million rows in those tables. But unfortunatly, this is basically only one survey... I can only imagine that it will get longer and longer as we add more surveys.
So my question is: Is there a better way to query this data or worst case, is my table schema not efficient enough? In both cases, how can I fix/optimize it?
This one's a bit tricky. I want to be able to take an IP address of a request on my website and find it in a table. Specifically, I need to be able to record IP address RANGES for search engines so that when they attempt to find a page that isn't there, I can programmatically send a 404 header instead of give them the human-friendly page (please don't make suggestions on this - I'm using PHP, etc., and can't send both a 404 header and a human-friendly page).
Anyway. Let's say a search engine has the IP range 33.33.0.0 through 33.33.255.255 (I made that up). if I receive a request from IP 33.33.101.221, how can I store ONE record (as either a regular expression or maybe multiple fields or somehow else) so that I can match that IP? I'm normally pretty good at figuring stuff like this out, but this one has me stumped for the moment.
All help is greatly appreciated.
I'll be posting a similar request on the PHP forum - I hope that's not considered cross-posting.
I need to find the record_id of the record I just inserted so that I can display the newly created record id.
Right now it looks like this: (I'm using Cold Fusion to insert and retrieve records, but I think this is an SQL question)
INSERT INTO table (value1, value2, value3) values (value1, value, value3)
Select record_id FROM table WHERE value1 = value1 AND value2 = value2 AND value3 = value3
The problem is when someone puts in the exact same data for values 1 through 3 I get back only the first record_id, not the last one. Maybe that's the trick right there, add an ORDER BY field_id DESC??
I thought MySQL had a way to grab the last inserted record, or return the record_id while I'm still in the insert statement, that would be preferable.
OKay looking at this highly simplified query here is my problem. We have a ton of Asset numbers that are the same and for each asset number the inv_id should also be the same.....
However, since this data gets entered by humans - sometimes the inv_id is wrong.... I need to figure out a query that fleshes out the rows where asset_nums are the same but inv_id is different than other rows...?
Of course this may be impossible..... I mean what if there was only two records by the same asset_num and each inv_id was different? How could the query know which was the right one......
I need to run a report that finds the invalid inv_id's so that they can corrected.....
any thoughts on how this is done?
My thoughts are - using an inner select... something to the effect of :
SELECT .... FROM (SELECT * WHERE (inv_id = inv_id) > 1) GROUP BY asset_num.........
I just dont know exactly what the correct syntax is....
muchas gracias....
(btw this is actually ms-sql but mysql forum is so much more popular and the syntaxes are similar enough....thank you...)
