Script object into a text file
-
Hi All,
After this:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
--This works
declare @STR varchar(100)select @STR = 'declare @i int'
print @STR
select @STR = 'ECHO ' + @STR + ' >>C:\file1.txt'
EXEC xp_cmdshell @STR, no_output
--This does not work
declare @STR varchar(100)
select @STR = 'create table #test
(ind int primary key not null,
name varchar(50)
)'
print @STR
select @STR = 'ECHO ' + @STR + ' >>C:\file2.txt'
EXEC xp_cmdshell @STR, no_output
Additionally, if I want to save "print object_definition(object_id)" where object_id is an id for a view with definition longer than 8000 chars, then what is the solution to save its definition into a text file?
xp_cmdshell expects parameter 'command_string' of type 'varchar', and varchar(max) is not supported.
Thanks in advance,
IgorMi
Igor Micev,My blog: www.igormicev.com
-
Have a look at the scripter object that is available in Powershell. This link has some details.
This would be a much better way of doing what you want.
-
yes VARCHAR(MAX) is not allowed, but you can use VARCHAR(8000).
Try executing the code below:
declare @STR varchar(8000)select @STR = 'create table #test
(ind int primary key not null,
name varchar(50)
)'
PRINT @STR
select @STR = 'ECHO ' + REPLACE(REPLACE(@str,CHAR(10),' '),CHAR(13),' ') + ' >>D:\file2.txt'
EXEC xp_cmdshell @STR, no_output
I have also replaced, line-feeds and next line characters with space.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Hi Guys,
Thank you for your replies! They are both useful.
Albeit I'll go with the Scripter class in C#.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply