sql server 2008 r2 – SQL clustered index

I am using SQL Server 2008 R2. I have a table with columns Date, Time, ID(PK) , col1 , col2 , col3.
Some of my queries uses date in where condition,while other uses date and time. The id column is rarely used in the query.

To improve the query performance on which column i need to create the clusterdindex?

Date column only ? Or date and time column ? Or date,time and id column ?

SQL Server: Differential Maintenance Plan “fails” . No other message

I have created Backup Maintenance Plans for full, differential, and transaction logs.

I notice that the rest run without a hitch. The differential file is created, but get the following error:

Executed as user: MyDomaindba_Acct. Started:  10:51:08 PM  Finished: 10:51:13 PM  Elapsed:  4.625 seconds.  The package execution failed.  The step failed.

The event log says the same.

I have an SQL server running on 11.0.5058.0

sql server – Troubleshooting an automatic failover in 2 node multi subnet AG

I am trying to troubleshoot an automatic fail over that happened on our below infra- both of below running on HYPERV VMs

SQL1 – 8 cores 64 GB RAM – No load running (Primary) running on
SQL1SQLins

SQL2- 8 cores 64 GB RAM – No load running (secondary) running on
SQL2SQLins

Both are running SQL2017 (RTM-CU16) enterprise

While browsing in sql error log the very first error message it starts

A connection timeout has occurred on a previously established
connection to availability replica”SQL2iSQLins with
id{3w17383381-e4y2e483y-u92u329}Either a networking or firewall issue
exists, or the endpoint address provided for the replica.

Looking at MS documentation -> here seems above has been resolved and should not be applicable for SQL2017. Is it true or anyone still has faced this issue?

After 2 minutes in error log i see messages saying ” Always on Availability group connection with secondary database established for database bjsas on replica sql2sqlins”

But soon again in a minute i see
“The local replica of AG-VBDR’ is preparing to transition to resolving state and all failover messages

What happened if MS saying issue is fixed or am i looking in wrong direction?

parsing – How do I parse complex queries to SQL? Advanced search engine

I want to make an advanced search form, with search by syntax. I’ve already found a github repository with an implementation very similar to what I want, but I can’t understand the implementation so I can’t adapt it to my needs. The search I’m trying to accomplish is the one that scryfall uses to search mtg cards. Here is a list of tests in python for a better idea of what I mean:

'c:bm' => '((id IN (SELECT card_id FROM card_color WHERE color_id = 3)) AND (id IN (SELECT card_id FROM card_color GROUP BY card_id HAVING COUNT(card_id) > 1)))'
'c:u OR (c:g AND NOT tou>3)' => "(id IN (SELECT card_id FROM card_color WHERE color_id = 2)) OR ((id IN (SELECT card_id FROM card_color WHERE color_id = 5)) AND NOT (toughness IS NOT NULL AND toughness <> '' AND CAST(toughness AS REAL) > 3))"

I can’t find any resources explaining how this is done but I don’t even know what to search for. I guess it would be syntax search parsing in python (if I wanted to make this in python) but I can’t find anything. That’s why I’m asking here.

¿por que se reinicia SQL Server Management Studio al ejecutar star job at step?

buen dia tengo un jobs instalado que se encarga de generar respaldos automaticamente en sql server 2012 al momento de quererlo iniciar paso a paso de manera manual el SQL Server Management Studio se reinicia por consecuencia el jobs no inicia reviso el servicio del agente y este esta iniciado de igual forma lo reinicio y me sigue presentando los mismos problemas revisando la lista de errores me aparecen estos introducir la descripción de la imagen aquí

agradeceria de su apoyo brindado

sql server – What is the difference between SET NOEXEC ON and SET PARSEONLY ON?

Currently, SQL server supports the following statements: SET NOEXEC and SET PARSEONLY.

From the documentation:

SET NOEXEC ON:

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them.

SET PARSEONLY ON:

When SET PARSEONLY is ON, SQL Server only parses the statement.

I guess it’s “compiles” vs. “parses”. “Compiles” should include “parses”.

If I want to verify the correctness of SQL Scripts which one should I use? I am inclined to answer: SET NOEXEC ON.

But what else does compile do? Does “compile” mean the execution plans are created/modified? Is the existence of objects checked?

What happens if you have:

SET NOEXEC ON
SET PARSEONLY OFF

I find having both options available confusing. But maybe the details would make it clear.

Thanks

python – How to update a column in sql based on another table’s last row, column value

I’m currently on the process of making a CRUD Database program. I’m having some trouble with the database. I would like to link 2 columns with each other. It’s quite different from usual though, it’s a little more complicated.

So, I have a table named items_list which have a column called ‘Item Name’ where each item has its own ‘Stock Count'(another column).

Each of these items then have their own table (item_name) which have columns of Date, Description, and Stock Count. I would like to know how I could connect these tables together so that when I add the latest stock count (with the latest date) into the (item_name) table, it would automatically update the ‘Stock Count’ in the items_list table.

Additionally, I would like to know how to automatically create a new table when a new item is added into the items_list.

I know it might be very confusing but if you have further questions, feel free to email me at owenn2106@icloud.com. Thank you so much in advance.

Below is the program code I have:

from tkinter import *
import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
import sqlite3


def update(rows):
    tree.delete(*tree.get_children())
    for i in rows:
        tree.insert('', 'end', value=i)

def search():
    q2 = q.get()
    query = "SELECT TipeBarang, NoKartu, JumlahStok FROM items_list WHERE TipeBarang LIKE '%"+q2+"%'"
    cursor.execute(query)
    rows = cursor.fetchall()
    update(rows)

def clear():
    query = "SELECT TipeBarang, NoKartu, JumlahStok FROM items_list"
    cursor.execute(query)
    rows = cursor.fetchall()
    update(rows)

def getrow(event):
    rowid = tree.identify_row(event.y)
    print(rowid)
    item = tree.item(tree.focus())
    t1.set(item('values')(0))
    t2.set(item('values')(1))
    t3.set(item('values')(2))

def update_item():
    new = t1.get()
    new_card = t2.get()
    new_count = t3.get()
    if messagebox.askyesno('Confimation', 'Yakin ubah?'):
        query = "UPDATE items_list SET JumlahStok = ? WHERE TipeBarang = ? AND NoKartu = ?"
        parameters = (new_count, new, new_card)
        cursor.execute(query, parameters)
        clear()
    else:
        return True

def add_item():
    new = t1.get()
    new_card = t2.get()
    new_count = t3.get()
    if messagebox.askyesno('Confirmation', 'Tambah Barang Baru?'):
        query = "INSERT INTO items_list(TipeBarang, NoKartu, JumlahStok) VALUES(?, ?, ?)"
        parameters = (new, new_card, new_count)
        cursor.execute(query, parameters)
        clear()
    else:
        return True

def delete_item():
    item_id = ent1.get()
    if messagebox.askyesno('Confirmation', 'Yakin Hapus?'):
        query = "DELETE FROM items_list WHERE TipeBarang = "+"'"+item_id+"'"
        cursor.execute(query)
        clear()
    else:
        return True

def open_card_window():

    def update_small(rows):
        smalltree.delete(*smalltree.get_children())
        for i in rows:
            smalltree.insert('', 'end', value=i)

    def getrow_small(event):
        rowid = smalltree.identify_row(event.y)
        print(rowid)
        item = smalltree.item(smalltree.focus())
        text1.set(item('values')(0))
        text2.set(item('values')(1))
        text3.set(item('values')(2))
        text4.set(item('values')(3))
        text5.set(item('values')(4))

    def update_card():
        return True

    def add_card():
        return True

    def delete_card():
        return True

    card_content = Toplevel(root)
    card_content.title('Kartu Stok')

    text1 = StringVar()
    text2 = StringVar()
    text3 = StringVar()
    text4 = StringVar()
    text5 = StringVar()

    subframe1 = LabelFrame(card_content)
    subframe2 = LabelFrame(card_content, text='Ubah Konten')
    subframe1.pack(fill='both', expand='True', padx=20, pady=10)
    subframe1.config(bg='light grey')
    subframe2.pack(fill='both', expand='True', padx=20, pady=10)
    subframe2.config(bg='light grey')

    smalltree = ttk.Treeview(subframe1, height=20, columns=(1,2,3,4,5), show='headings')
    smalltree.pack(expand=True, fill='x')
    smalltree.heading(1, text='Tanggal')
    smalltree.heading(2, text='Keterangan')
    smalltree.heading(3, text='Masuk')
    smalltree.heading(4, text='Keluar')
    smalltree.heading(5, text='Total Stok')

    smalltree.bind('<Double 1>', getrow_small)


    #User Data Section
    label1 = Label(subframe2, text='Tanggal:')
    label1.grid(row=0, column=0, padx=5, pady=3)
    label1.config(bg='light grey')
    entry1 = Entry(subframe2, textvariable=text1)
    entry1.grid(row=0, column=1, padx=5, pady=3)

    label2 = Label(subframe2, text='Keterangan:')
    label2.grid(row=1, column=0, padx=5, pady=3)
    label2.config(bg='light grey')
    entry2 = Entry(subframe2, textvariable=text2)
    entry2.grid(row=1, column=1, padx=5, pady=3)

    label3 = Label(subframe2, text='Masuk:')
    label3.grid(row=2, column=0, padx=5, pady=3)
    label3.config(bg='light grey')
    entry3 = Entry(subframe2, textvariable=text3)
    entry3.grid(row=2, column=1, padx=5, pady=3)

    label4 = Label(subframe2, text='Keluar:')
    label4.grid(row=3, column=0, padx=5, pady=3)
    label4.config(bg='light grey')
    entry4 = Entry(subframe2, textvariable=text4)
    entry4.grid(row=3, column=1, padx=5, pady=3)

    label5 = Label(subframe2, text='Total:')
    label5.grid(row=4, column=0, padx=5, pady=3)
    label5.config(bg='light grey')
    entry5 = Entry(subframe2, textvariable=text4)
    entry5.grid(row=4, column=1, padx=5, pady=3)

    upd_crd = Button(subframe2, text='Update', command=update_card)
    add_crd = Button(subframe2, text='Add New', command=add_card)
    del_crd = Button(subframe2, text='Delete', command=delete_card)

    add_crd.grid(row=5, column=0, padx=5, pady=3)
    upd_crd.grid(row=5, column=1, padx=5, pady=3)
    del_crd.grid(row=5, column=2, padx=5, pady=3)

    item_id = ent1.get()
    query = "SELECT * FROM "+"'"+item_id+"'"
    cursor.execute(query)
    rows = cursor.fetchall()
    update_small(rows)


#Connecting to Database
db = sqlite3.connect('stok.db')
cursor = db.cursor()

root = Tk()
q = StringVar()
t1 = StringVar()
t2 = StringVar()
t3 = StringVar()

#SUB-FRAMES
wrapper1 = LabelFrame(root, text='List Stok')
wrapper2 = LabelFrame(root, text='Search')
wrapper3 = LabelFrame(root, text='Update Data')
wrapper1.pack(fill='both', expand='True', padx=20, pady=10)
wrapper1.config(bg='light grey')
wrapper2.pack(fill='both', expand='True', padx=20, pady=10)
wrapper2.config(bg='light grey')
wrapper3.pack(fill='both', expand='True', padx=20, pady=10)
wrapper3.config(bg='light grey')

#TREEVIEW
tree = ttk.Treeview(wrapper1, height=17, columns=(1,2,3), show='headings')
tree.pack(expand=True, fill='x')
tree.heading(1, text='Tipe Barang')
tree.heading(2, text='Nomor Kartu')
tree.heading(3, text='Jumlah Stok')

ttk.style = ttk.Style()
ttk.style.configure('Treeview', font=('helvetica',16))
ttk.style.configure('Treeview.Heading', font=('helvetica', 18, 'bold'))

tree.bind('<Double 1>', getrow)

query = 'SELECT TipeBarang, NoKartu, JumlahStok FROM items_list'
cursor.execute(query)
rows = cursor.fetchall()
update(rows)

#Search Section
searchfield = Entry(wrapper2, textvariable=q)
searchfield.pack(side=tk.LEFT, padx=25)
btn= Button(wrapper2, text='Search', command=search)
btn.pack(side=tk.LEFT, padx=6)
clr = Button(wrapper2, text='Clear', command=clear)
clr.pack(side=tk.LEFT, padx=6)

#User Data Section
lbl1 = Label(wrapper3, text='Tipe Barang:')
lbl1.grid(row=0, column=0, padx=5, pady=3)
lbl1.config(bg='light grey')
ent1 = Entry(wrapper3, textvariable=t1)
ent1.grid(row=0, column=1, padx=5, pady=3)

lbl2 = Label(wrapper3, text='Nomor Kartu:')
lbl2.grid(row=1, column=0, padx=5, pady=3)
lbl2.config(bg='light grey')
ent2 = Entry(wrapper3, textvariable=t2)
ent2.grid(row=1, column=1, padx=5, pady=3)

lbl3 = Label(wrapper3, text='Jumlah Stok:')
lbl3.grid(row=2, column=0, padx=5, pady=3)
lbl3.config(bg='light grey')
ent3 = Entry(wrapper3, textvariable=t3)
ent3.grid(row=2, column=1, padx=5, pady=3)

upd_btn = Button(wrapper3, text='Update', command=update_item)
add_btn = Button(wrapper3, text='Add New', command=add_item)
del_btn = Button(wrapper3, text='Delete', command=delete_item)

open_card = Button(wrapper3, text='Open Card', command=open_card_window)

add_btn.grid(row=5, column=0, padx=5, pady=3)
upd_btn.grid(row=5, column=1, padx=5, pady=3)
del_btn.grid(row=5, column=2, padx=5, pady=3)
open_card.grid(row=1, column=3, padx=10)

root.title('Stok Barang Sumber Makmur')
root.configure(background='light grey')
root.geometry('800x700')
root.mainloop()

Note: the column names are in Bahasa Indonesia. Where TipeBarang basically means Item Name and JumlahStok is Stock Count

sql server – SQL Trigger fails retrieve new record in second query

Any advice on this will be appreciated
This script created a table with new records.

My script is failing to assign the “Inserted” ID to the query but manually assigning a value works
(a) Tried Cast both side of the Where Clause to int
(b) Moved this section within the second BEGIN -END section

@MAIL_BODY = @MAIL_BODY +
    '<tr>' +
    '<td>' + @OrderID + '</td>' +
    '<td>' + @Description + '</td>' +
    '<td>' + @DiscountedPrice + '</td>' +
    '</tr>' FROM (dbo).(OrderItem) it INNER JOIN (dbo).(Product) pt on pt.ProductID=it.ProductID WHERE it.OrderID = @OrderID``

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER (dbo).(EMAILFINAL)
ON (dbo).(Order)
AFTER INSERT
AS

BEGIN
DECLARE @OrderID VARCHAR(3),
@inpToEmail VARCHAR(MAX),
@Description VARCHAR(MAX),
@DiscountedPrice VARCHAR(MAX),
@MAIL_BODY VARCHAR(MAX)

SELECT @OrderID = i.OrderID FROM inserted i;
IF (@OrderID IS NOT NULL)

SELECT @inpToEmail=dr.DistributorEmail from (dbo).(Distributor) dr
inner join (dbo).(User) Ur on ur.DistributorID=dr.DistributorID
inner join (dbo).(Order) odr on odr.UserID=Ur.UserID
where odr.OrderID= + @OrderID;

PRINT @inpToEmail

SET @MAIL_BODY = ” +

OrderID
Feature
Cost($)

SELECT
@MAIL_BODY = @MAIL_BODY +
” +
” + CAST((OrderID) AS VARCHAR(11)) + ” +
” + CAST((ProductName) AS VARCHAR(50)) + ” +
” + CAST((DiscountedPrice) AS VARCHAR(11)) + ” +

FROM (dbo).(Order_Product)
where Orderid = @OrderID

BEGIN
SET NOCOUNT ON;

declare
@HtmlHeader nvarchar(500),
@body nvarchar(max),
@inpEmailProfiler VARCHAR(50) =’Notifications’,
@inpBodyFormat VARCHAR(5) =’HTML’,
@inpSubjectLine NVARCHAR(MAX)=’Quotation’

SELECT

@MAIL_BODY = @MAIL_BODY +
” +
” + @OrderID + ” +
” + @Description + ” +
” + @DiscountedPrice + ” +

FROM (dbo).(OrderItem) it
INNER JOIN (dbo).(Product) pt on pt.ProductID=it.ProductID
WHERE it.OrderID = @OrderID

SELECT @MAIL_BODY = @MAIL_BODY + ” +

‘ + ‘
‘ + ‘
‘ + ‘
‘ + ‘Kind regards,’ +

‘ + ‘Kvikna ehf.’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @inpEmailProfiler,
@recipients = @inpToEmail,
@body_format=’HTML’,
@body = @MAIL_BODY,
@subject = @inpSubjectLine;

END
END