SqlServer 2005: Importing Data: Idenitity Fields Getting Made Into Non-identities
May 16, 2007
Hi;
We just migrated to SqlServer 2005. When I import tables from from
SqlServer database into another the identity fields get switched off
from being identities.
How can I prevent that from happening?
Thanks in advance for any information
Steve
-------------------------------------------------------------------
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools2005.090.1399.00
Microsoft Data Access Components (MDAC)2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer7.0.5730.11
Microsoft .NET Framework2.0.50727.42
Operating System5.1.2600
View 3 Replies
ADVERTISEMENT
Jun 10, 2008
hi all,i have a table called Employee in sql server 2005 as shown beloweid ename esal001 john 5000002 lina 4000 I need to fill the above table from xml file(Emp.xml) as shown<Root> <Employee> <eid>003</eid> <ename>rose</ename> <esal>2000</esal></Employee><Employee> <eid>004</eid> <ename>sam</ename> <esal>6000</esal></Employee></Root> plz try to help me i am in need or give me any helpful suggestions thanks in advance
View 6 Replies
View Related
Jun 7, 2007
First, thank you all for the help so far.My new, broader Question is this: What is the most efficient (read easiest) way to code in an inset statement into a remoste SQL 2003 database from IIS 6 running asp.net 2.0?Here is the situation: I have a form that requires the user to be signed in. I then want to get the value of a text box TBOne(String), a dropdownlist DDOne(Text), another Textbox TBTwo (int) and insert these values in to a NEW row of a database (DBTest) into table TTest, with the UserName (String), and todaysdate (SmallDateTime).I am a JSP programmer, and it's fairly easy to do in JSP, but I am trying to leverage the SQL Datasource, to make my life easier.Is there a simple way to do this? Something like:String username=page.someintg.User.name;String TBOne=TBOne.text;...Insert into DBTest.TTest Values(username, TBOne, ...) ? TIA Dan
View 5 Replies
View Related
Apr 28, 2007
Importing CVS to SQLServer 2005 in a web application:
I need to import CSV file to a table. This is a .Net 2.0 web application running in the WebServer which has .Net 2.0 installed. SQL Server 2005 is a database in another machine. What are my options? Would SSIS work in WebServer if no database installed in that machine.
Thanks
View 1 Replies
View Related
Feb 7, 2008
Hi,
In my project i have to import csv file into DB, i have written a C# windows application as below...
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;namespace FedExDataAnalysis
{
public partial class Form1 : Form
{public Form1()
{
InitializeComponent();
}protected string[] arrayValues;
//string row = "";
// int cnt = 0;private void Form1_Load(object sender, EventArgs e)
{string path = "c:\2008-01-09_01-00_FedExInv_232316837.csv";
importCSV(path);string path1 = "c:\2008-01-10_05-18_FedExInv_232316837.csv";
importCSV(path1);
}public void importCSV(string path)
{string row = "";int cnt = 0;
using (StreamReader sr = new StreamReader(path))
{while (sr.Peek() >= 0)
{
cnt = cnt + 1;
row = sr.ReadLine();arrayValues = row.Split(',');if (cnt > 2)
{if (arrayValues.Length > 0)
{
insertrecord();
}
}
}
}
}public void insertrecord()
{string str;
int Recordaffected;string inserted = "no";
string constr;SqlConnection conn;
SqlCommand cmd;
constr = "Data Source=I07;Initial Catalog=JuiceApp;User ID=sa;Password=dj$ql1736ju1c30ca1a";conn = new SqlConnection(constr);
conn.Open();
try
{str = "insert into xt_importcsv (Bill_to_accountno,Invoice_date,invoice_number,Original_ammountDue,Current_Balance,express_or_ground_trackingid,";
str = str + "transportation_charge_ammount,net_charge_ammount,shipment_date,actual_weight_amount,rated_weight_ammount,recipient_name,recipient_company,";str = str + "recipientaddress_line1,recipient_city,recipient_state,recipient_zipcode,recipient_country,shipper_name,trackingid_charge_description,trackingid_charge_ammount,";
str = str + "trackingid_charge_description1,trackingid_charge_amount1,trackingid_charge_description2,trackingid_charge_ammount2,trackingid_charge_description3,trackingid_charge_ammount3,";str = str + "trackingid_charge_description4,trackingid_charge_ammount4) values ('" + arrayValues[0].ToString().Replace(""", "") + "','" + arrayValues[1].ToString().Replace(""", "") + "','" + arrayValues[2].ToString().Replace(""", "") + "','" + arrayValues[4].ToString().Replace(""", "") + "','" + arrayValues[5].ToString().Replace(""", "") + "','" + arrayValues[8].ToString().Replace(""", "") + "','" + arrayValues[9].ToString().Replace(""", "") + "','" + arrayValues[10].ToString().Replace(""", "") + "','" + arrayValues[13].ToString().Replace(""", "") + "','" + arrayValues[18].ToString().Replace(""", "") + "','" + arrayValues[20].ToString().Replace(""", "") + "','" + arrayValues[25].ToString().Replace(""", "") + "','" + arrayValues[26].ToString().Replace(""", "") + "','" + arrayValues[27].ToString().Replace(""", "") + "','" + arrayValues[29].ToString().Replace(""", "") + "','" + arrayValues[30].ToString().Replace(""", "") + "','" + arrayValues[31].ToString().Replace(""", "") + "','" + arrayValues[32].ToString().Replace(""", "") + "','" + arrayValues[34].ToString().Replace(""", "") + "','" + arrayValues[80].ToString().Replace(""", "") + "','" + arrayValues[81].ToString().Replace(""", "") + "','" + arrayValues[82].ToString().Replace(""", "") + "','" + arrayValues[83].ToString().Replace(""", "") + "','" + arrayValues[84].ToString().Replace(""", "") + "','" + arrayValues[85].ToString().Replace(""", "") + "','" + arrayValues[86].ToString().Replace(""", "") + "','" + arrayValues[87].ToString().Replace(""", "") + "','" + arrayValues[88].ToString().Replace(""", "") + "','" + arrayValues[89].ToString().Replace(""", "") + "')";cmd = new SqlCommand(str, conn);
Recordaffected = cmd.ExecuteNonQuery();if (Recordaffected == 1)
{inserted = "yes";
}
}catch (System.Exception ex)
{Console.WriteLine(ex);
}
finally
{
conn.Close();
}
}
}
}
It is working fine.. It is importing data into DB. But the problem what m facing is , in the code above m using the split function and assigning the row of the CSV file into arrayValue. the prob is if the arrayValue has two entries lk: Name: "john,Mikel " it will split it separately and insert into the next field in the DB... M not getting how resolve this... Please help...
Thanks,
Chai.NV.
View 3 Replies
View Related
Jul 25, 2007
I would like to import data to a sqlserver express database. The database I am interested in importing was created in MSDE. I tried backing it up and using the restore function in sqlserver express but with no success. I have not been able to find an import function in the Management Studio Express interface. Is it possible to import this information, or should I get busy with my data entry?
View 4 Replies
View Related
Feb 25, 2008
I am currently Using SQL Server Express for testing purposes while waiting for SQL 2008 to be issued. (Our project will take some 2-3 years to complete so concerns about using a 'new' version of SQL Server are not pertinent). We have ordered this week Visual Studio 2008 as the shrink wrap version is due out the end of this week.
I have been able to migrate all our data from the old database (Superbase) but only field names and field types.
Indexes and validation stuff is lost.
The problem I have is that:
I can only import to an empty database, therefore if I start to create Indexes and otherwise modify the structure of this database in SQL Server then I will lose all this work on the next occasion I need to import (as Superbase will still continue to be our firm's current daily data handling tool for another year or two)
My thoughts where to create a copy of the original import, develop the constraints, indexes etc. on this secondary copy. then (on the day we are ready to migrate) empty the copy of data and import the data only
Does SQL Server Express have the tools to do this?
If not then are my best options to use SQL Server Beta until the new software is realeased (but notice that Visual Studio 2008 does not like SQL2008 - is there a fix for this?)
I hope this is the right forum for these questions
Thanks in advance
View 4 Replies
View Related
Nov 8, 2007
OK so there is some data in an Oracle DB that I have to summarize based on grouping info stored in a SqlServer DB. How can I import the Oracle data into a SqlServer temp table using SqlServer Express? Thanks.
View 2 Replies
View Related
May 12, 2004
I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.
View 4 Replies
View Related
Sep 12, 2001
Help -
I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
rb
View 2 Replies
View Related
Sep 20, 2006
Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
View 1 Replies
View Related
Nov 30, 2007
Hello,
I am using the SSIS import/export wizard to import an Access table into a sql server 2005 database. The table has fields in Arabic (name, last name, etc.) and non-Arabic fields (gender, phone number, category, etc.).
The destination table has nvarchar columns.
After the import, I can see the Arabic characters in the destination table, but they appear in inverse order (from left to right). In Access (or Excel), Arabic fields appear as they should (from right to left) and non-Arabic fields are OK as well (from left to right).
If I do a simple copy-and-paste of a "correct" Arabic text into the table, the result is still wrong (inversed letters)...
Please help, I can't see what else to do.
Thank you.
View 4 Replies
View Related
Mar 25, 2008
Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
View 4 Replies
View Related
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
Mar 28, 2006
I am getting a dump of information from SAP into an Excel file. I would like to take this excel file and import the data into a SQL 2005 Express database.
Is this possible? Or will I need the full version of SQL to do this?
Thanks
View 2 Replies
View Related
Feb 13, 2008
Please help - I am dealing with data transfer from multiple tables to multiple tables - what is the best practice to get this done? So far I have my connection to MySql figured out and I am using a DataReader source to get the data. What is the approriate Data Flow Transformation to pump data into three different tables in MS Sql 2005 database from three different tables in MySlq database? So far I was able to figure out how to do this for one table only...
Thanks,
Galina
View 12 Replies
View Related
Oct 11, 2006
Guys,
I am new to DTS 2005; having trouble on how to connect to MS Access to pull data? what kind of connection manager should I use (OLE?) and what specific Data Flow Source type? Please respond.
Thanks
View 5 Replies
View Related
Feb 21, 2006
When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.
The scenario is as follows:
Create table:
create table test3 (
name varchar (18) ,
tbname varchar (18)
)
Create and populate table:
create table maxtable (
tablename varchar (18) not null,
[...]
)
Try to insert into test3:
insert into test3 (name, tbname)
select i.name, o.name
from dbo.sysindexes i, sysobjects o, maxtable m
where i.indid > 0 and i.indid < 255
and i.id = o.id and i.indid = 1
and o.name = lower(m.tablename)
And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.
View 6 Replies
View Related
Oct 12, 2006
I need to import csv data into a SQL Server 2005 database using SQL Server Management Studio Express. I can't find any menu options for accomplishing this. Is this a limitation of the Express edition, or am I missing something when I try to find this feature?Thanks for any help provided.
View 7 Replies
View Related
Nov 27, 2007
HiI've got an Ingres database of some 200 tables which I need to importevery night into SQL Server 2005 for use by Reporting Services. Mostof the tables will come across unchanged (a few need massaging tohandle time intervals correctly), but the Import Wizard only seems towant to import one table (or more accurately query) at a time. I seemto remember the old 2000 Import Wizard handled multiple tables - isthere any way of processing multiple tables in 2005, or must I resignmyself to writing 200 import packages in SSIS.Chloe CrowderThe British Library
View 4 Replies
View Related
Feb 27, 2008
I hope I am giving the right information here to get help with this.
I am trying to get information from two tables that are in an access database over to sql 2005. I did this via dts and saved the import options as a file, but when I run this as a job I get the following error: Date 2/27/2008 11:14:08 AM
Log Job History (CombinationTable1)
Step ID 0
Server D86J0PD1
Job Name CombinationTable1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User BIZCHAIRDarleneMurphy. The last step to run was step 1 (CombinationTable1).
I have also tried to run this in the following way:
USE Combination_Table
CREATE TABLE GG Products
AS
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'W:MASTER DOCUMENTSDatabasesChannel_Advisor_Feed.mdb';'admin';'', GG Products)
go
Here I get errors regarding nulls and if I pointed to the correct database, which I have.
Any help with this problem and I hope I have given good information in order to get help.
Thank you
Dee
View 6 Replies
View Related
Dec 5, 2007
Does anyone know how I can do this?
I have 2 tables in an ODBC datasource (INV HEADER) and (INV DETAILS). The relationship on these 2 tables is (INVNUM).
I want to import these tables into SQL 2005 on a nightly basis by date. The problem is the date field is on the (INV HEADER) table and not the (INV DETAILS) table.
Basically I want to import all the (INV DETAILS) rows that have the same (INVNUM) as the (INVHEADER) but don't know how to do this.
I could use a join on the source tables but how would I direct specific columns to 2 different destinations?
Any help on this would be great and appreciated.
Thanks
View 3 Replies
View Related
Jun 11, 2007
Hi,
I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.
However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.
So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.
TIA,
Graham.
View 1 Replies
View Related
Sep 3, 2007
Hi all. I want to export data from excel 2003 to sql server 2005. I am using the following script:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Insert into Pamphlet
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:Pamphlet.xls;HDR=YES', 'SELECT * FROM [Sheet3$]')
But it is giving the following error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The above script is working fine with Excel 2002 but not with 2003. Can someone please provide me exact script of importing data from excel 2003 to sql server 2005. I have also tried the Linked server option but it is still giving the same error.
Please help me.
View 1 Replies
View Related
Nov 16, 2006
Hello everyone,
I have exported data from few tables from my old sql server 7.0 database. Now, I need to import those data into new database which is on SQL Server 2005 Express. How do I do that in 2005 express server? Any idea?
Or, is there any better way to import data for selected tables into new database?
Thanks,
Ujjaval
View 2 Replies
View Related
Jun 6, 2007
Just installed SQL Server 2005 Eval version and Management Studio does not display any Import/Export functions to load data into tables of an existing database. I thought that this feature was turned off only in Management Studio Express.
View 1 Replies
View Related
Apr 4, 2006
I have a simple Integration Services project and the problem is that decimal fields are importing as real (I'm loosing the digits behind the decimal point).
The project contains a data flow task importing a flat file (.csv) to an SQL Server destination. My .csv file has two decimal type fields. Example:
Field 1: 12345.67
Field 2: .123456
My database table that I'm importing to has two fields. The only way that I can get this data to import is to define the fields as "float" in both the text file and database table. I want the database table fields to be defined as decimal or numeric, not float:
Field 1: decimal(7,2)
Field 2: decimal(6,6)
When all fields are defined as decimal (in both the flat file and database file), I get the following results:
Field 1: 12345.00
Field 2: .000000
How does one import decimal data from a flat file (.csv)?
Thank you in advance!
View 1 Replies
View Related
Aug 4, 2004
Hi, I've just got my brand new shiny SQLServer and would like to set some new tables set up etc. But I'm really lazy and I was wondering if there is any way of importing table layouts, coulmns, whole structure type thing into SQLServer from Microsoft Access using DTS? I know it's a bit optimistic, but just wondered....
Any help gratefully accepted.
Thanks,
Paula.
View 1 Replies
View Related
Mar 10, 2008
I have designed an SSIS package and in a data flow task I've defined my source and destination components and mapped all the fields. The task works fine as long as I have the same number of fields in my CSV file as what I have defined in the task.
The issue is that if my CSV contains more fields than what are declared in the mapping, then the extra fields at the end of each line are concatenated into the last column defined in my map. For instance:
CSV SQL
Product -> Product
M0 -> M0
M1 -> M1
M2 -> M2
This works fine, but if my CSV file looks like this:
Product
M0
M1
M2
M3
M4
The values in fields M3 and M4 are concatenated with M2 and all 3 are imported into field M2 in my sql table. Any ideas?
View 5 Replies
View Related
Nov 27, 2006
I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.
I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.
Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.
Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.
Thanks for any help,
Jon
View 4 Replies
View Related
Jan 11, 2007
Hi all,
I ve few queries regarding SQLServer 2005, kindly help me to understand the things,
1. Datatypes like nvarchar, varchar, varbinary use 'max' to define their datalength,
a. is this 'max' always has the value of 2^31-1 ?
b. what other data types in sqlserver 2005 can use 'max' to define their datalength?
2. I defined a table (say 't') with a column 'name' of datatype nvarchar(max), now tried to execute the below query in the SQLServer client,
select column_name, data_type, character_maximum_length from information_schema.columns where table_name='t'
the result is,
+--------------+------------+--------------------------+
| column_name | data_type | character_maximum_length |
+--------------+------------+--------------------------+
| name | nvarchar | -1 |
+--------------+------------+--------------------------+
The question is why the datatype length is returned as -1, what happened to 'max'?
is there any other way to retrieve the actual data length (ie., 'max') from the table definition?
-Sn
View 1 Replies
View Related
Sep 1, 2004
chapter1
Delegates officially are to nominate Cheney as the GOP's vice presidential candidate before he addresses the group Wednesday night.
chapter2
"I think that the vice president's speech tonight is going to be about big issues, the big issues of this campaign -- the war on global terror, the president's education policy, the fact that the economy is turning up again," she told CNN's "American Morning."
chapter3
She said she had known her husband since he was 14 and planned to share anecdotes that many people have not heard before in her introduction.
Chapter4
Maverick Democratic Sen. Zell Miller of Georgia is scheduled to deliver Wednesday's keynote address -- a role he also played at the 1992 Democratic National Convention, which nominated President Clinton.
Chapter5
In the earlier speech, Miller, then governor of Georgia, said that "for 12 dark years, the Republicans have dealt in cynicism and skepticism. They have mastered the art of division and diversion, and they have robbed us of our hope."
I wanted to import the above word doc into my sql server DB.
I have two columns in the table
1.Chaptereid
2.chapter_notes
chater1,chater2,chapte2,chapter3,chapter4 and chapter5 should go into chaptered column and the text followed by the chapter id should be imported into chapter_notes column.
So how can I import this document.
Thanks.
View 2 Replies
View Related
Oct 12, 2006
Does anyone have any great suggestions on how I can import an online XML file into an SQL 2005 table?
So far I tried to accomplish it with SSIS Data Flow Task where my source is XML Source (Data access mode: XML file location; XML location: URL, Use inline schema = True). This set up properly identified the columns to be imported.
I used Copy Column data flow transformation task to load data to OLE DB destination table that has same structure.
When I run the task it does execute with no errors, however the table remains empty. It looks like I am failing to read the actual data.
Do you have any suggestions? I am willing to go around this approach with stored procs/com/you name it €“ just make it work!
Thanks a lot for your help!
LoveDanger
View 6 Replies
View Related
