Developing a Windows Authentication Security Model

I would like to use Windows Authentication for SQL Server 2005. I'm just no
w
trying to determine how this will work in our environment. Here are some of
my requirements.
- A given users may have different right in the each environment
(Development, QA, and production.
- A user will gain access to sql server via ione or more Domain Groups
- Do not want to change security permissions on a database when migrating it
from one environment to another.
So my real problem is how can a single windows account (user) have different
rights on say database ABC for each environment and meet the above
requirements.
Ok let's say our user is named "domain\user1" and they need to following
access in each environment:
Select, insert, update, and delete permissions on table ABC.dbo.MyTable in
development.
Select, update permissions on table ABC.dbo.MyTable in QA
Select only permissions on table ABC.dbo.MyTable in Production.
So my questions is how do I define one or more windows group that
"domain\user1" would be a member so when I assign the windows group to a SQL
and grant them access to a database that the access changes depending on the
environment for which the database is being accessed?
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamplesLet me add that the development environment has a separate domain, but the Q
A
and production environment have the same domain.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:

> I would like to use Windows Authentication for SQL Server 2005. I'm just
now
> trying to determine how this will work in our environment. Here are some
of
> my requirements.
> - A given users may have different right in the each environment
> (Development, QA, and production.
> - A user will gain access to sql server via ione or more Domain Groups
> - Do not want to change security permissions on a database when migrating
it
> from one environment to another.
> So my real problem is how can a single windows account (user) have differe
nt
> rights on say database ABC for each environment and meet the above
> requirements.
> Ok let's say our user is named "domain\user1" and they need to following
> access in each environment:
> Select, insert, update, and delete permissions on table ABC.dbo.MyTable in
> development.
> Select, update permissions on table ABC.dbo.MyTable in QA
> Select only permissions on table ABC.dbo.MyTable in Production.
> So my questions is how do I define one or more windows group that
> "domain\user1" would be a member so when I assign the windows group to a S
QL
> and grant them access to a database that the access changes depending on t
he
> environment for which the database is being accessed?
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples

Developer Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/d.../>
ll_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:

> Bruce Thomas wrote:
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/d...
tall_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Developer Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?
Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/de...stall_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:

> Bruce Thomas wrote:
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/de...stall_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Developer Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:
> Bruce Thomas wrote:
> > I have been trying to determine why an stored proc that I wrote that
> > makes a single random selection from a list of about 3 million phone
> > numbers works slowly in a particular client's office when in use by
> > multiple Clients. During investigation I found that the IT
> > entrepreneur who supplied and installed my Client's software provided
> > a Developer's edition of SLQ Server. As it is illegal to use this
> > Edition commercially, my Client will be upgrading to an appropriate
> > version before the app goes into commercial use. Meanwhile I would
> > like to know if the Developer's Edition includes a concurrent
> > workload governor like the the Personal Edition or any other kind of
> > governor that would result in performance degradation?
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Determining windows grouop membership of a SQL login

Hello,
I'm trying to determine the windows group membership of a SQL login I've
created from a Windows login. Specifically, I'm trying to determine if the
login is in BUILTIN\Administrators or otherwise has admin permissions. For
example,
CREATE LOGIN <login> FROM WINDOWS WITH DEFAULT_DATABASE <database>
USE <database>
CREATE USER <login>
The CREATE USER call will fail if the login has admin privs because they
already have access under 'dbo'. So how do I go about determining the
membership of this login? sp_helplogins, sp_helpuser, sp_helpgroup don't
seem to provide the information I need.
Suggestions?
KeithKeith,
You could check with: is_srvrolemember ('sysadmin','Domain\Loginame')
RLF
"Keith" <keith@.alh.com> wrote in message
news:uBrsCyq2HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I'm trying to determine the windows group membership of a SQL login I've
> created from a Windows login. Specifically, I'm trying to determine if
> the login is in BUILTIN\Administrators or otherwise has admin permissions.
> For example,
> CREATE LOGIN <login> FROM WINDOWS WITH DEFAULT_DATABASE <database>
> USE <database>
> CREATE USER <login>
> The CREATE USER call will fail if the login has admin privs because they
> already have access under 'dbo'. So how do I go about determining the
> membership of this login? sp_helplogins, sp_helpuser, sp_helpgroup don't
> seem to provide the information I need.
> Suggestions?
> Keith
>|||Thanks Russell! That seems to do the trick!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:O5C3HQr2HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Keith,
> You could check with: is_srvrolemember ('sysadmin','Domain\Loginame')
> RLF
> "Keith" <keith@.alh.com> wrote in message
> news:uBrsCyq2HHA.2752@.TK2MSFTNGP06.phx.gbl...
>

DETERMINING WHICH HIERARCHY OF A DIMENSION IS SELECTED

I am trying to use MDX to dynamically determine which hierarchy of the
time dimension is selected. I have a calculated member which is using
the last non-empty descendant of the [Time].[Calendar] dimension, but
I want it to work even if Fiscal is selected instead of Calendar.
Here is the code

SUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [Calendar
Date]), isEmpty([Measures].[Employee Count]) = False), 1),
[Measures].[Employee Count])

What can I put in place of [Time].[Calendar].CurrentMember to take the
current hierarchy of the time dimension instead of the Calendar
hierarchy? Thanks!"Sara" <sara2thebradley@.yahoo.com> wrote in message
news:ce695524.0402030731.75f8ea41@.posting.google.c om...
> I am trying to use MDX to dynamically determine which hierarchy of the
> time dimension is selected. I have a calculated member which is using
> the last non-empty descendant of the [Time].[Calendar] dimension, but
> I want it to work even if Fiscal is selected instead of Calendar.
> Here is the code
> SUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [Calendar
> Date]), isEmpty([Measures].[Employee Count]) = False), 1),
> [Measures].[Employee Count])
>
> What can I put in place of [Time].[Calendar].CurrentMember to take the
> current hierarchy of the time dimension instead of the Calendar
> hierarchy? Thanks!

You'll probably get a better response by posting this in
microsoft.public.sqlserver.olap, as it's quite a specialized area.

Simon

Determining what stored procedures are used

Hi,
We have a large database with over 1000 stored procedures,
we need to determine when last these procedures where used
so we can remove and redundant/unused procedures. Is
there any way to do this using SQL Server 2000?
Thanks,
StephenStephen, the answer is simple: yoes, you should use the Profiler. Check this
tool in Books OnLine, it is very powerful!
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Stephen Young" <stephen_c_young@.hotmail.com> wrote in message
news:1af101c3737d$540f7030$a301280a@.phx.gbl...
> Hi,
> We have a large database with over 1000 stored procedures,
> we need to determine when last these procedures where used
> so we can remove and redundant/unused procedures. Is
> there any way to do this using SQL Server 2000?
> Thanks,
> Stephen|||Thanks, will give it a shot.
>--Original Message--
>Stephen, the answer is simple: yoes, you should use the
Profiler. Check this
>tool in Books OnLine, it is very powerful!
>--
>Dejan Sarka, SQL Server MVP
>FAQ from Neil & others at: http://www.sqlserverfaq.com
>Please reply only to the newsgroups.
>PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"Stephen Young" <stephen_c_young@.hotmail.com> wrote in
message
>news:1af101c3737d$540f7030$a301280a@.phx.gbl...
>> Hi,
>> We have a large database with over 1000 stored
procedures,
>> we need to determine when last these procedures where
used
>> so we can remove and redundant/unused procedures. Is
>> there any way to do this using SQL Server 2000?
>> Thanks,
>> Stephen
>
>.
>

Determining what columns are all Null

Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean

Hi,

I wrote a procedure for that:

ALTER PROCEDURE spDetermineNullColumns

(

@.TableName SYSNAME

)

AS

BEGIN

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

DECLARE @.HIT INT

DECLARE @.sql NVARCHAR(2000)

SELECT IDENTITY(INT,1,1) AS IDCol,COLUMN_NAME

INTO #NullableColumns

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @.TableName AND IS_NULLABLE = 'YES'

SET @.ROWCOUNT = @.@.ROWCOUNT

SET @.I = 1

WHILE @.I <= @.ROWCOUNT

BEGIN

SET @.Hit = 0

SELECT @.sql = N'SELECT @.Hit = 1 '+ ' FROM ' + @.TableNAME +

' WHERE ' + COLUMN_NAME + ' IS NOT NULL'

FROM #NullableColumns WHERE IDCol = @.I

PRINT @.SQL

EXEC sp_executesql

@.query = @.sql,

@.params = N'@.Hit INT OUTPUT',

@.Hit = @.Hit OUTPUT

/* This here can be customized as you want to

IF @.Hit = 0

SELECT COLUMN_NAME + ' is completly NULL.'

FROM #NullableColumns WHERE IDCol = @.I

SET @.I = @.I +1

*/

END

END

GO

spDetermineNullColumns 'Categorie'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||DId that help Sean ?|||

You could do something like below:

select min(case when col1 is not null then 0 else 1 end) as col1_is_all_null

, min(case when col2 is not null then 0 else 1 end) as col2_is_all_null

...

from tbl

Determining trigger execution state

Is there a way to determine if a trigger fired due to an insert or update
operation. I need to know this for the following reason. I have fields in
my tables that I want to initialize only when a record is inserted. If the
record is later updated, I do not want these fields changed. I also do not
want to have to maintain two seperate triggers to do this. In SqlAnywhere,
they have variables you check in the trigger to determine this as the
following example illustrates.
IF Inserting
!do inserting code
END IF
IF Updating
!do updating code
END IF
Is there something similar to this in SQL server?
Thanks in advance for any help.
Mark GenovyHi Mark
If any rows were affected, an insert trigger will only populate the inserted
table, but an update trigger will populate both inserted and deleted. So I
usually just look at the count(*) value from these tables. However, if no
rows were affected, the triggers will still fire, and both inserted and
deleted will have 0 rows. So then you can't tell what operation occurred.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Genovy" <Mark Genovy@.discussions.microsoft.com> wrote in message
news:24381F86-0662-4913-A703-139BC0A9DF46@.microsoft.com...
> Is there a way to determine if a trigger fired due to an insert or update
> operation. I need to know this for the following reason. I have fields
> in
> my tables that I want to initialize only when a record is inserted. If
> the
> record is later updated, I do not want these fields changed. I also do
> not
> want to have to maintain two seperate triggers to do this. In
> SqlAnywhere,
> they have variables you check in the trigger to determine this as the
> following example illustrates.
> IF Inserting
> !do inserting code
> END IF
> IF Updating
> !do updating code
> END IF
> Is there something similar to this in SQL server?
> Thanks in advance for any help.
> Mark Genovy

determining the where statement

Hello,

Does anyone know how I can write the where statement according to the value?
In other words,
a command name would determine the where statement but the select statement is always the same.

This does not work:

create procedure new_procedure
(@.cid int, @.command VarChar(25))
Select * from a table
If @.command = 'pick1' begin
Where this = @.cid and that = 'Yes'
End
Else If @.command = 'pick2' begin
Where this = @.cid and that = 'No'
End
Else If @.command = 'pick3' begin
Where this = @.cid and that = 'No' and datefield >= DateAdd(d, -5, getdate())
End

something like that there.
Any ideas?
Thanks
Ehow many distinct values for @.command do you see ? is that something fixed like 3-4 options or would you have plenty of them..if you have only 3-4 options like you have above then you can do something like:

create procedure new_procedure
(@.cid int, @.command VarChar(25)) AS
begin
If @.command = 'pick1'
 select * from table Where this = @.cid and that = 'Yes'
Else If @.command = 'pick2'
 select * from table Where this = @.cid and that = 'No'
Else If @.command = 'pick3'
 select * from table Where this = @.cid and that = 'No' and datefield >= DateAdd(d, -5,
 getdate())
end

HTH

Determining the number of Active users logged into a SQL Server Database

How does one Determine the number of Active users logged into a SQL Server Database?

I want to use the info to control concurrent licensing for my Application

This query will give you a count grouped by DB, you can also use sp_who or sp_who2

select db_name(dbid),count(*) from master..sysprocesses
where spid > 49 --everyting below 50 is SQL Server itself
group by db_name(dbid)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks, I will. I believe this is exactly what I am looking for. Is it safe to assume that the data is available in "real time"? Also if a user logged in to the DB drops his connection by doing something stupid like powering off his workstation without closing the app first, will the count be reduced, the next time it is run?|||Yes the data is real time, the server connection will disapear once the connection times out or the connection has been removed from the connection pool.

Determining the most appropriate conversion for a string

Hi,

Is there some kind of code snippet to determine (a) the precise data type (and precision if appropriate) of a given table.columnname in the database, and (b) the most suitable conversion for a varchar that is being made to hold various types of data.

Presumably based on the above one could have some sort of CASE WHEN to cast the data to different types accordingly.

Thanks for any help on this.

You need to use the system tables. Check out sysobjects:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-t_2983.asp

syscolumns:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-c_5mur.asp

and systypes:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-t_2983.asp

This should get you part of the way:

declare @.tablename varchar(256)
declare @.colname varchar(256)

set @.tablename = .....
set @.colname = .....

SELECT obj.name as 'Table', col.name as 'Column', typ.name as 'DataType', col.length, col.xprec as 'Precision', col.xscale as 'Scale'
FROM sysobjects obj
join syscolumns col
on col.id = obj.id
join systypes typ
on typ.xtype = col.xtype
where obj.type = 'U'
and obj.name = @.tablename
and col.name = @.colname

Also, check out my blog for some information on system views:

http://blogs.claritycon.com/blogs/the_englishman/archive/2006/02/09/197.aspx

HTH

|||Thanks, that's really helpful...do you have any insight on determining the most appropriate conversion for a string piece of data? I.e. "23/10/04", "1.01F", "0.68", "0.00021", "1", etc.
|||

There are three system functions which would be useful here:

ISNUMERIC

ISDATE

ISNULL

See:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_79f7.asp

This should get you started:

CASE

WHEN ISNUMERIC(column) = 1 THEN ...... Process Number

WHEN ISDATE(COLUMN) = 1 THEN ... Process Date

ELSE ... Process string\

END

Also, you can use the LEN() and DATALENGTH() functions to determine the size of the value passed. If it is a number, you can also use CHARINDEX to look for a decimal point in the string, and return its value or not. That should help you determine whether the number is an integer, decimal or a money field, and help you work out the precision and size of the data.

HTH

|||ISNUMERIC will check for conversion to any of the numeric data types including integer, float, numeric and money. So if you need stricter checks you need to implement yourself. Another option is to use sql_variant but this will complicate your logic due to the conversions. On the other hand you can retain the base type of the value you are specifying.|||Many thanks for your replies. The requirement has actually now been made simpler. In short, I need to have an additional case to logic like the following (I have asterisked the key line):-

if @.stkData like '%+%'
begin
exec dbo.spNeoStackEvaluate @.stkLeft output
exec dbo.spNeoStackEvaluate @.stkRight output

***set @.stkDataOut = cast(@.stkRight as int) + cast(@.stkLeft as int)
return @.stkDataOut
end

Which should handle cases where the strings originated as money datatypes. The problem is that a simple rewrite of the above, casting @.stkRight as money, is not acceptable in T-SQL, but falls over.

So while replies so far have been very helpful in setting up some logic to know what kind of data I am dealing with, I would also like to know if a simple rewrite of the above to handle money-like strings is in some way possible?

-Thanks.
|||I should point out that all the variables here -- @.stkDataOut, @.stkRight, @.stkLeft, are originally declared as varchar(100) type.
|||

I am not quite sure what exacty your problem is here. As I suggested earlier, you can test the varchar value to seeif it is a decimal (or money) or an int by using charindex, and looking for the presence of a '.' character. By getting the position of the decimal point using charindex, you can then determine the size of the decimal or money value to cast it as. This should then be able to handle any case.

HTH

|||The problem is that even assuming a simple case, for test purposes -- e.g. '0.68' as the varchar data, casting it to money throws an error. So for example assuming a value of '20', casting it as an int is fine, but cast(@.somedata as money) where @.somedata is '0.68' fails. Now this piece of data originated in a field of money datatype. Are you saying that my cast statement must be more specific?
|||

I thought the problem was not specifying the size of the data type, but that does not appear to be the issue. Test code works fine:

declare @.char varchar(20)

set @.char = '0.68'

select cast(@.char as money)

-- returns 0.68

So I am not sure what your issue if. Are you sure that it is the value '0.68' that is throwing the error? I suggest you put a select @.somedata value before the cast to get the value of the varchar before the error is thrown, to double check you are not getting any characters in there. Also, what error are you getting? Can you post the code snippet along with the syntax error? What version of SQL Server are you running? You could try casting it as a decimal instead, but make sure you specify the size and precision, otherwise it will default to an integer value.

|||

Though this will work fine (i.e. accessing system tables to get column data types, attributes, etc.), I'd probably recommend you make use of the SQL-92 compliant INFORMATION_SCHEMA views or system provided functions when possible to retrieve the data, given that you may get different results and unpredictable updates across SQL Server versions, SKU's, and hotfixes/service packs.

In this case, the INFORMATION_SCHEMA.COLUMNS view provides you with all the things you are looking for and probably more: name, data type, precision, scale, schema, default, position, nullability, etc., etc.

Additionally, these system functions could help out as well:

COLUMNPROPERTY()
COL_NAME()
COL_LENGTH()

As for the money conversion, the posting above by Shughes should work fine as he mentions...does for me also :-)...

|||Please indicate if Chad and I solved your problem.|||Will do. I am evaluating it at the moment. It's one of a number of problems that I'm working through. I will post in greater detail later on.
|||Just briefly, this is definitely still erroring. The problem seems to consist in going from varchar to money (in order to carry out a valid calculation) and then going back to varchar.

I will post on this in more detail once I get various other problems sorted out.

Determining the cause of tempdb growth?

I'm trying to determine the cause of tempdb to grow from ~ 30MB to well over
40GB. I have a SQL Profiler trace that was run at the time of this growth
but I don't see any particular SQL statements that consumed a lot of CPU or
performed a lot of reads (maybe the maximum was 50000 reads).
Any ideas as to what I could do to identify this problem?
Thanks in advance.Creation of temp tables and ORDER BY statements can cause tempdb growth. = Tempdb may also be used by other (SQL Server internal) query =optimization processes.
With that said, 40GB does sound rather large. How large are your databases?
What type of operations were being performed during the growth?
-- Keith
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message =news:OPLVdaYmDHA.1800@.TK2MSFTNGP10.phx.gbl...
> I'm trying to determine the cause of tempdb to grow from ~ 30MB to =well over
> 40GB. I have a SQL Profiler trace that was run at the time of this =growth
> but I don't see any particular SQL statements that consumed a lot of =CPU or
> performed a lot of reads (maybe the maximum was 50000 reads).
> > Any ideas as to what I could do to identify this problem?
> > Thanks in advance.
> >

Determining Table Name from TableID in Cube Partition

Hi all,

I am trying to determine source table name of the cube partition using AMO.
I am able to get the partition source (which is of {Microsoft.AnalysisServices.DsvTableBinding} data type). There I can see the TableID property, but not the table's real database name. (TableID has dot between database schema and table name replaced by underscore).

Is there a way how to translate TableID into its original database name?

Thank you very much in advance.

Marek Stefanek
Use the TableID to find the DataTable object in the DataSourceView.Schema (which is a DataSet). Then in the ExtendedProperties collection of the DataTable, you should see some properties such as the original database table name and schema and the friendly name.|||Hi Matt,

Thank you a lot, it helped.

If I may, I would like to ask you another question: Is it possible to get the complete list of extended properties names? It is easy to retrieve the extended property value if you know the property name. But I have no idea from where I could get the property names.

Looking at properties of DataTable object during Script Task debug does not provide much information:

{System.Data.DataTable}
CaseSensitive: False
Columns: {System.Data.DataColumnCollection}
Constraints: {System.Data.ConstraintCollection}
Container: Nothing
DataSet: {System.Data.DataSet}
DefaultView: {System.Data.DataView}
DesignMode: False
DisplayExpression: ""
ExtendedProperties: {System.Data.PropertyCollection}
HasErrors: False
ChildRelations: {System.Data.DataRelationCollection.DataTableRelationCollection}
IsInitialized: True
Locale: {System.Globalization.CultureInfo}
MinimumCapacity: 50
Namespace: ""
ParentRelations: {System.Data.DataRelationCollection.DataTableRelationCollection}
Prefix: ""
PrimaryKey: {Length=0}
RemotingFormat: Xml {0}
Rows: {System.Data.DataRowCollection}
Site: Nothing
TableName: "dbo_VF_VYSLEDKY_2006"

It is probably obvious for skilled .NET developer, but for me its quite confusing.

Thanks!

Marek
|||

It's not a complete list, but here are the ones I think you're interested in:

public const string DBTableName = "DbTableName";
public const string DBSchemaName = "DbSchemaName";
public const string DBColumnName = "DbColumnName";
public const string FriendlyName = "FriendlyName";
public const string Description = "Description";

Determining permissions through Stored Procedures

Is it possible in SQL 2005 to determine what rights a user has to a given DB
(down to the table level) using a Stored Procedure, or Function? I would lik
e
to know if a user has "Insert" rights to a table before I give them an "Add
New" button on my form.
Thanks
DaveDave,
Using sp_helprotect is the traditional method, but it does not return
information about securables introduced in SQL Server 2005. You can use
sys.database_permissions and fn_builtin_permissions instead.
Here is something I use for quick checks that might help you get started.
select u.name, p.permission_name, p.class_desc, object_name(p.major_id)
ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:CB769D2E-1E60-440D-A474-C44B36C6860E@.microsoft.com...
> Is it possible in SQL 2005 to determine what rights a user has to a given
> DB
> (down to the table level) using a Stored Procedure, or Function? I would
> like
> to know if a user has "Insert" rights to a table before I give them an
> "Add
> New" button on my form.
> Thanks
> Dave
>|||Dave (Dave@.discussions.microsoft.com) writes:
> Is it possible in SQL 2005 to determine what rights a user has to a
> given DB (down to the table level) using a Stored Procedure, or
> Function? I would like to know if a user has "Insert" rights to a table
> before I give them an "Add New" button on my form.
Check out the function Has_perms_by_name in Books Online.
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

Determining Object Ownership

How do I determine the ownership of an object? (i.e. table)
SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID('table_name'),' Ownerid'))
Thanks
Yogish

Determining Object Ownership

How do I determine the ownership of an object? (i.e. table)SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID('tabl
e_name'),'Ownerid'))
Thanks
Yogish

Determining Object Ownership

How do I determine the ownership of an object? (i.e. table)one way... to get the owner of the AUTHORS table...
Bruce
SELECT name from sysusers
where uid = OBJECTPROPERTY(OBJECT_ID
('authors'),'OwnerID')
>--Original Message--
>How do I determine the ownership of an object? (i.e.
table)
>.
>|||another way...
select TABLE_SCHEMA as 'owner'
from information_schema.tables
where TABLE_NAME = 'authors'
>--Original Message--
>one way... to get the owner of the AUTHORS table...
>Bruce
>SELECT name from sysusers
> where uid = OBJECTPROPERTY(OBJECT_ID
>('authors'),'OwnerID')
>
>>--Original Message--
>>How do I determine the ownership of an object? (i.e.
>table)
>>.
>.
>|||SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID('table_name'),'Ownerid'))
--
Thanks
Yogish|||Another way... Open up EM and look at the "owner" column...
Thanks,
Mike C
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:39e401c51f64$0ea67db0$a401280a@.phx.gbl...
> another way...
>
> select TABLE_SCHEMA as 'owner'
> from information_schema.tables
> where TABLE_NAME = 'authors'
>
>
>>--Original Message--
>>one way... to get the owner of the AUTHORS table...
>>Bruce
>>SELECT name from sysusers
>> where uid = OBJECTPROPERTY(OBJECT_ID
>>('authors'),'OwnerID')
>>
>>--Original Message--
>>How do I determine the ownership of an object? (i.e.
>>table)
>>.
>>.

Determining numbers of rows affected in advance

Hi,
When workinf on a SQL2005 Db through Sql Server Management studio, is
there a way to determine in advance how many rows will be affected as
a result of an Update,Insert,or Delete statement without actually
performing the query?
thanks "in advance"
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and then
rollback. Another method is to determine the number of rows that will be
affected is to change the DML to a SELECT COUNT(*) query.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"
|||Aamir
I'm not sure what you mean by 'through SQL Server Management Studio' but I
strongly recommend you don't do any data modification through a graphical
window, but always do it through TSQL code, using a query window or
application. You have much more control, and the ability to use the
techniques Dan suggested.
If you only want an estimate, you could also just look at the estimated
query plan after entering your query in a query window. You can get this
plan with Cntl-L
If you hold your cursor over the estimated plan's first icon, it should
display the estimated number of rows to be returned.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"
|||The SELECT COUNT(*) method can be much more efficient than your actual DML
statement because it will probably (hopefully!!) have a very tight, cheap
query plan using indexes since it just needs a count. And if you then DO
choose to run the actual statement then these pages will already have been
pulled into RAM hopefully making the run of the DML faster. Note that this
is still only recommended if for some reason you really do need the count
prior to the execution.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:34560279-FE9B-4EA1-BBBF-36ABFCB77F22@.microsoft.com...
> No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and
> then rollback. Another method is to determine the number of rows that
> will be affected is to change the DML to a SELECT COUNT(*) query.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>
|||But for anything but really trivial quries, that optimizer estimate is
probably so off that it may not be very useful.
Linchi
"Kalen Delaney" wrote:

> Aamir
> I'm not sure what you mean by 'through SQL Server Management Studio' but I
> strongly recommend you don't do any data modification through a graphical
> window, but always do it through TSQL code, using a query window or
> application. You have much more control, and the ability to use the
> techniques Dan suggested.
> If you only want an estimate, you could also just look at the estimated
> query plan after entering your query in a query window. You can get this
> plan with Cntl-L
> If you hold your cursor over the estimated plan's first icon, it should
> display the estimated number of rows to be returned.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>
>
|||Well, I did say it was only an estimate. :-)
It may be way off, but it might not be. I think it would be better than
nothing, and perhaps better than completely running the whole query just to
get a rowcount, depending on how exact the OP needs the number to be.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...[vbcol=seagreen]
> But for anything but really trivial quries, that optimizer estimate is
> probably so off that it may not be very useful.
> Linchi
> "Kalen Delaney" wrote:
|||Thanks for all the responses.
And I'm sorry, should have clarified it. I am actually running a query
from query window in the management studio.
The estimate query plan was really way off and was in decimals ?
I liked the Rollback Transaction solution and it fits my needs. I know
it may be costly but I am not worried about that in my circumstances.
The Select and Selct Count statements are good but still not the same
as running the actual queries (some of them much complex with multiple
joins & subqueries)
Once again, thanks all.
On Jan 7, 12:21Xam, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Well, I did say it was only an estimate. :-)
> It may be way off, but it might not be. I think it would be better than
> nothing, and perhaps better than completely running the whole query just to
> get a rowcount, depending on how exact the OP needs the number to be.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "Linchi Shea" <LinchiS...@.discussions.microsoft.com> wrote in message
> news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
>
>
>
>
>
>
>
> - Show quoted text -

Determining numbers of rows affected in advance

Hi,
When workinf on a SQL2005 Db through Sql Server Management studio, is
there a way to determine in advance how many rows will be affected as
a result of an Update,Insert,or Delete statement without actually
performing the query?
thanks "in advance"> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and then
rollback. Another method is to determine the number of rows that will be
affected is to change the DML to a SELECT COUNT(*) query.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"|||Aamir
I'm not sure what you mean by 'through SQL Server Management Studio' but I
strongly recommend you don't do any data modification through a graphical
window, but always do it through TSQL code, using a query window or
application. You have much more control, and the ability to use the
techniques Dan suggested.
If you only want an estimate, you could also just look at the estimated
query plan after entering your query in a query window. You can get this
plan with Cntl-L
If you hold your cursor over the estimated plan's first icon, it should
display the estimated number of rows to be returned.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"|||The SELECT COUNT(*) method can be much more efficient than your actual DML
statement because it will probably (hopefully!!) have a very tight, cheap
query plan using indexes since it just needs a count. And if you then DO
choose to run the actual statement then these pages will already have been
pulled into RAM hopefully making the run of the DML faster. Note that this
is still only recommended if for some reason you really do need the count
prior to the execution.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:34560279-FE9B-4EA1-BBBF-36ABFCB77F22@.microsoft.com...
>> When workinf on a SQL2005 Db through Sql Server Management studio, is
>> there a way to determine in advance how many rows will be affected as
>> a result of an Update,Insert,or Delete statement without actually
>> performing the query?
> No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and
> then rollback. Another method is to determine the number of rows that
> will be affected is to change the DML to a SELECT COUNT(*) query.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>> Hi,
>> When workinf on a SQL2005 Db through Sql Server Management studio, is
>> there a way to determine in advance how many rows will be affected as
>> a result of an Update,Insert,or Delete statement without actually
>> performing the query?
>> thanks "in advance"
>|||But for anything but really trivial quries, that optimizer estimate is
probably so off that it may not be very useful.
Linchi
"Kalen Delaney" wrote:
> Aamir
> I'm not sure what you mean by 'through SQL Server Management Studio' but I
> strongly recommend you don't do any data modification through a graphical
> window, but always do it through TSQL code, using a query window or
> application. You have much more control, and the ability to use the
> techniques Dan suggested.
> If you only want an estimate, you could also just look at the estimated
> query plan after entering your query in a query window. You can get this
> plan with Cntl-L
> If you hold your cursor over the estimated plan's first icon, it should
> display the estimated number of rows to be returned.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> > Hi,
> >
> > When workinf on a SQL2005 Db through Sql Server Management studio, is
> > there a way to determine in advance how many rows will be affected as
> > a result of an Update,Insert,or Delete statement without actually
> > performing the query?
> >
> > thanks "in advance"
>
>|||Well, I did say it was only an estimate. :-)
It may be way off, but it might not be. I think it would be better than
nothing, and perhaps better than completely running the whole query just to
get a rowcount, depending on how exact the OP needs the number to be.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
> But for anything but really trivial quries, that optimizer estimate is
> probably so off that it may not be very useful.
> Linchi
> "Kalen Delaney" wrote:
>> Aamir
>> I'm not sure what you mean by 'through SQL Server Management Studio' but
>> I
>> strongly recommend you don't do any data modification through a graphical
>> window, but always do it through TSQL code, using a query window or
>> application. You have much more control, and the ability to use the
>> techniques Dan suggested.
>> If you only want an estimate, you could also just look at the estimated
>> query plan after entering your query in a query window. You can get this
>> plan with Cntl-L
>> If you hold your cursor over the estimated plan's first icon, it should
>> display the estimated number of rows to be returned.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
>> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>> > Hi,
>> >
>> > When workinf on a SQL2005 Db through Sql Server Management studio, is
>> > there a way to determine in advance how many rows will be affected as
>> > a result of an Update,Insert,or Delete statement without actually
>> > performing the query?
>> >
>> > thanks "in advance"
>>|||Thanks for all the responses.
And I'm sorry, should have clarified it. I am actually running a query
from query window in the management studio.
The estimate query plan was really way off and was in decimals ?
I liked the Rollback Transaction solution and it fits my needs. I know
it may be costly but I am not worried about that in my circumstances.
The Select and Selct Count statements are good but still not the same
as running the actual queries (some of them much complex with multiple
joins & subqueries)
Once again, thanks all.
On Jan 7, 12:21=A0am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Well, I did say it was only an estimate. :-)
> It may be way off, but it might not be. I think it would be better than
> nothing, and perhaps better than completely running the whole query just t=o
> get a rowcount, depending on how exact the OP needs the number to be.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "Linchi Shea" <LinchiS...@.discussions.microsoft.com> wrote in message
> news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
>
> > But for anything but really trivial quries, that optimizer estimate is
> > probably so off that it may not be very useful.
> > Linchi
> > "Kalen Delaney" wrote:
> >> Aamir
> >> I'm not sure what you mean by 'through SQL Server Management Studio' bu=t
> >> I
> >> strongly recommend you don't do any data modification through a graphic=al
> >> window, but always do it through TSQL code, using a query window or
> >> application. You have much more control, and the ability to use the
> >> techniques Dan suggested.
> >> If you only want an estimate, you could also just look at the estimated=
> >> query plan after entering your query in a query window. You can get thi=s
> >> plan with Cntl-L
> >> If you hold your cursor over the estimated plan's first icon, it should=
> >> display the estimated number of rows to be returned.
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>www.InsideSQLServer.com
> >>http://blog.kalendelaney.com
> >> "Aamir Ghanchi" <aamirghan...@.gmail.com> wrote in message
> >>news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com..=.
> >> > Hi,
> >> > When workinf on a SQL2005 Db through Sql Server Management studio, is=
> >> > there a way to determine in advance how many rows will be affected as=
> >> > a result of an Update,Insert,or Delete statement without actually
> >> > performing the query?
> >> > thanks "in advance"- Hide quoted text -
> - Show quoted text -