Copying records from sql query to txt file

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 2000 Forums

SQL Server Administration (2000)

Copying records from sql query to txt file

Author

Topic

yogenderyadav150783
Starting Member

5 Posts

Posted - 2007-09-07 : 04:25:24

I am copying the result of a query in a text file through executing bcp command and by using extended stored procedure xp_cmdshell in master database.
I have also given execution rights to xp_cmdshell
but the following error msg appears in query analyser :

Error 997 from GetProxyAccount
When i use the same bcp command at command prompt then the following errors "Invalid Object name " and "[Microsoft] [ODBC SQL Server Driver] [SQL Server]Statements could not be prepared" are appearing.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-08 : 00:12:27

Can you post your code?

yogenderyadav150783
Starting Member

5 Posts

Posted - 2007-09-10 : 03:24:15

DECLARE @F_Name varchar(50),
@Cmd varchar(2000)

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

SET @Cmd = 'bcp "SELECT * FROM table1..DB1" queryout "'
SET @Cmd = @Cmd + @F_Name + '" -U Username -P Password -c'

EXEC master..xp_cmdshell @Cmd

Here table1 is the name of a table in database DB1.
I have also given rights to execute xp_cmdshell SP in master database.

The Error Message appeared is as :

1.SQLState = S0002, NativeError = 208
2.Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'USER_MGMT..chdo1'.
3.SQLState = 37000, NativeError = 8180
4.Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

kishore_pen
Starting Member

49 Posts

Posted - 2007-09-10 : 03:51:12

Hay, your code is working fine in my machine, see the below example:

DECLARE @F_Name varchar(50)
, @Cmd varchar(2000)
SET @F_Name = REPLACE('C:\Db1_' + CONVERT(char(8), GETDATE(), 1) + '.txt', '/', '-')
SET @Cmd = 'bcp "SELECT * FROM Northwind.dbo.Customers" queryout "'
SET @Cmd = @Cmd + @F_Name + '" -U username -P password -c'
EXEC master..xp_cmdshell @Cmd
go

in the above query, just change "username & password"
Note: I have executed this query in my Northwind database.

Sample Output:
--------------
NULL
Starting copy...
NULL
91 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 15 Average : (6066.67 rows per sec.)
NULL

Please check your database, owner, table names in query.

AnimalMagic
Starting Member

28 Posts

Posted - 2007-09-10 : 09:32:54

If your using a sql login you will need to execute the following

exec sp_xp_cmdshell_proxy_account 'domain\username','Password'

This will tell SQL what credentials to use in your command. Make sure you also have the grant exec on xp_cmdshell to <sql login name>.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources