x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

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

Print | posted on Monday, June 26, 2006 12:57 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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 :))
6/26/2006 7:23 PM | Mladen
Gravatar

# re: OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)

Good Lord, what a maroon
6/27/2006 12:41 PM | Brett
Gravatar

# 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?
6/27/2006 5:54 PM | rockmoose
Gravatar

# 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.
6/28/2006 1:32 AM | Tara
Gravatar

# 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?

6/28/2006 12:41 PM | Brett
Gravatar

# 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.
6/28/2006 2:17 PM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET