FAQ Database Discussion Community


Last 13 full months SQL WHERE clause

sql,date,where-clause,sql-server-2014
I have a database with a date column that logs when a new "contact" is created. The contacts are generated when the call center receives a new call or e-mail. What I want is for the where clause to capture the last 13 months of full data. Examples: Today is...

SSRS 2014 URL access always generates XLS instead of XLSX when format is EXCEL

excel,reporting-services,sql-server-2014
I have SQL Server 2014 and I'm trying to access my reports via the URL access. As I understand, the Excel format should be giving me an XLSX file which supports an unlimited number of rows. However, after looking at my log files I noticed that I was getting maximum...

How do I create a local database inside of Microsoft SQL Server 2014?

sql-server,database,sql-server-2014
Forgive me if this question was already asked before. I just installed Microsoft SQL Server Management Studio 2014. I want to create a database so I can start building some tables from scratch. All I am getting is the ability to connect to a database. Not create a new database....

Backup compatibility from SQL Server Enterprise 2008 to SQL Server Standard 2014

sql,sql-server,sql-server-2008,sql-server-2014
I got an old database that I need to transfer from SQL Server 2008 to 2014. The problem that occurred was that restoring the database in 2014 threw an error saying that it's not compatible, due to some functions that are only available in Enterprise edition. Database "---name---" cannot be...

Could not load file or assembly Microsoft.SqlServer.TransactSql

sql-server,azure,sql-azure,ssdt,sql-server-2014
Database project build failed. Error Details: Error: MSB4018: The "SqlBuildTask" task failed unexpectedly. System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.TransactSql, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.SqlServer.TransactSql, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' I also go through this answer on stackoverflow...

SQL Service Broker - communication scenario - migration from SQL 2008 R2 to SQL 2014

sql-server,sql-server-2008-r2,sql-server-2014,service-broker
Summary: Is there anything new in MS SQL 2014 (versus 2008 R2) that does not allow the following scenario? Can the same approach be transfered to the new server? The application uses SQL Service Broker to collect data from satellite SQL Express machines (technology computers, now two, soon 4, and...

SQL Server: From 2008 To 2014 :-The data types datetime and time are incompatible in the add operator. Any other solution?

sql,sql-server-2008,datetime,sql-server-2014,sqlmigrations
I am migrating from SQL Server 2008 To 2014 and I get an error The data types datetime and time are incompatible in the add operator. I figured out the solution convert time to DateTime, but I have changes in many stored procedures and views. Is there any other solution...

T-SQL distribute a value between rows without a cursor

sql-server,tsql,sql-server-2014,window-functions
I have a simple live-case scenario on the SQL Server 2014. I have a table with orders and their quanities: declare @qty_to_distribute int = 50 create table orders (id int, priority int, qty int) insert into orders (id, priority, qty) values (1, 1, 10), (2, 2, 30), (3, 3, 20),...

SQL Server integration services deployment wizard port

ssis,sql-server-2014
I am trying to deploy a SSIS project from Visual Studio (VS). I can run my packages locally in VS against the SQL Server 2014 instance. I can also connect to the SQL Server 2014 instance via SQL Server Management Studio. However, I cannot connect when I try to deploy....

How to show results similar to countif in SQL Server 2014

sql-server-2014
I'm trying to count records in SQL Server and get the same results as I would in Excel by using Countif function. I've tried Count(*) and other different ways of counting records in SQL Server but couldn't get the same results. Is there a function or logic that would allow...

Silent SQL Server 2014 installation fails with Access Denied on tempfile

sql-server,sql-server-2014
Title basically describes it. Things I've checked: verified the name of the user in the remote powershell session (to make sure things are executing with the permissions I expect them to) checked the permissions on the described file. The expected user has full control of the file removed the .tmp...

SQL - Nested Select Statements with Max?

sql,sql-server,sql-server-2014
I have one table and in this table is PostDate and OrderNo and a bunch of other fields. I would like to get all the latest PostDates and then with those PostDates get the largest OrderNo. I would then like to show one row that shows the PostDate and that...

Generation of ROWID column in Triggers even when its Generation is switched off in SSMA v 6.0

sql-server,oracle,database-migration,sql-server-2014,sql-server-migration-assi
I used SSMA v6.0 for migrating my oracle Database to SQL Server 2014 Database. I turned off the generation of ROWID column and as expected it did not generate any additional ROWID column in any of my tables after Conversion, But Surprisingly it DID generate all the triggers associated with...

Finding duplicate number in couple SQL Server tables in c#

c#,database,sql-server-2014
I have a SQL Server database with a couple tables with records. My input is IMEI number that I must store in a table only if this IMEI number is not saved in all tables. my code is: public int Find_IMEI(string IMEI_NUM) { IMEI_lab.Text = IMEI_NUM.ToString(); int dflag = 0;...

How do I resolve a 0x80040e14 and 0xC0202071 in SSIS?

sql,visual-studio-2013,ssis,sql-server-2014
I am extremely new to SSIS and may need allot of things explained. What I am trying or attempting to accomplish is this: Connect to an XML site using SSIS in VS-2013 Delete the existing data in the table Dump the parsed XML data to the table I am getting...

Time data type not available in SQL Server Express 2014

sql-server,sql-server-2014
I'm creating a database for some sports records. I want to record the date of the event, and the score (which is a time value). So I want to store time and date in two separate columns. Should be able to do this in SQL Server 2014 with the 'date'...

Preventing SQL injection in a report generator with custom formulas

sql-server,sql-injection,sql-server-2014
For my customers, I am building a custom report generator, so they can create their own reports. The concept is this: In a control table, they fill in the content of report columns. Each column can either consist of data from DIFFERENT DATA SOURCES (=tables), or of a FORMULA. Here...

Why is SQL Server changing operation order and boxing the way it does?

sql,sql-server,sql-server-2014
Four simple SELECT statements: SELECT 33883.50 * -1; SELECT 33883.50 / -1.05; SELECT 33883.50 * -1 / 1.05; SELECT (33883.50 * -1) / 1.05; But the results are not as I would expect: -33883.50 -32270.000000 -32269.96773000 -32270.000000 That third result is the one that seems questionable. I can see what...

How to add a new schema in a Visual Studio 2013 Database Project

visual-studio-2013,database-project,sql-server-2014
I need to create an additional schema with some database objects in it within a Visual Studio 2013 Database Project. How can I do this?

GROUP BY with HAVING clause does not return a row?

sql,sql-server,sql-server-2014
Strange problem with a GROUP BY and HAVING clause on SQL Server 2014. It's distinctly possible I've done something wrong, but I cannot make sense of this. Here's the setup data: create table #accounts(accountid int) create table #data(accountid int, categoryid int, asofdate date, datavalue numeric(20, 10)) insert into #accounts values...

Why isn't my single-character full text search working?

sql-server,full-text-search,sql-server-2014
Given this code, I'm getting back no results: SELECT TOP 10 * FROM MyTable WHERE CONTAINS(TextColumn, '"W"') Things I've checked: Longer words return a result There are no stop LIST associated with this index There is definitely text such as "James W Brown" in the column The full text index...

using multi-value parameter with delimited string in dataset

sql,reporting-services,sql-server-2014
I have a table that contains a field with a delimited string, This is in fact a unsorted list of id's id| name | valueslist ---------------------------------------------- 1 | John | 46423423,36456325,76473234,98798796 2 | Mike | 36456325,98798796 3 | Sara | 46423423 I have a multi-value parameter based on a dataset...

Should I use NTILE to get a distribution of data by datalength?

sql-server,tsql,sql-server-2014
Using SQL Server 2014. I have a table with millions of rows which has a varchar(max) column called [Description]. The MAX(DATALENGTH(Description)) is 30000. I want to show a results set showing the distribution of the data by datalength in groups of 1000. So the first row would show a count...

IN Memory Oltp hash index vs non clustered

sql-server,sql-server-2014,in-memory
SQL 2014 memory tables supports two type of indexes ,hash non clustered,non clustered.Since memory optimized tables are not stored as rows,we need to have one index compulosary.Below is the syntax to create hash index CREATE TABLE dbo.sample_memoryoptimizedtable_Hash ( c1 int NOT NULL, c2 float NOT NULL, c3 decimal(10, 2) NOT...

defaults using Getdate and DateAdd are not working

sql,sql-server,sql-server-2014
I set up a table yesterday with the following code. the code ran with no error messages reported and the table appeared correctly set up in object explorer. Create Table PriceTable (Airport_IACO_Code Varchar (4) NOT NULL, Airline_IACO_Code Varchar (3) NOT NULL, FlightDate Date NOT NULL Default Getdate(), DepTime Time NOT...

SQL Case When - delete 'empty' rows from output

sql,row,sql-server-2014,case-when
I'm having difficulties with a query that I'm writing. The query looks like this: SELECT case when Year(LOAN_START_DATE) = 2010 then max(LOAN_RES_BANK_CODE) else 0 end as '2010', case when Year(LOAN_START_DATE) = 2011 then max(LOAN_RES_BANK_CODE) else 0 end as '2011', case when Year(LOAN_START_DATE) = 2012 then max(LOAN_RES_BANK_CODE) else 0 end as...

SQL Server 2014 Lookup value in Table2 between columns

sql,inner-join,sql-server-2014
I have two tables. In Table A I have a "StartDate" besides other values. I also have a column "TimeZone". In Table B I have TimeZone, StartDST, EndDST (DST=DaylightSavingTime) for several years. I want to check - if StartDate is in Table B between B.StartDST and B.EndDST. If NOT... it...

Reuse field values in WHERE clause

tsql,where,sql-server-2014
How do I optimize the performance of the following T-SQL so heavyFunctionCall function will get called just once. Looking for the fastest option among table variables, temp tables, CTEsor something else? SQL: select dbo.heavyFunctionCall(a, b, c) from T where dbo.heavyFunctionCall(a, b, c) > 10 ...

joining two tables by value of one column and calculating

sql,sql-server,join,sql-server-2014
Thank you for interested in question. I have in one table some thing like this sifrez | sifKorisnikPK | Status 1 | 1 | 'P' 2 | 1 | 'P' 3 | 1 | 'U' 4 | 2 | 'P' 5 | 2 | 'P' 6 | 2 | 'U'...

SQL Server procedure optimization testing pattern improvement

sql-server,testing,sql-server-2014
I've been doing some SQL Server procedures optimization lately and was looking for a testing pattern (time and result wise). I've came with this solution so far: SET NOCOUNT ON; ---------------------------------------------------------------------------------------------------------------- -- Procedures data and performance testing pattern ---------------------------------------------------------------------------------------------------------------- -- Prepare test queries (most likely will be taken from Logs.ProcedureTraceData...

SQL Server 2014 Migration

sql-server-2014
Recently we've been looking into upgrading our SQL Server 2008 Instances to 2014. As part of this we also need to complete a hardware migration and so we're going down the backup/restore route. We've completed a migration in our UAT environment and this has led to a few performance concerns...

Recursively calculate total value of current row using previous row value SQL Server

sql-server,tsql,common-table-expression,sql-server-2014
Assuming I have a table like this Id Initial Value CardCount Total 1 5 1 null 1 0 2 null 1 0 4 null 2 10 0 null 2 0 3 null 2 0 1 null And I want to update the total column to be the sum of the...

Any issues using SQL Server 2008 R2 backup on SQL Server 2014

sql-server-2008-r2,compatibility,sql-server-2014
Our website is backed by a database written using SQL Server 2008 R2 Enterprise. I am in India working with an outsource org and the only thing they can get is SQL server 2014 developer edition, which supports the compression options that are used with 2008 r2 enterprise. I have...

Azure virtual machine cost and pricing questions

iis,asp.net-mvc-5,sql-server-2014,azure-virtual-machine
I have an A-Series, A0 at $18.22 ($0.025/hr) Azure Virtual Machine setup. How much is the cost to update software in the virtual machine. For example, if I wish to do a Windows Update from the Virtual Machine's Control Panel, is there any cost associated with this? If I wish...

Default random 10 character string value for SQL Server column

sql-server,random,default-value,sql-server-2014
I have a column rndm in my table [guests]. Now, for the field Default value or Binding for the table, whenever a new row is inserted I want to automatically insert a 10-character random string into this column as the default value. This random string may not contain special characters,...

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

Is it a best practice to drop the temp table after using it, in addition to before creating the temp table?

sql-server,temp-tables,sql-server-2014
I have a stored proc that creates a temp table. It is only needed for the scope of this stored proc, and no where else. When I use temp tables list this, I always check to see if the temp table exists, and drop it if it does, before creating...

If numeric then Insert numeric else Insert non-numeric

tsql,insert,sql-server-2014
I have table source SRC such as: *Column1* First Second 45 Fouth Now I want to insert these data into table DEST (ID, NAME) with this logic: If row is numeric, insert into (ID, NAME) VAUES (45, 'TBD'). If the row is not numeric, generate ID and insert into (ID,...

Cannot login with new SQL User - SQL 2014

sql,authentication,sql-server-2014
I'm having some trouble with logging in with a newly created SQL User. I have created a login and user on my SQL Server. I have changed the SQL Authentication mode to mixed and restarted the service. Each time I try to login it gives me this error: Login failed...

How to change date-time format?

sql-server,date,datetime,sql-server-2014
I have a RegDate column of nvarchar(max) type in my table in which dates are stored in mm/dd/yyyy (5/22/2015 11:09:39 PM) and dd-mm-yyyy (19-05-2015 22:55:05) format. I want to get all these entries in one format i.e. dd/mm/yyyy. I tried to convert it by using Convert(varchar(10),cast(vr.RegDate as DATETIME),105) as RegistrationDate...

What is the default Escalation Threshold for Microsoft SQL server 2014

sql-server-2014
What is the default table Escalation Threshold for Microsoft SQL server 2014. Note: on SQL 2008 and 2012 was 5000 and there is reference, but I cant find any reference for 2014...

Using case in SQL server

sql,sql-server,sql-server-2014
I'm trying to use a CASE statement in SQL Server. I'm trying to update the ProfitUSD column where in the values fetched from the Profit column is multiplied by the conversion rate from the Rate column to get the Profit in USD (ProfitUSD column). Basically, I'm just trying to convert...

BCP utility incorrect syntax

sql-server,azure,bcp,sql-server-2014
What is incorrect about this syntax? bcp transitschedule in calendar_dates.txt -T -f calenar_dates.fmt -F 2 I have tried this through sqlcmd and SSMS to a database on Azure. When I run the command I get: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'in'. Yet,...

when can you join a varchar to an nvarchar

sql-server,sql-server-2014
I need to understand when a join between a varchar and nvarchar works and when it does not. Brief background: We will be changing many of our varchar columns to nvarchar columns. We also have dozens of SSRS reports with a total of 100 queries or so. I need to...

SQL select distinct by 2 or more columns

sql,sql-server,select,distinct,sql-server-2014
I have a table with a lot of columns and what I need to do is to write select that would take only unique values. The main problem is that I need to check three columns at the same time and if all three columns have same values in their...

How do I insert into Table2 from Table1 and then use the inserted ID to insert into Table3

sql-server,database,sql-server-2014
I have some tables that store the following data, which is not exactly what I actually have but is simplified so that it's easier to explain.. Table1 UserID (FK) TestID (FK) StartTime EndTime QuestionID1 AnswerToQuestion1 QuestionID2 AnswerToQuestion2 Since Table1 is a spreadsheet, I want to import this data into two...

Are Hekaton transaction logs larger than usual in a failover cluster?

sql-server,sql-server-2014,in-memory,alwayson
In a meeting a few days ago someone said that "using Hekaton increases the size of the transaction logs which increases the time to fail over" while describing the challenges of an AlwaysOn SQL cluster that uses Hekaton in-memory tables. I'm not a SQL expert so wondering if this is...

ServiceStack taking a long time to execute stored procedure

c#,sql,servicestack,ormlite-servicestack,sql-server-2014
I have implemented ServiceStack (v4.0.36) with an ORMLite connection to my SQL Server 2014 database. There is a search form on my website that passes any populated fields to a "/search" route as querystring parameters. My request DTO looks like: [Route("/search", "GET")] public class SearchRequest { public string FirstName {...

SQL RESTORE WITH RECOVERY; Hangs at 100%

sql-server,restore,sql-server-2014
I have done a lot of research about this. I am attempting to recover a database with SQL Server 2014 and it keeps hanging at 100%. A lot of people suggest that the solution is to just make sure that you restore with the RECOVERY option. I have tried that...

SQL bypass REPLACE by CASE statement

sql-server,tsql,replace,case,sql-server-2014
I have data where some records contain blank rows (=no space, no NULL). E.g.: LOCALE en-es en-uk uk-uk When I want to select that blank row, it's easy with: SELECT LOCALE FROM ABC WHERE LOCALE = '' But when I try to replace it as follows, it does not work,...

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

Fill the columns of a table with data from an specific cell on SQL Server?

sql,sql-server,ssms,sql-server-2014
Is it possible? I have a lot of inventory lists, one per store and the stores have an Id, I need to get the store Id and copy on the column next to the information, it could be with temporalized tables I guess, for example: I wanto to transform this:...

Send CSV file with db_sendmail - missing records

sql-server,csv,sql-server-2014,sp-send-dbmail
I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected -...

Convert SQL Server Date to mm-yyyy

sql,sql-server,tsql,sql-server-2014
I'm trying to convert my Date which is (eg. 2012-04-20 05:54:59) format in into mm-yyyy. I came across some solutions that says you would need to convert into varchar . Is there any way using the Convert function ? Thanks :)...

How to group by SQL based on certain conditions?

sql,sql-server,sql-server-2014
I am using Microsoft SQL Server 2014 and I have the following data about person watching Tv: Where times are in minutes from midnight. PersonId StartTime EndTime 1 300 600 1 250 700 1 200 800 1 900 1200 2 100 200 Now as you can see, what I want...

Trigger - Difference in FOR/AFTER/INSTEAD OF

sql-server,sql-server-2014
I need to rebuild following section on a trigger: { FOR | AFTER | INSTEAD OF } So if the trigger has "instead of" it could be 0 or 1: SELECT is_instead_of_trigger FROM sys.triggers Where object_id = Object_Id ('') How can i resolve if the particular trigger has for or...

SQL Server 2014 Edition - Enterprise vs Developer [on hold]

sql-server,sql-server-2014
I need to set up a development server for my team including programmers and database administrators. I wonder which version of SQL Server 2014 I should install on the development server. I was told that developer version is enough and will be installed on each developer's machine. But don't we...

Count and limit the number of files uploaded using VB.NET

.net,vb.net,visual-studio-2013,image-uploading,sql-server-2014
I want to take input of 5 images from user, show thumbnails and save them to 5 columns in SQL-database(image1,image2,image3,image4,image5). Please add code for sql insertion of these 5 files. Please help me out Private Sub BrowseMultipleFilesButton_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles BrowseMultipleFilesButton.Click Dim OpenFileDialog1 As...

Selecting data that could reside in many databases

sql,sql-server,sql-server-2012,sql-server-2014
This is so complicated (in my head at least) that I couldn't title my question properly. I have hundreds of databases (one for each Supplier) that I need to connect to from a central database that contains Order, Customer, and Supplier tables. Basically the Suppplier table in the central database...

Partial transpose of rows to columns

sql,tsql,sql-server-2014
The question is very simple, but I am having troubles with realization. The current table looks like this: ID Value A 1 A 2 A 3 B 1 B 2 C 1 And i need it like this: ID Value1 Value2 Value3 Value... A 1 2 3 NULL B 1...

Run multiple queries each selecting each respective Nth row?

sql-server,modulo,sql-server-2014
`Related to "Selecting every nth row from SQL Server 2008 query result where table does not have row id column", I understand that doing the following would select every 4th row. SELECT ID, Whatever FROM MyTable WHERE ID % 4 = 0 But what I want to do is run...

what is max database size limit while using AMR tool?

sql-server-2014,in-memory-database
what is the max size of database used for AMR tool that we used to identify table/procedure that we can move in In-Memory OLTP?

SQL Server check if where clause is true for any row

sql,optimization,sql-server-2014,spatial-query
I'm going to select those provinces which intersects any railroad. So I do it like this (Using SQL Spatial): SELECT * FROM ProvinceTable WHERE ( SELECT count(*) FROM RailroadTable WHERE ProvinceTable.Shape.STIntersects(RailroadTable.Shape) > 1 ) > 0 But it is not efficient because it has to check the intersection between every...

How to store longitute & latitude as a geography in sql server 2014?

sql,sql-server-2014
I'm a sql beginner that knows how to get my users location in longitude and latitute coordinates. My goal is eventually to be able to select all rows from myTable Where distance is less than 2km. However on my way towards this goal there is some things that confuses me...

Count records by month & type

sql,sql-server,sql-server-2014
I have a table in SQL Server 2014 with this schema: OccuredDate (date) TypeID (int) 2014-1-1 1 2014-1-2 1 2014-2-5 4 2015-5-23 2 2015-6-3 3 …it has thousands of rows comprised of dates & typeIDs, spanning years. So that I can plot this to a charting component, I’m trying to...

How can I determine the max usable size for my SQL Server Express tables?

sql,sql-server,sql-server-2014
I'm running up against a problem with SQL Server Express that I've never encountered before. I have a table with 32 Million rows in it, with this column set: [Insp_ID] [nvarchar](8) NOT NULL, [NodeID] [int] NULL, [NodeVisible] [bit] NULL, [Note] [nvarchar](max) NULL, [Points] [int] NULL, [QST_ID] [int] NULL, [QST_Num] [int]...

Update even though using constraints

sql-server,sql-update,foreign-keys,primary-key,sql-server-2014
I'm trying to update a value (small spelling error) in the SQL Server 2014 database. I do like this: update t_Table set funID = 'References' where funID = 'Referencies' When doing this I get the error The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_t_Table_Language_t_Table". The conflict occurred in...

error message: Format of the initialization string does not conform to specification starting at index 0

c#,asp.net,sql-server-2014
I searched SO but have not found a solution to my error. I am using VS 2013, and SQL Server 2014. Below is my connectionstring: using (SqlConnection sqlConnection = new SqlConnection("cnInvestTracker")) { } My web.config is: <connectionStrings> <add name="cnInvestTracker" connectionString="Data Source=localhost;Initial Catalog=InvestTracker;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> I am getting an...

SQL Server 2014 Analysis Services Deploy Permission Error

ssas,sql-server-2014
How can I resolve this permissions error when deploying an Analysis Services cube? The user already has SA privileges on the localhost server. ------ Build started: Project: FerrariRusso, Configuration: Development ------ Started Building Analysis Services project: Incremental .... Dimension [Date] : Create hierarchies in non-parent child dimensions. Dimension [Customer] :...

Return a constant table from Inline-valued table

sql,sql-server,tsql,sql-server-2014
Is it possible to return a constant table from an IVT? Now i'm using a regular table, and function is just returning data from this table with several additional operations. So i want to return a table Value ----- 0,265433 0,0629412 0,671626 I tried something like this: ALTER FUNCTION [dbo].[GetEigenvector]...

SQL Job step to map a network drive to a specific letter

cmd,sql-server-2014,sql-job
I'm working on creating a SQL job and the first step of it I need to map to a network drive and make sure it always gets mapped to Z:. Problem is if the drive is already mapped it fails with the error saying "The local device name is already...

String being truncated even if nvarchar(max)

sql-server,sql-server-2014
I have the following: IF OBJECT_ID('tempdb..#Columns', N'U') IS NOT NULL DROP TABLE #Columns CREATE TABLE #Columns (column_name nvarchar(max)) DECLARE @TableName nvarchar(100) SET @TableName = 'mytable' INSERT INTO #Columns SELECT 'd.' + column_name + ' as ''' + column_name + '/old'', i.' + column_name + ' as ''' + column_name +...

Column Logic in SQL [closed]

sql,sql-server,bigdata,sql-server-2014
First time poster here! I would like peoples opinions here. I am collecting daily stock data for the past 10yrs (so approximately 2500 rows of data which is not significant), however I have over 200 stocks (will grow to 1000 over time possibly) with about 30 individual fields per stock....

How Do I do A Cummulative Subtraction in SQL Views?

sql-server,sql-server-2014
I have an SQL view that has two fields. I want to calculate reducing balance in weekly terms. TableID item_no Qty_shp Qty_Stk Balance posting_date 1 WTR234 28 500 472 2015/03/09 2 WTR234 42 472 430 2015/03/15 3 WTR234 100 500 400 2015/03/16 4 WTR234 50 400 350 2015/03/22 As shown...

High Availability Error “An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.”

high-availability,sql-server-2014
I have trouble in configuring High Availability, I searched a lot but I was unsuccessful in fixing it. I got this error : "An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'." I have 3 nodes in implementing AlwaysOn , One of them...

T-SQL How to pull previous row regardless of the entered parameters

sql,stored-procedures,reporting-services,parameters,sql-server-2014
I am working on a report that prints readmit patients' visits information. The T-SQL query prints the repeat visits based on an IIF statement (using LEAD and LAG) that looks to see if there is a visit within 30 days of the visit's admit date for the same patient -...

Colon Separated List in SQL Server (Like LISTAGG)

sql-server-2014
I am looking to produce an concatenated string in SQL Server from the following data that looks like the results below: Table A | Table B: | LOC Acct ID | ID SN ATL 60 1 | 1 100A ATL 60 2 | 2 300B DAL 61 3 | 3...

Transform row to column in sql

sql,sql-server-2014,unpivot
I have table like this: PC(code, model, speed, ram, hd, cd, price) when i run the query : select model,speed,ram,hd,cd,price from PC where code=(select max(code) from PC) i get the result as below: model speed ram hd cd price 1233 800 128 20.0 50x 970.0000 But i need the result...

Stopping SQL from creating databases if there was an error on script

database,error-handling,sql-server-2014
Situation: When you run a script and there's errors (even if it's just only one), SQL creates the database anyway. You run your script, some errors pop up, yet you see your database was created... but without certain tables, triggers, relations, so on. It creates only what you "did well"...

Function that returns multiple columns

sql,sql-server-2014,sql-function
I am creating a function that splits up strings. I want to pass the function to a string and get several rows back (the amount of rows would be dynamic depending on the length of the string). I was thinking of possibly using a table function and joining this to...

SQL update column depending on other values in same column

sql,sql-server,sql-server-2014
I have a table similar to this: Index Name Type -------------------------------- 1 'Apple' 'Fruit' 2 'Carrot' 'Vegetable' 3 'Orange' 'Fruit' 3 'Mango' 'Fruit' 4 'Potato' 'Vegetable' and would like to change it to this: Index Name Type -------------------------------- 1 'Apple' 'Fruit 1' 2 'Carrot' 'Vegetable 1' 3 'Orange' 'Fruit 2'...

Add foreign key constraint to existing table with data in a Visual Studio database project

sql-server-2014,database-project
I have a Visual Studio database project hitting a SQL Server 2014 database. I need to create a child table for an existing parent table. The parent already has the foreign key column and I need to add the constraint to the new parent table (the FK is on the...

How to insert Data into sql server database

c#,sql,asp.net,sql-server,sql-server-2014
I'm trying to insert data into my SQL Server 2014 database, but I get an error Incorrect syntax near ')'. My table matches the types of data I put in, for example, I'm put an int into a int. Here is my code: string ip = Request.UserHostAddress; string name =...

OpenQuery and using dynamic SQL

sql,sql-server,sql-server-2014
We are trying to migrate to SQL Server 2014. There are several cases where we need to use OpenQuery for returning dynamic results. Result Sets will be different according to different input parameters. It was working in sql 2008R2 and I need to keep it working But there is the...

Select average of same days of past years

sql-server,sql-server-2014
I have a database with sales records of the type: DAY PRODUCT1 PRODUCT2 PRODUCT3 TOTAL date1(date) salesnumber(int) salesnumber(int) salesnumber(int) sum(int) date2(date) salesnumber(int) salesnumber(int) salesnumber(int) sum(int) ... ... ... ... ... It goes back to 7 years. Now I need to get a sales average of each of my 3 products...