Find Legacy SQL Joins

— will list both Views and Stored procedures in a particular database

SELECT OBJECT_NAME(object_id),OBJECT_DEFINITION(object_id) FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE ‘%*=%’ or OBJECT_DEFINITION(object_id) LIKE ‘%=*%’

Advertisements
Posted in Administration | Leave a comment

Inplace Upgrade Win 2008 to 2008R2

Just wanted to write this for the fun of it, I know it is an old edition which is out of main stream support as of Jan 2015, it has extended support till 2020 WindowsSupport_LifeCycle

The upgrade is pretty self explanatory. Also please take a backup of the whole server before starting the process. If just databases, back them up and copy them to an external device or a shared drive that’s not on this server so that we can rebuild SQL in case the upgrade corrupts the server.

Start the install process, Select “Install now” Tab

1

Then Select “Go online for Update’s” a recommended one (hopefully the server can connect to the web)
2

3
4

5

 

Click Upgrade
6

7
8

System reboots automatically many times over the period of the upgrade at the above step. If you are on a virtual environment connect to the server thru Vcenter.

9
10
11

12
13

It creates a new NIC along with the Existing one.. Reconfigure the NIC for you to be able to ping or RDP in to the server after the upgrade.

Old accounts work, log back in to the server and verify everything including SQL.

 

Posted in Windows | Leave a comment

MSSQL Replication between different SQL versions configuring Subscriptions

Came across an instance at work, where I had to set up replication between 2 different SQL versions,

Publisher: SQL 2005 SP3
Subscriber: SQL 2008 R2
Type: Push Subscription
Distributor: Publisher

1) Configure Publication on the Publisher
This article doesn’t describe the steps for configuring a Publisher, assuming here the Publications are already set ( will write up another article on a later date for Publication part)

2) Configure Subscriptions on the Subscriber
a)
1

b) Pick the Publisher
b

c)
c

d) Connect to the Publisher using “sa” account or any other SQL account.

d

e) Pick the Publication that needs to be subscribed
e

f) Pick the Distributor, in this case it is same as Publisher
f
g) Subscription database that holds the published table is picked here

g
h) Connecting to the Distributor and Subscriber, click on the ellipsis

h

h1

h2

i) Schedule the subscription to run after the snapshot SQL agent job is completed on the
Publisher.
i
i1
j) We can trigger the subscription immediately or after the snapshot is generated.
j
j1

k) Click finish and the subscription gets created with a job scheduled to run on the
Publisher.
k
k1

l) Repeat the same to set up multiple subscriptions, you can see the list of
subscriptions under “Local Subscriptions” in subscriber as well as under “Local
Publications” in the Publisher.

Posted in Uncategorized | Leave a comment

Actual Database Size

The below script would provide the Actual DB file usage size rather than DB size (Used + Free) should work for SQL 2008 and upwards

Borrowed the “dbo.sp_foreachdb” script from Aaron Bertrand (making-a-more-reliable-and-flexible-spmsforeachdb/) to list out all databases in a SQL server.

Step1: Create the below stored procedure.

USE [master];
GO
CREATE PROCEDURE dbo.sp_foreachdb
@command             NVARCHAR(MAX),
@replace_character   NCHAR(1)       = N’?’,
@print_dbname        BIT            = 0,
@print_command_only  BIT            = 0,
@suppress_quotename  BIT            = 0,
@system_only         BIT            = NULL,
@user_only           BIT            = NULL,
@name_pattern        NVARCHAR(300)  = N’%’,
@database_list       NVARCHAR(MAX)  = NULL,
@recovery_model_desc NVARCHAR(120)  = NULL,
@compatibility_level TINYINT        = NULL,
@state_desc          NVARCHAR(120)  = N’ONLINE’,
@is_read_only        BIT            = 0,
@is_auto_close_on    BIT            = NULL,
@is_auto_shrink_on   BIT            = NULL,
@is_broker_enabled   BIT            = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@sql    NVARCHAR(MAX),
@dblist NVARCHAR(MAX),
@db     NVARCHAR(300),
@i      INT;

IF @database_list > N”
BEGIN
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.name) – 1
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
)
SELECT @dblist = REPLACE(REPLACE(REPLACE(x,”,’,’),
”,”),”,”)
FROM
(
SELECT DISTINCT x = ‘N”’ + LTRIM(RTRIM(SUBSTRING(
@database_list, n,
CHARINDEX(‘,’, @database_list + ‘,’, n) – n))) + ””
FROM n WHERE n <= LEN(@database_list)
AND SUBSTRING(‘,’ + @database_list, n, 1) = ‘,’
FOR XML PATH(”)
) AS y(x);
END

CREATE TABLE #x(db NVARCHAR(300));

SET @sql = N’SELECT name FROM sys.databases WHERE 1=1′
+ CASE WHEN @system_only = 1 THEN
‘ AND database_id IN (1,2,3,4)’
ELSE ” END
+ CASE WHEN @user_only = 1 THEN
‘ AND database_id NOT IN (1,2,3,4)’
ELSE ” END
+ CASE WHEN @name_pattern <> N’%’ THEN
‘ AND name LIKE N”%’ + REPLACE(@name_pattern, ””, ”””) + ‘%”’
ELSE ” END
+ CASE WHEN @dblist IS NOT NULL THEN
‘ AND name IN (‘ + @dblist + ‘)’
ELSE ” END
+ CASE WHEN @recovery_model_desc IS NOT NULL THEN
‘ AND recovery_model_desc = N”’ + @recovery_model_desc + ””
ELSE ” END
+ CASE WHEN @compatibility_level IS NOT NULL THEN
‘ AND compatibility_level = ‘ + RTRIM(@compatibility_level)
ELSE ” END
+ CASE WHEN @state_desc IS NOT NULL THEN
‘ AND state_desc = N”’ + @state_desc + ””
ELSE ” END
+ CASE WHEN @is_read_only IS NOT NULL THEN
‘ AND is_read_only = ‘ + RTRIM(@is_read_only)
ELSE ” END
+ CASE WHEN @is_auto_close_on IS NOT NULL THEN
‘ AND is_auto_close_on = ‘ + RTRIM(@is_auto_close_on)
ELSE ” END
+ CASE WHEN @is_auto_shrink_on IS NOT NULL THEN
‘ AND is_auto_shrink_on = ‘ + RTRIM(@is_auto_shrink_on)
ELSE ” END
+ CASE WHEN @is_broker_enabled IS NOT NULL THEN
‘ AND is_broker_enabled = ‘ + RTRIM(@is_broker_enabled)
ELSE ” END;

INSERT #x EXEC sp_executesql @sql;

DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT CASE WHEN @suppress_quotename = 1 THEN
db
ELSE
QUOTENAME(db)
END
FROM #x ORDER BY db;

OPEN c;

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@command, @replace_character, @db);

IF @print_command_only = 1
BEGIN
PRINT ‘/* For ‘ + @db + ‘: */’
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ @sql
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
END
ELSE
BEGIN
IF @print_dbname = 1
BEGIN
PRINT ‘/* ‘ + @db + ‘ */’;
END

EXEC sp_executesql @sql;
END

FETCH NEXT FROM c INTO @db;
END

CLOSE c;
DEALLOCATE c;
END

Step2: Run the below on the Server to generate the report, it can be modified as need by adding more columns or removing them.

Create Table ##temp
(
DatabaseName sysname,
DB_File_Name sysname,
[Size-Mb] decimal (18,2),
[Used-Mb] decimal (18,2),
[Maximum_Size-Mb] decimal (18,2),
[Growth] varchar(20),
[GroupName] sysname,
[%Free] int
)
Exec sp_foreachdb’
Use ?;
Insert Into ##temp (DatabaseName, DB_File_Name, [Size-Mb], [Used-Mb],[Maximum_Size-Mb],[Growth],[GroupName],[%Free])
Select DB_NAME() AS [DatabaseName], Name,
cast(size/128.0 as numeric(10,1)) as [size-mb],
cast(FILEPROPERTY(name, ”spaceused”)/128.0 as numeric(10,1)) as [used-mb],
maxsize [maximum size-mb],
cast(growth*8/1024 as varchar(11))+
case when status&0x100000>0 then ”%” else ”” end [growth],
isnull(filegroup_name(groupid),”log”) [groupname],
cast(100*(size/128-(FILEPROPERTY(name,”spaceused”)/128))/(size/128.0) as int) [% free]
from sysfiles

Select * From ##temp
drop table ##temp

Posted in Administration, MSSQL | Leave a comment

Coalesce Inside a Case

Got in to an instance at work where i had to modify a SP to get non-null values out of a case statement code block

CASE
WHEN condition_expression THEN  COALESCE(Column1, Column2)
ELSE Column2
END AS New_Column

Posted in Development | Leave a comment

MySQL Startup error

Installed an windows version of MySQL and was trying to start it from a cmd prompt.

To Start MySQL server
from command line, you should start a console window (or “DOS window”) and enter the below or direct your command to where your MySQL binaries are installed on your system:
C:\> “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld”

If you get an error as below from running the above

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. please use –explicit_defaults_for_timestamp server option

the issue can be fixed by adding the below to my.ini , under “mysqld” section

# Enable the query cache
explicit_defaults_for_timestamp = true

run the below again to successfully start the services
C:\> “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld”

To check MySQL server status
C:\> “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin” -u root -p status

To Stop MySQL server
C:\> “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin” -u root -p shutdown

Posted in Administration, MySQL | Leave a comment

User and User Group Queries

I get user requests all the time for access rights and i don’t have read access to AD to check groups to see if the user is already a member or not.

We can run the below scripts from SQL server Management Studio

Domain Level:
— Collect AD Groups for “Domain Account” and stores it in a Temp table
EXECUTE AS LOGIN = ‘Domain Account’
SELECT name INTO #User from sys.login_token
WHERE TYPE = ‘WINDOWS GROUP’
REVERT

— Query the list of AD Groups that the user account belong to from Temp Table
SELECT name FROM #User

Server Level:
The below just are localized to the server, the queries work just for Domain user or Group that have access to the SQL server.If you include the ones not on the server they dont return any results.

/*This query based on Domain account passed lists all of the groups it belongs to and kind of permissions they have including the Domain account itself listing with in that server*/
EXEC master.dbo.xp_logininfo’Domain User’,’all’

— This query lists all members in a group.
EXEC master.dbo.xp_logininfo’MyDomain\MyGroup’,’members’

References:

http://sqlstudies.com/2014/12/29/finding-a-common-ad-group-for-a-set-of-users/
http://royalsql.com/2013/08/05/the-fairytale-girls-in-deep-distress-and-had-to-query-acitve-directory/

Posted in MSSQL, Permissions | Leave a comment