this will loop thru all tables that have a frag% > 40 and also puts the schema in front of the table name.

create table #frag(
tabName varchar(200)
)
insert into #frag
select S.name + ‘.’ + tbl.[name] TableName
from sys.dm_db_index_physical_stats (null, null, null, null, null )as mn
inner join sys.tables tbl on tbl.[object_id] = mn.[object_id]
inner join sys.indexes ind on ind.[object_id] = mn.[object_id]
inner join sys.schemas S on tbl.schema_id = S.schema_id
where [database_id] = db_id() and mn.avg_fragmentation_in_percent > 40
order by mn.avg_fragmentation_in_percent desc

while exists(select top 1 tabName from #frag)
begin
declare @name as varchar(200), @sql varchar(1000)
select top 1 @name = tabName from #frag
set @sql = ‘ALTER INDEX ALL ON ‘ + @name + ‘ REBUILD’
select @sql
delete from #frag where tabName = @name
exec(@sql)
end

drop table #frag

try update first and if rowcount = 0 then you need to insert.

SET NOCOUNT OFF;
set XACT_ABORT ON;
-- LEAVE NOCOUNT OFF IF SET TO ON THEN THE ROWCOUNT CHECK WILL FAIL.

 update [dbo].[mem_Favorites]
  set isActive = @isActive
  where mem_id = @mem_id and mem_favorite_id = @mem_favorite_id
  
-- if rowcount is  0 then it must be an insert

if @@ROWCOUNT = 0
 begin
  INSERT INTO [dbo].[mem_Favorites]
      ([mem_id]
      ,[mem_favorite_id]
      ,[DateAdded]
      ,[isActive])
   VALUES
      (@mem_id
      ,@mem_favorite_id
      ,GETDATE()
      ,@isActive)
 end
 

run this script to set a default schema for a sql user.  ALTER USER [UserName] WITH DEFAULT_SCHEMA=[schemaName] 

there are no settings in the connection string to do this, so set the user to login as normal and set the default schema for the user so you won't have to add the schema to the sql queries.

System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

Error msg is due to a dll that references a web service or has web service proxy class in it that has asyc references.

create proxy class from wsdl and add it to your sql server project.  Deploy the project with external access set for dll.  include XmlSerializers.dll with save access.

Using Web services and Xml Serialization in CLR Integration info on blog post here

//Create Transaction object and use test to check sql result.
//If result is bad then use rollback method
//uses System.Data.Common
//Create connection

object DbConnection myConn = myDB.CreateConnection();

Using (myConn)
{
 myConn.Open();
 DbTransaction myTran = myConn.BeginTransaction();
 cmdInsert.Connection = myConn;
 cmdInsertt.Transaction = myTran;
 cmdInsert.ExecuteNonQuery();
 mastID = Convert.ToInt32(cmdInsert.Parameters["@RecordID"].Value);

 if (mastID > 0 )
 {
  myTran.Commit();
 }
 else
 {
  myTran.Rollback();
 }
}