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.
Author |
Topic |
plismore
Starting Member
6 Posts |
Posted - 2009-03-16 : 22:33:02
|
[code]declare @tabId intdeclare @tabPath ntextset @tabId = 0set @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 errorMsg 2739, Level 16, State 1, Line 15The text, ntext, and image data types are invalid for local variables. |
|
|
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 errorMsg 306, Level 16, State 2, Line 6The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. |
|
|
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 |
|
|
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 RegardsSenthil.CWilling to update... |
|
|
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 executedeclare @tabId intdeclare @tab2 varchar(1000)declare @tabPath varchar(1000)DECLARE @mycur CURSORDEclare @pot int set @pot = 2569set @tabId = 0set @tabPath = ''SET @mycur = CURSORFORSELECT [TabID],[TabPath] FROM [freedom_ukr8].[dbo].[testTab]WHERE TabID >= '217'OPEN @mycurFETCH NEXT FROM @mycur INTO @tabId, @tabPathWHILE @@FETCH_STATUS = 0BEGINset @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> </p>' WHERE UserProfileModuleId = (SELECT UserProfileModuleId from [freedom_ukr8].[dbo].[dnnfusion_mp_UserModules]WHERE UserProfileModuleId = @pot)set @pot = @pot + 1FETCH NEXT FROM @mycur INTO @tabId, @tabPathENDDEALLOCATE @mycur |
|
|
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 intDECLARE @mycur CURSORDECLARE @pot intset @tabId = 0set @pot = 2569SET @mycur = CURSORFORSELECT * FROM [freedom_ukr8].[dbo].[testTab2]OPEN @mycurFETCH NEXT FROM @mycur INTO @tabIdWHILE @@FETCH_STATUS = 0BEGINUPDATE [freedom_ukr8].[dbo].[dnnfusion_mp_UserModules] SET [UserProfileId] = @tabIdWHERE (UserProfileModuleId = @pot)set @pot = @pot + 1FETCH NEXT FROM @mycur INTO @tabIdENDDEALLOCATE @mycur |
|
|
|
|
|
|
|