picsnet
asked on
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
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
ASKER
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
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
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 ...
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 ...
ASKER
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
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
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.S
Or online:
http://technet.microsoft.com/en-us/library/ms172437.aspx
Hope this helps ...
Do you have BOL installed? Check this:
ms-help://MS.SQLCC.v9/MS.S
Or online:
http://technet.microsoft.com/en-us/library/ms172437.aspx
Hope this helps ...
ASKER
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.
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.
Thought of replication ?
in BOL:
ms-help://MS.SQLCC.v9/MS.S
on line:
http://technet.microsoft.com/en-us/library/ms172367.aspx
Hope this helps ...
in BOL:
ms-help://MS.SQLCC.v9/MS.S
on line:
http://technet.microsoft.com/en-us/library/ms172367.aspx
Hope this helps ...
ASKER
yeah, same thing... requires workgroup edition
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can try linked databases
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
ASKER
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.
i think it would allow that, but i'm not quite sure what the syntax would be.
run it on the server
why delete the link?
why delete the link?
ASKER
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
i've been looking for an example like this on the internet of linked servers to pocketpcs and i can't find any
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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.
I misunderstood your DB setup
sorry.
sorry.
Glad I could be of any help and thanks for the grade !
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.Compa
Set col1 = ET.col1,
col2 = ET.col2,
col3 = ET.col3
from schema.ExpressTable ET
Where ET.LastModified > Comp.database.schema.Compa
... so you are pretty close. Just the linked server and your there ...
Hope this helps ...