November 21, 2007 at 10:44 pm
Want to save the result of a query if executed at sql server express to be saved ina text file by firing a trigger on the table insertion.
This can be done by xp_cmdshell(which is a stored procedure in sql server express) .but if i tried to execute it iam getting an error as u have to configure xp_configure.but iam not getting any hint how to configure that.
Can any one help me regarding this .
Thanks & Regards
Anujahnavi S
November 24, 2007 at 3:58 pm
you have to enable ability to execute xp_cmdshell, which by default is disabled in SQL 2k5
there's an example in BOL, how to set this value.
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
...and your only reply is slàinte mhath
November 24, 2007 at 8:44 pm
hi,
Or just use Surface Area Configration to enable xp_cmdshell
Regards,
Ahmed
November 24, 2007 at 10:17 pm
hi Ahmed,
Thanks For ur Reply.
It helped me alot.
I want more details if u can , please guide me.
I have activated xp_cmdshell,but my problem is
actually i want to store a result of a query to a text file on desktop or any other drive.but how can i give the path of the file in xp_cmdshell. Please check this and if possible help me
here is my trigger in which i created what i want
--------------------------------
Alter trigger TestTrigger on
tablefortrigger
for insert
as
Declare @Msg varchar(1000)
Declare @CmdString varchar (2000)
set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' — '
+(select convert(varchar(5), track)
+ ',' + lastname + ', ' + firstname
from inserted)
raiserror( 50005, 10, 1, @Msg)
set @CmdString = 'echo ' + @Msg + 'C:\Documents and Settings\admin\Desktop\save.txt'
print 'Trigger Fired Anu....'
exec master.dbo.xp_cmdshell @CmdString
---------------------------------
in this i have a table and i created this trigger for that .if any record is inserted i want this trigger to be fired and want the result to be placed/Saved in the "save.txt" file.
If possible please help me
Thanks in Advance
Anujahnavi S.
November 24, 2007 at 10:19 pm
hi Piotr,
Thanks For ur Reply.
It helped me alot.
I want more details if u can , please guide me.
I have activated xp_cmdshell,but my problem is
actually i want to store a result of a query to a text file on desktop or any other drive.but how can i give the path of the file in xp_cmdshell. Please check this and if possible help me
here is my trigger in which i created what i want
--------------------------------
Alter trigger TestTrigger on
tablefortrigger
for insert
as
Declare @Msg varchar(1000)
Declare @CmdString varchar (2000)
set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' — '
+(select convert(varchar(5), track)
+ ',' + lastname + ', ' + firstname
from inserted)
raiserror( 50005, 10, 1, @Msg)
set @CmdString = 'echo ' + @Msg + 'C:\Documents and Settings\admin\Desktop\save.txt'
print 'Trigger Fired Anu....'
exec master.dbo.xp_cmdshell @CmdString
---------------------------------
in this i have a table and i created this trigger for that .if any record is inserted i want this trigger to be fired and want the result to be placed/Saved in the "save.txt" file.
If possible please help me
Thanks in Advance
Anujahnavi S.
November 25, 2007 at 7:08 am
Hi Anujahnavi,
For the raiserror, you have to create the message 50005 before (outside the trigger)
EXEC sp_addmessage
@msgnum = 50005,
@severity = 10,
@msgtext = N'%s',
@lang = 'us_english'
GO
To raise the error msg
RAISERROR(50005,10,1,@msg)
For the the output file
set @CmdString = 'echo ' + @Msg + ' > C:\Documents and Settings\admin\Desktop\save.txt'
EXEC master..xp_cmdshell @cmd
Regards,
Ahmed
November 25, 2007 at 8:11 pm
Hi Ahmed,
Thanks for that .
Sorry for disturbing u . Iam trying for this since 13 days so iam asking u like this .if u don't mind plzz give me the query of the trigger to save my output to a txt file .
What i created i have already sent u but iam getting this error while inserting the record into my table.
1 'tablefortrigger' is not recognized as an internal or external command,
2 operable program or batch file.
3 NULL
I have even tried what u have given but i din't get.
Can u please give me the entire query if possible.
Thanks alot iin advance for helping me .
Thanks & Regards
Anujahnavi S
November 26, 2007 at 3:29 am
Hi Snujahnavi,
For the raiserror, you have to create the message 50005 before (outside the trigger)
EXEC sp_addmessage
@msgnum = 50005,
@severity = 10,
@msgtext = N'%s',
@lang = 'us_english'
GO
alter Trigger TestTrigger
On dbo.tablefortrigger
For Insert
As
Begin
Set nocount on
Declare @Msg nvarchar(255)/*Check sp_addmessage */
Declare @CmdString nvarchar (2000)
set @Msg = 'Inserted \ tablefortrigger \' + convert(varchar(20), getdate()) + ' — '
+(select convert(varchar(5), track)
+ ',' + lastname + ', ' + firstname
from inserted)
RAISERROR(50005,10,1,@msg)
Set @CmdString = 'echo ' + @msg + ' > C:\Documents and Settings\admin\Desktop\save.txt'
EXEC master..xp_cmdshell @CmdString
Set nocount off
End
Go
I made tests the result is not good when you use '|', so use another char '\'. (I think | is reserved char forwindows command shell)
Regards,
Ahmed
November 26, 2007 at 4:03 am
Hi Ahmed ,
Though tu will not return back .
Thanks and will be waiting for ur reply more.
U said that to use "/" rather than "|" but it's just to dissplay some text.it may be any symbol right? that no matters i suppose.
Iwant the result to be saved in the Save.txt file.
I thought xp_cmdshall will store the data in the specified file but how iam not having any hint.
Anujahnavi S.
November 26, 2007 at 4:44 am
hi,
I tested the script I already posted and it works fine, the file save.txt is created with the approriate message.
November 26, 2007 at 10:01 pm
hi Ahmed,
I did the same thing but y iam not getting the result in the text file?
I will tellu how i did.........
1. Activated xp_cmdshell
2. Created a Table which has 3 columns.
------------------------
create table tablefortrigger
(
track int identity(1,1) primary key,
Lastname varchar(25),
Firstname varchar(25)
)
------------------------
3. Created a trigger on this table.
------------------------
Create Trigger TestTrigger1
On dbo.tablefortrigger
For Insert
As
Begin
Set nocount on
Declare @msg nvarchar(255) /*Check sp_addmessage */
Declare @CmdString nvarchar (2000)
set @msg = 'Inserted \ tablefortrigger \' + convert(varchar(20), getdate()) + ' — '
+(select convert(varchar(5), track)
+ ',' + lastname + ', ' + firstname
from inserted)
RAISERROR(50006,10,1,@msg)
Set @CmdString = 'echo ' + @msg + ' > C:\Documents and Settings\admin\Desktop\save.txt'
EXEC master..xp_cmdshell @CmdString
Set nocount off
End
Go
------------------------
4.Inserted a row in to the table.
------------------------
Insert into tablefortrigger values('Anu','Jahnavi');
------------------------
This is what i have done.
But iam getting an error as.........
---> Access is Denied.
This is in result tab and
in message tab iam getting
---->Inserted \ tablefortrigger \Nov 27 2007 10:23AM — 1,Anu, Jahnavi
(1 row(s) affected)
Record is getting inserted in to the table but iam not getting it in the save.txt file(A Notepad which i have place earlier on my desktop).
Is there any thing wrong i went with plzz not me know..........please......
Actually i have joined recently in this office as a fresher.
But i got this task Which is like a .........for me.Iam not getting .
It has taken 6 days to know that this can be done by xp_cmdshell.
Previously i even din't heard about this.
In training period i have to impress them or else they will kick me out of the office.So Iam strugling for my job.
So as iam a fresher not getting . Please consider me and clarify my doubt.Sorry for troubling u Ahmed.
U r only the hope for me.
Will be waiting for ur reply.Even u can scold i will bare.But plzz guide me.
Thanks & Regards
Anujahnavi S.
November 26, 2007 at 10:03 pm
hi Ahmed,
I did the same thing but y iam not getting the result in the text file?
I will tellu how i did.........
1. Activated xp_cmdshell
2. Created a Table which has 3 columns.
------------------------
create table tablefortrigger
(
track int identity(1,1) primary key,
Lastname varchar(25),
Firstname varchar(25)
)
------------------------
3. Created a trigger on this table.
------------------------
Create Trigger TestTrigger1
On dbo.tablefortrigger
For Insert
As
Begin
Set nocount on
Declare @msg nvarchar(255) /*Check sp_addmessage */
Declare @CmdString nvarchar (2000)
set @msg = 'Inserted \ tablefortrigger \' + convert(varchar(20), getdate()) + ' — '
+(select convert(varchar(5), track)
+ ',' + lastname + ', ' + firstname
from inserted)
RAISERROR(50006,10,1,@msg)
Set @CmdString = 'echo ' + @msg + ' > C:\Documents and Settings\admin\Desktop\save.txt'
EXEC master..xp_cmdshell @CmdString
Set nocount off
End
Go
------------------------
4.Inserted a row in to the table.
------------------------
Insert into tablefortrigger values('Anu','Jahnavi');
------------------------
This is what i have done.
But iam getting an error as.........
---> Access is Denied.
This is in result tab and
in message tab iam getting
---->Inserted \ tablefortrigger \Nov 27 2007 10:23AM — 1,Anu, Jahnavi
(1 row(s) affected)
Record is getting inserted in to the table but iam not getting it in the save.txt file(A Notepad which i have place earlier on my desktop).
Is there any thing wrong i went with plzz not me know..........please......
Actually i have joined recently in this office as a fresher.
But i got this task Which is like a .........for me.Iam not getting .
It has taken 6 days to know that this can be done by xp_cmdshell.
Previously i even din't heard about this.
In training period i have to impress them or else they will kick me out of the office.So Iam strugling for my job.
So as iam a fresher not getting . Please consider me and clarify my doubt.Sorry for troubling u Ahmed.
U r only the hope for me.
Will be waiting for ur reply.Even u can scold i will bare.But plzz guide me.
Thanks & Regards
Anujahnavi S.
November 26, 2007 at 10:09 pm
Hi,
I assume you already ceated the message
EXEC sp_addmessage
@msgnum = 50005,
@severity = 10,
@msgtext = N'%s',
@lang = 'us_english'
GO
Please give me the error displayed
Regards,
Ahmed
November 26, 2007 at 10:18 pm
In a Grid format i got error as
Access Denied ---> First Row
NULL ---> Second Row
But iam getting the trigger message what i have given as
Inserted \ tablefortrigger \Nov 27 2007 10:23AM — 2,anu, jahnavi
(1 row(s) affected)
This came at Message window.
Anujahnavi S
November 26, 2007 at 10:20 pm
Insert into tablefortrigger (Lastname,Firstname) values('Anu','Jahnavi')
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy