|
|
You're in the
sql server programming
group:Refresh Views
sql server programming:
It works fine if the string is <=4000 char. I tried to change data type for @text parameter from nvarchar(4000) to varchar(8000) but the error still occur. Question. How can I do to refresh (drop and re-recreate) all views including with strings upper 4000 characters? What is wrong in my store procedure? Procedures ------------------------------------------------------- EXEC "T1_RefreshViews" ------------------------------------------------------- ALTER PROCEDURE T1_RefreshViews AS SET NOCOUNT ON DECLARE @name nvarchar(128) DECLARE @text nvarchar(4000) SELECT dbo.sysobjects.name, dbo.syscomments.text INTO dbo.[#TempObjects] FROM dbo.sysobjects INNER JOIN dbo.syscomments ON dbo.sysobjects.id = dbo.syscomments.id WHERE (dbo.sysobjects.xtype = 'V') AND (dbo.sysobjects.category = 0) ORDER BY dbo.sysobjects.name DECLARE Object_Refresh CURSOR FOR SELECT name,text FROM #TempObjects OPEN Object_Refresh FETCH NEXT FROM Object_Refresh INTO @name,@text WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('DROP VIEW ' + '"' + @name + '"') EXEC (@text) PRINT ('Droped and re-created ' + @name) FETCH NEXT FROM Object_Refresh INTO @name,@text END CLOSE Object_Refresh DEALLOCATE Object_Refresh DROP TABLE #TempObjects ------------------------------------------------------------------------- Thanks for any help jcp --
JCP (JCP@discussions.microsoft.com) writes:
[quoted text, click to view] > I'm using the store procedure below to drop and recreate all my views. > It works fine if the string is <=4000 char. > I tried to change data type for @text parameter from nvarchar(4000) to > varchar(8000) but the error still occur. > > Question. > How can I do to refresh (drop and re-recreate) all views including with > strings upper 4000 characters? > What is wrong in my store procedure? The problem is that if the definition is more than 4000 characters, it is split over several rows in syscomments. But the real question, why do you do this at all? What's wrong with sp_refreshview? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
You want to see the source code for sp_helptext? If so, execute:
USE master GO EXEC sp_helptext 'sp_helptext' -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "JCP" <JCP@discussions.microsoft.com> wrote in message
news:15056FA2-6346-46C6-BE1B-F00C40CEBF84@microsoft.com... > From query analyzer I run exec "sp_helptext" and give the following error: > > Server: Msg 201, Level 16, State 4, Procedure sp_helptext, Line 0 > Procedure 'sp_helptext' expects parameter '@objname', which was not supplied. > > Where can I find this SP? I would like to see the script > > Thanks > jcp > -- > Jose > > > "Shiju Samuel" wrote: > >> >>How can I do to refresh (drop and re-recreate) all views including with >> strings upper 4000 characters? >> >> Your code assumes that syscomments have a single column for each >> objects. >> The syscomments text column is nvarchar(4000) and when the text for of >> a objects goes over 4000 it is put on an another row by incrementing >> colid. >> >> If you do a sp_helptext of sp_helptext you get a code to handle this. >> >> Hope this will help >> >> Thanks >> Shiju Samuel >> >>
JCP (JCP@discussions.microsoft.com) writes:
[quoted text, click to view] > 1. Because if I do modifications in a table, the columns views doesn't > match to columns tables. Or run sp_refreshview. Or stop using SELECT *, which does not belong in [quoted text, click to view] > 2. With this procedure I can store the views in a table (not temp) If I > do a mistake or if I'm testing, from table views I can put the original > views back I have a more radical solution: put your source code under source control. Or at least have it on disk. The database is not the place to have source code, that's just a container for binaries. (Nevermind that the binaries are very similar to the original.) [quoted text, click to view] > 3. I I want encription, from the table where I keep de views, I can drop > the existing views and create new encrypted views. Which is a lot easier to do if you read from disk. As I said in my initial post, you run into problem when the view definition is over more than 4000 characters, because it extends over several rows in syscomments. There is not really any way in SQL 2000 to extract that definition and reexecute it using T-SQL. In SQL 2005 it is possible, thanks to the new MAX data types. However, it's still much better to store your source on disk or in a version-control system. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
1. Because if I do modifications in a table, the columns views doesn't match
to columns tables. If I drop and re-creat with the SP resolve this issue. 2. With this procedure I can store the views in a table (not temp) If I do a mistake or if I'm testing, from table views I can put the original views back 3. I I want encription, from the table where I keep de views, I can drop the existing views and create new encrypted views. Probably, already exist a sp to do this, but I don't know. Do you know any solution to resolve my questions? I would like to do the same for functions and SP Thanks -- Jose [quoted text, click to view] "Erland Sommarskog" wrote:
> JCP (JCP@discussions.microsoft.com) writes: > > I'm using the store procedure below to drop and recreate all my views. > > It works fine if the string is <=4000 char. > > I tried to change data type for @text parameter from nvarchar(4000) to > > varchar(8000) but the error still occur. > > > > Question. > > How can I do to refresh (drop and re-recreate) all views including with > > strings upper 4000 characters? > > What is wrong in my store procedure? > > The problem is that if the definition is more than 4000 characters, it > is split over several rows in syscomments. > > But the real question, why do you do this at all? What's wrong with > sp_refreshview? > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[quoted text, click to view]
>>How can I do to refresh (drop and re-recreate) all views including with strings upper 4000 characters?Your code assumes that syscomments have a single column for each objects. The syscomments text column is nvarchar(4000) and when the text for of a objects goes over 4000 it is put on an another row by incrementing colid. If you do a sp_helptext of sp_helptext you get a code to handle this. Hope this will help Thanks Shiju Samuel
From query analyzer I run exec "sp_helptext" and give the following error:
Server: Msg 201, Level 16, State 4, Procedure sp_helptext, Line 0 Procedure 'sp_helptext' expects parameter '@objname', which was not supplied. Where can I find this SP? I would like to see the script Thanks jcp -- Jose [quoted text, click to view] "Shiju Samuel" wrote:
> >>How can I do to refresh (drop and re-recreate) all views including with > strings upper 4000 characters? > > Your code assumes that syscomments have a single column for each > objects. > The syscomments text column is nvarchar(4000) and when the text for of > a objects goes over 4000 it is put on an another row by incrementing > colid. > > If you do a sp_helptext of sp_helptext you get a code to handle this. > > Hope this will help > > Thanks > Shiju Samuel >
The sp_helptext system procedure is located in the master database. You can
find more information on this and other system procedures in Books Online. ML --- Matija Lah, SQL Server MVP
LOL! That was too obvious, even for me. :)
ML --- Matija Lah, SQL Server MVP
[quoted text, click to view]
On Aug 31, 4:02 am, JCP <J...@discussions.microsoft.com> wrote: > 1. Because if I do modifications in a table, the columns views doesn't match > to columns tables. > If I drop and re-creat with the SP resolve this issue. > > 2. With this procedure I can store the views in a table (not temp) If I do a > mistake or if I'm testing, from table views I can put the original views back > > 3. I I want encription, from the table where I keep de views, I can drop the > existing views and create new encrypted views. > > Probably, already exist a sp to do this, but I don't know. > Do you know any solution to resolve my questions? > > I would like to do the same for functions and SP > Thanks > > -- > Jose > > "Erland Sommarskog" wrote: > > JCP (J...@discussions.microsoft.com) writes: > > > I'm using the store procedure below to drop and recreate all my views. > > > It works fine if the string is <=4000 char. > > > I tried to change data type for @text parameter from nvarchar(4000) to > > > varchar(8000) but the error still occur. > > > > Question. > > > How can I do to refresh (drop and re-recreate) all views including with > > > strings upper 4000 characters? > > > What is wrong in my store procedure? > > > The problem is that if the definition is more than 4000 characters, it > > is split over several rows in syscomments. > > > But the real question, why do you do this at all? What's wrong with > > sp_refreshview? > > > -- > > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se > > > Books Online for SQL Server 2005 at > >http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > > Books Online for SQL Server 2000 at > >http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Jose, Let me repeat Erland's question: What's wrong with sp_refreshview? Did you try it? Alex Kuznetsov, SQL Server MVP http://sqlserver-tips.blogspot.com/
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |