locked
Convert varchar(50) to varchar(255) RRS feed

  • Question

  • Hello All,

    Sorry in advance if I'm posting a question that has already been answered elsewhere, however, googling around has not provided much information. I've looked at several articles ( https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c3e8a7a8-36a7-4d09-9661-1d8ff0870e05/convert-varchar255-to-varchar50?forum=transactsql , https://stackoverflow.com/questions/2695994/varchar255-to-varcharmax being among them) however they don't seem to apply to my problem.  Basically I have a View that has a column named DefaultLocation. When I go into SSMS and look at the view (Object Explorer > Databases > DatabaseName > Views > dbo.ViewName > Columns > DefaultLocation) it shows DefaultLocation(varchar(50),null). Also, when I look at the column (in SSMS) in which the DefaultLocation is stored, it shows Table.Name to be varchar(255). Anyroad, I need DefaultLocation to be varchar(255) or something bigger than 255. I encountered a similar situation a week or so ago except it was the precision for a column being returned and I used CONVERT(DECIMAL(30,15), <expression>) however, I'm not sure how to do something similar in this case. I've tried both CONVERT and CAST(Table.Name as DefaultLocation) to no avail. Any help would be much appreciated.

    Wednesday, October 31, 2018 2:05 PM

Answers

  • Okay, Ramesh Babu Vavilla, I was able to fix the problem by reading this article: https://www.mssqltips.com/sqlservertip/1427/table-changes-not-automatically-reflected-in-a-sql-server-view/ and realizing that I had to re-run the ALTER View code in order to refresh view and bring in the table changes that were on the server. Now my view shows the correct precision varchar(255). Thank you for your help - without it, I would not have realized that it was a refreshing issue. Kind regards, Jonathan

    J Carlisle

    • Marked as answer by GriffManVB Wednesday, October 31, 2018 3:49 PM
    Wednesday, October 31, 2018 3:49 PM

All replies

  • SQL Server views are not automatically updated when the underlying table is changed.

    Please see:

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshview-transact-sql?view=sql-server-2017

    Wednesday, October 31, 2018 2:31 PM
  • @Ramesh Babu Vavilla. Thank you for your response. I tried running

    //USE AdventureWorks2012; //GO EXECUTE sp_refreshview 'MyViewName';

    In LinqPad and I get

    ) or end of expression expected (change the Query Language to 'C# Statements' for statement-based queries)

    Any idea what I'm doing wrong? the Query Language in linqpad is SQL and my connection is set to the correct DB.


    J Carlisle

    Wednesday, October 31, 2018 2:51 PM
  • Okay, Ramesh Babu Vavilla, I was able to fix the problem by reading this article: https://www.mssqltips.com/sqlservertip/1427/table-changes-not-automatically-reflected-in-a-sql-server-view/ and realizing that I had to re-run the ALTER View code in order to refresh view and bring in the table changes that were on the server. Now my view shows the correct precision varchar(255). Thank you for your help - without it, I would not have realized that it was a refreshing issue. Kind regards, Jonathan

    J Carlisle

    • Marked as answer by GriffManVB Wednesday, October 31, 2018 3:49 PM
    Wednesday, October 31, 2018 3:49 PM