How to Programming with Sql-server-2014

sql-server,sql-injection,sql-server-2014

I guess the MSDN has mentioned at the top itself that this applies to the 2008 and higher versions. So it would be 5000 for 2014 version as well. Applies to: SQL Server 2008 R2 and higher versions. From the MSDN When the Database Engine checks for possible escalations at...

Preventing SQL injection in a report generator with custom formulas

not really - many injections involve comments (to comment out the rest of the regulare statment) so you could check for comments (-- and /*) and the ; sign (end of statment). On the other side if you allow your users to put anything into the filters - why should...

SQL Server 2014 Lookup value in Table2 between columns

sql,inner-join,sql-server-2014

SELECT A.*, CASE WHEN EXISTS (SELECT 1 FROM TimeZoneDST B WHERE A.StartDuty between B.DSTstart and B.DSTend) THEN 1 ELSE 0 END as DSTexists FROM DutyList A; ...

T-SQL UPDATE statement to efficiently extend a DateTime2 value a number of days

sql,sql-server,tsql,sql-server-2014

update your_table set ExpirationUtc = dateadd(day, 90, ExpirationUtc) ...

Selecting data that could reside in many databases

sql,sql-server,sql-server-2012,sql-server-2014

While this type of partitioning might not be the best, maybe you're stuck with it. The basic dynamic SQL to do what you want isn't that hard though: declare @sql nvarchar(max) select @sql = N' select o.orderid, o.customerid, s.supplierid, p.productid, p.productname from [order] o join Supplier s on o.SuppplierID =...

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

excel,reporting-services,sql-server-2014

Found the solution to this problem so I figured I'd answer it here in case anyone else runs into this problem. the list of supported formats for URL access listed on Microsoft's website here shows the following list: HTML4.0, MHTML, IMAGE, EXCEL, WORD, CSV, PDF, XML, and NULL It does...

SQL bypass REPLACE by CASE statement

sql-server,tsql,replace,case,sql-server-2014

It is obvious that '' means blank (Not null) and it exists between two consecutive letters also. (As it is blank) So Sql can not go on replacing that blank in between every letter with the string you want. That's why you can not replace '' Check this query SELECT...

BCP utility incorrect syntax

sql-server,azure,bcp,sql-server-2014

Like SQLCMD, BCP is a stand-alone command-prompt utility that must be invoked from a command prompt. If the source text and format files reside on your client, you'll need to add the -S -U and -P parameters like you do with SQLCMD. For example: bcp databasename.schemaname.transitschedule in calendar_dates.txt -f calenar_dates.fmt...

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

c#,asp.net,sql-server-2014

The value "cnInvestTracker" itself isn't a valid connection string. Which is what you're trying to use here: new SqlConnection("cnInvestTracker") That constructor doesn't want the name of a connection string, it wants the connection string itself: new SqlConnection(ConfigurationManager.ConnectionStrings["cnInvestTracker"].ConnectionString) (You may have to add a reference to System.Configuration, and you may want...

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

Your trigger in Oracle is FOR EACH ROW. This type of triggers is not directly supported by SQL Server. So SSMA applies a template replacement for them using INSTEAD OF trigger and loop over inserted. Can you enable ROWID at least for tables with triggers (option "Add ROWID column for...

Cannot login with new SQL User - SQL 2014

sql,authentication,sql-server-2014

For testing purposes only does authentication pass for user BI_USER if the users SERVER ROLE is edited to give it the sysadmin privilege. If the answer is yes remove the sysadmin privilege and attempt to isolate which privilege is required to authenticate. What OS is SQL 2014 hosted on out...

ServiceStack taking a long time to execute stored procedure

c#,sql,servicestack,ormlite-servicestack,sql-server-2014

Adding an additional answer because really there were two issues. The real root seems to be with the stored procedure. I don't know why it wasn't consistently causing problems, but I rebuilt it using dynamic SQL to only include WHERE filters for parameters that are present. Originally, I had something...

Time data type not available in SQL Server Express 2014

sql,sql-server,sql-server-2008,sql-server-2014

Compatability level 90 = 2005. 100 = 2008, 110 = 2012, 120 = 2014. If your getting 90 back, it's set to not support data types introduced in and after 2008

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

SELECT * FROM sys.dm_db_persisted_sku_features; This will show you what features you have enabled that need to be disabled. I would restore a backup to an enterprise instance, and then begin to work from there to disable enterprise features After the above query returns no results you should be fine....

SQL RESTORE WITH RECOVERY; Hangs at 100%

sql-server,restore,sql-server-2014

As it turns out the issue was environmentally related and pretty straight forward: First of all, I was attempting to back up from a non-shrunk back up version of the origin database. The size of the log files was actually a known issue, hence we usually back up from a...

Data streams in case of Merge

sql-server,ssis,sql-server-2012,sql-azure,sql-server-2014

The answer to your questions a, b and c (if you're using SSIS transformation components in SSIS) is essentially “yes, all new data and existing data required for transformation will flow into SSIS instance, and the resulting merged data will flow out of SSIS instance to the target server”. More...

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

sql-server,tsql,sql-server-2014,window-functions

In your example 33883.50 * -1 / 1.05 is evaluated as 33883.50 * (-1 / 1.05) instead of (33883.50 * -1) / 1.05 which results in a loss in precision. I played a bit with it. I used SQL Sentry Plan Explorer to see the details of how SQL Server...

T-SQL distribute a value between rows without a cursor

Try this: create table o (id int, priority int, qty int) insert into o (id, priority, qty) values (1, 1, 10), (2, 2, 30), (3, 3, 20), (4, 4, 5), (5, 5, 1), (6, 6, 10) with cte1 as(select *, row_number() over(order by priority) as rn from o), cte2 as(...

How to set yearly jobs in Sql Server Agent

c#,asp.net,tsql,sql-server-2014

On the first day of every 6 months starting from January: ...

using multi-value parameter with delimited string in dataset

sql,reporting-services,sql-server-2014

Please try the following SQL Select statement /* create table valueslisttbl (id smallint, name varchar(20), valueslist varchar(1000)) insert into valueslisttbl values (1, 'John', '46423423,36456325,76473234,98798796'), (2, 'Mike', '36456325,98798796'), (3, 'Sara', '46423423') */ declare @chosenvalues varchar(100) --set @chosenvalues = '46423423' --set @chosenvalues = '36456325' --set @chosenvalues = '76473234' set @chosenvalues = '46423423,98798796'...

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

.net,vb.net,visual-studio-2013,image-uploading,sql-server-2014

If you only want to allow 5 image files, there is no need to bother making thumbnails before testing the number of files. Since FileDialog.FileNames Property returns an array of strings, the first thing you want to do after testing that the DialogResult is Ok, is test the length of...

How to change date-time format?

sql-server,date,datetime,sql-server-2014

You need to determine the format of the value you are converting before you can convert it. If it's simply between those two formats, you can simply search for - to determine it's format. I would also suggest storing the value in a datetime column as opposed to a varchar,...

Silent SQL Server 2014 installation fails with Access Denied on tempfile

For anyone who runs into something similar, what I ended up doing is programmatically writing a simple batch file to kick off the installer, creating a 'run once and delete' task, and then waiting for it to finish. I had admin privileges, so I just had the task run under...

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

sql-server,sql-update,foreign-keys,primary-key,sql-server-2014

There are two parts to your question. The features of SQL Server Developer Edition are identical to the features in Enterprise. The only difference is Developer edition is not license for production use. In addition, the SQL Server Developer software isn't technically licensed, each developer that uses the software must...

Update even though using constraints

Enable update cascade on the foreign key ALTER TABLE t_Table_Language DROP CONSTRAINT FK_t_Table_Language_t_Table ALTER TABLE t_Table_Language ADD CONSTRAINT FK_t_Table_Language_t_Table FOREIGN KEY (funID) REFERENCES t_Table(funID) ON UPDATE CASCADE EDIT: Or the other way around, i'm not sure which table has the foreing key ALTER TABLE t_Table DROP CONSTRAINT FK_t_Table_Language_t_Table ALTER TABLE...

Send CSV file with db_sendmail - missing records

sql-server,csv,sql-server-2014,sp-send-dbmail

Found the answer- it's actually a bug on 2014, please see: https://connect.microsoft.com/SQLServer/feedback/details/850260/sql-2014-sp-send-dbmail-64kb-query-result-limit

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

visual-studio-2013,database-project,sql-server-2014

Add a new item to your project of type Schema (listed under SQL Server > Security). For the objects that belong to this schema, add them as you normally would to your project. They will default to the dbo schema so you will have to change that in each object's...

Select average of same days of past years

c#,sql,asp.net,sql-server,sql-server-2014

If I understand what you want is the average of the sales made on the same date for the last five years for each product. If so I think this might be what you want: select s.day, oa.* from sales s outer apply ( select avg(product1*1.0) p1, avg(product2*1.0) p2, avg(product3*1.0)...

How to insert Data into sql server database

Your parameter names can not have brackets or spaces in them in SQL Server. So rename them all to @SignNumber, @redquantity, @bluequantity... etc.

Colon Separated List in SQL Server (Like LISTAGG)

sql-server,modulo,sql-server-2014

You don't need the GROUP BY. You do need a WHERE condition in the subquery to correlate the Acct value from the row of the outer query with the subset of values you want to concatenate in the inner query. You will need to add the DISTINCT after the...

Run multiple queries each selecting each respective Nth row?

I figured it out Given 20 rows and 4 iterations: Rows 1,5,9,13,17: SELECT ID, Whatever FROM MyTable WHERE ID % 4 = 1 Rows 2,6,10,14,18: SELECT ID, Whatever FROM MyTable WHERE ID % 4 = 2 Rows 3,7,11,15,19: SELECT ID, Whatever FROM MyTable WHERE ID % 4 = 3 Rows...

Arithmetic overflow HOLAP/ROLAP but not MOLAP

ssis,ssas,oledb,olap,sql-server-2014

MOLAP: Data is preprocessed and stored in cube. So whenever an MDX query runs, it picks up aggregated data directly from cube. The complexity of MDX has no effect on the relational source of data underneath. Bottom line: It is fast and independent of data source(once the cube is processed)....

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

sql-server-2014,database-project

This should not be an issue. By default a database project creates constraints with the NoCheck option - meaning SQL Server will not validate the data. Then, after postDeploy it will check the constraint. Take a look at you deployment script and it will validate this.

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

I asked a similar question over on the Microsoft forum and was told that DBCC DBREINDEX is not supported on 2014. I find this odd, since it works... sometimes. But, I can't discount language barrier. And, each time I have tried a similar statement: ALTER INDEX ALL ON QST2D REBUILD;...

How to show results similar to countif in SQL Server 2014

sql-server,query-performance,sql-server-2014

Do it with window function: select Fruits, count(*) over(partition by Fruits) as Counts from TableName ...

Slow query with WHERE PK IN (…)

Here what I'd do to optimize: Create a NON CLUSTERED index on pk2 column. Create a NON CLUSTERED index on field1 column. Rebuild/Reorganize indexes and statistics. Use a JOIN instead of IN. Use the OPTIMIZE FOR query hint. So, try this: DECLARE @C INT = 101; SELECT ID, COUNT(*) OVER...

In Where clause add check if Parameter is empty string

sql,select,conditional-statements,sql-server-2014

I think it is a or condition you need: SELECT a.Account_ID FROM Accounts a WHERE ((@FirstName='' and a.FirstName is null) or a.FirstName = @FirstName) /*Add check if @FirstName = '' then a.FirstName IS NULL */ AND a.LastName = @LastName AND a.Middle = @MiddleName AND a.Email = @Email AND a.Company =...

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

cmd,sql-server-2014,sql-job

Try checking if the drive is not mapped first: cmd.exe /c if not exist z:\* net use z: \\drivelocation DriveAccessCode...

How Do I do A Cummulative Subtraction in SQL Views?

sql,sql-server,bigdata,sql-server-2014

This can be solved using the aggregate function sum() with an over () clause. In the query I only partitioned the data based on week, but maybe you want to include the item_no as part of the partitioning too if there are multiple products. I think this query should give...

Column Logic in SQL [closed]

I don't understand why you have so many different columns. You should have one row per stock per time unit. I would envision a structure with a few dozen columns: Stock Identifier Date (or date/time) 30 or so measures per stock You seem to want to store things in columns...

defaults using Getdate and DateAdd are not working

sql,date,where-clause,sql-server-2014

You may find the handling of defaults a bit counter-intuitive in SQL Server. The syntax is: DEFAULT constant_expression It so happens that SQL Server extends the definition of constant_expression to include non-deterministic scalar functions, such as getdate(). These are functions that return a different value each time they are called,...

Last 13 full months SQL WHERE clause

Use this dates: SELECT EOMONTH(DATEADD(mm, -1, GETDATE())) SELECT DATEADD(dd, 1, EOMONTH(DATEADD(mm, -14, GETDATE()))) So you where clause would look like: WHERE dbo.ub_contact.contact_dt BETWEEN DATEADD(dd, 1, EOMONTH(DATEADD(mm, -14, GETDATE()))) AND EOMONTH(DATEADD(mm, -1, GETDATE())) ...

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

sql-server,database,sql-server-2014

Martin is right - use MERGE for this. You could do it all in one statement if you didn't have two row entries to INSERT - as such you could dump to a temp table and do the following: CREATE TABLE #answers (UserTestID INT, QuestionID1 INT, AnswerID1 INT, QuestionID2 INT,...

Azure virtual machine cost and pricing questions

iis,asp.net-mvc-5,sql-server-2014,azure-virtual-machine

Here are the details on licensing: http://azure.microsoft.com/en-us/pricing/licensing-faq/ Inbound data is not charged, but egress is charged. Pricing for egress is located here: http://azure.microsoft.com/en-us/pricing/details/data-transfers/...

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

sql-server,full-text-search,sql-server-2014

The answer is to make sure you have an empty stop list associated with the index. It isn't enough to simply have no stop list. CREATE FULLTEXT STOPLIST [EmptyStopList] ; GO ALTER FULLTEXT INDEX ON MyTable SET STOPLIST [EmptyStopList] ...

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

sql,visual-studio-2013,ssis,sql-server-2014

I have an answer for that one actually. You will need to set your connection string as an expression and either hard code or parametrize your user and password. The password is encrypted based on information from the machine that it is encrypted on. Once you move that encrypted property...

SQL Server integration services deployment wizard port

ssis,sql-server-2014

This link should give you everything you need about setting up your firewall to work with SSIS and its service. The port it uses seems to be port 135 Configure a Windows Firewall for Access to the SSIS Service...

Parsing Dates in SQL

sql,sql-server-2012,sql-server-2014

SQL Server 2012 onwards, you can use TRY_PARSE: -- If the culture argument isn't provided, the language of current session is used. SELECT TRY_PARSE('Thu, 4 Jul 2013 09:18:24' AS datetime2) AS 'datetime2'; TRY_PARSE: Returns the result of the expression, translated to the requested data type, or null if the cast...

SQL Server check if where clause is true for any row

sql,optimization,sql-server-2014,spatial-query

You want to use exists: SELECT pt.* FROM ProvinceTable pt WHERE EXISTS (SELECT 1 FROM RailroadTable rt WHERE pt.Shape.STIntersects(rt.Shape) = 1 ); ...

How to group by SQL based on certain conditions?

sql,sql-server,select,distinct,sql-server-2014

Gaps and Islands DECLARE @TVWatchingTime TABLE (PersonId int, StartTime int, EndTime int) INSERT INTO @TVWatchingTime VALUES (1, 300, 600), (1, 250, 700), (1, 200, 800), (1, 900, 1200), (2, 100, 200) ;WITH cteSource(PersonId, StartTime, EndTime) AS ( SELECT s.PersonId, s.StartTime, e.EndTime FROM ( SELECT PersonId, StartTime, ROW_NUMBER() OVER (ORDER BY...

SQL select distinct by 2 or more columns

This is exactly what the distinct keyword is for: SELECT distinct col1, col2, col3 FROM mytable ...

sys.columns shows details but table missing from sys.tables

sql,sql-server,tsql,sql-server-2014

It means that it is not a table, but either a View, or a Table-Valued Function, or one of several other types of objects. Use this instead: SELECT * FROM sys.objects WHERE [object_id] = 584895884; Please note that the [object_id] field is a number, not a string, so shouldn't be...

Return a constant table from Inline-valued table

You are messing up the syntax for giving an alias to your column: ALTER FUNCTION [dbo].[GetEigenvector] ( ) RETURNS TABLE AS RETURN ( SELECT 0.265433 AS Value UNION ALL SELECT 0.0629412 UNION ALL SELECT 0.671626 ) ...

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

sql-server,database,sql-server-2014

As per comments, First you need to install an instance of SQL Server if you don't already have one - https://msdn.microsoft.com/en-us/library/ms143219.aspx Once this is installed you must connect to this instance (server) and then you can create a database here - https://msdn.microsoft.com/en-US/library/ms186312.aspx...

SQL Case When - delete 'empty' rows from output

sql,row,sql-server-2014,case-when

Use the original query as a derived table, then MAX the columns: select MAX('2010'), ... from ( SELECT case when Year(LOAN_START_DATE) = 2010 then max(LOAN_RES_BANK_CODE) else 0 end as '2010', ... ) ...

How to write multiple if case then else situation as a single case

sql,sql-server,tsql,case,sql-server-2014

Wouldn't this work? IF len(@cl_CrawlSource) > 2 UPDATE tblCrawlUrls set cl_LastCrawlDate = SYSUTCDATETIME() ,cl_TotalCrawlTimes = @cl_TotalCrawlTimes ,cl_Ignored_By_Containing_Word = @cl_Ignored_By_Containing_Word ,cl_PageProcessed = 0 ,cl_CertainlyNotProductPage = @cl_CertainlyNotProductPage ,cl_CrawlSource = @cl_CrawlSource ,cl_FoundLinksCount = @cl_FoundLinksCount ,cl_DuplicateUrl_ByCanonical = @cl_DuplicateUrl_ByCanonical where cl_CrawlNormalizedUrl = @cl_CrawlNormalizedUrl Unless I'm missing something, every case statement has the...

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

sql-server,sql-server-2014,in-memory,alwayson

I believe that the exact opposite is the case. The transaction record is a logical transaction that describes the transaction rather than all of the modifications to the indexes that go along with a non In-Memory table. The log contains the logical effects of committed transactions sufficient to redo the...

How to use union all when using order by clause SQL Server 2014

sql,sql-server,tsql,union,sql-server-2014

Copy the query results into temp table and use Union all SELECT TOP 20 cl_RooSiteId, cl_CrawlOrgUrl,cl_LastCrawlDate INTO #Temp1 FROM tblCrawlUrls WHERE Sysutcdatetime() > Dateadd(MINUTE, 50000, cl_LastCrawlDate) AND cl_DuplicateUrl_ByCanonical = 0 AND cl_RooSiteId = 1 ORDER BY cl_LastCrawlDate ASC SELECT TOP 200 cl_RooSiteId, cl_CrawlOrgUrl,cl_LastCrawlDate INTO #temp2 FROM tblCrawlUrls WHERE Sysutcdatetime() >...

Default random 10 character string value for SQL Server column

sql-server,random,default-value,sql-server-2014

Expanding on what Deigo Garbar has already suggested. If you want to use the expression as the default value in your table you would need to put this expression in a function. But the problem is a UDF function will not allow you to use NEWID() function in it, it...

Finding duplicate number in couple SQL Server tables in c#

c#,database,sql-server-2014

You can send the single request to SQL server instead N requests, where N is the number of your tables, using the UNION clause. You need some like this request: SELECT SUM(C) FROM ( SELECT COUNT(*) AS C FROM {Table1} WHERE IMEI = @IMEI UNION SELECT COUNT(*) AS C FROM...

SQL Server procedure optimization testing pattern improvement

sql-server,testing,sql-server-2014

you can use this query SELECT total_elapsed_time FROM sys.dm_exec_query_stats WHERE sql_handle in (SELECT most_recent_sql_handle FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) WHERE session_id = (@@spid)) ...

SqlServer db mirror failover with EF6 fail Principle to Mirror only

c#,sql-server,entity-framework,sql-server-2014,database-mirroring

So as it turns out, the client is joining from a different lan so the failover partner registered on the sql server is not resolvable on the client. SELECT DB_NAME(database_id) AS 'DatabaseName', mirroring_partner_instance FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL; Will give you the "name" of the failover partner when...

SQL script working on SQL Fiddle, but not in MS SQL Server 2014

sql,sql-server,tsql,sql-server-2014

You are using Mysql syntax on sql server. You could do if exists as below in sql server: IF OBJECT_ID('dbo.City', 'U') IS NOT NULL DROP TABLE dbo.City Also remove back ticks and remove engine type etc from create table. CREATE TABLE City ( ID int IDENTITY PRIMARY KEY, Name char(35)...

Convert SQL Server Date to mm-yyyy

You can use FORMAT function, available from SQL Server 2012 onwards: DECLARE @myDate DATETIME = '2012-04-20 05:54:59' SELECT FORMAT(@myDate, 'MM-yyyy') Output: 04-2012 ...

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

sql,stored-procedures,reporting-services,parameters,sql-server-2014

Try this: SELECT TOP 2 UnitNumber, Patient, VisitID, AdmitDate, DischargeDate, PrevDisDate, DaysBtwnVisits FROM #tblTempCalculations AS T1 WHERE T1.ReadmittedFilter = 1 AND T1.UnitNumber IN (SELECT UnitNumber FROM #tblTempCalculations AS T2 WHERE DischargeDate BETWEEN @FromDischargeDate AND @ThroughDischargeDate) ORDER BY DischargeDate DESC ...

Smooth way to store enum to a DB

c#,sql,database,enums,sql-server-2014

You can cast enums to ints and vice versa, this is very fast. You can also convert between enums and strings, takes a bit more code, time, and storage. You can then trivially store ints or strings in your database.

Running composite query on SQL server 2014 does not return result set

java,sql,sql-server,sql-server-2014

I just changed the driver to JTD as follows and it worked like a charm Connection conn = null; String dbURL = "jdbc:jtds:sqlserver://DataBase Name"; String user = "username"; String pass = "password"; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(dbURL, user, pass); JDBCTest jt = new JDBCTest(); String sql = "update Table1...

How to schedule automatic increment for a column on a particular date

c#,asp.net,visual-studio-2013,sql-server-2014

You have some options: SQL Agent Jobs - you can run a particular stored procedure at designated date or repeatedly if configured so. See http://msdn.microsoft.com/en-IN/library/ms189237.aspx ASP.NET package called "Hangfire" available at Hangfire.io - it is well documented and available over NuGET. There are many others, but these two are quite...

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

sql-server,tsql,sql-server-2014

Don't use ntile. Use this: SELECT COUNT(*) cnt, DATALENGTH(Description)/1000 grp, CAST(DATALENGTH(Description)/1000 * 1000 as varchar(10)) +'-'+ CAST((DATALENGTH(Description)/1000 + 1) * 1000 as varchar(10)) Interval FROM @t GROUP BY DATALENGTH(Description)/1000 ...

Could not load file or assembly Microsoft.SqlServer.TransactSql

sql-server,azure,sql-azure,ssdt,sql-server-2014

This happened to me also and just fixed. I update ssdt from the link i get from Visual studio notification center and restart VS as administrator then it works fine. -Janak

GROUP BY with HAVING clause does not return a row?

sql,sql-server,join,sql-server-2014

Simply, because you are Grouping by d.categoryid - And there is no grouping by this column, which would return 0 for sum(datavalue): http://sqlfiddle.com/#!6/5e44cb/4 (it's 89) If you could add your expected output to your post, we might be able to help you with the query you need....

joining two tables by value of one column and calculating

Try this: SELECT A.sifKorisnikPK, IsNull(BrojDobrih,0) BrojDobrih, IsNull(BrojLosih,0) BrojLosih FROM (select distinct sifKorisnikPK from Rezervacija) A LEFT JOIN #LosaRez B ON A.sifKorisnikPK = B.sifKorisnikPK LEFT JOIN #DobraRez C ON A.sifKorisnikPK = C.sifKorisnikPK ORDER BY (IsNull(BrojDobrih,0) - IsNull(BrojLosih,0)) ...

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

sql-server,sql-server-2008-r2,sql-server-2014,service-broker

Yes. All SQL Server releases, from 2005 to 2014 inclusive, are compatible with each other at the Service Broker layer. In fact the 2008 instances are not even going to be able to figure out they are talking to 2014. You should be able to migrate one machine at a...

SQL Server 2014 Migration

Backup and restoring a database will leave any indexes in the same state as they were prior to your move. Rebuilding them will only help if they are a) fragmented and b) truly the cause of the slowdown you are seeing, but its more likely that your new hardware or...

OpenQuery and using dynamic SQL

sql-server,tsql,common-table-expression,sql-server-2014

There is a work around, but you're not going to like it. You need to create a wrapper procedure, that will define the meta data and execute the orginal procedure based on what columns are supplied to the wrapper. Here is a Link to a MSDN blog where they discuss...

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

You don't need to use recursion for this if you are using a modern version of SQL Server that can do aggregation as window functions. Then you can do sum() as a window function: update t1 set total = y.total from (select *, rn = ROW_NUMBER() over (order by id)...

String being truncated even if nvarchar(max)

sql,sql-server-2014,sql-function

Does PRINT len(@string) show the correct length? If so the PRINT @string is the problem. The print does only output up to 4000 chars. Try SELECT @string in order to output the result as a row which is not truncated....

Function that returns multiple columns

I am simply wondering if this is even possible in SQL Server 2014 Yes, you can create table-valued user defined functions that return, well, a table value. So the string 'ABC' would be returned as: COL1 COL2 COL3 A B C Well, now you're in trouble - table-valued functions...

Trigger - Difference in FOR/AFTER/INSTEAD OF

high-availability,sql-server-2014

There are Two types of Triggers, Instead Of Triggers that fires Instead of the Triggering action After or For Triggers that fires after the Triggering action. Catalog View sys.triggers's column is_instead_of_trigger will tell you whether is an Instead of trigger or not, if value 0 then it is For or...

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

I found it. my problem was installed same instance name.

How do I show balance log in a gridview

c#,asp.net,visual-studio-2013,sql-server-2014

As per my understanding of your query, I have been shared my suggestion below : 1.My point of view ,you can get your expected output by using stored procedure in Database. 2.Because Hopefully You stored the values of employee information, leave type ,leave details are into separate table with the...

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

Usually, it is considered a good practice to free up resource as long as you don't need it anymore. So I'd add DROP TABLE at the end of stored procedure. Temporary table lives as long as connection lives. Usually, applications use connection pooling (it is configurable) and connection doesn't close...

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

There is no other solution than re-factoring your code if you wish to upgrade to SQL2014. The example below demonstrates that setting the compatibility level to 2008 does not resolve this error. You will have to modify all your stored procedures and views. ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100...

SQL - Nested Select Statements with Max?

database,error-handling,sql-server-2014

If you just want the order number, you can use MAX again since you know the post date will also be the MAX due to your subselect: SELECT MAX(OrderNo), MAX(PostDate) FROM table WHERE PostDate = (SELECT MAX(PostDate) FROM table) ...

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

Yes, there is a simple solution. It's called transaction management: SET XACT_ABORT ON BEGIN TRANSACTION -- Do your things COMMIT TRANSACTION If anything fails, the whole transaction will be rolled back, as if it never existed....

Reuse field values in WHERE clause

tsql,where,sql-server-2014

Doing this would only run your function once on every row instead of twice: SELECT * FROM ( SELECT dbo.heavyFunctionCall(a, b, c) AS x FROM T) a WHERE x > 10 ...

Transform row to column in sql

sql,sql-server-2014,unpivot

If you consider this table and these test values: DECLARE @tbl TABLE(model INT,speed INT,ram INT,hd FLOAT,cd VARCHAR(10),price FLOAT) INSERT INTO @tbl VALUES (1233,800,128,20.0,'50x',970.0000) You could do something like this: SELECT unpvt.chr, unpvt.value FROM ( SELECT CAST(model AS VARCHAR(10)) AS model, CAST(speed AS VARCHAR(10)) AS speed, CAST(ram AS VARCHAR(10)) AS ram,...

SQL update column depending on other values in same column

sql-server,sql-server-2014,in-memory

You can run update with join to get row_number() within [type] group for each row and then concatenate this values with [type] using [index] as glue column: update t1 set t1.[type] = t1.[type] + ' ' + cast(t2.[rn] as varchar(3)) from [tbl] t1 join ( select [index] , row_number() over...

IN Memory Oltp hash index vs non clustered

The "non-clustered" indexes you're referring to are actually Range indexes. Both Hash and Range indexes are non-clustered, and there are no "clustered" indexes in in-memory OLTP tables (the primary key is forcibly implemented as a clustered hash index). Range indexes are implemented via modified B-trees and you can read more...

when can you join a varchar to an nvarchar

sql-server-2008-r2,compatibility,sql-server-2014

In general, joining varchar and nvarchar columns 'just works', with the following important caveats that generally boil down to "...but you shouldn't do it if you can avoid it": 1) Mismatching varchar/nvarchar in queries and joins can cause huge performance issues. Because the database has to basically perform a function...

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

Going forward with MS SQL backups has never been an issue. 2005 backup restored on 2008, no problem. Going backwards won't work. 2008 backup, restoring onto 2005, won't work....

Count records by month & type

You can do it using pivot, with something like this: SELECT OccuredDate, [1], [2], [3], [4] FROM ( SELECT OccuredDate, TypeID FROM Table1) AS SourceTable PIVOT ( count(TypeID) FOR TypeID IN ([1], [2], [3], [4]) ) AS PivotTable And per month version: SELECT DATEADD(month, DATEDIFF(month, 0, OccuredDate), 0) as Month,...

Partial transpose of rows to columns

sql,tsql,sql-server-2014

OK, with your help, and a help from a friend, i solved the problem like this. Select ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY Field1) AS order_num, Field1, Value into #tab1 from Source_Table Order by Field1 Select * from #tab1 PIVOT (Max(Value) FOR order_num IN ([1], [2], [3], [4], [5]))...

Using case in SQL server