Best Practices, Create a Test DB Subset

Hello, we have a production DB that is 125 GB. Lots of tables. Is there an
automated way to extract say 10% of all those records? Thanks.Hi,
You have to write your own query to extract 10% data from each table.
Thanks
Hari
SQL Server MVP
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:7F1A5FC8-6431-49E0-812B-14ECEC8998CB@.microsoft.com...
> Hello, we have a production DB that is 125 GB. Lots of tables. Is there
> an
> automated way to extract say 10% of all those records? Thanks.|||That's going to be very time consuming as there are 100+ user tables. I was
hoping to perhaps create something more automated.
"Hari Pra" wrote:

> Hi,
> You have to write your own query to extract 10% data from each table.
> Thanks
> Hari
> SQL Server MVP
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:7F1A5FC8-6431-49E0-812B-14ECEC8998CB@.microsoft.com...
>
>|||Try something like this..
select
name, id
into
#TempTables
from
sysobjects
where
type = 'u'
declare @.CurrentID int
select @.CurrentID = min(id) from #TempTables
while (@.currentid is not null)
begin
-- Currently just selecting, but you could dynamically add new tables into
the DB from here.
declare @.sqltext nvarchar(2000)
set @.sqltext = N'select top 10 percent * from ' + (select name from
#TempTables where id = @.currentid)
exec sp_executesql @.sqltext
select @.CurrentID = min(id) from #TempTables where id > @.CurrentID
end
"Pancho" wrote:

> Hello, we have a production DB that is 125 GB. Lots of tables. Is there
an
> automated way to extract say 10% of all those records? Thanks.|||Pancho
See if this helps
EXEC sp_msforeachtable 'select top 10 percent * from ?'
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:D790FDC0-4F2D-452F-8E47-B73FAC4A9D95@.microsoft.com...
> That's going to be very time consuming as there are 100+ user tables. I
> was
> hoping to perhaps create something more automated.
> "Hari Pra" wrote:
>sql

Best practice for Add, Edit records into database with lots of fields ?

What's the best practice for adding / editing a record into a database with lots of fields ?
I am not talking about the mechanics of it, as there are a lot of trivial examples using ADO.NET, stored procs, etc.

Deleting is easy, you just pass in (a few) primary key/keys to uniquely identify the record.

But in the real world when you have, say, a table with 100 fields! Do you code the INSERT sproc by hand, with 100 parameters... then call it with your ADO.NET code ? sounds like a lot of work to me...

What about updating! That's even worst, sometimes you may need to update only 3 or 4 fields, but using sprocs you would have to pass the whole 100 parameters in again, and "update" the whole record (when in fact you are only changing 3 or 4 fields).

With the update i could write different sprocs targeting only the fields i wish to update, but that sounds like duplicating work, vs having one generic update proc.

Sometimes i just feel like bypassing sprocs and having inline sql as it would be less work... but i know it is untidy.. and more potential to be buggy.

So come on guys (and gals)... let's hear your thoughts on how you would handle the insert / update scenarios when you have lots of fields ? Northwind examples are too trivial :-)

Hmm... more than one week and no comments, better file this one in the too hard basket... guess no one is willing to comment, or is the subject taboo? Where are all the certified professionals and / or other opinionated people... :-)

Or it could be there are no alternatives.

best practice analyzer help

Hi,
I have downloaded and run hte analyzer, but the result I get have lots of
exeption errors that contain the message:
Error while executing the Best Practice Group. See the log file
loadtraced_antodxp_0003_mshfsql_sqlbpa.log. Error message:
the log file cannot be found anywhere on the server or in my pc that I run
it from...
regards,
Dimitris
Based on your other post I'm guessing it is related to the trace files
you're feeding to BPA. Trace files should be files generated by SQL Server
profiler, not .sql files.
Let's try two things to isolate the errors you're seeing:
1) clear any specified Trace Files from the servers registered. Try to run
BPA. I'm guessing things should succeed.
2) Try with a .trc file generated by Profiler.
If this doesn't help, search for LOG folder under BPA installation folder
and post the log files.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dimitris" <dimitris@.microsoft.com> wrote in message
news:ubtp0bSTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have downloaded and run hte analyzer, but the result I get have lots of
> exeption errors that contain the message:
> Error while executing the Best Practice Group. See the log file
> loadtraced_antodxp_0003_mshfsql_sqlbpa.log. Error message:
> the log file cannot be found anywhere on the server or in my pc that I run
> it from...
> regards,
>
|||Thanks, it worked!
As you have already noticed I have posted a trace help request, you might be
able to help:
I have created a trace template using Profiler, called locks and have
included only the locks from the events menu (deadlocks, escalation and lock
timeout). I have saved a trace file called traceLocks, but I dont know what
to do with it. Can you help?
Best regards,
Dimitris
"Christian Kleinerman [MS]" <ckleiner@.online.microsoft.com> wrote in message
news:OUaZpwXTEHA.2336@.TK2MSFTNGP10.phx.gbl...
> Dimitris
> Based on your other post I'm guessing it is related to the trace files
> you're feeding to BPA. Trace files should be files generated by SQL Server
> profiler, not .sql files.
> Let's try two things to isolate the errors you're seeing:
> 1) clear any specified Trace Files from the servers registered. Try to run
> BPA. I'm guessing things should succeed.
> 2) Try with a .trc file generated by Profiler.
> If this doesn't help, search for LOG folder under BPA installation folder
> and post the log files.
> - Christian
>
> --
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "dimitris" <dimitris@.microsoft.com> wrote in message
> news:ubtp0bSTEHA.1732@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
run
>
|||Dimitris
BPA will scan statement start and completion as well as batch start and
completion events - for those events, it will lookup the SQL text being
exeuted and will scan it for best practices.
If you only have locking events, I'm not sure you'll be able to do much with
BPA.
If you create a trace file that captures batch/statement events, you can
include the path in PBA (e.g. c:\yourpath\yourtracefile.trc) for a given
server and BPA will perform the scan.
Let me know if you have additional questions.
- Christian
"dimitris" <dimitris@.microsoft.com> wrote in message
news:OUGWzLcTEHA.904@.TK2MSFTNGP12.phx.gbl...
> Thanks, it worked!
> As you have already noticed I have posted a trace help request, you might
be
> able to help:
> I have created a trace template using Profiler, called locks and have
> included only the locks from the events menu (deadlocks, escalation and
lock
> timeout). I have saved a trace file called traceLocks, but I dont know
what
> to do with it. Can you help?
> Best regards,
> Dimitris
> "Christian Kleinerman [MS]" <ckleiner@.online.microsoft.com> wrote in
message[vbcol=seagreen]
> news:OUaZpwXTEHA.2336@.TK2MSFTNGP10.phx.gbl...
Server[vbcol=seagreen]
run[vbcol=seagreen]
folder
> rights.
> of
> run
>

Benefits of SQL authentication?

SQL2K
SP4
I can find lots of info regarding the justification to use WINNT instead of
SQL authentication. Are there any good reasons to use SQL authentication
instead?
TIA, ChrisRWeb applications, Mac or Linux users, or any situation where the users are
not members of the domain.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:AFC675ED-FE57-41DD-8CF9-F494E112E95A@.microsoft.com...
> SQL2K
> SP4
> I can find lots of info regarding the justification to use WINNT instead
> of
> SQL authentication. Are there any good reasons to use SQL authentication
> instead?
> TIA, ChrisR|||Thanks, I should have been more specific. I know there are times when users
MUST use SQL auth. However, if it could go either way, what would the
benefits be to SQL auth?
"Arnie Rowland" wrote:

> Web applications, Mac or Linux users, or any situation where the users are
> not members of the domain.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:AFC675ED-FE57-41DD-8CF9-F494E112E95A@.microsoft.com...
>
>|||If you have a choice, the pros are for Windows authentication, and the cons
are for SQL Authentication.
I, honestly, can't recall situations where SQL Authentication is the best
choice over Windows Authentication -unless SQL Authentication is required
for some specific need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:F115C81F-55BA-46B1-A7AE-F156BD815A95@.microsoft.com...[vbcol=seagreen]
> Thanks, I should have been more specific. I know there are times when
> users
> MUST use SQL auth. However, if it could go either way, what would the
> benefits be to SQL auth?
>
> "Arnie Rowland" wrote:
>|||There aren't any benefits aside from needing to use it. You wouldn't
'choose' SQL Auth over Windows auth if you had both as a viable option -
ever. SQL Auth is a subset of Windows Auth - not vice versa.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:F115C81F-55BA-46B1-A7AE-F156BD815A95@.microsoft.com...[vbcol=seagreen]
> Thanks, I should have been more specific. I know there are times when
> users
> MUST use SQL auth. However, if it could go either way, what would the
> benefits be to SQL auth?
>
> "Arnie Rowland" wrote:
>

Benefits of SQL authentication?

SQL2K
SP4
I can find lots of info regarding the justification to use WINNT instead of
SQL authentication. Are there any good reasons to use SQL authentication
instead?
TIA, ChrisRWeb applications, Mac or Linux users, or any situation where the users are
not members of the domain.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:AFC675ED-FE57-41DD-8CF9-F494E112E95A@.microsoft.com...
> SQL2K
> SP4
> I can find lots of info regarding the justification to use WINNT instead
> of
> SQL authentication. Are there any good reasons to use SQL authentication
> instead?
> TIA, ChrisR|||Thanks, I should have been more specific. I know there are times when users
MUST use SQL auth. However, if it could go either way, what would the
benefits be to SQL auth?
"Arnie Rowland" wrote:
> Web applications, Mac or Linux users, or any situation where the users are
> not members of the domain.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:AFC675ED-FE57-41DD-8CF9-F494E112E95A@.microsoft.com...
> > SQL2K
> > SP4
> >
> > I can find lots of info regarding the justification to use WINNT instead
> > of
> > SQL authentication. Are there any good reasons to use SQL authentication
> > instead?
> >
> > TIA, ChrisR
>
>|||If you have a choice, the pros are for Windows authentication, and the cons
are for SQL Authentication.
I, honestly, can't recall situations where SQL Authentication is the best
choice over Windows Authentication -unless SQL Authentication is required
for some specific need.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:F115C81F-55BA-46B1-A7AE-F156BD815A95@.microsoft.com...
> Thanks, I should have been more specific. I know there are times when
> users
> MUST use SQL auth. However, if it could go either way, what would the
> benefits be to SQL auth?
>
> "Arnie Rowland" wrote:
>> Web applications, Mac or Linux users, or any situation where the users
>> are
>> not members of the domain.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:AFC675ED-FE57-41DD-8CF9-F494E112E95A@.microsoft.com...
>> > SQL2K
>> > SP4
>> >
>> > I can find lots of info regarding the justification to use WINNT
>> > instead
>> > of
>> > SQL authentication. Are there any good reasons to use SQL
>> > authentication
>> > instead?
>> >
>> > TIA, ChrisR
>>|||There aren't any benefits aside from needing to use it. You wouldn't
'choose' SQL Auth over Windows auth if you had both as a viable option -
ever. SQL Auth is a subset of Windows Auth - not vice versa.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:F115C81F-55BA-46B1-A7AE-F156BD815A95@.microsoft.com...
> Thanks, I should have been more specific. I know there are times when
> users
> MUST use SQL auth. However, if it could go either way, what would the
> benefits be to SQL auth?
>
> "Arnie Rowland" wrote:
>> Web applications, Mac or Linux users, or any situation where the users
>> are
>> not members of the domain.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:AFC675ED-FE57-41DD-8CF9-F494E112E95A@.microsoft.com...
>> > SQL2K
>> > SP4
>> >
>> > I can find lots of info regarding the justification to use WINNT
>> > instead
>> > of
>> > SQL authentication. Are there any good reasons to use SQL
>> > authentication
>> > instead?
>> >
>> > TIA, ChrisR
>>

Begin some web programming

I know there are lots of books out there.. Ive been a DBA(not an all rounder
like most of you'll are that can program as well) for a while and have now
decided to do some Web programming,,i,e, basically using SQL Server as data
source and maybe generating some reports,etc.. Can one guide me into
achieving this through some online content thats out there ?Some step by
step stuff...Using SQL 2000You'll want to pick a software product to program in first. For
ASP.NET resources, see http://msdn.microsoft.com/asp.net/
-- Mary
MCW Technologies
http://www.mcwtech.com
On Sun, 1 Feb 2004 11:43:38 -0800, "Hassan" <fatima_ja@.hotmail.com>
wrote:

quote:

---

>I know there are lots of books out there.. Ive been a DBA(not an all rounde
r
>like most of you'll are that can program as well) for a while and have now
>decided to do some Web programming,,i,e, basically using SQL Server as data
>source and maybe generating some reports,etc.. Can one guide me into
>achieving this through some online content thats out there ?Some step by
>step stuff...Using SQL 2000
>

---

|||Hi Hassan.
I found the msdn VB.Net 101's a very useful resource, so if you choose to
code in VB.Net, drop by here:
http://msdn.microsoft.com/vbasic/do...101samples.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I know there are lots of books out there.. Ive been a DBA(not an all

---

rounder

quote:

---

> like most of you'll are that can program as well) for a while and have

---

now

quote:

---

> decided to do some Web programming,,i,e, basically using SQL Server as

---

data

quote:

---

> source and maybe generating some reports,etc.. Can one guide me into
> achieving this through some online content thats out there ?Some step by
> step stuff...Using SQL 2000
>

---

|||When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
difference ?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi Hassan.
> I found the msdn VB.Net 101's a very useful resource, so if you choose to
> code in VB.Net, drop by here:
> http://msdn.microsoft.com/vbasic/do...101samples.aspx
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> rounder
> now
> data
>

---

|||When you develop within the ASP.Net framework, you choose a .Net language to
program asp pages with.
You can program ASP.Net pages with any of the .Net languages - C#, VB.Net,
C++.
I generally use C# or VB.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ee8Dr$T6DHA.2168@.TK2MSFTNGP12.phx.gbl...

quote:

---

> When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
> difference ?
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
to[QUOTE]
have[QUOTE]
by[QUOTE]
>

---

Begin some web programming

I know there are lots of books out there.. Ive been a DBA(not an all rounder
like most of you'll are that can program as well) for a while and have now
decided to do some Web programming,,i,e, basically using SQL Server as data
source and maybe generating some reports,etc.. Can one guide me into
achieving this through some online content thats out there ?Some step by
step stuff...Using SQL 2000You'll want to pick a software product to program in first. For
ASP.NET resources, see http://msdn.microsoft.com/asp.net/
-- Mary
MCW Technologies
http://www.mcwtech.com
On Sun, 1 Feb 2004 11:43:38 -0800, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>I know there are lots of books out there.. Ive been a DBA(not an all rounder
>like most of you'll are that can program as well) for a while and have now
>decided to do some Web programming,,i,e, basically using SQL Server as data
>source and maybe generating some reports,etc.. Can one guide me into
>achieving this through some online content thats out there ?Some step by
>step stuff...Using SQL 2000
>|||Hi Hassan.
I found the msdn VB.Net 101's a very useful resource, so if you choose to
code in VB.Net, drop by here:
http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I know there are lots of books out there.. Ive been a DBA(not an all
rounder
> like most of you'll are that can program as well) for a while and have
now
> decided to do some Web programming,,i,e, basically using SQL Server as
data
> source and maybe generating some reports,etc.. Can one guide me into
> achieving this through some online content thats out there ?Some step by
> step stuff...Using SQL 2000
>|||When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
difference ?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Hi Hassan.
> I found the msdn VB.Net 101's a very useful resource, so if you choose to
> code in VB.Net, drop by here:
> http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > I know there are lots of books out there.. Ive been a DBA(not an all
> rounder
> > like most of you'll are that can program as well) for a while and have
> now
> > decided to do some Web programming,,i,e, basically using SQL Server as
> data
> > source and maybe generating some reports,etc.. Can one guide me into
> > achieving this through some online content thats out there ?Some step by
> > step stuff...Using SQL 2000
> >
> >
>|||When you develop within the ASP.Net framework, you choose a .Net language to
program asp pages with.
You can program ASP.Net pages with any of the .Net languages - C#, VB.Net,
C++.
I generally use C# or VB.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ee8Dr$T6DHA.2168@.TK2MSFTNGP12.phx.gbl...
> When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
> difference ?
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > Hi Hassan.
> >
> > I found the msdn VB.Net 101's a very useful resource, so if you choose
to
> > code in VB.Net, drop by here:
> >
> > http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > > I know there are lots of books out there.. Ive been a DBA(not an all
> > rounder
> > > like most of you'll are that can program as well) for a while and
have
> > now
> > > decided to do some Web programming,,i,e, basically using SQL Server as
> > data
> > > source and maybe generating some reports,etc.. Can one guide me into
> > > achieving this through some online content thats out there ?Some step
by
> > > step stuff...Using SQL 2000
> > >
> > >
> >
> >
>