Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Executing a Sproc from VBA

    I have a spreadsheet that runs a macro when saved. The macro saves data to a SQL Server database, and then calls a sproc in that database. The sproc takes the saved data, puts it in a temp table, performs a few twists and turns, and then saves the data to another table that is used by my report writer.

    The whole process has always worked fine from my PC. I recently passed it on to another user in my group, and the sproc won't work from his PC. I put a line in the sproc to test if it was actually running or not:

    INSERT INTO SOMETABLE(TESTCOLUM)
    SELECT 'RUNNING'

    and it does in fact run. However, the actual "guts" of the sproc don't run - but no error is returned. I've checked the permissions on the sproc and all of the tables that the sproc touches, and the person has the same permissions as I do.

    I'm trying to leave no stone unturned - but I've looked under all the stones I know about. Anybody have any ideas what's going on?
    Inspiration Through Fermentation

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    have you fired up the profiler? and used the SQL:stmtStarting and SQL:stmtCompleted events?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm going with Hard coded drive mapping in the VBA that aren't mapped on the other users machine

    "Tell him what's he's won Johhny!"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    it would also help if you post the vb code and sproc
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here's the VB Code:

    Code:
    Public DBCNXN As New ADODB.Connection
    
    Function SetDBCNXN()
    'build the db connection
      If DBCNXN.State = adStateClosed Then
        DBCNXN.Provider = "sqloledb.1"
        DBCNXN.Properties("Data Source").Value = "CORPSQL"
        DBCNXN.Properties("Initial Catalog").Value = "Prod_Plan"
        DBCNXN.Properties("Integrated Security").Value = "SSPI"
        DBCNXN.Open
        DBCNXN.CommandTimeout = 0
      End If
    End Function
    
    Function SaveData()
      SetDBCNXN
      :
      :
      'A WHOLE BUNCH OF CODE THAT WORKS JUST FINE
      :
      :
      'calling the sproc that I'm having a problem with
      DBCNXN.Execute "exec accSchedGran"
      Exit Function
    ERR_SAVE:
      MsgBox Error
      Stop
      Resume
    End Function
    And here's the sproc itself

    Code:
    CREATE PROCEDURE accSchedGran 
    AS 
    
    DECLARE @CNTID INTEGER
    DECLARE @MAXID INTEGER
    DECLARE @SCHEDID INTEGER
    DECLARE @SUCCESS BIT
    
    CREATE TABLE #SCHED (
    	SCHEDID	INTEGER		IDENTITY,
    	PROD_DAY 	DATETIME,
    	SHIFT		CHAR(4),
    	HRUNID	INTEGER NULL,
    	IRUNID		INTEGER NULL
    )
    
    INSERT INTO #SCHED (PROD_DAY, SHIFT)
    SELECT     MS.PROD_DAY, MS.SCHED_SHIFT
    FROM         MASTER_SCHEDULE MS
    WHERE     EXISTS
                              (SELECT     PROD_DAY, SCHED_SHIFT, PRODLINE = CASE WHEN MAX(HCNT) > MAX(ICNT) THEN 'H' ELSE 'I' END
                                FROM          (SELECT     PROD_DAY, SCHED_SHIFT, COUNT(AUTOINDEX) AS HCNT, 0 AS ICNT
                                                        FROM          MASTER_SCHEDULE
                                                        WHERE      (PROD_LINE = N'H')
                                                        GROUP BY PROD_DAY, SCHED_SHIFT
                                                        UNION ALL
                                                        SELECT     PROD_DAY, SCHED_SHIFT, 0, COUNT(AUTOINDEX) AS ICNT
                                                        FROM         MASTER_SCHEDULE
                                                        WHERE     (PROD_LINE = N'I')
                                                        GROUP BY PROD_DAY, SCHED_SHIFT) RS
                                WHERE      RS.PROD_DAY = MS.PROD_DAY AND RS.SCHED_SHIFT = MS.SCHED_SHIFT
                                GROUP BY PROD_DAY, SCHED_SHIFT
                                HAVING      MS.PROD_LINE = CASE WHEN MAX(HCNT) > MAX(ICNT) THEN 'H' ELSE 'I' END)
    ORDER BY MS.PROD_DAY, MS.SCHED_SHIFT
    
    SET @CNTID=1
    SET @MAXID = (SELECT MAX(SCHEDID) FROM #SCHED)
    SET @SCHEDID=(SELECT MIN(AUTOINDEX) FROM MASTER_SCHEDULE WHERE PROD_LINE='H')
    
    WHILE @CNTID<=@MAXID
    BEGIN
    	UPDATE #SCHED
    	SET HRUNID=CASE WHEN (RS.PROD_DAY = #SCHED.PROD_DAY AND RS.SCHED_SHIFT= #SCHED.SHIFT) THEN @SCHEDID ELSE NULL END,
    	@SUCCESS=CASE  WHEN (RS.PROD_DAY = #SCHED.PROD_DAY AND RS.SCHED_SHIFT= #SCHED.SHIFT) THEN -1 ELSE 0 END
    	FROM (SELECT PROD_DAY, SCHED_SHIFT FROM MASTER_SCHEDULE WHERE AUTOINDEX>=@SCHEDID) RS
    	WHERE #SCHED.PROD_DAY=RS.PROD_DAY AND #SCHED.SHIFT=RS.SCHED_SHIFT AND SCHEDID=@CNTID
    	
    	IF @SUCCESS<>0 
    		SET @SCHEDID=(SELECT MIN(AUTOINDEX) FROM MASTER_SCHEDULE WHERE PROD_LINE='H' AND AUTOINDEX>@SCHEDID)
    	SET @SUCCESS=0
    	SET @CNTID=@CNTID+1
    END	
    SET @CNTID=0
    SET @SCHEDID=(SELECT MIN(AUTOINDEX) FROM MASTER_SCHEDULE WHERE PROD_LINE='I')
    WHILE @CNTID<=@MAXID
    BEGIN
    	UPDATE #SCHED
    	SET IRUNID=CASE WHEN (RS.PROD_DAY = #SCHED.PROD_DAY AND RS.SCHED_SHIFT= #SCHED.SHIFT) THEN @SCHEDID ELSE NULL END,
    	@SUCCESS=CASE  WHEN (RS.PROD_DAY = #SCHED.PROD_DAY AND RS.SCHED_SHIFT= #SCHED.SHIFT) THEN -1 ELSE 0 END
    	FROM (SELECT PROD_DAY, SCHED_SHIFT FROM MASTER_SCHEDULE WHERE AUTOINDEX>=@SCHEDID) RS
    	WHERE #SCHED.PROD_DAY=RS.PROD_DAY AND #SCHED.SHIFT=RS.SCHED_SHIFT AND SCHEDID=@CNTID
    	IF @SUCCESS<>0 
    		SET @SCHEDID=(SELECT MIN(AUTOINDEX) FROM MASTER_SCHEDULE WHERE PROD_LINE='I' AND AUTOINDEX>@SCHEDID)
    	SET @SUCCESS=0
    	SET @CNTID=@CNTID+1
    END
    
    
    TRUNCATE TABLE GRANULATION_SCHED
    
    INSERT INTO GRANULATION_SCHED(PROD_DAY, SHIFT, BRUN, PREMIX, HTONS, PRODUCT, ITONS, HRUN, IRUN, XRUN, NOTES)	
    SELECT #SCHED.PROD_DAY, #SCHED.SHIFT, SB.BASE_RUN_NUMB, MS1.PRODUCT AS PREMIX, MS1.SCHED_TONS AS HTONS, MS2.PRODUCT AS PRODUCT, MS2.SCHED_TONS AS ITONS,
    	MS1****N_NUM AS HRUN, MS2****N_NUM AS IRUN, SB.FINES_RUN_NUMB AS XRUN, MS2.SCHED_NOTE
    FROM #SCHED LEFT JOIN MASTER_SCHEDULE MS1 ON #SCHED.HRUNID=MS1.AUTOINDEX
    	LEFT JOIN MASTER_SCHEDULE MS2 ON #SCHED.IRUNID=MS2.AUTOINDEX 
    	LEFT JOIN GRAN_SCHED_BASE SB ON MS1.AUTOINDEX=SB.SCHED_ID
    
    DROP TABLE #SCHED
    GO
    As I said earlier ... I know it's attempting to run the sproc, because I added the line that dumped test data into another table - and that part worked.
    It just seems to not want to execute the rest of the sproc.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'll rephrase the question:

    I think my problem is somehow related to the temp table that the sproc creates.

    When a temp table is created, does it go in the same database - or is it created in a different database (master?)? Would my user then need permissions in that database?

    Hunting season is coming, and I can't take time off until I get this figured out.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Temp tables are created in the...tempDB database

    They are scoped to the session so don't worry about that person's permissions there.

    I would prefix your tables with the schema\ owner e.g.:
    Code:
    INSERT INTO dbo.GRANULATION_SCHED
    I have seen a problem with similar symptoms to this once when a sproc was creating tables, but they were not being created in dbo **


    ** Note - not an application I built
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    OK. I hadn't thought about that, and I made those changes.

    I've also taken the temp table out of the equation - I created an "actual" table (called tmpsched), and then checked to see if the data in it was changing - which it is.

    So now my problem must be with the last section:

    Code:
    TRUNCATE TABLE dbo.GRANULATION_SCHED
    
    INSERT INTO dbo.GRANULATION_SCHED(PROD_DAY, SHIFT, BRUN, PREMIX, HTONS, PRODUCT, ITONS, HRUN, IRUN, XRUN, NOTES)	
    SELECT dbo.tmpsched.PROD_DAY, dbo.tmpsched.SHIFT, SB.BASE_RUN_NUMB, MS1.PRODUCT AS PREMIX, MS1.SCHED_TONS AS HTONS, MS2.PRODUCT AS PRODUCT, MS2.SCHED_TONS AS ITONS,
    	MS1****N_NUM AS HRUN, MS2****N_NUM AS IRUN, SB.FINES_RUN_NUMB AS XRUN, MS2.SCHED_NOTE
    FROM dbo.tmpsched LEFT JOIN dbo.MASTER_SCHEDULE MS1 ON dbo.tmpsched.HRUNID=MS1.AUTOINDEX
    	LEFT JOIN dbo.MASTER_SCHEDULE MS2 ON dbo.tmpsched.IRUNID=MS2.AUTOINDEX 
    	LEFT JOIN dbo.GRAN_SCHED_BASE SB ON MS1.AUTOINDEX=SB.SCHED_ID
    The TRUNCATE statement isn't even doing it's thing - nor is the insert statement.

    The user has full permissions on:
    tmpsched
    MASTER_SCHEDULE
    GRAN_SCHED_BASE
    GRANULATION_SCHED

    edit: Why is the forum putting "****" in my code where there should be a "****"
    Last edited by RedNeckGeek; 10-29-09 at 10:13.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sure it wasn't, Pootle. ;-)

    Anyway, I might suspect that somewhere along the line someone added ON ERROR RESUME NEXT on you, though I do see a sort of catch block there. Just to be sure, though, you can try to catch an error with the stored procedure with this block of code:
    Code:
    if (DBCNXN.errors.count = 0) then
        wscript.echo "Connected Successfully"
      else
        wscript.echo "Connection failed.  Error message: " & DBCNXN.errors.item(0).description
        wscript.echo "Error Number: " & DBCNXN.errors.item(0).nativeerror
      end if
    You may need to tinker a bit with it, as I am nto sure you can take the popups that I am tossing blithely about.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Really full permission, is reserved for SA. Truncate table, requires ownership of the table. if the user is not in the db_owner group, this is likely your problem. Try to substitute DELETE for TRUNCATE.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by RedNeckGeek View Post
    edit: Why is the forum putting "****" in my code where there should be a "****"
    I'll guess you typed something like ". ru" (no spaces). This gets changed to **** because we got lots of spammers with a ". ru" suffix on their links. Hopefully we can get rid of this rather crude "fix" now the software is updated.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by MCrowley View Post
    Try to substitute DELETE for TRUNCATE.
    WOOHOOO! That was it. I've been messing with this on and off for weeks. I can't believe
    it was that simple. That's not in my SQL Server book - I'm gonna take it home and put a few rounds through it and then burn it.

    and Pootle - I was trying to type ". ru"

    Thanks guys!
    Inspiration Through Fermentation

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Truncate and delete are very different operations - common interview question to point out the differences. Truncate is actually a DDL operation.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by RedNeckGeek
    Hunting season is coming, and I can't take time off until I get this figured out.
    Start packing, then ;-)

Posting Permissions

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