— 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 ‘%=*%’
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.
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)
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.
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
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
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
