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!
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
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 RelatedHi 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.
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.
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)
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
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
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
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.
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)
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
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]....
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 RelatedHello,
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
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.
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!!!!
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>
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 RelatedHi, 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 RelatedHi All,
View 2 Replies View Relatedhi 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?
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?
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!
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
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?
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;
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] ...
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.
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.....
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.
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
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].....