How to schedule an SSIS package that invokes a web service

Hey,

I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.

Any help is greatly appreciated!!

Thanks!

You should use a configuration (right-click in the package and choose configurations) to set the ConnectionString property of the connection managers for the files. Or you could use expressions to set the connection strings (paths and filenames) based on variables. The variables can be set at runtime using the /SET option of DTEXEC.|||Also make sure you've configured SSIS logging, so you can find out why the package fails now or (once you fix the problem and go to production) if something goes wrong with scheduled package in production.|||That depends on where you want to keep them. I prefer to keep them in files on the disk. If your package is in SQL and you prefer to avoid the disk entirely, you can keep them in the database and just load them into variables via the Execute SQL task. The XML task and the XML Source component support receiving the XSD/XSLT from variables.
|||Thanks a lot for the suggestions. I will try them out and see how it works.|||

Hey,

Sorry for this delayed reply. Since I posted this question a lot of issues cropped up with my SQL server which eventually led to a total reinstallation of all apps on my pc. Anyway, I discovered that the problem I have been having was because of permission issues. I was able to fix that problem and just when I thought that I had everything going, I came across a new problem. After I save the SSIS package in sql server and create a job, the job starts failing. This is the error that I am getting:

-1073548540,0x,An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: Unable to connect to the remote server.

Any suggestions would be very helpful.

Thanks!

|||Could it be a authentication issue? Is there any security on the web service?|||

Guys!!Thanks a lot for all the suggestions. Really appreciate your help. Problem was a combination of many issues. One was related to 32bit/64bit differences, the other was authentication, and finally some syntax problems when invoking the web service. Seems it is working really well now.

Thanks again!

How to save PDF files to SQL Server?

Hellow, everyone:

I have PDF files on local hard drive, and want to save them to the table in SQL Server. Can I execute by SQL Server? If yes, how to do that.

Someone said to create a table with IMAGE column, and write PDF files to this table. How about details for this way?

Thanks a lot.

ZYTWhile you can store files inside of SQL Server, I recommend against it. Files are a "complex data type", so you can't sort, index, compare, or do any other practical operations with them. You can however store a UNC name for a file in a database, and there are many parctical things you can do with that.

There are some reasons for considering storing files inside of SQL Server, but they are few and far between and all of the reasons that I know about are kludges to address application design flaws. If you can avoid the headaches associated with storing files inside your database, I'd recommend avoiding it!

-PatP|||Hi, Pat:

Thanks for reply.

I have to save PDF files to SQL Server, and then retreive/dispaly them. Because security issue, the PDF files are not allowed put in web server, so frontend code cannot read them. PDF files are in database server.

Can you offer codes that can write/read PDF to/from SQL Server? Thanks a lot.

ZYT

How to run MDX query as scheduled job?

Hi,

Is there any way to run MDX

query as scheduled job on SQL Server 2005?

We need create local cubes by

weekly or monthly. How can we put “Create global cube” MDX query into scheduled

job?

Any info would be greatly

appreciated.

Thanks.

Yes. Create a new SQL Server Agent job and add a step with the step type of SQL Server Analysis Services Query. For this type of step, you need to specify the SSAS server to connect to, the database to run the query within, and the text for the query itself.

Should do exactly what you want...

HTH,

Dave Fackler

|||

Hi Dave,

Thanks for the quick replay.

Could you recommend any article or books that I can take look?

Thanks.

|||

I'm assuming you are asking about references for SSIS? If that is the case, check out Microsoft SQL Server 2005 Integration Services (SQL Server Series) by Kirk Haselden, Professional SQL Server 2005 Integration Services (Programmer to Programmer) by Brian Knight et al, and The Rational Guide to Extending SSIS 2005 with Script (Rational Guides) (Rational Guides) by Donald Farmer. All great books related to SSIS that should get you started...

HTH,

Dave Fackler

|||Thank you very much Dave for answered my question. It's helpful.

How to retrieve most recent file from local directory?

Hi,

Here's my situation. Every day I will be downloading extracts to a folder. The extracts are named:

20070529.Extract1.csv

20070528.Extract1.csv

20070527.Extract1.csv

20070529.Extract2.csv

20070528.Extract2.csv

20070527.Extract2.csv

So, on any given day, I will want to find the most recent versions of Extract1 and Extract2, for example:

20070529.Extract1.csv & 20070529.Extract2.csv

How would I go about doing this?

Thanks much

How about a ForEach loop on the files in the directory, with a script task inside to compare each filename to a variable. If the filename is "greater", store it in the variable, otherwise go to the next filename. You'd need two variables, one for Extract1 and one for Extract2.

After the ForEach, your variables should hold the correct filenames.

|||

I have an example that you may find helpful:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

|||

Hi Rafael,

I looked at your example. I'm afraid I don't understand how you set the EndDate and StartDate variables. I see that there is hardcoded value in the variables, but what if I don't want to do this?

In my case, on any given day the file will have the previous day's timestamp.

So if today is May 31, the file will have the name:

20070530Extract1.csv

So what I want to do is just get the MOST RECENT file that is less than today's date (since on Monday the time lag will be longer than just one day, due to no files being generated on weekends).

So I don't really need EndDate, just StartDate, and in my case, StartDate must be dynamic, ie today's date.

How do I do this?

Thanks

|||

Actually, I probably do need Start and End Dates. But this becomes complicated due to weekends. That is, on Monday, the file I want is from the previous Friday.

If there's an easy way to solve this problem, I'd love to find out what it is.

Meanwhile, I am wondering if I can leverage SQL Server to help me find what I need. That is, run an EXEC SQL Task, and store the result of my query into a variable. That would solve my problem very nicely. Then it doesn't matter what day of the week I'm running this, nor would I have to concern myself will setting start dates and end dates for comparison.

Here is part of an old backup script I used to use at another job:

CREATE TABLE #FileList (FileName SYSNAME NULL)
/* populate temp table with names of backup files from local backup directory */
INSERT INTO #FileList EXEC master..xp_cmdshell 'dir /B D:\Data\MSSQL\DB_BACKUP\DBServer\*.BAK'

/* select most recent backup file from temp table for recovery */
SET @.NewestFile = (SELECT TOP 1 FileName FROM #FileList WHERE FileName IS NOT NULL AND FileName LIKE 'myFile[_]%' ORDER BY FileName DESC)

So then my question would be, how do I save the "FileName" from the above query into a variable?

Thanks|||

Well, another thought. I don't want to have to use SQL Server to solve this problem.

I just need a script that will find the most recent file from a directory. I'm not even sure I want (or need to) to bother with end dates and start dates at all. There's gotta be a very simple script that can do this?

|||

sadie,

There is more than one way to get the latest file in the directory. Perhaps you can use the first part of my blog post to loop over the files in the folder capture the date part out of the file name. Then follow Jwelch suggestion, use a script task to compare the date of the current iteration with the previous one until you get the latest one.

A simpler approach may be to implement a table where you keep the latest file date you has processed, then have the package to retrieve that value to a variable and with a For Each loop, loop through all files in the folder. Depending in your requirements you write the rest of the logic by comparing the date of the current iteration against the date in the table. At the end of the process the table should be updated with the new latest date.

|||

You may find this thread helpful:

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

|||

Hi,

Well, I can't believe it but I figured it out myself, but thanks to Francesco Balena's "Programming Visual Basic...", I found a code snippet that worked for me.

Here is the solution to my problem. A very simple script, and I just need to output the file name to a variable:

Public Sub Main()

Dim rootDi As New DirectoryInfo("d:\myDir")

Dim newestFile As String

Dim fileDate As Integer

Dim fileDateSaved As Integer = 0 'initialize variable

For Each fi As FileInfo In rootDi.GetFiles("*.csv")

fileDate = CInt(Left(fi.Name, 8)) 'set to current file date in loop

If fileDate > fileDateSaved Then

fileDateSaved = fileDate

newestFile = fi.Name 'set variable to fName loop variable

End If

Next

MsgBox(newestFile)

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Congrats! Best way to learn, as Phil says.

Aren't you missing handling for two different files? Extract1 and Extract2? The code above will only give you one filename.

Simple fix:

Repeat your For Each loop, but use a different filter for each of them:

For Each fi As FileInfo In rootDi.GetFiles("*Extract1.csv")

For Each fi As FileInfo In rootDi.GetFiles("*Extract2.csv")

You'd also need 2 variables, one per filename to store.

|||

I should probably mention, this only works because I created a separate directory for each type of file I'm receiving.

That is, Extract1's goes in their own folder, Extract2's go in another folder.

|||That does make it a bit easier |||I'm trying to use this code but when i go to type dim rootdi as new directoryinfo("c:\mydir")

it does have directoryinfo available as a command i'm using vb6|||sorry it does not have directoryinfo as a command option

How to restore SQL 2005 DB to SQL 2000

Hello,

I'm tring to restor a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0818&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

BUTTONS:

OK

Why? and how to restore SQL 2005 DB to SQL 2000?

Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server. For more information about restoring databases, see Books Online topic RESTORE (Transact-SQL).

|||

Hi,

If its any clue or consolation, I get an error 3205 in the case where I backup {example} db from SQL 2000 SP3,

1. restore example db to same SQL version on another computer.

2. restore example db to SQL 2000 upgraded to SQL express 2005

3. backup from SQL express 2005, restore to same version on another computer.

"Too many backup devices specified for backup or restore; only 64 are allowed.
(Microsoft SQL Server, Error: 3205)"

Also, when alternatatively trying to attach the .mdf and .ldf files for this SQL 2000 database to a SQL express 2005 version, I get a

"Error 602 Severity Level 21 Message Text

Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes."

Although when I ran DBCC CHECK, it returned 0 errors.

ANY RECOMMENDATIONS PLEASE !

-

PS. with respect to your problem: Did you try uninstalling SQL express and then backup from SQL 2000 and restoring from that .BAK (then re-install SQL express)?

|||

The only way to get data from sql2005 to sql2000 is to export/import (SSIS packages can be setup to do this).

As for the 3205 problem, there was a problem with one of the SQL2000.SP3 QFE's where the backup header was written in a way that earlier versions of the server could not read. That was fixed in later versions. We recommend that you move to SQL2000.SP4.

When attempting to restore a sql2005-formatted backup, versions of SQL2000 earlier than SP4 may receive the 3205 due to changes in the format of the backup header.

As for the 602 during upgrade, that's new to me. You might want to contact our product support:

http://www.microsoft.com/services/microsoftservices/srv_support.mspx

Hope that helps.

|||

Dear ,

I have created a SQL DB 2005 ,and now my laptop have Win XP OS,that's why i can't install SQL 2005,and i want to restore the DB atleast to SQL Express installed with VS 2005..can i do that?

if you have any other suggestion please Advice...

i get excited after i knew that any SQL 2005 can't be restored to any earlier DB....what's my solution on my current state.

|||

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||Moustafa, any backup created on SQL 2005 can be restored to any other edition of SQL 2005.|||

i find this amazing that this is how MS decides to force an upgrade. I want to goto sql 2005 but I need to feel comfortable and if they don't allow a simple way to transform headers for backward compatibility I am seriously disappointed.

Who develops like this.......

|||

Sorry to disagree PailnessProd, but on DBServers there's no safe backwards compatibility, never, as when a DB server goes to a next version, it implies structural changes, thus, making backwards compatibility pretty hard, as older versions DON'T have the structural changes. If they had, they would be the latest version.

so it's the correct way to go for Microsoft. :)

|||

Steve,

Is it possible to "export all the tables from sql2005" if I'm using SQL Server 2005 Express?

Thanks,
Jason

Steve Schmidt wrote:

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||

You can use the bcp utility to copy table data between any version of sql server.

I suggest you might start a new thread with a more specific question with respect to export/importing data, if that is your goal. You might get a wider audience of potential responders.

To reiterate the answer to the question "how to restore sql2005 to sql2000": it is simply not possible.

|||

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn’t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!

At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.

Before beginning I must assume, firstly that the user, who will attempt such thing, hasa basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.

Step 1 Generating Scripts for the Database Elements and Structures

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3) Set the following Elements to the following Values

a. Script Collation , set to TRUE

b. Script Database Create, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to FALSE

e. Script Triggers, set to FALSE

Then Hit the Next button

4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5) Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3) Set all the Elements on the List to a False Value except the ones that follow:

a. Include IF NOT EXISTS , set to TRUE

b. Script Owner, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to TRUE

e. Script Triggers, set to TRUE

Then Hit the Next button

4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5) At the screen that follows hit the Select All button and the Next.

6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7) Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

www.hellasinternet.com

Panos Tzirakis & George Dounavis

|||

I may have to differ with you on that point. I am trying to do just that with a small database created on my laptop running SQL Server 2005. I have created a backup and tried to restore it on the class server also running SQL Server 2005. The following is the error message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

The above link is of no value as the 2005 to 2005 error occurance seems to be very rare and I have as yet found no one with a solution. I just spent an hour with the server admin for the class, and I seem to be the only one with this issue. Until a solution is found I am forced to create my assignments and work from the class server.

Since then, I have created a couple of databases, one empty, the other populated, with the exact same results. Do you have a solution for this particular error message?

|||

Hello,

PanosTZ - thanks for your detailed instructions.

I've attempted to follow your instructions (panosTz). It's worked successfully for one of my databases but I'm getting the following error for the other.

"Generate Script Progress

- Determining objects in database 'RetailStore' that will be scripted. (Error)

Messages

Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Smo)"

I can't get any information about the error received on any other forums or on the Microsoft KB.

Anyone who can explain what this means of point out area of fault would be much appreciated.

|||

Well, SQL Server 2005 Developer Edition is supported in Windows XP.

How to restore SQL 2005 DB to SQL 2000

Hello,

I'm tring to restor a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0818&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

BUTTONS:

OK

Why? and how to restore SQL 2005 DB to SQL 2000?

Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server. For more information about restoring databases, see Books Online topic RESTORE (Transact-SQL).

|||

Hi,

If its any clue or consolation, I get an error 3205 in the case where I backup {example} db from SQL 2000 SP3,

1. restore example db to same SQL version on another computer.

2. restore example db to SQL 2000 upgraded to SQL express 2005

3. backup from SQL express 2005, restore to same version on another computer.

"Too many backup devices specified for backup or restore; only 64 are allowed.
(Microsoft SQL Server, Error: 3205)"

Also, when alternatatively trying to attach the .mdf and .ldf files for this SQL 2000 database to a SQL express 2005 version, I get a

"Error 602 Severity Level 21 Message Text

Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes."

Although when I ran DBCC CHECK, it returned 0 errors.

ANY RECOMMENDATIONS PLEASE !

-

PS. with respect to your problem: Did you try uninstalling SQL express and then backup from SQL 2000 and restoring from that .BAK (then re-install SQL express)?

|||

The only way to get data from sql2005 to sql2000 is to export/import (SSIS packages can be setup to do this).

As for the 3205 problem, there was a problem with one of the SQL2000.SP3 QFE's where the backup header was written in a way that earlier versions of the server could not read. That was fixed in later versions. We recommend that you move to SQL2000.SP4.

When attempting to restore a sql2005-formatted backup, versions of SQL2000 earlier than SP4 may receive the 3205 due to changes in the format of the backup header.

As for the 602 during upgrade, that's new to me. You might want to contact our product support:

http://www.microsoft.com/services/microsoftservices/srv_support.mspx

Hope that helps.

|||

Dear ,

I have created a SQL DB 2005 ,and now my laptop have Win XP OS,that's why i can't install SQL 2005,and i want to restore the DB atleast to SQL Express installed with VS 2005..can i do that?

if you have any other suggestion please Advice...

i get excited after i knew that any SQL 2005 can't be restored to any earlier DB....what's my solution on my current state.

|||

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||Moustafa, any backup created on SQL 2005 can be restored to any other edition of SQL 2005.|||

i find this amazing that this is how MS decides to force an upgrade. I want to goto sql 2005 but I need to feel comfortable and if they don't allow a simple way to transform headers for backward compatibility I am seriously disappointed.

Who develops like this.......

|||

Sorry to disagree PailnessProd, but on DBServers there's no safe backwards compatibility, never, as when a DB server goes to a next version, it implies structural changes, thus, making backwards compatibility pretty hard, as older versions DON'T have the structural changes. If they had, they would be the latest version.

so it's the correct way to go for Microsoft. :)

|||

Steve,

Is it possible to "export all the tables from sql2005" if I'm using SQL Server 2005 Express?

Thanks,
Jason

Steve Schmidt wrote:

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||

You can use the bcp utility to copy table data between any version of sql server.

I suggest you might start a new thread with a more specific question with respect to export/importing data, if that is your goal. You might get a wider audience of potential responders.

To reiterate the answer to the question "how to restore sql2005 to sql2000": it is simply not possible.

|||

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn’t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!

At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.

Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.

Step 1 Generating Scripts for the Database Elements and Structures

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3) Set the following Elements to the following Values

a. Script Collation , set to TRUE

b. Script Database Create, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to FALSE

e. Script Triggers, set to FALSE

Then Hit the Next button

4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5) Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3) Set all the Elements on the List to a False Value except the ones that follow:

a. Include IF NOT EXISTS , set to TRUE

b. Script Owner, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to TRUE

e. Script Triggers, set to TRUE

Then Hit the Next button

4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5) At the screen that follows hit the Select All button and the Next.

6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7) Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

www.hellasinternet.com

Panos Tzirakis & George Dounavis

|||

I may have to differ with you on that point. I am trying to do just that with a small database created on my laptop running SQL Server 2005. I have created a backup and tried to restore it on the class server also running SQL Server 2005. The following is the error message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

The above link is of no value as the 2005 to 2005 error occurance seems to be very rare and I have as yet found no one with a solution. I just spent an hour with the server admin for the class, and I seem to be the only one with this issue. Until a solution is found I am forced to create my assignments and work from the class server.

Since then, I have created a couple of databases, one empty, the other populated, with the exact same results. Do you have a solution for this particular error message?

|||

Hello,

PanosTZ - thanks for your detailed instructions.

I've attempted to follow your instructions (panosTz). It's worked successfully for one of my databases but I'm getting the following error for the other.

"Generate Script Progress

- Determining objects in database 'RetailStore' that will be scripted. (Error)

Messages

Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Smo)"

I can't get any information about the error received on any other forums or on the Microsoft KB.

Anyone who can explain what this means of point out area of fault would be much appreciated.

|||

Well, SQL Server 2005 Developer Edition is supported in Windows XP.

How to restore SQL 2005 DB to SQL 2000

Hello,

I'm tring to restor a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0818&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

BUTTONS:

OK

Why? and how to restore SQL 2005 DB to SQL 2000?

Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server. For more information about restoring databases, see Books Online topic RESTORE (Transact-SQL).

|||

Hi,

If its any clue or consolation, I get an error 3205 in the case where I backup {example} db from SQL 2000 SP3,

1. restore example db to same SQL version on another computer.

2. restore example db to SQL 2000 upgraded to SQL express 2005

3. backup from SQL express 2005, restore to same version on another computer.

"Too many backup devices specified for backup or restore; only 64 are allowed.
(Microsoft SQL Server, Error: 3205)"

Also, when alternatatively trying to attach the .mdf and .ldf files for this SQL 2000 database to a SQL express 2005 version, I get a

"Error 602 Severity Level 21 Message Text

Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes."

Although when I ran DBCC CHECK, it returned 0 errors.

ANY RECOMMENDATIONS PLEASE !

-

PS. with respect to your problem: Did you try uninstalling SQL express and then backup from SQL 2000 and restoring from that .BAK (then re-install SQL express)?

|||

The only way to get data from sql2005 to sql2000 is to export/import (SSIS packages can be setup to do this).

As for the 3205 problem, there was a problem with one of the SQL2000.SP3 QFE's where the backup header was written in a way that earlier versions of the server could not read. That was fixed in later versions. We recommend that you move to SQL2000.SP4.

When attempting to restore a sql2005-formatted backup, versions of SQL2000 earlier than SP4 may receive the 3205 due to changes in the format of the backup header.

As for the 602 during upgrade, that's new to me. You might want to contact our product support:

http://www.microsoft.com/services/microsoftservices/srv_support.mspx

Hope that helps.

|||

Dear ,

I have created a SQL DB 2005 ,and now my laptop have Win XP OS,that's why i can't install SQL 2005,and i want to restore the DB atleast to SQL Express installed with VS 2005..can i do that?

if you have any other suggestion please Advice...

i get excited after i knew that any SQL 2005 can't be restored to any earlier DB....what's my solution on my current state.

|||

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||Moustafa, any backup created on SQL 2005 can be restored to any other edition of SQL 2005.|||

i find this amazing that this is how MS decides to force an upgrade. I want to goto sql 2005 but I need to feel comfortable and if they don't allow a simple way to transform headers for backward compatibility I am seriously disappointed.

Who develops like this.......

|||

Sorry to disagree PailnessProd, but on DBServers there's no safe backwards compatibility, never, as when a DB server goes to a next version, it implies structural changes, thus, making backwards compatibility pretty hard, as older versions DON'T have the structural changes. If they had, they would be the latest version.

so it's the correct way to go for Microsoft. :)

|||

Steve,

Is it possible to "export all the tables from sql2005" if I'm using SQL Server 2005 Express?

Thanks,
Jason

Steve Schmidt wrote:

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||

You can use the bcp utility to copy table data between any version of sql server.

I suggest you might start a new thread with a more specific question with respect to export/importing data, if that is your goal. You might get a wider audience of potential responders.

To reiterate the answer to the question "how to restore sql2005 to sql2000": it is simply not possible.

|||

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn’t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!

At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.

Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.

Step 1 Generating Scripts for the Database Elements and Structures

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3) Set the following Elements to the following Values

a. Script Collation , set to TRUE

b. Script Database Create, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to FALSE

e. Script Triggers, set to FALSE

Then Hit the Next button

4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5) Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3) Set all the Elements on the List to a False Value except the ones that follow:

a. Include IF NOT EXISTS , set to TRUE

b. Script Owner, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to TRUE

e. Script Triggers, set to TRUE

Then Hit the Next button

4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5) At the screen that follows hit the Select All button and the Next.

6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7) Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

www.hellasinternet.com

Panos Tzirakis & George Dounavis

|||

I may have to differ with you on that point. I am trying to do just that with a small database created on my laptop running SQL Server 2005. I have created a backup and tried to restore it on the class server also running SQL Server 2005. The following is the error message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

The above link is of no value as the 2005 to 2005 error occurance seems to be very rare and I have as yet found no one with a solution. I just spent an hour with the server admin for the class, and I seem to be the only one with this issue. Until a solution is found I am forced to create my assignments and work from the class server.

Since then, I have created a couple of databases, one empty, the other populated, with the exact same results. Do you have a solution for this particular error message?

|||

Hello,

PanosTZ - thanks for your detailed instructions.

I've attempted to follow your instructions (panosTz). It's worked successfully for one of my databases but I'm getting the following error for the other.

"Generate Script Progress

- Determining objects in database 'RetailStore' that will be scripted. (Error)

Messages

Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Smo)"

I can't get any information about the error received on any other forums or on the Microsoft KB.

Anyone who can explain what this means of point out area of fault would be much appreciated.

|||

Well, SQL Server 2005 Developer Edition is supported in Windows XP.

How to restore SQL 2005 DB to SQL 2000

Hello,

I'm tring to restor a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0818&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

BUTTONS:

OK

Why? and how to restore SQL 2005 DB to SQL 2000?

Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server. For more information about restoring databases, see Books Online topic RESTORE (Transact-SQL).

|||

Hi,

If its any clue or consolation, I get an error 3205 in the case where I backup {example} db from SQL 2000 SP3,

1. restore example db to same SQL version on another computer.

2. restore example db to SQL 2000 upgraded to SQL express 2005

3. backup from SQL express 2005, restore to same version on another computer.

"Too many backup devices specified for backup or restore; only 64 are allowed.
(Microsoft SQL Server, Error: 3205)"

Also, when alternatatively trying to attach the .mdf and .ldf files for this SQL 2000 database to a SQL express 2005 version, I get a

"Error 602 Severity Level 21 Message Text

Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes."

Although when I ran DBCC CHECK, it returned 0 errors.

ANY RECOMMENDATIONS PLEASE !

-

PS. with respect to your problem: Did you try uninstalling SQL express and then backup from SQL 2000 and restoring from that .BAK (then re-install SQL express)?

|||

The only way to get data from sql2005 to sql2000 is to export/import (SSIS packages can be setup to do this).

As for the 3205 problem, there was a problem with one of the SQL2000.SP3 QFE's where the backup header was written in a way that earlier versions of the server could not read. That was fixed in later versions. We recommend that you move to SQL2000.SP4.

When attempting to restore a sql2005-formatted backup, versions of SQL2000 earlier than SP4 may receive the 3205 due to changes in the format of the backup header.

As for the 602 during upgrade, that's new to me. You might want to contact our product support:

http://www.microsoft.com/services/microsoftservices/srv_support.mspx

Hope that helps.

|||

Dear ,

I have created a SQL DB 2005 ,and now my laptop have Win XP OS,that's why i can't install SQL 2005,and i want to restore the DB atleast to SQL Express installed with VS 2005..can i do that?

if you have any other suggestion please Advice...

i get excited after i knew that any SQL 2005 can't be restored to any earlier DB....what's my solution on my current state.

|||

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||Moustafa, any backup created on SQL 2005 can be restored to any other edition of SQL 2005.|||

i find this amazing that this is how MS decides to force an upgrade. I want to goto sql 2005 but I need to feel comfortable and if they don't allow a simple way to transform headers for backward compatibility I am seriously disappointed.

Who develops like this.......

|||

Sorry to disagree PailnessProd, but on DBServers there's no safe backwards compatibility, never, as when a DB server goes to a next version, it implies structural changes, thus, making backwards compatibility pretty hard, as older versions DON'T have the structural changes. If they had, they would be the latest version.

so it's the correct way to go for Microsoft. :)

|||

Steve,

Is it possible to "export all the tables from sql2005" if I'm using SQL Server 2005 Express?

Thanks,
Jason

Steve Schmidt wrote:

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||

You can use the bcp utility to copy table data between any version of sql server.

I suggest you might start a new thread with a more specific question with respect to export/importing data, if that is your goal. You might get a wider audience of potential responders.

To reiterate the answer to the question "how to restore sql2005 to sql2000": it is simply not possible.

|||

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn’t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!

At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.

Before beginning I must assume, firstly that the user, who will attempt such thing, hasa basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.

Step 1 Generating Scripts for the Database Elements and Structures

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3) Set the following Elements to the following Values

a. Script Collation , set to TRUE

b. Script Database Create, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to FALSE

e. Script Triggers, set to FALSE

Then Hit the Next button

4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5) Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3) Set all the Elements on the List to a False Value except the ones that follow:

a. Include IF NOT EXISTS , set to TRUE

b. Script Owner, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to TRUE

e. Script Triggers, set to TRUE

Then Hit the Next button

4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5) At the screen that follows hit the Select All button and the Next.

6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7) Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

www.hellasinternet.com

Panos Tzirakis & George Dounavis

|||

I may have to differ with you on that point. I am trying to do just that with a small database created on my laptop running SQL Server 2005. I have created a backup and tried to restore it on the class server also running SQL Server 2005. The following is the error message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

The above link is of no value as the 2005 to 2005 error occurance seems to be very rare and I have as yet found no one with a solution. I just spent an hour with the server admin for the class, and I seem to be the only one with this issue. Until a solution is found I am forced to create my assignments and work from the class server.

Since then, I have created a couple of databases, one empty, the other populated, with the exact same results. Do you have a solution for this particular error message?

|||

Hello,

PanosTZ - thanks for your detailed instructions.

I've attempted to follow your instructions (panosTz). It's worked successfully for one of my databases but I'm getting the following error for the other.

"Generate Script Progress

- Determining objects in database 'RetailStore' that will be scripted. (Error)

Messages

Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Smo)"

I can't get any information about the error received on any other forums or on the Microsoft KB.

Anyone who can explain what this means of point out area of fault would be much appreciated.

|||

Well, SQL Server 2005 Developer Edition is supported in Windows XP.

How to restore SQL 2005 DB to SQL 2000

Hello,

I'm tring to restor a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0818&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

BUTTONS:

OK

Why? and how to restore SQL 2005 DB to SQL 2000?

Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server. For more information about restoring databases, see Books Online topic RESTORE (Transact-SQL).

|||

Hi,

If its any clue or consolation, I get an error 3205 in the case where I backup {example} db from SQL 2000 SP3,

1. restore example db to same SQL version on another computer.

2. restore example db to SQL 2000 upgraded to SQL express 2005

3. backup from SQL express 2005, restore to same version on another computer.

"Too many backup devices specified for backup or restore; only 64 are allowed.
(Microsoft SQL Server, Error: 3205)"

Also, when alternatatively trying to attach the .mdf and .ldf files for this SQL 2000 database to a SQL express 2005 version, I get a

"Error 602 Severity Level 21 Message Text

Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes."

Although when I ran DBCC CHECK, it returned 0 errors.

ANY RECOMMENDATIONS PLEASE !

-

PS. with respect to your problem: Did you try uninstalling SQL express and then backup from SQL 2000 and restoring from that .BAK (then re-install SQL express)?

|||

The only way to get data from sql2005 to sql2000 is to export/import (SSIS packages can be setup to do this).

As for the 3205 problem, there was a problem with one of the SQL2000.SP3 QFE's where the backup header was written in a way that earlier versions of the server could not read. That was fixed in later versions. We recommend that you move to SQL2000.SP4.

When attempting to restore a sql2005-formatted backup, versions of SQL2000 earlier than SP4 may receive the 3205 due to changes in the format of the backup header.

As for the 602 during upgrade, that's new to me. You might want to contact our product support:

http://www.microsoft.com/services/microsoftservices/srv_support.mspx

Hope that helps.

|||

Dear ,

I have created a SQL DB 2005 ,and now my laptop have Win XP OS,that's why i can't install SQL 2005,and i want to restore the DB atleast to SQL Express installed with VS 2005..can i do that?

if you have any other suggestion please Advice...

i get excited after i knew that any SQL 2005 can't be restored to any earlier DB....what's my solution on my current state.

|||

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||Moustafa, any backup created on SQL 2005 can be restored to any other edition of SQL 2005.|||

i find this amazing that this is how MS decides to force an upgrade. I want to goto sql 2005 but I need to feel comfortable and if they don't allow a simple way to transform headers for backward compatibility I am seriously disappointed.

Who develops like this.......

|||

Sorry to disagree PailnessProd, but on DBServers there's no safe backwards compatibility, never, as when a DB server goes to a next version, it implies structural changes, thus, making backwards compatibility pretty hard, as older versions DON'T have the structural changes. If they had, they would be the latest version.

so it's the correct way to go for Microsoft. :)

|||

Steve,

Is it possible to "export all the tables from sql2005" if I'm using SQL Server 2005 Express?

Thanks,
Jason

Steve Schmidt wrote:

The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.

The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

|||

You can use the bcp utility to copy table data between any version of sql server.

I suggest you might start a new thread with a more specific question with respect to export/importing data, if that is your goal. You might get a wider audience of potential responders.

To reiterate the answer to the question "how to restore sql2005 to sql2000": it is simply not possible.

|||

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn’t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!

At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.

Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.

Step 1 Generating Scripts for the Database Elements and Structures

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3) Set the following Elements to the following Values

a. Script Collation , set to TRUE

b. Script Database Create, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to FALSE

e. Script Triggers, set to FALSE

Then Hit the Next button

4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5) Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3) Set all the Elements on the List to a False Value except the ones that follow:

a. Include IF NOT EXISTS , set to TRUE

b. Script Owner, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to TRUE

e. Script Triggers, set to TRUE

Then Hit the Next button

4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5) At the screen that follows hit the Select All button and the Next.

6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7) Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

www.hellasinternet.com

Panos Tzirakis & George Dounavis

|||

I may have to differ with you on that point. I am trying to do just that with a small database created on my laptop running SQL Server 2005. I have created a backup and tried to restore it on the class server also running SQL Server 2005. The following is the error message:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

The above link is of no value as the 2005 to 2005 error occurance seems to be very rare and I have as yet found no one with a solution. I just spent an hour with the server admin for the class, and I seem to be the only one with this issue. Until a solution is found I am forced to create my assignments and work from the class server.

Since then, I have created a couple of databases, one empty, the other populated, with the exact same results. Do you have a solution for this particular error message?

|||

Hello,

PanosTZ - thanks for your detailed instructions.

I've attempted to follow your instructions (panosTz). It's worked successfully for one of my databases but I'm getting the following error for the other.

"Generate Script Progress

- Determining objects in database 'RetailStore' that will be scripted. (Error)

Messages

Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Smo)"

I can't get any information about the error received on any other forums or on the Microsoft KB.

Anyone who can explain what this means of point out area of fault would be much appreciated.

|||

Well, SQL Server 2005 Developer Edition is supported in Windows XP.