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??
|