https://sqlzoo.net/wiki/SELECT_basics
Columns tables and types
select schema_name(o.schema_id) as SchemaName, object_name(o.object_id) as TableName, c.name as ColumnName, t.name as ColumnType
from sys.columns c
join sys.types t on c.system_type_id = t.system_type_id
join sys.objects o on c.object_id = o.object_id
where c.name = 'SUG-MUTZAR' or c.name = 'STATUS'
order by c.name
Cannot perform alter on a function because it is an incompatible object type
If you have a Multi-Statement Table-Valued Function (MSTVF):
CREATE FUNCTION [DataAccess].[ufn_
( ... )
RETURNS @ret TABLE (ID INT)
AS
BEGIN
...
RETURN
END
If you will try to alter it to – with changing it to an Inline Table-Valued Function:
ALTER FUNCTION [DataAccess].[ufn_
(
@ClientPolTable [DataAccess].[TT_
@SugMimshak INT,
@FromNechonutDate DATETIME = NULL, -- In case no date is selected
@ToNechonutDate DATETIME = NULL
)
RETURNS TABLE AS
RETURN
(
SELECT .....
)
You will be got this error:
Cannot perform alter on 'DataAccess.ufn_
Solution:
You can't alter a function from MSTVF to Inline. So, drop and create again the function.
CREATE FUNCTION [DataAccess].[ufn_
SSIS issue: Decimal fields are imported from csv files without the decimal part
Problem:
Decimal fields are imported from csv files to the database tables without the decimal part.
(12.547 --> 12.000).
The data imported via SSIS.
This is the definition ion the Flat File Connection Manager:
Solution:
Declare the fields in the Flat File Connection Manager as Numeric, with the correct precision and scale:
Decimal fields are imported from csv files to the database tables without the decimal part.
(12.547 --> 12.000).
The data imported via SSIS.
This is the definition ion the Flat File Connection Manager:
Solution:
Declare the fields in the Flat File Connection Manager as Numeric, with the correct precision and scale:
Why decimal declaration is not fit to decimal fields? Ask Microsoft :)
NULL in JOIN
Problem: Join 2 tables, but there are some fit cases with NULL value – and they are not return.
Fast fix: ISNULL in the JOIN.
But, let's take a look to pay attention on this.
create table #aaa (id int, thedate datetime)
create table #bbb (id int, thedate datetime)
insert into #aaa (id, thedate) values (1, '2010-01-01'), (2, NULL), (3, getdate())
insert into #bbb (id, thedate) values (1, '2005-07-01'), (2, NULL), (3, getdate())
-- "equel" nulls will not be return
select *
from #aaa a
join #bbb b on a.id = b.id
and b.thedate = a.thedate
-- only "equel" nulls will be return
select *
from #aaa a
join #bbb b on a.id = b.id
where b.thedate is null
and a.thedate is null
-- all joins will be return
select *
from #aaa a
join #bbb b on a.id = b.id
and isnull(b.thedate, '1900-01-01') = isnull(a.thedate, '1900-01-01')
-- PAY ATTENTION: if the "isnull data" is a possible value in your tables - it can be a problem:
insert into #aaa (id, thedate) values (4, '1900-01-01')
insert into #bbb (id, thedate) values (4, NULL)
select *
from #aaa a
join #bbb b on a.id = b.id
and isnull(b.thedate, '1900-01-01') = isnull(a.thedate, '1900-01-01')
drop table #aaa
drop table #bbb
So:
ISNULL(AAA, GETDATE) can't be a 100% solution to join on datetime,
ISNULL on numeric columns MUST take into consideration which value will be in the ISNULL (common mistake is to put zero – it's a valid numeric value!!!),
ISNULL(AAA, N'') can't be a 100% solution to join on strings (empty string it's a valid numeric value!!!),
And so on...
Remove leading zero's
declare @aaa nvarchar(50) = N'00000100200'
SELECT SUBSTRING(@aaa , PATINDEX('%[^0]%', @aaa), LEN(@aaa ))
Get job step(s) history
select j.name as JobName, s.step_id as Step, s.step_name as StepName,
msdb.dbo.agent_datetime(run_
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) as RunDurationMinutes
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
WHERE j.[enabled] = 1 -- Only Enabled Jobs
AND j.name = 'YourJobName' -- specific job
AND h.run_status = 1 -- 0=Failed, 1=Succeeded, 2=Retry, 3=Cancelled, 4=In Progress
AND s.step_id = 3 -- specific step
ORDER BY j.name, msdb.dbo.agent_datetime(run_
[SSISDB].[catalog].[set_object_parameter_value] Cannot find the parameter because it does not exist
What I tried to do:
Sets the value of a parameter in the Integration Services catalog.
EXEC [SSISDB].[catalog].[set_
@object_type=20,
@parameter_name=N'
@object_name=N'MyProcess',
@folder_name= N'MyProcess',
@project_name= N'MyProcess',
@value_type=R,
@parameter_value= N'MyServerName';
GO
Error message:
[SSISDB].[catalog].[set_
Cause and Fix:
Lowercase / Uppercase of the names – environment VS SSIS parameters.
When I set the letters to be exactly the same – it worked.
EXEC [SSISDB].[catalog].[set_
@object_type=20,
@parameter_name= N'MYSERVERNAME',
@object_name=N'MyProcess',
@folder_name= N'MyProcess',
@project_name= N'MyProcess',
@value_type=R,
@parameter_value= N'MYSERVERNAME';
GO