When I go to SQL Server Management Studio -> Management -> Maintenance Plans -> Right click on the maintenance plan that I want to execute but the option is greyed out.

• I have checked and SQL Server Integration Services is running.
• I can execute other maintenance plans.
• I have even tried modifying this plan without an issue but Execute stays greyed out.
• There is no other instance of the plan running at the moment.
• I have also disconnected from the SQL Server and connected back but that didn't help either.
• SQL Agent is running.

Solution:

Turns out it was an execution window that was buried. It doesn't show as an "open window" when you look at the taskbar at the SSMS icon, so it's deceptive. ALT-TAB your way through the windows and you should find it.

The open windows was:

From here: sql server - Maintenance Plan Execute option is greyed out - Stack Overflow

If you have a very large file that was created by using Generate Script (Data Only) via SQL Server Management Studio (SSMS) you will probably not be able to load it in SSMS to run it. The solution is to use the Command Prompt as follows.

sqlcmd -S <Server>\<Instance> -i <Full Path of .sql File> -I

The -I at the end is used when there is Quoted Identifiers, which is just about all the time!

Other Command Line Parameters that might be of use.

Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout] 
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit] 
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit] 
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary] 

I received the following error when I was attempting to compare columns in a SQL query between two tables located in two separate databases. In this case, the collation settings between the two databases were configured differently. Rather make any changes to the databases, I was able to apply a simple fix to my SQL query: In fact even when I changed the Collation in the Database it still did not Join!

ORIGINAL QUERY –

SELECT        TOP (100) PERCENT dbo.MyLoans.LoanNumber AS [Loan Number], LoansDW.dbo.TAP.BorrFirstName AS [PB First], LoansDW.dbo.TAP.BorrLastName AS [PB Last]

FROM            EncompassDW.dbo.TAP RIGHT OUTER JOIN

                         dbo.FiveStarLoans ON LoansDW.dbo.TAP.LoanNumber = dbo.FiveStarLoans.LoanNumber 

ORDER BY [Loan Number]

FIX –

Simply apply the default collation to the fields you are comparing.

SELECT        TOP (100) PERCENT dbo.MyLoans.LoanNumber AS [Loan Number], LoansDW.dbo.TAP.BorrFirstName AS [PB First], LoansDW.dbo.TAP.BorrLastName AS [PB Last]

FROM            EncompassDW.dbo.TAP RIGHT OUTER JOIN

                         dbo.FiveStarLoans ON LoansDW.dbo.TAP.LoanNumber = dbo.FiveStarLoans.LoanNumber COLLATE DATABASE_DEFAULT

ORDER BY [Loan Number]

I found myself with an issue with SQL Data. I could not trim the data with the trailing white space. Only this worked!

  UPDATE tblPipeClasses
  SET PipeClass =  Replace(PipeClass, Char(160),'')

I also wrote this function in VBA

Public Function RemoveExtendedAsciiCharacters(strString As String) As String

    Dim intCount As Integer    

    For intCount = 128 To 255

        If InStr(1, strString, Chr(intCount)) > 0 Then

            strString = Replace(strString, Chr(intCount), "")

        End If

    Next intCount    

    RemoveExtendedAsciiCharacters = strString

End Function

In SQL Server 2012R2 I found that my Log files were always getting so big that they would fill the L drive. So I wrote a script that will Truncate all the Log files. You can also do this on each individual database by right clicking, select Shrink, then Files. Click OK. See below for more details.

Also Check this URL: What is the command to truncate a SQL Server log file? - Stack Overflow

In management studio:

• Don't do this on a live environment, but to ensure you shrink your dev db as much as you can:
  • Right-click the database, choose Properties then Options
  • Make sure "Recovery model" is set to "Simple" not "Full"
  • Click OK
• Right-click the database again, choose Tasks -> Shrink -> Files
• Change file type to "Log"
• Click OK.

Alternatively, the SQL to do it:

USE DatabaseName
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
DBCC SHRINKFILE (DatabaseName_Log, 1)
ALTER DATABASE DatabaseName SET RECOVERY FULL 

From here: https://mstechtalk.com/fix-recovery-pending-state-sql-server/

Solution 1 worked like a chalm!

States of SQL Server Databases

If a single or multiple core files cannot be accessed in SQL server, it means that the SQL server database is corrupted. As per the measure of damage in the database, there are different states of SQL database. Some of them are explained below:

Online: If a single file is damaged and cannot be accessed, the database will remain available and online.

Suspect: In case, the transaction log file is damaged and it is creating obstructions on recovery or preventing transaction rollback from completion, it will result in failure of SQL database.

Recovery Pending: This state usually occurs when the SQL server knows that recovery of the database is to be done, but something is creating hindrance before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, but it has not started yet.

The state of a database can be known after executing the following query statement:

SELECT name, state_desc from sys.databases
GO

Reasons Behind Recovery Pending State in SQL Server

Once a reason is known, finding a solution becomes a lot easier. When SQL database requires recovery and it cannot be started, it is known as pending state. This situation arises when:

• The database is not cleanly shut down. In other words, one or more unfulfilled transaction is active at that time and the transaction log file has been deleted.
• Users attempted to transfer the transaction log files to a new drive to solve performance issues. But, this may leads to corruption of log files.
• Due to inadequate memory space or disk storage, database recovery cannot be started.

Workaround to Fix Recovery Pending State in SQL Server

There are 2 different manual methods to initiate SQL database recovery, which is marked in the recovery pending state. Both the solutions are described below.

Solution 1: Mark SQL Database in Emergency Mode and Start Forceful Repair

Follow these steps to resolve SQL server database issue:

1. Execute these queries to fix SQL server database in recovery pending state:

ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [DBName] set multi_user
GO

2. EMERGENCY mode marks the SQL database as READ_ONLY, deactivates logging, and gives the permission to system admin only
3. This method is capable of resolving any technical issue and bringing the database back to the accessible state. The database will automatically come out of the EMERGENCY mode.

Solution 2: Mark SQL Database in Emergency Mode, Disconnect the Main Database and Attach it Again

Execute these commands to fix recovery pending state in SQL server:

ALTER DATABASE [DBName] SET EMERGENCY;
ALTER DATABASE [DBName] set multi_user
EXEC sp_detach_db ‘[DBName]’
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’

These query statements will help the server to get corruption-free log and create a new one automatically.

Warning: Before performing any manual solution on the SQL server database, make sure that the proper backups of the data are created. In case, any error occurs, the data should still be available. Also, the manual approach is to be performed only when a user has a deep technical knowledge on the topic. If a user is unsure and the steps are not clear, another approach is explained in the next section.

Alternative Solution to Fix Recovery Pending State in SQL Server Database

No doubt, the manual solution to repair SQL server database is an effective technique, but it has to be performed with utmost care. Additionally, there are other disadvantages of the method too. So, it is recommended to use an automated tool like SQL Database Repair Software. According to the technical experts, it is the professional method to repair recovery pending state in SQL server 2012.

Final Words

A SQL server database is a lot more complex and difficult to handle. There are many glitches which occur in SQL database, and they have to be resolved as soon as possible. One such problem is a recovery pending state in SQL server. The multiple manual solutions have been described in the post to fix recovery pending state in SQL server database. Moreover, a third-party tool is also introduced to resolve the problem in a hassle-free manner.

From here: https://microsoft.github.io/AzureTipsAndTricks/blog/tip140.html

Note by cbattlegear One important caveat to this process (as shown below). If any writes are happening on the database while you do the export the import will be broken. Best practice is to run CREATE DATABASE AS COPY to create a copy of the database and create an export of the copy.

Right-click on the Database -> click Tasks > Export data-tier application

Now ensure you are connected to your local target SQL server instance (or SQL Azure instance) and right-click on the Database -> click Tasks > Import data-tier application and select the .backpac file that you created earlier. 

From here: https://stackoverflow.com/questions/19077356/how-to-determine-what-fields-were-update-in-an-update-trigger

create trigger utr_Table1_update on Table1
after update, insert, delete
as
begin
    with cte_inserted as (
        select id, (select t.* for xml raw('row'), type) as data
        from inserted as t
    ), cte_deleted as (
        select id, (select t.* for xml raw('row'), type) as data
        from deleted as t
    ), cte_i as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_inserted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    ), cte_d as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_deleted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    )
    insert into Table1_History (ID, Name, OldValue, NewValue)
    select
        isnull(i.ID, d.ID) as ID,
        isnull(i.Name, d.Name) as Name,
        d.Value,
        i.Value
    from cte_i as i
        full outer join cte_d as d on d.ID = i.ID and d.Name = i.Name
    where
        not exists (select i.value intersect select d.value)

These are som items that need to be considered/done when upgrading from MS Access backend to SQL Server backend.

1. Replace # with single quote (') for Dates in SQL.
2. Add Timestamp to all SQL tables.
3. Add dbSeeChanges to all DAO CurrentDB.Execute SQL statements. dbFailOnError should become dbSeeChanges + dbFailOnError
4. Convert all MulitValue fields to flat fields. This usually like multi-select combo baoxes that have to be converted to Listboxes.
5. Convert all Attachment Fields to VarBinary subTables!

QUESTION:

We are using the Bamboo Data-View Web Part in SharePoint 2013 to allow users to filter and export results from a single Library and I have just discovered that the Filter is hidden in a column when you have a Multiple Lines of Text in the grid. This makes the tool pretty useless now so I was hoping that someone has come across a better Web Part?

ANSWER:

So the answer is that Multi-line Text fields in SharePoint are NOT searchable or Filterable, end of story. I have found a solution that works. This solution is to keep a SQL Server table update to date with the library data, less the documents of course, and use a nvarchar(4000) column for the multi-line text fields. We will never exceed that number of characters so this works great plus it's much faster than rendering a SharePoint Library!