Remote view prompting for parameters on CREATE SQL VIEW

Remote view prompting for parameters on CREATE SQL VIEW

Post by Mark A. Nadi » Sun, 05 Dec 1999 04:00:00



If I try to execute the following, I am prompted for the values of
nMainSeg, nMainSt and nMainLen:

CREATE SQL VIEW "V_TEST" REMOTE CONNECT "conDataset" SHARE AS ;
select cDivCode ;
 FROM gldiv,glcharm  ;
 WHERE Gldiv.esegno = ?m.nMainSeg ;
  AND GLCharM.ePType =  1 ;
  AND cDivCode = UPPER( SUBSTRING( GLCharM.cAcNo, ?m.nMainSt,
?m.nMainLen)) ;
  AND  GLCharM.eAcType IN (  1, 2, 3) ;
  GROUP  BY cDivCode

If I remove the GROUP BY, I am not prompted (as expected). Also, if i
try to trick it by STORE 1 TO m.nMainSeg, m.nMainSt, m.nMainLen before
executing this, then I get a connectivity error indicating "Argument
datatype float is invalid for argument 3 of substring function" -
somehow nMainLen is getting converted to a float when VFP passing it
to SQL Server...

Any ideas on how to get around this issue?

Thanks in advance,

Mark
/\/\/


 
 
 

1. Remote View prompting for parameters on CREATE SQL VIEW

If I try to execute the following, I am prompted for the values of
nMainSeg, nMainSt and nMainLen:

CREATE SQL VIEW "V_TEST" REMOTE CONNECT "conDataset" SHARE AS ;
select cDivCode ;
 FROM gldiv,glcharm  ;
 WHERE Gldiv.esegno = ?m.nMainSeg ;
  AND GLCharM.ePType =  1 ;
  AND cDivCode = UPPER( SUBSTRING( GLCharM.cAcNo, ?m.nMainSt,
?m.nMainLen)) ;
  AND  GLCharM.eAcType IN (  1, 2, 3) ;
  GROUP  BY cDivCode

If I remove the GROUP BY, I am not prompted (as expected). Also, if i
try to trick it by STORE 1 TO m.nMainSeg, m.nMainSt, m.nMainLen before
executing this, then I get a connectivity error indicating "Argument
datatype float is invalid for argument 3 of substring function" -
somehow nMainLen is getting converted to a float when VFP passing it
to SQL Server...

Any ideas on how to get around this issue?

Thanks in advance,

Mark
/\/\/

/\/\/


2. DTS and transaction log?

3. How can I create a view with parameter input prompt

4. Text to database

5. Creating a View using a remote view to Oracle

6. mts help

7. SQL Server Remote View ALWAYS Prompting for Password

8. Converting sp from SQL 7.0 to 6.5

9. Creating a local view using CREATE SQL VIEW

10. Views with Prompts & Parameter markers

11. CREATE SQL VIEW REMOTE (gets all records, Why?)

12. Is it possible to combinbe remote views into one view

13. view designer says error when using mssql functions in remote views