Problem with using bcp

Hello all,
I try to execute the code below. Whatever I try I get the error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'dc_temp_text'.
This also happens when I put the owner and the database name in from of the
table name.
Can anybody tell me what it is that I'm missing?
TIA
Ronald
CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
begin
dbcc checkident (dc_temp_text, reseed, 0)
declare @.BcpCommand varchar(5000)
declare @.TableName varchar(100)
set @.Tablename = QuoteName('dc_temp_text')
/*
Schrijf het text bestand
*/
delete from dbo.dc_temp_text
insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
set @.BcpCommand = 'bcp "select * from '
set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
set @.bcpCommand = @.BcpCommand + '" queryout
c:\testuser1\text.txt -Uuser -Ppassword -c'
exec master..xp_cmdshell @.BcpCommand
endI assume that you have created the stored procedure in the same database
that you have the table dc_temp_text.
If you comment out the BCP portion I am guessing that the proc works
correctly.
I do not see where you specify the database or the server within the BCP
string.
Instead of
exec master..xp_cmdshell @.BcpCommand
Try
SELECT @.BcpCommand AS HereIsTheBCPstatement
If the command is correct you should be able to run it within DOS from your
PC. Use the output of the query to test your BCP statement.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>|||Keith,

> If you comment out the BCP portion I am guessing that the proc works
> correctly.
Yes, ther is no problem then. The table is filled and everything checks out.

> I do not see where you specify the database or the server within the BCP
> string.
I added that a few minutes later with no effect. I also tried to run the
statement in de dos prompt with the same result.
Any other ideas?
Greetings
Ronald
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>I assume that you have created the stored procedure in the same database
>that you have the table dc_temp_text.
> If you comment out the BCP portion I am guessing that the proc works
> correctly.
> I do not see where you specify the database or the server within the BCP
> string.
> Instead of
> exec master..xp_cmdshell @.BcpCommand
> Try
> SELECT @.BcpCommand AS HereIsTheBCPstatement
> If the command is correct you should be able to run it within DOS from
> your PC. Use the output of the query to test your BCP statement.
> --
> Keith Kratochvil
>
> "Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
> news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
>|||>I added that a few minutes later with no effect. I also tried to run the
>statement in de dos prompt with the same result.
Ok, now we have isolated the problem to the BCP statement.
Type BCP into Query Analyzer
Highlight the string BCP
Hit Shift_F1
Read up on BCP overview within Books Online. You will see that you are be
missing a few params.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:%23U3ffCEdGHA.380@.TK2MSFTNGP04.phx.gbl...
> Keith,
>
> Yes, ther is no problem then. The table is filled and everything checks
> out.
>
> I added that a few minutes later with no effect. I also tried to run the
> statement in de dos prompt with the same result.
> Any other ideas?
> Greetings
> Ronald
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
> news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>|||On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:

>Hello all,
>I try to execute the code below. Whatever I try I get the error:
>Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
>'dc_temp_text'.
>This also happens when I put the owner and the database name in from of the
>table name.
>Can anybody tell me what it is that I'm missing?
Hi Ronald,
Where do you put the owner and database name in front of the table name?
The execution of bcp will open a new connection to the server that will
connect to the user's default database. It is really important that you
always database-qualify the tablename on the bcp command.
Also, try what happens if yoou replace
exec master..xp_cmdshell @.BcpCommand
with
PRINT @.BcpCommand
Maybe this helps you see the problem in the generated bcp command. And
if not, you can post the output of the PRINT command here and see if
someone else sees a problem with it.
Hugo Kornelis, SQL Server MVP|||Ronald Hermans (rhermans@.datamedicare.nl) writes:
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
Is the server you are running on a default instance? Or is it a named
instance?
Since you do not specify -S the BCP command will connect to the default
instance. You get can the server name from @.@.servername.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Yes It is a Default instance, but just to make sure I entered the the
servername with the -S parameter. Unfortunatly it had no effect.
Ronald
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns97C06E3A3171Yazorman@.127.0.0.1...
> Ronald Hermans (rhermans@.datamedicare.nl) writes:
> Is the server you are running on a default instance? Or is it a named
> instance?
> Since you do not specify -S the BCP command will connect to the default
> instance. You get can the server name from @.@.servername.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo,
bcp "select * from dc_temp_text" queryout
c:\\text.txt -Sservername -Uuser -Ppassword -c
I tried to put the db name and the owner name in front of the table name
with no effect. I still get the same error.
Ronald
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> schreef in bericht
news:ulo462drdh8s5rv72pjm9bd4l9v12afjd8@.
4ax.com...
> On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:
>
> Hi Ronald,
> Where do you put the owner and database name in front of the table name?
> The execution of bcp will open a new connection to the server that will
> connect to the user's default database. It is really important that you
> always database-qualify the tablename on the bcp command.
> Also, try what happens if yoou replace
> exec master..xp_cmdshell @.BcpCommand
> with
> PRINT @.BcpCommand
> Maybe this helps you see the problem in the generated bcp command. And
> if not, you can post the output of the PRINT command here and see if
> someone else sees a problem with it.
> --
> Hugo Kornelis, SQL Server MVP|||bcp "select * from [your_db_name_goes_here].[dbo].dc_temp_text" queryout
"c:\text.txt" -Uuser -Ppassword -c
-oj
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:OaINpgMdGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hugo,
> bcp "select * from dc_temp_text" queryout
> c:\\text.txt -Sservername -Uuser -Ppassword -c
> I tried to put the db name and the owner name in front of the table name
> with no effect. I still get the same error.
> Ronald
>|||Hello all,
I think I may have found the problem (and if so, I'm going to cry). The
databasename starts with a 1 (one digit) and I think the bcp tool has a
problem with that.
Can anybody confirm this?
TIA
Ronald
"Ronald Hermans" <rhermans@.datamedicare.nl> schreef in bericht
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>