Find table and column name in MSSQL

It is time consuming job to browse each and every tables and columns to find required tables  and it's even difficult to search columns name in any database. There's a easy way to do the search.

For example, We want to search all the tables which contains user in the table names in sanjeebDB database:

select * from sanjeebDB.INFORMATION_SCHEMA.TABLES 

where TABLE_NAME like '%user%' 

     ORDER by TABLE_NAME  

Next example, we want to search all the tables which contains 'username' a column name:

select * from sanjeebDB.INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME  like '%username%'

If we need these functions repetitively then we can create a stored procedure and invoke the stored procedure passing keyword as a parameter:

 Create PROCEDURE [UTIL].[FIND_TABLE]
(
    @pTableName VARCHAR(max)
)
AS
BEGIN
     select * from sanjeebDB.INFORMATION_SCHEMA.TABLES 
     where TABLE_NAME like '%' + @pTableName + '%' 
     ORDER by TABLE_NAME  
END

create PROCEDURE [UTIL].[FIND_TABLE_BY_COLUMN]

(

    @pColumnName VARCHAR(max)

)

AS

BEGIN

     select * from sanjeebDB.INFORMATION_SCHEMA.COLUMNS where             COLUMN_NAME  like '%' + @pColumnName + '%'

END

To use them, run the below sql query:

exec util.FIND_TABLE 'user'

exec util.FIND_TABLE_BY_COLUMN 'username'

Good Luck!

How to give SQL User access to execute Stored procedures and read/write access to database

Assumptions: stored procedures and tables are created under schema dbo. in case different schema, change the below code accordingly. 

 --Create Login with a strong password

--Run the below code in Master Database

CREATE LOGIN [sanjeebuser] WITH PASSWORD = 'VeryStrongPa55w0rD1!'

--run the below code in your Selected Database, (optional: run in master database as well)

CREATE USER [sanjeebuser]

 FOR LOGIN [sanjeebuser]

 WITH DEFAULT_SCHEMA =  dbo

GO

--Run the below code in your selected Database. please do not run the code in Master database.

CREATE ROLE [dbstoregeprocedureaccess] AUTHORIZATION [dbo]

GRANT EXECUTE,ALTER,DELETE,UPDATE,INSERT ON SCHEMA::dbo TO dbstoregeprocedureaccess;

GRANT CREATE PROCEDURE TO dbstoregeprocedureaccess;

EXEC sp_addrolemember N'db_datareader', N'sanjeebuser'

EXEC sp_addrolemember N'db_datawriter', N'sanjeebuser'

EXEC sp_addrolemember N'dbstoregeprocedureaccess', N'sanjeebuser'

How to Enable Cors in .net standard API ( or .net core API) for PUT GET and DELETE Methods

 Error:

Access to fetch at 'https://localhost:44340/api/employee' from origin 'https://localhost:44339' has been blocked by CORS policy: Method PUT is not allowed by Access-Control-Allow-Methods in preflight response.

:44340/api/employee:1 Failed to load resource: net::ERR_FAILED

blazor.webassembly.js:1 crit: Microsoft.AspNetCore.Components.WebAssembly.Rendering.WebAssemblyRenderer[100]

      Unhandled exception rendering component: TypeError: Failed to fetch

WebAssembly.JSException: TypeError: Failed to fetch

  at System.Net.Http.WebAssemblyHttpHandler.doFetch (System.Threading.Tasks.TaskCompletionSource`1[TResult] tcs, System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2fcb610 + 0x00a30> in <filename unknown>:0 

  at System.Net.Http.WebAssemblyHttpHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f9dd40 + 0x00174> in <filename unknown>:0 

  at Microsoft.Extensions.Http.Logging.LoggingHttpMessageHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f98030 + 0x00160> in <filename unknown>:0 

  at Microsoft.Extensions.Http.Logging.LoggingScopeHttpMessageHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f71090 + 0x00182> in <filename unknown>:0 

  at System.Net.Http.HttpClient.FinishSendAsyncBuffered (System.Threading.Tasks.Task`1[TResult] sendTask, System.Net.Http.HttpRequestMessage request, System.Threading.CancellationTokenSource cts, System.Boolean disposeCts) <0x3394518 + 0x00278> in <filename unknown>:0 

  at sanjeebblazorapp.App.Services.EmployeeDataService.UpdateEmployee (sanjeebblazorapp.Shared.Employee employee) [0x00051] in C:\Users\sanje\source\repos\sanjeebko\sanjeebtutorial-blazor\sanjeebblazorapp.App\sanjeebblazorapp.App\Services\EmployeeDataService.cs:42 

  at sanjeebblazorapp.App.Pages.EmployeeEdit.HandleValidSubmit () [0x001b0] in C:\Users\sanje\source\repos\sanjeebko\sanjeebtutorial-blazor\sanjeebblazorapp.App\sanjeebblazorapp.App\Pages\EmployeeEdit.cs:81 

  at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion (System.Threading.Tasks.Task task) <0x3395450 + 0x000da> in <filename unknown>:0 

  at Microsoft.AspNetCore.Components.Forms.EditForm.HandleSubmitAsync () <0x3307b00 + 0x00218> in <filename unknown>:0 

  at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion (System.Threading.Tasks.Task task) <0x3395450 + 0x000da> in <filename unknown>:0 

  at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask (System.Threading.Tasks.Task taskToHandle) <0x2fb1590 + 0x000b6> in <filename unknown>:0 

Solution:

TO enable cors in .net core web API, cors policy need to be added in the services. Below code in blue color needs to be added in the Startup.cs file

public void ConfigureServices(IServiceCollection services)

        {

            

            services.AddDbContext<AppDbContext>(options =>

                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

   

            services.AddCors(options =>

            {

                options.AddPolicy("Open", builder => builder.AllowAnyOrigin().AllowAnyHeader().WithMethods("PUT","DELETE","GET"));

            });

            services.AddControllers(); 

        }

 public void Configure(IApplicationBuilder app, IWebHostEnvironment env)

        {

            if (env.IsDevelopment())

            {

                app.UseDeveloperExceptionPage();

            }

            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();

            app.UseCors("Open");

            app.UseEndpoints(endpoints =>

            {

                endpoints.MapControllers();

            });

        }

Create User Group in Azure using Azure CLI (powershell)

Steps1 - Login to the azure account with  right account with azure AD access. 

Step 2 - Open CloudShell by clicking the icon on the top right corner.

Step 3 - You'll need to associate CLI session with a Storage account. So follow the instruction and connect with the storage account. 

Step 4 - Select the PowerShell in the dropdown window 

You'll see something like this screenshot:

Step 5 - type the below command and press enter:

 PS /home/sanjeeb> Connect-AzureAd

 Step 6 - type the below code (change the group name as your need):

PS /home/sanjeeb>New-AzureADGroup -Description "siteadmingroup" -DisplayName "siteadmingroup" -MailEnabled $false -SecurityEnabled $true -MailNickName "siteadmingroup"

On success, you'll see the object id like below: 

ObjectId                             DisplayName             Description

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

daf23147-8123-dc23-bsb8-d2d2a5224271 siteadmingroup siteadmingroup

Generate Managed Identity Using Powershell for Azure DataFactory

Open the powershell in the azure. (Select the correct subscription and create storage location for powershell or select an existing storage account)  

Run the below query with correct value inside double quote: 

Set-AzDataFactoryV2 -ResourceGroupName "MYResourceGroup" -Name "mydatafactory" -Location "UK South"

Press enter, and you'll be prompted to confirm. Type Y and press Enter.

You should see output like below:

Confirm

A data factory with the name mydatafactory in the resource group MYResourceGroup exists.

Continuing execution may overwrite the existing one.

Are you sure you want to continue?

[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): Y

DataFactoryName   : mydatafactory

DataFactoryId     : /subscriptions/e01105ad-822a-447a-2dd0-43124d16223l/resourceGroups/MYResourceGroup/providers/Microsoft.DataFactory/factories/mydatafactory

ResourceGroupName : MYResourceGroup

Location          : uksouth

Tags              : {}

Identity          : Microsoft.Azure.Management.DataFactory.Models.FactoryIdentity

ProvisioningState : Succeeded

RepoConfiguration :

GlobalParameters  :

For detail information read: Managed identity for Data Factory - Azure Data Factory | Microsoft Docs

Done!

Create a batch file to copy/move files from network folder

Create a batch file (.bat) and paste the below code:

 @echo off

set DAYS=7

robocopy \\192.168.0.30\sanjeebapp\data\in E:\sanjeebapp\data\in\ /mov /maxage:%DAYS%

exit /b

Note: Change the source folder and destination folder as needed. /mov deletes the file from the source location . /maxage ignores the file which is older than DAYS. In this example, the files older than 7 days are not copied. 

Remove /mov from the script if you do not want to delete files in the source folder. 

 

Create Build/Release Pipeline for SSIS in Azure SQL server

Create a new pipeline in devops.

Select the source:

Select an empty template

Select Get Sources in the left panel:

Now add a new agent job to Build SSIS. Click the + icon.

Search for SSS job. (if it's not already installed, then first install it from the marketplace)

Add SSIS build and then add SSIS Deploy 

Once you add these two jobs, your screen should look like this:

Now Select Build SSIS:

in the Project Path field, select the directory where .sln file is located. 

Leave the rest of the fields as default.

Now Select Deploy SSIS job to configure deploying settings 

add source path = $(Build.ArtifactStagingDirectory) (i.e. output path value from Build SSIS job-Previous step).

select destination type = SSISDB.

Destination Server = realdatabaseserver01.database.windows.net

Destination Path = /SSISDB/masterdatahub

Authentication type = SQL Server Authentication (you can choose any other option as per your need)

Click on the Triggers tab and configure Continuous Integration. 

Now Save and Queue the Pipeline. 

Once released you will see the ssis package under masterdatahub folder

That's everything for Azure devops continuous integration/ continuous deployment process for releasing ssis project to azure sql server. Good luck!

How to modify Login in Azure SQL server

 for example, the login name is 'mysqluserlogin'

1) Enable the disabled login:

ALTER LOGIN mysqluserlogin ENABLE;

2) Change the login password:

ALTER LOGIN mysqluserlogin WITH PASSWORD = 'thisismynewpassword*&£1';

3) Change the login name:

ALTER LOGIN mysqluserlogin WITH NAME = mysqlusernewlogin;

How to create XSD schema from MSSQL Database table

 You'll need Microsoft SQL Server Management Studio to run the below query. 

Select the database where you want to run the query against. 

Open a new query window and run the below command 

    DECLARE @SanjeebXsdSchema xml

SET @SanjeebXsdSchema = (SELECT top 1 * FROM [dbo].[BOM_DW_Extract] FOR XML AUTO, ELEMENTS, XMLSCHEMA('sanjeebXsdSchema'))

SELECT @SanjeebXsdSchema

Result:

<xsd:schema xmlns:schema="sanjeebXsdSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="sanjeebXsdSchema" elementFormDefault="qualified">

  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

  <xsd:element name="dbo.BOM_DW_Extract">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="CompanyCode" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ParentPart" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentPart" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentSequence" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentQuantity" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="Reference" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="20" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="BomType" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="20" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="StartDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="EndDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ModifiedDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>

How To create SSIS user in Azure SQL server

I've assumed you have already configured domain in azure and have created a domain user.

Create Application in app registration  

First of all Create an app registration [myssissqlserver01]

Set up Active directory Admin in sql server

Now Go to the sql server (not the database) and on settings menu, click Active directory Admin

Click Set Admin and add your domain account. (in case you don't have domain configured , Configure a new domain and create a domain user first)

Connect to the database using MS Sql server management studio

Once admin is set , Open MS Sql server management studio and connect to the database.

Since we have added user to active directory admin, the user will have access to master database. So we can choose default database to connect, 

Create a user in ssis and add to a suitable role

Create a new query window selecting SSISDB database

execute the below queries. Note that the user name should be identical to the Application name created in App registration section . [ myssissqlserver01]

create user [myssissqlserver01] from external provider;

exec sp_addrolemember [db_owner],[myssissqlserver01];