Thursday, November 8, 2018

MSSQL search and replace value in all tables

It's little script for replace value in database


declare @from as nvarchar(50);
declare @to as nvarchar(50);

set @from  =[dbo].[getstringUUIDFromString] ('c3a5fb3e-745a-11e8-a209-0050568411f7'); 
set @to  =[dbo].[getstringUUIDFromString] ('c3a5fb3e-745a-11e8-a209-0050568411f7'); 



DECLARE @CURSOR CURSOR;DECLARE @script AS NVARCHAR(MAX);
SET @CURSOR = CURSOR SCROLL
FOR
 SELECT 
 '
 if exists (select top 1 * from  ['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+'] where '+isc.COLUMN_NAME + ' in ('+@from+'))
 begin
 print ''['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+']'';

 
 update ['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+']
 set '+isc.COLUMN_NAME + ' = '+@to+'
 where '+isc.COLUMN_NAME + ' in ('+@from+')
end;
 '
FROM information_schema.columns  isc
where  TABLE_CATALOG='DiachokERP' and TABLE_SCHEMA='dbo'
and data_type = 'binary' and CHARACTER_MAXIMUM_LENGTH=16

OPEN @CURSOR

FETCH NEXT
FROM @CURSOR
INTO @script
 

WHILE @@FETCH_STATUS = 0
BEGIN

 EXEC(@script);
 --print @script
 FETCH NEXT
 FROM @CURSOR
 INTO @script
END
CLOSE @CURSOR
DEALLOCATE @CURSOR;


and converting string from uid to uuid

CREATE function [dbo].[getstringUUIDFromString](@stringUUID as varchar(50))
returns nvarchar(50)
as
begin
--Возврат Сред(GUID, 20, 4) + Прав(GUID, 12) + Сред(GUID, 15, 4) + Сред(GUID, 10, 4) + Лев(GUID, 8);
declare @buffer nvarchar(50)
select @buffer ='0x'+ substring(@stringUUID,20,4)+right(@stringUUID,12)+substring(@stringUUID,15,4)+substring(@stringUUID,10,4)+left(@stringUUID,8)

return @buffer
end

GO

Saturday, October 13, 2018

add packages gdi plus to net core on macos

brew install mono-libgdiplus


I took it here

Sunday, September 2, 2018

Core install

install old drivers

pnputil.exe -i -a .\driversname.inf


install certs

Set-Location -Path Cert:\LocalMachine\Root\
Import-Certificate -FilePath "C:\install\FOR_SQL_1C\certnameshutdown -r.cer"

mount iso with show disk

Mount-DiskImage -ImagePath 'D:\ISO\Windows Server 2012 Trial\9200.16384.WIN8_RTM.120725-1247_X64FRE_SERVER_EVAL_EN-US-HRM_SSS_X64FREE_EN-US_DV5.ISO' -StorageType ISO -PassThru | Get-Volume


update SQL

.\setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /Action=Upgrade /InstanceName=MSSQLServer

update key(licence) SQL

.\setup.exe /q /ACTION=EditionUpgrade /INSTANCENAME=MSSQLSERVER /PID="insert your key" /IACCEPTSQLSERVERLICENSETERMS

Install patch

.\SQLServer2017-KB4464082-x64.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances

Sunday, August 5, 2018

Arduino

If You have next error in VS Code
"sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\sketch\build\preproc\ctags_target_for_gcc_minus_e.cpp: The system cannot find the path specified.
[Error] Exit with code=1"

information this https://github.com/Microsoft/vscode/issues/38985
you need change setting.json.
Set "output": "../build"


{
"sketch": "app.ino",
"board": "arduino:avr:leonardoeth",
"output": "../build"
}


and delete all folders in folder "build/sketch"

Friday, June 29, 2018

useful step for ASP Core+Angular

I tried made ASP Core c# & Angular 6 & material.
It is only one good variant:
Download VS Code (work for windows & mac)
Download Node.js
Download SDK .Net Core 2.1
After install Node --> reboot
and install angular/cli https://cli.angular.io/

after
Create core project 'dotnet new webapi -o ProjectName'
Create Angular project 'ng new ProjectName'
Open project in VS Code & install extentions

connect to gitlab

git init
git remote add origin git or https
git add .
git commit -m "Initial commit"
git push -u origin master
//if error try this
git push --set-upstream origin master --force
run angular project 'npm start' -> go to ref 'http://localhost:4200'
If work -> show Angular page

In VSCode add nuget package manager
and add next packages:
- NLog.Web.AspNetCore
- Swashbuckle.AspNetCore.SwaggerGen
- Swashbuckle.AspNetCore.SwaggerUI
- Swashbuckle.AspNetCore.Swagger
- Microsoft.VisualStudio.Web.CodeGeneration.Tools

and add next code to csproj file

<ItemGroup>
    <Content Update="nlog.config">
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </Content>
  </ItemGroup>
  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
    <DocumentationFile>bin\Debug\netcoreapp2.0\WebCoreAPI.xml</DocumentationFile>
  </PropertyGroup>

  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Release|AnyCPU'">
    <DocumentationFile>bin\Release\netcoreapp2.1\WebCoreAPI.xml</DocumentationFile>
  </PropertyGroup>
change project code



open 'startup.cs' and exchange next code
 public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
            services.AddSpaStaticFiles(
                 c =>
     {
         c.RootPath = "wwwroot";
     }
   );
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v0", new Info { Title = "My API v0", Version = "v0" });
                c.SwaggerDoc("v1", new Info { Title = "My API v1", Version = "v1" });
                //string PathXML = System.AppDomain.CurrentDomain.BaseDirectory + @"WebCoreAPI.xml";
                //c.IncludeXmlComments(PathXML);
                var basePath = AppContext.BaseDirectory;
                var xmlPath = Path.Combine(basePath, "WebCoreAPI.xml");
                c.IncludeXmlComments(xmlPath);
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseDefaultFiles();
            app.UseStaticFiles();
            app.UseSpaStaticFiles();
            app.UseSwagger(c =>
            {
                //Change the path of the end point , should also update UI middle ware for this change                
                c.RouteTemplate = "/api-docs/{documentName}/swagger.json";
            });
            app.UseSwaggerUI(c =>
            {
                //c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");
                c.RoutePrefix = "api-docs";
                c.SwaggerEndpoint("v0/swagger.json", "Api v0");
                c.SwaggerEndpoint("v1/swagger.json", "Api v1");

            });

            app.UseMvc(
            //     routes =>
            // {
            //     routes.MapRoute(
            //   name: "default",
            //   template: "{controller=Home}/{action=Index}/{id?}");

            //     routes.MapSpaFallbackRoute(
            //   name: "spa-fallback",
            //   defaults: new { controller = "Home", action = "Index" });
            // }
            );

            app.UseSpa(spa =>
            {
                // To learn more about options for serving an Angular SPA from ASP.NET Core,
                // see https://go.microsoft.com/fwlink/?linkid=864501

                spa.Options.SourcePath = "wwwroot";

                if (env.IsDevelopment())
                {
                    // spa.UseAngularCliServer(npmScript: "start");
                    spa.UseProxyToSpaDevelopmentServer("http://localhost:4200");
                }
            });
        }
    }

open Program.cs and change next:

 public class Program
    {
        public static void Main(string[] args)
        {
           var logger = NLog.Web.NLogBuilder.ConfigureNLog("nlog.config").GetCurrentClassLogger();
            try
            {
                logger.Debug("init main");
                BuildWebHost(args).Run();
            }
            catch (Exception ex)
            {
                //NLog: catch setup errors
                logger.Error(ex, "Stopped program because of exception");
                throw;
            }
            finally
            {
                // Ensure to flush and stop internal timers/threads before application-exit (Avoid segmentation fault on Linux)
                NLog.LogManager.Shutdown();
            }
        }

         public static IWebHost BuildWebHost(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>()
                .ConfigureLogging(logging =>
                {
                    logging.ClearProviders();
                    logging.SetMinimumLevel(Microsoft.Extensions.Logging.LogLevel.Trace);
                })
                .UseNLog()  // NLog: setup NLog for Dependency injection
                .Build();
    }


change angular.json, set correct output path => "outputPath": "wwwroot"

change index.html, set correct path

<base href="./">


and "HAPPY", after run debug code it's work.
Next trable
npm install --save @angular/material@6.2.1 @angular/cdk@6.2.1
ng add @angular/material@6.2.1
ng generate @angular/material:material-nav --name app-nav
and generate component
ng generate component searchserial
ng g service api - generate service api
add bootstrap https://www.intertech.com/Blog/using-bootstrap-4-with-angular/
use bootstrap https://www.c-sharpcorner.com/article/how-to-install-jquery-popper-and-bootstrap-in-angular/

before public in iis you need build ng project

ng build --prod --aot

information about API versions

Friday, June 8, 2018

SQL Server Statistics: Maintenance and Best Practices

SQL Server Statistics: Maintenance and Best Practices:
This script show bad statistics for database
"WITH    autostats ( object_id, stats_id, name, column_id )   AS ( SELECT   sys.stats.object_id ,   sys.stats.stats_id ,   sys.stats.name ,   sys.stats_columns.column_id   FROM     sys.stats   INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id   AND sys.stats.stats_id = sys.stats_columns.stats_id   WHERE    sys.stats.auto_created = 1   AND sys.stats_columns.stats_column_id = 1   )   SELECT  OBJECT_NAME(sys.stats.object_id) AS [Table] ,   sys.columns.name AS [Column] ,   sys.stats.name AS [Overlapped] ,   autostats.name AS [Overlapping] ,   'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id)   + '].[' + OBJECT_NAME(sys.stats.object_id) + '].['   + autostats.name + ']'   FROM    sys.stats   INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id   AND sys.stats.stats_id = sys.stats_columns.stats_id   INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id   AND sys.stats_columns.column_id = autostats.column_id   INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id   AND sys.stats_columns.column_id = sys.columns.column_id   WHERE   sys.stats.auto_created = 0   AND sys.stats_columns.stats_column_id = 1   AND sys.stats_columns.stats_id != autostats.stats_id   AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0"

'via Blog this'

Monday, May 14, 2018

PowerShell change service autorestart

This is script for set autorestart service

#Change these three to match up to the extracted registry data and run as Admin
$YourInput = "00,00,00,00,00,00,00,00,00,00,00,00,03,00,00,00,14,00,00,00,01,00,00,00,60,ea,00,00,01,00,00,00,60,ea,00,00,01,00,00,00,60,ea,00,00"

$RegPath   = 'HKLM:\SYSTEM\ControlSet001\services\servicename'
$AttrName  = "FailureActions"

$hexified = $YourInput.Split(',') | % { "0x$_"}
#IF(!(Test-Path ($RegPath+'\'+$AttrName)))
if (Get-ItemProperty -Path $RegPath -Name $AttrName -ErrorAction SilentlyContinue)
{
    'set'
    Set-ItemProperty -Path $RegPath -Name $AttrName -Value ([byte[]]$hexified)
}
else
{
    'new'
    New-ItemProperty -Path $RegPath -Name $AttrName -PropertyType Binary -Value ([byte[]]$hexified)
}

Wednesday, May 9, 2018

MS SQL script for compress all database

This script was created for the development server. We have high-speed disks and not enough space, and developers need more databases.
Run this script in MS SQL for compress all database


Declare @DatabaseName as NvarChar(500);
Declare @CURSOR CURSOR;
Declare @SQL nvarchar(max);


SET @CURSOR = CURSOR SCROLL
FOR

SELECT name AS [Database Name]
--,recovery_model_desc AS [Recovery Model]
FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb')
and name not like '%veeam%'
and recovery_model_desc!='SIMPLE'

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
print @DatabaseName;
SET @SQL='';
SET @SQL= @SQL+
'USE [master];

ALTER DATABASE ['+@DatabaseName+'] SET RECOVERY SIMPLE WITH NO_WAIT;

use ['+@DatabaseName+'];
EXEC sp_MSforeachtable ''ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)'';

EXEC sp_MSforeachtable ''ALTER TABLE ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'';

USE ['+@DatabaseName+'];

DBCC SHRINKDATABASE(N'''+@DatabaseName+''', 1 );
';
print @SQL
EXEC(@SQL);



FETCH NEXT FROM @CURSOR INTO @DatabaseName
END
CLOSE @CURSOR

GO

Wednesday, April 25, 2018

Arduino button sketch

I decided to work out arduino.
I had a problem with sticking the buttons, here is an attempt to solve the problem

const int relayPin = 7;
const int buttonPin = 2;
bool buttonpress = false;
bool stateswitch = false;


int buttonState = 0;
int lastButtonState = 0;
unsigned long buttonOnTime;

void setup() {
  // put your setup code here, to run once:
  Serial.begin(9600);
  pinMode(relayPin, OUTPUT);
  pinMode(buttonPin, INPUT);
}

void loop() {

  buttonState = digitalRead(buttonPin);
  if ((buttonState == HIGH) && (millis() - buttonOnTime > 500)) {
    buttonOnTime = millis();
    if (stateswitch)
    {
      Serial.println("Button off");
    }
    else
    {
      Serial.println("Button on");
    }
    stateswitch = !stateswitch;
    //    delay(500);
  } else if ((buttonState == HIGH))
  {
    buttonOnTime = millis();
  }



  //
  //  buttonState = digitalRead(buttonPin);
  //   if ((buttonState == HIGH)) {
  //
  //    Serial.print("Button:");
  //    Serial.println(buttonState);
  //    Serial.print("Relay:");
  //    stateswitch = digitalRead(relayPin);
  //    Serial.println(stateswitch);
  //    digitalWrite(relayPin, !stateswitch);
  //
  //
  //   }
  // delay(200);
  //
  //  if (buttonState == HIGH) {
  //    //Serial.println("HIGH");
  //    digitalWrite(relayPin, HIGH);
  //  }
  //  else
  //  {
  //    //Serial.println("LOW");
  //    digitalWrite(relayPin, LOW);
  //  }
  // put your main code here, to run repeatedly:
  //  digitalWrite(relayPin, LOW);
  //  delay(5000);
  //  digitalWrite(relayPin, HIGH);
  //  delay(5000);
}

Monday, March 26, 2018

Powershell скрипт для ускорения настройки 1С

Get-NetFirewallRule -DisplayName "Remote Desktop*" | Set-NetFirewallRule -enabled true
Get-NetFirewallRule -DisplayName "Удаленный рабочий стол*" | Set-NetFirewallRule -enabled true


$firewallRuleMSSQL = Get-NetFirewallRule -DisplayName 'MS SQL'
if (-Not $firewallRuleMSSQL)
{New-NetFirewallRule -DisplayName 'MS SQL' -Enabled true -LocalPort 1433 -Profile DOMAIN -Protocol TCP}

$firewallRuleragent = Get-NetFirewallRule -DisplayName 'ragent.exe'
if (-Not $firewallRuleragent)
{New-NetFirewallRule -DisplayName 'ragent.exe' -Enabled true -Program "c:\Program Files\1cv8\8.3.10.2561\bin\ragent.exe" -Profile DOMAIN}

$firewallRulermngr = Get-NetFirewallRule -DisplayName 'rmngr.exe'
if (-Not $firewallRulermngr)
{New-NetFirewallRule -DisplayName 'rmngr.exe' -Enabled true -Program "c:\Program Files\1cv8\8.3.10.2561\bin\rmngr.exe" -Profile DOMAIN}

$firewallRulerphost = Get-NetFirewallRule -DisplayName 'rphost.exe'
if (-Not $firewallRulerphost)
{New-NetFirewallRule -DisplayName 'rphost.exe' -Enabled true -Program "c:\Program Files\1cv8\8.3.10.2561\bin\rphost.exe" -Profile DOMAIN}

Add-LocalGroupMember -Group "Администраторы" -Member "kt\1Cv8", "kt\SystemVeeam"

#Прописываем зависимость sql от виртуального диска
$REGMSSQL = Get-ItemProperty -Path HKLM:\SYSTEM\ControlSet001\Services\MSSQLSERVER -name DependOnService
if (-Not $REGMSSQL)
{New-ItemProperty -Path "HKLM:\SYSTEM\ControlSet001\Services\MSSQLSERVER" -Name "DependOnService" -value "SPVVEngine" -PropertyType MultiString}
else
{Set-ItemProperty -Path "HKLM:\SYSTEM\ControlSet001\Services\MSSQLSERVER" -Name "DependOnService" -Value "SPVVEngine"}

#Прописываем зависимость 1С от скула
$REGMSSQL = Get-ItemProperty -Path "HKLM:\SYSTEM\ControlSet001\Services\1C:Enterprise 8.3 Server Agent (x86-64)" -name DependOnService
if (-Not $REGMSSQL)
{New-ItemProperty -Path "HKLM:\SYSTEM\ControlSet001\Services\1C:Enterprise 8.3 Server Agent (x86-64)" -Name "DependOnService" -value "Tcpip", "Dnscache", "lanmanworkstation", "lanmanserver", "MSSQLSERVER" -PropertyType MultiString}
else
{Set-ItemProperty -Path "HKLM:\SYSTEM\ControlSet001\Services\1C:Enterprise 8.3 Server Agent (x86-64)" -Name "DependOnService" -Value "Tcpip", "Dnscache", "lanmanworkstation", "lanmanserver", "MSSQLSERVER"}

Thursday, February 1, 2018

PDFtoPrinter for Windows

История: 1С и автоматизация работы под управляемые формы. Необходима печать PDF, фононо. Так и не нашел нормальной реализации.
Берем этот файлик https://www.dropbox.com/s/e1ttfk8mfy69ck1/PDFtoPrinter.exe?dl=1
качаем програмку для формирование настроек https://www.tracker-software.com/product/pdf-xchange-viewer, ставим, делаем настройки печати и сохраняем их в том же каталоге под именем "PDF-XChange Viewer Settings.dat", что и exe.
В отличии от adobe reader и foxit не оставляет зависших процессов в памяти, плюс как уже писал ранее возможна настройки печати


PDFtoPrinter for Windows:

'via Blog this'