u can see that exported file follwing location c:\T1Import88.CSV

        DECLARE @SQL VARCHAR(2000)
                DECLARE @AllowExportExcise VARCHAR(200)
                DECLARE @DataBase VARCHAR(100)          
                DECLARE @Server sql_variant
                DECLARE @Path VARCHAR(200)
                DECLARE @ExportResult INT
                SET @DataBase = DB_NAME(DB_ID())
                SELECT  @Server =  'rameshs\sqlexpress' -- your servername

                SET @ExportResult = 0
                SET @AllowExportExcise =  'True'
                IF @AllowExportExcise = 'True'
                BEGIN
                        SET @Path = 'C:\Books'
                        SET @SQL = 'bcp " exec (''select * from INFORMATION_SCHEMA.COLUMNS where
ordinal_position = 1'') "  queryout c:\T1Import88.CSV -T -c -t'+ CAST(@Server
AS VARCHAR(100)) + ' -U sr -P sr '       -- username password
                        EXEC @ExportResult = master..xp_cmdshell @SQL  ,NO_OUTPUT  
                        IF @ExportResult <> 0 -- : Failed
                        BEGIN
                                SELECT   2 AS ErrorID   -- 2 : Error in Export Excise          
                                                ,'Excise Export Failed; Path may be Incorrect' AS ErrMsg

                                RETURN  
                        END
                END
                SELECT   0  AS ErrorID
                                ,'' AS ErrMsg          

all the record stored in a single line i want to store every row in a every
line

pls help me

A few general notes:

Make above sysname datatype.

Why would able be sql_variant? Make it somw varchar type.

It is always a good thing to PRINT the string you are about to execute. Why would you use EXEC to
dynamically execute the SQL code for queryout? Skip that EXEC.

I've adapted your example (server name) so it runs on my machine, making it:

DECLARE @SQL VARCHAR(2000)
DECLARE @Server varchar(100)
SELECT @Server =  'localhost'
SET @SQL = 'bcp "select * from INFORMATION_SCHEMA.COLUMNS where ordinal_position = 1" queryout
c:\T1Import88.CSV -T -c -t'+ @Server + ' -E '
PRINT @sql
EXEC master..xp_cmdshell @SQL  --,NO_OUTPUT

You can now see that you have no -r (row terminator) and that the server name serves as column
terminator. Let's fix that, and also add -w making it Unicode so that Excel understands the file
format (assuming this is what you want to do):

DECLARE @SQL VARCHAR(2000)
DECLARE @Server varchar(100)
SELECT @Server =  'localhost'
SET @SQL = 'bcp "select * from INFORMATION_SCHEMA.COLUMNS where ordinal_position = 1" queryout
c:\T1Import88.CSV -T -c -t\t -r\n -S'+ @Server + ' -E -w'
PRINT @sql
EXEC master..xp_cmdshell @SQL  --,NO_OUTPUT

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

news:1EB6A0D3-EEFE-47CB-8B1A-38BE1FDC7CE1@microsoft.com...