Michalowo Zákysník

Pokud používáte ne zcela bezpečnou extended proceduru xp_cmdshell (občas to bez ní prostě nejde), tak by se občas třeba i hodilo získat nějakým hezkým způsobem výstup procedury ke zpracování. Možná to je veřejné tajemství jak na to, ale já na to přišel fakt nedávno.

Myslím, že kód níže mluví za vše :)

-- Nejdrive musime povolit xp_cmdshell

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

 

-- Vytvorime si tabulku pro ukladani informai z xp_cmdshell

CREATE TABLE ShellOutput (OutputLine VARCHAR(MAX))

GO

 

-- Spustime xp_cmdshell

INSERT INTO ShellOutput

Execute master.sys.xp_cmdshell 'bcp tempdb..Employee out c:\temp\Employee.txt -c'

GO

 

-- Podivame se do tabulky jejiz obsah vidime o par radku nize….

SELECT * FROM ShellOutput

GO

 

OutputLine

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

User name not provided, either use -U to provide the user name or use -T for Trusted Connection

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

(13 row(s) affected)

 

Co více dodat?