Post reply

complicated problem

  • August 19, 2004 at 4:06 am

    #152264

    i want to use a table which is in sql server 2000.

    whole my website is in ASP and iam using backend as oracle 8.0

    but i have one table that is in sqlserver2000 , which has username,id,password,email,mobile,contact address, and so on.

     

    which i need in some operations , what iam planing to do is

    i have a query , i want to directly connect to sqlserver2000/table

    and use that table in a join.

    i have done such thing when i joined 2,3 tables from another database in a joins

    query was something like that..

    select * from \Provider=SQLOLEDB; Data Source=10.16.58.120;Initial Catalog=ONLGR_Temp; UId=tempo; Pwd=mullahgulla\vusermanage

    but i forgot//

     

    Thanks in Advance.

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • August 19, 2004 at 5:42 am

    #519737

    you could set up your oracle server as a linked server in SQL

    you can then create a view in your SQL server that does the join for you.

    ou can then just connect to the SQL server from ASP and load the recordset

    MVDBA

  • August 19, 2004 at 5:51 am

    #519740

    no dear,

    not that,

    my oracle server is the main database used, there is only one table which is in sql server which i need for just a temporary join

    i had done this previously where i used 2 mdb's tables for a join using syntax like this

     

    select * from \Provider=SQLOLEDB; Data Source=10.16.58.120;Initial Catalog=ONLGR_Temp; UId=tempo; Pwd=mullahgulla\vusermanage

     

    Any idea, i know its possible.

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • August 19, 2004 at 7:23 am

    #519770

    pls answer my question

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • August 19, 2004 at 7:15 pm

    #519922

    The SQL Server version is as follows. Note that the Oracle version could very well be different and if so would likely need to be obtained from an Oracle specific forum.

    SELECT *

    FROM OPENROWSET('SQLOLEDB', 'INFONET' ; 'sa' ; 'password',

       'SELECT * FROM northwind.dbo.orders' )

    My Google search I used to locate the above was as follows. Refine to your needs.

    +"Data Source=" +Oracle +"Provider=SQLOLEDB" +JOIN

     

  • August 20, 2004 at 12:05 am

    #519940

    SELECT * FROM OPENROWSET('SQLOLEDB', '202.16.58.120';'dev';'user1234','SELECT * FROM onmgr_temp.dev.dept')

    when i type this command in (TOAD) hope u have used toad its an enterprise manager & queryanalyzer for oracle.

    iam getting this below errror

    ----------------------

    SELECT * FROM OPENROWSET('SQLOLEDB', '202.16.58.120';'dev';'user1234','SEL

    ORA-00933: SQL command not properly ended

    ^----------------

    Statement Ignored

    ------------------------------------------

    and in benthic software golden32

    it gives script parsing error unterminated string  

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • August 20, 2004 at 12:13 am

    #519941

     

    what exactly iam doing is , iam making an automated response to the clients when there policy is nearing expiration,

    that will require emailids,and mobilenos which is in sqlserver database, rest all is in oracle database

    GRP_ID

    GRP_COMPANY_ID

    are the fields which are common in all the tables, including the table in sql server. so i will simply be using them in the join

    there is table of policy, which stores the policy related details, 1 table is there which lets the client configure before how many days he wants to get reminded, and there will be 2 reminders 1  set by the client and 1 set by the system for 2 days prior.  that  table is "EMAIL_SMS_MASTER "

    and there is another table which stores a record when an email is sent

    first i query the table of policy

    here is the query which i need to join the sql database table for. emailid ,mobilno.

    select DAYSBLOCK,INCEPTION_DT,EXPIRY_DT,PLANNAME,EXPIRY_DT,sys.toolkit.decrypt(POLICY_NO),

    SECONDREMINDER,sys.toolkit.decrypt(po.GRP_ID)as grp_id,sys.toolkit.decrypt(po.GRP_COMPANY_ID) as GRP_COMPANY_ID,

    FIRSTREMINDER,sysdate,ceil(expiry_dt-sysdate) from EMAIL_SMS_MASTER  ems, POLICY po

    ,SMSANDEMAILSENT  sms

    where

    SYS.Toolkit.Decrypt(po.GRP_ID)=ems.GRP_ID

    and SYS.Toolkit.Decrypt(po.GRP_COMPANY_ID)=ems.GRP_COMPANY_ID

    and daysblock>=ceil(expiry_dt-sysdate)

    and ceil(expiry_dt-sysdate)>=1

    and sms.POLICYNO(+) =po.POLICY_NO

    AND POLICYINCEPTION_DT(+)=INCEPTION_DT

    AND POLICYEXPIRY_DT(+)=EXPIRY_DT

    AND SMS.GRP_ID(+)=PO.GRP_ID

    AND sms.GRP_COMPANY_ID(+)=po.GRP_COMPANY_ID

    group by

    DAYSBLOCK,

    ceil(expiry_dt-sysdate),PLANNAME,EXPIRY_DT,SECONDREMINDER,FIRSTREMINDER

    ,POLICY_NO,INCEPTION_DT,EXPIRY_DT,po.GRP_COMPANY_ID,po.GRP_IDhaving FIRSTREMINDER<>'y' or firstreminder is null

    or  SECONDREMINDER='' or SECONDREMINDER is null

    pls help me 

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • August 20, 2004 at 2:42 am

    #519959

    if you're running this on an oracle server then you need to post it on an oracle forum .

    MVDBA

  • August 20, 2004 at 3:13 am

    #519962

    first of all iam dont' know any oracle forums.

    and iam very used to this forum, u can help if u try

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • August 20, 2004 at 4:15 am

    #519964

    debugging oracle syntax error's isn't something most SQL server DBAs could do.

    you could try lazydba.com - they have sections for oracle & SQL problems

    MVDBA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply