Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Exclusive Access Could Not Be Obtained Because The Database Is In Use.

I have a test database that is automatically restored from the live database each day at 2:30AM.  I recently started receiving this message:

Exclusive access could not be obtained because the database is in use.

and the restore is not able to occur. 

Any ideas on how I can prevent this from happening?

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Exclusive Access Could Not Be Obtained Because The Database Is In Use.
How to close the existing connections to a particluar database in sql server. Please note that i donot want to start stop sql server. I just want to close the existing connections so that i can do a restore on that database programatically.

I am using sqldmo for this purpose. Does anyone knows how to do that with sqldmo or is there any other method??

Waiting for your earliest replies

View Replies !   View Related
Exclusive Access Could Not Be Obtained Because The Database Is In Use

We're using a backup with sql server agent when doing a backup / restore
procedure. In some cases I get the following error when the restore job

Executed as user: DOMAINAdministrator. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error 3101)
RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.

Is it related to unhandled lock? I've resolved this by restarting the sql
server. But is there a way to avoid such issues?
One more question. Is it OK to backup/restore database while there're users
connected? Or I can do only backup?

View Replies !   View Related
Exclusive Access Could Not Be Obtained Because The Database Is In Use


I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:


Exclusive access could not be obtained because the database is in use


Anyone know how to solve this problem?





View Replies !   View Related
Exclusive Access Could Not Be Obtained Because The
Could anybody give a lead as to what I can do get rid off this error please.

I alread tried following:
use master
Alter Database dbname set single_user with rollback immediate;

Still have the issue. SQL 2005 Server actually did lock the db.
So ran
Alter Database dbname set multi_user;
and refresh Query and it switch back to multi user.
But I can't restore db yet.

Thank you

View Replies !   View Related
Cant Restore SQL Server Databases: Exclusive Access Could Not Be Obtained

Hi everyone,


Hope somebody can help me on this. 


I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:


"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"


There are NO users using the application. These are DBs for the Portfolio Server 2007 application. 


After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.


Can they be deleted? if so, would there be any serious consequences?


Thank you for your help on this.


Oscar E.

View Replies !   View Related
More On Restoring Database In User Instance - Exclusive Use Not Obtained

Hi all. I have read and implemented the very helpful threads on backing up and restoring with user instance posted with MFriedlander. Thank you. However, during the rs.SqlRestore(srv) command I am getting the following error "Restore failed for Server '\.pipe4A1F91FF-F6FE-45 sqlquery'. "
"Exclusive access could not be obtained because the database is in use." 

I have implemented the changedatabase method as described in that thread.


right before the line


I do not use the default instance of sql express, but I do use an instance called 'test' for my app.  Should my changedatabase method also refer to my sql instance?

I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.



Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)



Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)

Dim srv As Server = New Server(ServerConnection)

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)

''Define a Restore object variable.

Dim rs As New Restore

'Set the NoRecovery property to true, so the transactions are not recovered.

rs.NoRecovery = True

rs.ReplaceDatabase = True

'Add the device that contains the full database backup to the Restore object.


'don't know why the below norecovery is changed to false

rs.NoRecovery = False

'Specify the database name.

rs.Database = sqlconnection.Database.ToString()


'Restore the full database backup with no recovery.


View Replies !   View Related
Exclusive Access To DataBase

I need to access a database to modify, updates,... massively . It's possible to lock a database and have exclusive access?

(SQLServer 2000)



View Replies !   View Related
Exclusive User Access To Database
Hi All,
My question is regarding SQL Server database security. I want to create a login using SQL Server Authentication and assign it db_owner rights for my database. So far so good. But the critical part is that I want to give exclusive rights for my database to this user only i.e. no other users (dbo, guest etc) should be allowed to access my database.
It will be good to present here the scenario which I need to implement. I am running an ASP.NET application that uses SQL server 2005 db at back end. The database server might have other databases as well but I don't want the administrator (either SQL server admin or the server administrator) to be able to get access to my database or even view the schema. I don't want any other user except my own user to be able to detach the database or perform backup or restore operations.
I hope I was able to deliver the requirement.

View Replies !   View Related
Trying To Get Exclusive Access To A DB For Restore.
I have created a SQL Agent job that is supposed to essentially duplicate a production database to another database. The code I am using is:

__________________________________________________ ______
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'kill '+cast(spid as varchar)+char(13)+char(10)
from sysprocesses where dbid=12

--Print (@SQL)

__________________________________________________ ________

__________________________________________________ ______

This works when I test it during the day, however when it runs at night I get the following error in the job log:

Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

I'm not sure why this happens because I have killed all open threads in step 1, and then create my own new thread in step two. Maybe someone else is initiating a new thread to quickly between the steps???

Anyway, I am trying to use:
__________________________________________________ __
__________________________________________________ ____ an alternative to the T-SQL killing PID's, but SQL 7.0 SP4 does not seem to support restricted user like 2000. It keeps giving me a syntax error. Does anyone have any suggestions?

If I bring step 1 and step 2 together, separated by "GO", could this fix the problem?

Thanks in advance!

Ryan Hunt

View Replies !   View Related
How To Obtain Exclusive Access Via SQL

i need to run a restore of a database overnight onto a different server using the live data .bak file. however the job failed on the first run (last night) with the error:

"Exclusive access could not be obtained because the database is in use. ...."

how do i gain this Exclusive use via an SQL statement please?

View Replies !   View Related
How To: Implement Exclusive Access To A Given Record.
I would like to know if it is possible and if so how to implement exclusive access to a given record within in a database table.
For example, you can open a file system file with exclusive access so other processes cannot access the file until it is released.  I would like to implement a solution that provides this same exclusive access at the database record level.  Preferably a solution that does not require creating and managing table access state flags.  (i.e. €“ FieldName.State = (Closed, Opened, Exclusive))  I would like for the database server to mange this solution and return an error status when trying to access a record that has been opened exclusively.  Furthermore, I would like to avoid using triggers.
Application Utilization
I€™m working on a system that has multiple threads that are each responsible managing data from the same normalized table.  Each of these system threads work on the same unique record in parallel.  Therefore it is possible that thread (0)€™s changes could be overwritten by thread (n)€™s.  I would like to have each thread open the unique record exclusively and release it when finished.  All of the other threads would loop until the record is released.
1. Is it possible to implement this at the server level?
2. What would be the easiest best practice for implementing this functional requirement?

View Replies !   View Related
How To Make Database Exclusive??????????
Hi I've backed up my database...
When i try to load it it's telling me that:
DBLIB error message: "General SQL Server error: Check messages from the SQL Server.".
Server error message: "Database in use. System Administrator must have exclusive use
of database to run load.".
Can someone tell me how to work around this problem...
I have put dbo use only for that database in EM..But i still get the same error...


View Replies !   View Related
Computer Name Could Not Be Obtained
i have stupid problem that had effect my application distripuotion which i think is related either to connection string or something of the security setting of sql server or iis , actually i do not know what to do: the details of the problem is a have multi connection strings in this form :
sqlconnectionPubs = New Data.SqlClient.SqlConnection("Server=SALES-SERVER;Database=NajdiaServiceRequestDta; Asynchronous Processing=true; Integrated Security=True; Trusted_Connection=Yes;")
and the connection works just fine in my computer , even i put the database in the main server and still i can post data from my pc to the main server where my database exist but when i try to post from any other computer in the network it tells me : run time error : computer name could not be obtained.
I appreciat any help

View Replies !   View Related
How To Obtained Values In 2 Tables
Hii'm fairly new to sql....i know the basic..........what i am trying to do is get values from 1 tables and insert it into another table using a store procedure........              

View Replies !   View Related
A Recordset Obtained Via ADO Doesn't Show The First Record Using Data Report
Te first record of a Recordest obtained from a Command Object executing a Stored Procedure, doesn't show the first record when I asociate this to a data report.(VB6 - SQL7) (ADO 2.1)

If I execute the stored procedure directly from query analizer, I have obtained the right resultset.

Does anyone Knows what could be happening?

Thank You ...

View Replies !   View Related
Database Access Via COM Objects - V- Database Access Via Stored Procedures
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?

View Replies !   View Related
Non-bitwise Exclusive Or?
Is there a way to do a logical exclusive OR (XOR) in sql server?

I'm trying to do this in where clause, something like:

(not exists (select 1 from table a where > '01/30/03') XOR
exists (select 1 from table a where < '01/30/03'))


View Replies !   View Related
Exclusive Mode

When I open an application, it prompts me for a message that SQL is locked in exclusive mode by other application.

How to solve this?

thanks in advance


View Replies !   View Related
Many To Many - Exclusive Result
A problem about many to many relationships from an SQL beginner. Here's a contrived abstract example, as I'd prefer not to give away specifics.

Imagine I have two tables: users, food
The relationship (to like) is many-to-many so I've got a link table, which might look like the below:

andrew, apples
bob, banana
bob, apples
chris, carrots
chris, apples
chris, banana

I want to select users who like bananas and apples exclusively.

The answer should be 'bob' ONLY. select * from users inner join food on <IDs> where food in ('bananas','apples') isn't suitable , because it'll also return 'chris' who should be disqualified (because he also likes carrots).

Apart from potentially being bad DB design (although this is an abstract example; I do have ID numbers), can anyone suggest how to get this in a scalable way?

View Replies !   View Related
Exclusive Locks

How do you lock a table in exclusie mode before running a query?


View Replies !   View Related
Exclusive Locking Problem
I have a spid that belongs to the SQLAgent that has my master db and log tied up with exclusive locks. The process it was running has hung. How do I unlock the db and log without killing this spid. I have replication running and it relies on SQLAgent.

View Replies !   View Related
Exclusive Lock During A SELECT
Anybody know how a SELECT statement can generate an exclusive lock on a table ?
I always thought that SELECT's take out shared locks. Is this something to do with temporary tables generated by ORDER BY's and DISTINCT ?
Rogue SQL below (from Site Server).

SELECT A.i_Dsid, A.i_Aid, A.vc_Val, A.i_Val, A.dt_Val, A.img_Val FROM Object_Attributes A, ( SELECT DISTINCT L.i_Dsid FROM Object_Lookup L , Object_Attributes OA2 (index = IND_vc_Aid) WHERE ((( L.i_ObjectClass = 9999 )) AND ( OA2.vc_Val LIKE ( '999999999.9999999%' ) AND OA2.i_Aid = 99)) AND (L.i_Container_Dsid = 99) AND ( OA2.i_Dsid = L.i_Dsid )) AS B WHERE B.i_Dsid = A.i_Dsid AND A.i_Aid NOT IN( 1, 2, 3, 4, 5 ) ORDER BY A.i_Dsid, A.i_Aid

Can anybody suggest a workaround ? Thanks.

View Replies !   View Related
Intent Exclusive Locks

Are Intent exclusive locks compatible with rowlock?
I am getting a deadlock since i have ix lock on a page and another process(select query) is trying to acquire a shared lock.How can i solve this?

View Replies !   View Related
Exclusive Insert Lock On A Table
Hello All!
I want to perform 4 or 5 statements as a transaction but I need to make sure that during this complete transaction no one else inserts or deletes records from a table named SomeTable.
So how can I lock MyTable at the beggining of the transaction so that during my transaction no one else can insert or delete anything in table SomeTable?

View Replies !   View Related
Inserts Resulting In Exclusive Key Lock
I'm relatively new to SQL and I've come across something that doesn't seem quite right. When an insert becomes part of an transaction I notice an exclusive KEY lock in Enterprise Manager. The table in question was using a Clustered index but I changed that, dropped the table and brought it back in but I still get the lock which keeps all others out of the table. Is this the expected behavior or is there something I am missing? Could the size of the tabe affect things? This is a very small table currently. I'm using MSSQL 7 sp3.


View Replies !   View Related
Can You Place An Exclusive Row Lock Using A Hint?

Is it possible to place an exclusive row lock when running a SELECT query by using a lock hint (or otherwise).

Basically, when a select statement is run against a table I don't won't any other users to read that row until it has been updated - at some later stage.

Any suggestions on whether this is possible would be welcome.



View Replies !   View Related
Query Trouble Regarding Bitwise Exclusive
HI, i am trying to make query that has computations with it. but when there's a point computing between int and float i had to use cast() function to convert certain data types. somehow it only works on converting float to integer because when i'm converting an integer into float inorder to be computed with a float it bombs. my query is like this ....

SELECT cast(((cast(((lat - (SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * 69.1) AS int) ^ 2) + (cast((69.1 * (lng - (SELECT Lng FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * (COS((SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210) / 57.3))) AS int) ^ 2)) AS float) ^ .5

.5 is where the query bombs. any idea why is this happenning?

by the way, i'm using sql server 7.0.


View Replies !   View Related
Deadlock -select With Exclusive Lock
Hi ,
I have some issues with deadlock.I am getting deadlock becuase of two
select on same table. The sql server log is like this ,
Select statements should have always shared lock.I am not getting why
its doing exclusive lock and creating deadlock.

5/6/2008 12:38 spid4s Unknown Deadlock encountered €¦. Printing
deadlock information
5/6/2008 12:38 spid4s Unknown Wait-for graph
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:1
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594050117632 (8d036f07c58f)
CleanCnt:3 Mode Flags: 0Ă—0
5/6/2008 12:38 spid4s Unknown Grant List 3:
5/6/2008 12:38 spid4s Unknown Owner:0Ă—12E9F160 Mode: S Flg:0Ă—0 Ref:1
Life:00000001 SPID:68 ECID:0 XactLockInfo: 0Ă—353D1C54
5/6/2008 12:38 spid4s Unknown SPID: 68 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:€™OR€™Xdes:
0Ă—2FBB67F0 Mode: X SPID:112 BatchID:0 ECID:0 TaskProxy0Ă—0792E378)
Value:0Ă—38baa20 Cost0/11888)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:2
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594049986560 (6f02e1cd37c3)
CleanCnt:3 Mode:X Flags: 0Ă—0
5/6/2008 12:38 spid4s Unknown Wait List:
5/6/2008 12:38 spid4s Unknown Owner:0Ă—12396EE0 Mode: S Flg:0Ă—2 Ref:1
Life:00000000 SPID:90 ECID:0 XactLockInfo: 0Ă—0AA8178C
5/6/2008 12:38 spid4s Unknown SPID: 90 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:€™OR€™Xdes:
0Ă—353D1C30 Mode: S SPID:68 BatchID:0 ECID:0 TaskProxy0Ă—13B3E378)
Value:0Ă—12e9e780 Cost0/6164)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:

If you have any idea regarding this please let me know ASAP.

Thanks in advance .

View Replies !   View Related
Having A Mental Block On An Exclusive Join
Hi, I've got three tables that I'm trying to pull data from. The first is a family of rings, the second is the individual rings and the third relates one ring to another:
FamilyID pk | FamName
RingID | FamilyID fk | RingName
RingID1 pk/fk | RingID2 pk/fk
I'm trying to pull a list of RingIDs and names for a given FamilyID. To complicate it, I want to exclude rings that are already associated to a given RingID, i.e. I only want the unassociated rings in a given family. To complicate it even a little more then name needs to be the this possible? Thanks!
Given @FamID and @RingID
Result Table
RingID | FamName + RingName

View Replies !   View Related
Mutually Exclusive Counts On Ordered Queries
Ive been playing with this for a few days and thought I might thow itout for seggestions.I have Several Queries that need counts returnedThe Queries are Mutually Exclusive meaning whatever Query they returnin first they cannot be included in the counts of any queries belowthem.This set of queries for exampleSelect ID From Customers where FIRST_NAME = 'Chris' (would return say150)Select ID From Customers where ST='OH' (This would retunr say 50, BUTRun alone it might return 70, however 20 of those were in the firstQuery so they arent to be retunred in this result set.The total for Bot Queries would be 200But If I reverse it like soSelect ID From Customers where ST='OH' (This now returns 70)Select ID From Customers where FIRST_NAME = 'Chris' (This now returns130)The total of course for BOT Queries is 200 but I dont need that total Ineed the total for EACH Query depending on its orderingWhat I need are the single counts depending on the order in which thequeries are runIt seems like a recursion problem, but It might go past 32 level so Icant use recursive SQL ( I dont think )I've thought of (or tried to think how to use Not In, Not Exist, etcbut still dosent come up with the results....)How Can I grab the counts for each Query ?Chris

View Replies !   View Related
Restoring Log, With STOP AT Function..problems With Exclusive Use
I restored a full backup of db...then I use this script to backup log, using the stop at function:
Restore log db from disk='e:Program FilesMicrosoft SQL ServerMSSQLBACKUPdb_tlog_200411300800.TRN' with
RECOVERY,STOPAT = 'Nov 30, 2004 1:00 AM'

when I run this script in Query Analyzer, I get:

Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

No other connections are present to the database except my connection through QA ...what can I do ?..please respond ASAP

thank you

View Replies !   View Related
Is It Possible To Read Data From A Table With Exclusive Lock ?

We currently use SQL Serv 2000 with an ERP application and VB applications.

Both system work with the same database & tables but the ERP application seems to put exclusive lock on all tables it needs during processes. So our VB applications are not able to read these data and we receive TIMEOUT error.

What can I do ?

thank you


View Replies !   View Related
How I Can Insert Xml Result Whitch Obtained With &"for Xml&" Into One Field Of Table
how i can insert xml result whitch obtained with "for xml" into one field of table
for example insert into table1 select * from table2 for xml

View Replies !   View Related
How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?

View Replies !   View Related
Are Exclusive Advertising Deals A Fair Business Practice?
This is a question to all of you running businesses out there - youcan check out my ramblings about this on my blog at: Check it out and post yourcomments there - I will review all comments and post the majorityopinion in a couple of weeks.PS sorry for the non-technical question, but we are in the SQL Servertools market and hence I thought this would be the best audience topost the question to.Thanks,JCxSQL Software

View Replies !   View Related
Cannot Get Access To My Access 2003 Database Tables
 I developed a database with Access 2003 and everything was working good until my tech came in and reformated my hard drive and install a new Ghost image that met our company standards.

Now I cannot go in and make any changes to any of the tables, queries and forms. All of this started when a new Ghost image was installed on my pc.

The message I get when I try to open my database is "You do not have permission to run "tblSwitchboard." I get the same error message when I try to do anything at all on the database.

I am at a loss as to what to do. Please help.



View Replies !   View Related
Question: Can I Synchronize The Mobile Device Which Has A SQL Server CE Database With The Access Database On The Desktop?
Dear All,
i have a question abt winCE 4.2 and SQL server CE.
i am using of Visual Studio 2005
My platform is using a PDA with winCE 4.2 and SQL server CE. The Host program is using dbf files on desktop side.

I got a problem of how to sync / read the sql CE data from a windows application.

so, i wanna ask,

1. any method to access the data from winCE data by windows application? or can i convert the sdf file to windows readable files? or any others?

2. Can i use a MDB to sync with SQL server CE?
 can i synchronize the mobile device which has a SQL Server CE database with the Access database on the desktop?

last question,
3. is that windows CE .net 4.2 not support pocket access (cdb) anymore?

please help me out

View Replies !   View Related
An Error Occurred While Trying To Access The Database Information. The Msdb Database Could Not Be Opened.
I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:

An error occurred while trying to access the database information. The msdb database could not be opened.

View Replies !   View Related
ODBC Connection From Access 2007 Database To SQL Server 2005 Database
I need detailed instructions on how to connect to a database from a Microsoft Access 2007 database to a Microsft Office Accounting 2007 database.  The accounting database is an SQL 2005 datbase.  It has an instance name of "MSSMLBIZ".
When I try I get an SQL error 53.  Do not have permissions or database does not exist.
Thanks in advance for any help.,

View Replies !   View Related
SSIS Synchroniseing Of A Access Database &&amp; Sql Server Database So They Both Run In Parrallel
Hi I am wondering if anyone knows of a way of synchroniseing two versions of a database one in access and one in sql server so they can both run in parrallel in ssis for updates etc.
Also can anyone recommend a book which is easy to use or resources as I can't find what I am looking for in the online help.
Thanks in advance

View Replies !   View Related
Data Copy From SQL Database To Access Database Failing
Hi all,

I am downgrading a SQL database to an Access database for legacy support purposes. I am sure a lot of you have already managed to overcome this hurdle.

To do this, I do not use the import/export wizard as it basically complaints with the following error on columns of nearly all my tables.

- The data type could not be assigned to the column "XXXX" in "Microsoft Access".

So I created a package myself and it seems to work fine, except for certain tables.

On these tables I am getting these error:

[CrdTrans Destination [1008]] Error: An OLE DB error has occurred. Error code: 0x80040E2F.

[CrdTrans Destination [1008]] Error: The "input "OLE DB Destination Input" (1021)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1021)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "CrdTrans Destination" (1008) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

Basically for each table, I have a SQL datareader that I link directly into an OLEDB destination. The data is extracted based on a normal SQL statement like 'SELECT * FROM CRDTRANS. The destination is just a table with the same name and equivalent structure in the Access database. The Access database has been wiped clean of data.

At first I thought it was the relationship constraints that was causing it. So I temporary deleted all my foreign key constraints. However these errors are still here. Does anyone know what I am doing wrong? There are a few explanations I could think of but could someone verify these thoughts for me.

1. Default value constraints in Access tables?

2. Indexes constraints in Access tables?

3. Corrupt indexes in Access tables?

Any help is very much appreciated. Thanks





View Replies !   View Related
The Microsoft Jet Database Engine Cannot Open MS-Access Database
I have MS-Access as data source for one of the reports. I can preview the report fine from BI studio however, it does not work when I deploy it on report server.
The error is :
An error has occurred during report processing.
Cannot create a connection to data source '<data source name>'.

The Microsoft Jet database engine cannot open the file '<UNC location of the MS-Access database>'. It is already opened exclusively by another user, or you need permission to view its data.

MS-Access database is located on a different server.
Any help to solve this? I understand it has something to do with permission both on server where reporting service is running as well as the server where MS-access database is located. Pls help.

View Replies !   View Related
Best Way To Return Data From SQL Database, Without Giving Access To Database
I have a quick question regarding getting data from a SQL database but I am slightly confused as to the best way to handle it.
Basically on of the projects I am working on I need to send data to another company, there are several calls required to the database to bring back various options for changing the questions asked on the front end.
Up to now all that has happened is there has been a mutual agreement between myself and this other company and they have just had access to the database to call a series of stored procs which I have written for them to access the data. Recently however the situation has changed and my client wants me stop them accessing the database however they still need to recieve the information from the database they recieve now.
What will be the best way to handle this? My knowledge of SQL is farily limited presently and I am only ust getting into learning a lot more about it.
Any help and advice would be greatly appreciated.
Kind regards,

View Replies !   View Related
Can You Convert Access Database Data Over To An SQL Database?
I would like to start using SQL, how would I convert my data in MS Access over to an SQL Server Database?

View Replies !   View Related
Updating Sql Database From Linked Access Database
I got thrown into a new project that is going to require me to update an SQL server database tables from an Access table on the backend of an Oracle database on another server. At the end of each day the Access dabase will be updated from the Oracle database.

What I need to do, is when the Access database is updated I need to have the table in the SQL database automaticaly updated. When a new record is added to the Access table I need the new record added to the SQL table. When a record is deleted in the Access table I need to keep that record in the SQL table and set a field to a value (such as 0). And when a record is updated in Access, have it updated in SQL.

Needless to say this is a bit out of my area and not sure how to accomplish this.

Any help is greatly appreciated.

View Replies !   View Related
Changing From An Access Database To A SQL Server Database
I have been given a project to change from an access database which we are using now to a SQL database.
I am wondering if there is a big change in the code using visual basic.
Any ideas would be gratefully appreciated

View Replies !   View Related

Copyright © 2005-08, All rights reserved