#transact-sql

Cycling SQL Server Logs

In the not too distant past, Windows servers were rebooted much too frequently for a truly stable environment, but we got used to the practice. With more recent versions of Windows, reboots have become less frequent and it isn’t uncommon to see a server that hasn’t been rebooted in more than 90 days. The issue is that SQL Server is using the same log file during that time, with thousands or even…

Who is Connected using SSMS to your SQL Server Database

Which users are connected to your database using SQL Server Management Studio? This may be helpful in quickly determining which users are directly connected vs. those connected via an application. SELECT CO.client_net_address, SE.host_name, SE.login_name, ST.text FROM sys.dm_exec_sessions SE INNER JOIN sys.dm_exec_connections CO ON SE.session_id = CO.session_id CROSS APPLY…

Se vi siete imbattuti in questo articolo è probabile abbiate l'esigenza di reimpostare il valore numerico (contatore seed) di una colonna Identity utilizzato da una tabella del vostro database SQL Server. Il metodo descritto in questo articolo funziona con tutte le versioni di SQL Server: SQL Server 2002, SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, fino all'attualmente più recente SQL Server 2017.

Argomenti

table_name: Nome della tabella di cui verificare il valore Identity corrente. La tabella specificata deve includere una colonna Identity. I nomi delle tabelle devono essere conformi alle regole per gli identificatori. I nomi in due o tre parti devono essere delimitati, ad esempio 'Person.AddressType' o . NORESEED: Specifica che non è necessario modificare il valore Identity corrente. RESEED: Specifica che è necessario modificare il valore Identity corrente. new_reseed_value: Nuovo valore da utilizzare come valore corrente della colonna Identity. WITH NO_INFOMSGS: Disattiva tutti i messaggi informativi.

Esempio

Ad esempio, la seguente istruzione: Reimposterà a 1 il valore della colonna identity della Tabella1. Una volta eseguita, l'istruzione fornirà il seguente messaggio informativo di conferma: Controllo delle informazioni Identity: valore Identity corrente '1', valore di colonna corrente '1'. Esecuzione DBCC completata. Se sono stati visualizzati messaggi di errore DBCC, rivolgersi all'amministratore di sistema.

Riferimenti

If you came across this article, you probably need to reset the numeric value (seed counter) of an identity column used by a table in your SQL Server database. The method described here works with all versions of SQL Server : SQL Server 2002, SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, up to the most recent SQL Server 2017.

Arguments

table_name: Is the name of the table for which to check the current identity value. The table specified must contain an identity column. Table names must follow the rules for identifiers. Two or three part names must be delimited, such as 'Person.AddressType' or . NORESEED: Specifies that the current identity value shouldn't be changed. RESEED: Specifies that the current identity value should be changed. new_reseed_value: Is the new value to use as the current value of the identity column. WITH NO_INFOMSGS: Suppresses all informational messages.

Example

For example, the following command: Will reset to 1 the identity column value of Table1. Once executed, the command will output the following confirmation message: Identity information control: current Identity value '1', current column value '1. DBCC execution completed. If DBCC error messages were displayed, contact your system administrator.

References

SQL Server Alert for Long-Running Transactions

If you have a long running SQL Server transaction, you might want to send out an alert. You will need to define values for what amount of time constitutes a long running transaction (shown as 10 minutes in the example script), who is to get the email (sent to the General email address in the example script) and what operator is to be notified (Alerts in the operator in the example script). Once…

SQL Server Script to Create INSERT Statements - #sql #adhoc

Though you can generate INSERT statements using SQL Server Management Services if the Generate Scripts functionality is enabled, this script may help in cases when it is not.

USE AtRiskGOSET NOCOUNT ON DECLARE @TableName VARCHAR(255)DECLARE @IndividualInserts BIT SET @TableName = 'TableName'SET @IndividualInserts = 0 DECLARE @TableStructure TABLE (ColumnName VARCHAR(255), DataType VARCHAR(50),…

SQL Server: List table columns names instead of using SELECT *

In SQL Server, and in any common SQL language, the asterisk ( * ) tells the database engine to return all columns within a table. Using “SELECT *” in your queries is a bad idea, and the reasons why we don’t recommend this is:

Security – If an unauthorized user gets access to your query and it returns * (meaning all columns)  it could leave every column available for misuse.

Performance– It…

You can build an interesting game with almost any computer language. Building a game using Transact-SQL seems to be the biggest challenge, but it has been done before. I wrote about this subject before, but there is now a new effort by Daniel Janik as described in this article.

To me this project is more than a simple game. This was not only a quest to help my son and others interested in…