Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

BCP Help Expand / Collapse
Author
Message
Posted Friday, August 09, 2013 4:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
Hi all I am tasked with importing large fixed width text file to SQL, previously I used a library called filehelpers to assist through my code. Now the files are growing I keep getting out of memory errors so decided to try BCP.


My command

bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmt

and the result

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: tt open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or specific error has occurred while establishing a connection to SQL Server is not found or not accessible. Check if instance name is correct and Server is configured to allow remote connections. For more information server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired



This is a localhost server with windows auth

Please help me learn why, I have tried with the -t switch and same result
Post #1482706
Posted Friday, August 09, 2013 4:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 12,208, Visits: 9,172
The localhost server, is it the default instance?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482714
Posted Friday, August 09, 2013 4:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
SSMS connects using localhost\DEV
Post #1482715
Posted Friday, August 09, 2013 4:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 12,208, Visits: 9,172
You need to specify the server as well in your bcp command line.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482717
Posted Friday, August 09, 2013 4:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:15 AM
Points: 318, Visits: 1,067
Try the following link

http://www.sqlteam.com/tools/bcphelper.zip

I use this when creating anything using BCP and saves me a load of time.
Post #1482718
Posted Friday, August 09, 2013 5:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
doh! ^^

bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmt -S localhost\DEV

works fine, thanks a lot.


My Next question is using that file is it possible to specify the format of a date time to "M/d/yyyyHH:mm:ss"


Post #1482719
Posted Friday, August 09, 2013 5:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 12,208, Visits: 9,172
Not an expert in bcp format files, but you can use a query in BCP, so I would do the formatting there.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482720
Posted Friday, August 09, 2013 4:13 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
jerome.morris (2013-08-09)
My Next question is using that file is it possible to specify the format of a date time to "M/d/yyyyHH:mm:ss"


You cannot specify any format for datetime values, but the format will always be YYYY-MM-DD hh:mm:ss.fff. Hm, you can use -R to get the regional settings (of the system locale, as IU recall.) BCP is primary intended to move data between servers. If you need formatting, you need to do that in the query.

But you are probably better off using SSIS. Or go back and fix your program. There is on reason why would get "out of memory" when you write a file. Unless you do something like reading all data in to a DataSet. Use ExecuteReader instead and you will be fine.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1482973
Posted Friday, August 09, 2013 8:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
Koen Verbeeck (8/9/2013)
Not an expert in bcp format files, but you can use a query in BCP, so I would do the formatting there.


You can only use a query to export from SQL Server to a file.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1482992
Posted Friday, August 09, 2013 8:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
jerome.morris (8/9/2013)
doh! ^^

bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmt -S localhost\DEV

works fine, thanks a lot.


My Next question is using that file is it possible to specify the format of a date time to "M/d/yyyyHH:mm:ss"


Kind of. Before I can answer that question, I have a couple of my own.

1. Will the "M" and "d" always be two digits?
2. Are you using a staging table as the BCP target (if not, I very strongly recommend that you do)?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1482994
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse