deleting duplicates  

Posted in ,

Deleting dublicate values from any table in SQL Server.
Key value (ID) may be changed.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[delete_dublicates_from_table]
@table nvarchar(255)
as begin
DECLARE @ID bigint
DECLARE @COUNT int
declare @query nvarchar(max)
declare @condamne table (
ids bigint,
counts int
)
set @query = 'SELECT [ID], COUNT([ID]) FROM '+@table+' GROUP BY [ID] HAVING COUNT([ID]) > 1 '
insert into @condamne exec sp_executesql @query
DECLARE CUR_DELETE CURSOR FOR
select * from @condamne
OPEN CUR_DELETE
declare @temp_query nvarchar(max)
FETCH NEXT FROM CUR_DELETE INTO @ID,@COUNT
WHILE @@FETCH_STATUS = 0
BEGIN
set @temp_query = (select 'DELETE TOP('+convert(nvarchar(3),@COUNT,120)+'-1) FROM '+@table+' WHERE ID = '+convert(nvarchar(32), @ID)+' ')
exec sp_executesql @temp_query
FETCH NEXT FROM CUR_DELETE INTO @ID,@COUNT
END
CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE
end

MySQL "on duplicate"  

Posted in ,

No way to use "update table" in a before update trigger.
Good sample, that shows it.

If you trying to use update operations on table with trigger connected to it,
operation falls with error


ERROR 1442 (HY000): Can't update table 'table_name' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.


Using stored procedures don't resolve this issue.
You will get same error (1422).

MySQL have solution for an action on duplicate triggers event.
But this work only if constraint is "UNIQUE KEY" and you want to insert
a duplicate value.


DROP TABLE IF EXISTS `test`.`test1`;
CREATE TABLE `test`.`test1` (
`id` int(10) unsigned NOT NULL,
`key` int(10) unsigned NOT NULL,
`data` varchar(45) NOT NULL default '2',
`id_inc` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY USING BTREE (`id_inc`,`id`),
UNIQUE KEY `_UNIQUE` USING BTREE (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

insert into test1 (id,`key`,`data`) values (1,1,'1');
insert into test1 (id,`key`,`data`) values (2,2,'2');


insert into test1 (`id`, `key`,`data`) values (2, 3, '3') on duplicate key update `data` = '`id` no double key 2';
insert into test1 (`id`, `key`,`data`) values (3, 3, '3') on duplicate key update `data` = '`key` no double key 3';
insert into test1 (`id_inc`, `id`, `key`,`data`) values (2, 4, 4,'4') on duplicate key update `data` = '`id_inc` no double key 2';
insert into test1 (`id_inc`) values (1) on duplicate key update `data` = '`id_inc` no double key 1';
insert into test1 (`id`, `key`,`data`) values (5, 5,'5');

select * from test1;

+----+-----+-----------------------+--------+
| id | key | data | id_inc |
+----+-----+-----------------------+--------+
| 0 | 0 | 2 | 1 |
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 2 |
| 2 | 3 | `key` no double key 3 | 3 |
| 5 | 5 | 5 | 4 |
+----+-----+-----------------------+--------+
6 rows in set (0.00 sec)

jabber transports  

Posted in ,

Installing MSN transport for jabberd2 on gentoo.

Install jabberd2 on gentoo is really easy task,
just "#emerge jabberd2".

pyMSNt hasn't gentoo ebuild.
you need WGET it manualy. Config it by following the instruction.

Next you may need to install some python libs.
(i need "emerge --sync" before updating)
If you see "unable to find a reactor. Exiting...."


#emerge --tree twisted
#emerge --tree twisted-words


in pyMSNt's server.xml ucomment lines:

< jid >msn
< discoName >MSN Transport
< background/ >


and check "secret" in route.xml, route-users.xml and config.xml.
It's described in manual.

XMLA for ANALYSIS  

Posted

If you want to connect to analysis 2005/2008 from XMLA (SOAP over HTTP(S))
you may configure IIS like this.

All seems simple, but exists some tricks.
1. In all settings - path to folder, extention and filter - you must have same path to msmdpump.dll.
2. Anonymous access and password protected access:
you must give access rights to special user account of IIS in SQL Managment console in "server"->Security. For user IUSR_MACHINENAME.
Here screenshot of restricted anonymous access and enabled HTTP basic auth by domain user-password record. It will be usefull if you wand connect to SSAS from non-windows platform.


Check this first if you see this error in logs:


The description for Event ID ( 10 ) in Source ( MSOLAP ISAPI Extension:
\\?\c:\inetpub\wwwroot\olap\msmdpump.dll ) cannot be found. The local
computer may not have the necessary registry information or message DLL
files to display messages from a remote computer. You may be able to use the
/AUXSOURCE= flag to retrieve this description; see Help and Support for
details. The following information is part of the event:


Here good guide of setting up IIS7 and excell. It's on russion, but screenshots from english versions.

Finally check msmdpump.ini if you running IIS and SSAS on different servers.


localhost
3600
100

Better SyntaxHighligting  

Posted

Here post about adding SyntaxHighlighter Version 1.5.1. But i have dark blogger theme and want dark code on it.
10 minuts of googling gived me this page. It's new version of Highlighter.

New code for blogger template: (after uploading scripts to your hosting)




<_link href='http://yourhost.com/styles/shCore.css' rel='stylesheet' type='text/css'/>
<_link href='http://yourhost.com/styles/shThemeMidnight.css' rel='stylesheet' type='text/css'/>


<_script src='http://yourhost.com/scripts/shCore.js' type='text/javascript'/>
<_script src='http://yourhost.com/scripts/shBrushXml.js' type='text/javascript'/>
<_script src='http://yourhost.com/scripts/shBrushSql.js' type='text/javascript'/>


h:selectOneMenu  

Posted in ,


Exception Details: java.lang.IllegalArgumentException
Cannot convert javax.faces.component.html.HtmlSelectOneMenu@1a2edef of type class javax.faces.component.html.HtmlSelectOneMenu to class java.lang.Integer


For

<h:selectOneMenu id="tagId" required="true"
value="#{pasteController.paste.tagId}"
binding="#{pasteController.paste.tagId}">
<f:selectItems value="#{TagsBean.topTags}" />
</h:selectOneMenu>


Exception happens if i adding this:

binding="#pasteController.paste.tagId}


But I don't need this for my form working.
(JSF 1.2_04-b20-p03)

When i googling about this exception i found some usefull posts.
using Converter and Bug in MyFaces

metanews


Add to Google