isql problem with null values

rbarry · #1 (**permalink**) 06-26-06, 09:12

I am having a problem running a stored procedure using isql to launch the procedure.

My stored procedure contains a statement like:

select @myvar = count(*) from mytable where mycolumn is null

when I run the procedure from query analyser a value is set in @myvar showing the correct number of null values from mytable which is output to another table where I can see it.

Without making any changes, when I run the identical procedure using isql to execute the procedure @myvar gets set to zero. The procedure cannot find the null values in mytable

I have tried setting ANSI_NULLS on and off inside the procedure but still get the same result.

Can someone please tell me what is going on, and how I might get isql to show me how many null values exist in the table?

Brett Kaiser · #2 (**permalink**) 06-26-06, 12:46

I don't seems to have anyproblem with this

Code:

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 <username> -P <password> -S <servername> -d Northwind -Q "' + @sql + '"'
EXEC master..xp_cmdshell @cmd
GO

DROP PROC mySproc99
DROP TABLE myTable99
GO

bpolunin · #3 (**permalink**) 06-26-06, 14:07

Which SQL Server are you running? If SQL 2000, which campatibility level?

rbarry · #4 (**permalink**) 06-26-06, 15:55

Brett,

Thanks for your reply, I'll try out your script tomorrow morning when I get into the office. And let you know the result.

The reason I'm confused, is I set up my script using osql and it worked fine on our test server, It didn't run at all on production, then I found that it did run if I changed osql to isql, but with the quirk noted. I'm sure it is something to do with the OLE DB config, but I am not sure where to start looking for differences. Also the osql version might work if I could get ODBC set up on production, but I'm not sure how to go about doing that.

Anyway, thanks again for all your help and I'd be grateful for any further suggestions.

psullivan · #5 (**permalink**) 07-27-06, 14:51

I don't have tons of experience with xp_cmdshell but my results from the example script above look pretty strange:

[img]www.sullivanstreet.us/query.jpg[/img]

Internet Services

Web Development

Digital Marketing

Consumer Tech