Link to home
Create AccountLog in
Avatar of WTsuk
WTsuk

asked on

SQL Store Procedure return 0 row when multi user on Application

On same application;
Only ONE user run attached SP1  - this works fine and  return Job Number list, but when USER2 is running on PC2, Logon2, Screen2 and SP2 and same time USER1 is running PC1, Logon1, Screen1, and SP1 then - this don't return job number list.

 These two are using same SQLtable (table have Job Number and NAME).  SP2 is replace by Job number, SP1 is try to get Job Number list for report.  This SQLtable have 5 million records by Jobnumbers and Name.


SP1 = "GetAllSavedJobs"

     Logic from "objSqAd.Fill(dtJobs)" skip to "Catch ex As Exception"


vb.net CODE

Private Function getSavedJobs()
        Dim cmdSavedJobs As New SqlCommand
        Dim objSqAd As New SqlDataAdapter
        Try
            con = GetConnection()
            dtJobs.Columns.Add("Select", Type.GetType("System.Boolean"))
            dtJobs.Columns("Select").DefaultValue = False
            With cmdSavedJobs
                .Connection = con
                .CommandType = CommandType.StoredProcedure
                .CommandText = "GetAllSavedJobs"
            End With
            objSqAd.SelectCommand = cmdSavedJobs
            objSqAd.Fill(dtJobs)  '''-------------------------------stop here and skip to catch'''

            Dim d(0) As DataColumn
            d(0) = dtJobs.Columns(1)

            dtJobs.PrimaryKey = d
            Dim dr1 As DataRow
            If Not objMergeJob.dtSelJobs Is Nothing Then
                For Each dr As DataRow In objMergeJob.dtSelJobs.Rows
                    dr1 = dtJobs.Rows.Find(dr(1))
                    dr1(0) = True
                Next
                dtArr = dtJobs.Select("Select = True")
            End If
            'objMergeJob.dtSelJobs.Rows.Find
            intRowCount = dtJobs.Rows.Count
            objtableStyle.MappingName = dtJobs.TableName
            fnFormatDataGrid()
            objtableStyle.AllowSorting = False
            dbgJob.TableStyles.Add(objtableStyle)
            dbgJob.DataSource = dtJobs
            dbgJob.Focus()
        Catch ex As Exception
            MessageBox.Show(dtJobs.Rows.Count) '' suk 20121112 wt
            Console.WriteLine(ex.ToString)

            ''MessageBox.Show(ex.ToString)   '' suk 20121112
        End Try
    End Function


SQL STORE PROCEDURE SP1

CREATE PROC [dbo].[GetAllSavedJobs]
AS

PRINT 'TEST'

      SELECT       plm.jobcode [Job No],si.seminartitle [Seminar Title]
      FROM (SELECT DISTINCT pm.jobNo jobcode FROM inr_mailinglist pm) plm
      INNER JOIN (SELECT js.jobno, sm.seminartitle
            FROM inr_seminarmaster sm
            INNER JOIN  inr_jobshedule js
            ON sm.seminarid =js.seminarid) si
      ON plm.jobcode =si.jobno
      ORDER BY plm.jobcode

print 'Suk Test'
RETURN
GO
Avatar of vasto
vasto
Flag of United States of America image

Check the SQLServer login for User2 - you can copy the permission from the login for User1
Avatar of WTsuk
WTsuk

ASKER

How to check SQLServer login? How to copy the permission from the login?

If only one user (can be USER1 or USER2 or USER3..)  log in then SP1 return job number list.
>objSqAd.Fill(dtJobs)  '''-------------------------------stop here and skip to catch'''

What is the error that you get in catch?
Avatar of WTsuk

ASKER

I attached system error as PDF format.  I really appreciate it.
systemMessage1.pdf
So its a timeout issue. How long does the SP take to execute if you run it through sql management studio?

You can increase command timeout using

objSqAd.SelectCommand.CommandTimeout = 30000

before calling .Fill

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.80).aspx
Avatar of WTsuk

ASKER

SP2 could take 30 min to 5 hours and SP1 could tgake 5 to 15 mins.
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
30 min to 5 hours. .Its too much, better you tune your code/ create required indexes
Avatar of WTsuk

ASKER

Thank you very much.  You saved my job.
Avatar of WTsuk

ASKER

This application processing time is one of serious issue.  I posted as another question.