411 REFERENCE · Wikibooks
Granite WMS/SQL
Reference material by Wikibooks contributors. This retains its source identity and is not 411 news reporting.
SQL tips and tricks
Database
Granite
Accpac
Insert ID
SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF
View sql result statistics
SET STATISTICS IO ON
SET STATISTICS TIME ON
Find triggers in database
select * from sys.triggers
select [definition] from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id
where obj.type ='TR'
Disable all triggers in database
EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"
Activity monitor - Release locks
Right click on the SQL instance
Open Activity monitor
Click processes
Helpful query
Example to iterate / loop over records
declare @id bigint --id to loop
select @id = min(id) from OptionalFields where AppliesTo = 'MASTERITEM' --example get all optional fields for masteritems
while @id is not null
begin
--do your thing insert update
insert into OptionalFieldValues_MasterItem(Belongsto_id, OptionalField_id)
select ID, @id from MasterItem;
--get next id
select @id = min(id) from OptionalFields where ID > @id
end