Generating XML file trigger ? - 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)

Generating XML file trigger ?

Author

Topic

RAHMONY
Starting Member

4 Posts

Posted - 2011-11-19 : 10:31:23

hello everyone im new here .
i need a trigger that does this , i have this table and i want to generate xml of new inserted data whenever it insert , it will create the xml file into a specific folder i specify , i looked around i found many way and couldnt succeed , i could make the query and it will make the output in xml format by (for xml )clause but it doesnt create the actual xml in where i want ,
i tried bcp commands(newbi on that ) but i didnt success , any idea ?

thx in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 12:52:35

you can use bcp itself. can we see the code you used?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RAHMONY
Starting Member

4 Posts

Posted - 2011-11-19 : 13:31:43

hello visakh16 thank u for yr respond .
heres the code i put in sql

-----------------------------------------------------------

DECLARE @FileName VARCHAR(50)

DECLARE @SQLCmd VARCHAR(500)

SELECT @FileName = 'C:\Temp\SampleXMLOutput.xml'

SELECT @SQLCmd = 'bcp ' +

'"SELECT first_name,last_name ' +

' FROM userrs ' +

' FOR XML PATH(''''), ROOT(''info''), TYPE "' +

' queryout ' +

@FileName +

' -w -T -S' RAHMONY-PC\RAHMO -U sa -P MYpassword

SELECT @SQLCmd AS 'Command to execute'

EXECUTE master..xp_cmdshell @SQLCmd
----------------------------------------------------------------------------------------------------------------------------
|
the result is this |
Msg 102, Level 15, State 1, Line 21 |
Incorrect syntax near 'RAHMONY'. |
|
------------------------------------------|
the only thing that i could do is the normal query the select all the records in xml format in the result area "for xml " but this bcp thing
i mean should that be excuted in command prompt or it could work in sql environment
cuz wat i did is to type that could as new query in sql !!
i tried to simulate this code from a other website

ps: wat i want is insert but as in the code i only made select cuz im tryin @ least to create an xml file in a folder then will modify that for insertion !
thx in advance

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-19 : 13:37:17

BCP is a command line program.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 14:19:12

quote:
Originally posted by russell

BCP is a command line program.

you can call it through xp_cmdshell

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 14:24:16

all of them should come inside braces. Also if you're using -T you dont require to pass username and password ie -U and -P options


DECLARE @FileName VARCHAR(50)

DECLARE @SQLCmd VARCHAR(500)

SELECT @FileName = 'C:\Temp\SampleXMLOutput.xml'


SELECT @SQLCmd = 'bcp ' +

'"SELECT first_name,last_name ' +

' FROM userrs ' +

' FOR XML PATH(''''), ROOT(''info''), TYPE "' +

' queryout ' +

@FileName +

' -w -T -S RAHMONY-PC\RAHMO -U sa -P MYpassword'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-19 : 15:04:22

Oops, didn't see he was using xp_cmdshell. some reason I thought he was trying to execute it as a SQL command.

RAHMONY
Starting Member

4 Posts

Posted - 2011-11-19 : 17:47:50

oh wow thnx for the help guys :)
i excuted that but i got this error s

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'userrs'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL
-----------------------------------
userrs is the table name i changed like that cuz user is already taken for the sys i guess but from wat i see i guess it canntont recognise where to go to excute
i put in first of the code
use dbname
and in the end "EXECUTE DBNAME..xp_cmdshell @SQLCmd" is it correct ?
i tried and checked the path but the file still not there !
any suggestions ?
and once again russell and visakh16 THAN U , u already helped !

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-19 : 19:28:54

SELECT first_name,last_name FROM dbname..userrs

EXEC master..xp_cmdshell

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 01:39:21

quote:
Originally posted by RAHMONY

oh wow thnx for the help guys :)
i excuted that but i got this error s

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'userrs'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL
-----------------------------------
userrs is the table name i changed like that cuz user is already taken for the sys i guess but from wat i see i guess it canntont recognise where to go to excute
i put in first of the code
use dbname
and in the end "EXECUTE DBNAME..xp_cmdshell @SQLCmd" is it correct ?
i tried and checked the path but the file still not there !
any suggestions ?
and once again russell and visakh16 THAN U , u already helped !

it should be master..xp_cmdshell
and which db is userrs existing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 01:39:45

quote:
Originally posted by russell

Oops, didn't see he was using xp_cmdshell. some reason I thought he was trying to execute it as a SQL command.

No probs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RAHMONY
Starting Member

4 Posts

Posted - 2011-11-21 : 09:53:00

thank u for the help :)
got it done

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 11:07:19

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources