.Net: July 2013

Tuesday 9 July 2013

INSERT into a table records extracted from another table

How to INSERT into a table records extracted from another table?

If you want to insert values from one table into another you can use this sql query.

insert into TBL_TARGET([USERNAME],[PASSWORD],[ACTION])

select [USERNAME] ,[PASSWORD], 'I'

from [TBL_SOURCE]

delete procedures, user functions or views from a database in SQL

How to delete procedures, user functions or views from a database in SQL?

In order to delete all functions, procedures or views from a database you can help yourself by using this query:

DECLARE @procedureName varchar(500)

DECLARE cur CURSOR

      FOR SELECT [name] FROM sys.procedures WHERE type in ('p', 'fn') and is_ms_shipped=0 and name not like 'sp[_]%diagram%'

      OPEN cur

      FETCH NEXT FROM cur INTO @procedureName

      WHILE @@fetch_status = 0

      BEGIN

            EXEC('DROP PROCEDURE ' + @procedureName)

            --select @procedureName

            FETCH NEXT FROM cur INTO @procedureName

      END

      CLOSE cur

      DEALLOCATE cur

get list with procedures, functions or views in Sql

How to get list with procedures, functions or views in Sql?

In order to obtain a list with procedures(P), functions(FN) or views(V) in Sql you can run this query:

SELECT [name], [type], create_date, modify_date

FROM sys.objects

WHERE [type] in ('P', 'FN', 'V') 

order by [Type ] asc

shut down an application in Asp.Net

How to shut down an application in Asp.Net?

Often when you are updating your website you would not be able to overwrite files, operate in database. That is why you can do the following. Create a file called app_offline.html. In this file you can insert content to show as long time as your application is down for maintenance. You can look here form my application offline page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Application Offline</title>
<style>
p
{
background-color: #ffffcc;
padding-top: 10px;
padding-bottom: 10px;
padding-left: 10px;
padding-right: 10px;
border-style: solid;
border-color: Black;
border-width: 1px;
}
</style>
</head>
<body>
<h1 class="error">
Website is updating
</h1>
<p style="font-size: 15px;">
This site is currently updating. Please wait for a while.
<br />
Thanks.
</p>
</body>
</html>

The way app_offline.htm works is that you place this file in the root of your application. When ASP.NET sees it, it will shut-down the app-domain for the application (and not restart it for requests) and instead send back the contents of the app_offline.htm file in response to all new dynamic requests for the application. When you are done updating the site, just delete the file and it will come back online or rename to _app_offline(for example) and you will have the file there for use next time you need it.

Also have in mind that by adding the app_offline.htm the application sends the Application_End and after this function return the rest of the threads of the program are killed. The maximum time of wait for theApplication_End to return is set on the pool settings.

If you stop the full pool then all the sites that under this pool follow the same procedure. If you only open the app_offline.htm then only this site is affected.

To avoid your threads to kill by this shutdown, set a wait state on the Application_End

void Application_End(object sender, EventArgs e) 

{

    // This is a custom function that you must make and

    //   check your threads in the program

    MyTheadClass.WaitForAllMyThreadsToExist();

    // after this function exit the rest of the threads are killed.

}

If you use the app_offline.htm feature, you should make sure you have at least 512 bytes of content within it to make sure that your HTML (instead of IE's friendly status message) shows up to your users. If you don't want to have a lot of text show-up on the page, one trick you can use is to just add an html client-side comment with some extra content to push it over 512 bytes. You can insert html comments just to make your file bigger.

error 15128 in Microsoft Sql Server

How to solve error 15128 in Microsoft Sql Server ?

If you ever meet this nice Sql Error, 15128 in Microsoft Sql Error you must do this next steps. At least they worked for me :)
But first I want to explain which is the scenario. You are logged with Windows Authentication and you created a new sql login. Now you want to disable Enforce Password Policy. When you want to do that it Sql tells you that you can not save the user, a new password must be set. Strange...so:

double click on new login created, leave the check boxes checked(enforce password policy and enforce password expiration) and change user's password temporally to what ever you want, click OK
then reopen and uncheck enforce password policy, enforce password expiration; Press OK
finally you reopen the user and specify the password you want
Test it ;)

Hope this will help you.

get total number of tables in a database in SQL

How to get total number of tables in a database in SQL?

It might happen that you will have to start working on a big database, someday. So, just out of curiosity if you ever want to find total number of tables that are in your table you can use this simple query:

SELECT COUNT(*) as TotalTables FROM sys.tables WHERE type in ('u')