Comments by Andrius Leonavicius (Top 200 by date)

You're welcome. I'm glad to hear that. :)

Thank you, Sergey. :)

You're welcome. :)

Hi,

It would be easier to help you if you could post input and output samples...

You're welcome. I'm glad to hear this. :)

+5

You're welcome. :) +5 for alternative queries.

Hi,

Great examples, but I can't agree with this one: "SQL Server 2008 does not support this kind of statement.".

Table Value Constructor was introduced in SQL Server 2008. Also, I tried the original query in SQL Server 2008 and it works (it doesn't in SQL Server 2005)...

Hi,

It works OK on my machine. Maybe you accidentally marked the keyboard 'VALUES' before execution?

What is the general rule for putting month values into two columns (in case of more than 12 values)?

Hi,

If it's only 12 values, why can't you just do it manually? Where is the problem?

No, that would not be OK. Unless you want to pay me for individual consultations. :)

You're welcome.

Really? Have you tried it with your data? I tried with the following data:
(3, 2, 'xyz'), (4, 2, 'abc'), (3, 2, NULL), (4, 2, NULL), (4, 2, '2'), (3, 2, '3')

Here's the difference:
3 4
xyz abc
3 2

versus

3 4
xyz --
-- 2
3 abc

Also, it would be nice if you would accept valuable answers (and rate them). :)

You're welcome. Sorry, but this is going off-topic...

You have connected to Analysis Server. I guess that you need Database Engine instead. I would suggest you to start here: Lesson 1: Connecting to the Database Engine[^]

OK, I get it now. I gave you a dynamic pivot solution recently: Transpose query result[^]. If you're using it, then you can easily rearrange values. For example, change
ORDER BY Attribute_SubID
to
ORDER BY Attribute_Sub DESC
and after
[...] AS RowNumber
FROM #DataTable:
add this line:
WHERE Attribute_Sub <> ''--''

Do you mean that it could be color3, color4, etc.?

You're welcome.

It is a bad idea to rename "Ssms.exe" to something else (this is executable file, not shortcut). You can easily create a shortcut on the Desktop or pin to Taskbar (and rename that shortcut or any other shortcut)...

The default locations for SQL Server 2008 R2 Management Studio:
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

Hi,

I just don't understand how with this query you're getting ParcelStatus = SUSPENDED for OrderID = 31363. Perhaps you pasted the wrong query? The query looks incomplete because of A.CUstomerID and unnecessary comma before the from...

Sorry, but without code it's not possible to tell what is wrong...

I'm glad to hear this. :)

If installation completed successfully, you should see "SQL Server Management Studio" somewhere in the start menu. Try this: click [START] and type "Ssms.exe" in the search field.

OK, take a look at this picture: Management Tools[^] Did you selected these?

Did you selected "Management Tools" option (checkbox) during installation?

Hi,

I think you're missing some important information. Please check your question (use "Improve question" button).

Hi,

Is it Windows 8/8.1? If yes, then try searching for "SQL" in the search field.

You're welcome.

Hi,

What is your version of SQL Server?

Table object? Are you talking about ASP.NET or just SQL Server?

You should have posted it as a comment to kumarbs, not as a solution!

Hi,

Are you sure that your stored procedure is in the same database as object 'Stock'?

Hi,

Could you post some sample data and your expected results?

You're welcome. I'm glad to hear this.

You're welcome. Also, please accept the solution by clicking the "Accept Solution" button. Thank you.

I'll try to explain this in details:
1. DATEADD(mi, DATEDIFF(mi, 0, [TimeStamp]), 0)
This is used to remove seconds from DateTime (2014-06-14 17:22:49.287 => 2014-06-14 17:22:00.000)
2. ROW_NUMBER() OVER (PARTITION BY [...] ORDER BY [...])
2.1. PARTITION BY is grouping the results by [TimeStamp] without seconds (2014-06-12 10:09:00.000, 10:09:01.000, 10:09:02.000, ... belongs to one partition; 2014-06-12 10:10:03.000, 10:10:04.000, ... belongs to another partition and so on).
2.2. ROW_NUMBER() assigns the sequential number of a row within a partition (1, 2, 3, ...; 1, 2, ...; ...).
2.3. ORDER BY - gives row number by [TimeStamp] order (2014-06-12 10:09:00.000 - 1, 10:09:01.000 - 2, 10:09:02.000 - 3, ...).
3. ;WITH data AS [...]
This is a Common Table Expression (CTE) - a temporary named result set (a subquery). I'm using it here because this allows me to filter and order the results:
WHERE [RowNumber] = 1
ORDER BY [TimeStamp]

These links might be useful for you:
1. http://www.codeproject.com/Articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R
2. http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER

Please copy updated code and replace '1,2,3' with your select.

I just noticed that my code was not HTML encoded after pasting here (some parts were missing). I'm sorry about that. It should work now.

Splitting a column value (values stored in one column => rows) and transposing columns to rows (columns => rows) are two different things. You asked how to split a column value (like 1,2,3) and I gave a solution for that. Nirav Prabtani posted a solution for transposing columns to rows. So, what do you actually want to do?

Did the installation completed successfully? Where are you looking for the icon? Have you tried searching for "SQL" in the search field?

Hi,

Your database seems to be corrupted. Do you have a backup?

This might be useful:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp

You should post it as a solution.

You're welcome.

The problem was that you were trying to solve this with one LEFT JOIN. You need two LEFT JOINS with appropriate WHERE clauses. Two joins work here in a similar way like IN and NOT IN; EXISTS and NOT EXISTS.

Please take a look at my answer (solution 2).

Hi,

Could you please explain this in more detail: "Record (2,'y',1), not inserted In Table2 and I want found it with Left Outer Join"?

I am glad to hear this.

You're welcome. Please let me how it works for you.

OK, try this:
HAVING SUM(ISNULL(DATEDIFF(MI, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) >= ISNULL(DATEDIFF(MI, 0, S.shFullDayhrs), 0)

What data type is S.shFullDayhrs?

Change this line:
SUM(ISNULL(DATEDIFF(MM, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) AS [TotalMinutes],
to this (difference in minutes):
SUM(ISNULL(DATEDIFF(MI, t1.[Attendence_Date], t2.[Attendence_Date]), 0)) AS [TotalMinutes],
or this (difference in minutes - hours):
SUM(ISNULL((DATEDIFF(MI, t1.[Attendence_Date], t2.[Attendence_Date]) % 60), 0)) AS [TotalMinutes],

Also, I can see the problem here:
Convert(varchar(90),SUM(ISNULL(DATEDIFF(MM, t1.[Attendence_Date], t2.[Attendence_Date]), 0)))
Try changing to this:
Convert(varchar(90),SUM(ISNULL((DATEDIFF(MI, t1.[Attendence_Date], t2.[Attendence_Date]) % 60), 0)))

By the way, why do you need two temporary tables?

You're welcome. Please accept (and rate) my solution. Thank you.

Explanation: Check in and check out times should be in the same row. In order to do this, I am using [RowNumber]: an odd number - check in time, an even number - check out time. For instance, 1 - in, 2 - out, 3 - in, 4 - out and so on. The first condition is used to join the separate time in and time out rows into single row using [RowNumber] like this: 1 = 2, 2 = 3, 3 = 4 and so on. The second condition is used to eliminate extraneous check out times from such join. By the way, you can add the following line above GROUP BY clause to remove unnecessary rows:
WHERE t1.[RowNumber] % 2 <> 0

If you want total minutes, you have to write MI instead of HH, not MM (MM stands for the month). Link: http://www.w3schools.com/sql/func_datediff.asp

You're welcome. ;)

Thank you, Sergey. +5 for you as well.

I think that you don't have a folder "Temp" in the C: drive.
Try adding the line in bold:
// Create a new file
Directory.CreateDirectory(Path.GetDirectoryName(fileName));
using (StreamWriter sw = File.CreateText(fileName))

Post it as a solution.

You're welcome.

Also, please read this:
http://www.hoax-slayer.com/do-not-use-all-capitals.html

Also, be careful with ExecuteScalar().ToString(). The ExecuteScalar() method can return null and your code would fail here.

For instance, change this:
cmd.ExecuteScalar().ToString()
with this (null values are handled):
Convert.ToString(cmd.ExecuteScalar())

I'm glad to hear it. :)

Well, not exactly (the loop is OK). Notice this statement: if (b != null) In the case of the "Bird", b is null. Take a look at the class Bird. This class doesn't implement the ILiveBirth interface...

Is it clearer now?

You're welcome. Please accept (and rate) my solution. Thank you.

You're welcome. I'm glad to hear it.

Thank you, Maciej.

Hi,

I noticed that your INNER JOIN is missing joining columns...

Hi,

Are the columns from the data (cid_10, cid_11 and cid_12) fixed or dynamic?

Hi,

First of all, if you see something bad, then let the author know about it by posting a comment. I just accidentally noticed this...

My code is good because I clearly stated that @ToDate is not included. "If that day would be included, the result would be 160000.00 (16 months)." - I was thinking about a time period from 2014-04-01 to 2015-07-31 (sorry, my bad). For a time period from 2014-04-01 to 2015-07-30 (the last day is included) the result would be 159677.42.

You're right about one thing: adding +1 for including @ToDate. But this wasn't my intention at all. I just made an assumption that @ToDate should not be included because OP gave this example: "fromdate='2014-04-20' and todate='2014-04-23' Working days=3" - 3, not 4...

By the way, I haven't rated "your" solution.

You're welcome.

What version of SQL Server are you using?

Hi,

Could you please clarify the formula for salary calculation? Let's say that fromdate='2014-04-01', todate='2014-05-31' and the salary is 10000. Assuming that April has 21 working days and May has 22 working days. How are you getting working days? Is 10000 the weekly or monthly salary? How are you calculating the salary per day?

Thank you, Manas.

Hi,

The column "ITEM_HEAD_ID" values are 1, 2 and 3. And you want to transform rows into columns. Is it static or dynamic transformation?

You're welcome.

Hi,

Do you think that your solution solves the issue? Please take a look at my answer (solution 2).

:)

You're right, sanket. Actually, I haven't noticed your solution because I haven't refreshed the page before I submitted my answer...

Hi,

Have you tried with small images? Maybe there is something wrong with your code. Also, I have found this post: http://jieyanghu.com/2010/12/29/manipulating-raw-bitmap-data-in-net/

Or maybe I don't understand what exactly you are trying to do...?

Why you are trying to copy the picture from MS Paint??? You need to save it to the desktop from MS Paint using Save or Save as. That's it! You should have it on the desktop.

Alternative way to do this is to paste the picture into MS Word, Right Click on the picture -> Save as Picture...
I am not sure if this always works...

You haven't done the step 4 correctly. Instead of trying to save the picture directly from the clipboard to the desktop, you need to save the picture from MS Paint using Save or Save as.

You're welcome. Please let me know if that solved your problem.

Hi,

phil.o is right, memory limit of the .NET object is 2 GB. .NET Framework 4.5 arrays can be greater than 2 GB. Link: http://msdn.microsoft.com/en-us/library/hh285054(v=vs.110).aspx

By the way, there is an article about the memory limits. Link: http://www.codeproject.com/Articles/483475/Memory-Limits-in-a-NET-Process

You're welcome. Thank you.

+5

Post it as a solution. ;)

You're welcome.

Could you post the code of your stored procedure? And what exactly do you mean by saying "not working"?

Agree with you, +5.
One more thing to mention: select only needed columns instead of *.

I don't know who downvoted my answer, but I really would like to know why.

I don't know who downvoted my answer (solution 2), but I really would like to know why.

+5 :)

Advice regarding comments here: Next time use a "Reply" button to respond to a comment.

This is strange. SELECT * FROM log could cause such error because Log is the name of mathematical function. Maybe you were selecting data from a table "log" before? Anyway, try to enclose with square brackets:
SELECT * FROM [dbo].[Account];
SELECT * FROM [dbo].[log];

SELECT * FROM Account; gives you this error: "Invalid object name 'log'"???

Hi,

Maybe this post will help you:
http://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/

IsNull function in MS Access is different than in SQL Server...

No, you can't do that. But temporary table is visible in the same session, so you should be able to access your temporary table in the stored procedure.

Also, please read this:
http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm

Revert back? What kind of RDBMS are you using? As I know, RAISERROR statement should indicate that you're using a SQL Server. So I was checking for syntax errors at first.

Speaking about the code, I don't know why you're using both if @action and if @tran_stat. It seems like unnecessary complexity... I don't know all details, but try to change this line:
--select @tran_stat from tbl_cyl_gas_master where serial_no=@serial_no
if @tran_stat = 'FR'
with this:
if @tran_stat = 'AU'

Thanks. ;)

Hi,

Please take a look at this:
http://searchenterprisedesktop.techtarget.com/answer/How-to-resolve-Windows-file-sharing-problems

Have you read the question? OP is asking for a solution in SQL Server, not MySQL. Besides, you're giving identical code that OP has posted as example...

Post it as solution. ;)

Nice, +5!

Are you sure it works as OP asked? Numbers that are not in the table? Because your solution should give numbers 3,5,6,8,9, not 1,2,4,7,10...

The identity_insert requires specifying all the column names in parenthesis!

Truncation deletes all rows and resets the counter for the identity column. But if you want to insert rows containing specific identity values (like your INSERT INTO TableName SELECT * FROM TableName), then you need to use this: SET IDENTITY_INSERT TableName ON. Try this:
TRUNCATE TABLE TableName
SET IDENTITY_INSERT TableName ON
INSERT INTO TableName (Column1, Column2, Column3, ...) SELECT Column1, Column2, Column3, ... FROM TableName
SET IDENTITY_INSERT TableName OFF

+5

Thank you, Maciej.

Hi,

What query have you tried so far?

No, you can't use IsNull function like that in MS ACCESS. IsNull ( expression ) function returns TRUE if the expression is a null value. Otherwise, it returns FALSE...

It should be like this:
FORMAT ((SELECT IIF(ISNULL(TotalPaid),0,TotalPaid) FROM InvoiceList WHERE InvoiceList.InvoiceId=NewInvoice_1.InvoiceId), "Fixed") AS AmountPaid

+5

I think that FORMAT function should handle NULL values without error, but you can replace NULL values to 0 like this:
IIF(ISNULL(ColumnName),0,ColumnName)

You're welcome. :)

Sorry, not enough information to help you with this error...

You can't pass any parameters to the trigger directly because triggers fires up automatically. There are two ways to pass some information to the trigger indirectly:
1. Store the information in a table.
2. Use CONTEXT_INFO.

This link might be useful to you:
http://www.rahulsingla.com/blog/2010/11/sql-server-passing-parameters-to-triggers

By the way, I just noticed you're using AFTER INSERT, but FOR UPDATE trigger. AFTER and FOR triggers do the same thing, there is no difference. An INSTEAD OF trigger is different than FOR/AFTER.
So use AFTER INSERT, UPDATE or FOR INSERT, UPDATE...

Hi,

You have already asked a similar question. I have already answered to your question, haven't I?
http://www.codeproject.com/Answers/745934/I-have-created-trigger-for-after-insert-now-how-to#answer2

Note: Ask in the comments if something is not clear about the given answer. If you want to edit or improve your question, you can do that by clicking on "Improve Question" link.

Hi,

You could use PIVOT and UNPIVOT to achieve this. Links:
1. http://technet.microsoft.com/en-us/library/ms177410(v=sql.100).aspx
2. http://www.negablog.com/2013/01/pivot-table-using-sql-server.html
3. http://www.negablog.com/2013/01/unpivot-table-in-sql-server.html

You should write your code, which would be executed only AFTER UPDATE.

IF (@Action = 'I') - your code will be executed only AFTER INSERT
IF (@Action = 'U') - your code will be executed only AFTER UPDATE

If you want to execute exactly the same code after insert and update, then use only this:
CREATE TRIGGER [dbo].[INSERT_IM_ServiceRequest_Hx] ON [dbo].[IM_ServiceRequest] AFTER INSERT, UPDATE
AS

Hi,

If 'IsFulltextEnabled' property is 1, then it means that a database is full-text enabled.
Where is the problem?

You're welcome. If I solved this problem, please accept my answer (solution 1) by clicking the "Accept Solution" button.

Hi,

I think that you have a trigger on table bfsend..s_ordtrs, which has subquery returning more than 1 value. Please check it by yourself or paste the code of the trigger here (using "Improve question" link).

Sorry, wrong +5. :D Thank you for noticing it. I have made an adjustment. ;)

+5

So what have you tried so far? You must try something first. If you stuck somewhere, then we could help you...

Thank you.

Thank you.

You're welcome. I am glad that you figured it out.

Nice answer, +5.

+5 for a clear explanation.

Wow, your proposed solution is really simpler than mine. I like it. :)

Are you sure you have records, which satisfies both WHERE conditions: date comparison and isnull(c.QCPRocessed,0) = 1?
Could you post some sample data for testing?

*If the solution to your question solves your problem, please mark it as solved. ;)

You're welcome.
Thank you.

Hi,

It looks like your SQL Server login doesn't have a permission to create a database. SQL Server login must be a member of the sysadmin or dbcreator server role.

Please take a look at my answer with similar problem:
http://www.codeproject.com/Answers/723623/How-Do-I-Clear-This-Error#answer1

Hi,

What exact version of installation media do you have?

Thanks.

Thank you, Maciej.

Hi,

Have you installed Service Pack 2 for Microsoft SQL Server 2008 R2?

Thank you, Sergey.

Thank you, Jörgen.

OK, so I suggested you to create trigger and use BULK* INSERT, UPDATE or DELETE (if its suitable in your situation). If you are inserting, updating or deleting multiple records at once, you will get better performance with this because you don't need to use a cursor. But again, it depends on situation (that's I was asking for).

Did you get my idea or you need more explanation?

*By saying BULK, I mean insert, update or delete multiple rows at once.

OK, so give us more information. Could you post some example? Please use "Improve question" link in order to add more information.

Do you mean this:
SELECT * FROM employee WHERE sal >= 100 AND sal < 1000;
?

Excuse me, what do you want?

Does these 4 tables have foreign keys? Well, instead of procedure you could create trigger, which would fire automatically: AFTER INSERT, UPDATE or DELETE. Answering to your question: maybe you could do a BULK INSERT, UPDATE or DELETE? I could help you further, but I need some more details...

Hi,

Let me ask you a question. Why do you need 4 tables representing each data type...?

Hi,

Please read this one:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6ab5559f-c6b4-461f-9046-c60ea5f97f5d/where-to-find-microsoftmasterdataservicesdll?forum=sqlmds

Hi,

You could use Dynamic Pivot to achieve this. Please take a look at this link:
http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

Also, please take a look at this one (in order to understand how PIVOT and UNPIVOT works):
http://technet.microsoft.com/en-us/library/ms177410(v=sql.90).aspx

Unfortunately, we can't read your mind. So what is your question?

Hi,

Regarding to the CASE expression. Your usage of it is incorrect. I would recommend you to read this:
http://www.mindfiresolutions.com/Using-CASE-expression-in-SQL-queries-965.php

I could help you further, but I need more information about requirements of your task and data structure (tables).

You have a great attention to details. +5 for that.

Hi,

You've already asked this question:
http://www.codeproject.com/Questions/731345/calculating-YTD-from-store-procedure

Please don't post duplicate questions. If you want to edit or improve your question, you can do that by clicking on "Improve Question" link.

So have you tried to run your procedure with compatibility level 80?

No, I don't think so. :) I can't speek for them, but I can tell something from my experience. Once I saw some easy question, which was posted like 5 minutes ago (0 solutions). Then I started writing a solution. After I posted it, my solution was 4th. Why? Because that data wasn't automatically refreshed while I was writing and others posted their solutions. Probably the same thing here...

+5P.S. OP's comment improved my mood. :)

I think they all copied questioner's SELECT statement and added keyword DISTINCT. :)

This looks good. But strange thing happened. I pasted your insert statement into NOTEPAD++ and it looks like this:

insert into merchant_cfg ( merchant_id , name, value ) values ??( 146 , 'detailed_listing_full.enabled' , 'true' )

It seems that you have some invisible character here, which could cause such error...

Thank you.

Hi,

Could you post CREATE TABLE statement? You need to run the following command to get it:
SHOW CREATE TABLE merchant_cfg\G

+5, nice answer.

I'm asking this because I know that APPLY can work in compatibility level 80 with SQL Server 2005 or greater (it might not work in all situations, not sure about yours)...

Hi,

Just to make things clear. Are you going to use this code with SQL Server 2000 or is it only SQL Server 2005 with compatibility level 80?

Hi,

Here is more information about .ani file format:
http://www.pcmag.com/article2/0,2817,1159391,00.asp

Try this one:
http://www.gdgsoft.com/download/anituner.aspx

You're welcome. Unfortunately, I don't know any free tool for this.

Hi,

I just (accidentally) noticed that I haven't replied to this. Sorry about that. You probably figured it out, but I'm going to tell you anyway.

You could eliminate viewed articles by adding the following code in a WHERE statement:

AND NOT EXISTS (SELECT 1
FROM UserAndArticle AS t3
WHERE t3.ArticleID = t2.ArticleID
AND t3.UserID = @UserID)

Thank you.

I figured this out and updated the solution.

Good question, Sergey. I'm going to figure this out.

Thanks for noticing. I made an adjustment.

Hi,

Why you're not storing your dates as DATETIME in SQL? You can format a DATETIME value into a specific date format in ASP.NET...

Yes, it's possible to get the data from backup, secured with PASSWORD or MEDIAPASSWORD. That's why this feature is deprecated in SQL Server 2012.

If I were you, I would try some software, which could recover (extract) data from .bak file...

Could you show us how you're declaring and setting @productCode?

OK, then you should temporary eliminate 'STUFF' query while searching for the real problem...

So, you're dealing with FOR XML PATH... I didn't realized it at first. I would try to get values from XML as NVARCHAR(MAX) type.

If that doesn't work, I could think of a workaround.
For example:
1. Use your second solution:
SELECT * FROM [TABLE_NAME] WHERE [COLUMN] LIKE '%' + @MyParameter + '%'
2. Insert these values into table variable and then get the right one from there.

If you're selecting without parameter (= 'PCA-S(PS)M6H' instead of = @MyParameter), does it always works correctly?

Hi,

The procedure itself looks OK. How you're calling it?

Hi,

What kind of version you are trying to install?

I can agree on that. :)

*Brackets are optional. ;)

Hi,

I think I get it now. Please try this one:

ALTER PROCEDURE [dbo].[approveddd] @DocID INT,
@ApproveID INT,
@ApproveBy NVARCHAR(50),
@DesigID INT
AS
IF EXISTS(SELECT 1
FROM Approval
WHERE DocID = @DocID
AND ApproveBy = @ApproveBy
AND ApproveID = 3)
UPDATE Approval
SET ApproveID = @ApproveID
WHERE DocID = @DocID
AND ApproveBy = @ApproveBy
AND ApproveID = 3
ELSE IF NOT EXISTS (SELECT 1
FROM Approval
WHERE DocID = @DocID
AND ApproveBy = @ApproveBy)
INSERT Approval
(DocID,
ApproveID,
ApproveBy,
DesigID,
ApproveDate)
VALUES(@DocID,
@ApproveID,
@ApproveBy,
@DesigID,
GETDATE())

Let me know it it works for you.

Hi,

How exactly you have applied my solution? If you just "copy-pasted", then it shouldn't work as you expect. I was unsure about ApproveID values, so I made some assumptions in my solution. Could you tell me more about it?

Or maybe I'm not understanding what you are trying to achieve. Could you please tell me how exactly it should work?

Hi,

Could you please give us more information about data merging?

markkuk is right. Unfortunately, there is no general answer how to test for a NULL and it depends on particular C++ library for accessing SQL database. Good luck.