Saturday, July 20, 2024
Wednesday, September 6, 2023
Microsoft is offering free online courses with certification.
Microsoft is offering free online courses with certification.
Monday, September 12, 2022
Get RowCount from a Dynamically Executed Statement
-- Get RowCount from a Dynamically Executed Statement
DECLARE
@Period VARCHAR (8) = ''
, @DEDFYXXRecords BIGINT = 0
, @EXECFYXX NVARCHAR (MAX) = ''
, @RowCount BIGINT = 0;
SELECT @EXECFYXX = ' INSERT Data( '
+ ' Period '
+ ', CostCenter '
+ ') '
+ 'SELECT '
+ ' FYXX.Period '
+ ', FYXX.CostCenter '
+ 'FROM DataFY'
+ LEFT (@Period, 2)
+ ' FYXX '
+ ' JOIN DataB da '
+ ' ON FYXX.Account = da.Acct '
+ ' WHERE da.GroupID = 99 '
+ ' AND FYXX.Period = '''
+ @Period
+ '''; SELECT @RowCount = @@ROWCOUNT; ';
-- EXEC sp_executesql @EXECFYXX, N'@RowCount BIGINT OUTPUT', @RowCount OUTPUT
SELECT @DEDFYXXRecords = @RowCount
SELECT @DEDFYXXRecords
Tool for Scripting View Generation
-- Tool for Scripting View Generation
-- Given two databases, source and dest, this script will generate a READ
-- ONLY view in dest for each table or view in source.
-- http://joeskb.blogspot.com/2013/08/tool-for-scripting-view-generation.html
SSIS/SQLAgent - Enable User to See and Start Jobs
-- SSIS/SQLAgent - Enable User to See and Start Jobs
-- To see, the user must be in the SQLAgentReaderRole or SQLAgentUserRole,
-- to start them the user must be in the SQLAgentOperaterRole. As these
-- only exist in MSDB then the user must also be a member there.
Find tables with foreign keys
-- Find tables with foreign keys
-- Source: http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server
SELECT
t.name TableWithForeignKey
, fk.constraint_column_id FK_PartNo
, c.name ForeignKeyColumn
FROM sys.foreign_key_columns fk
JOIN sys.tables t
ON fk.parent_object_id = t.[object_id]
JOIN sys.columns c
ON fk.parent_object_id = c.[object_id]
AND fk.parent_column_id = c.column_id
JOIN sys.tables t1
ON fk.referenced_object_id = t1.[object_id]
ORDER BY
TableWithForeignKey
, FK_PartNo
What are the sizes of my db's?
-- What are the sizes of my db's?
GO
WITH fs
AS (
SELECT
m.database_id
, m.[type]
, m.size * 8.0 / 1024 size
FROM sys.master_files m
)
SELECT
name
, (SELECT SUM (size) FROM fs WHERE [type] = 0 AND fs.database_id = db.database_id) DataFileSizeMB
, (SELECT SUM (size) FROM fs WHERE [type] = 1 AND fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db
Delete old backups
-- Delete old backups -- EXECUTE master.dbo.xp_delete_file 0,N'\\Myapp4\SomeSQLBACKUP\MyCoSQL-TEST','N',N'2017-01-05T11:14:40',1
File tools
-- File tools
-- Find the BAK files:
SELECT TOP 100
m.physical_device_name
, b.backup_start_date
, b.backup_finish_date
, b.backup_size/1024.0 BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE /* database_name like '%Mysmco%' */
m.physical_device_name LIKE '%bak%'
ORDER BY
b.backup_finish_date DESC
-- Find the MDF's and LDF's:
DECLARE @command varchar(1000)
SELECT @command = 'SELECT * FROM sys.database_files'
EXEC sp_MSforeachdb @command
-- Or ... easier
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
-- Or ... even easier
SELECT 'USE [' + name + ']; SELECT SF.filename, SF.size FROM sys.sysfiles SF'
FROM master.dbo.sysdatabases
Force the db offline
-- Force the db offline -- ALTER DATABASE [MyDB-DEV-NEW] SET OFFLINE -- Same but rollback pending transactions -- ALTER DATABASE [MyDB-DEV-NEW] SET OFFLINE WITH ROLLBACK IMMEDIATE
Set to multi user
-- Set to multi user -- ALTER DATABASE [MyDB-DEV-NEW] SET MULTI_USER; -- Same but rollback pending transactions -- ALTER DATABASE [MyDB-DEV-NEW] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
Sunday, August 21, 2022
Rollback any transactions and set db to single user
-- Rollback any transactions and set db to single user mode
-- ALTER DATABASE [MyDB-DEV-NEW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
The restore operation halted prematurely.
-- The restore operation halted prematurely.
-- Repeat the original RESTORE statement specifying WITH RESTART.
-- RESTORE DATABASE [MyDB-DEV] FROM DISK = 'C:\MyDB.bak' WITH RESTART
Restore a full database backup
-- Restore a full database backup
-- RESTORE DATABASE [MyDB-DEV] FROM DISK = 'C:\MyDB.bak'
Recover, NOT Restore, the db
-- Recover, NOT Restore, the db
-- https://msdn.microsoft.com/en-us/library/ms188696.aspx
-- RESTORE DATABASE [MyDB-DEV-NEW] WITH RECOVERY
Most recent Myweb_user
-- Most recent Myweb_user
SELECT TOP 33 wu.*
FROM dbo.Myweb_user wu
ORDER BY wu.MODDATE DESC
What is the status of the backups and restores?
-- What is the status of the backups and restores?
SELECT
r.session_id
, r.command
, CONVERT(NUMERIC(6,2), r.percent_complete) [Percent Complete]
, CONVERT(VARCHAR(20), DATEADD(ms,r.estimated_completion_time,GetDate()),20) [ETA Completion Time]
, CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) [Elapsed Min]
, CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) [ETA Min]
, CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) [ETA Hours]
, CONVERT(VARCHAR(1000)
, (SELECT SUBSTRING( text,r.statement_start_offset/2,
CASE
WHEN r.statement_end_offset = -1
THEN 1000
ELSE (r.statement_end_offset-r.statement_start_offset)/2
END)
FROM sys.dm_exec_sql_text(sql_handle)
)
)
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
Kill the active database connections
-- Kill the active database connections
-- Note: Uncomment the 'exec'
DECLARE @execSql VARCHAR (1000) = ''
, @databaseName VARCHAR (100) = ''
, @dbid INT = 0
SET @execSql = ''
SET @databaseName = 'MyDB-DNN-TMAR'
SET @dbid = db_id(@databaseName)
IF (ISNULL (@dbid, '') = '') PRINT 'Bad DB Name'
SELECT @execSql = @execSql + 'kill ' + CONVERT (CHAR(10), sp.spid) + '; '
FROM master.dbo.sysprocesses sp
WHERE dbid = @dbid
AND sp.DBID <> 0
AND sp.spid <> @@spid
SELECT @execSql
-- EXEC (@execSql)
GO
-- kill 59
Renaming Databases
-- Renaming Databases
sp_who2
-- Optional but should be done for MyDB-DEV-NEW
EXEC Utility.dbo.BACKUP_APP_INTERFACE_PARAMETER_TBL_sp
GO
-- kill 88
SP_WHO2
-- Optional but should be done for MyDB-DEV-NEW
ALTER DATABASE [MyDB-TMAR] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [MyDB-TMAR-NEW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
ALTER DATABASE [MyDB-TMAR]
Modify Name = [MyDB-TMAR_OLD];
GO
ALTER DATABASE [MyDB-TMAR-NEW]
Modify Name = [MyDB-TMAR];
GO
EXEC Utility.dbo.RESTORE_APP_INTERFACE_PARAMETER_TBL_sp 'MyDB-TMAR'
GO
-- ALTER DATABASE [MyDB-TMAR] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
-- ALTER DATABASE [MyDB-TMAR_OLD] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
--use utility
--go
--select * from sysobjects where type = 'p'
-- EXEC Utility.dbo.TMARDBRestore_RemoveTRSJobs 'MyJob'
EXEC Utility.dbo.TMARDBRestore_RemoveTRSJobs 'MyJob'
GO
USE [MyDB-TMAR]
GO
DROP FULLTEXT INDEX ON MyDB_product_search
GO
--DROP FULLTEXT CATALOG MyDB_product_search
--GO
ALTER DATABASE [MyDB-TMAR] SET MULTI_USER;
GO
ALTER DATABASE [MyDB-TMAR_OLD] SET MULTI_USER;
GO
USE [MyDB-TMAR]
GO
IF EXISTS (
SELECT *
FROM sys.database_principals
WHERE name = 'MyDB-'
)
DROP USER [MyDB-]
CREATE USER [MyDB-] FOR LOGIN [MyDB-]
-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]
EXEC sp_addrolemember N'db_owner', N'MyDB-'
-- NOTE: remember to add in user "Myweb" to prod1 and prod2
USE [MyDB-TMAR]
GO
IF EXISTS (
SELECT *
FROM sys.database_principals
WHERE name = 'Myweb'
)
DROP USER [Myweb]
CREATE USER [Myweb] FOR LOGIN [Myweb]
-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]
EXEC sp_addrolemember N'db_owner', N'Myweb'
GO
-- NOTE: remember to add in user "MyAdmin" to prod1 and prod2
USE [MyDB-TMAR]
GO
IF EXISTS (
SELECT *
FROM sys.database_principals
WHERE name = 'MyAdmin'
)
DROP USER [MyAdmin]
CREATE USER [MyAdmin] FOR LOGIN [MyAdmin]
-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]
EXEC sp_addrolemember N'db_owner', N'MyAdmin'
What text is the sp executing?
-- What text is the sp executing?
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 339
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO
What users are active db x?
-- What users are active db x?
USE master
GO
IF OBJECT_ID('tempdb..#dbSPIDS') IS NOT NULL DROP TABLE #dbSPIDS
CREATE TABLE #dbSPIDS (
SPID INT
, [Status] VARCHAR (256)
, [Login] VARCHAR (256)
, HostName VARCHAR (256)
, BlkBy VARCHAR (256)
, DBName VARCHAR (256)
, Command VARCHAR (MAX)
, CPUTime INT
, DiskIO INT
, LastBatch VARCHAR (256)
, ProgramName VARCHAR (256)
, SPID2 INT
, REQUESTID INT
)
INSERT #dbSPIDS EXEC sp_who2
-- SELECT d.*
SELECT 'KILL ', d.SPID, d.*
FROM #dbSPIDS d
where login not like '%sa%'
-- WHERE d.dbname LIKE '%MyDB%dnn%TMAR'
--WHERE d.dbname = 'MyDB-d-TMAR'
-- and (d.dbname LIKE 'MyDBTMAR' OR d.dbname LIKE 'MyDBTMAR_new' )
and (d.dbname LIKE '%760%' )
-- kill 54
Try/catch syntax
-- Try/catch syntax
BEGIN TRY
SELECT 2/0
END TRY
BEGIN CATCH
SELECT Utility.dbo.fnGetErrorTryCatch ()
END CATCH
Find routine with text (wraps 4000 N)
-- Find routine with text (wraps 4000 N)
-- http://www.sqltact.com/2012/04/syssqlmodules-vs-informationschemarouti.html
DECLARE @searchString NVARCHAR (4000) = ''
SET @searchString = 'product'
SELECT s.name
+ '.'
+ o.name
, o.type_desc
, m.[definition]
FROM sys.sql_modules m
JOIN sys.objects o
ON m.object_id = o.object_id
JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE m.[definition] like '%' + @searchString + '%'
ORDER BY o.name
Thursday, August 18, 2022
-- Common commands, settings
SET NOEXEC ON
-- SET NOEXEC OFF
-- SET NOCOUNT ON
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- (the 'recommended' way to look at sysobjects)
-- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Tables
-- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Columns
-- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Views
-- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Routines
-- drop temp table
-- IF OBJECT_ID('tempdb..#dbSPIDS') IS NOT NULL DROP TABLE #dbSPIDS
Monday, June 24, 2019
Linked Server Refering to Self
Likely a problem with DNS tables, this will fix it with a valid SQL user.
-- EXEC sp_addlinkedserver [MyServer], '', 'SQLOLEDB', '1.2.3.4'
-- EXEC sp_addlinkedserver [MyServer], '', 'SQLOLEDB', '1.2.3.4'
Friday, June 21, 2019
sp_send_dbmail solution for attachments
When using the @query parameter with sp_send_dbmail via the SQL
Agent to send an attachment in the email you may get the following error:
“Failed to initialize sqlcmd library
with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.”
Apparently this is the sp_send_dbmail
equivalent of the nearly useless Visual Basic “Object variable or With block
variable not set.” error.
Note that the syntax was
correct and everything worked fine when operating from a query session.
One way to get around it is
to preface the execution context with a power user:
EXECUTE AS LOGIN = 'ElmerFudd'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alert
Stuff',
@recipients = 'bugz@bunny.org; ',
@subject = 'Favorite vacations',
@query = N'
use msdb;
go
select top 10 ...
This took quite an effort to solve so I decided to share …
2¢
Thursday, May 30, 2019
FYI: SS2016 Requires .Net FW 3.5 for DBMail
This was painful enough to figure out that I figured I would share the information with the entire group.
Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard (will not install from a exe).
Detailz …
DatabaseMail.exe (in the instance Binn directory) which is invoked by the Service Broker which is part of the Core database engine since 2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency. Why it will not work with a more recent version of .Net is a mystery, perhaps they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).
Essentially there was no error information available, mail items would just sit in the queue.
Starting the exe itself on the server or invoking dbmail via a sql interface puts this in the application log of the server: “The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following: 'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 14001.'” ~ which, after a lot of time with The Google, led to the answer.
Don’t fall for missing DatabaseMail.exe.config threads as that is a red herring. 2¢, J++
Tools:
Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard (will not install from a exe).
Detailz …
DatabaseMail.exe (in the instance Binn directory) which is invoked by the Service Broker which is part of the Core database engine since 2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency. Why it will not work with a more recent version of .Net is a mystery, perhaps they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).
Essentially there was no error information available, mail items would just sit in the queue.
Starting the exe itself on the server or invoking dbmail via a sql interface puts this in the application log of the server: “The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following: 'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 14001.'” ~ which, after a lot of time with The Google, led to the answer.
Don’t fall for missing DatabaseMail.exe.config threads as that is a red herring. 2¢, J++
Tools:
This was painful enough to figure out that I figured I would
share the information with the entire group.
Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard
(will not install from a exe).
Detailz …
DatabaseMail.exe (in the instance Binn directory) which is
invoked by the Service Broker which is part of the Core database engine since
2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency.
Why it will not work with a more recent version of .Net is a mystery, perhaps
they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).
Essentially there was no error information available, mail
items would just sit in the queue.
Starting the exe itself on the server or invoking dbmail via
a sql interface puts this in the application log of the server:
“The activated proc
'[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue'
output the following: 'Could not create DatabaseMail.exe process.
Executing API 'CreateProcess' failed with error number 14001.'”
~
Which, after a lot of time with The Google, led to the
answer. Don’t fall for missing DatabaseMail.exe.config threads as that is a red
herring.
2¢, J++
Tools:
SELECT
CASE sent_status
WHEN 0 THEN 'Unsent'
WHEN 1 THEN 'Sent'
WHEN 2 THEN 'Failed'
WHEN 3 THEN 'Retrying'
END AS sent_status_desc
FROM msdb..sysmail_mailitems
SELECT *
FROM msdb..sysmail_event_log
ORDER BY log_id DESC
SELECT @@VERSION
SELECT sent_status, send_request_date a, *
FROM msdb.dbo.sysmail_mailitems
ORDER BY a DESC
SELECT *
FROM msdb.dbo.sysmail_sentitems
USE msdb
SELECT sent_status, *
FROM sysmail_allitems
SELECT is_broker_enabled
FROM sys.databases
WHERE [name] = 'msdb';
EXECUTE msdb.dbo.sysmail_help_status_sp
-- sp_configure
'Database Mail XPs'
SELECT *
FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC
-- select @@VERSION
SELECT *
FROM Msdb.dbo.sysmail_profile
SELECT *
FROM Msdb.dbo.sysmail_event_log order by log_date desc
SELECT *
FROM Msdb.dbo.sysmail_faileditems
EXEC Msdb.dbo.sysmail_help_queue_sp
Subscribe to:
Posts (Atom)
NO Payment Required!
Here are 10 FREE Microsoft courses to become skilled in 2023: ⤵️
[Bookmark for future reference 🔖]
1️⃣ Introduction to Power BI
• Import and transform data
• Create interactive visualizations
• Build dashboards and reports
🔗 https://lnkd.in/eUTx-SsE
2️⃣ Introduction to Azure Fundamentals
• Core Azure services and concepts
• Cloud security and privacy
• Azure pricing and support options
🔗 https://lnkd.in/eqrjxqpM
3️⃣ Introduction to Azure AI
• Computer vision services
• Natural language processing
• Azure Machine Learning options
🔗 https://lnkd.in/exKfhkzS
4️⃣ Introduction to Machine Learning
• Regression models
• Classification algorithms
• Clustering techniques
🔗 https://lnkd.in/eeCNUsnS
5️⃣ Introduction to Data Science with Python
• Data ingestion and wrangling
• Exploratory data analysis
• Data visualization and ML
🔗 https://lnkd.in/eEfkDvsV
6️⃣ Introduction to NoSQL databases using Azure Cosmos DB
• Overview of NoSQL databases
• Globally distributed database
• Multi-model data API
🔗 https://lnkd.in/eujbTuHC
7️⃣ Introduction to Azure Serverless Computing
• Azure Functions
• Logic Apps
• Event Grid
🔗 https://lnkd.in/ePhknqjU
8️⃣ Secure cloud solutions on Azure
• Identity and access controls
• Encryption methods
• Compliance tools
🔗 https://lnkd.in/eQysTP9a
9️⃣ Python for beginners
• Core programming concepts
• Hands-on projects
• Real-world applications
🔗 https://lnkd.in/epfNbFFU
🔟 Introduction to C#
• C# basics and syntax
• .NET framework
• Building applications
🔗 https://lnkd.in/efi5PpjQ
——————————————
If you like this post why not subscribe to the fastest growing newsletter about AI, Digital Transformation, Future Trends - from Leaders for Leaders
https://lnkd.in/erQ7xp-8
And if you want to Master ChatGPT and get the bestseller books, why not check this out:
https://lnkd.in/ewzEYZMU
——————————————
Follow #drstorm press “like” and the 🔔 on my profile and/or share with your network. #technology #digital #future #ChatGPT #GenerativeAI #AI