Posts

Showing posts from January, 2009

a small t-sql puzzle

A friend of mine found a small sql puzzle online somewhere and told me about that.I know it is not that hard to solve the problem using t-sql but I want to find out all the possible ways that I can to solve the puzzle.

Two tables
1.Users with their color assignments
2.All the possible colors.

Problem:
Find all the people that has all the colors in the second table.

Solutions:

Declare @usrs table(
[name] varchar(5),
Color varchar(10)
)
Declare @AllColors table(
Color varchar(10)
)
insert into @usrs values('a','red')
insert into @usrs values('a','green')
insert into @usrs values('a','blue')
insert into @usrs values('b','red')
insert into @usrs values('b','green')
insert into @usrs values('c','blue')
insert into @usrs values('c','red')
insert into @usrs values('c','green')
insert into @usrs values('d','blue')
insert into @usrs values('d','red')
insert in…

OSQL - Running all the SQL Scripts in a given folder

Like I said in my earlier post OSQL is very useful to run sql scripts and run queries.This post demonstrates the use of OSQL in running all the scripts in a given folder.

@ECHO OFF
:: Keep variables local

SETLOCAL
:: Check command line arguments
SET Server=%1
SET Database=%2
SET Test=%3

IF NOT DEFINED Server
GOTO Syntax

IF NOT DEFINED Database GOTO Syntax

IF DEFINED Test GOTO Syntax

:: Execute sql scriptsFOR %%f IN (*.sql)
DO
(
:: ECHO Running script %%f... ECHO ^>^> %%f
OSQL -E -S %1 -d %2 -n -m-1 -b -i "%%f"
IF ERRORLEVEL 1
( ECHO.
ECHO ^>^> Critcal error in script %%f! Ending execution.
GOTO End
)
)
:: Display complete message
ECHO.
ECHO Finished running sql scripts on database %2

:: Done
GOTO End

:Syntax
ECHO.
ECHO Usage: RunSQLScripts server database scriptpath
GOTO End

:End
ENDLOCAL

OSQL - Running Queries in a Batch File

OSQL is another very useful tool I and my group use very often.We used it to execute all the sql files in a folder against a given server and database.

Here is the batch file I use to check if all the user procedures and user defined functions in a database has a execute persmission for a user ROLE called 'APPUSER'.

This sample batch file also demonstrates how to execute sql queries using OSQL in a batch file.


@ECHO OFF
:: Keep variables local
SETLOCAL

SET Query="select so.name from sys.objects so LEFT OUTER JOIN sys.database_permissions p on so.[object_id] = p.major_id inner JOIN sys.database_principals dp on dp.name = 'APPUSER' where so.Type IN ('P') and p.major_id is null"

ECHO ^>^> Running CheckIfAPPUSERIsGrantedExecuteOnAllProcs
ECHO ^>^> ----------------------------------------------
:serverset server=
set /P server=^>^> Enter Server Name or Type exit to Exit: %=%

if "%server%"=="" goto serverif "%server%"=…