Daily Digest: Oracle’s Bridge to Salesforce.com, WinJS’ Next Step, Entity Framework 6, and more…


good2know_banner

Oracle Builds a Bridge to Salesforce.com with New Adapter: 
The adapter allows organizations to synchronize Salesforce.com and on-premises applications
View article…

Security Risk in Starbucks App a ‘Wakeup Call’ for Consumers: 
The weak protections for customer data in Starbucks’ mobile-payment app is a “wakeup call” for consumers who should never assume the apps they use in their smartphones are secure.
View article…


good2read_banner

WinJS Takes the Next Step: 
Eric Schmidt explores error handling in the Windows Library for JavaScript, also known as WinJS.
View article…

Code First Goodies in Entity Framework 6: 
Julie Lerman rounds out her coverage of Entity Framework 6 with a discussion of new Code First features relating to mappings and migrations.
View article…

Entity Framework and slow bulk INSERTs: 
View article…


good2watch_banner

Windows Azure Scheduler: 
The Windows Azure Scheduler allows you to invoke actions – such as calling HTTP/S endpoints or posting a message to a storage queue – on any schedule. With Scheduler, you create jobs in the cloud that reliably call services both inside and outside of Windows Azure and run those jobs on demand, on a regularly recurring schedule, or designate them for a future date.
View article…

MakerBot CEO Bre Pettis Discusses new 3D Printers: 
In this video I talk with MakerBot CEO about the exciting new Windows 8.1 compatible 3D printers announced at CES 2014.
View article…


Follow-me-on-twitter

Good2Know: Changing your Oracle password via stored procedure

As I was connecting to Oracle today I got a warning saying that my password would expire within 10 days.  What threw me off was that it suggested I’d change it using the PASSWORD command within SQL*Plus.  I’ve always thought of SQL*Plus of being a DBA type tool and I’ve always used Oracle SQL Developer instead (not the best of tools but it gets the job done).  I knew there had to be a way that you could do this from within Ora Sql Develper, and there was!  The answer is below.

alter user username identified by “oldpassword” replace “newpassword”

The advantage of this approach is that you could potentially handle it from within a store procedure.  Now I know what you are saying, that this could be a security issue and depending on how it is implemented I would agree.  Nevertheless, I would say that being able to do it without having to install yet another tool is a pretty cool thing.

Yet another simple approach to a simple need.

Good2Read: ACCESSING ORACLE DATA THROUGH SQL SERVER 2005 (LINK ORACLE TO SQL SERVER 2005)

I recently had the need to access data in Oracle from SQL Server 2005.  I was surprised to find out that there weren’t that many places in the web that would explain how this is done.  I had to go through post after post gathering crumbs of information until I finally got it to work.

The concept is rather simple:

  • You link the Oracle server
  • You then query it like you would any other linked server
    • select * from [ServerName].[TableName]

The problem, however, is the gotchas along the way.

There are two stored procedures that help in the first step of linking the servers: sp_addlinkedserver and sp_addlinkedsrvlogin.  They do what their name implies, one creates the server record in the system table and the other one creates a log in with which you will be accessing the linked server’s data.

The stored procedure:

Create PROCEDURE [dbo].[Create_ServerLink]
@ServerName varchar(50),
@Schema varchar(50),
@Database varchar(50)
@UserName varchar(50,
@Password varchar(50)
AS
BEGIN

/*
ServerName: the name of the linked server.  It is the name to be used in the statements to call data from oracle.
Schema: the name of the schema
Database: the name of the database
*/

declare @ServerExits bit

set @ServerExits = (select count(*) from sys.sysservers where  srvname = @ServerName)

if @ServerExits = 0
begin
EXEC sp_addlinkedserver @ServerName  ,  @Database,  ‘OraOLEDB.Oracle’,  @Database
EXEC sp_addlinkedsrvlogin @ServerName, ‘FALSE’,NULL, @UserName, @Password
end

END

Once linked you would access the server in the following manner:

select * from LinkedServerName..SchemaName.TableName

So if your linked server is called OracleData, the Schema name is Orion and the table name is Products then you would have the following:

select * from OracleData..Orion.Products

Notes:

  • You can link a server by going to the Server Objects\Linked Servers in SQL Server Management Studio, but I think this way is much better because you can add this stored procedure to any other stored procedure that uses oracle data.  This is specially useful in an evironment where schemas are added constantly (like where I work).
  • At first I used the MSDAORA provider which ended up being dirt slow.  After doing some research I found that the OraOLEDB.Oracle driver is much faster.

Remember to let me know if this helped you in any way!