Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
footohi
Starting Member
10 Posts |
 Posted - 2006-05-17 : 18:50:46
|
| My objective is the return output from my SQL db to a text file to be uploaded to DB2. All the queries have to be appended to one file.1st I've tried putting multiple queries (example below) in a Stored Proc, and then executing it with osql. The output has lots of extra lines in it with spaces even though my SP returns fine.EXEC master..xp_cmdshell 'osql.exe -S XXXX -U sa -P XXXXX -i "c:\HRMSbatches.sql" -n -h-1 -s -o "C:\temp\output.txt" -w 58'2nd for testing purposes I tried just running one of my queries to a text file to figure out why I'm getting all the extra lines, but I can't run the following query in the osql command:declare @String varchar(2000)set @String = 'select ""A1"" as Trans_Code, "" "" as blank1,APPLIC_ASU_ID as Employee_ID,REPLACE(CONVERT(char,GETDATE(),101),"/","") as Effective_Date,""01"" as Personnel_Action,CONVERT(CHAR(6),GETDATE(),12) as Personnel_Action_Date from HIRING_PROCESS'select @Stringdeclare @cmd varchar(400)set @cmd = 'osql.exe -S XXXX -U sa -P XXXXXX -Q "select ""A1"" as Trans_Code, "" "" as blank1,APPLIC_ASU_ID as Employee_ID,REPLACE(CONVERT(char,GETDATE(),101),""/"","""") as Effective_Date,""01"" as Personnel_Action,CONVERT(CHAR(6),GETDATE(),12) as Personnel_Action_Date from HIRING_PROCESS" -o "C:\temp\output.txt"'EXEC master..xp_cmdshell @cmdPlease help! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-17 : 20:13:47
|
| OSQL is not a good tool for what you are trying to do; BCP or DTS are better tools for this.You can use the DTS Export Wizard to export data to a text file by opening Enterprise Manager, connecting to a database, and then selecting Data Transformation Services, Export Data from the Tools menu, and then following the directions. You can chose tables, views or queries as your data source, and a text file as your output destination. You can read about Data Transformation Services or BCP in SQL Server Books Online. CODO ERGO SUM |
 |
|
|
footohi
Starting Member
10 Posts |
Posted - 2006-05-22 : 16:20:49
|
| I tried both DTS and BCP...DTS needed the data transformed or mapped to a single table output.All my queries are in a Stored Proc & differ in recordset structure.Are you saying I'd have to transform all the queries separate?BCP did not output the data properly, and I could not figure out how to format it for fixed datasets appended together.Please, any more help? |
|
|
|
|
|
|
|
|
complex query in osql to output file