sql – Selects the maximum value from one table and the corresponding value from another table

I would like a query to select the maximum price for each manufacturer and the corresponding model. The field I need to use to join the tables is the model. I use the next query:

Select a.model as modelo, max (a.price) as precio, b.maker as fabricante
from pc a, product b
Where a.model = b.model
Group of a.model, b.maker

More than one model is associated with a manufacturer. For this reason, the query is not grouped by manufacturer.

How should I change it to show only the model, the manufacturer and the maximum price?

sql server – Thrown to prevent the insertion of duplicate data

I have a table named dbo.Inventory in SQL Server 2016 as follows:

ProdCode     UPCCode     
   1          11111
   2          11112
   3          11113

When I add a new line, I have to write a trigger that looks for a duplicate in that column. If it is a duplicate, there must be an error and not change the field. If not, it must be inserted as usual. I'm really sure how I would do it, any help would be very grateful!

SQL SERVER availability groups and virtualized HA

vSphere and Hyper-v should complement AGs by adding additional HAs. However, if I lose a VM that hosts a primary synchronization replica, does not vSphere / Hyper-v simultaneously migrate the failed VM to another host if SQL Server automatically switches to the secondary synchronization replica?

Who would always win?
Can the SQL screw up?
Would not it be easier to use SQL for HA (and DR) and migrate the failed VM to a surviving host after switching SQL to a secondary replica?

All comments / experiences appreciated.

SQL Server – Search for all tables in a snapshot replication with primary keys

I was instructed to move all tables in a single snapshot replication that have transactional replication primary keys.

I've tried splitting it into two steps, finding all the tables in a snapshot replication, and then checking if those tables have a primary key.

I've tried to put together a few different code samples, but I may have to start over again, here's what I've got so far.


SELECT  DB_NAME ()                  PublisherDB
  , sp.name                     AS PublisherName
  , sa.name                     AS TableName
  , UPPER (srv.srvname)         AS SubscriberServerName
FROM    dbo.syspublications   sp
JOIN    dbo.sysarticles       sa ON sp.pubid = sa.pubid
JOIN    dbo.syssubscriptions  s ON sa.artid = s.artid
JOIN    master.dbo.sysservers srv ON s.srvid = srv.srvid;

SELECT          DB_NAME ()                    AS db
              , SCHEMA_NAME (o.schema_id)     AS (Schema)
              , so.name                       AS table_name
              , so.type
              , CASE WHEN TABLE_NAME IN (
                              SELECT    TABLE_NAME
                              FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                              WHERE     CONSTRAINT_TYPE = 'PRIMARY KEY'
                          ) THEN 1 ELSE 0 END AS HasPrimaryKey
--INTO            #t2
FROM            sys.objects o WITH (NOLOCK)
INNER JOIN      sysobjects                                      so WITH (NOLOCK)
--INNER JOIN #t1 ON t1.
WHERE           (
        (so.xtype = 'U') -- user table   xtype: docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-ver15
    OR  (so.xtype = 'V') -- view
    OR  (so.xtype = 'P') -- stored procedure
                AND so.category <> 2
                AND so.name IN (
                        SELECT  DISTINCT OBJECT_NAME (objid) FROM  dbo.sysarticles
ORDER BY        so.name
              , so.type;


DECLARE @jobName sysname;

SELECT  @jobId   = jobs.job_id
      , @jobName = jobs.name
FROM    msdb.dbo.sysjobs       jobs (NOLOCK)
JOIN    msdb.dbo.syscategories categories (NOLOCK) ON jobs.category_id = categories.category_id
WHERE   categories.name = 'REPL-Snapshot'
        AND jobs.name LIKE '%db-name%';

SELECT  @jobId
      , @jobName;

EXEC sp_start_job @job_id = @jobId;

sql server – Collect column names in Dynamic SQL statements

I'm about to update some processes created for SQL 2008R2 to 2016 that use a dynamic result set.

Assuming that there is a procedure that always has exactly one result set, but each time has a dynamic column return, is there any way to capture the column names at runtime in tsql without doing anything?
I'm not worried about data types, just column names.

Thanks a lot!


SELECT D.Part_No,D.Name,E.Customer_Part_No,A.Release_No,A.Quantity,G.Shipper_No,C.PO_No,E.Customer_Part_Description,A.Ship_Date,H.Customer_Address_Code
FROM Sales_v_Release_e A
LEFT JOIN Sales_v_PO_Line_e B ON A.PO_Line_Key = B.PO_Line_Key
LEFT JOIN Sales_v_PO C ON B.PO_Key = C.PO_Key
LEFT JOIN Part_v_Part_e D ON B.Part_Key = D.Part_Key
LEFT JOIN Part_v_Customer_Part E ON B.Part_Key = E.Part_Key
LEFT JOIN Sales_v_Shipper_Line_e F ON A.Release_Key = F.Release_Key
LEFT JOIN Sales_v_Shipper_e G ON F.Shipper_Key = G.Shipper_Key
LEFT JOIN Common_v_customer_address_e H ON G.Customer_Address_No = H.Customer_Address_No
WHERE Release_Status_Key = 75 AND Customer_Address_Code = @PLANTA 

SQL Server – sysmessages table problem

I have an annoying problem with a table called SysMessages in one of our databases.
When I execute a select statement, I do not get the contents of that table but the sys.sysmessages view in the master database.
A "use [DBNAME]" does not help, nor "select * from [DBNAME] .dbo.SysMessages".

I am in the middle of a migration to a newer SQL Server version.
In SQL Server 2008 R2, this problem did not occur with this database, but in SQL Server 2016, I can not query my own SysMessages table.

Any help would be appreciated.

Yours sincerely,

Bruno Piqueur