| Author |
Topic |
|
josh2009
Yak Posting Veteran
81 Posts |
 Posted - 2009-11-13 : 10:20:44
|
| I've been using the bcp utility to transfer tables in and out of out SQL database without any problems. I have been using the parameters -C -T succesfully when I use bcp. Now I need to be able to extract specific value from a column in table customers. However, when I use the queryout in bcp, I get an error. Here is my BCP statement -bcp "select site from dbname.dbo.customers where ID = 1000" queryout c:\bcp\site.dat -C -THere is the error I get -SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.SQLState = S1T00, NativeError = 0Error = [Microsoft][SQL Native Client]Login timeout expiredAny help will be greatly appreciated. Thanks in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2009-11-13 : 11:08:58
|
| That worked for me just fine. Thanks. The only thing is that when I tried outputting the value into a .txt file, the file contained random characters and not the site name that I was expecting. Is there a specific output file extension that I should be using? Thanks again. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2009-11-13 : 12:11:10
|
| .csv extension worked fine for me. I was hoping that it would be a txt file because I will be using the site value in my bcp command to output it to a folder named after the mobile site. The reason being right now, we have 10 different laptops which are used by mobile users. Typically, one mobile user goes to one site for a day. At the end of the day, all these mobile users would be merging their data using my merge solution. So my job is to merge the data between the different laptops and the production PC which I am successfully able to do with my sql stored procs. But to keep things straight, I need to be able to identify which records came from which site so that if I need to troubleshoot, I wont have to go through each and every text file output from my bcp command to get to the record I'm looking for. |
|
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2009-11-13 : 12:15:25
|
| To add another layer of difficulty, we use the same virtual image on all the laptops so I cant use the computer name of the laptop for identification purposes. Not unless there's a another way for me to identify one laptop from a different one, I'd be more than happy to try that alternative. Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-13 : 15:22:00
|
| let me get this straight1. You are bcping data out of mobile laptops to a central file location2. You are then bcping that data into a central sql server database3. Then you combine all the data into 1 tableHow does the file get to a central locationIs it on a network?If it is, why can't you just use T-SQl and put the data into the central database to begin with?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2009-11-13 : 16:54:11
|
Because our mobile users at times leave the laptop in a secured warehouse that's why our solution was to provide USB drives for them to store the data from the laptop and then stick it in the production machine for merging. And I am bcpping multiple tables from one site into a stick and merging them along with other sticks from different sites into a central database. It would be a lot easier for me to trouble shoot if I can identify records from specific sites thus my question about the queryout parameter of bcp. quote:
Originally posted by X002548
let me get this straight1. You are bcping data out of mobile laptops to a central file location2. You are then bcping that data into a central sql server database3. Then you combine all the data into 1 tableHow does the file get to a central locationIs it on a network?If it is, why can't you just use T-SQl and put the data into the central database to begin with?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
|
|
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2009-11-13 : 16:58:32
|
Thanks Tara. I see. I think I may have found the solution. I am using the command - 'wmic bios get serial number' to get the serial number from the laptop. Each laptop has a designated site assigned so I can identify it that way. I then assign the serial number to a variable in my script and am able to create the folders I need to identify one laptop from another. I am also using the date stamp to further subdivide the records for easier identification. Thanks again for all your help. Very much appreciated.quote:
Originally posted by tkizer
Csv files are text files. The csv extension may default to Excel as the software program if you've got Office installed. It can still be opened in notepad or similar programs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
BCP queryout