Tag Info

New answers tagged

0

Ok, got it. I had to force the TCP1433 port in SQL Configuration manager... That was only that. I found it when trying to connect with a wrong password : SQLServer denied connection because of this wrong pass. So, the mistake was between the listener and the server supposed to accept the connection... which was the second one, because of the read-only ...


1

Low tech, but... select col1 + ',' + cast(col2 as varchar(10)) + ',' + col3 from mytable;


0

From your examples, the statement below should do what you want to do. It's not fast as it involves casting ID to a string and manipulating it, but I think this is the only thing that will work for you. UPDATE TEST SET ID = CAST(LEFT(CAST(ID AS VARCHAR(20)), LEN(CAST(ID AS VARCHAR(20)) - 1)) + '1' + RIGHT(CAST(ID AS VARCHAR(20)), 1) AS INT) If ID is a ...


5

Right so the value is probably a date and is preserved as a date when it gets to SSRS. SSRS is applying it's own formatting to a date. If you don't like that format, apply the correct specifier. http://thavash.wordpress.com/2007/10/02/date-formatting-in-reporting-services-list-of-format-codes/ ...


-1

The date format is based on the language setting SELECT @@language If you query the sys.syslanguages table you can see the date format for each language. For example us_english is mdy and british is dmy. The language can be changed using the [SET LANGUAGE][1] command. SET LANGUAGE 'us_english'


2

It is not possible to have an INCLUDE column for an index that enforces a constraint. This has been brought up, but marked as "won't fix". If the benefits of having a covering index are that great, consider revising the indexing strategy that you currently have on that table to maximize query performance.


1

How do we add an INCLUDE in an index which is automatically created (through CONSTRAINTS)? Answer: INCLUDE (column [ ,... n ] ) represents non key column, Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and non-key columns. Check Create Index Arguments--> Include and Create Indexes with Included Columns ...


3

If I understand correctly, and you want to insert 10 values of the 2nd table for every iteration of the cursor (or cte), you can use this: ; WITH myCTE (value) AS ( SELECT value FROM sometable ) INSERT INTO @TOP10 SELECT t.Value1, t.Value2, t.Value3, t.Value4 FROM myCTE AS mc CROSS APPLY ( SELECT TOP (10) ...


2

Moving the calculation to the view won't change performance - it's still done at runtime. So all you've changed is the complexity of the outer query. In order to change performance you would need to add the computed column to the table, and persist it or index it. This means the work of the calculation is done at insert/update time instead of query time. ...


10

Take a look at the plans. When you use SELECT * it probably uses the clustered index, and when you only want one column, maybe there is a skinnier index to use. Don't "expect" a certain order. If you don't tell SQL Server how to order, then it will use the most efficient way possible, and this can change due to probably more than 20 factors. If you want a ...


1

RegEx is the only serious way to do this. Here's a simple example using the RegEx functions that come installed for free if you have Master Data Services installed. Otherwise google around for SQL Server CLR RegEx, the expression used should be transferrable: -- Find and replace commas in quotes USE MDS GO DECLARE @t TABLE( yourText VARCHAR(MAX), newText ...


2

Well, this is really awful, but okay, if you are going to refuse to even consider better alternatives... first, create a set-based split function that will track the order of the string (your looping function is really not optimal): CREATE FUNCTION [dbo].[SplitStrings_Ordered] ( @List VARCHAR(8000), @Delimiter VARCHAR(255) ) RETURNS TABLE AS ...


0

Well, that's all nice and dandy, but while R and Ř should be treated differently while sorting, they should be considered the same when comparing "accent insensitively". Dvořák should be sorted after Dvorak and even "dobře" after "dobrý", it makes no sense whatsoever no to find "Dvořák" when searching for "Dvorak" ignoring accents. As it is, the accent ...


4

I don't think it ever is (unless you are stuck in SQL Server 2000 and are looking for pity - don't worry, you'll have it). I would opt for statement-level OPTION (RECOMPILE) every time. I'll quote the relevant bit from Paul White's blog post, Parameter Sniffing, Embedding, and the RECOMPILE Options: When a parameter-sensitivity problem is encountered, a ...


1

If I understand your question correctly, this query should give you the most recent status of each order. It's crude, but it should work: SELECT t.ID, t.ordernumber, t.[status], t.[date] AS orderdate FROM YourTable t GROUP BY t.ordernumber, t.[status], t.[date], t.ID HAVING t.[date] = (SELECT MAX([date]) FROM YourTable WHERE ordernumber = t.ordernumber) ...


4

You can use ROW_NUMBER() and OVER clause to "group" (partition) the rows per ordernumber, then order them by date (descending) and keep only the first rows (WHERE rn = 1): WITH cte AS ( SELECT id, ordernumber, [date], status, rn = ROW_NUMBER() OVER (PARTITION BY ordernumber ORDER BY date DESC) FROM ...


0

It think what you are after is a custom sort order? You can do this using a case statement to add an extra field, then order by it. Something like: SELECT ID, ORDERNUMBER, DATE, STATUS, CASE WHEN ORDERNUMBER = 1 OR ORDERNUMBER = 4 THEN 1 WHEN STATUS = 'Active' AND /*UNKNOWN COLUMN*/ = 'Current' THEN 2 ... END as SortOrder FROM *YourTable* ORDER BY ...


0

Given the way this question has changed, it is difficult to determine exactly what you need. The current data and query in the question do not match the result you have posted. That said, assuming the data is the way I think it is, this should provide the result you want INSERT INTO T3 (name,number,fin) SELECT T1.name, T1.Rollno as number, ...


0

this structure is incorrect, use M:N relationship with 3 Table Student Table StudentId FirstName LastName 1 John Sampo 2 Ann Rolls 3 Martin Blake School Table SchoolId SchoolName 1 ABC Language ...


3

One thing to remember is that an ID does not have to be generated by the IDENTITY property. Using IDENTITY has challenges, since as soon as rows are moved from the standby server to the current server (using IDENTITY_INSERT of course) with an ID value higher than the current identity value it becomes the new current identity value. Which can complicate ...


6

You could do this by running something like declare @newid int; select @newid = IDENT_CURRENT('YourTable') + 2000; dbcc checkident('YourTable', reseed, @newid); This would reseed your table identity to be 2000 higher than it currently is and seems like it should resolve what you are looking to do.


3

This will do it: DECLARE @d DATE = '20140502', @dm TINYINT = 26; DECLARE @Start_Of_Fiscal_Month DATE SET @Start_Of_Fiscal_Month = DATEADD(DAY, @dm-1, DATEADD(MONTH, -1 + DATEPART(dd, @d)/26, DATEADD(DAY, 1-DAY(@d), @d))); SELECT @Start_Of_Fiscal_Month SELECT DATEDIFF(dd, @Start_Of_Fiscal_Month, @d) + 1


0

CREATE FUNCTION [dbo].[fnSplit] ( @sInputList VARCHAR(8000), -- List of delimited items @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items ) RETURNS @List TABLE (colData VARCHAR(8000)) BEGIN DECLARE @sItem VARCHAR(8000) WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0 BEGIN SELECT ...


1

This should return tag the records that need attention. I put the tagging in SELECT, but you could easily turn this into a second CTE and simply select out the payments to clean up. -- -- find all accounts with more than one payment and mark payments to cancel -- WITH cte_DuplicatePayments AS ( SELECT COUNT(*) OVER(PARTITION BY accountID) AS ...


4

This sounds more like an UNPIVOT to me. SELECT SalesTable.Pk, CrossApplied.WeekYear, CrossApplied.Value FROM SalesTable CROSS APPLY (VALUES ('01'+CAST([Year] AS CHAR(40)), Week1), ('02'+CAST([Year] AS CHAR(40)), Week2), ('03'+CAST([Year] AS CHAR(40)), Week3), .......) CrossApplied ...


2

The table-level ANSI_NULLS setting matters for automatic table-level operations like when processing computed columns. This also applies to other objects such as triggers (as seen here). For operations against the table (i.e. comparisons in queries) the connection applies though - this can be quite confusing if you are not expecting a difference in ...


1

Everyone has been putting solutions that depend on setting either language or datefirst. However, assuming that you're looking for dates only after 1900-01-01, (which was a Monday) SELECT ID ,Name ,Salary ,Date FROM <Table_Name> WHERE DateDiff(dd, 0, Date) % 7 in (5,6) Regardless of any settings, since 0 (or 1900-01-01) is a ...


0

There are two ways of doing this: One based on the name of the day of the week, the other based on the weekday number. An example of using the name of the week day (in English, adapt to your local language) SELECT ID, Name, Salary, Date FROM <table_name> WHERE DATENAME(WEEKDAY, Date) IN ('Saturday', 'Sunday'); Alternatively, you can use ...


5

Use the DATENAME() function and specify the datepart as weekday. select ID, Name, Salary, Date from dbo.yourTable where datename(weekday, Date) in ('Saturday', 'Sunday'); As Aaron pointed out, this relies on the language being set to English. Likewise, you could use the DATEPART() function with weekday and test for Saturday and Sunday values.


7

I've always been a fan of a dynamic sql approach for this type of problem. I find it provides the optimal balance between complexity versus quality query plan. In the following code, I define a base query which does whatever it would need to do and then only add in the filters if the provided parameter is not null. CREATE PROCEDURE [dbo].[GetData] ( ...



Top 50 recent answers are included