Sqlcmd: MS SQL Native Client: Communication Link Failure

Hi guys -

I have a big sql file (~6gig) that I'm trying to execute via sqlcmd. It's just a bunch of inserts that were generated using the 'generate scripts' sql tool.

So I connect just fine
> sqlcmd -S .\SQL2008 -d MyDatabase -i "C:\sqlscript.sql"

This happens regardless if i do the -e or not.

So it goes through stuff for a while and then in execution i get:
HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure
 error
Stuff does happen though since some records do get inserted.

Server timeout is set to 0. Named pipes and TCPIP are turned on

Any clue how to get the rest of the file to load in? I'm running SQL Server 2008 on Windows 7 64bit

Thanks!
LVL 7
rmm2001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rmm2001Author Commented:
Oops I forgot to put this...

Here's what the logs say:

A fatal error occurred while reading the input stream from the network. The maximum number of network packets in one request was exceeded. Try using bulk insert, increasing network packet size, or reducing the size of the request. The session will be terminated.

My packets are set at 10240
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
BULK INSERT should be better solution and it's easy to use. Or BCP it's the correspondent command line. Here you can find some help about the command: http://msdn.microsoft.com/en-us/library/ms162802.aspx

Good luck
0
rmm2001Author Commented:
So I tried the bcp. I made the file with -T -r -t -n. Then I load it in as -r -t -n and it gives me an EOF error. Is something just bad with my data or did a i miss a flag?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vitor MontalvãoMSSQL Senior EngineerCommented:
Seems to me it's data problem but can't tell more without see.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you share with us? May be useful for someone in future to resolve same issue.
Thank you.
0
rmm2001Author Commented:
I ended up using SSIS to do the load with max commit size being 50k - identity insert turned on. retain nulls turned on.
0
rmm2001Author Commented:
A different approach was used to do this same thing because nothing would work. Thanks for the info though!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.