As title, what's the best practise of importing XML data into SQL Server 2005?
I have found this one. http://support.microsoft.com/default.aspx/kb/316005. Is it good enough?
The XML is generated by another system on daily basis. So, the import should be able to handle insert and update cases. How can I do that? Thanks!
Hi Folks,
I am about to upgrade my main database server (5 db's - largest 16Gb) from
NT 4 SP 6a / SQL Server 7 SP3 to Windows 2000 SP 2 / SQL Server 2000 SP 2
I am planning to detach the db's, backup to tape a few times and then totally
trash the server, rebuilding it with the new software, restore the db's from
tape and the reattach the db's.
Any reason I should not use this method and can folk advise the best practice
way of achieving this?
Regards
Paul
________________________
Paul Starling
ERP Database Administrator
Hi!I have 6-7 tables total containing subobjects for different objects like phonenumbers and emails for contacts.This meaning i have to do some querys on each detailpage. I will use stored proc for fetching subobjects.My question therefore was: if i could merge subobjects into same tables making me use perhaps 2 querys instead of 4 and thus perhaps doubling the sizeof the tables would this have a possibility of giving me any performance difference whatsoever?As i see pros arefewer querys, and cons are larger tables and i will need another field separating the types of objects in the table.Anyone have insight to this?
View 5 Replies View RelatedI want to log all changes made to a table (only updates, since there will be no deletes or inserts).
I would like to see the user who changed it, the date and time, fieldname, old value, new value.
If more fields are changed during the update, than add more records into the logging table.
What is best practise to achieve this?
Thanks,
Frans
Hi,
I have some data that is updated every day but I don't know when. I'm trying to make a solution that runs a SQL query to check if this data has been updated. If it has, I'll send the updated data with FTP as as text file.
How would you solve this?
My idea is to have 2 SSIS packages.
- Package1: One runs at the same time every day (inserts any missing updates to a table)
- Package2: One runs every hour to check the missing updates table, and runs Package1 if any update for a missing data is found.
My only worries is if Package1 is running and at the same time Package2 decides to run Package1 then I could get into trouble if I'm using temp tables with the same name for the text file updates etc.
Thank you.
I need to load a lot of Excel, CSV, ... etc. files. These files have hundreds of columns and I need to validate the data. Some are simple range type checking, some are more complex checking involve multiple columns.
There may have several hundreds of such rules. And I may need to let the program to automatically correct some invalid data in the future.
Where to implement it in SSIS?
Or just load the files without any checking (all type to text), and checking using T-SQL?
(BTW, I don't have biztalk server).
Thanks in advance.
Read more >> Options >>
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 ????
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.
I have an Older version DB in SQL and would like to import in into the newly installed SQL Server 2005 (which comes with visual Studio 2005)...
Please advise how to do it!.. i can't seem to find a way...
Hi, hoping I can get a few view on a question I have relating to the above.
I am new to Stored Procedures and Triggers and I am trying to understand 'best practice' a little better. Here is my question: If I have a table that stores information, and when any field in that table is updated (and changes) I would like to inactive the row, prior to change and then add the change by way of a new, active row. This way I can see what it was before and that it's inactive, and what the active value is.
Hope this makes sense, if this is the wrong way to manage change history any suggestions would be appreciated.
A second question I have is as follows: If I have a table that stores a number, based on that number, what would be the best way to create new records in a different table that pulls from the first table. Where the number stored in table 1 represents how many times the record is to be created in the second table.
Thanks. If anyone needs more data, please feel free to ask, I will help as best as I can and appreciate any advice & comments that you can give.
Paul
I can not find "import/export" in SQL Server 2005 "microsoft SQL Server Management Studio Express" How do I move a database from SQL Server 2000 to SQL server 2005?
View 5 Replies View RelatedAll,
Subject: SQL Server 2005 - How do I import data from an Oracle Data Pump file?
Tried looking in the SQL help for this and it's not very forthcoming on how
you do this.
How do I achieve this? Can someone send me an example SSIS package OR tell
me the steps OR point me towards a resource that could help me?
Look forward to your replies.
Many thanks,
Regards,
Richard.
i hava tried installing the "SQL2000SampleDb" but the db is not
on the db list after the install
Hi,
I know how to import the .rpt file into Access database using import.
can some body tell about how to import to SQL Server 2005 database, both front end (using some utility or interface) and back end (directly accessing the server) solution.
Thanks,
Fahim.
I'm using SQL Server 2005 Management Studio Express, and trying to import data from an Excel spreadsheet into a new table. I'm used to doing this through Enterprise Manager in SQL Server 2000, but I'm not sure how to do it in the new interface. Is there a way to do a simple data import without also installing Business Intelligence Development Studio?
View 3 Replies View RelatedI am trying to import data from our as/400 to SQL server. We have a new server with SQL 2005, data is restored so now I am trying to convert our DTS over to SSIS but I can's seem to get a connection set up that will talk with our as/400. One note on our current sql 2000 I have no issues. Our sql 2005 is 64 bit, not sure if it makes a difference but thought I would mention it.
I am trying to set up connections so I can copy data from as/400 to sql 2005. I have a DNS set up on server called IPTSFIL which point to library iptsfil. I then need to create my source. There is no longer an ODBC option so I am trying ole db source. I created a connection but it€™s not working. In the connection there is a data link if I click on that I can point it to my DSN (IPTSFIL). But when I try to test it then thinks server name is IPTSFIL which is not the case and so it comes back with a port error due to that not being the system name. What am i doing wrong?
I read some people use data reader but I don't understand how to config that. Any input on how to help me get this working either using ole db or datareader would be appreciated.
I did test a client access session on server and I connect just fine. Also if I do a cwbping everything comes back sucessful.
Thanks in Advance,
Stacy
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
So I got 2 classes one I wrote to interrogate databases using normal ADO:Mine:SqlConnection myConnection = new SqlConnection(m_sConnectionString);SqlCommand myCommand = new SqlCommand(sQuery, myConnection);myCommand.CommandTimeout = 120; // 60 Seconds TimeoutmyConnection.Open();SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);return result;Microsoft WaySqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);return ((SqlDataReader)dbSvc.ExecuteReader(dbCommand));What's faster?My way:SqlConnection myConnection = new SqlConnection(m_sConnectionString);SqlCommand myCommand = new SqlCommand(sQuery, myConnection);myCommand.CommandTimeout = 120; // 60 Seconds Timeout// Use a DataTable – required for default pagingSqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);DataTable myTable = new DataTable();myAdapter.Fill(myTable);myConnection.Close();myConnection.Dispose();myConnection = null;return (myTable);Microsoft Way:SqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);DataTable dtData = null;DataSet dsData = dbSvc.ExecuteDataSet(dbCommand);dtData = dsData.Tables[0];return (dtData);Comments? Ideas?Al
View 4 Replies View RelatedI have master tables that I will be updating from our ERP system. Some examples I have seen take an approach of dropping a table in SQL server then creating it again before importing; some, and probably my choice, append and update; I have not seen an example where records are all deleted then the data appended afterwards. Of the three approaches which is generally regarded as best practise / most efficient?
View 1 Replies View RelatedHello,
I created an ASP.NET 2.0 web page with VB.NET. I’m running SQL
Server 2005 database with two tables, “Departments” and “phonelist”. In
“phonelist” I have 10 columns, “ID”, “Facility” “FName”, “LastName”,
“Title”, “Department”, “Location”, “Phone”, “Beeper” amd “Cell”.
I have an Excel that has 4 columns, “NAME”, “TITLE”, “PHONE” and
“BEEPER”. I would like to know how can I import the fields into my SQL
2005 database?
Hi all,I am developing a web application and the back end is MySQL database.
Now I want to shift all the data to SQL Server 2005.
Is there any method to do it?
Thanks
Tomy
Hi there
I am importing data from a database on one server to the same database (exactly the same table structure etc) on another server. I am specifying the data from certain tables on the source database which are completely empty on the destination database. The tables on the destination database each have a primary key column (Identity Specification = YES, Is Identity = YES, Identity Increment = 1, Identity Seed = 1, Not for Replication = YES). The import fails on the first table (YvGroup) whose primary key, "GroupID", is the foreign key within other tables (Relationship is set).
The error i'm getting is as follows:
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only column "GroupID". (SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed. (SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - YvGroup" (40)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard)
I'm sure the most important error is the first one, "Data Flow Task: Failure inserting into the read-only column". Is this happening because i'm trying to import data into primary key columns that are usually populated by an auto incremented integer and is seeing it as read only? Or is it due to the relationship with other tables? In which case if you have a table structure set up with relationships how would you ever be able to import data?
How can I get round this?Cheers
hi all,
how to import data from excel to sql server 2005 using asp.net 2.0 and c#
Ashok
Where is that neat little "Import/Export" DTS Wizard like I had in SQL Server 200 Developer?Or am I going to have to mess with the script generation features?
View 2 Replies View RelatedI have imported a TXT file every week on SQL 2000. Options,
Fixed field
File Type: ANSI
Skip rows: 0
Row delimited: {CR}{LF}
Text qualifier: DoubleQuote{"} (default grayed out).
It works fine. On SQL 2005, I cannot.
If I keep same settings as SQL 2000, I cannot see file from Preview. If I change to Delimited, I can see file but there is only one column.
Any suggestion?
Thanks
ZYT
i wat to import data into sql server 2005,problem is data is not in tabular form.,can anyone help???
View 8 Replies View RelatedHallo!
I would like to know how I may import a .bak file (produced by SQL-Server 2005) into SQLExpress.
Thank you very much for any help !
Regards,
Fabian
I have installed SQL Server 2005 Express on a laptop for development purposes and would like to import some Access databases that I was using coupled to programs written in VB 6.
If using SQL Server 2000 I could use the management studio to automate the import but I can not find any way to do this in Management Studio Express. Would have thought that this would be the typical migration route - Access to Server Express
Is there some way to automate this procedure?
Thanks
I am trying to import one table from postgreSQL to SQL Server 2005 using sql server import and export wizard. When i test the connection after providing data source, location, username, password in the Data Link Properties section I get the message "Test Connection Succeeded". As soon as i press next to go onto next step i get the following error.
TITLE: SQL Server Import and Export Wizard
------------------------------
Cannot get string literals from the database connection "Provider=PostgreSQL.1;User ID=sa;Data Source=localhost;Location=TestMasterMap;Extended Properties=".
------------------------------
ADDITIONAL INFORMATION:
Object reference not set to an instance of an object. (DTSWizard)
------------------------------
BUTTONS:
OK
------------------------------
I have tried all sorts of different combinations for these properties but it always fails on this step. Can anybody help me with this?
Hi,
I have created a SSIS dtsx package file on my file system that I want to manipulate in .NET
The package gets an access DB file and the a Preparation SQL Task creates the necessary sql statements for the data flow task. The data flow maps the tables from the source to the destination and then if you execute the package, it copies the Access DB into SQL Server.
My problem is, I want to manipulate this package in code, so that I can change the path to the source Access DB and then re execute the Preparation SQL Task so it recreates the new SQL statements, and then I want to update the data flow so that I it uses the new SQL and creates the necessary tables.
How is this possible in .NET
I have looked everywhere and have found some topics but with no luck.
Can anybody please help me
Hi! I have XP sp2, with SQL server 2005 Developer Edition 9.00.3054.00 and also have Microsoft SQL Server Express Edition 9.00.3042.00, well my problem alway I want import data into any server from any data source I recive this message:
TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified. (System.EnterpriseServices)
------------------------------
Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified.
------------------------------
The system cannot find the file specified. (Exception from HRESULT: 0x80070002)
------------------------------
BUTTONS:
OK
------------------------------
I have this name space but, I try for many ways and I can't import data in my server, What can I do???
Thanks a lot.
I'm importing a fixed or tab delimited text file into a SQL Server database. When I use SQL 2000 import wizard about 10,000 rows are missed or skipped, but when I use SQL 2005 all the rows are imported successfully.
If anyone has any ideas, I would greatly appreciate it :-)
Thanks!
Hi
I have tried to import Pervasive Database to SQL Server 2005.
I am not getting any option in Data Source for Pervasive So I tried following
I have Provided the following Connection String to SQL server Import and Export Wizard (in .net framework data provider for ODBC)
Connectio String: Dsn=DEMODATA;Driver={Pervasive ODBC Engine Interface}
DSN: DEMODATA
Driver:Pervasive ODBC Engine Interface
but i m getting error that "Can not get supported data types from th database connection."
Can you please look in to it and tell me possible ways by which I can import pervasive Database into SQL server 2005.
and let me know if there is any other tool by which we can see "Pervasive ODBC Engine Interface"
in Data Source
Thanks,
Arun