SqlServer
Select
select custtable.name, custgroup.name from custtable
inner join CUSTGROUP on CUSTGROUP.CUSTGROUP = custtable.CUSTGROUP
select T1.name, T2.name from custtable T1
inner join CUSTGROUP T2 on T2.CUSTGROUP = T1.CUSTGROUP
use [Sofff-Dev]
select * from [dbo].[CUSTTABLE]
-- select * from [Sofff-Dev].[dbo].[CUSTTABLE]
Select Not Exists
SELECT ProductID, ProductName
FROM Northwind..Products p
WHERE NOT EXISTS (
SELECT 1
FROM Northwind..[Order Details] od
WHERE p.ProductId = od.ProductId)
SELECT ProductID, ProductName
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
SELECT ProductID
FROM Northwind..[Order Details])
Select with creation Table Temp
//Same result :
//1 : We create a temporation Table T2 that contains the all select * from [DEMO2].[dbo].[UNIT]
SELECT * FROM [Soflex-dev].[dbo].[UNIT] T1
INNER JOIN (SELECT * FROM [DEMO2].[dbo].[UNIT]) T2 ON T1.UNITID = T2.UNITID
//2
SELECT * FROM [Soflex-dev].[dbo].[UNIT] T1
INNER JOIN [DEMO2].[dbo].[UNIT] T2 ON T1.UNITID = T2.UNITID
Select with dateTime
select custtable.name, custgroup.name from custtable
where datetime = '2013-01-01 10:00:00';
//Format DateTime is : 'YYYY-MM-DD HH:MM:SS'
SQL Server comes with the following data types for storing a date or a date/time value in the database:
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MM:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS
TIMESTAMP - format: a unique number
The DATEADD() function adds or subtracts a specified time interval from a date.
//DATEADD(datepart,number,date)
//DATEADD : Operation on Date Ex : Format year,month,day,minutes ...
SELECT OrderId,DATEADD(day,45,OrderDate) AS OrderPayDate
FROM Orders
http://www.w3schools.com/sql/sql_dates.asp
Select group by year
SELECT YEAR(REALDATE) AS 'Year', COUNT(*) FROM PRODTABLE
GROUP BY YEAR(REALDATE)
Order BY Year
Select Into (To create a new table)
Select Into create a new Table in the dataBase.
SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
AND ListPrice < $100
Select with union
--dateformat is use to use date as format string is a special order.
set dateformat dmy
go
--UNION ALL take also the double value, UNION remove the double value = distinct
select PERIOD, MSV, MAX(total), AVG(average)
from (
select SUBSTRING(fdt, 7, 4) + '-' + SUBSTRING(fdt, 4, 2) as 'PERIOD',
fdt, msv, dy, hr, hri, hrq,SUM("Value Active+" ) as total, AVG("Value Active+") as average
from DevReadData inner join Dalia.dbo.SPLPOTENTIALCUSTDEVICE cd
on cd.DATAAREAID = 'pwr' and cd.DEVICESERIALID = DevReadData.StoreDevNO
and cd.CUSTACCOUNT = '000003' where Data_Type = 1 and cast(FDT as DATE) between '' and '31/12/2154'
group by fdt, msv, dy, hr, hri, hrq
UNION ALL
select SEA as 'PERIOD', SEA as fdt, msv, dy, hr, hri, hrq, SUM("Value Active+" ) as total,
AVG("Value Active+") as average from DevReadData inner join Dalia.dbo.SPLPOTENTIALCUSTDEVICE cd
on cd.DATAAREAID = 'pwr' and cd.DEVICESERIALID = DevReadData.StoreDevNO
and cd.CUSTACCOUNT = '000003' where Data_Type =1 and cast(FDT as DATE) between '' and '31/12/2154'
group by SEA, msv, dy, hr, hri, hrq
) tmp
group by PERIOD, MSV order by PERIOD
Insert from select
If a line was deleted from the table, we can insert in from the save database.
INSERT INTO [AX593].[dbo].[PAYMTERM]
([PAYMTERMID]
,[PAYMMETHOD]
,[NUMOFDAYS]
,[DESCRIPTION]
,[NUMOFMONTHS]
,[CASHACCOUNT]
,[PAYMSCHED]
,[CASH]
,[PAYMDAYID]
,[SHIPCARRIERANCILLARYCHARGE]
,[POSTOFFSETTINGAR]
,[SHIPCARRIERCERTIFIEDCHECK]
,[CREDITCARDPAYMENTTYPE]
,[CREDITCARDCREDITCHECK]
,[DATAAREAID]
,[RECVERSION]
,[RECID])
SELECT * FROM [AX593_save].[dbo].[PAYMTERM] T1
WHERE NOT EXISTS (SELECT * FROM [AX593].[dbo].[PAYMTERM] T2
WHERE T2.RECID = T1.RECID AND T2.DATAAREAID = T1.DATAAREAID)
Delete the double lines on a key
//Sometimes it happens that we can't sysnchronise a database because we changed the
//primary key and exists values.
delete from MCSEMVALEXTERNALREASON
where RECID in
(
select recid from MCSEMVALEXTERNALREASON
-- d contains all the double lines
inner join
(select PARTITION,DATAAREAID,SERCOMID,EXTERNALREASONCODE
from MCSEMVALEXTERNALREASON
group by PARTITION,DATAAREAID,SERCOMID,EXTERNALREASONCODE
having COUNT(*) > 1) d
on d.DATAAREAID = MCSEMVALEXTERNALREASON.DATAAREAID and d.PARTITION = MCSEMVALEXTERNALREASON.PARTITION and
d.SERCOMID = MCSEMVALEXTERNALREASON.SERCOMID and d.EXTERNALREASONCODE = MCSEMVALEXTERNALREASON.EXTERNALREASONCODE
-- for a double line take the lines > first line
where RECID >
(select MIN(recId) from MCSEMVALEXTERNALREASON M
where M.DATAAREAID = MCSEMVALEXTERNALREASON.DATAAREAID and M.PARTITION = MCSEMVALEXTERNALREASON.PARTITION and
M.SERCOMID = MCSEMVALEXTERNALREASON.SERCOMID and M.EXTERNALREASONCODE = MCSEMVALEXTERNALREASON.EXTERNALREASONCODE)
)
Store procedure
/USE [AX593_Save]
GO
/****** Object: StoredProcedure [dbo].[Test_SH] Script Date: 06/03/2013 08:08:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test_SH]
@p1 int,
@p2 int
AS
DECLARE @pGroup Varchar(50)
BEGIN
SET NOCOUNT ON;
set @pGroup = '10';
PRINT 'Printing custGroup';
PRINT @pGroup;
SELECT * from [AX593_Save].[dbo].CUSTGROUP
WHERE CUSTGROUP = @pGroup;
RETURN @@IDENTITY
END
GO
//TO EXECUTE :
USE [AX593_Save]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[Test_SH]
@p1 = 1,
@p2 = 2
SELECT 'Return Value' = @return_value
GO
Shortcut
//CMD ssms.exe
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
//can also be in Program Files (x86)
"D:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
ShortCut
Data base name
AOS database
User login
Roles
Mapping
Add user for a specific database
Shrink of the log
task>Shrink>files : And the take LOG
Roles
//to allow shrink, option must be simple.
Option
Connexion ODBC
//User DSN : Only for one user. class on client
//System DSN : All user. class on server
ODBC
ODBC
ODBC
Example code with ODBC
Static server SPLPsPiTmp FillTable(SPLPsDate _dateTimeDate)
{
SPLPsPiTmp SPLPsPiTmp;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql, criteria;
SqlStatementExecutePermission perm;
int i;
// Set the information on the ODBC.
loginProperty = new LoginProperty();
loginProperty.setDSN("axpisys");
loginProperty.setDatabase("pidata");
//loginProperty.
//Create a connection to external database.
odbcConnection = new OdbcConnection(loginProperty);
if (odbcConnection)
{
sql = "SELECT * FROM PIINSERT1ROW;";
// sql = "SELECT * FROM MYTABLE WHERE FIELD = "
// + criteria
// + " ORDER BY FIELD1, FIELD2 ASC ;";
//Put in the criteria ex criteria = "'ABC'"
//because we need to put value for string with '----'
//sql = "SELECT * FROM "+#ExternalTableName +
//" where "+#ExternalFieldName + " = '" + criteria+"'"
//+ " ORDER BY PI_TAG ASC ;";
//Assert permission for executing the sql string.
perm = new SqlStatementExecutePermission(sql);
perm.assert();
//Prepare the sql statement.
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
//Cause the sql statement to run,
//then loop through each row in the result.
while (resultSet.next())
{
i++;
SPLPsPiTmp.clear();
//It is not possible to get field 3 and then 1.
//Always get fields in numerical order, such as 1 then 2 the 3 etc.
//print resultSet.getString(1);
//print resultSet.getString(3);
info(resultSet.getString(1));
info(resultSet.getString(3));
SPLPsPiTmp.PI_TAG = resultSet.getString(1);
SPLPsPiTmp.PI_TIMESTAMP = resultSet.getDateTime(2);
SPLPsPiTmp.PI_VALUE = resultSet.getReal(3);
SPLPsPiTmp.PI_STATUS = resultSet.getString(4);
SPLPsPiTmp.insert();
if (i > 20)
{
break;
}
}
//Close the connection.
resultSet.close();
statement.close();
}
else
{
error("Failed to log on to the database through ODBC.");
}
ttsCommit;
return SPLPsPiTmp;
}
Example code call Store procedure with ODBC
//For Store procedure we write statement.executeUpdate(sql) and not statement.executeQuery(sql).
Static server SPLPsmReadDataGroupByTmp FillTableStoreProc()
{
SPLPsmReadDataGroupByTmp SPLPsmReadDataGroupByTmp;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql;
SqlStatementExecutePermission perm;
int i;
loginProperty = new LoginProperty();
loginProperty.setDSN("axPwrBill");
loginProperty.setDatabase("PwrBill");
odbcConnection = new OdbcConnection(loginProperty);
if (odbcConnection)
{
sql = strfmt('EXEC [dbo].[sp_ReadData_total2]');
perm = new SqlStatementExecutePermission(sql);
perm.assert();
statement = odbcConnection.createStatement();
statement.executeUpdate(sql);
statement.close();
}
else
{
error("@SPL4287");
}
return SPLPsmReadDataGroupByTmp;
}
Start reporting services
Rep services
To see the locks, and kill a process
process
Job monitoring
// To see the job taht are running :
Job monitoring
sp_who2 give the process sleeping
click right on SQLTEST (Server) + new query : sp_who2
Job monitoring pb pageiolatch_sh + Parser
//
Job monitoring
Parser
Report for big table
// To see the big tables
big table
Execution plan
Execution plan
purge log
Execution plan
EXEC sp_helpdb [DatabaseName]
ALTER DATABASE [Database Name] SET RECOVERY SIMPLE
go
DBCC
SHRINKFILE(Database_log) //(do property on the data base, File take file type log)
go
Alter Database [Database Name] Set Recovery Full
EXEC sp_helpdb [DatabaseName]
join bewteen 3 tables and join 1-3
//Sql 2008, in the customer SOF where are problem in query 1,2,3 with join 1-3.
Join1-3
Trace on user
//Enable trace on user
trace
RecId Table
//Enable trace on user
RecId
Insert with fix value from record
//Enable trace on user
INSERT INTO [DYNAX_PROD_201612].[dbo].[VENDTABLE]
([ACCOUNTNUM]
...
,[DATAAREAID]
,[RECVERSION]
,[RECID]
,[VENDORIGIN]
,[CONFIRMTYPEID]
... )
SELECT [ACCOUNTNUM]
,[NAME]
...
,'BAC' --value we want to change, hard codded
,[RECVERSION]
,[RECID]
,[VENDORIGIN]
,[CONFIRMTYPEID]
....
FROM [DYNAX_PROD_201612].[dbo].[VENDTABLE]
WHERE ACCOUNTNUM = '00445'
Cannot create a record in Number sequence list
Cannot create a record in Number sequence list (NumberSequenceList). … The record already exists
//Good post :
https://timgolisch.wordpress.com/2010/08/13/ax-freeze-up-cannot-create-a-record-in-number-sequence-list-numbersequencelist/
//SELECT * FROM NumberSequenceList WHERE DataAreaID = ‘[company with the locking problem]’ AND TransID > 0
//SELECT * FROM NumberSequenceTTS WHERE DataAreaID = ‘[company with the locking problem]’
SELECT * FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceList] WHERE DataAreaID = 'PRJ' AND TransID > 0
SELECT * FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceTTS]WHERE DataAreaID = 'PRJ'
- JOIN LIST - TTS
SELECT * FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceList] TList
JOIN [DYNAX_PROD_201612].[dbo].[NumberSequenceTTS] TTTS ON TTTS.TRANSID = TList.TRANSID
WHERE TList.DataAreaID = 'PRJ' AND TList.TRANSID > 0
SELECT * FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceList] TList
WHERE TList.DataAreaID = 'PRJ' AND TList.TRANSID > 0
AND NOT EXISTS (
SELECT 1
FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceTTS] TTTS WHERE TTTS.TRANSID = TList.TRANSID)
-- Execute this delete to remove the lock
DELETE FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceList]
WHERE DataAreaID = 'PRJ' AND TRANSID > 0
AND NOT EXISTS (
SELECT 1
FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceTTS] TTTS WHERE TTTS.TRANSID = [DYNAX_PROD_201612].[dbo].[NumberSequenceList].TRANSID)
--DELETE FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceList] WHERE DataAreaID = 'PRJ' AND TransID > 0
--DELETE FROM [DYNAX_PROD_201612].[dbo].[NumberSequenceTTS]WHERE DataAreaID = 'PRJ'