The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Tuesday, September 28, 2021 Login
Public

SQL Server Walk the Dog8/18/2021 6:07:33 AM

Recently I was given several text files with 500k to 1 million update statements in each that needed to be run.

Try loading and running this in SQL Server Management studio and you'll find that if it does load and run, sometimes it fails part through with memory error or other issues (all related to the client, not the engine).

It would have been better for the teams to work together and write this as a table join update as opposed to generating so many individual statements, but that ship had sailed and now these needed to be run.

I ended up "Walking the Dog" or RBAR (Row by agonizing Row).

Load the text files to a table, use a cursor to read each row and dynamically execute it.  I threw in a global counter i could query to figure out where it was at in the process.  In total it took about 2-3 hours to run.

Here is how to walk the dog.

SET NOCOUNT ON; 

  

DECLARE @sqlstmt nvarchar(4000)

 

create table ##Global_Count (current_rownum int)

insert into ##Global_Count values (0)

 

 

PRINT '-------- starting --------'; 

  

DECLARE cur_statements CURSOR FOR  

SELECT sqlstmt FROM DocumentId_3

  

OPEN cur_statements 

  

FETCH NEXT FROM cur_statements INTO @sqlstmt

 

WHILE @@FETCH_STATUS = 0 

BEGIN 

       Exec sp_Executesql @sqlstmt

       update ##Global_Count set current_rownum = current_rownum + 1

FETCH NEXT FROM cur_statements INTO @sqlstmt

END

 

Close cur_statements

Deallocate cur_statements

 

select * from ##Global_Count

drop table ##Global_Count

8/18/2021 6:07:33 AM | Web_Blog
SQL Server Drop Constraints unknown name8/12/2021 6:01:02 AM
SQL Server will create constraints with a default name, that is not consistent across implementations if you do not specify a name.

Best practice would be to always specify a name.

But if you inherit a database across 1000's of locations and have to write update scripts, you may come across the need to drop a constraint for a column when you don't know the constraint name.

This script will find the constraint name based off the column and dynamically generate a drop statement for it.

SET @SelectStatement =  (SELECT 'ALTER TABLE [Machines] DROP CONSTRAINT ' + name

                                   FROM sysobjects               

                    WHERE parent_obj = object_id('TableName') AND xtype = 'D' AND name LIKE '%ColumnName%')

                           EXECUTE sp_executesql @SelectStatement


8/12/2021 6:01:02 AM | Web_Blog
Loblolly Pine Trees7/13/2021 5:06:26 AM
Trying to grow some Loblolly Pine Trees in Missouri.
Seedling sourced from my Dad in South Carolina, and transplanted to larger containers, once they are big enough I'm going to plan them in a line and make a boundary at the back of my property.



7/13/2021 5:06:26 AM | Web_Blog
Sidewalk replacement7/10/2021 8:44:09 AM
The sidewalk below my front step had collapsed down about 3 inches.  This leads to water running under the steps and that's bad as it puts water close to the foundation of the house, and even worse, snakes had decided to live under there (look in the pics and you'll see the snake eggs).

7/10/2021 8:44:09 AM | Web_Blog
Plenty of Beer 6/12/2021 8:33:46 AM
Found this letter I wrote to my Grandfather from Okinawa in 1991 / 92.

There was water rationing at the time, but the "money" quote.

"But that's OK, there's always Beer and Plenty of it.".

6/12/2021 8:33:46 AM | Web_Blog
EJ Horkay Class of 20215/22/2021 5:27:24 AM
Graduation May 13, 2021




5/22/2021 5:27:24 AM | Web_Blog
Unable to drop Statistics3/26/2021 10:00:29 AM
Recently ran into an issue where we were unable to drop a column statistic.

We were trying to modify a column and received the following error.

ALTER TABLE [schema].[table] ALTER COLUMN [column1] VARCHAR(MAX)
go

Msg 5074, Level 16, State 1, Line 1
The statistics 'Stat_52ead462d2bc49009bded731bf1417a8' is dependent on column 'column1'.
ALTER TABLE ALTER COLUMN column1 failed because one or more objects access this column.
Operation cancelled by user.

Notice the odd name of stat_{guid}, this is usually when sql server is auto generating a system name.

Searched for that statistic name and could not find it.

select *

from sys.stats so

where so.name = 'Stat_6c8147e7477849589b585591f65b5843'




I ended up guessing that i needed to drop the statistic on the column, so ran this query to find the statistic on that column and then manually dropped the statistic.

select s.name as statistics_name

,c.name as column_name

, sc.stats_column_id

from sys.stats as s

inner join sys.stats_columns as sc

       on s.object_id = sc.object_id and s.stats_id = sc.stats_id

inner join sys.columns as c

       on sc.object_id = c.object_id and c.column_id = sc.column_id

where s.object_id = object_id('stg.td_click')

From Here i dropped that statistic and then was able to proceed, notice it is not named like the error, but it worked and allowed me to then alter the column.

drop statistics stg.td_click._WA_Sys_00000010_29D8AC17

Found one similar issue.

3/26/2021 10:00:29 AM | Web_Blog