Joining 2 SQL Server Compact Edition 3.5 databases

Thomasian
Thomasian used Ask the Experts™
on
How can I create a query/connection string to join 2 SQL Server CE 3.5 databases?

TIA
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
what do you mean by "join"? Are you trying to create a linked server? Check this link for the connection string syntax for SQL CE.
http://www.connectionstrings.com/sql-server-2005-ce 
More info on how to create linked servers here:
http://msdn.microsoft.com/en-us/library/ms190479.aspx
 

Author

Commented:
I am trying to create a query with tables from 2 SQL Server CE databases.

I cannot create link servers because there will be no instance of SQL Server running.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples


Have you tried ?
select * from server.database.owner.table a                               --(usually owner is dbo)
inner join server.database2.owner.table2 b on a.id = b.id
 

Author

Commented:
I am using SQL Server CE so there is no server. And the database is a file, so I need to specify the file name and password for each database. Maybe on the connection string?
I see. . Just curious, is it possible for you to use SQL express instead?
Anyhow, here are two links that I think will help you creating the connection to the databases:
http://www.c-solutions-inc.com/articles/SQLServer2005CompactEditionTutorial_CRUD--2007-09-24--Edition.pdf
http://erikej.blogspot.com/2008/10/accessing-sql-compact-from-sql-server.html 
And of course use the first link I've suggested. (connectionstring.com, see my first comment)
 

Author

Commented:
I have never really tried SQL Server Express, so I'm not familiar with it.

In SQL Server Express, will I need to have a database administrator to manage the database? Because in CE, the database is completely standalone and I can manage everything through code.


Actually, I know how to connect to a single sql server ce database. But I can't seem to find any documentation/samples that shows how to connect to 2 databases at the same time.
Well, SQL express is designed for small businesses, but it has more capabilities than CE. It really doesn't require a lot of maintenance. I think you should read this whitepaper for more details.
http://74.125.95.132/search?q=cache:d_m3Moe5z4cJ:download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE638EBE/Compact_Express_Comparison.doc+sql+express+vs+compact&cd=1&hl=en&ct=clnk&gl=ca 
From the above link, leep this in mind: "SQL Server Compact Edition is designed to meet the needs of applications where it is important to embed a lightweight data engine directly into the application."  So when you have two databases maybe it's time for you to think on having a more robust database engine such us Express edition, or consider having all your data in one database or create a server to syncronize all your data. Just my opinion.
>> But I can't seem to find any documentation/samples that shows how to connect to 2 databases at the same time. <<
Worst case scenario, remember that you can work with both recordset programmatically using functions such us Seek.
 

Author

Commented:
I have posted a new question to help me decide which version of SQL Server to use. Thanks for your help on this.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_25105405.html?fromWizard=true

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial