none
Wrong table names RRS feed

  • Question

  • Sir I have these codes

    db1='at20'
    lcCONN1='Driver={sql server}; server=SERVER\SQLEXPRESS;Dbq='+"db1"+'; Uid=sa; Pwd=123'
    lcCONN1 = STRT(lcCONN1,CHR(13),'')
    lcCONN1 = STRT(lcCONN1,CHR(10),'')
    con1 = SQLstringCONNECT(lcCONN1)

    then I use this command

    TEXT TO cmdd noshow
    select * from u_forms where wstation='SALES'
    ENDTEXT
    
    if sqlexec(con1,cmdd)<0
    	aerror(laerror)
    	messagebox(laerror[1,2])
    endif

    It says: 


    it meas database is connected but table u_forms does not exists.

    Table is physically exists as seen in attached image



    Now I issue this command to check all tables in database

    TEXT TO cmdd noshow
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    ENDTEXT
    
    If SQLExec(con2,cmdd,'abc')<0
    	Aerror(laerror)
    	Messagebox(laerror[1,2])
    	lok=.F.
    	Return.F.
    ENDIF
    
    SELECT abc
    BROWSE

    it is showing only these tables


    The above tables are not part of database=at20

    What I am doing wrong?

    Why I am not getting actual tables of my database called at20 to run this query

    TEXT TO cmdd noshow
    select * from u_forms where wstation='SALES'
    ENDTEXT

    Please help me



    tqmd

    Sunday, September 1, 2019 5:52 AM

All replies

  • What if you run below query?

    select * from db1.dbo.u_forms where wstation='SALES'

    It looks like   you connected to "wrong" database (perhaps master db)



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 1, 2019 6:58 AM
    Moderator
  • Hi Tariq Mehmood,

    From your description, you have checked the table exists from SSMS. But you can’t find it in Microsoft Visual FoxPro. If I misunderstood, please let me know. 
    You used command to check all tables in at20 database, but it seem all tables that showing in the result are not from this database. It seems that you chose the wrong database when you run the query.

    By the way, for issues about running query on Microsoft Visual FoxPro, I suggest you open a thread in Microsoft Visual FoxPro forums, People there will help you more effectively. 

    Hope this could help you.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 2, 2019 7:06 AM
  • spt_fallback_db is a table in the System database "master", so you are connected to "master" database instead of "at20"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, September 2, 2019 9:04 AM
    Moderator
  • Hi Tariq Mehmood,

    Did you resolve your issue? If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, September 5, 2019 7:46 AM
  • lcCONN1='Driver={sql server}; server=SERVER\SQLEXPRESS;Dbq='+"db1"+'; Uid=sa; Pwd=123'

    It looks like you are "DBQ=" as the connection string keyword for the database name specification. With SQL Server ODBC drivers, the keyword should be "Database=" instead so you inadvertently connected to master because this driver did not recognize the database specification.

    Try:

    lcCONN1='Driver={sql server}; server=SERVER\SQLEXPRESS;Database='+"db1"+'; Uid=sa; Pwd=123' 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Thursday, September 5, 2019 10:08 AM
    Moderator