August 18, 2008 at 11:54 am
so last week i was trying to get soem data from my sql server to one with a different host. long story short, i used the database publishing wizard to generate a script and it seemed to work.
however after running this, i realized tha ton the original server there was soem strange HTML appended to every field in all the tables i had exported. the html in question is as follows...
"> <!--
i checked with my hostign provider and they were only able to go back to last tuesday from tape and that was nto far enough to correct the problem.
so... is there an easy way to create a query or script or somethgin to strip this html out of all the fields??
August 18, 2008 at 4:36 pm
anyone?? any idea how to write somethgin to strip that code out of all the fields in a particular table?
August 18, 2008 at 4:41 pm
Are they really all string fields?
[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 18, 2008 at 4:56 pm
This should work:
Declare @sql varchar(max)
Set @sql = 'Update T
Set '
Select @sql = @sql
+ CASE When ORDINAL_POSITION = 1 Then ' ' Else ', ' End
+ '['+COLUMN_NAME+'] = Replace(['+COLUMN_NAME+'], ''"> <!--'', '''')
'
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHEMA='dbo' and TABLE_NAME = 'FooTable'
Order by ORDINAL_POSITION
Set @sql = @sql + ' From dbo.FooTable T'
Print 'Executing: ' + @sql
EXEC (@sql)
Remember to replace "FooTable" with your actual table name in both places.
[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 18, 2008 at 5:07 pm
rbarryyoung (8/18/2008)
Are they really all string fields?
no not EVERY field is a string - but everyfield that is a string got this weirdness appended to the end of it.
August 18, 2008 at 5:14 pm
rbarryyoung (8/18/2008)
This should work:
Declare @sql varchar(max)
Set @sql = 'Update T
Set '
Select @sql = @sql
+ CASE When ORDINAL_POSITION = 1 Then ' ' Else ', ' End
+ '['+COLUMN_NAME+'] = Replace(['+COLUMN_NAME+'], ''"> <!--'', '''')
'
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHEMA='dbo' and TABLE_NAME = 'FooTable'
Order by ORDINAL_POSITION
Set @sql = @sql + ' From dbo.FooTable T'
Print 'Executing: ' + @sql
EXEC (@sql)
Remember to replace "FooTable" with your actual table name in both places.
I tried to run the script for one of the tables and i got the followign message:
"The Declare cusror SQL contruct or statement is not supported."
if it matters it is a SQL Server 2000 database (should have mentioned that sooner)
August 18, 2008 at 5:16 pm
OK, you might want this modification then:
Declare @sql varchar(max)
Set @sql = 'Update T
Set '
Select @sql = @sql
+ CASE When ORDINAL_POSITION = 1 Then ' ' Else ', ' End
+ '['+COLUMN_NAME+'] = Replace(['+COLUMN_NAME+'], ''"> <!--'', '''')
'
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHEMA='dbo' and TABLE_NAME = 'FooTable'
And DATA_TYPE Like '%char'
Order by ORDINAL_POSITION
Set @sql = @sql + ' From dbo.FooTable 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 18, 2008 at 5:26 pm
i still get the same error saying "The Declare cursot SQL construct or statement is nto supported"
but if goves me the optiopn to continue... so if i click continue it says:
"SQL Execution error:
Executed SQL statement: Select @sql = @sql
+ CASE When ORDINAL_POSITION = 1 Then ' ' Else ', ' End
+ '['+COLUMN_NAME+'] = Replace(['+COLUMN_NAME+'], ''"> <!--'', '''')
'
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHE...
Error Source: .Net SqlClient Data Provider
Error Message: Line 1: Incorrect syntax near 'max'
Must declare the variable '@sql'.
Must declare the variable '@sql'.
Must declare the variable '@sql'.
Must declare the variable '@sql'.
"
August 18, 2008 at 8:27 pm
Blair Dee (8/18/2008)
if it matters it is a SQL Server 2000 database (should have mentioned that sooner)
Yes, it does matter, and yes you should have mentioned that before. In fact you should have posted in one of the SQL 2000 forums instead of this SQL 2005 forum.
Still, it should be fixable...
[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 18, 2008 at 8:29 pm
All you need to do is modify the DECLARE statment:
Declare @sql AS varchar(max)
Set @sql = 'Update T
Set '
Select @sql = @sql
+ CASE When ORDINAL_POSITION = 1 Then ' ' Else ', ' End
+ '['+COLUMN_NAME+'] = Replace(['+COLUMN_NAME+'], ''"> <!--'', '''')
'
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHEMA='dbo' and TABLE_NAME = 'FooTable'
And DATA_TYPE Like '%char'
Order by ORDINAL_POSITION
Set @sql = @sql + ' From dbo.FooTable T'
Print 'Executing: ' + @sql
EXEC (@sql)
I do not have a SQL 2000 system to test on right now, but I think that this should work.
[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 18, 2008 at 9:14 pm
rbarryyoung (8/18/2008)
Blair Dee (8/18/2008)
if it matters it is a SQL Server 2000 database (should have mentioned that sooner)Yes, it does matter, and yes you should have mentioned that before. In fact you should have posted in one of the SQL 2000 forums instead of this SQL 2005 forum.
Still, it should be fixable...
oh sorry about that - i didn't realize i was in the sql server 2005 area.
August 18, 2008 at 9:16 pm
rbarryyoung (8/18/2008)
All you need to do is modify the DECLARE statment:
Declare @sql AS varchar(max)
Set @sql = 'Update T
Set '
Select @sql = @sql
+ CASE When ORDINAL_POSITION = 1 Then ' ' Else ', ' End
+ '['+COLUMN_NAME+'] = Replace(['+COLUMN_NAME+'], ''"> <!--'', '''')
'
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_SCHEMA='dbo' and TABLE_NAME = 'FooTable'
And DATA_TYPE Like '%char'
Order by ORDINAL_POSITION
Set @sql = @sql + ' From dbo.FooTable T'
Print 'Executing: ' + @sql
EXEC (@sql)
I do not have a SQL 2000 system to test on right now, but I think that this should work.
thanks for getting back to me on this but unfortunately i still am getting the same error
August 18, 2008 at 9:36 pm
I don't know what to tell you. If you are using my code verbatim, then there is no way that you should be getting a CURSOR error.
[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 18, 2008 at 9:39 pm
Could you please select your entire query window and post it, unedited here?
[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 18, 2008 at 10:05 pm
Wait a minute,... you aren't using the View Designer, are you?
[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 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply