MAPI Mail Client for SQL Mail?

Are there any free MAPI mail clients that I could use with an Internet mail
service (POP3/SMTP) so I could set up SQL Mail?
The Outlook Express that came with my dedicated server doesn't seem to do
the trick, and it's not easy to install Outlook on a dedicated server at a
hosting site where I can't just pop in a CD.
Thanks for any help.Don,
I am not sure I understand what you need to do. If this is SQL2005 you can
use Database mail to access any SMTP server. If you are on 2000 you might
want to check this out:
http://www.sqldev.net/xp/xpsmtp.htm
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
> Are there any free MAPI mail clients that I could use with an Internet
> mail
> service (POP3/SMTP) so I could set up SQL Mail?
> The Outlook Express that came with my dedicated server doesn't seem to do
> the trick, and it's not easy to install Outlook on a dedicated server at a
> hosting site where I can't just pop in a CD.
> Thanks for any help.
>|||Didn't know MVPs hung around on weekends ;) Thanks.
I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
backup) runs successfully (or more importantly fails) that I get an email.
Or if a CHECKDB identifies problems?
I've seen 'xpsmtp' but it seems a little complicated, and requires extra
non-intuitive steps when setting up jobs.
I just thought there might be a downloadable MAPI mail client somewhere (not
necessarily free) that would let me use SQL Mail as if I had Outlook
installed and could use email notification with jobs as easily as I could if
I had Outlook installed.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
> Don,
> I am not sure I understand what you need to do. If this is SQL2005 you can
> use Database mail to access any SMTP server. If you are on 2000 you might
> want to check this out:
> http://www.sqldev.net/xp/xpsmtp.htm
>
> --
> Andrew J. Kelly SQL MVP
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
do[vbcol=seagreen]
a[vbcol=seagreen]
>|||Setting up a MAPI mail client such as Exchange or Lotus Notes that will be
trouble-free and won't require you to pop-up a CD? Didn't know that such a
thing existed. Instead of MAPI, try using a SMTP client instead for sending
your warnings:
http://www.orcsweb.com/articles/sendmailsql.aspx
http://www.sqlteam.com/item.asp?ItemID=5003
http://support.microsoft.com/defaul...kb;en-us;312839
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Don Miller" <nospam@.nospam.com> wrote in message
news:evpT4hWyGHA.1256@.TK2MSFTNGP04.phx.gbl...
> Didn't know MVPs hung around on weekends ;) Thanks.
> I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
> backup) runs successfully (or more importantly fails) that I get an email.
> Or if a CHECKDB identifies problems?
> I've seen 'xpsmtp' but it seems a little complicated, and requires extra
> non-intuitive steps when setting up jobs.
> I just thought there might be a downloadable MAPI mail client somewhere
> (not
> necessarily free) that would let me use SQL Mail as if I had Outlook
> installed and could use email notification with jobs as easily as I could
> if
> I had Outlook installed.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
> do
> a
>|||Unfortunately setting up Mapi with SQL2000 can be quite troublesome and
using SMTP for job notification can involve a lot of extra work as well. If
you are willing to spend a few dollars your life could be made much easier
with a tool such as SQL Sentry from www.sqlsentry.net. It does a lot more
than just monitoring job status and can save a lot of aggravation.
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:evpT4hWyGHA.1256@.TK2MSFTNGP04.phx.gbl...
> Didn't know MVPs hung around on weekends ;) Thanks.
> I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
> backup) runs successfully (or more importantly fails) that I get an email.
> Or if a CHECKDB identifies problems?
> I've seen 'xpsmtp' but it seems a little complicated, and requires extra
> non-intuitive steps when setting up jobs.
> I just thought there might be a downloadable MAPI mail client somewhere
> (not
> necessarily free) that would let me use SQL Mail as if I had Outlook
> installed and could use email notification with jobs as easily as I could
> if
> I had Outlook installed.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
> do
> a
>

MAPI Mail Client for SQL Mail?

Are there any free MAPI mail clients that I could use with an Internet mail
service (POP3/SMTP) so I could set up SQL Mail?
The Outlook Express that came with my dedicated server doesn't seem to do
the trick, and it's not easy to install Outlook on a dedicated server at a
hosting site where I can't just pop in a CD.
Thanks for any help.Don,
I am not sure I understand what you need to do. If this is SQL2005 you can
use Database mail to access any SMTP server. If you are on 2000 you might
want to check this out:
http://www.sqldev.net/xp/xpsmtp.htm
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
> Are there any free MAPI mail clients that I could use with an Internet
> mail
> service (POP3/SMTP) so I could set up SQL Mail?
> The Outlook Express that came with my dedicated server doesn't seem to do
> the trick, and it's not easy to install Outlook on a dedicated server at a
> hosting site where I can't just pop in a CD.
> Thanks for any help.
>|||Didn't know MVPs hung around on weekends ;) Thanks.
I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
backup) runs successfully (or more importantly fails) that I get an email.
Or if a CHECKDB identifies problems?
I've seen 'xpsmtp' but it seems a little complicated, and requires extra
non-intuitive steps when setting up jobs.
I just thought there might be a downloadable MAPI mail client somewhere (not
necessarily free) that would let me use SQL Mail as if I had Outlook
installed and could use email notification with jobs as easily as I could if
I had Outlook installed.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
> Don,
> I am not sure I understand what you need to do. If this is SQL2005 you can
> use Database mail to access any SMTP server. If you are on 2000 you might
> want to check this out:
> http://www.sqldev.net/xp/xpsmtp.htm
>
> --
> Andrew J. Kelly SQL MVP
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
> > Are there any free MAPI mail clients that I could use with an Internet
> > mail
> > service (POP3/SMTP) so I could set up SQL Mail?
> >
> > The Outlook Express that came with my dedicated server doesn't seem to
do
> > the trick, and it's not easy to install Outlook on a dedicated server at
a
> > hosting site where I can't just pop in a CD.
> >
> > Thanks for any help.
> >
> >
>|||Setting up a MAPI mail client such as Exchange or Lotus Notes that will be
trouble-free and won't require you to pop-up a CD? Didn't know that such a
thing existed. Instead of MAPI, try using a SMTP client instead for sending
your warnings:
http://www.orcsweb.com/articles/sendmailsql.aspx
http://www.sqlteam.com/item.asp?ItemID=5003
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Don Miller" <nospam@.nospam.com> wrote in message
news:evpT4hWyGHA.1256@.TK2MSFTNGP04.phx.gbl...
> Didn't know MVPs hung around on weekends ;) Thanks.
> I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
> backup) runs successfully (or more importantly fails) that I get an email.
> Or if a CHECKDB identifies problems?
> I've seen 'xpsmtp' but it seems a little complicated, and requires extra
> non-intuitive steps when setting up jobs.
> I just thought there might be a downloadable MAPI mail client somewhere
> (not
> necessarily free) that would let me use SQL Mail as if I had Outlook
> installed and could use email notification with jobs as easily as I could
> if
> I had Outlook installed.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
>> Don,
>> I am not sure I understand what you need to do. If this is SQL2005 you
>> can
>> use Database mail to access any SMTP server. If you are on 2000 you might
>> want to check this out:
>> http://www.sqldev.net/xp/xpsmtp.htm
>>
>> --
>> Andrew J. Kelly SQL MVP
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
>> > Are there any free MAPI mail clients that I could use with an Internet
>> > mail
>> > service (POP3/SMTP) so I could set up SQL Mail?
>> >
>> > The Outlook Express that came with my dedicated server doesn't seem to
> do
>> > the trick, and it's not easy to install Outlook on a dedicated server
>> > at
> a
>> > hosting site where I can't just pop in a CD.
>> >
>> > Thanks for any help.
>> >
>> >
>>
>|||Unfortunately setting up Mapi with SQL2000 can be quite troublesome and
using SMTP for job notification can involve a lot of extra work as well. If
you are willing to spend a few dollars your life could be made much easier
with a tool such as SQL Sentry from www.sqlsentry.net. It does a lot more
than just monitoring job status and can save a lot of aggravation.
--
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:evpT4hWyGHA.1256@.TK2MSFTNGP04.phx.gbl...
> Didn't know MVPs hung around on weekends ;) Thanks.
> I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
> backup) runs successfully (or more importantly fails) that I get an email.
> Or if a CHECKDB identifies problems?
> I've seen 'xpsmtp' but it seems a little complicated, and requires extra
> non-intuitive steps when setting up jobs.
> I just thought there might be a downloadable MAPI mail client somewhere
> (not
> necessarily free) that would let me use SQL Mail as if I had Outlook
> installed and could use email notification with jobs as easily as I could
> if
> I had Outlook installed.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
>> Don,
>> I am not sure I understand what you need to do. If this is SQL2005 you
>> can
>> use Database mail to access any SMTP server. If you are on 2000 you might
>> want to check this out:
>> http://www.sqldev.net/xp/xpsmtp.htm
>>
>> --
>> Andrew J. Kelly SQL MVP
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
>> > Are there any free MAPI mail clients that I could use with an Internet
>> > mail
>> > service (POP3/SMTP) so I could set up SQL Mail?
>> >
>> > The Outlook Express that came with my dedicated server doesn't seem to
> do
>> > the trick, and it's not easy to install Outlook on a dedicated server
>> > at
> a
>> > hosting site where I can't just pop in a CD.
>> >
>> > Thanks for any help.
>> >
>> >
>>
>sql

Many Year Payments

I need to create a stored procedure that will show all clients that have made payments every year for a number of years (3) or (5). My tables are simple but I have no clue how to ask the SQL question, mostly because it is each and every year.

tblClients ClientID (pk)
tblOrders OrderID (pk) ClientID (fk)
tblOrderPayments PaymentID (pk) OrderID (fk)

Thanks for any help,Depends on how you would like the output. Also: In your tables there's no date. And is not possible for one payment to apply to many orders?

And before I start: You do not 'ask SQL questions', rather you 'construct SQL queries'. And lastly: Always use singular names for your tables, e.g. Client and not Clients, since all tables can hold multiple records all of your table names end up being plural and so the plural becomes redundant.

Anyway. I'll use some common sense to fill in the gaps in your question: I'll assume the payment date rather than the order date is the important one, and I'll assume therefore there's a date on the payment table. Also I'll assume for a moment that your DB design is correct although I doubt it, since it's almost certainly the case that several orders can be paid with one order. I do not know of businesses that does not allow you to pay off several orders in one payment.

So your question as stated is like follows: You want a list of all clients that's made at least one payment a year for the last X years. Is that right? Sounds like a weird question for a business to ask...

If this is indeed what they want, it's a rather interesting SQL problem. I'll give you a few solutions just for my own amusement, even though I suspect they'll be useless for you because your DB design is wrong and you probably misunderstood the needs of your management.

Here's my first attempt:

set @.EndYear = 2004
select CustomerID,
 CustomerName
 from Customer C
 where CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear)
 and CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 1)
 and CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 2)

It will work for the 3 year case, but sucks bigtime. If you have a small database (less than 1 million orders) then this will be the way to go since it's easy to understand and you probably won't notice and bad performance. If one of my guys wrote this query for me I'd not be impressed though.
It sucks because:
- Only works for a set number of years. You'll have to write 2 of them to cater for 3 or 5 year queries
- Uses IN with large sets, several times. Bad for performance.
It's not all bad, though. It's cool because:
- It's simple to understand for anyone out there
- Does not use cursors or temp tables

Here's a slightly better one:

set @.EndYear = 2004
select CustomerID,
 CustomerName
 from Customer C
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear) as Y1
 on C.CustomerID = Y1.CustomerID
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 1) as Y2
 on C.CustomerID = Y2.CustomerID
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 2) as Y3
 on C.CustomerID = Y2.CustomerID

The same as the first one, but without the expensive IN statements. Still uses a lot of DISTINCTs though, and you still have to write 2 queries for the 3 and 5 year cases. Let's extrapolate this query to one that will work for any number of years from 1 to 5:

set @.EndYear = 2004
set @.NoYears = 4
select CustomerID,
 CustomerName
 from Customer C
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear) as Y1
 on C.CustomerID = Y1.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 1
 or @.NoYears < 2)) as Y2
 on C.CustomerID = Y2.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 2
 or @.NoYears < 3)) as Y3
 on C.CustomerID = Y3.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 3
 or @.NoYears < 4)) as Y4
 on C.CustomerID = Y4.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 4
 or @.NoYears < 5)) as Y5
 on C.CustomerID = Y5.CustomerID

Whoa! That's a good one eh? Again, it will work, but I'll probably get blasted by the community here for even thinking about posting **** like that. The query will work for any number of years to check from 1 to 5, so you can do your 3 and 5 year queries on it just fine without the need to write 2 seperate queries.

Ok, enough of that. Now let's try another approach: Instead of doing it all in one SQL statement, we'll create a temp table with all CustomerIDs in it. Then we'll iterate through the years we want to check, and remove all customers that did not make a payment in each year. So in the end we're left with only the customers that made payments in all the years checked.

set @.EndYear = 2004
set @.NoYears = 4
set @.I = 0
select CustomerID
 into #Temp
 from Customer
while (@.I < @.NoYears)
begin
 delete T
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - @.I
 right join #Temp T
 on O.CustomerID = T.CustomerID
 where P.OrderID is null
 set @.I = @.I + 1
end
select C.CustomerID,
 C.CustomerName
 from Customer C
 join #Temp T
 on C.CustomerID = T.CustomerID

This is also a nice and simple solution, and will probably give you fairly decent performance. The good thing about it is that it will work for ANY number of years, although the more years you do the longer it will take. It's also sexy because it uses a RIGHT JOIN, my favourite type of join. :)

I'm going to stop here, but there are almost certainly more elegant solutions out there. I can imagine one where you use a helper table for all the years you want to check, and then do a distinct and a having count on a simple join in a sub-query - it will work in a single query and will be able to do an arbitraty number of years. I'll challenge anyone else out there to give it a shot and I'll post it in a day or two.|||Hi i think i found a nice solution for you :).

Data: i used the Northwind database for testing, i created a payments table containing customerid, orderid and paymentdate and populated it with testdata using the orders table.

DECLARE @.YearSpan int
SET @.YearSpan = 3
SELECT
 YearsOfPayment.CustomerID,
 Sum(FirstYear) As FirstYearPayments,
 Sum(SecondYear) As SecondYearPayments,
 Sum(ThirdYear) As ThirdYearPayments,
 Sum(FourthYear) As FourthYearPayments,
 Sum(FifthYear) As FifthYearPayments
FROM
 (
 Select
 Customers.CustomerID,
 (
 Case When Payments.Paymentdate Between '01/01/1996' And '12/31/1996' Then 1 Else 0 End
 ) AS FirstYear,
 (
 Case When Payments.Paymentdate Between '01/01/1997' And '12/31/1997' Then 1 Else 0 End
 ) AS SecondYear,
 (
 Case When Payments.Paymentdate Between '01/01/1998' And '12/31/1998' Then 1 Else 0 End
 ) AS ThirdYear,
 (
 Case When Payments.Paymentdate Between '01/01/1999' And '12/31/1999' Then 1 Else 0 End
 ) AS FourthYear,
 (
 Case When Payments.Paymentdate Between '01/01/2000' And '12/31/2000' Then 1 Else 0 End
 ) AS FifthYear
 From
 Customers
 Inner Join Payments On Payments.CustomerID = Customers.CustomerID
 ) AS YearsOfPayment
GROUP BY
 YearsOfPayment.CustomerID
 HAVING
 SUM(FirstYear) > 0
 AND SUM(SecondYear) > 0
 AND SUM(ThirdYear) > 0
 AND SUM(FourthYear) > (CASE WHEN @.YearSpan > 3 THEN 0 ELSE -1 END)
 AND SUM(FifthYear) > (CASE WHEN @.YearSpan > 4 THEN 0 ELSE -1 END)

Todo:
You have to replace the hardcoded dates with a @.FirstYear Parameter and modify the WHEN
clauses accordingly ;)

Good things here:
- if you need to check for more years, just modify it to return an additional CASE column
- performance won't change with the number of years checked (= the number of CASES you return since no subselects are involved

Further Tuning:
- to speed it up a even little more you can create and index on the paymentdate and put a WHERE statement in the "inner" SELECT to only check the years between @.firstdate and @.firstdate + @.yearspan

- Moon|||:: And before I start: You do not 'ask SQL questions', rather you 'construct SQL queries'.

... and before you post again, you should rethink your arrogant attidue and stop correcting someone since most people will find this very offensive.

:: And lastly: Always use singular names for your tables, e.g. Client and not Clients,
:: since all tables can hold multiple records all of your table names end up being plural
:: and so the plural becomes redundant.

... from OO point of view where you think of collections and items, a table is a collection and holding items (records) and therefore it is 100% valid to use plural.|||Moon's solution is novel, but only works for up to a certain number of years. Here's my solution that will work for an arbitrary number of years, with only one query and no temp tables or cursors:

set @.LastYear = 2004
set @.NumYears = 5
 select D.CustomerId,
 C.[Name]
 from Customer C
 join (select distinct O.CustomerId,
 datepart(year, P.[Date]) as PaidYear
 from [Order] O
 join Payment P
 on O.OrderId = P.OrderId
 and datepart(year, P.[Date]) <= @.LastYear
 and datepart(year, P.[Date]) > @.LastYear - @.NumYears) as D
 on C.CustomerID = D.CustomerID
group by D.CustomerID,
 C.[Name]
 having count(*) = @.NumYears

Who's your daddy! $5 for anyone with a more elegant solution.
And if I come off as arrogant, feel free to skip my comments and just look at my code.|||Relational databases are NOT OO and therefore your plural argument does not hold.

From IDEF1X

December 21, 1993

Section: 3.1.2 Entity Syntax

...The entity name is a noun phrase that describes the set of things the entity
represents. The noun phrase is in singular form, not plural. Abbreviations and
acronyms are permitted, however, the entity name must be meaningful and consistent
throughout the model...

IE and other data modeling standards organisations agree with the above statement. Also I was taught the same thing at University in my first year. I was under the impression that this is common knowledge.|||*points at the year, cough, :P*

well as long as Microsoft uses the plural convention in their own databases: master, Northwind, Pubs, ... i imply that there is a point in using plural for his own database objects as well ;)

Also i didn't say that using singular is a complete NoNo, just that using plural is not forbidden but valid to use.

PS: University != real life ;). I also was taught a few things in university such as C++ that are invalid by now - since when i learned C++, it was still a draft, without namespaces, and whatnot and if i would need to code in C++ today it would be a nearly a full restart.|||Yeah this one is nice :)

And sorry my yesterdays comment was rude as well :S.|||Like I said: If you start naming tables with plurals, then in the end ALL your tables (or at least a very large percentage) will end up being plural, and so the 's' at the end of every table becomes just another letter you have to type in every line of SQL code and serves no purpose whatsoever. Remember that 99% of all tables will have more than one row and therefore should in your argument be plural in the naming.

And who ever said MS wrote good code, eh?

The date may be 10 years old, but please feel free to provide me with a more recent reference from an SQL standards authority that says 'Yea, we made a mistake in '93, go ahead and use plurals now'

I disagree with you. There's no good reason to use plurals. Using plurals is a nono. Except if you're MS in which case you are expected to write bad code and thus are allowed plurals in table names.

Manipulating and deploying report models

All, we are reviewing the ad hoc report builder in SSRS 2005. This could be a big boon to our clients. But we have to be able to do some things first. First of all when we define our datasource view we need to be able to "filter" the available data in the view prior to being "delivered" to the report builder app the client gets to manipulate. We don't currently see a way to do this. But I'm working on it.

I'm thinking we may need to go the manual manipulation route using the Reporting Service2005 web service. So in a nutshell, I need to be able to pre filter the data a client gets prior to using the ad hoc report builder, we have too many clients to create views for each of them. So I've started giving it a go.

Thanks in advance

Marvin Hoffman

here's what I have so far, and it seems to be working, but I think maybe the URL is wrong, or I'm not calling the model name correctly. I'm getting this error "Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response."

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Net;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using ReportServiceAPI;

public partial class ReportBuilderExample : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

ReportingService2005 rs = new ReportingService2005();

rs.Url = "http://servername/reportserver/ReportingService2005.asmx";

NetworkCredential nc = new NetworkCredential("username", "password");

rs.Credentials = nc;

byte[] b = rs.GetModelDefinition("modelname");

}

}

I think that model name includes path and should start with slash, i.e.

byte[] b = rs.GetModelDefinition("/modelname");

Managing DB updates to client DB servers

I'm looking for ideas on how to write SQL scripts for updates that are
pushed out to clients for product updates. Obviously, We could just
keep track of the changes on a pad or write a database that requires
us to input those changes and eventually hand write the update
scripts. I was wondering if anybody has any solutions that may help
automate this process.

Is there a way to write an application that will compare a current
(updated) database structure against the last realease that will give
us the fields that need to be changed?

As far as creating the scripts for the initial install, thats easy. We
can do that right from the SQL Enterprise Manager.

Call me lazy! Any ideas?

ThanksDuncan (duncan.loxton@.gmail.com) writes:
> I'm looking for ideas on how to write SQL scripts for updates that are
> pushed out to clients for product updates. Obviously, We could just
> keep track of the changes on a pad or write a database that requires
> us to input those changes and eventually hand write the update
> scripts. I was wondering if anybody has any solutions that may help
> automate this process.
> Is there a way to write an application that will compare a current
> (updated) database structure against the last realease that will give
> us the fields that need to be changed?
> As far as creating the scripts for the initial install, thats easy. We
> can do that right from the SQL Enterprise Manager.

There a couple of products on the market. SQLCompare from Red Gate does
indeed compare two databases. DBGhost likes to tout itself as being
good for this. I have not use any of them.

Whatever method, you should keep all your code under version control,
and all your update scripts should have their foundation in the
version-control system. Basically a shipment is all changes between
the label for the previous shipment and this one. With some files added,
like triggers or indexes for changed tables.

In fact, once you have a good version control system up and running,
composing your scripts manually is not daunting task - but admittedly
it becomes boring after a while. The flip side is that you learn to
understand your process.

In fact, I started in our shop with something like this many years
ago. This has now evolved to a versatile toolset that we use. It is
available as freeware for anyone who want to try, see
http://www.abaris.se/abaperls/index.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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