Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Location
    London UK
    Posts
    5

    Question Unanswered: isql problem with null values

    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?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Which SQL Server are you running? If SQL 2000, which campatibility level?

  4. #4
    Join Date
    Jun 2006
    Location
    London UK
    Posts
    5
    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.

  5. #5
    Join Date
    Jul 2006
    Posts
    1
    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]
    Attached Thumbnails Attached Thumbnails query.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •