October 29, 2009 at 3:43 pm
Hi Folks
i am trying to get use dynamic sql to generate commands to an output file and when i look at the output i see one long string divided with little boxes where my linefeed character is suppose to be.
Anyone have an idea on what I need to do to get the output to look correct ?
my file: E:\SQL_SERVER_SQL\gen_database_tablenames.sql
consists of:
select
CAST ("use [" + name + "];" + char(10) + "go" + char(10) +
"select CAST (name as varchar(48)) as table_name from sysobjects where xtype='U' order by name;" + char(10) +
"go" + char(10) as varchar(156)) as Commands
from
sys.sysdatabases
where
dbid IN (1,3,4)
order by
name;
go
my command:
sqlcmd -E -i E:\SQL_SERVER_SQL\gen_database_tablenames.sql -o e:\sql_server_sql\create\list_tables.txt
output file (list_tables.txt):
NOTE; i placed a "^" where the little box shows up in my output file becase if i copy and paste it here it looks like i wanted it to..
Commands
--------------------------------------------------------------------------------------------------------------------------
use [master];^go^select CAST (name as varchar(48)) as table_name from sysobjects where xtype='U' order by name;^go^
use [model];^go^select CAST (name as varchar(48)) as table_name from sysobjects where xtype='U' order by name;^go^
use [msdb];^go^select CAST (name as varchar(48)) as table_name from sysobjects where xtype='U' order by name;^go^
this is what i should see:
use [master];
go
select CAST (name as varchar(48)) as table_name from sysobjects where xtype='U' order by name;
go
use [model];
go
select CAST (name as varchar(48)) as table_name from sysobjects where xtype='U' order by name;
go
use [msdb];
go
select CAST (name as varchar(48)) as table_name from sysobjects where xtype='U' order by name;
go
Thanks
Jim
October 29, 2009 at 3:52 pm
Hi
(Disclaimer: Can't try this at the moment due to a missing test system :hehe: )
The "little boxes" might appear in notepad because in Windows you need a CRLF (carriage-return line feed). Try "CHAR(13) + CHAR(10)".
Greets
Flo
October 29, 2009 at 4:22 pm
Hi Flo
Excellent !
Worked perfect
Thanks
Jim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply