DTS ERROR SQL 2005 1

[QuestionAsker]

ALL,

SQL server2005 Enterprise
Windows 2003 Server

I'm trying to run an import on a weekly basis of an entire database, tried replication but it just wouldn't happen. The issue is most of the tables are around 200Mb, one is 10Gb as it contains blobs of XML(don't ask!). Most of the tables copy over no problem, but when it gets to the badboy, the process dies with the error message :

- Execute the transfer with the TransferProvider. (Error)
Messages
* ERROR : errorCode=-1073450990 description=A buffer failed while allocating 10485760 bytes.
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

For help, click:

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

There is 7.83Gb of RAM in the server, and the drives on the server are 200gb + with the paging setup to be dynamic.

the sys.sp_config shows that the server is setup with all the deafault settings i.e max server memory as 20Gb.

Any ideas on how to get this to work?

thanks in advance

 

[mrdenny]

Why not simply backup and restore the database. It will be much faster than using SSIS to copy the data across.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com