Passing A Query To Sp_send_cdosysmail

Mar 31, 2004

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!

View 4 Replies


ADVERTISEMENT

Cannot Find Sp_send_cdosysmail

Nov 14, 2006

Hello, everyone:

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


Any help will be appreciated.

Thanks

ZYT

View 1 Replies View Related

Sp_send_cdosysmail - Parameter Inside Body Of Email

Jul 23, 2005

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

View 2 Replies View Related

Passing C# Value To The ASP SQL Query

Mar 31, 2007

Hi Guys,
Im trying to pass a value from a C# page to the corresponding ASP page.
I want to use this value in my SQL Query on the asp page within a Details View.
Ive made the variable public in c# page.
And im trying to concatenate it to my sql query within the <asp: SqlDataSource.
Thanks.
 

View 12 Replies View Related

Passing ArrayList Into Sql Query Or Any Other Way Around

Aug 13, 2005

Hi
I have an arraylist that contains the names of those userid's that i need to check in an online db to check out if they are online now.If the UserId column of the SqlTable matches any of the name of that of the arraylist, then i need to import the values of two corresponding fields say age, Nick etc.I would be very grateful to anyone who could kindly tell me how to do this in an Sql Query i.e How to actually send an arraylist into sql query or any other way around this problem.
Say the arraylist to be verified against the table is:public ArrayList BuddyList = new ArrayList();
Thank You.
Regards.

View 1 Replies View Related

Passing LoginName Into SQL Query

Mar 4, 2006

How do I pass the user’s LoginName into the WHERE clause of a SQL query?
 
I created the following query, but I don’t know how to get the user’s LoginName and pass its value into Param1. Nothing I try works.
 
SELECT property.propertyid, property.shortdesc, property.shortdesclink, property.text, property.UserID, [User].UserID AS Expr1, [User].Name FROM property INNER JOIN [User] ON property.UserID = [User].UserID WHERE ([User].Name = @Param1)

View 1 Replies View Related

Passing Column Name To A Query

Aug 22, 2001

Hi all,
Can somebody tell me how I can pass column name of a table to a stored proc.
Here's something close to what I'm trying to do....

Declare @colname varchar(30)
set @colname = "TITLE"
select name, id
from contacts
where contacts.@colname = "TITLE"

Regards
Uday

View 2 Replies View Related

Passing A Query Into A Variable

Aug 9, 2007

What is the easiest way to pass a value into a variable...
TSQL...

declare @@test int

--select count(*) from authors
select count(*) into @@test from authors

View 1 Replies View Related

Passing Value Inthe Query

Aug 10, 2006

Hi All,

I have problem creating query. here is the scenario

I have a table EMPLOYEE , the fields are

Employee Name, month1,month2,month3,..month12

thomas,100,1200,400...1200

wilson,500,1100,500...1000

carol,200,500,200... 3400

 

I am creating report using visual studio reporting services

I am passsing the report parameter to the query, but i could not the actuall result

MY PARAMETER  is 2 ( varrialble is gmonval)

I am writing the query  like,

select empname,"month"+Ltrim(@gmonval) from employee

the result which am getting is

thomas,month2

wilson,month2

carol,month2

 

it suppose to be

thomas,1200

wilsom,1100

carol,500

how to get the above result. please help

 

Regards

Saleem

 

 

 

View 8 Replies View Related

Passing A Variable To SQL QUERY

Oct 24, 2007



Hi
I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted.
Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas.
Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.

View 11 Replies View Related

Passing Column Name As A Query Parameter?

Dec 19, 2006

Hi,

Is there a way to pass the column name as a query parameter?? If I use '@Question', like below, I get an error. If I change it to the actual name of the column 'Question1', it works fine. However, I need it to be dynamic so I can pass the column name [Question1, Question2, Question3, etc...] in and not have to define this for each question.


Doesn't Work!!


Code:


SELECT

1.0 * SUM(CASE WHEN @ColumnName > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN @ColumnName = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN @ColumnName < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'


FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID

WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)






Works, but I need to pass in the column name dynamically.


Code:


SELECT

1.0 * SUM(CASE WHEN Question1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN Question1 = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN Question1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'


FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID

WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)

View 1 Replies View Related

Passing Value From A Access Form To A Query

Jun 23, 2006

Frustration has gotten the best of me on this one. Can anyone help?
I need to pass the current value in an Access Data Project (back-end is MS SQL) text field to the where condition in an SQL using VBA. This is what I have, but does not work.

Private Sub Command44_Click()
Me.SS.SetFocus
Dim strSQL As String
Dim strSSecurity As String
strSSecurity = Me.SS
strSQL = "Update Employees Set employees.PositionID = '',employees.jobcode = '' Where employees.ss = strSSecurity"
DoCmd.RunSQL strSQL
End Sub

It is supposed to take the current Social Security number from the form and match it against the employees table. Once it finds the matching record it should update the PositionID and JobCode fields to '' (empty string)
But it doesn't
Anyone with any ideas?
Thanks
Dan

View 2 Replies View Related

Passing Params To SP Using OPEN QUERY

Oct 8, 2014

How to pass parameter values to Stored Procedure using Openquery ?

DECLARE @CenterNumber nvarchar(8)
DECLARE @CenterName nvarchar(100)
DECLARE @tblLeads table(
LeadCount int)
SET @CenterNumber = '98454152'
SET @CenterName = 'neck'

[code]....

View 3 Replies View Related

How To Passing Multi Value Parameter In SQL Query

Jul 23, 2005

I have a query :Exec 'Select * From Receiving Where Code In (' + @pCode + ')'@pCode will contain more than one string parameter, eg : A1, A2, A3How can i write that parameters, I try use :set @pCode='A1','A2','A3'but get an error : Incorrect syntax near ','Please help meThanks

View 10 Replies View Related

Passing Parameters To AS400 Query

Dec 5, 2007

Hello,

I'm using

IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
to extract data from AS400 and copy them to SQLServer2005. It's my first experience with exptracting data from AS400.

I need to pass in SSIS a parameter to the extract query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > ?

I'm naming the parameter in the mapping as 1 but geetting an error
[as400 [1]] Error: The SQL command requires a parameter named ""00001"", which is not found in the parameter mapping.

I have tried 00001, '00001', ("00001" is not accepted from SSIS) but getting still this error.

A query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > '1071205'
works OK.

Can anybody help me ?

thanks in advance

View 7 Replies View Related

Loop By Passing Variable Into Query

Nov 2, 2007



Hi all,

I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.

The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.

View 3 Replies View Related

Passing LinkButton Text Value As Sql Query Parameter!!!

Oct 24, 2006

Hi,Could you inform me programmatically how can I pass LinkButton text value as Sql Query parameter?I tried the 1 command.CommandText = "SELECT DISTINCT [Description] FROM [Projects] WHERE ([Type] = " & SqlDbType.Text = LinkButton12.Text & ")"
 but it does not work!!!  Thanks in advance!!!!

View 2 Replies View Related

Passing Variable To Where Clause In Sql Query From A Grid

Mar 28, 2007

Hi everyone I am new to this site I have a major issue I cant figure out seeing how im fairly new to asp, and vb, but i have 5 years php mysql experience.
Im pulling the correct data into a grid. Then i need to make a button or some sort of link that will take the value of one field in the record set and replace it with @transid in the where statement I can enter in the value of transid into form field with that name and it will run the rest of the script correctly, I just cant get past this hurdle. If anyone can help that would be great. I tried to get this to work with java script but then realized thats not possible to transfer varaibles to asp from it.
///javascript 
function DisplayReciept(transactionnum)
{
recieptdis = transactionnum;
 
}
 
////field in grid 
<asp:BoundField htmlEncode=false DataFormatString="<a href=javascript:DisplayReciept{0}>Display</a>" DataField="transid" HeaderText="Show Reciept" SortExpression="transid" />
 
 //////////////query//////////////
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbsgcConnectionString %>"
SelectCommand="SELECT [fulldata] FROM [data] WHERE ([transid] = @transid)">
<SelectParameters>
<asp:FormParameter FormField="transid" Name="transid" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

View 1 Replies View Related

Passing An Array To Query In A Table Adapter

May 23, 2007

I'm looking for a way to pass an array of values as a parameter to a query in a table adapter.  For example I want to run a query something like:SELECT * FROM menu WHERE menu_role IN (@roles)And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.For some reason I can't figure out a way to do this.  Any help would be greatly appericated.Thanks,Ryan. 

View 6 Replies View Related

Passing In Query Paramater Values Through WebUserControls

Jun 7, 2007

Hi, I don't want to pass in parameter values in the url to get a certain record from my database (for a better search engine result) so instead i'm using webuser controls (this is a must) with Public Values that should be read by the SqlDataSource. So in the page that contains the usercontrol there is something like:UserControl.Value = "myvalue"Now I want the SqlDataSource in the usercontrol to read this value and pass it into a query (in the parameter of the WHERE statement, eg.: @recordID = myvalue).Thanks in advance! 

View 1 Replies View Related

SqlDataSource And Passing Multiple Int Parameters In Query

Aug 11, 2007

Hi All,

View 2 Replies View Related

Urgent: Passing Parameters From Vb To Access Query

Jan 12, 2004

hi all,

I have a view (query) created in ms acess. how can i pass a parameter from vb to the query at runtime? can i do it?

View 7 Replies View Related

Passing Date Parameter To Oracle Query

Aug 16, 2006

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is


WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.
But when I run the report from Preview, I get no results at all no matter what format I use in the function.
Any chance any of you have seen this and know how to work with it?

View 11 Replies View Related

Passing MS SQL2005 Query Result Into Javascript Function

Mar 20, 2007

I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function.
This is how I retrieve the longitude:
 <asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>"
SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)">
</asp:SqlDataSource>
I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this:
var map = new GMap2(document.getElementById("map"));var lat = <%=lat%>;var lon = <%=lon%>;var center = new GLatLng(lat,lon);map.setCenter(center, 13); 
 However, lat & long do not contain the retrieved result but rather a useless System.something string.
How do I assign the retrieved results to these variables and port them over to Javascript as required?
Many thanks!

View 14 Replies View Related

Is There A Way To Loop Over Report Parameters, And Format Them Before Passing Them Onto The Query

Nov 13, 2007

Good morning all,


I have a report which is getting its parameters from an ASP.net page. My ASP developer wants to send in simple values, such as the list 1,2,3,4 for a parameter. However my report needs that list to look like [CD RSRC].[RSRC].&[1], [CD RSRC].[RSRC].&[2], [CD RSRC].[RSRC].&[3], [CD RSRC].[RSRC].&[4].


Is there any way, on the report services side, to capture an incoming report parameter, parse it, loop over the parsed values and format them?


I don't think there is, but I wanted to check before I go back to the developer and tell him he has to send in tuple lists.


Thanks,
Kathryn

View 1 Replies View Related

Integration Services :: Passing Tablename In Query As Variable?

May 6, 2015

I am using a sql task to get all tablenames and then passing the output to another sql task inside a for each container.

So that the 2nd sql task will be executed for each table. My query looks like SELECT DISTINCT b.EmailAddress FROM  ? ......

Since I am passing the tablename as a variable (output from the 1st sql task), I get the following error:

[Task Execute SQL] Error:

Failed to execute the query 'SELECT DISTINCT b.EmailAddress FROM? AS a INNER... ':' Failed to extract

the result in a variable of type (DBTYPE_I4)'. Possible causes include the following: Problems with the query, not properly fixed ResultSet property, not properly set parameters or not properly established connection.

how to pass a tablename as a variable to a query?

View 5 Replies View Related

SQL Server 2012 :: Open Query To PostGres Passing Parameters?

Mar 24, 2014

Looking to pass in the @targetDbName into the Open Query.

The target DB is PostGres and requires 2 single quotes around the dataset name.

I have tried many possible variations using the '+ @variableName +'

USE JonathanDB
declare @dqzDateVer int;
declare @targetDbName varchar(25);
select @targetDbName = DB_NAME();
select @dqzDateVer = dqz_date_ver FROM OPENQUERY(ofr_meta_db, 'select dqz_date_ver from ofr_registry.dataset_feed_state where dataset_name=''JonathanDB'' and state = ''Done'' order by row_iddesc limit 1');
print @dqzDateVer ;
print @targetDbName;

View 1 Replies View Related

Integration Services :: Passing Parameterized Query Through Variable In SSIS

May 22, 2015

I have defined a variable Var_Query_SQL and passed the below query using expression but it is showing error. where am i going wrong.

"SELECT
       sample_id ,
       sample_time ,
       trans_date ,
       product = mh.[identity] ,
comments = s.m_smp_comment

[URL] ...

View 4 Replies View Related

Passing A Variable To A Linked Query (OPENROWSET For Excel Syntax)

May 11, 2007

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)



but I have not gotten all the ''''' + @variable syntax right.



Here is my raw openrowset with what I am aiming at.






Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @FIL VARCHAR(65)

SET @FIL = 'C:company foldersDocumentationINVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:company foldersDocumentationINVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT



Anyone game? Many thank-yous, in advance.



Kind Regards,

Claudia.

View 1 Replies View Related

Reporting Services :: Passing All Parameter Selection To Dataset Query

Nov 20, 2015

I have a report that prompts the user to select a parameter, for simplicity, let's say the parameter is for color choice, options are Red, Yellow, Blue or *. The * is for include all colors. I am passing that parameter back to the dataset query which, again for simplicity is

select
Hue from AvailableColors
whereColor
= @ColorParam.

For a specific color this works fine, for the "*" selection it returns a null. It would seem that I need to convert the * to % but I am not sure how.....

View 6 Replies View Related

Passing Parameter Query From SQL Function To Access Project Report

May 20, 2006

I can pass a parameter from an Access Query to an Access Report (MDB) by entering [Select Date] in the Query criteria and by placing an unbound control with a control source =[Select Date] on the report. I can't get this to work from a SQL Function Criteria to an unbound control on the Access Data Project Report. In the Function Criteria, I enter @SelectDate. In the Report control, I enter @SelectDate and it gives me an 'Invalide Column Name' error. Any idea how I can pass a parameter from a SQL Function to an ADP report?

THANKS!

p.s. I tried searching for other postings on this without any luck.

View 1 Replies View Related

How Do Use Stored Proc Passing Parameter From Table In Selcet Query Statement

Aug 8, 2002

i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.

for example i want to use

select p1 = sp_diff d1,d2 from table1

sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value

View 1 Replies View Related

SQL Server 2014 :: Passing Multiple Columns Values From Query To One Variable?

Aug 10, 2014

Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)

[code].....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved