Dimension or Fact?

Hi - I've got a dimensional modelling question that's got me stuck.
I have a "Student" entity and a "Student Transaction" entity.
The Student Transaction table will be a fact table -- that's easy.
And the Student will be a dimension of the Student Transaction table.
HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
of its own -- things like State, Country, etc. It's basically what
Kimball calls a rapidly changing monster dimension!
First - isn't this snowflaking, and is that bad? Should the Student
be another fact? But then how do I relate the Student to the Student
Transaction if they're both facts.
Also, the student has attributes like test scores and GPA that seem
like measures -- but a dimension can't have a measure. So how could I
get average test scores and gpa, etc...
The other thing I was thinking of doing was treating the Student as
both a dimension AND a fact! Basically create 2 cubes:
Student Transaction cube, which has Student table as a dimension
Student cube, which actually uses the same Student table, but as a
fact.
This seems to work, but it seems awfully weird to use the same
physical table as both a fact and a dimension... even if it is in
different cubes...
IF anyone has insight, i would appreciate it very much!
thanks
In your case Stundent is a Dimension, average, etc are properties of the dimension not measures.
It's a better practice to design the dimension with star structure. You can make a view to group all information an make the dimension based on that view.
Also you can think several cubes that uses Student dimension and time dimension, like test, etc. And then you can calcultate average and other. A way to join these cubes is with virtual cubes.
Good Luck
|||The "badness" of snowflaking is highly overrated. Conversely, the benefits
of denormalized stars are also overrated.
public @. the domain below
www.tomchester.net
"groove_sf" <dockv@.hot-NOSPAM-mail.com> wrote in message
news:cfpu70dvqh0s8c2p1etalnjdrvos9et1mf@.4ax.com...
> Hi - I've got a dimensional modelling question that's got me stuck.
> I have a "Student" entity and a "Student Transaction" entity.
> The Student Transaction table will be a fact table -- that's easy.
> And the Student will be a dimension of the Student Transaction table.
> HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
> of its own -- things like State, Country, etc. It's basically what
> Kimball calls a rapidly changing monster dimension!
> First - isn't this snowflaking, and is that bad? Should the Student
> be another fact? But then how do I relate the Student to the Student
> Transaction if they're both facts.
> Also, the student has attributes like test scores and GPA that seem
> like measures -- but a dimension can't have a measure. So how could I
> get average test scores and gpa, etc...
> The other thing I was thinking of doing was treating the Student as
> both a dimension AND a fact! Basically create 2 cubes:
> Student Transaction cube, which has Student table as a dimension
> Student cube, which actually uses the same Student table, but as a
> fact.
> This seems to work, but it seems awfully weird to use the same
> physical table as both a fact and a dimension... even if it is in
> different cubes...
> IF anyone has insight, i would appreciate it very much!
> thanks
>
|||I disagree that the GPA and test scores should be part of the Student dimension. They are semi-additive facts, and should be considered for a separate star in your constellation (assuming you are using an architected DW with conformed dimensions.)
Think about it this way - if you have student transactions as one "business process", thus is one star center, then GPA and test scores would merit its own cetricity in a separate fact table, since GPA and test scores are not transactions, but are "Grades
", though GPA is going to need to be atomized into GPA factoids so it can be calculated on the fly if you have other dimensions that will be used to slice and dice. For example, you might want GPA by students by a particular major, or students in the cla
ss of 2005, etc.
Thus you would have Student as a conforming dimension to both a Transactions fact table and a Grades fact table, thus the need for at least two stars in your constellation that share the conformed Student dimension.
sql

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
--Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
--Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
--Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_TranChar(4),--FULL or TRAN
@.ServerAliasVarChar(5),--CLI or FIN or OM or RPT
@.db_nameVarchar(150),--Name of Database
@.StripesInt--Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPathVarchar(8000),
@.ServerNameVarchar(200),--Name of server
@.StripeNumint,
@.RuntimeDateTime,
@.CompressFileVarchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
'\'
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_TranChar(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = ''''' + @.FILE + ''''''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = ''''' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + ''''''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Under the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:

> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_TranChar(4),--FULL or TRAN
> @.ServerAliasVarChar(5),--CLI or FIN or OM or RPT
> @.db_nameVarchar(150),--Name of Database
> @.StripesInt--Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPathVarchar(8000),
> @.ServerNameVarchar(200),--Name of server
> @.StripeNumint,
> @.RuntimeDateTime,
> @.CompressFileVarchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
> '\'
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_TranChar(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = ''''' + @.FILE + ''''''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = ''''' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + ''''''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Different procedures for SQL 2000 and 2005

I've got a stored procedure that now needs to be a little bit
different when my db has been installed on SQL 2000 vs SQL 2005. It's
created as part of the larger install script and my first thought was
that I keep a single install script and the script creates one or the
other version of the procedure depending on the SQL version that it's
being run on.
But when I try something like this:
if cast(serverproperty('productversion') as varchar(1)) = '8' --
8=2000 9=2005
create procedure xyz as
begin
set nocount on
-- SQL 2000 version
end
else
create procedure xyz as
begin
set nocount on
-- SQL 2005 version
end
Obviously it's not going to work; I get these errors from QA on SQL
2000, with pretty much the same from SSMS on 2005:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'procedure'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'else'.
Server: Msg 111, Level 15, State 1, Line 8
'CREATE PROCEDURE' must be the first statement in a query batch.
Can I do this? Or do I have to have two separate scripts, one for
2000 another for 2005 (and next year maybe a third script for 2008),
that are almost identical? Or use dynamic sql inside the if and else
to perform the creates (yuck) ? Or ...?
I've tried googling for how to handle this scenario, but no joy .
Can't believe that I'm the only one to encounter a multi-version
supporting need. How's this usually handled?
Thanks!
Hi Mark
You will need to make the procedure definition dynamic SQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xyz]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xyz]
IF LEFT(CAST(serverproperty('productversion') AS VARCHAR(128)),
CHARINDEX('.',CAST(serverproperty('productversion' ) AS VARCHAR(128)))-1) = '8'
BEGIN
EXEC ( '
create procedure xyz as
begin
set nocount on
-- SQL 2000 version
SELECT ''SQL 2000''
end
' )
END
ELSE
BEGIN
EXEC ( '
create procedure xyz as
begin
set nocount on
-- SQL 2005 version
SELECT ''SQL 2005''
end
' )
END
If you were using version control it would not be an issue to provide two
different sets of scripts and then you would only need to check the version
in the installer once.
John
"Mark Lemoine" wrote:

> I've got a stored procedure that now needs to be a little bit
> different when my db has been installed on SQL 2000 vs SQL 2005. It's
> created as part of the larger install script and my first thought was
> that I keep a single install script and the script creates one or the
> other version of the procedure depending on the SQL version that it's
> being run on.
> But when I try something like this:
> if cast(serverproperty('productversion') as varchar(1)) = '8' --
> 8=2000 9=2005
> create procedure xyz as
> begin
> set nocount on
> -- SQL 2000 version
> end
> else
> create procedure xyz as
> begin
> set nocount on
> -- SQL 2005 version
> end
> Obviously it's not going to work; I get these errors from QA on SQL
> 2000, with pretty much the same from SSMS on 2005:
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'procedure'.
> Server: Msg 156, Level 15, State 1, Line 7
> Incorrect syntax near the keyword 'else'.
> Server: Msg 111, Level 15, State 1, Line 8
> 'CREATE PROCEDURE' must be the first statement in a query batch.
> Can I do this? Or do I have to have two separate scripts, one for
> 2000 another for 2005 (and next year maybe a third script for 2008),
> that are almost identical? Or use dynamic sql inside the if and else
> to perform the creates (yuck) ? Or ...?
> I've tried googling for how to handle this scenario, but no joy .
> Can't believe that I'm the only one to encounter a multi-version
> supporting need. How's this usually handled?
> Thanks!
>

Different execution plans from c# code and MSSQLServerManagementStudio

Hi all,
I've been looking into a strange problem in the last weeks.
We use SQL Server 2005.
We have some code written in C# which goes to the database, executes
some stored procedures and retrieves the results.
Now, the procedures are a little bit strange, as in a procedure we
dynamically create the SELECT statement with different WHERE clauses
based on the parameters we receive and then use exec sp_executesql to
actually run the statement. (I assume that this prevents sql server to
compile the final statements and reuse the compiled execution plan,
right?)
All is well for most of the time. Sometimes though some of the
procedures get very slow.
For example a procedure which would usually run in 100ms now takes up
to 5 seconds. Or one which normally ran in 1 sec, runs more than 30
second.
I started examining with the SQL Server Profiler, and got strange
results:
- I launch the C# code, it calls the SP and the trace shows a Duration
of 5000 ms.
- I copy the statement executed in SQL Management Studio, run from
there and the Duration is 90ms
(Note that when I compared the SQLManagementStudio and C# runs of the
procedures, I used the same parameters).
I configured the profiler to show the execution plans, and the two
plans are different. It is clear that when I run the query from the SQL
management studio, it uses the right indexes, but when running from the
C# code it won't use the good indexes.
Interestingly I can run them alternatively several times (once from SQL
Management Studio, once from C#, again from SQLMS, again from C#,
a.s.o), and the results are unchanged: good performance from SQLMS, bad
performance from C#.
As our procedures generate dynamic sql statements, I assume that no
execution plan is reused here. The two exceutino plans generated are
just plain different.
If I restart the SQL Server, everything goes back to normal: the
procedures run fast from C#. After a day or two the performance problem
pops up in the same place, or in some other (but very similar)
procedure.
If I modify the procedure to include a WITH(Index(...)) hint,
everything is fast :-)
I also qualify the owner of the stored procedure when I call it.
(dbo.sp_ProcName, xxx.sp_AnotherProcName a.s.o).
In order to minimize the effect of the ,net framework classes, I
reproduced the calls using SqlConnection/SqlCommand,
OleDBConnection/OleDBCommand too. One of my colleagues also reproduced
it with code written in VB6 (non .net) (running from a different
machine).
Any idea why the C# call will generate a bad execution plan? And how I
could make sure it generates the good one (except using the
WITH(INDEX()) hints)?
Thank you,
Trucza Csaba
Hi
> As our procedures generate dynamic sql statements, I assume that no
> execution plan is reused here. The two exceutino plans generated are
> just plain different.
No, SQL Server could resuse an execution plan otherwise we would have seen
RECOMPILE statement in Profiler
Well , there are many scenarios that make hurt perfomance as we know as
"parameter sniffing"
Search on interenet for this tiltle to get more details as well please read
this article may shed some lights on.
http://www.sql-server-performance.com/q&a132.asp
http://www.sql-server-performance.co...ess_tuning.asp
<csaba.trucza@.gmail.com> wrote in message
news:1143715907.378509.198330@.j33g2000cwa.googlegr oups.com...
> Hi all,
> I've been looking into a strange problem in the last weeks.
> We use SQL Server 2005.
> We have some code written in C# which goes to the database, executes
> some stored procedures and retrieves the results.
> Now, the procedures are a little bit strange, as in a procedure we
> dynamically create the SELECT statement with different WHERE clauses
> based on the parameters we receive and then use exec sp_executesql to
> actually run the statement. (I assume that this prevents sql server to
> compile the final statements and reuse the compiled execution plan,
> right?)
> All is well for most of the time. Sometimes though some of the
> procedures get very slow.
> For example a procedure which would usually run in 100ms now takes up
> to 5 seconds. Or one which normally ran in 1 sec, runs more than 30
> second.
> I started examining with the SQL Server Profiler, and got strange
> results:
> - I launch the C# code, it calls the SP and the trace shows a Duration
> of 5000 ms.
> - I copy the statement executed in SQL Management Studio, run from
> there and the Duration is 90ms
> (Note that when I compared the SQLManagementStudio and C# runs of the
> procedures, I used the same parameters).
> I configured the profiler to show the execution plans, and the two
> plans are different. It is clear that when I run the query from the SQL
> management studio, it uses the right indexes, but when running from the
> C# code it won't use the good indexes.
> Interestingly I can run them alternatively several times (once from SQL
> Management Studio, once from C#, again from SQLMS, again from C#,
> a.s.o), and the results are unchanged: good performance from SQLMS, bad
> performance from C#.
> As our procedures generate dynamic sql statements, I assume that no
> execution plan is reused here. The two exceutino plans generated are
> just plain different.
> If I restart the SQL Server, everything goes back to normal: the
> procedures run fast from C#. After a day or two the performance problem
> pops up in the same place, or in some other (but very similar)
> procedure.
> If I modify the procedure to include a WITH(Index(...)) hint,
> everything is fast :-)
> I also qualify the owner of the stored procedure when I call it.
> (dbo.sp_ProcName, xxx.sp_AnotherProcName a.s.o).
> In order to minimize the effect of the ,net framework classes, I
> reproduced the calls using SqlConnection/SqlCommand,
> OleDBConnection/OleDBCommand too. One of my colleagues also reproduced
> it with code written in VB6 (non .net) (running from a different
> machine).
> Any idea why the C# call will generate a bad execution plan? And how I
> could make sure it generates the good one (except using the
> WITH(INDEX()) hints)?
> Thank you,
> Trucza Csaba
>
|||Hi Uri,
Uri wrote:
> No, SQL Server could resuse an execution plan otherwise we would have seen
> RECOMPILE statement in Profiler
That SQL Server reuses the execution plan of dynamic statements came to
me as a major surprise. Pleasant one.
I'm still confused though about why the execution plan differs when I
execute the same stored procedure with the same parameters from C# code
and from SQL Management Studio.
Cheers,
Csaba
|||> That SQL Server reuses the execution plan of dynamic statements came to
> me as a major surprise. Pleasant one.
But not always the best thing to to. In most cases, you will get an "exact text" matching of the
plan. If you search for different names, for example, you will have different plans for what in the
application is the same query. I've seen cases when the customer had 10,000 plans for the same query
in the plan cache. You can identify this in syscacheobjects by checking for "AdHoc" object types.

> I'm still confused though about why the execution plan differs when I
> execute the same stored procedure with the same parameters from C# code
> and from SQL Management Studio.
One possible reason can be different SET options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<csaba.trucza@.gmail.com> wrote in message
news:1143803884.817874.61660@.g10g2000cwb.googlegro ups.com...
> Hi Uri,
> Uri wrote:
> That SQL Server reuses the execution plan of dynamic statements came to
> me as a major surprise. Pleasant one.
> I'm still confused though about why the execution plan differs when I
> execute the same stored procedure with the same parameters from C# code
> and from SQL Management Studio.
> Cheers,
> Csaba
>

Different answers on limits in MSDE

I've seen a number of reputable NG posts (including one from an MVP)
reffering to the 5 concurrent connection limit of MSDE.
However I found this MS site reffering to an 8 operations limit for
MSDE?
"The Microsoft=AE SQL Server=99 2000 workload governor is designed to
limit the performance of an instance of the database engine any time
more than eight operations are active at the same time."
http://msdn.microsoft.com/library/?u...ec/8_ar_sa2_0=
ciq.asp?frame=3Dtrue
I have also read in the NG's that you can use the Performance
Monitoring counter called SQLServer:General Statistics\User Connections
to see if you go over the threshold. I've been regularly jumping to 10
User Connections but when I run DBCC CONCURRENCYVIOLATION to check it
says the following . . .
Concurrency violations since 2005-09-21 07:16:11.217
1 2 3 4 5 6 7 8 9 10-100 >100
0 0 0 0 0 0 0 0 0 0 0
Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
This indicates I've not went over any limits.
Can anyone give a definitive answer on this one?
Tostao
hi,
Tostao wrote:
> I've seen a number of reputable NG posts (including one from an MVP)
> reffering to the 5 concurrent connection limit of MSDE.
> However I found this MS site reffering to an 8 operations limit for
> MSDE?
> "The Microsoft SQL ServerT 2000 workload governor is designed to
> limit the performance of an instance of the database engine any time
> more than eight operations are active at the same time."
> http://msdn.microsoft.com/library/?u...asp?frame=true
>
> I have also read in the NG's that you can use the Performance
> Monitoring counter called SQLServer:General Statistics\User
> Connections
> to see if you go over the threshold. I've been regularly jumping to 10
> User Connections but when I run DBCC CONCURRENCYVIOLATION to check it
> says the following . . .
> Concurrency violations since 2005-09-21 07:16:11.217
> 1 2 3 4 5 6 7 8 9 10-100 >100
> 0 0 0 0 0 0 0 0 0 0 0
> Concurrency violations will be written to the SQL Server error log.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> This indicates I've not went over any limits.
> Can anyone give a definitive answer on this one?
you are right... the limit (not an actual limit, but a condition that makes
the built-in Query Governor kicks in) is not in 5 conncurrent connections
but 8 concurrent workloads, and actually only the ones listed in
http://msdn.microsoft.com/library/de...r_sa2_0ciq.asp )
you can stay in this number even with more active connections, as active
connections can be not all "working" at the same time but just sleeping in a
rounding way.. thus you do not exceed that "limit"...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for the prompt response Andrea. I should add that the MVP who
gave the contradicitory info was from microsoft.public.access and not
this NG.
One final question. I'm a Sys Admin not a DBA, so rather than use the
DBCC CONCURRENCYVIOLATION function, is there a Performance Monitor
counter that I can use to see if I'm nearing the 8 concurrent
operations on my MSDE instance?
|||hi,
Tostao wrote:
> Thanks for the prompt response Andrea. I should add that the MVP who
> gave the contradicitory info was from microsoft.public.access and not
> this NG.
> One final question. I'm a Sys Admin not a DBA, so rather than use the
> DBCC CONCURRENCYVIOLATION function, is there a Performance Monitor
> counter that I can use to see if I'm nearing the 8 concurrent
> operations on my MSDE instance?
not that I'm aware of..
and I think becouse DBCC CONCURRENCYVIOLATION is not evaluated at all on
full blown SQL Server editions and oly returns
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and not the kind of info reported on Personal Edition and MSDE 2000...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||There will be a message in the SQL Server error log when the throttle kicks
in. A warning is not all that valuable because the concurrent activity
limit is a transient thing. You might have 20 users logged on and never hit
it and in other cases you might hit it with 8 users if they are all doing
long running queries simultaneously. Hitting the limit isn't catastrophic -
it just injects a delay into the activities so other than poor performance,
your users won't notice.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:42ffrcF1iql40U1@.individual.net...
> hi,
> Tostao wrote:
> not that I'm aware of..
> and I think becouse DBCC CONCURRENCYVIOLATION is not evaluated at all on
> full blown SQL Server editions and oly returns
> --
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and not the kind of info reported on Personal Edition and MSDE 2000...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Thanks Roger - the background behind my request is I'm working on a new
Credit Card payment system for my employer. The solution is coming from
a vendor who usually implement MSDE.
Although it's structurally the same as SQL 2000, I am not comfortable
using MSDE. We're going to install the Enterprise Manager tools even if
we are forced into MSDE. This means we're liable to pay for the full
SQL license.
So I'm basically looking at the MSDE limitations to put a case to the
vendor to make a special exception for us and support installing their
programme onto a full version MS-SQL

Differences in ANSI SQL and MSDE Options

I have a database, and I want to run an update script on it. I
understand ANSI standard script. I've used Oracle 10g to learn
databases, and I've used C# for a while now with SQLCommand,
SQLConnection, etc. Now, I'm trying to update a SQL Server (I want to
say 2003 Compact...not sure). I have the script that attached this
database. It's biggest change in the actual running of script is
using GO, and it also doesn't have semi-colons at the end of
statements. Well, I need to know any other nuances like that when
using CREATE DOMAIN, CREATE TABLE, and ALTER TABLE. I'm not sure when
to use GO as I've seen people use it with plenty of commands and few
as well.
Another part that throws me for a loop (mostly cause I can't find
documentation or tutorials) is that it keeps using commands like "exec
sp_dboption," "exec sp_change_users_login," "exec
sp_addsrvrolemember," "exec sp_addrolemember," "GRANT ... CREATE RULE
TO." I'm more worried about how persistent these settings are than I
am anything else.
I just need to find a tutorial or some other form of help to figure
out what else needs to go into my .sql file that has all my DDL
statements. Any help would be appreciated. Any thoughts?
hi,
probably you will find better ANSI compliance if you move to SQLExpress, the
free edition of SQL Server 2005..
anyway..
Gold Panther wrote:
> I have a database, and I want to run an update script on it. I
> understand ANSI standard script. I've used Oracle 10g to learn
> databases, and I've used C# for a while now with SQLCommand,
> SQLConnection, etc. Now, I'm trying to update a SQL Server (I want to
> say 2003 Compact...not sure). I have the script that attached this
> database. It's biggest change in the actual running of script is
> using GO, and it also doesn't have semi-colons at the end of
> statements.
GO is not a SQL keyword.. it's just a batch terminator used in some
interactive tools like Enterprise Manager, Quary Analyzer, SQL Server
Management Studio, oSql.exe, SqlCMD.exe...
as these are the "official" tools provided by Microsoft, GO has become the
"standard" batch terminator in Microsoft SQL Server world...

>Well, I need to know any other nuances like that when
> using CREATE DOMAIN, CREATE TABLE, and ALTER TABLE. I'm not sure when
> to use GO as I've seen people use it with plenty of commands and few
> as well.
you can find lot of these in BooksOn Line, the official guide to SQL Server,
available for free downloadat:
SQL Server 2005 -
http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
SQL Server 2000 -
http://www.microsoft.com/technet/prodtechnol/sql/2000/downloads/docs/default.mspx
BTW, CREATE DOMAIN is not supported in Microsoft SQL Server..

> Another part that throws me for a loop (mostly cause I can't find
> documentation or tutorials) is that it keeps using commands like "exec
> sp_dboption," "exec sp_change_users_login," "exec
> sp_addsrvrolemember," "exec sp_addrolemember," "GRANT ... CREATE RULE
> TO."
these "kind of" statements are proprietary system stored procedures to
perform management tasks..
again, you can find them and their explanation in BOL..

> I'm more worried about how persistent these settings are than I
> am anything else.
eventually please review the "depracation" status of some
keywords/procedures/etc..
for instance, SQL Server 7.0 and 2000 used to attach databases via a system
stored procedure, sp_attach_db, now deprecated (in SQL Server 2005) in
favour of a proprietary extension of the CREATE DATABASE statement, CREATE
DATABASE ... FOR ATTACH, which perform the very same action, but can be
removed in future versions of SQL Server...
sp_adduser deprecated in favour of CREATE USER ... etc...

> I just need to find a tutorial or some other form of help to figure
> out what else needs to go into my .sql file that has all my DDL
> statements. Any help would be appreciated. Any thoughts?
you can have a look in BOL at the supported syntax of each DDL statement as
long as it's requirements..
regards
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||First, make sure what version of SQL Server you're targeting. "SQL Server"
Compact Edition is not really SQL Server--it's SQL Mobile with a new name.
It does not share the same SQL engine as SQL Server.
If the purpose of this exercise is to move database schema from another
existing database I suggest using SQL Server Integration Services (SSIS) to
do the job. This utility can do everything that needs to be done without you
having to build and convert a bunch of scripts.
As you've been told, a SQL script (a set of SQL batch statements) is a file
that separates the individual batches that can't run sequentially with the
"GO" keyword. No, this is not TSQL or any SQL--it's used by all of the
Microsoft SQL utilities to help parse the batches. SQLCMD (or ISQL/OSQL, SQL
Server Management Studio or Visual Studio) all recognize this file syntax
for SQL scripts.
It usually takes more that a simple tutorial on scripts to get one's head
around the utilities used by SQL Server to configure a database, add users,
set the appropriate rights and everything else you seem to be
encountering...
I think my book might help...
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
------
Microsoft MVP, Author, Mentor
Microsoft MVP
"Gold Panther" <lordbrucegiles@.yahoo.com> wrote in message
news:1176491620.113814.27300@.d57g2000hsg.googlegro ups.com...
>I have a database, and I want to run an update script on it. I
> understand ANSI standard script. I've used Oracle 10g to learn
> databases, and I've used C# for a while now with SQLCommand,
> SQLConnection, etc. Now, I'm trying to update a SQL Server (I want to
> say 2003 Compact...not sure). I have the script that attached this
> database. It's biggest change in the actual running of script is
> using GO, and it also doesn't have semi-colons at the end of
> statements. Well, I need to know any other nuances like that when
> using CREATE DOMAIN, CREATE TABLE, and ALTER TABLE. I'm not sure when
> to use GO as I've seen people use it with plenty of commands and few
> as well.
> Another part that throws me for a loop (mostly cause I can't find
> documentation or tutorials) is that it keeps using commands like "exec
> sp_dboption," "exec sp_change_users_login," "exec
> sp_addsrvrolemember," "exec sp_addrolemember," "GRANT ... CREATE RULE
> TO." I'm more worried about how persistent these settings are than I
> am anything else.
> I just need to find a tutorial or some other form of help to figure
> out what else needs to go into my .sql file that has all my DDL
> statements. Any help would be appreciated. Any thoughts?
>
|||Thank both of you for the help. I believe you helped me find the
information I need. By the way, I started to buy your book Mr.
Vaughn, but I ran into multiple books of your 13 (I think that's how
many you said you have) that involve databases. I was not sure at the
time which to look into. If I look again today, I will probably be
able to determine that as it was Friday afternoon when I looked the
first time. Everybody gets a little drained by the end of the week.
Thank you both though.
|||Ah, yes. The most current, comprehensive and most complete is Hitchhiker's
Guide to Visual Studio and SQL Server (7th Edition). Let me know if it
helps--I'm convinced it will.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
------
Microsoft MVP, Author, Mentor
Microsoft MVP
"Gold Panther" <lordbrucegiles@.yahoo.com> wrote in message
news:1176730402.516417.311350@.w1g2000hsg.googlegro ups.com...
> Thank both of you for the help. I believe you helped me find the
> information I need. By the way, I started to buy your book Mr.
> Vaughn, but I ran into multiple books of your 13 (I think that's how
> many you said you have) that involve databases. I was not sure at the
> time which to look into. If I look again today, I will probably be
> able to determine that as it was Friday afternoon when I looked the
> first time. Everybody gets a little drained by the end of the week.
> Thank you both though.
>
|||William (Bill) Vaughn wrote:
> Ah, yes. The most current, comprehensive and most complete is
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition). Let
> me know if it helps--I'm convinced it will.
>
I only have the 6th edition :D
great book..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||The 6th Edition was written before I left MS--almost a decade ago. A lot has
changed since then but a lot has really remained the same. The new book is a
total re-write from all of my books. All of the examples are new but many of
the best concepts are included and brought up to date. The 7th Edition is my
last book (on paper)--at least on technical subjects. I'm working on a
novel... ;)
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
------
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:58jhtvF2g5dshU1@.mid.individual.net...
> William (Bill) Vaughn wrote:
> I only have the 6th edition :D
> great book..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
|||> > William (Bill) Vaughn wrote:[vbcol=seagreen]
I will be testing that section once we get our test system in. If it
doesn't work the way I have it, I will definitely buy the book.
Thanks.
Off subject a little but what's the novel about?
|||It's about a clan of beings that live in the forest with a few magical
powers...
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Gold Panther" <lordbrucegiles@.yahoo.com> wrote in message
news:1176993337.593923.228560@.y80g2000hsf.googlegr oups.com...
> I will be testing that section once we get our test system in. If it
> doesn't work the way I have it, I will definitely buy the book.
> Thanks.
> Off subject a little but what's the novel about?
>
|||On Apr 19, 9:25 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> It's about a clan of beings that live in the forest with a few magical
> powers...
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "Gold Panther" <lordbrucegi...@.yahoo.com> wrote in message
> news:1176993337.593923.228560@.y80g2000hsf.googlegr oups.com...
>
>
>
> - Show quoted text -
That sounds great! I might buy that when you're done too. : )