.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Pass paramaters to Open Query (oracle)

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :Sql Server

I am trying to pass two paramaters into an oracle open query


The first is a comma split string value of item numbers

and the second would be date ranges, - 4 of them  1) most recent 6 months, 2) most recent 12 months 3) most recent 18 months 4) most recent 24 months.  As i understand it - the open query is treated as dynamic sql and this is more challenging -

This table has alot of info so I do not want to bring back everything




 @Year int



	from openquery(ops_oracle_iw,
		from dcp0.V_SALE_ITEM oss
	AND oss.SLS_PROC_WRK_DT BETWEEN TO_DATE('01/01/2009', 'MM/DD/YYYY') AND TO_DATE('12/31/2010' , 'MM/DD/YYYY')
	AND oss.EM_ITEM_NUM in ('2242428', '2243087' , '2243624 ' , ' 2242758' , '2244051', ' 2242782', '2243525')

') as sal

	left join BTSMART.cb1.V_CNTRC_LEAD_CB_ATTR cnt
	 left join btsmart.cb1.T_PGM_TYP typ
		on typ.pgm_typ_cd = cnt.pgm_typ_cd

View Complete Post

More Related Resource Links

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

Pass XSL "Currently Selected" Value via Query String

Hi, I have a DVWP with an ID column, that's connected to other webparts on the page to filter their values accordingly.   I also have a "Print" button that opens a custom ASP page that's essentially a print-friendly single item data view.  How do I pass the "selected item" value to the URL when a user hits the print button? I've already set up the "ID" parameter and filters on the target page, just need to know how to get the value info from the list ID field inserted into the URL upon button click.  Thanks in advance


hai.......I am a biggner in SQL DB . but i started a complicated and painfull work in SQL SERVER 2008.the problem  convert Oracle hierarchical query to SQL query please help. the query SELECT DISTINCT         LEVEL LVL,         SCH.NSCHEDULE_SL,         SCH.NSCHEDULE_SL_FM,         SCH.CSHED_CNAMEFROM  FA_SCHEDULES SCH WHERE     LEVEL  = 1 AND    NSCHEDULE_SL_FM  IS NULL AND    NBRANCH_SL  = 2  CONNECT BY PRIOR SCH.NSCHEDULE_SL_FM  = SCH.NSCHEDULE_SL AND    NBRANCH_SL  = 2please help me

Oracle Query

Get a Table in the format  Column 1 - Total of 1st 4 records and then total of next 4 records..... an d last if 2 records remain then total of last 2 records Column 2 - Amount will come from database Column 3 - In 1st 4 records 3 records will be 'N' and last(4th) will be Y, then for next 4 - 3 will be 'N' and last will be 'Y'   Total            Amount          Sign 5000.00        1000.00          N 5000.00        2000.00          N 5000.00        500.00            N 5000.00        1500.00          Y 2000.00        1000.00          N 2000.00        1000.00          YRegards, Sweta Jain

how to generate an Xml pass Simple query structure like

1. My sql query is        SELECT a.HD, a.HEAD, a.SH, a.SUBHEAD, a.TRK, a.TRACK, a.TGT, a.TARGET, a.PDC, a.DT_RELEASE, a.STATUS, a.COFROM V_HIERARCHY1 a2, My Data is HR    HR    Rec    Records    Leave    Leave Records    HRL.0001    Policy and Process for leave record.    27.06.2010    30.06.2010    WIP    EdlinkHR    HR    Rec    Records    Leave    Leave Records    HRL.0002    Quarterly Review    01.07.2010    04.07.2010    Planned    EdlinkHR    HR    DB    Database    SAL    Salary    DDS.0001    Calculation of Monthly salary    08.08.2010    09.08.2010    WIP    EdlinkHR    HR    Rec    Records    Ind    Induction Records    HRI.0001    Completion of dossiers.    08.07.2010    10.07.2010    WIP   &nbs

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson

Button in datalist, open new window with query string

hi everyone,i'm writing a button which need to open a new window and sending binded data as query string:<asp:DataList ID="DataList_Comment" runat="server" DataKeyField="Comment_id" DataSourceID="CommentDataSource" Width="482px"> <ItemTemplate> .... <br /> <input type="hidden" name="comment_id" id="comment_id" value='<%# Eval("Comment_id") %>' /> <input id="Button1" type="button" value="button" onclick="open_win(comment_id.value)" /> </ItemTemplate> </asp:DataList> here is the open_win function:<script type="text/javascript"> function open_win(id) { var url = "Report.aspx?type=comment&id=" + id; window.open(url); } </script> But the url created is ".../Report.aspx?type=comment&id=undefined", the queryString["id"] is not defined.In the calling page source html, comment_id has value.<input type="hidden" name="comment_id" id="comment_id" value='2' /> How can i set the variable value of "comment_id" in button onclick, to call the javascript o

Oracle Pass through queries

G'day So, this is a newbie type issue as I'm quite certain there is a simple solution I just haven't found it yet. I'm an old Oracle DBA who is new to SQL Server so I'm currently struggling. Anyway, I have created a linked server from my SQL Server database. I can easily issue distributed queries or I can use openquery without problems so the link is working. The OLEDB I am using is Microsoft's not Oracle's so it is possible that changing the version of the oledb may solve my problem. The SQL statements I am issuing are simple but are not being executed as pass through queries. The documentation seems to indicate that it should work (do the processing on the Oracle side) but it doesn't. This is important as the source table in Oracle I am hitting is about 7 gig in size (fact table) and the performance sucks. I can't use openquery because my SQL statement is dynamic. Openquery doesn't use variables it want's a constant so if I'm doing domething wrong there let me know. I can limit the result set to about 700,000 records with openquery but the other parameters are variables so I can't get to the small result set I want which are only a few dozen records at most. Is there a way to issue dynamic pass through queries? Should I change oledb versions / products? Should I be using Execute? The documentation says that "SQL Server 2005 extends the EXECUTE statement so

How to pass string values to parameterized sql query in Clause?

Hi,I'm using parameterized sql query to get data from database string query = "Select * from employee where employee_city in (@value)";strign city ="'NewDelhi','Bangalore','Mumbai'";I'm using following code to achive thisDataSet ds = new DataSet();SqlConnection con = new SqlConnection("Server=localhost;....");SqlCommand cmd = new SqlCommand();cmd.CommandText =query;SqlParameter param = cmd.Parameters.Add("@value",SqlDbType.VarChar);param.Value = city;SqlDataAdapter dap = new SqlDataAdapter();dap.SelectCommand = cmd;dap.Fill(ds);But this is not giving the result.If run the query in SQLServer query window as "Select * from employee where employee_city in ('NewDelhi','Bangalore','Mumbai')", records are there.But the same query will not return any records from ADO.Net.How to solve this?Thanks,Ashokan

Report Builder 3.0 for Oracle with Graphical Query Designer

Is there a possibility to have Graphical Query Designer in Report Builder 3.0 in case the connection is made to an Oracle database (through .NET Provider for Oracle for example)? I need to offer to the end users the means to build their own reports. Every user has associated a group of database objects and as soon as users are added new database objects are added. Therefore I cannot create a model because it needs to be changed frequently and the model in this case would contain objects to which the user does not have access. The only option is that the user should create his report query in a friendly manner. So the query text editor is of no good in this case.   I've found in documentation that for Oracle and other databases there is no Graphical Query Designer: In Report Builder 2.0, to specify a query for data source types Oracle, OLE DB, ODBC, and Teradata, you must use the text-based query designer (http://msdn.microsoft.com/en-us/library/dd220607(SQL.100).aspx). Is there a way to have Graphical Query Designer in Report Builder 3.0 or should I look for other products? In case Report Builder cannot solve this problem can you make some product recommendations - ad-hoc web reporting for .NET?

By pass the prompt - Do you want to open or save this file?


Is there any way that I can avoid or bypass the following prompt: Do you want to open or save this file?

I am using a data view webpart where I have hyper-linked a column with  an infopath file. So when ever I click on the hyperlink, a window prompts with the mentioned text.

Any help in this regard will be appreciated!

Having error in SSRS using oracle query


Hi ,

I have to create report based on date filter but when i used below query ,an error has occured

ORA - 01745 : invalid host/bind variable name.


query is


select top 2* from tablename where prodate>=:date and prodate<:date +1




Pass paramaters to SSRS Subscriptions using VS2005



Newbie here. I have created SSRS reports and built a user UI to run these reports. Now when creating SSRS subscriptions in report Manager, is it even possible to programmatically pass the delivery method (email or file share), To email address, scheduled time and date, etc from my front-end UI to the Report Manager?

Any help will be greatly appreciated. Thanks

Pass Query String


I want to pass the 2 parameters to the action "Initialize" when my application launch.

public ActionResult Initialize(string companyID, string quoteID)


Where my RegisterRoutes method looks like

        public static void RegisterRoutes(RouteCollection routes)
                "Default", // Route name
                "{controller}/{action}/{id}", // URL with parameters
                new { controller = "MyController", action = "Initialize", id = UrlParameter.Optional } // Parameter defaults

Can somebody tell me what changes require in my code?



Query Wizard for Oracle Connectivity with Moss 2007 using BDC


I am able to connect Oracle using BDC Metaman and get the data using small queries. But i have a big query to retrieve the data from Oracle and not able to use that query using BDC Metaman. Is there any query wizard to run my query to retrieve Oracle data. 

Is there any other tools having this facility (Query Wizard). 

Please assist me for the same. 



how to pass variable into SQL query in aspx



custom function which stores the current DATE into todayDate. I use this function  to convert system date into SQL friendly date. 



I used Data Grid and SQL Data source. 

SelectCommand="select * from sales where aab = 110026"


I want my select statement should take the date from todayDate.

ie: SelectCommand="select * from sales where aab = todayDate"

Help me to write this statement in correct  syntax 

Content Query web part - how to open documents on first click instead of first opening the document


Hi all, Hopefully this is a easy settings change question, when I create a content query web part and have it look at documents in a document library (Either document library or document centre) when I click on the name of the document (link to doc) in the content query it opens up a page that list the document properties, I then have to click on the "Name" of the document link to open the document but when i close the document I end up at the library that the document is in and not on the page that I clicked on  the link in content query web part. So in some cases a user is on a site and clisks on a link(in a content query web part) that eventaully opens a document in the document centre and when they close the doucment there end up in the document centre.

Any ideas why this behaviour is suddendly happening, it didn't happen when I first added the content wuery web parts, thanks in advance.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend