Best practice configuring Visual Studio Solution for .SDF databases

Hello,

In our company we haven't tamed Visual Studio yet for the part of configuring

the application database. Therefore I was wondering if there is someone that

can give me hints or clues on what the best practice is for configuring Visual

Solution to handle an SDF database (SQLCE 3.0). I have search a bit on the

internet and this forum, but couldn't find a decent how-to or best practices

guide that copes with this specific problem.

As

a matter of facts, i am also curious for guidelines and tips on the best way to

configure and manage SQL Server databases under Visual Studio. I guess (read as

hope) that the configuration of a mobile and full blown

database will have some overlaps somehow.

I've found the Database Project which with to manage (Create/Alter) databases,

but is this also the best way to store versions of the database in an

source repository? Can such a project allow me to 'automagically' have a

correct database (so with tables and data) when i want to deploy or debug my

device application?

I am asking this because now our database designers and software engineers have

to do allot of manual actions to update the application with the most recent

database version. SDF databases are flying around all over the place, and in

order to test a specific version of the application, the related database has

to be copied manually on the device. We are not searching for replication

related solutions, nor adding the SDF itself to the source repository, but because

most of our applications are server-client based, it would be really super cool

if we could somehow couple both database definitions and generation together. My

feeling says me there ought to be some feature embedded somewhere deep into

Visual Studio that we missed that would (partially) simplify and automate this

whole process for us.

Another

related question is if it is possible to couple XSD generated datasets to an

SDF database. Is it possible to update the generated code (or the describing

XSD documents) from an SDF database? i again guess that this somehow should be

possible in oderder to keep both code and data in sync, and i do not like the

alternative to always update the XSD when the SDF architecture changes. Somehow

i cannot find out how to do this. i was initially searching for the other way

around: Use an XSD to generate both the DataSet codewrappers and the database

itself.

Any

information is welcome and many thanks in advance!

Peter

Vrenken

Too bad there is no one that can give more information

regarding these issues. Can I assume that allot of people/companies haven’t got

a decent SDF database (configuration) setup or thought about it?

I would really like to open a dialog about these issues. Anyone

wants to join me?

Greetings from the rainy Netherlands,

Peter Vrenken

|||Do all you developers out there have got a decent database setup or never thought about it yet? I am hoping that VTS will solve some of the riddles for us but until that time i would really like to know how other companies manage their SDF databases.

Is there not a single developer (maybe a MVP) that wants to shed some light on it and describe how he does it (or how it should be done)?

Thanks in advance,

Peter Vrenken|||I used to put an .mdb in vss. No reason why this couldn't be done with the .sdf|||Hello and thanks for your response!

I know that as of VS2K5 SP1 the management of .SDF files from within a solution has been greatly enhanced.
You say that you ‘used’ to put an .mdb in VSS. Is this because you found a better solution?

Peter Vrenken|||Peter, there are several questions here and I'll try to help where I can. As I understand your issues you're trying to build SDF databases in a way that can be better managed through developer tools like Visual Studio. At this point VS can help, but not as much as it could. The VS team is working on an updated version of the tools that can address some (but not nearly all) of your issues. The SQL Server Management Studio can also do more to help in this regard. As far as scripting, there is little to no support in any of the tools. I too felt your frustration so I wrote my first EBook to supplement my just completed Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition). This is available at WWW.Hitchhikerguides.net. In the book I walk through the process of creating a database using a script reader that I wrote (and provide with the book), with replication and using the APIs. I expect it will help answer many more of your questions.|||"Used to" only in that I no longer use .mdb files.

I moved to MSDE where I kept the create scripts in vss.

I have migrated these to SQL Express (with the scipts in vss) but am now working on a new application with CE. Unfortunately the scripting that was created from SQL Server Management Tool does not work with CE so I am planning on using vss.

Best practice configuring Visual Studio Solution for .SDF databases

Hello,

In our company we haven't tamed Visual Studio yet for the part of configuring

the application database. Therefore I was wondering if there is someone that

can give me hints or clues on what the best practice is for configuring Visual

Solution to handle an SDF database (SQLCE 3.0). I have search a bit on the

internet and this forum, but couldn't find a decent how-to or best practices

guide that copes with this specific problem.

As

a matter of facts, i am also curious for guidelines and tips on the best way to

configure and manage SQL Server databases under Visual Studio. I guess (read as

hope) that the configuration of a mobile and full blown

database will have some overlaps somehow.

I've found the Database Project which with to manage (Create/Alter) databases,

but is this also the best way to store versions of the database in an

source repository? Can such a project allow me to 'automagically' have a

correct database (so with tables and data) when i want to deploy or debug my

device application?

I am asking this because now our database designers and software engineers have

to do allot of manual actions to update the application with the most recent

database version. SDF databases are flying around all over the place, and in

order to test a specific version of the application, the related database has

to be copied manually on the device. We are not searching for replication

related solutions, nor adding the SDF itself to the source repository, but because

most of our applications are server-client based, it would be really super cool

if we could somehow couple both database definitions and generation together. My

feeling says me there ought to be some feature embedded somewhere deep into

Visual Studio that we missed that would (partially) simplify and automate this

whole process for us.

Another

related question is if it is possible to couple XSD generated datasets to an

SDF database. Is it possible to update the generated code (or the describing

XSD documents) from an SDF database? i again guess that this somehow should be

possible in oderder to keep both code and data in sync, and i do not like the

alternative to always update the XSD when the SDF architecture changes. Somehow

i cannot find out how to do this. i was initially searching for the other way

around: Use an XSD to generate both the DataSet codewrappers and the database

itself.

Any

information is welcome and many thanks in advance!

Peter

Vrenken

Too bad there is no one that can give more information

regarding these issues. Can I assume that allot of people/companies haven’t got

a decent SDF database (configuration) setup or thought about it?

I would really like to open a dialog about these issues. Anyone

wants to join me?

Greetings from the rainy Netherlands,

Peter Vrenken

|||Do all you developers out there have got a decent database setup or never thought about it yet? I am hoping that VTS will solve some of the riddles for us but until that time i would really like to know how other companies manage their SDF databases.

Is there not a single developer (maybe a MVP) that wants to shed some light on it and describe how he does it (or how it should be done)?

Thanks in advance,

Peter Vrenken|||I used to put an .mdb in vss. No reason why this couldn't be done with the .sdf|||Hello and thanks for your response!

I know that as of VS2K5 SP1 the management of .SDF files from within a solution has been greatly enhanced.
You say that you ‘used’ to put an .mdb in VSS. Is this because you found a better solution?

Peter Vrenken|||Peter, there are several questions here and I'll try to help where I can. As I understand your issues you're trying to build SDF databases in a way that can be better managed through developer tools like Visual Studio. At this point VS can help, but not as much as it could. The VS team is working on an updated version of the tools that can address some (but not nearly all) of your issues. The SQL Server Management Studio can also do more to help in this regard. As far as scripting, there is little to no support in any of the tools. I too felt your frustration so I wrote my first EBook to supplement my just completed Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition). This is available at WWW.Hitchhikerguides.net. In the book I walk through the process of creating a database using a script reader that I wrote (and provide with the book), with replication and using the APIs. I expect it will help answer many more of your questions.|||"Used to" only in that I no longer use .mdb files.

I moved to MSDE where I kept the create scripts in vss.

I have migrated these to SQL Express (with the scipts in vss) but am now working on a new application with CE. Unfortunately the scripting that was created from SQL Server Management Tool does not work with CE so I am planning on using vss.

beginner: long running sp

hi,
i have a sp that takes around 5-6 sec even though there're around ONLY 5000,
therefore, i need to tune it.
any ideas for me to do so?
thanks a lot!
PS.
Table
====
OutboundQueue [JobID, ....] JobID - P.K
OutboundQueueItem [JobItemID, JobID, .....] JobItemID - P.K and JobID - F.K
SP
===
CREATE PROC GetJobItems @.Machine as varchar(20), @.OutboundType as int as
declare @.JobID as bigint
declare @.JobItemID as bigint
BEGIN
SET NOCOUNT ON
BEGIN Tran
-- change the un-caught local print failed jobs to failure (status =5)
update outboundqueueitem set Status =5 where JobItemID in (select
jobitemid from outboundqueueitem where OutboundType = 1 and isLocalPrint =
1 and Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10)
-- Select one JobItem from OutboundQueue
select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority desc,
SubmissionDate asc
-- ****************************************
*********************
-- select JobItems within that JobID just got
-- ****************************************
*********************
-- Create temp Table for storing JobItemIDs to be processed
CREATE TABLE #tmpJobItemID(tJobItemID bigint)
INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
@.OutboundType
-- Lock the Job at OutboundQueue, and update the status to "Working"
update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
Status = 2, LastWorkingDate = getDate() where JobItemID in (select
tJobItemID from #tmpJobItemID)
-- return recordset based on the sequence
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID) order by submissiondate
COMMIT Tran
ENDHello Mullin,
A lot of this is going to depend on your indexing. In order to really tell
what is going on we will need more information. Some of the best information
comes from looking at the execution plan and also looking at what IO occurs.
To get the I/O information do this before you call your procedure.
SET STATISTICS IO ON
EXEC <proc>
SET STATISTICS IO OFF
To view the execution plan for posting here you can do
SET SHOWPLAN_TEXT ON
GO
exec <proc>
GO
SET SHOWPLAN_TEXT OFF
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> hi,
> i have a sp that takes around 5-6 sec even though there're around ONLY
> 5000, therefore, i need to tune it.
> any ideas for me to do so?
> thanks a lot!
> PS.
> Table
> ====
> OutboundQueue [JobID, ....] JobID - P.K
> OutboundQueueItem [JobItemID, JobID, .....] JobItemID - P.K and JobID
> - F.K
> SP
> ===
> CREATE PROC GetJobItems @.Machine as varchar(20), @.OutboundType as
> int as
> declare @.JobID as bigint
> declare @.JobItemID as bigint
> BEGIN
> SET NOCOUNT ON
> BEGIN Tran
> -- change the un-caught local print failed jobs to failure (status
> =5)
> update outboundqueueitem set Status =5 where JobItemID in (select
> jobitemid from outboundqueueitem where OutboundType = 1 and
> isLocalPrint =
> 1 and Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10)
> -- Select one JobItem from OutboundQueue
> select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0
> and
> AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority
> desc,
> SubmissionDate asc
> -- ****************************************
*********************
> -- select JobItems within that JobID just got
> -- ****************************************
*********************
> -- Create temp Table for storing JobItemIDs to be processed
> CREATE TABLE #tmpJobItemID(tJobItemID bigint)
> INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem
> where JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and
> OutboundType = @.OutboundType
> -- Lock the Job at OutboundQueue, and update the status to "Working"
> update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
> Status = 2, LastWorkingDate = getDate() where JobItemID in (select
> tJobItemID from #tmpJobItemID)
> -- return recordset based on the sequence
> select * from OutboundQueueItem where JobItemID in (select
> tJobItemID from
> #tmpJobItemID) order by submissiondate
> COMMIT Tran
> END
>|||but, i'm using temp table, and can't output the text version of execution
plan with the following error. but i can view the graphical execution plan
Server: Msg 208, Level 16, State 1, Procedure GetJobItems, Line 23
Invalid object name '#tmpJobItemID'.
any ideas?
btw, the following is the server trace i select at Query Analyzer:
set noexec off set parseonly off SQL:StmtCompleted 0 0 0 0
select IS_SRVROLEMEMBER ('symin') SQL:StmtCompleted 0 0 0 0
SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0
SET NOCOUNT ON SP:StmtCompleted 0 0 0 0
BEGIN Tran -- change the un-caught local print failed jobs to failure
(status =5) SP:StmtCompleted 0 0 0 0
CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem FOR
UPDATE AS SP:StmtCompleted 0 0 0 0
IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
select JobItemID from inserted SP:StmtCompleted 0 0 2 0
OPEN jobitemid_cursor -- Perform the first fetch and store the values in
variables. -- Note: The variables are in the same order as the columns --
in the SELECT statement. SP:StmtCompleted 0 0 14 0
FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check @.@.FETCH_STATUS
to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
update outboundqueueitem set Status =5 where JobItemID in (select jobitemid
from outboundqueueitem where OutboundType = 1 and isLocalPrint = 1 and
Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10) --
Select one JobItem from SP:StmtCompleted 0 0 104 0
select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority desc,
SubmissionDate asc --
****************************************
************* SP:StmtCompleted 0 0
40 0
CREATE TABLE #tmpJobItemID(tJobItemID bigint) SP:StmtCompleted 0 0 10 0
INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
@.OutboundType -- Lock the Job at OutboundQueue, and update the status
to "Working" -- SP:StmtCompleted 0 0 48 0
CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem FOR
UPDATE AS SP:StmtCompleted 0 0 0 0
IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
select JobItemID from inserted SP:StmtCompleted 0 0 2 0
OPEN jobitemid_cursor -- Perform the first fetch and store the values in
variables. -- Note: The variables are in the same order as the columns --
in the SELECT statement. SP:StmtCompleted 0 0 14 0
FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check @.@.FETCH_STATUS
to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine, Status
= 2, LastWorkingDate = getDate() where JobItemID in (select tJobItemID from
#tmpJobItemID) -- return recordset based on the sequence -- select
* from Outbou SP:StmtCompleted 0 0 68 0
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID) order by submissiondate SP:StmtCompleted 0 0 18 0
COMMIT Tran SP:StmtCompleted 0 0 0 0
getjobitems 'aaa', 1 SQL:StmtCompleted 62 62 66 0
SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0
thanks!
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:309826632428962507725664@.news.microsoft.com...
> Hello Mullin,
> A lot of this is going to depend on your indexing. In order to really tell
> what is going on we will need more information. Some of the best
information
> comes from looking at the execution plan and also looking at what IO
occurs.
> To get the I/O information do this before you call your procedure.
> SET STATISTICS IO ON
> EXEC <proc>
> SET STATISTICS IO OFF
> To view the execution plan for posting here you can do
> SET SHOWPLAN_TEXT ON
> GO
> exec <proc>
> GO
> SET SHOWPLAN_TEXT OFF
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://sqlprogrammer.org/
>
>
>|||Mullin
I'd move the CREATE tempoary table at the beginning of the stored procedure.
If you re-write it as
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID where #tmpJobItemID.JobItemID =OutboundQueueItem .JobItemID )
order by submissiondate
check it out.
Also . run SQL Server Profile to see what is going on?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23RRxo6NCFHA.3588@.TK2MSFTNGP11.phx.gbl...
> but, i'm using temp table, and can't output the text version of execution
> plan with the following error. but i can view the graphical execution
plan
> Server: Msg 208, Level 16, State 1, Procedure GetJobItems, Line 23
> Invalid object name '#tmpJobItemID'.
> any ideas?
> btw, the following is the server trace i select at Query Analyzer:
> set noexec off set parseonly off SQL:StmtCompleted 0 0 0 0
> select IS_SRVROLEMEMBER ('symin') SQL:StmtCompleted 0 0 0 0
> SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0
> SET NOCOUNT ON SP:StmtCompleted 0 0 0 0
> BEGIN Tran -- change the un-caught local print failed jobs to failure
> (status =5) SP:StmtCompleted 0 0 0 0
> CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem
FOR
> UPDATE AS SP:StmtCompleted 0 0 0 0
> IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
> select JobItemID from inserted SP:StmtCompleted 0 0 2 0
> OPEN jobitemid_cursor -- Perform the first fetch and store the values
in
> variables. -- Note: The variables are in the same order as the
olumns --
> in the SELECT statement. SP:StmtCompleted 0 0 14 0
> FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check
@.@.FETCH_STATUS
> to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
> WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
> CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> update outboundqueueitem set Status =5 where JobItemID in (select
jobitemid
> from outboundqueueitem where OutboundType = 1 and isLocalPrint = 1 and
> Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10) --
> Select one JobItem from SP:StmtCompleted 0 0 104 0
> select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
> AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority
desc,
> SubmissionDate asc --
> ****************************************
************* SP:StmtCompleted 0 0
> 40 0
> CREATE TABLE #tmpJobItemID(tJobItemID bigint) SP:StmtCompleted 0 0 10
0
> INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
> JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
> @.OutboundType -- Lock the Job at OutboundQueue, and update the
status
> to "Working" -- SP:StmtCompleted 0 0 48 0
> CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem
FOR
> UPDATE AS SP:StmtCompleted 0 0 0 0
> IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
> select JobItemID from inserted SP:StmtCompleted 0 0 2 0
> OPEN jobitemid_cursor -- Perform the first fetch and store the values
in
> variables. -- Note: The variables are in the same order as the
olumns --
> in the SELECT statement. SP:StmtCompleted 0 0 14 0
> FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check
@.@.FETCH_STATUS
> to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
> WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
> CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
Status
> = 2, LastWorkingDate = getDate() where JobItemID in (select tJobItemID
from
> #tmpJobItemID) -- return recordset based on the sequence --
select
> * from Outbou SP:StmtCompleted 0 0 68 0
> select * from OutboundQueueItem where JobItemID in (select tJobItemID from
> #tmpJobItemID) order by submissiondate SP:StmtCompleted 0 0 18 0
> COMMIT Tran SP:StmtCompleted 0 0 0 0
> getjobitems 'aaa', 1 SQL:StmtCompleted 62 62 66 0
> SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0
> thanks!
>
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:309826632428962507725664@.news.microsoft.com...
tell
> information
> occurs.
>