Solved

MS SQL creatin a view

Posted on 2011-03-08
3
481 Views
Last Modified: 2012-05-11
I am creating a view that must be updated several times during the month. Right now I have 3 SQL queries to accomplish this.

1st Query    DROP VIEW  {view name}
 2nd            CREATE VIEW (view name) as
                   SELECT  (Several Columns)

3rd             Exec {database name}_grant_permissions
 -------------------------------------------------------------------------------------
How would I combine into 1 SQL Query to run
0
Comment
Question by:fbhunt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 30 total points
ID: 35069222
well, you can combine the drop/create by doing

alter view <viewname> as
select <...>

the exec would have to be separate

unless you want to put it all in a stored procedure, then you could just put all 3 statements inside it and execute it with "exec sp_Your_Proc"
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 65 total points
ID: 35069241
if the grant permissions is just to regrant the permissions on the view because it had been dropped, do indeed the ALTER VIEW instead, it will retain the permissions
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 30 total points
ID: 35069246
>>I am creating a view that must be updated several times during the month. <<
Which of course begs the question as to why you are doing this.  This sounds like a design problem that should be addressed.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question