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

]]>https://sqlarchive.wordpress.com/2016/05/18/find-legacy-sql-joins/feed/0lifesolioInplace Upgrade Win 2008 to 2008R2https://sqlarchive.wordpress.com/2016/05/18/inplace-upgrade-win-2008-to-2008r2/
https://sqlarchive.wordpress.com/2016/05/18/inplace-upgrade-win-2008-to-2008r2/#respondWed, 18 May 2016 10:59:26 +0000http://sqlarchive.wordpress.com/?p=95Continue reading →]]>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

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

Click Upgrade

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.

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.

]]>https://sqlarchive.wordpress.com/2016/05/18/inplace-upgrade-win-2008-to-2008r2/feed/0lifesolio12345678910111213MSSQL Replication between different SQL versions configuring Subscriptionshttps://sqlarchive.wordpress.com/2015/08/28/mssql-replication-between-different-sql-versions/
https://sqlarchive.wordpress.com/2015/08/28/mssql-replication-between-different-sql-versions/#respondFri, 28 Aug 2015 05:06:34 +0000http://sqlarchive.wordpress.com/?p=11Continue reading →]]>Came across an instance at work, where I had to set up replication between 2 different SQL versions,

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)

b) Pick the Publisher

c)

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

e) Pick the Publication that needs to be subscribed

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

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

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

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

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.

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;

]]>https://sqlarchive.wordpress.com/2015/06/22/actual-database-size/feed/0lifesolioCoalesce Inside a Casehttps://sqlarchive.wordpress.com/2015/02/12/coalesce-inside-a-case/
https://sqlarchive.wordpress.com/2015/02/12/coalesce-inside-a-case/#respondThu, 12 Feb 2015 02:31:34 +0000http://sqlarchive.wordpress.com/?p=51]]>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

]]>https://sqlarchive.wordpress.com/2015/02/12/coalesce-inside-a-case/feed/0lifesolioMySQL Startup errorhttps://sqlarchive.wordpress.com/2015/01/30/mysql-startup-error/
https://sqlarchive.wordpress.com/2015/01/30/mysql-startup-error/#respondFri, 30 Jan 2015 06:06:02 +0000http://sqlarchive.wordpress.com/?p=43Continue reading →]]>Installed an windows version of MySQL and was trying to start it from a cmd prompt.

To Start MySQLserver
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”

]]>https://sqlarchive.wordpress.com/2015/01/30/mysql-startup-error/feed/0lifesolioUser and User Group Querieshttps://sqlarchive.wordpress.com/2015/01/19/user-and-user-group-queries/
https://sqlarchive.wordpress.com/2015/01/19/user-and-user-group-queries/#respondMon, 19 Jan 2015 07:33:48 +0000http://sqlarchive.wordpress.com/?p=37Continue reading →]]>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’

There are 0 rows in 1 pages for object “MESTEMPLATEDOCUMENTS”.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS'(object ID 1394416337).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (dbo.MESTEMPLATEDOCUMENTS’).

Implementing the repair on Dev or Test first to make sure the repair doesn’t cause any unintended results. Please take a Backup of the Database on which you would be working

Steps Followed:
1) Run DBCC Checktable(tablename) to verify if the issue is still occurring
2) If it does, query the table to see if you have any data in there to gauge the data loss after
the repair.
3) The table in question is empty
4) Change DB to “Single User Mode”
USE [master]
GO
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
5) Run the below to correct the errors
DBCC CHECKTABLE (MESTEMPLATEDOCUMENTS, REPAIR_ALLOW_DATA_LOSS )

“java.io.FileNotFoundException: http://dev-perfdb2/olap/msmdpump.dll” is thrown by an application due to default configuration in the IIS sever which throws an error when the content length of the data (the data which is passed from an application to DB is passed through the IIS) is too large.

Resolution: So to subdue this error we need to increase the maxAllowedContentLength on the IIS server where OLAP is setupOS: Windows 2012SSAS : SQL 2012Web server: IIS 8.0

To grant permission for Non-Admin users to use Activity Monitor on an SQL 2012 server, follow the below
1) Add [Domain\Username] to [Computer Management] –> [Performance Monitor Users]
2) Run the below script on SQL Instance level for the User that your are granting access to