Re: Really tough ADO Stored Procedure Question. Please Help!!!
From: Scott (js_at_tds.net)
Date: 08/26/04
- Next message: Sam: "SQL server UNINSTALL problem"
- Previous message: Partha Mandayam: "Re: Problem with MS SQL install"
- In reply to: Ian: "Really tough ADO Stored Procedure Question. Please Help!!!"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 25 Aug 2004 22:15:33 -0400
Check to be sure you are connecting to the database via VB using the SAME
LOGON id as what you connected via Sql Analyzer. If your logon/permissions
are different, you could be hitting an error related to permissions and not
know it. The best way to find out if that is happening is to use the
Connection Objectss "Errors CVollection" in your VB. I didnt see any
mention of that, so here's a snippet of code to help you with that if you
need....
function Get_Child_Recs_Tools (msg, key)
'
' This proc gets a row count from the
' T_EQP_Tools table where fab_id = the key
' of fab_id of the T_EQP_Tools row that is
' about to be deleted.
' Any row count greater than 0 results in failure.
on error resume next
Get_Child_Recs_Tools = "NotOk"
tbl_primkey = cint(key)
sql_get = " Select Count(*) counter from TBLFAB where fabname = "
sql_get = sql_get & tbl_primkey
set cn = Server.CreateObject("ADODB.Connection")
cn.open str_Connect
set rs = Server.CreateObject("ADODB.Recordset")
rs.open sql_get, cn, adOpenForwardOnly
rs.movefirst
txt_rowcount = rs("counter")
if cn.Errors.Count > 0 then
Rtnmsg = ""
For each Error in cn.errors
Rtnmsg = Rtnmsg & "Error # : " & Error.Number & ", "
Rtnmsg = Rtnmsg & "Error Description : " & Error.Description
Next
cn.Errors.Clear
else
if txt_rowcount = 0 then
Get_Child_Recs_Tools = "Ok"
end if
end if
end function
P.S. I snipped this from an ASP application, so you'll have to cut out the
"Server." keyword from the ADO Object references and maybe make a few other
touchups.....
g'Luck
"Ian" <ian@NoWhere.com> wrote in message
news:OVPudKdhEHA.3476@tk2msftngp13.phx.gbl...
>
> Hi
>
> I was hopping some one can give me an explanation for this.
>
> Stored Procedure: (All this is in one stored procedure but this is the
> basis
> of what it does)
>
> Step 1: I have a stored procedure that selects some records from
> tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
> that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in
#Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
>
> Scenario:
>
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and
inserted
> back into tblTableOne.
>
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
>
> BUT.
>
> When I run the Stored procedure from my VB 6 application at first glance
it
> seems to work fine in the fact that there is no errors and when the data
is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
> is
> going wrong with Step 2 or 3.
>
> So I then added an output parameter to the SP to check that there was
data
> being imported form Excel and there were the correct no of records. and
> there are.
>
> So that has now narrowed it down to Step 3. Since it works fine from
Query
> Analyser and no errors are being thorn by the stored procedure. I have
also
> added Error handling just after the UPDATE statement and there is no
error
> in @@ERROR.
>
> I have been battling with this for about 12 hours now.
>
> Please any thing that I could try would be welcome.
>
> I have been trying different database option but no luck. Maybe I am
> setting
> them incorrectly or in the wrong place.
>
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
>
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still
no
> joy.
>
> I know it is a tough one.
>
> I am willing to email the SP and sample data it your up for a challenge.
>
> Thanks
>
> Ian
>
>
>
>
>
>
>
>
>
- Next message: Sam: "SQL server UNINSTALL problem"
- Previous message: Partha Mandayam: "Re: Problem with MS SQL install"
- In reply to: Ian: "Really tough ADO Stored Procedure Question. Please Help!!!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
