Is it possible to perform Sql 7 Enterprise upgrade to Sql 200o Standard Editions

Hi all,

I was curious if there any issues that I need to be aware off in attempting to upgrade an existing client database (Sql Server 7 Enterprise Edition) to Sql 2000 Standard edition. During upgrades are we forced to stay with the same editions or can the upgrade to lower level editions.

Thanks
ZeidNot possible, you need to uninstall and re-install SE.
EE is far superior than SE by any means.

Is it possible to generate alter Table statements using SMO

Hi

I'm trying to modify existing tables in a database.

How can I create alter Table scripts using SMO/DMO

Thank you

Yep, you can use the following to either execute and capture, just execute (which is the default) or just capture the executed commands:

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql

//s.ConnectionContext.CapturedSql.Text; //Get the Text

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks again Jens

I'm trying the follwong code

Server server1 = new Server(".");

Database db= server1.Databases["master"];

server1.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table Tbl in db.Tables)

{

tabl.Alter ();

}

db1.Refresh();

//writing to a file

writeToFile(server1.ConnectionContext.CapturedSql.Text, "alter", "tables");

But it is not generating Alter statments.

But if I use Create(), in place of alter(), it's generating Create statments.

|||Hi,
if you do not change anything, what are you supposed to see in the ALTER script :-) ?

In this sample I added a column to the table resulting in a script with an ALTER Script and an ADD column command.

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;

Table t = s.Databases["SMOTest"].Tables["TestTable"];

t.Columns.Add(new Column(t,"SomeSMOTest",DataType.DateTime));

t.Alter();

foreach (string st in s.ConnectionContext.CapturedSql.Text)

{

Console.WriteLine(st);

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens

Got it.

while comparing a table in one database to other table (identical) in other database,

if the Source table has some modified(altered) columns and need to be modified in the target table.

How to solve this problem.Any Idea.

I generated the alter scripts manually for each column.

Like

ALTER TABLE [dbo].[wo]

ADD [requested-time] varchar (8 ) NULL

Thank you

|||You will have to do this manually. Load the two schemas and compare the columns (if you just want to check the columns) with each other. Change the columns appropiately with SMO and get the script from the Context. if you want an integrated tool which can do this on its own use Visual Studio for database professionals, this does have a comparer and script generator for keeping the databases in sync.

HTH, Jens K. Suessmeyer.

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

Thanks alot Jens.

I'll try for this

Is it necessary to install Intergration Service for different instance ?

We have to install another instance to an existing SQL Server 2005 Server.
We would like to know whether it is necessary for us to select Integration
Service ? Besides, we would like to know besides Database Engine, is there
any other service we have to select ?
Moreover, we find that Reporting Services is shown as an instance when we
view "Installed Instance", we would like to know why it behaves as an
instance ?
In addition, it seems that even though we have installed default instance,
SQL Server 2005 still gives us a choice of installing Default Instance.
What will happen if we choose "Default Instance" if there is already have
one ? In SQL Server 2000, the choice of Default Instance is disabled.
Thanks
Peter> We would like to know whether it is necessary for us to select Integration Service ?
You can only have one instance of SSIS (which will serve all database engine instance that need to
use it). So, if SSIS is already installed and you select to install it again, setup will tell you
that it is already installed.
> Besides, we would like to know besides Database Engine, is there any other service we have to
> select ?
Not really. But only you know if you after installing this instance need also, say SSAS.
> Moreover, we find that Reporting Services is shown as an instance when we view "Installed
> Instance", we would like to know why it behaves as an instance ?
Because you can have several instances of RS, just the same way as you can have several instances of
the database engine.
> In addition, it seems that even though we have installed default instance, SQL Server 2005 still
> gives us a choice of installing Default Instance. What will happen if we choose "Default Instance"
> if there is already have one ?
Setup will tell you that it already is installed and won't do anything.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:e9v0AnyCIHA.4956@.TK2MSFTNGP06.phx.gbl...
> We have to install another instance to an existing SQL Server 2005 Server. We would like to know
> whether it is necessary for us to select Integration Service ? Besides, we would like to know
> besides Database Engine, is there any other service we have to select ?
> Moreover, we find that Reporting Services is shown as an instance when we view "Installed
> Instance", we would like to know why it behaves as an instance ?
> In addition, it seems that even though we have installed default instance, SQL Server 2005 still
> gives us a choice of installing Default Instance. What will happen if we choose "Default Instance"
> if there is already have one ? In SQL Server 2000, the choice of Default Instance is disabled.
> Thanks
> Peter
>|||Dear Tibor,
From your mail, my understanding is that for SSIS, only 1 instance can be
installed.
On the other hand, for others - like RS, AS, Database Engine, we can install
more than 1 instance.
Is there any other services that only installed once - Like Notification
Services ... ?
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZ9QyvzCIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> We would like to know whether it is necessary for us to select
>> Integration Service ?
> You can only have one instance of SSIS (which will serve all database
> engine instance that need to use it). So, if SSIS is already installed and
> you select to install it again, setup will tell you that it is already
> installed.
>> Besides, we would like to know besides Database Engine, is there any
>> other service we have to select ?
> Not really. But only you know if you after installing this instance need
> also, say SSAS.
>> Moreover, we find that Reporting Services is shown as an instance when we
>> view "Installed Instance", we would like to know why it behaves as an
>> instance ?
> Because you can have several instances of RS, just the same way as you can
> have several instances of the database engine.
>
>> In addition, it seems that even though we have installed default
>> instance, SQL Server 2005 still gives us a choice of installing Default
>> Instance. What will happen if we choose "Default Instance" if there is
>> already have one ?
> Setup will tell you that it already is installed and won't do anything.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:e9v0AnyCIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> We have to install another instance to an existing SQL Server 2005
>> Server. We would like to know whether it is necessary for us to select
>> Integration Service ? Besides, we would like to know besides Database
>> Engine, is there any other service we have to select ?
>> Moreover, we find that Reporting Services is shown as an instance when we
>> view "Installed Instance", we would like to know why it behaves as an
>> instance ?
>> In addition, it seems that even though we have installed default
>> instance, SQL Server 2005 still gives us a choice of installing Default
>> Instance. What will happen if we choose "Default Instance" if there is
>> already have one ? In SQL Server 2000, the choice of Default Instance is
>> disabled.
>> Thanks
>> Peter
>>
>|||Peter,
> From your mail, my understanding is that for SSIS, only 1 instance can be installed.
Correct.
> On the other hand, for others - like RS, AS, Database Engine, we can install more than 1 instance.
Also correct.
> Is there any other services that only installed once - Like Notification Services ... ?
Actually RS, AS and Db Engine are the only services for which you can install several instances.
You can only have one SQL Server browser - it doesn't make sense to have several.
You can only have one SQL Server VSS Writer - it doesn't make sense to have several.
You can only have one SSIS service
As for Notification Services (NS), you can only install it once. But you need to read about NS to
understand what that means. Installation of NS only installs some binary files (essentially). When
you develop an NS solution, you (among other things) run a program (NSCONTROL.EXE) to create the
Windows service. You can have several of these services, but that part is something you do *after*
you have installed the binary files. Also, NS will not ship with 2008, so it is essentially a dead
component which you probably don't want to build new solutions on.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:ez0wn7%23CIHA.4308@.TK2MSFTNGP06.phx.gbl...
> Dear Tibor,
> From your mail, my understanding is that for SSIS, only 1 instance can be installed.
> On the other hand, for others - like RS, AS, Database Engine, we can install more than 1 instance.
> Is there any other services that only installed once - Like Notification Services ... ?
> Peter
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uZ9QyvzCIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> We would like to know whether it is necessary for us to select Integration Service ?
>> You can only have one instance of SSIS (which will serve all database engine instance that need
>> to use it). So, if SSIS is already installed and you select to install it again, setup will tell
>> you that it is already installed.
>> Besides, we would like to know besides Database Engine, is there any other service we have to
>> select ?
>> Not really. But only you know if you after installing this instance need also, say SSAS.
>> Moreover, we find that Reporting Services is shown as an instance when we view "Installed
>> Instance", we would like to know why it behaves as an instance ?
>> Because you can have several instances of RS, just the same way as you can have several instances
>> of the database engine.
>>
>> In addition, it seems that even though we have installed default instance, SQL Server 2005 still
>> gives us a choice of installing Default Instance. What will happen if we choose "Default
>> Instance" if there is already have one ?
>> Setup will tell you that it already is installed and won't do anything.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:e9v0AnyCIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> We have to install another instance to an existing SQL Server 2005 Server. We would like to know
>> whether it is necessary for us to select Integration Service ? Besides, we would like to know
>> besides Database Engine, is there any other service we have to select ?
>> Moreover, we find that Reporting Services is shown as an instance when we view "Installed
>> Instance", we would like to know why it behaves as an instance ?
>> In addition, it seems that even though we have installed default instance, SQL Server 2005 still
>> gives us a choice of installing Default Instance. What will happen if we choose "Default
>> Instance" if there is already have one ? In SQL Server 2000, the choice of Default Instance is
>> disabled.
>> Thanks
>> Peter
>>
>>
>