xp_cmdshell

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » xp_cmdshell

xp_cmdshell

Rate Topic

Display Mode

Topic Options

Author

Message

senthil_becse

Posted Thursday, April 23, 2009 5:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 02, 2009 6:35 AM
Points: 3, Visits: 24

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

thanks in advance

Post #703052

Kruti Kansara

Posted Thursday, April 23, 2009 6:16 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 6:52 AM
Points: 468, Visits: 392

You might not have select permission on [eLOTTERY]..[TB_eL_ApplicationPath] !!

Please check.

"Don't limit your challenges, challenge your limits"

Post #703072

Florian Reischl

Posted Thursday, April 23, 2009 6:31 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, March 18, 2010 2:19 AM
Points: 2,022, Visits: 3,594

You don't specify any authentication information in your command line string.

Greets
Flo

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help "Numbers" or "Tally" Table
How to Post Performance Problems

Post #703085

senthil_becse

Posted Thursday, April 23, 2009 6:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 02, 2009 6:35 AM
Points: 3, Visits: 24

I tried without giving login information also.

if i copy the select statement and run it. its seems to be working fine.

Post #703106

Florian Reischl

Posted Thursday, April 23, 2009 7:22 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, March 18, 2010 2:19 AM
Points: 2,022, Visits: 3,594

Hi

I just tried your script. Since I don't specify either "-T" or "-U user -P pwd" I also get an error. Try this to connect to a server by windows authentication:

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

SET @FileName = 'D:\Temp\Test\test.csv'

SET @bcpCommand = 'bcp " SELECT TOP(10) N FROM Sandbox..Tally " queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -c -S MyServer -T'

EXEC master..xp_cmdshell @bcpCommand

Greets
Flo

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help "Numbers" or "Tally" Table
How to Post Performance Problems

Post #703143

senthil_becse

Posted Thursday, April 23, 2009 7:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 02, 2009 6:35 AM
Points: 3, Visits: 24

i tried login as windows authetication but its failed for me.
it tried with code
but it shows error

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

any other solution

Post #703185

Florian Reischl

Posted Thursday, April 23, 2009 7:56 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, March 18, 2010 2:19 AM
Points: 2,022, Visits: 3,594

I'm still not sure if your BCP call is correct.

If you want to connect to your server with windows authentication use "-T" (no user name and password):
bcp "SELECT your query" queryout "D:\Anywhere\file.txt" -c -S YourServer -T

If you want to connect to your server with sql server authentication use "-U usr -P pwd":
bcp "SELECT your query" queryout "D:\Anywhere\file.txt" -c -S YourServer -U usr -P pwd

You should also always use "-S" to specify the server.

Could you please post your current statement? Sure, with pseudo user information, if included.

Greets
Flo

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help "Numbers" or "Tally" Table
How to Post Performance Problems

Post #703204

« Prev Topic | Next Topic »

Permissions