SQL Server Management Studio: run

Showing posts with label run. Show all posts

Monday, March 26, 2012

Migrating an app from one cluster to another .. with the same name

Currently migrating a SQL application from an old Win2k cluster to some new
hardware.
The new hardware will run as a Win2K3SP1 cluster.
The App people would like to keep the names the same for all servers and
virtual services so that clients are not affected.
Downtime is not an option.
Whats the easiest way to do this?
Consider a DNS alias.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Rory Niland" <RoryNiland@.discussions.microsoft.com> wrote in message
news:DDCB983B-3F22-4360-8E07-91B02887ABB3@.microsoft.com...
Currently migrating a SQL application from an old Win2k cluster to some new
hardware.
The new hardware will run as a Win2K3SP1 cluster.
The App people would like to keep the names the same for all servers and
virtual services so that clients are not affected.
Downtime is not an option.
Whats the easiest way to do this?
|||Agreed, because you can't rename a SQL machine.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OvlMaCNcFHA.580@.TK2MSFTNGP15.phx.gbl...
> Consider a DNS alias.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Rory Niland" <RoryNiland@.discussions.microsoft.com> wrote in message
> news:DDCB983B-3F22-4360-8E07-91B02887ABB3@.microsoft.com...
> Currently migrating a SQL application from an old Win2k cluster to some
> new
> hardware.
> The new hardware will run as a Win2K3SP1 cluster.
> The App people would like to keep the names the same for all servers and
> virtual services so that clients are not affected.
> Downtime is not an option.
> Whats the easiest way to do this?
>

Migrating Access to SQL Server

We use MS Access 2000 as our database, but run into lots of proplems. So we decided to research the migration to SQL Server.

I used Access Upsizing utitlity to migrate tables and their data to SQL server very easily, and all the relationships, indecies and other information are converted correctly.

Next is to migrate the queries. I found that to be a pain. Is there any tool out there that can do it for me? Any help is greatly appreciated.

Once that's done, then it's the application itself, but I am not worrying that for now.

Thanks, guysI don't have a tool to recommend.

Have you checked out the part of Microsoft's SQL Server 2000 Resource Kit that deal with migration?Chapter 5 - Migrating Access 2000 Databases to SQL Server 2000. There is a section called "Migrating Access Queries" you might find helpful.

Terri

Wednesday, March 21, 2012

Migrate jobs from production to backup

The backup server is using the agent to run log shipping. If I were to
migrate jobs prior to a disaster, the agent would run them. Assuming I can
turn them off so that having them run is not an issue, migrating them one at
a time is tedious.
Is there a way to mass migrate the jobs from one server to the other?
(packages are already migrated)
If such a thing as a mass import of the jobs is available, will the job
migration automatically schedule the packages or will it be necessary to
reschedule them (assuming a disaster event occurs and log-shipping is the
backup solution).
Regards,
Jamie
Hi
"thejamie" wrote:

> The backup server is using the agent to run log shipping. If I were to
> migrate jobs prior to a disaster, the agent would run them. Assuming I can
> turn them off so that having them run is not an issue, migrating them one at
> a time is tedious.
> Is there a way to mass migrate the jobs from one server to the other?
> (packages are already migrated)
>
As described in http://support.microsoft.com/kb/314546 you can script all
jobs.

> If such a thing as a mass import of the jobs is available, will the job
> migration automatically schedule the packages or will it be necessary to
> reschedule them (assuming a disaster event occurs and log-shipping is the
> backup solution).
>
Scripting will re-create the same schedules. The jobs should be enabled when
you are scripting them if you don't want to change them on the destination
server.

> --
> Regards,
> Jamie
John
|||I hadn't noticed before, but there appears to be a wizard in SQL 2005 for
this. We have our production running on 2000 and with disk space at a
premium and backup time stretched to the limit, we needed to start
compressing backups and decided to buy a package. It works great. I am
impressed to see that the 2005 version already does this. Elated actually.
Regards,
Jamie
"John Bell" wrote:

> Hi
> "thejamie" wrote:
> As described in http://support.microsoft.com/kb/314546 you can script all
> jobs.
>
> Scripting will re-create the same schedules. The jobs should be enabled when
> you are scripting them if you don't want to change them on the destination
> server.
>
> John

Migrate jobs from production to backup

The backup server is using the agent to run log shipping. If I were to
migrate jobs prior to a disaster, the agent would run them. Assuming I can
turn them off so that having them run is not an issue, migrating them one at
a time is tedious.
Is there a way to mass migrate the jobs from one server to the other?
(packages are already migrated)
If such a thing as a mass import of the jobs is available, will the job
migration automatically schedule the packages or will it be necessary to
reschedule them (assuming a disaster event occurs and log-shipping is the
backup solution).
Regards,
JamieHi
"thejamie" wrote:

> The backup server is using the agent to run log shipping. If I were to
> migrate jobs prior to a disaster, the agent would run them. Assuming I c
an
> turn them off so that having them run is not an issue, migrating them one
at
> a time is tedious.
> Is there a way to mass migrate the jobs from one server to the other?
> (packages are already migrated)
>
As described in http://support.microsoft.com/kb/314546 you can script all
jobs.

> If such a thing as a mass import of the jobs is available, will the job
> migration automatically schedule the packages or will it be necessary to
> reschedule them (assuming a disaster event occurs and log-shipping is the
> backup solution).
>
Scripting will re-create the same schedules. The jobs should be enabled when
you are scripting them if you don't want to change them on the destination
server.

> --
> Regards,
> Jamie
John|||I hadn't noticed before, but there appears to be a wizard in SQL 2005 for
this. We have our production running on 2000 and with disk space at a
premium and backup time stretched to the limit, we needed to start
compressing backups and decided to buy a package. It works great. I am
impressed to see that the 2005 version already does this. Elated actually.
--
Regards,
Jamie
"John Bell" wrote:

> Hi
> "thejamie" wrote:
>
> As described in http://support.microsoft.com/kb/314546 you can script all
> jobs.
>
> Scripting will re-create the same schedules. The jobs should be enabled wh
en
> you are scripting them if you don't want to change them on the destination
> server.
>
> John

Monday, March 19, 2012

Migrate Database with change SQL-Authentifiction to Active Directo

i have a lot of Databaseserver and databases that run with integrated sql
authentification. now i must change the permissions from sql to active
directory interagted authentification. must i create all user and groups new
or can change the type of connect between sql account and active directory
user?You have to create them new, there is no automatic migration path for
that. For this purpose this is a good time to rethink your security
model and how the nt groups can match to the SQL Groups and the
permissions ont he tables.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||i cannot re-design the database structur. i must join the standa-a-lone sql
2000 server based on windows 2000 in a new active directory domain. i have
100 databases. now i locking for a way to change all permission that based on
sql-authentification to active directory objects (that i have created in AD).
can I carry out changing the rights by means of "T SQL"?
"Jens" wrote:
> You have to create them new, there is no automatic migration path for
> that. For this purpose this is a good time to rethink your security
> model and how the nt groups can match to the SQL Groups and the
> permissions ont he tables.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||"cannot re-design the database structur."
--Nobody wanted to make you do that. i just mentioned that you could
rethink your security design for that.
"all permission that based on
sql-authentification to active directory objects (that i have created
in AD). "
--Thats a very complicated work which can=B4t be described in some
sentences for your special situation. If the names are the same you
could do that with T-SQL, but that vey specific.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--

Migrate Database with change SQL-Authentifiction to Active Directo

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!