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


Top 5 Contributors of the Month
ASPEvil
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Post New Web Links

Oracle Pass through queries

Posted By:      Posted Date: September 11, 2010    Points: 0   Category :Sql Server
 
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


View Complete Post


More Related Resource Links

Pass paramaters to Open Query (oracle)

  

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

 

 


KDW
ALTER Proc [dbo].[USP_SALES_LAUNCH_X]


 @Year int


AS





	SELECT TOP 100	sal.EM_ITEM_NUM 
	, typ.PGM_TYP_DSCR
	,ref.SELL_DSCR
	,ref.NDC_NUM
	,ref.SPLR_ACCT_NAM

	from openquery(ops_oracle_iw,
		'select	
				 oss.INVC_DT
				,oss.EM_ITEM_NUM
				,oss.SLS_QTY 
				,oss.SLS_AMT 
				,oss.CNTRC_LEAD_TP_ID
		from dcp0.V_SALE_ITEM oss
	WHERE SEL_GNRC_IND IN ( ''Y'', ''N'')
	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
		on sal.CNTRC_LEAD_TP_ID= cnt.CNTRC_LEAD_NUM
	 left join btsmart.cb1.T_PGM_TYP typ
		on typ.pgm_typ_cd = cnt.pgm_typ_cd
		LEFT JOIN

Report Builder 2.0 - Text only queries with Oracle?

  

Hi all,

I'm setting up a reporting environment with SQL Server 2008 SP 2. Is there really only text based queries with Report Builder 2.0 hitting Oracle? That seems like a huge problem. Am I missing something? This seems like it would render the product fairly usless outside of spoon feeding reports to users.

Thanks

JBS


Cannot pass null values to my Oracle procs (ORA-06550)

  

I am unable to pass null values to my oracle procedures thru my ASP.NET pages. If I do I get the following error:

"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments"

But the thing is I need to send null values to the proc and the columns that I am sending null accepts the null values. Does anyone know what might be going on and how to fix it?


How to Pass Values Between Web Pages ASP.NET

  
You can pass information between pages in various ways, some of which depend on how the redirection occurs. The following options are available even if the source page is in a different ASP.NET Web application from the target page, or if the source page is not an ASP.NET Web page:

Use a query string.

Get HTTP POST information from the source page.

The following options are available only when the source and target pages are in the same ASP.NET Web application.

Use session state.

Create public properties in the source page and access the property values in the target page.

Get control information in the target page from controls in the source page.

How to Pass Values Between ASP.NET Web Pages

  
You can pass information between pages in various ways, some of which depend on how the redirection occurs. The following options are available even if the source page is in a different ASP.NET Web application from the target page, or if the source page is not an ASP.NET Web page:

Use a query string.

Get HTTP POST information from the source page.

The following options are available only when the source and target pages are in the same ASP.NET Web application.

Use session state.

Create public properties in the source page and access the property values in the target page.

Get control information in the target page from controls in the source page.

pass value from textbox to action, formcollection best way here?

  

Hi I'm building a e-commerce store and on quantity of product I'm going with a textbox. I thought I would ask how would you do this? a viewmodel to use a strongly typed textbox or just get the value of the textbox from the formscollection passed in to the action?


Thanks for any input


Pass a grid column value to a Modal

  

Hi, I have a requirement to hide a field called details on a gridview and show it in a jQuery modal dialog when users click on the Customer ID hyperlink. This grid is created programmatically. I will be hiding the details column via the CSS display:none property. Does some one know how can I pass the grid's selected hidden column value to the modal? Here is some of the code I started:

            GridView mygrid = new GridView();
            //.....................................More columns code for the grid

            //Here is where I need to pass the Details column value associated with the customer ID field selected to the Modal and open the Modal dialog.            
            HyperLinkField linkField = new HyperLinkField();
            string[] dataNavigateUrlFields = { "CustomerID" };
            linkField.DataTextField = "CustomerID";
            linkFi

Pass ASP.NET membership identity object across domain?

  

I am trying to figure a way to SSO with ASP.NET membership and role model.

I can implement custom membership provider which consume web services hosting on a server.

With encryption of data. No problem.

But my question is, If I sign on a website with my provider, got my identity object on that application,

could I pass it to another website on another domain which use the same provider and by doing so, do not need to login again?


If this is possible, I am going to implement this solution. Please tell me what's your take on this.


How to pass value to another form

  

Dear experts,

                     I have QuoteNumber filed in Db.aspx(ddquote.txt) i want to pass this QuoteNumber values(values should to another web form/page i.e. mail.aspx when i click mail button, Message should supposed to display in textbox(txtQuote.txt) of mail.aspx form/page is "You are sending "QuoteNumber"  quotation to "MailID", Thanks.

 

 

 


How to retrieve data from SqlDataSource and pass to a variable?

  

Hi there,

I have a page in my website where there is very little code-behind. I only have two lines of code in Page_Load() and the rest of the code is declarative ASP.NET code. I have a FormView control which uses a SqlDataSource to get some order information. The SqlDataSource calls a stored procedure called usp_GetOrderDetails, here's the code for the SqlDataSource:

    <asp:SqlDataSource ID="sdsFormOrderDetails" runat="server" 
ConnectionString="<%$ ConnectionStrings:ForexDB %>"
ProviderName="<%$ ConnectionStrings:ForexDB.ProviderName %>"
SelectCommand="usp_GetOrderDetails" SelectCommandType="StoredProcedure"
UpdateCommand="usp_UpdateOrder" UpdateCommandType="StoredProcedure"
>
<SelectParameters>
<asp:ControlParameter Name="orderId" ControlID="grdOrders" PropertyName="SelectedDataKey.Value" />
</SelectParameters>
<UpdateParameters>
<asp:ControlParameter Name="orderId" ControlID="frmOrderDetails" PropertyName="SelectedValue" />
</UpdateParameters>
</asp:SqlDataSource>

Need Oracle Data Provider .CS File for Oracle 10g Database connection !

  

Hi.,

I need a 'Wrapper.cs' file which takes care of the Database connection ( Oracle 10g) where

i can just call the method with my SQL Query

(eg)

Gridview1.DataSource = SampleWrapper.ExecuteDatatable("THE SQL QUERY");

Gridview1.DataBind();

Plz Post the link if there is any open source !    


Which tier do Linq queries belong?

  

Started in middle of a new project.  Our design architecture uses an n-tier (not MVC - no controllers) structure with Presentation, Data, Business and Service tiers.  However the lead analyst has the majoriety of code all in the Business tier claiming it all contains Business logic.  Our data tier is nothing more than a single .dbml model of the primary database to support all of the Linq queries in the Business tier.  To my mind Linq queries and classes they use are data access and belong in the data tier  but our lead analyst says they contain business logic therefore have to be in business layer.  


Combining two LDAP queries

  

Hey guys, I have these two queries I'd like to combine into one.  Is there any way of doing it?
Query 1: (&(!cn=SystemMailbox{*})(&(&(ou>="")(name=executive*)(objectCategory=organizationalUnit)(ou=*operations))))
Query 2: (&(!cn=SystemMailbox{*})(&(ou>="")(name=50*)))
Thanks in advance.

Hey guys, I have these two queries I'd like to combine into one.  Is there any way of doing it?


<

Data Points: LINQ Projection Queries and Alternatives in WCF Services

  

LINQ's ability to project randomly shaped data into anonymous types can be a blessing and a source of frustration, says Julie Lerman. It's great when you just need to grab a special view of your data, but it can be problematic in certain circumstances. She explains what the limitations are and how to get around them.

Julie Lerman

MSDN Magazine May 2010


Data Points: Precompiling LINQ Queries

  

Did you know that by precompiling LINQ queries you might actually be degrading your app's performance if you're not careful? Julie Lerman explains how to ensure you're not re-precompiling queries each time and losing the expected performance benefits across post-backs, short-lived service operations and other code where critical instances are going out of scope.

Julie Lerman

MSDN Magazine March 2010


Toolbox: Easy LINQ Queries, Becoming A Better Developer, And Logging Help

  

This month Toolbox provides help with error logging, finds an app that helps you write LINQ queries, and explores the non-technical side of development.

Scott Mitchell

MSDN Magazine September 2008


Parallel LINQ: Running Queries On Multi-Core Processors

  

Use Parallel Language Integrated Query (PLINQ) to take advantage of multi-processor computers and the newer wave of multi-core processors.

Joe Duffy and Ed Essey

MSDN Magazine October 2007


Categories: 
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