Error Databricks 'charindex'. This function is neither a registered temporary function nor a permanent function registered in the database 'test'.; line 6 pos 32

Error in DataBricks SQL 'charindex'. This function is neither a registered temporary function nor a permanent function registered in the database 'test'.; line 6 pos 32

Instead of this 

CHARINDEX('.', JDEAccount)

Use This 

INSTR(JDEAccount, '.')

Generate Insert SQL Script from SQL Server to DataBricks Azure

DECLARE

    @TABLENAME VARCHAR(MAX) ='GLaccountsEIMcsv',

    @FILTER_C VARCHAR(MAX)=''   -- where SLNO = 15 or some value

DECLARE @TABLE_NAME VARCHAR(MAX),

        @CSV_COLUMN VARCHAR(MAX),

        @QUOTED_DATA VARCHAR(MAX),

        @SQLTEXT VARCHAR(MAX),

        @FILTER VARCHAR(MAX) 

SET @TABLE_NAME=@TABLENAME

SELECT @FILTER=@FILTER_C

SELECT @CSV_COLUMN=STUFF

(

    (

     SELECT ',['+ NAME +']' FROM sys.all_columns 

     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 

     is_identity!=1 FOR XML PATH('')

    ),1,1,''

)

SELECT @QUOTED_DATA=STUFF

(

    (

     SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns 

     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 

     is_identity!=1 FOR XML PATH('')

    ),1,1,''

)

-- FOR SQL SERVER

--SELECT @SQLTEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER

-- FOR DATABRICKS SQL

SELECT @SQLTEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'   VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER

EXECUTE (@SQLTEXT)

SQL

https://www.mytecbits.com/microsoft/sql-server/auto-generate-insert-statements

Create Table Databricks select into a temp table

 %sql

CREATE TABLE tblBU_list_XX40_Desc_01 as

select * from tblBU_list_XX40_Desc

How to read Parquet file in DataBricks

# Databricks notebook source

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GLDATA",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("GLDATA")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GL_Details",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("GL_Details")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GLDetails_2023",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("GLDetails_2023")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/tblBU_list_MF40_Desc",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("tblBU_list_MF40_Desc")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/BudgetData",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("Budgetdata")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/Tbl_PurePeriod",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("Tbl_PurePeriod")

spark.catalog.setCurrentDatabase("test")

# COMMAND ----------

# MAGIC %sql

# MAGIC CREATE TEMPORARY VIEW VWtempGLDetails

# MAGIC     AS

# MAGIC select * from GLDetails_2023

# MAGIC union 

# MAGIC Select *  from GL_Details  --where glfy=22 and glpn=12

# COMMAND ----------

# MAGIC %sql

# MAGIC select * from VWtempGLDetails where glfy=23 and glpn=1 and gllt in('AA','AL','AN') and glco=2

# MAGIC limit 10

# COMMAND ----------

# MAGIC %sql

# MAGIC SELECT * FROM GLDATA WHERE fy=23 AND lt IN('AA','AL','AN') AND co=2 

# COMMAND ----------

# MAGIC %sql

# MAGIC select * from Tbl_PurePeriod

Set Database Catalog DataBricks Azure Data Lake

spark.catalog.setCurrentDatabase(StrDatabaseName)

Databricks create table from parquet file Azure Datalake

%sql

CREATE TABLE tblBU_list_MF40_Desc

USING parquet 

OPTIONS (path "File Location")

e.g /mnt/Dir1/Dir2/Dir3/Fin_DB_GL/tblBU_data

How to read Parquet files in Databricks

How to read parquet file in databricks and run select statement.

 

1. Write following code in cell1 and execute

df_GLDATAMFDB = spark.read.option("header",True).parquet("/mnt/FileServer/Folder1/Folder2/Folder3/",inferSchema="True")

df_GLDATAMFDB.createOrReplaceTempView("MFDB_GL")

2. After that in second cell run following:-

%sql

select  * from MFDB_GL

How to Create Next Number for Batch Processing in SQL SERVER

 

CREATE TABLE [dbo].[TBL_BatchNN](

[NN] [int] IDENTITY(1,1) NOT NULL,

[strgetdate] [datetime] NULL,

 CONSTRAINT [PK_TBL_BatchNN] PRIMARY KEY CLUSTERED 

(

[NN] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER Proc [dbo].[SP_BatchLoadNN] AS

Set nocount on

declare @NN int 

Insert into TBL_BatchNN(dateupdate) select getdate()

select @NN=max(NN) from TBL_BatchNN

delete TBL_BatchNN

SELECT  @NN

Overpunch characters vb.net Function for Money & decimal

 Private Function overpunchDec(def As String) As Decimal

        Dim Intvalint As Decimal

        Dim strval

        Dim strvalreal

        Dim strover = def.Trim().Substring((Len(def) - 1), 1)

        strvalreal = def.Trim().Substring(0, (Len(def) - 1))

        Dim strovernum As String

        'Dim strsignI As Decimal = 1 / 100

        'Dim strsignD As Decimal = -1 / 100

        Dim strsign As Decimal

        ''decimal

        Select Case strover

            Case "{"

                strovernum = "0"

                strsign = 1 / 100

            Case "A"

                strovernum = "1"

                strsign = 1 / 100

            Case "B"

                strovernum = "2"

                strsign = 1 / 100

            Case "C"

                strovernum = "3"

                strsign = 1 / 100

            Case "D"

                strovernum = "4"

                strsign = 1 / 100

            Case "E"

                strovernum = "5"

                strsign = 1 / 100

            Case "F"

                strovernum = "6"

                strsign = 1 / 100

            Case "G"

                strovernum = "7"

                strsign = 1 / 100

            Case "H"

                strovernum = "8"

                strsign = 1 / 100

            Case "I"

                strovernum = "9"

                strsign = 1 / 100

            Case "}"

                strovernum = "0"

                strsign = -1 / 100

            Case "J"

                strovernum = "1"

                strsign = -1 / 100

            Case "K"

                strovernum = "2"

                strsign = -1 / 100

            Case "L"

                strovernum = "3"

                strsign = -1 / 100

            Case "M"

                strovernum = "4"

                strsign = -1 / 100

            Case "N"

                strovernum = "5"

                strsign = -1 / 100

            Case "0"

                strovernum = "6"

                strsign = -1 / 100

            Case "P"

                strovernum = "7"

                strsign = -1 / 100

            Case "N"

                strovernum = "8"

                strsign = -1 / 100

            Case "N"

                strovernum = "9"

                strsign = -1 / 100

        End Select

        strval = strvalreal & strovernum

        Intvalint = Convert.ToInt32(strval) * strsign

        Return Intvalint

    End Function

VB.NET Read/Parse text file and Insert Data into SQL Server Table

VB.NET Read text file and Insert Data into SQL Server Table

Dim FILE_NAME As String = "c:/test/FileToUpload_20230102.013721.txt"

        Dim TextLine As String

        If System.IO.File.Exists(FILE_NAME) = True Then

            Dim objReader As New System.IO.StreamReader(FILE_NAME)

            Dim Str1

            Dim Str2

            Dim Str3

            Dim Str4

            Dim lineCount As Integer 'lines read so far in file

            Do While objReader.Peek() <> -1

                '                TextLine =   objReader.ReadLine() 

                TextLine = objReader.ReadLine()

                Str1 = TextLine.Substring(0, 2)

                If Str1 = "DE" Then

                    Str2 = TextLine.Substring(3, 1)

                    Str3 = TextLine.Substring(4, 3)

                    Str4 = TextLine.Substring(5, 10)

                    ' insert data into sql table

                    Dim query As String = "INSERT INTO  DBO.LoaddataPharmacy_Pal([RECORD TYPE],[RECORD INDICATOR],[ELIGIBLE COVERAGE CODE],[USER BENEFIT ID]) "

                    query = query & " VALUES ('" & Str1 & "' , '" & Str2 & "','" & Str3 & "','" & Str4 & "')"

                    Dim cmd As SqlCommand = New SqlCommand(query, _cn)

                    'cmd.Parameters.AddWithValue("@ROLEID", txtroleId.Text)

                    _cn.Open()

                    cmd.ExecuteNonQuery()

                    _cn.Close()

                End If

                Str1 = ""

                lineCount = lineCount + 1 'increment lineCount

           Loop

            ' Textbox1.Text = TextLine

        Else

            MessageBox.Show("File Does Not Exist")

        End If

Read & Write Parquet file using Databrick and Spark

Save Excel Without dropping leading zeros

 https://youtu.be/O_Fy308BlEg

#Excel #SQL-Server

PowerBI run SP in DirectQuery

When I try to run a Stored Procedure in PowerBI getting this error 

Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.

You may need to configure SQL Server , but after all setup , you have to do following

Create the SP, and use in OPENROWSET as following , test this is in SSMS

SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

If this is working fine create a view  as following:-

Create view [dbo].[VTest_dataTB_Delthis] as

 SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

 

use this in Power BI as following :

Ad Hoc Distributed Queries turned off as part of the security configuration

 Msg 15281, Level 16, State 1, Procedure PBIView_encrypted_Test1, Line 6 [Batch Start Line 0]

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Run Following-

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO

REBUILD indexes and update Stat SQL Server

Sometime when you load data, we are not able to get fast access using application. In order to fix this do following:- 

 ALTER INDEX ALL ON TBL_PlanBUDGET REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = 80)

Update STATISTICS TBL_PlanBUDGET