Do They Ever Plan On Fixing Imports From Excel (and Probably Anything Else ??)
Mar 26, 2007
hours wasted what else is new with Micro --- crap try to import an excel into a table, longest field (via vba macro report) is 278 receiving DB field is 4000 get "Truncation error, I must stop I am a piece of s*** program " the only thing you can find on the web is make sure you have SQL server 2005 SP2 install it same thing and g** only knows what other problems I have just created by installing another piece of Microsoft magic generated in India or China by the best technologists making at least $5 and hour
so MVPs when is this rediculous situation going to be fixed ?? oh that's right your answer will be "go to VISTA" which won't fix my problem but will probably help your stock situation
MS owes me (conservatively) 100K ... my current plan is to install Linux get up on MySQL and NEVER deal with SHoddy half built pieces of garbage again
so where is the fix ???
when will we see it (short of buying another bloated piece of ... oh i mean Vista)
an addendum
i am trying to import 25,000 rows with approx 20 columns from Excel
i added a first column with an ID .... if I import whole table it dies on row 1852 for truncation problems
if I empty that column all goes in make other exel get rid of all rows except ID and bad row (contains web addresses eg http://www. blah blah)
import that excel it dies on row 2395 in other words the first offending cell was no problem if it was the 2cnd row instead of the 15th row
try to tell me this is not a random bug .... long live MYSQL
I use Asp.Net Application to upload a Excel file and then a DTS to import data from the file to the SQL2000 and finally to display the read data on the screen.
The DTS starts with setting some variables with the help of Dynamic properties.
On Succes. DTS rum 2 simultaneous Transform Data task importing data from excel to SQL2000.
This works fine for "most" of the time, but then there are the other times. One of the TDT(Transform Data task) reads nothing from the excel file. but it can read the data if i upload the same file again right after.
I have a problem with the Import of an Excel file and hope one of you can help me out.
There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)). So far everything works fine but some fields like "9760020" imports "9.76002e+006".
Dear all,, I need your help,,I'm work in website project using ASP.NET,,I have to register the users of this site,, the users are over 200,,so,,I'm thinking in away to save my time,,All the information of these users are stored in Excel file,,What I want to do is to imports these data from the excel file into a table in my database(SQLserver database),,Could you help in coding by VB.NETThanks in advance,,
Our company wants to allow our customers the ability to import employee data. Each customer's employee data changes depending on things like organisation structure etc. so the format of the data to be imported needs to change. We can do a lot of this with dynamic SQL, but are looking at moving it to SSIS as we think it will save us a lot of pain later on and for other features in SSIS.
We're stuck at a fairly early part of the process as we don't know how to dynamically import our spreadsheet. So far our best idea is to connect to the excel connection as the only step in the data task and then to use a script task on the control task, write code to connect to the excel source (set the excel source to not treat the first row as headers so we can do a 'select top 1 *...' ) then create a dynamic sql command to create the table.
Once this is done we would then have another data task that actually puts the data into the newly created table. This all sounds very difficult though. What are the options for doing what we want to do... have a feeling that we're missing something basic.
I have an Excel workbook that needs to be imported. It has three sheets, but it's really the first that is giving me fits. Each of the three worksheets have header info and instructions on the first 8 rows. Worksheet 1 then has, on row 9, the column names for the group informtion. Row 10 has the group information. Row 11 has detail column headers. Row 12 and later have detail information. Worksheets 2 and three do not have detail information, just row 9 with the column names for the group informtion and Row 10 with group information.
Here is how I am thinking of handling this. Run a script, outside of SSIS to save each sheet as a CSV file to a folder. I believe that this must be done because some of the first 8 rows are blank and according to the docs, SSIS cannot have blank rows in imported Excel sheets. Loop over the files in the folder. For each file, exclude the first 8 rows. if the file name is the first worksheet then get the next two rows and process group info get the rest of the worksheet and process detail information if the file name is not the first worksheet then get the next two rows and process group info
My questions are: Does this seem feasible? Is there an easier way to do this? Any hints or tricks that might be helpful? Any pitfalls that I should watch out for?
Any SQL guru's out there know a better way of writing this SP, its very slow.....CREATE PROCEDURE SP_LIST_ACTIVITY_CLASS_BY_ENROLLMENT( @int_activity_class_Id INT,) asDECLARE @ENROLL_COUNT INTDECLARE @WAITLIST_COUNT INTDECLARE @CONFIRM INTDECLARE @PENDING INTDECLARE @WITHDRAWN INTDECLARE @APPROVED INTDECLARE @DELETED INT SET @ENROLL_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID NOT IN (4,5,6)) --TAKES 5 SECONDSSET @WAITLIST_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 5) --TAKES 5 SECONDSSET @CONFIRM = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 3) --TAKES 5 SECONDSSET @PENDING = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 2) --TAKES 5 SECONDSSET @WITHDRAWN = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 4) --TAKES 5 SECONDSSET @APPROVED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 1) --TAKES 5 SECONDSSET @DELETED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 6) --TAKES 5 SECONDS Select A.ACTIVITY_NAME, A.DESCR, C.CUSTOMER_ID, C.CUSTOMER_NAME, cast(A.PROVIDER_CODE as varchar) + '-' + cast(FY.FISCAL_YY as varchar) + '-' + cast(AC.CEU_ACTIVITY_CODE as varchar) + '-' + cast(isnull(ax.activity_seq_number,'XXX') as varchar) as ACTIVITY_CODE, MIN(S.SCHEDULE_DATE) AS SCHEDULE_DATE, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.ECOMMERCE_IND, @ENROLL_COUNT AS ENROLLMENT, @WAITLIST_COUNT AS WAITLIST, @CONFIRM AS CONFIRMED, @PENDING AS PENDING, @WITHDRAWN AS WITHDRAWN, @APPROVED AS APPROVED, @DELETED as DELETED, AX.WAITLIST_INDFrom ACTIVITY_CLASS AS AX JOIN ACTIVITY AS A ON (AX.ACTIVITY_ID = A.ACTIVITY_ID) JOIN CUSTOMER AS C ON (A.CUSTOMER_ID = C.CUSTOMER_ID) JOIN FISCAL_YEAR AS FY ON (AX.FISCAL_YEAR_ID = FY.FISCAL_YEAR_ID) JOIN ACTIVITY_CODE AS AC ON (AX.ACTIVITY_CODE_ID = AC.ACTIVITY_CODE_ID) JOIN SCHEDULE AS S ON (AX.ACTIVITY_CLASS_ID = S.ACTIVITY_CLASS_ID) JOIN ACTIVITY_STATUS AS SS ON (AX.ACTIVITY_STATUS_ID = SS.ACTIVITY_STATUS_ID)Where AX.ACTIVITY_CLASS_ID= @int_activity_class_Id GROUP BY A.ACTIVITY_NAME, A.DESCR,C.CUSTOMER_NAME,C.CUSTOMER_ID, A.PROVIDER_CODE, FY.FISCAL_YY, AC.CEU_ACTIVITY_CODE, ax.activity_seq_number, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.WAITLIST_IND,AX.ECOMMERCE_IND --TAKES 1 SECONDS--TOTAL 36 SECONDS
I have got dbo , in the database security - logins. But I do not find it in the System - Security - Logins. When I try to re-create it throws an error. The server principle - dbo already exists.
I have got all the schemas in the database under dbo.So I cannot drop the dbo from the database.
Hello everybody... I have a SQL 2000 Script that is not working on SQL 2005.. Or 2000 for that matter.. I wanted to know if somebody can help me fix this script.
/****** Object: Database NetManage_SQL ******/ IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NetManage_SQL') DROP DATABASE [NetManage_SQL] GO
Insert into [settings](Company, LicenseKey, comments) values('DEMO COMPANY','|95|49|47|26|253|195|170|232|71|19|151|77|188|231|23|64|87|62|215|53|169|186|27|65|218|111|185|218|238|127|2|115|187|245','Product License Key') Insert into [subnet](Subnet_Name, Subnet_Mask, Parent_Subnet) values('Network Enterprise','000000000000','0') Insert into [Device_Type](Device_Type_ID, Device_Type) values(0,'Not Assigned') Insert into [Device_Type](Device_Type_ID, Device_Type) values(1,'PC') Insert into [Device_Type](Device_Type_ID, Device_Type) values(2,'Printer') Insert into [Device_Type](Device_Type_ID, Device_Type) values(3,'Router') Insert into [Device_Type](Device_Type_ID, Device_Type) values(4,'Switch') Insert into [Device_Type](Device_Type_ID, Device_Type) values(5,'Hub') Insert into [Device_Type](Device_Type_ID, Device_Type) values(6,'Web Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(7,'FTP Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(8,'Mail Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(9,'DNS Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(10,'DHCP Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(11,'Other') Insert into [Device_Type](Device_Type_ID, Device_Type) values(12,'Virtual Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(13,'Other Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(14,'VoIP Phone') Insert into [Device_Type](Device_Type_ID, Device_Type) values(15,'SQL Server')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(0,0, 'Not Assigned') Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(1,0, 'Static') Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(2,0, 'DHCP') Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(3,0, 'Reserved')
Insert into [Users](UserName, DisplayStyle, [Password],Full_Name, EmailAddress, CanDelete, Priv) values('Administrator',0,'admin','Administrator', 'admin@company.com',0,1)
I keep getting error:
Msg 128, Level 15, State 1, Line 6
The name "Contact_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
I have a SQL2000 db that uses NT authentication for the users. All users have access to the public group on the db's and are assigned security inside the application. When I restore the db's to another server (can't do master) the logins seem out of synch. Users who had access to db's no longer did. I realize each user has their own sid. 2 questions - 1 if I dropped all the users and used DTS to import the logins would that be better. 2nd - I thought (couldn't find in BOL there was a sp_fixlogin% sp that could be run that might help me with this. Thanks
I am new to database design and a lot of things never made any sense to me regarding relationships and such. I have been working on a very large design that started out well enough, but as tables were added a lot of organization fell by the wayside. Now that I am getting closer to the end, I am finding a lot of places where there should be Foreign keys, maybe some triggers, etc (I have the same data item in 5 different places, when it is deleted in one place it must go from all). Assuming that the datatypes and sizes are identical for the duplicated bits of data, can I go about making FK-PK relationships and such now that there is a lot of stuff in the database, or do I have to start from scratch and rebuild the whole thing.
The other question is much more simple:
How do I make multiple rows "unique". I have a primary key, and an identity column, but I can't add a secong primary key, and Enterprise Manager only lets me make 'int' datatypes identity columns. I have tried the "add constraints" but it asks for an expression and I have no idea what the syntax might be.
I've got a huge inefficiency in my code that I'm trying to fix. I’m coding in VB.NET using ASP.NET and an MSSQL 2000 server.
I’m working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record’s fields in the temporary table.
Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I’m dealing with the order of 50,000 records (at least) here, building and sending those statements to the server takes forever.
Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that?
Thanks in advance for whatever help you can give, -Starwiz
Can someone please help me interpret this result set below and suggeston way I can speed up my table? What changes should I make?DBCC SHOWCONTIG scanning 'tblListing' table...Table: 'tblListing' (1092914965); index ID: 1, database ID: 13TABLE level scan performed.- Pages Scanned................................: 97044- Extents Scanned..............................: 12177- Extent Switches..............................: 13452- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]- Logical Scan Fragmentation ..................: 0.86%- Extent Scan Fragmentation ...................: 2.68%- Avg. Bytes Free per Page.....................: 1415.8- Avg. Page Density (full).....................: 82.51%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Thank you.
Hi all,While using ACCESS and asp for years, I have just had to move my site onto a new server and the opportunity to move my 50MB access DB to msSQL was too good an opportunity to pass up! I used the Migration tools from MS and the data migrated nicely, I can do everything that I used to be able to do on the forum, except when a new user signs up or a member tries to edit their profile, the following error message comes up: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]The UPDATE statement conflicted with the CHECK constraint "SSMA_CC$PORTAL_MEMBERS$M_ICQ$disallow_zero_length". The conflict occurred in database "DATABASENAME", table "dbo.PORTAL_MEMBERS", column 'M_ICQ'.
I have checked the database and the dbo.PORTAL_MEMBERS table and except for the autoincrement number, uname and pwrd, everything else is set to accept nulls.
I also went though and filled in everything on the form then I could update.
the same error applies when signing up - the only difference is the name of the table
I then tried to change the data straight in the table by opening it in Server Management Studio Express and got the same error if I did not fill in everything. This tells me that it is not the code, much to my relief!
So I am figuring that there is something somewhere in the DB that I have to change? could someone point me in the right direction - and as I am VERY NEW to this database system, and do not know my way around (it took me about 10 minutes to figure out how to open a table! *LOL*) very clear instructions would be appreciated.
I used a non-printing character (hex 97) in one of my derived column transformations which made my data flow task comopnents disappear, and throw the error during execution:
"Warning: The DataFlow task has no components. Add components or remove the task."
In order to fix the package, I thought I could open the .dtsx file in code/raw form, find the misbehaving character, remove it, and I'd be good to go.
But I can't figure out how to read the .dtsx code to find the character (despite my best efforts with a hex editor).
Anyone have any ideas? Fixing this package could save me days of rework...
Looking for suggestions on this one. What I want to do have have a text file that may have any number of rows and cols (with a predefined format) that a user can update or insert into a table. The definition of the row/cols and data mapping etc, has been done, it is the mechanics of actually doing the below I would appreciate help and advice on.
As the user is an 'end-user' (and has no SQL knowledge at all) the text file to import from will be placed in a predefined location and then a small script will be executed from their PC (as it happens, it's a Mac that runs an app that can exec an SQL command on the currently open database) that will in turn run a stored proc which is then reads in (imports or updates) the appropriate tables witht he contents of the external text file.
Sorry the explanation is a bit long winded but if anyone had any practical suggestions and examples, it would be greatly appreciated.
FYI, they are running SQL 2000 on both XP Pro and W2K3 server.
Hello Specialists !Please help me - i need advice in importing textual data to SQL Server.I am using DTS with a simple process : Text(source)->Connection.I want to increase speed of importing because i have to import 4GB (1000char lines lenght) of data.Do you have any tips for me ?Best regards,Manu
I am trying to add an Imports statement to the beggining of the custome code section of my report but I recieve this error. There is an error on line 0 of custom code: [BC30465] 'Imports' statements must precede any declarations.
I need to be able to send a text file with data seperated by tabs to a stored procedure that populates my table.
I am new to SQL and SPROCs that I am not sure how to even start. If you guys have any ideas I would really like to hear them or maybe any on-line docs and examples.
I was wondering about the stability of SSIS when it comes to importing data on a real-time basis. Let's say you have a scenario where flat files, for instance, will be dropped at random intervals ranging from 1 second to 10 seconds apart and the importer has to import these files immediately.
I would imagine that this is done with a package which runs a loop sniffing the directory forever but I stand corrected on the best ways of doing it.
I'm not too sure whether SSIS is a good idea for this as lots of people have had bad comments on SSIS in real-time in my company but they cannot elaborate on why enough to convince me. I have done some pretty cool stuff and must admit that I am a fan of the technology but dont want to defend it into a corner
I got anywhere from a couple hundred to a hundred thousand records that need to be updated or inserted into their SQL Server 2005 end destination. What are some of the best ways to accomplish this? Right now we are doing it manually through line by line updates and inserts. Would I use BC or some other bulk import tool?
I'm pretty new to using SQL 2005 Management Studio. Generally speaking, it works pretty much the way I'm used to (using Enterprise Manager) as far as moving data around and designing databases is concerned. But I've been trying to import some data to my local SQL server from an online SQL database and I am getting the most bizarre results.
Basically, it appears to work perfectly, but when the import is finished, there is only one new row in the destination table.
I have tried this with two completely different online databases and I have tried importing using a query and just downloading the table as-is, but whatever happens, I just get one row! All of the databases I'm exporting from or importing into are SQL 2000 - I just happen to be using the SQL 2005 client software.
Have you heard of anything like this?
I'm appending the report to the end of this email. As you can see, it says "success, success" all the way down and it clearly states "71236 rows transferred", but when I get done, there's just one.
Any thoughts?
One explanation is that every new row is overwriting the last new row somehow, but I don't think so because the row that actually gets copied is always the first one in the record set, not the last. Unless they're being transferred in reverse order, I suppose.
I'm pretty stumped and I haven't found any useful blogs or help on the web.
Hope you can think of something because I don't have Enterprise Manager on my computer any more and it's going to be a pain to install it.
Jon
Here's the report:
The execution was successful
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Success)
- Pre-execute (Success)
- Executing (Success)
- Copying to [inframes].[dbo].[sum_shop_clicks] (Success) * 71236 rows transferred
Messages * Information 0x402090df: Data Flow Task: The final commit for the data insertion has started. (SQL Server Import and Export Wizard)
* Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended. (SQL Server Import and Export Wizard)
- Post-execute (Success)
- Cleanup (Success) Messages * Information 0x4004300b: Data Flow Task: "component "Destination - sum_shop_clicks" (37)" wrote 71236 rows. (SQL Server Import and Export Wizard)
I have been trying to follow/implement the examples in the following help topics (thanks to Jamie for these links).
Building Packages Programmatically (http://msdn2.microsoft.com/en-us/library/ms345167.aspx) Connecting Data Flow Components Programmatically (http://msdn2.microsoft.com/en-us/library/ms136086.aspx)
The problem I am having is that MainPipe is not recognized as a valid type in my Script task, even though I have the imports statements that are listed in the example. I get the message "Error 30002: Type 'MainPipe' is not defined". The other and related problem is that when I type "imports microsoft.sqlserver.dts", the intellisense offers only two choices: {}Runtime and {}Tasks. I don't see any choice for Pipeline. Can anyone tell what I am missing? It seems to be some kind of configuration/installation issue, but I have no idea how to resolve it. I have tried this on 3 different machines, with both the RTM SQL 2005 standard edition, and with SP2 installed, all with the same result. Any help is appreciated
Here is my code:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.wrapper
Imports Microsoft.SqlServer.Dts.
Public Class ScriptMain
Public Sub Main()
'
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
New Microsoft.SqlServer.Dts.Runtime.Package()
Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
I've been reading about the "table lock on bulk load" option and TABLOCK hint.
So my understanding is by default only row locks are taken out and other queries can read/write data while the bulk load is going on. However if you were doing parallel bulk loads with overlapping keys from a clustered index then they may block each other.
But if the option is enabled, you can do the parallel bulk loads without blocking because a table lock is taken out, however, other processes couldn't read/write the data until they're all done.
Is that the gist of it? I think I got confused by some misinformation. Don't all those row locks eventually likely escalate to a table lock anyway though?
I want to write a batch file that will do just that. The problem is bcp or bulkcopy never works with my code, it doesn't recognize it. Maybe I'm going the wrong way to do this, but I could use some help. Ideally I'll make the batch to do the importing via bulkcopy or something like it of a text file into a table and use windows scheduler to automate it. Be as specific as you can please, I'm very new to sql server.
