Tag: sql-server-2012

SQL: Add values to STDEVP calculation

I have the following table. Key | Count | Amount —-| —– | —— 1 | 2 | 10 1 | 2 | 15 2 | 5 | 1 2 | 5 | 2 2 | 5 | 3 2 | 5 | 50 2 | 5 | 20 3 | 3 | 5 3 […]

Sum of data between 7AM to 7PM and 7PM to 7AM (next day)

I have following table: Date Reading1 Reading2 2017-02-15 07:00:00.0000000 33 30 2017-02-15 07:15:00.0000000 32 31 2017-02-15 07:30:00.0000000 32 31 2017-02-15 07:45:00.0000000 33 30 2017-02-15 08:00:00.0000000 33 28 2017-02-15 08:15:00.0000000 32 29 2017-02-15 08:30:00.0000000 32 31 2017-02-15 08:45:00.0000000 34 31 2017-02-15 09:00:00.0000000 34 31 2017-02-15 09:15:00.0000000 34 30 2017-02-15 09:30:00.0000000 31 30 2017-02-15 09:45:00.0000000 32 32 […]

“Conversion failed” Error in a CTE

Here is a trimmed-down version of a SQL Server 2012 query I am using to get all users with age between 25 and 34 years. In my table, The column “Feedback” is of type VarChar and stores the date of birth of a user as a VarChar in the format dd-mm-yyyy. The column “FeedbackDate” is […]

The connection“…” is not found.This error is thrown by connections collection when the specific connection element is not found

I have this error during my package execution.This error doesnt stop my data flow though but i want to get rid of this error as this might effect in near future.All my packages use the ssis logging to log the errors.Even though i have my connection (in this case i am using project level connections) […]

SQL Query-How to remove table spool

I am trying to fine-tune following query to remove table spool. It has 10 million records. I’ve given my execution plan below. Query: SELECT ATT.EMPCODE ,ATT.EMPNAME ,ATT.FROMDATE ,ISNULL(EMPPER.FIRST_NAME.”) ,ISNULL(EMPPER.MIDDLE_NAME.”) ,ISNULL(EMPPER.LAST_NAME.”) ,DET.[SUPERVISOR] ,ATT.[DEPARTMENT] ,SHI. [SHIFT TIME] ,DET.IN_TIME ,LAT.UT_TIME ,SHI.SHIFT_NAME ,PRO.[ACTUAL_TIME] ,LAT.[LATE] FROM Attendance ATT INNER JOIN [172.241.2.147].HIST_DATT.DBO.EMPLOYEE EMP ON ATT.N_ID=ATT.EMPID LEFT JOIN (SELECT * FROM [172.241.2.147].HIST_DATT.DBO.VIEW_ATTENDANCE_DETAILS […]

SQL Server Agent (2012) Inserting Quotes Into SSIS Parameters

I have a SQL Server Agent Job that is running an SSIS package and passing in a project-level parameter. The parameter value I typed in is: C:\Program Files (x86)\7-Zip\7z.exe The package was giving me an “invalid characters” error so I added a step in the package to write the parameter value to a table. It […]

Why in SQL Server 2012 some local groups are missing

During installation of SQL Server 2008, the following three local groups are automatically created where SQLEXPRESS is the instance name. SQLServer2005SQLBrowserUser$<computer name> SQLServerMSSQLServerADHelperUser$<computer name> SQLServerMSSQLUser$<computer name>$SQLEXPRESS SQLServerSQLAgentUser$<computer name> $SQLEXPRESS However with SQL Server 2012, the last three are missing. Why did they decide to stop using the local Windows groups? Why does SQL Server 2012 […]

Is there a way to keep Elasticsearch updated with SQL Server database?

Is there a way to create a trigger in SQL Server, or poll it for deletes? Logstash and JDBC have been implemented, and there are already options for updating and inserting… but it seems delete is not an option. If there is any way to do this, please leave suggestions.

Connection established. Data is not collected from DB2 using SSMA

I’m able to connect to DB2 via SSMA.Ref: https://msdn.microsoft.com/en-us/library/dn890647(v=sql.110).aspx However, just after the connection got established, I’m getting the error “Error occurred while collecting data” in SSMA. I referred this http://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00440n.html Still, getting the same error. I’m new to DB2 and need to migrate data from DB2 to SQL Server Could anyone please help what […]

How to fill data for missing months for more than 1 year

I have a report which displays month wise data from project start till the current month for a set of projects. When a project does not have data for few months it is not getting displayed. I need to display the missing month rows for each project and show 0 for those rows. There can […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.