Category: Uncategorized

Here’s some examples for creating linked server connection to an Always On Availability Group listener

–IF USING INTEGRATED SECURITY
EXEC master.dbo.sp_addlinkedserver @server = N’MY_LINKEDSERVER’

    ,@srvproduct = N’SQL’

    ,@provider = N’SQLNCLI11′

    ,@datasrc = N’MY_LISTENER’

    ,@provstr = N’Provider=SQLNCLI11;Server=MY_LISTENER;Database=MY_DBNAME;Integrated Security=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;’

    ,@catalog = N’MY_DBNAME’

GO
EXEC master.dbo.sp_serveroption @server=N’MY_LINKEDSERVER’, @optname=N’connect timeout’, @optvalue=N’63’

GO
–IF USING SQL LOGIN SECURITY
EXEC master.dbo.sp_addlinkedserver @server = N’MY_LINKEDSERVER’

    ,@srvproduct = N’SQL’

    ,@provider = N’SQLNCLI11′

    ,@datasrc = N’MY_LISTENER’

    ,@provstr = N’Provider=SQLNCLI11;Server=MY_LISTENER;Database=MY_DBNAME;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;’

    ,@catalog = N’MY_DBNAME’

GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’MY_LINKEDSERVER’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’MY_SQLLOGIN’,@rmtpassword=’MY_SQLPASSWORD’

GO
EXEC master.dbo.sp_serveroption @server=N’MY_LINKEDSERVER’, @optname=N’connect timeout’, @optvalue=N’63’

GO

If WordPress detects that it is running under a different process than the owner of the file, it will prompt for FTP connection information. If you do not have access to chown, or perform other methods of fixing this, here are two that can be implemented if all you have is FTP and write access on your wp-config.php file.

1) Locate your WordPress root diectory and find the wp-config.php file. Edit the file and insert this somewhere in the middle as it’s own block, normally put it after the
mysql username/password block.

/*** FTP login settings ***/
define("FTP_HOST", "localhost");
define("FTP_USER", "yourftpusername");
define("FTP_PASS", "yourftppassword");

2) Another option is to define FS_METHOD in your wp-config.php file. This bypasses WordPress’s recurring prompts, and allows auto-updates of your files to happen.

Open /Wp-Config.Php Paste the following code to your wp-config.php file, preferably just below every other line of code.

define('FS_METHOD','direct');

1. Run a checkdb

DBCC CHECKDB ([db_name_here]) WITH ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS

2. Ensure there is enough free disk space for the data files and transaction log files to grow.

3. Check SQL Server and Windows event logs to see if can determine when corruption started, any errors or events that may have contributed to corruption, when last restart of instance, last online of database, I/O interruptions, power outages, etc. If “under the gun” to get db back online, this step may have to be performed post-mortem.

4. Attempt repair

EXEC sp_resetstatus 'db_name_here';
 ALTER DATABASE [db_name_here] SET EMERGENCY
 DBCC CHECKDB('db_name_here')
 ALTER DATABASE [db_name_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DBCC CHECKDB('db_name_here', REPAIR_REBUILD)
 ALTER DATABASE [db_name_here] SET MULTI_USER

5. If REPAIR_REBUILD does not fix it and data loss is a concern, then make copies of the *.ldf, *.mdf, and *.ndf files.

You can then attempt to restore the database from last good backups and transaction log backups up to the point of corruption, or attempt a REPAIR_ALLOW_DATA_LOSS

EXEC sp_resetstatus 'db_name_here';
 ALTER DATABASE [db_name_here] SET EMERGENCY
 DBCC CHECKDB('db_name_here')
 ALTER DATABASE [db_name_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 BACKUP DATABASE [db_name_here] TO DISK = N'R:\backup_path_here\db_name_here.bak'
 DBCC CHECKDB('db_name_here', REPAIR_ALLOW_DATA_LOSS)
 ALTER DATABASE [db_name_here] SET MULTI_USER

6. If possible, can try restarting instance. If not possible to restart instance due to causing other DB outages, then attempt to offline database, then set back online. Sometimes suspect mode is due to a problem during the on-lining of the db and a second attempt to online db clears up suspect issue. Of course, in extreme cases, attempting this may result in the database not being able to online at all…

7. Once you do get it back online run a checkdb and a full backup

DBCC CHECKDB ([db_name_here]) WITH ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS

 BACKUP DATABASE [db_name_here] TO DISK = N'R:\backup_path_here\db_name_here.bak'

NOTE: See the suspect database a lot on EXPRESS editions and databases with AUTO_CLOSE enabled. If this is the case then sometimes the database does not “open” properly and reports as suspect. You can clear the suspect by offline then online the database. When it is back online, recommend turning the AUTO_CLOSE off.

#******************************************************************
# POWERSCRIPT FILE NAME: getddl.ps1
# Scripts out DDL for given list of database objects
#******************************************************************
set-strictmode -version Latest;
#set-strictmode -off;

######USER PROVIDED VALUES#######
$SERVER_NAME = "MYSERVERNAME\MYINST";             #INSTANCE NAME
$DATABASE_NAME = "master";                        #DATABASE NAME
$OBJ_LIST = ('sysobjects','sp_who');              #PROVIDE LIST OF OBJECT NAMES IN ARRAY, EXAMPLE
#$OBJ_LIST = Get-Content "C:\temp\objects.txt";   #PROVIDE LIST OF OBJECT NAMES IN TEXT FILE, ONE ENTRY PER LINE
$OUT_PATH = "C:\temp";                            #OUTPUT FOLDER, MUST ALREADY EXIST


##GLOBAL VARIABLES##
$C_OK = 0;    # 0 for OK exit status
$C_NOTOK = 1; # Non-Zero for NOT-OK exit status


function main()
{
   $retstat = $C_OK;

   $tstamp = timestamp;
   write-host "$tstamp starting up";

   #connection string
   $CONN_STRING = "Server=$SERVER_NAME;Database=$DATABASE_NAME;Trusted_Connection=True";

   #initialize connection objects
   $SqlConn = New-Object System.Data.SQLClient.SQLConnection;
   $SqlConn.ConnectionString = $CONN_STRING;
   $SqlCmd = New-Object System.Data.SQLClient.SQLCommand;
   $SqlCmd.Connection = $SqlConn;

   #define command
   $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure;
   $SqlCmd.CommandText = 'sp_helptext';

   #define parameters
   $SqlCmd.Parameters.Add("@objname",[system.data.SqlDbType]::NVarChar) | out-Null;
   $SqlCmd.Parameters['@objname'].Direction = [system.data.ParameterDirection]::Input;

    
   try
   {
      $tstamp = timestamp;
      write-host "$tstamp connecting to $CONN_STRING";
      $SqlConn.Open();

      $tstamp = timestamp;
      write-host "$tstamp processing records";
      $n = 0;

      foreach ($obj in $OBJ_LIST)
      {
         $SqlCmd.Parameters['@objname'].value = clnStr $obj;
           
         #cmdToString $SqlCmd; ##output SQL command 
         $rs = $SqlCmd.ExecuteReader();

         $outfile = "$OUT_PATH\$obj.sql";

         $tstamp = timestamp;
         write-host "$tstamp Collecting $obj";

         $s = '';
         while ($rs.Read())
         {
            $s += $rs.GetValue(0);
         }
         $rs.Close();
         $rs.Dispose();

         $tstamp = timestamp;
         write-host "$tstamp Writing $outfile";
         $sw = new-object system.IO.StreamWriter($outfile);
         $sw.write($s);
         $sw.close();

         $n++;
      }#foreach
    
      $SqlCmd.Dispose();
      $SqlConn.Close();
      $SqlConn.Dispose();    

      $tstamp = timestamp;
      write-host "$tstamp processed $n objects";
   }
   catch
   {
      $line = $_.InvocationInfo.ScriptLineNumber
      $e = $_.Exception
      Write-Host -ForegroundColor Red "Exception: $e at line $line"
      #echo $_.Exception|format-list -force;  ##verbose error message
      $retstat = $C_NOTOK;
      cmdToString $SqlCmd;
   }
   
   $tstamp = timestamp;
   write-host "$tstamp shutting down";    
   exit $retstat;
}#main()


function timestamp()
{
   return($(get-date -F "yyyyMMdd_HHmmss"));
}#timestamp()



function clnStr( $val )
{
   if ( $($val) -eq $null )
   {
      $ret = [System.DBNull]::Value;
   }
   else
   {
      $ret = $val.toString().replace("'","''");
   }
   return($ret);
}#clnStr()



function cmdToString($cmd)
{
   $c = " ";
   $s = "EXEC " + $cmd.CommandText;
   foreach ($p in $cmd.Parameters)
   {
      $s += $c + $p.ParameterName;
      if ( $p.Value.Equals([DBNull]::Value) )
      {
         $s += " = NULL";
      }
      else
      {
         $s += " = '" + $p.Value + "'";
      }
      $c = ", ";
   }
   write-host $s;
}#cmdToString()


clear;
main;

Run this query on the instance hosting your SSRS databases to get list of SSRS subscriptions and their corresponding SQL Agent job names.

   SELECT b.[name] "JobName"
        , e.[name] "ReportName"
        , e.[path] "ReportPath"
        , d.[description] "SubscriptionDescription"
        , a.SubscriptionID
        , laststatus
        , eventtype
        , LastRunTime
        , date_created
        , date_modified
     FROM ReportServer.dbo.ReportSchedule a 
     JOIN msdb.dbo.sysjobs b
       ON cast(a.ScheduleID as varchar(1000)) = b.[name]
     JOIN ReportServer.dbo.ReportSchedule c
       ON a.ScheduleID = c.ScheduleID
     JOIN ReportServer.dbo.Subscriptions d
       ON c.SubscriptionID = d.SubscriptionID
     JOIN ReportServer.dbo.[Catalog] e
       ON d.report_oid = e.itemid

This can be useful if you need to explicitly execute the subscription directly from SQL or need to customize the SQL Agent job in some way such as preventing the report from being run based on conditions that cannot be defined in SSRS subscription manager.

Sharepoint has built-in statistics that are optimized for how Sharepoint works.

For SharePoint 2010 it is not recommended to Auto-Create statistics for any databases.  Auto-Update statistics can be enabled for content databases if you are not manually updating statistics and you are experiencing performance issues.

For SharePoint 2013, enabling Auto-Create Statistics and Auto-Update Statistics is NOT supported.

https://docs.microsoft.com/en-us/SharePoint/administration/best-practices-for-sql-server-in-a-sharepoint-server-farm

If you need to reset your saved your credentials to access the share folder from your PC you can delete those credentials by opening a command prompt running as administrator.

Then type the command:

rundll32.exe keymgr.dll, KRShowKeyMgr

You can then delete the stored credentials used to authenticate the share folder path on your local pc.

You can then try to access that share folder path and it should prompt you for the account user name and password again.

Problem: SSIS Support Developer could not access SSIS packages in SQL Server 2008R2 Instance.

Solution:

1. Type in “Component Services” in search bar
2. Open Component Services
3. Expand Component Services -> Computers -> My Computer -> DCOM Config
4. Scroll down to MsDtsserver100 and right-click Properties

Select the Security tab

Click edit button for Launch and Activation Permissions and add permissions, same for Access Permissions

Click OK

 

Next, Right-click My Computer>Manage>Configuration>Local Users & Groups

Look for the Distributed COM Users group and add the account.

 

 

Finally, restart the SSIS service

Download and install LogParser from here:

https://www.microsoft.com/en-us/download/details.aspx?id=24659

Then create a text file query.sql containing your query

   SELECT distinct 
          replace_str(replace_str(to_lowercase(cs-username),'0#.f|ldapmember|',''),'0#.w|','') as username
     FROM C:\inetpub\logs\LogFiles\*.log
    WHERE cs-username > ''
      AND date >= '2017-01-01'

 

Then execute the following command

C:\Program Files (x86)\Log Parser 2.2\logparser file:"query.sql" -i:IISW3C -o:CSV -recurse > c:\temp\usernames.csv

(note: path to LogParser may vary on your system, it may also be handy to add it to the %PATH%)

Learn more about LogParser here:
https://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to ‘LogonUserW’ failed with error code: ‘1385’.

1385 = Logon failure: the user has not been granted the requested logon type at this computer.

Solution:

1. Look up the Windows user that is assigned to credential [##xp_cmdshell_proxy_account##]
2. Run secpol.msc
3. Navigate to “Local Policies” -> “User Rights Assignment” -> “Log on as a batch job”
4. Add the user found from step 1 to “Log on as a batch job”
5. Test by executing sql stored procedure: “EXEC master..xp_cmdshell ‘whoami'”

 

Troubleshooting xp_cmdshell failures