SQL Server 2008 OLE Automation Issues

Hi all,

I have a program here which posts data directly to the tables of an SQL database via OLEDB. In SQL Server 2005 this worked perfectly when OLE Automation was turned on for the server and didn't miss a beat.

However now we have SQL Server 2008 and when I turn on OLE Automation it still fails. The error I am getting is session not opened so it appears  that some security features still need to be changed or switched off. Has anyone got any ideas as to what may be causing the session to fail opening?

Thanks
auschocAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

macentrapCommented:
Ole Automation in SQL Server 2008 by default is turned off for security reasons. sp_OACreate will start the OLE shared execution environment.

Try this pls!

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
0
macentrapCommented:
this could also be the issue with SOAP kit

and MS XML version --- for 2008 you would require version 6
0
auschocAuthor Commented:
macentrap would that be on the client comptuer or the server computer?

i have tried the first solution without success :(
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

macentrapCommented:
first one will be on server as that where it is turned off.

What error you get there? .....is it still session not open




for XML and SOAP the environment have to be same on server and client.

in program do you also have reference to XML version ???
0
auschocAuthor Commented:
hmmm i am not sure that SOAP and XML apply in this instance. Just to give you a heads up as to how the system works

In the registry we setup a string for the program to use for its SQL DB connection

The string is like this

Provider=SQLOLEDB;Server=Testing\SQLEXPRESS2;Uid=sa;Pwd=testing;Database=database

that string is then used within the code to connect to the SQL server and post data when a 'post' button is hit within the product. Would this still be XML/SOAP based? There is no web services involved.
0
macentrapCommented:
also you please update the program you talking about , whats it coded in ?
0
macentrapCommented:
sorry missed your comment, didnt updated...


no it does  not apply on this .

0
macentrapCommented:
are you using 64 bit or 32 bit SQL  and version please
0
macentrapCommented:
please add

Provider=SQLOLEDB;Server=Testing\SQLEXPRESS2;Uid=sa;Pwd=testing;Database=database; Trusted_Connection=True
0
auschocAuthor Commented:
Thanks i'll try that and let you know how it goes :)

FYI program is coded in C# with some C++ legacy code.

both 64 bit and 32 bit (We support both) 2008 SQL R2 (Express and Full)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
auschocAuthor Commented:
Winner :) Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.