Forum Widgets
Latest Discussions
Issue with importing json file
I am using the following script to read data from a json file and import into SQL server. Data import works without any issue. However, I noticed that when the json file contains special characters (Ex: è) is replaces it with è I already saved the json file with UTF-8. Is there anyway to import the actual character? drop table if exists #Test3 CREATE TABLE #Test3 ( EMP_COMMON_FULL_NAME NVARCHAR(500), EMP_COMMON_PRIMARY_JOB NVARCHAR(500), PEOPLE_EMAIL NVARCHAR(500), EMP_COMMON_PRIMARY_ORG NVARCHAR(500), PEOPLE_BADGE_NUMBER NVARCHAR(500), PEOPLE_EXPECTED_WEEKLY_HOURS NVARCHAR(500), PEOPLE_EXPECTED_DAILY_HOURS NVARCHAR(500) ); DECLARE @json NVARCHAR(MAX); SELECT @json = BulkColumn FROM OPENROWSET(BULK 'F:\PowerBI\json\file2.json', SINGLE_CLOB, CODEPAGE = '65001') AS jsonFile; INSERT INTO #Test3 ( EMP_COMMON_FULL_NAME, EMP_COMMON_PRIMARY_JOB, PEOPLE_EMAIL, EMP_COMMON_PRIMARY_ORG, PEOPLE_BADGE_NUMBER, PEOPLE_EXPECTED_WEEKLY_HOURS, PEOPLE_EXPECTED_DAILY_HOURS ) SELECT JSONData.EMP_COMMON_FULL_NAME, JSONData.EMP_COMMON_PRIMARY_JOB, JSONData.PEOPLE_EMAIL, JSONData.EMP_COMMON_PRIMARY_ORG, JSONData.PEOPLE_BADGE_NUMBER, JSONData.PEOPLE_EXPECTED_WEEKLY_HOURS, JSONData.PEOPLE_EXPECTED_DAILY_HOURS FROM OPENJSON(@json, '$.data.children') WITH ( EMP_COMMON_FULL_NAME NVARCHAR(500) '$.attributes[0].value', EMP_COMMON_PRIMARY_JOB NVARCHAR(500) '$.attributes[1].value', PEOPLE_EMAIL NVARCHAR(500) '$.attributes[2].value', EMP_COMMON_PRIMARY_ORG NVARCHAR(500) '$.attributes[3].value', PEOPLE_BADGE_NUMBER NVARCHAR(500) '$.attributes[4].value', PEOPLE_EXPECTED_WEEKLY_HOURS NVARCHAR(500) '$.attributes[5].value', PEOPLE_EXPECTED_DAILY_HOURS NVARCHAR(500) '$.attributes[6].value' ) AS JSONData; SELECT * FROM #Test3; { "metadata": { "numNodes": "500", "metadataKey": "fewfewf-sdvrv-evfewsv", "cacheKey": "fewfewf-sdvrv-evfewsv", "currencyCode": "NONE", "totalNodes": "500", "totalElements": "500" }, "data": { "key": { "ROOT": "-1" }, "coreEntityKey": {}, "attributes": [], "children": [ { "key": { "PEOPLE": "67648" }, "coreEntityKey": { "EMP": { "id": "11111" } }, "attributes": [ { "key": "EMP_COMMON_FULL_NAME", "alias": "Name", "rawValue": "Sam, Rogers", "value": "Sam, Rogers" }, { "key": "EMP_COMMON_PRIMARY_JOB", "alias": "Primary Job", "rawValue": "Accountant", "value": "Accountant" }, { "key": "PEOPLE_EMAIL", "alias": "Email Address", "rawValue": "email address removed for privacy reasons", "value": "email address removed for privacy reasons" }, { "key": "EMP_COMMON_PRIMARY_ORG", "alias": "Location", "rawValue": "1ère Inc/1ère Inc", "value": "1ère Inc/1ère Inc" }, { "key": "PEOPLE_BADGE_NUMBER", "alias": "Active Badge Number", "rawValue": "1234", "value": "1234" }, { "key": "PEOPLE_EXPECTED_WEEKLY_HOURS", "alias": "Weekly Hours", "rawValue": "35.75", "value": "35.75" }, { "key": "PEOPLE_EXPECTED_DAILY_HOURS", "alias": "Daily Hours", "rawValue": "7.8", "value": "7.80" } ], "children": [], "summaryListDisplay": [], "rootEntity": "PEOPLE", "customProperties": {} }, { "key": { "PEOPLE": "22222" }, "coreEntityKey": { "EMP": { "id": "22222" } }, "attributes": [ { "key": "EMP_COMMON_FULL_NAME", "alias": "Name", "rawValue": "F3irst1, Last1", "value": "F3irst1, Last1" }, { "key": "EMP_COMMON_PRIMARY_JOB", "alias": "Primary Job", "rawValue": "Cl3erk", "value": "Cl3erk" }, { "key": "PEOPLE_EMAIL", "alias": "Email Address", "rawValue": "email address removed for privacy reasons", "value": "email address removed for privacy reasons" }, { "key": "EMP_COMMON_PRIMARY_ORG", "alias": "Location", "rawValue": "1ère 3Inc/1ère Inc", "value": "1ère 3Inc/1ère Inc" }, { "key": "PEOPLE_BADGE_NUMBER", "alias": "Active Badge Number", "rawValue": "23222", "value": "23222" }, { "key": "PEOPLE_EXPECTED_WEEKLY_HOURS", "alias": "Weekly Hours", "rawValue": "30.0", "value": "30.00" }, { "key": "PEOPLE_EXPECTED_DAILY_HOURS", "alias": "Daily Hours", "rawValue": "30.0", "value": "30.00" } ], "children": [], "summaryListDisplay": [], "rootEntity": "PEOPLE", "customProperties": {} } ], "summaryListDisplay": [], "rootEntity": "ROOT", "customProperties": {} } }dp3450Nov 30, 2024Copper Contributor26Views0likes1CommentHelp in Query to calculate the % from last two timelines
I want to compare the FAmt from last recent two lines and if difference is more than 80% then it should be in the result. The group/index keys are A,B,C,D, Edate. Create table #AR (A int, B char(10), C char(10), D char(10), Edate datetime, FAmt decimal (6,2), G char(2)) Insert into #AR values ('AA','CCC','1','E', '2022-01-01',12.60,'2') Insert into #AR values ('AA','CCC','1','E', '2023-01-01',15.80,'2') Insert into #AR values ('AA','CCC','1','E', '2024-01-01',18.60,'2') Insert into #AR values ('BB','DCC','1','E', '2022-01-01',11.40,'2') Insert into #AR values ('BB','DCC','1','E', '2024-01-01',15.60,'2') Insert into #AR values ('CC','DCC','1','E', '2021-01-01',12.60,'2') Insert into #AR values ('CC','GCC','2','E', '2022-01-01',15.60,'2') Insert into #AR values ('CC','GCC','2','E', '2023-04-01',18.60,'2') Insert into #AR values ('CC','GCC','2','E', '2024-04-01',34.80,'2') --Note: This FAmt is more than 80% from last timeline and it should be in the expected result Expected result: A B C D Edate FAmt G Comments CC GCC 2 E 2024-04-01 34.80 2 Current Amount is > 80% from last timelineKenny_GuaNov 30, 2024Copper Contributor22Views0likes1CommentUpgrading from 2012 (SP4) to 2019 including SSRS
Hi, I have done a fair bit of googling/reading and found conflicting paths/advice so am looking for a definitive guide. Step by step would be ideal if possible. One of our servers is currently running SQL 2012 and we need to upgrade to 2019. The instance also includes SSRS and the report server is on the same server. With approx. 100+ reports in use. I've read that SSRS does not upgrade when you upgrade the SQL version as it is now a stand alone program (true?) So basically I need to upgrade (in place) SQL Server (easy) but then do what ever I need to do to get SSRS to 2019 and obviously have the report definitions (RDLs) recognise their new environment and upgrade themselves in the process. I imagine the schema path at the top of the RDL would need changing at the very least. We also have scheduled subscriptions that we need to retain.... and of course datasources that point to the relevant DBs (not sure if these would be affected as part of any upgrade/install of SSRS). Would be grateful for any legitimate sources that deal with this scenario and I thank you in advance.JayworkNov 27, 2024Copper Contributor3Views0likes0CommentsNested Query Slowing Performance
Hi, At the beginning of my SQL code I have a cte target table called TARGETS, which contains 3 columns TARGET_NAME, MODE AND VALUE. Further down in my query I am trying to use a nested query to retrieve values from this table, however, it is really slowing down the performance. I am wondering if there is a better way to do this? An example of the nested query looks like this SUM(CASE WHEN LEG = 'Empty' AND LOCATION = 'Perth' THEN (SELECT VALUE FROM TARGETS WHERE TARGET_NAME = 'Empty Perth' AND MODE = 'Yes') WHEN LEG = 'Empty' AND LOCATION= 'Melbourne' THEN (SELECT VALUE FROM TARGETS WHERE TARGET_NAME= 'Empty Melbourne' AND MODE = 'No') etc... END) / COUNT(DISTINCT(LEG)) AS Target FROM LEG_DETAILS Many Thanks,leerjones85Nov 27, 2024Copper Contributor24Views0likes3CommentsHelp with SQL Code
Hi, I have a table called FACT_DELAYS which contains the following columns: ID, START_DATE, END_DATE, FILTERED_MINUTES If an event is still active then END_DATE IS NULL. Example Data: 1, 20/10/24, null, 25 2, 07/11/24, null, 67 3, 02/08/23, 10/11/24, 43 4, 01/01/22, null, 20 The problem I have got is that it gives me an as of now value when I run the sql. I want to be able to dynamically show the previous 7 days and if a delay covers multiple days then it shows up in each day. My desired output would be: DATE, COUNT(FILTERED_MINUTES) 11/11/24, 3 10/11/24, 4 09/11/24, 4 08/11/24, 4 07/11/24, 4 06/11/24, 3 05/11/24, 3 Any Help will be much appreciated! ThanksSolvedleerjones85Nov 26, 2024Copper Contributor60Views0likes4Commentsone column refer to 2 columns
Hello there global table is dbo.Employee(EmployeeId int, sex char(10)) Another global table is dbo.GradeEmployee(PolicyEmployeeId int (FK of Employee-EmployeeId), FaultEmployeeId int (FK of Employee-EmployeeId), grade char(5) Another is my temp table called #TmpEmployee(EmployeeId int, employeeName char(50), quantity) GradeEmployee- PolicyEmployeeId is derived from Employee-EmployeeId GradeEmployee- FaultEmployeeId is also derived from Employee-EmployeeId Now, the task is I want to get the data from #TmpEmployee table, there I want to Display in the below format PolicyEmployeeId, FaultEmployeeId, employeeNameSum(quantity) Sum(quantity) is total quantity of a pair FaultEmployeeId, PolicyEmployeeId, note in #TmpEmployee table sometime the Quantity of FaultEmployeeId wont present; sometime the Quantity of PolicyEmployeeId wont present but, in the result it has to display. How to write the select query?JohnMathewNov 26, 2024Copper Contributor26Views0likes1CommentSQL Help : Time difference for events within a specific group that resets based on an anchor event
Hi All, Need help with an SQL query to find the time difference between events that happen in sequence, where an anchor event resets the calculation. Below is the sample data and the expected output. thanksSolvedsukiNov 25, 2024Copper Contributor73Views0likes5CommentsData masking in SQL server 2019
We tried to mask a column in SQLDB I first backup the table tblContacts to tblContacts_backup then I issue following SQL command to mask the column "Email" ALTER TABLE tblContacts ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'default()'); Then I check the result but not thing happen (no mask applied to the column) compared tblContacts and tblContacts_backup but no different SELECT Email FROM [ABC].[dbo].[tblContacts] where Emailis not null SELECT Email FROM [ABC].[dbo].[tblContacts_backup] where Emailis not null I have done following step but the problem still not solve make sure the user is not db admin role REVOKE UNMASK TO the user disconnect and connect again checked configure have been applied to the column select c.name AS column_name, t.name AS data_type, m.is_masked, m.masking_function FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id LEFT JOIN sys.masked_columns m ON c.object_id = m.object_id AND c.column_id = m.column_id WHERE t.name = 'tblContacts_backup' found is_masked = 1 and m.masking_function = default() What else can I check or try?johnyhhNov 25, 2024Copper Contributor39Views0likes1CommentCannot truncate logs full when transaction cannot rollback
Hello, I recently experienced the problem when my database has a full transaction log but I can't truncate it via SSMS. I had to pass commands manually in T-SQL, but since the log was full, a query in progress was killed and proceeded to a rollback. However, the rollback was blocked at 0% because the logs were full, and the log files were locked because a transaction was in progress… Is there any other way of getting out of this situation without restarting the instance? (I've tried a backup + log shrink, adding log files, changing the recovery model to Simple, etc. But nothing works)QueryRunsFastNov 21, 2024Copper Contributor31Views0likes2CommentsError when importing CSV file
Hi, I am new at MS SQL and have the community edition in my local machine. Trying to import a CSV file but getting the following error message: Total Defect Qty:=SUM([Defect Qty]) TITLE: Microsoft SQL Server Management Studio ------------------------------ Error inserting data into table. (Microsoft.SqlServer.Import.Wizard) ------------------------------ ADDITIONAL INFORMATION: Error inserting data into table. (Microsoft.SqlServer.Prose.Import) ------------------------------ The given value '[name of the column]' of type String from the data source cannot be converted to type nvarchar for Column 4 [Label]. (Microsoft.Data.SqlClient) ------------------------------ String or binary data would be truncated in table '[dbo].[name of the file]', column 'Label'. Truncated value: '[name of the column '. (Microsoft.Data.SqlClient) ------------------------------ BUTTONS: OK ------------------------------ Do you think you can help me please? Thanks, NaveenSolvednaveen73Nov 20, 2024Copper Contributor59Views0likes5Comments
Resources
Tags
- Data Warehouse64 Topics
- Integration Services58 Topics
- sql server42 Topics
- Reporting Services41 Topics
- SQL35 Topics
- Business Intelligence35 Topics
- Analysis Services29 Topics
- Business Apps22 Topics
- analytics18 Topics
- Big Data10 Topics