Post reply

sintaxis

  • January 29, 2020 at 10:48 pm

    #3721402

    hello my frineds

    i need to know how to "translate" this query:

    this is from oracle toad, and its to retrieve the information of a table from a connection with an Avaya CMS server

    SELECT *

    FROM "root"."hagent"@cms70db

    we are migrating from toad oracle to sql server manager, so how would you write a query to check all the info from a table from a connection to Avaya CMS???

    thanks a lot!

     

  • January 30, 2020 at 12:47 am

    #3721434

    this is something that you should be asking to the vendor of Avaya.

    but that query in Oracle is querying a "db link" - this equates roughly to a Linked Server in SQL Server

    while the query itself is easy - it would be something like "select * from cms70db.root.hagent" setting up the linked server (cms70db) is where you may find issues.

     

    see https://www.quora.com/How-do-I-connect-an-SQL-server-to-Avaya-CMS-Database for some info - it is old but will guide you on the correct direction - but for support you really need to go to Avaya

  • January 30, 2020 at 5:25 pm

    #3721704

    hey SSChampion , thanks for replying , another question when i write that query in sql server what buttons should i press instead of ctrl+enter (that's what i press in toad to run a query), because there is a button in sql server that says execute but im not sure if im running that as a script instead as a regular query or if that matters at all

  • January 30, 2020 at 7:15 pm

    #3721744

    in Management Studio, hitting the Execute button, (or F5 on keyboard) will run all the statements in the current tab.  If you only want to run one statement, you have to highlight it with the cursor (by clicking and dragging over the query, or holding SHIFT key and use arrows on keyboard) then hit Execute or F5.

  • January 30, 2020 at 9:24 pm

    #3721781

    Thanks Chris!

    another question XD

    well the query is saying that incorrect syntax near "hagent". Expecting '.', ID, or QUOTED_ID.

    im writing frederico's query like this:

     

    USE [Pit]

    GO

    select * from cms196.root.'hagent' and hagent is the only red underlined word in my query, does anyone knows what is the syntax error here? what else should i add or remove?

     

    thanks a lot for your help guys

  • January 30, 2020 at 10:09 pm

    #3721799

    You might not need the quotes and likely need to follow four part naming conventions (Server.Database.Schema.ObjectName). So try without the quotes and adding another dot between cms196 and root so that you have: select * from cms196..root.hagent

    Sue

  • January 30, 2020 at 10:15 pm

    #3721800

    i tried like you wrote Sue and i receive the following after pressing f5:

     

    Msg 7312, Level 16, State 1, Line 4

    Invalid use of schema or catalog for OLE DB provider "Search.CollatorDSO" for linked server "cms196". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

    Completion time: 2020-01-30T16:14:28.1314148-06:00

  • January 30, 2020 at 10:31 pm

    #3721802

    Check the provider you are using - the providers listed under Linked Servers just above the list of linked servers. Select the properties for the provider by right clicking and select/check the Zero Level Only property.

    Sue

  • January 30, 2020 at 11:05 pm

    #3721823

    so my boss and i gave up trying to pull the tables this was because at the end we succeed in doing it, (thanks a lot to you all guys) but we noticed the tables need the schema name due to sintaxys but that schema will disapear because we are migrating our database from windows to azure so, my boss asked me to check if with External resources  template we can pull the tables without adding the schema name in the querys, what do you think guys? is it possible?

     

    Capture

  • January 30, 2020 at 11:29 pm

    #3721826

    does not matter what we think will work - you are trying to extract data from a third party database (avaya which is, as far as I know, an informix database.)

    In order to connect to it you need to see on the vendor (see the link I provided before) the instructions of how to connect to it.

    while from a onprem database it may be easy, from an azure it may not be as easy as that and you may need to go through Data Factory - but the fact is that it is the vendor that can help you.

    It is very likely that a External resource in Azure will not allow it.

    also take in consideration that if this is a once off migration from Avaya to a SQL Azure database then using SSIS or similar tool will be a lot less hassle and easier to setup - again the link I supplied can help you

  • January 30, 2020 at 11:58 pm

    #3721830

    other aspect - assuming that you did install the correct driver on your SQL Server and that your cms is using the defaults then the syntax should be

    select *

    from cmslinkedservername.cms.root.hagent (as per Avaya manuals)

     

  • January 31, 2020 at 1:54 pm

    #3722004

    omg now you made me think on the driver, the one i used is this:

     

    Capture

    i've created the connection with odbc data source admin , i used 32 bits version because i read in a webpage informix is kinda old and the drivers are only available in the 32 bits version which is right cause in the 64 bits version i couldnt find it, mmm do you know how to check if mi driver is the correct one?, i will take a look at the webpage you provided me before, thanks a lot man for all your help

Viewing 12 posts - 1 through 11 (of 11 total)

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