
Category Archives: MS BI
Measure Ms Sql Execution time in resolution of milliseconds
Temporary tables and Table variables – MsSQL

Temporary Tables
The simple answer is yes you can. Let look at a simple CREATE TABLE statement:
CREATE TABLE #Yaks ( YakID int, YakName char(30) )
Temporary tables are created in tempdb
. If you run this query:
CREATE TABLE #Yaks ( YakID int, YakName char(30) ) select name from tempdb..sysobjects where name like '#yak%' drop table #yaks
Another Example
CREATE TABLE #TibetanYaks( YakID int, YakName char(30) ) INSERT INTO #TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' -- Do some stuff with the table drop table #TibetanYaks
Table Variables
If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:
DECLARE @TibetanYaks TABLE ( YakID int, YakName char(30) ) INSERT INTO @TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' -- Do some stuff with the table
Example
DECLARE @TibetanYaks TABLE ( YakID int, YakName char(30) ) INSERT INTO @TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' UPDATE @TibetanYaks SET YakName = UPPER(YakName) SELECT * FROM @TibetanYaks
Global Temporary Tables
You can also create global temporary tables. These are named with two pound signs. For example, ##YakHerders
is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it. These are rarely used in SQL Server.
Summary
That shows you an example of creating a temporary table, modifying it, and returning the values to the calling program. I hope this gives you what you were looking for.
How to search text at all Ms SQL tables, fields and all object?

1. Create the following Stored Procedure at the database you want to search:
CREATE PROCEDURE SearchTables @Tablenames VARCHAR(500) ,@SearchStr NVARCHAR(60) ,@GenerateSQLOnly Bit = 0 AS /* Parameters and usage @Tablenames -- Provide a single table name or multiple table name with comma seperated. If left blank , it will check for all the tables in the database @SearchStr -- Provide the search string. Use the '%' to coin the search. EX : X%--- will give data staring with X %X--- will give data ending with X %X%--- will give data containig X @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database. By default it is 0 and it will search. Samples : 1. To search data in a table EXEC SearchTables @Tablenames = 'T1' ,@SearchStr = '%TEST%' The above sample searches in table T1 with string containing TEST. 2. To search in a multiple table EXEC SearchTables @Tablenames = 'T2' ,@SearchStr = '%TEST%' The above sample searches in tables T1 & T2 with string containing TEST. 3. To search in a all table EXEC SearchTables @Tablenames = '%' ,@SearchStr = '%TEST%' The above sample searches in all table with string containing TEST. 4. Generate the SQL for the Select statements EXEC SearchTables @Tablenames = 'T1' ,@SearchStr = '%TEST%' ,@GenerateSQLOnly = 1 */ SET NOCOUNT ON DECLARE @CheckTableNames Table ( Tablename sysname ) DECLARE @SQLTbl TABLE ( Tablename SYSNAME ,WHEREClause VARCHAR(MAX) ,SQLStatement VARCHAR(MAX) ,Execstatus BIT ) DECLARE @sql VARCHAR(MAX) DECLARE @tmpTblname sysname IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') BEGIN INSERT INTO @CheckTableNames SELECT Name FROM sys.tables END ELSE BEGIN SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' INSERT INTO @CheckTableNames EXEC(@sql) END INSERT INTO @SQLTbl ( Tablename,WHEREClause) SELECT SCh.name + '.' + ST.NAME, ( SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) FROM SYS.columns SC JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id AND STy.user_type_id =SC.user_type_id WHERE STY.name in ('varchar','char','nvarchar','nchar') AND SC.object_id = ST.object_id ORDER BY SC.name FOR XML PATH('') ) FROM SYS.tables ST JOIN @CheckTableNames chktbls ON chktbls.Tablename = ST.name JOIN SYS.schemas SCh ON ST.schema_id = SCh.schema_id WHERE ST.name <> 'SearchTMP' GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ; UPDATE @SQLTbl SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) DELETE FROM @SQLTbl WHERE WHEREClause IS NULL WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) BEGIN SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0 IF @GenerateSQLOnly = 0 BEGIN IF OBJECT_ID('SearchTMP','U') IS NOT NULL DROP TABLE SearchTMP EXEC (@SQL) IF EXISTS(SELECT 1 FROM SearchTMP) BEGIN SELECT Tablename=@tmpTblname,* FROM SearchTMP END END ELSE BEGIN PRINT REPLICATE('-',100) PRINT @tmpTblname PRINT REPLICATE('-',100) PRINT replace(@sql,'INTO SearchTMP','') END UPDATE @SQLTbl SET Execstatus = 1 WHERE Tablename = @tmpTblname END SET NOCOUNT OFF go
2. Run the following stored procedure as follows:
DECLARE @return_value int
EXEC @return_value = [dbo].[SearchTables]
@Tablenames = N'%',
@SearchStr = N'%TextToSearch%',
@GenerateSQLOnly = 0
SELECT 'Return Value' = @return_value
A Beginner’s Guide to SQL

A great guide for learning SQL from the start, from Udemy:
https://blog.udemy.com/beginners-guide-to-sql/
What Wikipedia has to say about UDemy:
Udemy.com is a platform or marketplace for online learning. Unlike academic MOOC programs driven by traditional collegiate coursework, Udemy provides a platform for experts of any kind to create courses which can be offered to the public, either at no charge or for a tuition fee.[1]Udemy provides tools which enable users to create a course, promote it and earn money from student tuition charges.
In addition to SQL You will find there also the following tutorials:
BI SSRS / SSMS / SSAS / SSMS
BI SSRS / SSMS / SSAS / SSMS
Password: nayabi2015 https://www.dropbox.com/sh/qjawz1tn033f2qn/AACgZP17vaBk4as_HwGBr4IIa?dl=0
Bulk Copy Program – BCP SQL
Bulk Copy Program – BCP SQL
Bulk Copy Program (BCP) – command-line tool used to import or export data against a Microsoft SQL Server or Sybase database. The tool is often more efficient than more recent GUI-based applications, such as DTS, to import and extract data.
Resources: http://en.wikipedia.org/wiki/Bulk_Copy_Program http://databases.about.com/od/sqlserver/a/bcp.htm http://sqlfool.com/2008/12/bcp-basics/
Pyramid Analytics – Videos
Learn SSIS in 19 video lessons

01 - Getting Started 02 - Performing Basic Tasks 03 - Basic Transformations 04 - Variables 05 - Data Types and Data Conversion 06 - Expressions 07 - Conditional split and derived column transforms 08 - Debugging 09 - Lookup Transforms 10 - Sequence Containers and FOR Loops 11 - Looping Over Files 12 - Other Foreach Loops 13 - Script Tasks using C# 14 - Script Tasks using Visual Basic 15 - Script Components 16 - Expression and Other Constraints 17 - Event-handling and logging 18 - Error handling 19 - Parameters and deployment
The Connection Strings Reference
The Connection Strings Reference
ConnectionStrings.com help developers connect software to data. It’s a straight to the point reference with connection strings, a knowledge base of articles and database connectivity content and a host of Q & A forums where developers help each other in finding solutions.
http://www.connectionstrings.com/
Step by step of executing SSIS 2012 package through stored procedure
Step by step of executing SSIS 2012 package through stored procedure
http://blogs.msdn.com/b/biblog/archive/2013/05/07/step-by-step-of-executing-ssis-2012-package-through-stored-procedure.aspx
SSAS Cubes, Excel and SharePoint
SSAS Cubes, Excel and SharePoint
http://workerthread.wordpress.com/2012/01/10/ssas-cubes-excel-and-sharepoint-keeping-contributors-happy/
What is SSAS? Analytic services learning resources

Microsoft SQL Server Analysis Services, SSAS, is an online analytical processing (OLAP) and data mining tool inMicrosoft SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services, Reporting Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors – Multidimensional and Tabular (from wikipedia).
Learn SSAS: PDF, Videos and Demo files:
01. Download SSAS Tutorials PDF file from Microsoft 02. Adventure Works for SQL Server 2012 from CodePlex 03. SSAS 11 Videos of PCTeach.me: 04. http://pcteach.me/Series/microsoft-ssas/ 05. 6 Lessons of SSAS MDX 06. 7 Videos of SSAS MDX
SSAS Videos at PCTeach.me:
01. Analysis Services - 01 Prerequisite Guide 02. Analysis Services - 02 Data Source Creation 03. Analysis Services - 03 Data Source Views 04. Analysis Services - 04 Cube Creation 05. Analysis Services - 05 Dimension Fundamentals 06. Analysis Services - 06 Dimension Hierarchies 07. Analysis Services - 07 Dimension Attribute Relationships 08. Analysis Services - 08 Dimension Storage 09. Analysis Services - 09 Dimension Discretization 10. Analysis Services - 10 Parent/Child Dimension Hierachies 11. Analysis Services - 11 Star and Snowflake Schemas