SQL Server: Transfer data from one table to another [Memory Allocation Failure]

Question

So I have the following query that transfters the data from one table in DB1 to another table in DB2.

DB1 Table: tblSupportAttachments

DB2 Table: tblSupportAttachments

/* tblSupportAttachments */
SET @Sql = N'
INSERT INTO tblSupportAttachments
        ( fldName ,
          fldExtension ,
          fldFile ,
          fldCreatedBy ,
          fldCreatedDate ,
          fldSupportId
        )
SELECT fldName ,
          fldExtension ,
          fldFile ,
          fldCreatedBy ,
          fldCreatedDate ,
          fldSupportId 
FROM ' + @SourceDB + '.tblSupportAttachments';
EXECUTE sp_executesql @Sql;

fldFile: is of type varbinary(MAX)

This table basically holds data about a particular file, the file is directly uploaded to teh database i the fldFile field it could be a pdf, zip file or whatever

When I try to transfer this data to another table in another database I get the following error:

OLE DB provider "SQLNCLI11" for linked server "10.0.88.99" returned message "Memory allocation failure". Msg 7330, Level 16, State 2, Line 2 Cannot fetch a row from OLE DB provider "SQLNCLI11" for linked server "10.0.88.99".

Does anyone know why this happens?

When I use the Generate Script feature I get the following error:

Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> System.InvalidOperationException: Internal connection fatal error. at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.ProcessAttention(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ResetCancelAndProcessAttention() at System.Data.SqlClient.TdsParserStateObject.CloseSession() at System.Data.SqlClient.SqlDataReader.TryCloseInternal(Boolean closeReader) at System.Data.SqlClient.SqlDataReader.Close() at Microsoft.SqlServer.Management.Smo.DataEnumerator.CleanUp() at Microsoft.SqlServer.Management.Smo.DataEnumerator.Dispose() at Microsoft.SqlServer.Management.Smo.SingleFileWriter.ScriptData(IEnumerable1 dataScript, Urn table) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptDataToWriter(IEnumerable1 dataScripts, Urn urn) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateSpecialUrn(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List1 orderedUrns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List1 urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

score 0 · Answer 1 · answered Mar 06 '14 at 14:36

0

Can you please share the no . of rows you are trying to transfer and acess level of loging used to for linked server on source and target server.

Please check by giving sysadmin to user on target and source. You can also try OpenRowset or import exprt wizard for same.

answered Mar 06 '14 at 14:36

Saurabh Sinha

1,363
1
9
11

I'm trying to transfer 5450 rows from SourceDB (which is in a linked server) to the Destination Database which is where this insert query is being executed. The access level is sysadmin for both the target and source. – Eric Bergman Mar 06 '14 at 14:43

score 0 · Answer 2 · answered Mar 06 '14 at 14:50

0

Can you also try open rowset or export import wizard. and let us no the error.

Please also share sql server version , server memory and max server memory

answered Mar 06 '14 at 14:50

Saurabh Sinha

1,363
1
9
11

I'm using SQL Server 2012. The Server memory is 2GB, the max server memory is 1GB, Included the error that is generated when using Export Script method. – Eric Bergman Mar 06 '14 at 15:10

score 0 · Answer 3 · answered Mar 06 '14 at 15:57

Your error : " An error occurred while scripting the objects. ---> System.InvalidOperationException: Internal connection fatal error" AND "Memory allocation error"

from your error it seems its a clear memory issue , still i am not sure as this error is quite new.

For sql 2012 Memory recommendations are below and you are already @ minimum.

Minimum: Express Editions: 512 MB All other editions: 1 GB

Recommended: Express Editions: 1 GB All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance.

you can try one more test by using same query but reducing no. of rows transfered using where clause.

This test will confirm if its due to ammount of data memory cant process or linked server and memory.

Please let me know what you get

If I use the Export Data feature of the Sql Server Management Studio everything works perfectly, the problem happens when I use the INSERT statement I included before. — Eric Bergman, Mar 06 '14 at 17:36

SQL Server: Transfer data from one table to another [Memory Allocation Failure]

3 Answers3