FAQ Database Discussion Community


Dotnetnuke migration from SQL 2005 to SQL 2012

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...

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...

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...

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....

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...

How to create “cell blocks” in SQL Server 2012

sql,sql-server,sql-server-2012,ssms
I have an SQL query and I want to visually separate parts of it for readability. This is in the query code itself and will only be for my own reference. My current solution is to type a long string of ---------- as a comment and I'd like to know...

How can I resolve a collation conflict

sql,sql-server-2012
I am in the process of trying to move data from one database to another. In the old database there is a column of datatype DateTime, which needs to be mapped to a new column in the new database which is of type Date. To try and accomplish this I...

SQL Server 2012 How to change the data type of a column from bit to datefield?

datetime,sql-server-2012,bit,data-type-conversion,sqldatatypes
I have a table Person with a column called onvacation. This column is of data type bit since it's a boolean in the code. It has values null, 0 and 1. I would like to change the data type of this column from bit to datetime so that all values...

Determine if T-SQL select statement returns any rows

sql-server,sql-server-2012
I am using Microsoft SQL Server 2012. I want to set a boolean value in a stored procedure. I want to set the value to true if a certain select query returns more than zero rows. Declare @ShowQuotingTool as bit (select count(*) from tblAgentsLicensedState where AgentCode = '016893' and StateCode...

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,...

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...

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...

“Invalid use of a side-effecting operator 'OPEN SYMMETRIC KEY' within a function.” error while opening a symmetric key

sql-server,sql-server-2008,security,sql-server-2012,encryption-symmetric
I am trying to open symmetric key inside two functions. Like this: CREATE FUNCTION DECRYPTDATA ( @CipherText NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Result NVARCHAR(MAX) OPEN SYMMETRIC KEY MyKEY DECRYPTION BY CERTIFICATE MyCERT SELECT @Result = CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@CipherText)) RETURN @Result END GO CREATE FUNCTION ENCRYPTDATA ( @Text NVARCHAR(MAX) ) RETURNS...

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....

Update table using random of multiple values in other table

sql,sql-server,tsql,random,sql-server-2012
Consider this data: CREATE TABLE #Data (DataID INT, Code VARCHAR(2), Prefix VARCHAR(3)) INSERT INTO #Data (DataID, Code) VALUES (1, 'AA') , (2, 'AA') , (3, 'AA') , (4, 'AA') , (5, 'AA') , (6, 'AA') CREATE TABLE #Prefix (Code VARCHAR(2), Prefix VARCHAR(3)) INSERT INTO #Prefix (Code, Prefix) VALUES ('AA', 'ABC')...

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...

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...

How to display data from a specific week?

c#,sql,sql-server,sql-server-2012
I want to display data from a specific week by giving it a date e.g. 2015-06-01 This is my table that I want to use to get the week BookingID CheckInDate CheckOutDate 1 2015-05-25 2015-05-31 2 2015-05-26 2015-06-03 3 2015-06-01 2015-06-07 4 2015-06-01 2015-06-12 Here are my database manager code...

T-SQL Insert when not in table and when max timestamp Issue

sql-server,tsql,sql-server-2012,max,sql-insert
I am trying to insert rows from one table to another that are not in the one I am moving them to. I also want to only move the ones that have the highest datestamp. (I want to only insert rows that are not in tb1 and have the max...

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%' ...

SSRS 2012: How do I return the most recent rows only?

sql-server,reporting-services,sql-server-2012,unique-constraint
Okay, here is my current query: SELECT pk.RxFill.PATIENT_PRICE ,PersonQuotas.QuotaYear ,PersonQuotas.QuotaMonth (and a bunch of other stuff to link them) FROM pk.RxMain INNER JOIN pk.Doctor ON pk.RxMain.PHARMACY_ID = pk.Doctor.PHARMACY_ID AND pk.RxMain.DOCTOR_ID = pk.Doctor.DOCTOR_ID INNER JOIN pk.Formula ON pk.RxMain.PHARMACY_ID = pk.Formula.PHARMACY_ID AND pk.RxMain.FORMULA_ID = pk.Formula.FORMULA_ID INNER JOIN pk.Patient ON pk.RxMain.PHARMACY_ID = pk.Patient.PHARMACY_ID...

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...

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....

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...

How to use IF ELSE in SQL statements

sql,if-statement,sql-server-2012
I have a stored procedure and I am trying to use IF ELSE conditions. Alter procedure spInsertVirtualTransactions ( @TerminalID varchar(8), @Pan varchar(20), @Product varchar(4) ) AS DECLARE @TERMINALNEWID VARCHAR(8) select @TERMINALNEWID=TERMINALID FROM TERMINAL WHERE [email protected] Declare @PlatinumLimitTaxi int = 4, @PlatinumLimitAirportLounge int = 4, @PlatinumLimitRoadAssistance int = 4, @GoldLimitTaxi int...

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...

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...

Need help to transpose rows to columns

sql,sql-server,sql-server-2012,pivot
I'm sorry to flat out ask for someone to write my code for me but I've been tearing my hair out for nearly an hour trying to get the pivot operator to work in SQL. I have the following results set: SCCY AccountedPremiumCurrent AccountedPremiumPrevious ---- ----------------------- ------------------------ CAD 99111.0000 NULL...

How regular expression OR operator is evaluated

c#,.net,sql-server,regex,sql-server-2012
In T-SQL I have generated UNIQUEIDENTIFIER using NEWID() function. For example: 723952A7-96C6-421F-961F-80E66A4F29D2 Then, all dashes (-) are removed and it looks like this: 723952A796C6421F961F80E66A4F29D2 Now, I need to turn the string above to a valid UNIQUEIDENTIFIER using the following format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx and setting the dashes again. To achieve this, 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')...

Loop in SQL Server without a Cursor

sql,sql-server,tsql,sql-server-2012
Below is some SQL Server code I have been working on. I know now that using a cursor is a bad idea in general, but I cannot figure out how else I can make this work. The performance is terrible with the cursor. I'm really just using some simple IF...

SQL - specifying a condition in COUNT / returning a value from the same field more than once in one SELECT

sql,sql-server,sql-server-2012
SELECT u.USER_NAME AS [User] ,COUNT(ad.ACTIVITY_ID) AS [Total Activities Late] FROM USERS u INNER JOIN ACTIVITY_DATA ad ON u.USER_ID = ad.USER_ID WHERE ad.DAYS_LATE > 0 AND ad.COMPLETED_DATE IS NULL GROUP BY u.USER_NAME SELECT u.USER_NAME AS [User] ,COUNT(ad.ACTIVITY_ID) AS [Upto One Week Late] FROM USERS u INNER JOIN ACTIVITY_DATA ad ON u.USER_ID...

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 =...

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...

i want to remove un-necessary joins from my following query to increse the efficiency.

sql,sql-server,sql-server-2008,sql-server-2012
i have this code select sales.customerid ,name1.FullName as CustomerName, sales.managerID , name2.FullName as ManagerName, sales.BranchID ,name3.FullName as BranchName, EmployeeID ,name4.FullName as EmployeeName from dbo.Sales as sales join dbo.name as name1 on name.id = sales.customerid join dbo.name as name2 on name.id = sales.managerid join dbo.name as name3 on name.id = sales.branchid...

MSSQL Automatic Merge Database

sql,sql-server,sql-server-2012
I have a PC that has a MSSQL database with 800 variables being populated every second. I need that database to merge/backup to a second database on another server PC at least every 10 minutes. Additionally, the first database needs to be wiped clean once per week, in order to...

SQL statement to find values close to each other

sql,sql-server,sql-server-2012
I'm struggling with defining the SQL to find a list of values that are statistically close to each other. For example, let's say we have a table of prices, and I want to get all the prices that vary within $0.25 of each other. Prices: 1.00 1.25 2.00 4.00 4.50...

How to get the value for the primary key, which is filled by a default sequence in ms sql?

sql-server-2012
For identity create table [Sites] ( [SiteId] bigint identity(1,1) NOT NULL, [Name] nvarchar(50) NOT NULL) ALTER TABLE Sites ADD CONSTRAINT PK_Sites PRIMARY KEY ([SiteId]) i do INSERT INTO dbo.Sites(Name) VALUES('test'); SELECT @@IDENTITY; and get SiteId value. create table [Sites] ( [SiteId] bigint NOT NULL DEFAULT (NEXT VALUE FOR [seqMain]), [Name]...

Merge two SQL query results into one result

sql,sql-server,sql-server-2008,sql-server-2012
I have query like below: SELECT COUNT(*) AS AppleSupports FROM VendorItemPricing WHERE VendorName = 'Apple' SELECT COUNT(*) AS HpSupports FROM VendorItemPricing WHERE VendorName = 'HP' Above queries give me results like below: AppleSupports 63 HpSupports 387 How can make my query to get results in one row like below? AppleSupports...

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...

SQL Search Query with Dynamic search condition

sql,sql-server,sql-server-2012
I have an old dynamic SQL query as below where the conditions in the where clause is appended dynamically based on the search text. Example 1 : Search string 'AMX AC-DIN-CS3 Bracket' SELECT * FROM Tx_Product Where Fk_CompanyId=1 and (ModelNumber like '%AMX%' or Manufacturer like '%AMX%' or Category like '%AMX%'...

how to sum the time grouped by individual day in Sql-server

mysql,sql,sql-server,sql-server-2008,sql-server-2012
I have a table with id, play, starttime and endtime. I want to find the total play time per day. I think the query will be similar as the following but I am sure it is not right. It will be also very convenient if i get 0 when no...

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,...

how to select a column value as number or vice versa

sql,sql-server-2012
i have a column in my sql table that holds the value 'Yes' and 'No' im copying the table into a new database where the data type of that column has changed from nchar(3) to tinyint is there a way to select those particular field as numeric into the new...

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 Server connection string with “$” in password

powershell,sql-server-2012,connection-string
I have a connection string to a SQL Server 2012 DB: $CONN_STR = "Server=SERVER;Database=mydb;User ID=myuser;Password=abc1$4def;" I'm creating a connection in PowerShell, but the login fails due to invalid password. I know that the password is correct and validated that in SSMS. I've tested with another account and from what I...

Calculate total time between two times in sql

sql-server-2012
I am using following sql to calculate time difference for records in a table and taking sum of them grouping by employeeId and login Date, But I am injecting a condition in the middle,If the LogOffTime is null I want to take the difference of current time of the day...

Return records even if they don't exist in the joined tables

sql,sql-server,sql-server-2012
I have a SQL Server database with three tables: GROUPS GroupID GroupTitle 1 Group 1 2 Group 2 3 Group 3 4 Group 4 MEMBERS MemberID MemberName 19 Jon 20 Marie 21 Andrew MEMBER GROUPS MembersGroupID GroupID MemberID 221 1 20 231 1 21 I made a left join and...

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...

SQL Server 2012 - Dynamic SQL

sql-server,sql-server-2012,dynamic-sql
Can someone please explain why the below query works? I assume the first DECLARE uses a VARCHAR that's long enough to hold the table name. But why does the second DECLARE use a VARCHAR and why does it's corresponding query need to be wrapped in 'quotes'? USE Northwind DECLARE @TableName...

Data streams in case of Merge

sql-server,ssis,sql-server-2012,sql-azure,sql-server-2014
We are seeing enormous amounts of data-traffic to and fro our SSIS server. We cannot find the culprit. Is there any way to find out which package is causing all the trafffic? Any advice on that? We are thinking that maybe all the merges we do cause all the traffic....

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...

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...

Implement more conditions on upsert (user defined table types) in SQL Server?

sql-server,stored-procedures,sql-server-2012,user-defined-types
I've a table named 'VendorItemPricing' in my database. I'll insert/update data into this table periodically using Data Table, in other words bulk insert/update operation I'll perform. Below is my stored procedure to perform this operation, and it works good. Assume this is my table VendorItemPricing: ItemPartNumber VendorName VendorPrice UpdatedDate ObsoleteItem...

Improve read XML string in sql server

c#,sql-server,xml,tsql,sql-server-2012
I have the xml string sends to SP as nvarchar(Max) '<Devices><ID value="13" /><ID value="39" /></Devices>' And I use this way to return IDs DECLARE @DeviceIDs nvarchar(max) = N'<Devices><ID value="13" /><ID value="39" /></Devices>' ,@iDevice INT; DECLARE @Devices table (DeviceId int PRIMARY KEY) EXEC sp_xml_preparedocument @iDevice OUTPUT, @DeviceIDs Insert Into @Devices(DeviceId) SELECT...

SQL Server union not sorting correctly

sql-server,sql-server-2012
I have the following SQL statement: select top 1 scrr.name, sess.end_time from tbl_session sess inner join tbl_scripts scrr on sess.script_id = scrr.script_id where end_time >= '5-May-2015 14:58:00' and end_time < '06-May-2015 14:58:00' and scrr.script_type in (1,3,4) and sess.operator_id = 95 UNION select top 1 scr.name, oh.end_time from tbl_outbound_history oh inner...

Determine overlapping times between record sets

sql,sql-server,sql-server-2012
I have 2 sets of data I need to compare via time stamps. I need to determine how long a staff member is with a patient in a specific room. Initially, we were told the patient arrives to a room first and is the last to leave. This is not...

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()),...

How to insert records as break times in a select in a Stored Procedure

sql,sql-server,tsql,sql-server-2012
Having this table in Sql Server 2012, I need to return the break times inserted in the right position. I am able to use temporary tables if necessary. Break: It is the difference between the times, so I need a new record representing the gaps. Table: ╔════════════════╦════════════════╦══════════╦════════╗ ║ Start ║...

TSQL filtering by character match

sql,sql-server,tsql,sql-server-2012
How to filter out number of count matches without building new user functions(i.e. you can use built-in functions) on a given data? The requirement is to get rows with the gw column numbers appearing the same amount of times or if there is different set of amounts their number must...

Exception “Procedure or function expects parameter, which was not supplied.” thrown even after supplying the parameter

c#,.net,sql-server,stored-procedures,sql-server-2012
I am trying insert some entries in the database using a stored procedure which is called by a C# method as shown below: public int addProfile(UserProfile NewUserPro) { DataSet ds = ExecuteStoredProcedure("AddNewPro", new SqlParameter[]{ new SqlParameter("@UserID", NewUserPro.UsrId), new SqlParameter("@Type", NewUserPro.Type), new SqlParameter("@Phone", NewUserPro.Phone), new SqlParameter("@Name", NewUserPro.Name), new SqlParameter("@FatherName", NewUserPro.FatherName), new...

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"...

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...

Cartesian coordinate- Select coordinates which fall within an area

sql,sql-server,google-maps,sql-server-2012
I'm working on a problem, where I have to map indoor Cartesian coordinates to a certain zone. The problem has numerous zones lets say zone1, zone 2, zone 3 and zone 4 with coordinates (x1,y1), (x2,y2) (x3,y3) and (x4,y4) respectively. I have a huge data set of visitors who are...

dbcontext.entity.select not available in Visual Studio 2013

c#,asp.net,sql-server-2012,linq-to-entities,entity-framework-6
it's been a while since I work with EntityDataModel. I used to do things like: var listaProductos = dbContext.Productos.Select(p => new I just started a new web project with asp.net and added the entity data model but If i try to do the same as the example above I can't...

How to set a column name in SQL query as parameter?

c#,sql-server-2012
I want to transfer to CommandText table name as parameter, something like @column. How can I do this? Because column name is transferred as custom parameter. using (SqlConnection connection = SQL.Connection()) { using (SqlCommand cmd = connection.CreateCommand()) { cmd.Parameters.Add("@data", SqlDbType.VarChar).Value = "some_string"; cmd.CommandText = "UPDATE users SET [email protected]"; cmd.ExecuteNonQuery(); }...

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...

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...

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...

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...

Change date format in SQL Server 2012

sql,sql-server,date,datetime,sql-server-2012
In the files I'm importing to my SQL Server database, the dates are formatted differently in different columns. I've been having issues with (for example) 02-06 getting entered as February 6 or June 2. I will use SELECT [entry_date] = DATE_FORMAT([Date], GET_FORMAT(DATE,'EUR')) FROM table_name to standardize the dates within the...

How to use max function with select top in sql

sql,sql-server,sql-server-2012
I have a table, lets call it TempAccount, with a column named AccountID. It contains numbers from 1,2,3...and so on. My requirement is that I should select the maximum value from the top 10 AccountIDs. I know I can do it by creating a temp table and inserting the top...

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...

Format Numbers in Multiple columns of SSRS Report

reporting-services,sql-server-2012,ssrs-2008
Currently I am working on a ssrs report. The table in report is having about 30 columns. For each column I have to modify the number format (to either 2 decimal or no decimal numbers). I can do it by click on every column and modify the number property in...

Declare table dependency in stored procedure when using T-SQL

sql-server,tsql,sql-server-2012
I am going to use dynamic sql in my stored procedure to remove some of the code duplication. But I see one big drawback for me in this case: I have rather big DB with lots of objects. My stored procedure is using few tables and since it is compiled...

SQL code simplifying with ISNULL or COALESCE assistance

sql,sql-server-2012
so i have this stored procedure and i was asked to simplify it by using a ISNULL or COALESCE but i can't seem to make it work CREATE PROCEDURE [GetCount] @Session NVARCHAR(7), @Activity BIT = true AS IF @Activity = 1 BEGIN SELECT COUNT([user].[ActivityID]) AS [Count] FROM [ActivityListing] AS [act]...

Updating every row in a table with information gleaned from other tables

sql,database,sql-server-2012
An old database that has been in use for many years has finally reached that stage where it needs to be retired and substituted for one more suited to the needs of the business it is being used in. Where possible the new database has been designed to make the...

Invalid column name 'CalanderDate'. when using a with and a union

sql,sql-server-2012,union
i have the following query : WITH Calender AS ( SELECT @FromDate AS CalanderDate UNION ALL SELECT CalanderDate + 1 FROM Calender WHERE CalanderDate + 1 <= @ToDate ) INSERT INTO #C SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25) , case [dbo].[CalculateNumericWeekCode] (4, CalanderDate) when 1 then 'A' when 2 then 'B' when...

Can not able to take back up get error Msg 3201, Level 16, State 1, Line 1

sql-server,sql-server-2012,dynamic-sql
I am practicing to create procedure which take back up of all data base except system database using Dynamic SQL my procedure compile successfully but when i run it i get below error for each data base Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'F:\DHAVAL_BACKUP_DEMO\itmusket_StudentApp_2015_05_31_10:27:07.BAK'....

Sql query to select 3rd element from data? [duplicate]

sql,sql-server,sql-server-2012
This question already has an answer here: How to select the nth row in a SQL database table? 23 answers I have written sql query Select * from Products WHERE Products.Category=="Fruits"; the returned answer is 1-Banana 2-Mango 3-Apple 4-Oranges 5-Grapes now i want to select fruit at index 3rd...

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...

Combine two rows based on common ID

sql,tsql,merge,sql-server-2012
I have a query that returns two lines per ID. I would like to be able to merge these into one single row, based on the ID. There will only ever be two rows per ID. As in the example below, one row will only ever contain ColumnA and the...

Deleting non matching records from a table prior to linking

sql,database,sql-server-2012
I have two tables in a new database containing data migrated from an old database. These two tables need to be linked, but prior to that I need to remove those records that don't have matching id's. The two tables in question are illustrated below; I need to eradicate those...

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...

slq join get sum of amount from first table and sum of cost from second table when second table has more rows then first table by grouping with hours

sql,sql-server,sql-server-2012
I have two SQL Server tables below: Invoice InvoiceId Amount [Date] 1 10 2015-05-28 21:47:50.000 2 20 2015-05-28 21:47:50.000 3 25 2015-05-28 23:25:50.000 InvoiceItem Id InvoiceId Cost 1 1 8 2 1 3 3 1 7 4 2 15 5 2 17 6 3 20 7 3 22 Now I...

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...

Change column values based on another column in same table

sql,sql-server,sql-server-2012
I have the table +---------------------+ | ID | Code | Amount -| +---------------------+ | 1 | 101 | 1.2 | | 1 | 102 | 1.3 | | 1 | 103 | 1.3 | | 1 | 104 | 1.4 | | 1 | 105 | 1.2 | | 2...

Insert into Change Log based on Trigger After Update

sql-server,triggers,sql-server-2012,sql-server-triggers
I need to code an AFTER UPDATE trigger. When the pay rate of the employees table is modified. It needs to take that information and create a line in the ChangeLog table. My tables are below: Employees: EmployeeID INT (PK), PayPerHour MONEY ChangeLog: ChangeID INT (PK), EmpID (FK to EmployeeID),...

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...

Subquery returned more than 1 value. This is not permitted when the subquery follows =,.. or when the subquery is used as an expression

sql,sql-server,tsql,stored-procedures,sql-server-2012
I have this following stored procedure to make a reservation .I have not done the front end to insert the values so I use the execute stored procedure from the sql server menu to insert into the database but it gives me the subquery returned more than 1 value and...

Inserting Dataset + additional columns into a table

sql-server,tsql,sql-server-2012,dataset
This seems like a pretty straightforward problem but for the life of me, I can't seem to figure out how to do this. I have a dataset [a combination of some UNION's] that needs to be inserted into a table. Dataset: SELECT COL1 AS A, COL2 AS B, COL3 AS...

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...

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...

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...

Inner join with one row of another table

sql,sql-server,sql-server-2012,inner-join
**Table Employee** Id Name 1 EmpName1 2 EmpName2 3 EmpName3 **Table EmpDeptHistory** Id EmpId Dept Date 1 1 Housing 2015-03-02 2 2 Finance 2015-01-03 3 1 WareHouse 2015-05-02 4 2 Housing 2015-02-06 5 3 WareHouse 2015-02-02 6 1 Housing 2015-05-01 7 2 Finance 2015-01-02 8 2 Housing 2015-05-04 9 2...

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....

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...

How do I Specify Computed Columns in a Table which are based on another Column in SQL Server?

sql,sql-server,sql-server-2012
I have the following fabricated data seen below. I would like to alter the Total_Investment Column to make it a Computed Column which computes the total Dollar_Amount for the Unit Name that is present in that row. For example, for Row 1, the Total_Investment slot would show the sum of...

Cannot insert duplicate key row in object 'dbo.ta_Kullanici' with unique index 'IX_ta_Kullanici'.\r\nThe statement has been terminated

c#,entity-framework,sql-server-2012
I try to develop a program for insert datas from excel table to SqlServer. I had an error when I try to insert data to sqlserver. That is my error message here "Cannot insert duplicate key row in object 'dbo.ta_Kullanici' with unique index 'IX_ta_Kullanici'.\r\nThe statement has been terminated." ID is...

Using a Table of Column Definitions to Create an Insert Query

sql-server,sql-server-2012
I have a table called raw_data that contains a column with a large string of data fields formatted in fixed length sub-strings. I also have a table table_1 that specifies the column name and the data range in the string for each value. I need to create a SQL INSERT...