maintenance plan

Im building a new sql server and would like to use my existing maintenance
plans. Where are the files stored and how do I use them on the new server?
Thanks,
HowardHi,
Maintenece plans will be stored in MSDB database.So if you restore the MSDB
database; then you should be able to
get all Maintenance plans and jobs.
If the SQL Server name is different from old server then you may need to
change the Originating server name in Sysjobs table.
Thanks
Hari
SQL Server MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:uv9r$rHxGHA.3904@.TK2MSFTNGP02.phx.gbl...
> Im building a new sql server and would like to use my existing maintenance
> plans. Where are the files stored and how do I use them on the new server?
> Thanks,
> Howard
> shou

maintenance plan

Im building a new sql server and would like to use my existing maintenance
plans. Where are the files stored and how do I use them on the new server?
Thanks,
HowardHi,
Maintenece plans will be stored in MSDB database.So if you restore the MSDB
database; then you should be able to
get all Maintenance plans and jobs.
If the SQL Server name is different from old server then you may need to
change the Originating server name in Sysjobs table.
Thanks
Hari
SQL Server MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:uv9r$rHxGHA.3904@.TK2MSFTNGP02.phx.gbl...
> Im building a new sql server and would like to use my existing maintenance
> plans. Where are the files stored and how do I use them on the new server?
> Thanks,
> Howard
> shou

Maintaining Variable After EXEC

Hello,

I am fairly new at stored procedures. I have created some that will
go through a table and return a start date and an end date that is
dependent upon the fiscal period you want, but I then need to use
those dates in another stored procedure to retrieve the information I
need. My stored procedure looks like this.

================================================== ====================

CREATE PROCEDURE dbo.R920ExtTotal
@.MthsBack Decimal OUTPUT
AS

DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
as SMALLDATETIME

Exec @.StartDate = GetMthStart @.MthsBack

Exec @.EndDate = GetMthEnd @.MthsBack

SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
([date] BETWEEN "' +
CONVERT(nvarchar, @.StartDate) +
'" and "' +
CONVERT(nvarchar, @.EndDate) +
'")'

Select @.sSQL

EXEC (@.sSQL)

Return
GO

================================================== ===============

The problem is my variables @.StartDate and @.EndDate do not retain
their values after the EXEC statement and revert to 01/01/1900. How
can I get around this problem?

Thanks!!!!

ChipVariables are only available in the scope within which they are declared. If
you need to move values out of a stored procedure, you must use output
parameters for all of them. Also, why are you building dynamic SQL in your
procedure? You can use the variables directly in SQL queries - there is no
need to convert them to strings, and insert them into a SQL string.

On 6 Jan 2004 11:05:01 -0800, cmayan@.lesliecontrols.com (Chip Mayan) wrote:

>Hello,
>I am fairly new at stored procedures. I have created some that will
>go through a table and return a start date and an end date that is
>dependent upon the fiscal period you want, but I then need to use
>those dates in another stored procedure to retrieve the information I
>need. My stored procedure looks like this.
>================================================== ====================
>CREATE PROCEDURE dbo.R920ExtTotal
>@.MthsBack Decimal OUTPUT
>AS
>DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
>as SMALLDATETIME
>Exec @.StartDate = GetMthStart @.MthsBack
>Exec @.EndDate = GetMthEnd @.MthsBack
>SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
>([date] BETWEEN "' +
>CONVERT(nvarchar, @.StartDate) +
>'" and "' +
>CONVERT(nvarchar, @.EndDate) +
>'")'
>Select @.sSQL
>EXEC (@.sSQL)
>Return
>GO
>================================================== ===============
>The problem is my variables @.StartDate and @.EndDate do not retain
>their values after the EXEC statement and revert to 01/01/1900. How
>can I get around this problem?
>Thanks!!!!
>Chip|||Chip Mayan (cmayan@.lesliecontrols.com) writes:
> I am fairly new at stored procedures. I have created some that will
> go through a table and return a start date and an end date that is
> dependent upon the fiscal period you want, but I then need to use
> those dates in another stored procedure to retrieve the information I
> need. My stored procedure looks like this.
>================================================== ====================
> CREATE PROCEDURE dbo.R920ExtTotal
> @.MthsBack Decimal OUTPUT
> AS
> DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
> as SMALLDATETIME
> Exec @.StartDate = GetMthStart @.MthsBack
> Exec @.EndDate = GetMthEnd @.MthsBack
> SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
> ([date] BETWEEN "' +
> CONVERT(nvarchar, @.StartDate) +
> '" and "' +
> CONVERT(nvarchar, @.EndDate) +
> '")'
> Select @.sSQL
> EXEC (@.sSQL)
> Return
> GO

I'm afraid that there are a couple of errors or strange things in this
procedure.

First: there is absolutely no reason to use dynamic SQL here. Just write:

SELKCT count(extension) AS Total
FROM r920f00
WHERE [date] BETWEEN @.StartDate AND @.EndDate

Second: the calls to set @.StartDate and @.EndDate looks funny. If
GetMthStart and GetMthEnd are user-defined functions it would be alright,
but you indicated that they were stored procedures. The return value from
a stored procedure is always an integer value, so you cannot return a
date here. And I would strongly recommend you to use return values solely
for indication of success/failure (with 0 meaning success, and about
everything else meaning failure.) So you would have to make the output
parameters:

EXEC GetMthStart @.MthsBack, @.StartDate OUTPUT

Third: the @.MthsBack parameter is declared as output, but you never assign
it any value, you only seem to use it for input.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Main report drive Subreports

Will SRS support passing parameters from the main report to the subreport
which calls a stored proc that does not contain any parameters ? it will
automatically filter the data without the where clause.
I used to be able to do this in Crystal reports which I don't need to
specify a where clause in my subreport stored proc and I am not sure if SRS
will support this and I hope that I don't need to rewrite all the stored proc
for this.
Need help on this one !
ThanksRS has two different ways of filtering the data. One is to use it as a query
filter. In this case the parameter would either part of the query or passed
to a stored procedure. SQL Server would then only return the necessary data
to the report. The other possibility is to use a filter. The filter has RS
filter the data. Whatever data is defined in the dataset goes to the report
and then the report filters the data. I stay away from this because it is
much more efficient for the database to send only the necessary records to
Reporting Services. However, it sound like you want to use filters.
You would do the following. Create a subreport with a parameter. Use the
parameter in your filter. When you embed the subreport into the report then
do a right mouse click on the subreport and map the report parameter to
whatever is appropriate on the main report.
So in your case the subreport has a dataset based on a stored procedure that
has no parameters. That dataset returns the data. Then using the report
parameter passed into it by the main report, the report takes the data from
the stored procedure and filters it.
Read up in book online on filtering. You can apply the filter in multiple
spots. The value you want to filter on would be an expression which is where
you would then reference the report parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric Hu" <EricHu@.discussions.microsoft.com> wrote in message
news:132A8634-04DA-4688-8C40-F53513085D43@.microsoft.com...
> Will SRS support passing parameters from the main report to the subreport
> which calls a stored proc that does not contain any parameters ? it will
> automatically filter the data without the where clause.
> I used to be able to do this in Crystal reports which I don't need to
> specify a where clause in my subreport stored proc and I am not sure if
> SRS
> will support this and I hope that I don't need to rewrite all the stored
> proc
> for this.
> Need help on this one !
> Thanks|||Brian,
In my report parameter, it's either a single area or all areas. The filter
works good with one area, but when I select all areas I want to have all
recordset coming back. Is there a way to conditionally turn the filter on or
off ? I only need to filter if the user select a single area.
Thanks,
Eric
"Bruce L-C [MVP]" wrote:
> RS has two different ways of filtering the data. One is to use it as a query
> filter. In this case the parameter would either part of the query or passed
> to a stored procedure. SQL Server would then only return the necessary data
> to the report. The other possibility is to use a filter. The filter has RS
> filter the data. Whatever data is defined in the dataset goes to the report
> and then the report filters the data. I stay away from this because it is
> much more efficient for the database to send only the necessary records to
> Reporting Services. However, it sound like you want to use filters.
> You would do the following. Create a subreport with a parameter. Use the
> parameter in your filter. When you embed the subreport into the report then
> do a right mouse click on the subreport and map the report parameter to
> whatever is appropriate on the main report.
> So in your case the subreport has a dataset based on a stored procedure that
> has no parameters. That dataset returns the data. Then using the report
> parameter passed into it by the main report, the report takes the data from
> the stored procedure and filters it.
> Read up in book online on filtering. You can apply the filter in multiple
> spots. The value you want to filter on would be an expression which is where
> you would then reference the report parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric Hu" <EricHu@.discussions.microsoft.com> wrote in message
> news:132A8634-04DA-4688-8C40-F53513085D43@.microsoft.com...
> > Will SRS support passing parameters from the main report to the subreport
> > which calls a stored proc that does not contain any parameters ? it will
> > automatically filter the data without the where clause.
> >
> > I used to be able to do this in Crystal reports which I don't need to
> > specify a where clause in my subreport stored proc and I am not sure if
> > SRS
> > will support this and I hope that I don't need to rewrite all the stored
> > proc
> > for this.
> >
> > Need help on this one !
> >
> > Thanks
>
>|||The filter is an expression. You should be able to do this.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric Hu" <EricHu@.discussions.microsoft.com> wrote in message
news:00C86BEC-B4EC-4A91-8E09-D4C549915CD7@.microsoft.com...
> Brian,
> In my report parameter, it's either a single area or all areas. The filter
> works good with one area, but when I select all areas I want to have all
> recordset coming back. Is there a way to conditionally turn the filter on
> or
> off ? I only need to filter if the user select a single area.
> Thanks,
> Eric
> "Bruce L-C [MVP]" wrote:
>> RS has two different ways of filtering the data. One is to use it as a
>> query
>> filter. In this case the parameter would either part of the query or
>> passed
>> to a stored procedure. SQL Server would then only return the necessary
>> data
>> to the report. The other possibility is to use a filter. The filter has
>> RS
>> filter the data. Whatever data is defined in the dataset goes to the
>> report
>> and then the report filters the data. I stay away from this because it is
>> much more efficient for the database to send only the necessary records
>> to
>> Reporting Services. However, it sound like you want to use filters.
>> You would do the following. Create a subreport with a parameter. Use the
>> parameter in your filter. When you embed the subreport into the report
>> then
>> do a right mouse click on the subreport and map the report parameter to
>> whatever is appropriate on the main report.
>> So in your case the subreport has a dataset based on a stored procedure
>> that
>> has no parameters. That dataset returns the data. Then using the report
>> parameter passed into it by the main report, the report takes the data
>> from
>> the stored procedure and filters it.
>> Read up in book online on filtering. You can apply the filter in multiple
>> spots. The value you want to filter on would be an expression which is
>> where
>> you would then reference the report parameter.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Eric Hu" <EricHu@.discussions.microsoft.com> wrote in message
>> news:132A8634-04DA-4688-8C40-F53513085D43@.microsoft.com...
>> > Will SRS support passing parameters from the main report to the
>> > subreport
>> > which calls a stored proc that does not contain any parameters ? it
>> > will
>> > automatically filter the data without the where clause.
>> >
>> > I used to be able to do this in Crystal reports which I don't need to
>> > specify a where clause in my subreport stored proc and I am not sure if
>> > SRS
>> > will support this and I hope that I don't need to rewrite all the
>> > stored
>> > proc
>> > for this.
>> >
>> > Need help on this one !
>> >
>> > Thanks
>>|||Thanks for the quick reply. I am not sure how to do this.
The way the filter works is that:
In Expression, type or select the expression for the field that you want the
filter to evaluate.
In Operator, select the operator that you want the filter to use to compare
the evaluated field and the value.
In Value, type the expression or value against which you want the filter to
evaluate the value in Expression.
Seem like you always need to assign a value to a filter, so in the case of
all values , I don't want to have a filter and how will this work with
expression ?
"Bruce L-C [MVP]" wrote:
> The filter is an expression. You should be able to do this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric Hu" <EricHu@.discussions.microsoft.com> wrote in message
> news:00C86BEC-B4EC-4A91-8E09-D4C549915CD7@.microsoft.com...
> > Brian,
> >
> > In my report parameter, it's either a single area or all areas. The filter
> > works good with one area, but when I select all areas I want to have all
> > recordset coming back. Is there a way to conditionally turn the filter on
> > or
> > off ? I only need to filter if the user select a single area.
> >
> > Thanks,
> > Eric
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> RS has two different ways of filtering the data. One is to use it as a
> >> query
> >> filter. In this case the parameter would either part of the query or
> >> passed
> >> to a stored procedure. SQL Server would then only return the necessary
> >> data
> >> to the report. The other possibility is to use a filter. The filter has
> >> RS
> >> filter the data. Whatever data is defined in the dataset goes to the
> >> report
> >> and then the report filters the data. I stay away from this because it is
> >> much more efficient for the database to send only the necessary records
> >> to
> >> Reporting Services. However, it sound like you want to use filters.
> >>
> >> You would do the following. Create a subreport with a parameter. Use the
> >> parameter in your filter. When you embed the subreport into the report
> >> then
> >> do a right mouse click on the subreport and map the report parameter to
> >> whatever is appropriate on the main report.
> >>
> >> So in your case the subreport has a dataset based on a stored procedure
> >> that
> >> has no parameters. That dataset returns the data. Then using the report
> >> parameter passed into it by the main report, the report takes the data
> >> from
> >> the stored procedure and filters it.
> >>
> >> Read up in book online on filtering. You can apply the filter in multiple
> >> spots. The value you want to filter on would be an expression which is
> >> where
> >> you would then reference the report parameter.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Eric Hu" <EricHu@.discussions.microsoft.com> wrote in message
> >> news:132A8634-04DA-4688-8C40-F53513085D43@.microsoft.com...
> >> > Will SRS support passing parameters from the main report to the
> >> > subreport
> >> > which calls a stored proc that does not contain any parameters ? it
> >> > will
> >> > automatically filter the data without the where clause.
> >> >
> >> > I used to be able to do this in Crystal reports which I don't need to
> >> > specify a where clause in my subreport stored proc and I am not sure if
> >> > SRS
> >> > will support this and I hope that I don't need to rewrite all the
> >> > stored
> >> > proc
> >> > for this.
> >> >
> >> > Need help on this one !
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>

mail stored procedure- message -2147220975

i'm using a stored procedure that sends mail using the OA extended
procedures. it works fine on a number of servers.
Using Query analyzer, when executed the above message number shows up on the
messages tab. What's this? I've verified the stored procedure compiled and
the syntax check passes.
Using "xp_sendmail"
"Microsoft? SQL Server? provides a set of extended stored procedures that allow SQL Server to operate as a workgroup post office for a MAPI-enabled e-mail system."
Excuse me if this is not necessary, but have you tested the mail agent in SQL Server (SQL Mail) to determine if it is configured correctly and can send mail?
Another option is to cut and paste the sendmail in the stored procedure to find out if it for any syntac errors.
I use xp_sendmail in several stored procedures to alert me if a job fails and for other reasons. I haven't encountered this particular error message, but maybe my suggestions will help?
Jon
Message posted via http://www.sqlmonster.com

mail stored procedure- message -2147220975

i'm using a stored procedure that sends mail using the OA extended
procedures. it works fine on a number of servers.
Using Query analyzer, when executed the above message number shows up on the
messages tab. What's this? I've verified the stored procedure compiled and
the syntax check passes.Using "xp_sendmail"
"Microsoft? SQL Server? provides a set of extended stored procedures that al
low SQL Server to operate as a workgroup post office for a MAPI-enabled e-ma
il system."
Excuse me if this is not necessary, but have you tested the mail agent in SQ
L Server (SQL Mail) to determine if it is configured correctly and can send
mail?
Another option is to cut and paste the sendmail in the stored procedure to f
ind out if it for any syntac errors.
I use xp_sendmail in several stored procedures to alert me if a job fails an
d for other reasons. I haven't encountered this particular error message, b
ut maybe my suggestions will help?
Jon
Message posted via http://www.droptable.com

mail stored procedure- message -2147220975

i'm using a stored procedure that sends mail using the OA extended
procedures. it works fine on a number of servers.
Using Query analyzer, when executed the above message number shows up on the
messages tab. What's this? I've verified the stored procedure compiled and
the syntax check passes.Using "xp_sendmail"
"Microsoft? SQL Server? provides a set of extended stored procedures that allow SQL Server to operate as a workgroup post office for a MAPI-enabled e-mail system."
Excuse me if this is not necessary, but have you tested the mail agent in SQL Server (SQL Mail) to determine if it is configured correctly and can send mail?
Another option is to cut and paste the sendmail in the stored procedure to find out if it for any syntac errors.
I use xp_sendmail in several stored procedures to alert me if a job fails and for other reasons. I haven't encountered this particular error message, but maybe my suggestions will help?
Jon
--
Message posted via http://www.sqlmonster.com

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job'
thanks
IanHi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job'
> thanks
> Ian|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:
>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job'
>thanks
>Ian|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:
> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> > Hi
> >
> > I recently had a big problem with an important job, after sending an email
> > using the msdb..sp_send_dbmail stored procedure the job succeeded!
> > BUT
> > the emails never went out! because the email server was problematic at
> > roughly the same time. This is the error that was logged in the sql 2005
> > email job log.:
> > No connection could be made because the target machine avtively refused
> > it.
> > mail server failure ... bla bla bla
> >
> > When i reran the job a couple of hours later the mails went out because
> > the
> > mail server was up and running.
> >
> > My question - is there not a way to tell the mail profile to retry sending
> > the email a couple of times with say 1 minute intervals between retries -
> > same as in a sql 2005 job'
> >
> > thanks
> >
> > Ian
>
>|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:
> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
> >Hi
> >
> >I recently had a big problem with an important job, after sending an email
> >using the msdb..sp_send_dbmail stored procedure the job succeeded!
> >BUT
> >the emails never went out! because the email server was problematic at
> >roughly the same time. This is the error that was logged in the sql 2005
> >email job log.:
> >No connection could be made because the target machine avtively refused it.
> >mail server failure ... bla bla bla
> >
> >When i reran the job a couple of hours later the mails went out because the
> >mail server was up and running.
> >
> >My question - is there not a way to tell the mail profile to retry sending
> >the email a couple of times with say 1 minute intervals between retries -
> >same as in a sql 2005 job'
> >
> >thanks
> >
> >Ian
>

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job?
thanks
Ian
Hi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job?
> thanks
> Ian
|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job?
>thanks
>Ian
|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>
|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job'
thanks
IanHi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job'
> thanks
> Ian|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job'
>thanks
>Ian|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded the
n
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>

Mail Id-how to store?

Hi,
How to store email address in SQL Server and is there a special data type
for it and whats the maximum limit that can be stored?
Kindly help me.
Regards,
ShyamHi,
Use the data type VARCHAR. The maximum limit is 8000 characters. I recomment
you to have 120 bytes for email storage maximum.
Declare @.emailid varchar(120)
Tahnks
Hari
SQL Server MVP
"Shyam" <Shyam@.discussions.microsoft.com> wrote in message
news:412215F3-FF74-4BFB-988B-7A2C55E2812D@.microsoft.com...
> Hi,
> How to store email address in SQL Server and is there a special data type
> for it and whats the maximum limit that can be stored?
> Kindly help me.
> Regards,
> Shyam|||See if this helps:
http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Shyam" <Shyam@.discussions.microsoft.com> wrote in message
news:412215F3-FF74-4BFB-988B-7A2C55E2812D@.microsoft.com...
Hi,
How to store email address in SQL Server and is there a special data type
for it and whats the maximum limit that can be stored?
Kindly help me.
Regards,
Shyam

Mail attachment with CDOSYS

Hi,
I am using the following stored procedure to send a mail with attachment. But the mail is sent without the attachment. Can anyone help me?

CREATE PROCEDURE DBO.sp_Send_Mail_test(
@.p_From as nvarchar(50),
@.p_To as nvarchar(50),
@.p_Subject as nvarchar(255),
@.p_Body as varchar(1000),
@.p_CC as text = null,
@.p_BCC as text = null,
@.p_Attachment varchar(500)=null
)
AS
Declare @.Message int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)

EXEC @.hr = sp_OACreate 'CDO.Message', @.Message OUT

EXEC @.hr = sp_OASetProperty @.Message, 'From',@.p_From

EXEC @.hr = sp_OASetProperty @.Message, 'To', @.p_To

EXEC @.hr = sp_OASetProperty @.Message, 'Subject', @.p_Subject

EXEC @.hr = sp_OASetProperty @.Message, 'TextBody', @.p_Body

EXEC @.hr = sp_OAMethod @.Message, 'CDO.Message.Attachment.Update', Default, @.p_Attachment

If @.p_CC is not null
BEGIN
EXEC @.hr = sp_OASetProperty @.Message, 'CC',@.p_CC
END

If @.p_BCC is not null
BEGIN
EXEC @.hr = sp_OASetProperty @.Message, 'BCC',@.p_BCC
END

EXEC @.hr = sp_OAMethod @.Message, 'Send', NULL

EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description out

EXEC @.hr = sp_OADestroy @.Message
IF @.hr <> 0
BEGIN
SELECT hr=convert(varbinary(4),@.hr), Source=@.source, Description=@.description
RETURN
END

Regards,
Bharathram GMy first guess would be that you didn't use a UNC for the file name.

I'm moving this post to the SQL Server Forum for you... I think that you'll get a lot better responses there than you will in a "pure SQL" forum.

-PatP

Macro

Has anyone ever tried to run a stored procedure through a macro before?? And If so do you get a message that says that the stored procedure exectuted successfully but a macro Halt macro message that says "Action Failed"?? Now the stored procedure executes just fine its just that the macro itself is having some trouble. Can anyone help me?? pleaseFrom where you're trying to execute, I've never come across in SQL server.|||Probably way off here but is it possible a return value that is expected but not given?|||This is the stored procedure I am using

CREATE PROCEDURE [InsertTerms]
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @.@.Error <> '0'
RETURN
GO

this is the macro that executes it|||Why not just use a pass thu?|||IF @.@.Error <> '0'
RETURN
GO

In your case, 0 is always returned. Does the data get inserted? Does the macro always indicate "Action Failed"?|||Yes the data does get inserted, and yes the macro is always indicates failed action|||Access xp has no problems with this, access 2000 apparently has a problem|||Do the sprocs get listed in your macro drop down list?

I betcha it's a connectivity thing...

Macro

Our IT department says to me all a stored procedure is, is a Macro. This is what Our network tech says to me. There are days I just want slap these people. This is the same one that made the Flat File comment, I think databases are very important and can be very complex I get annoyed when they IT people make them seems as if they are of no importance at all.

Venting again sorryHey Pat, it's your turf! Tell'em!|||An operating system is really just a big assembler macro to support the important stuff that users really need (applications). When you are dealing with "experts" that make those kind of comments and expect you to take them seriously, it is a sure sign of trouble.

While you can use a stored procedure much like a macro, you can use a C++ compiler the same way. That doesn't really say squat about the abilities of either the stored procedure or the compiler, just about how they are being used at the moment.

You just need to give the net-weenies the credit they are due. If they earn your respect, that's a good thing. If not, adapt and move on.

-PatP|||Thank you Pat, your absolutely right. Its not even worth the stress, I think what annoyed me the most is how he made it seem as though it was a demeaning and minuscucle task. These people make comments like this on a daily basis, they think DBA's are a waste of time and money. Whatever|||I get the feeling of deja vu all over again. We've had this discussion before, haven't we?

The hard-core net-weenie likes to think that they are masters of all that they survey. They like it even better when they can convince others that they are masters of all that they survey. It takes a lot to bring them back to the real world, and some of them won't survive the trip.

They've lost sight of the fact that we geeks simply make it possible for the average joe users to get their jobs done. True that a real geek can make it possible for a thousand users to do the work of 10,000 users without the geek, but they are still just a geek when you get right down to it.

A good geek that knows their stuff, and works hard to improve life for everyone around them is incredibly valuable. An average geek that does a respectable job is still quite valuable. A PITA is a PITA, regardless of their GQ (Geek Quotient).

Keep in mind that a geek almost never provides a deliverable of any kind to the client, so by themselves a geek is worth nothing at all to the company. The only real value that a geek has is what they can do to make life easier/faster/more productive for the average joes. A geek that stands in the way of the average joe (unless they are enforcing legal or company policy limits), is probably a liability instead of an asset.

-PatP|||thank you Pat that valuable information for someone like me who is new to the World of the "Geeks". Well fine I'll just adapt and move on. Even though I'm just a Mini Geek and soon to be a Major Geek, I'm Dam Proud of it. Of course I'm one of those females who pushes the cute guys away, in order to get a better view of Programming Geeks.

LOL :)|||What Dam are you proud of? Or, what dam describes the level of pride you feel.

I'm a confused geek.|||Now Derrick! I suspect the lady meant the other flavor of damn, and not only that, but you darn well know it! ;)

-PatP|||Oh why do I even bother...I'm dam proud to be a GEEK...lol

Oh Nevermind|||Sort of like the first time I heard my dentist talking about a "rubber dam"... Heck, I always KNEW those things were dams!

-PatP