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 55 total)
You must be logged in to reply to this topic. Login to reply