How to connect CF to a Linked Server defined in SQL Enterprise Manager

I have a linked server setup in Enterprise Manager that I want to connect to via Cold Fusion.  Does anyone know if a way to defined this type of Data Source in CF Administrator?  The linked server is an AS400 that I am not allowed to directly access via the web.  I do however have access to SQL server, which in turn has access to AS400 via OLEDB Provider.  Any comments welcome ... sulzener
sulzenerAsked:
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.

mrichmonCommented:
Well one option I can think of off the top of my head is that you can set up a datasource to any database on the server.  Then use a stored procedure that uses the OPENQUERY syntax to access that linked server.

I don't know if this is the most efficient way, but the first thing that came to mind for now...

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
mrichmonCommented:
Well one option I can think of off the top of my head is that you can set up a datasource to any database on the server.  Then use a stored procedure that uses the OPENQUERY syntax to access that linked server.

I don't know if this is the most efficient way, but the first thing that came to mind for now...
Tacobell777Commented:
you can just do

SELECT *
FROM LinkedServerName.Database.Table

in a cfquery
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Tacobell777Commented:
you can just do

SELECT *
FROM LinkedServerName.Database.Table

in a cfquery

make sure you have the right permissions setup
mrichmonCommented:
I sometimes had that not work.... but if it does it is definately a better way to go...
sulzenerAuthor Commented:
Do I need to setup a Data Source in Administrator before I do the SELECT?  I am able to use SQL Server Query Analyzer to do queries on the Linked Server.  Here is a sample syntax that works in Query Analyser: SELECT * FROM OPENQUERY (LinkedServerName, 'SELECT * FROM AS400FileName)
sulzenerAuthor Commented:
BTW, I tried to create a Data Source to the linked server, but it will not Verify!
CF Data Source Name: AS400SQL
Database: LinkedServerName
Server: IPAddress of SQL server
Username and Password: ? - tried both AS400 User/Pswd and SA User/Pswd (neither would work)
Tacobell777Commented:
you should be able to use any datasource, no matter where it points.
sulzenerAuthor Commented:
How do I get it to verify?  Do you have any suggestions?
Tacobell777Commented:
verify?
The datasource?
Don't you have any existing datasources? If so, just use those in the connection but use the SQL as per above.
sulzenerAuthor Commented:
I'm sorry but that doesn't make sense to me.   To answer your question:
Yes.  I have several datasurces connected to databases on the SQL server box.  This is the first time I tried to query to a Linked Server defined in SQL from CF.  Are you saying to use one of my other datasources connecting to the SQL box, but to use the cfquery on the Linker Server?  Can you give me a simple sample.  Maybe you are talking about a way to connect to SQL Linked Srver in CF w/o using the defined data source in Administrator?  If so, I do not know how to do that!
mrichmonCommented:
You just need the datasource to connect to any database (a physical one not the linked one) on the SQL server.

Then run the same OPENQUERY you used in query analyzer using cfquery and the datasource you created.
sulzenerAuthor Commented:
You guys were right.  It worked.  I didn't realized you could do that.  Curious question to mrichmon.  When you said that it sometimes didn't work ... Did you mean it worked on and off intermittantly?  Or that the SELECT would sometimes not work at all.  I am wondering if I go this route if it is going to me problematic for me.  What do you think?  I will split points to both you guys.  Thanks.
mrichmonCommented:
>>Did you mean it worked on and off intermittantly?  Or that the SELECT would sometimes not work at all.
Isn't "sometimes not work at all" the definition of inermittant?
sulzenerAuthor Commented:
Sorry.  I stink at typing.  My grammer is worse.  I meant sometimes (as in intermittantly) and NOT AT ALL.  I guess I wanted to know what you meant by "sometimes had that not work"?
mrichmonCommented:
The times I had it not work it didn't connect at all.  But in general I use the OPENQUERY since that never failed so I can't say if the other is intermittant or not.  Maybe Tacobell has used that method more.

Note that depending on the type of linked server you may need
LinkedServerName.Database.Table to include schema or owner in the scope reference as well
Tacobell777Commented:
it never failed for me, I dont see any reason why it should.
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
Web Servers

From novice to tech pro — start learning today.