sql server 2005 error 800040e14 when calling a stored procedure from VB6

I am running a VB6 program that calls a stored procedure in SQL server 2005

When I run the stored procedure from SQL Management Studio it runs fine.
When I try to run it from within VB6 it processes the truncate statements and then aborts with the following message:

12/23/2011 18:41:08.08 ExecuteCmd,0,,Threw an Exception.gErrMsg=
[Microsoft][ODBC SQL Server Driver][SQL Server]P:\Pro50\ICDATA\(-2147217900, EXEC usp_UpdateItemPriceFile 'P:\Pro50\', 'US', '390')

The message code in hex is 800040e14

If I take the execute string

EXEC usp_UpdateItemPriceFile 'P:\Pro50\', 'US', '390'

from VB6 and paste it into SQL Management Studio it runs fine.


Here is the code fragment from VB6 which calls the stored procedure:

SQL = " EXEC usp_UpdateItemPriceFile '" & gSBTDataPath & "', '" & gCountryCode & "', '" & gUserID & "'"
Debug.Print SQL

ErCode = ExecuteCmd(SQL, gCN)

Here is the function ExecuteCmd:

Public Function ExecuteCmd(pCmdText As String, Conn As ADODB.Connection) As Long
   Dim adoCmd As New ADODB.Command
   Dim ErCode As Long
   '--------------------------------------------------------------------
   On Error GoTo Out
   If Conn Is Nothing Then '=>Fix it
     ErCode = OpenAdoConnectionIfClosed(Conn, gDSN, cDefCommandTimeout)
   End If
   Set adoCmd.ActiveConnection = Conn
   adoCmd.CommandText = pCmdText
   adoCmd.CommandType = adCmdText
   'added on 6/11/01 due to error saying that timer time out
   'default setting is 30 . BT
   adoCmd.CommandTimeOut = 60
   adoCmd.Execute , , adExecuteNoRecords
   Set adoCmd.ActiveConnection = Nothing
   gErrMsg = ""
   Exit Function
Out:
   ExecuteCmd = -100
'   ErrMsg = "Error=" & Err.Number & "(" & Err.Description & ")"
   gErrMsg = Err.Description & "(" & Err.Number & "," & pCmdText & ")"
   Dim errnumber As Long
   errnumber = Err.Number
   
   If UCase$(pCmdText) = "DROP TABLE TEMPSENT" Or _
      UCase$(pCmdText) = "DROP TABLE TEMPSCHEDULED" Then '=>No need to debug.print
   Else '=>Debug.Print
      ErCode = LogError(Err, "modzzADO", "ExecuteCmd", 0, "Threw an Exception.gErrMsg=" & gErrMsg)
   End If
   ErCode = -100
'   If errnumber = -2147217900 Then
'     ExecuteCmd = -400 'duplicate
'   End If
   Exit Function
   Resume '=>For Debugging
End Function

The stored procedure reads Visual FoxPro files and loads SQL server tables.
The code will truncate the tables which upon success enters a row in the tblSBTupdateLog table. Then it stops.
And here is the stored procedure:

USE [BELL_USSQL]
GO
/****** Object:  StoredProcedure [dbo].[usp_UpdateItemPriceFile]    Script Date: 12/23/2011 17:44:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[usp_UpdateItemPriceFile] (@InputPath varchar(200) , @Country varchar(2), @User varchar(6)
)
AS


--Declare @Country varchar(2), @User varchar(6)
--set @Country = 'US'
--set @User = '390'

Declare @tblName varchar(15), @Cono char(2), @Description varchar (200), @Abbrev varchar(200), @SQL varchar(200)
Declare @Path varchar(200)
Declare @Delta datetime
DECLARE @myERROR int -- Local @@ERROR
DECLARE @myRowCount int -- Local @@ROWCOUNT
set @Delta = getdate()
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), 'BEGIN UpdatePrices', 0, 0, 0 , @Country, '00', @User, 'UpdatePrices')

truncate table tblPricingTemp01 --delete from tblPricingTemp01
truncate table tblPricingTemp   --delete from tblPricingTemp
truncate table ICPRICEALL       --delete from ICPRICEALL
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(),'Delete Price table data', 0, 0, datediff(s,@delta,getdate()), @Country, '00', @User, 'UpdatePrices' )

/*
Input to procedure should be tblcompany, Path
Loop through tblcompany to load ICPRICE from all companies for the database
For combined databases, include country in tblCompany and make select based upon company.
*/


--Declare @InputPath varchar(200)
--set @InputPath = 'P:\PRO50\'

set @Path = @InputPath
if Right(@path,1) <> '\' set @path = @path + '\'
set @Path = @Path + 'ICDATA\'
print @Path

Declare ConoCursor CURSOR FOR
Select cono, description, abbrev from tblCompany  
OPEN ConoCursor
--Get First Record
FETCH NEXT from ConoCursor
INTO @cono, @description, @abbrev
--LOOP THROUGH ALL COMPANIES and populate tables
print 'company '+ @cono
WHILE @@Fetch_Status = 0
BEGIN
      --======= Populate ICPRICEALL from VFP tables ICPRICxx
      --Company 01

      set @tblName = 'ICPriceALL'
--      set @Path = 'C:\BELL_VB\PRO50\ICData'
      --set @cono = '01'
      set @SQL='select "' + @cono + '" as cono, item, popt, custno, sellpr2, sellum, sellpr1 from icpric' + @cono + ' where psize = "DEF"  and sellpr1 > ' + '0' + ''
print @SQL
      exec( 'insert  into ' + @tblName + ' (cono, item, popt, custno, sellpr2, sellum, sellpr1) select T.* from openrowset(''MSDASQL'',
      ''Driver=Microsoft Visual FoxPro Driver;
      SourceDB=' + @Path + ';
      SourceType=DBF'', ''' + @SQL + ''')T' )
      SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT

    insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), @tblName, @myRowCOUNT, @myERROR, datediff(s,@delta,getdate()), @Country, @Cono, @User, 'UpdatePrices' )

      --Get Next COMPANY Record
      FETCH NEXT from ConoCursor
      INTO @cono, @description, @abbrev
END
close ConoCursor
deallocate ConoCursor



--============Create _UniqueConoItemCustnoMaxDate from tblCP
--drop table #tblUCIMD
select  tblCP.cono, tblCP.item, tblCP.custno, Max(tblCP.invdte) AS MaxDate into #tblUCIMD  
--SELECT DISTINCT tblCP.cono, tblCP.item, tblCP.custno, Max(tblCP.invdte) AS MaxDate
FROM tblCP
WHERE tblcp.price>0 and tblcp.qtyshp >0 GROUP BY tblCP.cono, tblCP.item, tblCP.custno
--select * from #tblUCIMD order by cono, item, custno
SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), '#tblUCIMD', @myRowCOUNT, @myERROR, datediff(s,@delta,getdate()), @Country, @Cono, @User, 'UpdatePrices' )

--============Create tblPricingTemp01
--select * from #tblPricingTemp01
--Delete from tblpricingTemp01
Insert INTO  tblPricingTemp01
Select DISTINCT T.cono, T.item, T.custno, T.MaxDate, C.price as Price, C.umeasur
--INTO  #tblPricingTemp01A
--FROM
--SELECT DISTINCT #TBLUCIMD.cono, #TBLUCIMD.item,
--#TBLUCIMD.custno, #TBLUCIMD.MaxDate, tblCP.price, tblCP.umeasur
FROM #TBLUCIMD T INNER JOIN tblCP  C ON (T.item = C.item)
AND (T.custno = C.custno)
AND (T.cono = C.cono)
AND (T.MaxDate = C.invdte)
WHERE C.price>0  and C.qtyshp>0 and C.invno
Not Like 'CM%' And C.invno Not Like 'DM%' and C.item <>'EQUIPMENT' and C.item <> 'NONSTOCK'
order by t.cono asc, t.item asc, t.custno asc,  c.price desc
SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), 'tblPricingTemp01', @myRowCOUNT, @myERROR, datediff(s,@delta,getdate()), @Country, @Cono, @User, 'UpdatePrices' )

--select * from tblPricingTemp01 order by cono, item, custno
--=========================Create tblPricingTemp

--Delete table #tblpricingtemp
--SELECT *  from #tblpricingtemp
/*
INSERT INTO tblPricingtemp (cono, item, custno, SP1, SUom, MSP, source)
SELECT  TA.cono, TA.item, TA.custno, TA.price AS SP1, TA.umeasur AS Suom, ' ' AS MSP, 'U' AS Source
--INTO #tblpricingTemp1C
FROM tblpricingtemp01 TA
allows duplicates for cono/item/custno violating PK constraint for TblPricingTemp
*/

--Selects Maximum price for insertion into tblPricingTemp
;
with A as
( select t.cono, t.item, t.custno, t.price AS SP1, t.umeasur AS Suom, ' ' AS MSP, 'U' AS Source,
row_number() over( partition by t.cono, t.item, t.custno  order by t.cono, t.item, t.custno, t.price desc) as num
from tblpricingtemp01 T )
INSERT INTO tblPricingtemp (cono, item, custno, SP1, SUom, MSP, Source)
select cono, item, custno, SP1, Suom, MSP, Source from A
where num = 1
SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), 'tblPricingTemp', @myRowCOUNT, @myERROR, datediff(s,@delta,getdate()), @Country, @Cono, @User, 'UpdatePrices' )

--select * from tblpricingtemp01 order by cono, item, custno

--==================Update tblPricingTemp
--Overlay pricing from ICPRICEALL where source = I and  cono/item match
UPDATE  tblPricingTemp  
SET tblPricingTemp.SP1 = icpriceall.sellpr1, tblPricingTemp.SUoM = icpriceall.sellum,
tblPricingTemp.MSP =
(Select Case
  WHEN icpriceall.sellpr2=0 Then ''
  ELSE '*'
END),
tblPricingTemp.source = 'I'
FROM ICPRICEALL
WHERE ICPriceAll.item=tblPricingTemp.item AND ICPriceAll.cono=tblPricingTemp.cono  
AND ICPriceAll.popt='I'
SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), 'tblPricingTemp Update 1', @myRowCOUNT, @myERROR, datediff(s,@delta,getdate()), @Country, @Cono, @User, 'UpdatePrices' )

--==================Update tblPricingTemp cannot combine as this update query has custno in WHERE clause
--Overlay pricing from ICPRICEALL where source = C and cono/item/custno match
UPDATE tblPricingTemp  
SET tblPricingTemp.SP1 = icpriceall.sellpr1, tblPricingTemp.SUoM = icpriceall.sellum,
tblPricingTemp.MSP =
( Select Case
  WHEN icpriceall.sellpr2=0 Then ''
  ELSE '*'
END),
tblPricingTemp.source = 'C'
From ICPRICEALL
WHERE ICPriceAll.item=tblPricingTemp.item AND ICPriceAll.cono=tblPricingTemp.cono  
AND ICPriceAll.custno=tblPricingTemp.custno
AND ICPriceAll.popt='C'
SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), 'tblPricingTemp Update 2', @myRowCOUNT, @myERROR, datediff(s,@delta,getdate()), @Country, @Cono, @User, 'UpdatePrices' )

insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), 'END UpdatePrices', 0, 0, datediff(s,@delta,getdate()), @Country, @Cono, @User, 'UpdatePrices' )


jup9140Asked:
Who is Participating?
 
jup9140Connect With a Mentor Author Commented:
Problem was with permissions and use of TEMP files folder.
Changed the SQL service account environment variable to point to C:\Temp instead of the user's TEMP folder under Documents and Settings.
SQL server wanted to use its private TEMP folder but calls to it from users on other computers were looking in C:\TEMP for information sqlserver put in folder and could not get access.
Thanks to all who provided input to this problem especially kbirecki.
Jeff
0
 
kbireckiCommented:
jup9140,
I'd be happy to try to help.  That is a very thorough question, but I get the sense you are thinking the problem may be in the coding of the queries, etc.  I think since you verified the stored proc actually runs successfully when executed in Mgmt Studio, the sp and everything it does is fine.  I think the problem is in the *calling* of the sp from the VB6 side.  You may be inherently thinking of it this way already, but I adopted a theme for solving problems I call "plain vanilla" or "binary".  It is basically a process of repeatedly asking yourself the question "Is problem on _this_ side of the tracks or _that_ side of the tracks?" until you narrow down where the exact cause is.  This approach is very effective, and the trick in this concept is knowing where to create the boundaries to be tested.  You have to think analytically to find reasonable and simple points to separate one side from another.  In a way, you've already started this, even if you don't know it, which is a good thing.  Using this idea:

You verified the stored procedure and everything it performs *does* in fact work, so all the code there should not be in question.  The problem looks to be more in the method of the *calling* the sp.  That's the first binary separation: sp works when called from Mgmt Studio, but not from VB6.  Until something changes that assertion, this tells you which direction to start looking.

Create an ultra simple-do nothing sp, call that instead of your full blown process and see if it executes or fails with the same error.  Your test proc should be as identical as possible to the sp in question except that it won't have anything actually in it except maybe something like "Select top 1 * from AnyTable" and then end.  This do-nothing sp should be in the same SQL DB as the one in question, and have the same permissions, so the best way to do that might be to copy the original sp in Mgmt Studio to include all of it's assigned permissions and then just clip out all the executing SQL code leaving a something like "Select top 1 * from AnyTable".  This dummy proc can even have the parameters just like the real one, it just won't use them.  Remember, the purpose of plain vanilla is to change only one thing at a time.  If you do what I just described in this paragraph, I expect you *will* repeat your problem, because you already confirmed that the sp executed from Mgmt Studio works, this just reaffirms that assumption.  Confirming a negative is just as valuable as confirming a positive.

Now for something that might solve the problem.  The next thing to try would be to put a fully qualified reference to the sp being called.  In your vb6 app, change the statement:

SQL = " EXEC usp_UpdateItemPriceFile '" & gSBTDataPath & "', '" & gCountryCode & "', '" & gUserID & "'"

Open in new window


...to...

SQL = " EXEC [SQL_SERVER].dbo.usp_UpdateItemPriceFile '" & gSBTDataPath & "', '" & gCountryCode & "', '" & gUserID & "'"

Open in new window


...where [SQL_SERVER] is the name of your SQL server, including the square brackets (just in case).

Now for the "binary" aspect.

IF SUCCESS: Using a fully qualified reference is the solution.  This may not be ideal, so I think there is a setting on the SQL server to change whether fully qualified references are always required or not, but I haven't found it yet in a quick internet search.

IF FAILED: That is obviously not the problem, but it is still good to leave that in place for now.  On to the next idea.

Next, examine the connection string.  Use this article to create a udl file and get the connection string from it.  Try that as you connection string and test your process again with the ultra simple-do nothing sp.

IF SUCCESS: That should be it.

IF FAILED: That is obviously not the problem.  Let us know where this leaves you.

Good luck!
0
 
jup9140Author Commented:
Thanks for your thoughtful response. I have tried and successfully executed a simple stored procedure that takes one parameter and adds a row to a table using the parameter.
Also the stored procedure listed above actually starts to run. The truncate table section of the stored procedure executes because an entry is made to the log table. Then the stored procedure ends with the error message returned as shown to the VB6 program: [Microsoft][ODBC SQL Server Driver][SQL Server]P:\Pro50\ICDATA\(-2147217900, EXEC usp_UpdateItemPriceFile 'P:\Pro50\', 'US', '390').
There are two other lengthy stored procedures that both do not run from VB6 but do run from Management Studio and also on my test platform which is SQL 2000. The rest of the VB6 program makes ADO calls to the database using pass through SQL and they function fine.
I understand your approach and try to change one variable at a time so that I can isolate the cause of the problem.  
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
kbireckiCommented:
Also, if the problem is sp oriented, you could add debuging statements throughout the code at strategic points to see that your assumptions are met.  For instance, create a table like tblDebug and place statements like "Insert INTO tblDebug Value1, Value2, etc" for whatever makes sense to check, and then compare one successfull execution use Mgmt Studio to a  failed execution from VB6 and there should be someplace where you can find a difference and thus the source of the problem.
0
 
jup9140Author Commented:
Thanks again.
I already have statements in the stored procedure (insert into tblsbtupdatelog ) that let me know the outcome of every step in the procedure. That is how I know that the procedure stops after the truncate table statements. I thought that maybe the database was too large so I shrunk the database and log files but that had no effect. I have looked up the error but cannot find any relevant articles.
0
 
kbireckiCommented:
Do the tables being truncated have triggers?
0
 
kbireckiCommented:
...or constraints?
0
 
jup9140Author Commented:
No triggers.
0
 
kbireckiCommented:
If you've narrowed down to the truncate stmt, that is good.  Did you check permissions for the user configured in the Connection string that they can execute the truncate stmt (See this page.)?  Can you log into the Mgmt Studio using that user and just execute one of the truncate statements because I believe that could be a permission issue.  Try switching back and forth between users that work and don't work executing that stmt and see if that helps indicate anything.

For instance, on this page, you will see some restrictions for truncating tables.  Snippet:
Restrictions

You cannot use TRUNCATE TABLE on tables that:

Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

Participate in an indexed view.

Are published by using transactional replication or merge replication.

For tables with one or more of these characteristics, use the DELETE statement instead.

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).

Try changing the first truncate statement that fails to a totally new table unrelated to the process you are working out, this way you should be able to be confident that there are no conflicts with these restrictions.
0
 
jup9140Author Commented:
Tables that are truncated have no constraints or linkage to any other tables. I can just delete all from these tables or make them temporary tables. However the truncate statements complete. The procedure fails on the next statement that reads data from foxpro.
 
0
 
kbireckiCommented:
OK, so the truncate statements themselves work, right?  I misunderstood that, sorry.  So let's narrow down some more.  Let's take the procedure usp_UpdateItemPriceFile and put line numbers on it to easily refer to it:


ALTER Procedure [dbo].[usp_UpdateItemPriceFile] (@InputPath varchar(200) , @Country varchar(2), @User varchar(6)
)
AS


--Declare @Country varchar(2), @User varchar(6)
--set @Country = 'US'
--set @User = '390'

Declare @tblName varchar(15), @Cono char(2), @Description varchar (200), @Abbrev varchar(200), @SQL varchar(200)
Declare @Path varchar(200)
Declare @Delta datetime
DECLARE @myERROR int -- Local @@ERROR
DECLARE @myRowCount int -- Local @@ROWCOUNT
set @Delta = getdate()
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), 'BEGIN UpdatePrices', 0, 0, 0 , @Country, '00', @User, 'UpdatePrices')

truncate table tblPricingTemp01 --delete from tblPricingTemp01
truncate table tblPricingTemp   --delete from tblPricingTemp
truncate table ICPRICEALL       --delete from ICPRICEALL
insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(),'Delete Price table data', 0, 0, datediff(s,@delta,getdate()), @Country, '00', @User, 'UpdatePrices' )

/*
Input to procedure should be tblcompany, Path
Loop through tblcompany to load ICPRICE from all companies for the database
For combined databases, include country in tblCompany and make select based upon company.
*/


--Declare @InputPath varchar(200)
--set @InputPath = 'P:\PRO50\'

set @Path = @InputPath
if Right(@path,1) <> '\' set @path = @path + '\'
set @Path = @Path + 'ICDATA\'
print @Path

Declare ConoCursor CURSOR FOR
Select cono, description, abbrev from tblCompany  
OPEN ConoCursor
--Get First Record
FETCH NEXT from ConoCursor
INTO @cono, @description, @abbrev
--LOOP THROUGH ALL COMPANIES and populate tables
print 'company '+ @cono
WHILE @@Fetch_Status = 0
BEGIN
      --======= Populate ICPRICEALL from VFP tables ICPRICxx
      --Company 01

      set @tblName = 'ICPriceALL'
--      set @Path = 'C:\BELL_VB\PRO50\ICData'
      --set @cono = '01'
      set @SQL='select "' + @cono + '" as cono, item, popt, custno, sellpr2, sellum, sellpr1 from icpric' + @cono + ' where psize = "DEF"  and sellpr1 > ' + '0' + ''
print @SQL
      exec( 'insert  into ' + @tblName + ' (cono, item, popt, custno, sellpr2, sellum, sellpr1) select T.* from openrowset(''MSDASQL'',
      ''Driver=Microsoft Visual FoxPro Driver;
      SourceDB=' + @Path + ';
      SourceType=DBF'', ''' + @SQL + ''')T' )
      SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT

    insert into tblSBTUpdateLog (DateChanged, TableAffected, RowsAffected, Err, Delta, Country, Cono, UserID, UserProc) values( getdate(), @tblName, @myRowCOUNT, @myERROR, datediff(s,@delta,getdate()), @Country, @Cono, @User, 'UpdatePrices' )

      --Get Next COMPANY Record
      FETCH NEXT from ConoCursor
      INTO @cono, @description, @abbrev
END
close ConoCursor
deallocate ConoCursor

Open in new window



Lines 18-21 work fine, right?  The problem begins at or after line 33 correct?  I still think that if you can execute your usp with the exact same parameters in Mgmt Studio successfully, but it fails when executed from VB6 that the problem is permission related somewhere, we just have to find it.  For now, can we use this line numbered section to help identify where the core problem occurs.

The next action in the code deals with a path, so I suppose we could check that the path has what is expected with something like a DOS "dir" command.  I found an article that demonstrates how to make a simple stored procedure to view the contents of a directory in a table, but it needed a little tweaking.  Let's use this to see the contents of the folder you are dealing with on line 36 to verify it has the file(s) expected.  You could add a line like the following after line 36 and check the resulting table:

EXEC uspDir @Path

Open in new window


Here is my slightly modified version of the proc to do this based on the article cited: (Note, I use SQL Server 2005, and I changed the proc name to "uspDir" because I prefix all user defined procs with "usp", but you can obviously do what works for you.)

CREATE TABLE [dbo].[Directory_Contents_Stage] (
   [dir]  varchar(255)
 , [dir_output]  varchar(255)
)
GO

CREATE TABLE [dbo].[Directory_Contents] (
   [dir]  varchar(255)
 , [Create_Time] datetime
 , [File_Size]  int
 , [File_Name]  varchar(255)
 , [Struct_Type] char(9)
)
GO

CREATE PROC uspDir(@path varchar(2000), @Supress int = 0)
AS

SET NOCOUNT ON
TRUNCATE TABLE Directory_Contents_Stage

    DECLARE @cmd varchar(4000)
     SELECT @cmd = 'Dir "' + @path + '"'

INSERT INTO Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the file specified.')
    BEGIN
         PRINT 'The system cannot find the file specified.'
         GOTO Dir_Error
    END

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'File Not Found')
    BEGIN
         PRINT 'File Not Found'
         GOTO Dir_Error
    END         

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the path specified.')
    BEGIN
         PRINT 'The system cannot find the path specified.'
         GOTO Dir_Error
    END

     UPDATE Directory_Contents_Stage SET [dir] = @path

DELETE FROM Directory_Contents WHERE [dir] = @path

INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , CONVERT(int,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size] 
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
     , [dir]
     , 'FILE'
      FROM  Directory_Contents_Stage
     WHERE  SUBSTRING(dir_output,1,1) <> ' '
       AND (SUBSTRING(dir_output,1,1) <> ' ' 
       AND  SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62))

INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) As [File_Name]
     , [dir]
     , CHAR(60)+'Dir'+CHAR(62) AS  [Struct_Type]
       FROM  Directory_Contents_Stage 
      WHERE  SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62)

IF @Supress = 0

  SELECT [dir], Create_Time, File_Size, [File_Name], [Struct_Type] 
    FROM Directory_Contents
   WHERE [dir] = @path
ORDER BY [Struct_Type] DESC, [Create_Time] DESC

/*MOD: 12-25-11/KB - Disabled this "GO" stmt to allow the subsequent labels to be accessible, otherwise the compiler can't see them.*/
--GO

SET NOCOUNT OFF

Dir_Exit:
SET NOCOUNT OFF
RETURN

Dir_Error:
-- Add Error Handling
GOTO Dir_Exit
GO


/*MOD: 12-25-11/KB - Disabled from here on to not automatically drop the proc and tables right after they get created.*/

--EXEC uspDir 'C:\*.*'
--GO

--DROP PROC uspDir
--GO

--DROP TABLE Directory_Contents, Directory_Contents_Stage
--GO

Open in new window


Now, can you break lines 54-59 out into multiple select statements to pull data for display before actually inserting into the next table just to see what data is moving around and that it matches what you expect?  This would be along the lines of finding the "plain vanilla" version to identify where the root problem is occurring.  I don't think it is a data issue per se, but doing this may bring something to light.  Could the problem could be NTFS permissions related to the SQL user account calling the proc from VB6?  Maybe.  But the way to test that is to log into Mgmt Studio with the same credentials as are being used in the VB6 app and then execute the usp.

Now, I do have some version of VFP at the office, but I don't have it installed on my system and I won't be able to get back there until the middle of next week to try and install it to create similar VFP tables and reproduce your situation.  If you have a sample VFP file you could upload for me to try, I'll see what I can reproduce.  Even better would be a generic copy of your tblCompany table with a few generic records and the corresponding VFP files that I could try to run the same thing you are doing.  That might make it easier for me to see what is going on.  Ideally, what we should try to do is create a mini replicated environment  to simulate the section of the proc that is failing.
0
 
jup9140Author Commented:
Since my last post. I have extracted a snipped of the code that loads the itemprice file and changed it so that it uses a linked server.
USE [BELL_USSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure JPLS_Test
as


Declare @cono varchar(2), @SQL varchar(200),@OpenQuery varchar(200)
set @cono ='01'
set @SQL = 'Select "' + @cono + '" as cono, item, popt, custno, sellpr2, sellum, sellpr1 from ICPRIC' + @cono + ' where psize = "DEF"  and sellpr1 > 0 '')'
print @SQL
--insert into icpriceall (cono, item, popt, custno, sellpr2, sellum, sellpr1)
set @OpenQuery = 'insert into icpriceall (cono, item, popt, custno, sellpr2, sellum, sellpr1)   select * from openquery(JPLS_TEST,'''
exec (@openquery+@SQL)

Then I call it from VB6
SQL = "exec JPLS_TEST"
ErCode = ExecuteCmd(SQL, gCN)

Public Function ExecuteCmd(pCmdText As String, Conn As ADODB.Connection) As Long
   Dim adoCmd As New ADODB.Command
   Dim ErCode As Long
   '--------------------------------------------------------------------
   On Error GoTo Out
   If Conn Is Nothing Then '=>Fix it
     ErCode = OpenAdoConnectionIfClosed(Conn, gDSN, cDefCommandTimeout)
   End If
   Set adoCmd.ActiveConnection = Conn
   adoCmd.CommandText = pCmdText
   adoCmd.CommandType = adCmdText
   'added on 6/11/01 due to error saying that timer time out
   'default setting is 30 . BT
   adoCmd.CommandTimeOut = 60
   adoCmd.Execute , , adExecuteNoRecords
   Set adoCmd.ActiveConnection = Nothing
   gErrMsg = ""
   Exit Function


And get this message:

 in ExecuteCmd In Threw an Exception.gErrMsg=[Microsoft][ODBC SQL Server Driver][SQL Server]Select "01" as cono, item, popt, custno, sellpr2, sellum, sellpr1 from ICPRIC01 where psize = "DEF"  and sellpr1 > 0 ')(-2147217900,exec JPLS_TEST)


I believe it has something to do with permissions. There are three computers involved: the VB6 computer, the SQL computer, and  the FoxPro computer.
I believe I have administrative rights on all three computers.

0
 
kbireckiCommented:
I too have been thinking it is a permissions problem.  So using the "binary" methodology, we need to verify this by isolating and testing the various stages of permissions incrementally that are at work throughout a minimal transaction that is failing.  Let me give it a whirl to identify a thread of stages for each permission in use and you correct me where you think I might have missed something, and then let's see what we can come up with to test each stage independently to see where the problem may be.

I'm thinking the big picture of a simplified test scenario might be:

VB6 app -> SQL client (SQL user acct) -> SQL Server -> VFP Driver (VFP ODBC acct) -> VFP Tables

Permissions to be tested
1. VB6 app has an ADODB connection string to define permissions to connect to SQL server using a specific SQL user acct.
2. The SQL server probably has a defined VFP ODBC acct, right?  

How is the VFP Driver configured on the SQL Server and accessing the VFP Tables?  Are the VFP tables on a separate physical computer accessed over a network share or local on the SQL server?  Can you put a copy of some sample VFP files on the local SQL server, and create a link from SQL Server to those local copies of the files to keep it all local and avoid network-related issues.  And make sure the acct used on the VFP driver has full permissions, even if it is not what you want security-wise in the end, this will help eliminate this as a possibility of security limitations.  And if the VFP ODBC driver uses a local Windows account, make sure that acct has full NTFS permissions on the VFP files folder.

Also, can you use a SQL administrator account in the VB6 app connection string to eliminate any possible SQL Server user acct limitations.

I think those things might help identify if/where permissions may still be an issue.  I don't think there would be any permission-based aspects left, right?
0
 
jup9140Author Commented:
I copied the foxpro files to the sq server machine. Then I set up an ODBC connection to point to the local data. I ran the program from VB and it WORKED!

I also set up a linked server and changed the code to openquery referencing the linked server. This did not work nor did it work when I added login information to the linked server.

I spoke with the network support people for the client and they ran a trace. It showed ACCESS DENIED messages when SQL tried to get data from FOXPRO on the other machine.
I am at a total loss for what to do to make this work. I know there has to be an answer but right now I have run out of ideas.
0
 
kbireckiCommented:
OK, I've been away, but I wanted to post again to keep the thread alive so the automated e-e stuff doesn't start a close process.  I'm going to review this again and feedback tomorrow after I've had a chance to refresh my memory.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.