Sorted Input
Mar 17, 2006
If a component requires a sorted input it would seem reasonable that you can check the IsSorted property of the attached input, but this will always return false. I have tried this when connecting the output of the Sort transform to my component, and then check the IsSorted property for this input. It is always false. How can this be, and also how can I see if the path is indeed sorted?
If using a virtual input column in my UI, I get a SortKeyPosition on the columns, but when overriding SetUSageType in the component class I always get zero for the key. Why is the sort information not quite there for me?
View 8 Replies
ADVERTISEMENT
Nov 16, 2005
All in the subject.
View 11 Replies
View Related
Feb 21, 2008
I am using the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have created the 2 datasources, ordering each on a field commmon to both.
I have created the two derived columns headers and am now moving on to the merge.
It is failing with the following error:
"the input is not sorted"
And whilst I definitely have an order by on the query, when I look at the metadata between the datasource and the derived column, the Sort Key Position items displays "0" for all my fields, I was expecting the sort field to have a "1" in this column. What am I missing?
Any help would be most appreciated!
View 7 Replies
View Related
Apr 13, 2008
Hi guys,
The result of my query I am not able to get it sorted by months.Here is my query & the output.
declare @tbl as table(date datetime,amt int)
insert into @tbl
select '2-jan-2008',100 union all
select '15-jan -2008',200 union all
select '20-jan -2008',500 union all
select '12-jan-2008',300 union all
select '02-feb-2008',100 union all
select '09-feb-2008',250 union all
select '03-mar-2008',500 union all
select '05-mar-2008',800
select Months,SumAmt,MaxAmt,MinAmt from
(
select datename(mm,date)as Months,sum(amt)as SumAmt,min(amt)as MinAmt,max(amt)as MaxAmt,row_number()
over(partition by datename(mm,date) order by datename(mm,date))as rowid from @tbl
group by datename(mm,date)
)t
The result is
Months SumAmt MaxAmt MinAmt
------------------------------ ----------- ----------- -----------
February 350 250 100
January 1100 500 100
March 1300 800 500
I want to get it sorted month wise i.e Jan,Feb,March.But I cannot find a way
View 2 Replies
View Related
May 14, 2007
Hi,
I am a newbie so this might be a very basic question. I have a table that not sorted. So I write a query to sort the table like this
Select * from custInfo where custID <> '0' order by custID Desc
Now obviously I have a selection of the original table in desc order. Now I want to get the first custId from thid Desc selection. How do I do this.
So if the unsorted table looked like this
001 Martha
005 Steve
002 Mike
003 David
and the sorted selection looks like this
005 Steve
003 David
002 Mike
001 Martha
And then I want to select Steve's custID which is 005. how do i do this.
Thanks in advance.
View 2 Replies
View Related
Nov 28, 2003
we have a simple table
Key, Name, Address, City, State, Zip ................ect
I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.
I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.
Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.
Appleby
Jones
Robertson
This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.
thanks in advance -arthur
View 3 Replies
View Related
Jul 18, 2005
I have four tables that need to be loaded into an ASP.NET application. They need to be loaded together into one result set and sorted. Is it possible to load four tables together and sort them using an SQL statement?
To clarify, say I have the following data:
Table1: Anglesey, Cardiff, Ceredigion
Table2: London, Dorset, Lancashire
Table3: Antrim, Armagh
Table4: Glasgow, Berwick, Edinburgh
I'd want the data retrieved from all four tables and sorted so that the data retrieved would be:
Anglesey, Antrim, Armagh, Berwick, Cardiff, Ceredigion, Dorset, Edinburgh, Glasgow, Lancashire, London
I am aware of and am using the SELECT ... ORDER BY feature of MSSQL in my present ASP.NET application to retrieve from single database tables. I'm using merged datasets and a sort method to solve the above problem at the moment.
View 9 Replies
View Related
Aug 2, 2006
Hi All,
I'm new to SQL Server.
I have the following table in a database of SQL Server 2005 Express.
ColA ColB
----- -----
A 12
A 10
B 50
B 13
What I want to achieve is the following result :
Col A Aggr
----- ------
A 2,12
B 13, 50
I tried the following query :
SELECT ColA, dbo.Concatenate(ColB) as Aggr
FROM
(SELECT TOP(100) PERCENT ColA, ColB
FROM TABLE_A
ORDER BY ColB) AS Derived_A
GROUP BY ColA
where Concatenate is the CLR User-defined aggreate function written in C#.
What I want to do is to first sort the rows on ColB and then to execute aggregate function over the sorted rows so as to get the above-mentioned results. However, what I got is the following
Col A Aggr
----- ------
A 2,12
B 50,13 -- not sorted
I learnt from the SQL Server documentation that even though the ORDER BY clause is presented in the subquery, the query result is not guaranteed to be sorted. Only ORDER BY clause used in the outer query should work. So, how should this problem be solved? One way of which I'm thinking is to do the sorting in the aggregate function, but I'm worrying if this is harmful to the performance. Could anyone help me to solve the problem?
Thanks!
Regards,
Nathan
View 3 Replies
View Related
Jul 29, 2015
In the database I am querying, there is a field called "group". I can find out when "group" changes with a time field that was logged whenever group changes. I am trying to find what the value was changed into.
Code:
SELECT
Assignment_Log.DBID,
Assignment_Log.Assigned_Group,
Assignment_Log.Submit_Date
FROM Assignment_Log
This will tell me:
The unique ID of the database
The group it was assigned FROM
The time the assignment took place
I need to find the assignment TO
so what I need to do for the last column is something along the lines of...
Find the next record after the current record by looking at the next Submit_Date and view the Assigned_Group.
If no "next record" can be found, leave the cell empty.
is this possible?
View 2 Replies
View Related
Nov 19, 2013
Is there a way to join tables that have multiple matches to each other (2 records in one table and 2 in another) so that you get 2 records returned instead of 4 with only 1 JOIN ON qualifier?
In our warehouse DB, there is a master location table, an inventory location table, and physical table for counting all product in the warehouse. The master location table has one record per location, but there could be multiple items in that location so my outer join from the master location to the inventory table returns something like:
select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC
order by M.LOC_CODE
LOCATION ITEM LOT QTY
01-01-A 100 abc 25
01-02-A NULL NULL NULL
01-03-A 200 def 50
01-03-A 200 ghi 50
My problem is adding the third counted inventory table because it could look like anything depending on what we find in the racks:
LOCATION ITEM LOT QTY
01-01-A 100 abc 25 (exact match)
01-02-A 150 cba 75 (Item found)
01-03-A 200 ghi 50 (LOT swapped)
01-03-A 300 def 50 (Item Changed)
My join is returning 4 rows for location 01-03-A which I understand, but I'm wondering if I can sort within the join or make some temp tables so that instead of:
select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY, E.AS CNTD_ITEM, E.CNTD_LOT, E.CNTD_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC LEFT OUTER JOIN
E_PHYS_INVT E ON M.MASTER_LOC = E.LOC_CODE
order by M.LOC_CODE
LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 50 01-03-A 200 ghi 50
01-03-A 200 def 5001-03-A 300 def 50
01-03-A 200 ghi 5001-03-A 200 ghi 50
01-03-A 200 ghi 5001-03-A 300 def 50
I'd like to just end up with 2 lines sorted by location, item, lot, qty so I can see that there is a problem with that location.
LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 5001-03-A 200 ghi 50
01-03-A 200 ghi 5001-03-A 300 def 50
View 2 Replies
View Related
Jul 23, 2005
HiBeen at this for 2 days now.Each business has several packages which they can sort usingsort_order.I'm trying to get one package for each business(that I can do), howeverI want it to be the one with the lowest sort_order valueAs you can see below the first record has sort_order=5 when it shouldbe 1.Most of the sort_order columns will be zero by defaultAny help so i can get on with my life!CheersGary------------Current select-------------------SELECT *FROM dbo.testAccommodation_Packages T1WHERE (NOT EXISTS(SELECT *FROM testAccommodation_PackagesWHERE business_id = T1.business_id AND Package_ID < T1.Package_ID))--------------results:-----------------------Package_IDbusiness_iditem_namesort_order123rd Night FREE ...5113Donegal Town ... 0204Executive ...0--------------To recreate----------------------CREATE TABLE [testAccommodation_Packages] ([Package_ID] [int] IDENTITY (1, 1) NOT NULL ,[business_id] [int] NULL ,[Item_Name] [nvarchar] (300) NOT NULL ,[sort_order] [int] NULL CONSTRAINT[DF_Accommodation_Packages_sort_order] DEFAULT (0),)-------------------------------------------------INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('1','2','3rd Night FREE when you stay 2 nights MIDWEEK (129 EuroPPS)','5')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('2','2','Selected Donegal Town Hotel Weekend Sale - 2 B&B and 1Dinner Only € 129 PPS','4')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('3','2','2 Night Specials -Jan, Feb & Mar 2 B&B and 1 Dinner 149Euro PPS','3')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('4','2','Easter Hotel Breaks in Donegal Town - 2 B&B + 1 D€169pps','2')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('5','2','2005 Bluestack Hillwalking, 2 nights B&B, 1 Dinner, 5course Lunch 159 Euros PPS (~109 Stg)','1')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('6','2','April Pamper Package - 2 Night Special ONLY€195pps','10')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('7','2','Discount Hotel Prices for 8th & 9th April Only € 119PPS','7')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('8','2','Golden Year Breaks in Donegal - 4B&B + 2 Dinner€229pps','8')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('9','2','Hotel Summer Breaks Sale in Donegal - 2B&B + 1 Dinner€169pps','9')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('10','2','STAY SUNDAY NIGHTS FOR €25PPS','6')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('11','3','Donegal Town Midweek Special 99 Euro PPS 3 Nights B&B','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('12','3','Bridge Weekend 2 nights B&B 79 Euro PPS (approx 55Stg) Double Room','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('13','3','Donegal Spring Weekend Specials 2 B&B 1 Dinner109.00euros pps','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('14','3','Valentines Weekend 2 nights B&B and 1 four coursegourmet dinner 99Euro PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('19','3','Golden Years Break.40% OFF 4 nights B&B€129.00p.p.s.','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('20','4','Executive Celebration Offer 1 night B&B + Dinner €139 PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('21','4','Watercolour Painting Break 3 B&B Full Board andTuition € 335 PPS','0')
View 1 Replies
View Related
Dec 27, 2007
Hi there,
I have a matrix report that shows a certain columns and rows. I need a row counter to show me the amount of rows in that matrix but it doesn't count properly by using the expression: =Int(RowNumber("matrix1") -1)
I actually don't have any idea on what this expression should be to fix the row count...to illustrate what I get in the report:
______________________________________________________________________________
Chkp | Row | Serial No | Call of Date |Colour| Pillar | AV | Sport | USA | SRA | TAPE |
2019 | 1 | 7006892 | 2003/02/11 | 3644 | 78222 | 3902 | 9182 | | | |
| 3 | 7000123 | 2003/02/11 | 3299 | 17283 | | 9238 | 2793 | | |
| 5 | 7302031 | 2004/01/11 | 3902 | 28932 | | 3233 | 2332 | | |
| 7 | 7273211 | 2004/02/08 | 6727 | 39232 | 8228 | 3293 | | | |
| 8 | 7382728 | 2004/11/11 | 7822 | 32342 | 3902 | | | | |
| 13 | 7342934 | 2004/12/03 | 8323 | 23422 | 2031 | 9212 | 2934 | 2934 | 2982 |
| 18 | 7329491 | 2004/13/02 | 8291 | 92423 | 7922 | 0231 | 2342 | 2355 | 2223 |
| 22 | 7328438 | 2004/12/22 | 8399 | 92311 | | 0289 | 8392 | | 3982 |
As you can see above, the row count numbers are not lining up at all, it starts at 1, then jumps 2 every count then all of a sudden jumps 5 per line...a pattern that is interesting but difficult to understand. The unique value in this matrix is that serial number but even though i group it by the serial number, it still gives me the funny Row count values.
What would be the approach to solving this?
Regards
Mike
View 3 Replies
View Related
Jan 13, 1999
How do I get a list of table names and their sizes, sorted by sizes? The command sp_spaceused only lists one table at a time.
Thanks for your help.
Lan.
View 2 Replies
View Related
Aug 27, 2014
So if an item is a bottle, it will have a unique BottleKey and Null for CaseKey and if an item is a Case, it will have a unique CaseKey and Null for BottleKey. However, the PrimaryIDs are the same for both the bottle and case. I get the results to look like this:
Bottlekey CaseKey PrimaryID
4754 NULL ABC-234
NULL 5465 ABC-234
.... .... .......
Is there a way to get the result sorted by Primary ID?
So that the rows are halved from what we have in the table above and the results look like
PrimaryID CaseKey BottleKey
ABC-234 5465 4754
View 1 Replies
View Related
Feb 6, 2008
Hi all,
I import MS Excel 2003 spread sheet in MS SQL Server 2000 through MS SQL Server 2000 Enterprise Manager (rightclick on the table to be filled with dataall taskimport data). My excel file have 2000 rows and 100 columns of data. All the data are imported in relevant attributes cells in good manner. But the rows are sorted automatically. I am trying to say that first row data is match with my excel file. But second row data have gone to 7th row and 7th row have gone to 5th row like that. I need the data sequence what I have in my excel file. What is the problem occurred? How can I solve this?
Can I export my MS Excel 2003 file to MS SQL Server database?
Please help me. I don't have more knowledge in MS SQL Server 2000.
If your answer has any query to run then please mention where should I run that query.
Thanks,
With Regards,
bala.
View 3 Replies
View Related
Jan 12, 2005
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?
this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
View 6 Replies
View Related
Jun 14, 2007
I have some relatively simple SQL that acts differently between SQL Server 2000 and 2005. Although it is easy to fix I'd like to know if this difference is expected (documented) or a bug, and if there is perhaps a setting/switch I can use to avoid a code review of hundreds of stored procs to look for similar scenarios. Executed the following script in SQL Server 2005 –CREATE TABLE #Floats(FloatID INT IDENTITY,FloatNumber FLOAT NOT NULL)DECLARE @sngCounter floatSET @sngCounter = 20WHILE @sngCounter >= 0BEGININSERT INTO #Floats ( FloatNumber ) VALUES( @sngCounter )SET @sngCounter = @sngCounter - 1ENDSELECT * FROM (SELECT TOP 100 PERCENT * FROM #Floats ORDER BY FloatNumber) AS FloatNumbersDROP TABLE #FloatsGOProduces the following resultset –FloatID FloatNumber----------- -----------1 202 193 184 175 166 157 148 139 1210 1111 1012 913 814 715 616 517 418 319 220 121 0In SQL Server 2000 resultset is this –FloatID FloatNumber----------- --------21 0.020 1.019 2.018 3.017 4.016 5.015 6.014 7.013 8.012 9.011 10.010 11.09 12.08 13.07 14.06 15.05 16.04 17.03 18.02 19.01 20.0
View 4 Replies
View Related
Nov 3, 2007
I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join. With the new, converted field, I do a look up. From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK. This is necessary because SUMDWK is one of the sorted fields. In the look up, it is not possible to change the Output Alias. Does anybody know a way around this? Thanks.
View 14 Replies
View Related
Dec 25, 2007
i ran a preview of a matrix based report whose column headers are dates. The dates seem to be displaying in a somewhat (not completely) random order from left to right. How can I ensure that they display chronologically from left to right?
View 1 Replies
View Related
Jul 16, 2015
Why Merge Transformation Need to Sorted Inputs?
View 4 Replies
View Related
Apr 17, 2006
before anyone even says it, i checked the collation order on everything and it's the same. i get the error when the snapshot is trying to be bulk copied to the subscriber.
i'm on sql2k sp4, server and db collations are SQL_Latin1_General_SP1_CI_AS. here's a repro. 1st, run this in a blank db:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Event_Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Event_Transactions]
GO
CREATE TABLE [dbo].[Event_Transactions] (
[EventTransactionId] [int] IDENTITY (1, 1) NOT NULL ,
[OrphanedFlag] [bit] NOT NULL ,
[ProcessedFlag] [bit] NOT NULL ,
[ProcessedTimeStamp] [datetime] NULL ,
[EventTimeStamp] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [EventTransactions_IDX_ProcessedOrphanedEventTimeSt amp] ON [Event_Transactions] (
[ProcessedFlag],
[OrphanedFlag],
[EventTimeStamp]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Event_Transactions] ADD
CONSTRAINT [PK_Event_Transactions] PRIMARY KEY NONCLUSTERED
(
[EventTransactionId]
) ON [PRIMARY]
GO
insert into Event_Transactions (
OrphanedFlag
,ProcessedFlag
,ProcessedTimeStamp
,EventTimeStamp
)
values (
1
,0
,NULL
,'2004-05-07 15:15:24.000'
)
insert into Event_Transactions (
OrphanedFlag
,ProcessedFlag
,ProcessedTimeStamp
,EventTimeStamp
)
values (
0
,1
,'2004-07-08 13:04:01.513'
,'2004-07-07 16:52:08.000'
)
Now, use transactional replication to replicate it to another db taking all the defaults. when the distribution agent tries to apply the snapshot, it fails with the message mentioned in the title..
Has anyone ever seen this? It's keeping us from considering MS replication for one of our major products. Thanks.
View 1 Replies
View Related
Oct 8, 2015
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
View 2 Replies
View Related
Sep 25, 2014
Any fix for the seemingly random sort order of the variables in the dropdown list when configuring parameters and connection managers in the SSISDB catalog?
I imported all of our connection strings into an environment (about 200 of them). They were inserted in alpha order and the ID values within the internal.environment_variables table shows them in order as well, by ID and by name.
When I run profiler and capture the command that retrieves them and run it in ssms they are in order but in the dropdown they seem random.
There are no values within any of the tables that accounts for the order they are in.
If a package has 5 connections you need to go through the unsorted list 5 times to find them.
Sometimes you get lucky and they are in the first 20 or so.
I know I can write a script, just wondering if there is a fix for the sorting.
View 2 Replies
View Related
Jun 15, 2015
I have a database that has entries that I want sorted by date order. Each entry has an auto ID number allocated (primary key auto sequencing), which I want to change to reflect the sorting (so the first date has the first auto ID number and so on).I've gone into the database and sorted the entries as I want them. Then I've gone into Design View to delete and restablish the primary key autosequence. However, it is not keeping the date order in the database (ie entry ID 3140 date is 12/06/2015, but 3141 is 02/02/2012). How do I get it to maintain the order?
View 3 Replies
View Related
May 12, 2014
I'm somewhat new to report builder and have been trying to recreate a report previously created in an Excel Pivot Table. I'm encountering an issue arranging the data the way it's arranged in Excel.Â
Specifically, I would like the values column to precede an additional column. Â
Until I can post pictures I'll have to try and mock it:
COLUMNS
Values
Results (my data I want as a 2nd column)
I can't figure out how to get report builder to do it the same way. Whenever I add the 'Result' data as a column it always appears on top. I'm guessing what I need to do is somehow get result set as a child of the first Static group, but I'm unsure how to do that.
View 4 Replies
View Related
Dec 1, 2015
I have created a table from another table where I specified that one of the fields, an number field, is sorted in ascending order and have NOT specified that it is to be an indexed field and there are 10 million records, from 1 to 10,000,000 exactly.
Now, if I query that table, asking to return records 1-1,000 from that non indexed number field that I sorted in ascending order (where number field <= 1,000) , will it run as fast as if it were indexed?
In other words, does SQL know somehow that these records are sorted in ascending order and so will not do a full table scan, stopping at 1,000 to return my data set?
Or is there no way for SQL to know this and only specifying an indexed field allows SQL to know that its in some order and so it doesn't have to do the full scan?
View 15 Replies
View Related
May 6, 2015
I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:
ColumnA :  abc,cde,efg  Â
ColumnB : cde,xyz,abc  Â
the result in  Â
ColumnC : cde,abc
similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.
View 5 Replies
View Related
Jan 4, 2006
Using MSDE and OSQL
I begin with:
C:OSQL -D VID -i C:accepted.sql -o C:Resultsaccepted.txt -n -w500 -Usa
That gives me data such as this:
363 Cynthia KY 36
542 Charlene NC 3
594 Amanda NJ 9
592 Robert NJ 54
Then this command to create a table
CREATE TABLE accepted
(
Customer_idnvarchar(50)NULL,
Cust_Namenvarchar(50)NULL,
Cust_Statenvarchar(50)NULL,
Cust_Countnvarchar(50)NULL
)
GO
I've created this BCP format file:
8.0
4
1 SQLCHAR 0 50 "/t" 1 Customer_id SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "/t" 2 Cust_Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "/t" 3 Cust_State SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "/r/n"4 Cust_Count SQL_Latin1_General_CP1_CI_AS
Table is created. I can SELECT * FROM accepted and see my column names.
Then I try to BCP into the table using:
C:>BCP sales..east in C:Resultsaccepted.txt -t -f C:cpformataccepted.fmt -Usa -Ppwd
I get this error:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
and so on......
In the .fmt file I've tried "", " ", " " and everthing I could think of as a delimiter. Still no luck. I've tried almost every switch available to both OSQL and BCP.
The data I am trying to BCP is a SQL result so I don't think any special delimiters are placed. I've tried not using the .fmt file and using the prompts but still no luck. Data is CAST in the query and doesn't excede 45 characters.
Hope I've explained my problem well enough.
-Deana
View 11 Replies
View Related
Oct 5, 2006
Hi,I need to input Chinese character into the table of the database. I did try to install/run both Chinese/English version of Visual Studio into Chinese/English version of Server 2003 but it still didn't work.Please help !stephen
View 3 Replies
View Related
Feb 4, 2008
I use txtbox.Text = cmd.ExecuteReader() but it doesn't work. How can I fix it ?
View 4 Replies
View Related
Feb 25, 2008
I have two textboxes on the page...shipdate and duedate. When the page loads, shipdate has today's date loaded and duedate has a date that's 28 days later than today. When I change the dates and submit it's not updating in the database instead I'm getting the two dates in pageload. What am I doing wrong?Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
ShipDateTxt.Text = Today()
DueDateTxt.Text = DateAdd(DateInterval.Day, 28, Today())
End SubProtected Sub LoanRequest_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LoanRequest.Click
Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)Dim cmd As New Data.SqlClient.SqlCommandWith cmd
.Connection = conn
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "UpdateloanerInfo".Parameters.AddWithValue("@requestorid", Integer.Parse(Request.QueryString("requestorid")))
.Parameters.AddWithValue("@shipdate", ShipDateTxt.Text).Parameters.AddWithValue("@duedate", DueDateTxt.Text)
End With
Here's updateloanerinfo stored procedure:
@requestorid int,@shipdate datetime,@duedate datetime
AS update LibraryRequest
set [shipdate] = @shipdate,[duedate] = @duedate
Where requestorid=@requestorid
View 3 Replies
View Related
Oct 13, 1999
Can I pass a parameter to a DTS package?
Here is what I am trying to do: Every month we need to import a fixed format text file into one of our tables. The format and location of the file is same every month except for the name. I want to create a DTS package to import it and call this DTS package first thing in a stored procedure(after which I do some processing with this imported data). I want to create the filename in my stored procedure and then call this DTS package to import it.
I am usig DTS as the interface is so much easier and want to avoid bcp :-)
Is it possible?
Thanks in advance,
Nishi
View 1 Replies
View Related
Aug 29, 2000
I am just learning SQL Server and I am stuck with a few things. I was wondering if you can define input masks for columns in the database in SQL Server. I have used this feature many times in Access and on the front end in Oracle Forms. Is there such a thing in SQL Server?? Is it just called something different.....? I will be trying to format telephone numbers and dates. Thank you for you time.
Nadine Sinclair
View 1 Replies
View Related
