SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Titusville, FL
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored Procedure Problem

    I'm new to stored procedures so if this is common knowledge, please excuse my ignorance.

    I'm trying to convert an ASP page that's been around forever to use a stored procedure instead of the straight sql call that it's been doing for years.

    Anyway, here's the SP:
    Code:
     
    CREATE PROCEDURE   SP_fastrunlog
    (
       @runlogdate datetime
    
    )   
    AS
    
    SELECT * from VW_RUNLOG WHERE (apptdate =   @runlogdate) ORDER BY appttime
    
     return
    GO
    The view VW_RUNLOG is big, huge and terribly complex. It returns dozens of fields. I'm able to loop through the results and all of that fun stuff... I'm able to see all of the fields, and everything is cheery EXCEPT one field doesn't show up in the results. The field is a text field named 'comments'. It works find if i call the view, but it doesn't come through if i use the SP.

    I get the same results when i paste the SQL from the view into the SP. And i get the same results when i put just the important code on a page by itself like so:
    Code:
    sql="EXECUTE sp_fastrunlog '2/18/2004'"
    
    'sql="SELECT * FROM VW_RUNLOG WHERE (apptdate = '2/18/2004') ORDER BY appttime"
    Set rs = server.createobject("adodb.recordset")
    			rs.open SQL,Objconnect,1,1
    	 
    	 
    Do While rs.EOF = False
    
     Response.Write "Name: " &rs("name") & " Comments: " & rs("comments") &"<br>"
    
    rs.MoveNext
    LOOP
    rs.Close
    set rs = nothing
    Alternatly commenting out the SQL commands, the one using the view displays both the name and the comments... using the SP, it only displays the name and not the comments.

    SO: WTF?

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,635
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    That comment field wound not happen to be a TEXT field would it?

    WWB

  3. #3
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, that stored procedure is so "wrong" its hard to know where to start! So I won't

    Is there data in "comments!?

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Titusville, FL
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wwb:
    Yes, it's a text field. I had the idea that was the problem but i'm unable to find any way to either fix it or get around it. I'm new to using stored procedures, so if it's 'common' knowledge I apolize.


    dhtmlgod:
    yes, there are data in comments.

  5. #5
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bump

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's the mostest curiousest bump i ever saw

    a sitepoint community advisor bumping a thread?

    that he/she didn't participate in?

    ten weeks later?

    why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why? Because I would like to know what the answer is to the problem.

  8. #8
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dhtmlgod
    Wow, that stored procedure is so "wrong" its hard to know where to start! So I won't
    Awww! Go on!

    (Is it really *THAT* bad?!!)
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  9. #9
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Dallas, TX
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    No Root Cause but Solution

    Not sure what causes this, but I have ran into this problem before. I have fixed it in the past by specifying all of the field names in the query. I always put the text field last.

    In other words:

    Do:

    Select column1, column2, columntext from table

    Don't:

    Select * from table

    Hope this helps!

  10. #10
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,635
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    To add to sappro's answer, it is an issue with the way the memory cursors work in the SQL Server ADO and ODBC drivers. Text and image columns must be the far-right hand columns in your select clause. Smarter cookies always put it to the far right in their tables.

    Figured this out on a PHP project, which had some interesting results when text was not to the far right . . .

    WWB

  11. #11
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've heard of that happening, but I've never encountered it myself. Putting the variable length/binary fields last is the "official" workaround.

    However, I have a feeling that this bug doesn't crop up with the OLEDB drivers... so I suspect that you are using the old ODBC ones. If this is the case (why?!), I highly recommend that you switch to using the moderm, leaner, meaner, more stable OLEDB drivers instead.

    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  12. #12
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,635
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Had to use ODBC for the PHP app--could not install the MSSQL extensions. Not to mention it started life running on access until we knew we could secure a production sql server.

    It came out pretty good in the end.

    WWB

    PS: Correct me if I am wrong, but are not the ODBC drivers for MSSQL a wrapper around the oledb drivers?

  13. #13
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99
    Correct me if I am wrong, but are not the ODBC drivers for MSSQL a wrapper around the oledb drivers?
    Precisely, which is why ODBC drivers are slower and less stable than pure OLEDB, as well as often generating less useful error messages...

    http://www.adopenstatic.com/faq/whyOLEDB.asp
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  14. #14
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,635
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the link. Now, if only PHP had OLEDB functions . . .

    WWB

  15. #15
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99
    Thanks for the link. Now, if only PHP had OLEDB functions
    Are you running *nix or Windows? If the latter, then:
    http://aspn.activestate.com/ASPN/Coo.../Recipe/123709
    (No reason why you can't do the same with SQL Server OLEDB)

    See here too:
    http://phplens.com/adodb/supported.databases.html
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

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