windows 8.1 apps are not downloading

Hi There

if you are struck with the weird problem, that windows is unable to download your app, even though you asked it to…

and the app simply sits in the pending status, it might be because of your connection settings…

Yes, the internet connection settings, called metered connections.

Windows 8 / 8.1 have put in a good stuff to stop the download on metered connections, if the internet is chargeable, in order reduce the cost,

Coming to solution of how to get rid of the metered connection use the below

1. Swipe in from the right edge of the screen, tap Settings, and then tap Change PC settings.
   (If you’re using a mouse, point to the upper-right corner of the screen, move the mouse pointer down, click Settings, and then click Change PC settings.)
2. Tap or click Network, and then tap or click Connections.
3. Tap or click the connection you want to change, and then, under Data usage, turn Set as a metered connection on or off.

if this have solved your problem please do let me know through the comments.

 

Generate Create statements for Statstics in SQL Server

 

Howdy EveryOne

if you want to if exists delete and create scripts for the statstics in the SQL Server, the below code works fine.

declare @SQ VARCHAR(4)
SET @SQ = ''''
;WITH StatNames
AS
(
SELECT  s.name StatName, T.name as TableName,s.stats_id stats_id, T.object_id [object_id]
FROM    sys.stats   S   WITH (NOLOCK)
JOIN    sys.Tables  T   WITH (NOLOCK)
ON      S.object_id     = T.object_id
AND     S.user_created  = 1
),
Deflat
AS
(
SELECT SN.StatName,SN.TableName,STUFF(
(  select  ',' + C.name
FROM    sys.stats_columns SC   WITH (NOLOCK)
JOIN    sys.columns C WITH (NOLOCK)
ON      SC.column_id = C.column_id
AND     C.object_id = SN.object_id
AND     SC.stats_id = SN.stats_id
AND     C.object_id = SC.object_id
ORDER   BY SC.stats_column_id
FOR     XML PATH ('')
),1,1,'') Cols
FROM  StatNames SN
)
SELECT 'IF EXISTS ( SELECT 1 FROM sys.stats S, sys.tables T WHERE S.name = ' + @SQ +  StatName + @SQ + ' AND T.name = ' + @SQ + TableName + @SQ + ' ) '
+ ' DROP STATISTICS ' + TableName +'.'+StatName
+ ' CREATE  STATISTICS ' + StatName  + ' ON ' + TableName + '(' + Cols + ')'
FROM Deflat

Generate SQL Insert Statements with Indentation

Howdy Everyone.

Generating SQL insert statements for a given table is always a tedious task in SQL Server, Mostly the automated generated scripts are not indented with spaces, so that it will be easier to read and most of all it will be easy to change a column values or remove column values.

The below code will give you the insert statements along with the indentation of the code.

 

<pre>IF EXISTS ( SELECT 1 FROM sys.procedures WHERE name = 'InsertGenerator')
    DROP PROCEDURE InsertGenerator

GO

CREATE PROCEDURE InsertGenerator
(
    @TableName  VARCHAR(100)
)
AS
BEGIN
    DECLARE @IndentationTable   TABLE
    (
        ColumnName      VARCHAR(500)
        ,MaxLength      BIGINT
    )

    DECLARE @SQL        NVARCHAR(MAX)
    DECLARE @Insert     NVARCHAR(MAX)
    DECLARE @SQ         VARCHAR(4)
    DECLARE @SSQ        VARCHAR(4)
    DECLARE @ColumnName VARCHAR(300)
    DECLARE @DataType   VARCHAR(50)
    DECLARE @MaxLength  BIGINT

    SET @SQ     = ''''
    SET @SSQ    = ''''''''''

    BEGIN TRY
        
        DECLARE maxLength CURSOR
        FOR
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME=@TableName 

        DECLARE Cols CURSOR
        FOR
            SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME=@TableName 


        OPEN maxLength
        FETCH NEXT FROM maxLength INTO @ColumnName
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @SQL = 'SELECT ' + @SQ +  @ColumnName + @SQ + ' ColumnName, MAX(LEN('+@ColumnName+')) MaxLength FROM '  + @TableName  + ' '
            INSERT INTO @IndentationTable (ColumnName,MaxLength)
            EXEC sp_executeSQL @SQL
            FETCH NEXT FROM maxLength INTO @ColumnName
        END
        CLOSE maxLength
        DEALLOCATE maxLength

        UPDATE @IndentationTable         
        SET MaxLength = MaxLength + 8

        SELECT @Insert =  'SELECT ' + @SQ + 'INSERT INTO ' + @TableName + '(' + (SELECT STUFF( (SELECT ',' + ColumnName FROM @IndentationTable FOR XML PATH ('')),1,1,'')) + ' ) VALUES ( ' + @SQ + '+'
        SET @SQL = @Insert
        OPEN Cols
        FETCH NEXT FROM Cols INTO @ColumnName,@DataType
        WHILE (@@FETCH_STATUS = 0)
        BEGIN


            SELECT  @MaxLength = MaxLength 
            FROM    @IndentationTable
            WHERE   ColumnName = @ColumnName
            
            
                        
            SELECT @SQL = @SQL + CASE  
                                    WHEN @DataType IN ('BIGINT','BIT','DECIMAL','INT','MONEY','NUMERIC','SMALLINT','SMALLMONEY','TINYINT','FLOAT','REAL')   THEN   ' CAST( ' + @ColumnName + ' AS VARCHAR) '
                                    WHEN @DataType IN ('DATE','DATETIME2','DATETIME','DATETIMEOFFSET','SMALLDATETIME','TIME') THEN @SQ + 'CAST( ' + @SQ + '+' + @SSQ + '+' + 'CAST( ' + @ColumnName + ' AS VARCHAR ) ' + '+' + @SSQ + '+' + @SQ + ' AS ' + @DataType + ' ) ' + @SQ
                                    WHEN @DataType IN ('CHAR','VARCHAR','TEXT') THEN @SSQ + '+' + @ColumnName + '+' + @SSQ
                                    WHEN @DataType IN ('NCHAR','NVARCHAR','NTEXT') THEN 'N' + @SSQ + '+' + @ColumnName + '+' + @SSQ
                                    ELSE   CAST(@ColumnName AS NVARCHAR)
                                  END  +  '+' + 'REPLICATE( ' + @SQ+ ' ' +@SQ + ','+CAST(ISNULL(@MaxLength,20) AS VARCHAR) + '-LEN(' + @ColumnName + '))' +     '+'  + @SQ +',' + @SQ + '+'

            FETCH NEXT FROM Cols INTO @ColumnName,@DataType    
        END
        CLOSE Cols
        DEALLOCATE Cols

        SET @SQL = LEFT(@SQL,LEN(@SQL)-LEN('+'  + @SQ +',' + @SQ +  '+'))
        SELECT @SQL = @SQL + '+' + @SQ + ')' + @SQ +  ' FROM ' +  @TableName
        select @SQL 
        EXEC sp_executeSQL @SQL

    END TRY
    BEGIN CATCH
        SELECT CAST(ERROR_LINE() AS VARCHAR) + '    ' + ERROR_MESSAGE() + '   AT ' + CAST(ERROR_LINE() AS varchar)
    END CATCH
END

GO

To execute this code you have to give the stored proc name followed by the table name.

Example:
EXEC InsertGenerator <table_name>

Hope this helps you guys in generating a better and readable code.

Audit the data changes using triggers

Hi Every one

Although SQL Server have introduced the functionality of audit on data itself, it is not available in all the versions except for the enterprise endition. As a alternative we can keep a trigger on each table for writing the before and after data. The below code consists of a table audit_change_log which will hold the data and the SP uspAudit will create the triggers on the tables.

if exists ( select 1 from sys.tables where name = 'Audit_Change_log')
    drop table Audit_Change_log
GO

create table Audit_Change_log
(
     ID             BIGINT          IDENTITY(1,1)
    ,tableName      Varchar(100)    NOT NULL
    ,previousData   VARCHAR(MAX)    NULL
    ,CurrentData    VARCHAR(MAX)    NULL
    ,UpdtTime       DATETIME        DEFAULT GETDATE()
    ,operation      VARCHAR(20)     NULL
)

GO

IF EXISTS ( SELECT 1 FROM sys.procedures where name = 'uspAudit')
    DROP procedure uspAudit
GO

CREATE procedure uspAudit
(
     @action         CHAR(1) = 'C'
)
AS
BEGIN
    DECLARE @SQL        NVARCHAR(4000)
    DECLARE @SQ         NVARCHAR(4)
    DECLARE @tblName    NVARCHAR(500)
    BEGIN TRY
        SET @SQ = ''''
        DECLARE abcd CURSOR
        FOR SELECT name FROM sys.tables where name <> 'Audit_Change_log'
        OPEN abcd
        FETCH abcd into @tblName
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF NOT EXISTS ( SELECT 1 FROM information_schema.columns where table_name = @tblName AND Data_type in ('text','ntext','image'))
            BEGIN
                SELECT @SQL = 'IF OBJECT_ID (' + @SQ + 'trg_' + @tblName + @SQ + ',' + @SQ + 'TR' + @SQ +') IS NOT NULL DROP TRIGGER ' + 'trg_'+@tblName + ';'
                --select @SQL
                EXEC(@SQL)
                SELECT @SQL = 'CREATE TRIGGER trg_'+@tblName+' ON ' + @tblName + ' AFTER INSERT, UPDATE, DELETE AS ' +
                                + ' INSERT INTO  Audit_Change_log (tableName,previousData,CurrentData) SELECT ' + @SQ +  @tblName + @SQ + 'AS tableName '
                                +', (select * from deleted for XML AUTO) AS previousData, (select * from inserted for XML AUTO) as CurrentData; '
                                + ' UPDATE  Audit_Change_log SET  operation = CASE  WHEN previousData IS NULL THEN ' + @SQ +  'Insert'  + @SQ
                                + ' WHEN CurrentData IS NULL THEN ' + @SQ + 'Delete' + @SQ
                                + ' ELSE ' + @SQ + 'Update' + @SQ + ' END WHERE ID = @@IDENTITY ;'
                --select @SQL
                EXEC (@SQL)
            END
            ELSE
            BEGIN
                SELECT @SQL = 'IF OBJECT_ID (' + @SQ + 'trg_' + @tblName + @SQ + ',' + @SQ + 'TR' + @SQ +') IS NOT NULL DROP TRIGGER ' + 'trg_'+@tblName + ';'
                EXEC(@SQL)
                SELECT @SQL = 'CREATE TRIGGER trg_'+@tblName+' ON ' + @tblName + ' AFTER INSERT, UPDATE, DELETE AS ' +
                                + ' INSERT INTO  Audit_Change_log (tableName,previousData,CurrentData) SELECT ' + @SQ +  @tblName + @SQ + 'AS tableName '
                                +', (select '  + (select stuff((select ','+column_name from information_schema.columns where data_type not in ('text','ntext','image') and table_name = @tblName for xml path('')  ),1,1,''))
                                + ' from deleted for XML AUTO) AS previousData'
                                + ', (select '+  (select stuff((select ','+column_name from information_schema.columns where data_type not in ('text','ntext','image') and table_name = @tblName for xml path('')  ),1,1,''))
                                + ' from inserted for XML AUTO) as CurrentData;'
                                + ' UPDATE  Audit_Change_log SET  operation = CASE  WHEN previousData IS NULL THEN ' + @SQ +  'Insert'  + @SQ
                                + ' WHEN CurrentData IS NULL THEN ' + @SQ + 'Delete' + @SQ
                                + ' ELSE ' + @SQ + 'Update' + @SQ + ' END WHERE ID = @@IDENTITY ;'
                EXEC (@SQL)

            END
            FETCH abcd into @tblName
        END
        CLOSE abcd
        DEALLOCATE abcd
    END TRY
    BEGIN CATCH
        CLOSE abcd
        DEALLOCATE abcd
        SELECT CAST(ERROR_LINE() AS VARCHAR) +':' +      ERROR_MESSAGE()
    END CATCH
END
GO

EXEC uspAudit

Loop in SQL Server without For,while, goto

Hi Every one

Usually we all use a While, for or some old timers use GOTO to loop a batch. but there is some other way to do the same.

create table emp
(
id  INT IDENTITY(1,1) PRIMARY KEY
,name varchar(200) NULL
)
GO

insert into emp (name) values ('suri')
GO 100

 

The above insert statement will insert 100 records into the table emp even though we have not mentioned any loop, The trick is to mention the number after the GO statement. It will loop through the complete batch (last GO statement to the current statement or from the start of the script).

Drawbacks: This can cause a serious trouble if we dont use the batch delimiter before the statement we want to execute, for example if we are missing the first GO statement we will get an error saying the table emp already exists, as it will try to execute the create table statement as well.

Clustered Index should not be used for Sorting

Howdy Everyone…

It is a common misconception that writing a clustered index on a table will get the table sorted on the index key columns. Although this is true for some extent, there are some exceptions to it, more over it is not documented and not supported by Microsoft, so they can remove this functionality without even letting you know about it (and not support it in the future versions of SQL server).

The below SQL script is a perfect example where we can see on why we should use order by clause every time.

use tempdb

create table emp

(

id  INT IDENTITY(1,1) PRIMARY KEY

,name varchar(200) NULL

)

GO

 

select object_name(object_id) table_name,name,type_desc  From sys.indexes where  object_id = object_id('emp')

table_name	name	type_desc
emp	PK__emp__5FFB4DF9	CLUSTERED

insert into emp (name) values ('raj')

insert into emp (name) values ('suri')

insert into emp (name) values ('siva')

insert into emp (name) values ('murali')

 

select * from emp

id	name
1	raj
2	suri
3	siva
4	murali

create unique index uix_empName ON emp (name)

select object_name(object_id) table_name,name,type_desc From sys.indexes where  object_id = object_id('emp')

table_name	name	type_desc
emp	PK__emp__5FFB4DF9	CLUSTERED
emp	uix_empName	NONCLUSTERED

select * from emp

id	name
4	murali
1	raj
3	siva
2	suri

As you can see the second select statement resulted in different order of rows even though there is a clustered index on the table.

Understanding Recursive queries

Howdy Everyone…

With the introduction of common table expressions (aka CTE), people started using it for building recursive and hierarchical solutions… At a first glance at the solution I am not able to understand what is happening there…. So here is the comeplete explanation of how it is happening for an application programmer than a database expert.

Let the scenario be printing all the numbers from 100 to 1, How we might have accomplished this task without the use of CTE is as below

declare @n int
set @n = 100
while(@n > 0)
BEGIN
	SELECT @n
	SET @n = @n-1
END

Simple one, I think there is no need of further explanation from my end to explain the above stuff….
Now coming to the recursive queries…. how we will do the same…

declare @n table ( a int)
insert into @n values (100)


;with CTE
AS
(
	SELECT a from @n        --- Anchor Query
	UNION ALL
	SELECT (a-1) from cte   --- Recursive Query
	where (a-1) > 0
)
select * from cte

What is happening over there… too much code…. You might be thinking using the earlier method is easier… but believe me using the recursive queries might help you to avoid a large of number cursors in your implementation.
Now going into the explanation bit,
We have defined a table variable named @n which will hold the values of 100 in a column a.
In a recursive CTE, you should always have a anchor query followed by union all or else you will get the error from SQL (Recursive common table expression ‘CTE’ does not contain a top-level UNION ALL operator.)
Next we have to build the recursion by querying on the common table expression itself (in this case CTE), but you have to use limit where the recursion should end, in this case the where clause of the second select statement will do that (where (a-1) > 0)
Incase if you didn’t mention the where clause you will get an error from SQL Server with the max recurssion exceeded. The reason is SQL Server dont know when to stop the recursion or where to stop the recursion.
So, by default it will loop for 100 times and if it is more than that we get and error like (The statement terminated. The maximum recursion 100 has been exhausted before statement completion.)
We can restrict the same by using the query hint OPTION (MAXRECURSION <number>), which will limit the number of loops.

Get the object name from the fully qualified name

Howdy Everyone.

I have seen an interesting built in function of SQL Server today, It is PARSENAME.

The PARSENAME function will return the object/schema/database/server name from the given string.

For more information you can check MSDN article below

http://msdn.microsoft.com/en-us/library/ms188006.aspx

TSQL – How to find last sunday of the month

Howdy Every One.

How to get last day of any month is explained in my previous post..

Now, How to get the last sunday of any month… A little tricky isn’t it…. one of my friend went to write the loop and then tried to compare the values if it is a sunday or not from the last day of the month (by decreasing each day until he reaches a sunday)…. The method works but there is another way to do this… much simpler way….

To accomplish we need to know what day of week it is, we can get that by using dw parameter in the datepart.

Now if we know what day of the week the current input is… if the @@datefirst value is set to its default (7) then sunday will be 1 in the day of weeks.

If you subtract the number of days from seven and add it will give you the number of days between a sunday and the current date…….

Check the below code

declare @last_day_of_the_month	datetime
declare @last_sunday_of_the_month datetime
declare @input_date	datetime
set DATEFIRST 7 -- to be on the safe side
select @input_date = getdate()
select @last_day_of_the_month = dateadd(dd,datepart(dd,@input_date)*-1,dateadd(mm,1,@input_date))
select @last_sunday_of_the_month = dateadd(dd,(datepart(dw,@last_day_of_the_month)*-1)+1,@last_day_of_the_month)
select @last_sunday_of_the_month

How to find the last day of the month

Howdy Everyone.

Most of the times I see, people getting up with new ideas to get the last day of the month…

But the simplest method we can use in SQL Server is by adding 1 to month and subtracting the number of days in the date, confusing….

Well let me explain with an example

Let us take the date 20-AUG-2011. Now if we add 1 to the month by using DATEADD function.. the result will be 20-SEP-2011. Now the day part of the resultant date is 20 and if we subtract the 20 from the resultant by using dateadd the result will be 30-AUG-2011.

The code is given below

DECLARE @input_date datetime
set @input_date = getdate() -- for testing purposes
select dateadd(dd,datepart(dd,@input_date)*-1,dateadd(mm,1,@input_date))