Mirror of master, msdb, tempdb, or model databases.

Hi Guys,

Since,You cannot mirror the master, msdb, tempdb, or model databases.What will happen if i create new login,change existing security profile and new jobs,change of existing job on princicpal db. how these will be mirrored to other server and in case failover, how it will treat.

Thaks

Mirroring works at the database level only. dts/ssis packages, jobs etc are not mirrored. Database users are mirrored but not their corresponding SQL Server login.

You need to keep a copy of dts/ssis packages, jobs etc on the mirror server and whatever changes you make to them on the principal server, you will also have to make on the mirror server if you want to keep the servers exactly in sync.

hope this helps

Migration of SQL Serve r2000 packages

Hi,

In SQL Server 2005, when I tried to migrate existing SQL Server 2000 DTS packages using Migration Wizard, I ran into the following error message.

"Index was out of range.Must be non-negative and less than the size of the collection.

Parameter name: index[mscorlib]"

Please help!

Thanks,

KP

Please take a look at this earlier thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=130931&SiteID=1
|||Hi there,

Can you describe the problem you're seeing in detail so we can try reproducing it and fixing it?

Thanks,
Silviu

SQL Server team|||

Please check your packages for a space before or (more likely) after the name of the package. This causes the error described.

Unfortunately this problem is not currently detected by Upgrade Advisor when it scans package names for invalid characters.

-Doug

Migration of SQL Serve r2000 packages

Hi,

In SQL Server 2005, when I tried to migrate existing SQL Server 2000 DTS packages using Migration Wizard, I ran into the following error message.

"Index was out of range.Must be non-negative and less than the size of the collection.

Parameter name: index[mscorlib]"

Please help!

Thanks,

KP

Please take a look at this earlier thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=130931&SiteID=1|||Hi there,

Can you describe the problem you're seeing in detail so we can try reproducing it and fixing it?

Thanks,
Silviu

SQL Server team|||

Please check your packages for a space before or (more likely) after the name of the package. This causes the error described.

Unfortunately this problem is not currently detected by Upgrade Advisor when it scans package names for invalid characters.

-Doug

Migration data from SQL 7.0 to SQL 2005

I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
on to of the existing 7.0 instance and perfrom a full system upgrade. What I
don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
import the SQL 7.0 database and convert it for use inder 2005? I don't want
to burn my tracks by upgrdaing the existing server. If the install goes bad,
I can't fall back if nercessary. The books online for the lates preview don't
indicate the abaility migrate data fromj 7.0 as a stand alone process
sepparate from a full upgrade.
You should be able to detach the database from SQL Server 7.0 and attach it
to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
procedures. For more information about upgrading databases from previous
versions to SQL Server 2005, see SQL Server 2005 Books Online.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> I know that sQL 2005 support upgrading a SQL 7.0 database if you install
it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
want
> to burn my tracks by upgrdaing the existing server. If the install goes
bad,
> I can't fall back if nercessary. The books online for the lates preview
don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.
|||I have read the books online and I don't get the impression that you can
either directly attach a 7.0 database or alternatively, use the "copy
database wizard". Attempts to directly attach a SQL 7.0 database have failed
when initiated by scripts generated using the new scripting wizard. The “copy
database wizard” clearly states in its opening text that it can only be used
when copying SQL 2000 or 2005 data. There is no mention of copying SQL 7.0
data at all.
The books online recommend not using “sp_attach_db” command since it will no
longer be supported in the future. They suggest using the “CREAT DATABASE”
command in its place which is what the script generated by the wizard used.
In eaither case, the action failed.
"Narayana Vyas Kondreddi" wrote:

> You should be able to detach the database from SQL Server 7.0 and attach it
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> it
> I
> want
> bad,
> don't
>
>
|||OK, I got it to work. For some reason, scheduling the script to run via the
agent causes it to fail when executed. However, when I copied the script to
the query analyzer and executed it from there, it worked correctly.
"Narayana Vyas Kondreddi" wrote:

> You should be able to detach the database from SQL Server 7.0 and attach it
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> it
> I
> want
> bad,
> don't
>
>
|||For additional help with upgrading to SQL Server 2005 from earlier versions
of SQL Server, you may be interested in the SQL Server 2005 Upgrade Advisor:
http://www.microsoft.com/downloads/d...DisplayLang=en
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
>I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> I can't fall back if nercessary. The books online for the lates preview
> don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.

Migration data from SQL 7.0 to SQL 2005

I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
on to of the existing 7.0 instance and perfrom a full system upgrade. What I
don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
import the SQL 7.0 database and convert it for use inder 2005? I don't want
to burn my tracks by upgrdaing the existing server. If the install goes bad,
I can't fall back if nercessary. The books online for the lates preview don't
indicate the abaility migrate data fromj 7.0 as a stand alone process
sepparate from a full upgrade.You should be able to detach the database from SQL Server 7.0 and attach it
to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
procedures. For more information about upgrading databases from previous
versions to SQL Server 2005, see SQL Server 2005 Books Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> I know that sQL 2005 support upgrading a SQL 7.0 database if you install
it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
want
> to burn my tracks by upgrdaing the existing server. If the install goes
bad,
> I can't fall back if nercessary. The books online for the lates preview
don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.|||I have read the books online and I don't get the impression that you can
either directly attach a 7.0 database or alternatively, use the "copy
database wizard". Attempts to directly attach a SQL 7.0 database have failed
when initiated by scripts generated using the new scripting wizard. The â'copy
database wizardâ' clearly states in its opening text that it can only be used
when copying SQL 2000 or 2005 data. There is no mention of copying SQL 7.0
data at all.
The books online recommend not using â'sp_attach_dbâ' command since it will no
longer be supported in the future. They suggest using the â'CREAT DATABASEâ'
command in its place which is what the script generated by the wizard used.
In eaither case, the action failed.
"Narayana Vyas Kondreddi" wrote:
> You should be able to detach the database from SQL Server 7.0 and attach it
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> > I know that sQL 2005 support upgrading a SQL 7.0 database if you install
> it
> > on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> > don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> > import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> > to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> > I can't fall back if nercessary. The books online for the lates preview
> don't
> > indicate the abaility migrate data fromj 7.0 as a stand alone process
> > sepparate from a full upgrade.
>
>|||OK, I got it to work. For some reason, scheduling the script to run via the
agent causes it to fail when executed. However, when I copied the script to
the query analyzer and executed it from there, it worked correctly.
"Narayana Vyas Kondreddi" wrote:
> You should be able to detach the database from SQL Server 7.0 and attach it
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> > I know that sQL 2005 support upgrading a SQL 7.0 database if you install
> it
> > on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> > don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> > import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> > to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> > I can't fall back if nercessary. The books online for the lates preview
> don't
> > indicate the abaility migrate data fromj 7.0 as a stand alone process
> > sepparate from a full upgrade.
>
>|||For additional help with upgrading to SQL Server 2005 from earlier versions
of SQL Server, you may be interested in the SQL Server 2005 Upgrade Advisor:
http://www.microsoft.com/downloads/details.aspx?FamilyID=cf28daf9-182e-4ac2-8e88-f2e936558bf2&DisplayLang=en
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
>I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> I can't fall back if nercessary. The books online for the lates preview
> don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.

Migration data from SQL 7.0 to SQL 2005

I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
on to of the existing 7.0 instance and perfrom a full system upgrade. What I
don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
import the SQL 7.0 database and convert it for use inder 2005? I don't want
to burn my tracks by upgrdaing the existing server. If the install goes bad,
I can't fall back if nercessary. The books online for the lates preview don'
t
indicate the abaility migrate data fromj 7.0 as a stand alone process
sepparate from a full upgrade.You should be able to detach the database from SQL Server 7.0 and attach it
to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
procedures. For more information about upgrading databases from previous
versions to SQL Server 2005, see SQL Server 2005 Books Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> I know that sQL 2005 support upgrading a SQL 7.0 database if you install
it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
want
> to burn my tracks by upgrdaing the existing server. If the install goes
bad,
> I can't fall back if nercessary. The books online for the lates preview
don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.|||I have read the books online and I don't get the impression that you can
either directly attach a 7.0 database or alternatively, use the "copy
database wizard". Attempts to directly attach a SQL 7.0 database have failed
when initiated by scripts generated using the new scripting wizard. The “c
opy
database wizard” clearly states in its opening text that it can only be us
ed
when copying SQL 2000 or 2005 data. There is no mention of copying SQL 7.0
data at all.
The books online recommend not using “sp_attach_db” command since it wil
l no
longer be supported in the future. They suggest using the “CREAT DATABASE
command in its place which is what the script generated by the wizard used.
In eaither case, the action failed.
"Narayana Vyas Kondreddi" wrote:

> You should be able to detach the database from SQL Server 7.0 and attach i
t
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> it
> I
> want
> bad,
> don't
>
>|||OK, I got it to work. For some reason, scheduling the script to run via the
agent causes it to fail when executed. However, when I copied the script to
the query analyzer and executed it from there, it worked correctly.
"Narayana Vyas Kondreddi" wrote:

> You should be able to detach the database from SQL Server 7.0 and attach i
t
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> it
> I
> want
> bad,
> don't
>
>|||For additional help with upgrading to SQL Server 2005 from earlier versions
of SQL Server, you may be interested in the SQL Server 2005 Upgrade Advisor:
http://www.microsoft.com/downloads/...&DisplayLang=en
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
>I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> I can't fall back if nercessary. The books online for the lates preview
> don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.

Migrating to Internationalized Datatypes

Hi,
Can anyone tell me how can I move my existing datatypes to
Internationalized datatypes in SqlServer2000. i.e
CHAR to NCHAR
VARCHAR to NVARCHAR
TEXT to NTEXT
I am facing two Problems when I use Alter table Command to Modify the Column
Datatype.
1)
When I try to alter CHAR Column to NCHAR that has Index Defined, SQL
Server gives an error ( Alter Column failed because One or more Objects
access this column )
I tried by disabling the constraints on the table, but that also did not hel
p.
2) Conversion From TEXT to NTEXT seems to be Unsupported . On Alter of
text Column Error comes, Cannot alter column because it is 'text'
Thanks,
AnujHi
I would create an identical table in the DB with the new table structure.
Then use SELECT/INSERT statements to copy the data over, or use DTS to map
the columns and then let DTS copy the data over.
Rename the original table and then re-name the new table to the original
table.
Re-create all constraints, Clustered Indexes and other indexes.
In-place changes will cause a lot of data movement, pages splits and updates
to indexes, making it a slow process. As the same time, with doing the copy
to a new table, you can first verify that everything looks good before you
proceed with re-naming tables.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anuj Gupta" <Anuj Gupta@.discussions.microsoft.com> wrote in message
news:3076DEC3-4E4A-4873-96F8-692D4D335E05@.microsoft.com...
> Hi,
> Can anyone tell me how can I move my existing datatypes to
> Internationalized datatypes in SqlServer2000. i.e
> CHAR to NCHAR
> VARCHAR to NVARCHAR
> TEXT to NTEXT
> I am facing two Problems when I use Alter table Command to Modify the
Column
> Datatype.
> 1)
> When I try to alter CHAR Column to NCHAR that has Index Defined, SQL
> Server gives an error ( Alter Column failed because One or more Objects
> access this column )
> I tried by disabling the constraints on the table, but that also did not
help.
> 2) Conversion From TEXT to NTEXT seems to be Unsupported . On Alter of
> text Column Error comes, Cannot alter column because it is 'text'
> Thanks,
> Anuj