Save the Result Of a sql server express query to a text file from sql server express not from command prompt

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  November 30, 2007 at 1:53 am

  #755835

  Hi anujahnavi,

  I made a mistake in the query

  EXEC master.dbo.xp_cmdshell 'bcp "select * from tablefortrigger order by track FOR XML Auto" queryout "d:\anu.xml" -S. -Usa -Puniverse -c, no_output

  but still not work, when I use a BCP in Stored Proc it works properly but When I put it in trigger it does not.

  Still investigating

  Regards,

  Ahmed

• Chandra Sekhara Vyas Dhara

  SSChampion

  Points: 10207

  More actions

  November 30, 2007 at 2:00 am

  #755837

  Example

  EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  November 30, 2007 at 2:03 am

  #755838

  hi vyas,

  we tried it, the output is an xml file, we have not the appropriate output.

  Tks

• anujahnavi

  Mr or Mrs. 500

  Points: 557

  More actions

  November 30, 2007 at 4:11 am

  #755876

  hi Ahmed,

  Seperately if i run it it's working but in a trigger it's not working.

  Iam also trying for that only by doing another task too .

  so iam a bit late to give u a repy.

  Ok

  If u get please forword it to me.

  Thank you.

  Thanks & Regards

  Anujahnavi S.

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  November 30, 2007 at 6:07 am

  #755914

  Hey, I've just tested this and surprisingly it works both in procedure as well as in trigger...:w00t::w00t:

  --Ramesh
  

  ---

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  November 30, 2007 at 11:59 am

  #756096

  Thanks Ramesh for your help.

  If you use a BCP inside the trigger :

  - If the BCP use another table, it will work

  - If the BCP use the same table (for which the trigger has been created) it never work (the query will be running for a while then you have to restart the server)

  Regards,

  Ahmed

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  November 30, 2007 at 11:14 pm

  #756206

  Ahmed,

  Actually, it works in both the cases....

  Here is what my code looks....

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TRIGGER [dbo].[TestTrigger1]

  ON [dbo].[auNumbers]

  FOR INSERT

  AS

  BEGIN

  SET NOCOUNT ON

  EXEC master.dbo.xp_cmdshell 'bcp "SELECT TOP 1 * FROM dbo.auNumbers ORDER BY Number FOR XML AUTO" queryout f:\sharedrive\anu.xml -Smyserver -Usa -Panypwd -c -r -t'

  SET NOCOUNT OFF

  END

  GO

  --Ramesh
  

  ---

• Avaneesh -388582

  SSChasing Mays

  Points: 657

  More actions

  December 1, 2007 at 1:09 am

  #756215

  Hi,

  to save the result to a text file, you can simple right click on the results pane in query analyser and save dialog box pops up then save it as yourfilename.txt.

  Regards

  Avaneesh.

• anujahnavi

  Mr or Mrs. 500

  Points: 557

  More actions

  December 3, 2007 at 2:34 am

  #756387

  hi Ahmed,

  Did u get any result of our query?

  If possible make it know.

  it's taking lot lot lot of time to execute the query.

  I want a trigger as soon as possible.

  Don't think otherwise

  I am having other task......i want to execut the query in a particular time interval can i do this with out trigger?

  Thanks in advance.

  Thanks & Regards

  Anujahnavi S.

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  December 3, 2007 at 6:29 am

  #756444

  Hi Anujahnavi,

  I am sorry for the delay, I made a lot of tests (also I made a lot of search over the Internet).

  It seems the problems are :

  - The last inserted record in the table.

  - When we have over then 50, the XML you will have will not be well formated.

  So I retreive the data to an XML file exluding the last inserted record.

  When the xml is generated, you have to go through to get well formated one.

  Alter TRIGGER [dbo].[TestTrigger1]

  ON [dbo].[tablefortrigger]

  FOR INSERT

  AS

  BEGIN

  SET NOCOUNT ON

  Declare @count int

  Declare @cmd varchar(255)

  -- Create the thable just to add all records of tablefortrigger except the last inserted record

  Create table temp([lastname] [nvarchar](50) NULL,[firstname] [nvarchar](50) NULL,[track] [int])

  set @count=( select count(*) from [dbo].[tablefortrigger])-1

  set @cmd='select top ' +Convert( varchar(10),@count) + ' * from [dbo].[tablefortrigger] ORDER BY track FOR XML Auto, root(''Tracks'')'

  set @cmd= 'bcp "' +@cmd + '" queryout d:\A00Result.xml -Sahmed -Usa -Puniverse -c '

  EXEC master.dbo.xp_cmdshell @cmd

  Drop table temp

  SET NOCOUNT OFF

  END

  I think it will be more efficient if you generate your XML file from your application (.net/Java)

  Regards,

  Ahmed

• anujahnavi

  Mr or Mrs. 500

  Points: 557

  More actions

  December 27, 2007 at 12:17 am

  #762852

  hi Ahmed,

  After a long time........

  again the same query .

  Left the query at that time and now i want to do the same thing from tommorrow.

  Actually ,i am not having any user id or password for my sqlserver express.

  It's in windows athentication mode .is it so i can do this trigger at that point too?

  i will come back with lots of doubts tomm.

  Hope u will help me out.

  Thanks & Regards

  Anujahnavi S.