Mathew M.Varghese (6/4/2012)

---

Dear Daxesh Patel,

You are correct. If i run "update statistics table " and check DBCC SHOW_STATISTICS, Row sampled is only 20% of actual rows.

If database "auto update...

check example 1 in "More Information" section

http://support.microsoft.com/kb/262499

As I have mentioned this in my first comment,

you cannot set a variable using exec('') since the command will be executed in different session, you have to use sp_executesql...

In following query if a.Islogin=0 then consider a.name as database Role else database user

select a.name UserName,a.Islogin, c.name ObjectName,

case b.actadd

when 1 then 'SELECT'

when 2 then 'UPDATE'

when 3 then...

Can you print command and try executing that in SSMS?

something likle this...

declare @cmd as varchar(max),@MaxTotalidade as ??

set @cmd ='set '+@MaxTotalidade+'=(select convert(bigint,isnull(max(cod_sincronismo_fim),0)) from SGCTCentral.dbo.sincronismo where

tp_processo =''I'' and cod_tp_classificacao =2 and...

I think when Field3 = 'a' you want to equate Field2 = 1 and Field3 = 'b' equate Field2 <> 2

This should work,

Where

(Field3 = 'a' and Field2 = 1)

OR

(Field3...

What server role Rep_Login has? for all sysadmin default schema is set to 'dbo' even if you have set default schema for that user.

When you re-create indexes, STATISTICS will have 100% accurate data, but when you run just UPDATE STATISTICS without specifying FULLSCAN or SAMPLE (percent|row), sample percentage may be less than 100%...

Yes, you need to test against the hardware you have

Compression does not increase CPU usage always as you expect

Also consider reduction in IO (disk or network) reduces CPU usage...

You cannot get all database information in one select, you have to run select on individual databases.

You may take resultset for individual database into a temp table and then using...

For database role,

select a.name DBUserName,

(select x.Name from sys.sysusers x where x.uid=b.groupuid) AS AssociatedRole ,

Isnull((SELECT top 1 Name FROM master..syslogins sl WHERE a.sid = sl.sid or a.name = 'Public'),'') LoginName,

HasDBAccess

from...

40,000 is not a big number for 4 columns import, shouldn't be the issue

Eventhough lots of page split will happen and indexes on this tables will be fragmented very soon....

I think I found the issue (not the solution)

odbc does not store sql login information (UID & PWD), and user needs to supply uid and pwd while using the odbc.

Only...

Server properties looks fine

It is single instance (default only) SQL server

Mixed mode (using sql logins since long time on this server)

Login which I am using in odbc is active and...

Thanks Lynn,

I have attached word doc with screen shots