• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Sql Express to Sql Compact Query

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
picsnet
Asked:
picsnet
  • 8
  • 6
  • 4
2 Solutions
 
YveauCommented:
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
 
picsnetAuthor Commented:
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
 
YveauCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
picsnetAuthor Commented:
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
 
YveauCommented:
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
 
picsnetAuthor Commented:
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
 
YveauCommented:
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
 
picsnetAuthor Commented:
yeah, same thing... requires workgroup edition
0
 
YveauCommented:
... options are pretty limited then ...
0
 
Mikal613Commented:
0
 
picsnetAuthor Commented:
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
 
Mikal613Commented:
run it on the server
 why delete the link?
0
 
picsnetAuthor Commented:
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
 
Mikal613Commented:
If you have access to the Server Database then you can just execute the sp_addlinkedserver from the PPC
0
 
picsnetAuthor Commented:
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
 
picsnetAuthor Commented:
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
 
Mikal613Commented:
I misunderstood your DB setup
sorry.

0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 8
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now