Update Statistics Manually. Re-indexing manually

DECLARE @tablename varchar(80),@shemaname varchar(80)
DECLARE @SQL AS NVARCHAR(200)
DECLARE TblName_cursor CURSOR FOR
SELECT t.name,s.name FROM sys.tables t join sys.schemas s
on s.schema_id=t.schema_id


OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
INTO @tablename,@shemaname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'
PRINT @SQL

EXEC sp_executesql @statement = @SQL

 FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

MS SQL add Update Statistics job (Schedule) to get better performance ( Reindexing )

/****** Object: Job [Update CRM Statistics] Script Date: 05/01/2017 09:43:42 ******/
 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/01/2017 09:43:43 ******/
 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
 EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Update CRM Statistics',
 @enabled=1,
 @notify_level_eventlog=3,
 @notify_level_email=0,
 @notify_level_netsend=0,
 @notify_level_page=0,
 @delete_level=0,
 @description=N'No description available.',
 @category_name=N'[Uncategorized (Local)]',
 @owner_login_name=N'xxxx', @job_id = @jobId OUTPUT

/*xxxx = login username for the database which has dbaadmin permission*/
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Update Statistics] Script Date: 05/01/2017 09:43:43 ******/
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update Statistics',
 @step_id=1,
 @cmdexec_success_code=0,
 @on_success_action=1,
 @on_success_step_id=0,
 @on_fail_action=2,
 @on_fail_step_id=0,
 @retry_attempts=0,
 @retry_interval=0,
 @os_run_priority=0, @subsystem=N'TSQL',
 @command=N'DECLARE @tablename varchar(80),@shemaname varchar(80)
 DECLARE @SQL AS NVARCHAR(200)
 DECLARE TblName_cursor CURSOR FOR
 SELECT t.name,s.name FROM sys.tables t join sys.schemas s
 on s.schema_id=t.schema_id
 OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname

WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @SQL = ''UPDATE STATISTICS ''+@shemaname+''.['' + @TableName + ''] WITH FULLSCAN '' ---+ CONVERT(varchar(3), @sample) + '' PERCENT''
 PRINT @SQL

EXEC sp_executesql @statement = @SQL

FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname
 END

CLOSE TblName_cursor
 DEALLOCATE TblName_cursor
 ',
 @database_name=N'CRM',

/*CRM is the database NAME*/
 @flags=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Reindexing',
 @enabled=1,
 @freq_type=4,
 @freq_interval=1,
 @freq_subday_type=1,
 @freq_subday_interval=0,
 @freq_relative_interval=0,
 @freq_recurrence_factor=0,
 @active_start_date=20141124,
 @active_end_date=99991231,
 @active_start_time=193000,
 @active_end_time=235959,
 @schedule_uid=N'b5ec9450-ee63-43bc-926f-ab5e36d5dc9e'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 COMMIT TRANSACTION
 GOTO EndSave
 QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
 EndSave:

GO

VS2013 An exception occurred while initializing the database

I’m doing a code first Entity database

{“An exception occurred while initializing the database. See the InnerException for details.”}

It was working fine, but I decided to delete the physical database to see it recreate it, but it just fails every time.

I get the error

Cannot attach the file ‘C:\ASP\OdeToFood\OdeToFood\App_Data\OdeToFoodDb.mdf’ as database ‘OdeToFoodDb’.”}

Its code first so I don’t understand why its not recreating the database.

 

 

So deleting the file from Visual Studio was a dumb idea.

SQL still has it registered.

Fire up MS Sql Management Tool, connect to the server in my case

(localdb)\v11.0

You will see the database, still exists, delete it, you will get an error. The file doesn’t exist. Refresh and its gone from SQL. Now run your code again and all is good.

So delete the DB from the management tool not visual studio

SQL Server replication requires the actual server name to make a connection to the server

Symptom:

Error Msg: “SQL Server replication requires the actual server name to make a connection to the server”

 

When attempting to create a new replication publication the following error is produced:

New Publication Wizard

SQL Server is unable to connect to server ‘SSSS’.

Additional information:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternative name are not supported. Specify the actual server name, ‘OOOO’. (Replication.Utilities)

Where “SSSS” is the name of the current server, and (in my case) “OOOO” was the previous name of the same server.

Steps to reproduce this problem:

Microsoft SQL Server Management Studio
Expand the server
Expand Replication
Right click “Local Publications” and select “New Publication …”
Cause:

This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function ‘@@SERVERNAME’ still returned the original name of the server. This can be confirmed by:

select @@SERVERNAME
go

This should return the name of the server. If it does not then follow the procedure below to correct it.

Remedy:

To resolve the problem the server name needs to be updated. Use the following:

sp_addserver ‘real-server-name’, LOCAL

if this gives an error complaining that the name already exists then use the following sequence:

sp_dropserver ‘real-server-name’
go
sp_addserver ‘real-server-name’, LOCAL
go

If instead the error reported is ‘There is already a local server.’ then use the following sequence:

sp_dropserver old-server-name
go
sp_addserver real-server-name, LOCAL
go

Where the “old-server-name” is the name contained in the body of the original error.

Stop and restart SQL Server.

 

 

Reff: http://www.cryer.co.uk/brian/sqlserver/replication_requires_actual_server_name.htm 

sql drop stored procedure if exists

Drop and re-create procedure if it exists in T-SQL not working

 

IF EXISTS(SELECT 1

          FROM   INFORMATION_SCHEMA.ROUTINES

          WHERE  ROUTINE_NAME = 'YOUR_STORED_PROCEDURE_NAME'

                 AND SPECIFIC_SCHEMA = 'dbo')

  BEGIN

      DROP PROCEDURE YOUR_STORED_PROCEDURE_NAME

  END

GO




CREATE PROCEDURE dbo.YOUR_STORED_PROCEDURE_NAME

AS-- Logic Comes Here

GO

Open Cart Add bulk menu to bulk product

SELECT * FROM ocw8_product_to_category WHERE product_id IN

(SELECT product_id FROM `ocw8_product` WHERE model = ‘Sloppy Plate’)

INSERT INTO ocw8_product_to_category (product_id,category_id)
VALUES (75,68)

INSERT INTO ocw8_product_to_category (product_id,category_id)
VALUES (76,68)
GO
INSERT INTO ocw8_product_to_category (product_id,category_id)
VALUES (78,68)
Go
INSERT INTO ocw8_product_to_category (product_id,category_id)
VALUES (79,68)
GO
INSERT INTO ocw8_product_to_category (product_id,category_id)
VALUES (80,68)
GO
INSERT INTO ocw8_product_to_category (product_id,category_id)
VALUES (81,68)
Go