Debugging help :0) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Debugging help :0)

Author

Topic

kien
Starting Member

27 Posts

Posted - 2002-06-28 : 17:09:13

Can someone please help me debug this stored procedure. The syntax checks out fine, but the stored procedure doesn't work. Your help is much appreciated

I have two tables:
#SearchResults (RecordID int, Latitude decimal, longitude decimal)
tblTest (Record ID, Latitude, Longitude, County, FeatureName, ADM)

This stored procedure accepts a space delimited location string, processes it and places matcing RecordID, latitude and longitude into the #SearchResult table. We then search through vwRecordSetSearch, selecting records that matches the sets of coordinates in the #SearchResult table.

PS: thanks again

Note: credit is given to graz whose code i modified.
http://www.sqlteam.com/itemID=1876

------------------
CREATE PROCEDURE dbo.spSearchByKeyword
@keyword varchar(100)
AS

declare @separator char(1)
declare @separator_position int -- This is used to locate each separator character
declare @keyword_value varchar(102) -- this holds each array value as it is returned
declare @like_text varchar (102)

-- Build my Temp Table to hold results
CREATE TABLE #SearchResults (RecordID int, Latitude decimal, Longitude decimal)

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @separator = ' '
set @keyword = @keyword + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @keyword) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @keyword)
select @keyword_value = left(@keyword, @separator_position - 1)
select @like_text = '%' + @keyword_value + '%'

INSERT #SearchResults
SELECT v.RecordID, v.Latitude, v.Longitude
FROM tblTest v
WHERE v.Country like @like_text

INSERT #SearchResults
SELECT v.RecordID, v.Latitude, v.Longitude
FROM tblTest v
WHERE v.FeatureName like @like_text

INSERT #SearchResults
SELECT v.RecordID, v.Latitude, v.Longitude
FROM tblTest v
WHERE v.ADM like @like_text

-- This replaces what we just processed with and empty string
select @keyword = stuff(@keyword, 1, @separator_position, '')
end

SELECT DISTINCT v.*
FROM vwRecordSetSearch v, #SearchResults S
WHERE (v.Latitude = S.Latitude) AND (v.Longitude = S.Longitude)
Group by S.RecordID

RETURN (0)
GO

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 10:25:52

quote:
SELECT DISTINCT v.*
FROM vwRecordSetSearch v, #SearchResults S
WHERE (v.Latitude = S.Latitude) AND (v.Longitude = S.Longitude)
Group by S.RecordID

Is that returning an error or unexpected results or no results?

For testing.. do
select * from #searchResults
Just to see if there are infact entries being made into the temp table

-----------------------
Take my advice, I dare ya

kien
Starting Member

27 Posts

Posted - 2002-07-02 : 12:36:01

Hi M.E (or anyone else reading this)

I've ran the above code in Query Analyzer and everything works fine. However when i access it through the web application, things start to go wrong.

I'm passing the input string from Visual Basic 6, to the stored procedure in SQL server 2000.

I'm not definite as to long things get passed back and forth. When I do a simple stored procedure with only one select statement, everything works great.

Things start to screw up when i insert data into temp. tables using select statements, at the same time as trying to retreive data using another select statement.

Please help

Thanks, much appreciated!
Kien

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 13:37:26

I don't actually believe this problem is with your SQL procedure at all. Tis fine. If it works in a query analyzer (try putting in long variables into your proc through query analyzer) but not through your web application, then your problem is with the web application bit.

Unfortunately not many here seem to know much about vb6/asp or whatever your using, but if you want I could try to trouble shoot that coding. It may just be something getting truncated.

-----------------------
Take my advice, I dare ya

kien
Starting Member

27 Posts

Posted - 2002-07-02 : 14:17:59

Hi M.E.

Here's the scoop. The following works:
----
CREATE PROCEDURE dbo.spSearchByKeyword
@Keyword varchar(100)
AS

DECLARE @query varchar (102)
SET @query = '%' + @Keyword + '%'

SELECT *
FROM vwRecordSetSearch
WHERE (Country LIKE @query)

RETURN (0)
GO
----
But the code with insert tables.. does not work.
The format that I'm sending out to VB 6 is the same as the above, so i don't think the problem is with trucation. Below is the select statement in the piece of code that doesn't work:

SELECT DISTINCT v.*
FROM vwRecordSetSearch AS v, #SearchResults AS S
WHERE (v.Latitude = S.Latitude) AND (v.Longitude = S.Longitude)
----

Below is the portion of the VB 6 code that messes up.
I'm not sure how well this can help since it is only an excert of a huge piece of code. I did not write the following, which would explain my lack of understanding :)

Thanks again

PS: the error is generate near the bottom of the following code
-----

Public Function Search(ParamArray varInputParameters() As Variant) As ADODB.Stream
' search method is defined by mstrSearchMethod

On Error GoTo ErrHandler

If Len(mstrSearchMethod) < 1 Then
RaiseErr , 5
Exit Function
End If

Dim strSearchMethod As String
strSearchMethod = mstrSearchMethod

' setup command object
Dim objComm As ADODB.Command
Set objComm = New ADODB.Command

Call AppendReturnValue(objComm)

Dim intCounter As Integer
For intCounter = 0 To UBound(varInputParameters)
Call AppendInputs(objComm, varInputParameters(intCounter))
Next intCounter

' prepare recordset
Dim objRec As ADODB.Recordset

'open connection
mobjConn.Open

' prepare command and execute it
With objComm
.ActiveConnection = mobjConn
.CommandTimeout = 30
.CommandType = adCmdStoredProc
.CommandText = mstrSearchProcName 'ie spSearchByKeyword
End With

Set objRec = objComm.Execute

' obtain return value
Dim intSprocRetValue As Integer
intSprocRetValue = objComm.Parameters(0).Value

Set objRec.ActiveConnection = Nothing

' close connection
mobjConn.Close

Set objComm = Nothing

'set number of returned records
mintNumOfRecords = objRec.RecordCount

'save recordset into stream as XML
Dim objStream As ADODB.Stream
Set objStream = New ADODB.Stream

objRec.Save objStream, adPersistXML

'objStream.SaveToFile "D:\Temp\xml.txt", adSaveCreateOverWrite

' close recordset and destroy
objRec.Close
Set objRec = Nothing

' check return value - go to error handler if <> 0
If intSprocRetValue <> 0 Then ' error occured
GoTo ErrHandler
End If

Set Search = objStream

Set objStream = Nothing

Exit Function

ErrHandler:

' deallocate objects
If Not IsEmpty(objComm) Then Set objComm = Nothing
If Not IsEmpty(objRec) Then Set objRec = Nothing
If Not IsEmpty(objStream) Then Set objStream = Nothing

' close connection if open
If mobjConn.State = adStateOpen Then mobjConn.Close

RaiseErr , 2 '*************** error message 2 displays, so i'm assuming the mobjconn is still open, but have no idea why.

End Function

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 15:17:19

AHHHH VB..

I'll have to take a bit and look over this coding. Won't promise you a solution... been over a year since I last made anything in vb. I think I'll just ask the

what he thinks wrong

Gimmie a bit, I got a appointment I gotta goto first... Anyone else who visits these forums know vb?

-----------------------
Take my advice, I dare ya

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-07-02 : 15:37:20

Turn on SQL Profiler to see exactly what your VB app is passing to your server.

Once you have done this, post one statement that works and one that doesn't. That would be useful debugging information.

Jason_D
Starting Member

11 Posts

Posted - 2002-07-02 : 15:51:58

I say you take an early vacation!... heehee

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-07-03 : 03:28:52

Are you moving to the correct recordset in the ADO Recordset collection you are calling...

this is the piece of code firing your error message...

intSprocRetValue = objComm.Parameters(0).Value

If you cannot break it and put a watch on objComm, then call an alert.. e.g..

alert objComm.Parameters(0).Value

Peace

Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources