Creating a local SQL Server Compact Edition DB

Hi All ...

I'm setting up replication for the 1st time following the steps in the Books Online to get it up and running:

ms-help://MS.SSCE.v31.EN/ssmmain3/html/5a82aa7a-41a3-4246-a01a-2b1e4b2fdfe9.htm.

Anyhow, I get down to the section labeled - Create a new SQL Server Compact Edition database and am having a problem. I open SQL Server Management Studio as instructed. I click Connect and then am supposed to select SQL Server Compact Edition - yet can't find that option.

And, yes, I've installed Compact Edition on my development laptop. So, how do I get this option so that I can create the local DB for Compact Edition and continue development?

UPDATE:

Ok, duh moment, I have SQL Server Management Studio Express loaded on my laptop, so did not have the option. I've attempted to uninstall SQL Server Management Studio Express and install the full version from the SQL Server CDs. However, when I go to install the full version it tells me that the management tools are already installed and won't let me install anything. So, how do I get around this so that I can install full version of SQL Server Management Studio and then get the option to connect to the SQL Server Compact Edition?

Any help will be appreciated.

UPDATE 2:

Ok, figured it out. You can't just uninstall SQL Server Management Studio express, you must go in to Add/Remove Programs, SQL Server 2005, Change. Then select to uninstall the Workstation components. Then you can go use the SQL Server install discs to install the full blown SQL Server Management Studio along with the other Workstation components.

Thanks ...

David L. Collison

Any day above ground is a good day!

As a script-kiddie wannabe, I will suggest that you can use the following vbscript as well:

<job id="SqlServerCompactEditionDatabaseCreator">
<script language="VBScript">

Option Explicit

Dim strConnectionString
Dim oCatalog

strConnectionString = "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=" & WScript.Arguments(0) & ";"

Set oCatalog = WScript.CreateObject("ADOX.Catalog")
oCatalog.Create strConnectionString

</script>
</job>

Save the script to something like SqlServerCompactEditionDatabaseCreator.wsf, and you can create a SQL Server Compact Edition database with something like cscript SqlServerCompactEditionDatabaseCreator.wsf foo.sdf from the command prompt.

Then again, that is probably just my 2 cents

-Raymond

creating a local SQL DB

Does the MSDE 2000 allows you to create your own local
database like SQL 2000 developer edition would.
"manoa" <anonymous@.discussions.microsoft.com> wrote in message
news:003d01c4e3a1$6c41fc00$a501280a@.phx.gbl...
> Does the MSDE 2000 allows you to create your own local
> database like SQL 2000 developer edition would.
Absolutely.
Steve

Creating a local copy of a database

Hi all,

I'm having a problem right now. I'm trying to create an offline copy of a database(live) in my local machine. The database is maintained in a different server machine and I want a copy of the particular DB as an offline copy.

The problem is there's DTS feature in SQL Server 2000 but I don't find any such tool in SQL Server 2005 edition. Can anyone please guide me on how to do this process?

Thanks & Regards,

ThunderRock

hi thunder,

Dts is now known as SSIS and it is found on VS2005

for the solution.

1. you can detach the database and copy the datafiles and attach it to the destination servers

2. you can use the database import export wizrd

|||

Hi Joeydj,

Thanks for the reply.

Now I've already done that but I wanted to know if there was some other method by which the complete Db can be imported. Because if we go by that method, we don't get the stored procedures as well as triggers and functions present in the source DB. So I want to know if there is any method by which the SPs, triggers as well as functions can be imported. We can write a query script and do it manually. But is there any features provided in Sql Server 2005 by which we can do it directly?

Another thing I noticed in the new database is that the data which get imported into the local machine(i.e the target DB) doesn't reflect the dependencies of the DB Objects present in the DB. So how do we go about solving this issue also?

Can you please guide on this? Anyone who have done this before and have some idea, please guide.

Thanks,

ThunderRock

|||

ThunderRock,

I just completed setting up database replication scenario that uses an SQL2005 db as a 'gold standard', and replication to a subscriber to an SqlExpress database. The subscriber db can be used offline for inquires in our case. We use the database snapshot to provide inquiry services for the subscriber when there is a network or preferred application downtime.

With replication options setup appropriately, you can have the replicated database include whatever objects you wish. Our case is only using snapshot replication, as that is all we require, but from what I understand other types of replication would work in like manner.

We get our source data from a foreign host system as a huge text file, and we use a custom app to transform it, and bulk copy it into our Sql2005 db. Then, we've setup jobs to perform the replication. On the subscriber end, we installed SqlExpress with Advanced Tools, and set it up as a subscriber to the Sql2005 db. We developed a simple windows form application to use for inquiry on the local database, and deployed the app to the subscriber desktop. It is functioning as intended and allows our users to inquire on the mission critical data during those odd periods of network or preferred application downtime.

Now, there may be some issues causing the issues you mention, there are some limitations on the amount of data, and supported memory configs, but our db has about 500,000 records, which is not huge, and not very many stored procedures at this point. So, we have not run into any showstoppers at this point.