General SQL - Revision history

Crmtogetherkb at 11:35, 30 September 2022

2022-09-30T11:35:54Z

← Older revision		Revision as of 12:35, 30 September 2022
Line 619:		Line 619:

	**note that if there is no parent company there will be no update so you may need to change the query or provide a separate query to update any orphan records		**note that if there is no parent company there will be no update so you may need to change the query or provide a separate query to update any orphan records

			-----
			get the TCPIP port

			USE master
			GO
			xp_readerrorlog 0, 1, N'Server is listening on', N'any', NULL, NULL, N'asc'
			GO

Crmtogetherkb at 22:47, 22 February 2020

2020-02-22T22:47:34Z

← Older revision		Revision as of 23:47, 22 February 2020
Line 190:		Line 190:
	)		)

			Example for the bcp above

			BULK INSERT saminc.dbo.ARCSM
			FROM 'C:\temp\importfiles\ARCSM.dat'
			WITH (
			ROWTERMINATOR = '\n'
			);

	----		----
Line 368:		Line 375:
	EG. random^&		EG. random^&

			See Bulk import for details on importing this data

	----		----

Crmtogetherkb at 14:36, 23 June 2017

2017-06-23T14:36:12Z

← Older revision		Revision as of 15:36, 23 June 2017
Line 594:		Line 594:
	comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and		comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
	cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId		cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId
	and comm.Comm_SecTerr<>comp.Comp_SecTerr		and comm.Comm_SecTerr<>comp.Comp_SecTerr
	and Comm_TargetListId is null; --Comm_TargetListId-no updates on emarketing items		and Comm_TargetListId is null; --Comm_TargetListId-no updates on emarketing items
	OPEN @commCursor;		OPEN @commCursor;
	FETCH NEXT FROM @commCursor INTO @Comm_SecTerr,@Comm_CommunicationID;		FETCH NEXT FROM @commCursor INTO @Comm_SecTerr,@Comm_CommunicationID;

Crmtogetherkb at 14:35, 23 June 2017

2017-06-23T14:35:47Z

← Older revision		Revision as of 15:35, 23 June 2017
Line 573:		Line 573:
	The select statement below should return all communication records that don't match the parent company		The select statement below should return all communication records that don't match the parent company

	select comp.Comp_SecTerr,comm.Comm_CommunicationID		select comp.Comp_SecTerr,comm.Comm_CommunicationID
	FROM		FROM
	communication comm, company comp, Comm_Link cmli		communication comm, company comp, Comm_Link cmli
	where		where
	comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and		comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
	cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId		cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId
	and comm.Comm_SecTerr<>comp.Comp_SecTerr		and comm.Comm_SecTerr<>comp.Comp_SecTerr
	and Comm_TargetListId is null		and Comm_TargetListId is null

	To get these communications to match run the following SQL		To get these communications to match run the following SQL

	DECLARE @Comm_SecTerr as INT;		DECLARE @Comm_SecTerr as INT;
	DECLARE @Comm_CommunicationID as INT;		DECLARE @Comm_CommunicationID as INT;
			DECLARE @commCursor as CURSOR;
	DECLARE @commCursor as CURSOR;		SET @commCursor = CURSOR FOR
			select comp.Comp_SecTerr,comm.Comm_CommunicationID
	SET @commCursor = CURSOR FOR		FROM
	select comp.Comp_SecTerr,comm.Comm_CommunicationID		communication comm, company comp, Comm_Link cmli
	FROM
	communication comm, company comp, Comm_Link cmli
	where		where
	comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and		comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
Line 598:		Line 596:
	and comm.Comm_SecTerr<>comp.Comp_SecTerr		and comm.Comm_SecTerr<>comp.Comp_SecTerr
	and Comm_TargetListId is null; --Comm_TargetListId-no updates on emarketing items		and Comm_TargetListId is null; --Comm_TargetListId-no updates on emarketing items
			OPEN @commCursor;
	OPEN @commCursor;		FETCH NEXT FROM @commCursor INTO @Comm_SecTerr,@Comm_CommunicationID;
	FETCH NEXT FROM @commCursor INTO @Comm_SecTerr,@Comm_CommunicationID;		WHILE @@FETCH_STATUS = 0
			BEGIN
	WHILE @@FETCH_STATUS = 0		update Communication
	BEGIN		set Comm_SecTerr=@Comm_SecTerr
	update Communication		where
	set Comm_SecTerr=@Comm_SecTerr		Comm_CommunicationId=@Comm_CommunicationID
	where		--print 'update Communication set Comm_SecTerr='+cast(@Comm_SecTerr as VARCHAR (50)) +' where Comm_CommunicationId='+cast(@Comm_CommunicationID as VARCHAR (50))
	Comm_CommunicationId=@Comm_CommunicationID		FETCH NEXT FROM @commCursor INTO @Comm_SecTerr, @Comm_CommunicationID;
	--print 'update Communication set Comm_SecTerr='+cast(@Comm_SecTerr as VARCHAR (50)) +' where Comm_CommunicationId='+cast(@Comm_CommunicationID as VARCHAR (50))		END
	FETCH NEXT FROM @commCursor INTO @Comm_SecTerr, @Comm_CommunicationID;		CLOSE @commCursor;
	END		DEALLOCATE @commCursor;

	CLOSE @commCursor;
	DEALLOCATE @commCursor;

	**note that if there is no parent company there will be no update so you may need to change the query or provide a separate query to update any orphan records		**note that if there is no parent company there will be no update so you may need to change the query or provide a separate query to update any orphan records

Crmtogetherkb at 14:34, 23 June 2017

2017-06-23T14:34:33Z

← Older revision		Revision as of 15:34, 23 June 2017
Line 573:		Line 573:
	The select statement below should return all communication records that don't match the parent company		The select statement below should return all communication records that don't match the parent company

	select ~~Comm_SecTerr,~~ Comp_SecTerr~~,comp_name~~,comm.*		select comp.Comp_SecTerr,comm.Comm_CommunicationID
	FROM		FROM
	communication comm, company comp, Comm_Link cmli		communication comm, company comp, Comm_Link cmli
	where		where
	comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and		comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
	cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId		cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId
	and comm.Comm_SecTerr<>comp.Comp_SecTerr		and comm.Comm_SecTerr<>comp.Comp_SecTerr
			and Comm_TargetListId is null

	To get these communications to match run the following SQL		To get these communications to match run the following SQL

	~~UPDATE~~		DECLARE @Comm_SecTerr as INT;
	~~communication~~		DECLARE @Comm_CommunicationID as INT;
	SET
	~~communication~~.~~Comm_SecTerr=comp~~.~~comp_secterr~~		DECLARE @commCursor as CURSOR;
	FROM
	communication comm, company comp, Comm_Link cmli		SET @commCursor = CURSOR FOR
	where		select comp.Comp_SecTerr,comm.Comm_CommunicationID
	comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and		FROM
	cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId		communication comm, company comp, Comm_Link cmli
	and comm.Comm_SecTerr<>comp.Comp_SecTerr		where
			comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
			cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId
			and comm.Comm_SecTerr<>comp.Comp_SecTerr
			and Comm_TargetListId is null; --Comm_TargetListId-no updates on emarketing items

			OPEN @commCursor;
			FETCH NEXT FROM @commCursor INTO @Comm_SecTerr,@Comm_CommunicationID;

			WHILE @@FETCH_STATUS = 0
			BEGIN
			update Communication
			set Comm_SecTerr=@Comm_SecTerr
			where
			Comm_CommunicationId=@Comm_CommunicationID
			--print 'update Communication set Comm_SecTerr='+cast(@Comm_SecTerr as VARCHAR (50)) +' where Comm_CommunicationId='+cast(@Comm_CommunicationID as VARCHAR (50))
			FETCH NEXT FROM @commCursor INTO @Comm_SecTerr, @Comm_CommunicationID;
			END

			CLOSE @commCursor;
			DEALLOCATE @commCursor;

			**note that if there is no parent company there will be no update so you may need to change the query or provide a separate query to update any orphan records

Crmtogetherkb at 10:19, 22 June 2017

2017-06-22T10:19:07Z

← Older revision		Revision as of 11:19, 22 June 2017
Line 565:		Line 565:

	ref: http://www.sqlservercentral.com/scripts/Views/147316/		ref: http://www.sqlservercentral.com/scripts/Views/147316/


			----

			Updating (fixing) Territories

			The select statement below should return all communication records that don't match the parent company

			select Comm_SecTerr, Comp_SecTerr,comp_name,comm.*
			FROM
			communication comm, company comp, Comm_Link cmli
			where
			comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
			cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId
			and comm.Comm_SecTerr<>comp.Comp_SecTerr

			To get these communications to match run the following SQL

			UPDATE
			communication
			SET
			communication.Comm_SecTerr=comp.comp_secterr
			FROM
			communication comm, company comp, Comm_Link cmli
			where
			comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
			cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId
			and comm.Comm_SecTerr<>comp.Comp_SecTerr

Crmtogetherkb at 10:11, 8 June 2017

2017-06-08T10:11:41Z

← Older revision		Revision as of 11:11, 8 June 2017
Line 552:		Line 552:
	user_accountlockedout=null		user_accountlockedout=null
	where User_Logon='admin'		where User_Logon='admin'


			----
			Refresh views in a database. Useful for random SQL errors that have nothing in the log for them.

			DECLARE @sqlcmd NVARCHAR(MAX) = ''
			SELECT @sqlcmd = @sqlcmd + 'EXEC sp_refreshview ''' + name + ''';'
			FROM sys.objects AS so
			WHERE so.type = 'V'
			SELECT @sqlcmd
			--EXEC(@sqlcmd)

			ref: http://www.sqlservercentral.com/scripts/Views/147316/

Crmtogetherkb at 10:20, 22 February 2017

2017-02-22T10:20:57Z

← Older revision		Revision as of 11:20, 22 February 2017
Line 542:		Line 542:

	* C:\DB Backups\ is a folder on the server		* C:\DB Backups\ is a folder on the server


			----

			SQL to reset admin password and remove lockout

			update Users
			set User_Password='',
			user_accountlockedout=null
			where User_Logon='admin'

Crmtogetherkb at 09:15, 29 August 2016

2016-08-29T09:15:27Z

← Older revision		Revision as of 10:15, 29 August 2016
Line 529:		Line 529:

	Ref: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes		Ref: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes


			----

			SQL to backup your CRM and CRMSelfService DB

			BACKUP DATABASE [CRM] TO DISK = N'C:\DB Backups\CRM.bak' WITH FORMAT, INIT, NAME = N'CRM-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
			GO

			BACKUP DATABASE [CRMSelfService] TO DISK = N'C:\DB Backups\CRMSelfService.bak' WITH FORMAT, INIT, NAME = N'CRMSelfService-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
			GO

			* C:\DB Backups\ is a folder on the server

Crmtogetherkb at 09:02, 29 August 2016

2016-08-29T09:02:17Z

← Older revision		Revision as of 10:02, 29 August 2016
Line 499:		Line 499:

	SELECT @@VERSION		SELECT @@VERSION


			----

			Some queries to estimate the size of a database

			SELECT
			database_name = DB_NAME(database_id)
			, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
			, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
			, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
			FROM sys.master_files WITH(NOWAIT)
			WHERE database_id = DB_ID() -- for current db
			GROUP BY database_id

			Ref: http://stackoverflow.com/questions/18014392/select-sql-server-database-size

			with fs
			as
			(
			select database_id, type, size * 8.0 / 1024 size
			from sys.master_files
			)
			select
			name,
			(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
			(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
			from sys.databases db

			Ref: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes