I need help on how to find the Row Number Transformation,
I have followed this link http://www.sqlis.com/default.aspx?93
but I cannot find the Trash Destination. Could someone please tell me where I can download it?
Thanks
Has anyone else experienced similar problems? I've been trying to use "Row number" from sqlis.com site with "Union All" but no lucks.
[DTS.Pipeline] Error: component "Union All" (1840) failed the pre-execute phase and returned error code 0x80070057.
thanks
Hi there,
This seems a bug to me. Or does anyone has a logical explanation that escapes me?
When in SSIS Designer Version 9.00.1399.00 I add output columns (numeric 4,0 ) to a scriptcomponent and fill them with valid numeric data in thescript I get a database error 'invalid number' when I use these columns in an OLE db Command-transformation . This errormessage disappears when I replaces those columns by a dataconversion to the datatype they originally have.
Derived Column Name Derived Column Expression
STATUS_DEF Replace 'STATUS_DEF' (DT_NUMERIC,4,0)STATUS_DEF
Maybethis info is usefull for somebody else who can't figure out wathever he's doing wrong.
Paul Baudouin
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
hello all,
i'm not new to SQL but i cant seem to get this right:
hope some one will:
how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter)
i.e :
id | name | val
---------------
1 | name1 | 2.7
2 | name1 | 3.5
3 | name1 | 3.5
4 | name1 | 3.5
5 | name1 | 1.3
6 | name2 | 3.1
7 | name2 | 3.1
8 | name2 | 2.9
requested result:
if input param(name)=name1
result:
1 | name1 | 2.7
if input param(name)=name2
result:
8 | name2 | 2.9
hope some one can write the sql cmd for this
i'll be grateful !
thank you!
How do you find the number of licenses that are installed on a SQL Server2000?
View 2 Replies View RelatedI have a table
Col1 Col2
1 1000
2 1001
4 1003
5 1004
7 1006
I want to find the first free number from first column.
Now It should return 3.
After inserting a row with col1 = 3 it should return 6 and after inserting a row with col1 = 6 it should return 8.
Is it posible ?
I have a part number field which contains whole numbers. (1 - 5000+) however there are gaps in the numbers e.g. Part Number 3876 maybe missing. The table has new parts inserted from a VB Client. Im trying to write a stored procedure where when a user trys to run an update without entering a part number the first missing part number is found. If they do provide a part number then their number is committed as long as it is unique. I've tried using cursors but am not sure on the best approach. Any suggestions?
Thanks in advance
Alex
Can someone suggest a FAST way to select Currency values where the number has more than X decimal places? There are zillions of rows so looping in code is not the preferred solution.
(a bug forgot to round)
Thanks for your help
I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.
View 3 Replies View RelatedI try to find the number of rows in a table with this commands:
CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value
This is the result:
Incorrect syntax near '@countrec'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error:
Line 39:
Line 40: MyCommand.Connection.Open()
Line 41: MyReader = MyCommand.ExecuteReader
Line 42: iRecordCount = CountRec.Value
Line 43:
Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb Line: 41
What to do? I need a complete example to see how it works.
Thanks...
I am using SQL 6.5 and I would like to know (using Isql/w)
the number of users connected to my server at any given time.
Thanks for your help in advance.
Hi,
I have a sql query which gives me the result set with lots of columns and rows.
a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395
i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?
Thanks
hi
How to find current row number in MS SQL 2000 server DB ?
like in oracle rowid is there to get unique no. of each row.
I want to track the last row in the resultset ..
is any fun for this ??
My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.
View 11 Replies View RelatedI have created a table:
CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;
And insert the following data:
INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)
How do i calculate the age and how can i group by the StudentNumber like 'IT%'
Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh
View 4 Replies View RelatedHow to find out number of SQL Server seats purchased?
View 2 Replies View Relatedis there any way to find the number of sqlservers exists (count and name of server) in a network using sql statement or stored procedure
View 1 Replies View RelatedI need to find out the count of number of records older than 100 days from a table having 'order_date' as yyyymmdd format eg. 20041115. Thanks in advance.
View 9 Replies View RelatedHi all,
@str varchar(500)
select @str = 'abcd,efgh,i,jklmn,opsqrtuv'
For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this?
Thanks in advance,
-Praveena
I have a table which has 2 columns and the data is like below
API_Number Group_Name
1234 Group A
3241 Group A
1234 Group B
4567 Group C
7896 Group D
3241 Group E
I wanted to find the API numbers which are repeating in different groups. In the output I want
API_Number Group_Name
1234 Group A,Group B
3241 Group A,Group E
I'm trying to find the SQL Server License Number used to install the software on the server. Does the installation program stores the information on the Registry or not? Looking through registry I found the ProductCode but I'm not sure if that is the valid License Number ... It does looks more like the unistall code ...
Thanks for your help
wamregua
This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.)
How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency?
CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS
DECLARE @NewOrderId int,
@nSQLError int,
@nRowCount int
BEGIN TRAN
UPDATE CUSTOMERS
SET ORDER_NUMBER=ORDER_NUMBER + 1
WHERE COMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -999
End
SELECTORDER_NUMBER
FROMCUSTOMERS
WHERECOMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -998
End
COMMIT TRAN
Consider the following data
create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')
[code]....
if status = 0 then unused status = 1 then used
i need to find 10 continues unused numbers
This is the sample data. In live I'm having plenty of data like that . So quite complex.
note : In real world I need to find the 1000 continues number ranges.
I have this table of Marks as shown below. All I need is to find the average Marks at various intervals of S.no. That is I need averages at every 3rd S.No. as shown.
S.No. Marks
1 ------ 5
2 ------ 5
3 ------ 6 1st Average Value here (16/3)
4 ------ 5
5 ------ 6
6 ------ 7 2nd Average Value here (18/3)
7 ------ 7
8 ------ 7
9 ------ 8 3rd Average Value here (22/3)
10 ----- 8
11 ----- 9
12 ----- 8 4th Average Value here (26/3)
So basically I need a new table which will have 4 average values for the table above. Of-course the table can be much bigger and the average values can be at any nth value of S.No.
Hi,
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Thanks in advance,
Lawrie.
tell me the difference between Audit transformation and rowcount transformation.
Because audit and rowcount transformation will provide the environment variables.
Only difference i am finding is rowcount returns the count of rows its updating .
Apart from these is there any other difference?
Tell me the scenario where i need to use the audit transformation.
which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query
View 10 Replies View RelatedIn my staging table I am having data like below
ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
Now I want to find the Number of times a '¯'character appears in a string. I should get output 14
How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now
View 4 Replies View RelatedI need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like
Product_20120607060930.txt
Product_20130708060930.txt
[code]....
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.