Tag Archives: directory

Icon for Raspberry Pi

How to find a program’s directory in Raspbian OS

For Linux distros most programs are stored in the /usr directory. There is no “Programs Files” directory like for Windows. The executables are typically stored in /usr/bin with additional stuff in /usr/share and libraries in usr/lib etc.

There is also usr/local where stuff gets put when you do the compiling yourself. With /bin tending to be command line tools and /sbin being the directory for command utils only for root.

The quickest way to find the actual directory a program resides in is through terminal using the command “which”.

Here are some examples:

which nano

which gpicview

which chromium-browser

The programs referenced are the preinstalled text editor, image viewer and web browser. All of these examples will return the /usr/bin/ directory.

Note chromium is referred to as “chromium-browser” as typing “which chromium” will return no result as that is the incorrect name for the program. If you are unsure of a program name, run the program and then look for it in task manager to confirm.

How to tell if you are a member of a SQL Server group or create a list of group members using T-SQL

The following scripts will help you determine if you are a member of a group or role or create a list of group members in SQL Server without having to use SQL Server Management Studio. This is a particularly handy script in determining who might have access to the server through Active Directory groups.

/*
The code below indicates whether the current user is a member 
of the specified Microsoft Windows group or SQL Server database role.
A result of 1 = yes
,0 = no
,null = the group or role queried is not valid.
*/

SELECT IS_MEMBER('group or role')

/* 
Example. 
*/

SELECT IS_MEMBER('db_owner')

/*
The code below will create a list of all the logins that are members 
of a group. Don't forget to include domain, e.g. admin\user
*/

EXEC master..xp_logininfo 
@acctname = [group],
@option = 'members'

 

How to identify the location of the SQL Server Error Log file

So you want to find out where the SQL Server Error Log file is located for a specific SQL Server instance?

The Error Log is a great place to start tracking down reasons why SQL Server might be experiencing problems.

The queries below (first one works on SQL Server 2008, second one works on 2016) use the extended stored procedure XP_READERRORLOG, which is typically used to read error logs directly, to return the location of the SQL Server Error Log files. The returned value is then cleaned up so only the directory location is provided. You can then copy and paste the result into your file explorer. 

SQL Server 2008

USE Master;
GO

DECLARE @dir AS VARCHAR(MAX)

-- Command will create the temporary table in tempdb
CREATE TABLE [dbo].[#TmpErrorLog] (
	[LogDate] DATETIME NULL
	,[ProcessInfo] VARCHAR(20) NULL
	,[Text] VARCHAR(MAX) NULL
	);

-- Command will insert the errorlog data into temporary table
INSERT INTO #TmpErrorLog (
	[LogDate]
	,[ProcessInfo]
	,[Text]
	)
EXEC [master].[dbo].xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file'
	,NULL
	,NULL
	,N'asc';

-- retrieves the data from temporary table and writes it to a variable to remove everything but directory location
SET @dir = (
		SELECT REPLACE(REPLACE(REPLACE([Text], 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG', '')
		FROM #TmpErrorLog
		)
SET @dir = LEFT(@dir, LEN(@dir) - 1)

SELECT @dir AS ErrorFileLocation

DROP TABLE #TmpErrorLog

SQL Server 2016

USE Master;
GO

DECLARE @dir AS VARCHAR(MAX)

-- Command will create the temporary table in tempdb
CREATE TABLE [dbo].[#TmpErrorLog] (
	[LogDate] DATETIME NULL
	,[ProcessInfo] VARCHAR(20) NULL
	,[Text] VARCHAR(MAX) NULL
	);

-- Command will insert the errorlog data into temporary table
INSERT INTO #TmpErrorLog (
	[LogDate]
	,[ProcessInfo]
	,[Text]
	)
EXEC [master].[dbo].xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file';


-- retrieves the data from temporary table and writes it to a variable to remove everything but directory location
SET @dir = (
		SELECT REPLACE(REPLACE(REPLACE([Text], 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG', '')
		FROM #TmpErrorLog
		)
SET @dir = LEFT(@dir, LEN(@dir) - 1)

SELECT @dir AS ErrorFileLocation

DROP TABLE #TmpErrorLog

 

How to run multiple SQL scripts automatically in order.

If you’ve been working towards a new deployment to a live database chances are you have written several scripts (possibly dozens) that have been developed/tested against the the development server.

Now the time has come to put the update live. Which would require executing each script against the live database.

This task can be automated by using a very handy batch script to run against the directory the files are saved in.

Caveat: This process does not take into account error handling or rollbacks, it’s just a simple example people can build on.

In order for this to work the files must have been named in a manner that the necessary order of execution corresponds to ascii sort order, i.e. 001_CreateTable.sql, 002_PopulateTable etc. This is standard practice for sql file naming conventions.

Simply create a .BAT file with the following command:
(Swap servername and databaseName for your required server and database names, TIP: SELECT @@servername can provide you with the full server name.)

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause

Place this .BAT file in the directory from which you want the .SQL files to be executed, double click the .BAT file and the command will loop and execute every SQL script in the folder.

How to dynamically pull all text file names from a folder into a SQL Server table

So here’s a quick out of the box solution for . . . you guessed it, dynamically pulling all text files from a folder into a SQL Server table. On it’s own this script isn’t very powerful but it can be paired with SQL queries to make some powerful functionality.

To use simply change the @path variable to the directory path you’re targeting and as it states in the comments . . .

-- NOTE: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
-- *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED

Sayōnara

— THIS QUERY USES CMD TO CAPTURE ALL THE FILES IN A FOLDER
— AND WRITE THE FILE NAMES TO A TABLE
— THE QUERY REQUIRES THAT xp_cmdShell BE ENABLED TO WORK
— THE FOLLOWING ENABLES xp_cmdShell:
— TO ALLOW ADVANCED OPTIONS TO BE CHANGED
EXEC sp_configure 'show advanced options'
,1
GO
— TO UPDATE THE CURRENTLY CONFIGURED VALUE FOR ADVANCED OPTIONS
RECONFIGURE
GO
— TO ENABLE THE FEATURE
EXEC sp_configure 'xp_cmdshell'
,1
GO
— TO UPDATE THE CURRENTLY CONFIGURED VALUE FOR THIS FEATURE
RECONFIGURE
GO
— THE FOLLOWING CREATES A TABLE AND POPULATES IT WITH THE FILES LOCATED IN THE SPECIFIED DIRECTORY
IF OBJECT_ID('ALLFILENAMES', 'U') IS NOT NULL
DROP TABLE ALLFILENAMES
CREATE TABLE ALLFILENAMES (
WHICHPATH VARCHAR(255)
,WHICHFILE VARCHAR(255)
)
DECLARE @filename VARCHAR(255)
,@path VARCHAR(255)
,@cmd VARCHAR(1000)
— GET THE LIST OF FILES TO PROCESS
— NOTES: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
— *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED
SET @path = 'C:\Users\Data\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES (WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES
SET WHICHPATH = @path
WHERE WHICHPATH IS NULL
SELECT *
FROM ALLFILENAMES