Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    24

    Exclamation Unanswered: How to get Primary key....plz help!!!!

    i'm having problem to get th primary key from d database....
    for your information i'm using java to get the primary key....
    this is my code...
    rs = stt.executeQuery("sp_columns "+table_db+";");
    while(rs.next())
    {
    out.write("\n\n"+rs.getString("COLUMN_NAME"));
    out.write(",\t"+rs.getString("TYPE_NAME"));
    out.write(",\t"+rs.getString("IS_NULLABLE"));
    }

    rs = stt.executeQuery("sp_foreignkeys @table_name = N'table_db';");


    but the problem is....
    i get this error message...could anyone tell me what's the problem....
    java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
    find server 'table_db' in sysservers. Execute sp_addlinkedserver to add th
    e server to sysservers.

    how do i solve this problem....

    thanx to anyone who can help me......

  2. #2
    Join Date
    May 2002
    Posts
    299
    We need to see more code, especially for stt definition. "sysservers" has nothing to do with table_db.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    24
    Connection conn = null;
    Connection conn2 = null;
    Statement stt = null;
    Statement stt2 = null;
    ResultSet rs = null;
    ResultSet rs2 = null;

    String driver = prop.getProperty("driver");
    String url = prop.getProperty("URL");
    String username = prop.getProperty("username");
    String password = prop.getProperty("password");
    String database = prop.getProperty("database");
    String table_db = prop.getProperty("table");
    String output = ""+prop.getProperty("target.folder")+"/" + database + "_" + table_db + ".txt";

    System.out.println("Server : "+url);
    System.out.println("Database : "+database);
    System.out.println("Table : "+table_db);
    System.out.println("File : "+output);

    try
    {
    Class.forName(driver);
    conn = DriverManager.getConnection(url+database, username, password);
    conn2 = DriverManager.getConnection(url+database, username, password);
    stt = conn.createStatement();
    stt2 = conn.createStatement();
    }
    catch(Exception e){ System.out.println("Connection : "+e.getMessage()); e.printStackTrace();}

    try
    {
    rs = stt.executeQuery("sp_columns "+table_db+";");
    while(rs.next())
    {
    out.write("\n\n"+rs.getString("COLUMN_NAME"));
    out.write(",\t"+rs.getString("TYPE_NAME"));
    out.write(",\t"+rs.getString("IS_NULLABLE"));
    }
    }
    catch(Exception g){ System.out.println("Fetch : "+g.getMessage()); g.printStackTrace();}

    try
    {
    rs = stt2.executeQuery("sp_foreignkeys @table_server = N'Met2Parameters';");
    while(rs.next())
    {
    out.write(",\t"+rs.getString("PKCOLUMN_NAME "+table_db+")"));
    }
    }
    catch(Exception g){ System.out.println("Fetch Primary Key: "+g.getMessage()); g.printStackTrace();}

    this is the code that you asked... hope u can help me....

  4. #4
    Join Date
    May 2002
    Posts
    299
    do you get anything back when executing this:

    stt.executeQuery("exec sp_columns "+table_db+";");

    or

    stt.executeQuery("select @@version [version];");
    --
    -oj
    http://www.rac4sql.net

  5. #5
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    24
    i dun hav any prob to execute that statement.... the prob that i'd got was that sp_foreignkeys statement.......
    actually i hav discovered the problem.... but still can't solve the prob...
    nway thanx 4 ur time... i really appreciate it.....

  6. #6
    Join Date
    May 2002
    Posts
    299
    I see. Do you actually have a linked server called 'Met2Parameters'. post back if you need additional help.
    --
    -oj
    http://www.rac4sql.net

  7. #7
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    24
    firstly, i'm sori.. i dun evn know wat's the table_server... i thought it was db's table ... but luckily i'd found wat's the table server.... unfotunately... i can't execute the sp_foriegnkeys bcoz i can't get the permission to get the data... when i check it back.. the table_server dun hav data access... means the server just hav rpc,rpc out,use remote collation ....

    i'm just a little bit confused..... when i'm trying to use sp_fkeys pktable_name....
    it returns no data....
    PKTABLE_QUALIFIER PKTABLE_OWNER PKTABLE_NAME PKCOLUMN_NAME FKTABLE_QUALIFIER FKTABLE_OWNER FKTABLE_NAME FKCOLUMN_NAME KEY_SEQ UPDATE_RULE DELETE_RULE FK_NAME PK_NAME DEFERRABILITY
    -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------

    0 Row(s) affected

    is it means that the table in the db dun hav pk n fk......??
    bcoz when i check with winsql(just like ent manager but little features)... it shows the db has pk n fk...
    sori... i'm new to this db... i'm alwiz using mysql...
    can u giv some tips or idea ..... thanx

  8. #8
    Join Date
    May 2002
    Posts
    299
    sp_foreignkeys is used to get info from a linked server.
    sp_fkeys is used to get info from a local table.

    here is a quick example for finding the pk.

    SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'authors'
    AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

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