Adding bolt type and size via SQL

[j.hamelin]

Hi,

I tried to add some bolt types and sizes using SQL SMS, and I expected to see them in the Management Tools, but nothing appeared.

I'm not sure if I did something wrong. Let me show you exactly what I did:

But in MT :

No bolts were present, only the ones in metric sizes that I had tried to convert to SAE sizes, but they still appear as "M12" instead of the new names I used.
In short, none of the changes I made through SQL seem to appear in the Management Tool, as if I were working on a clone database.

That’s what I initially thought, so I double-checked but SQL is definitely connected to the main (and only) database I use in Advance Steel.

Did I forget something?

After that, I tried adding them directly in the Management Tools, but nothing seemed to be saved after closing and reopening it.
Could it be that my database is suddenly locked?

Nothing gets saved when I work directly in the Management Tools, but the data remains if I do it through SQL...
I'm a bit lost here 😢

Advance Steel 2025

SQL SMS v18.12.1

SQL Server Management Studio 15.0.18424.0
SQL Server Management Objects (SMO) 16.100.47021.0+7eef34a564af48c5b0cf0d617a65fd77f06c3eb1
Microsoft Analysis Services Client Tools 15.0.19750.0
Microsoft Data Access Components (MDAC) 10.0.26100.3624
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.26100

[Sebastian_Eiche]

Hi,
implenting bolts by using the sql management studio is really not easy, I prefer to use the management tools.
But therefore you need to know: 
to add a bolt, all informations are needed in combination. this means a bolt is defined by the type+grade+diameter+set+the rules for the length (clamping length).

all these parameters are needed to implement the bolt, if one parameter is missing, it will not work:
https://help.autodesk.com/view/ADSTPR/2021/ITA/?guid=GUID-094BC075-AA0B-401F-A411-664C6612D186

[j.hamelin]

Thank you, Sebastian. I’ve used this method many times before, but I was expecting it to be easier to add multiple bolts via SQL instead of creating them one by one in the Management Tool.

I'm now looking for a step-by-step procedure to properly edit all the necessary database tables to add bolts correctly.

Faster doesn’t always mean easier.

I was hoping that using SQL would simplify the process, but in the end, creating bolts manually through the Management Tool, although slower, might be more straightforward and reliable 😞

[Julio_Soto · Accepted solution]

I’ve personally added an entire library of custom bolts and do not use any of the default Advance Steel ones. Sebastian is right — adding them via SQL is not easy. But if you’re adding a large library of bolts, it’s really the only feasible way. Adding bolts through the Management Tools is fine if you're just adding a couple of bolts to an existing bolt definition. But doing this for an entire library would require thousands of manual entries. It’s just not a realistic option.

What I developed is a somewhat elaborate system of Excel files that link to each other, helping auto-populate tens of thousands of lines of data, which I then manually paste into the AstorBase.mdf file. The last time I did this was a few years ago, so here’s what I remember:

You have to make entries in the following tables of the AstorBase.mdf database:

• SetBolts – You need to define a bolt for every length you want to use.

• SetOfBolts – Here you define which bolt sets are available for every bolt, diameter, and grade.

• ScrewNew – If you want auto-calculated lengths (which I did).

• SetNutBolts – Define every nut and washer for each grade.

• StrengthClass – For any custom bolt materials or grades.

• Standard – For custom bolt names.

• Sets – For custom bolt sets.

• Source – Honestly, I don’t know why this is needed, but I know it’s referenced in some of the tables above. I typically just enter the ISO or ASTM standard here.

To give you an idea of the scale: for one custom bolt type, for one specific grade, I had to generate about 500 lines of data for the SetBolts table. Then, for my custom bolt assemblies, I needed 1,200 lines, and for the AutoLength table, another 1,200 lines — totaling almost 3000 lines of data for one bolt type and grade.

I auto-populated all of this using Excel formulas and links between files. Unless someone shows me a better way, this is the only practical method I’ve found to build an entire hardware library.

I haven’t even started on anchors yet — and that’s a whole different beast, lol.

Personally, I think the out-of-the-box bolt libraries in Advance Steel are not very good — and often not accurate — which is why I built my own. But after doing it myself, I have to say: the amount of redundant information that has to be entered into AstorBase.mdf just feels criminal.

Professionally, I’m not a programmer, so I try not to criticize developers — I know I couldn’t do their job. But this whole setup really has me scratching my head. How is this the best possible method? And I guarantee it won’t be any better in Revit.

[markhubrich]

I found a safer way to examine the database 😄 

Python script of course lol. I think a bolt & anchor configurator is about to be born! I think I'll use QT for GUI because it's beautiful lol.

[markhubrich · Accepted solution]

# sql_dump.py

import os
import json
import pyodbc
from config import DB_CONFIG

OUTPUT_DIR = "sql_dump"

def connect_sql_server():
    conn_str = (
        f"DRIVER={{{DB_CONFIG['driver']}}};"
        f"SERVER={DB_CONFIG['server']};"
        f"Trusted_Connection={DB_CONFIG['trusted_connection']};"
    )
    print(f"🔌 Connecting to {DB_CONFIG['server']}...")
    conn = pyodbc.connect(conn_str, autocommit=True)
    print("✅ Connected successfully.")
    return conn

def get_databases(cursor):
    cursor.execute("SELECT name, database_id FROM sys.databases ORDER BY name")
    all_dbs = [row.name for row in cursor.fetchall()]

    # Filter for ASTOR .mdf files under STEEL\DATA
    filtered = []
    for db in all_dbs:
        path = db.lower()
        if (
            "steel\\data" in path or "steel/data" in path
        ) and "astor" in path and db.lower().endswith(".mdf"):
            filtered.append(db)
    return filtered

def sanitize_name(mdf_path):
    return os.path.splitext(os.path.basename(mdf_path))[0].upper()

def get_tables(cursor):
    cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'")
    return [row[0] for row in cursor.fetchall()]

def dump_table(cursor, db_name, table_name):
    try:
        cursor.execute(f"SELECT * FROM [{table_name}]")
        columns = [column[0] for column in cursor.description]
        rows = [dict(zip(columns, row)) for row in cursor.fetchall()]

        os.makedirs(OUTPUT_DIR, exist_ok=True)
        file_name = f"{db_name}__{table_name}.json"
        file_path = os.path.join(OUTPUT_DIR, file_name)

        with open(file_path, "w", encoding="utf-8") as f:
            json.dump({
                "_source_database": db_name,
                "_table_name": table_name,
                "data": rows
            }, f, indent=2, ensure_ascii=False)

        print(f"✅ Dumped {file_name} ({len(rows)} rows)")
    except Exception as e:
        print(f"❌ Failed to dump {db_name}.{table_name}: {e}")

def main():
    conn = connect_sql_server()
    cursor = conn.cursor()

    databases = get_databases(cursor)
    print(f"\n📦 Found {len(databases)} core Advance Steel databases")

    for db in databases:
        db_label = sanitize_name(db)
        print(f"\n🔍 Processing: {db_label}")
        try:
            cursor.execute(f"USE [{db}]")
            tables = get_tables(cursor)
            print(f" - Found {len(tables)} tables")
            for table in tables:
                dump_table(cursor, db_label, table)
        except Exception as e:
            print(f"❌ Error in {db_label}: {e}")

    conn.close()
    print("\n🎉 Dump complete.")

if __name__ == "__main__":
    main()

# config.py
DB_CONFIG = {
    'server': r'(LocalDB)\ADVANCESTEEL2025',
    'trusted_connection': 'yes',
    'driver': 'ODBC Driver 17 for SQL Server'
}

in cmd prompt, hopefully you have pip installed, 

pip install pyobdc

[Julio_Soto]

did you just get into Python Scripting, because I don't think I've seen you post stuff like this before. I saw you post some Ptython in another post too.

And what is that script doing?

[markhubrich]

yeah i just never used it with advance steel. That script queries all the data and dumps out a very large amount of .json files...  4008 files to be exact lol

[markhubrich]

Yep I'm doing this lol...  https://github.com/meistro57/AdvSteel-FastenSuite

[Julio_Soto]

If you need input let me know. I spent a lot of time doing this and got very familiar with the tables.

[markhubrich]

Great thanks! I will be picking your brain 😄 

[Julio_Soto]

I'm watching your git

[j.hamelin]

I really don’t want to cut you off... I keep learning from every comment you guys make!
Please, keep them coming. 😅

@Julio_SotoThank you so much for the list of tables I need to populate.
I feel a bit more confident now about where I should start.

[bigcarl5000kg]

@markhubrich 

amazing job, thank you

[markhubrich]

@bigcarl5000kg Don't thank me yet lol. I gotta make something work first. I didn't even think this whole thing through yet, although I'm confident I can make it work. 

[bigcarl5000kg]

Just the thought is amazing 😁🤟

[markhubrich]

@Julio_Soto  I will have a nice big red HILTI button in there to repair all the Hilti fasteners!! Bye Bye HY-150 lmao. That button can simply turn the HY-150 to HY-200 lol. Oooh I could put a utility in there that would scrape Hilti.com for anchors based on filter criteria. This has been in the back of my mind forever and seems like a good challenge for me right now. 

[Julio_Soto]

I didn't want to wipe the OOTB fasteners, so all I did was remove all the OOTB entries in the Standard table so only my botls and anchors showed up. The standard table can be repopulated fairly easy.

[Julio_Soto]

Is there a place on git where I can send message to you? I"m newish to git. I have some thoughts on where you may want to start. 

[markhubrich]

Probably lol. I'm still pretty new with the git on this side of things. I'm usually cloning repos and messing around that way. I never read the GitHub instructions neither lol. Might be time to double back and do that too. I'm all ears.