If you try to export data from sql server to excel then use this stored procedure as bellow
Cretae an excel file in C: and name it Test.xls
CREATE proc EXP_FROM_SQLServer_TO_Excel
(
@File_Name as varchar(100) = ' ', - -The file content that would be exported from sql server
@Query AS VARCHAR(100) - -What you want exactly put in the excel sheet
)
as
BEGIN

--Provider
DECLARE @Provider VARCHAR(100)
–Excel Connection string
DECLARE @ECS VARCHAR(100)
--Command
DECLARE @Cmd VARCHAR(100)
–file name
DECLARE @FN VARCHAR(100)
Select @FN = 'C:\' + @File_Name + '.xls'
SELECT @Cmd = 'Copy C:\Test.xls ' + @ FN
EXEC MASTER..XP_CMDSHELL @Cmd, NO_OUTPUT
SET @Provider = 'Microsoft.Jet.OLEDB.4.0'
SET @ESC = 'Excel 8.0;Database=' + @FN
EXEC('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT Field1, …….., Fieldn FROM [Sheet2$]'') –replace the Sheet2$ by your sheet name

select Field1, …….., Fieldn from YourDataTable')

SET NOCOUNT OFF
END

Then you can call this stored procedure from SQL Server or even from C# code as follow

SqlConnection oConnection = new SqlConnection("ConnectionString");
SqlCommand oCommand = new SqlCommand();
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.CommandText = "EXP_FROM_SQLServer_TO_Excel";
SqlParameter FileName = new SqlParameter("File_Name", yourInputValue);
SqlParameter Query = new SqlParameter("Query", yourinputvalue2);
oCommand.Parameters.Add(FileName);
oCommand.Parameters.Add(Query);
oCommand.ExecuteNonQuery();

I tried it and It works for sure

i have the procedure i have called it also. its running all well. only thing that is the hurdle is that i am not bale to save it anywhere. It gets stored in my documents folder only

can you be more specific and write the code instead of suggesting what is wrong in my code.it will be more help if u would code the line for saving the file.

I cant express how angry you make me.

If you are incapable of changing the value of a variable or setting a variable to a value and your home work is to do th is.. You are on the wrong course.

Could you precise from where you are attemding to transfer the data