View With Read Only Option ?
Oct 12, 2004Hi,
Is there anyway to create view with read only option as in Oracle ? (OR) is there any
workaround for the same ?
Please advice,
Thanks,
Sam
ADVERTISEMENT
SQL 2012 :: Catalog Doesn't Have Option To Give Read Access To SSIS Catalog To View Package Run Reports
Oct 23, 2014"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
Select With (nolock) OR TransactionScope With Option Read Uncommitted Data
May 29, 2007Hi Sql gurus :))I've got a question that I couldn't find a satisfying answer on the net.What is the difference between:1) running sql query (select from sth with nolock) with no transaction2) running sql query (select from sth) withing a TransactionScope with option Read Uncommitted dataBasically, both should do the same work. However is anyone aware of any potential problems using any of both approaches ?We use 1) to improve our web application scalability since the system works in such a way that any selects and updates on that table (sth) do not interfere with one another.However, updates are done in a TransactionScope. And when having simultaneous select with nolock and update in a Transaction scope (the select statement has a where clause and returns records that are not updated by the update statement). However sometimes ( we still cannot figure it out when) the select statement returns some records twice.For example, the select should return 1000 records , but (sometimes) it returns 1002 records ( the extra 2 records are copies of some of the original 1000 records).Removing the nolock, makes the problem does not appear - but i want to be 100% sure that nolock is our troublemaker. And if it is - why ?We also have a problem that this particular nolock select sometimes return even less records than it should.I know it sounds impossible but it happens.So anyone who has experience with select with nolock, please share :)Thanks in advance, Yani
View 4 Replies View RelatedWhich Is Better Option From Performance Point Of View?
Jun 29, 2007 hi all,
if i want to join two tables and populate data into an oldb destination which option will be better
from performance point of view?
1. using look up transformation
2.writing join query in oldb source using sql commmand option.
'Open View' Option Does Not Sort Records According To ORDER BY
May 26, 2008Hi,
I have few views in SQL Server 2005. In Design View, the results of View are ok. In OPEN VIEW option, records are not sorted correctly, ORDER BY is ignored.
What could be the reason for this ?
Thanks a lot in advance!
Read Only Cells In A Simple View
Feb 12, 2007Mikel Arzak writes "Hi,
I have a DB migrated from SQL Server 2000 to SQL Server 2005 and I
have a strange problem that I don't find any reason.
I make a simple SQL Query with one table showing all the fields and
everything goes well. But when I insert another auxiliar table and
showing one field, then I can't change any field of the main table.
SQL Server shows me the message Read Only Cell. Why this happens? This problem didn't happen in SQL Server 2000.
The select sentence that works:
SELECT Notas_Estructura.* FROM Notas_Estructura
The previous Select sentence modified that doesn't work:
SELECT Notas_Estructura.*, Alumnos.Apellido1, Alumnos.Apellido2,
Alumnos.Nombre FROM Notas_Estructura INNER JOIN Alumnos ON Notas_Estructura.CodAlumno = Alumnos.CodAlumno
Thanks for your help."
How To Read Select Statement From A View?
Oct 18, 2007Hello,
I want to write a t-sql script, that reads the select statement from
an existing view.
How can I get access to the select-statement in the view definition?
Help!
Thanks a lot.
Best Regards
Gerhard
User Permissions - Read Only With Create View
Jan 11, 2008I need to create a new login with SELECT rights so the users can view all tables with no UPDATE, DELETE, OR INSERT rights. But this user needs to be able to CREATE VIEWS. I have assigned the user to the Public role and gone in and modified Securables for the Database to be able to CREATE VIEW. When I connect using my new user and try to create a view, I get the error message: CREATE VIEW permissions denied in database 'test01'.
Help Please
TMDavis
Im New To Using Views, And This Program They Have Me Working On Is Using Them Quite Heavily..(cant Read From A View)
Oct 11, 2007This is my first post, so if i have not posted things in the best manner please lemme know how to be more informative,clear, so that i can learn.
So i have made a view with the following command
GO
/****** Object: View [dbo].[AppraisalView_C] Script Date: 10/11/2007 12:10:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AppraisalView_C]
AS
SELECT a.Counter
,a.DateCreated
,a.DateModified
,a.UserCreated
,a.UserModified
,a.AppraisalDate_C
,a.TypeID_C
,a.Customer_C
,a.Employee_C
,b.Notes_C
,b.Value_C
,b.AppraisalLineItemID_C
,b.AppraisalID_C
FROM dbo.Appraisal_C AS a
INNER JOIN dbo.AppraisalLineItem_C AS b ON a.AppraisalID_C = b.AppraisalID_C
---------------------------------------------------------------------------------------
the program im working on creates the SQL call to read from this view and creates
the following query
SELECT A.[AppraisalDate_C], A.[AppraisalID_C], A.[AppraisalLineItemID_C], A.[Customer_C], A.[Employee_C], A.[Notes_C], A.[TypeID_C], A.[Value_C]
FROM AppraisalView_C A
WHERE [AppraisalView_C].[AppraisalID_C] = 'APP-000006'
but I end up getting the dreaded "Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "AppraisalView_C.AppraisalID_C" could not be bound." error....
I cant change the Query that is called, but i can change the view, what is wrong?
In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing
Apr 27, 2008Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies View RelatedSQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option
Feb 10, 2015So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
Transact SQL :: Making Server Database Read / Write From Read Only
Jan 12, 2012i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.
View 11 Replies View RelatedSQL 2012 :: Identify Whether Files Are In Read / Write Or Read Only
Mar 24, 2015How to identify whether the files are in read write or read only?
View 1 Replies View RelatedRecovery :: Switch (Standby / Read-Only) DB To Be Read And Write
Aug 26, 2015I'm trying to do Sharepoint DR with Log Shipping and every thing configured except one thing which is switch the WSS_Content (Standby /Read-Only) DB to be ready and Write.
I tried from
GUI or ALTER DATABASE [WSS_Content] SET
READ_WRITE WITH NO_WAIT
but I received the below error:
Database WSS_Content is in Warm Standby
Reset Database Files From Read-only To Read-write
Jan 18, 2008I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1
Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
thanks
Read Text File From SQL Server, Read Its Content, And Load It In RichTextBox (Related Component: Context.Response.BinaryWrite(), And StreamReader)
Nov 26, 2007OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
SQL 2012 :: Read Committed For Read Only Database?
Jun 27, 2014i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.
we do daily population of some table in some other databases from this daily refreshed DB.
will read uncommitted or NOLOCK with select queries to retrieve data faster.
there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?
SQL 2012 :: Change Read-only And Read-write
Aug 15, 2014Can a user of db owner role of a database change the databse option to read only and read-write?If not what permission I need to grant to the user?
View 1 Replies View RelatedSet READ UNCOMMITTED (dirty Read) At Login.
Jul 23, 2005Is it possible to set READ UNCOMMITTED to a user connecting to an SQL2000 server instance? I understand this can be done via a front endapplication. But what I am looking to do is to assign this to aspecific user when they login to the server via any entry application.Can this be set with a trigger?
View 1 Replies View RelatedCome On SQLdatareader....read....read....read...ok Help.
Mar 12, 2004OK, I'm using VS2003 and I'm having trouble. The page works perfectly when I created it with WebMatrix but I want to learn more about creating code behind pages and this page doesn't work. I think it has some things to do with Query builder but I can't seem to get change outside "please register". I have the table populated and it is not coming back with "login successful" or "password wrong" when I've entered correct information. Enclosed is what I've done in VS2003. Can you see where my error is? Any help would be greatly appreciated.
Thanks again.
Imports System.data.sqlclient
Imports System.Data
Public Class login2
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "server=LAWORKSTATION;user id=sa;database=test;password=t3st"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"
Me.SqlCommand1.Connection = Me.SqlConnection1
End Sub
Protected WithEvents lblUsername As System.Web.UI.WebControls.Label
Protected WithEvents lblPassword As System.Web.UI.WebControls.Label
Protected WithEvents txtUsername As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlConnection1.Open()
Dim dr As SqlDataReader = SqlCommand1.ExecuteReader
If dr.Read() Then
If dr("password").ToString = txtPassword.Text Then
lblMessage.Text = "login successful"
Else
lblMessage.Text = "Wrong password"
End If
Else
lblMessage.Text = "Please register"
End If
dr.Close()
SqlConnection1.Close()
End Sub
End Class
Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?
Jul 23, 2005I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns
Jul 24, 2012Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Calling A Stored Procedure From A View OR Creating A #tempTable In A View
Aug 24, 2007Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
Different Query Plans For View And View Definition Statement
Mar 9, 2006I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedAlter View / Create View
Aug 14, 2000I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
Updating My View Changes My View Content
Feb 17, 2006I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
Which Would Be The Best Option?
Apr 28, 2008I have a table name “StringResources? which contains resources for different cultures. Right now, whenever admin adds any new resource, it immediately available to end user. Now the new requirement came up. We want admin to add resource first and when he is ready with all the resources for particular culture then only the resources should be available to end user.
Important: StringResource table has SQLCacheDependecy set. So any change for particular culture will invalidate the cache. This how the select statement looks.SELECT dbo.StringResources.resourceType, dbo.StringResources.cultureCode, dbo.StringResources.resourceKey, dbo.StringResources.resourceValue FROM dbo.StringResources
WHERE dbo.StringResources.cultureCode=@cultureCode
Which would be the best option below:
1> Add new Boolean column “Published? and show only resources which are published to end user.
Advantage: No need of extra table.
Disadvantage: This will invalidate the cache every time resource is added even if it’s not published.
Other option
2> Add new temporary table with same structure. When admin add new resource, add it to this temp table and when publish move resources to String Resources table.
Advantage: Admin will have separate working space. It will invalidate cache only when resources are published.
Disadvantage: Need extra table.
An ALL Option
Sep 9, 2006How can I get an All option into the cascading prompt?
I want to view data for all states in USA. I find I can't proceed to load report unless I have filled in a value for all prompts. Any help?
Thanks
AWE OPTION
May 19, 2008I have a small doubt.
If we enable AWE option we can have advantage of available physical memory.we can have more memory by using max server memory property.
then why this AWE option comes in to picture.
I read bol but iam not able to understand what exactly happens.
Could any one tell me why this AWE option if we have max server memory property.
Option (RECOMPILE)
Oct 25, 2007Hi,
What is equivalent to OPTION (RECOMPILE) in SQl Server 2000.
Create table #Employee
(
EmpId int IDENTITY,EmpName varchar(30)
)
insert into #Employee(EmpName )
select EmpName from AllEmployees
OPTION (RECOMPILE)
Best Option To Update Every Jan 1st
Feb 27, 2006I have a vacation request app I'm designing, and it has a VacationData Table with TotalVac, UsedVac, VacLeft, VacationCarriedOver, and VacCompleted.
I need to take the VacLeft and divide by 2 and place that data in the following two spots, one in the VCO and add it to the TotalVac, which is pulled from another table w/hire date and other info.
I only need to run this on Jan 1 of every year, any suggestions?
Lock Option
Jul 12, 2001I was running a query and it didn't execute stating you have run out of locks
How could I set the locks
thanks
Sejal
Not For Replication Option
Oct 14, 1999I am trying to alter a table that has an identity field to make the column have the not for replication option. I just can't seem to get the syntax down for the alter table command. PLEASE HELP. I know it can't be that hard!!!
View 2 Replies View Related