Posted on

How to ALTER all tables columns in SQL Database

declare @cols table (i int identity, tablename varchar(100), colname varchar(100))

insert into @cols
select TABLE_NAME, COLUMN_NAME
from information_schema.COLUMNS
where DATA_TYPE='nvarchar'

select * from @cols

declare @i int, @maxi int
select @i = 1, @maxi = MAX(i) from @cols

declare @sql nvarchar(max)

while(@i <= @maxi)
begin
    select @sql = 'alter table sub_history.'+tablename+' alter column ' + colname + ' varchar(255) NULL'  from @cols where i = @i
    exec sp_executesql @sql
    select @i = @i + 1
end
Posted on

Exception- The definition for user-defined data type ‘MyTypeList’ has changed.

The definition for user-defined data type ‘MyTypeList’ has changed. Exception when we rename or changed any User defined TYPE and it used in stored procedures.
Stored Procedures which is using this type variable will not run and throwing the exception. If we have large numbers of SP then it will to time consuming or irritating to re-compile all SP one by one.
Instead of using this manual process this script will help to fix all references.

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.MyTypeList', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
	PRINT @Name
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

Posted on

Installing TORTOISE-SVN. MSI error 2503 2502 errors

Install Tortoise-svn.msi, or MSI and other programs in the process reported 2503, 2502 error, mainly the user’s permissions

Open CMD as “Run As administrator”
Then enter msiexec/package “c:\temp\TortoiseSVN-1.14.1.29085-x64-svn-1.14.1.msi” in the inside;
Press Enter, and the installation box will pop up, then click Next to proceed.

Posted on

ASP.NET MVC Redirect to Action from a Class

var context = new System.Web.HttpContextWrapper(System.Web.HttpContext.Current);
                var rc = new System.Web.Routing.RequestContext(context, new System.Web.Routing.RouteData());
                var urlHelper = new UrlHelper(rc);
                context.Response.Redirect(urlHelper.Action("login", "account"), false);
                return;
Posted on

How to check SQL Job is in running state?

Method 1:
System Stored procedure: sp_help_job, returns information on the job, its steps, and more.

EXEC msdb.dbo.sp_help_job @Job_name = ‘Your Job Name’

Method 2:

IF EXISTS(SELECT 1
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobactivity A
ON A.job_id=J.job_id
WHERE J.name=N’Your Job Name’
AND A.run_requested_date IS NOT NULL
AND A.stop_execution_date IS NULL
)
PRINT ‘The job is running!’
ELSE
PRINT ‘The job is not running.’

Posted on

Cannot load Counter Name data because an invalid index ” was read from the registry

Cannot load Counter Name data because an invalid index ” was read from the registry

When we are trying to read system performance counters, some time we will get this type of error on machines.

“Cannot load Counter Name data because an invalid index ” was read from the registry”

To fix the issue:

Click the START button
Type CMD
Right click CMD PROMPT and select “Run As Administrator”
Type LODCTR /r
Wait a few seconds, you will get “Error: Unable to rebuild performance counter setting from system backup store, error code is 2”

Now again Type LODCTR /r

This time it will run successfully with the message “Info: Successfully rebuilt performance counter setting from system backup store”

Close he CMD.
















Posted on

RESTSHARP: GET / POST/ DOWNLOAD and UPLOAD with and without Authentication

//GET:
var client = new RestClient("http://www.example.com/");
client.Authenticator = new HttpBasicAuthenticator("username", "password");
var request = new RestRequest(URI, Method.GET);
request.AddQueryParameter("id", customid);
var queryResult = client.Execute(request).Content;

//POST:
var client = new RestClient("http://www.example.com/");
client.Authenticator = new HttpBasicAuthenticator("username", "password");
var request = new RestRequest(URI, Method.POST);
request.RequestFormat = DataFormat.Json;
request.AddQueryParameter("id", customid);
request.AddBody(Parameters);
IRestResponse response = client.Execute(request);

//DOWNLOAD FILE:
var client = new RestClient("http://www.example.com/");
client.Authenticator = new HttpBasicAuthenticator("username", "password");
var request = new RestRequest(URI, Method.GET);
request.AddQueryParameter("id", customid);
return client.DownloadData(request);

//UPLOAD FILE:
var client = new RestClient("http://www.example.com/");
client.Authenticator = new HttpBasicAuthenticator("username", "password");
var request = new RestRequest(URI, Method.POST);
request.RequestFormat = DataFormat.Json;
request.AddQueryParameter("id", customid);
request.AddFile("file", file);
IRestResponse response = client.Execute(request);

Posted on

Search in MSSQL tables

Sometime we need to reverse engineering in the database tables to find a specific values, we can find the column name easily but in large database, to search a specific key value is too hard. From below query we can easily search value in all tables, it will list column with table in the result.

DECLARE @SearchStr nvarchar(100) = 'test'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal', 'numeric')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT distinct ColumnName, ColumnValue FROM @Results order by ColumnValue

Posted on

Cannot load MVC4 Web project in VS 2017 or VS 2019

I have a web project, it could load fine in VS15, but in VS2019 it’s failing, saying the project is incompatible. The migration report contains the following message: ========== .csproj: The application which this project type is based on was not found. Please try this link for further information: http://go.microsoft.com/fwlink/?LinkID=299083&projecttype=E3E379DF-F4C6-4180-9B81-6769533ABE47

To resolve it:

1. Open a .csproj file using a text editor such as Visual Studio Code or notepad

2. Find the code <ProjectTypeGuids> {E3E379DF-F4C6-4180-9B81-6769533ABE47}; {349c5851-65df-11da-9384-00065b846f21}; {fae04ec0-301f-11d3-bf4b-00c04f79efbc} ProjectTypeGuids>

3. Delete the code section {E3E379DF-F4C6-4180-9B81-6769533ABE47}  and save the file.

4. Re-open the solution.