This works for me to find queries on any database in the instance.  I&#39;m sysadmin on the instance (check your privileges):


    SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    WHERE dest.dbid = DB_ID(&#39;msdb&#39;)
    ORDER BY deqs.last_execution_time DESC

This is the same answer that Aaron Bertrand provided but it wasn&#39;t placed in an answer.

Following works perfectly for me : hope this helps

    SELECT
        deqs.last_execution_time AS [Time], 
        dest.TEXT AS [Query]
     FROM 
        sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY 
        deqs.last_execution_time DESC

I currently have an AngularJS application with routing built in.
It works and everything is ok.

My app.js file looks like this:

    angular.module(&#39;myapp&#39;, [&#39;myapp.filters&#39;, &#39;myapp.services&#39;, &#39;myapp.directives&#39;]).
      config([&#39;$routeProvider&#39;, function ($routeProvider) {
          $routeProvider.when(&#39;/&#39;, { templateUrl: &#39;/pages/home.html&#39;, controller: HomeController });
          $routeProvider.when(&#39;/about&#39;, { templateUrl: &#39;/pages/about.html&#39;, controller: AboutController });
          $routeProvider.when(&#39;/privacy&#39;, { templateUrl: &#39;/pages/privacy.html&#39;, controller: AboutController });
          $routeProvider.when(&#39;/terms&#39;, { templateUrl: &#39;/pages/terms.html&#39;, controller: AboutController });
          $routeProvider.otherwise({ redirectTo: &#39;/&#39; });
      }]);

My app has a CMS built in where you can copy and add new html files within the **/pages** directory.

I would like to still go through the routing provider though even for the new dynamically added files.

In an ideal world the routing pattern would be:

$routeProvider.when(&#39;/**pagename**&#39;, { templateUrl: &#39;/pages/**pagename**.html&#39;, controller: CMSController });

So if my new page name was &quot;contact.html&quot; I would like angular to pick up &quot;/contact&quot; and redirect to &quot;/pages/contact.html&quot;.

Is this even possible?! and if so how?!

**Update**

I now have this in my routing config:

    $routeProvider.when(&#39;/page/:name&#39;, { templateUrl: &#39;/pages/home.html&#39;, controller: CMSController })

and in my CMSController:

    function CMSController($scope, $route, $routeParams) {
        $route.current.templateUrl = &#39;/pages/&#39; + $routeParams.name + &quot;.html&quot;;
        alert($route.current.templateUrl);
    }
    CMSController.$inject = [&#39;$scope&#39;, &#39;$route&#39;, &#39;$routeParams&#39;];

This sets the current templateUrl to the right value.

*However* I would now like to change the *ng-view* with the new templateUrl value. How is this accomplished?

AngularJS dynamic routing

Is it possible to get the object property name as a string

    person = {};
    person.first_name = &#39;Jack&#39;;
    person.last_name = &#39;Trades&#39;;
    person.address = {};
    person.address.street = &#39;Factory 1&#39;;
    person.address.country = &#39;USA&#39;;

I&#39;d like to use it like this:

    var pn = propName( person.address.country ); // should return &#39;country&#39; or &#39;person.address.country&#39;
    var pn = propName( person.first_name );      // should return &#39;first_name&#39; or &#39;person.first_name&#39;


**NOTE**: this code is exactly what I&#39;m looking for. I understand it sounds even stupid, but it&#39;s not.

This is what I want to do with it.

HTML
    &lt;input type=&quot;text&quot; value=&quot;Jack&quot; id=&quot;id_first_name &quot;/&gt;

    person = {};
    person.id_first_name = &#39;Jack&#39;;
    person.id_last_name = &#39;Trades&#39;;
    person.address = {};
    person.address.id_address = &#39;Factory 1&#39;;
    person.address.id_country = &#39;USA&#39;;


    extPort.postMessage
    (
      {
        message : MSG_ACTION,
        propName( person.first_name ): person.first_name
      }
    };

----------------------**ANSWER**-----------------------

Got it thanks to ibu. He pointed the right way and I used a recursive function

    var res = &#39;&#39;;
    
    function propName(prop, value) {
    	for (var i in prop) {
    		if (typeof prop[i] == &#39;object&#39;) {
    			if (propName(prop[i], value)) {
    				return res;
    			}
    		} else {
    			if (prop[i] == value) {
    				res = i;
    				return res;
    			}
    		}
    	}
    	return undefined;
    }
    
    var pn = propName(person, person.first_name); // returns &#39;first_name&#39;
    var pn = propName(person, person.address.country); // returns &#39;country&#39;

DEMO: http://jsbin.com/iyabal/1/edit

Get object property name as a string

I know how to get the last executed queries using the following SQL in SSMS -

    SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC

But I want to find them for a specific database. I don&#39;t want to use SQL Profiler, if I don&#39;t have to. Plus I don&#39;t think SQL Profiler will allow me to view queries that were already run without profiling turned on. I need to do this from SSMS.

Last executed queries for a specific database

I know how to get the last executed queries using the following SQL in SSMS -
<pre><code class="hljs language-vbnet">SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
</code></pre>
But I want to find them for a specific database. I don't want to use SQL Profiler, if I don't have to. Plus I don't think SQL Profiler will allow me to view queries that were already run without profiling turned on. I need to do this from SSMS.

I&#39;m finding a way to aggregate strings from different rows into a single row. I&#39;m looking to do this in many different places, so having a function to facilitate this would be nice. I&#39;ve tried solutions using `COALESCE` and `FOR XML`, but they just don&#39;t cut it for me.

String aggregation would do something like this:

    id | Name                    Result: id | Names
    -- - ----                            -- - -----
    1  | Matt                            1  | Matt, Rocks
    1  | Rocks                           2  | Stylus
    2  | Stylus

I&#39;ve taken a look at [CLR-defined aggregate functions][1] as a replacement for `COALESCE` and `FOR XML`, but apparently **SQL Azure** *does not* support CLR-defined stuff, which is a pain for me because I know being able to use it would solve a whole lot of problems for me.

Is there any possible workaround, or similarly optimal method (which might not be as optimal as CLR, but *hey* I&#39;ll take what I can get) that I can use to aggregate my stuff?

[1]: http://picnicerror.net/development/sql-server/concatenate-multiple-row-values-sql-server-with-clr-2012-01-10/

Optimal way to concatenate/aggregate strings

I have an existing table with data. I have just added a new column but I cannot find how to add a unique constraint on that column. Could someone please advise? Right-clicking and selecting &quot;check constraints&quot; wasn&#39;t helpful.

Add unique constraint in SQL Server 2008 GUI?

I want to update my column CODE_DEST with an incremental number.  I have:

    CODE_DEST   RS_NOM
    null        qsdf
    null        sdfqsdfqsdf
    null        qsdfqsdf

I would like to update it to be:

    CODE_DEST   RS_NOM
    1           qsdf
    2           sdfqsdfqsdf
    3           qsdfqsdf

I have tried this code:

    UPDATE DESTINATAIRE_TEMP
    SET CODE_DEST = TheId 
    FROM (SELECT  Row_Number()   OVER (ORDER BY [RS_NOM]) AS TheId FROM DESTINATAIRE_TEMP)

This does not work because of the `)`

I have also tried:

    WITH DESTINATAIRE_TEMP AS
      (
        SELECT 
        ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
        FROM DESTINATAIRE_TEMP
      )
    UPDATE DESTINATAIRE_TEMP SET CODE_DEST=RN

But this also does not work because of union.

How can I update a column using the `ROW_NUMBER()` function in SQL Server 2008 R2?

SQL Update with row_number()

I am creating a project in which I need to run 2-3 SQL commands in a single SQL connection.
Here is the code I have written: 

    SqlConnection con = new SqlConnection(@&quot;Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\project.mdf;Integrated Security=True&quot;);
    con.Open();
    SqlCommand cmd = new SqlCommand(&quot;select *  from &quot; + mytags.Text + &quot; &quot;, con);
    SqlDataReader rd = cmd.ExecuteReader();
    if (rd.Read())
    {
        con.Close();
        con.Open();
        SqlCommand cmd1 = new SqlCommand(&quot;insert into &quot; + mytags.Text + &quot; values (&#39;fname.lname@gmail.com&#39;,&#39;&quot; + TextBox3.Text + &quot;&#39;,&#39;&quot; + TextBox4.Text + &quot;&#39;,&#39;&quot; + TextBox5.Text + &quot;&#39;,&#39;&quot;+mytags.Text+&quot;&#39;)&quot;, con);
        cmd1.ExecuteNonQuery();
        label.Visible = true;
        label.Text = &quot;Date read and inserted&quot;;
    }
    else
    {
        con.Close();
        con.Open();
        SqlCommand cmd2 = new SqlCommand(&quot;create table &quot; + mytags.Text + &quot; ( session VARCHAR(MAX) , Price int , Description VARCHAR(MAX), Date VARCHAR(20),tag VARCHAR(10))&quot;, con);
        cmd2.ExecuteNonQuery();
        con.Close();
        con.Open();
        SqlCommand cmd3 = new SqlCommand(&quot;insert into &quot; + mytags.Text + &quot; values (&#39;&quot; + Session + &quot;&#39;,&#39;&quot; + TextBox3.Text + &quot;&#39;,&#39;&quot; + TextBox4.Text + &quot;&#39;,&#39;&quot; + TextBox5.Text + &quot;&#39;,&#39;&quot; + mytags.Text + &quot;&#39;)&quot;, con);
        cmd3.ExecuteNonQuery();
        label.Visible = true;
        label.Text = &quot;tabel created&quot;;
        con.Close();
    }
            
I have tried to remove the error and I got that the connection is not going to else condition. Please review the code and suggest if there is any mistake or any other solution for this.

How to run multiple SQL commands in a single SQL connection?

I have to insert about 2 million rows from a text file.

And with inserting I have to create some master tables. 

What is the best and fast way to insert such a large set of data into SQL Server?



Insert 2 million rows into SQL Server quickly

In one of my tables `Fee` in column &quot;ReceiptNo&quot; in SQL Server 2012 database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things.

1. if it is 1205446 it is jumps to 1206306, if it is 1206321, it jumps to 1207306 and if it is 1207314, it jumps to 1208306. What I want to make you note is that the last three digits remain constant i.e 306 whenever the jumping occurs as shown in the following picture.

2. this problem occurs when I restart my computer

![enter image description here][1]

  [1]: http://i.stack.imgur.com/tmjTp.jpg

Identity increment is jumping in SQL Server database

I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly.

Say I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:

1,
2,
3,
4,
5,
1004,
1005

This is happening on a random number of tables at very random times, can not replicate it to find any trends.

How is this happening?
Is there a way to make it stop?

SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry

###RFC 4180:

**[RFC 4180][1]** defines *`Common Format and MIME Type for Comma-Separated Values (CSV) Files`*. One of the requirements of the *`RFC 4180`* is stated as below. This is the point *`#7`* in the RFC link.

    If double-quotes are used to enclose fields, then a double-quote
    appearing inside a field must be escaped by preceding it with
    another double quote.  For example:
    
    &quot;aaa&quot;,&quot;b&quot;&quot;bb&quot;,&quot;ccc&quot;

###SQL Server 2000:

**`DTS Export/Import Wizard`** in *`SQL Server 2000`* seems to conform to the above mentioned standards even though the RFC 4180 itself seem to have been published only on *October 2005*. I am using the below stated *SQL Server 2000* version.

    Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
    May  3 2005 23:18:38 
    Copyright (c) 1988-2003 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

###SQL Server 2012:

**`SQL Server Import and Export Wizard`** in *`SQL Server 2012`* does not export the data from table to CSV file according to the standard defined in RFC 4180. I am using the below stated *SQL Server 2012* version.

    Microsoft SQL Server 2012 - 11.0.2316.0 (X64) 
    Apr  6 2012 03:20:55 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)

###Issue Simulation:

Here is a sample that I ran in both *SQL Server 2000* and *SQL Server 2012*. I ran the below query to create a table and insert few records. The `ItemDesc` column has data with double-quotes in it. My intention is to export the data from both these SQL Server versions using their in-built export data wizard and compare the generated CSV files.


    CREATE TABLE dbo.ItemInformation(
    	ItemId nvarchar(20) NOT NULL,
    	ItemDesc nvarchar(100) NOT NULL
    ) 
    GO
    
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;100338754&#39;, &#39;Crown Bolt 3/8&quot;-16 x 1&quot; Stainless-Steel Hex Bolt&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;202255836&#39;, &#39;Simpson Strong-Tie 5/8&quot; SSTB Anchot Bolt&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;100171631&#39;, &#39;Grip-Rite #11 x 1-1/2&quot; Electro-Galvanized Steel Roofing Nails&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;202210289&#39;, &#39;Crown Bolt 1/2&quot; x 3&quot; &quot;Zinc-Plated&quot; Universal Clevis Pin&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;100136988&#39;, &#39;Tapcon 3/16&quot; x 1-3/4&quot; Climaseal Steel &quot;Flat-Head&quot; Phillips Concrete Anchors (75-Pack)&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;203722101&#39;, &#39;KwikTap 3/16&quot; x 2-1/4&quot; &quot;Flat-Head&quot; Concrete Screws (100-Pack)&#39;);
    GO

On the *`DTS Export/Import Wizard`* in *`SQL Server 2000`*, I used the below settings to export the data to CSV file. I saved the file under the name `SQLServer2000_ItemInformation.csv`.

![DTS Export/Import Wizard][2]

On the *`SQL Server Import and Export Wizard`* in *`SQL Server 2012`*, I used the below settings to export the data to CSV file. I saved the file under the name `SQLServer2012_ItemInformation.csv`.

![SQL Server Import and Export Wizard - Choose a destination][3]

![SQL Server Import and Export Wizard - Configure Flat File Destination][4]

Here is the comparison between the two files using Beyond Compare. The left side contains the file generated by `SQL Server 2000` and the right side contains the file generated by `SQL Server 2012`. You can notice that the left side file from `SQL Server 2000` contains additional double-quotes to compensate the embedded quotes in the data column. This conforms to the standard specified in `RFC 4180` but it is clearly missing from the file generated by `SQL Server 2012`

![File Comparison][5]

###Searches on the web:

I searched for this bug on the web and found the following links. Following are the bug reports on Microsoft Connect. All these issues seem to be related to importing a file but nothing about exporting data. All these bugs have been closed as `Fixed`.

 - [*SSIS flat file parser does not read Column delimiters embedded in text data*][6]
 - [*Flat File Connection Manager not handling Text Delimiters in CSV Files*][7] 
 - [*Embedded quotes in Flat File Import fails*][8]
 - [*BUG: Flat File Connection Manager: multiple-character text qualifier does not load all data*][9]

Below post on MSDN blog states that changes have been made in SQL Server 2012 with respect to `Flat file source supports embedded qualifiers and a variable number of columns per row`

 - [*SSIS - What’s New in SQL Server Denali*][10]

Another post on MSDN blog states the same under the section `Embedded Qualifiers`.

 - [*Flat File Source Changes in Denali*][11]

###Workaround that I know of:

I know a workaround to fix the issue by writing a query that would replace all double-quotes (**`&quot;`**) in my column data with two double-quotes (**`&quot;&quot;`**) so that the exported file will end up with correct embedded qualifier data. This would avoid pulling the data directly from the table as it is.

###My questions:

 - I don&#39;t know if this issue has been truly fixed in `SQL Server 2012`. Has this issue been fixed only for *`importing`* files that have embedded text qualifiers and **`not`** for *`exporting`* data to CSV?

 - Probably, I am clearly doing something wrong and missing the obvious. Could someone please explain to me what I am doing wrong here?

###Microsoft Connect:

I have submitted a bug report on Microsoft Connect website to get their feedback. Here is the link to the bug report. If you agree that this is a bug, please visit the below link to vote up on `Microsoft Connect` website.

[*Embedded text qualifier during export to CSV does not conform to RFC 4180*][12]


  [1]: https://www.rfc-editor.org/rfc/rfc4180
  [2]: http://i.stack.imgur.com/fdFSY.png
  [3]: http://i.stack.imgur.com/toryR.png
  [4]: http://i.stack.imgur.com/MvbUx.png
  [5]: http://i.stack.imgur.com/ofh2w.png
  [6]: https://connect.microsoft.com/SQLServer/feedback/details/282396/ssis-flat-file-parser-does-not-read-column-delimiters-embedded-in-text-data
  [7]: http://connect.microsoft.com/SQLServer/feedback/details/560592/flat-file-connection-manager-not-handling-text-delimiters-in-csv-files
  [8]: https://connect.microsoft.com/SQLServer/feedback/details/267529/embedded-quotes-in-flat-file-import-fails
  [9]: https://connect.microsoft.com/SQLServer/feedback/details/540056/bug-flat-file-connection-manager-multiple-character-text-qualifier-does-not-load-all-data
  [10]: http://blogs.msdn.com/b/mattm/archive/2011/07/12/ssis-what-s-new-in-sql-server-denali.aspx?PageIndex=2#comments
  [11]: http://blogs.msdn.com/b/mattm/archive/2011/07/17/flat-file-source-changes-in-denali.aspx
  [12]: https://connect.microsoft.com/SQLServer/feedback/details/778980/embedded-text-qualifier-during-export-to-csv-does-not-conform-to-rfc-4180#details

How to fix the embedded text qualifier issue while exporting data to CSV flat file?

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Recover unsaved SQL query scripts

I have backed up a database I had created on an other machine running SQL server 2012 express edition and I wanted to restore it on my machine, which is running the same. I have ticked the checkbox overwriting existing one, and got this error:


    Backup mediaset is not complete. Files: D:\question.bak. Family count:2. Missing family sequence number:1	




Content Type	Original Author	Original Content on Stackoverflow
Question	Mukus	View Question on Stackoverflow
Solution 1 - Sql Server	tommy_o	View Answer on Stackoverflow
Solution 2 - Sql Server	Jack Gajanan	View Answer on Stackoverflow

Last executed queries for a specific database

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

Solution 2 - Sql Server

Get object property name as a string

AngularJS dynamic routing

Attributions