Posts

Showing posts with the label SQL Server

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 windowNow you can see the query which returned 200 rows allowing you to editOn 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

Image
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

Image
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

Image
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.

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

Image
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)

Image
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

Image
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

Image
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

Image
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)

Image
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'.

Image
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  (1,STUDENT_NAME_TY('Abhimanyu','Kumar'));
or we can use the follow…

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 dat…

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

Image
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

Image
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.

Popular posts from this blog

5 Ways to send data between ASP.NET Pages