Derived Table Alternative

I have an application that has two different database backends, one is SQL Server Compact Edition and the other is SQL Server. The reason is because the application may run at home on one of our sales agent's computers or here in the office.

I have a query that uses a derived table and works just fine in SQL Server, however when I run it in the compact edtion (having the exact same table structures) it will not run. My question is...does the Compact Edtion or the Mobile Edition allow derived tables. If not is there a way to work around this? I will happily give an example if it will help.

Thank you,

Adam

Hi Adam,

a sample would be very useful, thanks.

|||

The three tables used for this are Assignment, Activity, and User. The Assignment table can have multiple Activities entered by different Users (only 1 user per activity). I need a list of all of the active assignments for a given User along with the last activity that was added to that assignment. The assignment may or may not have an activity but the assignment still needs listed. The activity returned must be one entered by the same user that the assignment belongs to.

Here is an example that works in SQL Server but not SqlCE:

select assignment.assignmentID, activityDateStamp
from assignment
left join
(
select assignment.assignmentID, max(activityDateStamp) as activityDateStamp
from activity
join assignment on assignment.assignmentID = activity.assignmentID
where assignment.userID = 40
and activity.userID = 40
and activityActive = 1
and assignmentActive = 1
group by assignment.assignmentID
) maxActivity on maxActivity.assignmentID = assignment.assignmentID
where assignment.userID = 40
and assignmentActive = 1

The output would look similar to this:

assignmentID activityDateStamp
-
123 NULL
4322 2006-06-23
423 2006-12-15
431 NULL

Thanks again for any help.

|||

Derived tables are not supported in SSCE.

|||I guess if we can provide some feedback to the team for future versions having derived/nested queries would definitely be something worth having. This limitation currently prevents us doing more than the simplest of queries.|||

Thanks Nick.

What I did not mention earlier was that support for derived tables aka nested queries would be there with the next release of Orcas!!

Deployment on MSDE

Hi All,
Is it possible to use SQL Server 2000 Developer Edition for development and then deploy the database on MSDE?
Regards.
hi sm,
"sm" <sm@.discussions.microsoft.com> ha scritto nel messaggio
news:1143B7F5-D2C5-42B9-8C24-3E21589830C2@.microsoft.com...
> Hi All,
> Is it possible to use SQL Server 2000 Developer Edition for development
and then
>deploy the database on MSDE?
this is usually the solution to go...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

deployment of sqlserverce.dll

I registered to redistribute the runtime for SQL Server Compact edition.

I have a desktop application which uses SDF files as the database.

In VS 2008 - when I set System.Data.SqlServerCe configured to Copy Local as TRUE - sqlserverce.dll does not get inserted to my bin directory. I am assuming if I manually put the DLL file into my bin directory (and deploy it) - the DLL file will automatically be used by my app?

If you install the 3.5 redistributable/runtime with your app, you do not need any SQL Compact DLLs in your bin folder.

http://www.microsoft.com/downloads/details.aspx?FamilyID=4dd09a86-03eb-40a1-bd32-0fd8bcdf7be0&displaylang=en

|||

This defeats the purpose of packaging up the database with your deployment package. I am not the original poster of this thread but I will need a solution that does not need/want to install the Compact Edition Runtime installation with my application deployment. The installation of the runtime package requires the user to have admin rights and most of my end users will not have admin rights.

Therefore, please provide us with the steps to include the Sql Server CE provider assemblies with our application deployment package.

Thank you.

|||Maybe tutorial 1 here will be able to assist you: http://msdn2.microsoft.com/en-us/sql/bb219480.aspxsql

Deploying SQL Server 2000 Developer Edition

Hi All,
Is it possible to use SQL Server 2000 Developer Edition for development and then deploy the database on MSDE?
Regards.
Yes
"sm" wrote:

> Hi All,
> Is it possible to use SQL Server 2000 Developer Edition for development and then deploy the database on MSDE?
> Regards.
|||backup the DB on your Development Server and restore to the MSDE machine
or
sp_detachdb the DB on your Development Server and then sp_attachdb on the
MSDE machine
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andras Jakus" <AndrasJakus@.discussions.microsoft.com> wrote in message
news:EDE37FB7-2D1F-4255-9C37-D414518CB3ED@.microsoft.com...[vbcol=seagreen]
> Yes
> "sm" wrote:
and then deploy the database on MSDE?[vbcol=seagreen]

Deploying SQL Server 2000 Developer Edition

Hi All,
Is it possible to use SQL Server 2000 Developer Edition for development and
then deploy the database on MSDE?
Regards.Yes
"sm" wrote:

> Hi All,
> Is it possible to use SQL Server 2000 Developer Edition for development an
d then deploy the database on MSDE?
> Regards.|||backup the DB on your Development Server and restore to the MSDE machine
or
sp_detachdb the DB on your Development Server and then sp_attachdb on the
MSDE machine
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andras Jakus" <AndrasJakus@.discussions.microsoft.com> wrote in message
news:EDE37FB7-2D1F-4255-9C37-D414518CB3ED@.microsoft.com...[vbcol=seagreen]
> Yes
> "sm" wrote:
>
and then deploy the database on MSDE?[vbcol=seagreen]

Deploying SQL Server 2000 database on MSDE

Hi All,
I have done by development using SQL Server 2000 Developer Edition. How do I deploy the database in MSDE?
Regards.
Hi,
2 Methods
1. Backup the database in SQL 2000 developer edition (Backup DATBASE) and
copy to MSDE machine and Restore it (RESTORE DATABASE)
2. Detach the (SP_DETACH_DB) in SQL 2000 developer edition , copy the MDF
and LDF to MSDE machine and then Attach the database (SP_ATTACH_DB)
See the command usage in books online.
Note:
If your database size is > 2 GB then you cant restore or attach the database
to MSDE. MSDE allows a maximum 2 GB database size.
Thanks
Hari
MCDBA
"sm" <sm@.discussions.microsoft.com> wrote in message
news:2AF7E862-5006-43E8-A828-A6337E7D2870@.microsoft.com...
> Hi All,
> I have done by development using SQL Server 2000 Developer Edition. How do
I deploy the database in MSDE?
> Regards.
|||Thanks.
"Hari Prasad" wrote:

> Hi,
> 2 Methods
> 1. Backup the database in SQL 2000 developer edition (Backup DATBASE) and
> copy to MSDE machine and Restore it (RESTORE DATABASE)
> 2. Detach the (SP_DETACH_DB) in SQL 2000 developer edition , copy the MDF
> and LDF to MSDE machine and then Attach the database (SP_ATTACH_DB)
>
> See the command usage in books online.
> Note:
> If your database size is > 2 GB then you cant restore or attach the database
> to MSDE. MSDE allows a maximum 2 GB database size.
> Thanks
> Hari
> MCDBA
>
> "sm" <sm@.discussions.microsoft.com> wrote in message
> news:2AF7E862-5006-43E8-A828-A6337E7D2870@.microsoft.com...
> I deploy the database in MSDE?
>
>

Deploying RS server components on server without MSSQL

For the application I need to integrate RS with, I have a physical server
running MSSQL 2000 SP3a Standard Edition and another physical server that
runs my web apps. Both servers are running windows 2000 SP2. We do not want
to install RS server components on the db server itself because we want it to
remain a dedicated database server.
So, we want to host the Report Server database on the db physical server and
run all of the RS server components on the application physical server.
Yet when I try to install RS on my application server, I get the following
message in the install 'System Prerequisite Check': 'This edition of
Reporting Services does not support installing the server components on this
operating system'. What does this mean? How can I get around it?
-kind regards, Brian ParkerThe issue is not the database. RS is designed to run this way (note that you
need to have another SQL Server license if RS is on another server than
where the DB resides but that is a licensing issue). The issue is the OS.
Here is a link on the prereqs:
http://www.microsoft.com/sql/reporting/productinfo/sysreqs.asp
a.. Windows® 2000 Server with Service Pack 4 (SP4) or later
a.. Windows 2000 Professional with SP4 or later1
I wasn't sure if you had server or professional. If server then it needs
SP4. If professional then this additional info holds:
Windows XP Professional and Windows 2000 Professional only support Reporting
Services Developer Edition
So, one of those two things is what is happening.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Parker" <BrianParker@.discussions.microsoft.com> wrote in message
news:97ECE792-B9F0-4762-A278-1BC8790797A0@.microsoft.com...
> For the application I need to integrate RS with, I have a physical server
> running MSSQL 2000 SP3a Standard Edition and another physical server that
> runs my web apps. Both servers are running windows 2000 SP2. We do not
> want
> to install RS server components on the db server itself because we want it
> to
> remain a dedicated database server.
> So, we want to host the Report Server database on the db physical server
> and
> run all of the RS server components on the application physical server.
> Yet when I try to install RS on my application server, I get the following
> message in the install 'System Prerequisite Check': 'This edition of
> Reporting Services does not support installing the server components on
> this
> operating system'. What does this mean? How can I get around it?
> -kind regards, Brian Parker

Deploying MSDE

Hi All,
Is it possible to use SQL Server 2000 Developer Edition for development and then deploy the database on MSDE?
Regards.
Yes, it is.
There is very little missing from MSDE, but it has limitations in database
size and number of connections. (Also, see SQL Server 2005 Express Edition
for where this is going.)
Russell Fields
"sm" <sm@.discussions.microsoft.com> wrote in message
news:806DD4DC-6E84-4335-A335-A8DE1E931A8F@.microsoft.com...
> Hi All,
> Is it possible to use SQL Server 2000 Developer Edition for development
and then deploy the database on MSDE?
> Regards.
|||So does all this mean, a VS.NET 2005 developer can create a database in SQL
Server and then ship this free version of SQL Server and it will install
easily.
If so, Hallelujah!
MSDE is a pain to install and especially to have your users install it.
SQL Server is great for large companies but for small scale 5-10 users,
buying a $5000+ product just for a database and having all the management
overhead is a pain in the rear and that is the nitch that jet files have
filled before and MSDE was trying to fill.
For me I want to use a free limited version of SQL Server for such
applications and if the company gets real large and starts needing more then
they can buy the big brother. But I really need it to install easily
without much user intervention.
Is this what we can expect?
Thanks Russell,
Shane
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OBmBI%23AdEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Yes, it is.
> There is very little missing from MSDE, but it has limitations in database
> size and number of connections. (Also, see SQL Server 2005 Express
Edition
> for where this is going.)
> Russell Fields
> "sm" <sm@.discussions.microsoft.com> wrote in message
> news:806DD4DC-6E84-4335-A335-A8DE1E931A8F@.microsoft.com...
> and then deploy the database on MSDE?
>

Deploy C# Express app with SQL Server express DB

Hello Everyone,

I have developed an application in Visual C# Express Edition, that uses a SQL Server Express database. I am deploying it using ClickOnce, and I'm wondering if there are any settings I have to change to SQL Server express, in order for my application to be able to access the database, since I'm getting an error when I start the application. I consider myself a beginner developer, so please take that in mind, I'm looking for the easiest way to do this.

Any help will be highly appreciated,

Andrs

Is SQL Server Express installed as a prerequisite within the ClickOnce deployment or will a SQL Server be accessed which is not on the current client ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Deploy .MDF to a web host

Hi all,

I'm new to SQLServer (Express edition) so I was wondering: if the web
host supports SQLServer 2005 do I just need to move the .MDF file to my
directory on the web host to be able to use it?

Thanks,
LorenzoIf you have rights attaching a user database to a SQL Server and the
instance has access to your upload directory you can do this.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Jens wrote:
> If you have rights attaching a user database to a SQL Server and the
> instance has access to your upload directory you can do this.

Thanks for the answer, Jens

Lorenzo|||lbolognini@.gmail.com (lbolognini@.gmail.com) writes:
> I'm new to SQLServer (Express edition) so I was wondering: if the web
> host supports SQLServer 2005 do I just need to move the .MDF file to my
> directory on the web host to be able to use it?

You should probably talk to your web host about this. Jens suggested that
you may rights to attach databases. I say that the likelyhood for this is
nil, as this requires hefty rights on the server.

A little more likely is that they create an empty database for you,
and then you may be permitted to overwrite that database with a backup
of your database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx