SQL Server Import Speeds
Jul 20, 2005
I have two avenues to access my primary SQL Server. (I work remotely
using a VPN connection.)
Usually, I hit the server from my local machine but I also login to a
desktop machine inside the main building using a terminal ap and hit
the server from that desktop.
Anyway, when I import files locally using the Enterprise Manager's DTS
import wizard, the process is incredibly slow. It doesn't matter if
the file is stored on my local machine or if the file is stored on the
same server that has SQL Server mounted.
It's as if the data from the file is being channeled to my local
machine and then back to the server. Shouldn't the entire process run
server-side?
When I use the EM import wizard through the terminal app, on the
desktop inside the building, the import process flies.
The only difference between the two setups is that I must add the
domain name to the address of the SQL Server and the same internal
server where the files are stored.
Can I do anything about this?
Thanks!
View 1 Replies
ADVERTISEMENT
Mar 31, 2008
Monthly, I copy a table from one database to another database. I delete the original table and copy the table back speed the performance of the query on the order of 10 to 1. Why does this work?
Detail:
I have a legacy table that a small application queries about once a month. The table was poorly designed and the query runs a date range comparison on one field and has a sub query that runs string comparison against six fields. I cannot change the calling app or table design. When the app calls the query, the call times out due to the inordinate length of time. To fix this until next months query, I copy the table out, delete the original and copy back. What changes when the table is copied to another database and then copied back? The performance of the query changes from 10sec to 1.
View 3 Replies
View Related
Feb 22, 2006
I have found an (encrypted) SP which takes ~20 seconds to run on one of our client DBs.
If I drop and recreate (or alter) the SP giving it the same contents it will run in ~1 second.
I need to know
1. why the sp started running so slowly
2. how to stop it happening again
3. how to fix it without resorting to drop/recreate (I can't exactly write a batch script to recreate all our sps every so often)
I have tried sp_updatestats and sp_recompile before running the sp to no avail...
anyone heard of a problem like this before?
cheers
H
View 11 Replies
View Related
Mar 13, 2014
Why I see absolutely no performance improvement when I spread my primary file group over 8 separate files on 8 separate disks, as opposed to having the primary file group all in one file on one disk.
I have set up 2 identical databases, one spread over 8 disks and one on one disk. Each database has a table called DATA and a column called VALUE. Value is NVARCHAR(200). I have filled each table up in both databases with 20,000 rows.
I then perform a select on each table in each database using CHECKPOINT and DBCC DROPCLEANBUFFERS to ensure I am reading from disk before each query and the execution times are identical in both databases.
I then ran the same queries against each database using a load testing tool and the batch requests per second on each DB is identical under load.
Surely the database with data spread over 8 disks should be FAR faster than the single file database as you have the combined reading power of 8 disks as opposed to 2??
Also, the same is happening for write speeds. When I create the data on both databases, the time it takes is identical on both.
BOL says it should be faster with multiple disks.
Just FYI this is on an Azure virtual machine and each disk is a locally redundant data disk that I have attached to the virtual machine.
Whether write speeds should increase with multiple disks or just read speeds?
View 8 Replies
View Related
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
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
Nov 29, 2006
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.
I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.
Trouble is that it gets most of the way through the import until it spews forth the following error messages:
- Prepare for Execute (Error)
Messages
Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard).
There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.
Does anyone know how I can get the import to work?
View 2 Replies
View Related
Jul 29, 2015
I am trying to import an xlsx spreadsheet into a sql 2008 r2 database using the SSMS Import Wizard. When pointed to the spreadsheet ("choose a data source") the Import Wizard returns this error:
"The operation could not be completed" The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine (System.Data)
How can I address that issue? (e.g. Where is this provider and how do I install it?)
View 2 Replies
View Related
Feb 18, 2015
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies
View Related
Sep 22, 2015
I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
[code]...
The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.
Secondly I need to make 2 more changes to the code posted above.
1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')
View 7 Replies
View Related
Jun 15, 2007
Hi!
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql
an Execute SQL Task: START TRANSACTION
a Sequence Container: Transaction Scoping Sequence, which contains
an Execute SQL Task: AllowedToFailPrologueSql
an Execute SQL Task: PrologueSql
a Foreach Loop Container, which contains
a Transfer Task with an icon I did not notice in the Toolbox
an Execute Package Task: Execute Inner Package
an Execute SQL Task: EpilogueSql
an "on success" arrow to
an Execute SQL Task: COMMIT TRANSACTION
an Execute SQL Task: PostTransaction Sql
an "on failure" arrow to
an Execute SQL Task: ROLLBACK TRANSACTION
an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
Thanks in advance.
Dan
View 17 Replies
View Related
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
Jan 12, 2006
Hi all,
when trying to ímport files to our database server from a client, I keep getting an error:
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1).
(SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175).
(SQL Server Import and Export Wizard)
... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it.
I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck.
best regards
Musa Rusid
View 1 Replies
View Related
Feb 25, 2008
Hi,
I'm new to SQL Server 2005 Express and SQL. I've been using Access for year and have not moved on to asp.net for my website. I understand from 1and1.com that I will need to imort a backup of my SQL Server 2005 Express file to my on site database. I don't find any way to do that. Please advise as to how I get the express version to work on my site.
Thanks!
Leesha
View 8 Replies
View Related
May 27, 2008
table objects' indexes cant copy from source sql server db to target sql server.
pls advise how to enable indexes copying in SQL server Import & Export Wizard(sql server 2005).
regards
ym ho
View 7 Replies
View Related
Mar 18, 2008
I have two databases on two different servers (an old hosting solution and a new). I need to get all the data from one server to the other but I have no clue as how to do it with MS SQL Server Management Studio Express.
I can easily make the create table scripts that will allow me to create the tables on the new server but what about all the data. I wondered if there is some way to export and import data from one server to another?
With the old SQL Enterprise Manager I was able to import or export as I saw fit but I can't seem to find anything like this with the express manager.
Anyone has any advise as I would like to avoid coding somekind of export program myself,
Many kind regards
Weinreich
View 3 Replies
View Related
Sep 22, 2004
Iam an intermediate stage programmer to the world of .Net
Can anyone show me how to import a CSV file to a table in the SQL Server database with ASP.Net and VB.Net or even C#.Net ??
Please post any code snippets that you have.
Thank You.
View 1 Replies
View Related
Mar 23, 2006
I come from China ,I would like to do study about clickstream data warehouse with MS SQL Server,now the difficult is how to import web log into database. Seeking specific procedures. Thanks! :) you can contact me :foreverichance@163.com. :)
View 7 Replies
View Related
Jul 20, 2005
A newby question:I've got a .dmp from from an Oracle database that I need to importinto SQL Server. I have no idea how to do this.If anyone know how to do this, I'd very, very much appreciate thehelp.Thanks, CC
View 4 Replies
View Related
Jan 3, 2007
hello,
i've to do this:
i've a client that exports data from an msde local db as an xml file based on a select for xml, then i have to insert this xml file into an sql server 2005 db, but not as an xml data type but as they are in the local db:
1) select something from somewhere for xml ok
2) buid the xml file ok
3) insert something into somewhere ????
View 3 Replies
View Related
Dec 3, 2007
Hello,
I am importing a CSV file that is built by someone else. The data has fields like this:
"This is one of the field's data, with some commas, which is throwing the import off"
Can you control in SQL for it to say that whenever commas appear between "", to ignore these as column separators? Right now, each comma within text between "" is considered a separate column, throwing a wrench into things.
Thanks.
View 8 Replies
View Related
Mar 20, 2008
Hi,
I'm trying to import data from one database into another database in another server, it gave me message like "character set is different..". How can I solve this problem?
Thanks
View 2 Replies
View Related
Apr 5, 2004
Hi guys,
I am looking to import data into the SQL Server database, from a CSV file..
anyone with any suggestions for how to start with it ??
Thanks heaps
View 4 Replies
View Related
Feb 22, 2005
Hi all,
I have exported all data(including database, tables wiith data) into an XML File.
How to import this xml file into MSDE.?
Can anyone suggest me with good coding in C#?
Thanks and with regards,
Madhusudhan
View 1 Replies
View Related
Feb 16, 2006
I've a question regarding the usage of Import/Export wizard of a database located on one remote server (Say A) to another (Say B).
It works fine but the problem is that Stored Procedures are not transferred between server A and B. Is there any solution for this problem?
View 2 Replies
View Related
Mar 26, 2001
I have some large (over 2GB) .txt files that are tab delimited. I have imported them into a database on my SQL Server - I get a successful message when the import is complete. However when I go to display/return the top 100 records (for example) I get a catastrophic failure error. I have been at this all day does anyone have any ideas? Thanks for any help, Jeff
View 2 Replies
View Related
Oct 4, 2000
Import Exchange into SQL Server with DTS?
Our users enter data into the MS Exchange database using custom Outlook forms. We want to import that data in SQL Server using SQL Server Data Transformation Services (DTS). We do not see Exchange in the Data Source list in the DTS wizard. Is there an easy way to do this? Maybe an OLEDB provider for Exchange?
Thanks,
Bob
View 1 Replies
View Related
Feb 28, 2003
Hi:
I am writing a custom application which will allow users to upload data in a Excel to SQLServer.
The problem I have is that Excel can come with a number of worksheets. I would ideally like to read from the default work sheet - is there a way I can specify that my code should only pick up the default worksheet?
Thanks,
Bianca
View 3 Replies
View Related
Sep 18, 1998
I just loaded SQL Server on my local NT Server. Client gave me several .dat files. I created a new device and database with the same name (catalog.dat) shut down the server, overwrote the new .dat file with the client`s file, opened up SQL Server and now the catalog database says "Suspect" and it cannot be accessed via SQL/w....
What am I doing wrong?
I have a solid Access background, but I fear that SQL Server is a totally different animal... HeeeelP!??!?!
Thanks!
Dave McGrath
View 3 Replies
View Related
Jul 15, 2004
Can anyone tell me how to import an mdf file into SQL Server 2000?
Thank you, your help is very much appreciated.
View 3 Replies
View Related
Dec 5, 2004
Hi.
I have my *.mdf and *.ldf file and need to import it to my new sqlserver 2000.
Can anybody tell me how to do it
BEST REGARDS
Badleif
View 1 Replies
View Related
Jul 19, 2007
Good day all!
Hopefully this is just a quickie but we'll see, won't we? :p
I have a DTS package that needs moving from one server to our test bed, which is completely disconnected from the network - therefore I have to transport it by removable media (USB flash drive for instance).
Is there anyway I can do this? I've tried doing a "save as..." Structured Storage File (*.dts) but then I have no clue as to what to do with it to get it back in to the database!
Any suggestions me hearties?
Yarr!
View 10 Replies
View Related
Jul 23, 2013
I need to import several million records into a SQL table from a pipe-delimited text file. I have tried SSMS 'Import/Export' which is slow so I tried bcp from the command line.Importing records into an empty table with no indexes worked a dream. However..Tried to import 13 million records into an indexed table. After almost 50% of the import (6721000 records) bcp appears to have hung and the Server disk is flashing continuously.
View 7 Replies
View Related