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)
 Anyone help me troubleshoot this query plz

Author  Topic 

plismore
Starting Member

6 Posts

Posted - 2009-03-16 : 22:33:02
[code]
declare @tabId int
declare @tabPath ntext
set @tabId = 0
set @tabPath = ''

SELECT @tabId = [TabID], @tabPath = [TabPath]
FROM [freedom_ukr8].[dbo].[testTab]
WHERE TabID
BETWEEN '217' AND '1073'
ORDER BY [TabPath]


INSERT INTO [freedom_ukr8].[dbo].[dnnfusion_mp_Module_MyText]
([UserProfileModuleId]
,[Body])

SELECT UserProfileModuleId,'<a href="http://r8yourpolitician.co.uk/'+ @tabPath + '/tabid/'+ convert(ntext,@tabId) + '/Default.aspx"><img height="105" width="229" src="/Portals/0/ratemeUK.png" alt=""/></a>'
FROM [freedom_ukr8].[dbo].[dnnfusion_mp_UserModules]
WHERE UserProfileModuleId
between 2569 and 3415[/code]

plismore
Starting Member

6 Posts

Posted - 2009-03-16 : 22:33:40
I keep getting hit with this error

Msg 2739, Level 16, State 1, Line 15
The text, ntext, and image data types are invalid for local variables.
Go to Top of Page

plismore
Starting Member

6 Posts

Posted - 2009-03-16 : 22:35:48
ok i changed the datatype of my @tabPath variable and changed the datatype in the cast and now im getting this error

Msg 306, Level 16, State 2, Line 6
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Go to Top of Page

plismore
Starting Member

6 Posts

Posted - 2009-03-16 : 22:40:24
ok i took away the Order BY and the query executed,

problem I have is that my two variables are always the same they did not change as i was hoping they would
Go to Top of Page

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-16 : 23:45:27
SELECT @tabId = [TabID], @tabPath = [TabPath]
FROM [freedom_ukr8].[dbo].[testTab]
WHERE TabID
BETWEEN '217' AND '1073'
ORDER BY [TabPath]


You can't get multiple values in the variables @tabId and @tabPath

i.e in your where clause you must use your Key attribute.
The result set must contain single Row.

SELECT @tabId = [TabID], @tabPath = [TabPath]
FROM [freedom_ukr8].[dbo].[testTab]
WHERE TabID =100


Regards

Senthil.C
Willing to update...
Go to Top of Page

plismore
Starting Member

6 Posts

Posted - 2009-03-16 : 23:49:30
Thanks for the feedback ,

I redesigned it with a cursor but as you will probably guess the query is taking for ever to execute

declare @tabId int
declare @tab2 varchar(1000)
declare @tabPath varchar(1000)
DECLARE @mycur CURSOR
DEclare @pot int
set @pot = 2569
set @tabId = 0
set @tabPath = ''

SET @mycur = CURSOR
FOR
SELECT [TabID],[TabPath]
FROM [freedom_ukr8].[dbo].[testTab]
WHERE TabID >= '217'

OPEN @mycur

FETCH NEXT FROM @mycur INTO @tabId, @tabPath

WHILE @@FETCH_STATUS = 0
BEGIN


set @tab2 = convert(varchar,@tabId)

UPDATE [freedom_ukr8].[dbo].[dnnfusion_mp_Module_MyText]
Set [Body] = '<p><a href="http://r8yourpolitician.co.uk'+ @tabPath + '/tabid/' + @tab2 + '/Default.aspx"><img alt="" src="/Portals/0/ratemeUK.png" /></a>&#160;</p>'
WHERE UserProfileModuleId = (SELECT UserProfileModuleId from [freedom_ukr8].[dbo].[dnnfusion_mp_UserModules]WHERE UserProfileModuleId = @pot)
set @pot = @pot + 1

FETCH NEXT FROM @mycur INTO @tabId, @tabPath
END
DEALLOCATE @mycur
Go to Top of Page

plismore
Starting Member

6 Posts

Posted - 2009-03-17 : 01:36:33
i solved it , i ended up getting all the entrys different but they were not aligned with the right profile, so i went and created a temp table , did an insert into it asc the order and then run this,

declare @tabId int
DECLARE @mycur CURSOR
DECLARE @pot int
set @tabId = 0
set @pot = 2569
SET @mycur = CURSOR
FOR
SELECT * FROM [freedom_ukr8].[dbo].[testTab2]

OPEN @mycur

FETCH NEXT FROM @mycur INTO @tabId

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE [freedom_ukr8].[dbo].[dnnfusion_mp_UserModules]
SET [UserProfileId] = @tabId
WHERE (UserProfileModuleId = @pot)
set @pot = @pot + 1

FETCH NEXT FROM @mycur INTO @tabId
END
DEALLOCATE @mycur
Go to Top of Page
   

- Advertisement -