sql-server-2005,sql-server-2012,dotnetnuke,database-migration , Dotnetnuke migration from SQL 2005 to SQL 2012


Dotnetnuke migration from SQL 2005 to SQL 2012

Question:

Tag: sql-server-2005,sql-server-2012,dotnetnuke,database-migration

I'm trying to upgrade my DNN v6 from a SQL Server 2005 to a SQL Server 2012. My problem is, after modifying the web.config to match the new appSettings, my website automaticaly runs the install wizard. My IIS was running on a 2003 server and is now on a 2012 server.

Is it a necessary step to install a new instance of DNN?

Thanks for your help


Answer:

I found out a solution. I restored my original version of database and finaly I passed every first and last upgrade of every major version. It worked well then.


Related:


How to go backwards through the previous 12 months?


sql,sql-server,sql-server-2012
I need to create a report that checks through the previous 12 months only. I have this code which is working: DECLARE @date DATETIME = DATEADD(year,-1,GETDATE()) WHILE @date < GETDATE() BEGIN --do marvelous things SET @date = DATEADD(MONTH,1,@date) END ...however it brings the results back starting from June 2014 (as...

Not getting the correct SUM values in SQL Server 2012 when using a PIVOT


sql-server,sql-server-2012,pivot,distinct,aggregate-functions
I am trying to create a query that will pivot over some rows but will SUM some columns and then group them together. I've used the PIVOT function before but I am running into issues when my result set contains similar values. This is SQL Server 2012. Sample code: CREATE...

UNION ALL on 2 tables select with Cases


sql,sql-server,sql-server-2005,union-all
I'm running SQL Server 2005. I have 2 tables with the same columns but holding very different data. SELECT * FROM Table1 WHERE ItemID IN ('4','2','1') ORDER BY CASE WHEN ItemID = 4 then 1 WHEN ItemID = 2 then 2 WHEN ItemID = 1 then 3 END UNION ALL...

Dynamic Where Condition Based on Parameter


sql,sql-server-2012
I have a parameter based on which I need to include or exclude one condition in a where clause. If the CustomerType parameter is blank, then I need to include a condition CustomerType is null, otherwise I need to exclude that CustomerType is null condition. The SQL query is: IF(@CustomerType...

Combine columns from three different tables into a single column


sql,sql-server-2012
I am new to SQL and I am not sure what to Google. I have three tables with different numbers of columns. I would like to combine these following three tables into a single column(no duplicates). Table1 Col1 Col2 Col3 1 a aa 2 b ab 3 c bb Table2...

One Table - Two Fields - Same value - T/SQL


sql-server,tsql,sql-server-2012
Source: CREATE TABLE #TempTab (ID INT, Value INT, Value1 varchar(10), Value2 varchar(10), value_ INT, Value1_ varchar(10), Value2_ varchar(10), Value3_ varchar(10), Limit INT) INSERT INTO #TempTab SELECT 1, 1,'One','One',1,'One','One','Yes',2 UNION ALL SELECT 1, 1,'One','One',1,'One','Two','No',2 Current query try: SELECT t1.ID, CA.Value3_ FROM #TempTab t1 CROSS APPLY( SELECT Value, Value3_ FROM #TempTab t2...

check multiple schedules for missing dates


sql-server,sql-server-2012
I have a range of dates that I want to see if someone is working or not. I would like to see an entry for each person against each date, with null or zero if they are not working E.g. if dates are last week, 6/1 to 6/7, then I...

SQL Transaction Stuck open


sql,sql-server-2012
Hello I was creating a Stored PROC, so I went into a new Query typed out the following SQL to test before I create the Stored PROC and now when I type select * From InvoicesT; it just keeps trying without returning anything. I believe that the transaction is still...

Fastest way to add a grouping column which divides the result per 4 rows


sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

Retrieve the Return Result with Chronological Order Based on Parameter


tsql,stored-procedures,sql-server-2012
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id. Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1...

SQL Insert Date Mystery on 2012 from 2005


sql-server,sql-server-2005,sql-server-2012
I have migrated a 2005 db to 2012 There is a stored procedure that run to insert todays date into a table IF NOT EXISTS(SELECT * FROM tblTime WHERE [Day] = DATEPART(day,GETDATE()) AND [Month] = DATEPART(month,GETDATE()) AND [Year]= DATEPART(year,GETDATE()) ) BEGIN INSERT INTO tblTime (Period,[Day],[Month],[Year],MonthPrefix) VALUES (CONVERT(VARCHAR(10), GETDATE(), 105), DATEPART(day,GETDATE()),...

Different SELECT's for an INSERT INTO


sql,sql-server-2005
I'm trying to have an IF block after an INSERT INTO statement and depending on some conditions, execute a different SELECT that will feed the INSERT INTO. Here's a (failing) example of what I'm trying to do: INSERT INTO #TempTable (COL1, COL2, COL3) IF @VAR = 'YES' BEGIN SELECT *...

SQL varchar variable inserts question mark


sql,sql-server-2012
I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529‏' select @A Results: R2300529? any explanation? i'm using SQL server 2012....

How to use OFFSET and Fetch without Order by in SQL Server


sql-server,sql-server-2012,sql-order-by,fetch,offset
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...

SQL update statement:Subquery returned more than 1 value


sql,sql-server-2012
I have table named dbo.Movies and now i want update 3 rows This statement works only with one record declare @movietype nvarchar(100) = 'Thriller' declare @price real = 10 if @price < (select Price from dbo.Movies where MovieType = @movietype) begin update mo set mo.Price = Price - @price FROM...

Group Count in T/SQL


sql,sql-server,sql-server-2012
Source: CREATE TABLE #TempTab (Value INT, Value1 varchar(10), Value2 varchar(10), GRP varchar(10)) INSERT INTO #TempTab SELECT 1,'One','One','One' UNION ALL SELECT 1,'One','One','One' UNION ALL sELECT 1,'One','One','Two' UNION ALL SELECT 2,'One','One','One' UNION ALL SELECT 2,'One','One','Two' UNION ALL SELECT 2,'One','One','Three' UNION ALL SELECT 3,'One','One','One' UNION ALL SELECT 3,'One','One','One' Current query effort: SELECT Value,...

SQL - Group by Elements of Comma Delineation


sql,tsql,sql-server-2012
How can I group by a comma delineated list within a row? Situation: I have a view that shows me information on support tickets. Each ticket is assigned to an indefinite number of resources. It might have one name in the resource list, it might have 5. I would like...

Parsing Dates in SQL


sql,sql-server-2012,sql-server-2014
I have a column in my database that is currently a varchar. the data looks like this: Thu, 4 Jul 2013 09:18:24 Thu, 11 Jul 2013 10:07:01 Tue, 28 Jan 2014 11:38:37 Fri, 26 Jul 2013 14:13:42 I want to be able to convert that to a date so that...

How to extract *Area Code* from a phone number in SQL [closed]


sql,sql-server-2012
I have a SQL table with following for Phone Number column. (201) 411-5200x4002 1 (5410) 651-8868 x 5 1-308-987-5920 x8031 2118477656 2195992321x289 313-877-5916 I need to extract out Area Code out of these phone numbers. Area code is first three digits of number. But if we have country code too...

ASp.Net Identity Role manager


asp.net,vb.net,webforms,sql-server-2012,asp.net-identity
I am using Visual Studio 2013. I have a ASP.net (vb) Webforms site with asp.net identity. I trying to create a page that manages User role and create roles. I cant find any help online for this when it comes to web forms. This code works for asp.net membership but...

SQL Server : Recursive Advanced Query on two conditional logics Not showing hierarchy relationship


sql,sql-server,tsql,sql-server-2012,common-table-expression
Recently I got a challenge to resolve in SQL Server 2012. This is the background of the problem. We are maintaining a self referencing entity (hierarchy) in our product entity. Each product is having parent and child relationship. Product is having special grouping called master products which derive based on...

TSQL Table Value Function Parameter different behavior if parameter a variable


sql-server,tsql,sql-server-2012
I have a table value function in TSQL that works well if I pass a string but not if I pass a varchar. For example, this works: SELECT * from [dbo].[SplitString]('ta;aa;qq', ';') This does not work: declare @invarchar as varchar set @invarchar = 'ta;aa;qq' SELECT * from [dbo].[SplitString](@invarchar, ';') If...

Split string and number columns


sql,sql-server,sql-server-2012
Let's say I have a table such as ItemID ClassID ------------------------ 1 10, 13, 12 2 5, 7 and would like to copy the data to another table like so ItemID Numbering ClassID ---------------------------------- 1 1 10 1 2 13 1 3 12 2 1 5 2 2 7 Separating...

Verify data integrity for varbinary column


sql,sql-server,image,sql-server-2012
I am inserting a .jpg into a varbinary(max) column in SQL Server 2012 using the following command: INSERT INTO Employees VALUES(5, (SELECT * FROM OPENROWSET(BULK N'C:\4.jpg',SINGLE_BLOB) AS image)) It inserts a record in the table. Then I used the following command to restore the file from db to file system....

SQL Server 2012 & Polybase - 'Hadoop Connectivity' configuration option missing


sql-server,hadoop,sql-server-2012
As described in the title, I am using SQL Server 2012 Parallel Data Warehouse with Polybase feature to try to access a HDInisght Hadoop cluster. As a starting point for every connection to Hadoop from SQL Server, I find to execute the command sp_configure @configname = 'hadoop connectivity', @configvalue =...

Symmetric Key Decryption returning values with space between characters


c#,asp.net,sql-server,sql-server-2012,encryption-symmetric
I am using following Scaler-valued function to decrypt data that is encrypted using a symmetric key: USE [DBNAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[DECRYPTDATA] ( @CipherText NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Result NVARCHAR(MAX) SELECT @Result = CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@CipherText)) RETURN @Result END And I...

INSERT single quoted value for all column types in SQL Server


sql-server,sql-server-2008,sql-server-2008-r2,sql-server-2012
Say you have a column of int (also bit, etc.) CREATE TABLE TEST (a int) It seems it doesn't matter if you insert value with '1' or 2 INSERT INTO TEST VALUES ('1') INSERT INTO TEST VALUES (2) Is there any side-effect if I insert/update values by single-quoting them (e.g.'value')...

SQL Server 2000 query conversion [closed]


sql,sql-server,sql-server-2005,sql-server-2000
I have a query written in SQL Server 2000 and I want to re-write that to use in SQL Server 2005 and newer versions. CREATE TABLE #TEMP(AffinityGroupName VarChar(100), MostPopularVehicle VarChar(100), LeadCount Int, CNTL_ID int, CNTT_ID int, DayTime datetime, MemberFirstName varchar(50), MemberLastName varchar(50), MemberAddress1 varchar(150), MemberAddress2 varchar(150), MemberCity varchar(50), MemberState varchar(2),...

Finding records in main table that match records in another table in SQL Server


sql,sql-server,sql-server-2012
I have a SQL Server database with three tables: Trips, Slices, and Legs. Each Trip has a one to many relationship with Slices and Slices has a one to many relationship with Legs. Trips represents a full trip, a slice represents only the outbound or return portions of a trip,...

How to search a column name in all tables in a database in SQL Server 2012? [duplicate]


sql,sql-server,sql-server-2012
This question already has an answer here: SQL Server search for a column by name 5 answers Is it possible to search like below: Like '%tax%' ...

Zeroth Row for a OLTP table


sql,sql-server,sql-server-2008,sql-server-2012
I have come across a OLTP Database where all the tables has a default row,value 0 for a primary key and other columns with its default value or a null value.I am not sure why is this row required in first place? can anyone explain what are the advantages and...

Convert SSIS (Visual Studio 2013) to work on SSIS 2012


visual-studio-2013,ssis,sql-server-2012
Is there a way of converting a SSIS package created in Visual Studio 2013 to work on SSIS 2012? The start of my package looks like this: <?xml version="1.0"?> <DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package" DTS:CreationDate="12/18/2014 8:53:33 AM" DTS:CreationName="Microsoft.Package" DTS:CreatorComputerName="LOCAL111" DTS:CreatorName="username1" DTS:DTSID="{C6D60123-5529-4BC1-B426-B219A0709EB7}" DTS:ExecutableType="Microsoft.Package" DTS:LastModifiedProductVersion="12.0.2430.0"...

SSRS Subscription: newly defined fails, old one works


email,sql-server-2012,subscriptions,reporting-services-2012
Newly Created subscription for new report fails with error " Failure sending mail: The specified string is not in the form required for an e-mail address.Mail will not be resent." It is defined in the same way the existing one, using same ReportServer with unchanged config since it started working....

SQL Query using FOR XML PATH that works right


sql,tsql,sql-server-2012
I have read the many posts regarding collapsing multiple table columns into a single column using FOR XML PATH, but I am not a database person and cannot make other posts' suggestions work. I expect my problem is easy to solve for an experienced SQL DB person. I have MS...

create single query to fetch data from tables as seperate rows


mysql,sql-server,sql-server-2012
i am thinking if there is a better way to query product, product options and product option choices from database my database structure is like: product table (product_id, product_name) product_options table (option_id, option_name, product_id) product_optionChoices (Choice_id, choice_name, Option_id) i want a single query that return all product and options in...

Where to declare a SET in SQL?


sql,sql-server-2012
I have been attempting to figure out where I can SET a variable in an SQLQuery. I have 2 different select statements that will have a different WHERE clause depending on the answer to an IF statement, but I would like to only have to run the query to make...

Unable to execute the create statement generate by cursor


sql-server,sql-server-2012
DECLARE @COLNAME VARCHAR(100) DECLARE @CREATE VARCHAR (1000) DECLARE @TYPE VARCHAR(1) SET @COLNAME = '' SET @TYPE = 5 SET @CREATE = 'CRATE TABLE TABLE_TYPE_' + @TYPE + '(' DECLARE MyCursor CURSOR FOR SELECT Name FROM LAYOUT WHERE RecordType = @TYPE ORDER BY Start OPEN MyCursor FETCH NEXT FROM MyCursor INTO...

Compare only DATE from 2 datetime columns - sql


sql,sql-server-2012
How can I code to compare only the date (not time) from two different columns that is storing the date in 2015-01-10 01:20:00 format? Example. Select * from Admissions Where AdmitDateTime = DepartDateTime Above will try to compare 2015-01-10 01:20:00 to 2015-01-10 01:20:00 instead of only date 2015-01-10 to 2015-01-10....

Photos as Varbinary(MAX) in SQL Server 2012 causes error 502753 in Access 2010


sql,ms-access,sql-server-2012,ms-access-2010,varbinarymax
I have an Access 2010 odbc front end with a SQL Server 2012 back end. My images (.bmp) are stored as Varbinary(Max). When I insert an image using my bound object frame in an Access form, the image displays on the form and report perfectly. I had a large number...

Rename table via sp_rename or ALTER SCHEMA


sql,sql-server,sql-server-2012
I am going to perform a table-wide update on a huge table (+200Millon records) so I am going to populate the data via SELECT into a separate table, than drop the original and rename the new one. In one of the articles someone mentioned that it is better to create...

SQL Server Versions - 2012 vs 2014 [closed]


sql-server,oracle,sql-server-2008,sql-server-2008-r2,sql-server-2012
My team needs to get SQL Server as our backend database. I would like to find out if we should get 2014 or 2012 R2. The rest of the company is probably still SQL Server 2008 R2, Oracle and probably MySQL. I wonder if we get 2014 version which is...

SQL server Delete script order


sql,sql-server,sql-server-2012
My tables have the following relations As you can see FirstEntity can have multiple Transactions related record. Transaction is divided into two tables because it represents an inheritance hierarchy (Table Per Type in Entity Framework). I need to create a script which delete all record from FirstEntityTransaction and Transaction given...

how to sort alphanumeric data in sql..?


sql,sql-server,sql-server-2008,sql-server-2012
i have items table which is having Size as follows: 1sq.mm. 1.5sq.mm. 0.5sq.mm. 2.5sq.mm. 0.75sq.mm. 4sq.mm. 20mm 25mm 20mtr 75mm x 50mm 100mm x 50mm 100mm x 100mm 75mm x 75mm i wanted to display it has 0.5sq.mm. 0.75sq.mm. 1.5sq.mm. 2.5sq.mm. 4sq.mm. 20mm 20mtr 25mm 75mm x 50mm 75mm x...

How do I get Sum of two Colunms with Same Foreign Key


sql,sql-server-2005
I have three tables: Carrier(C), DropShipper(D) and ShoppingCart(S) with the following schema Table C c.id(Pk,int,not null) c.dropshipperid(Fk,int,not null) c.Prodid(int, not null c.cost(money null) Table D D.Dropshipperid(Pk,int,not null) D.Dropshipper(nvarchar(50)) D.Remarks(nvarchar(50)) Table S s.cartid(PK, char(36)) s.prodid(pk,fk,int not null) s.qty(int not null) Here are sample data: c.id c.dropshipperid c.prodid c.cost -------------------------------------------- 1 1...

sql query to join and concat values


sql,sql-server,sql-server-2008,tsql,sql-server-2005
sql server: 2012 These are 3 tables. First table is the user's (consultants) table The second one is the prices table, each user from the first column has a price The last table define in what area (city) the consultants offer services Consultants Prices Area uid | Name uid |...

Get Parent and grand parents of a particular child


sql-server,sql-server-2005,sql-cte
i have table like below tablename :ExampleTable ChildID ChildCommonID ParentID 1 2 0 2 3 0 3 4 1 4 5 3 5 6 4 The Problem is : i have a child id example :ChildID= 5 so i need to check wheather it has a parent or not if...

How do I return records based on which records only exist during a certain time period?


sql-server-2005
I've got a table of invoices and customer codes. I'd like to pull only the customers that have not done any business with us since 2009. My regular select statement is simple SELECT INVOICE.CUSTOMER_CODE FROM INVOICES WHERE INVOICES.ORDER_DATE <= '01-01-2010' But, this statement also pulls customer codes of customers who...

How to reference SQL snippet multiple times in a query?


sql-server,tsql,sql-server-2012
I need to convert some stored procs to views and the stored procs have a lot of DECLARE statements which create constants that get referenced later in the query. For example SELECT @FIRSTDAYLASTYEAR = DATEADD(YEAR, DATEDIFF(YEAR, '1901-01-01', DATEADD(YEAR, -1, getdate())), '1901-01-01') I need to refer to @FIRSTDAYLASTYEAR in a single...

Make rows to columns in SQL Server for each record


sql-server,sql-server-2012
I have a large(ish) DB. Simply put customer records. Now I have two tables; one is CustomerInfo and PhoneNumbers. Some sample data would be, CustomerInfo ````````````` CustID | CustName | CustomerLocation --------+---------------+-------------------- 1 | Paul | Bristol 2 | Eugin | Bournemouth 3 | Francis | London PhoneNumbers ```````````` PhoneID...