Brett Kaiser (x002548) Blog

Not Just a Number - Brett Kaiser

OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter…)

What?  Huh?  This can't be...can it?  Well this person seemed to think so:

http://www.dbforums.com/showthread.php?t=1219537

I didn't think that this was possible...and, well, it isn't...so I am posting this for future reference....and to start blogging all things SQL again

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1)
SELECT 1    UNION ALL
SELECT 2    UNION ALL
SELECT 3    UNION ALL
SELECT null UNION ALL
SELECT null UNION ALL
SELECT null
GO

CREATE PROC mySproc99 @Rows int OUTPUT AS
 SELECT @Rows = COUNT(*) FROM myTable99 WHERE Col1 IS NULL
GO

DECLARE @Rows int
EXEC mySproc99 @Rows OUTPUT
SELECT @Rows

DECLARE @cmd varchar(8000), @sql varchar(8000)
SELECT @sql = 'DECLARE @Rows int EXEC mySproc99 @Rows OUTPUT SELECT @Rows'
SELECT @cmd = 'osql -U sa -P <password>-S <servername> -d Northwind -Q "' + @sql + '"'
EXEC master..xp_cmdshell @cmd
GO

DROP PROC mySproc99
DROP TABLE myTable99
GO

Legacy Comments


Mladen
2006-06-26
re: OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)
nice SA password, brett :))

and that sure is a complex server name you got there :))

Brett
2006-06-27
re: OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)
Good Lord, what a maroon

rockmoose
2006-06-27
re: OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)
This site is currently down for maintenance. Currently we are experiencing data constancy issues with dBforums.

constancy?, is that a mysql term?

Tara
2006-06-28
re: OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)
osql -E -S <servername> ...

That'll prevent oopsies in the future.

Brett
2006-06-28
re: OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)
Don't you still have to supply a user name?

And where have you been?

VaKay?


Tara
2006-06-28
re: OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)
No you don't need to supply a user name when you use the E switch. It's like when you log into Query Analyzer or Enterprise Manager with Windows Authentication. It uses the credentials of the person logged in. In your case with xp_cmdshell, it uses the service credentials.