DELETE FROM OPENQUERY

Question: How can I DELETE using OPENQUERY? Normally for a SELECT from OPENQUERY , I'll do something like : SELECT col1 ,col2 FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Can a similar method be used but for DELETE? Answer: To use DELETE , it's a similar process to the SELECT but using the DELETE syntax. Taking the SELECT example above - you'll do the following. Note:this example will delete ALL the rows in the linked server table . : DELETE FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Read more on OPENQUERY,Linked Servers and AS400 SQL Server – Datetime date range , AS400... Read more →

Get Windows OS version using t-sql

Method 1 : Query sys.dm_os_windows SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info; Method 2 : Use xp_cmdshell - although this does mean enabling xp_cmdshell , which is in many organisations as security violation exec master..xp_cmdshell 'systeminfo' Method 3: The @@version returns system and build information for the current SQL Server. SELECT @@version Method 4: xp_msver returns details about the actual build number of the server and information about the server environment.This can be very useful if you want to integrated as part of stored procedure, batch or other type of t-sql code. xp_msver Sample output 1 ProductName NULL Microsoft SQL... Read more →

How to create a graph table in SQL Serevr 2017

Question: How do I create a graph table in SQL Server 2017? Answer: To create a graph database table uses similar syntax as the CREATE TABLE syntax with the addition of the AS NODE keyword or the AS EDGE at the end of the CREATE TABLE syntax. Normally you would create a table such as : CREATE TABLE dbo.MyNode (ID int , NAME varchar(30)); In the graph table CREATE you would do it like: CREATE TABLE dbo.MyNode (ID int , NAME varchar(30)) AS NODE; or CREATE TABLE dbo.MyEdge(ID int , NAME varchar(30)) AS EDGE ; Once you've created the tables... Read more →

How to use parsename with SERVERPROPERTY('productversion')

Question: I'd like to use SERVERPROPERTY('productversion') for purposes of reporting on current sql server level. I'm using the SERVERPROPERTY('productversion') funcion , but am struggling with how to split the parts of the details , to make it easier for reporting. For example : 13.0.5081.1 , I'd like to split as Major = 13 Minor = 0 Build = 5081 Revision = 1 Answer: The PARSENAME function can be used to split the SERVERPROPERTY('productversion') and avoid using various string functions. PARSENAME is commonly used to split object names into there constituent parts. Here is an example: select parsename('master.dbo.sysdatabases',1) select parsename('master.dbo.sysdatabases',2) select... Read more →

How to convert sys.master_files size column into GB

Question: I need to convert the sys.master_files size column into GB. Currently when I run select name,size from sys.master_files I get back the file size as KB. I've tried doing something - size/1024/1024 - but I'm getting incorrect results. How do I get the current sys.master_files size unit into the GB format? Answer: The size column in the sys.master_files is an int data type and is represented as 8-KB pages. In the example you've given above - you're not taking into account the 8-KB pages. You need to multiply the size value by 8. i.e size * 8 Find an... Read more →