| Author |
Topic |
|
JeffT
Posting Yak Master
111 Posts |
 Posted - 2007-02-02 : 09:43:16
|
| Hi,I'm running the osql below from a batch file:%SQLPath%osql -U%DBUser% -P%DBPswd% -S%SQLServer% -d%Gatewaydb% -w256 -b -n; -c -h-1 -iBatchScripts\SQL_Get_Create_Batch1.sql -oZ:\mbox\mmis\835\in\create_835_1.batMy output file comes out fine with one exception, there is a blank space at the beginning of each line. Is there a way to remove this ? One suggestion I have seen is to do this with BCP instaed of osql but I don't know if BCP can "run" my "SQL_Get_Create_Batch1.sql" file above. The ".sql" creates a temporary table and then does a select.Thanks,J |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-02-02 : 11:06:36
|
| It sounds like you need to cast everything in your output query to varchars so that the numbers don't output with leading spaces. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-02 : 11:07:02
|
| You can run it in bcp but will probably have to execute a set fmtonly off at the start of the query - it will cause the command to be run twice but as long as you aren't changing data it will only be a performance problem.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-02-02 : 12:06:38
|
| Thanks snSQL and nr !nr, I guess i'm not clear on what "set fmtonly off" does. Also, if use bcp, I know you can do something like: bcp "select record from...." queryout "test.txt" etc., etc. but can you run sql with bcp that is contained in an ".sql" file (the one I have created that is currently run by the "osql" creates a temporary table, does a couple of SELECTs and then drops the temp table) Thanks,J |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-02 : 20:34:44
|
| You can put all that in a stored proc and call it from bcp using queryout "exec mydb..mysp". You could add it to the statment but an sp is easier to handle.As to set fmtonly.When you run a bcp statement it first gets the structure of the resultset. It does this by executing the command using set fmtonly on - this doesn't return any data. Unfortunately the temp tables aren't created so it causes an error.If you code"set fmtonly off exec mydb..mysp" then the command will be executed first with set fmtonly on but this will be overriden by the set fmtonly off - the sp will be executed ok and return data which will be ignored. It will then be executed again to get the resultset.Note that the sp is now executed twice so any updates will be repeated. If you update a counter it will be updated twice.If you flag the rows extracted then the flag will be set on the first call and the rows will be missed on the second - use an intermediate value to get round this.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-02-04 : 00:28:48
|
| Thanks nr. I have created the stored procedure and am trying to run it with bcp but I can't seem to get the syntax right. It's telling me it can't find the stored procedure although it seems like I am pointing to it:%MSSQLPATH%bcp "exec usp_create_batch_1" queryout "Z:\mbox\835\in\create_835_1.bat" -b1000 -c -t~ -U %DBUser% -P %DBPswd% -S%SQLServer%Thanks,J |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-04 : 11:06:41
|
Prefix your SP name with the name of the database and owner like this:%MSSQLPATH%bcp "exec db1.dbo.usp_create_batch_1" queryout "Z:\mbox\835\in\create_835_1.bat" -b1000 -c -t~ -U %DBUser% -P %DBPswd% -S%SQLServer% Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-02-04 : 14:57:27
|
| Thanks very much Harsh, I will try this.J |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-02-05 : 00:31:01
|
| In order to get rid of the blank space at the beginning of each line when using OSQL, you have to use -s'' The -s parameter controls what the column separator is. From Books Online...-s col_separatorSpecifies the column-separator character, which is a blank space by default. To use characters that have special meaning to the operating system (for example, | ; & < >), enclose the character in double quotation marks (").--Jeff Moden |
|
|
|
|
osql - blank space beginning of line