MS SQL Server :: Sorting Data Upon Import Into SQL 7

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.

Any suggestions would help.

Thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

SQL Server Import And Export Wizard Fails To Import Data From A View To A Table

Feb 25, 2008

A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

- Setting Destination Connection (Stopped)

- Validating (Stopped)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Stopped)

- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)

- Post-execute (Stopped)

Does anyone encounter this problem before and know what is happening?

Thanks for kindly reply.

Best regards,
Calvin Lam

View 6 Replies View Related

Import Data From MS Access Databases To SQL Server 2000 Using The DTS Import/Export

Oct 16, 2006

I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.

Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.

Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com

View 4 Replies View Related

Sorting Data

Apr 17, 2008

Hi,

I have a column containing data

3 KB
16 KB
2 KB
4 KB
43 KB

How to sort this column and display results like:

43
16
4
3
2

Thanks.

View 7 Replies View Related

Sorting Some Data...

Jul 20, 2005

ok..here goes..I have a name field in my table called "bname"all of the data in this field has full names in it, spaces areseparated by a "+"ie. "john+doe"i have two other fields that are currently NULL. "First_name" and"Last_name"now my question is obvious.I need to populate "First_name" with all of the data in "bname" to theleft of the "+"and likewise to the Right of the "+" in "Last_name"i sort of know how i would do this in ASP... but im trying to makethis a DTS package... so i was wondering if this could be done in SQLThanks for any help! :DMario C.

View 4 Replies View Related

Sorting Data, ADO, SQL Server Etc...

Feb 21, 2008

Ok, it is a given that sorting operations are sometimes expensive in SQL Server using the ORDER BY clause because the relational model is not really well suited to sort data blah blah blah.

So the canned DBA response is "this is a presentation issue, handle it in the presentation layer".

OK, great. Problem solved. So I am digging around in the ADO.Net object model trying to give a junior developer some guidance and i am trying to figure out what is the best way in ADO.Net to order what we used to call a recordset where the sorting is not done at the SQL Server, but in IIS.

I imagine there is some overhead in this operation as well. Is it really going to save me some overall performance or am I just shifting the burden and I will not likely see any appreciable difference?

View 8 Replies View Related

Sorting Out Data In A Field

Apr 19, 2004

Hi there,

I got one prob and i need help to solve it.
I got this one field in a table which contain a lot of datas and i need to sort it out into as many different data as a field for each.
Example:

ASP_Tin No
----------------

ss24fg
ss98jk
tp98ij
yh88ij
yh67tr
tp34ed
fg98bv
fg56sl
..........

and i need to sort it out into like this

ss tp yh fg
------------- ------------- ---------------- ---------------
ss24fg tp98ij yh88ij fg98bv
ss98jk tp34ed yh67tr fg56sl

i need to do it in sql statement which is in stored procedure
hope somebody can help me
Thanx

View 2 Replies View Related

Sorting Data Having Months

Sep 21, 2006

Dear all,

I have a field [Month] in my table AA.having Data like ( January,February,March,April etc.)

I need to sort them by starting of month

lets say as

January

February

March

How i can i do this

Have no index and can't define a index on this field

Thanks for ur time and Help

Regards

Mohd Sufian

View 12 Replies View Related

IMPORT New Data Since Last IMPORT - DTS/Stored Procs?

Jan 7, 2004

Hello:

I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:

On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.

Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?

On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?

Any advice will be greatly appreciated!

View 3 Replies View Related

Sorting The Data In Ascending Order .

Aug 16, 2002

Hi ,

Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table .

Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data .

Is there any way to sort the data so that i can see it in the reverse way when i open my tables ?

Many Thanks .

Anita.

View 1 Replies View Related

Sorting Based On Various Data Types

Jul 23, 2005

I've been tasked with designing a database to store collectiveagreements. I need to store each sub-article of each article of eachagreement in it's own row within a table. I'm having difficultyfiguring out the best way to store each sub-article's identifier sothey sort properly.The majority of the collective agreements use numbers in a dottednotation, for example:1.11.21.3.11.3.21.3.31.4Some agreements mix letters with numbers, such as:A-1.11.1.A.1Any ideas how I should deal with this?

View 4 Replies View Related

Sorting Three Different Data Through One Column With 2 Values Only.

Jan 21, 2008

can anyone please help on a sql statement that would sort out three kinds of data. let's say i have a column that contains a status flag. there are only 2 possible values, add and delete. now, i want to sort out three kinds of data: add_only, delete_only, and update. Update is the data that contains duplicate records in the table. Which means, there are two rows having same "student_number" but two different status flags. This happens when there's an update or change in data, such as new address or new phone number. The old row/data is sent with status flag of delete, while the updated data is sent with status flag of add. Example below. An output table with a new column of tag is created.

reports table

STUDENT_NO
NAME
ADDRESS
PHONE
STATUS FLAG

1
McKenzie
Ohio
9111111
add

2
Hario
New Jersey
1234567
add

3
Oda
Japan
9876543
delete

4
Davis
Chicago
1112222
add

5
Rodriguez
Detroit
4445555
add

1
McKenzie
Ohio
7778888
delete

4
Davis
Detroit
1112222
delete

create, remove, update table

STUDENT_NO
NAME
ADDRESS
PHONE
STATUS FLAG
TAG

2
Hario
New Jersey
1234567
add
create

5
Rodriguez
Detroit
4445555
add
create

3
Oda
Japan
9876543
delete
remove

1
McKenzie
Ohio
9111111
add
update

4
Davis
Chicago
1112222
add
update

Anyone knows what SQL statements to use to have this output? Thanks.

View 6 Replies View Related

Problem Sorting Data Returned From Database

May 31, 2007

I need help in sorting data return from database. For example, I have the following data:ID             field1                                                                                                                                                                                             1    Computer                                                                                                                                                                                       2           Cell Phone                                                                                                                                                                3            iPod                                                                                                                                                                                                    4                Car                    I know that a select statement will look something like this: SELECT field1 FROM table ORDER BY field1. However, I want the data to be display this way in the gridview: ID             field1


          3    iPod


                     1    Computer


                                 2    Cell Phone


                                   4            Car    Any idea of how I can make this happen. Any idea is appreciated.

View 11 Replies View Related

Problem With Sorting The Column Data On Table

Apr 9, 2008

Hi All,

I want to sort the column data which is of positive and negative number like the following.

-5823
-1
200
100

i want to sort like
-1
100
200
-5823

How can i do that can any one help me..

Thanks in advance.

-john

View 10 Replies View Related

SELECT And Then I'm Clueless. Ordering And Sorting Data

Nov 13, 2007

I want to select the below info from a sql 2005 db, to use it in a dynamic datadriven menu. Only problem, I don't know how to formulate my select statement. So here's the data

ID, TEXT HREF DESCR PARENTID

1
hMenu1
link1.htm
desc text
0

2
hMenu2
link2.html
desc text
0

3
sMenuM1
link3.htm
desc text
1

4
sMenuTSMenu3
link4.htm
desc text
3

I want the data structured so that below the row with id 1, would come it's subitems, subitems for the subitems and so on. So the recordset I'd like to return would in this case be:hMenu1
sMenuM1
sMenuTSMenu3
hMenu2
I've indented the data just to make things clearer. And finally, I want the substructure to be infinite at least in theory.I also do not want to use recursive logic to do this, if possible. I can change the structure of my table if neeed.Cheers!/Eskil

View 7 Replies View Related

Sorting The Report Data After Exporting To Excel

May 11, 2007

We have a requirement where in we need to sort the data after exporting to excel .But when we are trying to apply the filter on the excel and trying to sort we are getting an error ..

Is there any workaround for this??

View 1 Replies View Related

Sorting Columns In Grid Data Viewer

Aug 28, 2007

Has anyone ever looked at the way the grid data viewer sorts it data when
clicking on a column header? If you click on a column header, something
happens to the sorting of the data in the viewer, but it's not always clear
to me _what_ is happening. It appears the data is sorted ascending on the
column you clicked on. If you click again the sort order seems to inverse to
descending. However, if you look closely, it turns out that the data is not
always sorted correctly, especially when you click on an integer valued
column.

I found this when doing a demo on the AdventureWorks demo extracting data
from the SalesOrderDetail table. If you sort on the OrderQty column, data is
correctly sorted in ascending quantities. However, if you click the column
again, orders with an order quantity of 2 are displayed on top (while there
are orders with a much higher order quantity) and if you scroll down the
list, you notice that there is no clear sorting anymore. The same happens
with other columns.

Is this supposed to work as I would expect it to do or is there a logical
explanation for the behaviour I see?

--
Best regards,

Hans Geurtsen
Docent Kenniscentrum

Info Support
De Smalle Zijde 39
3903 LM Veenendaal
The Netherlands
www.infosupport.nl

View 1 Replies View Related

Import Data From Excel-Sheet Via OleDb In VB.Net - How To Get A Columns Data As String?

Oct 25, 2007

Hello,

i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:

Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next

it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.

So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?

Thanks for Reading

- Pierre from Berlin

[seems i got redirected into the wrong forum, please move into the correct one]

View 1 Replies View Related

Exported Flat File Data Will Not Import To Same Table Without Extensive Data-type Manipulation

Jul 13, 2007

I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."

Seems simple, right?

I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?

Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.

View 12 Replies View Related

How To Optimize Data Import With Huge Volumes And Joins Across Data Sources Not All SQL Server Based?

Jun 7, 2006

I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios:
Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.

Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.

I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.

The result of this query would be exactly what I need to import.
How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.

In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server.
I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do.
Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!

Thanks in advance for your suggestions!

View 9 Replies View Related

Where Can I Find The Import Data/Export Data Options If I Only Have The SQL Server Management Express Studio?

Oct 4, 2007

Hi all,

It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.

Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?

Your prompt response is greatly appreciated.

Thanks!!
Tram

View 8 Replies View Related

Memo Data Type Import Error While Importing Data From Access File Into SQl Server 2005

Sep 10, 2007

I have one column in SQL Server 2005 of data type VARCHAR(4000).

I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.

now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.

Could you please let me know what is the reason?

I know that memo data type does not supported into the SQl Server 2005.

I am with SQL Server 2005 Standard Edition with SP2.

Please help me to understans this issue correctly?

View 4 Replies View Related

Transact SQL :: Excel Data Import Truncate Data Length To 255

Jul 29, 2015

I am trying to import data from an excel Sheet to SQL Database using OPENROWSET. After import I found that all the cells containing data of more than 2000 length got truncated to 255 characters only. I tried finding the solution and found that We need to have the data with length more than 255 in first 8 rows of Excel sheet. It worked for me also. But In real scenario the data that I cant do the manual work on excel. I tried out with Dot Net utility and SSIS package also but the truncation is still the issue.

INSERT into tmp_Test
SELECT
*
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel
12.0;Database=D:Book1.xlsx', [Sheet1$])

View 9 Replies View Related

Import Data Where Current Data Has Unique Identity

Aug 10, 2000

I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.

How can I maintain a unique field to act as primary key in my data when
I want to add (and delete) data that doesn't have this field.

I tried adding the uniqueidentifier field but this gives error message.

The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming

Any help appreciated,Thanks, Keith

View 1 Replies View Related

SQL 2012 :: Accurate Sorting Data Each Time With Millions Of Records Without Time Field?

Apr 25, 2014

Sample Table

USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

It seems to work fine with one million records.

Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.

View 7 Replies View Related

Import Data From Excel Data Sheets

Jan 6, 2007

Hello

Looks like I'am the first one to pop this forum!

But I'am a forward guy so lets get to the problem.

A problem with my replication system as occured.

I have a working SQL server that can do replication trough internet, everyting works

The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server

the subscriber on my client don't get the rows. That will say imported data is not being replicated.

only rows that i have manually inserted will be replicated.

I used the import wizard that came with SQL server.

Is there a solution to this problem?

View 5 Replies View Related

Import Excel Data - Missing Data

Mar 4, 2008

Hello all, I am using the Import Wizard to pull in data from an Excel spreadsheet. One column in particular SQL Server sees as a float data type but it contains varchar data. So I change this in the wizard but some of these values are missing when I select * from Sheet1$ in SQL Server 2005. Any ideas why this would happen? I have formatted the particular column as text in Excel.

View 6 Replies View Related

Import Data From Excel Data Sheets

Jan 6, 2007

Hello

A problem with my replication system as occured.

I have a working SQL server that can do replication trough internet, everyting works

The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server

the subscriber on my client don't get the rows. That will say imported data is not being replicated.

only rows that i have manually inserted will be replicated.

I used the import wizard that came with SQL server.

Is there a solution to this problem?

View 7 Replies View Related

Data Import

Dec 28, 2007

I need to import data from a SQL Server (2000) to a SQL Server (2005 Express).I need some data only, and the database structure is different between the 2 databases.How should I do that? What's the best practice?

View 3 Replies View Related

How To Import Data????

Jul 28, 2000

Hi!

There are various ways to import data in the sql server enterprise, but i want to import data through code, if it is possible, please help me..

View 1 Replies View Related

Import Data

Aug 10, 2000

I'm trying to import data into an SQL Server (7.0) and I'm wondering which Source (Microsoft Data Link, Microsoft ODBC Driver for Oracle, Microsoft ODBC Driver for SQL Server, etc.) -- I THINK we would use the SQL Server driver but I'm not sure... to use and where to go from there? So far, I get seem to get things to work in my favor. I appreciate any help :)

View 2 Replies View Related