osql query problem

Question

Post reply

osql query problem

carolina garcia

Mr or Mrs. 500

Points: 537
More actions
March 15, 2005 at 9:42 am

#92631

Hi everyone
I need help. I am using osql in order to obtaing the results of this query
select ('EXEC sp_change_users_login ' + '''Auto_Fix''' + ',' + '''' + u.name + '''' + ',' + 'NULL') as name from master..syslogins l right join sysusers u on l.sid =u.sid where l.sid is null and issqlrole <> 1 and isapprole <> 1 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'system_function_schema')
this is the command that I used in my schedule task
osql -S -E -h-1 -i F:\sqlbk\sqlmaint\scripts\select.sql -o F:\sqlbk\sqlmaint\scripts\selecto.sql -n
I get an output file with the results with more than a hundred records like this:
EXEC sp_change_users_login 'Auto_Fix','yajaidam',NULL
and I have to submit this output in order to fix the orphans, but at the end of that output it was generated also the last line indicating how many records the query got back: (109 rows affected), so when I tried to run this oputput file with the following command
osql -S -E -i F:\sqlbk\sqlmaint\scripts\selecto.sql -o F:\sqlbk\sqlmaint\scripts\orphans.out -n
I get an error because the last line that was wrote by my previous query is not recognized ((109 rows affected))
HOW CAN I AVOID TO write it in the output file since the first query? is there any command?
Thanks
Hi everyone
I need help. I am using osql in order to obtaing the results of this query
select ('EXEC sp_change_users_login ' + '''Auto_Fix''' + ',' + '''' + u.name + '''' + ',' + 'NULL') as name from master..syslogins l right join sysusers u on l.sid =u.sid where l.sid is null and issqlrole <> 1 and isapprole <> 1 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'system_function_schema')
this is the command that I used in my schedule task
osql -S -E -h-1 -i F:\sqlbk\sqlmaint\scripts\select.sql -o F:\sqlbk\sqlmaint\scripts\selecto.sql -n
I get an output file with the results with more than a hundred records like this:
EXEC sp_change_users_login 'Auto_Fix','yajaidam',NULL
and I have to submit this output in order to fix the orphans, but at the end of that output it was generated also the last line indicating how many records the query got back: (109 rows affected), so when I tried to run this oputput file with the following command
osql -S -E -i F:\sqlbk\sqlmaint\scripts\selecto.sql -o F:\sqlbk\sqlmaint\scripts\orphans.out -n
I get an error because the last line that was wrote by my previous query is not recognized ((109 rows affected))
HOW CAN I AVOID TO write it in the output file since the first query? is there any command?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6084 More actions · Answer 1

To avoid the rowcount being printed use the statement "SET NOCOUNT ON "

so make your first sql script look like this:

SET NOCOUNT ON select ('EXEC sp_change_users_login ' + '''Auto_Fix''' + ',' + '''' + u.name + '''' + ',' + 'NULL') as name from master..syslogins l right join sysusers u on l.sid =u.sid where l.sid is null and issqlrole <> 1 and isapprole <> 1 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'system_function_schema')

Note that the space is sufficient to separate the statements

carolina garcia Mr or Mrs. 500 Points: 537 More actions · Answer 2

Thousands of THANK YOUS!!!!

Muchas Gracias....It did worked!!!!

Thanks