Change From when using xp_sendmail

I am writing a stored procedure that will send an email, using xp_sendmail, in the error handling if the procedure fails. However, I want the from and return address to list a shared mailbox, so that any questions from the user are directed back to the work queue. Can this be done?
DavidLook into CDO or xp_smtp_sendmail. (http://www.sqldev.net/download/xp/80/xpsmtp80-v1.1.0.8.zip) It's much easier and more flexible (bofem)

Change Date Format from mm/dd/yyyy to dd/mm/yyyy

Hi,
I have a query to retrieve data from a table for the give two dates(from and
to) in a stored procedure.
select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
now I have give the dates like this to get the results
@.FromDate =01/01/2005(mm/dd/yyyy)
@.ToDate=6/2/2005(mm/dd/yyyy)
but I would like to give the date in this format (dd/mm/yyyy) like
@.FromDate =01/01/2005(dd/mm/yyyy)
@.ToDate=2/6/2005(dd/mm/yyyy)
can someone help me with this(I know there is a way in which you can specify
in select query itself, but forgot :-) )
Thanks
KiranWhy do you need to express the date in a special format? Why not let the end
user use the format the
end user want (based on regional settings) and let the application send a fo
rmat to SQL server which
is language neutral?
See http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:Ot61f56ZFHA.3840@.tk2msftngp13.phx.gb
l...
> Hi,
> I have a query to retrieve data from a table for the give two dates(from a
nd to) in a stored
> procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can speci
fy in select query
> itself, but forgot :-) )
> Thanks
> Kiran
>|||Kiran wrote:
> Hi,
> I have a query to retrieve data from a table for the give two
> dates(from and to) in a stored procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND
> @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can
> specify in select query itself, but forgot :-) )
> Thanks
> Kiran
Always use a portable date format in SQL... or else.
YYYYMMDD
or
YYYY-MM-DDThh:mm:ss.mmm
David Gugick
Quest Software
www.imceda.com
www.quest.com

change data format in stored procedure

Hi All,

My scenario is that I want to change the default SQL server format in my stored procedure more preferably only during the course of stored procedure execution (not permanent changes does any one have idea that how will I able to achieve this simple task...

regards,

Anas

You can use the CONVERT function for this. As a common approach Date formatting should be done on the client and only on the server if you have no client application or the client application does not support it.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

change current database in stored procedure

I am trying to move whole bunch of stored procedures from 1 db to another.
Instead of going into each of them and change the table names to fully
qualified name, is there a command that I can set the current database? I
know USE does not work in sp. Any help will be highly appreciated!
USE will work in dynamic SQL I think, e.g.
EXEC('USE somedb; EXEC someproc;')
However, if you're going to bother going in and putting USE statements or
something similar, why not just go in and put in the fully qualified name?
I don't see what you will be saving by NOT putting in the fully qualified
names...
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>I am trying to move whole bunch of stored procedures from 1 db to another.
> Instead of going into each of them and change the table names to fully
> qualified name, is there a command that I can set the current database? I
> know USE does not work in sp. Any help will be highly appreciated!
|||Thanks for your response, Chew. It was just because we have too many stored
procedures that we need to move. Each of them uses so many tables. Going
into each of them and change the table names will literally kill me.
However, I do think change them to fully qualified names is the right thing
to do. At the mean time, we just want a quick solution. I tried doing
exec('use db') and it did not work.
alter procedure junk
as
begin
exec ('use abc')
select * from iv00101 where itemnmbr = '12345'
end
"Foo Man Chew" wrote:

> USE will work in dynamic SQL I think, e.g.
> EXEC('USE somedb; EXEC someproc;')
> However, if you're going to bother going in and putting USE statements or
> something similar, why not just go in and put in the fully qualified name?
> I don't see what you will be saving by NOT putting in the fully qualified
> names...
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>
>
|||1. Move all the stored procecures from DatabaseA to DatabaseB
2. Create views in databaseB with same name as in DatabaseA
Database A
=======
TableA
CREATE PROC Proc1
AS
SELECT * FROM TableA
DatabaseB
create a View
CREATE VIEW TableA
AS
SELECT * FROM DatabaseA.dbo.TableA
Move the Procedures
This way you don't have to touch you code. All you need to do create buch of
views
Thanks
Ravi
"Ed" wrote:
[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many stored
> procedures that we need to move. Each of them uses so many tables. Going
> into each of them and change the table names will literally kill me.
> However, I do think change them to fully qualified names is the right thing
> to do. At the mean time, we just want a quick solution. I tried doing
> exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
|||Ed wrote:[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many
> stored procedures that we need to move. Each of them uses so many
> tables. Going into each of them and change the table names will
> literally kill me. However, I do think change them to fully qualified
> names is the right thing to do. At the mean time, we just want a
> quick solution. I tried doing exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
EXEC runs in its own context. Just fully qualify using DB.OWNER.SPNAME.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||> exec('use db') and it did not work.
Because that is not what I said to do.
You need to do everything inside a single EXEC.
EXEC('Use somedb; EXEC someproc')

change current database in stored procedure

I am trying to move whole bunch of stored procedures from 1 db to another.
Instead of going into each of them and change the table names to fully
qualified name, is there a command that I can set the current database? I
know USE does not work in sp. Any help will be highly appreciated!USE will work in dynamic SQL I think, e.g.
EXEC('USE somedb; EXEC someproc;')
However, if you're going to bother going in and putting USE statements or
something similar, why not just go in and put in the fully qualified name?
I don't see what you will be saving by NOT putting in the fully qualified
names...
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>I am trying to move whole bunch of stored procedures from 1 db to another.
> Instead of going into each of them and change the table names to fully
> qualified name, is there a command that I can set the current database? I
> know USE does not work in sp. Any help will be highly appreciated!|||Thanks for your response, Chew. It was just because we have too many stored
procedures that we need to move. Each of them uses so many tables. Going
into each of them and change the table names will literally kill me.
However, I do think change them to fully qualified names is the right thing
to do. At the mean time, we just want a quick solution. I tried doing
exec('use db') and it did not work.
alter procedure junk
as
begin
exec ('use abc')
select * from iv00101 where itemnmbr = '12345'
end
"Foo Man Chew" wrote:
> USE will work in dynamic SQL I think, e.g.
> EXEC('USE somedb; EXEC someproc;')
> However, if you're going to bother going in and putting USE statements or
> something similar, why not just go in and put in the fully qualified name?
> I don't see what you will be saving by NOT putting in the fully qualified
> names...
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
> >I am trying to move whole bunch of stored procedures from 1 db to another.
> > Instead of going into each of them and change the table names to fully
> > qualified name, is there a command that I can set the current database? I
> > know USE does not work in sp. Any help will be highly appreciated!
>
>|||1. Move all the stored procecures from DatabaseA to DatabaseB
2. Create views in databaseB with same name as in DatabaseA
Database A
=======TableA
CREATE PROC Proc1
AS
SELECT * FROM TableA
DatabaseB
create a View
CREATE VIEW TableA
AS
SELECT * FROM DatabaseA.dbo.TableA
Move the Procedures
This way you don't have to touch you code. All you need to do create buch of
views
Thanks
Ravi
"Ed" wrote:
> Thanks for your response, Chew. It was just because we have too many stored
> procedures that we need to move. Each of them uses so many tables. Going
> into each of them and change the table names will literally kill me.
> However, I do think change them to fully qualified names is the right thing
> to do. At the mean time, we just want a quick solution. I tried doing
> exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
> > USE will work in dynamic SQL I think, e.g.
> >
> > EXEC('USE somedb; EXEC someproc;')
> >
> > However, if you're going to bother going in and putting USE statements or
> > something similar, why not just go in and put in the fully qualified name?
> > I don't see what you will be saving by NOT putting in the fully qualified
> > names...
> >
> >
> > "Ed" <Ed@.discussions.microsoft.com> wrote in message
> > news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
> > >I am trying to move whole bunch of stored procedures from 1 db to another.
> > > Instead of going into each of them and change the table names to fully
> > > qualified name, is there a command that I can set the current database? I
> > > know USE does not work in sp. Any help will be highly appreciated!
> >
> >
> >|||Ed wrote:
> Thanks for your response, Chew. It was just because we have too many
> stored procedures that we need to move. Each of them uses so many
> tables. Going into each of them and change the table names will
> literally kill me. However, I do think change them to fully qualified
> names is the right thing to do. At the mean time, we just want a
> quick solution. I tried doing exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>> USE will work in dynamic SQL I think, e.g.
>> EXEC('USE somedb; EXEC someproc;')
>> However, if you're going to bother going in and putting USE
>> statements or something similar, why not just go in and put in the
>> fully qualified name? I don't see what you will be saving by NOT
>> putting in the fully qualified names...
>>
>> "Ed" <Ed@.discussions.microsoft.com> wrote in message
>> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>> I am trying to move whole bunch of stored procedures from 1 db to
>> another. Instead of going into each of them and change the table
>> names to fully qualified name, is there a command that I can set
>> the current database? I know USE does not work in sp. Any help
>> will be highly appreciated!
EXEC runs in its own context. Just fully qualify using DB.OWNER.SPNAME.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> exec('use db') and it did not work.
Because that is not what I said to do.
You need to do everything inside a single EXEC.
EXEC('Use somedb; EXEC someproc')

change current database in stored procedure

I am trying to move whole bunch of stored procedures from 1 db to another.
Instead of going into each of them and change the table names to fully
qualified name, is there a command that I can set the current database? I
know USE does not work in sp. Any help will be highly appreciated!USE will work in dynamic SQL I think, e.g.
EXEC('USE somedb; EXEC someproc;')
However, if you're going to bother going in and putting USE statements or
something similar, why not just go in and put in the fully qualified name?
I don't see what you will be saving by NOT putting in the fully qualified
names...
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>I am trying to move whole bunch of stored procedures from 1 db to another.
> Instead of going into each of them and change the table names to fully
> qualified name, is there a command that I can set the current database? I
> know USE does not work in sp. Any help will be highly appreciated!|||Thanks for your response, Chew. It was just because we have too many stored
procedures that we need to move. Each of them uses so many tables. Going
into each of them and change the table names will literally kill me.
However, I do think change them to fully qualified names is the right thing
to do. At the mean time, we just want a quick solution. I tried doing
exec('use db') and it did not work.
alter procedure junk
as
begin
exec ('use abc')
select * from iv00101 where itemnmbr = '12345'
end
"Foo Man Chew" wrote:

> USE will work in dynamic SQL I think, e.g.
> EXEC('USE somedb; EXEC someproc;')
> However, if you're going to bother going in and putting USE statements or
> something similar, why not just go in and put in the fully qualified name?
'
> I don't see what you will be saving by NOT putting in the fully qualified
> names...
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>
>|||1. Move all the stored procecures from DatabaseA to DatabaseB
2. Create views in databaseB with same name as in DatabaseA
Database A
=======
TableA
CREATE PROC Proc1
AS
SELECT * FROM TableA
DatabaseB
create a View
CREATE VIEW TableA
AS
SELECT * FROM DatabaseA.dbo.TableA
Move the Procedures
This way you don't have to touch you code. All you need to do create buch of
views
Thanks
Ravi
"Ed" wrote:
[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many stor
ed
> procedures that we need to move. Each of them uses so many tables. Going
> into each of them and change the table names will literally kill me.
> However, I do think change them to fully qualified names is the right thin
g
> to do. At the mean time, we just want a quick solution. I tried doing
> exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>|||Ed wrote:[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many
> stored procedures that we need to move. Each of them uses so many
> tables. Going into each of them and change the table names will
> literally kill me. However, I do think change them to fully qualified
> names is the right thing to do. At the mean time, we just want a
> quick solution. I tried doing exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>
EXEC runs in its own context. Just fully qualify using DB.OWNER.SPNAME.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> exec('use db') and it did not work.
Because that is not what I said to do.
You need to do everything inside a single EXEC.
EXEC('Use somedb; EXEC someproc')

Change attribute value in stored procedure

Can someone show me the syntax (if it is possible) to modify an attribute value in a stored proc? For example if I have an element that is being passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to change the 2 to something else (like a 3) depending on a condition? Or do I have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to change the value? Will someone
please let me know? Many Thanks!
There's no XML manipulation functionality built into SQL Server 2000 (other
than shredding the XML into a table, performing your update, and retrieving
it back out as XML again using a FOR XML query - definitely not an efficient
approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
better performing this kind of logic in the client application before
submitting it to the stored procedure (apply a style sheet or use an XML API
like the DOM or the XmlDocument in .NET).
The next release of SQL Server includes some extensions to XQuery that allow
you to modify data within an XML value, which is great if you can wait until
then, but probably of no practical use to you at the moment - sorry!
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
Can someone show me the syntax (if it is possible) to modify an attribute
value in a stored proc? For example if I have an element that is being
passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to
change the 2 to something else (like a 3) depending on a condition? Or do I
have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
change the value? Will someone please let me know? Many Thanks!
|||Thanks Graeme! That is exactly what I was looking for.
"Graeme Malcolm" wrote:

> There's no XML manipulation functionality built into SQL Server 2000 (other
> than shredding the XML into a table, performing your update, and retrieving
> it back out as XML again using a FOR XML query - definitely not an efficient
> approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
> better performing this kind of logic in the client application before
> submitting it to the stored procedure (apply a style sheet or use an XML API
> like the DOM or the XmlDocument in .NET).
> The next release of SQL Server includes some extensions to XQuery that allow
> you to modify data within an XML value, which is great if you can wait until
> then, but probably of no practical use to you at the moment - sorry!
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
> news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
> Can someone show me the syntax (if it is possible) to modify an attribute
> value in a stored proc? For example if I have an element that is being
> passed in as text like
> <CustomerName id="2" /> Is there SQLXML functionality that I can use to
> change the 2 to something else (like a 3) depending on a condition? Or do I
> have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
> change the value? Will someone please let me know? Many Thanks!
>
>

Chaging the Min and Max Scale of a Chart at run time

I have a stored procedure which will bring me back the Min, Max and Mean of different result sets. What I want to do with the Y-Axis is set the Min scale value of the chart to be Min -%5 and the Max scale value to be Max + 5%.

Is there a way to change the Y-Axis values at report run time without spitting my own RDL?

In RS 2005, the Min/Max/CrossAt/MajorInterval/MinorInterval settings can be expression based. Note that by using an aggregate function such as first you can even reference certain values from another dataset than the chart is bound to. For example, you could use an expression similar to:

=CDbl(First(Fields!MinValue.Value, "StoredProcDataset")) - 0.05

-- Robert

|||That's great Robert but it's not apparent seeing that it doesn't follow the usual standard of having the combo box with the <expression> as a selection. Worked for me though and I thank you.|||

Hi!

I want to change my y-axis into always showing integers, start with zero and show an y-axis longer than the maximum value of the points.
How do I do that in csharp?

My code is like this now: (Chart looks ok for values between 0 and 25, but makes a chart with a max value 1 have an Y axis that is divided into 0.1 and up to 1.0 ):

chartGraf.Axis.Y.TickmarkStyle = AxisTickStyle.Smart;

chartGraf.Axis.Y.LineThickness = 1;

chartGraf.Axis.Y.RangeMin = 0;

if ( MyMaximumValue < 3) {

chartGraf.Axis.Y.RangeMax = 3;

}

-Heidi

Chaging the Min and Max Scale of a Chart at run time

I have a stored procedure which will bring me back the Min, Max and Mean of different result sets. What I want to do with the Y-Axis is set the Min scale value of the chart to be Min -%5 and the Max scale value to be Max + 5%.

Is there a way to change the Y-Axis values at report run time without spitting my own RDL?

In RS 2005, the Min/Max/CrossAt/MajorInterval/MinorInterval settings can be expression based. Note that by using an aggregate function such as first you can even reference certain values from another dataset than the chart is bound to. For example, you could use an expression similar to:

=CDbl(First(Fields!MinValue.Value, "StoredProcDataset")) - 0.05

-- Robert

|||That's great Robert but it's not apparent seeing that it doesn't follow the usual standard of having the combo box with the <expression> as a selection. Worked for me though and I thank you.|||

Hi!

I want to change my y-axis into always showing integers, start with zero and show an y-axis longer than the maximum value of the points.
How do I do that in csharp?

My code is like this now: (Chart looks ok for values between 0 and 25, but makes a chart with a max value 1 have an Y axis that is divided into 0.1 and up to 1.0 ):

chartGraf.Axis.Y.TickmarkStyle = AxisTickStyle.Smart;

chartGraf.Axis.Y.LineThickness = 1;

chartGraf.Axis.Y.RangeMin = 0;

if ( MyMaximumValue < 3) {

chartGraf.Axis.Y.RangeMax = 3;

}

-Heidi

Certain rows to excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

You can easily achieve this in DTS Import/Export Wizard. Suppose you're using SQL2000, open Enterprise Manager->go to the table from which you want to export data->right click choose All Tasks-> Export Data-> in the Specify Table Copy or Query step, choose Use a Query...->enter the SELECT command (e.g. SELECT * FROM Orders WHERE EmployeeID=6), or use Query Builder->complete the wizard.

|||

Thanks for the reply, How should I run the same queries for all the IDs separately in DTS and save the results to excel files?

certain rows to Excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

Hi,

While Using DTS Import/Export Wizard,
After Choosing Data Source and DataDestination,
Wizard asks to Specify Table Copy or Query,
At the Time Select the Radio Button [Use a Query to Specify the Data To Transfer].

It will Take you To Type Sql Statement where Query Statement Panel is there ,you can Type Query with your Condition.

|||

Thanks for the reply.

This should be done once every day , so I am trying to automate it. How should I develop my query and dts based on this criteria? Any example will be greatly appreciated.

|||

Select Jobs From SQL Server Agent Under Management of the Selected Server(In EnterpriseManager)

Select New Job and Name it
Select Steps Tab in New Job Properties
Click New step and Name Step and Write Query in Command Panel
Click New Schedule and Name Schedule and Select Schedule Type

Now you Achieve the Required Thing

|||

I still do not see how the query that I am writing will export data to a different excel file based on the ID. All the rows that have the same ID should go to a single excel file, so I am expecting more than one excel file based on the distinct values of ID to be created. How can I do this in DTS?

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the sam
e
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will b
e
greatly appreciated.
"Paul Ibison" wrote:

> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use th
e
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:

> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the packag
e
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they ar
e,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I a
m
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic...926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic...926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the same
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will be
greatly appreciated.
"Paul Ibison" wrote:
> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use the
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:
> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the package
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they are,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I am
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > This table has IDs and Desc already in it. IDs might be duplicated and I am
> > trying to export the rows with the same IDs to a single excel file, so
> > expecting many excel files based on the distinct values of IDs. DTS seems
> > quite complicated to me since I have not done anything with that yet.
> >
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Centralizing Two Stored Procedure Queries (one using Cursor Ou

Alejandro,
Sorry, one more question: In your example, would there be any way to return
only a subset of the fields in the stored proc query. For example, if you
wanted a temporary table with only the orderid field. Like this:
create table #t (orderid int)
insert into #t(orderid)
exec dbo.p1 @.sd, @.ed
I'll need it like this because my store proc "spTrucker" has many, many
fields, and I only need about 4 or 5 of them in the cursor.
Thanks again,
John
"Alejandro Mesa" wrote:
> John,
> You do not need a cursor output parameter. You can grab the result of the
sp
> "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL
.
> Example:
> use northwind
> go
> create procedure dbo.p1
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> select
> orderid, orderdate, customerid
> from
> dbo.orders
> where
> orderdate >= convert(char(8), @.sd, 112)
> and orderdate < convert(char(8), dateadd(day, 1, @.ed), 112)
> return @.@.error
> go
> create procedure dbo.p2
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> declare @.orderid int
> declare @.orderdate varchar(25)
> declare @.customerid nchar(5)
> create table #t (orderid int, orderdate datetime, customerid nchar(5))
> insert into #t(orderid, orderdate, customerid)
> exec dbo.p1 @.sd, @.ed
> declare my_cursor cursor local fast_forward
> for
> select orderid, orderdate, customerid
> from #t
> order by orderdate
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.orderid, @.orderdate, @.customerid
> if @.@.error != 0 or @.@.fetch_status != 0 break
> raiserror('%d %s %s', 10, 1, @.orderid, @.orderdate, @.customerid) with nowa
it
> end
> close my_cursor
> deallocate my_cursor
> go
> exec dbo.p2 '19970701', '19970731'
> go
> drop procedure p2, p1
> go
> How to share data between stored procedures
> http://www.sommarskog.se/share_data.html
>
> AMB
>
> "John Walker" wrote:
>John,
The ddl for the temporary table need to match all columns returned by the
sp. You can not grab just a subset.
AMB
"John Walker" wrote:
> Alejandro,
> Sorry, one more question: In your example, would there be any way to retu
rn
> only a subset of the fields in the stored proc query. For example, if you
> wanted a temporary table with only the orderid field. Like this:
> create table #t (orderid int)
> insert into #t(orderid)
> exec dbo.p1 @.sd, @.ed
> I'll need it like this because my store proc "spTrucker" has many, many
> fields, and I only need about 4 or 5 of them in the cursor.
> Thanks again,
> John
> "Alejandro Mesa" wrote:
>

central place for stored procedure variables

Hello,
I have numerous stored procedures with the following. Every time I make a
change I have to update all of my stored procedures. Is there a more
efficient way of doing this?
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
Thanks in advance,
sck10Why don't you put this data in a table?
David Portas
SQL Server MVP
--|||Depending on your overall system architecture, security requirements, system
complexity and maintenance provisions, you can either have the paths
represented as values in a table within the database, or an external config
file, or in rare instances in a registry or in .ini files etc.
Anith|||sck10 wrote:
> Hello,
> I have numerous stored procedures with the following. Every time I
> make a change I have to update all of my stored procedures. Is there
> a more efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx
> varchar(300), @.strPhoto_home varchar(300),
> @.strPhoto_aspx varchar(300), @.strRightArrow_home varchar(300),
> @.strRightArrow_aspx varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img
> src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif" width="../../8"
> height="../../5">'
Put the HTML snippets in a table and write a function to access the
table by string ID. For example:
Create Table HTMLSnippet (
HTMLID CHAR(15) NOT NULL PRIMARY KEY,
HTMLSnip VARCHAR(100),
HTMLDesc VARCHAR(255))
-- insert values into table
Insert Into HTMLSnippet Values ('DOCPATHHOME, '<a href="http://links.10026.com/?link=web_doc/', 'The
Doc path home')
-- etc
Create Function dbo.GetHTMLSnippet (
@.HTMLID CHAR(15) )
RETURNS VARCHAR(100)
AS
DECLARE @.HTMLCode VARCHAR(100)
SELECT @.HTMLCode = HTMLCode From HTMLSnippet Where @.HTMLID = @.HTMLID
RETURN @.HTMLCode
-- to use function
Declare @.test varchar(1000)
Select @.test = dbo.GetHTMLSnippet('DOCPATHHOME') + ...
Not tested, but should give you a good start.
David Gugick
Imceda Software
www.imceda.com|||I agree with David, put it into a table and then SELECT the values in your
SP's. Then if they change you only have to change them in the table on
time, all your SP's will be able to use the updated info appropriately...
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>|||One more vote for using SQL Server to store data! Alternatively you might
consider building something (or finding something) to do macro's for SQL
code to have this automatically done for you (it might be slightly faster to
do what you have done rather than a table, if you have ultra-high
performance needs:
For example in your code put:
--<start macro replace-name_of_macro>
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
--<end macro replace-name_of_macro>
Now it would be easy to go through all of the files with stored procedures
in them and do a find and replace for this stuff. I would probably
suggest just using a table and cross joining to it whenever you need these
values in a few cases to try first. In the end it will be a far better
solution than putting data into variables. SQL Server does not now, and
forseeable future will not have constants like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>

CDOSYS object not working

I am now trying to send email using CDOSYS from a stored procedure on a
Windows 2003 server and Sql Server 2000.
I got this code from the net and trying to figure out why it won't work.
It seems to work ok until I do the "send".
I am getting an error:
Source: CDO.Message.1
Description: The transport failed to connect to the server.
Is this the SMTP server or the code that is using the microsoft addresses
(ie. http://schemas.microsoft.com/cdo/co...ation/sendusing)?
My code is:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%
Declare @.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/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp
****************************************
***/
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)
Select @.From = 'tfs@.ftsolutions.com',@.To = 'tfs@.ftsolutions.com', @.Subject =
'Backup Notification', @.Body='The Body of the message'
--***** Create the CDO.Message Object *****
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
print '@.hr after Creating CDO.Message'
print @.hr
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
print '@.hr after the sendUsing line'
print @.hr
-- This is to configure the Server Name or IP address.
print 'Before the Smtp Server Setup'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value',
'smtp.earth.com'
print '@.hr After setting up the SMTP server'
print @.hr
-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
print '@.hr after Update'
print @.hr
-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject
print @.hr
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
print @.hr
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL
print @.hr
-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%
All the "print @.hr" lines are all showing 0, except the last one that is
showing: -2147220973.
Thanks,
TomHave you tried using xp_smtp_sendmail instead? I believe it's easier
to use than CDOSYS.
I found these links on Google Groups:
http://www.sqldev.net/xp/sp_smtp_sendmail.htm
http://www.aspfaq.com/2403
HTH
Barry|||"Barry" <barry.oconnor@.manx.net> wrote in message
news:1139940520.616802.88260@.g47g2000cwa.googlegroups.com...
> Have you tried using xp_smtp_sendmail instead? I believe it's easier
> to use than CDOSYS.
I did get it to work if I used the IP address instead of the name address.
I was going to look at sp_smtp_sendmail when Tibor mentioned it. But I did
get CDOSYS working. Is sendmail better?
Thanks,
Tom
> I found these links on Google Groups:
> http://www.sqldev.net/xp/sp_smtp_sendmail.htm
> http://www.aspfaq.com/2403
> HTH
> Barry
>|||Personally, I've never used it. I have read about it and seems quite
good.
I searched Google and I think general opinion is that it's easier to
use and has better scalability.
I guess it's a question of personal preference.
Barry|||I was reading about it also and found that the drawback was that you need an
email client to use it. CDOSYS doesn't need this.|||xp_smtp_sendmail doesn't need an email client. The old x_sendmail does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ezp9AvZMGHA.1536@.TK2MSFTNGP11.phx.gbl...
>I was reading about it also and found that the drawback was that you need a
n email client to use
>it. CDOSYS doesn't need this.
>

CDOSYS Mail Using SQL Server

The procedure works without problems for the basic 'textbody' and 'htmlbody' type messages. The problem is I would like to get the CreateMHTMLBody method working. This works beautifully as a VBScript which I'm attempting to convert to SQL.

CREATE PROCEDURE usp_send_cdosysmail
@.From_Addr VARCHAR(500) ,
@.To_Addr VARCHAR(500) ,
@.Subject VARCHAR(500),
@.Body VARCHAR(4000) ,
@.SMTPserver VARCHAR(25) = 'localhost',
@.BodyType VARCHAR(10) = 'textbody'
AS

DECLARE @.imsg INT
DECLARE @.hr INT
DECLARE @.source VARCHAR(255)
DECLARE @.description VARCHAR(500)
DECLARE @.output VARCHAR(1000)

EXEC @.hr = sp_oacreate 'cdo.message', @.imsg out
EXEC @.hr = sp_oasetproperty @.imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
EXEC @.hr = sp_oasetproperty @.imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @.SMTPserver
EXEC @.hr = sp_oamethod @.imsg, 'configuration.fields.UPDATE', NULL

EXEC @.hr = sp_oasetproperty @.imsg, 'to', @.To_Addr
EXEC @.hr = sp_oasetproperty @.imsg, 'from', @.From_Addr
EXEC @.hr = sp_oasetproperty @.imsg, 'subject', @.Subject

IF @.BodyType <> 'URL'
BEGIN
EXEC @.hr = sp_oasetproperty @.imsg, @.BodyType, @.Body
END
ELSE
BEGIN
-- Generate The Body From The Input URL
EXEC @.hr = sp_oamethod @.imsg, 'CreateMHTMLBody', @.body, 0, '', ''
END

EXEC @.hr = sp_oamethod @.imsg, 'send', NULL

When I test the "EXEC @.hr = sp_oamethod @.imsg, 'CreateMHTMLBody', @.body, 0, '', ''" step it gives me a blank email.
The return code (@.hr) for that step is -2147211483 (x80042725).
There is no error message. Search as I might, I haven't been able to find a reference to that error.I found the answer:
EXEC @.hr = sp_OAMethod @.imsg, 'CreateMHTMLBody', NULL, @.body, 0, '',''
works!!!!

CDOSYS email body truncated

I have written a stored procedure, based on the sample code to send email.
Only problem is that the message body is truncated to 255 characters. Any
ideas? Thanks!
The procedure is listed below. [It has an extra sp_OAGetProperty statement
to retrieve and print the message body after it it set. When I run it, the
email is sent but the body is truncated (the 2 print statements show the
before and after).]
Is this an environment problem? Or am I doing something really stupid?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Send_CDOSYSMail]
@.from varchar(100),
@.to varchar(100),
@.cc varchar(100) = null,
@.bcc varchar(100) = null,
@.subject varchar(100),
@.body varchar(4000),
@.html char(1) = 'N'
/ ****************************************
*****************************
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
****************************************
*******************************/
AS
declare @.iMsg int
declare @.hr int
declare @.source varchar(255)
declare @.description varchar(500)
declare @.output varchar(1000)
-- Tidy up the parameters
set @.from = ltrim(rtrim(@.from))
set @.to = ltrim(rtrim(@.to))
set @.cc = ltrim(rtrim(@.cc))
set @.bcc = ltrim(rtrim(@.bcc))
set @.subject = ltrim(rtrim(@.subject))
set @.body = ltrim(rtrim(@.body))
-- Create the CDO.Message Object
exec @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
-- Configure the Message Object
-- ... configure a remote SMTP server
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
-- ... configure the Server Name or IP address
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value', '<mysmtpservername>'
-- 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', @.to
exec @.hr = sp_OASetProperty @.iMsg, 'From', @.from
exec @.hr = sp_OASetProperty @.iMsg, 'Cc', @.cc
exec @.hr = sp_OASetProperty @.iMsg, 'Bcc', @.bcc
exec @.hr = sp_OASetProperty @.iMsg, 'Subject', @.subject
-- Send the email
if @.html = 'Y'
begin
exec @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.body
end
else
begin
exec @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.body
end
print @.body
declare @.xbody varchar(8000)
exec @.hr = sp_OAGetProperty @.iMsg, 'TextBody', @.xbody OUT
print isnull(@.xbody,'?')
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
exec @.hr = sp_OAMethod @.iMsg, 'Send', NULL
-- error handling
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
exec @.hr = sp_OADestroy @.iMsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHello,
Based on my test, the email body is printed twice and the results are
correctly. Can you print the email body correctly?
For your reference, XPSMTP provides a SMTP based SQL Mail solution for
sending MIME based email over
SMTP, implemented as an Extended Stored Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||> Can you print the email body correctly?
No ... as I said before it is truncated to 255 characters (in the email that
is sent and in the result returned from sp_OAGetProperty).

> For your reference, XPSMTP provides a SMTP based SQL Mail solution for
> sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
Thanks, I will have to use that if there is no obvious reason for what's
going wrong.|||Cracked it, after some googling. I'd forgotten I was using SQL Server 7
(it's an ancient system I'm trying to support!) ... Apparently, there is a
limitation in SQL 7 that truncates all strings passed to COM objects to 255
characters. As a result, the datatypes of the parameters are limited to 255
characters, since only the first 255 characters will be passed to the CDOSYS
COM object. (It's a limitation of the sp_OA... methods.) This is no good fo
r
the body, so the work around is to use a TEXT field for this. Don't know
what the upper limit is, but it seems to work and certainly goes beyond the
255 character limit.|||Hello,
I don't know you are using SQL server 7 and I tested the issue on SQL
server 2000. I found the following articles address the issue:
278448 BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters
http://support.microsoft.com/?id=278448
324146 FIX: SQL Server 7.0 sp_OASetProperty Method Does Not Accept More Than
http://support.microsoft.com/?id=324146
The following information in the BOL is for your reference:
text
Variable-length non-Unicode data in the code page of the server and with a
maximum length of 231-1 (2,147,483,647) characters. When the server code
page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than
2,147,483,647 bytes.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

CDONS.mail not working

I have a procedure that sends mail using CDONTS.mail
CREATE PROCEDURE SendMail(
@.From varchar(255),
@.To varchar(255),
@.Message varchar(8000),
@.Subject varchar(255))
AS
DECLARE @.CDO int, @.OLEResult int, @.Out int
--Create CDONTS.NewMail object
EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
--Call Send method of the object
execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
@.Subject, @.Message, 1 --0 is low 1 is normal
IF @.OLEResult <> 0 PRINT 'Send'
--Destroy CDO
EXECUTE @.OLEResult = sp_OADestroy @.CDO
return @.OLEResult
It runs well in a server but fails in other server
When I executed it in QA, it displays "The command(s) completed
successfully." without sending the mail
Is that server missing any settings?
MadhivananDon't use CDONTS.NewMail, it's been deprecated and no longer ships with
Windows.
My suggestion is to set up an SMTP server and use xp_smtp_sendmail. Barring
that, use CDO.Message. See http://www.aspfaq.com/2403 for more details on
both methods (note that in the working sample, each property is set
individually, whereas you attempt to pass a bunch of properties into a
single call).
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1136812148.799884.141170@.g49g2000cwa.googlegroups.com...
> I have a procedure that sends mail using CDONTS.mail
>
> CREATE PROCEDURE SendMail(
> @.From varchar(255),
> @.To varchar(255),
> @.Message varchar(8000),
> @.Subject varchar(255))
> AS
> DECLARE @.CDO int, @.OLEResult int, @.Out int
> --Create CDONTS.NewMail object
> EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
> IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
>
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
> --Call Send method of the object
> execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
> @.Subject, @.Message, 1 --0 is low 1 is normal
> IF @.OLEResult <> 0 PRINT 'Send'
> --Destroy CDO
> EXECUTE @.OLEResult = sp_OADestroy @.CDO
> return @.OLEResult
> It runs well in a server but fails in other server
> When I executed it in QA, it displays "The command(s) completed
> successfully." without sending the mail
> Is that server missing any settings?
> Madhivanan
>

Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar