Editing Vendor, SKU and Prices using Microsoft SQL Management Studio

Editing records directly in the Management studio is dangerous. There are no safeguards in place as provided by Cabinetvision.

DO NOT PROCEED without creating a backup of your data

Use at your own risk.

Contents of this Article

  • Open Management Studio (as Administrator) / Check Edit all rows
  • Create View
  • Edit View to sort by Vendor then Material Name
  • Edit Data
  • Verify changes in Material Manager
  • Filtering options

Open Management Console

  1. Open Management Studio (as Administrator)
  2. Open Tools/Options/SQLServer Object Explorer
  3. Set Value for top Edit <n> Rows command to 0
  4. Click ok

Create View

  1. Open Databases/CxMaterials_??
  2. Right-click Views > New View
  3. Add any table (We will be using an SQL statement Add Tables/Joins in the next step)
  4. Highlight current SQL statement and replace with the statement below

SELECT dbo.Vendor.Name AS VendorName, dbo.Material.Name AS MaterialName, dbo.MaterialVendor.SKU, dbo.MaterialVendor.Cost

FROM   dbo.MaterialVendor INNER JOIN

            dbo.Vendor ON dbo.MaterialVendor.VendorID = dbo.Vendor.ID INNER JOIN

            dbo.Material ON dbo.MaterialVendor.MaterialID = dbo.Material.ID

WHERE (dbo.MaterialVendor.VendorID > 0)

  1. Right-Click in SQL Pane/Verify SQL Syntax (or Verify SQL Syntax Button- Yellow SQL), (reapply SQL Staement if failed)
  2. Right-Click in SQL Pane/Execute SQL (or Execute SQL Button -Red !)
  3. Close view, Save as EditVendorSkuPrice
  4. Open Views, if EditVendorSkuPrice does not show Right-Click Views/Refresh or close and reopen Microsoft SQL Server Management Studio (As Administrator).

 

Edit View to sort by Vendor then Material Name

  1. Open Databases/CxMaterials/Views
  2. Right-Click EditVendorSkuPrice/Edit all Rows
  3. Click Show SQL Pane
  4. Edit SQL to Read

SELECT VendorName, MaterialName, SKU, Cost

FROM   EditVendorSkuPrice

ORDER BY VendorName,MaterialName

  1. Right-Click in SQL Pane/Verify SQL Syntax (or Verify SQL Syntax Button- Yellow SQL), (reapply SQL Staement if failed)
  2. Right-Click in SQL Pane/Execute SQL (or Execute SQL Button -Red !)

Edit data

  1. Select cell to edit
  2. Edit data

Verify changes in Material Manager

  1. Open Cabinet Vision
  2. Check data has been edited

Filtering options

You can filter the output by modifying the SQL statement

SELECT VendorName, MaterialName, SKU, Cost

FROM   EditVendorNew

WHERE (VendorName LIKE '%Blum%')

ORDER BY VendorName, MaterialName

  1. Test syntax using the Verify SQL Syntax button (Yellow SQL)
  2. Test output using the Execute SQL Button (Red !)