DML without logging

Is there a way to stop logging DML? I have a large delete and I dont want to log the operation, is it possible?Truly non-logged operations are not possible in MS-SQL.

The closest you could probably get would be to copy the data to another data source (most probably a file), delete the data there, truncate your original table, then reload the edited data source back into your table.

-PatP|||No...

But you can bcp the data you want to keep, TRUNCATE the TABLE and then bcp the data back in...use native mode (-n)

EDIT: DAMN, sniped again

And Non logged is a misnomer...even TRUNCAT is logged...but at the page level (I think)

Pat?

dml without generating log transactions ?

Hi There

I know the answer to this is probably no, but had to ask anyway.

Is there a way to perform a dml statement without generating anything in the transaction log ?

The reason i ask is that i have a database that uses simply recovery model, however i need to move a 1 billion row table to this DB, i know that even though it is in simple recovery it is one transaction, it will be written to the log until committed then the space will be released in the log file.

I am using a simple: insert into DW_DB..table select * from DB..other_table.

I have dropped all indexes before the operation.

However this is a big problem, the log for the db in simple recovery that i am moving the data to grew to 128 Gig and the disk ran out of space, the other drives on the machine do not have much space.

Is there a way i can move the billion row table into the new DB without generating such a huge log ?

Thanx

Hi There

Part 2 for the question:

The transaction has rolled back, however there is still 27 gigs space used in the transaction log, there are no open transactions in the db, the db is in simple recovery, i cannot backup the log as it is simple recovery, what is this 27 gigs in the transaction and how do i clear it ?

Thanx

|||

Please ignore my second comment, this problem went away after checkpointing the database, however any feedback ont he original post would be greatly appreciated.

Thanx

|||You can use select into command which is bulk operation and it is minimally logged in the case of simple recovery model.|||Thank you , this worked perfectly.

DML Statements in code vs. Stored Procedures

Hi,
We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)
We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way".
What i mean by using seperate stored procedures is:
- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)
- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)
- The body contains a DML statement that uses the parameters
- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)
For select statements they think our approach is best...
I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.
Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...
ThanksHere was the previous big discussion on stored procs vs. dynamic sql:
Rob Howard:
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
Then Frans Bouma:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
The Rob Howard rebuttal:
http://weblogs.asp.net/rhoward/archive/2003/11/18/38446.aspx
That should be a good start.

DML error logging

Oracle 10g2 offers DML error logging, which enables to load data with traditional SQL without having a complete roll-back in case one record is refused. http://orafaq.com/node/76

This is almost similar to loading capabilities of ETL-tools (at least in the error-handling department)

Does anyone have a clue whether Microsoft is going to add such functionality to SQL Server 2005?

I think you have that in SSIS, (almost 99% sure, so ask over there in their forum too http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1) but depending on the situation, you can do this easily in an instead of trigger, if you know the criteria to check for. Just something like the following in the insert trigger will do:

insert into exceptions (columns)
select columns
from inserted
where <bad data check>

insert into real table (columns)
from inserted
where not <bad data check>

This might be a solution for a table where the user is doing heads down keying of data.

Another alternative is to do the same thing in a procedure, if you are doing single row edits by using a TRY...CATCH block and inserting into the exception table on error.

Will they add something like this to 2005, no. But the next version? Perhaps, go here: https://connect.microsoft.com/SQLServer/Feedback and voice your opinion/idea for solution. If you do, post back here with the URL and request votes.

DML delete issues

Hi all,

I'm trying to return a processed Excel XML document from a stored procedure.

The procedure pulls a template document in XML format from a table, inserts data using DML and returns the result. The problem I've hit is in removing worksheets from the document prior to returning it. Inserts work fine when I try to remove a worksheet everything hangs. I'm thinking this could be a namespace problem, but am at a loss.

A small example(assume that @.XML contains a standard ExcelXML document):

set @.XML.modify('declare namespace ss="urnchemas-microsoft-comfficepreadsheet";
declare namespace o="urnchemas-microsoft-comfficeffice";
declare namespace x="urnchemas-microsoft-comffice:excel";
delete (/ss:Workbook[1]/ss:Worksheet[1])')

Any help greatly appreciated...

Regards,

Andy

I tried your case (delete the first Worksheet from a excel file in 2003 xml format) and it works fine:

Code Snippet

declare @.x xml = '<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>jinghaol</Author>
<LastAuthor>jinghaol</LastAuthor>
<Created>2007-04-24T16:15:41Z</Created>
<LastSaved>2007-04-24T16:17:18Z</LastSaved>
<Company>Microsoft</Company>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12345</WindowHeight>
<WindowWidth>18960</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">adsf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">asdf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="2" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:Index="2">
<Cell><Data ss:Type="String">asdf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>'
select @.x
set @.x.modify('
declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
declare namespace o="urn:schemas-microsoft-com:office:office";
declare namespace x="urn:schemas-microsoft-com:office:excel";
delete (/ss:Workbook[1]/ss:Worksheet[1])')
select @.x
go

I couldn't exactly copy/paste/run your query because the editor puts many and in namespace declare part.

DML by Linked Servers

Hi,

I have two SQL-Server 2000 servers - SERVERDB01 and SERVERDB02.
I made a linked server from SERVERDB01 to SERVERDB02.

This instruction is ok.
SELECT * FROM openquery (SERVERDB02,'select * from dbacdl.aux_data')

I need execute DML commands by Linked Servers.
i.e. -> truncate dbacdl.aux_data or delete dbacdl.aux_data

How can I do?

Thank you by attention.

Bye.how about issuing the truncate or delete using the four part nameing convention?

<Server Name>.<Database Name>.<Owner Name>.<Object Name>|||Paul Young,

thank you by the answer, but It did wrong.

I was in the SERVERDB01
TRUNCATE TABLE SERVERDB02.SICDB.DBACDL.AUX_DATA

This mensage appear:
The object name 'SERVERDB02.SICDB.DBACDL.' contains more than the maximum number of prefixes. The maximum is 2.

I did some mistake, maybe.

Bye.|||No, I answqered your question a little to fast. Truncate won't work but delete will, try again using delete rather than truncate.|||Paul Young ,

It's ok.

thank you very much.

Bye.

DML against remote tables (MSSQL to DB2)

I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME

I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?Upon further review I found this site: http://support.microsoft.com/kb/270119/EN-US/

It shows how to use openquery to execute DML statements. nifty.