Loading stored procedure results into tables

There are multiple methods that can be used to import stored procedures results into tables.

OPENROWSET AND OPENQUERY methods require that stored procedures don’t have;

  1. temp tables inside
  2. Don’t return duplicate columns

There is no need for a linked server, but you would need to get the connection string right. You need to specify the full path to the stored procedure including the database name and the stored procedure owner.

METHOD–1 Using OPENROWSET

This is one time step to configure database server.

--one time step
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

You can run your stored procedures that will load data into tables;

IF OBJECT_ID('tempdb..#STG_OPENROWSET') IS NOT NULL DROP TABLE #STG_OPENROWSET
SELECT * INTO #STG_OPENROWSET FROM OPENROWSET('SQLNCLI', 
'Server=(local);Trusted_Connection=yes;',
'EXEC DatabaseName.[dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020')
SELECT * FROM #STG_OPENROWSET
DROP TABLE #STG_OPENROWSET

METHOD–2 Using OPENQUERY.

This is one time step to configure database server.

--one time step
Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE

You can run your stored procedures that will load data into tables;

IF OBJECT_ID('tempdb..#STG_OPENQUERY') IS NOT NULL DROP TABLE #STG_OPENQUERY
SELECT  * INTO    #STG_OPENQUERY
FROM    OPENQUERY(DatabaseServerName, 
'EXEC	Database.[dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020');
SELECT * FROM #STG_OPENQUERY
DROP TABLE #STG_OPENQUERY

If stored procedures are using temp tables or returning duplicate columns, you will get these errors using Method-1 and Method-2;

invalid object #tablename.

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET”

OPENROWSET are not allowed in Azure SQL. They are allowed in a VM that is connected to Azure.

METHOD–3  Manually create temp tables

There is no database configuration required.

You can run your stored procedures that will load data into tables;

--the proce is using temp tables so this is work around
IF OBJECT_ID('tempdb..#STG_TempTable') IS NOT NULL DROP TABLE #STG_TempTable
CREATE TABLE #STG_TempTable
(
	ID int,
	[name] nvarchar(255),
	shortName nvarchar(25),
)
INSERT #STG_TempTable 
EXEC [dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020
SELECT * FROM #STG_TempTable

By using this method, your database administrator will be happy because you are not making any security related changes at database server level.

References

https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table

Steps to perform when relocating to a different city/state

This is a brief list of steps to be performed when relocating to a different city/state.

  1. Inform your current landlord about the date you are moving out
  2. Disable/remove any rental auto payments
  3. Inform your Power provider to disconnect power
  4. Inform your Cable/Internet provider to disconnect service
  5. Inform your postal service provider about your new mailing address

More to come..

SQL Server NULL or empty value checking

This is how to check NULL or empty input values for a date column. Currently NULL or empty values produces ‘1900-01-01’ value which is not acceptable.

DECLARE @InputDate DATE

--set input to spaces
SET @InputDate = ''
--don't want 1900-01-01 output, instead NULL value
SELECT ISNULL(NULLIF(@InputDate, ''), NULL) AS InputDateSpaces

--set input to null
SET @InputDate = NULL
--don't want 1900-01-01 output, instead NULL value
SELECT ISNULL(NULLIF(@InputDate, ''), NULL) AS InputDateNULL

--set input to date
SET @InputDate = '2021-06-25'
--don't want 1900-01-01 output, instead date value
SELECT ISNULL(NULLIF(@InputDate, ''), NULL) AS InputDateRealDate

These are the results;

Select Date between Two Date Columns

This is how to run a SQL Query where you have two date columns and like to see if your date falls between those columns;

SELECT * FROM FiscalCalendarRef
WHERE 1=1
AND '2021-09-13' BETWEEN StartDate AND EndDate

If EndDate column is defined as NULL, you can change it like this;

SELECT * FROM FiscalCalendarRef
WHERE 1=1
AND '2021-09-13' BETWEEN StartDate AND COALESCE(EndDate, GETDATE())

Does Azure charge for stopped VMs?

If we stop a VM inside OS then it goes into “stopped” state. Azure will charge for the compute (hardware), network and storage services.

We can stop maximum charges for VM by using the stopped (deallocated) state. For this we would need Azure Portal (or Azure CLI) to stop the VM. This will shutdown the Operating System and deallocate the compute resource/network resource allocated for the VM. These compute/network resource can be used for another customer.  In this case Azure will report the status of the VM as being in a “Stopped (Deallocated” state.

Stopping (deallocating) a VM also release the internal IP address. A quick tip to prevent this from happening is to make sure that you have at least one VM that you haven’t put in the Stopped (Deallocated) state. You’d still be charged for that one but not the others, whilst maintaining your virtual IP.

Azure doesn’t charge for the VM core hours in Stopped (deallocated) state. However, it continues to accrue charges for the Azure storage needed for the VM’s OS disk and any attached data disks.