Showing posts with the label SQL Server

Migrating database from ASP.NET Identity to ASP.NET Core Identity

As you know ASP.NET Core Identity (table structure) is different from what we had earlier in ASP.NET Identity. Actually the identity system which we have today with .NET Core is very mature and continuously evolved be it ASP.NET Membership, ASP.NET Identity 1, ASP.NET Identity 2 and now ASP.NET Core Identity. Recently I had to migrate few application to ASP.NET Core and similar its identity database. Because the table schema is changed, i had to re-think and create migration script which I would like to share with you today. It is very simple and easy, just three step and I had everything ready: STEP 1 : Change name of existing tables STEP 2 : Create ASP.NET Core Identity tables STEP 3 : Migrate data from old tables (ASP.NET Identity) to new tables (ASP.NET Core Identity) Script: You should also watch my video, where I demoed migration.

Customizing "Edit Top 200 Rows" in SQL Server Management Studio

It is a very common requirement that, sometimes we want to change the edit top 200 rows query. This is very much possible and easy in SQL Server Management Studio. Right click on table and select "Edit Top 200 Rows" Press "Ctrl + 3" on rows in edit mode window Now you can see the query which returned 200 rows allowing you to edit On this query window, right click to execute the query on window itself And voila, you have records based on your query to edit those on window :) Hope this helps.

Moving SQL Azure to local SQL or Azure SQL to VM SQL

There are many requirements like moving database between: 1. SQL Azure to local development SQL Server 2. SQL Azure to Virtual Machine SQL Server 3. Development SQL Server to Production SQL Server 4. Production SQL Server to Development SQL Server

Rename database data files and logical files in SQL Server

When I rename database using SQL Server Management Studio I always change its data files (mdf, ldf) name as well as logical file name of the resource. And this is very common need for SQL Server guy.

SQLServerGeeks Annual Summit 2015 - Asia's first and the only SQL Server Conference

If you are in Bangalore or around or SQL Server enthusiast, here is something for you. SQLServerGeeks guys announced Annual Summit 2015 on August 27, 28, 29, 2015 in Bangalore at NIMHANS Convention Center.

nopCommerce 3.30 - an open source ecommerce

nopCommerce is an open source ecommerce software that contains both a catalog frontend and an administration tool backend. nopCommerce is a fully customizable shopping cart. It's stable and highly usable. From downloads to documentation, offers a comprehensive base of information, resources, and support to the nopCommerce community.

Single File Upload to Multiple File Upload in MVC

In this blog post you will learn how to take advantage of HTML5 in MVC to turn single file upload into multiple file upload functionality. Today almost every browser extended the support to HTML5 and in case any browser does not, it will still work as it was working before and upload single file at a time.

Using Microsoft SQL Server Management Studio to connect SQL Azure databases

In this article you will learn how to connect SQL Azure database from SQL Server Management Studio.

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Today I added a new user in my existing database for some web application experiments and when I tried to delete the user I received following error message.

Creating ASP.NET WebApplication/ASP.NET MVC Application Membership Database on SQL Server rather than LocalDb

I was asked this question from one of my blog reader: How can a DB be created in SQL Server rather than locally (LocalDb) which is default?

Unable to retrieve metadata for 'MusicStore.Models.Album'. Using the same DbCompiledModel to create context against different types of database servers is not supported. Instead, create a separate DbCompiledModel for each type of server being used.

You will get this error in the step by step tutorial (on page here ) provided by Microsoft that starts from here .

Entity Framework Console Applications with SQL Server Compact

Microsoft SQL Server Compact Edition is an embedded database system that allows us to integrate it in our Web, Desktop and Mobile applications.

Mapping Table-Valued Functions (TVFs) using Entity Framework Database First

Entity Framework 5 brings number of improvements and Mapping Table-Valued Functions (TVFs) Support is one of them.

ASP.NET Session States in SQL Server Mode (Session State Story)

A session is defined as the period of time that a unique user interacts with a Web application. Active Server Pages (ASP) developers who wish to retain data for unique user sessions can use an intrinsic feature known as session state. Programmatically, session state is nothing more than memory in the shape of a dictionary or hash table, e.g. key-value pairs, which can be set and read for the duration of a user's session.

Using 'Like' operator in parameterized queries

The main advantage of parameterized query is to protect the database from SQL Injection. Today I used this concept in my one of the project. Do you know using ‘Like’ operator in parameterized query is bit different?

System.Data.SqlClient.SqlException: Cannot open database "Northwind" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\DefaultAppPool'.

System.Data.SqlClient.SqlException: Cannot open database "Northwind" requested by the login.  The login failed.  Login failed for user 'IIS APPPOOL\DefaultAppPool'. In this quick post you will learn how to fix this issue. Today in the morning when I was executing one of my web applications I fall in titled error and the same I’m sharing, that how I fixed this.

Abstract Datatype

Abstract datatype are datatypes that consists of one or more subtypes. This datatype is used to describe the data accurately. Abstract datatypes can be nested and can contain references to other abstract datatype. This datatype can be reused. Abstract datatype is object oriented thing. Creating abstract datatype CREATE TYPE STUDENT_NAME_TY AS OBJECT (FIRST_NAME VARCHAR2(15), LAST_NAME VARCHAR2(15)); In above example AS OBJECT identify STUDENT_NAME_TY as an object implementation.  Using abstract datatype in table CREATE TABLE STUDENT (STUDENT_ID NUMBER(4) PRIMARY KEY, NAME STUDENT_NAME_TY); Viewing details of table DESC STUDENT; or we can use the following SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME='STUDENT'; or we can use the following SELECT ATTR_NAME, LENGTH, ATTR_TYPE_NAME FROM USER_TYPE_ATTRS WHERE TYPE_NAME='STUDENT_NAME_TY'; Inserting data in table INSERT INTO STUDENT VALUES

SQL Server 2012 is Available for Download

As the foundation of the cloud-ready information platform, SQL Server 2012 will help organizations unlock breakthrough insights across the organization as well quickly build solutions and extend data across on-premises and public cloud backed by mission critical confidence. SQL Server 2012 capabilities and highlights are as follows:- Greater availability Deliver the required 9s and data protection with AlwaysOn, delivering added functionality that allows customers to experience even more of the flexibility and value. Breakthrough insight Unlock new insights with rapid data exploration across the organization with Power View, available to customers for the first time. Credible, consistent data Provide a consistent view across heterogeneous data sources with the BI Semantic Model (BISM) a single model for Business Intelligence applications. Make data quality apart of everyday life with the Master Data Services add-in for Excel and new Data Quality Services

Introducing Microsoft SQL Server 2012 (Free ebook)

If you are a SQL Server guy and want to explore Microsoft SQL Server 2012 then a good new waiting for you. Microsoft has just finished and provided free ebook: Introducing Microsoft SQL Server 2012. Get it Now Introducing Microsoft SQL Server 2012 includes 10 chapters:- PART I   DATABASE ADMINISTRATION (by Ross Mistry) 1.   SQL Server 2012 Editions and Engine Enhancements 2.   High-Availability and Disaster-Recovery Enhancements 3.   Performance and Scalability 4.   Security Enhancements 5.   Programmability and Beyond-Relational Enhancements PART II   BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner) 6.   Integration Services 7.   Data Quality Services 8.   Master Data Services 9.   Analysis Services and PowerPivot 10.   Reporting Services Get it Now

Time Format in SQL

Today I received a question from a guy on SQL title, I'm not a SQL guy, but when I dive to solve this, done. Look at the question:- { SELECT LEFT(CONVERT(VARCHAR, online_time,109),4)+' '+ right(convert(varchar, online_time,109),2) as online_time from time above is my query i want output as 04:30 am  4:30 am will do(if initial 0 is removed no prob) 09:45 pm 11:15 am my prob is that above query is giving right output for time before 10 am/pm but after 9:59 am/pm time is wrong i.e, it displays 11:3 pm instead of 11:30 pm and give right for 4:40 pm how i can do that } Now, look at my solution:- SELECT SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6) GO --OUTPUT = 11:30 and if you want to add AM or PM then SELECT SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6) + ' ' + SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 25, 2) COLNAME GO --OUTPUT = 11:30 AM Please Note: At the place of ' '

Popular posts from this blog

Migrating database from ASP.NET Identity to ASP.NET Core Identity

Customize User's Profile in ASP.NET Identity System

Lambda two tables and three tables inner join code samples