Email the results of the query

Hello,

I want to email the results of the query. The query output can be email either as the HTML body of the email to be sent or as a excel attachement.

I am wondering as to what would be the best way to achive this.

a) I could have a email client module in C# or VB.NET 2.0, which would fetch the query resultset and store it in a dataset. I format it as HTML body and send the email using System.NET. For excel, can use XSLT

Please suggest if there is any other approach that would be more suitable.

Thanks.

Check here to view a similar posting from earlier today.|||

Found what I was looking for. So here goes

Solution was Configure Database Mail and then use the msdb.dbo.sp_send_dbmail stored procedure. For your reference http://builder.com.com/5100-6388-6164310.html

Eliminating spaces in query output

Is there a way that I can do a select statement that will just select the text within a field. ie. If the field is 200 wide, but the actual text is only 20 characters, can I select JUST the 20 characters with nothing else? I need to do this for a huge number of rows ....

Also, I'm running the select statement via osql if that helps.When selecting the column name you can use the trim function, e.g.

select rtrim(ltrim(emp_name))
from employees

This will trim blank spaces from the left and right hand ends of the character string. If you only need to clear trailing blanks just use the rtrim function.|||RTRIM was just what I needed, thank you.

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Tex
tdata),217)
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regardsDid you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Tex
tdata),217) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx
.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Tex
tdata),217)
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards|||Thanks Vinod

>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techin...ctdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:17fe001c44a19$f48dfb80$a301280a@.phx
.gbl...
substring[vbcol=seagreen]
exec%',Textdata),217)[vbcol=seagreen]
>
>.
>

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217 )
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regards
Did you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217 ) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Textdata),217 )
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards
|||Thanks Vinod

>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinf...tdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
substring[vbcol=seagreen]
exec%',Textdata),217)
>
>.
>

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217)
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regardsDid you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Textdata),217)
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards|||Thanks Vinod
>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
>> Hello,
>> The output of the following query returns two identical
>> records, i need to eliminate all records that are
>> identical but if i use the "distinct" before the
substring
>> function its returned one error.
>> This is the query that i'm using:
>> select EvClassDesc,
>> STime,
>> substring(Textdata,patindex('%
exec%',Textdata),217)
>> as [TextData],
>> objid
>> into DestinationT
>> from OriginalT
>> where objid = 1111111 and EvClassDesc = 'SP:Star'
>> Thanks,
>> Best regards
>
>.
>

efficient way to put multiple tables in one xml output

hi guys,

i'm looking for alternatives on how to put multiple tables in one xml output.

i have this .net program that has been passed to me that currently loops through some tables and calls an sp that returns a query that has a FOR XML AUTO, ELEMENTS on it, then appends <Extract> ... </Extract> as a root node for all the extracted tables then saves it as an xml file. i'm trying to optimize it by limiting the number reads to the db server, so one option is for me do the loop inside the sp.

is this efficient? any snippet? by the way, i'm using sql2k

thanks guys.

/rh4m1ll3

hi,

Bit confused with the requirement. plz let me know what you are doing exactly from the below 2 approaches:

go to one table 1 - execute sp - return xml file1 - add <Extract> ... </Extract> as root node

go to one table 2 - execute sp - return xml file2 - add <Extract> ... </Extract> as root node

go to one table 3 - execute sp - return xml file3 - add <Extract> ... </Extract> as root node

..........

( or )

go to one table1 - get some details

go to one table2 - get some details

go to one table3 - get some details

...........

pass details to sp - return xml file - add <extract> .. <extract> as root node

confirm what you are doing?

Regards,

kwaerol.

|||alright this is the current setup

here's the psuedo of the current c# app

Code Snippet

for each table.. {
x += executescalar("theSPwithForXML") //appends each result
}
x = "<Extract>" + x + "</Extract>"
// then saves x as xml file

what i'm trying to achieve is

x = executescalar("theSPthatAppendsAllTablesIntoOneXML")
// then saves x as xml file

since you cannot assign the result of a query with a FOR XML to a variable in SQL2K, i'm looking for other alternatives or workarounds in TSQL, so that "theSPthatAppendsAllTablesIntoOneXML" can return a single xml with all the tables that i need to convert.

the last resort that i'm looking at is returning multiple result instead

CREATE PROCEDURE theSPthatAppendsAllTablesIntoOneXML
AS

SELECT *
FROM Table1
FOR XML AUTO, ELEMENTS

SELECT *
FROM Table2
FOR XML AUTO, ELEMENTS

SELECT *
FROM Table3
FOR XML AUTO, ELEMENTS

-- etc etc

GO|||

Hi,

You are correct. As per my knowledge we cannot take the xml output into a local variable in sql2k.

I feel that every time u do some sql operation and get some xml output

At last want to append <extract> nodes and save that as xml file.

If I am correct. Why don't you get the resultsets at one slot from db and then make use of Load xml using XMldatadocument.

If required you can create one schema file and make use of it to load the xml data document from dataset (having multiple Tables)

Regards,

Subbu