Production vs. Test Environment, Linked Servers = Lots of Dynamic SQL!

We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?
You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegr oups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>
|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?
|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegr oups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>

Production vs. Test Environment, Linked Servers = Lots of Dynamic SQL!

We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Hi
I am not sure why you would need to change this unless the linked servers
had different names.
John
"bdtmike" wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Well, they do have separate names (they are separate servers).
John Bell wrote:
> Hi
> I am not sure why you would need to change this unless the linked servers
> had different names.
> John
> "bdtmike" wrote:
> > We have a SQL 2000 Database in both a production and test environment.
> > There are numerous processes and procedures in this database that must
> > communicate cross-server with another SQL 2000 database. This other
> > server also has a corresponding production / test environment.
> >
> > There are many references in my database to the linked server (dts &
> > sprocs). Add to this, when my database is being operated in the test
> > environment, the procedures and processes must reference the
> > corresponding test server for the other database. In other words, my
> > production database on my production server must reference their
> > production database on their production server. My database in the
> > testing server must reference their database in the testing server.
> >
> > I'd love to keep both databases on the same server for test &
> > production, but for business reasons, it simply can't be done.
> >
> > My problem is that when I move my production into testing (and back), I
> > must go through and meticulously change all the references in my
> > sprocs. Failure to change them all would be disasterous. Instead, I
> > wanted a dynamic way to do this so that the reference to the linked
> > server could be a variable which is set, depending on whether I'm
> > Production or Testing.
> >
> > Unfortunately, table names in SQL statements cannot be variable so what
> > I end up with is a TON of dynamic SQL. Certainly there must be a
> > better way to do this!
> >
> > Anybody have encountered this situation before?
> >
> >|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>|||But the linked server name can be the same!
Check out example 2 in the sp_addlinkedserver examples in BOL, specify
different data sources and the same linked server name.
John
"bdtmike" wrote:
> Well, they do have separate names (they are separate servers).
> John Bell wrote:
> > Hi
> >
> > I am not sure why you would need to change this unless the linked servers
> > had different names.
> >
> > John
> >
> > "bdtmike" wrote:
> >
> > > We have a SQL 2000 Database in both a production and test environment.
> > > There are numerous processes and procedures in this database that must
> > > communicate cross-server with another SQL 2000 database. This other
> > > server also has a corresponding production / test environment.
> > >
> > > There are many references in my database to the linked server (dts &
> > > sprocs). Add to this, when my database is being operated in the test
> > > environment, the procedures and processes must reference the
> > > corresponding test server for the other database. In other words, my
> > > production database on my production server must reference their
> > > production database on their production server. My database in the
> > > testing server must reference their database in the testing server.
> > >
> > > I'd love to keep both databases on the same server for test &
> > > production, but for business reasons, it simply can't be done.
> > >
> > > My problem is that when I move my production into testing (and back), I
> > > must go through and meticulously change all the references in my
> > > sprocs. Failure to change them all would be disasterous. Instead, I
> > > wanted a dynamic way to do this so that the reference to the linked
> > > server could be a variable which is set, depending on whether I'm
> > > Production or Testing.
> > >
> > > Unfortunately, table names in SQL statements cannot be variable so what
> > > I end up with is a TON of dynamic SQL. Certainly there must be a
> > > better way to do this!
> > >
> > > Anybody have encountered this situation before?
> > >
> > >
>|||Yes, in theory they could. But I'm saying that in my situation they
arent' the same name. And the chance that I'm going to get this
organization to change their server name just for me is...Zero.|||Any changes to code after it has been tested introduces a risk which they
should be made aware off. If this risk is removed by having a better
configuration then it should be acceptable.
John
"bdtmike" wrote:
> Yes, in theory they could. But I'm saying that in my situation they
> arent' the same name. And the chance that I'm going to get this
> organization to change their server name just for me is...Zero.
>

Production vs. Test Environment, Linked Servers = Lots of Dynamic SQL!

We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>

production or test server...or both

Hi, We currently have SQL2000 running in a production environment with 2 commercial applications. I would like to use the same server to upsize some access applications, test and develope new internal applications. I have been warned by our IS dept, however, that I should not have any test applications on a production server as I run the risk of closing down/screwing up the performance the server (looping queries...etc) through the test applications.
They say I should buy another server, set alone as a test server, and only add the internal applications to the production server after thorough testing.
Of course , our IS dept offers no test or production server for our dept to use - so this gets pretty expensive for us.
My question - can I do both on one server or not?
thanks!
JeffIMHO I agree with your IS dept 100%. Now if your Production box has ample resources and your Production apps can stand some interference or down time due to a screw ups then why not.

BIG DISCALMER!! I can guarantee you that at some point someone will run something that will drag the system down or even (gulp) bring the server down. Just like your IS guys have pointed out.

In some shops the above would be unacceptable, maybe not in yours. You sort of have to way all the pros and cons. Would a low end server really cost that much considering the potential problem?|||RE: They say I should buy another server, set alone as a test server, and only add the internal applications to the production server after thorough testing. Of course , our IS dept offers no test or production server for our dept to use - so this gets pretty expensive for us.

Q1 Why not simply buy and use a low end workstation pc (to lower the costs install Win 2k Pro or XP) with the dev version (or even the 120 day eval) of Sql Server. One ought to be able to easily create that kind of test server for under $1K.

Q2 If $1K is too much, how about making some poor user's workstation (say yours) a dual boot OS box (install the test platform on the new install)? If you have test workstations and you are the one doing the testing you wouldn't need your workstation while you are doing tests anyway, or would you?|||Be very careful of installing evaluation editions of anything by microsoft. I did this with sql server version 7 and had to reinstall which destroyed everything - according to documentation ms has gotten better - but do so at your own risk. Anyway, a better solution is to use msde 1 or 2000 depending on what your solution requires. The following is a link that tells you how to get msde ...

link (http://support.microsoft.com/default.aspx?scid=KB;en-us;324998&)|||Oh - to answer your question - of course you can install anything on the production server - the question is should you. Some people will debate that it is ok to have a test instance with a production instance. However, you can have so many issues with this design. IMO - You use the production for production only - no matter how well stacked your machine is - I have seen enough problems (like memory leaks, over enthusiastic sql statements ...) - Just get a cheapy peepy machine and install want you need - find free software or existing licenses so you have no/minimal costs with the software part.|||RE: Be very careful of installing evaluation editions of anything by microsoft. I did this with sql server version 7 and had to reinstall which destroyed everything - according to documentation ms has gotten better - but do so at your own risk.

Note: The low cost possibility (using a 120 day eval edition) was explicitly made in reference to use on a test server (as a means to further lower total test server costs).

RE: Anyway, a better solution is to use msde 1 or 2000 depending on what your solution requires.

NOTE: MSDE (and Desktop Sql Server editions) do have some intentional limitations built in. As long as you do not need to test any non-MSDE functionality, e.g. (DBs <= 2GB), MSDE is a good general functionality test environment (otherwise, the Sql Server dev. version is often "best", at a fairly reasonable cost). Accordingly, unless specifically deploying to MSDE, avoid using MSDE for any scalability / load testing purposes (or as a substitute for valid Sql Server product performance comparisons).|||MSDE is fully compatible with sql server - so once you have the money to buy that developers edition - no problems mate. Obviously, if you have the $500, then buy the developers edition. Even further - here is a quote from microsoft regarding msde:

"An attractive alternative to using the Microsoft Jet database, MSDE 2000 is designed primarily to provide a low-cost option for developers who need a database server that can be easily distributed and installed with a value-added business solution. Because it is fully compatible with other editions of SQL Server, developers can easily target both SQL Server and MSDE 2000 with the same core code base. This provides a seamless upgrade path from MSDE 2000 to SQL Server if an application grows beyond the storage and scalability limits of MSDE 2000."

MSDE is free without the headaches of the evaluation edition - a no brainer.

As far as the limitations - for the purpose of upsizing from access - this is no problem - even with the size limitation, since it is the same as access. The following article goes into depth about this:

article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acmsdeop.asp)|||MSDE makes for a good general functionality test environment.

MSDE also often serves a variety of production purposes very well, (even though MSDE does have intentional limitations built in). However, MSDE is a licensed use product with licensed use requirements.

Some information (from MS) on some MSDE limitations: MSDE was TUNED { - "TUNED" may be regarded as a synonym for "CONSTRAINED" in this case - } to deliver the same performance you'd get using the SQL Server enterprise engine AT UP TO FIVE CONCURRENT USERS, and supports up to 2 gigabytes (GB) of data for any individual database. The storage limit is per database, not per server. A single MSDE server can support multiple MSDE databases, each containing up to the 2-GB limit. For more users, or when you need more data storage, you should use SQL Server or SQL Server Enterprise Edition for optimal performance at this higher level of scalability.

Unlike SQL Server, MSDE can't be a replication publisher (although it can act as a replication subscriber) in a transactional replication environment. Also, note that replication of MSDE databases is only possible if SQL Server Client Access Licenses (CALs) are purchased. If you require replication publishing, you'll want to use SQL Server Desktop rather than MSDE. (A CAL is also required for SQL Server Desktop.)


Some information (from MS) on products that permit licensed "free" use of MSDE:

Products that enable use and redistribution of MSDE:
SQL Server 2000 (Developer, Standard, and Enterprise Editions)
Visual Studio .NET (Architect, Developer, and Professional Editions)
ASP.NET Web Matrix Tool
Office XP Developer Edition
MSDN Universal and Enterprise subscriptions

Products that enable use but not redistribution:
Microsoft Access

Some other possible considerations: (see http://www.microsoft.com/sql/howtobuy/msdeuse.asp)
Using MSDE does not reduce or eliminate the need for CALs when interacting with SQL Server 2000 Standard Edition or SQL Server 2000 Enterprise Edition in a production environment.

Q. Can I use SQL Server tools and services in conjunction with MSDE?
A. You can only use SQL Server tools and services in conjunction with MSDE if you acquired MSDE via SQL Server 2000 (any edition) or if you are using MSDE in conjunction with a properly licensed copy of SQL Server 2000. Visit the How to Buy page to obtain a valid SQL Server license.

The following utilities are installed by the MSDE setup application and are provided without restrictions for use with the copy of MSDE that is installed by your application: bcp.exe, cnfgsvr.exe, dcomscm.exe, osql.exe, sqlmangr.exe, scm.exe, sqladhlp.exe, and svrnetcn.exe. The dtsrun.exe utility is also provided, but may not be used during development.

Note: The following Visual Studio family of products also include the right to use and redistribute MSDE in this scenario: Microsoft Visual Basic .NET, Visual C# .NET, Visual C++ .NET, and Visual J# .NET.

Several Microsoft product licenses convey the right to use and redistribute the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) version 2.0. In all cases, you should refer to the End User License Agreement (EULA) for a full statement of the rights conveyed by your product license.

Processor Upgrade on a sql cluster

In our environment we have a cluster active standby with a shared storage
iscsi
The two machines are Windows 2000 Advanced Server with Intel Xeon
Single-Core 3.4Ghz with SQL Server 2000
Enterprise Edition.
Now we want to sustitute these two with another two machines with
Dual Intel Xeon Dual-Core 3.6Ghz
We want to clone the actual machines in the new environment cause we want a
current machine for disaster recovery and cause we want also to test the
disaster recovery plan...
1) the two new dual core processors are view from operating system as a four
processor is this configuration supported with the environment Windows 2000
Advanced Server and SQL 2000 Server Enterprise Edition ?
2)when we have transferred the disk and the configuration and the boot is ok
and sql services are started what tuning and best practice we have to apply
for the new environment for tuning performance with new dual vs single cpu
node
Hi Davide,
1) Yes, you can manage 4 processors with these licenses.
2) We need more information about your environment to answer this. Generally
speaking, you should take care of CPU affinity and IO affinity, and take a
look at your files and filegroups configurations, especially on tempdb
database
Regards
Antonio Soto
Solid Quality Leaning
http://www.solidqualitylearning.com
DISCLAIMER:
Anything written in this message represents solely the point of view of the
sender. This message does not imply endorsement from Solid Quality Learning,
and it does not represent the point of view of Solid Quality Learning or any
other person, company or institution mentioned in this message
"Dr Davide BOmbarda" <DrDavideBOmbarda@.discussions.microsoft.com> escribi
en el mensaje news:CC20F619-9ED5-4CBB-BA62-B23AF4B2C75B@.microsoft.com...
> In our environment we have a cluster active standby with a shared storage
> iscsi
> The two machines are Windows 2000 Advanced Server with Intel Xeon
> Single-Core 3.4Ghz with SQL Server 2000
> Enterprise Edition.
> Now we want to sustitute these two with another two machines with
> Dual Intel Xeon Dual-Core 3.6Ghz
> We want to clone the actual machines in the new environment cause we want
> a
> current machine for disaster recovery and cause we want also to test the
> disaster recovery plan...
> 1) the two new dual core processors are view from operating system as a
> four
> processor is this configuration supported with the environment Windows
> 2000
> Advanced Server and SQL 2000 Server Enterprise Edition ?
> 2)when we have transferred the disk and the configuration and the boot is
> ok
> and sql services are started what tuning and best practice we have to
> apply
> for the new environment for tuning performance with new dual vs single cpu
> node
>
>
|||I/O affinity only comes into play with the Datacenter Edition when working
with specific SAN configurations. Even then, you need to be pushing the I/O
channel pretty hard to even see any effect.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:umQqsqpKGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hi Davide,
> 1) Yes, you can manage 4 processors with these licenses.
> 2) We need more information about your environment to answer this.
> Generally speaking, you should take care of CPU affinity and IO affinity,
> and take a look at your files and filegroups configurations, especially on
> tempdb database
> Regards
> Antonio Soto
> Solid Quality Leaning
> http://www.solidqualitylearning.com
> DISCLAIMER:
> Anything written in this message represents solely the point of view of
> the sender. This message does not imply endorsement from Solid Quality
> Learning, and it does not represent the point of view of Solid Quality
> Learning or any other person, company or institution mentioned in this
> message
>
>
> "Dr Davide BOmbarda" <DrDavideBOmbarda@.discussions.microsoft.com> escribi
> en el mensaje news:CC20F619-9ED5-4CBB-BA62-B23AF4B2C75B@.microsoft.com...
>

processing dimension does nothing for 12 hours then continues suddenly

Processing our large dimensions on our production 64-bit hardware has worked fine. Our dev environment is 32-bit and has SQL, SSRS, SSIS, and SSAS on the same box. The /3GB switch seems to make lots of things unstable so we're only dealing with 2GB that SSAS could possibly use, and half of that is consumed by other processes. So I'm having trouble getting the cube to process due to low memory availability and large dimensions. I've changed MaxParallel to 1 so that it won't do multiple dimensions or partitions in parallel. That usually prevents it from running out of memory and failing. I'm still having sporatic problems.

Specifically, last night we saw processing hang for over 12 hours right in the middle of processing one of the largest dimensions then suddenly pick up and continue on. There were no other workloads going on that box, so I'm looking for an explanation of what SSAS was doing all that time? Before it kicks off the next bit of processing, I know it looks to see if there are enough resources. If there are not enough resources, what does it do? Does it sleep then check again after a while? What's it waiting for, and what can I do to make it happy so it continues when it gets into a wait state like that?

Here are my log files from this oddity last night:

6/4/2007 6:39:50 PM (ProgressReportBegin, WriteDecode): Started processing the 'Account Number' hierarchy.
6/4/2007 6:39:50 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 0
6/4/2007 6:40:14 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 1
6/4/2007 6:40:14 PM (ProgressReportEnd, WriteDecode): Finished processing the 'Account Number' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportBegin, WriteDecode): Started processing the 'First Name' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 0
6/5/2007 8:54:32 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 1
6/5/2007 8:54:32 AM (ProgressReportEnd, WriteDecode): Finished processing the 'First Name' hierarchy.

Hi Greg,

I had some problems with SSAS 2005 getting stuck during processing. For me it was allways during cube processing (vs dimension processing). We had one suggestion from Microsoft, that actually sometimes worked. We were told to execute simple MDX query to SSAS to wake it up. Unfortunatelly that did not work consistently.

What worked for us is changing SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150.

As in your case your dimension started around 9:00am (somebody in the office), there is very good chance that somebody submitted some query to server and wake it up.

More information about problem like mine is here:

http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx

Regards,

Vidas Matelis

|||

I'll try that tip about submitting an MDX query and report back if that works. I had read Wayne's post but had completely forgotten about the "submit a simple MDX query" tip. Thanks Vidas.

As for raising the # of threads, I'll play with that, too. Since we've got MaxParallel=1 to prevent it from doing so much in parallel it runs out of memory (whether that's the best idea, I don't know) I certainly don't think it's short on threads. But I can easily check in perfmon.

|||

The suggestion of "run an MDX query and that will wake up the processing" does seem to do the trick. One problem, though, is when your cube isn't processed, you can't run an MDX query to wake it up. The best solution I've found is to run a Clear Cache statement:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>

processing dimension does nothing for 12 hours then continues suddenly

Processing our large dimensions on our production 64-bit hardware has worked fine. Our dev environment is 32-bit and has SQL, SSRS, SSIS, and SSAS on the same box. The /3GB switch seems to make lots of things unstable so we're only dealing with 2GB that SSAS could possibly use, and half of that is consumed by other processes. So I'm having trouble getting the cube to process due to low memory availability and large dimensions. I've changed MaxParallel to 1 so that it won't do multiple dimensions or partitions in parallel. That usually prevents it from running out of memory and failing. I'm still having sporatic problems.

Specifically, last night we saw processing hang for over 12 hours right in the middle of processing one of the largest dimensions then suddenly pick up and continue on. There were no other workloads going on that box, so I'm looking for an explanation of what SSAS was doing all that time? Before it kicks off the next bit of processing, I know it looks to see if there are enough resources. If there are not enough resources, what does it do? Does it sleep then check again after a while? What's it waiting for, and what can I do to make it happy so it continues when it gets into a wait state like that?

Here are my log files from this oddity last night:

6/4/2007 6:39:50 PM (ProgressReportBegin, WriteDecode): Started processing the 'Account Number' hierarchy.
6/4/2007 6:39:50 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 0
6/4/2007 6:40:14 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 1
6/4/2007 6:40:14 PM (ProgressReportEnd, WriteDecode): Finished processing the 'Account Number' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportBegin, WriteDecode): Started processing the 'First Name' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 0
6/5/2007 8:54:32 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 1
6/5/2007 8:54:32 AM (ProgressReportEnd, WriteDecode): Finished processing the 'First Name' hierarchy.

Hi Greg,

I had some problems with SSAS 2005 getting stuck during processing. For me it was allways during cube processing (vs dimension processing). We had one suggestion from Microsoft, that actually sometimes worked. We were told to execute simple MDX query to SSAS to wake it up. Unfortunatelly that did not work consistently.

What worked for us is changing SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150.

As in your case your dimension started around 9:00am (somebody in the office), there is very good chance that somebody submitted some query to server and wake it up.

More information about problem like mine is here:

http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx

Regards,

Vidas Matelis

|||

I'll try that tip about submitting an MDX query and report back if that works. I had read Wayne's post but had completely forgotten about the "submit a simple MDX query" tip. Thanks Vidas.

As for raising the # of threads, I'll play with that, too. Since we've got MaxParallel=1 to prevent it from doing so much in parallel it runs out of memory (whether that's the best idea, I don't know) I certainly don't think it's short on threads. But I can easily check in perfmon.

|||

The suggestion of "run an MDX query and that will wake up the processing" does seem to do the trick. One problem, though, is when your cube isn't processed, you can't run an MDX query to wake it up. The best solution I've found is to run a Clear Cache statement:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>

processing dimension does nothing for 12 hours then continues suddenly

Processing our large dimensions on our production 64-bit hardware has worked fine. Our dev environment is 32-bit and has SQL, SSRS, SSIS, and SSAS on the same box. The /3GB switch seems to make lots of things unstable so we're only dealing with 2GB that SSAS could possibly use, and half of that is consumed by other processes. So I'm having trouble getting the cube to process due to low memory availability and large dimensions. I've changed MaxParallel to 1 so that it won't do multiple dimensions or partitions in parallel. That usually prevents it from running out of memory and failing. I'm still having sporatic problems.

Specifically, last night we saw processing hang for over 12 hours right in the middle of processing one of the largest dimensions then suddenly pick up and continue on. There were no other workloads going on that box, so I'm looking for an explanation of what SSAS was doing all that time? Before it kicks off the next bit of processing, I know it looks to see if there are enough resources. If there are not enough resources, what does it do? Does it sleep then check again after a while? What's it waiting for, and what can I do to make it happy so it continues when it gets into a wait state like that?

Here are my log files from this oddity last night:

6/4/2007 6:39:50 PM (ProgressReportBegin, WriteDecode): Started processing the 'Account Number' hierarchy.
6/4/2007 6:39:50 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 0
6/4/2007 6:40:14 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 1
6/4/2007 6:40:14 PM (ProgressReportEnd, WriteDecode): Finished processing the 'Account Number' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportBegin, WriteDecode): Started processing the 'First Name' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 0
6/5/2007 8:54:32 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 1
6/5/2007 8:54:32 AM (ProgressReportEnd, WriteDecode): Finished processing the 'First Name' hierarchy.

Hi Greg,

I had some problems with SSAS 2005 getting stuck during processing. For me it was allways during cube processing (vs dimension processing). We had one suggestion from Microsoft, that actually sometimes worked. We were told to execute simple MDX query to SSAS to wake it up. Unfortunatelly that did not work consistently.

What worked for us is changing SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150.

As in your case your dimension started around 9:00am (somebody in the office), there is very good chance that somebody submitted some query to server and wake it up.

More information about problem like mine is here:

http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx

Regards,

Vidas Matelis

|||

I'll try that tip about submitting an MDX query and report back if that works. I had read Wayne's post but had completely forgotten about the "submit a simple MDX query" tip. Thanks Vidas.

As for raising the # of threads, I'll play with that, too. Since we've got MaxParallel=1 to prevent it from doing so much in parallel it runs out of memory (whether that's the best idea, I don't know) I certainly don't think it's short on threads. But I can easily check in perfmon.

|||

The suggestion of "run an MDX query and that will wake up the processing" does seem to do the trick. One problem, though, is when your cube isn't processed, you can't run an MDX query to wake it up. The best solution I've found is to run a Clear Cache statement:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing AS2000 Cubes from SSIS

Hi All,

I have a scenario where I want to execute AS 2000 Cubes from a SSIS package. In my Prod environment I have two servers one with the SQL 2000 database and the AS2000 cubes on it and the other with SSIS installed on it.

What I am doing here is I have a DTS package with a process cube task in it, this DTS package is saved as a structured file and then I call this DTS package from SSIS using the Execurte 2000 package task. Just FYI the process cubes task is using local as the server reference. I want to know how this will work when I execute this DTS package from within an SSIS package running on a different server? Appreciate all help.

Thanks

The "normal" way to process AS 2000 from DTS was to use the OLAP Processing task for the job, but that only gets installed as part of AS 2000, and hence will not be available on your SSIS/DTS machine. Using local as the server reference will also fail, unless you run the DTS package on the AS server, as quite obviously the local server is not the AS server, for a package running on SSIS/DTS machine. There is no magic way to get the DTS package to run on the other machine, other than calling the package from that machine itself. I would stick with a DTS package on the AS2000 box.

|||

Thanks Darren,

Actually I do have DTS and AS2000 installed on the same server which also has SQL Server 2000 databases on it, However I am executing these DTS packages (which process the cubes using local as server reference) from SSIS packages which are running on a different server. The DTS packages are saved as structured files on the SSIS server. Hope I am explaining it properly - what my concern is, whether executing these DTS packages having process cubes task within it from a different server will work?

Appreciate your help.

Thanks

|||

I Still have this concern whether my packages will run or not, it would be better to know now rather than getting to know in prod. Any help is appreciated.

Thanks

|||No this will not work. All the AS stuff is on the wrong server, it needs to be on the server upon which the DTS package runs. If calling DTS from SSIS then that means the DTS package runs on the same server as SSIS, which is not the AS server. This is all about execution location, which for both DTS and SSIS is cleint side, they are not client/server like SQL Server.|||

Thanks Darren,

Appreciate your help.

Thanks

|||

Hi,

I have a Cube on my AS2000 server running on the same machine as my SQL server 2000. I can execute the DTS from the SQL Enterprise manager properly.

I need to implement a C# code that will enable me to execute the DTS. I already have one as follows which works on any DTS:

---

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(serverName,null,null,DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,null,null,null,packageName,ref pVarPersistStgOfHost);

package.Execute();

---

As I said, it works with any DTS, but as soon as I work with one which Processes Cubes, it hangs for about 2 hours, when the cube can normally be processed in 20 seconds, and crash on the following

DTS Execution error:

Do you have a clue why is it so please?

|||

I've been thinking about this some more. As I recall you can process OLAP 200 cubes remotely, well I think you could in DTS, so all you need is -

1 DTS

2 DSO

3 OLAP Task

Now in SSIS we have 1 already. 2 we can get, see the feature pack download on MS site April or November. For 3, the task, it seems that the documenattion says you need to install AS 200 to get this, well as I recall it was actually just a DTS custom task that used DSO, so why not go get the DLL, register it by hand in DTS, and give it a try -

The task DLL should be in X:\Program Files\Microsoft Analysis Services\Bin\msmdtsp.dll on any SQL 2000 box with AS installed.

|||

Darren,

I am using SQL Server 2000. I do not have SSIS and I have to get this sorted way before we upgrade to 2005.

1. Are there any issues with the methods LoadFromSQLServer from the class Package2Class when we invoke a DTS with OLAP Cube.

2. I have already installed Analysis Services. When I deployed my application to a test web server which has SQL client installed, I have a COM exception. Does it mean that I need to have AS2000 installed in order for LoadFromSQLServer to instantiate the correct steps object which are of type OLAP tasks?

Can you please advise me on the coding and permission stuff that will allow a code on .net 1.1 (since it is an upgrade on a legacy system) to launch a DTS on SQL2000 which processes a cube on AS2000?

Thanks

WaaZ

|||

WaaZ, I was replying to the thread from db_guy. I'm not sure why you have posted a DTS question in the SSIS forum, and it is not really the same issue anyway, as you are not using SSIS at all. A better thread would make more sense to start with, but this is a SSIS focused forum I'm afraid. I can branch into a new thread if you want.

1 - No issues with that method that would not be raised if using another execution host in the same location and context. Instead of running your application, try running DTSRUN in the same place, as the same user, and see what errors you get.

2 - To load a DTS package, you need DTS installed locally. If you use the AS Proc Task, then you need AS support and the AS task also installed locally.

|||

Thanks Darren,

Finally we decided that we will be processing the cubes using DTS and not SSIS, but I will surely try to test the stuff you mentioned , will let you know soon.

Thanks

Processing AS2000 Cubes from SSIS

Hi All,

I have a scenario where I want to execute AS 2000 Cubes from a SSIS package. In my Prod environment I have two servers one with the SQL 2000 database and the AS2000 cubes on it and the other with SSIS installed on it.

What I am doing here is I have a DTS package with a process cube task in it, this DTS package is saved as a structured file and then I call this DTS package from SSIS using the Execurte 2000 package task. Just FYI the process cubes task is using local as the server reference. I want to know how this will work when I execute this DTS package from within an SSIS package running on a different server? Appreciate all help.

Thanks

The "normal" way to process AS 2000 from DTS was to use the OLAP Processing task for the job, but that only gets installed as part of AS 2000, and hence will not be available on your SSIS/DTS machine. Using local as the server reference will also fail, unless you run the DTS package on the AS server, as quite obviously the local server is not the AS server, for a package running on SSIS/DTS machine. There is no magic way to get the DTS package to run on the other machine, other than calling the package from that machine itself. I would stick with a DTS package on the AS2000 box.

|||

Thanks Darren,

Actually I do have DTS and AS2000 installed on the same server which also has SQL Server 2000 databases on it, However I am executing these DTS packages (which process the cubes using local as server reference) from SSIS packages which are running on a different server. The DTS packages are saved as structured files on the SSIS server. Hope I am explaining it properly - what my concern is, whether executing these DTS packages having process cubes task within it from a different server will work?

Appreciate your help.

Thanks

|||

I Still have this concern whether my packages will run or not, it would be better to know now rather than getting to know in prod. Any help is appreciated.

Thanks

|||No this will not work. All the AS stuff is on the wrong server, it needs to be on the server upon which the DTS package runs. If calling DTS from SSIS then that means the DTS package runs on the same server as SSIS, which is not the AS server. This is all about execution location, which for both DTS and SSIS is cleint side, they are not client/server like SQL Server.|||

Thanks Darren,

Appreciate your help.

Thanks

|||

Hi,

I have a Cube on my AS2000 server running on the same machine as my SQL server 2000. I can execute the DTS from the SQL Enterprise manager properly.

I need to implement a C# code that will enable me to execute the DTS. I already have one as follows which works on any DTS:

---

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(serverName,null,null,DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,null,null,null,packageName,ref pVarPersistStgOfHost);

package.Execute();

---

As I said, it works with any DTS, but as soon as I work with one which Processes Cubes, it hangs for about 2 hours, when the cube can normally be processed in 20 seconds, and crash on the following

DTS Execution error:

Do you have a clue why is it so please?

|||

I've been thinking about this some more. As I recall you can process OLAP 200 cubes remotely, well I think you could in DTS, so all you need is -

1 DTS

2 DSO

3 OLAP Task

Now in SSIS we have 1 already. 2 we can get, see the feature pack download on MS site April or November. For 3, the task, it seems that the documenattion says you need to install AS 200 to get this, well as I recall it was actually just a DTS custom task that used DSO, so why not go get the DLL, register it by hand in DTS, and give it a try -

The task DLL should be in X:\Program Files\Microsoft Analysis Services\Bin\msmdtsp.dll on any SQL 2000 box with AS installed.

|||

Darren,

I am using SQL Server 2000. I do not have SSIS and I have to get this sorted way before we upgrade to 2005.

1. Are there any issues with the methods LoadFromSQLServer from the class Package2Class when we invoke a DTS with OLAP Cube.

2. I have already installed Analysis Services. When I deployed my application to a test web server which has SQL client installed, I have a COM exception. Does it mean that I need to have AS2000 installed in order for LoadFromSQLServer to instantiate the correct steps object which are of type OLAP tasks?

Can you please advise me on the coding and permission stuff that will allow a code on .net 1.1 (since it is an upgrade on a legacy system) to launch a DTS on SQL2000 which processes a cube on AS2000?

Thanks

WaaZ

|||

WaaZ, I was replying to the thread from db_guy. I'm not sure why you have posted a DTS question in the SSIS forum, and it is not really the same issue anyway, as you are not using SSIS at all. A better thread would make more sense to start with, but this is a SSIS focused forum I'm afraid. I can branch into a new thread if you want.

1 - No issues with that method that would not be raised if using another execution host in the same location and context. Instead of running your application, try running DTSRUN in the same place, as the same user, and see what errors you get.

2 - To load a DTS package, you need DTS installed locally. If you use the AS Proc Task, then you need AS support and the AS task also installed locally.

|||

Thanks Darren,

Finally we decided that we will be processing the cubes using DTS and not SSIS, but I will surely try to test the stuff you mentioned , will let you know soon.

Thanks

Process SQL Transactions? Easy Question :)

Hi,

Environment - VB.NET, ASP.NET, SQL Server 2000.

In SQL 2000, I am sending an XML, which carries data for two tables. Let's say, I am inserting half of the fields in TABLE1 and rest in TABLE2.

Specifically, I want to use Transaction Processing for inserting the rows in both tables. If insertion in one table fails, any inserted data should ROLLBACK and come out of procedure with relevant error code.

Please advice or send me any example links. Thanks

PankajHave you looked at the Begin Tansaction examples in the BOL? That contains an example with the commit transaction but not rollback transaction. I'm assuming you'll do the inserts in one SP. If that's true then you can follow those examples to help do your inserts and then test for errors and rollback if errors are encountered. Something like
SP...

BEGIN TRANSACTION
Insert code here

IF @.@.Error <> 0
ROLLBACK TRANS
ELSE
COMMIT TRANS

That's a rough idea but hopefully will get you started.

Process Cube

I have a cube with one customer count measure that use DistinctCount Aggregate Function. My testing Environment is Window Server 2003 x64 Sp1, 300 GB of Ram, and SQL 2005 EE. When I full process the cube, I use the MS Performance tool to monitor the MSAS:Proc Aggregations. I find out the Temp file bytes written/sec has been used, but the usage of the RAM never go above 15 GB. Why the temp file still in use. When I use AS2000 I know I can adjust the Process Buffer size to avoid the use of Temp file to increase the process performance. Should I take the same action in AS2005. But my OLAP\Process\BufferMemoryLimit has been set to 60 => 180GB can be use. Why temp file still in use? Should I adjust the OLAP\ProcessPlan\DistinctBuffer too that is the new item in AS2005?

Analysis Services 2005 is quite different in the way it does processing and manages memory.

First I would recommend you look at the performance guide for some clues on how to improve processing performance. I also not sure what the real concern is: is that your are seeing Analysis Server using less memory during processing? Is that you compare AS2000 and AS2005 and you are seeing slower processing performance?

Just to note, and you might know it alreday, processing of distinct count partitions is quite different from processing of regular partitions. Same goes for querying. Records stored in distinct count partitions as stored sorted. You can even see that for processing such partition Analysis Server will send different SQL query, it will ask data from SQL Server to come in sorted order.
What I am getting at is processing and querying of distinct count partitions is quite different from regular partitions. If you wanted to optimize processing and query performance the first and most important is to make sure your partition your cube along dimension that you get your distinct count for.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||my concern is how the memory usage in AS2005. Since my server has 300GB of memory, but AS2005 doesn't use up all the memory during the process, instead it use the Temp file that can slow down the process performance.