Ask your technical questions on forums or here :
ASP.NET or MVC | C# | Windows Phone
Microsoft Technology Journals by Abhimanyu K Vatsa
HOME ABOUT RAZOR BOOK SPEAKING MVC ASP.NET JQUERY VIDEOS EBOOK ARCHIVE
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

10 Mar 2016

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.

1 Mar 2015

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.

8 Oct 2014

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, nopCommerce.com offers a comprehensive base of information, resources, and support to the nopCommerce community.

14 Mar 2014

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.

16 Apr 2013

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.

11 Dec 2012

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.

15 Oct 2012

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?

8 Oct 2012

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.

5 Oct 2012

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.

10 Sep 2012

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.

23 Jul 2012

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?

16 Jun 2012

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.

28 May 2012

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 
(1,STUDENT_NAME_TY('Abhimanyu','Kumar'));

or we can use the following

INSERT INTO STUDENT VALUES
(&STUDENT_ID, STUDENT_NAME_TY('&FIRST_NAME, &LAST_NAME'));

Selecting data from table

SELECT STUDENT_ID, S.NAME.FIRST_NAME FROM STUDENT S;

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 that is integrated with 3rd party data providers through Windows Azure Marketplace Datamarket. Customers can test this functionality for the first time.

Productive development experience

Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Data Tools for a unified and modern development experience across database, BI, and cloud functions. Additionally, Express customers can test a new LocalDB version for fast, zero-configuration installation.


DOWNLOAD SQL SERVER 2012

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.


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

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 ' ' you can add spaces or any separator like - or / etc.

Look at my output screen


A network-related or instance-specific error occurred while establishing a connection to SQL Server


A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)

In this post I am going to show you the way to overcome this issue.

Just follow the quick steps:

(i) Open SQL Server Configuration Manager



(ii) Start the selected services as given in image below.



In most cases these steps will fix your issue. Thanks.

Time Out to Connect SQL Server Issue


When performing tasks involving a large amount of data in Ultimate Survey Enterprise survey software via your web browser, an error is thrown that looks similar to the following:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: Timeout expired

Solution:

1. Open SQL Server Enterprise Manager and find the server that contains your Ultimate Survey Enterprise survey software database.
2. Right click on the server name.
3. Select properties.
4. Choose the Connections tab.
5. Increase the query timeout value. The value is in seconds. Set it to 0 for unlimited time
6. Click OK.