also, check this out:

https://dba.stackexchange.com/questions/186871/how-to-free-the-unused-space-for-a-table

another version assuming that you need diff in ms , there are two records per table (status 1 and 2)  and the amount of records is needed of the second...

questions:

1. Are there any LOB columns (text, image, varchar(max), etc) in the tables where you truncate (delete?) the data?
2. do you see and how do you check that free space...

First idea

you can create second job with no schedule (and even disabled)  which invokes your SSIS package

in the first job when there are new records just run the second job...

you can substitute the values in the report

example:   =iif(isNothing(Parameters!AddressLine1.Value)=False

, iif(Parameters!AddressLine1.Value<> Fields!address1_line1.Value

, Parameters!AddressLine1.Value

, Fields!address1_line1.Value

)

, Fields!address1_line1.Value

)

Also, parameter AddressLine1 should be Visible and Allow blank value.

• This reply was modified 5 years, 7 months ago by  Andrey. Reason: added comment

try to connect from command line using sqlcmd app

example:

-- locally/remotely

sqlcmd -S [sql\instance] -U [username] -P [password]

-- remotely

sqlcmd -Stcp:[sql\instance] -U [username] -P [password]

* [sql\instance], [username] & [password] should be replaced with...

Forgive my ignorance here. How would I do the select and union all select as a variable?  The table I am dealing with is 70,000 rows with hundreds of...

such one?

with input ([start],[end],[quant]) as 
(
select 10,50,5
)
, [data] as (
select 
[start]
,[quant]
,[end]
,[quant] [ttl]
from input
union all
select 
[start]+[quant]
,[quant]
,[end]
,[ttl]+[quant]
from [data]
where [start]+[quant]<=[end]
)
, kms as (
select 
convert(varchar(10),[start])+ '-'+ convert(varchar(10),lead([start],1,NULL) over (order by [start]))+'...

ok, glad it helped

here is the code:

with data ([Vendor Name],[Vendor No],[Invoice No],[INVOICE DATE],[Check No]) as 
(
select 'Company123', '11445','11112','20180502',123456
union all select 'Company123', '11445', '11113', '20180601',123456
union all select 'Company123',...

see attached

• This reply was modified 5 years, 7 months ago by  Andrey. Reason: attachment corrected

please, show the output of the following:

select @@version

if your users are blocked running selects against your tables,  RCSI may help you with the issue

link:

https://sqlperformance.com/2014/05/t-sql-queries/read-committed-snapshot-isolation

you haven't given a chance to help you and not provided

• sample data
• desired output

btw, nice article to read :

https://www.mssqltips.com/sqlservertip/6030/how-to-build-an-efficient-request-process-for-sql-server-dbas/

substring fuction expects a string to work with

you column BLLG_END_DT is of datetime type

to get the desired result try the following:

SELECT TOP 10 
BLLG_END_DT
--,SUBSTRING(BLLG_END_DT, 1, 10)
,format(BLLG_END_DT, 'yyyy-MM-dd')...

check the following dmv (column transaction_begin_time):

sys.dm_tran_active_transactions

in case of sp_whoisactive, there's a parameter @get_transaction_info which will answer your question

link:

http://whoisactive.com/docs/12_transaction/