Same query for Fiscal and Normal calendar

tblFiscalMapping

Assume that you have a mapping table between Fiscal and Normal calendar above.

Sometimes, you need to write one stored procedure with a param @IsFiscal to determine when to return order info on Fiscal year or Normal calendar year.

You can implement as the following

DECLARE @StartMonth SMALLDATETIME;   
DECLARE @EndMonth SMALLDATETIME;

IF ISNULL(@IsFiscal,0) = 0
    SELECT @StartMonth = CAST(CAST(@Year AS VARCHAR) + '-01-01' AS SMALLDATETIME);
ELSE
    SELECT @StartMonth = CAST(CAST([Year] AS VARCHAR) + '-' + CAST(NormalMonth AS VARCHAR) + '-01' AS SMALLDATETIME)
    FROM dbo.tblFiscalMapping
    WHERE FiscalYear = @Year;

SET @EndMonth = DATEADD(m, 11, @StartMonth);

SELECT OrderID, OrderNumber, OrderDate, CustomerID, TotalAmount

FROM dbo.tblOrder

WHERE DATEDIFF(m, @StartMonth, OrderDate) >= 0
                                AND DATEDIFF(m, @EndMonth, OrderDate) <= 0

Create SQL Server linked server to PostgreSQL database

1. Download ODBC Driver at this address http://www.postgresql.org/ftp/odbc/versions/msi/

Choose the suitable version with your PostgreSQL database. I’m using PostgreSQL 9, so I chose latest version. (^_^)

2. Install ODBC Driver for PostgreSQL

3. Create connection to PostgreSQL database with ODBC Driver

4. Now, it’s time to create linked server to PostgreSQL database:

You can create with GUI

Or can create with scripts

EXEC master.dbo.sp_addlinkedserver @server = N'OPENERP', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'OpenERP';

GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPENERP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;

GO

Finally, you can query PostgreSQL database inside SQL Server

5. Verify the linked server

Query Excel spreadsheet on SQL Server 2008 64 bit with Linked Server

For SQL Server 64 bit, if you use Microsoft.Jet.OLEDB.4.0, you may experience this issue:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

To fix this, please follow the following:

1. Download and install Microsoft Access Database Engine 2010 Redistributable at this address http://www.microsoft.com/en-us/download/details.aspx?id=13255 

2. After installing, create a linked server to your Excel file

EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL_LINKED_SERVER_NAME'
                    , @srvproduct=N'Excel'
                    , @provider=N'Microsoft.ACE.OLEDB.12.0'
                    , @datasrc=N'C:\Your_Excel_File.xls'
                    , @provstr=N'Excel 12.0';

GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL_LINKED_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;

GO

3. Run OPENQUERY to get data from your Excel file

SELECT * FROM OPENQUERY(EXCEL_LINKED_SERVER_NAME,'SELECT * FROM [Sheet1$]')

Join quiz 1

Look at the above picture and choose the correct answer:
A. SELECT * FROM TableA t1 INNER JOIN TableB t2 ON t1.ID = t2.ID
B. SELECT * FROM TableA t1 LEFT JOIN TableB t2 ON t1.ID = t2.ID
C. SELECT * FROM TableA t1 RIGHT JOIN TableB t2 ON t1.ID = t2.ID
D. SELECT * FROM TableA t1, TableB t2 WHERE t1.ID = t2.ID
E. Both A and D
F. None of the above

Answers: [E]

Get rows without duplicate values

CREATE TABLE T1(a INT,b INT,c INT)

INSERT INTO T1(a,b,c)

SELECT 1,2,3

UNION

SELECT 2,2,4

UNION 

SELECT 3,3,3

UNION

SELECT 4,3,5

SELECT *

FROM T1

WHERE a NOT IN (SELECT MAX(a)

                FROM T1

                GROUP BY b)

CLR Quiz

You're developing an application which has a feature to read data from a spreadsheet, performs some calculations, and returns the data to a SQL Server computer. You decide to use CLR function and need to register the assembly with SQL Server by using the CREATE ASSEMBLY statement and the least privileged security permission set. Which permission set should you use?

A. Default
B. SAFE
C. EXTERNAL_ACCESS
D. UNSAFE
E. Both B and C
F. None of the above

Answer: [C]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Login failed

You're developing a website with 5 members. A member needs access to a SQL Server database that is located on a server named SQL1. You create a login named member1 by using the following Transact-SQL statement. CREATE LOGIN member1 WITH PASSWORD = 'member$1'. The member reports that when he logs in, he receives the following error message: "Login failed. The user is not associated with a trusted SQL Server connection." You need to resolve the error and allow him to access to SQL1. What should you do?

A. Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
B. Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode.
C. Give the login access to a specific database by using the CREATE USER Transact-SQL statement. 
D. Ensure that the login name is created with square brackets ([]).
E. Both A and C
F. None of the above

Answer: [A]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

SQL Quiz

You discovered that the schema changes that were recently made to your SQL Server database have caused your applications (website, mobile, or desktop apps) to stop functioning. It is unclear who made the changes.  You need to implement a mechanism that will track schema changes in your database. What should you do?

A. Implement a stored procedure that writes data about schema changes to a log table.
B. Implement DDL AFTER triggers that write user and schema information to a log table.
C. Implement a DML INSTEAD OF trigger that writes data about schema changes to a log table.
D. Implement a DML AFTER trigger that writes data about schema changes to a log table.
E. Both A and C
F. None of the above

Answer: [B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Add new column as NOT NULL to existing table

You are modifying a table named tblEmployee in a SQL Server database. You want to add a new column named JobTitle to the tblEmployee table. The table currently contains data. The HR Department has not yet created a job title for each employee. JobTitle is a required value for each employee. You want to add this new column by using the least amount of effort. What should you do?

A. Define the new column as NULL. Update the JobTitle column to the same value as 'Undefined'. Modify the JobTitle column to be NOT NULL.
B. Define the new column as NOT NULL with a default value of 'Undefined.'
C. Define the new column as NULL. Use application logic to enforce the data constraint.
D. Define the new column as NULL with a default value of 'Undefined.'
E. Both A and B
F. None of the above

Answer: [B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Create View Quiz

You are creating a view to join the tblEmployee and tblDepartment tables in a SQL Server database. You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to accomplish this goal by using the least possible amount of overhead. What should you do?

A. Create CHECK constraints on the tables.
B. Create a DDL trigger to roll back any changes to the tables if the changes affect the
columns in the view.
C. Create the view, specifying the WITH SCHEMABINDING option.
D. Create the view, specifying the WITH CHECK option.
E. Both C and D
F. None of the above

Answer: [C]
Highlight to find out the answer.
Come here to know more about CREATE VIEW
Visit here to practice your SQL Server skills.

Trigger quiz

Your company uses a SQL Server database to manage data on e-Commerse site. This database contains a trigger named tgInsertOrder, which fires when order data is inserted into the tblOrder table. The trigger is responsible for ensuring that a customer exists in the tblCustomer table before data is inserted into the tblOrder table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort.
Which T-SQL statements can you use to achieve this goal?

A. ALTER TABLE tblOrder DISABLE TRIGGER tgInsertOrder
B. DROP TRIGGER tgInsertOrder
C. DISABLE TRIGGER tgInsertOrder ON tblOrder
D. ALTER TRIGGER tgInsertOrder ON tblOrder NOT FOR REPLICATION
E. Both A & C
F. None of the above

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Query transactions by date

You work for a supermarket that uses a SQL Server database to store line items from point of sales (POS) transactions. The POS processes 150,000 transactions every day. The application requires a clustered index on the TransactionID column. You need to create a table that supports an efficient reporting solution that queries the transactions by date.
What will you do to achieve this goal?

A. Place a nonclustered index on the date column.
B. Add a unique clustered index on the date column.
C. Map each partition to a filegroup, with each filegroup accessing a different physical drive.
D. Create a partitioning scheme that partitions the data by date.
E. Both A and D
F. None

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Data type precedence

Do you remember data type precedence?
Which of the following data types has the highest data type precedence in SQL Server 2008?

A. DATETIME2
B. XML
C. BIGINT
D. UNIQUEIDENTIFIER
E. DATETIME
F. NTEXT

Answer:[B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

SQL Server quiz

You're managing a SQL Server computer that was installed using default settings. After a power failure, the SQL Server (MSSQLSERVER) service on your database server does not start. You need to find out the cause of the problem. Which action should you perform?

A. In Event Viewer, view the system log or the application log
B. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file.
C. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
D. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.
E. Both A and C
F. Both B and C

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

OpenXML

What will be the output of the following scripts?

DECLARE @xml XML,@hdoc INT
SELECT @xml = '<myxml>
<value id = "1" name = "SQL Server">
 <category>2</category>
</value>
<value id = "2" name = "Oracle">
 <category>3</category>
</value>
</myxml>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT DISTINCT category 
FROM OPENXML (@hdoc, '/myxml/value',1)
WITH (id INT, name VARCHAR(10), category INT)
EXEC sp_xml_removedocument @hdoc

A. NULL
B. 3,2
C. 2,3
D. Error: Incorrect syntax near /value

Answer:[A]
Highlight to find out the answer.

Get user connections

Sometimes you need to determine who is connecting to the database by using which applications, so I think you can use this script.

SELECT t1.session_id, t1.local_tcp_port
           , t2.login_name, t2.host_name, t2.program_name
FROM sys.dm_exec_connections t1  
     INNER JOIN sys.dm_exec_sessions t2 on t1.session_id = t2.session_id

Convert columns to rows

Sometimes you need to convert columns to rows to get data you need.
For example, this query is used to get which customers buy which services.

DECLARE @CustomerService TABLE(CustomerID INT
       , Service1 CHAR(1)
       , Service2 CHAR(1)
       , Service3 CHAR(1)
       )
INSERT INTO @CustomerService VALUES(1,'Y','Y','N')
INSERT INTO @CustomerService VALUES(2,'N','N','Y')
INSERT INTO @CustomerService VALUES(3,'Y','N','N')

SELECT CustomerID, [Service], ServiceValue
FROM 
   (SELECT CustomerID
   , Service1
   , Service2
   , Service3
 FROM @CustomerService) pvt
UNPIVOT
   (ServiceValue FOR [Service] IN 
   (Service1
  , Service2
  , Service3)
) A
WHERE ServiceValue = 'Y'

CAST with DATETIME2

What will be the output of the following script in SQL Server 2008?
SELECT CAST(0 AS DATETIME2 )

A. 1900-01-01 00:00:00.0000000
B. 0001-01-01 00:00:00.0000000
C. 1753-01-01 00:00:00.0000000
D. An error will be generated: "Explicit conversion from data type int to datetime2 is not allowed."
E. An error will be generated:"Type DATETIME2 is not a defined system type."

Answer:[D]
Highlight to find out the answer.

Find output

What is the result of the following batch?

DECLARE @Test TABLE(a INT IDENTITY, b VARCHAR(5))
DECLARE @sRet VARCHAR(255)
SET @sRet = 'Happy'
SELECT @sRet = ISNULL(b, '!') FROM @Test ORDER BY a
INSERT @Test VALUES('Happy')
INSERT @Test VALUES('Lunar')
INSERT @Test VALUES('New')
INSERT @Test VALUES('Year')
SELECT @sRet = @sRet + ' ' + b FROM @Test ORDER BY a
SELECT @sRet

A. NULL
B. ! Happy Lunar New Year
C. An error will be generated
D. Happy Happy Lunar New Year
E. Happy Lunar New Year

Answer:[D]
Highlight to find out the answer.

Find weak login passwords in your server

This is the script I posted on sqlservercentral.com last year.
DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))
--Define weak password list
--Use @@Name if users password contain their name
INSERT INTO @WeakPwdList(WeakPwd)
SELECT ''
UNION SELECT '123'
UNION SELECT '1234'
UNION SELECT '12345'
UNION SELECT 'abc'
UNION SELECT 'default'
UNION SELECT 'guest'
UNION SELECT '123456'
UNION SELECT '@@Name123'
UNION SELECT '@@Name'
UNION SELECT '@@Name@@Name'
UNION SELECT 'admin'
UNION SELECT 'Administrator'
UNION SELECT 'admin123'
-- SELECT * FROM @WeakPwdList
SELECT t1.name [Login Name], REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]
FROM sys.sql_logins t1
INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)