XML issue

[bigphish]

I am trying to insert dataset as xml datatype,although the query is working fine, I am getting error in the application.

error:Cannot drop the table '#temp2', because it does not exist or you do not have permission.

I have tried alot to fix this..every thing in my query seems fine.adding the Query below:

create table #temp2 (JobID int,winnerId int,Title Nvarchar(50),FirstName nvarchar(50),LastName nvarchar(50),
Destination nvarchar(50),DateBooked datetime,DateDeparture datetime,Operators nvarchar(50),PhoneHome nchar(25),
PhoneWork nchar(25),Address nvarchar(200),TCAT nvarchar(50),Notes nvarchar(50),LastModifiedBy nvarchar(50),LastModifiedDate datetime)

insert into #temp2


SELECT  
	Data.Col.value('JobID\[1\]','int')as JobID,
	Data.Col.value('WinnerID\[1\]','int')as WinnerID,
	Data.Col.value('Title\[1\]','nvarchar(50)')as Title,
	Data.Col.value('FirstName\[1\]','nvarchar(50)')as FirstName,
	Data.Col.value('LastName\[1\]','nvarchar(50)')as LastName,
	Data.Col.value('Destination\[1\]','nvarchar(50)')as Destination,
	convert(datetime, left(Data.Col.value('DateBooked\[1\]','varchar(50)'),10)) as DateBooked,
	convert(datetime, left(Data.Col.value('DateDeparture\[1\]','varchar(50)'),10)) as DateDeparture,
	Data.Col.value('Operators\[1\]','nvarchar(50)')as Operators,
	Data.Col.value('PhoneHome\[1\]','nchar(25)')as PhoneHome,
	Data.Col.value('PhoneWork\[1\]','nchar(25)')as PhoneWork,
	Data.Col.value('Address\[1\]','nvarchar(200)')as Address,
	Data.Col.value('TCAT\[1\]','nvarchar(50)')as TCAT,
	Data.Col.value('Notes\[1\]','nvarchar(50)')as Notes,
	Data.Col.value('LastModifiedBy\[1\]','nvarchar(50)')as LastModifiedBy,
	convert(datetime, left(Data.Col.value('LastModifiedDate\[1\]','varchar(50)'),10)) as LastModifiedDate



FROM @Winnerpersonal.nodes('/NewDataSet/WPersonal') AS Data(Col)


insert into tbl\_WinnerPersonal(JobID,Title,FirstName,LastName,Destination,DateBooked,DateDeparture,Operators,PhoneHome,PhoneWork,Address,TCAT,Notes,LastModifiedBy,LastModifiedDate)
select @JobID,Title,FirstName,LastName,Destination,DateBooked,DateDeparture,Operators,PhoneHome,PhoneWork,
Address,TCAT,Notes,LastModifiedBy,LastModifiedDate from #temp2	where WinnerID < 0

drop table #temp2

CAn any one know wherer I went wrong??

[Not Active]

bigphish wrote:

CAn any one know wherer I went wrong??

Yes. Posting a SQL Server question to the ASP.NET forum

---

I know the language. I've read a book. - _Madmatt

[David Mujica]

Temp tables are destroyed after the sessions ends, so you may want to try removing the explicit "drop table #temp2". And see if things work. You can verify whether the table is present or not by using the following command: select name from tempdb..sysobjects where name = '#Temp2' Step through your code and you should see the table being created, then automatically destroyed for you when the session ends. Refer to this article for some additional info on temp tables: http://www.sqlteam.com/article/temporary-tables[^] Good luck. :thumbsup:

[Ennis Ray Lynch Jr]

IIRC, the table variable is preferred to temp tables. Similar syntax:

DECLARE @temp2 TABLE (JobID int,winnerId int,Title Nvarchar(50),FirstName nvarchar(50),LastName nvarchar(50),
Destination nvarchar(50),DateBooked datetime,DateDeparture datetime,Operators nvarchar(50),PhoneHome nchar(25),
PhoneWork nchar(25),Address nvarchar(200),TCAT nvarchar(50),Notes nvarchar(50),LastModifiedBy nvarchar(50),LastModifiedDate datetime);

Now no need to drop

Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. A man said to the universe: "Sir I exist!" "However," replied the universe, "The fact has not created in me A sense of obligation." --Stephen Crane