Copying table data from SQL Server 2005 to SQL Server 2000 - Very Slow when using OLEDB Source a

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

What defect are you referring to in "Transfer Objects" task?|||

Check this out for the defect:

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=336f6832-f68c-4a7f-be74-3e62e1310609

MShah

|||This bug was specific to using "SQL Server Authentication" at the destination of the transfer. It has been fixed in SP1.

So, to copy data from one table to the other, you should be able to use Transfer task with "Windows Authentication" at the destionation if you are using SQL Server 2005 RTM. If you have SP1 installed, you should be able to use "SQL Server Authentication" or "Windows Authentication".|||

Thanks. I will upgrade to SP1. We only use SQL Server Authentication here, no windows / mixed mode.

sql

copying sql server logins and users

Hi All,
I have a user database with 20 users. I have to transfer this database to some other location (location B) with the user and login, so that others would be able to login (assuming i have provided them username and pwd for logging). I can take the backup of the database, which can be restored at location b, in which the users will also be available.
Now my question i
Is there a mechansim to copy sql server logins also, such that, when the database is restored at location b, the users will be able to simply login...
Thanks
GYKThis should help
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246133&
Ray Higdon MCSE, MCDBA, CCNA
--
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:40A125DF-6C22-48DA-9780-DA7A7ED52B5E@.microsoft.com...
> Hi All,
> I have a user database with 20 users. I have to transfer this database to
some other location (location B) with the user and login, so that others
would be able to login (assuming i have provided them username and pwd for
logging). I can take the backup of the database, which can be restored at
location b, in which the users will also be available.
> Now my question is
> Is there a mechansim to copy sql server logins also, such that, when the
database is restored at location b, the users will be able to simply
login...
> Thanks
> GYK

copying connection/registration info to other pc

hi folks
i need to transfer all my sql 2000 registerede databases from my desktop to
my laptop.
is there an easy way to do this? hope so ;)
thanks!This data is stored in the regsitry in the HKCU key. You could try copying
the regsitry from your desktop and importing it into your laptop's regsitry.
But make sure you backup the registry key on your laptop before you do. I
don't know if the user credentials are stored in the key or if they're
encrypted with a machine key, so you'll want to be careful so you can
restore everything in case this breaks Enterprise manager.
Mark
"moishie" <moishie@.discussions.microsoft.com> wrote in message
news:5B1F9178-D71E-42E8-B661-E64B6F2551CC@.microsoft.com...
> hi folks
> i need to transfer all my sql 2000 registerede databases from my desktop
> to
> my laptop.
> is there an easy way to do this? hope so ;)
> thanks!|||Thanks, Mark
i appreciate it
"Mark Miller" wrote:

> This data is stored in the regsitry in the HKCU key. You could try copying
> the regsitry from your desktop and importing it into your laptop's regsitr
y.
> But make sure you backup the registry key on your laptop before you do. I
> don't know if the user credentials are stored in the key or if they're
> encrypted with a machine key, so you'll want to be careful so you can
> restore everything in case this breaks Enterprise manager.
> Mark
>
> "moishie" <moishie@.discussions.microsoft.com> wrote in message
> news:5B1F9178-D71E-42E8-B661-E64B6F2551CC@.microsoft.com...
>
>

copying connection/registration info to other pc

hi folks
i need to transfer all my sql 2000 registerede databases from my desktop to
my laptop.
is there an easy way to do this? hope so ;)
thanks!This data is stored in the regsitry in the HKCU key. You could try copying
the regsitry from your desktop and importing it into your laptop's regsitry.
But make sure you backup the registry key on your laptop before you do. I
don't know if the user credentials are stored in the key or if they're
encrypted with a machine key, so you'll want to be careful so you can
restore everything in case this breaks Enterprise manager.
Mark
"moishie" <moishie@.discussions.microsoft.com> wrote in message
news:5B1F9178-D71E-42E8-B661-E64B6F2551CC@.microsoft.com...
> hi folks
> i need to transfer all my sql 2000 registerede databases from my desktop
> to
> my laptop.
> is there an easy way to do this? hope so ;)
> thanks!|||Thanks, Mark
i appreciate it
"Mark Miller" wrote:
> This data is stored in the regsitry in the HKCU key. You could try copying
> the regsitry from your desktop and importing it into your laptop's regsitry.
> But make sure you backup the registry key on your laptop before you do. I
> don't know if the user credentials are stored in the key or if they're
> encrypted with a machine key, so you'll want to be careful so you can
> restore everything in case this breaks Enterprise manager.
> Mark
>
> "moishie" <moishie@.discussions.microsoft.com> wrote in message
> news:5B1F9178-D71E-42E8-B661-E64B6F2551CC@.microsoft.com...
> > hi folks
> >
> > i need to transfer all my sql 2000 registerede databases from my desktop
> > to
> > my laptop.
> > is there an easy way to do this? hope so ;)
> >
> > thanks!
>
>

Copying all data from table in one DB to another db

I have to SQL Databases.
The first one is used with MS Access to transfer/convert data from clients
existing database (could be any type) to a database (SQL Database A) in SQL
server. During the process data is modified to meet my applications
requirements
SQL database A - Has no constraints etc just the data fields.
SQL database B - has all the necessary constraints etc.
I have many tables that need to be coppied in a certain order. Is there a
way without DTS to create a script to copy all rows from the tables in SQL
Database A to SQL Database B so i can process the import in table order I
require.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.525 / Virus Database: 322 - Release Date: 9/10/2003As long as the user has the correct permissions to the objects in both
databases, you could issue a series of INSERT INTO/SELECT statements to
move the data from the tables in Database A to Database B.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||... and those statements can be created with a cursor fairly easily. Look
at the sysobjects table with type U. Those objects are user tables.
hth
Quentin
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:ycqzEXFlDHA.576@.cpmsftngxa06.phx.gbl...
> As long as the user has the correct permissions to the objects in both
> databases, you could issue a series of INSERT INTO/SELECT statements to
> move the data from the tables in Database A to Database B.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Copy, Delete Files, and Transfer via DTS

Hi,
I need to transfer files from another server to my database server (SQL
Server 7).
The job details is:
1. copy the files to backup folder ( d:\ftp\backup_data\ ), the backup
filename added with currentdate and time. I use xp_cmdshell.
2. load the file content to database. I use DTS from text file.
3. delete the source file. (xp_cmdshell).
The problem is xp_cmdshell is denied for accessing a mapping path, so copy
and deletion process will not run. I've try both sql server administrator
level or NT 4 administrator level login, but it still did'nt work.
Is there's anyway to do this?
Thanks in advance
TeguhThe access rights you will need to move, copy are not
yours, instead its the service account of you sql server.
Give the directory that rather than your own user id, and
that should do it.
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
>--Original Message--
>Hi,
>I need to transfer files from another server to my
database server (SQL
>Server 7).
>The job details is:
>1. copy the files to backup folder (
d:\ftp\backup_data\ ), the backup
>filename added with currentdate and time. I use
xp_cmdshell.
>2. load the file content to database. I use DTS from text
file.
>3. delete the source file. (xp_cmdshell).
>The problem is xp_cmdshell is denied for accessing a
mapping path, so copy
>and deletion process will not run. I've try both sql
server administrator
>level or NT 4 administrator level login, but it still
did'nt work.
>Is there's anyway to do this?
>Thanks in advance
>Teguh
>
>.
>

Copy Sql Server Reports to Flash Drive

Hi- What do I have to copy to my flash drive to transfer reports from home to work? I am using Sql Server Express w/ advanced services.

Yes, I'm new to this, but learning. Thanks in advance!

JP

You'll need the RDL files and the data source file if using a shared data source.