Wednesday, September 6, 2023

Microsoft is offering free online courses with certification.

 Microsoft is offering free online courses with certification.


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

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;
 

Set to read only

 

-- ALTER DATABASE [MyDB-DEV-NEW] SET READ_ONLY;

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 is the spid doing?

 -- What is the spid doing? 

 

       DBCC INPUTBUFFER (86)

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

Find tables with column named ...

 EXEC Utility.dbo.findTWithC 'MyDB-tmar', 'product_code'

-- 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'

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 …

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:


This was painful enough to figure out that I figured I would share the information with the entire group.



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