How to change current_user to display dbo

Question: Currently when I execute the sql code - SELECT CURRENT_USER , the return is my login name . I'd like to change the result to display dbo , as there are a number of application code which rely on the login name to be dbo. How can I change the recordset to display dbo? Answer: The easiest way to change the SELECT CURRENT_USER - result is to change the privilege to sysadmin. There are more details on select current_user shows dbo . The quick summary is if the current user is a sysadmin privilege , the recordset returns “dbo”,... Read more →

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 →