A Previous Restore Operation Was Interrupted And Did Not Complete Processing On File

Sep 6, 2007



Hi

I am new to Forum. So not sure if i am posting my problem uner the right topic.

We have a sql server 2005 enterprise edition 4 way cluster on windows 2003 advance server.

I am logshipping these database to a different server at a different location.
My logshipping went fine until one the cluster server failed and the server instance failed over to another node.
The backup that happened around that time got copied over to the secondary by the copy job.
The log file that got copied to the secondary server tried restoring and i think it failed int he middle of restoring it.
(You would think that the sql would knoe if the backup is in complete and will move on to the next file. Not sure what happened there.)
There is no indication of the *.TUF file in the directory where i have the log files.

I tried restoring it manually and i got the following error

Msg 4319, Level 16, State 3, Line 1

A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.


I looked in the msdb..log_shipping_secondary_databases and looked for the last file that it restored and tried restoring it again with the following restore command by removing and adding some of the keywords that you see after the "WITH" clause.
MSFT do not recommand to use continne_after_error unless its absolutley necessary. I stilll get the above error.


restore log sessiondata

from disk = 'I:sql13qasmlogssessiondatasessiondata_20070901124516.trn'

with restart, CONTINUE_AFTER_ERROR, norecovery

When i add the restart int he with clause,


The restart-checkpoint file 'J:Microsoft SQL ServerMSSQL.5MSSQLBackupsessiondata.CKP' was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified.

Msg 4319, Level 16, State 1, Line 1

A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

I checked it the backup directory and i can't locate the .CKP file.

Does anyone ever come accross this issue?

Is there anyother way i could recover this DB in a standby or norecovery mode.

Any kind of help to resolve this issue (beside copy the full backup and redo the whole log-shipping process again) would be appreciated. sicne my primary and secondary server are totally ina different location, i need to ship a tape, if i need a full backup. This is the 3rd time its happening on that cluster. its frustrating to ship a tape everytime this happens.








View 4 Replies


ADVERTISEMENT

Help!!!! Restore File Mdf After Exec Drop Table... To Previous State.

Aug 8, 2006

Hi all.
First, sorry about my poor english.

I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks

View 14 Replies View Related

Not Enough Storage Is Available To Complete This Operation

Aug 31, 2004

Hi,

I am having trouble trying to import a big file (aprox 250Mb is size) into an SQL Server database and I keep getting the message:

"Not enough storage is available to complete this operation".

The application tries to import the file by executing a stored procedure:

CREATE PROCEDURE sp_updateMaterialBlob
@MaterialId Int,
@BLOB image
AS
BEGIN
Update Material SET blob = @blob where id = @materialId
END

The application uses an ADO connection. I've tried increasing the memory of the client machine but that didn't work. Whenever I do run the import, nearly all the memory on the machine is used up but every time after several hours I get the same error message. What is the cause of the problem and how do I resolve it? Ideally I want to use my application to do the import rather than anything bespoke.

View 7 Replies View Related

Not Enough Storage Is Available To Complete This Operation

Oct 26, 2006

I get this exception after awhile of browsing thru my application, i dunno what causes this because it never happens in the same place. I would like to know if anyone else is experiencing this situation and if there is a solution.

SqlCeException
Not enough storage is available to complete this operation.

em System.Data.SqlServerCe.SqlCeConnection.ProcessResults()
em System.Data.SqlServerCe.SqlCeConnection.Open()
em System.Data.SqlServerCe.SqlCeConnection.Open()
em System.Data.Common.DbDataAdapter.QuietOpen()
em System.Data.Common.DbDataAdapter.FillInternal()
em System.Data.Common.DbDataAdapter.Fill()
em System.Data.Common.DbDataAdapter.Fill()

View 9 Replies View Related

SqlCe 3.0: Not Enough Storage Is Available To Complete This Operation.

Mar 8, 2007

I'm getting the "out of memory" exception when trying to do various things on a sql mobile [sql mobile 2005] database on a windows mobile 5 device. Our app uses compact framework 2.0 and is written in c#.

Our application uses strongly typed data sets, and save/loads them from a sql mobile database. The error occurs in different places, but always somewhere we interact with the database. It might fail on "DELETE FROM Location" in one run, and on a DataAdapter.Update on another, and a DataAdapter.Fill in another.

The memory thing I can add to the today screen suggests that when I encounter this error there is only 1-2Mb of program memory free [and about 13Mb of storage]. Unfortunately windows mobile 5 doesn't seem to have a way to adjust the allocation between storage/program memory anymore.

The failure has been occuring while trying to import data [which we are loading from a web service, as weakly typed datasets and copying into our specialized ones].

I have implemented:

Using statements, and/or dispose statements on:

Data Adapters

explicitly calling the dispose method on commands used in data adapters: http://support.microsoft.com/kb/824462
Sql Connections
Sql commands [where not used in adapters]
Using/dispose around the larger data sets in the loading process
Gc.Collect() before database interaction [this seemed kind of like a last resort]
Reduction of memory usage [by loading fewer records from the web service at a time]

There does not seem to be a storage memory limitation, a fully loaded database file is about 3Mb. Our test handheld has only 64Mb of memory, and I've seen at most maybe 25Mb free [storage and program combined].

We are opening [and closing] a connection each time we touch the database. How much of a difference will going to a single connection opened when the app starts make?

Any ideas? Perhaps there's something I've missed?

thanks,
-Stair Counter

View 13 Replies View Related

Error: Not Enough Storage Available To Complete This Operation

Apr 8, 2008

Hello I recently bought a Palm Centro from Sprint and I wanted to install the cd that came with it. On this cd is Palm desktop and Sprint music manager which I need to use my phone. The problem is when I insert the disk. After my laptop reads it it displays the error message along with: Line:134 Char:2 Code:0 and finally URL: file://E:EnglishEssential_Software
esources.html. I have no idea what to do or why this is happening can you please explain????? Maybe offer some steps also thank you

View 1 Replies View Related

SQL Server 2000: Not Enough Storage Is Available To Complete This Operation

Jul 20, 2005

Hi all,I'm getting this error when trying to import data from a text file intoSQL Server 2000 (Windows Server 2003) using the DTS import wizard.Any ideas what could be causing this? There aren't any restrictions(that i can find) on the file sizes etc.Thanks in advance.Dave

View 4 Replies View Related

Is Busy: ...waiting To Complete An Internal Operation

Jul 30, 2007

Frequently, when I'm connecting to my server through SSMS i get "Is Busy: Microsoft SQL Server is waiting to complete an internal operation...."

And it tells me that if I get that message a lot, I should let Microsoft know.

So -- Microsoft, I'm letting you know.

Can anyone tell me why this is happening? I've searched MSDN and the knowledge base...I can only find one reference, and that is to a database diagram issue.

It takes a while to connect when this happens. So far, I haven't found a common denominator. It happens if I'm logged into my server and I try to connect to another instance, and it happens when I'm using my desktop...

View 2 Replies View Related

Integration Services :: Not Enough Storage Is Available To Complete Operation In Ssis

Sep 29, 2015

In my data flow had LOOKUP.so it failed due to Not enough storage is available to complete this operation.

View 5 Replies View Related

Could Not Complete Cursor Operation Because The Table Schema Changed

May 30, 2006

Microsoft SQL Server  2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue?  I suspect the application logic may cause this error. Please advise.

Thanks a lot!

 

View 16 Replies View Related

Sp_OACreate In SQL Server 2005, Fails With 'Not Enough Storage Is Available To Complete This Operation'

Jan 20, 2007

Hi all,

I have created a small COM in C# so that I can programatically create and execute stored procedures with SMO. At this point the COM has nothing in it but just a test prototype.
But when I tried to create the object as follows, I get the error indicated below.
It is not a memory issue because I have adequate storage and RAM.

Please Help!

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)

-- Create an object.
EXEC @hr = sp_OACreate 'SQLInterop.CsharpHelper', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

This is the error I am getting:

Error Code: 0x8007000E
Description: Not enough storage is available to complete this operation.
Source: ODSOLE Extended Procedure


This is the C# code for the COM:

using System;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.EnterpriseServices;

// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("CSServer")]
[assembly: AssemblyDescription("Test SQL .NET interop")]
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("MyKey.snk")]

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(true)]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("ff35c6b4-81bf-47dd-9290-fcbbb49008d9")]

// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.0.1")]
[assembly: AssemblyFileVersion("1.0.0.1")]

// the ApplicationName attribute specifies the name of the
// COM+ Application which will hold assembly components
[assembly: ApplicationName("SQLInterop")]

// the ApplicationActivation.ActivationOption attribute specifies
// where assembly components are loaded on activation
// Library : components run in the creator's process
// Server : components run in a system process, dllhost.exe
[assembly: ApplicationActivation(ActivationOption.Server)]
namespace SQLInterop
{
public interface ITest
{
string SayHello();
string SayIt(String strMessage);
}

//[SecurityRole("RBSecurityDemoRole", SetEveryoneAccess = true)]
[ComVisible(true)]
[CLSCompliant(false)]
[ClassInterface(ClassInterfaceType.None)]
public class CSharpHelper : ITest
{
public string SayHello()
{
return "Hello from CSharp";
}

public string SayIt(String strMessage)
{
return strMessage + ": from CSharp";
}
}
}

View 8 Replies View Related

SQL Server Admin 2014 :: How To Do System Restore To Previous Restore Point

Dec 31, 2014

In Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.

If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.

View 6 Replies View Related

Server: The Operation Has Been Cancelled. - When Processing Cube

Jan 3, 2007

When I highlight a few
partitions and start processing, the process occasionally stops with a
message that operation has been canceled, like this:

Response 3
Server: the operation has been cancelled.
Response 4

Server: the operation has been cancelled.

Response 5
Server: the operation has been cancelled.

..etc...

(no further error message details are provided)
SQL profiler shows that batch was completed (but rowcount shown in process progress log is too small).

Analysis
Services profiler shows no messages at that time at all. It just shows
messages when it started, and when I restarted the processing.

The Analysis trace appears to be stopped when processing stops with "Server: the operation has been cancelled."This
is an occasional error and sometime occurs within 15-20 minutes from
starting to process. It could fail on 1st partition in the process list
, or on some partition in the middle. Some partitions might run for a
few hours and not error out, but sometimes it fails quickly.I was not sure if this is an issue with the underlying fact data, so I broke up the last partition where it failed into 5 smaller partitions, they processed OK separately.I think restarting SQL Server and SSAS helps to process a few more partitions. Some that failed with this problem process OK after restart, but some fail again.
(apologies for re-posting, wanted to put under more specific thread title)

View 19 Replies View Related

Analysis :: Is There Any Rollback Operation Whenever Cube Processing Fail

Apr 24, 2014

I Have one cube, First time its Processed successfully and browsed fine. Now i was change in the cube Calculations/somewhere, now again process the process failed. I am unable to browse my cube. Is there any option to Rollback this processed cube and Browse with existing cube?What is require is, If cube process is fail, i want to browse the cube with existing cube after modify the cube also(if fails).

View 9 Replies View Related

Transact SQL :: Truncate And Insert Operation - Avoid No Rows In Table During Processing

Oct 22, 2015

I have a job which executes hourly.

Essentially:

Begin
Truncate table A
Insert into A
(Col1,
Col2,
Col3...
)
Select Value1,
Value2,
Value3...
From Table B
End

The insert operation query takes approximately 3.5 minutes to execute. What's occurring is the Table is immediately truncated, and there are no rows in the table for those 3.5 minutes.

How can I avoid having this gap - where there are no rows in the table for that period of time during the job execution ? The table could be locked, but that doesn't seem like the best solution.

View 8 Replies View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.

Sep 20, 2007

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View 7 Replies View Related

Sched. Db Restore Task Won&#39;t Complete Due To Db Being In Use.

Oct 27, 1999

"Database in use. The system administrator must have exclusive use of the database to run the restore operation." I created a script in the 7.0 database to reconfig the db to 'dbo use only' and identify and then kill all but the 'SA' and 'NT AUTHORITYSYSTEM' processes but that still is not enough. Can anyone please tell me what I'm missing here?
Thanx.
W.

View 1 Replies View Related

Restore A Complete SQL Server Installation Into A New Clean One

Oct 13, 1998

A customer is about to replace their PDC with SQL Server running on it.
MY companies role is to build the new server, and get their SQL Server running again.
I need to have the devices, databases, tables, data and the USERS copied across. I cannot use transfer manager as their will be only 1 instance of SQL Server running on the network.

If all we have is an NT backup is perfomed on the entire disk, i.e. the MSSQL/DATA directory.
How do we recreate the databases and their associated tables and also, how do we recreate the User accounts ?
Any ideas would be appreciated.

Please feel free to e-mail me with a response
NEILA@ANGLIABC.CO.UK

TIA

View 4 Replies View Related

Transact SQL :: Verify Complete Restore Path (check For Missing Files)

Jul 29, 2015

In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).

View 3 Replies View Related

How Can I Restore With Previous Snapshot

Jun 19, 2008

Dear All,
replication is not working for me now, how can i find the old snapshots and resore them to the present snapshot?

Arnav
Even you learn 1%, Learn it with 100% confidence.

View 3 Replies View Related

Database Restore Operation Failed

Nov 1, 2012

I am working on sql server 2008. I have a database , flooded with GBs of data in each days.

I back up the database and trying to restored it on another server.

But the operation failed.

View 1 Replies View Related

Does Fragmentation Changes After Backup/restore Operation?

Jul 30, 2007

Hello,
If i backup a database and then restore it, would physical structure remain the same? specially fragmentation.
I'm concerned about output of DBCC SHOWCONTIG.

Senario: I want to check if client database needs defragmentation, so he's sending db backup file. But is it possible that when i restore it fragmentation info has got lost?

Thank you.
STG Labs

View 3 Replies View Related

Automate Backup And Restore Operation

Apr 17, 2015

I want to automate backup and restore operation in T-sql script I need to give resource server,database and backup file location as input and then I need to restore that backup in different server so I want to give destination server as input too.

View 4 Replies View Related

Backup And Restore With Missing Previous Backups

Jun 23, 2015

I've a SQL Server Express 2012 DB that I need to backup and restore on a different machine.I know that in the past someone performed full db and logs backup with sqlcmd.exe and I found some of this backup files but not all of them.In the last 6 months no backups has been taken.What is the right procedure I need to follow in order to save a backup of this DB and restore it on different machine withou losing data?

View 9 Replies View Related

Help Getting Error When Using Operation Rename A File In The File System Task Editor?

Aug 18, 2006

Does anyone know how to do this using variables?  Everytime I try it, I get the

Error: Failed to lock variable for read access with error 0xc00100001. 

 I also tried it writing a script and still the same error.  If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename.  So the value of the variables will change everyday.  Here is my expression:

@[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )

The result:

C:Documents and SettingsmroushDesktopOSU20060818

the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)

 

View 6 Replies View Related

Flat File Connector Stops Processing File On Empty Row And Generates Fatal Error

Dec 27, 2007

Here's a really annoying problem. Let's say you have a text file with 2 million rows.Delimiters all look good and rows are previewed well but the file has a missing row at say lin 1234567 - way deep in the file. When SSIS encounters the blank row, an error is raised and processing on the file STOPS! I verified this in by checking the SSIS log and have even developed an error routine to notify me via email when the error occurs (really cool if I do say so myself ). The main problem still remains - how to resume processing from the point of failure in the file? Any help is appreciated. Thanks.

View 13 Replies View Related

Integration Services :: FTP Task Can't Complete File Transfer

Oct 7, 2015

Executing the FTP Task - The execution starts and after 3 or more minuts the execution stops with the RED X but with no errors, and the file is not transferred.I use the same entries to the FTP connection manager as it is for the Dreamweaver...The variable that I created for file in the site is FileName1 and the site directory tree is The local path is And The File Transfer is filled up like this: After the Execution stops I get..And the file was not transfered..Also, when I try to Specify the Variable Expretion.

View 8 Replies View Related

Recovery :: Maintenance Task To Copy Bak File To Secondary Drive After Backup Is Complete?

Nov 6, 2015

I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.

An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.

View 7 Replies View Related

Unable To Restore Databases From Tape: Timeout Expired. The Timeout Period Elapsed Prior To Completion Of The Operation Or The

Jul 6, 2007

I'm trying to test my backups by restoring from production onto a DEV machine.



However, when I insert the backup tape into the drive, then go into SSMS and right click on

Databases -> Restore Database -> From Device .... -> Tape -> Add -> (my tape drive \.Tape0) -> Contents

it displays the contents as it should.



So I click "Close" to close the contents and bring me back to the Specify Backup where I have \.Tape0 selected and I then click "OK"

SSMS sits for a few seconds, and then I get a time out error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------






That's all the further I can get... any suggestions?



I'm VERY perplexed by this...

View 11 Replies View Related

Daily File Processing Help

Jan 9, 2004

Hello DBA's:

I need to extract a particular file from our AS 400 system on a daily basis and do some processing on it. Also I want to do the daily processing only on those records that have been added/updated since the initial load.
Here is the approach and possible implementation.

Approach
I have the DB2 source data containing 10 columns (Col1 to Col5 together form the key) and the rest are attributes. I am interested only in the key and two attributes. So I load my table with only Col1 to Col7 ( 5 for key and the two attributes). I then do my processing on this table.

Here is the implementation given by a member of dbforums -



You'll then have to deal with 3 potential actions,

INSERT: New records on the file.
DELETES: Records that don't exists.
UPDATES: Records that are on the file, but attributes have changed.


You had given me this code template.

CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL--NO CHANGE
SELECT '1','2','x','y','z' UNION ALL-- UPDATE (My comment - Instead of an update I want to insert a new record)
SELECT '1','3','g','h','i' UNION ALL--NO CHANGE
SELECT '2','3','a','b','c'--INSERT
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

--DO DELETES FIRST (My comment - Before deleting, I want to copy the rows that I am going to delete on a separate table to maintain history. Then I want to delete from a). I don't get the logic. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =. why the where clause condition)
DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- INSERT (My comment - I don't get the logic of the where. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =)

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- UPDATE

UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO
------------

Can anybody look at My comments and answer them or revise this code template if need be?

Brett Kaiser - I sent you an e-mail on this. Can you respond to it when time permits.

Thanks

View 9 Replies View Related

Cannot Copy The Mdf File After Processing

Feb 1, 2007

I have a windows service that connects to a regular sql server 2005 database and basically bulk copies data into a SQL express database. Afterwards, I just want to copy the MDF file into a different directory. I keep getting the "Process cannot access file because it is being used by another process" error. I've tried changing the connection strings but nothing seems to work. I'm closing the connections in the code as well. Here is the code. Any thoughts or help would be appreciated. RefreshDB calls Sync 3 times and if all three calls are successful, it attempts to copy the database file to the specified location. This is where I get the error.

Public Function RefreshDB() As Boolean
Dim success As Boolean = True
Dim tables() As String = {"Job", "Equipment", "PMScheduled"}
For Each tableName As String In tables
If SyncTable(tableName) = False Then
success = False
Exit For
End If
Next
If success Then
'copy the new database to the target directory
Try
File.Copy(My.Settings.DBFilePath, My.Settings.TargetDirectory + "EMField.mdf")
Catch ex As Exception
My.Application.Log.WriteEntry(ex.Message + "(RefreshDB)", TraceEventType.Critical)
Return False
End Try
End If
Return success
End Function


Private Function SyncTable(ByVal tableName As String) As Boolean
Dim reader As SqlDataReader
Dim sourceViewName As String = String.Format("EMField{0}View", tableName)
Dim connectionString As String = My.Settings.EMFieldConnectionString
'insert the path to the database into the connectionstring
connectionString = connectionString.Replace("[DBFilePath]", My.Settings.DBFilePath)
Try
'clear the target table first
Using targetConnection As New SqlConnection(connectionString)
Using truncateCommand As New SqlCommand(String.Format("TRUNCATE TABLE {0}", tableName), targetConnection)
targetConnection.Open()
truncateCommand.ExecuteNonQuery()
targetConnection.Close()
End Using
End Using
'create a datareader from the source database
Using sourceConnection As New SqlConnection(My.Settings.EMLiteConnectionString)
Using readCommand As New SqlCommand(String.Format("SELECT * FROM {0}", sourceViewName), sourceConnection)
sourceConnection.Open()
reader = readCommand.ExecuteReader
BulkCopy(tableName, reader, connectionString)
reader.Close()
sourceConnection.Close()
End Using
End Using
Return True
Catch ex As SqlException
My.Application.Log.WriteEntry(ex.Message + "(sync)", TraceEventType.Critical)
Return False
End Try
End Function

Public Sub BulkCopy(ByVal tableName As String, ByRef reader As SqlDataReader, ByVal connectionString As String)
Try
'bulk copy from source to target database
Using bulkCopy As New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = tableName
bulkCopy.WriteToServer(reader)
bulkCopy.Close()
End Using
Catch ex As SqlException
'throw exception back to calling sub
Throw ex
End Try
End Sub

View 5 Replies View Related

LOG File Growing, Similar Issue To A Previous One

May 23, 2007

I have an 19 gig database that somehow has a 100gig log file. The DB MUST BE in full recovery mode, I backup the transaction logs EVERY hour and shrink nightly. but for some reason my logfile WILL NOT SHRINK.





HELP,



I've used both the DBCC Shrinkfile (xxxxxx) and DBCC ShrinkDatabase (xxxxx) and these don't seem to work. I Have No current backup, I have Not capacity for addtional 100 gig worth of backup drive or off-site tape.

View 1 Replies View Related

SQL 2012 :: Current Operation Cancelled Because Another Operation In Transaction Failed

Nov 20, 2013

I'm using SQL Server 2012 Analysis services in Tabular mode and connected to Oracle Database and while importing, I'm getting below error after importing some rows.

OLE DB or ODBC error: Accessor is not a parameter accessor.. The current operation was cancelled because another operation in the transaction failed.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved