Export some text to .txt - SQL Server Forums

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

Transact-SQL (2005)

Export some text to .txt

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-30 : 03:54:41

Hi, i have created a sto-pro to identify which user backuping the database...however i require to create a 'updatable' text file to contain the result of sto-pro, any ideas how to do it?

thx alot ^^

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 04:02:05

OPENROWSET?

N 56°04'39.26"
E 12°55'05.63"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-30 : 04:04:10

i google it first...thx peso

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-30 : 04:33:40

Found my solution but need to enable it at sscm..non of us got the rights to enable...T.T
any other solution??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-30 : 04:42:50

BCP

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-30 : 05:02:21

[code]DECLARE @cmd sysname, @var sysname
SET @var = 'Meow'
SET @cmd = 'echo ' + @var + ' > C:\"documents and settings"\trainee1\desktop\var_out.txt'
EXEC master..xp_cmdshell @cmd[/code]
[code]output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Access is denied.
NULL

(2 row(s) affected)
[/code]
Amitofo+Amen+Ya Allah what is the error means?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-30 : 05:25:40

[code]DECLARE @sql VARCHAR(3000)
set @sql='bcp " select * from testing..TableA (NoLock)" queryout "C:\abc.txt" -c -T'
exec master..xp_cmdshell @sql[/code]
[code]output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL[/code]

AMITOFO+AMEN+YA ALLAH Hope the query works

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-30 : 05:29:57

quote:

Originally posted by waterduck

DECLARE @sql VARCHAR(3000)
set @sql='bcp " select * from testing..TableA (NoLock)" queryout "C:\abc.txt" -c -T'
exec master..xp_cmdshell @sql

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL

AMITOFO+AMEN+YA ALLAH Hope the query works

There is no drive named C in the server

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-30 : 06:10:30

Sorry if im stupid...but i using remote desktop into the server...there are C:...and lots of network drive like I:,R:

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-30 : 06:55:18

refer to http://msdn.microsoft.com/en-us/library/ms175046(SQL.90).aspx

quote:

xp_cmdshell Proxy Account

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password.

Permissions

Requires CONTROL SERVER permission.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-30 : 08:11:45

quote:
Originally posted by waterduck

Sorry if im stupid...but i using remote desktop into the server...there are C:...and lots of network drive like I:,R:

AMITOFO+AMEN+YA ALLAH Hope the query works

When you specify C:\ it means it refers to the Server directory

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources