IMporting sql2000 backup database ?

Dear all,
I have an database backup from SQL server2000.
How can I import it inside SQL server 2005 ?
regards
serge
serge calderara wrote:
> Dear all,
> I have an database backup from SQL server2000.
> How can I import it inside SQL server 2005 ?
> regards
> serge
Use the RESTORE DATABASE command...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||here is how I am proceedig.
First I have creat a new database named NOMOS, same as my old backup. Only
system tables are created here as I want to restore the whol configuration
from my backup.
Then I select the Restore backup from management studio and select my new
created database NOMOS as the destination and my backup file as a source
Then I get error message :
"The backup sets hold a backup of a database other than the existing 'NOMOS'
database"
Which is not treu casue I just made the backup of that databased before
installing 2005 ?
thnaks for comments
regards
serge
"Tracy McKibben" wrote:

> serge calderara wrote:
> Use the RESTORE DATABASE command...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||Sorry I find it out...
I forgot to set the overwrite option
:-)
"Tracy McKibben" wrote:

> serge calderara wrote:
> Use the RESTORE DATABASE command...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

IMporting sql2000 backup database ?

Dear all,
I have an database backup from SQL server2000.
How can I import it inside SQL server 2005 ?
regards
sergeserge calderara wrote:
> Dear all,
> I have an database backup from SQL server2000.
> How can I import it inside SQL server 2005 ?
> regards
> serge
Use the RESTORE DATABASE command...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||here is how I am proceedig.
First I have creat a new database named NOMOS, same as my old backup. Only
system tables are created here as I want to restore the whol configuration
from my backup.
Then I select the Restore backup from management studio and select my new
created database NOMOS as the destination and my backup file as a source
Then I get error message :
"The backup sets hold a backup of a database other than the existing 'NOMOS'
database"
Which is not treu casue I just made the backup of that databased before
installing 2005 '
thnaks for comments
regards
serge
"Tracy McKibben" wrote:

> serge calderara wrote:
> Use the RESTORE DATABASE command...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Sorry I find it out...
I forgot to set the overwrite option
:-)
"Tracy McKibben" wrote:

> serge calderara wrote:
> Use the RESTORE DATABASE command...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

IMporting sql2000 backup database ?

Dear all,
I have an database backup from SQL server2000.
How can I import it inside SQL server 2005 ?
regards
sergeserge calderara wrote:
> Dear all,
> I have an database backup from SQL server2000.
> How can I import it inside SQL server 2005 ?
> regards
> serge
Use the RESTORE DATABASE command...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||here is how I am proceedig.
First I have creat a new database named NOMOS, same as my old backup. Only
system tables are created here as I want to restore the whol configuration
from my backup.
Then I select the Restore backup from management studio and select my new
created database NOMOS as the destination and my backup file as a source
Then I get error message :
"The backup sets hold a backup of a database other than the existing 'NOMOS'
database"
Which is not treu casue I just made the backup of that databased before
installing 2005 '
thnaks for comments
regards
serge
"Tracy McKibben" wrote:
> serge calderara wrote:
> > Dear all,
> >
> > I have an database backup from SQL server2000.
> > How can I import it inside SQL server 2005 ?
> >
> > regards
> > serge
> Use the RESTORE DATABASE command...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Sorry I find it out...
I forgot to set the overwrite option
:-)
"Tracy McKibben" wrote:
> serge calderara wrote:
> > Dear all,
> >
> > I have an database backup from SQL server2000.
> > How can I import it inside SQL server 2005 ?
> >
> > regards
> > serge
> Use the RESTORE DATABASE command...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Importing MS Access queries and relationships to MS SQL Server

Dear fellow programmers,
I am having a big problem on how to import queries and relationships
from MS Access to SQL Server. Any suggestions will be a great help & will be
appreciated.
Thanks in advance,
Jeri
What is the specific problem? More generally, understand that there are
differences between MS Access and SQL Server.
"Jir" <Jir@.discussions.microsoft.com> wrote in message
news:CE9B458D-6EAA-4678-87E5-08CC7D5D8739@.microsoft.com...
> Dear fellow programmers,
> I am having a big problem on how to import queries and
relationships
> from MS Access to SQL Server. Any suggestions will be a great help & will
be
> appreciated.
> Thanks in advance,
> Jeri
sql

Importing long XML documents.

Dear All,
I am using
sp_xml_preparedocument hdoc OUTPUT
[, xmltext]
[, xpath_namespaces]
How do I pas a file or a long string into xmltext ?
(The file is more than about 300 Kbytes in size).
Using the a quoted text of the file at xmltext works.
So how do I get the file there, or a database field there ?
Ben Brugman"ben brugman" wrote:
> Dear All,
> I am using
> sp_xml_preparedocument hdoc OUTPUT
> [, xmltext]
> [, xpath_namespaces]
> How do I pas a file or a long string into xmltext ?
> (The file is more than about 300 Kbytes in size).
> Using the a quoted text of the file at xmltext works.
> So how do I get the file there, or a database field there ?
> Ben Brugman
>
Hi Ben
You don't say which version of SQL Server this is!
http://sqlxml.org/faqs.aspx?faq=61 suggests declaring a parameter and using
it as a variable. You can (possiblty) use BCP/BULK INSERT or OPENXML in
SQL2000 to load the data in. In SQL 2005 you can also use OPENROWSET to load
in the file. Examples are in Books Online.
John|||"John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
news:78EC2FA5-DE22-4DBF-A762-0B40B15E14EA@.microsoft.com...
> "ben brugman" wrote:
>> Dear All,
>> I am using
>> sp_xml_preparedocument hdoc OUTPUT
>> [, xmltext]
>> [, xpath_namespaces]
>> How do I pas a file or a long string into xmltext ?
>> (The file is more than about 300 Kbytes in size).
>> Using the a quoted text of the file at xmltext works.
>> So how do I get the file there, or a database field there ?
>> Ben Brugman
>>
> Hi Ben
> You don't say which version of SQL Server this is!
> http://sqlxml.org/faqs.aspx?faq=61 suggests declaring a parameter and
> using
> it as a variable. You can (possiblty) use BCP/BULK INSERT or OPENXML in
> SQL2000 to load the data in. In SQL 2005 you can also use OPENROWSET to
> load
> in the file. Examples are in Books Online.
I am using SQLServer 2000. The problem at the moment is that the file has
300Kbytes,
and a variable can only hold 8000, so this won't fit. And although
sp_xml_preparedocument can handle the text type, there is no variabele for
this. Using a stored procedure with a text parameter there is no way to fill
the parameter with the 300K bytes text, from a file or a table field.
That is if I get the file in a text field in the first place, haven't even
thought about that problem.
I followed your link to faq=61, (also
http://www.sqlxml.org/faqs.aspx?faq=42)and read some other questions as
well, and allthough I have seen the 'correct' questions, most of the
questions do not even get answered (or I am missing something). Even an
anwser that say it's not possible what I want is ok, but at the moment I am
thinking the only soluttion is complex very unelegant and risky and involves
a lot of coding.
I am supprised that MicroSoft supplies this functionality but then cripples
it to XML length's of only 8000 bytes. (Look like crippleware, or a demo
version, were you can taste the product, but have to buy it to enjoy the
product, except in this case it's the complete product).
Any suggestions are welcome.
Thanks for your time and attention,
Ben Brugman

Importing from Crystal Report 7 to Reporting Services

Dear all,

I was wondering if I can import reports made by crystal report 7 to Reporting Services 2000.

Thank you.This one isn't free, but may be worth a try for $5-$15.
Another consulting company, Hitachi, provides services to do this. See Post.
There is no built-in way to convert reports.

Importing excel to sql server....

Dear All
I am trying to import data from excel sheet to sql server database, by using method 2, it creates a table on fly but it never shows any table in database tables' list but when i execute the code again, system throws an exception that table already exists.
On the other hand method two assumes that there is an existing table in db, but after execution, it never shows data, table remains empty. Here is code, please tell me whats wrong with this code.
Regards
<code>
Dim ExcelConnectionAsNew System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\annie\anna.xls;Extended Properties=Excel 8.0;")

ExcelConnection.Open()
'For existing Table.............METHOD 1
Dim ExcelCommandAsNew System.Data.OleDb.OleDbCommand("INSERT INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] SELECT * FROM [Sheet1$];", ExcelConnection)

'For new Table.................METHOD 2
Dim ExcelCommandAsNew System.Data.OleDb.OleDbCommand("SELECT * INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] FROM [Sheet1$];", ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
</code>

You are missing linked server because Excel only knows Access SQL not T-SQL. Try the code below. Hope this helps.

/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

|||Hi
Thanx for reply but i dont want to create tables own my own because their are more than five hundred excel sheets to import. I need fastest mechanism to imort excel data to sql server!!!
And second problem is that when i export data from sql server to excel then excel is deformating the data e.g. i have a string "000001" in sql server table but excel sheet used to make it 1 (numeric). I am using clipboard to copy data from sql server to excel sheet (It is the only fastest way to export data from sql server to excel).
Please help.|||Execl is not a RDBMS it is a flat file application to do what you want you have two choices DTS on DTSRUN exe or DTS on xp_cmdshell and Sharepoint Portal. Hope this helps.

Importing Excel file using DTS use of VB package

Dear All,
I had created a Package for importing a excel file.
while i created the package the data was ported.
and i had saved the package.
now when i try to port the data by executing the package.
the file is not ported.
what could be the problem.
help me......
Regards _PremAny errors?
Is the file empty, are you trying to import the same file?
Instead of DTS you can also use OPENROWSET or OPENDATASOURCE
Here is an OPENROWSET example
INSERT INTO YourTable
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Madhivanan

Importing Excel file to SQL Server (Opinions please)

Dear All,

I am writing a procedure to importdaily the customer excel file to SQL server 2000, I managed to do that where the excel file will be imported directly to the SQL server after creating the new data table, & then I need to read the created table & import it row by row to my original data table.
The problem:

I. The original excel file has the following:
a. a protection password
b. The contents has two merged headers (which effecting the import procedure)
c. And last line is a totals line

Before importing the file I have manually to remove (a – b & c)!!

The Solution:

II. I am trying to find a way to do the above points automatically inside the project.

III. Also I thought of importing the excel file to a DataGrid first then:
a. Let the user approve the file contents &
b. Remove manually point (I.b.) above (I don't now how yet, need to try it).
c. Then import the DataGrid the the SQL server.

I think I prefer solution (III), any suggestions are highly appreciated

BR

Try this thread and read my post there is code and a link to all you will need including free Video tutorials from Microsoft. Hope this helps

http://forums.asp.net/928520/ShowPost.aspx

|||

Dear Caddre,

Thank you for your reply,
I have checked the posted links. they are talking about exporting to excel from sql server/browser.
this not what I want.

Anyhow thanks again.

|||

Hi again,
I desided to preview the excel sheet in a datagrid when the user selects the file, worked fine.
but still I am stuck if the excel file has a password, I tried to do the following :

oOLEDBConn =New OleDb.OleDbConnection
oOLEDBDA = New OleDbDataAdapter("SELECT * FROM " & sDataSheet & " ", oOLEDBConn)
oDS =New DataSet
oOLEDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & sFileName & ";Extended Properties=Excel 8.0;Password='123';"
oOLEDBConn.Open()
oOLEDBDA.Fill(oDS, "Sheet")

In this case i am getting the following error whether the file has a password or not:
(Cannot start your application. the workgroup information file is missing or opened exclusively by another user)

Any suggestions please.

importing excel file into sql server 2005 in asp .net using c#

Dear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me?

rgds,
RK

try the link below [SqlBulkCopy]

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

 
// Connection String to Excel Workbookstring excelConnectionString = @."Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";// Create Connection to Excel Workbookusing (OleDbConnection connection =new OleDbConnection(excelConnectionString)){ OleDbCommand command =new OleDbCommand ("Select ID,Data FROM [Data$]", connection); connection.Open();// Create DbDataReader to Data Worksheetusing (DbDataReader dr = command.ExecuteReader()) {// SQL Server Connection Stringstring sqlConnectionString ="Data Source=.; Initial Catalog=Test;Integrated Security=True";// Bulk Copy to SQL Serverusing (SqlBulkCopy bulkCopy =new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName ="ExcelData"; bulkCopy.WriteToServer(dr); } }}
 

Good Luck./.

|||

Hi

you have to first read the XL content then you have to insert into Sql server.

you can transfer your XL to dataset by Oledb provider by this code

string conection_string ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=YOUR XL PATH; Extended Properties='Excel 8.0;IMEX=1;HDR=YES;'";

OleDbDataAdapter adp =newOleDbDataAdapter("SELECT * FROM [Sheet1$] ", conection_string);

DataSet ds =newDataSet();

adp.Fill(ds);

now your xl content would be in dataset ds , then you can access this dataset as well and read dataset line by line and insert into Sql

add namespace :using System.Data.OleDb;

|||

Dear what is Sheet1$ can u tell me detail?

|||

ramu778:

Dear what is Sheet1$ can u tell me detail?

sorry to interrupt

in ConnectionString; for DataSource we give the XLS file path...

and in below code line... we have to give the Sheet name in the Select Query from which we have to read the date from, as there would be more than one Sheet in an excel file...

OleDbDataAdapter adp =newOleDbDataAdapter("SELECT * FROM [Sheet1$] ", conection_string);

hope it clears./.

|||

Hi

sheet1 is nothing but the name of the XL sheet , there are some name at bottom of XL like sheet1,sheet2 and so, we have to give that name

Importing data from a text file with quotes

Dear all,

I trying to import a set of datas from a text file to a table call Movement.

Table structure for Movement is as following:

1, JobNo, Character (10)
2, Date, smalldatetime
3, ItemDesc, Character (100)
4, StaffID, Character (5)
5, Quantity, Decimal(10,4)
6, Completed,Bit

text file sample contains:

"T200601100";1/10/2006;"A3 papers, Plastic covers","T1001",500,1
"T200601101";1/11/2006;"Ink Refiller for MF0012/3","T1001",5.5,1
"T200601102";1/12/2006;"1' roller","T1012",50,1
"T200601103";1/13/2006;"A1 Papers White, A3 Paper Black","T1022",500,1
"T200601104";1/13/2006;"Folders","T1022",10,1

Now the problem is when i use bulk insert statement i put in the double quote (""") into the fields too. How can i solve this problem out?

Thanks in advance.You can use a format file with bcp/bulk insert to import data that contains field delimiters. You have to basically create dummy columns in the format file for the delimiter which you will ignore. See books online for more details on how to use format file with more columns than that of the table.