Best Practices Question for Outputting

Hey guys,
Little bit of a newbie question here...I have a database with about 20or so tables in a relational model. I am now working on an outputscheme and had a quick question regarding best practices foroutputting. Would it be best to
1) Set up a view that basically joins all of these tables together, then bind a DataSet/DataTable to it and output as needed?
2) Setup individual views for each table and run through them?
Thanks for the help!
e...
I've never liked creating do-everything views. You'll never get the same performance as you would by just creating individual stored procedures which join the tables you need to get the specific fields and records you need to fulfill each type of query or scenario you have. Unless you have a pretty simple site that doesn't do more than a couple very similar things, it's a lot of overhead that's not needed. Your other question: Why set up a view on one table? Unless you're doing a lot of calculated fields in the view that are derived from underlying fields in the table, that's a waste. Not knowing anything really about your situation, my stock advice is to create a stored procedure for every type of query you'll need. Add parameters as needed, but each proc should fulfill a specific need. Don't try to make a proc too general. They tend to get bigger and more confusing over time when they try to do too many different things.|||One very good thing about views is it reduces redundancies in yourprocedures. I'd personally make a few views of the most commontypes of joins you'd make. I've seen this as a problem with manydatabase driven sites and applications where one table change requiresyou to alter 30 stored procedures, and code for multiple pages. Alot of minor changes can remain minor if you consolidate alittle. It's extremely funny though when you have issues where afield name is spelled ammount. :)

Best Practices of cube release process

Have you guys come across best way to move/release cube changes from test to production server. Any way to automate this process.

- Ashok

You're probably going to want to give the Deployment Wizard a try:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1485584&SiteID=1

|||The Deployment Wizard is probably a good option. It will handle things like not overwriting datasources, roles and partitions which can be handy if these vary between your environments (which they often do). This tool can also be run from the command line which allows for a degree of automation.

Best practices for concurrent development of DB

Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
ChristophLook into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph

Best practices for concurrent development of DB

Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
Look into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas

"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph

Best practices for concurrent development of DB

Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
ChristophLook into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph

BCP's /F switch - forposting.txt [0/1]

Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
therefore looks at line 2 and sees that there are 75 fields, and
therefore internally defines a line as 75 fields. It then goes back to
line 1 in order to start looking for line 2, determining that line 2
will come after the first 75 fields it finds. Instead of that putting
it at the *beginning* of my line 2 as I would want, it starts counting
with the four fields of line 1 and then another 71 fields into line 2,
and considers that point the "line end".
Erland, thanks much for the offer, and I'm attaching a sample file with
the first header row and then 3 rows of (approximately) 75 fields each.
I'd be very grateful for anything you can do.
In article <Xns97CC2D50F29Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> BCP ? BCP never gets , but users of BCP often gets
> over it!
> I think the best description of BCP is that it reads a *binary* file.
> The file may look like a text file to you and me, for BCP it's binary.
> When BCP reads the file, it looks the format definition of the first
> field as specified by the format file or command-line switches. When
> it has found the end of that column, it goes for the next etc. The
> row terminator is just the terminator for the last field.
> When you say -F2, that is not the second line in the file. It's the second
> record in the file according to the format specification. Given your
> description, I can guess that will be line 3 in the file. That first
> skipped record has an embedded newline in field 4, and some embedded
> field terminators in field 75.
> As for what you want to do, you will have to split the file. BCP
> is not smart enough to handle two tables or two different formats.
> Or, hm, maybe you can get away with a single file - but requires
> quite some luck. Getting that single line into a table should be
> simple. Define a format file for those four fields, and then specify
> -L 1, to get only the first row.
> Skipping that first line may be more problematic. If you can post a
> sample, I might be able to come with something. But no promises.
>
>On Tue, 23 May 2006 13:30:19 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:

>Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
>therefore looks at line 2 and sees that there are 75 fields, and
>therefore internally defines a line as 75 fields. It then goes back to
>line 1 in order to start looking for line 2, determining that line 2
>will come after the first 75 fields it finds. Instead of that putting
>it at the *beginning* of my line 2 as I would want, it starts counting
>with the four fields of line 1 and then another 71 fields into line 2,
>and considers that point the "line end".
BCP checks to see what the field terminator - usually a tab or comma -
is for the first field. It then reads the file, byte by byte, until
it comes to that terminator. Then it continues for field 2, field 3,
and so forth. When it looks at field 4 in the file the terminator it
is looking for is still the comma or whatever, so it continues right
past the carriage return and line feed and finds the column terminator
of the first field of the second line. It then continues along the
second line, counting its way up to its 75th field. THAT field it
knows does not end in a comma, but in a carriage return / line feed
pair. SO, it keeps reading to the end of line 2 to find the next
CR/LF, and THAT is the end of what BCP sees as line 1. After that is
line 2, so that is where it starts based on the /F2 parameter.
In reaching the end of line 2, and considering it to be the end of
line 1, the first three fields are the first three on the first line.
What BCP sees as the fourth field is the last field of the first line,
the CR/LF of the first line, and the first field of the second line.
BCP then works through the second line, treating field 2 as 5, 3 as 6,
and so forth, until it looks for field 75. What BCP sees as field 75
will be field 72, 73, 74 and 75 all strung together.
Roy Harvey
Beacon Falls, CT|||On Tue, 23 May 2006 13:30:19 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:

>Erland, thanks much for the offer, and I'm attaching a sample file with
>the first header row and then 3 rows of (approximately) 75 fields each.
>I'd be very grateful for anything you can do.
Using your sample data file, the following two commands split it into
two files that BCP should be able to handle. Of course it only works
if the rows are all prefixed with H and O as in the sample data.
findstr /B "H" forposting.txt > H.txt
findstr /B "O" forposting.txt > O.txt
I'd never used findstr before, thanks for prompting me to add a new
trick to the loolbox!
Roy Harvey
Beacon Falls, CT|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
> therefore looks at line 2 and sees that there are 75 fields,
Nonono! BCP does not think in lines! As Roy said, it thinks in fields.
Keep in mind that BCP can be used to import binary files as well, and
it works in a strictly binary way. The lines are only there to mislead
you. :-)
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