prob in xp_cmdshell bcp queryout

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

prob in xp_cmdshell bcp queryout

Author

Topic

senthil4181
Starting Member

2 Posts

Posted - 2009-04-23 : 06:58:03

I got this following problem below and my database is existing in my server.
when i am trying to replace my select query to fetch data frominformation_schema.tables its working fine.

but when i am trying this coding

DECLARE @FileName varchar(100),@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp " SELECT ApplicationID, Portname, IPaddress, RootPath, BannerImagePath, PrizeImagePath, ImagePath, Description, CreatedDate FROM [eLOTTERY]..[TB_eL_ApplicationPath] " queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -c '

EXEC master..xp_cmdshell @bcpCommand

i got following problem

Password:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'eLOTTERY..TB_eL_ApplicationPath'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

advance thanks for ur help

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-23 : 07:51:10

Try adding the schema name. i.e.

[eLOTTERY]..[TB_eL_ApplicationPath]

should maybe become (if dbo is your schema:

[eLOTTERY].dbo.[TB_eL_ApplicationPath]

senthil4181
Starting Member

2 Posts

Posted - 2009-04-23 : 08:00:36

i tried with this one also.
[eLOTTERY].dbo.[TB_eL_ApplicationPath]
Same Error:
Password:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'eLOTTERY.dbo.TB_eL_ApplicationPath'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

And i am having dbo schema only

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-23 : 08:36:51

Can you post the result of @bcpCommand just before you execute it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-23 : 13:48:47

You are missing the -S switch in your bcp command. You'll also need -T or -U/-P.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources