November 28, 2007 at 2:16 am
Hi Anujahnavi,
bcp Bulk Copy
Check the link http://msdn2.microsoft.com/en-us/library/ms162802.aspx
Elements
Check link http://msdn2.microsoft.com/en-us/library/ms188273.aspx
root('tracks') I have just a root node to the xml document to be valide.
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(50006,10,1,@msg)
Set @CmdString = 'echo ' + @msg + ' > D:\save.txt'
EXEC master..xp_cmdshell @CmdString, no_otput
EXEC master.dbo.xp_cmdshell 'bcp "select * from tablefortrigger order by track FOR XML Auto, ELEMENTS, root(''Tracks'')" queryout D:\Result.xml -S. -Uusername -Ppassword -c -r -t', no_output
Set nocount off
End
Go
Regards,
Ahmed
November 28, 2007 at 2:58 am
Ahmed,
I created a result.xml file in d: is it ok
it automatically creates it?
How can i create a xml file from sqlserverexpress.
ur fead of me right Ahmed.
Sorry for disturbing u .
Anujahnavi S.
November 28, 2007 at 3:02 am
you mean to load the created xml file
November 28, 2007 at 3:15 am
I want to create a empty xml file in d: and want to save the result to that xml file
Anujahnavi S.
November 28, 2007 at 4:12 am
Ahmed garu,
It's not at all recognizing it as a query(select * from tablefortrigger)
Iam getting the output as
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
what should i do?
If the file is not created earlier too it is creating it in the previous xp_cmdshell.
here iam giving 2xp_cmdshell's iam not getting a new file created.
Where is the problem Ahmed garu.
Thanks & Regards
Anujahnavi S.
November 28, 2007 at 4:19 am
Hi,
I have just 3 years as DBA.
I am leaning a lot helping you.
Did you replace username by your login user and password by your login passwor in the following
EXEC master.dbo.xp_cmdshell 'bcp "select * from tablefortrigger order by track FOR XML Auto, ELEMENTS, root(''Tracks'')" queryout D:\Result.xml -S. -Usa -Puniverse -c -r -t', no_output.
Try to execute the query above and tell me what is the result
November 28, 2007 at 5:26 am
Ahmed ,
Iam not having any of the user name or password.
directly windows athentication iam using.
so no need of giving those..
i specified server name.
I have given u know the output what iam getting .....that means the query is not getting identified.
the same thing u have given i pasted and changed the servername and the xml file name.
Anujahnavi S.
November 28, 2007 at 5:28 am
Ahmed ,
Ur DBA so u can do this .
But iam a .net programmer.So iam unable to do this.
Thank God for showing u as a helper.
Anujahnavi S.
November 28, 2007 at 11:58 pm
hi Ahmed,
I got the result to a xml file.
After getting it if i want to execute "select * from tablefortrigger" after executing the cmdshell command of xml file the query is taking lot of time to execute can i know y it is?
Anujahnavi S.
November 29, 2007 at 4:17 am
Hi Anujahnavi,
I am sorry for the delay (little bit sick).
ALTER Trigger [dbo].[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 + ', ' + firstnamefrom inserted)
RAISERROR(50006,10,1,@msg)
Set @CmdString = 'echo ' + @msg + ' > D:\save.txt'
EXEC master..xp_cmdshell @CmdString, no_output
EXEC master.dbo.xp_cmdshell 'bcp "select * from tablefortrigger order by track FOR XML Auto, ELEMENTS, root(''Tracks'') queryout D:\Result.xml -S. -Usa -Puniverse -c -r -t', no_output
Set nocount off
End
Regards,
Ahmed
November 29, 2007 at 5:36 am
hi Ahmed ,
I got the result saved inthe xml file but .........it's only once.
After that iam unaable to execute any of the query on that table.Even "select * from tablefortrigger".
It's going on ............. Executing minimum of 45 min i waited for that ..........Why is this happenning Ahmed?
Anujahnavi S.
November 29, 2007 at 5:44 am
Hi anujahnavi,
Stop and restart your server (Stop the service).
I corrected a bug in the previous post,
I have to go to the office.
Have a nice day.
November 29, 2007 at 6:05 am
Ahmed ,
I din't find any difference between ur last post and the present.
Is it that u removed the ' " ' (double qoutations ) from the query of exec xp_cmdshell before "queryout"? I dont think so .
I stopped the server service even though my query execution is going on how is it possible?
(in controle panel ---> Services ---> Server ) i stopped this and
(in controle panel ---> Services ---> sqlexpress) also i stopped.
If it is the other one please not it know.
Any qury related to that table is taking more time .
Hellp me out plzz.....(if not even i completed my task it will be waste of use).
Thanks & Regards
Anujahnavi S.
November 29, 2007 at 8:03 am
Yes I remove "
Go to SSMS (SQL Server Managemnet Studio), rigth click on the Server click Stop then Start
November 29, 2007 at 9:45 pm
Ahmed ,
This is my trigger .......
Alter Trigger TestTrigger1
On tablefortrigger
For Insert
As
Begin
Set nocount on
EXEC master.dbo.xp_cmdshell 'bcp "select * from tablefortrigger order by track FOR XML Auto queryout d:\anu.xml -S system22 -Usa -Panu -c -r -t'
Set nocount off
End
by this the anu.xml file is getting created but with no data.
Anujahnavi S.
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply