Error with BCP

[Banana]

Running SQL Server 2005 Developer edition, I'm trying to do a backup of selected tables using Bulk Copy Program, but keep getting an error:

SQLState = 08001, NativeError = 67
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a 
connection to SQL Server [67].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 67
Error = [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.

I'm using trusted connection, and the SQL Server is local. The surface area configuration allows for both named pipes/TCP IP and local/remote connection.

This is the command I tried to pass:

bcp.exe "foo.dbo.bar" out foo.txt -S WIN-<serial number> -T

I am positively sure I'm just missing something blindingly obvious but what could it be?

 

[WayneRyan]

Banana,

I BCP a billion times, but initially I'd NEVER use it for Backup purposes.
You really need an EXACT copy of the source table's structure to even begin
to restore some data (not character mode).

Regardless, nothing in your command line should give you that error. Since
you're obviously on a net, are your SERVER PRIvileges set to who you really
think you are?

What if you go into Query Analyzer as "Trusted" and examine the System_User?

I think --> Select TOP 1 System_User From AnyTable

It might be System_User() ... but does that return something with what you expect
has the privileges to export from BCP?

Hope that gets you started.

Wayne

 

[Banana]

WayneRyan,

Thanks for that tidbit.

1) While this isn't an actual backup of live data (it's all development), I just need a quick'n'dirty of preserving data in case of a botched ALTER TABLE... If BCP is wrong solution, I'm all ears.

2) I may have not been clear, but this isn't on net. This is on my own computer, a local copy with the server right there. I'm not sure why it acts as if it's a remote connection nor do I see any options to force localhost connection, but...

3) Executing the query you suggested, I got what I expected to be the username...

 

[WayneRyan]

Banana,

This is just a ShellAndWait from a local server.

Doesn't look much different from yours.

strApp = "C:\Program Files\Microsoft SQL Server\80\tools\binn\bcp"

strCmd = Chr(34) & strApp & Chr(34) & _
         " " & Me.cboSource & ".dbo." & TableName & " format nul -f c:\" & TableName & ".fmt -SLocal -n -T" & Chr(34)
start.cb = Len(start)
ReturnValue = CreateProcessA(0&, strCmd, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
Do
   ReturnValue = WaitForSingleObject(proc.hProcess, 0)
   DoEvents
   Loop Until ReturnValue <> 258
ReturnValue = CloseHandle(proc.hProcess)
'
'
strCmd = Chr(34) & strApp & Chr(34) & _
         " " & Me.cboSource & ".dbo." & TableName & " out c:\" & TableName & ".txt -SLocal -n -T" & Chr(34)
start.cb = Len(start)
ReturnValue = CreateProcessA(0&, strCmd, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
Do
   ReturnValue = WaitForSingleObject(proc.hProcess, 0)
   DoEvents
   Loop Until ReturnValue <> 258
ReturnValue = CloseHandle(proc.hProcess)

btw,

I'd just do a Select * Into TempDB..SomeTable From SomeTable

Every so often, you can just make a .BAK of the TempDB. Sure wish that
SQL Server had selective restore.

Wayne

 

[WayneRyan]

Banana,

How do you know the Server name?

It's either SQL not having privs for YOUR username or

The -S argument --> what does Enterprise MGR say for your server name?

Do you run --> sqlmangr.exe (It'll tell you your server's name).

Do you link tables or run ADO connections to it?

I'm confused.

Wayne

 

[WayneRyan]

Banana,

One last thought, for Backup purposes you can easily Import the tables into
an Access DB.

Wayne

 

[Banana]

I got the name from SQL Server Management Studio. Access isn't even involved at all for this specific task; I'm just using the command line to execute the BCP. I suppose I could import into Access, but that may take ages and I'd be buried under a glacier before then (lot of datas. blech!)

Will try the tempdb idea.

 

[Banana]

Update- After getting an error trying to opening diagram in SSMS, it turns out that all the along I never was a member of administrator group. I had thought I was already a member, but may have had missed a step when I installed the server. Anyway everything, including BCP, seems to work now.

Goes to show that error messages you get may not be always spot-on (and I can understand why- it would be a security issue if we just broadcast the authorization failure)

Thank, WayneRyan.