Script with Data (200MB) Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Script with Data (200MB) Error

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-05-24 : 06:20:26

Dear All

My apploligies for this long post,but need to explain bit clear about the issue.

I have an SQl file of size 200 mb.This file creates a DB with all tables and the data.I try to ececute the scrip

I got this error :-

Cannot execute script
Aditional information
->Expection of type 'system.outofmemeory expection'was thrown.(mscorlib)'

I sort this issue by using sqlcmd (execute c:\>sqlcmd -s MYSQLSERVER\INSTANCENAME -i c:\myquery.sql)

Now i am not getting the above error but i am geeting

1.'Msg 8152,level 16,state 2, server xyz,line 452
string or binary data would be truncated
the statment has been terminated.'

2.cannot insert explict value for identity column in table 'XYZ'when identity is set to off

I try to fix the issue (string or binary data would be truncated) by increasing the column length, but i cant able to save the scrip.If i try to save the script
it throws 'the operation could not be completed not enough storage is available to process this command'

I want to execute the script without any error and need to restore the DB with data.Coluld any one please tell me how to sort all this issues.

Note:
Its not easy to check each line of the scipt to make ammendment as it contains around 90 000 insert() ... values() statements for each tables as well.

Any help would be highly appreciated.Thanks in advance.

Regards,
SG

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-24 : 06:55:41

Couple of suggestions based on your description:

1. The error message "cannot insert explict value for identity column" indicates that you are trying to insert a value into an identity column. You cannot do this, unless you set IDENTITY_INSERT to ON for that table. So your script needs to be modified.

2. If you need to insert 90,000 rows into a table, the efficient and recommended way is to use other methods such as bcp, bulk insert, SSIS or DTS packages etc. I would investigate if it would be possible to extract the data you want to insert from your script file into a delimited text file and then use one of these approaches.

3. Use a dedicated text editor such as TextPad to edit your script file. Such editors can handle large files without choking