Drop and re-recreate views

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the store procedure below to drop and recreate all my views.
It works fine if the string is <=4000 characters.
I tried to change data type for @text parameter from nvarchar to ntext but
something is wrong. It occur error

Question.
How can I do to refresh (drop and re-recreate) all views including with
strings upper 4000 characters?

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
 
A better newsgroup to ask this question would be m.p.sqlserver.programming.

text and ntext types cannot be used as a local variable (ie, you cannot
write something like « declare @t text »); hence possibly the error you got.

One way to solve this would be to split the field into multiple variables
and concatenate them:

exec (@t1 + @t2 + ...)

Maybe you could also replace nvarchar(4000) with varchar(8000). If you are
on SQL-Server 2005 instead of 2000, then you could try with nvarchar(max),
see http://msdn2.microsoft.com/en-us/library/ms178158.aspx ; however, I
don't know if you can use these with exec .

Finally, I really don't understand why you want to drop/recreate your views;
maybe there are other ways of achieving what you want to do.
 
I didn't test yet using varchar(8000), I will and I'll give you feed back.

I need to refresh because I don't know why, sometimes the views or sp change
the values between columns (fields).

For example
Column A has the following values: 1;2;3;4
Column B has the following values: A;B;C;D
Column C has the following values: 5;6;7;8

Sometimes, for example, the values of column A goes to column B and B goes
to A.
If I drop and re-recreate this problem is solved.
Do you have any idea why it's happen?

Thanks
jcp


Sometimes the values of the column
5
5
5
--
Jose


Sylvain Lafontaine said:
A better newsgroup to ask this question would be m.p.sqlserver.programming.

text and ntext types cannot be used as a local variable (ie, you cannot
write something like « declare @t text »); hence possibly the error you got.

One way to solve this would be to split the field into multiple variables
and concatenate them:

exec (@t1 + @t2 + ...)

Maybe you could also replace nvarchar(4000) with varchar(8000). If you are
on SQL-Server 2005 instead of 2000, then you could try with nvarchar(max),
see http://msdn2.microsoft.com/en-us/library/ms178158.aspx ; however, I
don't know if you can use these with exec .

Finally, I really don't understand why you want to drop/recreate your views;
maybe there are other ways of achieving what you want to do.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


JCP said:
I'm using the store procedure below to drop and recreate all my views.
It works fine if the string is <=4000 characters.
I tried to change data type for @text parameter from nvarchar to ntext but
something is wrong. It occur error

Question.
How can I do to refresh (drop and re-recreate) all views including with
strings upper 4000 characters?

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
Click to expand...
Click to expand...
 
You will have problems with Views if you change the design of the tables (or
even just reorder their columns?). Any change to the tables?

This is one of the reasons why I don't use views myself.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


JCP said:
I didn't test yet using varchar(8000), I will and I'll give you feed back.

I need to refresh because I don't know why, sometimes the views or sp
change
the values between columns (fields).

For example
Column A has the following values: 1;2;3;4
Column B has the following values: A;B;C;D
Column C has the following values: 5;6;7;8

Sometimes, for example, the values of column A goes to column B and B goes
to A.
If I drop and re-recreate this problem is solved.
Do you have any idea why it's happen?

Thanks
jcp


Sometimes the values of the column
5
5
5
--
Jose


Sylvain Lafontaine said:
A better newsgroup to ask this question would be
m.p.sqlserver.programming.

text and ntext types cannot be used as a local variable (ie, you cannot
write something like « declare @t text »); hence possibly the error you
got.

One way to solve this would be to split the field into multiple variables
and concatenate them:

exec (@t1 + @t2 + ...)

Maybe you could also replace nvarchar(4000) with varchar(8000). If you
are
on SQL-Server 2005 instead of 2000, then you could try with
nvarchar(max),
see http://msdn2.microsoft.com/en-us/library/ms178158.aspx ; however, I
don't know if you can use these with exec .

Finally, I really don't understand why you want to drop/recreate your
views;
maybe there are other ways of achieving what you want to do.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


JCP said:
I'm using the store procedure below to drop and recreate all my views.
It works fine if the string is <=4000 characters.
I tried to change data type for @text parameter from nvarchar to ntext
but
something is wrong. It occur error

Question.
How can I do to refresh (drop and re-recreate) all views including with
strings upper 4000 characters?

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
Click to expand...
Click to expand...
Click to expand...
 
I can't say always when I changed the columns or add columns in tables the
views stay crazy. I can say, some times it happens. What I do, I drop and
re-created with the function that I created.
But now I have 2 views with the string upper 4000 char and it doesn't work
for this views.
So, in my function to drop and re-recreated, I put filter for this views.
But I would like get a solution to avoid this issue.

I tested using varchar(8000) but it doesn't work.
When the string is upper than 4000 char, the dbo.sysobjects table shows one
more row for this view. I think the problem is here.

jcp

--
Jose


Sylvain Lafontaine said:
You will have problems with Views if you change the design of the tables (or
even just reorder their columns?). Any change to the tables?

This is one of the reasons why I don't use views myself.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


JCP said:
I didn't test yet using varchar(8000), I will and I'll give you feed back.

I need to refresh because I don't know why, sometimes the views or sp
change
the values between columns (fields).

For example
Column A has the following values: 1;2;3;4
Column B has the following values: A;B;C;D
Column C has the following values: 5;6;7;8

Sometimes, for example, the values of column A goes to column B and B goes
to A.
If I drop and re-recreate this problem is solved.
Do you have any idea why it's happen?

Thanks
jcp


Sometimes the values of the column
5
5
5
--
Jose


Sylvain Lafontaine said:
A better newsgroup to ask this question would be
m.p.sqlserver.programming.

text and ntext types cannot be used as a local variable (ie, you cannot
write something like « declare @t text »); hence possibly the error you
got.

One way to solve this would be to split the field into multiple variables
and concatenate them:

exec (@t1 + @t2 + ...)

Maybe you could also replace nvarchar(4000) with varchar(8000). If you
are
on SQL-Server 2005 instead of 2000, then you could try with
nvarchar(max),
see http://msdn2.microsoft.com/en-us/library/ms178158.aspx ; however, I
don't know if you can use these with exec .

Finally, I really don't understand why you want to drop/recreate your
views;
maybe there are other ways of achieving what you want to do.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I'm using the store procedure below to drop and recreate all my views.
It works fine if the string is <=4000 characters.
I tried to change data type for @text parameter from nvarchar to ntext
but
something is wrong. It occur error

Question.
How can I do to refresh (drop and re-recreate) all views including with
strings upper 4000 characters?

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top