sql server to day question

CREATE TABLE mytable ( id INT, name VARCHAR(10) );

 GO

 INSERT mytable ( id, name )

 VALUES 

 ( 1, 'S' ), 

 ( 2 , 'P'),

 ( NULL, 'Q' ), 

 ( 4, 'T' ), 

 ( NULL, 'M' ), 

 ( 6, 'N' ); 

 SELECT COUNT(*) FROM mytable;

ANSWER: 
1) 2

2) 4

3) 6

4) NULL


post the right answer

SSIS Real Time Question

How do I access Microsoft Excel 2007 from SSIS 2005 version. I am unable to access the files from SSIS

ANS:
Use ACE connector for Excel.
ACE OLEDB 12.0:

TYPE OLE DB ProviderUSAGE Provider=Microsoft.ACE.OLEDB.12.0MANUFACTURER Microsoft 

Xlsx files

This one is for connecting to Excel 2007 files with the Xlsx file extension. That is the Office Open XML format with macros disabled.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

SSRS Real time scenarios

SSRS (SQL Server Reporting Services)Real Time Scenarios:

Scenario 1:

 Show the report like Drill Down. Here I have 3 groups as shown in below.

Main Category Name 

     +  Sub Category Name1

               + Sub Details1

                             Details1Name     rate      qty    amount

                +Sub Details2

                               Details1Name     rate      qty    amount

                +SubDetails3

                               Details1Name     rate      qty    amount

       +SubCategory Name2

       +Sub Catagory Name3   

             .....


Scenario 2:

I have an SSRS Report that needs to be filtered via multi-value parameter selection. normally we can done for single column. But here we want to allow multi-select based on multiple columns. The column layout is as follows:

AFLAG    BFLAG    CFLAG     DFLAG
X                X               X           X
NULL          X             NULL      NULL
X               NULL           X         NULL
X                X                X         X
X              NULL            X         NULL
NULL         X                X          NULL
X               X               NULL     NULL
X               X               NULL    NULL

In my report parameter, when multiple AFLAG, BFLAG, CFLAG or DFLAG values are selected, I want to be able to display every Reports that contains an 'X' in each column once, where there are multiple values marked with 'X' in a single report.


Scenario 3:

Testing Procedure In SSIS

Test cases For SSIS Project :

Testing For a SSIS project Is very important question in interviews.


 @ Verify all the tables have been imported

@ Verify all the rows in each table have been imported

@ Verify all columns specified in source query for each table have been imported

@ Verify all the data have been recived without any truncation for each column

@ Verify the schema at source and destination

@ Verify the time taken /speed for data transfer

@ Fields truncated due to diffence in length of the field.

What is Report Server Data Base in Reporting Services?

What is Report Server Data Base in Reporting Services?

Report Server Data Base:
The report server database is the main store of the data in reporting services. It contains report defenations, report models, data source, schedules, security information and snapshots.Because of this this is critical that the database be backup regularly .
Now we are going to discuss about some tables in sql server reporting servicies with functinal areas.

1.Resources:

Catalog Table: It contains the report defenations and folder locations and data source information.
  
Data Source Table: It contains Every individual data source information.

2.Security:

Users Table: It contains the user name and security ID(SID) information for autherized users.

Policies Table: It contains a listing references to different security policies.

Policy Users Role Table: It contains an association of users / groups, roles, and policies.

Roles Table: It contains a list of defined roles and the tasks the roles can perform.

3.Snapshots:

Snapshot data Table:  It contains information used to run an individual snapshot, including query parameters and snapshot dependencies. 

ChunkData Table: it stores the report snap shots.

History Table: It stores a refernce between stored snapshots and the date they were captured.

4.Scheduling:

schedule Table: it contains information for different report execution and subscription delivery scedules.

Report Schedule Table: It contains an association between a given report ,its execution schedule and the action to take.

Subscriptions Table: it contains a listing of individual subscriptions ,including the owner,parameter and delivery extensions.

Notifications Table: It contains the subcription notification information such as data processed ,last run time, and delivery extensions.

Event Table:  It contains Temporary storage location and event notifications.

Active Subscriptions Table: It contains suscription sucess/failure information

Running Jobs Table: It contains the currently executing scheduled process.

5. Administration:

Configuration info Table:  It contains Reporting services configure information ,which should be administrated through prescribed interfaces and not by directly editing this table data.

Keys Table: It contains the public and private keys for data encryption.

ExecutionLogStorage Table: It contains a listing of reports that have been executed and critical meta data about the event.


6.Report Models:

ModelDrill Table: It contains information used when implementing report builder infinate drill down features.

ModelItemPolicy Table: It contains an association between a given report item,model  and policy.

ModelPrespective Table: It contains an association between a given report model and its perspective.

Explain the Reporting service life cycle?

Explain the Reporting service life cycle?

Ans: The reporting service life cycle as a a three phase process 

1.reports are autherd by end users or report specialists

2.those are managed as part of centralized reporting system

3.finally those  finished reports are delivered to end users through various means

What is report server temp db?

What is report server temp db?

The ReportServerTemp DB stores temporary reporting service information. This includes both session and cache data.

Reporting services will not function properly without the reportServerTempDB  database. If the data base is lost you can simply rebuild it. 

 i will give some tables and related functions in Reporting Server Temp DB

 

Chunk Data Table: It will stores report definition and data for session cached reports and cached instances.

Execution Cache Table: It will stores execution information including time out for cached instances.

PersistedStream Table: It will stores the session level rendered output for an individual user.

Session Data Table: It persists individual user session level information, including report paths and time outs for given session information.

Session Lock Table: It will storesTemporary storage to handle locking of session data.

SnapshotData Table: It will stores temporary snapshot.