Do I need to Pause this?

[dvannoy]

I have a little problem. I am running a SQL2000 SP as follows

1. Creates a Temp Table
2. Inserts records from a count
3. Selects records from the temp table that match another table
4. drops the temp table

The problem is I cannot seem to tie the SP to an ADO recordset. I have many other SP's running so I know it's not my code. I tryed to Pause the app and no luck.

when running the SP from the server in Query Analyzer it takes about 20-30 seconds to complete.

maybe I am not doing the pause long correctly, I dont know.

Any Help would be appreciated
Thanks

 

[Transcend]

Could you explain a little more what it is you are trying to do?

Do you have some code that doesn't work?

You shouldn't need to pause anything if you are just manipulating and then loading some data ...

Transcend

 

[dvannoy]

OK..

SQL2000 SP Code Below.. Takes a good 30 seconds to run from the server..

CREATE PROCEDURE DISCREPANT_WIP_SUMMARY_TSR

@StartingDate DATETIME,
@EndingDate DATETIME,
@TSR Varchar(10)
AS

CREATE TABLE WIPS
(WIP INT,
GENNO INT)

INSERT INTO WIPS

SELECT COUNT(WIPNO) AS NO
,GENNO
FROM DRUMSAMPLES
WHERE TYPE <> 'CO' AND TYPE <> 'SC'

AND (NOT(DISCDATE IS NULL))
GROUP BY GENNO
HAVING COUNT(WIPNO) > 3

SELECT ASSIGNEDTO,TERRITORY,GENNO,GENERATOR
,DATERECEIVED,WIPNO,SAMPLENO,DRUMS
,APPROVAL,NEWPC,BILLINGDISP FROM
DRUMSAMPLES
WHERE DATERECEIVED BETWEEN @STARTINGDATE
AND @ENDINGDATE
AND ASSIGNEDTO = @TSR
AND GENNO IN(SELECT GENNO FROM WIPS)
ORDER BY DATERECEIVED,WIPNO

DROP TABLE WIPS
GO

VB Code Below...

Dim xlsApp As Excel.Application
Dim xlsWBook As Excel.Workbook
Dim xlsWSheet As Excel.Worksheet
Dim i, j As Integer
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

On Error Resume Next

If cboTsr.Text = "" Then
MsgBox "Please Select a TSR to Continue!", vbCritical, "Select TSR"
cboTsr.SetFocus
Exit Sub
End If

Screen.MousePointer = vbHourglass

With cmd

.ActiveConnection = strCn
.CommandType = adCmdStoredProc
.CommandText = "DISCREPANT_WIP_SUMMARY_TSR"
.Parameters.Append .CreateParameter("StartingDate", adDate, adParamInput)
.Parameters("StartingDate").Value = (txtStartRange.Text)
.Parameters.Append .CreateParameter("EndingDate", adDate, adParamInput)
.Parameters("EndingDate").Value = (txtEndingRange.Text)
.Parameters.Append .CreateParameter("TSR", adVarChar, adParamInput, 10)
.Parameters("TSR").Value = (cboTsr.Text)

End With

With rs

rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Open cmd

End With

If rs.RecordCount = 0 Then
MsgBox "There are no Records to Export at This Time!", vbCritical, "Exporting Error"
Screen.MousePointer = vbDefault
Exit Sub
End If

frmFormatMessage.Show

Set xlsApp = GetObject(, "Excel.Application")


If Err.Number <> 0 Then
Set xlsApp = New Excel.Application
Err.Clear
End If

Set xlsWBook = xlsApp.Workbooks.Add
Set xlsWSheet = xlsWBook.ActiveSheet

For j = 0 To rs.Fields.Count
xlsWSheet.Cells(2, j + 1) = rs.Fields(j).Name
Next j

Unload frmFormatMessage
Set frmFormatMessage = Nothing

rs.MoveFirst

For i = 1 To rs.RecordCount

For j = 0 To rs.Fields.Count
xlsWSheet.Cells(i + 2, j + 1) = rs.Fields(j).Value
Next j

If ProgressBar1.Value = 0 Then
ProgressBar1.Value = 1
End If

ProgressBar1.Value = ProgressBar1.Value + 1

rs.MoveNext

If ProgressBar1.Value = 100 Then
ProgressBar1.Value = 0
End If
Next i
rs.MoveFirst

For i = 1 To rs.Fields.Count
xlsWSheet.Columns(i).AutoFit
Next i
xlsWSheet.Range("A1").Select

xlsApp.Visible = True

Set xlsApp = Nothing
Set xlsWBook = Nothing
Set xlsWSheet = Nothing

Screen.MousePointer = vbDefault
ProgressBar1.Value = 0


I get a record count of "0" it will not return records back from the SP. Like I said, I have many other SP's running like this that work fine. The only difference is the above SP creates a temp table. I think the code is running faster then the SP is returning records so thats why I wanted to some how pause until the records get returned.

 

[dvannoy]

anyone?

 

[Transcend]

I don't think that is the case, your stored procedure is simply not returning any values to your recordset.

Instead of this

With cmd

.ActiveConnection = strCn
.CommandType = adCmdStoredProc
.CommandText = "DISCREPANT_WIP_SUMMARY_TSR"
.Parameters.Append .CreateParameter("StartingDate", adDate, adParamInput)
.Parameters("StartingDate").Value = (txtStartRange.Text)
.Parameters.Append .CreateParameter("EndingDate", adDate, adParamInput)
.Parameters("EndingDate").Value = (txtEndingRange.Text)
.Parameters.Append .CreateParameter("TSR", adVarChar, adParamInput, 10)
.Parameters("TSR").Value = (cboTsr.Text)

End With


Try this

With cmd

.ActiveConnection = strCn
.CommandType = adCmdStoredProc
.CommandText = "DISCREPANT_WIP_SUMMARY_TSR"
.Parameters("@StartingDate").Value = (txtStartRange.Text)
.Parameters("@EndingDate").Value = (txtEndingRange.Text)
.Parameters("TSR").Value = (cboTsr.Text)

End With

since you have set the active connection you shouldn't need to create the parameters, just set the values

Let me know how you go

Transcend

 

[SonOfEmidec1100]

Two possibilities
1) You have more than one select, try adding SET NOCOUNT ON/OFF (see below)

2)You are getting an error, such as a timeout use a proper error handler instead of ON ERROR RESUME NEXT, or at least comment it out to see if you can determine the error.

CREATE PROCEDURE DISCREPANT_WIP_SUMMARY_TSR
    
    @StartingDate DATETIME,
    @EndingDate DATETIME,
    @TSR Varchar(10)
AS

[blue]
SET NOCOUNT ON
[/blue]
CREATE TABLE WIPS
(WIP INT,
GENNO INT)

INSERT INTO WIPS

SELECT COUNT(WIPNO) AS NO
,GENNO
FROM DRUMSAMPLES
WHERE TYPE <> 'CO' AND TYPE <> 'SC'

AND (NOT(DISCDATE IS NULL))
GROUP BY GENNO
HAVING COUNT(WIPNO) > 3

[blue]
SET NOCOUNT OFF
[/blue]

SELECT ASSIGNEDTO,TERRITORY,GENNO,GENERATOR
,DATERECEIVED,WIPNO,SAMPLENO,DRUMS
,APPROVAL,NEWPC,BILLINGDISP  FROM
DRUMSAMPLES
WHERE DATERECEIVED BETWEEN @STARTINGDATE
AND @ENDINGDATE
AND ASSIGNEDTO = @TSR
AND GENNO IN(SELECT GENNO FROM WIPS)
ORDER BY DATERECEIVED,WIPNO

DROP TABLE WIPS
GO

 

[dvannoy]

OK, now that i set the NOCOUNT in my SP I get records returned back. I set a labels caption to the record count. but now I am getting an error when trying to export the data to excel.

"Cannot create active X object"

Set xlsApp = GetObject(, "Excel.Application")

I have many other SP's exporting in the same way so why would I be getting this error?

 

[SonOfEmidec1100]

Not too sure if this helps, the error implies there is no existing instance of the xl object.

Re MSDN

"Use the GetObject function when there is a current instance of the object or if you want to create the object with a file already loaded. If there is no current instance, and you don't want the object started with a file loaded, use the CreateObject function."

Set xlsApp = CreateObject("Excel.Application")

 

[johnwm]

If you're not sure if Excel will be running or not, try this:

Sub setmyXL()
    On Error Resume Next 'ignore errors
    Set xlsApp = GetObject(, "Excel.Application") 'look for a running copy of Excel
    If Err.Number <> 0 Then 'If Excel is not running then
       Set xlsApp = CreateObject("Excel.Application") 'run it
    End If
    Err.Clear   ' Clear Err object in case error occurred.
    On Error GoTo 0 'Resume normal error processing
    xlsApp.Workbooks.Open (App.Path & "\calcs.xls")
    xlsApp.Visible = True
End Sub

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts