?
Solved

Sql Express to Sql Compact Query

Posted on 2007-10-15
18
Medium Priority
?
323 Views
Last Modified: 2013-11-17
Hello, I'm looking to see if this is possible, and if it is... What the syntax for it is...

I'm syncing between Sql Compact and Sql Express.  It is a very simple syncing scenario... Very Very Simple!!!

But what I want is something like this displayed in pseudo:

Update [Sql Compact Database.Table]
Set col1 =  ExpressTable.col1,
      col2  = ExpressTable.col2,
      col3  = ExpressTable.col3
Where ExpressTable.LastModified > SqlCompact.LastModified

As you can see, I have no idea about how to make the connections, and the update statement is no where near syntactically right.   I think you can understand what I'm trying to do though.  

I want to update all rows on the sql compact side that have been modified on the sql express side.  

I think the example for this is probably only about 10 lines of sql long if you know how to do it.  I just need to find someone who knows how to do it, and I bet someone here does.  

Tony
0
Comment
Question by:picsnet
  • 8
  • 6
  • 4
18 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20081043
I don't know if you can create a linked server on a SQL Server Express edition, I think you can ...
So create a linked server to the SQL Compact edition (lets call it Comp) and then from the SQL Express do:

Update Comp.database.schema.CompactTable
Set col1 =  ET.col1,
      col2  = ET.col2,
      col3  = ET.col3
from schema.ExpressTable ET
Where ET.LastModified > Comp.database.schema.CompactTable.LastModified

... so you are pretty close. Just the linked server and your there ...

Hope this helps ...
0
 
LVL 1

Author Comment

by:picsnet
ID: 20081127
Umm, I've never created a linked server before.  I think I may have problems creating one to a sqlcompact considering that you have can any number of devices.  

Is this possible at runtime, or something that has to be set up beforehand.  Could the SqlExpress location be static, and the Compact location be dynamic.  

Tony
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20081188
If there is a way you can connect to the compact edition, you can create a linked server for that.
Can you define a ODBC datasource to connect to the compact edition ? Then use that system DSN to create a linked server with. As long as you do not connect to the linked server as it is not available, there will not be any problem. Otherwise you would receive a connection error ... duh :-)
So ODBC looks like a good option to me ...

Hope this helps ...
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Author Comment

by:picsnet
ID: 20081287
Would that really work.  If you bought 1000 devices, would you have to create 1000 data sources.  When I've made a datasource, it generally has been to a database in a certain location.  I'm not sure if you can do one that is more dynamic than that or not.  

The compact can come online in many different forms, bluetooth, wireless, connected to the pc.  It probably has some sort of address that stays the same, but again... you don't want to have to configure it for each device individually.  

Everything will be initiated from the handheld so I will know the consolidated address, and I will know that pocketpcs local database address.  So I will have both pieces of the puzzle.  I'm still just not completely sure this linked server can be done at runtime like I'm wanting from what you have said.  

You've given a lot of good information... I'm just not sure I will be able to use it yet.  I need to know more about the linked server, and my options, and any other options I may have.  

Tony

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20081368
It would be more convenient to write the code for the compact edition, because the SQL Server Express will always be reachable on the same address.

Do you have BOL installed? Check this:
ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMProg3/html/a776a75b-5aa8-4a7b-a154-5806f3b615f8.htm
Or online:
http://technet.microsoft.com/en-us/library/ms172437.aspx

Hope this helps ...
0
 
LVL 1

Author Comment

by:picsnet
ID: 20081528
I saw that.  I was just reading up on it.  I saw somewhere in a forums where one of the moderators at msdn couldn't get the cross-database stuff to work.  He said to use rda.  I was looking at rda.  It requires a minimal of workgroup edition to publish.  Unfortunately, that might be an issue.   We sell to a lot of smaller places, and the program definitely doesn't require anything more than express, and an additional $739 added to the price tag is pretty substantial.  Most of these places aren't going to have a full version of sql.  

I know that I can query from the consolidated, put it in a dataset, and then push it into the compact... but I'm not sure if this is much much slower, or if it is the best thing to do.  

It may be my only path at this moment.  
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20081581
Thought of replication ?

in BOL:
ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMMain3/html/ba165b7b-f973-4654-89a2-159e1e2cbb9f.htm
on line:
http://technet.microsoft.com/en-us/library/ms172367.aspx

Hope this helps ...
0
 
LVL 1

Author Comment

by:picsnet
ID: 20082140
yeah, same thing... requires workgroup edition
0
 
LVL 18

Accepted Solution

by:
Yveau earned 1600 total points
ID: 20083300
... options are pretty limited then ...
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 20084930
0
 
LVL 1

Author Comment

by:picsnet
ID: 20085908
i would like to try the linked database think but i'm not sure how to do it.  I would have to call that stored procedure remotely from the pocket pc to create a linked server to that pocket pc... then i guess i'd delete it when the app closed.  

i think it would allow that, but i'm not quite sure what the syntax would be.  
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 20085945
run it on the server
 why delete the link?
0
 
LVL 1

Author Comment

by:picsnet
ID: 20085992
its pocket pc... its going to come on at a different address everytime.  i can't just create a linkedserver to something that is connected in 3 different ways, and is only connected part of the time.  it would have to created remotely from the pocket pc, and i don't want to just create them everytime and leave them.  you should dispose of it.  

i've been looking for an example like this on the internet of linked servers to pocketpcs and i can't find any
0
 
LVL 48

Assisted Solution

by:Mikal613
Mikal613 earned 400 total points
ID: 20086200
If you have access to the Server Database then you can just execute the sp_addlinkedserver from the PPC
0
 
LVL 1

Author Comment

by:picsnet
ID: 20086229
i know that, but i don't know the syntax to refer to the pocketpc i'm on.... i've never did a linked server before, much less doing it this way.  i think i have an idea based on that file, but i'm not sure... i've been trying to find an example
0
 
LVL 1

Author Comment

by:picsnet
ID: 20086463
SQL Server CE has a subset of SQL Server functionalities; its database engine is smaller, too. This was accomplished by leaving out functionality like the SQL Admin service. This was inevitable because since there is no such thing as a service in Windows CE, there are no agents, jobs, alerts, operators, or schedules to administer. Since SQL Server CE is a single-user product, there are no linked servers, and since its security is controlled by a file password, there is no need for GRANT, DENY, or REVOKE. Also missing are SQL Server's Transact-SQL extensions; there are no stored procedures, triggers, multi-statement batches, DECLARE, SET, IF, WHILE, string functions, numeric functions, or niladic (no-argument) functions.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 20086475
I misunderstood your DB setup
sorry.

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20087488
Glad I could be of any help and thanks for the grade !
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question