Easy question for experienced developers

How do I add a previously-made SQL Compact Edition database to my Visual Basic.Net 2005 project inside Visual Studio, and ensure that it gets built into the ClickOnce-deployed solution? Our dba has completed the db design for our project, and I need to add the file to the project I am writing business logic for.

I've been writing software for about 15 years but just now using SQL CE for the first time. I hope my questions are not too simplistic for this forum, but I ran into a problem with something so simple that I'm at a loss, and I thought I'd throw it in this newbie thread.

To wit, what is wrong with this syntax:

INSERT INTO refState(StateName)
VALUES('Alberta')
INSERT INTO refState(StateName)
VALUES('Alaska')

This is a snippet from a T-SQL script I've been using for years that populates reference tables for things like states, countries, etc.

When I try to run the query in Visual Studio 2005 Pro while I have my refState table open in the IDE, I receive the following error:

'Unable to parse query text'.

Then I say to myself, What? Why? So I try to verify the SQL syntax, and then I receive this new error:

'This command is not supported by this provider.'

On the surface it looks like ANSI SQL is out the window. So, I'm guessing I'm missing some fundamental understanding.

Little help? thanx

|||

Well, consider that the Compact Edition is not SQL Server and does not understand TSQL. The problem with your SQL is that the SQLCe engine can process one (and only one) SQL statement at a time. It does not understand the concept of multiple operations or resultsets as supported in TSQL.

In my EBook, I show an example (and a class) used to import schema files that can be used to create a SQLCe database and populate the schema. Gettting data into the database is also a challenge as SSIS does not work well with SQLCe (yet). One approach is to setup Merge Replication with a SQL Server Workgroup (or better) engine, use RDL or the new (unreleased) ADO Synchronization Services. Sure, you can write your own INSERT loop to import data and this would not be that hard to do... code-intensive but doable.

hth

See www.hitchhikerguides.net

|||

Thx for the feedback Bill.

Also, I found what we needed as far as setting up private ClickOnce deployments (which is what we're using) at the following URL:

http://msdn2.microsoft.com/en-us/library/bb219482.aspx

Dynamic Tables Names and Temporary Tables Options

Firstly I consider myself quite an experienced SQL Server user, and
am
now using SQL Server 2005 Express for the main backend of my
software.

My problem is thus: The boss needs to run reports; I have designed
these reports as SQL procedures, to be executed through an ASP
application. Basic, and even medium sized (10,000+ records) reporting
run at an acceptable speed, but for anything larger, IIS timeouts and
query timeouts often cause problems.

I subsequently came up with the idea that I could reduce processing
times by up to two-thirds by writing information from each
calculation
stage to a number of tables as the reporting procedure runs..
ie. stage 1, write to table xxx1,
stage 2 reads table xxx1 and writes to table xxx2,
stage 3 reads table xxx2 and writes to table xxx3,
etc, etc, etc
procedure read final table, and outputs information.

This works wonderfully, EXCEPT that two people can't run the same
report at the same time, because as one procedure creates and writes
to table xxx2, the other procedure tries to drop the table, or read a
table that has already been dropped...

Does anyone have any suggestions about how to get around this
problem?
I have thought about generating the table names dynamically using
'sp_execute', but the statement I need to run is far too long
(apparently there is a maximum length you can pass to it), and even
breaking it down into sub-procedures is soooooooooooooooo time
consuming and inefficient having to format statements as strings
(replacing quotes and so on)

How can I use multiple tables, or indeed process HUGE procedures,
with
dynamic table names, or temporary tables?

All answers/suggestions/questions gratefully received.

Thanksbrstowe wrote:

Quote:

Originally Posted by

How can I use multiple tables, or indeed process HUGE procedures,
with
dynamic table names, or temporary tables?

Isn't this your answer right here? Just CREATE TABLE #foo instead
of CREATE TABLE foo, etc. and each process will get their own local
version of the #foo table.|||On Oct 5, 7:07 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

brstowe wrote:

Quote:

Originally Posted by

How can I use multiple tables, or indeed process HUGE procedures,
with
dynamic table names, or temporary tables?

>
Isn't this your answer right here? Just CREATE TABLE #foo instead
of CREATE TABLE foo, etc. and each process will get their own local
version of the #foo table.

please be honest and tell me if I have completely misunderstood...?
the temporary tables are session/process/user specific...

two users could essentially create/drop tables, and populate/delete
information from tables with exactly the name e.g. #temptable1 at
exactly the same time?

thanks