Importing table question

I am part of a group that is developing a SQL database. When I import the ne
w tables from another server, They primary keys for the tables do not import
with the table. Does any one know why this would be happening?Do you mean the primary key column or the primary key definition
(constraint)? Also, what tool (exactly) are you using for the import?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jstokes" <anonymous@.discussions.microsoft.com> wrote in message
news:09B8020D-AF6F-4139-8365-B8AD3A965359@.microsoft.com...
> I am part of a group that is developing a SQL database. When I import the
new tables from another server, They primary keys for the tables do not
import with the table. Does any one know why this would be happening?

Importing Excel file into a MS-SQL 2005 table

I hope this is the right forum for my question.

I'm developing a website for a Prepaid Calling Cards distributor. Each of the cards they sale have a list of the countries the card is good for. I need to import this data into my countries_rates table. The file they are giving me is an excel file that contain 3 colums (fields)

1- Country-Name

2- Rate

3- Card_$_Price

these files contain aproximaly 400 rows so it will be a hasle to have to insert it manually every week.

In my web application I need to create a form where the user will select the card from a dropdownlist and then find the excel file to be imported for that card.

I would like to know how do I do that with Visual Studio 2005, SLQ 2005 and C#

please direct me to some links where I can learn how to do this or please send me some code snips I can see how is done.

Tia

Charles

In short - here's some old (.NET 1.1) code I used. Basic idea is to uploadfile and then open the excelfile by using a OleDbConnection.

Dim strConnAs String ="Provider=Microsoft.Jet.OleDb.4.0;" _ &"data source=" & sFileName &";" _ &"Extended Properties=Excel 8.0"Dim objConnAs New System.Data.OleDb.OleDbConnection(strConn)Dim oAdapterAs New System.Data.OleDb.OleDbDataAdapter("SELECT * from " &"[Sheet1$]", objConn)

Dim ExcelDataAs DataSet =New DataSetDim oCollAs New CollectionDim nErrAs IntegernErr = 0Dim sErrAs StringTry objConn.Open() oAdapter.Fill(ExcelData)

Now you have the data from Excel in the dataset ExcelData and can loop through it and

insert it/update your SQL db

|||

Thanks for the code snip. But I don't use VB I'm using C# do you have the C# version. Sorry to be such a pain

Tia

Charles

Importing Data from Access 2002 in SQL Express

Hi All,

At the moment i have a piece of software that uses Access to store the data into a number of tables.

I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script.

Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb

The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process.

Any ideas?

Thanks in advance

Martin

Hi,

here is a good article to refer http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx you may also use SSMA to migrate data from Access to SQL Express.

Hemantgiri S. Goswami

Importing Data from access 2000 in SQL

Hi All,

At the moment i have a piece of software that uses Access to store the data into a number of tables.

I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script.

Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb

The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process.

Any ideas?

Thanks in advance

Martin

I do not know if you can write it with the help of query. Yes, if you can import the Access data, that would be great.

Well, you might like to write a small application for your data migration. The application should be able to read data from your access database and will push the data in SQL.

|||Create a "linked server" to the access file, and just use TSQL queries to pull the data from the linked server.|||

Excuse my ignorance, but where can i find the linked server option ?(i am using SQL 2005 Managment Studio Express) and cant find the option to link servers (the version i am running also has no help!!)

Thanks Again

Martin

|||It is under: ServerName -> Server Objects -> Linked Servers. Right click and select "New Linked Server".

Give it a name, like "oldaccess". Then select the MS Jet 4.0 OLEDB driver. Put in the full path of the Access database in the "Data Source".

To insert into your SQL table use:

INSERT INTO tablea
SELECT * FROM oldaccess.tablea

See this tutorial: http://www.databasejournal.com/features/mssql/article.php/3103331

Importing binary files into sql server database

I work for a company that makes heat transfers for the imprinted apparel market. We're developing a database of merchandise images for all of our non-design inventory. Using Access we're going to be inserting thumbnails of psd (photoshop) files. We're wondering if there is any way to import multiple psd's into the sql server database into matching records like matching a column named "filename" and the actual filename of the file without having to upload each file individually. We want to be able to dump the files from the database of the matching records, also. This way, once our catalog designer has found which designs they need to put into the new catalog, it will dump the psd's for us. The same for our staffer who does color separations.

Any suggestions out there? If you need me to post further of what we're trying here, I will. This is for the bossman.store the location and file names and not the files themselves. you may be able to use text\image\blobs but I would reccomend against it. it will bloat your database, probably hamper performance and likely cause unneeded difficulty.|||store the location and file names and not the files themselves. you may be able to use text\image\blobs but I would reccomend against it. it will bloat your database, probably hamper performance and likely cause unneeded difficulty.

As mentioned here (and in other posts, do a search), the general consensus among the community is to store a pointer (link) in the database and store the binary (image file) on a web server or somewhere else.

However, I have run into situations where this was not permitted (courtesy of some rabid security folks) or impractical. I currently manage a pair of 200 GB image repositories and SQL does just fine storing the blobs. Do a google search for "pure asp upload" or similar variants and you will find some sample code for uploading binary streams to a database.

Regards,

hmscott|||at my last gig we measured our image data in the terabytes and there is no way I am bloating my database and my database server with that stuff and we got new stuff all of the time. One week managment came in with 2 terabytes from the Smithsonian. If I store that stuff in SQL Server not only would I have I needed a new machine, which I did, but I would have had to migrate all of my DBs onto a new machine as well.

Besides logisitics, technically blob data is just a pain to handle. I was going back over some Ken Henderson last night and as he points out if you store data in blobs, the data is not really in the row. What you get is a sixteen bit pointer to other data pages and if you do not insert the blob at record creation you do not get a pointer. So it is reccomended to insert a default value. My two cents regarding this is now you are wasting space and inserting junk data. Ken also points out that BLOB data creates a transaction log conundrum. Do you log it and bloat the log or not and compromise your db's integrity.|||Thanks for these suggestions. This is a pretty new trail for us to blaze. Glad to know there are forums like this where we can get questions answered.

Importing Access database into SQLExpress

I am trying to complete a lab in the Microsoft self-paced training kit, Developing Web Applications with Microsoft Visual C# .NET.

They have provided an Access database named Contacts.mdb and a batch file named InstContacts.bat that uses InstContacts.sql to import the data.

I have installed SQLExpress, but when I run the batch file I get the following:

C:\Microsoft Press\...\data>rem The following command line installs the Contact SQL database

C:\Microsoft Press\...\data>osql -i InstContacts.Sql -E
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Nevermind, I found the answer here.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421593&SiteID=1