Recently Indexed Messages:-



Integration Services :: SSIS - Extracting From Excel Begin At A Row Include A Special Character?

Sep 18, 2015

I want to import date from excel file  from the row if this row has a character like 'TableName', and I don't know the rownumber and end column number ,so I can't use openrowset 'select * from Sheet$ A11:N'.   And I am thing if I can get the rownumber of the row contain  string 'TableName' and the max column ,that May be easy to extract data.

View 2 Replies

DB Engine :: Cannot Access Destination Table - Appears Sometimes

Apr 23, 2015

I have a simple powershell script, that is writing single row to remote SQL server as sort of keepalive solution for branch machine. It opens System.Data.SqlClient.SqlConnection, SqlBulkCopy writes table, and closes connection.

Sometimes script throws an error in form of - Cannot access destination table.

We are not managing internet connectivity in that place, so I don't know for sure, but can this error be caused by connection timeout that comes when script tries to write ?

View 4 Replies

T-SQL (SS2K8) :: Split Pipe Delimited String Into Two Columns

Mar 6, 2014

I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.

Is there a way I can extract the values from the string into two separate columns?

XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c

View 5 Replies

T-SQL (SS2K8) :: Joining Two Tables

Jul 24, 2015

I've having difficulty joining two tables and getting the results I want. What I'm trying to accomplish is an Health Savings Account (HSA) upload file to go to our Insurance vendor. Here's a summary

Table1: PS_AL_CHK_DED

Description: This table stores all deductions in an employee's paycheck. I'm specifically looking for a field in that table called AL_DEDCD where it equals "H". This is the deduction from the employee's paycheck.

EXAMPLE:

> EMPLID...........CHECK_DT...........AL_DEDCD.............AL_AMOUNT
123456............6/30/2015..............H.............................100.00
123456............6/30/2015..............R..............................75.00
987654............6/30/2015..............H..............................50.00
987654............6/30/2015..............A..............................85.00
456123............6/30/2015..............H..............................200.00
456123............6/30/2015..............L...............................25.00


Table2: PS_AL_CHK_MEMO:

Description: This table stores the employer contribution based on their deduction. For example, we match 50% of their contribution. It is possible for the employee to have a deduction (stored in the ps_al_chk_ded table) but it is not possible for them to have an employer contribution (stored in ps_al_chk_memo) without a deduction (stored in the ps_al_chk_ded).

EXAMPLE:

>EMPLID..........................CHECK_DT........................MEMO_CD...................AL_AMOUNT
123456..............................6/30/2015........................H..................................50.00
123456..............................6/30/2015........................XX................................10.00
987654..............................6/30/2015........................H..................................25.00
987654..............................6/30/2015........................ZZ................................20.00
456123..............................6/30/2015........................MM. ............................40.00
456123..............................6/30/2015........................NN................................5.00

What I want in my output is a combination of these tables that looks at the employee in the ps_al_chk_ded table, takes only the records where al_dedcd = H and returns the corresponding employer contribution (memo_cd =H in al_chk_memo). So you almost have to join the two tables on all three of those fields and I can't get it to work. Here is what I would like the data to look like:

>> EMPLID...........CHECK_DT...........AL_DEDCD.............AL_AMOUNT(1)...............AL_AMOUNT(2)
123456............6/30/2015..............H.............................100.00.............................50.00
987654............6/30/2015..............H..............................50.00..............................25.00
456123............6/30/2015..............H..............................200.00............................0.00

This didn't seem like it was too complicated but no matter how I do it, I can never get the appropriate amount from the employer contribution. I attached a screenshot that shows my queries and result set. In the screenshot, it returned the employee deduction just fine but then returns 48.25 for the employer contribution. However, the MEMO_CD in that specific record is equal to T. In this case, it should return 0.00 for the employer contribution. If I try and add something like d.al_dedcd = c.memo_cd on the join, I barely get any results. The key is matching on those fields and then returning 0.00 when there is no record of MEMO_CD = H in the al_chk_memo table for the deduction in ps_al_chk_memo.

View 9 Replies

SQL 2012 :: SSRS Multi-value Parameters - Total / Sum / Count

Sep 11, 2015

I have a multi-value parameter that I am having a hard time writing a COUNT expression for in SSRS. Here is the situation:

1. If the "(Select All)" in the drop down is selected, COUNT all last names for ALL of the Auditor parameter
2. If a specific or multiple auditors are selected from the drop down, COUNT all last names based on that selection for the Auditor parameter

Currently, I am having it COUNT by ALL and it works but if a specific or multiple auditors are chosen, then the COUNT doesn't work.

View 3 Replies

Integration Services :: Handling Empty Text File Load Into Table Through SSIS Data Flow?

Jun 16, 2015

We have created SSIS package to load a text file into a table. Source system shares 10 text files and recently they stopped generating data for one of the text file (comping empty), after few months they will start generating the data for the empty file batch processing. 

The Issue here is Data Flow task is getting failed while loading empty text file into table. How to handle this empty file load issue in SSIS package.

View 3 Replies

SQL 2012 :: Need To Export One Table To Another Database In A Different Server / DB

Feb 26, 2015

My need is to take just one table from a particular database and import it to a another database ( in a different server/db ).

View 9 Replies

Select Max Int Column And Other Columns

Feb 27, 2015

I have three records, with one column that is an identity/PK. I want the last record inserted and the whole record's information from the query, using a where clause to limit the record returned from the table.

I get an error if I do not have the group by added. However, with this query, I still get three records returned.

SELECT max([tablePK])
,[orderGUID]
,[tblPeopleFK]
,[tbl_shippingAddressFK]
,[tblBillingMethodFK]
,[shippingMethod]

[Code] ....

View 1 Replies

SQL 2012 :: Windows 2008 Evaluation Default Username / Password?

Nov 19, 2014

i have installed windows 2008 evaluation, during installation i was not asked for username and pssword but when i try and start up its asking for administrator password.

View 0 Replies

SQL 2012 :: Entity Framework Returning Cached Data

Mar 23, 2014

I have a datagridview bound to a table that is part of an Entity Framework model. A user can edit data in the datagridview and save the changes back to SQL. But, there is a stored procedure that can also change the data, in SQL, not in the datagridview. When I try to "refresh" the datagridview the linq query always returned the older cached data. Here's the code that I have tried using to force EF to pull retrieve new data:

// now refresh the maintenance datagridview data source
using (var context = new spdwEntities())
{
var maintData =
from o in spdwContext.MR_EquipmentCheck
where o.ProdDate == editDate
orderby o.Caster, o.Strand
select o;
mnt_DGV.DataSource = maintData;
}

When I debug, I can see that the SQL table has the updated data in it, but when this snippet of code runs, maintData has the old data in it.

View 0 Replies

SQL Server 2008 :: How To Find If A Database Has Been Accessed / Used Since Last Month

Jul 6, 2015

Is there anyway I can find or make a report if a databases has been accessed/used since last month?

View 4 Replies

Analysis :: SSAS Named Set With A Two Dimension Attribute Tuple

Jul 15, 2015

In my SSAS Cube I have created a dynamic named set "top 10 e-learnings by language" which consists out of a set of tuples. Each tuple has two attributes out of the same base dimension "training": attribute 1 is "sprache" (language) and attribute 2 is "training text".

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 eTrainings pro Sprache]
AS Generate(
{ [Training].[Sprache].[Sprache].Members },
TopCount(
EXISTING { [Training].[Sprache].CurrentMember * [Training].[Training Text].[Training Text].Members },
10,
[Measures].[Teilnahmen eTraining]
)
), DISPLAY_FOLDER = 'Training' ;

Normally a named set would be automatically visible in Excel Pivot under the dimension you used to create the named set, but it seems that named sets with tuples which have more than one attribute are placed in a separate folder "Sets" in between the measures and dimensions.Additionally in the SSAS cube browser this named set is not visible at all.Is there any way to tell the named set in which dimension it should appear or any workaround?

View 2 Replies

SQL Server 2012 :: Cursor Stop Query At Specific Time?

Oct 29, 2015

This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.

I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.

CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2

[code]....

View 1 Replies

SQL Server 2012 :: Create Weeks Calendar From Date

Sep 30, 2015

I need to create a week calendar from date in SQL 2012. Week date starts with Sunday regardless if first Sunday or last Sunday overlaps with previous or next month. For example, the first week in Sep 2015 starts on Sunday 8/30/2015 and ends in 9/5/2015. Too, the last week of Sep 2015 starts on 9/27/2015 and ends on 10/3/2015. Here is the final format:

WeekStartdate WeekEndDate WeekName
8/30/2015 9/5/2015 Sep_Week1
9/6/2015 9/12/2015 Sep_Week2
.....
9/27/2015 10/3/2015 Sep_Week5

View 9 Replies

Query For Cumulative GPA Calculation

Oct 9, 2015

I have a table with the following creation sql script

CREATETABLE [dbo].[TEST_ENROLLMENT](
      [ENROLLMENT_ID] [int]
NOTNULL,
      [COURSE_OFFERING_ID] [int]
NOTNULL,
      [STUDENT_ID] [int]

[Code] ....

Below is the sql insert for the above table’s data

INSERTINTO TEST_ENROLLMENT(ENROLLMENT_ID,COURSE_OFFERING_ID,STUDENT_ID,DEPT_ID,COURSE_CODE,CREDIT,SECTION_ID,
SEMESTER,ACAD_LEVEL,ACAD_YEAR,DATE_ENROLLED,[STATUS],TOTAL_MARK,GRADE_LETTER,QPE,Honor,INSTRUCTOR_ID,USERNAME
)VALUES(877,356,104,15,'IRD

[Code] ...

I was trying to calculate GPA and Commulative GPA (CGPA) for this student and the formula of GPA is SUM(Honor)/SUM(CREDIT) and the formula of the CGPA is the Average of the SUM(Honor)/SUM(CREDIT) grouped by semester and I wrote the below query which is calculating the GPA correctly but wrongly calculating the CGPA.

How to get the correct CGPA shown in the below desired result set table.

select x.STUDENT_ID,AVG(x.gpa)as
GPA,AVG(y.gpa)AS'COMMULATIVE GPA'
from
(
select STUDENT_ID,SUM(Honor)/SUM(CREDIT)
gpa,ACAD_YEAR,SEMESTER

[Code] ....

Current result set (Incorrect CUMULATIVE GPA)

STUDENT_ID GPA CUMULATIVE GPA
104 2.9 2.9
104 2.25 2.25
104 3 3
 
Desired result set

STUDENT_ID GPA COMMUTATIVE GPA
104 2.9 2.9
104 2.25 2.575
104 3 2.716

View 5 Replies



Copyrights 2005-15 www.BigResource.com, All rights reserved