Monday, September 29, 2014

Create SQL Login

<![if !supportLists]>1.       <![endif]>CREATE a login for SQL Server  for windows authentication

      CREATE LOGIN [domainName\loginName] FROM WINDOWS;    
                                                     
<![if !supportLists]>2.       <![endif]>CREATE a login with password –( SQL Login) this will work from SQL 2000 onwards

CREATE LOGIN VenkatSangu WITH PASSWORD = 'password'

<![if !supportLists]>3.  <![endif]> CREATE a login with password and password must change when the user first time connecting to SQL Server  this will not support in older version i.e SQL Server 2000/2005

CREATE LOGIN VenkatSangu WITH PASSWORD='pwd@123pdw' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON
     
To use MUST_CHANGE keyword must passwords must meet the following guidelines
<![if !supportLists]>·         <![endif]>The password does not contain the account name of the user.
<![if !supportLists]>·         <![endif]>The password is at least eight characters long.
<![if !supportLists]>·         <![endif]>The password contains characters from three of the following four categories:
<![if !supportLists]>·         <![endif]>Latin uppercase letters (A through Z)
<![if !supportLists]>·         <![endif]>Latin lowercase letters (a through z)
<![if !supportLists]>·         <![endif]>Base 10 digits (0 through 9)
<![if !supportLists]>·         <![endif]>Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%).


Thanks
Venkat Sangu

Friday, September 26, 2014

Recently executed query




Using below query you can get executed time and query text
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Results of above query



Using below query you can find the Recently excuted query for specific SPID
SELECT SDEC.[most_recent_sql_handle], DEST.[text], last_read, Last_write
FROM sys.[dm_exec_connections] SDEC
 CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
WHERE SDEC.[most_recent_session_id] = 52

Results of above query

Thanks
Venkat Sangu

Thursday, September 18, 2014

SQL & PDW

SQL & PDW

 

·        Functions will not work in PDW Environment

·        Foreign keys does not support in PDW

·        Datatypes

o   No BLOBs

o   No CLR Types

o   No Numeric

o   Numeric replaced with decimal

·        In PDW you can’t use update/delete statements using joins.

·        You can’t do update statistics automatically.

 

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...