Hi all,

DECLARE @SQL varchar(2000)
SELECT @SQL = 'CREATE TABLE ' + @TABLENAME + ...........
EXEC @SQL
SELECT @SQL = 'echo ' + @SQL + ' > D:\SQL1.txt'
SELECT @SQL 'SQL'
EXEC master..xp_cmdshell @SQL

I build the dynamic Query and that Query output should be finally written to the file system. I used the above program to write it to the file system. The problem here is only part of the string is written and when i printed @SQL variable it outputs only the part of the String. (Dynamic query is stored in @SQL variable), but my dynamic execution of table structure goes fine and the table is created successfully in the database.

Pls help me out in resolving it.






Chandru

__________________
Thanks,
Chandra

Not sure if this is the problem but this may help, something to try. You may just be getting extra blanks at the end that your not seeing. To troubleshoot you may want to test the length of fields to see if they are as long/short as you think they are.

DECLARE @SQL varchar(2000)
SELECT @SQL = 'CREATE TABLE ' + rtrim(@TABLENAME) + rtrim(...........)
EXEC @SQL
SELECT @SQL = 'echo ' + rtrim(@SQL) + ' > D:\SQL1.txt'
SELECT @SQL 'SQL'
EXEC master..xp_cmdshell @SQL

If you a printing the string in Query Analyzer, the default setting for each column is something like 255 characters. If you want to see it all, you'll need to select [TOOLS][OPTIONS], select the [RESULTS] tab, and set the [Maximum Characters Per Column] setting to something larger. I set mine to 8,060 and leave it.

--Jeff Moden

Chandru your problem is only due to enter key character (char(13)), if you use any return key withing @SQL string , only that part of string that comes before enter (char(13)) is taken by SELECT statement and this way '> D:\sql.txt' is also trimmed out ..

if there is no return key in @SQL string , your CREATE statement will be logged to system file easily ...

manudutt, You are wrong. That is not the case...

Try this out in your system... and open sql.txt file.
carumuga,

Unless you post the complete code, we wouldn't be able to point out what you are missing. How about posting the complete code here?

Cheers

_________________________
- Vijay G
Strive for Perfection