problem w/ linked table permissions


This question involves SQL server as well as Access, so I hope that someone can follow.
I'm trying to give someone read and write access to a table in SQL server through a linked table scenario in Access.
I set up a new user account with datareader and datawriter permissions and made sure it went into the particular table as well. I also re-created the DSN on the user's local machine using this new login information. However, each time you open up the table after logging in using this info and try to edit a field, it gives a "Write Conflict" error and gives three choices: Save Changes, Copy to Clipboard, Drop Changes and of course, Save Changes is disabled.
We also tried logging in using the db admin account and it won't work.
Anyone have similar experiences with this If so, is there a work-around so that you can edit data through Access

Posted On: Friday 26th of October 2012 12:06:16 AM Total Views:  368
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




inserting problem

, I am having a problem with inserting a value into a table. Here is my code: sql = "INSERT INTO AnthonyInbox (MessageID, Sender, Subject, Message, Date)"+ "VALUES('" + msgNum + "','" + from + "','" + subject + "','" + mainMsg + "','" + date + " ')"; cmd = new SqlCommand(sql, con); cmd.ExecuteNonQuery(); msgNum, from, subject, and date are succesfully entered in the table, but mainMsg is blank in the table. I have check that mainMsg contains some string, but for some reasons won't enter the value in the table. Below is the definition of the table: MessageID varchar 50 Sender varchar 50 Subject varchar 200 Message varchar 8000 Date varchar 50 PLEASE help me
VIEWS ON THIS POST

132

Posted on:

Friday 26th October 2012
View Replies!

Big problem with ms sql

i got a .bak file from someone. supposedly this is the backup of the ms sql server. unfortunately i do not have ms sql installed on my computer and i can't seem to open the file in notepad. how can i convert this file to either access so that i can view the contents of the db or how can i convert it to mysql server (which i have).
VIEWS ON THIS POST

233

Posted on:

Friday 26th October 2012
View Replies!

Query problem

I had a college voting there were 2 positions Director1 and director2 Want to find out how many votes did each candidate get here is my query there are 2 fields in the table called dir1,dir2 it gives an error saying cannot use unionall any idea SELECT dir1 AS Director, COUNT(*) AS Total FROM voting GROUP BY dir1 UNION ALL SELECT dir2 AS Director, COUNT(*) AS Total FROM voting GROUP BY dir2
VIEWS ON THIS POST

257

Posted on:

Wednesday 7th November 2012
View Replies!

SQL Query problem... please help

I do not have much experience with sql, however i do use ms sql 2000 and have set up a product database to use for my website. I am having diffficulty with two things... 1. I am finding difficulty in putting links into my DHTML menu that will return the correct product category from the database. I have set up a database called met_products and need to use this to access the information. Basically, I think that i need to include products.aspseries=.... but i dont know how to put this code in. I have a DHTML menu already setup and ready to link to, however i cannot seem to get my head around the type of code that is needed to do this. 2. Also, I am looking at comprimising a search engine facility for the products only so that the customer can type a keyword in a search and it searches just the products on the sql database. Can you help me with any code ideas on this I know that i will need to use the WHERE function, but I am unsure as to how to put this in and link it to my search.
VIEWS ON THIS POST

174

Posted on:

Wednesday 7th November 2012
View Replies!

Curious connection problem

hi, We have a database that is Access on the front end and SQL server 2005 on the back end. Everyone seems to connect to it ok, except for one person who gets locked out from time to time. She gets sqlstate 0100, 08001 errors and cannot re-attach to the database. However, when we run ipconfig /flushdns and set up a connection to the database again, it seems to work. Everybody else is set up the same way and they never have problems. Does anybody know what's going on We can't figure out why this is happening. Where should we look THanks ! !
VIEWS ON THIS POST

195

Posted on:

Wednesday 7th November 2012
View Replies!

SQL query speed problem... What to do?

180 GB Medical Records database. Approximately 50 users in at any given time. Database has 2 physical files, both of the "primary" filegroup. Loading a patient's data from the application takes anywhere from 10-45 seconds right now. It is entirely dependent on how much "old" data exists for patients... A patient with 30 previous visits takes a lot longer than a patient with 2 visits. Disk system is a IBM FastT500 SAN controller, and database is located on a 10-disk RAID5 10K-RPM SCSI array. When retrieving patient data, disk queue spikes above 100 in performance monitor. I suspect entirely too many IO operations are attempting to be done here. Since there are only 2 database files, the OS can only allocate 2 IO threads and this creates a system bottleneck. I would like to do the following things to this database: 1: Take several core tables (patient, patient_encounter, patient_procedure, user_todo_list, etc) and put them on their own filegroup. 2: Transfer all non-clustered indexes to yet another filegroup. Moving the tables won't be too difficult, but there are hundreds or thousands of indexes in this database. Does anyone know of a script to take all non-clustered indexes and move them to a specified filegroup
VIEWS ON THIS POST

173

Posted on:

Wednesday 7th November 2012
View Replies!

case problem

please ignore
VIEWS ON THIS POST

283

Posted on:

Wednesday 7th November 2012
View Replies!

Name-sort problem: 'Aa' shows after 'Az'

I have a problem how to sort out names in the right order. Here's the sorting-code: sql = "SELECT * FROM list order by lastname,firstname ASC" everything is fine except that the lastnames that starts with "Aa" comes out last on the list after "Az". I live in Norway so many lastnames starts with "Aa", but it "sounds like" "" that is the last char in our alphabet. So maybe the code think that "Aa" is "" and therefore it comes out after "Az" on my namelist. Any suggestions regards, Dan Grnli Norway
VIEWS ON THIS POST

192

Posted on:

Wednesday 7th November 2012
View Replies!

join problem

hi, I have 2 table here. table1 code name 0001 Julian 0002 Melvin 0003 John 0004 Peter table2 code1 code2 code3 0001 0003 0004 0004 0002 0001 I want my result set like these. code1 code2 code3 Julian John Peter Peter Melvin Julian Any one have sugestion or can help me.
VIEWS ON THIS POST

210

Posted on:

Wednesday 7th November 2012
View Replies!

Beginner with problems

Help needed Trying to teach myself ASP for my new job having trouble getting information from a form to the database looked at lots of different code and I am even more confused than before I started. any help would be grea
VIEWS ON THIS POST

178

Posted on:

Wednesday 7th November 2012
View Replies!

Dot.NET Oracle database problem

Situation:Were supposed to connect to the test Database(Oracle 9i) in a remote server from a local PC using VB.net.The 2 providers we tested are:Microsoft OLE DB provider for OracleMicrosoft OLE DB provider for ODBC driversWere not sure if they are the correct ones to be used.When we tested the connection with Microsoft OLE DB provider for Oracle. The following data link error was prompted.Error message :Test connection failed becos of an error in initializing provider. Oracle Client and Networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.Next we tested the connection with Microsoft OLE DB provider for ODBC drivers.( which is also another driver for .NET and Oracle). The connection test was successful. We are able to preview the tables in the DB However, when we try to drag a table from the solution explorer into the web-form the following error was shown. Error message : You cannot use an OLE DB provider for ODBC drivers.Please recommend us any drivers you think would be appropriate to connect Microsoft visual studio .NET and Oracle DB that is currently being used.Also should we install the driver in our local PC or in the remote server side where the DB is locatedAlso were not sure about what the problem really is. Please advice us and provide us with any assistance.
VIEWS ON THIS POST

242

Posted on:

Wednesday 7th November 2012
View Replies!

stored proc problem

I have a stored proc on a sql 6.5 svr. that run fines from the SQL anlizer, and in VB6 code.However when I run it from asp it blows up. saying that cant ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed. /prod_ol_rpt/Prod_asp/storedp.asp, line 29 this is my code
VIEWS ON THIS POST

222

Posted on:

Wednesday 7th November 2012
View Replies!

"derived table" invalid object name problem

hi I've mad a stored procedure that uses derived table to insert params into a table name TMovie but after settting null params to their defaults,I use the derived table to store columns default values.The same technique worked with other tables with 2 or 3 columns ,but in this table that has many columns a strange message appeared to me when calling the stored procedure: Msg 208, Level 16, State 1, Procedure sp_TMovie_Insert, Line 64 Invalid object name '____TEMP____TABLE___'. (1 row(s) affected) (1 row(s) affected) here is stored procedure code code; Code: PROCEDURE [dbo].[sp_TMovie_Insert] @Key uniqueidentifier , @Name varchar (100) , @Genre varchar (50) , @ReleaseWindow varchar (50) , @PosterImage image = null , @Actors varchar (200) , @Synopsis varchar (500) , @Rating varchar (50) , @ReleaseDate varchar (50) , @RunningTime varchar (50) , @TStamp datetime , @DateCreated datetime , @CreatedBy varchar (50) , @LastUpdatedBy varchar (50) = null , @Source varchar (50) , @ErrorCode int OUTPUT AS SET NOCOUNT ON select * from (SELECT column_name,column_default FROM INFORMATION_SCHEMA.COLUMNS where table_Name ='TMovie' ) as ____TEMP____TABLE___ declare @t nvarchar(4000) --temporary variable if @Key is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='Key') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @Key = convert ( uniqueidentifier,@t) end if @Name is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='Name') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @Name = convert ( varchar (100),@t) end if @Genre is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='Genre') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @Genre = convert ( varchar (50),@t) end if @ReleaseWindow is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='ReleaseWindow') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @ReleaseWindow = convert ( varchar (50),@t) end if @PosterImage is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='PosterImage') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @PosterImage = convert ( varbinary(max),@t) end if @Actors is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='Actors') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @Actors = convert ( varchar (200),@t) end if @Synopsis is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='Synopsis') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @Synopsis = convert ( varchar (500),@t) end if @Rating is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='Rating') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @Rating = convert ( varchar (50),@t) end if @ReleaseDate is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='ReleaseDate') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @ReleaseDate = convert ( varchar (50),@t) end if @RunningTime is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='RunningTime') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @RunningTime = convert ( varchar (50),@t) end if @TStamp is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='TStamp') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @TStamp = convert ( datetime,@t) end if @DateCreated is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='DateCreated') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @DateCreated = convert ( datetime,@t) end if @CreatedBy is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='CreatedBy') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @CreatedBy = convert ( varchar (50),@t) end if @LastUpdatedBy is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='LastUpdatedBy') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @LastUpdatedBy = convert ( varchar (50),@t) end if @Source is null begin set @t=(select column_def from ____TEMP____TABLE___ where column_name='Source') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') set @Source = convert ( varchar (50),@t) end -- INSERT a new row in the table INSERT INTO [dbo].[TMovie]( [Key],[Name],[Genre],[ReleaseWindow],[PosterImage],[Actors],[Synopsis],[Rating],[ReleaseDate],[RunningTime],[TStamp],[DateCreated],[CreatedBy],[LastUpdatedBy],[Source] ) VALUES ( @Key,@Name,@Genre,@ReleaseWindow,@PosterImage,@Actors,@Synopsis,@Rating,@ReleaseDate,@RunningTime,@TStamp,@DateCreated,@CreatedBy,@LastUpdatedBy,@Source ) -- Get the Error Code for the statment just executed SET @ErrorCode = @@ERROR and here's the calling statements: Code: DECLARE @return_value int, @ErrorCode int EXEC @return_value = [dbo].[sp_TMovie_Insert] @Key = NULL, @Name = N'eee', @Genre = N'eeee', @ReleaseWindow = N'erere', @PosterImage = NULL, @Actors = NULL, @Synopsis = N'sdfsdfdsf', @Rating = N'33', @ReleaseDate = N'11111', @RunningTime = N'dfdfdf', @TStamp = NULL, @DateCreated = NULL, @CreatedBy = NULL, @LastUpdatedBy = NULL, @Source = NULL, @ErrorCode = @ErrorCode OUTPUT SELECT @ErrorCode as N'@ErrorCode' SELECT 'Return Value' = @return_value GO why the message of "invalid object name" appears to me is this a known limitation in derived tables
VIEWS ON THIS POST

231

Posted on:

Saturday 10th November 2012
View Replies!

Database datafield problem

I have a user's table that has many fields. Now I want to add a field for rating users. Normally the rating may go as far as 10000 or 20,000 but it's possible to have a bigger number like 100,000 (but very rare). So what should I do. Should I put the rating in the same user table or should I make a separate table for it. in this way the page size for each record would be reduced for each record of user. CB
VIEWS ON THIS POST

263

Posted on:

Saturday 10th November 2012
View Replies!

Field problem

A few weeks back, shadow wizard suggested me that I should put all of the thread's data in my forum DB in the post table and not the thread table. The thread table should contain information like thread ID, thread subject, date/time, user id. And the post table should have the root post as well as other posts. Originally Posted by shadow wizard no, having post data in the threads table is wrong. threads table should contain information regarding threads only. posts table should contain everything related to the posts. Thread Address: http://forums.aspfree.com/classic-asp-development-5/new-post-criteria-190220.html Now I am developing a similar DB. It's a 50 word comment DB. A user posts a topic with some [b]explanation/[b] on that topic/issue and then other users post comments in 50 words. Here again I have two tables. First one is COMMENT TOPIC which has fields topic id, subject date, time etc and second table is COMMENTS table which has field topic id and comments. So should I put the explanation in the COMMENT TOPIC table or in the COMMENTS table I personally think that I should divide the data load on two separate tables. COMMENT TOPIC without the comment explanation would be a very small table in terms of data. the load (in terms of page and extent size) would be on COMMENTS table. CB
VIEWS ON THIS POST

243

Posted on:

Saturday 10th November 2012
View Replies!

Server registration problem

hi there, i m tryin to register my laptop as a local sql server for my work by giving it windows authentication.. it doesnt connect..it says 'Server does not exist or access denied'. i m an admin for my laptop and have full rights. what might be the problem
VIEWS ON THIS POST

308

Posted on:

Saturday 10th November 2012
View Replies!

Paging problems

I have a gridview with paging set to true but I am aware that every record is being retrieved instead of just the ten the user wants to see. Eventually there will be a lot of records and a lot of users and obvisouly retrieving all the records will be too much of a strain on the database and the site so I want to adjust the SQL query to just retrieve the records the user is viewing. I found this SQL script SELECT * FROM (SELECT ROW_NUMBER() OVER (Order BY dateadded) AS Row, id, title, description FROM Products) WHERE Row Between (@pageindex - 1)* @pagesize AND @pageindex * @pagesize pageindex and pagesize being parameters passed into the storedprocedure and but I keep getting an error message saying "Incorrect syntax near the keyword WHERE" but I can't see whats wrong, any ideas
VIEWS ON THIS POST

196

Posted on:

Saturday 10th November 2012
View Replies!

Urgent : Filter condition problem

i have recordset rs on that i am applying filter as rs.Filter = " PRCD = '"&product&" ' and HQ_NAME='"&hqs"' " here,PRCD is Column of datatype varchar(20) HQ_NAME is Column of datatype varchar(20) the problem is with PRCD,if substitue value directly as PRCD='SLO1NMK' filter is working but PRCD = '"&product&" ' not working.where as HQ_NAME='"&hqs&"' is working individually fine wat's problem PLEASE HELP
VIEWS ON THIS POST

182

Posted on:

Saturday 10th November 2012
View Replies!

Migrate Sybase DB to MS SQL... datetime problem.

I have a Sybase server with my source data. I have a MS SQL 2000 server for my destination. Trying to migrate all the data from the Sybase instance over to the SQL instance... not really all the data, just the tables that have a rowcount above '0'. I don't care about PK/FK relationships, constraints, triggers or stored procs. I came up with the following script, using the linked server features of SQL2000. Code: declare tablecursor scroll cursor for select name from openquery(LINKEDSERV, 'select name from sysobjects where type= ''U'' and uid = 1 order by name') open tablecursor declare @tablename varchar(50) declare @sql nvarchar(800) fetch first from tablecursor into @tablename while @@fetch_status = 0 begin set @sql = N' IF (select * from openquery(LINKEDSERV, ''select count(*) from ' + @tablename + ''')) > 0 BEGIN select * into TargetDB.dbo.' + @tablename + N' from openquery(LINKEDSERV, ''select * from ' + @tablename + ''') END' -- select @sql exec sp_executesql @sql fetch next from tablecursor into @tablename end close tablecursor deallocate tablecursor But, about part way through I get an error. Server: Msg 8114, Level 16, State 10, Line 3 Error converting data type DBTYPE_DBDATE to datetime. Seems that implicit conversion is not working for the Sybase datetime datatype and the SQL2000 datetime datatype. I don't want to write an import for each table... I have about 100 total tables to target. Any ideas Can DTS help in this case, even though this source server is not an MS server I want the whole database, and I don't want to write a construct for each table.
VIEWS ON THIS POST

466

Posted on:

Saturday 10th November 2012
View Replies!

Insert problems

Microsoft VBScript compilation error '800a0409' Unterminated string constant /CreateProfile.asp, line 26 Code: strSQL = "INSERT INTO ProfileManagement(demo_date,epn,store_id,tline,bio,pointer,salesleader,cosconfirmed,[20pounds],language,merchandisingexp,merchandisingjobs,shortnotice,fulltimejob,otheragency,cnum,staffingline)" strSQL = strSQL & "VALUES ('" & ("request.QueryString OBJrs (""demo_date"")" & "','" & ("epn") & "','" & ("store_id") & "','" & ("tline") & "','" & ("bio") & "','" & ("request.QueryString OBJrs (""pointer"")" & "','" & ("salesleader") & "','" & ("cosconfirmed") & "','" & ("20pounds") & "','" & ("language") & "','" & ("merchandisingexp") & "','" & ("merchandisingjobs") & "','" & ("shortnotice") & "','" & ("fulltimejob") & "','" & ("otheragency") & "','" & ("cnum") & "','" & ("staffingline")"')"
VIEWS ON THIS POST

184

Posted on:

Saturday 10th November 2012
View Replies!