Querying a linked DB Through ASP.NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Querying a linked DB Through ASP.NET

Author

Topic

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-09-08 : 03:09:03

Guys ...

I have a SQL SERVER 2005 running on my local machine. My local machine is also configure for web applications through IIS.

There is a remote oracle db, which is maintained by another department. They gave us read only access but they discourage web access over their oracle database.

Even if you try to access it through web ... they come to know that certain logins are trying to access it through web ... How ... i dont know.

Now what i did is linked that oracle database with my SQL server 2005 and developed an ASP.NET application which is indirectly accessing oracle database though my SQL Server 2005.

Whenever a user visits my web application on the backend the authentication done on my sql server but the query runs on linked oracle server... as I open connection to my sql server 2005

do you guys think that Oracle database administrator can ever know that his oracle db is access through a web application. whereas they allow us to copy required data on our machines through SQL Server 2005?

Kristen
Test

22859 Posts

Posted - 2007-09-08 : 05:05:03

Is the data you need on the Oracle database huge, or fast changing?

We prefer to have our associated Oracle data hosted in our SQL Server database on the web, and we "pull" changed data from the Oracle database every 10 minutes, so that we have local, relatively up-to-date, data locally.

(Actually, they used to use a direct-connect to Oracle, and the extra abstraction and lack of bandwidth meant that performance became an issue, plus the link from Web (hosted on the backbone) to Oracle (hosted in the office) could fail if the telecomms were disrupted, e.g. my major storms.

Kristen

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-09-08 : 05:16:30

Thanks Kristen for your fruitful reply.

If i choose the option to "pull" changed data lets say after an hour ... what i suppose to do. should I schadule a SSIS package to do the task or there is any other way to do it.

How can i identify the change data? the data on remote Oracle machine is maintained by DATE. If i work on DATE criteria then I have to pull it after 24hours ... and this will not work.

Thank in advance.

Kristen
Test

22859 Posts

Posted - 2007-09-08 : 05:34:40

Yeah, we were probably lucky in that the oracle database we were pulling from had a ChangedDate (for some bizarre reason stored as an INT, keep that in mind when looking at my example below pls) on every table.

So we check the most recent changed date we had locally, and then use that to get more recent records only:


SELECT @CHANGE_DATE = MAX(CHANGE_DATE)
FROM MyLocalTable

SELECT @strSQL = 
'SELECT *
INTO	##TEMP_TABLE
FROM	OPENQUERY(ORACLE_SERVER, 
''SELECT *
FROM
(
SELECT
	COL1,
	COL2,
	...
FROM	ORACLE_SERVER.ORACLE_TABLE
WHERE	    CHANGE_DATE >= ' + CONVERT(VARCHAR(20), @CHANGE_DATE) + '
ORDER BY CHANGE_DATE
	, PkCol1, PkCol2, ...
) X WHERE ROWNUM <= 5000
'')'
-- SELECT [SQL] = @strSQL -- Debug only - e.g. to review the SQL generated
EXEC (@strSQL)

note that this pulls a maximum of 5,000, but in date and PK order, so will "resume" next time. Only issue with this is if there are MORE than 5,000 rows with the SAME most-recent ChangedDate (in which case something more sophisticated is needed)

The we update our LocalTable from the ##TEMP_TABLE (Delete "missing" records, Update existing records and Insert new records)

Kristen

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-09-10 : 01:10:20

Thanks I will try it and let you know if it works ... but I am sure it should work ... Any way thanks a lot ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources