Tag: Getdate()) )

How many transactions were started during the execution of the request?

Post author By Full Stack
Post date February 28, 2021
No Comments on How many transactions were started during the execution of the request?

I have a transaction. The code is set implicit_transactions on select getdate() begin transaction begin transaction select * from price p1, product p2 where p1.product_id = p2.product_id rollback delete from PRODUCT where product_id = 100871 select 100860,2,3,getdate(),null from PRODUCT commit drop table price The question is how many transactions were started during the execution of […]

Tags []]" or "[1, 2, 3, from the server's point of view, Getdate()) ), I have a transaction. The code is set implicit_transactions on select getdate() begin transaction begin transaction select * from price p, null from PRODUCT commit drop table price The question is how many transactions were started during the execution of the request? As I under, product p2 where p1.product_id = p2.product_id rollback delete from PRODUCT where product_id = 100871 select 100860, some people refer to this as a "nested transaction" although this is not official terminology. So, were there 2 transactions started

Database Development

File is getting generated into folder, But not a csv file

Post author By Jessica Alba
Post date February 23, 2021
No Comments on File is getting generated into folder, But not a csv file

For the below SP, normal text file is getting generated instead of csv file. ALTER PROCEDURE [dbo].[GenerateCDR_Test] AS BEGIN DECLARE @FileName varchar(50), @bcpCommand varchar(2000) SET @FileName = ‘C:\TransactionsData’ + (‘Tansactions_’+ (CONVERT(VARCHAR, GETDATE(), 112) + ‘‘ + CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + ‘‘ + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + ‘_’ + CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR)) + ‘.csv’); […]

Tags @bcpCommand varchar(2000) SET @FileName = 'C:\TransactionsData' + ('Tansactions_'+ (CONVERT(VARCHAR, 112) + '' + CAST(DATEPART(HOUR, For the below SP, Getdate()) ), GETDATE()) AS VARCHAR) + '_' + CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR) + '' + CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR)) + '.csv'); SET @bcpCommand = 'BCP '+'"SELECT * FROM [LinkVisaCard].[dbo].[CDRGeneration]"' +' queryout ' SET @bcpCo, normal text file is getting generated instead of csv file. ALTER PROCEDURE [dbo].[GenerateCDR_Test] AS BEGIN DECLARE @FileName varchar(50)

Database Development

Unable to resolve column level collations

Post author By Jessica Alba
Post date February 23, 2021
No Comments on Unable to resolve column level collations

Getting below error from after executing the query from the –Adding missing headers into the file—- error: SQLState = 22008, NativeError = 241 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string. SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Unable to resolve […]

Tags ''CallBackReferenceID''' Select @cmd = 'BCP '+'"Select '+ @columnHeader +' UNION ALL SELECT convert(varchar(20), ''CallBackRequestDate'', ''CallBackResponseDate'', ''ResponseDate'', 'Action', [RecordID]), $sessionId, 112) + '_' + CAST(DATEPART(HOUR, amount, CallBackReferenceID From [LinkVisaCard].[dbo]. [Transaction]"'+ ' QUERYOUT C:\TransactionsData\' +@fileName +' '+ '/c /t"|" /T'+@ServerNam, channel, CONVERT(VARCHAR(20), Getdate()) ), GETDATE()) AS VARCHAR) -- ALTER the file name SET @fileName ='Tansactions_'+ @todaysDateFormatted + '.csv'; SET @ServerName = 'TR, GETDATE()) AS VARCHAR) + '_' + CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR) + '_' + CAST(DATEPART(SECOND, Getting below error from after executing the query from the --Adding missing headers into the file---- error: SQLState = 22008, MSISDN, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Unable to resolve column level collations please do the needfull ALTER, NativeError = 241 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from chara, REFERENCEID, RequestDate, StatusID

Database Development

Order by column which casted to varchar

Post author By Jessica Alba
Post date February 23, 2021
No Comments on Order by column which casted to varchar

situation is that, i want to prepare datasets for front end chart. the raw data from table contains LAC | FLUID_MERIT|FA_BDATE |GRP |FARM_FK —————————————— 0 |234.56 |2020-01-01 |12 |10048 1 |234.56 |2009-01-01 |13 |10048 10 |234.56 |2020-01-01 |13 |10048 0 |234.56 |2020-01-01 |13 |10049 2 |234.56 |2009-01-01 |12 |10049 3 |234.56 |2009-01-01 |12 |10048 0 […]

Tags 2)) AS DataSet from HerdAnalytics_tbl where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, 2)) AS DataSet from HerdAnalytics_tbl where GRP = 12 and LAC in (select LAC from HerdAnalytics_tbl where FARM_FK = 10048 and LAC != 0) gr, 2)) AS DataSet from HerdAnalytics_tbl where GRP = 12 when i use union to merge them i could not use order by in the 4th query and also it, CAST(AVG(FLUID_MERIT) as decimal(10, FA_BDATE, Getdate()) ), GETDATE()) > 12 union select CAST(LAC as varchar), GETDATE()) > 6 and DATEDIFF(MONTH, i want to prepare datasets for front end chart. the raw data from table contains LAC | FLUID_MERIT|FA_BDATE |GRP |FARM_FK ----------------, situation is that

Database Development

Return a single value from a second table where the date:time in the table is the first date:time greater than a date:time in another table

I have two tables, the first records the datetime of a successful commit while the second inserts details of the user submitting the commit action after the commit including the datetime of the row insert. What I need is to return the SysLogUser for each Audit row where the RecordIds match and the SysLogDate is […]

Tags -11, ' + @PKSelect + ', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, '-') AS 'Our Ref', ''' + @UserName + '''' + ' from #ins i full outer join #del d' + @PKCols + ' where i.' + @fi, '') + '''<' + COLUMN_NAME + '=''+convert(varchar(100), '') AND (Type = 'D' AND SUBSTRING(OldValue, '') AS 'New Value', '') COALESCE(OldValue, '0.0') ) OR Type IN ('U', 'D') ) ORDER BY UpdateDate DESC ---------- To create my Audit table I use: IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_I, 'D') BEGIN SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS, 'dd/MM/yyyy') "Date", 'hh:mm:ss') TIME, 'I') AND SUBSTRING(NewValue, "0.00", "char", (fieldname, (SELECT TOP 1 SysLogUser FROM SystemLog WHERE SysLogDate > UpdateDate ), [type] CHAR(1), @bit - 1) SELECT @char = ((@field - 1) / 8) + 1 IF SUBSTRING(COLUMNS_UPDATED(), @field INT, @maxfield = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName WHILE @field < @maxfield, @maxfield INT, @PKCols VARCHAR(1000), @PKSelect VARCHAR(1000) --You will need to change @TableName to match the table to be audited. -- Here we made GUESTS for your exam, @sql VARCHAR(2000), @TableName) RETURN END SELECT @field = 0, @UpdateDate = CONVERT (NVARCHAR(30), @UpdateDate VARCHAR(21), @UserName VARCHAR(128), * FROM AUDIT WHERE COALESCE(NewValue, &pk, 1, 1) & @bit > 0 OR @Type IN ('I', 126) -- Action IF EXISTS (SELECT * FROM inserted) IF EXISTS (SELECT * FROM deleted) SELECT @Type, 16, 20, 22, 22) AS 'ClientID', 4, 6) '000076' ) AND (Type = 'I' AND ( NewValue NOT IN ('0', 6) '000076' OR Type IN ('U', A.* FROM Audit A JOIN SystemLog SL ON A.RecordId = SL.RecordID AND SL.SysLogDate = [First A.UpdateDate that is greater t, char *int, CHARINDEX('(', coalesce(i.' + COLUMN_NAME +', COALESCE(NewValue, convert(varchar(1000), d.' + @fieldname + ')' + ', d.' + COLUMN_NAME + '))+''>''' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, DELETE AS DECLARE @bit INT, deletes and updates: /****** Object: Trigger [dbo].[tr_client] Script Date: 03/02/2021 09:15:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_, FieldName VARCHAR(128), FORMAT (UpdateDate, FORMAT(UpdateDate, Getdate()) ), I have two tables, i.' + @fieldname + ')' + ', INFORMATION_SCHEMA.KEY_COLUMN_USAGE c WHERE pk.TABLE_NAME = @TableName AND CONSTRAINT_TYPE = 'PRIMARY KEY', insert(), N’IsUserTable’) = 1) CREATE TABLE Audit (Type CHAR(1), newValue, NewValue VARCHAR(1000), oldValue, OldValue VARCHAR(1000), PK VARCHAR(1000), SUBSTRING(pk, SUBSTRING(TableName, tablename, TableName VARCHAR(128), the first records the datetime of a successful commit while the second inserts details of the user submitting the commit action after the com, UpdateDate, UpdateDate datetime, UserName VARCHAR(128)) GO ---------- I have triggers on several tables in our DB to audit inserts, UserName) select ''' + @Type + '''

Database Development

What Query Filled My Log File

Post author By Jessica Alba
Post date January 21, 2021
No Comments on What Query Filled My Log File

My Log file seems to have blown up overnight. I think it was around 50GB and is now 200GB. I want to find out if it was a bad query that someone ran, or if this is just normal growth. There is a lot of development happening on the instance. I ran this to try […]

Tags Getdate()) ), My Log file seems to have blown up overnight. I think it was around 50GB and is now 200GB. I want to find out if it was a bad query that some, or if this is just normal growth. There is a lot of development happening on the instance. I ran this to try to identify long running queries, qp.query_plan, qs.* FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC ) AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_hand, using the 'default' keyword(s): SELECT dbo.fn_IsEdCptCode('99283') SELECT dbo.fn_IsEdCptCode('ABCD') Server: Database: ------- Data Modificat

Database Development

What Query Filled My Log File

Post author By Jessica Alba
Post date January 21, 2021
No Comments on What Query Filled My Log File

Tags Getdate()) ), My Log file seems to have blown up overnight. I think it was around 50GB and is now 200GB. I want to find out if it was a bad query that some, or if this is just normal growth. There is a lot of development happening on the instance. I ran this to try to identify long running queries, qp.query_plan, qs.* FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC ) AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_hand, using the 'default' keyword(s): SELECT dbo.fn_IsEdCptCode('99283') SELECT dbo.fn_IsEdCptCode('ABCD') Server: Database: ------- Data Modificat

Database Development

What Query Filled My Log File

Post author By Jessica Alba
Post date January 21, 2021
No Comments on What Query Filled My Log File

Tags Getdate()) ), My Log file seems to have blown up overnight. I think it was around 50GB and is now 200GB. I want to find out if it was a bad query that some, or if this is just normal growth. There is a lot of development happening on the instance. I ran this to try to identify long running queries, qp.query_plan, qs.* FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC ) AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_hand, using the 'default' keyword(s): SELECT dbo.fn_IsEdCptCode('99283') SELECT dbo.fn_IsEdCptCode('ABCD') Server: Database: ------- Data Modificat

Database Development

SQL Server estimating badly

Post author By Jessica Alba
Post date November 30, 2020
No Comments on SQL Server estimating badly

I have a heap with over a million inserts daily. This table is a "staging table" where messages are received and sent to differens queues that are processed by the application. A stored proc named dbo.leg_msgs does an UPDATE (show plan https://www.brentozar.com/pastetheplan/?id=ryw9SDGoD) on this table, using an covered index with columns starting from most selective […]

Tags [dt_hr_entrada] [datetime] NOT NULL, [dt_hr_envio] [datetime] NULL, [dt_hr_legado] [datetime] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO Stored proc: CREATE PROCEDURE [dbo].[leg_msgs] ( @pTipoMensagem, [dt_hr_lock] [datetime] NOT NULL, [dt_hr_ok_leg] [datetime] NULL, [fl_montou_protocolo] [char](1) NULL, [flag_sentido] [char](1) NULL, [flg_enviada] [bit] NOT NULL, [flg_enviar] [char](1) NOT NULL, [flg_proc_util] [char](1) NOT NULL, [flg_ret_legado] [bit] NOT NULL, [guid_lock] [uniqueidentifier] NOT NULL, [guid_protocolo] [uniqueidentifier] NOT NULL, [id_fila] [int] NULL, [id_legado] [int] NULL, [nr_ctrl_if] [char](20) NULL, [str_protocolo_enviada] [text] NULL, [str_protocolo] [text] NOT NULL, [tp_mensagem] [int] NOT NULL, @bol_delete bit -- Valores para @tempo_espera -- 0.00069445 (00:02:00) -- 0.00104167 (00:01:30) --, @cd_msg varchar(10), @ds_strong_id varchar(max), @dt_fim datetime, @dt_hr_entrada datetime, @dt_hr_entrada_str varchar(50), @dt_hr_lock datetime, @dt_ini datetime, @dt_movto varchar(10), @evento_id varchar(10), @guid_lock char(36), @guid_protocolo char(36), @id_fila_rep int, @id_status_matera varchar(5), @id_tag_numctrl varchar(20), @montou_prot char(1), @nm_fila varchar(100), @nm_proc_montagem_protocolo varchar(100), @nm_tag_sit_lanc varchar(200), @pIdFila INT = NULL ) AS BEGIN SET NOCOUNT ON DECLARE @guid_operacao char(36), @pIntegracaoViaSQL CHAR(1) = 'N', @qtd_regs int, @str_protocolo varchar(max), @strSQL nvarchar(1000), @tempo_espera numeric(20, 10, 112) SET @dt_hr_lock = getdate() SET @guid_lock = newid() SET @dt_ini = CAST(@dt_movto + ' 00:00' AS datetime) S, 121) IF @@ERROR = 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK TRANSAC, 121) OPTION (RECOMPILE) IF @@ROWCOUNT 0 BEGIN BEGIN TRANSACTION UPDATE dba.dbo.leg WITH (ROWLOCK READPA, but if the number of rows returned gets above 5000, but my current COMPATIBILITY_LEVEL = 110, but SQL Server is still missing estimates. The screenshot below is after the rebuild. CPU time = 0 ms, elapsed time = 0 ms. Table 'leg'. Scan count 1, getdate() - @tempo_espera, Getdate()) ), guid_lock = '00000000-0000-0000-0000-000000000000' WHERE tp_mensagem = @pTipoMensagem AND dt_hr_entrada BETWE, I have a heap with over a million inserts daily. This table is a "staging table" where messages are received and sent to differens queues tha, it starts being sluggish. My first approach was to rebuild table and indexes, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Any thoughts? edit: SQL Server 2017, lob read-ahead reads 0. Table 'Worktable'. Scan count 1, logical reads 16321, logical reads 32676, physical reads 0, read-ahead reads 0, using an covered index with columns starting from most selective to least selective. CREATE TABLE [dbo].[leg](CREATE TABLE [dbo].[leg](

Database Development

SQL Server estimating badly

Post author By Jessica Alba
Post date November 30, 2020
No Comments on SQL Server estimating badly

I have a heap with over a million inserts daily. This table is a "stage table" where messagens are received and sent to differents queues that are processed by the application. Also important to mention A stored proc named dbo.leg_msgs has a UPDATE (show plan https://www.brentozar.com/pastetheplan/?id=ryw9SDGoD) on this table, using an covered index with columns […]

Tags [dt_hr_entrada] [datetime] NOT NULL, [dt_hr_envio] [datetime] NULL, [dt_hr_legado] [datetime] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE PROCEDURE [dbo].[leg_msgs] ( @pTipoMensagem, [dt_hr_lock] [datetime] NOT NULL, [dt_hr_ok_leg] [datetime] NULL, [fl_montou_protocolo] [char](1) NULL, [flag_sentido] [char](1) NULL, [flg_enviada] [bit] NOT NULL, [flg_enviar] [char](1) NOT NULL, [flg_proc_util] [char](1) NOT NULL, [flg_ret_legado] [bit] NOT NULL, [guid_lock] [uniqueidentifier] NOT NULL, [guid_protocolo] [uniqueidentifier] NOT NULL, [id_fila] [int] NULL, [id_legado] [int] NULL, [nr_ctrl_if] [char](20) NULL, [str_protocolo_enviada] [text] NULL, [str_protocolo] [text] NOT NULL, [tp_mensagem] [int] NOT NULL, @bol_delete bit -- Valores para @tempo_espera -- 0.00069445 (00:02:00) -- 0.00104167 (00:01:30) -- 0.00069445 (00:, @cd_msg varchar(10), @ds_strong_id varchar(max), @dt_fim datetime, @dt_hr_entrada datetime, @dt_hr_entrada_str varchar(50), @dt_hr_lock datetime, @dt_ini datetime, @dt_movto varchar(10), @evento_id varchar(10), @guid_lock char(36), @guid_protocolo char(36), @id_fila_rep int, @id_status_matera varchar(5), @id_tag_numctrl varchar(20), @montou_prot char(1), @nm_fila varchar(100), @nm_proc_montagem_protocolo varchar(100), @nm_tag_sit_lanc varchar(200), @pIdFila INT = NULL ) AS BEGIN SET NOCOUNT ON DECLARE @guid_operacao char(36), @pIntegracaoViaSQL CHAR(1) = 'N', @qtd_regs int, @str_protocolo varchar(max), @strSQL nvarchar(1000), @tempo_espera numeric(20, 10, 112) SET @dt_hr_lock = getdate() SET @guid_lock = newid() SET @dt_ini = CAST(@dt_movto + ' 00:00' AS datetime) SET @dt_fim, 121) IF @@ERROR = 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK TRANSACTION RETURN, 121) OPTION (RECOMPILE) IF @@ROWCOUNT 0 BEGIN BEGIN TRANSACTION UPDATE dba.dbo.leg WITH (ROWLOCK READPAST), but if the number os rows return get greater than 5000, but SQL Server is still missing estimates. CPU time = 0 ms, elapsed time = 0 ms. Table 'leg'. Scan count 1, getdate() - @tempo_espera, Getdate()) ), guid_lock = '00000000-0000-0000-0000-000000000000' WHERE tp_mensagem = @pTipoMensagem AND dt_hr_entrada BETWEEN @dt_ini A, I have a heap with over a million inserts daily. This table is a "stage table" where messagens are received and sent to differents queues tha, it starts being sluggish. My first approach was rebuild table and indexes, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Any thoughts?, lob read-ahead reads 0. Table 'Worktable'. Scan count 1, logical reads 16321, logical reads 32676, physical reads 0, read-ahead reads 0, using an covered index with columns starting from most selective to less selective. CREATE TABLE [dbo].[leg]( [guid_operacao] [u