http://www.databasejournal.com/features/mssql/report-subscription-in-ssrs-2008-r2.html
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.
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
SSIS
http://venkateswarlu.co.in/FAQ/Chapter_1.aspx — –V Good
SSIS Event Handlers and Error Logging:
Fuzzy Grouping:
http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/
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:
.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:
http://blog.sqlauthority.com/sql-server-interview-questions-and-answers/
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
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:
- Database – schedule using Jobs
- File system – schedule using control M
- 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
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
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
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;
‘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
https://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
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 25th 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.
SSIS and Sql Server Journey 