tag:blogger.com,1999:blog-1391695206712094920.post248712564900203982..comments2023-08-22T08:29:12.843-07:00Comments on Learning Microsoft Technologies.......: Linked Server setup with OracleUma Shankar Patelhttp://www.blogger.com/profile/02287010306811895923noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-1391695206712094920.post-40682321123327369822014-08-21T04:05:18.161-07:002014-08-21T04:05:18.161-07:00Use Dynamic SQL QUERY as
http://www.mssqltips.com/...Use Dynamic SQL QUERY as<br />http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/Uma Shankar Patelhttps://www.blogger.com/profile/02287010306811895923noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-65119169766030806072014-08-21T04:00:32.158-07:002014-08-21T04:00:32.158-07:00Thanks Uma Shankar, But I have one more query for ...Thanks Uma Shankar, But I have one more query for you :). How can I pass linked server name dynamically to OPENQUERY? I have three databases in Oracle local, stage and production. So I have created three linked server but I am getting problem of passing Linked Server name dynamically to OpenQuery so that I can push data in one of the Oracle database based on a condition.<br /><br />So far I have tried to achieve my objective with something like this by passing linked server as variable:<br /><br /><br />DECLARE @LinkedServerName VARCHAR(100);<br />SET @LinkedServerName='StageLinkedServer';<br /><br />OPENQUERY(@LinkedServerName,'SELECT MAX(id) from oracletable') a;<br /><br />and later I have tried by passing linked server name by direct select query:<br /><br />OPENQUERY(SELECT name FROM sys.servers where server_id=3,'SELECT MAX(id) from oracletable') a;<br /><br />But none of them worked for me.<br />Jitenderhttps://www.blogger.com/profile/00688875538371921757noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-11168973799553830142014-08-20T04:07:26.371-07:002014-08-20T04:07:26.371-07:00Hi Jitender,
Create sequence in oracle for identit...Hi Jitender,<br />Create sequence in oracle for identity column and then use in Query on linked server..<br />Try somthing like this if possible becoz i did'nt try this<br />INSERT INTO OPENQUERY(linkedservername,'select dept_seq.NEXTVAL,<br />col1,<br />col2,<br />col3 <br />from oracletablename')<br /><br /> SELECT<br /> col1,<br /> col2,<br /> col3<br /> FROM sqldatabase.dbo.tablename;<br /><br />other wise<br />use <br />DECLARE @unq_id int; <br />SELECT a.id in @unq_id <br />FROM OPENQUERY(PRODNEW,'SELECT MAX(id) from oracletable') a;<br /><br />INSERT INTO OPENQUERY(linkedservername,'select id,<br />col1,<br />col2,<br />col3 <br />from oracletablename')<br /><br /> SELECT<br />(@unq_id +1),<br /> col1,<br /> col2,<br /> col3<br /> FROM sqldatabase.dbo.tablename;<br /><br /><br /><br /><br />Uma Shankar Patelhttps://www.blogger.com/profile/02287010306811895923noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-44613562013632293252014-08-20T03:12:23.250-07:002014-08-20T03:12:23.250-07:00Hi, How can we Auto Increment value in Oracle tabl...Hi, How can we Auto Increment value in Oracle table where insertion is happening through SQL Server Linked Server?Jitenderhttps://www.blogger.com/profile/00688875538371921757noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-14092568282447800662014-04-17T23:31:28.830-07:002014-04-17T23:31:28.830-07:00Did you create Linked Server with Oracle.
If not t...Did you create Linked Server with Oracle.<br />If not try above steps and use <br /> EXECUTE ('begin my1.spProc(?,?,?,?); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;<br />Uma Shankar Patelhttps://www.blogger.com/profile/02287010306811895923noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-52655591508887660022014-04-17T22:25:15.336-07:002014-04-17T22:25:15.336-07:00Use
EXECUTE ('begin my1.spProc(?,?,?,?); end...Use <br /><br />EXECUTE ('begin my1.spProc(?,?,?,?); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;<br /><br /><br />Some times an error generates regarding RPC.<br />In that case , go to properties of Linked Server and under Server Option set RPC and RPC out to true.Uma Shankar Patelhttps://www.blogger.com/profile/02287010306811895923noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-85231916456671927832014-04-17T07:24:24.661-07:002014-04-17T07:24:24.661-07:00Hi , I have to call the storeproc with one input p...Hi , I have to call the storeproc with one input param and three output param.can any one help with me <br /><br />StoreProcName(Param1 IN VARCHAR2,Param2 OUT INT,Param3 OUT INT, Param4 OUT DATE) - this is the oracle store proc which needs to be run in the sql with the sqlserver, please helpAnonymoushttps://www.blogger.com/profile/15810654105338361810noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-33802192211204262292014-04-17T07:24:02.985-07:002014-04-17T07:24:02.985-07:00Hi , I have to call the storeproc with one input p...Hi , I have to call the storeproc with one input param and three output param.can any one help with me <br /><br />StoreProcName(Param1 IN VARCHAR2,Param2 OUT INT,Param3 OUT INT, Param4 OUT DATE) - this is the oracle store proc which needs to be run in the sql with the sqlserver, please helpAnonymoushttps://www.blogger.com/profile/15810654105338361810noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-80626652937361692402014-02-20T21:20:29.557-08:002014-02-20T21:20:29.557-08:00you can do this by using alias as below
declare @...<br />you can do this by using alias as below<br />declare @param1 varchar(15)<br />set @param1='value of parameter'<br />select * from openquery(linkedservername,'select * from oracleTable') a where a.id=@param1<br /><br />a.id is your column of oracle table.Uma Shankar Patelhttps://www.blogger.com/profile/02287010306811895923noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-9477322507041746162014-02-20T10:57:01.408-08:002014-02-20T10:57:01.408-08:00Hi can you please help me
Im trying to querying f...Hi can you please help me<br /><br />Im trying to querying from sql to Oracle database but I want to use parameters<br /><br />I declared the parameter @fecha in sql and I want to use it in the oracle select<br /><br />select * from openquery(linkserver,'select * from oracletable where fecha=@fecha')<br /><br />Is this posible? Any suggestions?<br /><br />I´ll really appreciated your help<br /><br />Anonymoushttps://www.blogger.com/profile/04287522592910077998noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-24858858850131519492014-02-20T10:55:08.527-08:002014-02-20T10:55:08.527-08:00Hi everyone
Im trying to querying from Sql Server...Hi everyone <br />Im trying to querying from Sql Server 2008 to a linked server in Oracle.<br />But I want to use sqlparameters <br /><br />For example, from sqlserver I´m trying<br /><br />select * from openquery(linkedserver,'select * from oracletable where fecha=@fecha')<br /><br />Is this posible? Any suggestions?<br /><br />I´ll really appreciated<br /><br />Anonymoushttps://www.blogger.com/profile/04287522592910077998noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-35451863370795600542014-02-20T10:51:12.895-08:002014-02-20T10:51:12.895-08:00Hi can you please help me
Im trying to querying f...Hi can you please help me<br /><br />Im trying to querying from sql to Oracle database but I want to use parameters<br /><br />I declared the parameter @fecha in sql and I want to use it in the oracle select<br /><br />select * from openquery(linkserver,'select * from oracletable where fecha=@fecha')<br /><br />Is this posible? Any suggestions?<br /><br />I´ll really appreciated your help<br /><br />Anonymoushttps://www.blogger.com/profile/04287522592910077998noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-62933729693565860042013-06-25T21:37:55.234-07:002013-06-25T21:37:55.234-07:00It is very good question.Let me explain with examp...It is very good question.Let me explain with example<br /><br />select <br />a.Customer_Number,<br />a.Customer_Name<br />from openquery(proderp,'select * from Apps.Customer_Deatils')a <br /><br />where Convert(varchar,a.Creation_Date,101)=Convert(varchar,GETDATE(),101)<br /><br />There are some important points as<br />1. a.Customer_Number ,Customer_Name are field of Oracle table that I am selected in "select * from Apps.Customer_Deatils".<br />2. You can use Oracle field in where clause as I used Creation date.<br />3. If you are comparing SQL Server date with Oracle date,You should CAST date,because sometimes it is not compatible to each other.<br />Uma Shankar Patelhttps://www.blogger.com/profile/02287010306811895923noreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-12289982827619665722013-06-23T23:30:56.323-07:002013-06-23T23:30:56.323-07:00Hi,
How to fetch the values from the linked sever...Hi,<br /><br />How to fetch the values from the linked sever based on some condition in where clause?For example to fetch all the values with Date as '24/JUN/2013' <br />I tried but it doesnt allow special chars in the where clause! Please helpAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-68672203849936803852013-06-23T23:26:16.339-07:002013-06-23T23:26:16.339-07:00VERY NICE POSTVERY NICE POSTAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-85170423876049647932013-03-18T07:40:09.075-07:002013-03-18T07:40:09.075-07:00This opens a HUGE security hole. Anyone accessing...This opens a HUGE security hole. Anyone accessing the data stored in Oracle accesses it under the security context of "username"Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1391695206712094920.post-66604168348181011402013-02-16T11:32:43.581-08:002013-02-16T11:32:43.581-08:00Just want to say your article is as amazing. Τhe с...Just want to say your article is as amazing. Τhe сlearnesѕ to <br />yοur роst is ѕimρly ѕpectacular and i can suppose <br />yоu are knowledgeable in thiѕ subϳеct.<br />Well alοng with youг permiѕѕіon let <br />me to snatch уour feed to keep updatеd wіth imminent рost.<br />Τhanks one millіon anԁ please carry on <br />the rewarding work.<br /><br />Looκ іnto my site :: <a href="http://www.youtube.com/watch?v=E_qpOqiuHUA" rel="nofollow">cheap rv rentals indiana</a>Anonymousnoreply@blogger.com