Categories
Mastering Development

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 […]

Categories
Database Development

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’); […]

Categories
Database Development

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 […]

Categories
Database Development

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 […]

Categories
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 […]

Categories
Database Development

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 […]

Categories
Database Development

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 […]

Categories
Database Development

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 […]

Categories
Database Development

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 […]

Categories
Database Development

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 […]