script runs succesfull but doesnt update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

script runs succesfull but doesnt update

Author

Topic

youri.korpel
Starting Member

3 Posts

Posted - 2008-01-10 : 08:05:04

Hi Guys,

When executing the script below, it does execute succesfull, but doesnt update the [dbo].[AllUserData] table. The database is not set to read only and i am running as dbadmin. The database is a Sharepoint 2007 Content database. All tables and columns
exist. A csv file is supplied with the correct columns and data.

The script runs well till the declare statement. I think there is something wrong with the criteria.

Can someone help me please?

The script:

create table ##moss_update (
tp_leafname nvarchar(128),
tp_dirname nvarchar(256),
tp_created datetime)
GO

Insert into ##moss_update
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\DATA\;HDR=NO',
'SELECT * FROM [c4.csv]')
GO

ALTER TABLE ##moss_update
ADD identifier int identity (10001,1)
GO

SELECT * FROM ##moss_update
GO

DECLARE @identifier AS int
SET @identifier = 10001
DECLARE identifier_cur CURSOR FOR
SELECT [identifier]
FROM [dbo].[##moss_update]
OPEN identifier_cur
FETCH NEXT
FROM identifier_cur
INTO @identifier
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @tpleafname AS nvarchar(128)
DECLARE @tpdirname AS nvarchar(256)
DECLARE @tpcreated AS datetime
SELECT @tpleafname = tp_leafname,
@tpdirname = tp_dirname,
@tpcreated = tp_created
FROM [dbo].[##moss_update]
WHERE identifier = @identifier
UPDATE [dbo].[AllUserData]
SET tp_created = @tpcreated
WHERE tp_leafname = @tpleafname
AND tp_dirname = @tpdirname
FETCH NEXT
FROM identifier_cur
INTO @identifier
END
CLOSE identifier_cur
DEALLOCATE identifier_cur
GO

DROP TABLE ##moss_update
GO

sshelper
Posting Yak Master

216 Posts

Posted - 2008-01-10 : 08:39:16

I don't see anything wrong with you script. What you can do is verify that in your [dbo].[AllUserData] table, there are records that match the tp_leafname and tp_dirname as specified in your UPDATE statement.

SQL Server Helper
http://www.sql-server-helper.com

youri.korpel
Starting Member

3 Posts

Posted - 2008-01-10 : 08:50:10

Verified that all columns exist in the [dbo].[AllUserData].
The datatype is correct too.

quote:
Originally posted by sshelper

I don't see anything wrong with you script. What you can do is verify that in your [dbo].[AllUserData] table, there are records that match the tp_leafname and tp_dirname as specified in your UPDATE statement.

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources