Thursday, 14 November 2024

to shrink all databases on your server

DECLARE @DB_Name NVARCHAR(255);

DECLARE @DB_LgoFileName NVARCHAR(255);

DECLARE @ScriptsList NVARCHAR(max);


DECLARE db_cursor CURSOR FOR

SELECT name

FROM sys.databases

WHERE  state_desc = 'ONLINE' and name NOT IN ('master', 'model', 'msdb', 'tempdb'); -- Exclude system databases


OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @DB_Name;


WHILE @@FETCH_STATUS = 0

BEGIN

    SET @DB_LgoFileName = (SELECT name FROM sys.master_files WHERE type_desc = 'LOG' AND database_id = DB_ID(@DB_Name));

set @DB_LgoFileName='['+@DB_LgoFileName+']';

set @DB_Name='['+@DB_Name+']';

print '@DB_Name: '+ @DB_Name;

print '@DB_LgoFileName: '+ @DB_LgoFileName;


set @ScriptsList= 'USE ' + @DB_Name + ';';


    -- Shrink the log file

    set @ScriptsList=@ScriptsList+' ' +('DBCC SHRINKFILE (' + @DB_LgoFileName + ', 1);');


    -- Shrink the database

    set @ScriptsList=@ScriptsList+' '+('DBCC SHRINKDATABASE (' + @DB_Name + ');');


    -- Set recovery model to SIMPLE

    set @ScriptsList=@ScriptsList+' '+('ALTER DATABASE ' + @DB_Name + ' SET RECOVERY SIMPLE;');


    -- Shrink the log file again

    set @ScriptsList=@ScriptsList+' '+('DBCC SHRINKFILE (' + @DB_LgoFileName + ', 1);');


    -- Set recovery model back to FULL

    set @ScriptsList=@ScriptsList+' '+('ALTER DATABASE ' + @DB_Name + ' SET RECOVERY FULL;');

EXEC(@ScriptsList);

    FETCH NEXT FROM db_cursor INTO @DB_Name;

END


CLOSE db_cursor;

DEALLOCATE db_cursor;


Tuesday, 13 February 2024

Entity framework scaffolding oracle database first

First:

install the following packages, be careful of package version. 












Second:

open package manger and run the following command after you update with yours

Scaffold-DbContext "Data Source=serverip/sid;Persist Security Info=True;User ID=un;Password=pw;Connection Timeout=0" Oracle.EntityFrameworkCore -OutputDir Model -Context "ContextName" -Tables  table1, table2....,tablen

If you didn't list the tables names this command will generate for all db objects 


Monday, 23 October 2023

Enable Disable Identity Field to migrate data

CREATE TABLE [dbo].[AnIdentityTable](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [_Name] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_AnIdentityTable] PRIMARY KEY CLUSTERED

(

       [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

 

-- Enable insert values in the Identity Field

SET IDENTITY_INSERT [dbo].[AnIdentityTable] ON;

 

INSERT INTO [dbo].[AnIdentityTable] (Id, _Name)

VALUES (5, 'Some Value');

 

-- Disable insert values in the Identity Field, after disabling it starts frm the max value

SET IDENTITY_INSERT [dbo].[AnIdentityTable] OFF; 

Monday, 9 October 2023

Python to MSSQL

 import pandas as pd

import pyodbc
from sqlalchemy import create_engine # pip install SQLAlchemy
from sqlalchemy.engine import URL
import pypyodbc # pip install pypyodbc

# Database connection settings
server = 'serverName'
database = 'DB_Name'
username = 'un'
password = 'pw'


from sqlalchemy.types import (
    Boolean,
    Date,
    DateTime,
    Float,
    Integer,
    SmallInteger,
    String,
    Unicode,
    BigInteger,
)
def get_sqlalchemy_types(df_types):
    # dictionary mapping pandas dtypes to SQLAlchemy types
    dtype_to_sqlalchemy_type = {
        # 'int64': BigInteger,
        # 'float64': Float,
        'object': Unicode(length=255),
        # 'datetime64[ns]': DateTime
    }    
    # create an empty dictionary to store column names and SQLAlchemy types
    col_dict = {}    
    # loop through columns and add name and type to dictionary
    for col in df_types.columns:
        col_dtype = str(df_types[col].dtype)
        if col_dtype in dtype_to_sqlalchemy_type.keys():
            sqlalchemy_type = dtype_to_sqlalchemy_type[col_dtype]
            col_dict[col] = sqlalchemy_type
   
    return col_dict

conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};
                DATABASE={database};UID={username};PWD={password}'

conn = pyodbc.connect(conn_str)
connection_url = URL.create('mssql+pyodbc', query={'odbc_connect': conn_str})
enigne = create_engine(connection_url, module=pypyodbc)
result.to_sql('tablename', enigne, if_exists='append', index=False, schema='dbo',
dtype=get_sqlalchemy_types(result),
chunksize=100)
conn.close()

Wednesday, 4 October 2023

Sunday, 7 May 2023

MSSQL split column values by delimiter and get the last value

 

SELECT BQCODE, REVERSE(LEFT(REVERSE(BQCODE), CHARINDEX('/', REVERSE(BQCODE)) - 1))

FROM [jordan_settelement_pacels]

Connect Devexpress Dashboard to SQL datasource and Run Custom queries

 Add the following code in the page load function to allow the devexpress dashboard connect to database and to create custome queries 


protected void Page_Load(object sender, EventArgs e) {

            DashboardFileStorage dashboardFileStorage = new DashboardFileStorage("~/App_Data/Dashboards");

            ASPxDashboard1.SetDashboardStorage(dashboardFileStorage);

 

            // Uncomment this string to allow end users to create new data sources based on predefined connection strings.

            ASPxDashboard1.SetConnectionStringsProvider(new DevExpress.DataAccess.Web.ConfigFileConnectionStringsProvider());

           

            DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();

           

            // Registers an SQL data source.

            //DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQL Data Source", "NWindConnectionString");

            //SelectQuery query = SelectQueryFluentBuilder

            //    .AddTable("SalesPerson")

            //    .SelectAllColumnsFromTable()

            //    .Build("Sales Person");

            //sqlDataSource.Queries.Add(query);

            //dataSourceStorage.RegisterDataSource("sqlDataSource", sqlDataSource.SaveToXml());

           

            // Registers an Object data source.

            DashboardObjectDataSource objDataSource = new DashboardObjectDataSource("Object Data Source");

            objDataSource.DataId = "Object Data Source Data Id";

            dataSourceStorage.RegisterDataSource("objDataSource", objDataSource.SaveToXml());

           

            // Registers an Excel data source.

            DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source");

            excelDataSource.ConnectionName = "Excel Data Source Connection Name";

            excelDataSource.SourceOptions = new ExcelSourceOptions(new ExcelWorksheetSettings("Sheet1"));

            dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml());

            

            ASPxDashboard1.SetDataSourceStorage(dataSourceStorage);

            ASPxDashboard1.EnableCustomSql = true;

            ASPxDashboard1.EnableJsonDataSource = true;

            ASPxDashboard1.EnableTextBoxItemEditor = true;

            ASPxDashboard1.AllowExecutingCustomSql= true;

            ASPxDashboard1.AllowCreateNewDashboard = true;

            ASPxDashboard1.AllowCreateNewDashboard = true;

            ASPxDashboard1.AllowCreateNewJsonConnection = true;

            ASPxDashboard1.AllowExportDashboard = true;

            ASPxDashboard1.AllowExportDashboardItems = true;

            ASPxDashboard1.AllowInspectAggregatedData = true;

            ASPxDashboard1.AllowInspectRawData = true;

            ASPxDashboard1.AllowMaximizeItems = true;

            ASPxDashboard1.AllowOpenDashboard = true;

            ASPxDashboard1.AllowReadUnlistedColumnsFromClientApi = true;

 

        }

 

        protected void DataLoading(object sender, DataLoadingWebEventArgs e) {

            if(e.DataId == "Object Data Source Data Id") {

                e.Data = Invoices.CreateData();

            }

        }

 

        protected void ConfigureDataConnection(object sender, ConfigureDataConnectionWebEventArgs e) {

            if(e.ConnectionName == "Excel Data Source Connection Name") {

                ExcelDataSourceConnectionParameters excelParameters = (ExcelDataSourceConnectionParameters)e.ConnectionParameters;

                excelParameters.FileName = HostingEnvironment.MapPath(@"~/App_Data/Sales.xlsx");

            }

        }