Feeds:
Posts
Comments

Archive for June, 2008

One really handy feature of Microsoft Sql Server is the ability to link to non-Sql Server databases, such as Oracle.  This allows you to write simple queries in Sql Server against the other database.

For example, to link to Oracle, you would use the following commands:

EXEC sp_addlinkedserver   ‘ServerAlias‘,  ‘Oracle’,  ‘MSDAORA’,  ‘TNS_NAME’
EXEC sp_addlinkedsrvlogin ‘ServerAlias‘, ‘FALSE’, NULL, ‘OracleId, ‘OraclePwd’

Then, in theory, you should be able to write queries like the following in Sql Server Management Studio:

select * from [ServerAlias]..[OracleSchema].[TableName]

When I first tried this, the following error message was given:

Msg 7319, Level 16, State 1, Line 1
The OLE DB provider “MSDAORA” for linked server “XYZ” returned a “NON-CLUSTERED and NOT INTEGRATED” index “TABLE_IDX2” with the incorrect bookmark ordinal 0.

The solution to this problem is to force the Oracle Indexes to be ignored by changing the “Index as access path” option on the MSDAORA provider (not the link).

image

 

The next problem I ran into was:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider “MSDAORA” for linked server “XYZ” supplied inconsistent metadata for a column. The column “NUMBER_FIELD” (compile-time ordinal 39) of object “”SA”.”TABLENAME”” was reported to have a “DBTYPE” of 130 at compile time and 5 at run time.

Microsoft’s proposed solution to this problem requires recreating the tables on oracle to fix the inconsistency.  But what if you only have read only access to the Oracle server?

The workaround is to use the OPENQUERY function to execute the sql in pass through-mode on Oracle.  So, if you rewrite the query as follow, the inconsistency error goes away:

SELECT * FROM OPENQUERY(ServerAlias, ‘SELECT * FROM SA.MYBADTABLE’).

Two other important points:

  • The Oracle table names must be upper ciase.
  • The queries should be relatively simple.  The optimization of these linked queries is relatively limited so keep it simple!

Hope this helps.

Read Full Post »

Looks simple enough but it took a few tries to get the SMTP servings right for a “Google for Domains” account:

image

Remember that Google limits each mail account to sending 500 messages a day to foil spammers.  If you have a higher volume than that, then you will have to look elsewhere for an SMTP server.

As a bonus, when you use GMail for your SMTP server you can see the history of emails sent from DotNetNuke in your GMail Sent folder.   This really helps with debugging email notification issues.

For example, I could never figure out where the Test messages were going until I saw them in my Sent Folder.  By looking in the Sent folder, it was very obvious that they are sent to the Host’s Email address which I still had set to the default value of support@portal_domain.com.

image 

Let me know if this helps you out.

Read Full Post »