Post reply

reference a 'function' on another database

  • December 19, 2013 at 8:41 am

    #281495

    Hi

    I have a report which is on the test reporting site and live reporting site.

    I had to make a change to a function that the report uses, which I have done on the test database. However the user wants to test the report against live data to make sure it is accurate, as the test data is out of date.

    Im not sure how I can get the report to look at the function on the test database, but still run again live data.

    I have tried the following:

    1.testdb.Rfunctioname (which is the database and the function name.)

    2.[testdb].Rfunctioname

    3.datasource.testdb.Rfunctionname (which is the datasource.database.function)

    4.datasource.Rfunctioname(which is the datasource and function name)

    Any help please?

    Also its against an oracle database

    Thanks

    Rakhee

  • December 19, 2013 at 8:48 am

    #1674866

    you need to use a three part naming conventions which includes the schema:

    testdb.dbo.Rfunctioname

    testdb..Rfunctioname

    you also may run into permissions problems, as the person calling the procedure or code needs access to the proc;

    i use master.dbo.DelimitedSplit8k in tons of code, and i had granted access to that proc.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 19, 2013 at 9:04 am

    #1674879

    Thanks.

    I have tried as you have suggested but I still get the error

    (ORA-00904: "TESTDB"."DBO"."RFUNCTION": invalid identifier)

    I entered it without the quotes.

  • December 19, 2013 at 9:14 am

    #1674887

    it's an oracle function that you'd access via a linked server then?

    that's a different story, i must have missed that part.

    you have a linked server in place already?

    ie, from SSMS, you are able to execute sp_tables_ex MyOracleLinkedServerName and get results?

    i assume the function just returning a bunch of data, maybe with parameters, then it's a four part naming convention:

    here's my best guesses:

    --scalar function?

    select MyOracleLinkedServerName..testdb.Rfunctioname

    --talbe value function?

    select * FROM MyOracleLinkedServerName..testdb.Rfunctioname

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 19, 2013 at 9:20 am

    #1674895

    ... Got bitten by the quote bug...

    It seems Lowell was on top of this already.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • December 19, 2013 at 9:22 am

    #1674896

    Yes I put right at the end of the topic that its against oracle, sorry I should have made that clear from the beginning.

    The report is created in SSRS but the data (including the functions) is against an oracle database.

    My situation is there are 2 oracle databases on the server one called LIVE and called TEST. The report is currently looking at the live database, but I want one of the functions to run against the test databse.

    Hope that makes more sense? Do I still need the database link?

    THanks

    R

  • December 19, 2013 at 12:18 pm

    #1674956

    rkaria (12/19/2013)

    Hope that makes more sense? Do I still need the database link?

    THanks

    R

    Yup, but you're on the wrong forum. Even though you're displaying your output in MS SSRS, the data stream is all that it cares about. To produce the data stream, you need to write the oracle query, in which case you need folks familiar with that db engine.

    I would recommend you ask on an Oracle Board the same question, but instead of mentioning that it's aimed at SSRS (which I've seen get some lambasted answers), just mention you're trying to pull the data through an ODBC connection (which is what you're doing) and need to know how to properly write the SQL that's passed in.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 20, 2013 at 1:33 am

    #1675065

    ok will do thanks, for the info.

Viewing 8 posts - 1 through 7 (of 7 total)

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