Is there a way in SQL 2005 Express Edition to import/export data from another MSSQL Server? Something similar to the right click/ import table functionality of MSSQL2000.
I've read similar threads which all recommend using MS Access but there has to be a better way, right?
regards,
dj
I have backup.sql created by mysql. How can I let mssql server to import such file?
View 3 Replies View RelatedHi is there a way to emulate the MSSQL import wizard with a command prompt so I can shedule an automatic import form a csv file?
Thansk!
I have connected to my mysql db with myodbc. When I start importing I get errors on some varchar variables.
Some of my varchar(255) becomes varchar(-1) when I import. When this happens smssql fails to create table.
Anyone have a clue?
Thanks.
Getter..
the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in text
no space, no symbol, no line break between each record
i must use the length of char to define it.
Please help me how to import into mssql
the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in db
no space, no symbol, no line break between each record
----------------------------------------------------------
I try bcp method, but some error happens. Please give me suggestions. thx
I run the following code in query analyzer.
BULK INSERT Chain.dbo.POLL59
FROM 'D:POLL59.DWN'
WITH (FORMATFILE = 'D:cp.fmt')
?????
it shows the error
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:cp.fmt'.
***
For your information
data file
000000011301220051222000192000000000011301320051222000030000000000019067420051222000000001<there are many space >
bcp.fmt file <I use tab to separate and use ascii>
8.0
4
1 SQLCHAR 0 4 "" 1 PLUEVT ""
2 SQLCHAR 0 9 "" 2 PLUSKU ""
3 SQLCHAR 0 8 "" 3 PLUFRD ""
4 SQLCHAR 0 9 "" 4 PLUPRC ""
!!!!
I try to edit collation name in Chinese_Taiwan_Stroke_CI_AS or others, but the error also happens.
hi
im using ms sql server 2000 with the enterprise manager
i want to make a relationship between to tables.
let say first table has 2 column: ID, RegionName
the other hase 3 column: ID, employeeName, RegionName.
i want that while im trying to add new record from the enterprise manager to the second table, when i will try to assign value for the RegionName i will get a combobox with all the RegionName that the first table contains.
thanks a lot
Dovalle
I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME
I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?
Hi all,
Iam reading this forum from some time but its my first post here
Today i found by accictend this really nice PDF poster with SQL 2005 internal System Tables and thought that maybe someone here will be interested having it.
Poster is really huge, just like db schema on it ;)
But ok no more words, just grab it from here if You want http://rapidshare.com/files/79265626/sql.2005.sys.tables.pdf
Regards, Tom
PS.
1. If You can't read PDF after download with FireFox (like me) turn off pdf plugin and then download starts normally
2. If someone from admins think that its not good forum for this post pleas move it to somewhere that it best fits.
Hallo everyone
Now I have to analyse so many Software systems that my company is using and I have to extract the Datamodels behind these systems.
One of these systems is developed with a MSSQL server backend. Now I have to open this server tables and see the table structures and data directly how we see in Access.
Please help me by telling the way to open this database.
any help is very much appreciated.
:) thanks in advance.
menardo writes "Hi there!
I'm a very newbie in SQL programming. Can anybody help me how do I start to learn scriptings.
Can you give me the process or scripts on how to export it??
Thanks in advance.
-menardo"
Hi,
Can I give a sequence to each table that I will try to import from one database to another by using import /export wizard
Regards
Vaibhav
Hi there,
I am having difficulties in importing table from one sql server database file to another sql server database file.
A few months ago, I converted access file to ms sql express file. I had made many changes on the ms sql express file, however, the data in this file isn't the latest as the old system is still being used. Now, I want to deploy my new system, I need to import in necessary tables from the old system database, as well as, I want to retain the tables and data I created on the ms sql express file that I have been using so far for development and testing.
May I know how to import tables from other database? Just as in ms access where we can import tables from other access file. I'm using sql express 2005 and sql server management tool. Any advice/help is very much appreciated.
Thanks....
Still really new at all this, but learning lots thanks to this forum.
I was wondering - is there a way to import Access tables into my SQL Server 2005 ?
(The Data and the Table Design?)
I would like to know:
How can I import tables from the Enterprise Manager to Excel (copy and paste)?
Is it possible?
thanks
I am really new to SQL Server so be patient i guess. I have installed sql server 2005 on my home computer but at first i wasnt able to connect to the server at school where i had started creating tables already. Instead of going up to the school i opted to do it from home and start over. Well I am wondering if there is a way to copy all the tables that i have created on my home computer and just import them into the database on the server at school. Hope this isnt confusing. Anyways, this forum always has helped me in the past, so i am confident someone will be able to point me in the right direction. Thanks in advance.
View 3 Replies View Relatedi am using sql server express edition.
i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.
what is the fastest option? some of these tables hold over 1m records.
so ideally i would like to schedule a refresh or update job outside busy hours.
I have heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.
many thanks
luzippu
I have some Access tables that I want to import into SQL. I can do this using the AllTasks/ImportData. However if my Access table has a zero length string in a field this is imported into the SQL table as <NULL>. How can I make it import it as a zero length string ?
View 2 Replies View RelatedI am an SQL newbie and despite 3 hours on the Internet this morning I am still confused!
I have some XML tables that I want to import into my SQLExpress database.
I have followed the example at http://support.microsoft.com/kb/316005 How to import XML into SQL Server with the XML Bulk Load component.
I understand the example but when I run the vb code:
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=MySQLServer;database=MyDatabase;uid=MyAccount;pwd=MyPassword"
objBL.ErrorLogFile = "c:error.log"
objBL.Execute "c:customermapping.xml", "c:customers.xml"
Set objBL = Nothing
I get an error message saying that ActiveX cannot create object 'SQLXMLBulkLoad.SQLXMLBulkLoad'.
I have SSMSE and the databse on my PC harddrive.
What am I missing here?
Thanks in antcipation.
Chris
Hi, I am trying to import an XML file to SQL tables using SSIS, Data Flow Task --> XML Source --> OLEDB destination.
I have both the XML file and its xsd file.
The extraction of the XML file output 5 tables (mc, r, i, bc and c, have relationship among them). My problem is that one of the tables is empty (table mc). I tried to add a "root" node to the XML file (the xsd file was modified too), and all 5 tables were populated correctly. Is there any other way to fix this problem without manually changing the XML file?
The XML file looks like this:
<mc MajCmd="Atlantic">
<r Region="East">
<i Installation_UIC="M00146" Installation_Name="Cherry Point">
<bc BC="111" BC_Title="Airways">
<c Restoration_Cost_C3="0.0000" Comment_Type="" Override_C_Rating="" Comment="" />
</bc>
</i>
</r>
</mc>
The xsd file looks like this:
<?xml version="1.0" encoding="utf-8"?>
<xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
<xs:element name="mc">
<xs:complexType>
<xsequence>
<xs:element name="r" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="i" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="bc" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="c" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
</xs:complexType>
</xs:element>
..........
</xsequence>
<xs:attribute name="BC" type="xstring" />
<xs:attribute name="BC_Title" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="Installation_UIC" type="xstring" />
<xs:attribute name="Installation_Name" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="Region" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="MajCmd" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element ref="mc" />
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>
Thanks!
ChaInn
I have problems importing tables from db2 using the import wizard using IBM OLE DB Provider for DB2
The tables and data ('Preview...') can be viewed but when trying to map the columns ('Edit...' next to the tables) or import the data an error is recieved
[DB2/NT] SQL0443N Routine "SYSIBM.SQLCOLUMNS" (specific name "COLUMNS") has returned an error SQLSTATE with diagnostic text "SYSIBM:CLI:-727". SQLSTATE=38553
Schemas is used in both DB2 and Sql 2005
There is no problem importing to Sql 2000
I'm trying to migrate some tables from 2000 to 2005. I used the wizard in SSIS to create a package bringing them in.
The first database I did this on it worked great, in no time.
However, I tried this on another database and I get a LOT of these:
Information: 0x4004800C at {0417E15E-7306-4DAD-BEDD-1FDB17390EB0}, DTS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Now, some things to note:
1. I'm trying to do about 170 tables at once - although none of them are bigger than a MB
2. Copying files between these 2 servers is also taking a long time
Is it too many tables at once? Is it the actual pipeline between the servers? The message above seems to indicate that it is a problem local to the machine, and maybe our file-copying problem is just another issue entirely.
Hi all,
I've just installed the Sql Server 2005 Express Ed + Management Studio Express and I can't get how to import data from a MS Access 2003 file. From what I've read is not possible to do through the Management Studio Express so is there any other tool to do that?
Thanks in advance,
Marc Soleda
how do i import or what is the best way of import from xml file to a sql server tables?how do i export or what is the best way of export from sql server tables to xml file?thanks
View 4 Replies View Relatedhi
guys i need to import all tables from sqlserver to informix ...............
any idea really appreciated
Hi all,
I am a newbie to .NET and would appreciate all your valuble
suggestions. I have and issue were I am trying to import data from a
few selected columns MS Access and a couple of columns in SQL
Server Table Y and trying to populate another table X . Both
tables X and Y are in the same Database . I am wondering if I could
design a custom package for this task.
Ananth
Quick Question!
If you have both invoice header lines and invoice detail lines in a comma delimited file, how can I get the data in the file to be imported into two different tables. I can produce a text file eg:
1,20,10/03/2002,39 High Street Any Town,,
2,20,Fluffy Slippers,2,Red,10.99
2,20,Pyjamas,3,Black,15.99
2,20,Trousers,1,Lilac,24.99
1,21,10/03/2002,11 Gibson Close,
2,21,Sandles,1,Black,12.99
2,21,Shoes,4,Blue,23.99
1,22,13/03/2002,45 Mill Street,
2,22,Womble Feet,4,White,16.99
2,22,Glass Slipper,1,Transparent,23.99
Lines with 1 in the first column should go in the InvoiceHeader table
Lines with 2 in the first column should go in the InvoiceDetails table.
I have tried with DTS but to no avial - ActiveX scrips in the Transform Data Task can only seem to access one data destination - it one table not two.
Any Ideas?
Julia
Can someone tell me if there's a way to export data from one database to another without losing the primary key information that has been set in the source database?
Please respond to URL. Thanks!
CC
I have 1 Excel spreadsheet. It has about 50 fields. I need to import this into my Database.The problem is; there are 2 tables that I need to import this into. These 2 tables are related with [ID].So, I need to split the data from 1 spreadsheet between 2 SQL server table. and keep the records related.
View 4 Replies View RelatedHi,
I am using DTS in SQL server2k
What is the best way to insert CSV records, where 1 record maps onto multiple tables with parent/child or PK/FK relationship.
eg.
CSV record
(ID, param1, param2, param3)
ParentTable(ID as PK)
Param1Table(ID as FK in ParentTable, param1)
Param2Table(ID as FK in ParentTable, param2)
Param3Table(ID as FK in ParentTable, param3)
Currently i am using multiple Transform Data tasks with ActiveX script. But it seems to work slow.
Thanks and regards,
Henry
Hi There,
I am trying to create a DTS package that will import dbf tables. My problem is that the data was created back in the pre-dos 5 days, so to save room they took the dates and convert them to 2 characters.
Now I would like to import these tables and conver the date back. I do have a Function that I can run in FoxPro to "Unpack" these dates.
CODE
Function UnPackDate( cDate )
*!*PARAMETERS: f_datestr - Character (manditory). This should be a two
**!* byte string created with PACKDATE().
*!*
*!* RETURNS: A date from 1/1/1970 and 11/29/2126
If EMPTY(cDate)
Return {//}
Endif
Local nDate
nDate = (ASC( LEFT(cDate,1) ) * 256) + ASC( RIGHT(cDate,1))
Return ( CTOD("01/01/1970") + nDate - 8225 )
*================================================= ======================
Does anyone know how to take this function and make it work while doing an import?
Thanks
Sue
Hi
I have to import some Access 2003 tables into a SQL Server 2005. Since I have to do this every 6 months (different Access db into same SQL-Server) and since I have to check every single row I can't do it with the import data wizzard. I need to import the access tables into "temporary" tables in sql server and from there I'll put the rows into the correct tables...
Does anyone know how to import Access tables into SQL-Server 2005 with C#?
Thank you for your help!
Michael
Hi,
I want to import into an MDB table a csv file.
I'm trying to use the bulk copy table.
my function is:
SQL = "SELECT * INTO [my_table] FROM [ODBC;Driver=Micrsoft text driver (*.txt; *csv) ;Dbq=c:\;Extensions=asc,csv,tab,txt;].table.csv"
db.OpenEx( "Driver=Microsoft Access Driver .mdb);DBQ=c:\access.mdb;", CDatabase::noOdbcDialog );
db.ExecuteSQL( SQL );
when i run this function i get an error : "You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database"
when i try to import in the same way a dbf file (insted the csv file) with VFP it's working well.
what seems to be the problem? how can i fix it? or if some one know how can i import a large csv file into access DB in an efficient diffrent way?
thanks ishay