Delete statement with JOIN in SQL Server 2005

We often need to delete data from table based on JOIN.  In that case, what we do is, try to execute SELECT query with JOIN, we find and confirm the records we want to delete and then remove SELECT statement and write DELETE instead. But this will not work in case of JOIN. We need to give table alias explicitly to tell SQL Server Engine that what table we need to include in delete. Have a look at following case.

–create table1 for demo

create table Orders1

(

      OrderID INT not null constraint pk_ordid primary key,

      orderdate datetime

)

–create table2 for demo

create table orderDetails1

(

OrderDetailsID int not null,

OrderID INT not null constraint fk_ordid references Orders1(OrderID)

)

–insert records

insert into orders1

select 1,getdate() union all

select 2,getdate() union all

select 3,getdate() union all

select 4,getdate()

insert into orderdetails1

select 1,3 union all

select 2,2 union all

select 3,3 union all

select 4,1 union all

select 5,2

GO

–try to select records which you want to delete

select o.OrderID,o.orderDate,od.OrderDetailsID

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

order by o.orderid

–now try to delete records by removeing

–SELECT statement and put DELETE instead.

–it will not work, as SQL Engine will be confused,

–what to delete, orders1? or orderdetails1?

delete

–select o.OrderID,o.orderDate,od.OrderDetailsID

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

–order by o.orderid

–here is the solution, you have to specify

–I need to delete from od (OrderDetails)

delete od

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Error Fixed Cannot open database "dbname" requested by the login. The login failed. Login failed for user ‘Machine\ASPNET’ In ASP.NET and SQL express

One of the readers was using windows authentication in SQL connection string. When he tries to access database from connection string from Windows Application, it was working but when he tries to access same database with same connection string, it was throwing above error.

Main reason behind this error is permission issue. Now, question pops up in your mind, if that is a connection issue than why it was allowing same connection string in Windows application? Simple logical reason is that, Windows application uses local system account to connect with database when connection string built for Windows Authentication and ASP.NET uses ASPNET user of the web-server to connect with database. If your ASPNET user don’t have permission to access database, you will greeted with error

“Cannot open database ‘databasename’ requested by the login. The login failed for user ‘Machine-name/ASPNET’”.

So, now you know this is a permission issue, as soon as you will grant access to your db to ASPNET user, you will be ok but what, if you don’t have SQL Server client tool? It is quite possible if you are having SQL Express edition.

First let us see the command which will helpful to give permission to ASPNET user.

EXEC sp_grantlogin ‘Machine-name\ASPNET’

USE ADVENTUREWORKS–(YOUR DATABASE NAME)

EXEC sp_grantdbaccess ‘Machine-name\ASPNET’

EXEC sp_addrolemember ‘db_owner’, ‘Machine-name\ASPNET’

Run above script and you are done but what if you don’t have SQL Server client tool? You have to use OSQL utility from DOS prompt to run above command.

After getting DOS screen, follow below given command.

Note: “Ritesh” is my webserver name where ASPNET user resides. “.” in OSQL command represent my databse server you can give your SQL server’s IP or Name there, like: ‘Localhost\SQLExpress’

C:\Documents and Settings\Ritesh>cd\

C:\>osql -S . -E

1> sp_grantlogin ‘Ritesh\ASPNET’

2> GO

1> use adventureworks

2> GO

1> sp_grantdbaccess ‘Ritesh\ASPNET’

2> go

1> SP_addrolemember ‘db_owner’,’Ritesh\aspnet’

2> go

So, here with you will solve your error with above command along with OSQL utility which is good utility itself.

Enjoy !!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Three certificate in Expert-Exchange along with 200K points in last 30 days

If you are regular reader of my blog than you might remember that I have published news few days back, exactly on 30^th Mar 09, that I have started helping community in Expert-Exchange. Here is my article on that topic http://www.sqlhub.com/2009/03/expert-and-premium-member-in-expert.html

It’s even less than a month I have published that news and today I am awarded with my third certificate, out of which two MASTER and one GURU in Microsoft SQL Server zone and Microsoft SQL Server 2005. I am really pleased that I have got chance to helping the IT community by my blog and by expert-exchange.

Here is a summary of my small journey in expert-exchange.

Points in EE
0 Point on Mar 26, 2009
50K Points on APR 11, 2009
100K points on Apr 16, 2009
150K points on Apr 19, 2009
200K points on Apr 26, 2009

Certificates in EE
MASTER SQL Server Apr 13, 2009
MASTER SQL Server 2005 Apr 22, 2009
GURU SQL Server Apr 26, 2009

Answer History in EE
1   Question Answered on Mar 26, 2009
50  Question Answered on APR 09, 2009
100 Question Answered on APR 19, 2009
150 Question Answered on APR 25, 2009

List is growing on and on    

Moreover, I have entered in HALL of Fame for the year (12th), month (1st) and week(1st) in my very first month for SQL Server zone and in Month and Week for SQL server 2005 zone, looking forward to hold this position and enter in over all zone Hall of Fame very soon.

If you would like to see my EE profile, do watch it at:

http://www.experts-exchange.com/M_4847866.html

Thanks for all the co-operation and acceptance of everybody and special thanks to Mr. Pinal Dave (SQLAuthority.com).

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Load Relational XML data in SQL Server 2005

When question comes to play with XML, even seasoned programmer try to get away from it, not because it is difficult task but lack of knowledge. I promise, loading XML data into SQL Server 2005 as a relational data, is not going to be as difficult as you are thinking.

Let me show you one example which will fill two relational tables in SQL Server from XML data. Let us first create one XML file at C:\vendor.XML with following data in it.

   <vendor>

<c>

  <venID>101</venID>

  <first_name>James</first_name>

  <last_name>Butler</last_name>

<po>

  <poid>1001</poid>

  <venID>101</venID>

  <po_date>2008-04-01T00:00:00</po_date>

  <price>400.00</price>

  </po>

<po>

  <poid>1002</poid>

  <venID>101</venID>

  <po_date>2008-05-01T00:00:00</po_date>

  <price>299.0000</price>

 </po>

<po>

  <poid>1002</poid>

  <venID>101</venID>

  <po_date>2009-04-01T00:00:00</po_date>

  <price>600.0000</price>

</po>

 </c>

</vendor>

<

Now, create two tables to feed vendor details and purchase order detail, than we will load XML file into both tables.

–create table to hold Vendor information

CREATE TABLE vendor (

        venid INT NOT NULL

        , first_name VARCHAR(50)

        , last_name VARCHAR(50)

        )

–create table for holding Purchase order information.

CREATE TABLE PurcharOrder (

        poID INT NOT NULL

        , venid INT NOT NULL

        , po_date DATETIME

        , price MONEY

        )

GO

–hold xml from file

DECLARE @x XML

SET @x= (

SELECT xm.Col1 FROM OPENROWSET(BULK ‘c:\vendor.xml’,SINGLE_BLOB) AS xm(Col1)

         )

— show file contents

select @x

INSERT INTO vendor

SELECT

ven.value(‘./venID[1]’, ‘INT’) as vid

, ven.value(‘./first_name[1]’, ‘VARCHAR(50)’) as first_name

, ven.value(‘./last_name[1]’, ‘VARCHAR(50)’) as last_name

–, C.query(‘.’) 

FROM @x.nodes(‘/vendor/c’) tab(ven)

INSERT INTO PurcharOrder

SELECT

ven.value(‘./poid[1]’, ‘INT’) as POID

, ven.value(‘./venID[1]’, ‘INT’) as VenID

, ven.value(‘./po_date[1]’, ‘DATETIME’) as po_date

, ven.value(‘./price[1]’, ‘MONEY’) as price

–,ven.query(‘.’)

FROM @x.nodes(‘/vendor/c/po’) tab(ven)

GO

–check both table, whether data comes or not.

select * FROM Vendor

Select * from PurcharOrder

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Fix Error: Msg 8116, Level 16, State 1, Line 1 Argument data type ntext is invalid for argument 1 of replace function.

Have you ever come across MSG 8116? It is really annoying error in SQL Server 2000 and SQL Server 2005, when you try to perform some string operation on fields having NTEXT as column data type, you most probably greeted with error something like header of the article.  Let us see how the error comes and how to eliminate it?

USE [adventureworks]

GO

if exists (select * from dbo.sysobjects where id =object_id(N‘[dbo].[EMPS]’) and OBJECTPROPERTY(id, N‘IsUserTable’) =1)

DROP TABLE Emps

GO

CREATE TABLE [dbo].[emps](

[Name] [ntext],

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

–insert records

INSERT INTO emps

SELECT ‘ RITESH shah ‘,‘MIS’,‘ECHEM’ UNION ALL

SELECT ‘ Rajan ‘,‘MIS’,‘mar’

GO

select rtrim(name) from emps

–or

select replace(name,‘ ‘,”) from emps

–you will be greeted with following error in above queries

–Msg 8116, Level 16, State 1, Line 1

–Argument data type ntext is invalid for argument 1 of rtrim function.

–there solution is:

–either you convert your column physically to varchar or cast it to varchar while querying

select replace(convert(varchar(25),name),‘ ‘,”) from emps

–or

select ltrim(rtrim(convert(varchar(25),name))) from emps

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

List of all available DateTime format in SQL Server 2005

We, as a SQL Developer, often need to check, change and see different types of available DateTime format in SQL Server. I would like to share one small yet handy script which will list all available DateTime format for you.

set nocount on

DECLARE @Date int

create table #temp

(

Type int,

date varchar(50)

)

set @Date=131

While @Date>=0

begin

if((@Date>=0 and @date<15) or (@Date>=20 and @date<26) or (@Date>=100 and @date<=114) or @Date in (120,121,126,130,131))

insert into #temp

select convert(varchar,@date),convert(varchar,getdate(),@Date)

set @Date=@Date–1

end

select * from #temp

drop table #Temp

Here is the result.

Type        date

———– ————————————————–

131         28/04/1430  1:26:57:153PM

130         28 ???? ?????? 1430  1:26:57:1

126         2009-04-23T13:26:57.153

121         2009-04-23 13:26:57.153

120         2009-04-23 13:26:57

114         13:26:57:153

113         23 Apr 2009 13:26:57:153

112         20090423

111         2009/04/23

110         04-23-2009

109         Apr 23 2009  1:26:57:153PM

108         13:26:57

107         Apr 23, 2009

106         23 Apr 2009

105         23-04-2009

104         23.04.2009

103         23/04/2009

102         2009.04.23

101         04/23/2009

100         Apr 23 2009  1:26PM

25          2009-04-23 13:26:57.153

24          13:26:57

23          2009-04-23

22          04/23/09  1:26:57 PM

21          2009-04-23 13:26:57.153

20          2009-04-23 13:26:57

14          13:26:57:153

13          23 Apr 2009 13:26:57:153

12          090423

11          09/04/23

10          04-23-09

9           Apr 23 2009  1:26:57:153PM

8           13:26:57

7           Apr 23, 09

6           23 Apr 09

5           23-04-09

4           23.04.09

3           23/04/09

2           09.04.23

1           04/23/09

0           Apr 23 2009  1:26PM

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Fix user and login synchronization after moving database from sql server 2000 to sql server 2005

When I have moved my SQL Server 2000 database to SQL Server 2005 first time in past, I faced user and login synchronization problem. I want to share it with you as I have seen this question in forums many times.

When you move your database from SQL Server 2000 to SQL Server 2005, logins and users of SQL Server 2000 will be out of sync in SQL Server 2005. This is really very annoying. After digging I came to know what the problem is and I have developed one small script which is very useful and handy to make your login and user in sync back in your new server.

USE AdventureWorks –give your own database name which you have

–restored from SQL Server 2000 to SQL Server 2005

GO

DECLARE @UserName Nvarchar(50)

DECLARE c CURSOR DYNAMIC

FOR SELECT DISTINCT [Name] FROM [sysusers] WHERE [sysusers].[uid] < 16000 AND [Name] NOT IN (‘guest’, ‘sa’, ‘dbo’,‘public’,‘sys’,‘INFORMATION_SCHEMA’) ORDER BY [Name]

OPEN c

FETCH NEXT FROM c INTO @UserName

WHILE @@FETCH_STATUS=0

BEGIN

EXEC sp_change_users_login ‘Auto_Fix’, @UserName, NULL, @UserName

FETCH NEXT FROM c INTO @UserName

SELECT @UserName

END

CLOSE c

DEALLOCATE c

GO

Enjoy Upgrading!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

httpRuntime – Increase file size limit from 4 MB to bigger size in ASP.NET

I wrote an article on how to upload image to SQL Server database BLOB fileld and retrieve it back. You can refer that article at:

http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html

After trying to implement this functionality, one of my readers has observed that he can’t upload the file big file. He came to me for solution. Actually in ASP.NET you can upload file with maximum size of 4MB. That is the default settings. Than how could you upload bigger file than 4096 kb? Isn’t there any way around? Because this is somehow very common need.

Well, they is a will, there is way!!! You can set maximum file size with httpRuntime tag. You can set this at machine.config or web.config but I insist, not to touch machine.config file as it has web server wide effect, you should set it in web.config so that it affect one website or one subdirectory in which web.config reside.

You have to add one line under <system.web> element of your web.config. The line is

<httpRuntime maxRequestLength=”20480″ />

This will increase file limit from 4 MB to 20MB.

Enjoy!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find user in all database with Sp_MSforEachDB in SQL Server 2005

I just assisted one person in one of the forum who wanted the list of all databases which are having user “Guest”. Since this is something needs to iterate between all available databases in SQL Server except “Master” and “TempDB”, I thought to do it by undocumented stored procedure available in Microsoft SQL Server 2005, Sp_MSforEachDB.

Let us see how I did it.

EXEC sp_MSforeachdb

‘if ”?” <> ”tempdb” and ”?” <> ”master”

begin

USE ? SELECT name,db_name() databasename FROM sys.sysusers

WHERE name = ”Guest” ORDER BY name

end

‘

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Date calculation with CTE in SQL Server 2005

One of my close friends is working on one project for Construction Company recently as a free lancer, when I visited his home yesterday, he was calculating days in C#, since I believe to manipulate data more in SQL Server I helped him in this situation and we did calculation in SQL Server itself.

Here is small part of script the script with one small situation. In big construction company, they might have many contractors to work on site; they might have one table which stores information about contractor and their start and end date. Now, I want to calculate which contractor has worked how many days in particular month. Well, it seems pretty easy, isn’t it?

You can use date difference function and sum it up, you are done. Right? NO, it won’t give you proper picture, for example if one contractor has worked from 27-Jan-09 to 5-Feb-09 than date function will display 9 days in January month but what I want is 4 days in Jan and 5 days in FEB. Let’s have a look at how to solve this by CTE, there may other ways also, if any of my reader new that, kindly give it here so that other readers can find few other ways to do the same task.

–create table for demo

create table site

(

contractor varchar(20),

startDate datetime,

endDate datetime

)

–have some date for testing,

INSERT INTO Site

SELECT ‘Ritesh’,‘2009-02-01’,‘2009-02-07’ union all

SELECT ‘Ritesh’,‘2009-02-15’,‘2009-03-15’ union all

SELECT ‘Ritesh’,‘2009-01-01’,‘2009-01-14’ union all

SELECT ‘Rajan’,‘2009-02-25’,‘2009-03-05’ union all

SELECT ‘Rajan’,‘2009-03-11’,‘2009-04-01’

–cte as a powerful solution of this situation.

WITH CTE AS

(

select contractor,startDate, endDate, startdate totDate from site f

union all

select s.contractor,s.startDate, s.endDate, s.totDate+1 from cte s

where s.totdate+1<s.enddate

)

select contractor,year(totdate) as ‘year’,month(totdate) as ‘month’,count(*) as ‘TotalDays’ from cte

group by contractor,year(totdate),month(totdate)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah