Formatting @query Results Using Xp_sendmail

Jul 20, 2005

I know formatting should be handled in the client app and not in SQL Server
but this is the situation I must develop in so any help would be
appreciated.

I'm running the following in Query Analyzer

USE Pubs
DECLARE @MessageSubject VARCHAR(50)
SELECT @MessageSubject = 'Report'
EXEC master.dbo.xp_sendmail 'me@mine.com',
@query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
@subject = @MessageSubject

The results in my email look like:

au_fname au_lname
------------------- ----------------------------------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson

I would like the results to be like:

First Name: Abraham
Last Name: Bennet

First Name: Reginald
Last Name: Blotchet-Halls

First Name: Cheryl
Last Name: Carson

Thanks

View 2 Replies


ADVERTISEMENT

Formatting Results Of The Query

Jul 23, 2004

my current query returns data as follows:

stopID reason count

h1 e 5
h1 v 2
h1 a 1
h2 v 2
h2 m 8
h3 t 6
h3 v 1

Is it even possible to sort/format this query in SQL to
return the same data but in this type of format? Using
the values of the 'reason' column as column names?

stopID e v a m t

h1 5 2 1 0 0
h2 0 2 0 8 0
h3 0 1 0 0 6


thanks

-r

View 14 Replies View Related

Xp_sendmail - Formatting

Jun 12, 2001

Requirement:
On Insert, Trigger xp_sendmail to complete pre-formatted email with specific content and send to given email address.

Question:
What tools/methods are available to develop a template that SQL Mail's (SQL Server) xp_sendmail function can utilise without resorting to attachments.

e.g. - how do Amazon.com format and send their automated shipping confirmaion emails ??

View 1 Replies View Related

Simple SQL Question About Formatting Results

Apr 14, 2004

Embarrassed to ask but nevertheless here i go:

I have a SQL like this:

select sum(qty)
from tblXYZ


The results is displayed as (for example) 123456789.56


I want the results to be displayed as 123,456,789.56 (i.e. use the currency or the comma format; whatever it's called).

Please help

View 9 Replies View Related

Formatting Case Statement Results

Mar 11, 2008

I am trying to format the Sys field (which is Int) in case the first byte is zero. But I'm getting an error msg;
Msg 156, Level 15, State 1, Procedure sp_Pull_Cancels, Line 43
Incorrect syntax near the keyword 'Right'.

CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END as Right('0000' + Cast(Sys as varchar (4)),4)

Any ideas how I construct this CASE statement to get the desired results.

Thanx,

View 13 Replies View Related

Xp_sendmail Refuses To Run Query

May 23, 2000

I'm new to using xp_sendmail, and here is what I get...
=====================================
EXEC master..xp_sendmail
@recipients = 'amccollough',
@subject = 'EMAIL TEST',
@message = 'This is a test'
(result="Mail Sent", it works fine)
=====================================
But this one fails...
=====================================
EXEC master..xp_sendmail
@recipients = 'amccollough',
@subject = 'EMAIL TEST',
@message = 'This is a test',
@query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES'
(result="Server: Msg 17969, Level 16, State 1, Line 0
Unable to login to SQL Server.")
=====================================
I've tried tons of variations with adding in @dbuse and @set_user, and am totally lost as what to try next. Anybody have suggestions?

View 1 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Formatting In Sql Query

Dec 12, 2006

Hello All,
Is it possible to format an integer to a float value with 2 decimals in an sql query.
If yes, Please help?

View 2 Replies View Related

SQL 6.5 Query Formatting Help

Feb 1, 1999

I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this

select distinct(companyname),max(calldate) from tablename

what I want to see is the latest call made to a customer for each company
but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?

View 1 Replies View Related

SQL Query Formatting

May 24, 2006

Hi

Im new to sql server what i wanted to do was as follows:

i got a table with application_name and app_version

it looks like as follows

Application_name App_ver
-----------------------------
Oracle 8.1.0
Oracle 8.5
Oracle 9.1
Oracle 10.1
Sql Server 6.5
Sql Server 7
Sql Server 2000
Sql Server 2005

i want to get query the table and produce an out put as follows:

Application_name App_vers
-------------------------------------------
Oracle 8.1.0, 8.5, 9.1, 10.1
Sql Server 6.5, 7, 2000, 2005

please note i need the commas in place
i have written several queries to try and do it but no luck

im using sql server 2000

i will be greatful if any one can help...

View 7 Replies View Related

Query For Table Formatting

Oct 11, 2012

I have a table as below,

Code:
ClientId Balance '10/1/2012' '10/2/2012' '10/3/2012' '10/4/2012' '10/5/2012'
1 1000 1000 null null null null
2 2000 null 2000 null null null
3 3000 null null 3000 null null
4 4000 null null null 4000 null
5 4000 null null null 4000 null

How can I change above table into below format?

Code:
ClientId Balance '10/1/2012' '10/2/2012' '10/3/2012' '10/4/2012' '10/5/2012
1 1000 1000 1000 1000 1000 1000
2 2000 null 2000 2000 2000 2000
3 3000 null null 3000 3000 3000
4 4000 null null null 4000 4000
5 4000 null null null 4000 4000

View 1 Replies View Related

Query Formatting In SQL Management Studio

Feb 2, 2007

I recently upgraded my SQL Server to 2005 and instead of the Enterprise Manager I am now using SQL Server Management Studio.

Does anyone know how to turn off query formatting in views?

In enterprise manager I use to be able to double-click the view and it would open an sql editor which left the code exactly how I formatted it (space, returns etc for ease of reading). Now in Management Studio, each time I modify a view it reformats it so it become confusing to build them.

View 1 Replies View Related

Formatting Dates As YYYY/MM/DD For A Particular Query

Oct 3, 2005

Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?

The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2)).... Which seems so rediculous. There's gotta be a better way!

View 3 Replies View Related

Formatting Decimal Places In A Query In MS SQL

Jan 14, 2004

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.

View 5 Replies View Related

Should Be Easy - Help Formatting A Conditional Query.

Aug 28, 2006

SQL Server 2000. Here's what I have so far. The section of the query I need help with is highlighted in blue.
 
CREATE PROCEDURE dbo.GetByVersion
(
 @targetVersion varchar(30),
 @product varchar(50)
)
AS
 SET NOCOUNT ON;
SELECT *  FROM  MyTable
WHERE (product = @product)
AND
  CASE 
    WHEN @targetVersion='' THEN (targetVersion='')
    ELSE (targetVersion LIKE @targetVersion + '%')
  END
GO
 
I get a syntax error in the Stored Procedure editor on an equal sign in this line:
    WHEN @targetVersion='' THEN (targetVersion='')

What I want is this (in psuedocode):
  if @targerVersion is blank
    search for records where the targetVersion column = blank
  else
    search for records where the targetVersion column starts with @targetVersion
 
Can anyone offer any suggestions as to how I might modify my query to do what I want? Any help is very much welcome - Thanks in advance! :-)

View 5 Replies View Related

Formatting For Writing My Query To An Excel Sheet

Feb 5, 2004

Hi ,

I am executing my query and writing it to an excel sheet by choosing "query" results to an excel sheet. It does not format them well. I have trimmed my fields too. There are about 10 fields in the database and I need to show them in the excel file all the ten fields adjacent to one another. Is there anyway I can format them other than the programming aspect.

Thanks

View 1 Replies View Related

Query Diff Results From Ent Manager Query And Query Analizer

May 28, 2008

ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)

SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007

but in query analizer I get the result of

12/31/2006

Why the different dates

View 4 Replies View Related

Reporting Services :: Select Query - Formatting A Date Affecting MIN

Sep 2, 2015

In my SELECT query I have: MIN(a.orderdue) AS 'Oldest order date'

This works in that it brings through the oldest order date, however it brings through a date format like: 2015-06-11 11:30.000

So I amended the SELECT query to:

MIN (CONVERT(varchar(17),a.orderdue,103)) AS 'Oldest order date'

This brings the date through as 11/06/2015, which is preferable.

But I have noticed that doing this has affected the output: the MIN function no longer returns the first (oldest) date, but a completely different value.

Obviously my changing the formatting for the date has affected the MIN output. Is there any way I can amend the formatting of the date without this happening?

View 3 Replies View Related

Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results

Sep 22, 2015

-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo

[code]....

This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

View 2 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Easy SQL Question. How To Display Query Results In Query Analyzer

Feb 12, 2008

When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare     @SniierId as   uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare    @SniierAlias  as nvarchar(50)declare    @AlwaysShowEditButton  as bitdeclare     @SniierName  as  nvarchar (128)/* Check access for Sniier */SELECT TOP 1       @SniierName      = Sniiers.SniierName,        @SniierAlias    = Sniiers.SniierAlias,        @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId

View 3 Replies View Related

Returning Results Based On One Query Into Existing Query

Aug 2, 2012

linking two tables together to get an end result

find below the code i have used

The first part of the query provides me with the info i need

SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp

[Code].....

View 1 Replies View Related

Transact SQL :: Use Query Results As Select Criteria For Another Query

Jul 10, 2015

I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.

While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.

View 6 Replies View Related

Query A Query With Multiple Results

Aug 22, 2005

Hi,New to .Net and SQL.  I have two tables that I have joined together.  RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name.  I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts.  I have a details report that gives each contract info. for each specific adjusters rentals.  However, I want to just list the adjuster once and give all of their totals.  In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.Do I need to code this on the page with a do while loop?Appreciate any help.SELECT     SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled)                       / COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled)                       / COUNT(dbo.RentalControl_Main.Rate) AS TotAvgFROM         dbo.RentalControl_Main INNER JOIN                      dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterIDWHERE     (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND                       (dbo.RentalControl_Main.AgencyID = '2')

View 1 Replies View Related

Sql Query Results #?

Apr 20, 2004

I was wondering if there was a keyword that would allow you to return the number of results from a query such as (this is fake just giving an example so that someone can give me the real answer)


select TotalReturned
from table
where field=""

View 7 Replies View Related

Query Results

Oct 7, 1999

I am a beginner with SQL, so please excuse any ignorance here.

Under SQL 6.5, when executing a SELECT DISTINCT query against a table in a certain database, the results were returned alphabetized. Now, after migrating the database to SQL 7.0, the same query does not return the results alphabetized. If I add an index for the column specified in the query to the table, the results come back alphabetized. Is there any way to correct this other than adding indexes for all columns in all tables that are queried in this manner?

TIA,

Paige

View 2 Replies View Related

Same Query, Different Results

Mar 2, 2005

I am running the same exact query in SQL 2000 and I get different results. Should I try running DBCC CHECKDB and look for errors?

View 5 Replies View Related

No Query Results

Apr 30, 2008



Hi,

I have this line of code that when run in the query doesn't return anything:

srv.AGENT_S_ACC_CODE IN ('" & Replace(Replace(Parameters!arcode.Value," ",""),",","','") & "')
The parameter is setup and the query works when I hardcode it with a value but will not return anything with this, does anyone know why?

Thanks,
Rhonda

View 2 Replies View Related

Restrict Query Results

Oct 25, 2006

Hi guys, say i wrote a query that returns 1,000 records.. what kinda query could i write that only returns say the first 50 records of the 1,000 recs..

View 4 Replies View Related

Use Results From First Query As Parameter In Second

May 13, 2007

Hello,
I want to select all the customerIDs where an email address exists. Easy: select customerid from customers where emailaddress = @emailaddress
Now I want to use the resulted customerIDs from the above query as a parameter to select all the email addresses with emailstatus equal to 3.
How do I create this type of while statement in a stored procedure? I prefer to not create a temporary table.
My idea was to do it like this:
select emailaddress from emailaddresseswhere emailstatus = 3 and customerid = (select customerid from customers where emailaddress = @emailaddress)
This doesn't seems to work. I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
thanks

View 4 Replies View Related

Help Grabbing Top Three Results From A SQL Query!

Jul 17, 2007

I have a pretty big SQL query with a one to many sort of relationship...
There are client accounts that we're reporting on.  Each account has four different historical categories attached.  Each historical category can have maybe fifty entries in each, sorted by date.
I need to figure out how to grab the unique accounts and show only the three most recent results per each historical subcategory with the account...   the three most current results from each of the four subcategories, displayed by the parent client account number.
I've created four views to isolate the subcategories as a start, thinking I could bring them into a parent query ...
but my question would be... how do I generate JUST the top three historical transactions by account number?  If I select TOP 3, I get only the 3 newest in the MASTER LIST, not per client account, which is what I need to do.
Does this make sense?  I could really use a good SQL helping hand with this one.
Thank you!

View 12 Replies View Related

Faster Query Results

Oct 17, 2007

Can anyone give me the basics of speeding up reports that use queries or views or nested views?
Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc.
We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports.
Thanks
 

View 4 Replies View Related







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