====================================================================
- From rezwan_marshall
- Hi Amish- have questions.When upgrade to sql server sp3 1) Do I need to disconnect applications prior to sp3 installation and connect them back after? 2) What generic tests or (cursory patching test) can be done on sql server and the applications to verify that the upgrade was successful ? Thanks in advance -Marshall
- Answer
- Yes, You need downtime for this. Always take downtime before doing any major upgradation in SQL Server To get version of SQL Server select SERVERPROPERTY ('productlevel') This will give you current version of SQL Server.Use it to check version of SQL Server after upgradation Amish Shah
- =====================================================================
-
- From rezwan_marshall
- Hi. I need to implement a trigger to email me when someone creates a database on a server without letting me know.I dont know how to write the trigger.Can you help please? Thanks Rezwan
- Answer
- you have to use ddl trigger which controls different activity on the server Here is example for trigger on create database statement CREATE TRIGGER MONITOR_DATABASE ON ALL SERVER FOR CREATE_DATABASE AS BEGIN DECLARE @CREATE_DB_STATEMENT VARCHAR(MAX) SELECT @CREATE_DB_STATEMENT = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') SELECT @CREATE_DB_STATEMENT /* use sp_send_dbmail statement here */ END you have to use sp_send_dbmail in trigger to send mail and use @CREATE_DB_STATEMENT as message for mail .
- ====================================================================
- From Ahmed Bouzamondo
- Hi, I found your scripts about index usages very useful. Good work
- ====================================================================
- From Prakash
- I am running following query on adventureworks but its giving me error Msg 319, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. declare @id int set @id = 109 with test as (select *, row_number() over (order by loginid ) as rownum from HumanResources.Employee where managerid = @id) select * from test
- Answer
- You have to use CTE in BEGIN END clause. DECLARE @id INT SET @id = 109 BEGIN WITH test AS (SELECT *, Row_number() OVER(ORDER BY loginid) AS rownum FROM humanresources.employee WHERE managerid = @id) SELECT * FROM test END
- ====================================================================
Hi Amish
I want to to edition of my SQL Server. How can I do it.
Answer
serverproperty function will give you this details
select serverproperty('edition')
select SERVERPROPERTY('productlevel')
Look at serverproperty in BOL for more details.
Cheers
Amish Shah
===================================================================
From Anirabn
I have restored a database on other server , the server has same login but I can connect to database. Can you guide me?
Answer
You have map login to the user in database.
Look at sp_change_users_login in BOL
exec sp_change_users_login 'update_one',
Look at BOL for more options.
===================================================================
Question:- Is there any performance difference between "select count(*) from table1" or "select count(1) from table1"
Answer:- No there is no difference between this. If you look at execution plan both are same.
Optimizer is smart to enough to get idea what to do :-)
