Click here to Skip to main content
15,867,308 members
Everything / Database Development / SQL Server / SQL Server 2005

SQL Server 2005

SQL-Server-2005

Great Reads

by Wonde Tadesse
This tip/trick helps to set a default value for blob data type such as Image, VarBinary.
by Steve Krile
Fully AJAX-enabled user control used to select names from a database in a drop-down format.
by Mubin M. Shaikh
Create time dimension with 24 hour plus values and time buckets in your data warehouse
by Max Paulousky
This article presents the techniques and caveats of building Silverlight applications that can be notified about database state changes via polling duplex. WSHttp binding and CLR triggers are used to implement the solution as well.

Latest Articles

by Narapareddy Shyamprasad
SQL Dynamic Pivots
by Luca Astolfi
User defined procedure for make an HTML table from T-SQL Select statment
by JasonShort
Wouldn’t it be handy if you could use regular expressions (RegEx) in your SQL code?  You could do some really advanced queries through the ability to specify regular expressions rather than just the simple like % matching you normally use. SQL CLR can do that!
by Ebenezar John Paul
An Ideal Code Review Checklist that applies for most programming languages

All Articles

Sort by Score

SQL Server 2005 

15 Jan 2013 by Wonde Tadesse
This tip/trick helps to set a default value for blob data type such as Image, VarBinary.
24 Aug 2009 by Steve Krile
Fully AJAX-enabled user control used to select names from a database in a drop-down format.
5 Sep 2013 by Mubin M. Shaikh
Create time dimension with 24 hour plus values and time buckets in your data warehouse
27 Nov 2009 by Max Paulousky
This article presents the techniques and caveats of building Silverlight applications that can be notified about database state changes via polling duplex. WSHttp binding and CLR triggers are used to implement the solution as well.
3 Sep 2008 by Ahmed Negm
This article introduces how to create and manage .NET enterprise applications using your favorite technology (Data Access Application Block, LINQ, NHibernate, ASMX, and WCF) with the Model Driven Development approach by Sculpture.
14 Aug 2013 by Harmen Brouwer
How to create and deploy a SSRS rendering extension, explained by a functional Zip Rendering extension for SSRS 2005, 2008 (R2) and 2012.
6 Sep 2007 by aleksisa
Build a process to import Leads records from Excel into MS CRM via email.
13 Mar 2012 by Tecfield
This tip shows how to rename a database and its MDF and LDF files in SQL Server.
25 Oct 2008 by Abhijit Jana
Overview of Log Shipping in SQL Server 2005
27 Jun 2012 by freedeveloper
A small control to control paging in Windows Presentation Foundation.
15 Sep 2008 by Oleg Vorkunov
Set of SQL CLR Stored Procedures to execute T-SQL asynchronously without using a Service Broker.
21 Dec 2007 by Bruno Sonnino
This article will show how to use data binding and styles to show data coming from a Microsoft SQL database using the new object-relational model introduced in Visual Studio 2008, LINQ to SQL, allowing grouping, sorting and filtering of data with almost no code.
28 May 2010 by DecodedSolutions.co.uk
This is a simple article describing how to create an add an outlook appointment to your MS Outlook Calender.
16 Mar 2012 by Anurag Gandhi
A Query to copy an individual table data between databases
8 Jul 2010 by Md. Marufuzzaman
How easy to read a file without using of the seven extended stored procedures.
25 Oct 2012 by Enrique Albert
WPF Validation using the IDataErrorInfo interface combined with Validation attributes
2 Aug 2009 by Abhishek Sur
Gives an introduction of how to create SQL CLR managed objects in SQL server.
26 May 2013 by Tharaka MTR
This post will show you how to fix orphaned SQL users.
19 Dec 2007 by Bryan Sachdeva
This article describes how to create a wrapper class for the native Phone API, and then uses it to retrieve and store the call history.
19 Feb 2010 by Md. Marufuzzaman
This is good... You can also consider this link.[^]
28 Oct 2010 by Sunasara Imdadhusen
SQL Server 2005 : Generate Script with Data from Database – Database Publishing Wizard
25 Nov 2010 by Sivaraman Dhamodharan
How do you use the Template explorer and assign parameter values.
29 Nov 2011 by Savalia Manoj M
You Can try it in MS-SQL Like Declare @Month BigintSet @Month =11Declare @date date;Set @date='2011-11-01'while DATEPART(Month,@date)=@MonthBegin --Select DATEPART(DAY,@date); --Select DATEPART( WEEKDAY, @DATE ) DECLARE @Name VARCHAR(20) SELECT @Name = CASE (...
23 Feb 2012 by Mahmud Hasan
How to use variable in SELECT TOP SQL command
16 Aug 2012 by Espen Harlinn
One thing you should probably consider is to use CTEs to perform paging when the table gets bigger - have a look at Data Paging Using CTE (Common Table Expression)[^]This will allow you to browse the full table, even if you end up having a 100 million records or more.Best regardsEspen...
5 Dec 2010 by #realJSOP
0) This question is unanswerable, other than to recommend that you hire a programmer.1) Don't use your email address as a user ID. It's beyond stupid...
20 Dec 2012 by Wendelius
This tip describes how to delete duplicate rows from a table that doesn't have a key.
1 Apr 2011 by Dylan Morley
DECLARE @Iterator INTSET @Iterator = 0WHILE (@Iterator
30 Apr 2012 by Martin Thwaites
If you have an issue with a slow part of a site after a large dataload, that you can't replicate after a DB restore, it's likely you could be dealing with a similar issue.
9 Jun 2012 by VJ Reddy
The Solution 1 given by Stephen Hewison 2 is good.Alternatively, the following query can be used to Update Status in BLL when the Status is close for all corresponding Batch_Ids in BML table UPDATE BLLSET BLL.Status = 'Close'WHERE BLL.Batch_Id IN ( --List...
6 Mar 2013 by Amol_B
try this...GO to the folder r where you have stored mdf file. select file-> Right click ->click on . give full permissions to file for logged in user Security.
30 Jul 2009 by Giovanni Bejarasco
27 Jul 2011 by Reiss
Use the BETWEEN[^] functione.g.CREATE PROCEDURE Select_From_MyTable( @FromDate DATETIME, @TODate DATETIME)ASSELECT *FROM MY_TABLE WHERE MY_DATE BETWEEN @FromDate AND @TODate[Edit]Forgot to mention that the between function is inclusive so will include the...
6 Nov 2011 by Pranit Kothari
This will help,http://stackoverflow.com/questions/3712678/how-can-i-insert-data-into-two-tables-simultaneously-in-sql-server[^]
10 Dec 2011 by Monjurul Habib
Try the following, and hope it will work;SELECT sec.name, paid.BalanceFROM section sec INNER JOIN paid_table paid ON paid.sectionid = sec.idWHERE (paid.id = (SELECT MAX(id) FROM ...
25 Apr 2012 by Prasad_Kulkarni
Please refer following threads:Beginner SQL Tutorial[^]SQL Server Indexes [^]SQL Server Index Basics[^]Introduction to Indexes in SQL Server[^]A Guide to SQL Server Indexes[^][EDIT]This CP Article will clear all your doubts:Defining Indexes with SQL Server 2005[^]Some...
29 May 2012 by Prasad_Kulkarni
InProc and OutProc is related to asp.net session management. Session can be maintained in 3 different ways as seen below. InProc - session kept as live objects in web server (aspnet_wp.exe). Use "cookieless" configuration in web.config to "munge" the sessionId onto the URL (solves...
9 Jun 2012 by Stephen Hewison
I think this will do it:UPDATE BLLSET BLL.Status = 'Close'FROM BLL INNER JOIN (SELECT BML.Batch_Id, FROM BML INNER JOIN BLL ON BML.Batch_Id = BLL.Batch_Id GROUP BY BML.Batch_Id HAVING Count(Distinct BML.Status) = 1 AND MIN(BML.Status) = 'Close')...
15 Jun 2012 by Manas Bhardwaj
Something like this:SELECT CAST(ROUND(27.897, 2, 1) as DECIMAL(18,2)) Look at documentation for ROUND[^].
19 Aug 2012 by Christian Graus
Coalesce lets you specify a second column to get a value from, if the first is null. Your data format looks a bit stupid, but it's easy to write the SQL to select it. select particulars, coalesce(column1, column2) as myval from mytableI know from experience that you have paying...
17 Sep 2012 by aasim abdullah
Get all indexes list in detail, with their involved columns
18 Dec 2015 by Md. Marufuzzaman
This tip shows you an easy way to split Microsoft SQL Server table row data.
16 Oct 2007 by Adam Page
Setting up an SMS gateway to conduct an SMS survey and storing the survey results in an MS Access database, from which you can generate/print out reports.
25 Mar 2010 by Mustafa Ismail Mustafa
Could you please revise the way you posted that? It hurts the eyes.Also, you should consider SQL DMO:http://msdn.microsoft.com/en-us/library/aa258911(SQL.80).aspx[^]http://www.sqlteam.com/article/introduction-to-sql-dmo[^]These links will get you started. Once you give it a try,...
30 Aug 2010 by AspDotNetDev
DECLARE @IncrementValue intSET @IncrementValue = 1UPDATE Table1 SET Column1 = Column1 + @IncrementValue
10 Nov 2010 by Abdul Quader Mamun
17 Feb 2011 by John Gathogo
Catastrophic results associated with unqualified use of columns belonging to tables referenced on main query in a subquery
28 Feb 2011 by Dylan Morley
You need to ask yourself why you've got 'ch x' in country name if it's supposed to be United Kingdom. Anyway...do the case statement in your SELECT statementselect ctrycode, case when countryname='ch x' then 'United Kingdom' ELSE countryname end AS countryname, fees from ...
20 Apr 2011 by E.F. Nijboer
If you didn't create a clustered index then this is automatically created for the primary key. It could however boost performance to create an index yourself if your select is not based on the primary index but another column....
18 Jul 2011 by thatraja
Here you goTop 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and...
23 Jul 2011 by Uday P.Singh
Before performing UNION operation must know this:UNION RULES1.A UNION must be composed of two or more SELECT statements, each separated by the keyword UNION.2.Each query in a UNION must contain the same columns, expressions, or aggregate functions, and they must be listed in the same...
2 Nov 2011 by Gopal Krishna Ranjan
Dear,In that scenario we should use dynamic cursor: - DECLARE @MYVAR NVARCHAR(100)DECLARE MYTESTCURSOR CURSORDYNAMIC FORSELECT MESSAGE FROM ELMAH_ERROROPEN MYTESTCURSORFETCH LAST FROM MYTESTCURSOR INTO @MYVARCLOSE MYTESTCURSORDEALLOCATE MYTESTCURSORSELECT @MYVARThe...
2 Jan 2012 by Espen Harlinn
I would rethink the design, you only need to execute the inserts/updates/deletes inside the transaction - so create your transaction inside the SAVE_Click method. Best regardsEspen Harlinn
2 Jan 2012 by Mehdi Gholam
This is a very bad design. Transactions should be as short as possible as they take up resources on the RDBM server.You should absolutely not start on form load and commit/rollback on a button click which could be anything between 10 secs to 10 minutes in between.
3 Feb 2012 by Manfred Rudolf Bihy
You want to look into the TOP(n)[^] clause of TSQL[^].Here's the idea assuming activityids are only positive integers including zero:Select the first 100 activity entries:SELECT TOP(100) *FROM tblactivityWHERE activityid > -1ORDER BY activityid ASCFor the next 100 entries...
1 Mar 2012 by Shahin Khorshidnia
Sorry again. May I suggest another working method?1. In your database:have a Table (for example Person)Person:ID BigInt (PK)Name NVarChar(50) ...Node_ID BigInt (FK)Node_ID refers to IDIf Node_ID = Null then It means the record is a...
8 Mar 2012 by Ganesan Senthilvel
You almost got it; but little tweak at group by level.select substring(Id,1,4) as Id,count(Id) as count_id from Code_Project group by substring(Id,1,4)
6 May 2012 by OriginalGriff
It's because LIKE is a simple Regex processor, and '[' introduces a sequence of characters:...LIKE 'x[abc]x' would match any of 'xax', 'xbx' or 'xcx' but would not match 'xdx'See here: http://www.w3schools.com/sql/sql_wildcards.asp[^]I don't know how you get round it...
9 May 2012 by Wendelius
And a bit different approach. DECLARE @RefDate DATETIME = CONVERT(datetime, '01.01.1900',104);DECLARE @Seconds int = 92730;DECLARE @CompareDate datetime = DATEADD(second, 92730, @RefDate);SELECT @Seconds, CAST(DATEDIFF(HOUR, @RefDate, @CompareDate) AS varchar(100)) +...
24 May 2012 by RDBurmon
Okey , Since you have not specified exact table structure and data so I have considered belowTable1 (A BIGINT , B NVARCHAR(50) , C NVARCHAR(50) , D NVARCHAR(50) , E NVARCHAR(50) )Table2 (F NVARCHAR(50) , G NVARCHAR(50) , H NVARCHAR(50) , B NVARCHAR(50) , K...
13 Jun 2012 by Stephen Hewison
The reason your second query is not showing a true result is because you're joining the tables without any kind on link. That means that the counts will be multiplied by the other tables. test * usertesting * user1.So if you had the following tables:TableA, TableB, TableC1 6 ...
17 Aug 2012 by ManojKumar19
Update row column with another row column in same table SQL only if it is null.
28 Aug 2012 by akhil khare
Find multiple strings (delimenated by space) in all selected columns.
28 Aug 2012 by OriginalGriff
Try:SELECT SUM(ISNULL(deductions, 0)) FROM Billing
1 Sep 2012 by __TR__
I am not sure if i understood your question correctly. I am assuming you want an idea on how to structure your tables for your service project. It would actually depend on the requirements and what data is required.Given the description of the project details one approach would be, You...
1 Sep 2012 by __TR__
Refer these CP articles. It should give you some idea.Storing and Retrieving Images from SQL Server Using Strored Procedures and C#.net[^]Storing and Retrieving Images from SQL Server using Microsoft .NET[^]SQL Database Image Storage & Easy Thumbnails[^]Save An Image Into SQL Server 2000...
4 Sep 2012 by Prasad_Kulkarni
Refer:CP: SQL Injection Attacks and Some Tips on How to Prevent Them[^]MSDN: Protect From SQL Injection in ASP.NET[^]How you can prevent an SQL injection[^]How to Avoid SQL Injection[^]
13 Dec 2012 by Paw Jershauge
MS SQL system views, mapped over version.
27 May 2013 by Tharaka MTR
Generate insert statements from a specific table data using SQL Server
19 Sep 2013 by Love 2 code
Hi,my solution involves a temp table:Select * Into #AP from (Select Category='Computer',RATES_MASTER='50000'unionSelect Category='Computer',RATES_MASTER='20000'unionSelect Category='Computer',RATES_MASTER='15000'unionSelect...
25 Aug 2010 by OriginalGriff
You are doing it the wrong way already!SQL Connections are a very scarce resource. By opening the connection on form load and closing it on form close, you are denying it to other processes - this may prevent your own application from working, under some circumstances and you will have no...
13 Sep 2010 by Al-Farooque Shubho
See This Article[^]Also, you can always Google for searching and exploring on the things you want to learn.Best of luck
27 Sep 2010 by venugopalm
Integrating database development into Visual Studio to collaborate application development team and the database designers
15 Dec 2010 by Abdul Quader Mamun
Thank you for your question.create proc ppas declare @aa xmlbegin set @aa= select phone no from table1; SELECT @aa=[phone no] AS PhoneNo FROM table1; EXEC stordproc2(@aa)endThanks,Mamun
21 Dec 2010 by JF2015
CREATE PROCEDURE sp_Emp_Save( @Eno INT, @Ename VARCHAR(100), @JOb VARCHAR(100), @Gender CHAR(1), @Sno INT, @Sname VARCHAR(100)) AS BEGIN IF NOT EXISTS (SELECT Ename,Job FROM EMP WHERE Eno = @Eno) BEGIN INSERT INTO EMP...
6 Jan 2011 by Sandeep Mewara
It might be surface area configuration error or sql service configuration issue. Have a look at this thread for potential reasons and resolutions: Resolving A network-related or instance-specific error occurred while establishing a connection to SQL Server[^]If needed, See steps for...
13 Jan 2011 by Dylan Morley
Have a look herehttp://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm[^]e.gSELECT CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME )
19 Jan 2011 by Manfred Rudolf Bihy
I doing a bit of guessing here but I think you really meant to say you want to have code in text form inside the database and then include that code in your aspx page by placing it inside &lt%; %> tags. Given the above assumption holds true I can tell you that: Yes it can be done, but it...
22 Jan 2011 by Estys
See example #2 in the following link :http://techonthenet.com/sql/update.php[^]Cheers
27 Jan 2011 by Pravin Patil, Mumbai
bool status = true;if (status == true){ for (int status = 0; status
17 Mar 2011 by Yusuf
When you modeling, look further than the real world it self. Your shipment may need a seal, but your seal does not need to know about the shipment, or does it?tblShipment - ID - PK Description - Describes your shipment (You may or many not need this) TruckNo ...
19 Mar 2011 by Goalie35
Hello.A friend of mine approached me about re-architecting his company's entire system. They're a somewhat small company but are starting to grow pretty quickly and their current system is outdated and takes too much manual labor to interact with. I don't normally deal with architecture of...
31 Mar 2011 by ZeeroC00l
Using a Custom Action On Database during installation[^]Custom Action[^]The above methods explain as to how you can go about creating a new database during the installation. I think the same with some modification can be used to insert the details into Database.
3 Apr 2011 by dan!sh
Use this in click event:SoundPlayer player = new SoundPlayer("file path here");player.Play();Although, if this is a web application, you will have to embed the sound file in the page on the button click.
7 Apr 2011 by Manfred Rudolf Bihy
Use TSQL's IF statement to check if a parameter is NULL: http://msdn.microsoft.com/en-us/library/ms187471.aspx[^]IF @param1 IS NULLBEGIN ...END...and construct your SQL accordingly.As an alternative you may choose to use default values for the parameter definitions of...
13 Apr 2011 by Costica U
This should run a little fasterSqlDataReader reader = DbInfo.DataRdr(Conn, "SELECT DISTINCT Events.Location FROM Events WHERE NOT EXISTS( SELECT 1 FROM Location WHERE Code = Events.Location")int colOrdinal = reader.GetOrdinal("Location");while(reader.Read()) { ...
29 Apr 2011 by Wonde Tadesse
use the following code.' Text=''>
4 May 2011 by GR3YF0X
can you possibly do this using BI tools like SSIS and dont create a connection for each SP call. Keep the connection open until you are done. Hope this helps
19 May 2011 by CS2011
Yes it is possible. Try the following command //Make read onlyALTER DATABASE database-name SET READ_ONLY//Back to read writeALTER DATABASE database-name SET READ_WRITE
23 May 2011 by OriginalGriff
Try: using (SqlConnection con = new SqlConnection(strConnect)) { con.Open(); using (SqlCommand com = new SqlCommand("SELECT name FROM myTable WHERE id=@ID", con)) { ...
28 May 2011 by thatraja
Here you goSteps to troubleshoot SQL connectivity issues[^]SQL Server 2005 Connectivity Issue Troubleshoot - Part I[^]Troubleshoot Connectivity Issue in SQL Server 2005 - Part II[^]Troubleshoot Connectivity Issue in SQL Server 2005 - Part III[^]BTW I gave you this bunch also...
29 May 2011 by Abhinav S
Some links -Storing and Retrieving Images from SQL Server using Microsoft .NET[^]http://www.shabdar.org/sql-server/105-store-save-images-in-sql-server.html[^]Save An Image Into SQL Server 2000...
3 Jun 2011 by R. Giskard Reventlov
See here[^].You should probably use:exec sp_executesql MyProc
8 Jun 2011 by OriginalGriff
No, it won't be. The reason is simple:What if you have a second table, which contains information related to the first?For example, if your first table is Customers, and your second is Purchases, then there would be a numeric field in your second table to relate the purchase to a specific...
25 Jun 2011 by thatraja
ArticlesSQLS...
27 Jul 2011 by OriginalGriff
As Reiss says, you can use BETWEEN, or you can simply say SELECT * FROM myTable WHERE [date] >= '2011-07-21' AND [date]
27 Aug 2011 by Simon Bang Terkildsen
SELECT MAX(id) FROM mails where ...see SQL MAX()[^] or MAX T-SQL[^]
20 Sep 2011 by sachin10d
Try thisselect empCode, [Date], [1] intime ,[2] break_out,[3] break_in,[4] outtimefrom(select empcode,convert(date,[DATE]) [date],convert(time,[time]) time ,ROW_NUMBER() over(partition by empcode,date order by empcode,date,time) Tin from emp)datapivot( max([time]) for Tin...
17 Oct 2011 by Not Active
The last day of June has been the same since about 1582. The 30th.
22 Oct 2011 by RaisKazi
Have a look at below links to understand Joins.Using JoinsUsing Inner JoinsUsing Outer JoinsNot sure What do you mean exactly by? - "wanna show it dropdownlist."Which Front-End Technology you are using? Asp.Net, Win-Forms(.Net) or something else?