| Author |
Topic |
|
hummy
Starting Member
32 Posts |
 Posted - 2008-07-02 : 15:52:17
|
| Hi There. I have a problem which i'm hoping someone can help me on.I have extracted from another database the data from the table as a series of insert statements. All in all there are about 2 million records in this insert statement. I've tried various things, but always i seem to get MSCORLIB insufficient memory error. i've seperated the scripts into batches of 10, 25, and even 50.So after every 25 statements the GO statement is used.Now everytime i load the entire script into SQL Server query analyser and execute it, it gives the insufficient memory error. However if i select say 200 records and highlight them and then execute the highlighted statement then those records run in fine. Can anyone tell me what it is i need to change in my insert statements to get the complete thing run in without me having to manually select and execute the statements. THanks |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-02 : 16:37:50
|
| Try setting your database to simple recovery mode.Though you really should go back and use BCP to transfer the data. Much faster.e4 d5 xd5 Nf6 |
 |
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-07-02 : 17:49:03
|
| I'd love to use BCP but i'm under the impression that BCP is to transfer data SQL Server to SQL Server. I have a different database platforms which i'm extracting data from in the form of insert statements and wanting to run against SQL Server. My database is already in Simple recovery mode. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-02 : 18:55:27
|
| BCP files are merely text files. If you can generate them from another system, you can BCP them into SQL Server.You could also generate the data as CSV or tab-delimited and import it with DTS or SSIS.The method you are using is NOT recommended, as you are finding out.e4 d5 xd5 Nf6 |
|
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-07-03 : 06:36:03
|
| does any one know of a good resource that tells me exactly what format the data needs to be in that i extract from another system to allow me to use BCP for importing the data in. Thanks |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 07:51:16
|
| You don't need to set up a DTS job for 1 file if you are using management studio. If you have a csv file or similar delimited text file.Just go to the database you want the data in.Right click - go to tasks -> import data....Then choose flat file and just follow the wizard.-------------Charlie |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 07:52:45
|
| Of course, you'll need to extract your data from the host system as a flat file. If whatever you are using on the host system can't do that then I'll eat my virtual hat.-------------Charlie |
|
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-07-03 : 08:24:33
|
| it might just be me, but when i right click and go to tasks i have no Import Data option. All i have is backup, restore, generate script, shrink & detach. I am using SQL Server Express. Could this be the cause. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 08:39:48
|
| Could be, I'm not sure but it sounds likely.We use the enterprise edition so I can't confirm.Going back to your set of inserts...I do this quite a lot when I have to import data to a remote access server. I wrote a little java app that takes a csv file and turns it into a table definition and then a set of inserts.The first draft produced output likeCREATE TABLE #foo ([someColumn] VARCHAR(400))INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....GoINSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....INSERT INTO #foo .....GOAnd I found that to crawl and eventually give me the error you are getting. I changed it toCREATE TABLE #foo ([someColumn] VARCHAR(400))INSERT INTO #foo SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...GOINSERT INTO #foo SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...UNION SELECT ...GOetc.etc.I found that this worked best with batches of about 100 UNIONS per GO or so.If you are trying a million insert statements try changing to the INSERT SELECT UNION method and see if that helps.Other than that, I guess you could check up BCP or see if you have access to DTS.Check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=156956&SiteID=1 which seems to describe someone having the same problem as yourself.-------------Charlie |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 08:45:47
|
| One of the posts in that link I sent says."Hello,Try:C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exeWorked for me!"Worth looking to see if you have that wizard!-------------Charlie |
|
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-07-03 : 08:50:01
|
| no that wizard does not seem to exist on SQL Express |
|
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-07-03 : 09:02:33
|
| I'm just trying out the BCP tool and perhaps someone can help me and tell me where i am going wrong. I've exported the data from my system as a CSV file. Here is a sample fo the records:3AC5A113-3B78-4B9B-93D2-581E0F6597AC,Agent,28C6741D-6FAF-43E4-B871-87C46B2ECEED,Agent,B95BAFFF-975A-4B3B-A52D-67DA639E004A,Agent,I've created a format file to import the data into SQL Server. Here is the format file i have.9.021 SQLCHAR 2 36 "," 1 CONTACTID ""2 SQLCHAR 2 5 "," 2 Text ""The BCP command i am using is as follows:bcp agent..test in C:\1.csv -Sserver -Uuser -Ppassword -f bcp.fmtBut the BCP process fails. I get the following message:SQLState = 22001, Native Error =0Error = [Micorosft][SQL Native Client]String Data, Right TruncationCan anyone tell me where i've gone wrong?THanks |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 09:24:48
|
| Hi there.I think you wantbcp <dapabase>..bcpTest in simple_csv.csv -Sdevdb2 -Usa -P<password> -fbcp.fmtWith a bcp.fmt file like9.021 SQLCHAR 0 36 "," 1 field1 SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 5 "\r\n" 2 field2 SQL_Latin1_General_CP1_CI_ASAnd make your data file a simple csv one (comma separated columns with a newline ending a row)I've tested this with the data you provided as a simple csv.-------------Charlie |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 09:25:53
|
| Sorry -- obviously replace the field1 and field2 and table / database names with yours.My table was set up :CREATE TABLE bcpTest ( field1 CHAR(36) , field2 CHAR(5) )-------------Charlie |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 09:27:44
|
| And. If you run bcp without the config file it will ask you for defaults and will make one up for you!-------------Charlie |
|
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-07-03 : 09:57:30
|
| Thanks for your detailed explanation. So i've made the changes as you've specified and still not working with the FMT file. I'll try just importingt he data without the format file and see if i have any success. |
|
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-07-03 : 10:14:13
|
| ok, so even when i don't specify a format file the import fails. Can someone help me....I keep getting this String Truncation error message reported earlier. If someone is kind enough to want to have a look i can provide remote access to my system. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 10:55:11
|
| I'm sorry Hummy,I can't remote assist you. I'm at work and they don't mind me posting to this forum but they are not going to be happy with me connecting to you.However,The truncation error you are getting means that your data is overflowing your columns in your target table somehow. (one of your data items is too wide.What I suggest is this.1) Get the data into your database any way we can. We can then manipulate it.Try making a table...CREATE TABLE foo ( field1 VARCHAR(100) , field2 VARCHAR(20) )Then try doing a bcp to this table.use bcp without a config file and fallow the screen prompts.For your first field take the default for datatype. Then a prefix of 0 and default of 100 (basically follow the defaults) For the first delimiter - ','For the second follow the defaults and set the delimiter to '\n' (newline).Try this and then at least you have the data in the database. Moving it around is then just a sql matter.Charlie.-------------Charlie |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-03 : 12:11:43
|
| UPDATE -- Charliehummy's csv file was in UNICODE encoding. BCP doesn't understand this unless you specify a format file which seems to be rather complicated.However, getting the dataset in ANSI format (which is all that is required here) works.-------------Charlie |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-03 : 13:39:38
|
| Nicely handled, Charlie.e4 d5 xd5 Nf6 |
|
|
|
|
How to run 2 million insert statements