sql server: reorg / rebuild fragmented indizies (blows up transaction log)

--0) tables with no primary key - create key first to reorg / rebuild indezis

select s.name, o.name, I.Name

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where I.name is null order by s.name,o.name







--1) reogranize indexes

select s.name, o.name, I.Name,st.avg_fragmentation_in_percent,

'alter index '+i.name +' on ' + o.name + ' Reorganize;'

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where st.avg_fragmentation_in_percent>5 and I.name is not null

order by st.avg_fragmentation_in_percent desc







--2) rebuild indezies where reorg hasn't succeeded

select s.name, o.name, I.Name,st.avg_fragmentation_in_percent,

'alter index '+i.name +' on ' + o.name + ' Rebuild;'

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where st.avg_fragmentation_in_percent>5 and I.name is not null

order by st.avg_fragmentation_in_percent desc

power shell: youngest entry in dir

Get-ChildItem | Sort-Object -Property @{Expression={$_.LastWriteTime};Ascending=$false} | Select-Object -First 1

sql server and power shell

#execute tsql script using actual Windows user:


invoke-sqlcmd  -serverinstance "localhost\SQLEXPRESS" -Query "select * from sys.database_files"


invoke-sqlcmd -inputfile "Backup.sql" -serverinstance "localhost\SQLEXPRESS" # -database "mydatabase"




#backup database - since sqlserver 2012 (2008 not working!)
Backup-SqlDatabase -ServerInstance "localhost\SQLEXPRESS" -Database "mydb" -BackupFile "c:\sqlBackup\mydb.Bak"


sometimes ist necesarry to Import sqlSnapIns:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

normale Shell: sqlcmd -S localhost\SQLEXPRESS

EF Code First Migrations Basics

EF (Entity Framework) Migrations are a way to generate versioned Database Scripts from c# Classes. Developpers can specify which Changes are sumerized to a new Version. EF Migrations are enabled by Package Manager Console Command Enable-Migrations. It creates a Migration Folder and a Configuration Class in your Visual Studio Project.
When all changes for a new Version are made to the Entity c# Classes, the Package Manager Console Command "Add Migration (Migrationname)" creates a c# Class deriving from DbMigrations with a Up and a Down Method, which executes the necesarry SQL for Up/Downgrading the DB. The DB itself is upgraded either by the Package Manager Console Command Update-Database (which also lets you create script by -script Parameter) or by using Database Initializer, which are executed when the DBContext first Needs the Database:
//upgrades Production Datebase, maybe first make a backup
Database.SetInitializer(new MigrateDatabaseToLatestVersion());
//creates new Db
Database.SetInitializer(new CreateDatabaseIfNotExists());

So the Software itself can automatically create or upgrade the needed Database.
Summary of Commands and Options: https://coding.abel.nu/2012/03/ef-migrations-command-reference/ or in the Package Manager Console: get-help Add-Migration

most important Package Manager Console Commands:

1. Enable-Migrations
2. Add Migration (Migrationname)
3. Update-Database

Examples:

getting all migrations which have been deployed to a database so far:
get-migrations

adding a migration after changing EF-Relevant Code:
add-migration Mig0025

update Database to a specific Migration
Update-Database -TargetMigration Mig0005 -Verbose -connectionStringName Db1

Add Custom SQL to Migration

use the SQL Methode in the Up / Down Methode:

EF Code First Migrations Basics

EF (Entity Framework) Migrations are a way to generate versioned Database Scripts from c# Classes. Developpers can specify which Changes are sumerized to a new Version. EF Migrations are enabled by Package Manager Console Command Enable-Migrations. It creates a Migration Folder and a Configuration Class in your Visual Studio Project.
When all changes for a new Version are made to the Entity c# Classes, the Package Manager Console Command "Add Migration (Migrationname)" creates a c# Class deriving from DbMigrations with a Up and a Down Method, which executes the necesarry SQL for Up/Downgrading the DB. The DB itself is upgraded either by the Package Manager Console Command Update-Database (which also lets you create script by -script Parameter) or by using Database Initializer, which are executed when the DBContext first Needs the Database:
//upgrades Production Datebase, maybe first make a backup
Database.SetInitializer(new MigrateDatabaseToLatestVersion());
//creates new Db
Database.SetInitializer(new CreateDatabaseIfNotExists());

So the Software itself can automatically create or upgrade the needed Database.
Summary of Commands and Options: https://coding.abel.nu/2012/03/ef-migrations-command-reference/ or in the Package Manager Console: get-help Add-Migration

most important Package Manager Console Commands:

1. Enable-Migrations
2. Add Migration (Migrationname)
3. Update-Database

Examples:

getting all migrations which have been deployed to a database so far:
get-migrations

adding a migration after changing EF-Relevant Code:
add-migration Mig0025

update Database to a specific Migration
Update-Database -TargetMigration Mig0005 -Verbose -connectionStringName Db1

Add Custom SQL to Migration

use the SQL Methode in the Up / Down Methode:

Entity Framework Basics

Entity Framework is a open source OR Mapper from Microsoft. It is installed via NuGet Package Manager to a Visual Studio Project.
There are 3 main ways to use it:
1.Database first: By Adding a new Entity Framework Model you can choose "from existing Database". A Wizard let you select the objects you want to generate Entity Model and Entity Classes for.
2.Model first: By Adding a new Entity Framework Model you can choose "Empty Model". then you can add Entities in the Model Designer - Entity c# Classes are generated when you build the Project, you can deploy it to a database by choosing "Update Database" from the Context Menu in the Model Designer.
3.Code first: You write c# classes and add them as DbSet to a Entity Context Class (derives from System.Data.Entity.DbContext). Then the DBContext creates a Database with Tables for every Class in a DbSet Property and for all related classes. It also creates Relations.

simple wpf entity framework databinding to ms DataGridView and Telerik RadGridView

main Xaml Window: Codebehind: namespace WpfApplication1 { ///

/// Interaction logic for MainWindow.xaml ///

public partial class MainWindow : Window { StpDbEntities1 _stpDbEnt = new StpDbEntities1(); public MainWindow() { InitializeComponent(); } private void Window_Loaded(object sender, RoutedEventArgs e) { MyDataGrid.ItemsSource = _stpDbEnt.tMachine.Local; MyRadGridView.ItemsSource = _stpDbEnt.tMachine.Local; _stpDbEnt.tMachine.Load(); //Window1 w1=new Window1(); //w1.Show(); //Show first Name - not necesarry var machinename = _stpDbEnt.tMachine.First().Name; if (machinename == null) throw new ArgumentNullException("machinename"); MessageBox.Show(machinename); } private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e) { _stpDbEnt.SaveChanges(); } } }

power shell for loop

for ($i=1; $i -le 10; $i++) { Write-Host "Loop:" $i }

Creating some sql server Databases per Tsql script

declare @counter int = 1 declare @sql nvarchar(50) while @counter < 100 begin set @counter = @counter + 1 set @sql = 'Create Database T'+ convert(nvarchar(3),@counter) print ' sql=' + @sql exec (@sql) end

oracle kill sessions

select inst_id,sid,serial# from gv$session where schemaname like 'PSA%'; alter system kill session '14,24893,@1';

oracle constraints

select * from user_Constraints where constraint_name='myConstraint';

news visual studio VS 2013 neu

Save Load Color SChema Blue Alt F12 CRT, Scrollbar Options Alt+Cursor Down / Up verschiebt Codeblöcke Search in Options, Fenstergröße änderbar XAML F12, INtllisense für Resourcen Code Lenses Visual Studio Blog

oracle datetime examples

to_date('22.6.2015 12:03','DD.MM.YYYY HH24:MI') alter table myTable add PERFORMED_AT DATE; --TIMESTAMP stores date and time alter table myTable modify PERFORMED_AT TIMESTAMP;

last / letzte oracle sql statements

select vsession.machine,service_name,vsession.username,vcursor.sql_id,vsql.last_load_time,vsql.executions,vcursor.sql_text as short_sql,vcursor.sid,vsession.program,vsession.osuser,vsql.sql_fulltext as long_sql from v$open_CURSOR vcursor LEFT OUTER JOIN v$sql vsql on (vcursor.sql_id = vsql.sql_id) INNER JOIN v$session vsession on (vcursor.sid = vsession.sid) WHERE vsql.sql_fulltext like '%ETL%' order by vsql.last_load_time;

oracle Invalide Packete / Packets

SELECT owner,object_name,subobject_name,object_type, status FROM all_objects WHERE status='INVALID';

einfaches / simple power shell script

[CmdletBinding()] param( [Parameter(Mandatory=$True)] [string[]] $Computername='localhost' ) Get-VM -ComputerName $Computername with help: <# .Synopsis short Explanation .Description long Explanation .Parameter Computername paramdesc .Example getVm localhost #> [CmdletBinding()] param( [Parameter(Mandatory=$True)] [string[]] $Computername='localhost' ) Get-VM -ComputerName $Computername

nützliche power shell cmdlets

update-help help alias Import-PSSession New-PSSession Get-PSSession help exit-PSSession -ShowWindow icm $env:PSModulePath -split ";" 's1','s2' | foreach {write-output $_}

vs (visual studio) shotcuts

shift Alt Enter ... Fenster groß / klein
 Vs2013: Alt +Pfeiltaste: Zeile verschieben
 Strg Alt Space ... toggle INtellisense Mode
 Strg-Shift-V Rotate Zwischenablage
 crt-shift Mausrad Zoom
 crt k,s : surround with
 crt F10 debug till curser

wichtige EInstellungen:
Optionen / Projekte und Solutions / Aktives Element im Projektmappen Explorer überwachen

powershell drivesize remaining

PS C:\Windows\system32> icm hv01,hv02,hv04 {Get-Volume} | select pscomputername, driveletter,sizeremaining | Format-Table -AutoSize

sql disk io

short reblogged from http://www.mssqltips.com/sqlservertip/2127/benchmarking-sql-server-io-with-sqlio/ 1) change param.txt to bigger testfile (10Gb) and correct drive: param.txt c:\testfile.dat 8 0x0 10240 #name threads cpuaffinity sizeInMB 2) create testfile: sqlio -kW -s5 -fsequential -o4 -b64 -Fparam.txt C:\Program Files (x86)\SQLIO>sqlio -kW -s5 -fsequential -o4 -b64 -Fparam.txt sqlio v1.5.SG parameter file used: param.txt file c:\testfile.dat with 8 threads (0-7) using mask 0x0 (0) 8 threads writing for 5 secs to file c:\testfile.dat using 64KB sequential IOs enabling multiple I/Os per thread with 4 outstanding size of file c:\testfile.dat needs to be: 10737418240 bytes current file size: 0 bytes need to expand by: 10737418240 bytes expanding c:\testfile.dat ... done. using specified size: 10240 MB for file: c:\testfile.dat initialization done CUMULATIVE DATA: throughput metrics: IOs/sec: 1327.03 MBs/sec: 82.93 VM on HyperV on SSD Raid,other examples: Al: IOs/sec: 3862.24 MBs/sec: 241.39 3) RANDOM WRITE TEST: (-d ... Drive -s seconds -t threads simultan) sqlio -dC -BH -kW -frandom -t1 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kW -frandom -t2 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kW -frandom -t4 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kW -frandom -t8 -o1 -s60 -b64 \testfile.dat C:\Program Files (x86)\SQLIO>sqlio -dC -BH -kW -frandom -t1 -o1 -s90 -b64 \testfile.dat sqlio v1.5.SG 1 thread writing for 90 secs to file C:\testfile.dat using 64KB random IOs enabling multiple I/Os per thread with 1 outstanding buffering set to use hardware disk cache (but not file cache) using current size: 10240 MB for file: C:\testfile.dat initialization done CUMULATIVE DATA: throughput metrics: IOs/sec: 1082.02 MBs/sec: 67.62 Al -t1: IOs/sec: 915.90 MBs/sec: 57.24 4)RANDOM READ TEST: sqlio -dC -BH -kR -frandom -t1 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kR -frandom -t2 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kR -frandom -t4 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kR -frandom -t8 -o1 -s60 -b64 \testfile.dat C:\Program Files (x86)\SQLIO>sqlio -dC -BH -kR -frandom -t1 -o1 -s30 -b64 \testfile.dat sqlio v1.5.SG 1 thread reading for 30 secs from file C:\testfile.dat using 64KB random IOs enabling multiple I/Os per thread with 1 outstanding buffering set to use hardware disk cache (but not file cache) using current size: 10240 MB for file: C:\testfile.dat initialization done CUMULATIVE DATA: throughput metrics: IOs/sec: 2409.36 MBs/sec: 150.58 Al: IOs/sec: 278.00 MBs/sec: 17.37

powershell examples

get-host update-help Get-ExecutionPolicy Get-PSDrive get-vm -ComputerName hv01,hv02,hv04 | where State -eq Off | sort name help New-SelfSignedCertificate -Examples New-SelfSignedCertificate -DnsName www.test.net -CertStoreLocation Cert:\LocalMachine\My $var=read-host "Enter a Computername" Write-Output "Test" Write-Host "Test" Write-Warning "Test" 's1','s2' | foreach {write-output $_}

values constructor sql server

select * from (values (10),(20)) as TabName(colName)

kiwi syslog daily logfile

Default/Actions/Log to file C:\Programme\Syslogd\Logs\Interfaceueberwachung\log%DateISO.txt

Sql Server Performance optimierung

set statistics io, time on;

linux :ende von dateien anschauen

Tail –f /var/log/syslog Oder Tail –f /var/log/messages

Anzahl der Zeilen: -n

tail filename -n

wireshark filters

udp ... all udp entries ip.addr == 192.168.1.1 ... all from / to 192.168.1.1

team foundation server command line remove Locks

Zurücksetzen von Locks/CheckOuts für andere Benutzer: Die Operationen können mit dem TF Kommando in einer Visual 2010 Konsole ausgeführt werden. Checkouts von jemanden anderen rückgängig machen: D:\TFS\TFSTestprojekt>tf undo $/Intern_Testprojekt_EP_V004 /workspace:WST08008;har mar

Sql Server Trigger to set Last Change Date & Last Change User

ALTER TRIGGER [dbo].[Holidays_SetModified] ON [dbo].[Holidays]
AFTER INSERT,UPDATE AS
BEGIN
  --PRINT '[sf_KISData].[sf_KISData_Cascade_Modified] BEGIN'
  DECLARE @id AS int
  DECLARE @mod AS DATETIME
  DECLARE @LUserId as int
  DECLARE curInserted CURSOR LOCAL FOR
        SELECT id, DateLastModified,LUserId FROM Inserted
  OPEN curInserted FETCH NEXT FROM curInserted INTO @id, @mod, @LUserId
  WHILE (@@FETCH_STATUS = 0)
   BEGIN
     IF NOT UPDATE(DateLastModified) OR @mod IS NULL
     BEGIN
        UPDATE Holidays SET DateLastModified= getutcdate(), LUserId=User_Id() WHERE id= @id      END
     FETCH NEXT FROM curInserted INTO @id, @mod, @LUserId
 END
 CLOSE curInserted
 --PRINT '[sf_KISData].[sf_KISData_Cascade_Modified] END'
END

Sql Server User Functions

SELECT USER_NAME(), SYSTEM_USER, CURRENT_USER,SESSION_USER, USER_ID(); select * from sys.sysusers

(freie) Antiviren software vergleich

free AVG: schnell und einfach zu bedienen, Netzlaufwerkscan möglich, im (chip test eher nicht so gut Trend Micro im Chip test beste Erkennungsrate, kann keine Netzlaufwerke scannen (zumindest nicht in trial version) und es gibt keine freie version free Avast: für Programmierer ungeeignet, da scannt nach jedem kompelieren, Netzlaufwerksscan ok

oracle sid

reblogged from http://www.bunyamindemir.com/?p=113 1) SELECT global_name FROM global_name; 2) SELECT INSTANCE_NAME FROM v$instance; 3) echo $ORACLE_SID; 4) SHOW PARAMETER INSTANCE_NAME; 5) SELECT sys_context('USERENV','INSTANCE_NAME') FROM dual; 6) SELECT ORA_DATABASE_NAME FROM dual;

Sql Server Datafile Size in MB / GB

SELECT DB_NAME(database_id) [DatabaseName], [name] AS [LogicalName], (size*8)/1024/1024 [GB] FROM sys.master_files order by size desc select (size*8)/1024 [MB],* from sys.database_files

power shell zip

#to create a new zip file: # Set-Content $arg[0] (“PK”+ [char]5 + [char]6 + (“$([char]0)”* 18)) #copy a file to new zip file $Shell=New-Object -ComObject Shell.Application $ZipFolder=$Shell.Namespace(‘F:\backup.zip’) $ZipFolder.CopyHere(‘C:\0bat\test.txt’)

ISO: Windows Burn Disc Image not visible / Datenträger Abbild brennen nicht sichtbar

Windows Explorer rigth mouse button Burn Disc Image not visible / rechte Maustaste Datenträger Abbild brennen nicht sichtbar => set Windows Explorer as standard program for ISO => rigth mouse button Burn Disc Image visible => Windows Explorer als Standard Programm setzen => rechte Maustaste Datenträger Abbild brennen sichtbar

Oracle Autoextend - Autogrow of Datafiles - automatisches Datenfile vergößern

ALTER TABLESPACE USERS ADD DATAFILE 'C:\oradata\dev4\USERS02.dbf' SIZE 500M AUTOEXTEND ON NEXT 500M; wenn das USERS02.dbf voll wird wird es um 500MB vergrößert when USERS02.dbf is fll oracle increases its size by 500MB

c# wpf fill comboox with enum

string s=cbScchVersion.SelectedValue.ToString(); object o=Enum.Parse(typeof(AppObject.ScchVersion),s); int i=(int)o; AppObject.ScchVer = (AppObject.ScchVersion) i;

Visual Studio Find and Replace Regular Expressions / Suchen und Ersetzten mit Regulären Ausdrücken (regex)

auf gefunde Ausdrücke greift man mit z.b. \0 zu, \0 gibt den ersten gefunden Ausdruck zurück. möchte man z.b. in allen DllImport Statements ,CallingConvention=CallingConvention.Cdecl hinzufügen, bsp: [DllImport(DRIVER_DLL_NAME, EntryPoint = "is_StopLiveVideo")] suchen: EntryPoint.*" ersetzen: \0, CallingConvention=CallingConvention.Cdecl Ergebnis: [DllImport(DRIVER_DLL_NAME, EntryPoint = "is_StopLiveVideo",CallingConvention=CallingConvention.Cdecl)]

PInvokeStackImbalance was detected (c# / .net) - fix : CallingConvention.Cdecl

[DllImport(DRIVER_DLL_NAME, EntryPoint = "is_ClearSequence")] // is_ClearSequence private static extern int is_ClearSequence(int hCam); [DllImport(DRIVER_DLL_NAME, EntryPoint = "is_ClearSequence", CallingConvention = CallingConvention.Cdecl)] // is_ClearSequence private static extern int is_ClearSequence(int hCam);

sql server tables unused space / unbenutzer Platz in Sql Server Tabellen, filesize / filegröße

--sql server tables unused space / unbenutzer Platz in Sql Server Tabellen: SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY UnusedSpaceKB desc --filegröße / filesize SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files where right(physical_name,3)='mdf' order by size desc

bootrec (windows 10)

bootrec /fixmbr
bootrec /fixboot
bootrec /rebuildbcd

wichtig: bios bootdevice muß harddisk sein => boot von hd einstellen und bootmenu aufrufen um dann von install disk zu booten, dann obige commandos ausführen win server braucht keine extra partition bcdboot c:\windows /s c: ... kopiert boot files von c:\windows\Boot auf C:\Boot wenn HyperV nicht automatisch startet: bcdedit /set hypervisorlaunchtype Auto

windows server 2012 Disk management

Server Manager unterstützt scheinbar keine MBR Initialisation, daher: power shell: Initialize-Disk 4 –PartitionStyle MBR

TSQL if then

--simplest / einfach if 1=1 Print 'Ja-Yes' else print 'nein-no' --one begin End block if 1=0 Begin Print 'Ja-Yes' End else print 'nein-no' --two ifs if 1=0 Begin Print '1:Ja-Yes' End else if 1=1 print '1:nein-no, 2:ja-yes' else print '1:nein-no, 2:nein-no'

vs2008 ssrs: Filter in "test1,test2,test3" not working in preview

the in clause is not working in preview, but on reportserver: I have a sharepoint datasource, and a dataset, which I filter in a table with th IN clause - doesn't display any matches, but when I deploy it is working

einfache / simple cte (common table expression)

with cte as ( select Year(GETDATE())*10000+MONTH(GetDate())*100+DAY(GetDate()) as intDate ) select * from cte

ssrs display subreport without data / Unterbericht ohne Daten anzeigen

ein Unterbericht ohne Daten wird nicht angezeigt (wenn alle Datasets in diesem Report keine Zeilen haben, wird allerdings als eigenständiger Bericht angezeigt) => um ihn dennoch anzeigen einfach ein Dummy Dataset mit select 'dummy' as dummy einfügen, das hat immer eine Zeile und damit wird der Subreport angezeigt. a subreport with all datasets having 0 rows isn't displayed (although as normal report is displayed) => add a dummy dataset which always returns a row to display (e.g. select 'dummy' as dummy )

ssrs gauge aggregate function report item

error / fehler:
The value expression for the gauge panel 'GaugePanel1' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

solution / lösung: change Indicator Properties / Values and States / States Measurement Unit from percentage to numeric

in Indicator Properties / Values and States / States Measurement Unit von Prozent auf numerisch ändern

ssrs: vs2008 vorschau fehler / visual stuido 2008 preview error

sometimes gauge panels with 3 traffic light indicators shows in visual stuidio 2008 preview all three lights on, although it should only show one - when deploying, and showing same data, only one traffic light of three is on, as it should be ... manchmal werden bei einem Gauge Panel mit 3 Ampel Indikatoren in der Visual Studio Vorschau alle 3 Ampellichter zugleich angezeigt - obowhl immer nur eines "leuchten" sollte. Wenn derselbe Report deployed wird und mit denselben Daten aufgerufen wird, wird korrekt nur ein Ampellicht (rot/grün/gelb) angezeigt ...

ssrs Tabelle mit nur Gruppierungszeilen ohne Details

eine neue Tabelle hat standardmäßig nur eine Zeile, die der Detailsgruppe "Detail1" zugeordnet ist. Geht man nun auf die Eigenschaften von "Details1" und fügt eine Gruppierung hinzu, verschwinden die waagrechten Detailszeilenzeichen und eine reine Gruppierungszeile entsteht (

sql server reporting services ssrs: transformation scope for state indicator must be specified

see property page of indicater scope property: e.g.: Scope=tablix1

ssrs not showing table

some grouping trouble can lead to not showing table at all - solution: delete group or check it

SSRS 2008 Dataset Filter

SSRS 2008 Dataset Filter can't include following expressions / können folgende Expressions nicht verwenden: Aggregatsfunktionen (=First(...)) ReportItems

hypervisor not running: bcdedit /set hypervisorlaunchtype Auto

bcdedit /set hypervisorlaunchtype Auto

Reporting Services: getting values of textboxes with reportitems - werte aus Textboxen auslesen mittels ReportItems: =ReportItems!textbox3.Value

z.b. / eg.: =ReportItems!textbox3.Value

ssrs 2008 shared sharepoint datasource doesn't work

using internal ssrs 2008 shared sharepoint datasource: no problem generating dataset using same shared datasource, doesnt work

sql server: Check Constraints ein / ausschalten - switch on / off (z.b. Foreign Keys)

alter table tablename nocheck constraint all alter table tablename check constraint all

sharepoint berechnete spalte bedingte Formatierung

Formel für berechnete Spalte (Annahme in Spalte Priority steht 01 02 oder 03) ="

•

" siehe

Expression Textbox SSRS 2008 (Sql Server Reporting Services)

wenn bei einer TextBox in SSRS 2008 (Sql Server Reporting Services) mit rechter Maustaste im Context Menü "Expression" ausgegraut ist und auch nicht in TextBoxProperties General kein Value vorhanden ist, kann man durch Eingabe von z.b. ="X" in die Textbox direkt im Designer den Menüpunkt Expression bzw. die Value Eigenschaft wieder aktivieren.

OU (Organisation Unit) of ACtive Directory User

In Active Directory Users and COmputers: Menu View / Advanced Features => USer Prperties Show Tab Object, There you finde the Canonical Name of object

SSRS Ampeln (KPI) in VS2008 BI

Manchmal "leuchten" alle Ampeln (trotz Gegenteiliger Bedingungen) eines SSRS Reports im Preview des VS2008 Business Intelligence Studios, dann hilft es das Visual Studio zu schießen und neu zu öffnen.

Bindingnavigator manual Delete

BindingNavigator.DeleteItem = null; //manual Delete this.bindingNavigatorDeleteItem.Click += new System.EventHandler(this.bindingNavigatorDeleteItem_Click); private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e) { if (DialogResult.Yes == MessageBox.Show("Wirklich löschen / Sure to delete ?", "Confirm / Bestätigen", MessageBoxButtons.YesNo)) { bindingsource.RemoveCurrent(); } }

sql server: restore many transactionlogs

example of restoring sql server transaction logs to a full backup: beispiel einiger Befehlszielen um transaktionslogs wiederherzustellen:
RESTORE LOG [CrmBak] FROM DISK = N'E:\SqlServerBackup\CRM\CRM_backup_2014_01_01_230431_6398282.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
RESTORE LOG [CrmBak] FROM DISK = N'E:\SqlServerBackup\CRM\CRM_backup_2014_01_02_230438_1759387.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
RESTORE LOG [CrmBak] FROM DISK = N'E:\SqlServerBackup\CRM\CRM_backup_2014_01_03_230429_5963993.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
to generate the restore command lines I used commandline: dir >dir.txt then with notepad++ I removed the date /time columns by pressing alt+marking with mouse, then used edit/Column editor to insert restore.... before the filename and ' WITH FILE=1 ... after the filename
um die Restore Befehle zu erzeugen, verwendetete ich den DOS BEfehel DIR: dir> dir.txt dann mit notepadd++ mit gedrückter alt taste mit der maus die unerwünschten spalten markeiren und löschen, dann mit edit / Column editor fügte ich vor dem dateinamen restore ... und nach dem DAteinamen 'WITH FILE=1 ... hinzu

wenn ein transaktionslog fehlt oder das zugrundeliegende full backup nicht in den Zeitraum des transaktionslogs fällt erhält man: Meldung 4305, Ebene 16, Status 1, Zeile 1 Das Protokoll in diesem Sicherungssatz beginnt bei LSN 29111000000034100001. Diese liegt für eine Anwendung auf die Datenbank nicht weit genug zurück. Eine frühere Protokollsicherung, die LSN 29015000000200600001 einschließt, kann wiederhergestellt werden. Meldung 3013, Ebene 16, Status 1, Zeile 1 RESTORE LOG wird fehlerbedingt beendet.

Sql Server TSQL Script: Backup ALL Databases (foult tolreant / Fehlertolerant)

--TODO: Enter Profilename and Email ADress at last statement DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name DECLARE @FEHLER VARCHAR(MAX) DECLARE @MSG VARCHAR(MAX) SET @path = 'F:\Backup\' SET @Msg='' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BEGIN TRY print 'BACKUP DATABASE '+@name+' TO DISK = '+@fileName; BACKUP DATABASE @name TO DISK = @fileName END TRY BEGIN CATCH set @Fehler= 'Error: BACKUP DATABASE '+@name+' TO DISK = '+@fileName print @Fehler set @MSG=@MSG+@Fehler END CATCH FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor --SEND Mail - use SSMS Management DatabaseMAil right click Send Testmail to get Profilename and test mail (and set up if needed EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName', @recipients = 'x@y.z', @body = 'Backup Bericht SQLServer (wenn leer keine Fehler)', @subject = @MSG ;

create sql server login when not exists

if (SELECT SUSER_ID('test')) is null begin create login test with password='pwd' end

disk queue length - Festplatten Warteschlange

In Windows resource Monitor, the disk queue length Counter should be lower then 2, if ist greater then 5 there is a serious Problem. experience: coping large amount of mixed files: disk queue length 1-2 coping large file on same disk: disk queue length 2-5 idle: 0 hyperV Server with raid 10, 13 virtual machines: disk queue length 1

Vsiaul Studio 2008 + Team Foundation Server 2010

Visual Studio 2008 mit TFS2010 nutzen:
1. SP1 für VS2008 installieren (wurde TeamFoundationExplorer nach SP1 installation installiert muss SP1 Setup nochmals ausgeführt werden.)

2. Compatibility Patch installieren (Visual Studio Team System 2008 Service Pack 1 Forward Compatibility Update for Team Foundation Server 2010)

3. Team Foundation Server als http://tfsservername:8080/tfs/DefaultCollection hinzufügen, oder in registry - richtige VS Version (9) statt 10 wählen:

 

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\TeamFoundation\Instances\tfs01]

"Uri"="http://tfs01:8080/tfs"
"Type"=dword:00000000

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\TeamFoundation\Instances\tfs01\Collections]

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\TeamFoundation\Instances\tfs01\Collections\DefaultCollection]

"Uri"="http://tfs01:8080/tfs/defaultcollection"
"Type"=dword:00000001

 

 

Desktop Symbole ohne Admin Rechte verschieben

Als normaler Benutzer darf man Desktop Symbole, die man nicht selbst angelegt hat, nicht löschen oder verschieben. Damit das doch geht, einfach vollzugriff auf C:\Users\Public\Desktop (bzw %public%\desktop) setzen, z.b. der Users group des lokalen Rechners oder gleich den Domain Benutzern Vollzugriff geben:

creating Table on secondary filegroup on sql server / eine Tabelle in der zweiten Filegroup anlegen am Sql Server

angenommen die Datenbank
asuming the database has a filegroup named "secondary":

create table tSecondary
(
    Id integer
)
on 'Secondary'

windows username, hostname, domainname by cmd (commandline)

benutzername / username:
echo %username%
or /oder
whoami

rechnername / hostname:
hostname

domainname:
echo %userdomain%
or / oder
systeminfo

Webservice for / für sharepoint List

sharepint List Adress: myserver/Intranet/mysite/Lists/mylist/Allitems.aspx
Webservice Adress: myserver/Intranet/mysite/_vti_bin/Lists.asmx

show row numbers in Visual Studio / Zeilenzahlen in VS anzeigen

Tools/Options
TextEditor/AllLanguages

sql server datenbank offline nehmen / take database offline / verbindungen lsöchen / kill connections

use master
go
ALTER DATABASE GBOv2_Reports_Test
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO


alle Verbindungen zu einer db killen / delete all Connections to a db
declare @kill varchar(8000) = '';
select @kill=@kill+'kill '+convert(varchar(5),spid)+';'
    from master..sysprocesses
where dbid=db_id('GBOv2_Reports_Test');
exec (@kill);


-- Show all processes of SQL Server / alle processe (Verbindungen) des SQL Servers anzeigen
select * from master..sysprocesses

windows 7 mbr reparieren - fixmbr - fdisk /fixmbr - bootrec.exe /fixmbr

In der Windows 7 wiederherstellungskonsole verwendet man bootrec.exe /fixmbr statt früher aus msdos fdsik /fixmbr

linux ftp server vsftpd

feststellen ob der ftp Dämon läuft:

netstat -a | grep ftp

in etc/VSFTPD liegt das config file
in etc/ftpusers sind user aufgelistet, die kein ftp machen dürfen (z.b. root)

user sieht normalerweise sein home dir

remote desktop

Allgemein

Version feststellen cat /etc/*-release

sudo reboot
sudo poweroff
sudo shutdown

Nano Texteditor

Select: ALT + M + A

Copy: ALT + 6

Paste: CTRL + U

Zeile ausschneiden: CRTL +K

SUCHEN: CRTL-W

User

user anzeigen: cat /etc/passwd
benutzer hinzufügen: sudo adduser BENUTZERNAME
Passwort ändern: sudo passwd BENUTZERNAME
Benutzer zu einer Gruppe hinzufügen: sudo usermod -aG gruppenname Username
gruppe hinzufügen: sudo groupadd www

Netzwerk

ifconfig

ftpclient: sudo apt-get ftp

ssh xxx.yyy.zzz.aaa -l loginname

Crontab (not crone)

alle einträge listen: crontab -l

edit bzw erstmalig erzeugen: crontab -e

*/1 * * * * $HOME/test.py >> test.log 2>&1

jede minute test.py starten und in test.log schreiben

test.py:

#!/usr/bin/env python

import time

print(time.strftime("%d.%m.%Y %H:%M:%S"))

Installierete Software

dpkp -l > pakete.txt

sudo apt-get update # Fetches the list of available updates

sudo apt-get upgrade # Strictly upgrades the current packages

sudo apt-get dist-upgrade # Installs updates (new ones)

Disk Partitionen

ls /dev/
ls /mnt/
mount

mmcblk0 ... SdCard0

sda ... SolidDriveA (Hard Disk A)

sudo fdisk -l

sudo blkid -o list

df -h  ... DiskFree -human readable

cat /proc/partitions
sudo fdisk /dev/mmcblk0
p ... list partition
q ... quit without writing

Dateien / Files

Datein mit rechte usw: ls -l
rechte: owner/gruppe/other

freier Speicherplatz auf den HDs: df -h
Dir anzeigen: ls

alle Dateien listen: ls -a
suchen: find / -Name ftp* sucht alle Dateien mit ftp

löschen: rm
kopieren von Bäumen (-r) ohne überschreiben existierender Dateien (-n) und Ausgabe der gerade kopierten Datei --verbose
cp --verbose -rn quelle ziel

Rechte anzeigen: ls-l   (owner gruppe andere, username gruppenname )
chmod 774 datei

Owner ändern:
chown -c user:gruppe datei

zu anderem linux rechner kopieren:
scp myfile user@192.168.1.8:

standard Dirs:

1. / – Root

Every single file and directory starts from the root directory.
Only root user has write privilege under this directory.
Please note that /root is root user’s home directory, which is not same as /.
2. /bin – User Binaries

Contains binary executables.
Common linux commands you need to use in single-user modes are located under this directory.
Commands used by all the users of the system are located here.
For example: ps, ls, ping, grep, cp.
3. /sbin – System Binaries

Just like /bin, /sbin also contains binary executables.
But, the linux commands located under this directory are used typically by system aministrator, for system maintenance purpose.
For example: iptables, reboot, fdisk, ifconfig, swapon
4. /etc – Configuration Files

Contains configuration files required by all programs.
This also contains startup and shutdown shell scripts used to start/stop individual programs.
For example: /etc/resolv.conf, /etc/logrotate.conf
5. /dev – Device Files

Contains device files.
These include terminal devices, usb, or any device attached to the system.
For example: /dev/tty1, /dev/usbmon0
6. /proc – Process Information

Contains information about system process.
This is a pseudo filesystem contains information about running process. For example: /proc/{pid} directory contains information about the process with that particular pid.
This is a virtual filesystem with text information about system resources. For example: /proc/uptime
7. /var – Variable Files

var stands for variable files.
Content of the files that are expected to grow can be found under this directory.
This includes — system log files (/var/log); packages and database files (/var/lib); emails (/var/mail); print queues (/var/spool); lock files (/var/lock); temp files needed across reboots (/var/tmp);
8. /tmp – Temporary Files

Directory that contains temporary files created by system and users.
Files under this directory are deleted when system is rebooted.
9. /usr – User Programs

Contains binaries, libraries, documentation, and source-code for second level programs.
/usr/bin contains binary files for user programs. If you can’t find a user binary under /bin, look under /usr/bin. For example: at, awk, cc, less, scp
/usr/sbin contains binary files for system administrators. If you can’t find a system binary under /sbin, look under /usr/sbin. For example: atd, cron, sshd, useradd, userdel
/usr/lib contains libraries for /usr/bin and /usr/sbin
/usr/local contains users programs that you install from source. For example, when you install apache from source, it goes under /usr/local/apache2
10. /home – Home Directories

Home directories for all users to store their personal files.
For example: /home/john, /home/nikita
11. /boot – Boot Loader Files

Contains boot loader related files.
Kernel initrd, vmlinux, grub files are located under /boot
For example: initrd.img-2.6.32-24-generic, vmlinuz-2.6.32-24-generic
12. /lib – System Libraries

Contains library files that supports the binaries located under /bin and /sbin
Library filenames are either ld* or lib*.so.*
For example: ld-2.11.1.so, libncurses.so.5.7
13. /opt – Optional add-on Applications

opt stands for optional.
Contains add-on applications from individual vendors.
add-on applications should be installed under either /opt/ or /opt/ sub-directory.
14. /mnt – Mount Directory

Temporary mount directory where sysadmins can mount filesystems.
15. /media – Removable Media Devices

Temporary mount directory for removable devices.
For examples, /media/cdrom for CD-ROM; /media/floppy for floppy drives; /media/cdrecorder for CD writer
16. /srv – Service Data

srv stands for service.
Contains server specific services related data.
For example, /srv/cvs contains CVS related data.

Webservice Proxy Klasse c# in Visual Stuido erzeugen

VS2008:
1) unter References add Service Referenc
2) unter Advanced Add Web Reference
3) Url eingeben (wdsl)  => zeigt Servicenamen und Methodennamen an, schlägt Web Reference Name vor, diesen anpassen
4) es wird Code unter WebReferences\WebReferenceName\Reference.cs erzeugt, die wichtigste Klasse heißt genauso wie das Service (unter 3 angezeigt)

.net Datacolumn Expressions Examples

Surname LIKE 'H*'

oracle simple package

Header:
CREATE OR REPLACE
PACKAGE PA_PDB_UTIL AS

PROCEDURE P_PDB_SeekId (inId IN VARCHAR2, ret OUT integer);

END PA_PDB_UTIL;

Body:
CREATE OR REPLACE
PACKAGE BODY PA_PDB_UTIL AS

  PROCEDURE P_PDB_SeekId (inId IN VARCHAR2, ret OUT integer) AS
  BEGIN
      dbms_output.put_line ('beginn!');
      dbms_output.put_line (inPannenId);
      select count(*) into ret from test where Id=inId;
      dbms_output.put_line (ret);
      dbms_output.put_line ('End');
    NULL;
  END P_PDB_SeekId;

END PA_PDB_UTIL;

Testen mit:
set serveroutput on;  
declare ret integer;
begin
 PA_PDB_UTIL.P_PDB_SeekPannenId('test', ret);
end;

oracle top cpu time sql

SET pages 3000

SET LINES  120

SET trimspool ON

SET trimout   ON

COLUMNS sql_text FORMAT a40 word_wrapped HEAD 'SQL|Text'

COLUMNS cpu_time       HEAD 'CPU|Time'

COLUMNS elapsed_time   HEAD 'Elapsed|Time'

COLUMNS disk_reads     HEAD 'Disk|Reads'

COLUMNS buffer_gets    HEAD 'Buffer|Gets'

COLUMNS rows_processed HEAD 'Rows|Processed'

TTITLE 'SQL-Statements nach CPU-Nutzung'

SELECT * FROM

     (SELECT sql_text,

             cpu_time/1000000 cpu_time,

             elapsed_time/1000000 elapsed_time,

             disk_reads,

             buffer_gets,

             rows_processed

      FROM   v$sqlarea Where SQL_Text Like '%T_PDB_%'

      ORDER BY cpu_time DESC

)

WHERE rownum <=25;

In welcher Tabelle ist die Spalte namens ? Where is column (in which table)

oracle:
select * from user_tab_columns where table_name like 'T_PDB%' and  Column_NAME like '%PRO%';

sql-server:
select c.name,t.name from sys.columns c join sys.tables t on c.object_id=t.object_id where c.name like 'Bem%'

Access Query 2 Oracle IIF

IIF ... CASE

    f.bla AS S_Bla,
    (
      CASE TO_CHAR(Reparaturdatum, 'YYYY')
        WHEN '1899'
        THEN '-'
        ELSE TO_CHAR(Reparaturdatum, 'DD.MM.YYYY')
      END)                   AS S_RDatum,
    f.Fallzaehler            AS S_Fallzaehler,

NZ ... NVL

screenshot registry Oracle 32bit Treiber / Driver

access auf ODBC Backend umstellen - move from Access to ODBC Backend

Beim Umstellen eines reinen Access Backends auf ein ODBC Backend (z.b. Oracle over ODBC) gibt es folgendes zu beachten:
1) recordset Find Operationen sind extrem langsam, da zuerst die ganze Tabelle in den Access Speicher geladen wird und dort dann gesucht wird. Besser das Recordset gleich mit der richtigen WHERE Bedingung aufmachen:
    Set rs= New ADODB.Recordset
    rs.Open "select * from mytable where ID='" & 923 & "'", connection, adOpenKeyset, adLockReadOnly
    statt:
    rs.Find "ID= '" & 923 & "'", , adSearchForward, adBookmarkFirst

2) wenn man Sätze mit rs.AddNew / rs.Update hinzufügt, so werden diese zwar im Backend hinzugefügt aber nicht im Recordset = nochmal nachladen

enumerate enum

        enum eAbgeschlossen
        {
            Abgeschlossen_Closed,
            Alle_All,
            NichAbgeschlossen_NotClosed
        }

            foreach (eAbgeschlossen e in Enum.GetValues(typeof(eAbgeschlossen)).Cast())
            {
                cbAbgeschlossenFilter.Items.Add(e.ToString()); //adding to Combobox
            }

The server encountered an error while creating Virtual Machine (Hyper V)

When creating a new virtual Machine on Hyper V Server (Windows 8, Server 2012) sometimes ths error occurs:

The server encountered an error while creating Virtual Machine
The operation failed.
An unexpected error occurred: Logon failure: the user has not been granted the requested logon type at this computer. (0x80070569).

Work around: Hyper-V Virtual Machine Management Service restart.

-----------------------------------------------------------------------------------------------------------

Beim Erzeugen einer neue Virtuellen Maschine am Hyper V Server (Windows 8, Windwos Server 2012) tritt manchmal folgender Fehler auf:

The server encountered an error while creating Virtual Machine
The operation failed.
An unexpected error occurred: Logon failure: the user has not been granted the requested logon type at this computer. (0x80070569).

Work Around: Hyper-V Virtual Machine Management Service restarten.

The trust relationship between this workstation and the primary domain failed

Wenn beim Login der Fehler
The trust relationship between this workstation and the primary domain failed
erscheint, einfach den Computer aus der Domäne nehmen (in eine Workgroup), neustarten und wieder in die Domäne hängen

Login Error:
The trust relationship between this workstation and the primary domain failed
remove the Computer from the domän (to a workgroup), restart and join the domain again

CRT ALT DEL Remote Desktop: CRT ALT END

Um CRT ALT DEL über Remote Desktop (RDS) zu schicken, verwende CRT-ALT_END

Entity Framework liest statt unterscheidlicher Datensätze mehrfach den gleichen aus

Wenn bei einer view der Primary Key fehlt, so liefert das Entity Framework eventuell immer den gleichen Datensatz.
Man muß dann einen künstlichen Primary Key scahffen, z.b. mittels

IsNUll(ROW_NUMBER() OVER(ORDER BY Lastname, FirstName),-1) AS 'ID'

 und andere Spalten, die als Primary Key herangezogen werden mit NullIf(keyKandidat,-1) als Kandidat für den Primary Key aussschließen. Dann die View im Model löschen und neu hinzufügen.

asp.net Identy replaces membership in Vs2013 / ap.net idenity ersetzt membership provider in Visual Studio 2013

asp.net identity hat auf sql server ein einfacheres schema als der membership provider und erzeugt die Tabellen selbst wenn sie erstmals benötigt werden (Create Table Recht vorausgesetzt), wenn nicht vorhanden, man braucht also kein asp.net config tool. funktioniert out of the box

asp.net identity has a more simple schema on sql server than membership rpovider and creates the tables, if necesary (needs create table privileg) - don't need asp.net config tool anymore

how to query grace periode days left of terminal services / Abfrage der verbleibenden gratis Tage von Remote Desktop Services

wmic /namespace:\\root\CIMV2\TerminalServices PATH Win32_TerminalServiceSetting WHERE (__CLASS !="")  CALL GetGracePeriodDays


C:\Windows\system32>wmic /namespace:\\root\CIMV2\TerminalServices PATH Win32_TerminalServiceSetting WHERE (__CLASS !="")
 CALL GetGracePeriodDays
Executing (\\myserver\root\CIMV2\TerminalServices:Win32_TerminalServiceSetting.ServerName="BRIL_INT")->GetGracePeriodDay
s()
Method execution successful.
Out Parameters:
instance of __PARAMETERS
{
        DaysLeft = 74;
        ReturnValue = 0;
};

dIRECTORY MIT vba AUSLESEN - READ DIRECTORY IN vba

Dim FileName
FileName = FileSystem.Dir("C:\")
While ("" <> FileName)
  Debug.Print FileName
  FileName = FileSystem.Dir() 'Read next entry by passing no params
Wend