How to use optional parameter in SQL server SP

When you are going to write a generic SP for any business purpose you may realize the necessity of optional parameter. Yes Sql server gives us the opportunity to use optional parameter in SP arguments. You may write a SP with 3 arguments but based on your business rule you may pass one or two or three valuse as you want. This policy not only ease our life but also help us to write short SP. Here

Error: 'object could not be found' or 'invalid object name'?

As a Developer i thought you may experience one of two errors frequently, even though you thought the object already exist. The error like below:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Table/View Name'.

OR

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ''.






Solution:
To Avoid Such type of error keep in mind always the following considerations:

1. The

How to determine whether a table is exist or not in Sql Server database

In some cases we need to identify whether a table is exist or not in a sql server database. This is very simple & now i am sharing with you. Hope it will works like a handbook for you.













Query:
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tablename')
SELECT 'table exists.'
ELSE
SELECT 'table does not

Syntax of Shrinking SQL Server database mdf and log file

The below code snippet will help you to shrink the database. Run the code snippet through your SQL Server query analyser.
















backup log with truncate_only
use
dbcc SHRINKFILE (,2)
dbcc ShrinkDatabase (, 2)

Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

When you google this error you will get a lots of solution. But no one help me to resolve my problem. So i am working on this issue to find out my solution & finally i got a simple problem which i want to share with you. My situation is i have a link server (SQL Server 2008) with my working Sql server 2005. I have created a table in the link server means Sql server 2008 which is given below:

Set a database to read only mode using SQL Server 2005 / 2008

In most DBA or Database Administration purpose we need to set a Database to readonly mode. Here i will show you how you can set Database in Read Only mode for Sql server 2005 & Sql Server 2008. One thing keep in mind that you can not use same sql command for both sql server 2005 and 2008. Thats why here i will show the different ways to manage a Database to Read Only mode.
The another important

Kick expire close all user connection from Sql Server 2005 / 2008 Database

In some cases DBA's need to expire or close all connections from SQL server 2005 / SQL server 2008 database such as for attach detach DataBase, Make DB readonly, perform maintenance tasks etc. For such type of issues DBA wants to get exclusive access to the database. To do so, you can set the database to Single User Mode, which permits only one database connection at a time. At that moment if

Creating RSS feed using Asp.Net 2.0 / 3.5

RSS means Really Simple Syndication which is a Web content syndication format.

If you are looking for "Read/Consume RSS feed" article then click here.

Now let’s try to find out what RSS is about. Basically the website owner should return a RSS feed - and that's simply an XML document following a certain standard, describing new or latest articles on your site. When one copy your feeds & read it

Display Images in GridView from Sql Server Database Table Using Asp.net C#

In my previous post i showed you "How one can upload images into Sql Server using Asp.net C# FileUpload control". In this post i will show you how one can display images into a GridView from Sql Server table. As you know in most of the web applications requires to handle different type of images like large,thumbnail etc. If those web applications are e-commerce site then you must be carefull when

Save Images into Sql Server Database Table using asp.net FileUpload Control

This article will explain how one can insert or save images into a sql server database table using asp.net FileUpload control. You may ask why we will save or store images into sql server database table instead of a server folder? The answer is its easy to use, easy to manage, easy to backup as well as easy programming. But one thing you have to keep in mind that you need to extend the size of

Javascript to get CheckBoxList value

To read CheckBoxList Selected Value, Selected Index & Selected Text in serverside is an easy job but using javascript its a bit difficult. In this article i want to share with you how one can read CheckBoxList SelectedValue, SelectedIndex & SelectedText by using javascript. Before explanation i want to share with you that getting SelectedIndex & SelectedText from javascript is a bit easy rather

The syntax of SQL Server Cursor

Cursor means memory address where SQL data processed. By using cursor one can process data one by one by looping through all records within a cursor. So when developer thinks that there is no way to accomplish an operation by writing a single query then the alternative solution is cursor. That means cursor ease our life by providing a looping mechanism through all records. But one thing keep in

SQL Server 2008 Error: Saving changes is not permitted

Few days ago I have migrated one of my Database from SQL Server 2005 to SQL Server 2008. Actually what I did during migration? I have created a Database first in SQL Server 2008. Then I have imported all of my SQL Server 2005 tables into the SQL Server 2008 Database. I thought that Everything is perfect but when I live my application for testing then I found that System can not insert data into

How to pass SP parameters into Dynamic SQL

In my first article I wrote An Introduction on creating Dynamic SQL in SQL Server Stored Procedure (SP). In this article I will try to write a bit advance Dynamic SQL. Here i will show you How one can transfer or pass parameter into Dynamic SQL query & get scalar value as output parameter or can Store output data into a temporary table.CLICK HERE To read "How can invoke/read SQL Server SP from

Write & Execute Dynamic SQL in SQL Server Stored Procedure

Most often we have to Create Dynamic SQL for different conditions to bring back results in our Asp.net applications. If we make the SQL in page code behind then its simple & easy. But think when one need to reuse the query with another set of values then what he can do? Copy the code block & paste it into the newly created aspx page? No here i will try to give you examples on How To Create a

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

Few days ago i found an error in RDLC report after published. The client claims that sometimes the report shows fine but sometimes shows the error. Then i test this report in my development PC but no luck. I can not generate any error. After that in my testing i found an error like below:"An error has occurred during report processing.Exception has been thrown by the target of an

How to delete multiple rows in a GridView using Asp.net

You knew that GridView allow us to delete a single row at a time. Here i would like to show you "How we can remove multiple GridView rows like Gmail Deletion". First of all i assume that you can Bind Sql server data into a GridView. So to make easier this article i will show only the delete operation. Plus i will discuss on deletion problem of master child data later on this article.To do that

Add/Modify/Resize/Change/Delete/Drop/Rename MS SQL Server Table Column using T-SQL

As a software developer or Sql Server Administrator most often we need to change a column datatype or length or rename even delete or drop. So Add/Modify/Resize/Change/Delete/Drop/Rename operations is a very common task in our daily lives. Lets start an example: Let you start inserting data into a table but getting the ERROR: String or binary data would be truncated.The reason is you are trying

Making or creating comma separated value list in a single line using SQL SP or UDF

Most of the times we need to display a summary or quotation line in our page by making a comma separated value list from SQL Server table data rows. But the reality is we did not get any built in function from SQL Server. But we did it by applying few techniques. Lets for SQL Server we can use the COALESCE function, In SQL Server 2000 we can use a variable smartly in our SELECT statement. To do

Login failed for user: The user is not associated with a trusted SQL Server connection

This is your first project? Am i right? Yes almost 50%(my guess) Asp.net beginner got the below message while he run his project. The message is:Login failed for user ''. The user is not associated with a trusted SQL Server connection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error