This is my technical area for troubleshooting and learning new programming skills and much more. Here, you will find answers on a wide range of technologies such as AI, Machine Learning, OpenAI, Databricks, ASP.NET, C#, Python, Microsoft Access, MySQL, Amazon Web Services, SQL Server, PL/SQL, JD Edwards, SAS, Salesforce, APIs, MVC, and many others. Please feel free to visit and join the discussion!
Wednesday, December 21, 2022
Tuesday, December 20, 2022
Wednesday, September 21, 2022
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 :
Tuesday, September 20, 2022
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.
Tuesday, September 13, 2022
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
Thursday, August 18, 2022
GRANT Permission SQL Server
GRANT EXECUTE, SELECT, UPDATE, INSERT, DELETE ON SCHEMA::ABC_EXT TO [MMC\JDEDEV]
Friday, July 29, 2022
Convert From date and to date in Julian , JD Edwards in VBA
Dim FrmDateImp
Dim ToDateImp
Dim tmpToDateImp
FrmDateImp =
Right(STR(PERIOD_BEG), 2) & "/01/" &
Mid(str(PERIOD_BEG), 2, 4)
tmpToDateImp = Right(STR(PERIOD_END),
2) & "/01/" & Mid(str(PERIOD_END), 2, 4)
ToDateImp =
DateSerial(YEAR(tmpToDateImp), Month(tmpToDateImp) + 1, 0)
Dim FrmDate_DGJ As Long
Dim ToDate_DGJ As Long
FrmDate_DGJ = ((DatePart("YYYY", FrmDateImp) - 1900) * 1000) + DatePart("y", FrmDateImp)
ToDate_DGJ = ((DatePart("YYYY", ToDateImp) - 1900)
* 1000) + DatePart("y", ToDateImp)
QryDateSummaryStr = "GLDGJ BETWEEN
'" & FrmDate_DGJ & "' and '" & ToDate_DGJ &
"'"
Wednesday, July 27, 2022
Change Date Format Excel Formula
Change date format excel formula, this is good for developer too, who wants to create SQL statement from excel cell.
=TEXT(B3,"mm/dd/yyyy")
=TEXT(B3,"mm-dd-yyyy")
Tuesday, July 26, 2022
Dynamic SQL table columns for dates in Aging report, SQL Server
How to create Dynamic SQL table columns for dates in Aging report JD Edwards, SQL Server
DECLARE @SQL_STR VARCHAR(8000)
DECLARE @DueDate VARCHAR(80)
DECLARE @DueDate14 VARCHAR(80)
DECLARE @DueDate28 VARCHAR(80)
DECLARE @DueDateAfter28 VARCHAR(80)
SET @DueDate = '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+1, 101)+'],'
SET @DueDate14 = '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+14, 101)+'],'
SET @DueDate28 = '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'
SET @DueDateAfter28 = '['+'Due After ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'
SET @SQL_STR =
'SELECT RPAN8 AS Supplier_Number, sup.ABALPH AS Supplier_name,
RPPST AS Pay_Stat, RPPYIN AS Pay_Inst, RPAN8 AS Payee_Number, PR.ABALPH AS Payee_Name,
RPDCT AS Doc_Type, RPDOC AS Doc_Number, RPSFX AS ITM, RPKCO AS CO, RPVINV AS Invoice_Number,
[dbo].[JDT_JTG](RPDIVJ) AS Invoice_Date,
[dbo].[JDT_JTG](RPDDJ) AS Due_Date,
CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE(), 101) and CONVERT(VARCHAR(10), GETDATE()+1, 101)
THEN RPAG/100
ELSE 0 END '+ @DueDate + '
CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+2, 101) and CONVERT(VARCHAR(10), GETDATE()+14, 101)
THEN RPAG/100
ELSE 0 END '+ @DueDate14 + '
CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+15, 101) and CONVERT(VARCHAR(10), GETDATE()+28, 101)
THEN RPAG/100
ELSE 0 END '+ @DueDate28 + '
CASE WHEN [dbo].[JDT_JTG](RPDDJ) > CONVERT(VARCHAR(10), GETDATE()+28, 101)
THEN RPAG/100
ELSE 0 END AS '+ @DueDateAfter28 + '
CASE WHEN [dbo].[JDT_JTG](RPDDJ) >= CONVERT(VARCHAR(10), GETDATE(), 101)
THEN RPAG/100
ELSE 0 END AS Total_Due
FROM MFDB.PRODDTA.F0411
LEFT OUTER JOIN MFDB.PRODDTA.F0101 Sup ON Sup.ABAN8 = RPAN8
LEFT OUTER JOIN MFDB.PRODDTA.F0101 PR ON PR.ABAN8 = RPAN8
WHERE RPFY = 22
--AND rpan8 = 642564
AND RPPST = ''A'';
'
--PRINT (@SQL_STR)
EXEC (@SQL_STR)
Saturday, July 23, 2022
Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)
Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)
Dim myDate As Date
myDate = "07/23/2022"
Debug.Print (((DatePart("YYYY", myDate)) - 1900) * 1000) + DatePart("y", myDate)
Wednesday, June 29, 2022
find a table column in stored procedure in SQL Server
SELECT distinct OBJECT_NAME (id)
FROM syscomments
WHERE text LIKE '%GLMCU%'
GO
Tuesday, June 14, 2022
DROP INDEX SQL Server
DROP INDEX SQL Server if exists
DROP INDEX IF EXISTS IDX_tbl_XXXData_RR ON tbl_XXXData_RR;
Monday, June 13, 2022
How to load Sql queries in SQL Server table
Sometimes we need to load SQL code in a sql table and run that code in any application. you can load long SQL code using following:-
1. make your query in one line.
2.Open an excel and add a column name in excel, paste that query in a excel cell.
3. Load this excel in SQL server using sql import wizard.
4. Create an insert statement to load in your main table with all the fields.
INSERT INTO table_sql(sqlname,sqldesc,date,user,sqlcode)
Select sqlname,sqldesc,date,user, Column01 sqlcode from YourUpoadedTable
Wednesday, May 25, 2022
Schema LEVEL SECURITY ,SQL Server
' PRINT @strHTML -----------------Login information------------------------------------------------------------- SELECT ROW_NUMBER () OVER (ORDER BY name) AS RowNumber, name, dbname,language, CONVERT(CHAR(10),CASE denylogin WHEN 1 THEN 'X' ELSE '--' END) AS IsDenied, CONVERT(CHAR(10),CASE isntname WHEN 1 THEN 'X' ELSE '--' END) AS IsWinAuTHENtication, CONVERT(CHAR(10),CASE isntgroup WHEN 1 THEN 'X' ELSE '--' END) AS IsWinGroup, createdate,UPDATEdate, CONVERT(VARCHAR(2000), CASE sysadmin WHEN 1 THEN 'sysadmin,' ELSE '' END + CASE securityadmin WHEN 1 THEN 'securityadmin,' ELSE '' END + CASE serveradmin WHEN 1 THEN 'serveradmin,' ELSE '' END + CASE setupadmin WHEN 1 THEN 'setupadmin,' ELSE '' END + CASE processadmin WHEN 1 THEN 'processadmin,' ELSE '' END + CASE diskadmin WHEN 1 THEN 'diskadmin,' ELSE '' END + CASE dbcreator WHEN 1 THEN 'dbcreator,' ELSE '' END + CASE bulkadmin WHEN 1 THEN 'bulkadmin' ELSE '' END ) AS ServerRoles INTO #syslogins FROM master..syslogins WITH (nolock) ORDER BY name SET @rc = @@rowcount SELECT @strHTML = '
' PRINT @strHTML PRINT '
| Logins information | ||||||||
| There are no logins on this server | ||||||||
| Logins information | ||||||||
| Login Name | Default DB | Language | Denied acess? | Windows Auth? | Window group? | Date created | Date UPDATEd | Server roles |
| ' + CONVERT(VARCHAR(50),name) + ' | ' + '' + CONVERT(VARCHAR(50),CASE ISNULL(dbname,'--') WHEN '' THEN '--' ELSE ISNULL(dbname,'--') END) + ' | ' + '' + CONVERT(VARCHAR(50),ISNULL(language,'--')) + ' | ' + '' + CONVERT(VARCHAR(10),ISNULL(IsDenied,'--')) + ' | ' + '' + CONVERT(VARCHAR(10),ISNULL(IsWinAuTHENtication,'--')) + ' | ' + '' + CONVERT(VARCHAR(10),ISNULL(IsWinGroup,'--')) + ' | ' + '' + CONVERT(VARCHAR(30),ISNULL(createdate,'--')) + ' | ' + '' + CONVERT(VARCHAR(30),ISNULL(UPDATEdate,'--')) + ' | ' + '' + CONVERT(VARCHAR(100),ISNULL(ServerRoles,'--')) + ' | ' + '
Back To Top ^
' PRINT'
' END DROP TABLE #syslogins ---------------Fetch data per database------------------------------------------------- CREATE TABLE #LoginMap (LoginName VARCHAR(200), UserName VARCHAR(200) NULL) CREATE TABLE #RoleUser (RoLEName VARCHAR(200), UserName VARCHAR(200) NULL) CREATE TABLE #ObjectPerms (RowNumber INT IDENTITY, UserName VARCHAR(50), PerType VARCHAR(10),PermName VARCHAR(30), SchemaName VARCHAR(50), ObjectName VARCHAR(100), ObjectType VARCHAR(20), ColName VARCHAR(50), IsGrantOption VARCHAR(10)) CREATE TABLE #DatabasePerms (RowNumber INT IDENTITY,UserName VARCHAR(50),PermType VARCHAR(20),PermName VARCHAR(50),IsGrantOption VARCHAR(5)) DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY name OPEN dbs FETCH NEXT FROM dbs INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN TRUNCATE TABLE #LoginMap TRUNCATE TABLE #RoleUser TRUNCATE TABLE #ObjectPerms TRUNCATE TABLE #DatabasePerms SELECT @strHTML = '
' PRINT @strHTML -----------------Mapping of logins to users------------------ EXEC(' INSERT INTO #LoginMap SELECT login.loginname,users.name FROM ['+ @dbname+'].dbo.sysusers users INNER JOIN [master].[dbo].[syslogins] login ON users.[sid] = login.[sid] WHERE users.uid < 16382 and users.name not in (''public'',''dbo'',''guest'') ') SET @strHTML = '' PRINT '
| Mapping of logins to users | |
| There are no mappings in this database | |
| Mapping of logins to users | |
| Login Name | User Name |
| ' + CONVERT(VARCHAR(50),LoginName) + ' | ' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + ' |
Back To Top ^
' ----------------SQL roles per user------------------ EXEC ('INSERT INTO #RoleUser SELECT b.name AS Role_name, a.name AS User_name ' + 'FROM ['+ @dbname+']..sysusers a ' + 'INNER JOIN ['+ @dbname+ ']..sysmembers c on a.uid = c.memberuid ' + 'INNER JOIN ['+ @dbname+ ']..sysusers b ON c.groupuid = b.uid ' + 'WHERE a.name <> ''dbo''' ) SET @strHTML = '' PRINT '
| Roles per user | |
| There are no users mapped to roles in this database | |
| Roles per user | |
| Role Name | User Name |
| ' + CONVERT(VARCHAR(50),RoLEName) + ' | ' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + ' |
Back To Top ^
' ----------------Database level Permissions------------------------- EXEC ('INSERT INTO #DatabasePerms (UserName,PermType,PermName,IsGrantOption) SELECT usr.name, CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END, perm.permission_name, CASE WHEN perm.state != ''W'' THEN ''--'' ELSE ''X'' END AS IsGrantOption FROM ['+@dbname+'].sys.database_permissions AS perm INNER JOIN ['+@dbname+'].sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE perm.major_id = 0 ORDER BY usr.name, perm.permission_name ASC, perm.state_desc ASC' ) SET @rc = @@rowcount PRINT '
| Database level permissions | |||
| There are no specific permissions on the database level | |||
| Database level permissions | |||
| User Name | Permission type | Permission Name | Grant option? |
| ' + CONVERT(VARCHAR(50),UserName) + ' | ' + '' + CONVERT(VARCHAR(50),ISNULL(PermType,'--')) + ' | ' + '' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + ' | ' + '' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + ' | '+ '
Back To Top ^
' ----------------Database object Permissions------------------------- EXEC ('INSERT INTO #ObjectPerms (UserName,PerType,PermName,SchemaName,ObjectName,ObjectType,ColName,IsGrantOption) SELECT usr.name AS UserName, CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END AS PerType, perm.permission_name,USER_NAME(obj.schema_id) AS SchemaName, obj.name AS ObjectName, CASE obj.Type WHEN ''U'' THEN ''Table'' WHEN ''V'' THEN ''View'' WHEN ''P'' THEN ''Stored Proc'' WHEN ''FN'' THEN ''Function'' ELSE obj.Type END AS ObjectType, CASE WHEN cl.column_id IS NULL THEN ''--'' ELSE cl.name END AS ColName, CASE WHEN perm.state = ''W'' THEN ''X'' ELSE ''--'' END AS IsGrantOption FROM ['+@dbname+'].sys.database_permissions AS perm INNER JOIN ['+@dbname+'].sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN ['+@dbname+'].sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN ['+@dbname+'].sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id WHERE obj.Type <> ''S'' ORDER BY usr.name, perm.state_desc ASC, perm.permission_name ASC' ) SET @rc = @@rowcount PRINT '
| Object permissions | |||||||
| There are no specific permissions to objects in this database | |||||||
| Object permissions | |||||||
| User Name | Permission type | Permission Name | Schema Name | Object Name | Object type type | Column Name | Grant option? |
| ' + CONVERT(VARCHAR(50),UserName) + ' | ' + '' + CONVERT(VARCHAR(50),ISNULL(PerType,'--')) + ' | ' + '' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + ' | ' + '' + CONVERT(VARCHAR(50),ISNULL(SchemaName,'--')) + ' | ' + '' + CONVERT(VARCHAR(50),ISNULL(ObjectName,'--')) + ' | ' + '' + CONVERT(VARCHAR(30),ISNULL(ObjectType,'--')) + ' | ' + '' + CONVERT(VARCHAR(50),ISNULL(ColName,'--')) + ' | ' + '' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + ' |
Back To Top ^
' FETCH NEXT FROM dbs INTO @dbname END ---------------Close cursor and drop all temporary objects------------- CLOSE dbs DEALLOCATE dbs DROP TABLE #LoginMap DROP TABLE #RoleUser DROP TABLE #ObjectPerms DROP TABLE #DatabasePerms PRINT '' GO https://www.mssqltips.com/sqlservertip/1818/script-to-auto-generate-a-security-report-for-your-sql-server-instance/