Linking Exchange Server to SQL server

I have SBS 2003 - Exchange and SQL Servers are on the same box.
I have managed to create linked database and can see Exchange Public Folders
in Linked database in Enterprise Manager.
Now the problem is how do a access / read the tables ?
There are a couple of examples in MSDN:
http://msdn2.microsoft.com/en-us/library/ms191461.aspx
For a more comprehensive list of objects, please see this :
http://msdn.microsoft.com/library/de...nces_oledb.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul,
When I try to run the sample query in Query Analyzer following error comes
up -
Server: Msg 7321, Level 16, State 2, Procedure Contacts, Line 3
An error occurred while preparing a query for execution against OLE DB
provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
ICommandPrepare::Prepare returned 0x80040e14].
Please advice if possible
Regards
Sudarshan Deshmukh
"Paul Ibison" wrote:

> There are a couple of examples in MSDN:
> http://msdn2.microsoft.com/en-us/library/ms191461.aspx
> For a more comprehensive list of objects, please see this :
> http://msdn.microsoft.com/library/de...nces_oledb.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Hello Paul,
If i run the following -
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM SCOPE(''shallow traversal of ".\Contacts"'')')
It comes up with last names...
I am trying to put emails in public folder and get them into SQL server
database.
Is their any simpler way of doing this or totally other way... ?
Please advice ?
Thanks for the showing the correct path in advance :-)
Sudarshan
"Paul Ibison" wrote:

> There are a couple of examples in MSDN:
> http://msdn2.microsoft.com/en-us/library/ms191461.aspx
> For a more comprehensive list of objects, please see this :
> http://msdn.microsoft.com/library/de...nces_oledb.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||If you want to avoid using the (fairly obscure ) syntax, take a look at
Microsoft Access - there's a 'Link Exchange / Outlok' wizard which looks
like it might be the easiest way. I can certainly connect to my own mailbox
but unfortunately not the public folders - no doubt this is an Exchange
permission issue. Alternatively it must be possible on the Exchange console
to output the data in csv format. I don't know Exchange well enough for
this. Probably best at this point to try posting in the Exchange newsgroup
who'll know this type of level of detail. Probably also checking on the
legality of obtaining other people's email
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Linking Exchange Server to SQL server

I have SBS 2003 - Exchange and SQL Servers are on the same box.
I have managed to create linked database and can see Exchange Public Folders
in Linked database in Enterprise Manager.
Now the problem is how do a access / read the tables ?There are a couple of examples in MSDN:
http://msdn2.microsoft.com/en-us/library/ms191461.aspx
For a more comprehensive list of objects, please see this :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wss/wss/wss_references_oledb.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hello Paul,
When I try to run the sample query in Query Analyzer following error comes
up -
Server: Msg 7321, Level 16, State 2, Procedure Contacts, Line 3
An error occurred while preparing a query for execution against OLE DB
provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
ICommandPrepare::Prepare returned 0x80040e14].
--
Please advice if possible
Regards
Sudarshan Deshmukh
"Paul Ibison" wrote:
> There are a couple of examples in MSDN:
> http://msdn2.microsoft.com/en-us/library/ms191461.aspx
> For a more comprehensive list of objects, please see this :
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wss/wss/wss_references_oledb.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||Hello Paul,
If i run the following -
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM SCOPE(''shallow traversal of ".\Contacts"'')')
It comes up with last names...
I am trying to put emails in public folder and get them into SQL server
database.
Is their any simpler way of doing this or totally other way... ?
Please advice ?
Thanks for the showing the correct path in advance :-)
Sudarshan
"Paul Ibison" wrote:
> There are a couple of examples in MSDN:
> http://msdn2.microsoft.com/en-us/library/ms191461.aspx
> For a more comprehensive list of objects, please see this :
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wss/wss/wss_references_oledb.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||If you want to avoid using the (fairly obscure :)) syntax, take a look at
Microsoft Access - there's a 'Link Exchange / Outlok' wizard which looks
like it might be the easiest way. I can certainly connect to my own mailbox
but unfortunately not the public folders - no doubt this is an Exchange
permission issue. Alternatively it must be possible on the Exchange console
to output the data in csv format. I don't know Exchange well enough for
this. Probably best at this point to try posting in the Exchange newsgroup
who'll know this type of level of detail. Probably also checking on the
legality of obtaining other people's email :)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .sql

Linking Exchange Server to SQL server

I have SBS 2003 - Exchange and SQL Servers are on the same box.
I have managed to create linked database and can see Exchange Public Folders
in Linked database in Enterprise Manager.
Now the problem is how do a access / read the tables ?There are a couple of examples in MSDN:
http://msdn2.microsoft.com/en-us/library/ms191461.aspx
For a more comprehensive list of objects, please see this :
http://msdn.microsoft.com/library/d...ences_oledb.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hello Paul,
When I try to run the sample query in Query Analyzer following error comes
up -
Server: Msg 7321, Level 16, State 2, Procedure Contacts, Line 3
An error occurred while preparing a query for execution against OLE DB
provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
ICommandPrepare::Prepare returned 0x80040e14].
--
Please advice if possible
Regards
Sudarshan Deshmukh
"Paul Ibison" wrote:

> There are a couple of examples in MSDN:
> http://msdn2.microsoft.com/en-us/library/ms191461.aspx
> For a more comprehensive list of objects, please see this :
> http://msdn.microsoft.com/library/d...ences_oledb.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||Hello Paul,
If i run the following -
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM SCOPE(''shallow traversal of ".\Contacts"'')')
It comes up with last names...
I am trying to put emails in public folder and get them into SQL server
database.
Is their any simpler way of doing this or totally other way... ?
Please advice ?
Thanks for the showing the correct path in advance :-)
Sudarshan
"Paul Ibison" wrote:

> There are a couple of examples in MSDN:
> http://msdn2.microsoft.com/en-us/library/ms191461.aspx
> For a more comprehensive list of objects, please see this :
> http://msdn.microsoft.com/library/d...ences_oledb.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||If you want to avoid using the (fairly obscure ) syntax, take a look at
Microsoft Access - there's a 'Link Exchange / Outlok' wizard which looks
like it might be the easiest way. I can certainly connect to my own mailbox
but unfortunately not the public folders - no doubt this is an Exchange
permission issue. Alternatively it must be possible on the Exchange console
to output the data in csv format. I don't know Exchange well enough for
this. Probably best at this point to try posting in the Exchange newsgroup
who'll know this type of level of detail. Probably also checking on the
legality of obtaining other people's email
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

server with IBM DB2 UDB for iSeries IDMDASQL OLE Provider Errors

Hello Everyone

I’m having problems retrieving data from IBM DB2 AS/400 linked server

I have managed to connect a linked server using the IBM DB2 UDB for iSeries "IBMDASQL" OLE DB Provider.

But when I run a query to the database I get only the field names and the following errors:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "iseries" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "iseries".

My Server is Standard 64bit edition on cluster with CTP 2 installed .

I don’t have the right to use Microsoft OLE DB provider …..?

Thank you.

Firstly check to correct the Access Denied error and ensure the user you are using to COnnect to IBM DB2 should have access.

Also check this http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?forum=292&thread=143817&cat=5 link on IBM forums.

|||

Hello Satya

Thank you for your time.

I had my DB2 user tested with a non cluster 32bit installation.

I already posted my case to mentioned IBM link.

My problem is that I can’t use a Local system account for my 64bit clustered environment.

After executing a simple select query against the linked server I get an empty result set at the result tab with all table fields included and the errors

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "GRATHD1" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "GRATHD1".

at the messages tab.

Thank you

|||

I would like to know how you setup the link. I have tried repeatedly to get a link for AS/400 to work, but can't seem to find the proper combination of parameters. Searching the internet has provided no help to me.

Thanks

|||

Hello Lee

I'm posting the script produced by my linked server

Notice that if you set the SQL Server service account to "local system" the linked server works.

/****** Object: LinkedServer [GRATHD1] Script Date: 01/30/2007 11:15:54 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'GRATHD1', @.srvproduct=N'IBM DB2 UDB for GRATHD1', @.provider=N'IBMDASQL', @.datasrc=N'GRATHD1'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'as400username',@.rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=N'DomainName\UserName',@.rmtuser=N'AS400username',@.rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'use remote collation', @.optvalue=N'true'

|||

you musto add the options

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

|||

PS to the Microsoft people

When the server is running under the local system account and not a domain user this option is not necessary.