Strange Issue With SQL Table Read Only In Access
Apr 21, 2006
I have a data file that I converted from Access to SQL Server using the
upsizing wizard. I then attached the tables to the Access front end using an
ODBC connection. I have one table that is read only for some reason. I'm
not sure how that happened, it went through the same procedures as the other
tables in terms of upsizing and connecting. The original table was not read
only or hidden or anything else, it had full permissions.
I've tried disconnecting and reconnecting the table but still the same
thing. In SQL Server, the permissions are the same as all the other tables.
I can't seem to figure out what's making this one read only.
I'm using SQL Server 2005 Express and Access 2003. Any ideas would be
greatly appreciated.
Thanks!
View 11 Replies
ADVERTISEMENT
Sep 5, 2007
Hi,
We currently use an access database to manipulate data in tables on a SQL server. The end user copies a list of ProjNo from a spreadsheet into an Access table. The Access table is used as the criteria for 5 Access queries. It is common that one of the queries does not get run.
I figured I would create a Storied Procedure on the SQL server, call the storied procedure from MS Access using the MS Access table as the criteria to run the Storied Procedure. I do not know how to provide the Storied Procedure the ProjNo in the MS Access table. The number of ProjNo records in that table can vary.
I've found info on how to add one record at a time but I need a more of a batch process approach.
Can you point me in the right direction?
Thanks,
Mary
View 5 Replies
View Related
Apr 7, 2015
How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.
View 1 Replies
View Related
Feb 27, 2002
Strange MS Access behaviour! URGENT please!!
We have a view looking at all the columns of a table on a SQL server 7.0 db. When we link this view to MSAccess, we are seeing the old data which was there previously in the original table! not the latest data. We recompiled the table and views and also re-linked the view to MSAccess with no help,we still see the old data only.This is happening only for one of the views not for all.Any help???
sa.
View 2 Replies
View Related
Sep 7, 2007
I have three machine:
S: Running SQL Server Express
V: Running SSIS package in VS.Net
F: Shared folder host excel files
And an openrowset SQL statement: select * from openrowset(..... \Fexcel.xls....). This statement can be run in SS management studio connecting to S using my Windows logon(integration security) without any problem.
However, the same SQL running inside SSIS package (integration security using my Windows account) get the following error:
Error: 0x0 at Check headers: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine cannot open the file '\Fexcel.xls'. It is already opened exclusively by another user, or you need permission to view its data.".
Error: 0xC002F210 at Check headers, Execute SQL Task: Executing the query "....openrowset....." failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(My Windows account is administrator of Windows and sysadmin or SQL Sever Express on S)
View 1 Replies
View Related
Aug 29, 2006
I have an existing MS SQL 2005 server which is used to maintain data that has been collected from various sources around a plant. A new system has been added on our network where the data is provided in a daily MS Access database. The name of the MS Access database changes every day.
I would like to use the job manager to run a Stored Proc every night to extract the daily data from the MS Access database. Is there any way to do this.
Regards, Eric
View 1 Replies
View Related
Jan 29, 2001
I have SQL Server 7 database & I need to create some interface between users & database to let users see what is in the database, but not let them change or destroy data. Do I need to create some other database in-between users & the original database? Users can't program & want access to all data.management doesn't want to create a replication of the database. They want to let users have access through MS Word or Access. What could you advise me?
View 1 Replies
View Related
Jan 18, 2008
Hi,
How can I provide a user read only access to all the databases on the server. I have 15 databases on the server. I know I can give db_datareader access in each database individually but that is time consuming and I have 10 servers for this to be done.
thanks in advance.
View 3 Replies
View Related
May 31, 2007
The error comes up like this -
System.Data.SqlClient.SqlException .... because the database is read-only
( I'm putting this here so searches can find it easier)
I got this idea from a related thread, but they used a NETWORK user that is obsolete in 2007.
I found that you have to edit the actual file properties on the ASPNETDB.MDF and ASPNETDB.LDF files so that the ASPUSER has full access. i.e. on the file properties, security tab, hit the Add button and type in ASP.NET then return. Back at the security properties, click on the 'Full Control' checkbox at the top. That took care of it for me.
This was after checking the IIS control panel for the properties on the folder that contained my application and App_Data folders to be sure that the "Write" checkbox was already checked, it was
Of course this assumes that you have installed the express version of SQL from Microsoft
View 2 Replies
View Related
Feb 5, 2008
Hello,
We have SQL Server 2005 with 300+ databases in it. It was set up by the conractor. How can I create new account with read-only access to all databases? Is there any "quick" method of doing it (like in Sybase, it is very easy). I've created new login, but I do not see where can I set up the permissions properly. Thanks
View 8 Replies
View Related
Mar 31, 2008
Hi guys,
I want to give someone read only permission to our SQL server so they can log in and look at the database structure and query a few tables. I've set up the user but the problem is that when I connect in SQL server remotley, it shows all the other DB's on the server even though it is restricting access to them. Can these other DB's be hidden so that only the DB the user has access to shows up?
Cheers for your help!
View 9 Replies
View Related
Dec 6, 2006
I have a set of comma separated variables in a Script Component list. I want to access them in Script code and use them to build string in the code.
View 9 Replies
View Related
Jun 29, 2007
Sorry for the simple question, but I'm not having any luck getting this to work: I need to give someone read-only access to everything in a database.
If someone could help me by giving me (or linking me to) a step-by-step, idiot-proof recipe for giving Bob in my office read-only access to the whole database, I'd be very grateful. And remember: I'm good at db programming, but completely ignorant on the subject of db admin.
View 3 Replies
View Related
Apr 24, 2006
I have a table. It shows "Rows: 2" when I open it in the table properties dialog. But there is no record in the table when I open it from Open Table -> Return All Rows.
I don't understand how this can happen. Any help is appreciated.
Thanks.
View 6 Replies
View Related
Apr 24, 2006
I have a table. It shows "Rows: 2" when I open it in the table properties dialog. But there is no record in the table when I open it from Open Table -> Return All Rows.
I don't understand how this can happen. Any help is appreciated.
Thanks.
View 3 Replies
View Related
Jul 14, 2007
Hi,
I have a strange problem with a sql table.
One of my table, dbo.Customer, gives me different values of "row count" everytime I check the properties.
The dbo.Customer have around 12 000 rows but when I check the properties (or open the table) the value always differs. The table row count can have any value from ~9000 - 12000.
Its like the table are trying to load all rows but cant.
Anyone have any idea what the problem can be?
View 1 Replies
View Related
Feb 13, 2006
Hi there,
I have successfully installed SQL Server Express.
I have copied NorthWind to SQL Server.
I have created an ODBC to the SQL NorthWind.
But my problem is, I open the data but I cannot edit or insert records
to the Customer table.
I have gone into SQL Management Studio and modified Permissions
on NorthWind and the Customer table with Alter, Insert, Update.
But no luck.
Has anyone experienced this?
Email me at justintoronto@hotmail.com if you have a solution.
I will try to check back here also.
Thanks,
Justin
View 1 Replies
View Related
Nov 25, 2015
I am facing a very weird issue in our SSAS database. We have several roles with 'Read definition' access. None of these roles are able to see data in the cubes. I have checked these roles over and over and there is no problem with the definition. Each of the roles have been given read access to Data Source, Cube and Dimensions.
The users are able to access the definition and structure of the cube i.e. they can see the measures & dimensions available but when they drag measures and dimension attributes in the browser (SSMS) OR execute an MDX - they get null values. The roles with 'Full Control (administrator)' access are able to see all the data without any issues.
I have tried the following:
1. Deleted all roles and re-created.
2. Created roles directly on SSAS DB.
3. Deployed all objects and processed the DB.
Each time only the admins are able to see the data.
View 7 Replies
View Related
Nov 17, 2011
Any easy way for a batch file or automated process to read from one db and table and what ever entry is missing out of another database + table it writes those missing entries to.
This is a simple table in one db that is filled with usernames, I want to see if there are missing usernames in another db and table and write those entries.
db1.usr_table.usr_name = jdoenew
If jdoenew is missing in the 2nd db I will need to write entries like:
db1.usr_table.usr_name = jdoenew
db1.usr_table.password = tmppassword
db1.usr_table.active = 1
View 1 Replies
View Related
Jan 18, 2007
Try a little experiment. Partly to humor me, and make me believe I am not quite insane.
Step 1: Install SQL server 2000, such that the data files are not in the default location, but in a location with a shorter path (i.e. install the data files to E:MSSQL8).
Step 2: Run the following queries, and comment on any oddities:
select filename
from master..sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename)), filename
from sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename))
from sysaltfiles
where dbid = 2
I am guessing that #2 is some sort of odd effect caused by the fixed length data field, but I just want to make sure that other people get this oddity, and not just me. I have no idea what could be causing the third output...or perhaps the lack of it.
View 2 Replies
View Related
Nov 29, 2005
Hi
I've got a database in SQL Server 2000 on Windows Server 2003 which appears to be okay from SQL Enterprise Manager and from SQL Query Analyser but which has a problem with one particular table when connected to from a VB DLL using ADO. The queries do either a select or an insert - the latter is via a stored procedure. Once the failure happens, any other queries generate a -2147217871[Microsoft][ODBC SQL Server Driver]Timeout expired.
If I delete the table and recreate it all is well. If I run an integrity check nothing shows up. I've tried deleting the indexes and that made no difference either. SQL Server is patched up to SP4 and ADO is from the MDAC 2.8. VB is version 6 Enterprise with SP6. None of the other databases in the instance exhibit the problem and they're all structured in an identical way. The table is quite large but we've got much larger one's using the same procedures and they don't have a problem. Also, if I restore a backup of the problem database onto another machine the problem shows on the other machine too.
Any ideas anyone?
View 2 Replies
View Related
Feb 19, 2008
Hi,
I am working for my client on SQL 2005 Enterprise & observed a strange behaviour. Consider a table(EMPTABLE) as below:
PK, INT
VARCHAR
EMPID
EMPNAME
1
A
2
B
3
C
4
D
5
E
6
F
7
G
8
H
9
I
10
J
Now, I wanted the 5th highest EMPID from 2 from the table. Now, we know that the SQL
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 should (from all other possibilities) return me the desired value as the last row of the output table. I tried to get this in a derived table & get the MAX value i.e.
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2
) der
But this returns me 10, when I would've thought 7 was the expected answer
I remodified my query as mentioned below to strangely get 7 as the result...
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 ORDER BY EMPID ASC
) der
How did the ORDER BY hep it???
Also, if I use
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >2
) der
I get the same result without having to specify the ORDER BY in my derived TABLE...
I tried looking for similar threads. But nothing could explain this to me...
If anyone knows what I am not able to understand, please let me know.
View 6 Replies
View Related
Mar 23, 2015
We log ship our production database tansaction logs to a secondary database on a separate remote domain.
Incidentally, this was not set up via the "Ship Transaction Logs" wizard, but instead we had to use the various system stored procedures after restoring a copy of the database on the remote domain in standby mode.
We now have an issue where users are unable to access the remote secondary database without having sysadmin privileges. Clearly we want to avoid this!
We want to be able to use Windows Authentication mode on the server of the secondary database. But since the two domains differ, is it possible to even achieve this?
How can we set up read-only access for users on the secondary database without giving them sysadmin privileges?
View 3 Replies
View Related
Sep 15, 2015
I have 3 servers taking part in an AlwaysOn AG.
I want to grant a user READ access to only one replica, and certainly not to the principle.
How do I go about doing this?
View 4 Replies
View Related
Jan 17, 2008
Hello, I am trying to configure a Script Component as a data source. Although this should be a simple exercise, I am running into a problem.
My control flow contains a Foreach Loop with a file iterator. The Directory Expression of the Foreach Loop Editor is supplied by an expression mapped to a package level variable called inputdirectory. The FileNameRetrieval Expression is mapped to a package scoped variable called filename.
My data flow is encapsulated by the Foreach Loop, and contains a Script Component as Source for the Data Flow Source, and a Flat File for the Data Flow Destination. The contents of the Script Designer are as follows:
Code Block
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
Inherits UserComponent
Dim thisFileDate As Date
Dim thisFileName As String
Dim thisFilePath As String
Public Overrides Sub CreateNewOutputRows()
thisFileName = ReadOnlyVariables("filename").Value.ToString()
thisFilePath = ReadOnlyVariables("inputdirectory").Value.ToString()
thisFileDate = File.GetCreationTime(thisFilePath & "" & thisFileName)
FileSpecBuffer.AddRow()
FileSpecBuffer.FileName = thisFileName
FileSpecBuffer.FullPath = thisFilePath & "" & thisFileName
FileSpecBuffer.CreateDate = thisFileDate
End Sub
End Class
When I debug the package, I get the following error:
The collection of variables locked for read access is not available at this point.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadOnlyVariables()
at ScriptComponent_67311120e6eb4162a3ea1f70847f04de.ScriptMain.CreateNewOutputRows()
at ScriptComponent_67311120e6eb4162a3ea1f70847f04de.UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
My googlefu fails me at reconciling this. I have read various posts about changing the line ReadOnlyVariables("filename").Value to ReadWriteVariables("filename").Value, and handling the buffer assignment in PostExecute. The problem is that all examples shown are either for Script Component as Destination or Script Component as Transformation. I have tried playing with the Custom Properties in the Script Commponent set the ReadOnlyVariables and ReadWriteVariables, using a PreExecute method, a PostExecute method, all with different errors returning. I'm at a loss here. Could anybody provide me with a simple working example so that I can correctly populate my output buffer in the context of Script Component as Source?
I fully understand that I could just run a Script Task Using System.IO.Directory, and System.IO.File, but I really want to make this package work in the manner I've described. Any help would be appreciated.
Thanks,
John T
View 5 Replies
View Related
Feb 12, 2008
Hey ,
I have sql server 2000, and i am using Microsoft Application block.
I have Transaction Like...
SqlConnection conn = new SqlConnection(ASLConnectionString); conn.Open(); SqlTransaction Trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
But If i have another process that try to read the Table if that old transaction running.. it wouldn't Allow me to read the DATA.
i tried Isolation level "snapshot" but that works under sql server 2005 only. so i can not use that. And i have some services that will run during day time.
So if anybody have solution to read the data from table...from sql server 2000 even if another transaction is running?
Please Give any guideline..Help will be appriciated.
thanks & regards,
Nirav Patel
View 1 Replies
View Related
Aug 9, 2004
Hello,
I was wondering how to make a table read-only. I want to make a script that creates a table, adds some data to it, then makes the table read-only to prevent someone from accidentally changing the values.
Thanks to anyone who can help,
Chris
View 2 Replies
View Related
May 11, 2004
Hi
I need a SP to read table A (1000 records) and updates Table B. I think I have to use Cursor, but I'm not very good at writing SP. Any help would be appreciated. Thanks.
View 2 Replies
View Related
Oct 17, 2001
Hi, we're trying to read from a table and write back to the same table and are having a lot of trouble with blocking. What could we do to prevent our application from hanging due to blocking of this type?
View 1 Replies
View Related
Dec 7, 2014
I need to read a db Table but sorted by index - is there a generic "select * from tableA sorted by index" where it just uses whatever index it finds (main index) or do i have to name the index?
View 2 Replies
View Related
Apr 7, 2004
Is it possible to make a table (or set of tables) within a SQL Server database read only (still granting the db_datawriter fixed database role to database users) without dropping and recreating the db_datawriter database role?
View 1 Replies
View Related
May 9, 2008
hi
i want read table name in database in sql while loop statement
View 7 Replies
View Related
Apr 17, 2008
I work on an MS Access project ( *.apd - file) that is linked with a
SQL-express server 2005 (local installed). On this server are 2 databases x
and y.
I linked some tables of database x to y and here's the problem: the linked
tables are read-only.
My authentication for the server is Windows authentication and I'm
administrator.
I say this because I think the problem is a security problem.
when I try to change data in the
linked table in Microsoft SQL Server Management Studio Express, I get now the follow error:
---------------------------
No row was updated.
The data in row 12 was not committed
Error source: .Net SqlClient Data provider.
Error Message: Transaction context in use by another session.
Correct the errors an retry or press ESC to cancel the change(s).
---------------------------------
All other programs are close, except MSE
thanks a lot!!!!!
Can somebody help me to make the linked tables are not read-only? What is
the problem, what did I wrong?
Thanks a lot!
View 3 Replies
View Related
