Mailing Result Set Rows

In the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1071904&SiteID=1 mike.groh stated that you would define the result set as a data set variable through an execute sql task. He then pushed the variable out as a dataset type. (ds = CType(Dts.Variables("Email_CurrentDataset").Value, DataSet)

How would this type of object be declared as a user variable? Is it of type object? What is the logic behind converting a result set to a data set?

Specifically I am having problems with converting / casting the result set to a data set in that I am getting an error stating that it cannot convert the com object to a data.dataset class...

Imports ADODB

Imports System

Imports System.Xml

Imports System.Collections

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts

Imports Microsoft.SqlServer.Dts.DtsClient

PublicClass ScriptMain

PublicSub Main()

Dim ds As DataSet

Dim dr As DataRow

Dim str AsString

Dim dtm AsString

Dim s AsString

Try

ds = CType(Dts.Variables("resultSet").Value, DataSet) ' <-- Error on converting

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

EndTry

Here's the code to convert an ADO resultset to an ADO.NET dataset.

Code Snippet

Dim oledbAdapter As New OleDb.OleDbDataAdapter
Dim dataTable As New System.Data.DataTable

oledbAdapter.Fill(dataTable, Me.Variables.resultset)

Mailing Reports

Hi,
I am rendering a report in PDF Format when a user needs in my Web Form(on
click of a button)
When this report is rendered in PDF format, Is there a way to send him the
report by Email.
An Email has to be sent when a report is rendered by a particular user.
Is there an option for this...
Thanks
KiranKiran:
I was coming in to ask a similar question. I know what you want to do can be
done through subscriptions, and I have looked at the database table where
subscriptions are stored and I believe it would just be a matter of inserting
a onetime subscription line into the subscription table. But what I want to
know is there any resource out there taht tells you how to add lines to the
subscription table. I have been thinking of purchasing hitchhikers guide but
I am unsure if it is going to help me with anything I do not already know.
"Kiran" wrote:
> Hi,
> I am rendering a report in PDF Format when a user needs in my Web Form(on
> click of a button)
> When this report is rendered in PDF format, Is there a way to send him the
> report by Email.
> An Email has to be sent when a report is rendered by a particular user.
> Is there an option for this...
> Thanks
> Kiran
>
>|||Since you have your own webform here is another option for you (although
doing a one time subscription will work as well) but you might find this
easier. Save the PDF as a file and then send it using the following SMTP
client. This SMTP client requires no install of any mail client. You need to
have a SMTP server somewhere for this to work. It is very easy to use and is
widely used in the SQL Server community.
http://sqldev.net/xp/xpsmtp.htm
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kiran" <Kiran@.nospam.net> wrote in message
news:%23Tr1E%232DFHA.512@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am rendering a report in PDF Format when a user needs in my Web Form(on
> click of a button)
> When this report is rendered in PDF format, Is there a way to send him the
> report by Email.
> An Email has to be sent when a report is rendered by a particular user.
> Is there an option for this...
> Thanks
> Kiran
>|||Hi Bruce,
How will I save the PDF on the server so as to mail it.
Thanks
Kiran
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eBl8qd3DFHA.576@.TK2MSFTNGP15.phx.gbl...
> Since you have your own webform here is another option for you (although
> doing a one time subscription will work as well) but you might find this
> easier. Save the PDF as a file and then send it using the following SMTP
> client. This SMTP client requires no install of any mail client. You need
> to
> have a SMTP server somewhere for this to work. It is very easy to use and
> is
> widely used in the SQL Server community.
> http://sqldev.net/xp/xpsmtp.htm
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Kiran" <Kiran@.nospam.net> wrote in message
> news:%23Tr1E%232DFHA.512@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I am rendering a report in PDF Format when a user needs in my Web Form(on
>> click of a button)
>> When this report is rendered in PDF format, Is there a way to send him
>> the
>> report by Email.
>> An Email has to be sent when a report is rendered by a particular user.
>> Is there an option for this...
>> Thanks
>> Kiran
>>
>|||Bruce; That was very helpful.. but is there a reference to where I can add
the subscription to the database through my application?
thanks
"Bruce L-C [MVP]" wrote:
> Since you have your own webform here is another option for you (although
> doing a one time subscription will work as well) but you might find this
> easier. Save the PDF as a file and then send it using the following SMTP
> client. This SMTP client requires no install of any mail client. You need to
> have a SMTP server somewhere for this to work. It is very easy to use and is
> widely used in the SQL Server community.
> http://sqldev.net/xp/xpsmtp.htm
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Kiran" <Kiran@.nospam.net> wrote in message
> news:%23Tr1E%232DFHA.512@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > I am rendering a report in PDF Format when a user needs in my Web Form(on
> > click of a button)
> >
> > When this report is rendered in PDF format, Is there a way to send him the
> > report by Email.
> >
> > An Email has to be sent when a report is rendered by a particular user.
> >
> > Is there an option for this...
> >
> > Thanks
> > Kiran
> >
> >
>
>|||To add a subscription from you web app you have to use soap (web services)
if you have been using URL integration then you need to learn how to use the
web services to accomplish this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
news:162C8BD3-843B-44FE-A968-3A08770105E4@.microsoft.com...
> Bruce; That was very helpful.. but is there a reference to where I can add
> the subscription to the database through my application?
> thanks
> "Bruce L-C [MVP]" wrote:
> > Since you have your own webform here is another option for you (although
> > doing a one time subscription will work as well) but you might find this
> > easier. Save the PDF as a file and then send it using the following SMTP
> > client. This SMTP client requires no install of any mail client. You
need to
> > have a SMTP server somewhere for this to work. It is very easy to use
and is
> > widely used in the SQL Server community.
> >
> > http://sqldev.net/xp/xpsmtp.htm
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Kiran" <Kiran@.nospam.net> wrote in message
> > news:%23Tr1E%232DFHA.512@.TK2MSFTNGP15.phx.gbl...
> > > Hi,
> > >
> > > I am rendering a report in PDF Format when a user needs in my Web
Form(on
> > > click of a button)
> > >
> > > When this report is rendered in PDF format, Is there a way to send him
the
> > > report by Email.
> > >
> > > An Email has to be sent when a report is rendered by a particular
user.
> > >
> > > Is there an option for this...
> > >
> > > Thanks
> > > Kiran
> > >
> > >
> >
> >
> >|||I know it is possible but I haven't been programming asp.net for awhile now
so I can't help you with that.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kiran" <Kiran@.nospam.net> wrote in message
news:%230kCjB4DFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Hi Bruce,
> How will I save the PDF on the server so as to mail it.
> Thanks
> Kiran
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eBl8qd3DFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Since you have your own webform here is another option for you (although
> > doing a one time subscription will work as well) but you might find this
> > easier. Save the PDF as a file and then send it using the following SMTP
> > client. This SMTP client requires no install of any mail client. You
need
> > to
> > have a SMTP server somewhere for this to work. It is very easy to use
and
> > is
> > widely used in the SQL Server community.
> >
> > http://sqldev.net/xp/xpsmtp.htm
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Kiran" <Kiran@.nospam.net> wrote in message
> > news:%23Tr1E%232DFHA.512@.TK2MSFTNGP15.phx.gbl...
> >> Hi,
> >>
> >> I am rendering a report in PDF Format when a user needs in my Web
Form(on
> >> click of a button)
> >>
> >> When this report is rendered in PDF format, Is there a way to send him
> >> the
> >> report by Email.
> >>
> >> An Email has to be sent when a report is rendered by a particular user.
> >>
> >> Is there an option for this...
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>|||I have a similar question.
I am using the email provider available out of the box with reporting
services. I am on a flat network using 2 separated domains (resource and
user)and Routed IP schemes. The companies email is handled offsite using an
SMTP server that requires the user to login. There does not appear to be an
option to do this using the out of the box delivery method. And
unfortunately I do not know enough about setting up my own SMTP server
locally. I am trying to mail the reports from the resource domain to the
user domain.
--Eric Cathell, MCSA
"Kiran" <Kiran@.nospam.net> wrote in message
news:%23Tr1E%232DFHA.512@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am rendering a report in PDF Format when a user needs in my Web Form(on
> click of a button)
> When this report is rendered in PDF format, Is there a way to send him the
> report by Email.
> An Email has to be sent when a report is rendered by a particular user.
> Is there an option for this...
> Thanks
> Kiran
>

Mailing Lists

Hi All

We are running SQL Server & Outlook with an exchange server. We are
looking for a way to import the address book & email details into a
database table.

We have managed to do this with MS Access by using the import option
exchange(). Is there a similar way this can be done in SQLServer 2000"Mick Kehoe" <mick@.kehoe.demon.co.uk> wrote in message
news:86917a0f.0409220431.21399510@.posting.google.c om...
> Hi All
> We are running SQL Server & Outlook with an exchange server. We are
> looking for a way to import the address book & email details into a
> database table.
> We have managed to do this with MS Access by using the import option
> exchange(). Is there a similar way this can be done in SQLServer 2000

I'm not familiar with Exchange, however if you can export the addresses to a
CSV file, then DTS or bcp.exe can import the data. Alternatively, if you
already have them in Access, then you could use DTS or a linked server to
pull them into MSSQL.

Simon|||From MS SQL 2k Books On Line ...

The Microsoft OLE DB Provider for Exchange exposes data stored in a
Microsoft Exchange 2000 Web Store in tabular form. This data can be queried
using an SQL-like language that is very similar to the SQL subset supported
by the OLE DB Provider for Microsoft Indexing Service.

Microsoft SQL ServerT 2000 distributed queries can be used to query data
from the Exchange Web Store through this OLE DB Provider and can be joined
with tables in SQL Server. The Exchange Web Store should be located in the
same computer as SQL Server. Web Stores located in other computers cannot be
accessed using the OLE DB Provider for Exchange.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41517712$1_1@.news.bluewin.ch...
> "Mick Kehoe" <mick@.kehoe.demon.co.uk> wrote in message
> news:86917a0f.0409220431.21399510@.posting.google.c om...
> > Hi All
> > We are running SQL Server & Outlook with an exchange server. We are
> > looking for a way to import the address book & email details into a
> > database table.
> > We have managed to do this with MS Access by using the import option
> > exchange(). Is there a similar way this can be done in SQLServer 2000
> I'm not familiar with Exchange, however if you can export the addresses to
a
> CSV file, then DTS or bcp.exe can import the data. Alternatively, if you
> already have them in Access, then you could use DTS or a linked server to
> pull them into MSSQL.
> Simon

mailing labels in SQL Server Reporting Services

I am creating a report to be used to print mailing labels. Size 1" x 2 5/8" (3 columns of 10 labels). I seem to have everything but the vertical spacing working properly. I have used a rectangle set to the size of the labels which contains a list box containing text boxes containing expressions to pull in the fields. Some of the street addresses are two lines with the majority being only one line.

I have joined the two fields containing the street addresses and inserted a new line command so each field will print on its own line but yet be contained within the same text box. Everything except the text box containing the street addresses are set to not increase or decrease. My problem is that on the pages of the report where labels vary between the one and two line street addresses the labels tend to creep down which in some cases causes data to be printed over two labels. Any ideas on what I can do to format these to print properly?

Thanks

How did you create 3 columns mailing labels? Is there a way in reporting services to display the data in 3 columns?

Thanks

|||

Report Properties menu on the Layout Tab the first section lets you specify the number of columns and spacing for your report. I then set up a rectangle the size of the label, added a list box and the fields for the data to be shown.

|||

I changed the layout, and added 2 columns to my report, my fields are in a list control, when i preview the report it just shows in one column, the other column is not even displayed, do you know what I could be doing wrong, I am sure it is something silly?

Thanks again

Espy

|||In preview I also only see one column but if you use the print preview you should see all columns. (Took me a few tries to get all this figured out too!). Hope that helps.|||

I did the print layout and get a black screen with nothing in it

When I printed the report it just printed one column

I am not sure what I am doing wrong

Any other suggestions?

thanks for your help

|||

Sorry, I may not be much help in explaining your results; but, below are the Layout settings I have used and I have walked back through the steps: I started a blank report; went to Report - Report Properties - Layout and set the following

Columns: 3; Spacing 0.125 in

Page Width: 8.5in; Page Height: 11 in

Left margin: 0.25in; right margin: 0.125in

Top margin: 0.4in; Bottom margin: 0.5in

Then back on the Layout tab for the report itself I inserted a rectangle and sized it to fit within what would be the actual margins of a label. Then, I adjusted the body of the report itself by dragging the right side towards the left to match the right edge of the rectangle. The actual columns began to show on my report page when I pulled the right side of the body over towards the left is when (sorry, I may not be explaining this very well). Hope this helps.

Good luck.

|||

I was able to make it work

Thanks for your help

|||

Hi

am new to reporting service. I need to do label printing in two columns. that is coming in one column. I changed the layout-columns -2.Is it needed to change anything else ? Its showing in one column. .Please suggest anything. Your earlier conversation helped alot. Thank You very much........

|||

The following may be what you need next, repeating part of my reply posted earlier:

"Then back on the Layout tab for the report itself I inserted a rectangle and sized it to fit within what would be the actual margins of a label. Then, I adjusted the body of the report itself by dragging the right side towards the left to match the right edge of the rectangle. The actual columns began to show on my report page when I pulled the right side of the body over towards the left is when (sorry, I may not be explaining this very well). Hope this helps.

Good luck."

|||

I tried that. now its comin in 2 column, but value that came in the first column is repeating in second column. But in print preview only the second column is showing. adjusted the rectangle. Some silly mistakes will be there. Then, I need 8 labels in 1 column, but only 6 is coming. I increased the report size. but no use. Please help

|||Sorry for the delay in responding to you. Did you get your question resolved? If not, I reviewed the previous posts and then recreated a label. Make sure that in your report properties (menu choice Report - Report Properties - Layout) you have specified the number of columns (8 in your case), your page width and height to match the sheet of labels, and the margins to match the sheet of labels. I then refined the Body of the report; inserted a list box linked to the data source - and sized to match the size of the label; inserted a rectangle sized to match the size of the label and then inserted text boxes with the fields I want to show. From there its just fine-tuning the margins, padding, etc. Please note that I never did get a response addressing my original question though. Hope this helps to answer your question.|||

I am very thankful to you. I got it in a nice maaner by increasing the page width. But I integrated to the program it is not showing that alignment. I am thinking font mismatch will b there . But its not working for me. If u hav any idea pls suggest that. That will help me very much. I am Sure. Thanks alot for ur proper guidings........

|||

Hi

My reports are coming correctly. But the pbms am facing is that,

If am giving page range in print preview(1-2) then 1st page 'll be showing and 2nd page's 1st column [ If 1-3 then 1st,2nd and 3rd page's 1st col; 1-1 means 1st page's ist column]

if it is coming row wise it 'll be helpful.

|||We encountered the same problem - our "fix" was to render in PDF when wanting to print only certain pages. Once entire report is in pdf then you can print specified pages.

mailing labels in SQL Server Reporting Services

I am creating a report to be used to print mailing labels. Size 1" x 2 5/8" (3 columns of 10 labels). I seem to have everything but the vertical spacing working properly. I have used a rectangle set to the size of the labels which contains a list box containing text boxes containing expressions to pull in the fields. Some of the street addresses are two lines with the majority being only one line.

I have joined the two fields containing the street addresses and inserted a new line command so each field will print on its own line but yet be contained within the same text box. Everything except the text box containing the street addresses are set to not increase or decrease. My problem is that on the pages of the report where labels vary between the one and two line street addresses the labels tend to creep down which in some cases causes data to be printed over two labels. Any ideas on what I can do to format these to print properly?

Thanks

How did you create 3 columns mailing labels? Is there a way in reporting services to display the data in 3 columns?

Thanks

|||

Report Properties menu on the Layout Tab the first section lets you specify the number of columns and spacing for your report. I then set up a rectangle the size of the label, added a list box and the fields for the data to be shown.

|||

I changed the layout, and added 2 columns to my report, my fields are in a list control, when i preview the report it just shows in one column, the other column is not even displayed, do you know what I could be doing wrong, I am sure it is something silly?

Thanks again

Espy

|||In preview I also only see one column but if you use the print preview you should see all columns. (Took me a few tries to get all this figured out too!). Hope that helps.|||

I did the print layout and get a black screen with nothing in it

When I printed the report it just printed one column

I am not sure what I am doing wrong

Any other suggestions?

thanks for your help

|||

Sorry, I may not be much help in explaining your results; but, below are the Layout settings I have used and I have walked back through the steps: I started a blank report; went to Report - Report Properties - Layout and set the following

Columns: 3; Spacing 0.125 in

Page Width: 8.5in; Page Height: 11 in

Left margin: 0.25in; right margin: 0.125in

Top margin: 0.4in; Bottom margin: 0.5in

Then back on the Layout tab for the report itself I inserted a rectangle and sized it to fit within what would be the actual margins of a label. Then, I adjusted the body of the report itself by dragging the right side towards the left to match the right edge of the rectangle. The actual columns began to show on my report page when I pulled the right side of the body over towards the left is when (sorry, I may not be explaining this very well). Hope this helps.

Good luck.

|||

I was able to make it work

Thanks for your help

|||

Hi

am new to reporting service. I need to do label printing in two columns. that is coming in one column. I changed the layout-columns -2.Is it needed to change anything else ? Its showing in one column. .Please suggest anything. Your earlier conversation helped alot. Thank You very much........

|||

The following may be what you need next, repeating part of my reply posted earlier:

"Then back on the Layout tab for the report itself I inserted a rectangle and sized it to fit within what would be the actual margins of a label. Then, I adjusted the body of the report itself by dragging the right side towards the left to match the right edge of the rectangle. The actual columns began to show on my report page when I pulled the right side of the body over towards the left is when (sorry, I may not be explaining this very well). Hope this helps.

Good luck."

|||

I tried that. now its comin in 2 column, but value that came in the first column is repeating in second column. But in print preview only the second column is showing. adjusted the rectangle. Some silly mistakes will be there. Then, I need 8 labels in 1 column, but only 6 is coming. I increased the report size. but no use. Please help

|||Sorry for the delay in responding to you. Did you get your question resolved? If not, I reviewed the previous posts and then recreated a label. Make sure that in your report properties (menu choice Report - Report Properties - Layout) you have specified the number of columns (8 in your case), your page width and height to match the sheet of labels, and the margins to match the sheet of labels. I then refined the Body of the report; inserted a list box linked to the data source - and sized to match the size of the label; inserted a rectangle sized to match the size of the label and then inserted text boxes with the fields I want to show. From there its just fine-tuning the margins, padding, etc. Please note that I never did get a response addressing my original question though. Hope this helps to answer your question.|||

I am very thankful to you. I got it in a nice maaner by increasing the page width. But I integrated to the program it is not showing that alignment. I am thinking font mismatch will b there . But its not working for me. If u hav any idea pls suggest that. That will help me very much. I am Sure. Thanks alot for ur proper guidings........

|||

Hi

My reports are coming correctly. But the pbms am facing is that,

If am giving page range in print preview(1-2) then 1st page 'll be showing and 2nd page's 1st column [ If 1-3 then 1st,2nd and 3rd page's 1st col; 1-1 means 1st page's ist column]

if it is coming row wise it 'll be helpful.

|||We encountered the same problem - our "fix" was to render in PDF when wanting to print only certain pages. Once entire report is in pdf then you can print specified pages.

mailing labels in SQL Server Reporting Services

I am creating a report to be used to print mailing labels. Size 1" x 2 5/8" (3 columns of 10 labels). I seem to have everything but the vertical spacing working properly. I have used a rectangle set to the size of the labels which contains a list box containing text boxes containing expressions to pull in the fields. Some of the street addresses are two lines with the majority being only one line.

I have joined the two fields containing the street addresses and inserted a new line command so each field will print on its own line but yet be contained within the same text box. Everything except the text box containing the street addresses are set to not increase or decrease. My problem is that on the pages of the report where labels vary between the one and two line street addresses the labels tend to creep down which in some cases causes data to be printed over two labels. Any ideas on what I can do to format these to print properly?

Thanks

How did you create 3 columns mailing labels? Is there a way in reporting services to display the data in 3 columns?

Thanks

|||

Report Properties menu on the Layout Tab the first section lets you specify the number of columns and spacing for your report. I then set up a rectangle the size of the label, added a list box and the fields for the data to be shown.

|||

I changed the layout, and added 2 columns to my report, my fields are in a list control, when i preview the report it just shows in one column, the other column is not even displayed, do you know what I could be doing wrong, I am sure it is something silly?

Thanks again

Espy

|||In preview I also only see one column but if you use the print preview you should see all columns. (Took me a few tries to get all this figured out too!). Hope that helps.|||

I did the print layout and get a black screen with nothing in it

When I printed the report it just printed one column

I am not sure what I am doing wrong

Any other suggestions?

thanks for your help

|||

Sorry, I may not be much help in explaining your results; but, below are the Layout settings I have used and I have walked back through the steps: I started a blank report; went to Report - Report Properties - Layout and set the following

Columns: 3; Spacing 0.125 in

Page Width: 8.5in; Page Height: 11 in

Left margin: 0.25in; right margin: 0.125in

Top margin: 0.4in; Bottom margin: 0.5in

Then back on the Layout tab for the report itself I inserted a rectangle and sized it to fit within what would be the actual margins of a label. Then, I adjusted the body of the report itself by dragging the right side towards the left to match the right edge of the rectangle. The actual columns began to show on my report page when I pulled the right side of the body over towards the left is when (sorry, I may not be explaining this very well). Hope this helps.

Good luck.

|||

I was able to make it work

Thanks for your help

|||

Hi

am new to reporting service. I need to do label printing in two columns. that is coming in one column. I changed the layout-columns -2.Is it needed to change anything else ? Its showing in one column. .Please suggest anything. Your earlier conversation helped alot. Thank You very much........

|||

The following may be what you need next, repeating part of my reply posted earlier:

"Then back on the Layout tab for the report itself I inserted a rectangle and sized it to fit within what would be the actual margins of a label. Then, I adjusted the body of the report itself by dragging the right side towards the left to match the right edge of the rectangle. The actual columns began to show on my report page when I pulled the right side of the body over towards the left is when (sorry, I may not be explaining this very well). Hope this helps.

Good luck."

|||

I tried that. now its comin in 2 column, but value that came in the first column is repeating in second column. But in print preview only the second column is showing. adjusted the rectangle. Some silly mistakes will be there. Then, I need 8 labels in 1 column, but only 6 is coming. I increased the report size. but no use. Please help

|||Sorry for the delay in responding to you. Did you get your question resolved? If not, I reviewed the previous posts and then recreated a label. Make sure that in your report properties (menu choice Report - Report Properties - Layout) you have specified the number of columns (8 in your case), your page width and height to match the sheet of labels, and the margins to match the sheet of labels. I then refined the Body of the report; inserted a list box linked to the data source - and sized to match the size of the label; inserted a rectangle sized to match the size of the label and then inserted text boxes with the fields I want to show. From there its just fine-tuning the margins, padding, etc. Please note that I never did get a response addressing my original question though. Hope this helps to answer your question.|||

I am very thankful to you. I got it in a nice maaner by increasing the page width. But I integrated to the program it is not showing that alignment. I am thinking font mismatch will b there . But its not working for me. If u hav any idea pls suggest that. That will help me very much. I am Sure. Thanks alot for ur proper guidings........

|||

Hi

My reports are coming correctly. But the pbms am facing is that,

If am giving page range in print preview(1-2) then 1st page 'll be showing and 2nd page's 1st column [ If 1-3 then 1st,2nd and 3rd page's 1st col; 1-1 means 1st page's ist column]

if it is coming row wise it 'll be helpful.

|||We encountered the same problem - our "fix" was to render in PDF when wanting to print only certain pages. Once entire report is in pdf then you can print specified pages.

Mailing Labels from a subreport?

I have a main report which is essentially a letter. When the letter is
completed, I have a SubReport which are mailing labels. In the sub report I
have 2 columns and in the main report I have 1 column. When I run the main
report, I only get 1 column of Labels. When I run the subreport I get the 2
columns that I'm looking for.
Any suggestions on how I can get the main report to show 2 columns of Labels?
Regarding the dimension of the Main Report:
Report
Margin .5 Left, .5 Right, .5 Top, .5 Bottom
Page Size 7.5in Width, 11in Height
Body
Columns 1
Size 7.5in, 21.7448in
On the Sub Report :
Columns 2
Column Spacing .25
Size 3.5in, 0.73958in
Is this possible?
Thanks!I've done some research and such a thing is not possible. The columns is
driven from the top level report.
I am thinking of a work-around:
I'll essentially have 2 datasets one on the left and one on the right. They
will both call the same stored procedure to get the label. However, the left
side will call with a parameter of "Even" and the right with "Odd". The only
rows that will be returned on the left will be the ones that are moded by
Even Numbers. The only rows that will be returned on the right will be the
ones moded by Odd Numbers. To get this Even/Odd in the stored procedure, I
will have an identity column.
"CraigZello" wrote:
> I have a main report which is essentially a letter. When the letter is
> completed, I have a SubReport which are mailing labels. In the sub report I
> have 2 columns and in the main report I have 1 column. When I run the main
> report, I only get 1 column of Labels. When I run the subreport I get the 2
> columns that I'm looking for.
> Any suggestions on how I can get the main report to show 2 columns of Labels?
> Regarding the dimension of the Main Report:
> Report
> Margin .5 Left, .5 Right, .5 Top, .5 Bottom
> Page Size 7.5in Width, 11in Height
> Body
> Columns 1
> Size 7.5in, 21.7448in
>
> On the Sub Report :
> Columns 2
> Column Spacing .25
> Size 3.5in, 0.73958in
> Is this possible?
> Thanks!
>
>

Mailing labels for dummies

Ok, I'm an idiot. Does anyone out there have some step by step instructions for creating a report that will do mailing labels. I've got multiple columns on my report but I end up with the contents of each column being the same across each grouping/row. In other words for each record read, since I have three columns, I get three occurances of each customer and address until all the data selected for that row is presented, then I go to the next customer which again is repeated across all three columns.

I'm sorry I'm so stupid, but I've looked at other entries on the forum, and I just can't figure it out. We are trying to get out from underneith Business objects thumb and any help would be greatly appreciated. Thanks! - Eric -

Carpet King,

I think what you are looking for is the Multiple Column type report. you will set this up by using going into the report properties and setting the number of colums to 3.

Hope this helps

|||

Thanks Bret for the input. Your posting helped me to figure out where to look and change the properties. It through me for a while 'cause the pre-view doesn't show the multiple columns. As a result, after reading your input and making the change, it took a while to figure out that what you told me to do really did fix the problem.

The SQL reporting services is still Marginal (at best!) in creating labels. I'm having a heck of a time getting things evened out so that they will print on my labels correctly. I'm still having a lot of problems getting it set up just right so that information from one label doesn't carry over to another.

None the less, thank you very much for your help! I don't want you to feel like I am shooting the messanger, just venting. Sorry. :B - Eric -

|||

Hello,

I am working on mailing labels and I have the 3 columns, but now I am trying to get them to print across the page,rather than down the page. (Trying to prevent wasting labels by going down the page.) Below is an example of what I need:

1 2 3 4

NOT

1

2

3

4

Any ideas would be greatly appreciated!

mbsqlnewbie

Mailing labels for dummies

Ok, I'm an idiot. Does anyone out there have some step by step instructions for creating a report that will do mailing labels. I've got multiple columns on my report but I end up with the contents of each column being the same across each grouping/row. In other words for each record read, since I have three columns, I get three occurances of each customer and address until all the data selected for that row is presented, then I go to the next customer which again is repeated across all three columns.

I'm sorry I'm so stupid, but I've looked at other entries on the forum, and I just can't figure it out. We are trying to get out from underneith Business objects thumb and any help would be greatly appreciated. Thanks! - Eric -

Carpet King,

I think what you are looking for is the Multiple Column type report. you will set this up by using going into the report properties and setting the number of colums to 3.

Hope this helps

|||

Thanks Bret for the input. Your posting helped me to figure out where to look and change the properties. It through me for a while 'cause the pre-view doesn't show the multiple columns. As a result, after reading your input and making the change, it took a while to figure out that what you told me to do really did fix the problem.

The SQL reporting services is still Marginal (at best!) in creating labels. I'm having a heck of a time getting things evened out so that they will print on my labels correctly. I'm still having a lot of problems getting it set up just right so that information from one label doesn't carry over to another.

None the less, thank you very much for your help! I don't want you to feel like I am shooting the messanger, just venting. Sorry. :B - Eric -

|||

Hello,

I am working on mailing labels and I have the 3 columns, but now I am trying to get them to print across the page,rather than down the page. (Trying to prevent wasting labels by going down the page.) Below is an example of what I need:

1 2 3 4

NOT

1

2

3

4

Any ideas would be greatly appreciated!

mbsqlnewbie

Mailing labels don't display properly when run from report server

I have some mailing labels that when I run it from my report works fine as
two columns. When I deploy it to the report server and run it from there it
only displays one column. Anyone have any information that could help solve
this?When viewing the report in HTML format e.g. in report manager, you will only
see one column because multiple columns are currently not supported in HTML
format. You will however get them when using PDF export.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"GORAMS" <GORAMS@.discussions.microsoft.com> wrote in message
news:EE7C98FE-8D7A-4BBD-9057-48B68A71140D@.microsoft.com...
>I have some mailing labels that when I run it from my report works fine as
> two columns. When I deploy it to the report server and run it from there
> it
> only displays one column. Anyone have any information that could help
> solve
> this?|||Thanks so much for your reply. I was able to run the report and when I
exported them out to a PDF format, it properly displayed my report.
"Robert Bruckner [MSFT]" wrote:
> When viewing the report in HTML format e.g. in report manager, you will only
> see one column because multiple columns are currently not supported in HTML
> format. You will however get them when using PDF export.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "GORAMS" <GORAMS@.discussions.microsoft.com> wrote in message
> news:EE7C98FE-8D7A-4BBD-9057-48B68A71140D@.microsoft.com...
> >I have some mailing labels that when I run it from my report works fine as
> > two columns. When I deploy it to the report server and run it from there
> > it
> > only displays one column. Anyone have any information that could help
> > solve
> > this?
>
>|||Hi,
Just want to know how did you use label priting in MC Repots. because as far
as my knowle'ge i can have only one row at a time.
I am unable to print label with three column.
Any help?
Labhesh Shrimali
Bangalore
"GORAMS" wrote:
> Thanks so much for your reply. I was able to run the report and when I
> exported them out to a PDF format, it properly displayed my report.
> "Robert Bruckner [MSFT]" wrote:
> > When viewing the report in HTML format e.g. in report manager, you will only
> > see one column because multiple columns are currently not supported in HTML
> > format. You will however get them when using PDF export.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "GORAMS" <GORAMS@.discussions.microsoft.com> wrote in message
> > news:EE7C98FE-8D7A-4BBD-9057-48B68A71140D@.microsoft.com...
> > >I have some mailing labels that when I run it from my report works fine as
> > > two columns. When I deploy it to the report server and run it from there
> > > it
> > > only displays one column. Anyone have any information that could help
> > > solve
> > > this?
> >
> >
> >|||Make the height of the body of the report small enough so that several
rows will print on one page - also, set up the body of the report to
have three columns.
You need to carefully control the size of the page, the body of the
report, and any page header and footer.|||This is a great feature but can reporting services print records left to
right rather than top to bottom. For Example, I want to print the records in
order like this
1 2
3 4
rather than
1 3
2 4
Thank you for your time.
Dan
"Parker" wrote:
> Make the height of the body of the report small enough so that several
> rows will print on one page - also, set up the body of the report to
> have three columns.
> You need to carefully control the size of the page, the body of the
> report, and any page header and footer.
>|||Dan -
If you read this, the only way I can think of is to do the work in a
stored procedure to combine two (or more) records into one - and then
print the resulting record on a single row.
I had to do something like this when I needed to put 4 records on a
page (one in each corner) and duplex print them.
Dan Storch wrote:
> This is a great feature but can reporting services print records left to
> right rather than top to bottom. For Example, I want to print the records in
> order like this
> 1 2
> 3 4
> rather than
> 1 3
> 2 4
> Thank you for your time.
> Dan|||HI Dan,
Were you ever able to figure out how to get the labels to print from left to
right rather than down the page?
Any help would be greatly appreciated!
Thanks!
"Dan Storch" wrote:
> This is a great feature but can reporting services print records left to
> right rather than top to bottom. For Example, I want to print the records in
> order like this
> 1 2
> 3 4
> rather than
> 1 3
> 2 4
> Thank you for your time.
> Dan
> "Parker" wrote:
> > Make the height of the body of the report small enough so that several
> > rows will print on one page - also, set up the body of the report to
> > have three columns.
> >
> > You need to carefully control the size of the page, the body of the
> > report, and any page header and footer.
> >
> >

Mailing Labels

Is it possible to do mailing labels through Reporting Services. I can't
figure out a way to display three columns of information per page'?
anybody have any examples?I set up a report to print Avery 5162 labels yesterday. I had to play with
the margins, font size, textbox positioning, and textbox spacing to get the
data to fit on the labels correctly. After adding a blank report and creating
a dataset here are the steps I took to create the report.
Hope it helps. 1) Change Report Properties Margins to: 0.125in, 0.125in,
0.825in, 0in (Your's will probably be different.)
2) Change Body Layout Properties: Columns=2, ColumnSpacing=0.25in, Size=4in,
1.375in(the label size)
3) Add a List object: Location=0in, 0in, Size=4in, 1.375in(the label size)
4) Add a Rectangle object: Location=0in, 0in, Size=4in, 1.375in(the label
size)
5) Add and arrange data fields When you first view the report on the Preview
tab it will only show one column. You'll need to switch to Print Preview to
see the two column report.|||Thanks.. .I actually already did practically the same thing.... take care...
"GORAMS" wrote:
> I set up a report to print Avery 5162 labels yesterday. I had to play with
> the margins, font size, textbox positioning, and textbox spacing to get the
> data to fit on the labels correctly. After adding a blank report and creating
> a dataset here are the steps I took to create the report.
> Hope it helps. 1) Change Report Properties Margins to: 0.125in, 0.125in,
> 0.825in, 0in (Your's will probably be different.)
> 2) Change Body Layout Properties: Columns=2, ColumnSpacing=0.25in, Size=4in,
> 1.375in(the label size)
> 3) Add a List object: Location=0in, 0in, Size=4in, 1.375in(the label size)
> 4) Add a Rectangle object: Location=0in, 0in, Size=4in, 1.375in(the label
> size)
> 5) Add and arrange data fields When you first view the report on the Preview
> tab it will only show one column. You'll need to switch to Print Preview to
> see the two column report.

Mailing Labels

Is there any practical method of using Reporting Services for generating
mailing labels? For example, Avery XXXX labels.
Assume that all clients will be using the latest version of IE on a windows
domain.
Thanks in advance.
MarkYou will need to write a multi-column report and the render the report using
Print Preview, PDF, or TIFF (multi column reports appear as one column in
Preview and the HTML renderers). See the follow topic for more information
about multi-column reports:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_4uwj.asp
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:u5op5zStEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Is there any practical method of using Reporting Services for generating
> mailing labels? For example, Avery XXXX labels.
> Assume that all clients will be using the latest version of IE on a
> windows
> domain.
> Thanks in advance.
> Mark
>|||Bruce, I've created a 2 column report, for a label printing application, but
when I print it, I get all the labels in just one column. I've chaged the
page width to make it wide enough to discard size problems, I've also changed
column width, etc.
I just run out of ideas. Why can't I get both columns filled with labels?
Thanx
"Bruce Johnson [MSFT]" wrote:
> You will need to write a multi-column report and the render the report using
> Print Preview, PDF, or TIFF (multi column reports appear as one column in
> Preview and the HTML renderers). See the follow topic for more information
> about multi-column reports:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_4uwj.asp
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
> news:u5op5zStEHA.2804@.TK2MSFTNGP14.phx.gbl...
> > Is there any practical method of using Reporting Services for generating
> > mailing labels? For example, Avery XXXX labels.
> >
> > Assume that all clients will be using the latest version of IE on a
> > windows
> > domain.
> >
> > Thanks in advance.
> >
> > Mark
> >
> >
>
>

Mailing Label

Hi,
I have several reports realized with SSRS, in general address lists. I'm
trying to find a way to add to this reports the capability to produce mailing
labels. I'd like to do it with a custom export format (it would be nice to
have it in the "export as" dropdown with pdf, csv and excel formats...). The
idea is to avoid duplicating every report to get a "Mailing label" version,
this to keep everything as DRY as possible.
Is it possible? Are there other ways without keeping multiple copies of
queries and parameters?
--
NPOn Mar 13, 11:31 am, Numerica <numer...@.nospam.nospam> wrote:
> Hi,
> I have several reports realized with SSRS, in general address lists. I'm
> trying to find a way to add to this reports the capability to produce mailing
> labels. I'd like to do it with a custom export format (it would be nice to
> have it in the "export as" dropdown with pdf, csv and excel formats...). The
> idea is to avoid duplicating every report to get a "Mailing label" version,
> this to keep everything as DRY as possible.
> Is it possible? Are there other ways without keeping multiple copies of
> queries and parameters?
> --
> NP
I've heard of other people w/similar needs using tables w/grouping
inside list controls on multi-column reports. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hi Numerica,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
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.|||I didn't find any suitable solution, can you hel me?
--
NP
"Wei Lu [MSFT]" wrote:
> Hi Numerica,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> 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.
>|||Hello NP,
I don't think that you could directly using the Mailing Label as the export
format because you need to specify the data you could use.
If your report did not have the same data structure, it may be a big
problem to let your custom render know the data structure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
thanks for the answer, let's say in my reports name and address fields have
always the same name or position could it help?
Can you send me some links to documentation for writing custom report formats?
--
NP
"Wei Lu [MSFT]" wrote:
> Hello NP,
> I don't think that you could directly using the Mailing Label as the export
> format because you need to specify the data you could use.
> If your report did not have the same data structure, it may be a big
> problem to let your custom render know the data structure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello NP,
Here is the sample:
http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/default.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks a lot Wei Lu.
--
NP
"Wei Lu [MSFT]" wrote:
> Hello NP,
> Here is the sample:
> http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/default.aspx
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||My Pleasure, NP.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

mailing from MS SQL

Hi, is it possible to send an email from MS SQL? I need to have a trigger which will activate the sending an email. Thanksdo not do it from a trigger. What happens if there is a bulk import of 10,000 records. You want 10,000 emails? Otherwise please google or refer to SQL Server Books Online for xp_sendmail or xp_smtpmail.|||A bulk import of 10,000 records is still only going to execute the trigger once. A cursor or loop-based import would stress out your e-mail system, though.
It's just a bad idea for the scope of a trigger's actions to extend outside the database. Don't do it.
An alternative is to have your trigger load messages into a queue table for regularly processing by a scheduled job (which could run as frequently as once per minute).|||I was assuming that we would execute xp_sendmail for every record in inserted smart guy.|||I know you were, and I know you know the difference. Just wanted to make sure the poster did not get the wrong impression.

Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@.FROM = N'testsql2000@.is.depaul.edu',

@.TO = N'dvaddi@.depaul.edu',

@.server = N'smtp.depaul.edu',

@.subject = N'Status of sqlserver!',

@.type = N'text/html',

@.message = @.message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks

Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@.FROM = N'testsql2000@.is.depaul.edu',

@.TO = N'dvaddi@.depaul.edu',

@.server = N'smtp.depaul.edu',

@.subject = N'Status of sqlserver!',

@.type = N'text/html',

@.message = @.message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks

Mailing DBA when sql server Agent Fails

Hello

I would like to know a script which mails the DBA mail box when the sql server agent fails ( I am working on sql server 2000) using SMTP. I have got SMTP server configured for all my other job failure notifications.

Please let me know any scripts that can be run on the command prompt or any ideas of how I can do it.



ThanksWhen the Agent service fails, or just when a job fails?|||When the Agent Service Fails.

Thanks|||Since the agent executes the scripts, how do you expect it to execute a script to notify you that it is no longer executing scripts?

Two alternatives:
1) Set up an agent on another server to check the functioning of the first one.
2) Set a script to run when the service is restarted (note that this will NOT notify if the server crashes and does not automatically restart).|||I use a configurable WMI script to check the status of selected services on selected servers. I check for any that are set to auto start and are not in a running state (or status, can't remember which).

Function CheckServices ( sComputer, sFQDN )

Dim oConnMail
Dim oCommMail

Dim oWMIService
Dim oItems
Dim oItem

Dim sDisplayName
Dim sSubject
Dim sMessage

Set oWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Security )}!\\" & sFQDN & "\root\cimv2")
Set oItems = oWMIService.ExecQuery("SELECT * FROM Win32_Service",,48)

' Define objects for mail message
Set oConnMail = CreateObject("ADODB.Connection")
Set oCommMail = CreateObject("ADODB.Command")

' Open connection to mail database
oConnMail.ConnectionString = Replace(sConnectionString, sDatabase, "master")
oConnMail.Open

' Open connection to catalog database
oConn.ConnectionString = sConnectionString
oConn.Open

If Err.number = 0 Then
For Each oItem in oItems
If IsNull(oItem.DisplayName) Then
sDisplayName = oItem.Name
Else
sDisplayName = oItem.DisplayName
End If

' This part of the routine verifies that
' services that are set to Auto start are still running;
' if not running, then an alert is e-mailed
If oItem.StartMode = "Auto" and oItem.State <> "Running" Then

oCommMail.CommandText = "spSendMail"
oCommMail.CommandType = 4
oCommMail.ActiveConnection = oConnMail

sSubject = "Service " & oItem.Name & " stopped running on " & oItem.SystemName
sMessage = "Service " & sDisplayName & " stopped running on " & oItem.SystemName & VbCrLf & _
"Service Specific Exit Code: " & oItem.ServiceSpecificExitCode & vbCrLf & _
"Exit Code: " & oItem.ExitCode & vbCrLf & _
"Status: " & oItem.Status & vbCrLf & _
"State: " & oItem.State
' sMessage = "Test"
oCommMail.Parameters.Refresh
oCommMail.Parameters("@.Subject") = sSubject
oCommMail.Parameters("@.Message") = sMessage
oCommMail.Parameters("@.Recipient") = sRecipient

oCommMail.Execute

End If
Next

' Close mail database objects
Set oCommMail = Nothing
oConnMail.Close
Set oConnMail = Nothing
Else
Call AddLogEntry(sFQDN, "Error", "ServicesCheck", Err.number & " - " & Err.Source & " - " & Err.Description)
Err.Clear
End If

oConn.Close

End Function|||Hello

Thanks for your script.

How do I go on to execute the script. And do I need to download any WMI .

Thanks|||Hello

Thanks for your script.

How do I go on to execute the script. And do I need to download any WMI .

Thanks

I have a management server which is a dumping ground for a lot of different monitoring tools (some COTS, some custom). This script is scheduled from this server using Windows Scheduled Tasks. I pass in selected parameters to the script from the command line.

WMI is native to Windows 2000/Server 2003; you shouldn't need to load anything special there. You may need to adjust your permissions on the target server to be able to read from the WMI repository (I think you need to be at least a Power User, but I could be wrong.

Note that the script provided is a function. It would run inside of a main script that would:

1. Pull in a list of servers to poll
2. Pull in any system parameters (such as the e-mail address(es) you want the alert to go to
3. Call the function (passing in the name of the computer)

Regards,

hmscott