August 19, 2008 at 6:58 am
rbarryyoung (8/18/2008)
Wait a minute,... you aren't using the View Designer, are you?
no i am not using the view designer - not even sure how to get into that. i am just going and opening a table and displaying the SQL window and then pasting the code there.
the SQL server is with my web hosting provider and i connect to it via an SSH tunnel. the server is a sql server 2000 server but because i am runnign vista on my machine i am unable to run enterprise manader (2000), so i am using SQL Server Management Studio Express (2005) to connect to the database from my vista machine.
August 19, 2008 at 8:07 am
Blair Dee (8/19/2008)
rbarryyoung (8/18/2008)
Wait a minute,... you aren't using the View Designer, are you?no i am not using the view designer - not even sure how to get into that. i am just going and opening a table and displaying the SQL window and then pasting the code there.
Same problem, that is not a real query window. It can only accept single SQL statements, nothing else. You need to open a real query window in your database and paste the code in there. You can do this with "New Query..." from the right-click menu on the database, or Ctrl-N from your Open Table window.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 8:33 am
ok so i tried running it from a new query window and i get the following messages:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'max'.
Msg 137, Level 15, State 1, Line 6
Must declare the variable '@sql'.
Msg 137, Level 15, State 2, Line 6
Must declare the variable '@sql'.
Msg 137, Level 15, State 2, Line 15
Must declare the variable '@sql'.
Msg 137, Level 15, State 2, Line 17
Must declare the variable '@sql'.
here is the entire unedited query window that i used:
Declare @sql AS varchar(max)
Set @sql = 'Update T
Set '
+ CASE When ORDINAL_POSITION = 1 Then ' ' Else ', ' End
+ '['+COLUMN_NAME+'] = Replace(['+COLUMN_NAME+'], ''"> <!--'', '''')
'
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHEMA='dbo' and TABLE_NAME = 'dbo.itaps_RAENS'
And DATA_TYPE Like '%char'
Order by ORDINAL_POSITION
Set @sql = @sql + ' From dbo.itaps_RAENS T'
Print 'Executing: ' + @sql
August 19, 2008 at 9:04 am
OK, that's the SQL Server 2000 thing.
Change "varchar(max)" to "varchar(8000)" (and hope it fits).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 9:18 am
well i think were getting closer...
now i get the following:
Executing: Update T
Set , [Title] = Replace([Title], '"> <!--', '')
, [description] = Replace([description], '"> <!--', '')
From dbo.itaps_RAENS T
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
August 19, 2008 at 9:53 am
Try this:
Declare @sql AS varchar(8000)
Set @sql = 'Update T
Set '
Select @sql = @sql
+ '['+COLUMN_NAME+'] = Replace(['+COLUMN_NAME+'], ''"> <!--'', '''')
,'
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHEMA='dbo' and TABLE_NAME = 'itaps_RAENS'
And DATA_TYPE Like '%char'
Order by ORDINAL_POSITION
Set @sql = Substring(@sql, 1, Len(@sql)-1))--remove the last comma
Set @sql = @sql + ' From dbo.itaps_RAENS T'
Print 'Executing: ' + @sql
EXEC (@sql)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 12:20 pm
worked like a charm - thank you!!!!!!!
August 19, 2008 at 12:56 pm
Glad I could help. (eventually) 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply