SQL Server Management Studio: machine

Showing posts with label machine. Show all posts

Friday, March 30, 2012

MIgrating DTS packages from one machine to another

I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for an
exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
user databases. I can move databases, logins, scheduled jobs. I have lot of
DTS packages and Maintenance Plans. Is there an easy way to recreate these
DTS packages and Maintenance plans on the new server with out creating each
one of them manually?
--
RKYou can open the package on one server, using DTS Designer, and then using
[Package], [Save As], save it to a different server.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
>I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
>an
> exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> user databases. I can move databases, logins, scheduled jobs. I have lot
> of
> DTS packages and Maintenance Plans. Is there an easy way to recreate these
> DTS packages and Maintenance plans on the new server with out creating
> each
> one of them manually?
> --
> RK|||Arnie Rowland wrote:[vbcol=seagreen]
> You can open the package on one server, using DTS Designer, and then using
> [Package], [Save As], save it to a different server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
http://www.sqldts.com/?204
Regards
Amish Shah

Wednesday, March 28, 2012

MIgrating DTS packages from one machine to another

I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for an
exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
user databases. I can move databases, logins, scheduled jobs. I have lot of
DTS packages and Maintenance Plans. Is there an easy way to recreate these
DTS packages and Maintenance plans on the new server with out creating each
one of them manually?
--
RKYou can open the package on one server, using DTS Designer, and then using
[Package], [Save As], save it to a different server.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
>I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
>an
> exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> user databases. I can move databases, logins, scheduled jobs. I have lot
> of
> DTS packages and Maintenance Plans. Is there an easy way to recreate these
> DTS packages and Maintenance plans on the new server with out creating
> each
> one of them manually?
> --
> RK|||Arnie Rowland wrote:
> You can open the package on one server, using DTS Designer, and then using
> [Package], [Save As], save it to a different server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
> >I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
> >an
> > exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> > user databases. I can move databases, logins, scheduled jobs. I have lot
> > of
> > DTS packages and Maintenance Plans. Is there an easy way to recreate these
> > DTS packages and Maintenance plans on the new server with out creating
> > each
> > one of them manually?
> > --
> > RK
http://www.sqldts.com/?204
Regards
Amish Shah

Monday, March 26, 2012

Migrating Analysis Services Roles

Hi,
I have an Analysis Services database that contains 5 cubes. Is it possible
to migrate the database and cube roles from a development machine to a
production machine by performing a file copy? If so, exactly what has to
copied. If not, what is the best way to do it if it is possible.
Thanks!
Look at the Release Management section in the AS Operations Guide
http://www.microsoft.com/technet/pro.../anservog.mspx
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:7204F1A0-8FA6-47BB-8023-FB18D845CAF0@.microsoft.com...
> Hi,
> I have an Analysis Services database that contains 5 cubes. Is it possible
> to migrate the database and cube roles from a development machine to a
> production machine by performing a file copy? If so, exactly what has to
> copied. If not, what is the best way to do it if it is possible.
> Thanks!
sql

Migrating Analysis Services Roles

Hi,
I have an Analysis Services database that contains 5 cubes. Is it possible
to migrate the database and cube roles from a development machine to a
production machine by performing a file copy? If so, exactly what has to
copied. If not, what is the best way to do it if it is possible.
Thanks!Look at the Release Management section in the AS Operations Guide
http://www.microsoft.com/technet/pr...n/anservog.mspx
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:7204F1A0-8FA6-47BB-8023-FB18D845CAF0@.microsoft.com...
> Hi,
> I have an Analysis Services database that contains 5 cubes. Is it possible
> to migrate the database and cube roles from a development machine to a
> production machine by performing a file copy? If so, exactly what has to
> copied. If not, what is the best way to do it if it is possible.
> Thanks!

Migrating an SQL server to another machine

Hello,
I have to migrate SQL 200o from an NT4 to a 2003 machine. I would like to
keep al the settings/users/databases intact.
How could this be acomplished?
Kind regards,
Paul.
One or more of below should get you started:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <microsoftnews@.knipsel.nl> wrote in message news:%23CO5wfIoEHA.800@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have to migrate SQL 200o from an NT4 to a 2003 machine. I would like to
> keep al the settings/users/databases intact.
> How could this be acomplished?
> Kind regards,
>
> Paul.
>
>
|||Thanks Tibor!
This will cetainly help!
Regards,
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OXnhJvIoEHA.3324@.TK2MSFTNGP10.phx.gbl...
> One or more of below should get you started:
>
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <microsoftnews@.knipsel.nl> wrote in message
> news:%23CO5wfIoEHA.800@.TK2MSFTNGP14.phx.gbl...
>

Migrating an SQL server to another machine

Hello,
I have to migrate SQL 200o from an NT4 to a 2003 machine. I would like to
keep al the settings/users/databases intact.
How could this be acomplished?
Kind regards,
Paul.One or more of below should get you started:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <microsoftnews@.knipsel.nl> wrote in message news:%23CO5wfIoEHA.800@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have to migrate SQL 200o from an NT4 to a 2003 machine. I would like to
> keep al the settings/users/databases intact.
> How could this be acomplished?
> Kind regards,
>
> Paul.
>
>|||Thanks Tibor!
This will cetainly help!
Regards,
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OXnhJvIoEHA.3324@.TK2MSFTNGP10.phx.gbl...
> One or more of below should get you started:
>
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <microsoftnews@.knipsel.nl> wrote in message
> news:%23CO5wfIoEHA.800@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> I have to migrate SQL 200o from an NT4 to a 2003 machine. I would like to
>> keep al the settings/users/databases intact.
>> How could this be acomplished?
>> Kind regards,
>>
>> Paul.
>>
>>
>

Friday, March 23, 2012

Migrate SQL Server 2000

Hello. We have a new database server machine that will replace our old
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
Jhttp://www.microsoft.com/downloads/...&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/...&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and i
p
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to b
e
> retired machine to our new machine? Perhaps setting up an empty SQL 2000
on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
> http://www.microsoft.com/downloads/...&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/...&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>|||You could either:
-. Do a back up the database on your old database and restore of your
database on your new server.
-. Do an dettach and attach of your database data and log file.
Personally, I usually choose the backup and restore approach, just as you
said. Also you might need to restore the system databases: master, and msdb
(at the very least), if you want to bring over the login information, and
also the job information. If you want you can also backup and restore the
model database.
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Lucas
For info on backup and restore system database:
"J" wrote:

> Actually both old and new server will be SQL 2000. So I guess I can look
at
> my first task as a full restore based on all of the backups?
> I think we will eventually upgrade to SQL 2005 so I'll definitely keep you
r
> helpful info in mind. Thanks for the info Maninder. I totally appreciate
> it.
> J
> "Maninder" <msdhanjal@.gmail.com> wrote in message
> news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
>
>|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...[vbcol=seagreen]
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:
>

Migrate SQL Server 2000

Hello. We have a new database server machine that will replace our old
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
J
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and ip
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to be
> retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J
|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegro ups.com...
> http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>
|||You could either:
-. Do a back up the database on your old database and restore of your
database on your new server.
-. Do an dettach and attach of your database data and log file.
Personally, I usually choose the backup and restore approach, just as you
said. Also you might need to restore the system databases: master, and msdb
(at the very least), if you want to bring over the login information, and
also the job information. If you want you can also backup and restore the
model database.
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Lucas
For info on backup and restore system database:
"J" wrote:

> Actually both old and new server will be SQL 2000. So I guess I can look at
> my first task as a full restore based on all of the backups?
> I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
> helpful info in mind. Thanks for the info Maninder. I totally appreciate
> it.
> J
> "Maninder" <msdhanjal@.gmail.com> wrote in message
> news:1169493039.530765.30020@.s34g2000cwa.googlegro ups.com...
>
>
|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...[vbcol=seagreen]
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:

Migrate SQL Server 2000

Hello. We have a new database server machine that will replace our old
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
Jhttp://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and ip
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to be
> retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
> http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>> Hello. We have a new database server machine that will replace our old
>> database server machine running SQL 2000 (will take on the same name and
>> ip
>> address). I was wondering if anyone could recommend or point me to some
>> info on what is the best method in getting the data from our old soon to
>> be
>> retired machine to our new machine? Perhaps setting up an empty SQL 2000
>> on
>> the new machine and then taking the backup files from the old machine and
>> doing a restore on each database starting with the master db?...just
>> wondering if this will affect any of the system databases (master, temp,
>> msdb, model) like the sql/window logins/passwords and/or other backend
>> things that should or would need to remain in tact?
>> Thanks in advance.
>> J
>|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:
>> Actually both old and new server will be SQL 2000. So I guess I can look
>> at
>> my first task as a full restore based on all of the backups?
>> I think we will eventually upgrade to SQL 2005 so I'll definitely keep
>> your
>> helpful info in mind. Thanks for the info Maninder. I totally
>> appreciate
>> it.
>> J
>> "Maninder" <msdhanjal@.gmail.com> wrote in message
>> news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
>> > http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
>> > This is What you can Read or goto
>> > http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
>> >
>> > This is Helpful.
>> >
>> > Rest in Short: Just Detach and attach the Databases to the new SQL2K5
>> > Server and change the Compatability Level to 90
>> >
>> > As for the DTS PAckages you might have on SQL2000, Well its a whole new
>> > Story. DTS Packages without several connections are easy to migrate,
>> > but Some require to be recreated from Scratch, if you have on of those
>> > smart Developers with you, He will figure out a process in time.
>> >
>> > Maninder
>> > MCDBA
>> >
>> >
>> > On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>> >> Hello. We have a new database server machine that will replace our
>> >> old
>> >> database server machine running SQL 2000 (will take on the same name
>> >> and
>> >> ip
>> >> address). I was wondering if anyone could recommend or point me to
>> >> some
>> >> info on what is the best method in getting the data from our old soon
>> >> to
>> >> be
>> >> retired machine to our new machine? Perhaps setting up an empty SQL
>> >> 2000
>> >> on
>> >> the new machine and then taking the backup files from the old machine
>> >> and
>> >> doing a restore on each database starting with the master db?...just
>> >> wondering if this will affect any of the system databases (master,
>> >> temp,
>> >> msdb, model) like the sql/window logins/passwords and/or other backend
>> >> things that should or would need to remain in tact?
>> >>
>> >> Thanks in advance.
>> >>
>> >> J
>> >
>>

Wednesday, March 21, 2012

migrate server to another machine

Hi,
I will migrate the databases using the Microsoft SQL Database Migration
Wizard to another machine.
Can(how) I keep the same all username and password and data?
Usually hwo to do that? Thanks.http://databasejournal.com/features/mssql/article.php/2228611
That should get you what you need.
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"js@.someone.com" wrote:
> Hi,
> I will migrate the databases using the Microsoft SQL Database Migration
> Wizard to another machine.
> Can(how) I keep the same all username and password and data?
> Usually hwo to do that? Thanks.
>
>|||Thanks for the help.
Can Microsoft SQL Database Migration Wizard used for
migration?
"MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
news:52EBC31B-6988-48E7-AD51-8E072A9B061C@.microsoft.com...
> http://databasejournal.com/features/mssql/article.php/2228611
> That should get you what you need.
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "js@.someone.com" wrote:
>> Hi,
>> I will migrate the databases using the Microsoft SQL Database Migration
>> Wizard to another machine.
>> Can(how) I keep the same all username and password and data?
>> Usually hwo to do that? Thanks.
>>|||Hi,
The simplest way u can migrate ur database and logins are
restore the backup on the other server and trabfer all the logins from
dts package.
yes u can use microsoft migration wizard for migration.
from
Doller
js@.someone.com wrote:
> Thanks for the help.
> Can Microsoft SQL Database Migration Wizard used for
> migration?
>
> "MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
> news:52EBC31B-6988-48E7-AD51-8E072A9B061C@.microsoft.com...
> > http://databasejournal.com/features/mssql/article.php/2228611
> >
> > That should get you what you need.
> > --
> > MeanOldDBA
> > derrickleggett@.hotmail.com
> > http://weblogs.sqlteam.com/derrickl
> >
> > When life gives you a lemon, fire the DBA.
> >
> >
> > "js@.someone.com" wrote:
> >
> >> Hi,
> >> I will migrate the databases using the Microsoft SQL Database Migration
> >> Wizard to another machine.
> >> Can(how) I keep the same all username and password and data?
> >>
> >> Usually hwo to do that? Thanks.
> >>
> >>
> >>|||>> yes u can use microsoft migration wizard for migration.
Thanks, using migration wizard will auto keep the logins?
"doller" <sufianarif@.gmail.com> wrote in message
news:1128420204.497530.42080@.g14g2000cwa.googlegroups.com...
> Hi,
> The simplest way u can migrate ur database and logins are
> restore the backup on the other server and trabfer all the logins from
> dts package.
> yes u can use microsoft migration wizard for migration.
> from
> Doller
>
> js@.someone.com wrote:
>> Thanks for the help.
>> Can Microsoft SQL Database Migration Wizard used for
>> migration?
>>
>> "MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
>> news:52EBC31B-6988-48E7-AD51-8E072A9B061C@.microsoft.com...
>> > http://databasejournal.com/features/mssql/article.php/2228611
>> >
>> > That should get you what you need.
>> > --
>> > MeanOldDBA
>> > derrickleggett@.hotmail.com
>> > http://weblogs.sqlteam.com/derrickl
>> >
>> > When life gives you a lemon, fire the DBA.
>> >
>> >
>> > "js@.someone.com" wrote:
>> >
>> >> Hi,
>> >> I will migrate the databases using the Microsoft SQL Database
>> >> Migration
>> >> Wizard to another machine.
>> >> Can(how) I keep the same all username and password and data?
>> >>
>> >> Usually hwo to do that? Thanks.
>> >>
>> >>
>> >>
>|||Can you tell use exactly what "database migration wizard" is found. I don't have such tool on my SQL
Server 2000 installation. Do you refer to the "Copy Database Wizard"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"js" <js@.someone@.hotmail.com> wrote in message news:OIuy1XOyFHA.1132@.TK2MSFTNGP10.phx.gbl...
>> yes u can use microsoft migration wizard for migration.
> Thanks, using migration wizard will auto keep the logins?
>
> "doller" <sufianarif@.gmail.com> wrote in message
> news:1128420204.497530.42080@.g14g2000cwa.googlegroups.com...
>> Hi,
>> The simplest way u can migrate ur database and logins are
>> restore the backup on the other server and trabfer all the logins from
>> dts package.
>> yes u can use microsoft migration wizard for migration.
>> from
>> Doller
>>
>> js@.someone.com wrote:
>> Thanks for the help.
>> Can Microsoft SQL Database Migration Wizard used for
>> migration?
>>
>> "MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
>> news:52EBC31B-6988-48E7-AD51-8E072A9B061C@.microsoft.com...
>> > http://databasejournal.com/features/mssql/article.php/2228611
>> >
>> > That should get you what you need.
>> > --
>> > MeanOldDBA
>> > derrickleggett@.hotmail.com
>> > http://weblogs.sqlteam.com/derrickl
>> >
>> > When life gives you a lemon, fire the DBA.
>> >
>> >
>> > "js@.someone.com" wrote:
>> >
>> >> Hi,
>> >> I will migrate the databases using the Microsoft SQL Database Migration
>> >> Wizard to another machine.
>> >> Can(how) I keep the same all username and password and data?
>> >>
>> >> Usually hwo to do that? Thanks.
>> >>
>> >>
>> >>
>|||I don't know other tools, but found "Copy Database Wizard".
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23v2vNKQyFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Can you tell use exactly what "database migration wizard" is found. I
> don't have such tool on my SQL Server 2000 installation. Do you refer to
> the "Copy Database Wizard"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:OIuy1XOyFHA.1132@.TK2MSFTNGP10.phx.gbl...
>> yes u can use microsoft migration wizard for migration.
>> Thanks, using migration wizard will auto keep the logins?
>>
>> "doller" <sufianarif@.gmail.com> wrote in message
>> news:1128420204.497530.42080@.g14g2000cwa.googlegroups.com...
>> Hi,
>> The simplest way u can migrate ur database and logins are
>> restore the backup on the other server and trabfer all the logins from
>> dts package.
>> yes u can use microsoft migration wizard for migration.
>> from
>> Doller
>>
>> js@.someone.com wrote:
>> Thanks for the help.
>> Can Microsoft SQL Database Migration Wizard used for
>> migration?
>>
>> "MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
>> news:52EBC31B-6988-48E7-AD51-8E072A9B061C@.microsoft.com...
>> > http://databasejournal.com/features/mssql/article.php/2228611
>> >
>> > That should get you what you need.
>> > --
>> > MeanOldDBA
>> > derrickleggett@.hotmail.com
>> > http://weblogs.sqlteam.com/derrickl
>> >
>> > When life gives you a lemon, fire the DBA.
>> >
>> >
>> > "js@.someone.com" wrote:
>> >
>> >> Hi,
>> >> I will migrate the databases using the Microsoft SQL Database
>> >> Migration
>> >> Wizard to another machine.
>> >> Can(how) I keep the same all username and password and data?
>> >>
>> >> Usually hwo to do that? Thanks.
>> >>
>> >>
>> >>
>>
>|||I'm confused. You referred to a tool named "Data Migration Wizard". Are you now saying that the tool
you meant is actually the "Copy Database Wizard"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:OlKQjWRyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>I don't know other tools, but found "Copy Database Wizard".
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23v2vNKQyFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Can you tell use exactly what "database migration wizard" is found. I don't have such tool on my
>> SQL Server 2000 installation. Do you refer to the "Copy Database Wizard"?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "js" <js@.someone@.hotmail.com> wrote in message news:OIuy1XOyFHA.1132@.TK2MSFTNGP10.phx.gbl...
>> yes u can use microsoft migration wizard for migration.
>> Thanks, using migration wizard will auto keep the logins?
>>
>> "doller" <sufianarif@.gmail.com> wrote in message
>> news:1128420204.497530.42080@.g14g2000cwa.googlegroups.com...
>> Hi,
>> The simplest way u can migrate ur database and logins are
>> restore the backup on the other server and trabfer all the logins from
>> dts package.
>> yes u can use microsoft migration wizard for migration.
>> from
>> Doller
>>
>> js@.someone.com wrote:
>> Thanks for the help.
>> Can Microsoft SQL Database Migration Wizard used for
>> migration?
>>
>> "MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
>> news:52EBC31B-6988-48E7-AD51-8E072A9B061C@.microsoft.com...
>> > http://databasejournal.com/features/mssql/article.php/2228611
>> >
>> > That should get you what you need.
>> > --
>> > MeanOldDBA
>> > derrickleggett@.hotmail.com
>> > http://weblogs.sqlteam.com/derrickl
>> >
>> > When life gives you a lemon, fire the DBA.
>> >
>> >
>> > "js@.someone.com" wrote:
>> >
>> >> Hi,
>> >> I will migrate the databases using the Microsoft SQL Database Migration
>> >> Wizard to another machine.
>> >> Can(how) I keep the same all username and password and data?
>> >>
>> >> Usually hwo to do that? Thanks.
>> >>
>> >>
>> >>
>>
>>
>|||js wrote:
> I don't know other tools, but found "Copy Database Wizard".
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23v2vNKQyFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Can you tell use exactly what "database migration wizard" is found. I
>> don't have such tool on my SQL Server 2000 installation. Do you refer to
>> the "Copy Database Wizard"?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "js" <js@.someone@.hotmail.com> wrote in message
>> news:OIuy1XOyFHA.1132@.TK2MSFTNGP10.phx.gbl...
>> yes u can use microsoft migration wizard for migration.
>> Thanks, using migration wizard will auto keep the logins?
>>
>> "doller" <sufianarif@.gmail.com> wrote in message
>> news:1128420204.497530.42080@.g14g2000cwa.googlegroups.com...
>> Hi,
>> The simplest way u can migrate ur database and logins are
>> restore the backup on the other server and trabfer all the logins from
>> dts package.
>> yes u can use microsoft migration wizard for migration.
>> from
>> Doller
>>
>> js@.someone.com wrote:
>> Thanks for the help.
>> Can Microsoft SQL Database Migration Wizard used for
>> migration?
>>
>> "MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
>> news:52EBC31B-6988-48E7-AD51-8E072A9B061C@.microsoft.com...
>> http://databasejournal.com/features/mssql/article.php/2228611
>> That should get you what you need.
>> --
>> MeanOldDBA
>> derrickleggett@.hotmail.com
>> http://weblogs.sqlteam.com/derrickl
>> When life gives you a lemon, fire the DBA.
>>
>> "js@.someone.com" wrote:
>>> Hi,
>>> I will migrate the databases using the Microsoft SQL Database
>>> Migration
>>> Wizard to another machine.
>>> Can(how) I keep the same all username and password and data?
>>>
>>> Usually hwo to do that? Thanks.
>>>
>>>
>>>
>
You can look up the description of the "Copy Database Wizard" in Books
On Line. It says that in addition to copying the actual databases, it
can also copy metadata like logins etc.
Regards
Steen|||> You can look up the description of the "Copy Database Wizard" in Books On Line. It says that in
> addition to copying the actual databases, it can also copy metadata like logins etc.
The reason I want to be certain what tool is referred to is that Copy Database Wizard does not carry
SID for SQL Server logins. So instead of letting CDW carry over logins, you use sp_help_revlogins
which will keep both password as well as SID for SQL server logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%2333kVZYyFHA.596@.TK2MSFTNGP12.phx.gbl...
> js wrote:
>> I don't know other tools, but found "Copy Database Wizard".
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23v2vNKQyFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Can you tell use exactly what "database migration wizard" is found. I don't have such tool on my
>> SQL Server 2000 installation. Do you refer to the "Copy Database Wizard"?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "js" <js@.someone@.hotmail.com> wrote in message news:OIuy1XOyFHA.1132@.TK2MSFTNGP10.phx.gbl...
>> yes u can use microsoft migration wizard for migration.
>> Thanks, using migration wizard will auto keep the logins?
>>
>> "doller" <sufianarif@.gmail.com> wrote in message
>> news:1128420204.497530.42080@.g14g2000cwa.googlegroups.com...
>> Hi,
>> The simplest way u can migrate ur database and logins are
>> restore the backup on the other server and trabfer all the logins from
>> dts package.
>> yes u can use microsoft migration wizard for migration.
>> from
>> Doller
>>
>> js@.someone.com wrote:
>> Thanks for the help.
>> Can Microsoft SQL Database Migration Wizard used for
>> migration?
>>
>> "MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
>> news:52EBC31B-6988-48E7-AD51-8E072A9B061C@.microsoft.com...
>>> http://databasejournal.com/features/mssql/article.php/2228611
>>>
>>> That should get you what you need.
>>> --
>>> MeanOldDBA
>>> derrickleggett@.hotmail.com
>>> http://weblogs.sqlteam.com/derrickl
>>>
>>> When life gives you a lemon, fire the DBA.
>>>
>>>
>>> "js@.someone.com" wrote:
>>>
>>> Hi,
>>> I will migrate the databases using the Microsoft SQL Database Migration
>>> Wizard to another machine.
>>> Can(how) I keep the same all username and password and data?
>>>
>>> Usually hwo to do that? Thanks.
>>>
>>>
>>>
>>
> You can look up the description of the "Copy Database Wizard" in Books On Line. It says that in
> addition to copying the actual databases, it can also copy metadata like logins etc.
> Regards
> Steensql

migrate server to another machine

Friday, March 9, 2012

Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException

I have a SSIS project that has one parent package and three child packages. When I run the project on my development machine in debug mode it works fine. Also if i run the packages using dtexec on my development machine it still works fine. However the problem comes in when I try and run the project using dtexec on the staging server i get the following error:

Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.

does anyone have any idea how to fix this please?

thanks

Probably the data on staging server is different from the data on development machine?

You are trying to put data into buffer that is bigger than the buffer column size allocated for this data. E.g. script component builds some string, gets a string of length 60, and then tries to put it into STR column of length 50 - you'll get this exception as a result. You need to adjust the column size to match expected data, or cleanup the input data to avoid the overflow.

You probably don't see it on development machine, because your test data is different and this condition never occurs.

http://msdn2.microsoft.com/en-us/microsoft.sqlserver.dts.pipeline.doesnotfitbufferexception.aspx

|||thanks for the response but I am not sure that that is the problem since I am using the exact same data and exact same packages on both machines?|||

After some testing I have found that when I run the child packages in series as apposed to in parallel it all works fine. This should do for the mean time but if you have any idea why this is could u please let me know.

Thanks for the help.

Gary

|||

Hi again

I have a second package that has the same setup but manipulates a different data set and I am getting this same error but this time I can't find a way around it.

The data and the packages are exactly the same on both servers.On the development server the packages run with no problem but on the staging server I get buffer memory issues.

Anyone know why this is maybe?

It seems that the column width is too wide for the buffer so the buffer is overflowing into a second buffer which is being used by the other child package.

Any ideas?

thanks

|||

Pipeline Buffers are not shared between child and parent packages.

Could you describe what the packages are doing? What component reports this error? Do both dev and staging machine have the same architecture (32-bit, or 64-bit)?

|||

In the parent package data is extracted from multiple flat files and bulk inserted into multiple database table. Following this 4 child packages are initiated using the execute package task.

within each child package a data flow is started. In the data flow information is extracted from a table (populated in the parent package). The data is transformed using a script component. A row count is taken and then the data is inserted into a second database table.

After some investigation. I have discovered:

-on the development server the entire package works perfectly with no errors

-on the staging server each child package works when it is the only one executed from the parent package.

-on the staging server when I run two child packages it seems the first child package works fine but in the second package to be executed the script component does not get executed (I inserted a MessageBox and ComponentMetaData.FireInformation and there was no output).

This is the error message in the log when running all 4 child packages together:

OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:12:57 PM,2006/06/29 02:12:57 PM,0,0x,The value is too large to fit in the column data area of the buffer. Record ID - Merchant Number (ODS: QMAKBACCTNBR) : 00000000000000000002063 MerchantNumber (ODS: QMAKBACCTNBR)OnInformation,,,Transform Data Into MerchantStatementsMonthly,,,2006/06/29 02:15:07 PM,2006/06/29 02:15:07 PM,1074016264,0x,Post Execute phase is beginning.

OnInformation,,,ODS To DWH MerchantStatementsMonthly,,,2006/06/29 02:15:07 PM,2006/06/29 02:15:07 PM,1074016264,0x,Post Execute phase is beginning.

OnInformation,,,TrOnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:03 PM,2006/06/29 02:23:03 PM,-1073450910,0x,Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:03 PM,2006/06/29 02:23:03 PM,-1073450910,0x,Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:06 PM,2006/06/29 02:23:06 PM,-1073450974,0x,The ProcessInput method on component "Script Component" (10641) failed with error code 0x80131600. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:06 PM,2006/06/29 02:23:06 PM,-1073450974,0x,The ProcessInput method on component "Script Component" (10641) failed with error code 0x80131600. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1071636284,0x,The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1073450975,0x,Thread "WorkThread0" has exited with error code 0x80131600.

OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1071636284,0x,The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1073450975,0x,Thread "WorkThread0" has exited with error code 0x80131600.

OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:09 PM,2006/06/29 02:23:09 PM,-1073450952,0x,The PrimeOutput method on component "Q19400D" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:09 PM,2006/06/29 02:23:09 PM,-1073450952,0x,The PrimeOutput method on component "Q19400D" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

The development server is 32 bit and the staging server is 64bit but they are both runnning 32 bit operating systems.

Any help would be appreciated.

thanks

|||

We had somewhat the some problems as described above. We are implementing a datawarehouse-solution using sql2005 and an 64bit windows 2003 server. We use workflow packages that executes childpackages simultaniously. Errors concerning memory and buffers popped up at random in different childpackages.

We installed SP1 and the Cumulative hotfix package (build 2153). This helped a little but didn't solve all the problems. We are testing for two days now and it seems that changing the SSISpackage-property MaxConcurrentExecutables from -1 to 1 in the workflow package solves the remaining memory and buffer problems. Setting this property prohibits concurrent execution of childpackages.

It seems buffermanagement on 64bit servers is still a bit buggy.

Hope this helps in your case too.

Jan

|||

Thanks for the help. I narrowed the problem down to the script components in the child packages so I deleted them and recreated them and it seems to work fine.

No sure why this is. Any one know?

|||

Strange.

Did you create them in a beta version?

Do you have a copy of the old package? Maybe check the differences in the XML in the .dtsx file.

-Jamie

|||

hi,

I also have same problem and also recreate my Task....but not solve my problem

I'm using Script Component Task to do custom transformation.
I add 12 output columns in output0.
5 of these columns is DT_STR and their length is 4, 3 is DT_STR 2, 3 is
DT_STR 12 and 1 is DT_I4.

When executing the package ,I get an exception as below.

The value is too large to fit in the column data area of the buffer.
at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32
columnIndex, String value)
at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32
columnIndex, Object value)
at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32
ColumnIndex, Object value)
at
ScriptComponent_04dc3685b8d744898f09d88716141989.Output0Buffer.set_isjsst(String
Value)
at
ScriptComponent_04dc3685b8d744898f09d88716141989.ScriptMain.Input0_ProcessInputRow(Input0Buffer
Row)
at
ScriptComponent_04dc3685b8d744898f09d88716141989.UserComponent.Input0_ProcessInput(Input0Buffer
Buffer)
at
ScriptComponent_04dc3685b8d744898f09d88716141989.UserComponent.ProcessInput(Int32
InputID, PipelineBuffer Buffer)
at
Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32
inputID, PipelineBuffer buffer)

I debug and find the execption comes form output0Buffer set value.
I alse do some testing,
1.Create a simple package wiht same Script Component Task and find no
exception.
2.Remove some output0 columns and find no exception.
It's very confusing me...

Any help is very appreciated.

|||I had the same error and exception. I deleted and recreated the script component and the destination component that the output from the script component was going to. It works for me now.|||was getting the same error - found the resolution was to make sure the output column created in the design script was set to the same size as the source column .

In my case I was trying to set the desitination column to 35, since that is the size of the ultimate destination column in the destination database. Even though I was making sure the string inside the script was truncating to 35 characters, the design script component would still throw the error.

I instead create a derived column component immediately following the script component, and use that one to do the trucation to the destination column size...|||In my case it was Custom Component which did not update OutputBuffer field lengths after a change in a Source component

Thanks team for pointing the right direction!