Do not delete things, it bites

Hello,

I noticed that when you delete things or substitute tables, you get in trouble.

- Delete a named query used in a partition before deleting the partition

- Delete a named Calculation from a table in the Ds view, whathever you do

- Replace a table with another table which has less columns even if none of the lesser columns are used nowhere.

The XML do not get updated accordingly leading in all kinds of errors, some requiring restore from an older version.

Just a FYI.

BTY, I do not know how to submit bug reports.

Philippe

Build 9.00.2175.00

go to connect.microsoft.com|||

I think we have both come across the same problem (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640957&SiteID=1).

For permissions, I have manually cleaned up the xml files. If you post this on connect.microsoft.com, let me know as I cannot do that. I can vote for it.

btw, how did you get build 9.00.2175.00?

|||

Yes, the changes you made in DSV will not propagate to other linked ojects. Since you are deleting things from DSV, the related binding will be broken and it is up to the user to rebind or remove it. Anyway, you should get validation error when you try to deploy and find out the broken binding.

We have already got customer request on this issue. We will consider to improve the user experience in the next release.

|||I disagree. It is not a case of improving the user experience. It is a bug.|||

A bug means that the software is not doing what it is supposed to do in the design spec. This is not a bug because we don't have it in design spec. You can say it is design flaw. However, from the user experience, people hated to see something that deleted automatically without any control. Therefore, what I suggested is to enhance the user experience. After deleting the objects in DSV, we could show a list box to list all the broken bindings and give user the option to delete the broken objects. Some advanced users may want to leave the broken bindings so that they can bind to different things instead of regenerate the objects or redo all the modifications.

Anyway, we already addressed the issue. We will consider it in future release.

Thanks

|||

This often happens when building complex products such as SQL2005. Do not take it the wrong way.

When is the planned release of the fix?

Do I need to delete sp_replincrementlsn?

Hi,
maybe someone have suffered this before.
I'm able to make in the server a publication (W2K ADV. with SQL Server 2000
Standar and SP2). I can't upgrade the SP4 now.
Then I'm trying to make in my computer a suscription (WXP with SQL Server
developer and SP4) .
The error is the "14053: Can't load the DLL replincrementlsn, etc." when I
search the stored procedure sp_replincrementlsn I can find it in the master
database in the stored procedures (not in extended stored procedures) so if
I try to register the xprepl.dll I can't because the procedure already
exists.
What can I do? Can I delete a stored procedure of the master database? How?
Thank's in advance!!!
Un saludo,
Enrique Barcel
I think you need to reapply the current sp. Have you tried a reboot? I have
run into resource problems like this that were solved on reboot.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Enrique Barcel" <ebarceloSINSPAM@.EnerWeb.es> wrote in message
news:%23cpZLPyeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> Hi,
> maybe someone have suffered this before.
> I'm able to make in the server a publication (W2K ADV. with SQL Server
> 2000
> Standar and SP2). I can't upgrade the SP4 now.
> Then I'm trying to make in my computer a suscription (WXP with SQL Server
> developer and SP4) .
> The error is the "14053: Can't load the DLL replincrementlsn, etc." when I
> search the stored procedure sp_replincrementlsn I can find it in the
> master
> database in the stored procedures (not in extended stored procedures) so
> if
> I try to register the xprepl.dll I can't because the procedure already
> exists.
> What can I do? Can I delete a stored procedure of the master database?
> How?
> Thank's in advance!!!
> Un saludo,
> Enrique Barcel
>
>
sql

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 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 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.

Distribution Agent error

distribution agent giving error like unable to update sp...
can any one suggest which data will delete from which talbles and replication should work normally.
John,
please can you post up the exact error message you receive, and any
'special' configuration settings you have.
TIA,
Paul Ibison

Distributed Transaction problem

Hi,
We have a procedure that delete the records from a local
database table and then delete the records from another
database table that can be on local or remote machine. I
am testing the procedure with both the databases on same
local machine but getting following error when trying to
execute procedure:
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have a databaseA on local machine HARV and databaseB
also on HARV
I created a linked server QA1 for databaseB
Following select statement works fine:
select * from qa1.databaseB.dbo.tab1
and following delete statement also works fine:
delete from qa1.databaseB.dbo.tab1 where col1 = 1
BUT when I want to use begin and commit tran it doesn't
worked:
I tried using begin distributed tran but didn't helped
OS is Windows 2000 and standard edition (not win 2003 or
enterprise edition)
MSDTC is running
@.@.servername is not null
There are no triggers on tables
Any feedback will be appreciated?
Thanks
--HarvinderThat is a known limitation of loopback linked servers and DTC
Look up loopback linked server in BOL. Scroll down and read:
--snip--
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:
Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction conte
xt in use by
another session.
--snip--Bummer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Harvinder" <hs@.metratech.com> wrote in message
news:1259b01c410fd$d1a135b0$a101280a@.phx
.gbl...
> Hi,
> We have a procedure that delete the records from a local
> database table and then delete the records from another
> database table that can be on local or remote machine. I
> am testing the procedure with both the databases on same
> local machine but getting following error when trying to
> execute procedure:
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have a databaseA on local machine HARV and databaseB
> also on HARV
> I created a linked server QA1 for databaseB
> Following select statement works fine:
> select * from qa1.databaseB.dbo.tab1
> and following delete statement also works fine:
> delete from qa1.databaseB.dbo.tab1 where col1 = 1
> BUT when I want to use begin and commit tran it doesn't
> worked:
> I tried using begin distributed tran but didn't helped
> OS is Windows 2000 and standard edition (not win 2003 or
> enterprise edition)
> MSDTC is running
> @.@.servername is not null
> There are no triggers on tables
> Any feedback will be appreciated?
> Thanks
> --Harvinder
>