Update only Date Part : Sql Server

Select *,
dateadd(dd,datediff(dd,SS.StartDateTime,'2012-07-19 00:00:00.000'),SS.StartDateTime)
from t_sessions S
INNER JOIN tIdx_ConferenceSessions CS
ON S.PK_Session = CS.FK_Session
inner join tIdx_SessionSettings SS
on s.PK_Session = SS.FK_Session
Where FK_Conference = 893

Working with Date and Time : Sql Server

Get Time in Hour:Minute or Hour:Minute:Second format (24 hours)

[code language="sql"]
SELECT
CONVERT(VARCHAR(8) , GETDATE() , 108) AS HourMinuteSecond,
CONVERT(VARCHAR(5) , GETDATE() , 108) AS HourMinute ,
CONVERT(TIME,GETDATE()) AS HourMinuteSecondNanoSec --for Sql Server 2008
[/code]

Get Time in Hour:Minute - 12 Hours Format

[code language="sql"]
SELECT SUBSTRING(CONVERT(varchar, getdate(), 100), 13, 2) + ':'
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 16, 2) + ' '
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 18, 2) AS MyTime
[/code]

Reference:http://pcunleashed.com/sql-server/get-12-hour-time-from-datetime-datatype-in-sql-server-2005/


Get Date

[code language="sql"]
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY], --US Format
CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] --British / French Format
[/code]

Get Date Time

[code language="sql"]
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) [Mon DD YYYY HH:MIAM (or PM)]
, CONVERT(VARCHAR(26), GETDATE(), 109) [Mon DD YYYY HH:MI:SS:sssAM (or PM)]
, CONVERT(VARCHAR(24), GETDATE(), 113) [DD Mon YYYY HH:MM:SS:sss(24h)]
, convert(varchar, getdate(), 120) [yyyy-mm-dd hh:mm:ss(24h)]
, convert(varchar, getdate(), 121) [yyyy-mm-dd hh:mm:ss.mmm]
[/code]

Happy Coding :)

Working with Date and Time : Sql Server

Get Time in Hour:Minute or Hour:Minute:Second format (24 hours)
[code]
SELECT
CONVERT(VARCHAR(8) , GETDATE() , 108) AS HourMinuteSecond,
CONVERT(VARCHAR(5) , GETDATE() , 108) AS HourMinute ,
CONVERT(TIME,GETDATE()) AS HourMinuteSecondNanoSec --for Sql Server 2008
[/code]

Get Time in Hour:Minute - 12 Hours Format
[code]
SELECT SUBSTRING(CONVERT(varchar, getdate(), 100), 13, 2) + ':'
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 16, 2) + ' '
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 18, 2) AS MyTime
[/code]

Reference:http://pcunleashed.com/sql-server/get-12-hour-time-from-datetime-datatype-in-sql-server-2005/

Get Date
[code]
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY], --US Format
CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] --British / French Format
[/code]

Get Date Time
[code]
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) [Mon DD YYYY HH:MIAM (or PM)]
, CONVERT(VARCHAR(26), GETDATE(), 109) [Mon DD YYYY HH:MI:SS:sssAM (or PM)]
, CONVERT(VARCHAR(24), GETDATE(), 113) [DD Mon YYYY HH:MM:SS:sss(24h)]
, convert(varchar, getdate(), 120) [yyyy-mm-dd hh:mm:ss(24h)]
, convert(varchar, getdate(), 121) [yyyy-mm-dd hh:mm:ss.mmm]
[/code]

Happy Coding :)

SQL Server Schema Binding Views

In SQL Server, views are not bound to the schema of the base tables by default. In such case we may change the schema of the base table at any time, regardless of the fact that the associated view may or may not work with the new schema. We can even drop the underlying table while keeping the associated view without any warning. In this case when the view is used, we will get an invalid object name error for the base table.

So if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the "WITH SCHEMABINDING" clause to bind the view to the schema of the base tables.

Read further:

http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/

SQL Server Schema Binding Views

In SQL Server, views are not bound to the schema of the base tables by default. In such case we may change the schema of the base table at any time, regardless of the fact that the associated view may or may not work with the new schema. We can even drop the underlying table while keeping the associated view without any warning. In this case when the view is used, we will get an invalid object name error for the base table.

So if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the "WITH SCHEMABINDING" clause to bind the view to the schema of the base tables.

Read further:

http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/

Get All Stored Procedures, Tables : Database Comparision

[code language="sql"]
Select name,type_desc from database1.sys.objects where name not in
(Select name From database1.sys.objects)
and type in( 'U', 'V', 'FN', 'IF', 'P')
order by type
[/code]

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table--
UQ = UNIQUE constraint (type is K)
V = View--
X = Extended stored procedure

[/code]

Dot Net Interview Questions

C# OOPS Concept :

OOPS Concept
virtual
override
can you over ride without virtual
two classes with same interface:
webservices --types of webservices,
interfaces and abstract --difference : interfaces as purely
abstracts
according to defination of C#--multiple is nt allowi....

serization
types of serialization

page events
master page --title dynamically change from child --page event: page
int of child page

cache - types
sql catch dependency
web farm, garden
which is better

asemblies -- type , gac, snk, unique name , register,
-application kise diference
--steps : to deploy assembly
--where gac is located
--state management
--cookies and sessions ,, cookies disabled
-- upload excel , sql bulk copy

xlst
com and dcom
json--webservice --xml ,
how webservice deploy
webservice service sequerity

Session managed
cross page reference -state management --

---design patterns
--models

Reporting Subscription: Quick Links

http://www.isolutionspartners.com/Blog/tabid/65/EntryId/35/How-to-configure-SSRS-Report-Subscriptions-to-use-Gmail-for-GP.aspx

http://www.databasejournal.com/features/mssql/report-subscription-in-ssrs-2008-r2.html

SQL Server Tools

SQL Server Stored Procedures Comparer:

http://www.codeproject.com/KB/database/StoredProceduresComparer/spcomparer_demo.zip

Compare Databases Tool: http://dbcomparer.com/

SSIS Package EXCEL Connection Error: Solution

Issue Description:

Package was running fine

Error:

[Connection manager "Dest File"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Solution:

You have an Excel connection. The package will need to run in 32 bit mode. To enable 32 bit mode, right click the project in the Solution Explorer and go to properties. Under Debugging, set the property Run64BitRunTime to False.

linked Server Query: Fetching data from table with column of datatype "XML"

ERROR: Xml data type is not supported in distributed queries. Remote object 'server_name.database_name.dbo.table_name' has xml column(s).

Solution:

[code]
SELECT * FROM

(
SELECT * FROM OPENQUERY([server_name],'SELECT column_1, column_2,CAST (column_XML as varchar(max)) Converted_column_XML
FROM
database_name.[dbo].table_name')

)AS X
[/code]

Database: Create User with "Create Proc Permission"

[code]
USE MASTER
CREATE LOGIN da WITH PASSWORD = '12345'

USE yourdatabasename
GO
CREATE USER da FOR LOGIN da

CREATE ROLE yourrolename

EXEC sp_addrolemember 'db_datareader',yourrolename
EXEC sp_addrolemember 'db_datawriter', yourrolename
EXEC sp_addrolemember @RoleName = 'yourrolename', @MemberName = 'da'

GRANT EXECUTE TO da
GRANT CREATE PROCEDURE TO da

GRANT ALTER ON SCHEMA::dbo TO yourrolename
[/code]

Must read: Article on Triggers

Triggers: http://vivekjohari.blogspot.in/2011/11/sql-triggers-introduction.html

addons topics:

database models:

http://en.wikipedia.org/wiki/Database_model

Query Optimization tips:

http://hungred.com/useful-information/ways-optimize-sql-queries/

 

Sql Server Mirroring

Prerequisites:
A) SQL Servers on Principal and Mirror server shopud be started under account with the same name (xyzMachineUser)
B) You should be connected to SQL Server under sqlmirroring SQL Account (With same user name and password: lets say we have account: sqlmirroring )

Steps:
1) Take Full Backup from Principal Database (database must be in Full recovery model)
2) Take Transaction Log backup from Primary Database
3) Restore Full backup on mirror server with NORECOVERY option on mirror server(with user sqlmirroring)
4) Restore Transaction Log backup with NORECOVERY option on mirror server
5) Right click on database you want to start mirroring -> Properties -> Mirroring..
6) Click ”Configure Security” -> Choose not to Configure Witness server -> Connect to Mirror Server under sqlmirroring user (other steps – click next) (Enable encryption – remove checkbox mark)
7) In dialog window choose “Do not start mirroring”
8) Check radiobutton “High Performance”
9) In “Server network addresses” replace machine names to ip addresses of servers
10) Click “Start Mirroring”

If principal server goes down, you should restore mirror database using this commands:
ALTER DATABASE database_name SET PARTNER OFF
GO
RESTORE DATABASE database_name WITH RECOVERY
GO
And connect your front end with this database.
Your website is UP again.

Explicitly insert data into identity column : Sql Server

To insert data into Identity Column
Set identity_insert table_name ON
Explicitly specify the column names of the table in which data is to be inserted
Set identity_insert table_name OFF

[code language="sql"]
set identity_insert table_name ON
insert into table_name(identity_column_name,second_column_name) values
(21, 'text 1'),
(23, 'text 2'),
(24, 'text 3')
set identity_insert table_name OFF
[/code]

Sql Server Service not starting up. Error: 17058

Server Error: 17058, Severity: 16, State: 1.
Error:
Server initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 5(Access is denied.).
Issue: Created a new user and restarted service with the new user.
It started giving the error mentioned above.
Solution: Moved the file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG" to a different location and the service started.
Hurray!!
Hope it helps :)

NOTE: Ideally on Sql Server restart a new error file is created.

If existing file is named as "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG"

New file will be named as "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG.1"

Need to dig more.. right?

Wednesday#90210

SSRS Reporting Subscription Issue : Unresolved

SSRS Reporting Subscription Issue: Failure sending mail: The server rejected the sender address. The server response was: 530 5.7.0 Must issue a STARTTLS command first.

Problem Description:

Trying to set up SSRS Reporting Subscription using GMAIL account.

SSRS Subscription raises error mentioned above.

Solution available online:

1. Make Gmail to always send email using Secured connection:  http://www.quickonlinetips.com/archives/2008/07/secure-https-gmail/

2. Changing in reporting manager configuration file: http://sqldbastuff.blogspot.in/2010/06/email-configuration-for-ssrs.html

tried both.. No luck yet :(

Raised issue: http://www.sqlservercentral.com/Forums/Topic1308102-162-1.aspx

Any help would be highly appriciated.

Expand Collapse Group Functionality in SSRS Reports

http://www.sqlchick.com/entries/2010/9/19/expand-or-collapse-all-grouped-items-on-ssrs-report.html

Must read!!!

Copy previous cell data in Excel - Excel Macro

Let's say you have a list of entries in column A, similar to the above example, and within the list you have many blank cells.
Here is a quick and easy way to fill those blanks with the value of the cell above.
Using VBA Macro:

[code]
Sub FillBlanks()
Dim rRange1 As Range, rRange2 As Range
Dim iReply As Integer
If Selection.Cells.Count = 1 Then
MsgBox "You must select your list and include the blank cells", vbInformation
Exit Sub

ElseIf Selection.Columns.Count > 1 Then
MsgBox "You must select only one column", vbInformation
Exit Sub
End If

Set rRange1 = Range(Selection.Cells(1, 1), Cells(65536, Selection.Column).End(xlUp))

On Error Resume Next
Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rRange2 Is Nothing Then
MsgBox "No blank cells Found", vbInformation
Exit Sub
End If

rRange2.FormulaR1C1 = "=R[-1]C"

iReply = MsgBox("Cells copied", vbYesNo + vbQuestion)
If iReply = vbYes Then rRange1 = rRange1.Value

End Sub

[/code]

Working With Excel: Tips and Tricks

Replace Spaces with other characters : Use Substitute function

=SUBSTITUTE(G2," ","")

Shrink database when log is full

http://sqlserver8.blogspot.in/2012/04/shrink-database-when-log-is-full.html

Configure DB mail and Send Email on Backup Failure

Configure Email:

http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/

Notify Operator:

http://808techblog.com/2009/07/setup-sql-server-2008-maintena.html

Split First Name Last Name Company- Split 1 Column to multiple

Name	Email
user Name1	xyz@abc.com
user Name2	xyz1@abc.com
User Name 3 (Company Inc)	xyz2@abc.com
Mr 123456 678	xyz3@abc.com
user Name4 123344 (Company Interactive 123)	xyz4@abc.com
Marcelle XYZ	xyz5@abc.com

Create a temp table and insert the data.

As you can see we may or may not have "braces" with company name.

Use the following code to split the first name, last name and company name.

[code language="sql"]
--drop table temp
Create table temp (
id int identity(1,1)
,Name nvarchar(500)
,Email nvarchar(100)
)

insert into temp values ('user Name1','xyz4@abc.com'),('user Name4 123344 (Company Interactive 123)','xyz4@abc.com')

select * from temp

alter table temp
add
FirstName nvarchar(100),LastName nvarchar(100),Company nvarchar(100)

DECLARE
@Id int
,@Name nvarchar(1000)
,@FirstName nvarchar(100)
,@LastName nvarchar(100)
,@Company nvarchar(100)
,@sDelimiter nVARCHAR(100)

DECLARE vendor_cursor CURSOR FOR
select Id, Name from temp

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @Id, @Name

WHILE @@FETCH_STATUS = 0
BEGIN

select @FirstName = ''
,@LastName = ''
,@Company = ''

SET @sDelimiter = ' '
IF CHARINDEX(@sDelimiter,@Name,0) <> 0
BEGIN
SELECT @FirstName = RTRIM(LTRIM(SUBSTRING(@Name,1,CHARINDEX(@sDelimiter,@Name,0)-1))),
@Name = RTRIM(LTRIM(SUBSTRING(@Name,CHARINDEX(@sDelimiter,@Name,0)+LEN(@sDelimiter),LEN(@Name))))
END

SET @sDelimiter = '('
IF CHARINDEX(@sDelimiter,@Name,0) <> 0
BEGIN
SET @LastName = RTRIM(LTRIM(SUBSTRING(@Name,1,CHARINDEX(@sDelimiter,@Name,0)-1)))
set @Company = REPLACE (RTRIM(LTRIM(SUBSTRING(@Name,CHARINDEX(@sDelimiter,@Name,0)+LEN(@sDelimiter),LEN(@Name)))),')','')
END
ELSE
SET @LastName = substring(@Name,charindex(' ', @Name)+1,len(@Name))

update temp
set
FirstName = @FirstName
,LastName = @LastName
,Company = @Company
where
Id = @Id

FETCH NEXT FROM vendor_cursor
INTO @Id, @Name
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

[/code]

Finally, your temp table will have the separated columns.

Split First Name Last Name Company- Split 1 Column to multiple

Name	Email
user Name1	xyz@abc.com
user Name2	xyz1@abc.com
User Name 3 (Company Inc)	xyz2@abc.com
Mr 123456 678	xyz3@abc.com
user Name4 123344 (Company Interactive 123)	xyz4@abc.com
Marcelle XYZ	xyz5@abc.com

Create a temp table and insert the data.

As you can see we may or may not have "braces" with company name.

Use the following code to split the first name, last name and company name.

[code]
--drop table temp
Create table temp (
id int identity(1,1)
,Name nvarchar(500)
,Email nvarchar(100)
)

insert into temp values ('user Name1','xyz4@abc.com'),('user Name4 123344 (Company Interactive 123)','xyz4@abc.com')

select * from temp

alter table temp
add
FirstName nvarchar(100),LastName nvarchar(100),Company nvarchar(100)

DECLARE
@Id int
,@Name nvarchar(1000)
,@FirstName nvarchar(100)
,@LastName nvarchar(100)
,@Company nvarchar(100)
,@sDelimiter nVARCHAR(100)

DECLARE vendor_cursor CURSOR FOR
select Id, Name from temp

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @Id, @Name

WHILE @@FETCH_STATUS = 0
BEGIN

select @FirstName = ''
,@LastName = ''
,@Company = ''

SET @sDelimiter = ' '
IF CHARINDEX(@sDelimiter,@Name,0) <> 0
BEGIN
SELECT @FirstName = RTRIM(LTRIM(SUBSTRING(@Name,1,CHARINDEX(@sDelimiter,@Name,0)-1))),
@Name = RTRIM(LTRIM(SUBSTRING(@Name,CHARINDEX(@sDelimiter,@Name,0)+LEN(@sDelimiter),LEN(@Name))))
END

SET @sDelimiter = '('
IF CHARINDEX(@sDelimiter,@Name,0) <> 0
BEGIN
SET @LastName = RTRIM(LTRIM(SUBSTRING(@Name,1,CHARINDEX(@sDelimiter,@Name,0)-1)))
set @Company = REPLACE (RTRIM(LTRIM(SUBSTRING(@Name,CHARINDEX(@sDelimiter,@Name,0)+LEN(@sDelimiter),LEN(@Name)))),')','')
END
ELSE
SET @LastName = substring(@Name,charindex(' ', @Name)+1,len(@Name))

update temp
set
FirstName = @FirstName
,LastName = @LastName
,Company = @Company
where
Id = @Id

FETCH NEXT FROM vendor_cursor
INTO @Id, @Name
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

[/code]

Finally, your temp table will have the separated columns.

Restoring Accidentally deleted or Updated Rows without Backup restore - Sql Server

In Reference to my previous post : Restoring Database to a previous point of time –Recommended for all DBA’s

have been looking some thing better to avoid restoring database and recover the deleted \ updated data for given tables.

Finally got the solution for the same. Hurray!!!

Following is the link to ultimate tool designed by Mr.Imran.

Restoring Deleted Rows - Sql Server

Restoring Modified Rows - Sql Server

Happy coding :)

Update Mutiple Tables in Single Query - Sql Server

Use Views - Simple :)

Reference: http://weblogs.sqlteam.com/brettk/default.aspx

I must say the blog mentioned above is SUPER!!!


NOT POSSIBLE to update multiple tables.

Important tools for Sql Server

http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/Free-SQL-Server-tools-that-might-make-your-life-a.aspx

SSMS Tools Pack : Highly recommended

For Formating: http://www.tsqltidy.com/tsqltidySSMSAddin.aspx

 

Restoring Database to a previous point of time --Recommended for all DBA's

The scenario

A coworker calls you in a panic because he accidentally deleted some production data, and he wants you to restore the lost records.

Read further

http://www.techrepublic.com/blog/datacenter/restore-your-sql-server-database-using-transaction-logs/132

Preferable:

http://www.linglom.com/2009/07/03/solved-system-data-sqlclient-sqlerror-the-tail-of-the-log-for-the-database-dbname-has-not-been-backed-up/

Create Comma Separated List- Sql tips and tricks

Create list of Integer type column

[code]

DECLARE @listStr VARCHAR(MAX)

SELECT @listStr = COALESCE(@listStr+',' ,'') + CAST(ColumnName AS Varchar(10))
FROM TableName
SELECT @listStr
GO

[/code]

Replace ColumnName and TableName

Function to replace all special characters - Sql Sever

[code language="sql"]
CREATE FUNCTION dbo.RemoveSpecialCharacter(@Str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN

SET @Str = Replace(@str, Char(13), ' ')
--replace all enters with space
SET @Str = Replace(@str, Char(9), ' ')
--replace all tabs with space
SET @Str = Replace(@str, Char(10), ' ')
--replace all line feeds with space

RETURN @Str

END
[/code]

Useful function to be used for data export to excel sheet(Using SSIS) as the "Enter" makes the data to move to next line and the reports gets distoreted.

SSIS Container Good Examples

For Each Container Example

http://beyondrelational.com/modules/2/blogs/106/posts/11122/loop-through-each-record-in-a-text-file-recordset-destination.aspx

Mapping SP variable in OLEDB Source-->

 http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx

Useful Special Characters For Sql Server Dynamic Queries

Char(44) -- comma
Char(39) -- Single Quote
char(9) -- Tab
char(10) -- Line feed
char(13) -- Enter\Carriage return
Char(32) -- Space

Go to SSMS.. Type :

[code language="sql"]

Select Char(44)

[/code]

and see the magic :)

Sql Server Formatter - Free and Useful

http://architectshack.com/PoorMansTSqlFormatter.ashx#Download_8 --> Download SSMS Plugin

http://poorsql.com/ --> Free Online Formatter

 

Trick: Row Count for larger tables

http://brian.chipsofttech.com/blogs/brian/post/SQL-Server-Row-Count-for-Large-Tables.aspx

Twist in ISNULL function - Interesting

http://www.sqlservercentral.com/articles/T-SQL/76861/

Trim Leading and Trailing quotes before inserting to database - Sql Server

Importing from a excel to SSIS is a tedious task. Some time we have to convert from excel to tab seprated text file and then import.

While converting from excel to tab separated file "quotes" get appended to the text.

Following is a Sql function to remove leading and trailing quotes from text and then insert to db.

[code language="sql"]</pre>
CREATE FUNCTION dbo.TrimQuotes(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT, @End Int, @Length INT
SET @Str = REPLACE(RTRIM(LTRIM(@Str)),'""', '"')
SELECT @Start = CHARINDEX('"', @Str), @End = CHARINDEX('"', REVERSE(@Str)),
@Length = LEN(@Str)
IF @Start =1
SET @Start = @Start + 1 -- new string must start from second character
ElSE
SET @Start = 1 --as there is not starting quote. we will not trim it
IF @End != 1
SET @End = 0 -- as there is not ending quote. we will not trim it

SELECT @Str = SUBSTRING(@Str,@Start, @Length - @Start - @End + 1)

RETURN @str

END
GO
SELECT dbo.TrimQuotes ( '"abc"') , dbo.TrimQuotes ( ' abc" ') , dbo.TrimQuotes ( '"a"b"c') , dbo.TrimQuotes ( 'a"b"c')
, dbo.TrimQuotes ( '"abc'), dbo.TrimQuotes ( '"a""bc'), dbo.TrimQuotes ( '"""b""c""')
<pre>
[/code]

Job to list all the Sql Server Agent Jobs

[code language="sql"]

with JobStatus as

(

select

job_id AS JobId,

row_number() over(partition by job_id order by run_date desc, run_time desc) asExecutionRank,

case run_status when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when3 then 'Canceled' end as LastRunStatus

from

msdb.dbo.sysjobhistory

)

select

j.job_id as JobId,

j.name as JobName,

j.[description] as JobDescription,

j.[enabled] as IsJobEnabled,

js.LastRunStatus,

ja.run_requested_date as LastRunDate,

ja.next_scheduled_run_date as NextRunDate,

datediff(second, ja.start_execution_date, ja.stop_execution_date) as JobDuration

from

msdb.dbo.sysjobs j

join msdb.dbo.sysjobactivity ja on j.job_id = ja.job_id

left join JobStatus js on j.job_id = js.JobId

and js.ExecutionRank = 1

order by

JobName,

NextRunDate
[/code]

Find the table to which a given column belongs - Sql Server

[code]
SELECT * FROM sys.columns WHERE NAME LIKE '%ColumnName%'
[/code]
Result Set will contain object_ids in which the "ColumnName" exists. Use following queries to get the corresponding object names.
[code]
SELECT * FROM sys.objects WHERE object_id in (39879409,
729261853,
2044026513
)
[/code]

More civilized code goes here;)

[code]
SELECT * FROM sys.objects O
INNER JOIN sys.columns C
ON O.object_id = C.object_id
WHERE C.NAME LIKE '%ColumnName%'

[/code]

Clustered and Non Clustered Indexes

Clustered Index

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows

• Only 1 allowed per table


• Physically rearranges the data in the table to conform to the index constraints


• For use on columns that are frequently searched for ranges of data


• For use on columns with low selectivity

Non-Clustered Index

A nonclustered index is analogous to an index in a textbook.

Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum

• Up to 249 allowed per table in SQL 2000,2005 and upto 999 allowed in SQL 2008


• Creates a separate list of key values with pointers to the location of the data in the data pages


• For use on columns that are searched for single values


• For use on columns with high selectivity

In following example column FileName is varchar(400), which will increase the size of the index key bigger than it is allowed. If we still want to include in our cover index to gain performance we can do it by using the Keyword INCLUDE.

USE AdventureWorks
GO
CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName)

Non-key columns can be included only in non-clustered indexes. Columns can’t be defined in both the key column and the INCLUDE list. Column names can’t be repeated in the INCLUDE list. Non-key columns can be dropped from a table only after the non-key index is dropped first. For Included Column Index to exist there must be at least one key column defined with a maximum of 16 key columns and 1023 included columns.

Avoid adding unnecessary columns. Adding too many index columns, key or non-key as they will affect negatively on performance. Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency. More disk space will be required to store the index. Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Another example to test:
Create following Index on Database AdventureWorks in SQL SERVER 2005

USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
GO

Test the performance of following query before and after creating Index. The performance improvement is significant.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000'
AND '99999';
GO

One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.

You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.
SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process. To use this tool, first use SQL Profiler to capture a trace of the activity for which you wish to optimize performance. You may wish to run the trace for an extended period of time to capture a wide range of activity. Then, using Enterprise Manager, start the Index Tuning Wizard and instruct it to recommend indexes based upon the captured trace. It will not only suggest appropriate columns for queries but also provide you with an estimate of the performance increase you’ll experience after making those changes!

When Non Clustered Indexes should be used:

http://www.sql-server-performance.com/2007/nonclustered-indexes/

Before you create nonclustered indexes, understand how your data will be accessed. Consider using nonclustered indexes for:

• Columns that contain a large number of distinct values, such as a combination of last name and first name (if a clustered index is used for other columns). If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is usually more efficient.


• Queries that do not return large result sets.


• Columns frequently involved in search conditions of a query (WHERE clause) that return exact matches.


• Decision-support-system applications for which joins and grouping are frequently required. Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.


• Covering all columns from one table in a given query. This eliminates accessing the table or clustered index altogether.

References: http://blog.sqlauthority.com/2007/04/23/sql-server-understanding-new-index-type-of-sql-server-2005-included-column-index-along-with-clustered-index-and-non-clustered-index/

Index tuning: http://www.brentozar.com/archive/2009/07/tuning-tip-identify-overlapping-indexes/

Covered Index: http://blog.sqlauthority.com/2010/03/09/sql-server-improve-performance-by-reducing-io-creating-covered-index/

Interview question - Quick Links

SSRS

http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_1.aspx

http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_2.aspx

http://venkateswarlu.co.in/FAQ/CHAPTER_10.aspx

http://sqlserversolutions.blogspot.com/2011/06/ssrs-interview-questions.html

Running value function: http://mangalpardeshi.blogspot.in/2009/03/runningvalue-function-reporting.html

SSRS Formatting

http://sqlsafety.blogspot.in/2010/02/ssrs-formatting.html

SSRS Functions:

http://www.venkateswarlu.co.in/BI/ssrs/SSRS_Common_Functions__Text.aspx

SSRS Tips and Tricks

http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/

Dataware housing

http://blog.sqlauthority.com/2007/07/25/sql-server-data-warehousing-interview-questions-and-answers-introduction/

SSIS

http://venkateswarlu.co.in/FAQ/Chapter_1.aspx -- --V  Good

http://www.sql-server-business-intelligence.com/sql-server/interview-questions-and-answers/ssis-interview-questions-and-answers-pdf-download

http://www.aboutsql.in/

SSIS Event Handlers and Error Logging:

http://beyondrelational.com/modules/12/tutorials/24/tutorials/9686/getting-started-with-ssis-part-10-event-handling-and-logging.

Fuzzy Grouping:

http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/

http://f5debug.net/2011/04/25/sql-server-integration-services-ssis-%E2%80%93-part-45-%E2%80%93-fuzzy-grouping-transformation-in-ssis/

Fuzzy Look Up: http://www.bimonkey.com/2009/06/the-fuzzy-lookup-transformation/

SSIS Performance Tuning: http://technet.microsoft.com/en-us/library/cc966529.aspx

SSIS Check Points:

http://simonworth.wordpress.com/2009/11/08/ssis-package-properties-checkpoints/

Multicast Transformation vs Condition Split: 

http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/

.Net: http://faq.programmerworld.net/programming/asp.net-interview-questions-answers.htm

Sql Server Performance tuning: http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/ <--do Check the comments to the post

SDLC:

http://www.wiziq.com/tutorial/119305-Software-Engineering-What-Why-amp-How

SQL:

User Defined Datatypes: http://www.venkateswarlu.co.in/articles/SQL/UserDefined_Data_Types_in_sql_server.aspx

Performance Related: http://www.venkateswarlu.co.in/articles/SQL/SQL_performance.aspx

http://www.venkateswarlu.co.in/articles/SQL/Performance_Tuning.aspx

Rank, Dense_Rank, row_number: http://www.venkateswarlu.co.in/articles/SQL/RANK_RowNumber_DenseRank.aspx

Miscellaneous: http://venkateswarlu.co.in/sqlserver/articles.aspx

.Net:

http://www.dotnetfunda.com/interview/showcatquestion.aspx?category=33

http://faq.programmerworld.net/programming/asp.net-interview-questions-answers.htm

http://www.careerride.com/ASPNet-Questions.aspx

Single tier – MS access, MS excel- Single file – direct acess ---usually single user application

2 tier – Client – Server application  --multi user apps.

Multi Tier applications?

-      3 tier or N tier

-      Presentation Layer , Business Logic and Database Layer

• Presentation Layer – UI or Front end
  
  
  
  • Handles User interactions – Screen validations
  
  
  • Should not contain business logic
  
  
  
  


• Business Logic Layer – Middle ware or Back end
  
  
  
  • Set of rules to process the information
  
  
  • Should not contain presentation or data access code
  
  
  
  


• Data Layer
  
  
  
  • Database , Datasets , RDBMS\DBMS
  
  
  • Provide access to back-end i.e. database
  
  
  
  

3 Tier Architecture Advantages:

-      Easy to maintain

-      Components are resusable

-      Faster development by division of work

• Web designer can do presentation


• Software engineer can do the logic part


• DB devloper and admin can handle the backend and data modeling

http://blog.simcrest.com/what-is-3-tier-architecture-and-why-do-you-need-it/

http://queens.db.toronto.edu/~papaggel/courses/csc309/docs/lectures/web-architectures.pdf

Design Patterns?

Solution to common problems defined by gang of four programmers.

• Factory  -  Creates an instance of several derived classes


• Abstract Factory -  Creates an instance of several families of classes


• Singlton – single instance of a class can exists


• Builder - Separates object construction from its representation

data modeling?  

• Process of defining and analysing data requirements to support business  processes.


• ER diagrams are designed in VISIO to model the data requirements

SDLC Models?

Software development life cycle :

• Waterfall model


• Rapid application development Model


• Incremental\Iterative Model


• Proto-type Model


• Spiral Model

Waterfall model – requirement garthering, designing, implementation, verification\testing, maintainace

• Each phase has to be completed before the next starts


• A review takes place after a particular phase completes and the next starts. – review is with the aim to check if the project is on right path.


• No overlapping of phases


• Easy to use and implement


• Not good for projects where requirements changes frequently.


• Poor model for complex projects

Incremental\Iterative Model This method doesn’t require to start with full requirement.

• Also called Multi – waterfall model


• Development and delivery is broken down into increments

Proto-type Model In this model , a proto-type (an early approximation of a final system or product) is build for user acceptance and the improvements are done untill the users are convinced.

Spiral Model  Combined features of proto-type and waterfall model.

• Similar to incremental model


• Planing, Risk analysis, engineering and evaluation

Rapid application development Model Based on component integration techniques

• Short development cycle and linear squential model


• Phases: business modeling , data modeling , process modeling, application modeling, testing.

http://www.slideshare.net/SivaprasanthRentala1975/sdlc-models

White box testing(tranasparent testing) – internal – logical\data flow testing

Black box testing – input and desired output testing, not concerened with the inner code.

Grey box à combination of white and black box

SSIS – Tasks?

• Data Flow task –extracts data from source, allow transformations, and the load data into target data destination


• File System task – copy/move/delete files and folder over a file system


• FTP task  – copy/move/delete files and folder over a FTP


• Execute package


• Execute SQL


• Execute Script


• Send Mail


• Execute sql server agent job


• Notify operator


• Back up database


• Bulk insert


• For Loop:  Repeat a task a fixed number of times


•  Foreach Loop: Repeat a task by enumerating over a group of objects

http://venkateswarlu.co.in/FAQ/Chapter_1.aspx#.UPvFTB1vNAM

SSIS Auditing and Error Handling?

http://hardik-bhavsar.blogspot.in/2011/08/auditing-and-error-handling-in-ssis.html

SSIS deploying packages:

1. Database – schedule using Jobs


2. File system – schedule using control M


3. Can be called via command prompt or execute the .dtsx package

Sql Server

Normalisation – process of organising data in form of table and define relationship and hence form a realtional database.

RDBMS – ACID – atomicity , consistency, isolation, durability

1 NF – eliminating redundant groups

2 NF – eliminating redundant data

3NF - Eliminate Columns Not Dependent On Key

Trigers – DDL (drop table, create table, alter table or login events)

DML (insert update delete)

Instead off – fired instead off insert update delete

SSRS – variables – overview?

Delete duplicate rows

[code]

WITH CTE (COl1,Col2, DuplicateCount) AS
(SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) ASDuplicateCount
FROM DuplicateRcordTable)
DELETE FROM CTE WHERE DuplicateCount >1

[/code]

Master – contain information for all databases

Model – template for database --

MSDB –dts packages, jobs

Resource: read only – contain all system objects – sys.objects

Split Function: write a split function to get output of (1,2,3)  in a table in different rows

Scope Identity and @@identity

@@IDENTITY

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.

SCOPE_IDENTITY

It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

IDENT_CURRENT

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

Date , date part functions

http://shawpnendu.blogspot.in/2009/05/datepart-dateadd-datediff-sql-server.html

Case and if statement: difference , syntax

[code]

SELECT   ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;

[/code]

‘If statement’ cannot be used inside select statement <--- v important

Table variable and #table difference – advantages and disadvantages:

http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table

major disadvantage of table variable

• Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.


• Temp tables behave same as normal tables and are bound by transactions

http://ssisdevelopers.wordpress.com/2013/03/04/temp-table-vs-table-variable-sql-server/

Power of CTE

http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

Can two different SPs create same #table or ##table (temp tables)

Rank dense rank row number

Pivot and unpivot

Find the top third row from a dataset

Find the day(Monday, Tuesday etc) on the first day of the current month (lets say today is 25^th feb)

Steps for optimisation

OOPS Concepts? Inheritance, polimorphism (operation overloding and overridding) , encapsulation,abstraction

http://www.c-sharpcorner.com/UploadFile/eecabral/OOPSand.NET211102005075520AM/OOPSand.NET2.aspx

http://manishagrahari.blogspot.in/2011/08/oops.html

http://dotnetstories.wordpress.com/2009/06/21/object-oriented-programming-concepts-with-c3-0/

Boxing – converting a value type to reference type

unboxing -- reverese i.e. converting a reference type to value type

http://stackoverflow.com/questions/2111857/why-do-we-need-boxing-and-unboxing-in-c

Delegates – function pointers

Cloud computing

Cloud computing is the use of computing resources (hardware and software) that are delivered as a service over a network (typically the Internet). The name comes from the use of a cloud-shaped symbol as an abstraction for the complex infrastructure it contains in system diagrams. Cloud computing entrusts remote services with a user's data, software and computation. There are many types of public cloud computing:^[1]

• Infrastructure as a service (IaaS)


• Platform as a service (PaaS)


• Software as a service (SaaS)


• Network as a service (NaaS)


• Storage as a service (STaaS)


• Security as a service (SECaaS)

In the business model using software as a service, users are provided access to application software and databases. The cloud providers manage the infrastructure and platforms on which the applications run. SaaS is sometimes referred to as “on-demand software” and is usually priced on a pay-per-use basis. SaaS providers generally price applications using a subscription fee.

Advantages:

• SaaS allows a business the potential to reduce IT operational costs by outsourcing hardware and software maintenance and support to the cloud provider.


• This enables the business to reallocate IT operations costs away from hardware/software spending and personnel expenses, towards meeting other IT goals.


• In addition, with applications hosted centrally, updates can be released without the need for users to install new software.

Disadvantages:

• One drawback of SaaS is that the users' data are stored on the cloud provider’s server. As a result, there could be unauthorized access to the data.


• Basically from the security point of view it is not the safest as compared to others.

Software as a Service (SaaS)

Software as a service (SaaS) sometimes referred to as "on-demand software", is a software delivery model in which software and associated data are centrally hosted on the cloud. SaaS is typically accessed by users using a thin client via a web browser.e.g. Facebook.

SSRS

http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_1.aspx

http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_2.aspx

http://venkateswarlu.co.in/FAQ/CHAPTER_10.aspx

http://sqlserversolutions.blogspot.com/2011/06/ssrs-interview-questions.html

SSRS Formatting

http://sqlsafety.blogspot.in/2010/02/ssrs-formatting.html

SSRS Functions:

http://www.venkateswarlu.co.in/BI/ssrs/SSRS_Common_Functions__Text.aspx

SSRS Tips and Tricks

http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/

Dataware housing

http://blog.sqlauthority.com/2007/07/25/sql-server-data-warehousing-interview-questions-and-answers-introduction/

SSIS

http://venkateswarlu.co.in/FAQ/Chapter_1.aspx -- --V  Good

http://www.sql-server-business-intelligence.com/sql-server/interview-questions-and-answers/ssis-interview-questions-and-answers-pdf-download

SSIS Event Handlers and Error Logging:

http://beyondrelational.com/modules/12/tutorials/24/tutorials/9686/getting-started-with-ssis-part-10-event-handling-and-logging.

Fuzzy Grouping:

http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/

http://f5debug.net/2011/04/25/sql-server-integration-services-ssis-%E2%80%93-part-45-%E2%80%93-fuzzy-grouping-transformation-in-ssis/

Fuzzy Look Up: http://www.bimonkey.com/2009/06/the-fuzzy-lookup-transformation/

SSIS Performance Tuning: http://technet.microsoft.com/en-us/library/cc966529.aspx

SSIS Check Points:

http://simonworth.wordpress.com/2009/11/08/ssis-package-properties-checkpoints/

Multicast Transformation vs Condition Split: 

http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/

.Net: http://faq.programmerworld.net/programming/asp.net-interview-questions-answers.htm

Sql Server Performance tuning: http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/ <--do Check the comments to the post

SDLC:

http://www.wiziq.com/tutorial/119305-Software-Engineering-What-Why-amp-How

SQL:

User Defined Datatypes: http://www.venkateswarlu.co.in/articles/SQL/UserDefined_Data_Types_in_sql_server.aspx

Performance Related: http://www.venkateswarlu.co.in/articles/SQL/SQL_performance.aspx

http://www.venkateswarlu.co.in/articles/SQL/Performance_Tuning.aspx

Rank, Dense_Rank, row_number: http://www.venkateswarlu.co.in/articles/SQL/RANK_RowNumber_DenseRank.aspx

.Net:

http://www.dotnetfunda.com/interview/showcatquestion.aspx?category=33

http://faq.programmerworld.net/programming/asp.net-interview-questions-answers.htm

http://www.careerride.com/ASPNet-Questions.aspx

Microsoft Azure - An Intro

Microsoft Azure

Advantages:
Manageability, High Availability :
Higher availability with less management required. (less labor required to maintain network security , maintenance of servers etc ).No Physical servers to buy, install, patch, maintain or secure.

Scalability:

1 GB to 50 GB support and scalable with partitioning to larger DBs.

Global Scalability:

If you want to target a specific region, you can deploy your database at the closest data center

Familiar Development Model : Built on T-Sql

Shortcomings:

Doesn’t support XML datatypes and many Sql commands.

No support for backup and restore. Need to use following third party components.

1. For Restore: SQL Azure Migration Wizard (SSMS 2008 R2 should be installed) To Migrate database from Sql Server to Windows Azure. Other way out is to generate Script from SSMS 2008 R2 for windows azure and then deploy the script on Azure server. But not a robust solution.


2. For DB Sync: Microsoft Sync Framework Power Pack for SQL Azure to synchronize data between a data source and a SQL Azure installation. But if the source DB is more than 50 GB, the client needs to manage the partitioning manually.


3. For Partitioning: Enzo SQL Shard Third party component for partitioning.


4. For Backup: Found following third party components\scripts. We need to drill down to these to check the best suitable for us. Used to backup from Sql Azure to Sql Blob Storage. Please note: Microsoft charges ~$0.10 per GB for Blob Storage.


5.  Red Gate Backup tool: Considered as best but presently available as beta version. Probably would be paid software once it comes out of Beta.


6. Script to automate back up to sql blob storage


7. SQL Azure Migration Wizard: The SQL Azure Migration Wizard gives you the options to analyzes, generates scripts, and migrate data (via BCP) from:

1. SQL Server to SQL Azure


2. SQL Azure to SQL Server


3. SQL Azure to SQL Azure

Other options not available when working with a SQL Azure database include: (one in red would affect us)

1. Full-text indexing


2. CLR custom types (however, the built-in Geometry and Geography CLR types are supported)


3. RowGUIDs (use the uniqueidentifier type with the NEWID function instead)


4. XML column indices


5. Filestream datatype


6. Sparse columns


7. SQL Azure Doesn't Support Database Mirroring or Failover Clustering

Pricing:

As per the example below Pricing is quite simple:

Database + Data transfer based per day charging

Costing Examples: http://www.microsoft.com/en-us/showcase/details.aspx?uuid=09aa4f10-333a-4c98-aed1-4cb300de63ec&WT.mc_id=otc-f-corp-jtc-DPR-MVA_INTROSQLAZURE

Following page makes the pricing a bit confusing as it states that we would be charged on Bandwidth usage, catching etc in addition to the Database and Data transfer.

Costing Calculator: https://www.windowsazure.com/en-us/pricing/calculator/

Pricing can also be based on Computation Power, Db storage, Blob Storage, Bandwidth

1. Compute: For front end application hosting. You can have 0 to 10 Instances of VM. à Required to consider in cast we plan to host our front end also.


2. Database :2 plans.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   Database	Standard pay-as-you-go pricing
   Web Edition (up to 5 GB)	$9.99 per 1 GB of database per month
   Business Edition (up to 150 GB)	$99.99 per 10 GB of database per month (Maximum charge of $499.95 per database)*

   
   


3. Data transfers:  All inbound data transfers, i.e. data going into the Window Azure platform datacenters, are free. Price for outbound data transfers, i.e. data going out of the Windows Azure platform datacenters, is shown below.
   
   
   
   
   
   
   
   
   
   
   
   
   

   Pricing details for outbound data transfers

   North America and Europe regions: $0.12

   Asia Pacific Region: $0.19

   
   


4. Bandwidth: 0 to 2000 Gb. Charged as per bandwidth used.
   http://msdn.microsoft.com/en-us/library/windowsazure/ee730903.aspx
   Bandwidth used between SQL Azure and Windows Azure or Windows Azure AppFabric is free within the same sub-region or data center. When deploying a Windows Azure application, locate the application and the SQL Azure database in the same sub-region to avoid bandwidth costs. For more information, see Accounts and Billing in SQL Azure.


5. Catching services, Storage Services etc are also provided and charged.

Some important tools that you can use with SQL Azure for Tuning and Monitoring include:

1. SSMS Query Optimizer to view estimated or actual query execution plan details and client statistics


2. Select Dynamic Management views to monitor health and status:
   http://msdn.microsoft.com/en-us/library/windowsazure/ff394114.aspx

Some Depreciated features in Sql Azure

1. ‘ANSI_NULLS’ is not a recognized SET option.


2. Deprecated feature ‘SET ANSI_PADDING OFF’ is not supported in this version of SQL Server.


3. Deprecated feature ‘More than two-part column name’ is not supported in this version of SQL Server.  This a significant change if your using Schemas.


4. Deprecated feature ‘Data types: text ntext or image’ is not supported in this version of SQL Server.


5. Deprecated feature ‘Table hint without WITH’ is not supported in this version of SQL Server.


6. Global temp Tables are not supported in this version of SQL Server.


7. A full list id found at Deprecated Database Engine Features in SQL Server 2008 – MSDN.

Important Links:

Video tutorial for scripting out DB Schema for migration to SQL Azure using SSMS R2

https://www.microsoftvirtualacademy.com : Free course to learn basics of SQL Azure by Microsoft.

http://www.geeksco.com/blog/?p=36 : sql azure errors and trouble shooting

http://sqlazuretutorials.com/wordpress/sql-statements-not-supported-for-sql-azure/ : SQL Statements not supported for sql azure

SQL Server - DBCC Commands

Very informative post

Grant Execute to Given User - Dynamic Query

declare @username varchar(255)
set @username = 'YourUser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='PROCEDURE'

The above query with result in all the User Defined Stored Procedures with "Grant Execute Permissions.

Run the resulting commands and 'YourUser' has the Execute Rights :)

SSIS Expression To Append Time and Date with File Name Dynamically

Use Following expression to append date and time at the time of package execution with the file name.
[code]
"D:\output\LogFile"
+ (DT_WSTR, 50)(DT_DBDATE)GETDATE()+"#"
+(DT_STR,2,1252)DATEPART("hh",GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("ss",GETDATE())
+".txt"
[/code]
Evaluated Expression would look some thing like this:

D:\output\LogFile2012-01-17#12-11-54.txt

Using Variable:

@[User::OutputFileLocation]  : Variable to hold file location

@[User::OutputFileName]  : Variable to hold file name

Expression:
[code]
@[User::OutputFileLocation]
+ @[User::OutputFileName]
+ (DT_WSTR, 50)(DT_DBDATE)GETDATE()+"#"
+(DT_STR,2,1252)DATEPART("hh",GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("ss",GETDATE())
+".txt"
[/code]

Hope it Helps :)

BIDS 2008 does not support XML datatype in OLE DB Command

Senario Overview:

We have some SP which expect some XML Type input parameters and also return XML Type parameter.

Having a requirement to insert data in bulk to Database we planned to use SSIS.

Working on the package, I found that SSIS 2008 doesn't supports XML datatypes in OLE DB Command.

Error Details:

Operand type clash: int is incompatible with xml

Googled.. and found that the issue has been solved in latest releases .. :)

But, No luck after installing the latest patch.

Posted the ISSUE on MSDN Form:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/be51fb99-5c28-4b3e-8d4b-a02df6e72ca4

Found the following work around:

1. Create a new SP and use NVARCHAR(MAX) instead of Input XML Datatypes and call this SP from SSIS ( We could have modified the original though, but preferred avoiding dependencies issues)

2. Call the main SP from the new SP after type casting to XML

Now new issue:

As we have one XML Output datatype . It still went on giving error even after casting to NVARCHAR(MAX)

Finally, NVARCHAR(4000) worked fine.

Not a robust solution but good enuf to full fill our requirements :)

Combining Multiple Rows into One Row - Sql Server

In this example i am going to describe how to combine multiple  rows in one column in MS SQL.

Here is the scenario
uSER_id and respective Hobbies are listed in the table.

i want to combine all the hobbies of a user


declare @User table
(
UserId int,
Hobbies VARCHAR(100)
)

INSERT INTO @User (UserId,Hobbies) values
(1, 'gaming') , (1, 'cricket'),
(2, 'movies') , (2, 'cricket'), (2, 'gardening') , (2, 'football'),
(3, 'gaming') , (3, 'football')
SELECT * FROM @User



SELECT DISTINCT
UserId,
Allhobbies = substring( ( SELECT ', ' + Hobbies
FROM @User T1
WHERE T1.UserId = T2.UserId FOR XML path(''), elements
),2,500)
FROM @User T2