,DATEPART(d,dl.FullDate) as DayOfMonthNumber ,DATENAME(weekday,dl.FullDate) as DayOfWeekName ,DATEPART(dw,dl.FullDate) as DayOfWeekNumber SELECT CONVERT(INT,CONVERT(VARCHAR,dl.FullDate,112)) as DateKey SET DATEFIRST 7 - Set the first day of the week to Monday WHERE dt_cte.FullDate < INTO FullDate FROM dt_cte SELECT DATEADD(DAY,1,FullDate) AS FullDate Include more dates in Date dimension, the existing dates are not being replacedĭECLARE DATE = '' -change start date if DATE = -change end date if required ĭeclare int = - Delete leap year records (February 29)ĭelete from FactCurrencyRate where month() = 2 and day() = 29ĭelete from FactProductInventory where month() = 2 and day() = 29Īlter table FactCurrencyRate drop constraint FK_FactCurrencyRate_DimDateĪlter table FactFinance drop constraint FK_FactFinance_DimDateĪlter table FactInternetSales drop constraint FK_FactInternetSales_DimDateĪlter table FactInternetSales drop constraint FK_FactInternetSales_DimDate1Īlter table FactInternetSales drop constraint FK_FactInternetSales_DimDate2Īlter table FactProductInventory drop constraint FK_FactProductInventory_DimDateĪlter table FactResellerSales drop constraint FK_FactResellerSales_DimDateĪlter table FactSurveyResponse drop constraint FK_FactSurveyResponse_DateKey For example: if the current year is 2021, the data after running the script will be from 2017 to 2021.ĭeclare date = DATEADD (dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) +1, 0))
#Adventureworkslt 2014 script update#
AdventureWorksDW original database contains data from 2010 to 2014, ths script will update the data to be (current year - 4 yars) to current year It uses the current year as the last year for the data in the Adventure Works database. The script updates the date colums for the AdventureWorksDW database with recent dates and it inserts new dates in the date dimension. You don’t want to have to explain why the data is old! However, the data is from 2014, making it difficult to showcase demos for stakeholders. The Microsoft AdventureWorks databases are excellent training resources. Why would you want to update your AdventureWorks Database?
#Adventureworkslt 2014 script plus#
The server I log onto is the local one, so the Server name is the same as my User name (through Windows Authentication), plus "\SQLEXPRESS". But as I understand, I am an administrator. Running an SQL script (by writing the script after clicking 'New Query'), EXEC sp_attach_db = (2), I get the error: Msg 262, Level 14, State 1, Line 1ĬREATE DATABASE permission denied in database 'master'.īased on some websites (like this), the error is caused when there's a Permissions issue. I do the same process as above, and get the same error as above. Running SSMS as administrator (right clicking on program, and 'Run as Administrator'.).I have tried their solutions to no avail, including: I realise that there are many questions similar to the one I will ask below.
So I've looked up other ways to add this database. Parameter name: nColIndex Actual value was -1.
which immediately gets me the error: Cannot show requested dialog. Initially I have tried: Right clicking on Databases, 'attach'.
As a new person to this area, or noob, this seemingly simple task has caused me much hardship and grief. I am trying to add the AdventureWorks database in MS SQL Server Management Studio (SSMS) 2008 R2.
0 kommentar(er)
