problems after running the database publishign wizard

  • 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.

  • 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]

  • 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 '

    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 = 'dbo.itaps_RAENS'

    And DATA_TYPE Like '%char'

    Order by ORDINAL_POSITION

    Set @sql = @sql + ' From dbo.itaps_RAENS T'

    Print 'Executing: ' + @sql

  • 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]

  • 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 ','.

  • 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]

  • worked like a charm - thank you!!!!!!!

  • 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