Failed BCP Process in SQL Server 2005

[Ken Sturgeon]

In a SQL 2000 database the following BCP command runs successfully. It essentially reads from the ELIGDATA1 table in a database named GW301 and puts all of the data from that table into a file named eligdata1.txt.

 

EXECUTE master..xp_cmdshell "BCP GW301..ELIGDATA1 OUT F:\Data\eligdata1.txt -T -c -t"

 

Notice the ".." between the database and table names. This is where the optional db owner could be supplied. When I execute the same statement in SQL 2005 it errors with the following message. SQL 2000 and 2005 are installed on the same physical Windows 2003 Server.

 

SQLState = 42S02, NativeError = 208

Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'GW301..ELIGDATA1'.

 

It's obviously unable to fine the ELIGDATA1 table, yet I can freely select from that table... I know that it exists and has data in it.

 

I've seen several articles that indicate that appropriate bcp permissions have to be set in SQL 2005 but I've not run across any articles that show how one goes about setting those permissions. I would assume that the NT AUTHORITY\LOCAL SERVICE account. I've added the local service account as a user for the database in SQL 2005 and added it to the db_owner schema and role.

 

I've also attempted to include the owner schema within in the bcp statement...

 

EXECUTE master..xp_cmdshell "BCP GW301.db_owner.ELIGDATA1 OUT F:\Data\eligdata1.txt -T -c -t"

 

I've also tried other variations of passing in an owner...

 

EXECUTE master..xp_cmdshell "BCP GW301.dbo.ELIGDATA1 OUT F:\Data\eligdata1.txt -T -c -t"
EXECUTE master..xp_cmdshell "BCP GW301.sa.ELIGDATA1 OUT F:\Data\eligdata1.txt -T -c -t"

 

I'm clueless at this point as to how I can resolve this issue and would appreciate any help.

 

Thanks in advance.

--
-- Ken Sturgeon

[Bob]

You're not using the -S server switch? Try that.

wBob

[Ken Sturgeon]

Well Bob, that certainly makes a difference although now I'm getting a
different error that implies that the server doesn't exist.

EXECUTE master..xp_cmdshell "BCP GW301..ELIGDATA1 OUT

F:\Dataload\GW301\eligdata1.txt -T -c -t -S=srvsqldev01\SQL2005"

resulted in...

SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
NULL

This certainly brings reason as to why I was getting the previous message;
the process was looking for the target table in our default instance which
is SQL 2000 and the table does not exist there. Now I need to find out why
it can't access SQL 2005. I have again tried passing in various owner values
of sa, dbo, db_owner as well.

Thanks for getting me a step closer.

-- Ken

"Bob" <B...@discussions.microsoft.com> wrote in message
news:8104FFD7-05D1-4A2F...@microsoft.com...

[Immy]

try this! you had an '=' sign rather than a hyphen.

EXECUTE master..xp_cmdshell "BCP GW301..ELIGDATA1 OUT

F:\Dataload\GW301\eligdata1.txt -T -c -t -S srvsqldev01\SQL2005

yes, the default instance would have caused you the problem you experienced.

"Ken Sturgeon" <akstu...@charter.net> wrote in message
news:%236oncN9...@TK2MSFTNGP03.phx.gbl...

[Bob]

Don't think you need the equals sign ie

-Ssrvsqldev01\SQL2005

[Ken Sturgeon]

Ok, it's official.... you and Bob are my new heroes.

Thanks VERY much for your help... both of you.

--
-- Ken Sturgeon

"Immy" <thereala...@hotmail.com> wrote in message
news:uOMjRQ9...@TK2MSFTNGP04.phx.gbl...

[Immy]

Haha - thanks! It always helps if people can post enough detail as you did
in your original post.
Happy BCP-ing!

"Ken Sturgeon" <akstu...@charter.net> wrote in message

news:emlZtT9l...@TK2MSFTNGP03.phx.gbl...

[Deepak]

Hi,

When I run the following sql as following the guidelines/

EXECUTE master..xp_cmdshell
"BCP LHAPP14.dbo.IMR_WARDS_BED_AVAILABILITY OUT G:\IMR\eligdata1.txt -

T -c -t -S srvsqldev01\SQL2005"

I am getting the error below.. Any solutions??

SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error
Locating Server/Instance Specified [xFFFFFFFF].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = -1
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 connecti
ons.
NULL

Regards,
Deepak George

On May 16, 5:18 pm, "Immy" <therealasianb...@hotmail.com> wrote:
> Haha - thanks! It always helps if people can post enough detail as you did
> in your original post.
> Happy BCP-ing!
>

> "Ken Sturgeon" <aksturg...@charter.net> wrote in message

>
> news:emlZtT9l...@TK2MSFTNGP03.phx.gbl...
>
>
>
> > Ok, it's official.... you and Bob are my new heroes.
>
> > Thanks VERY much for your help... both of you.
>
> > --
> > -- Ken Sturgeon
>

> > "Immy" <therealasianb...@hotmail.com> wrote in message

> >news:uOMjRQ9...@TK2MSFTNGP04.phx.gbl...
> >> try this! you had an '=' sign rather than a hyphen.
>
> >> EXECUTE master..xp_cmdshell "BCP GW301..ELIGDATA1 OUT
> >> F:\Dataload\GW301\eligdata1.txt -T -c -t -S srvsqldev01\SQL2005
>
> >> yes, the default instance would have caused you the problem you
> >> experienced.
>

> >> "Ken Sturgeon" <aksturg...@charter.net> wrote in message

> >>news:%236oncN9...@TK2MSFTNGP03.phx.gbl...
> >>> Well Bob, that certainly makes a difference although now I'm getting a
> >>> different error that implies that the server doesn't exist.
>
> >>> EXECUTE master..xp_cmdshell "BCP GW301..ELIGDATA1 OUT
> >>> F:\Dataload\GW301\eligdata1.txt -T -c -t -S=srvsqldev01\SQL2005"
>
> >>> resulted in...
>

> >>>SQLState= 08001,NativeError= 17

> >>> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> >>> exist or access denied.

> >>>SQLState= 01000,NativeError= 53

> >>>>> -- Ken Sturgeon- Hide quoted text -
>
> - Show quoted text -

[Erland Sommarskog]

Deepak (deepak...@gmail.com) writes:
> When I run the following sql as following the guidelines/
>
> EXECUTE master..xp_cmdshell
> "BCP LHAPP14.dbo.IMR_WARDS_BED_AVAILABILITY OUT G:\IMR\eligdata1.txt -
> T -c -t -S srvsqldev01\SQL2005"
>
> I am getting the error below.. Any solutions??
>
> SQLState = 08001, NativeError = -1
> Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error
> Locating Server/Instance Specified [xFFFFFFFF].

It appears that you post to a thread initiated by someone else, but
you are using the same server and instance name as that guy. Do you
by coincidence have the same server name, or did you copy more than
you should have?

A good idea is to build a string:

SELECT @bcpcmd = "BCP ... -S " + @@servername
EXEC master..xp_cmdshell @bcpcmd

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[error@discussions.microsoft.com sql error]

Ok, this is the error

Still not working…

SQLState = HY010, NativeError = 0

Error = [Microsoft][SQL Native Client]Function sequence error

/C bcp "SET FMTONLY OFF EXEC MTSHistory.dbo.prcMTSRptJBOPerformanceReport
'<ProdDate.yyyymmdd>', '-1'" queryout
\\rspen001\develop\tidal\STRDPerformance<ProdDate.yyyymmdd>.txt -T -S
Artemis\Web -c -t ,

[Erland Sommarskog]

sql error (sql er...@discussions.microsoft.com) writes:
> Ok, this is the error
>
> Still not working…
>
>
>
> SQLState = HY010, NativeError = 0
>
> Error = [Microsoft][SQL Native Client]Function sequence error
>
> /C bcp "SET FMTONLY OFF EXEC MTSHistory.dbo.prcMTSRptJBOPerformanceReport
> '<ProdDate.yyyymmdd>', '-1'" queryout
> \\rspen001\develop\tidal\STRDPerformance<ProdDate.yyyymmdd>.txt -T -S
> Artemis\Web -c -t ,

This is a completely different error from the error in the post you
quote. I can't find the other post on my newsserver, but my feed has
been a little flaky lately.

To take the old post first:

>> Notice the ".." between the database and table names. This is where the
>> optional db owner could be supplied. When I execute the same statement
>> in SQL 2005 it errors with the following message. SQL 2000 and 2005 are
>> installed on the same physical Windows 2003 Server.
>>
>> SQLState = 42S02, NativeError = 208
>> Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name
>> 'GW301..ELIGDATA1'.

This is because -S was not used to specify the server, so BCP was looking
in the wrong instance.

Now back to the Function Sequence Error. The trick of using SET FMTONLY
OFF to lure BCP may work, or may fail. I don't know why you need to
use SET FMTONLY OFF, but I guess that you have a temp table in the
strored procedure. If you use table variables instead, this should resolve
the issue.

If you give more about the context where you run this, we might be able
to suggest other ideas as well.

[sql error]

Hi Erland,

Thank you for your email.

That issue was resolved after installing SQL 2000 on one of the agent
servers, which was SQL 2005 previously...

Yes, you are correct.. there is a temp table in the stored procedure.... we
did try to change to global variables, but reverted to use SET FMTONLY ...

Have you had file size issues or have a sense of file size limits using BCP?

Nice to know you guys are out there!

[williams solomon]

sir,

this is following batch file failed when execute at command prompt.
kindly help in this case.

bcp sstvt.dbo.ct_year IN year.dat -T -c -t $
-S[csourcetech\SQLEXPRESS] >> errtest.txt

errtest.text contents is as follows

SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error
Locating Server/Instance Specified [xFFFFFFFF].

SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired

SQLState = 08001, NativeError = -1

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.

*** Sent via Developersdex http://www.developersdex.com ***

[Roy Harvey (SQL Server MVP)]

Try using "double quotes" rather than [square brackets] around the
server name.

Roy Harvey
Beacon Falls, CT