Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

i've been attempting to script a database using SQL Server 2005 Management Studio. i cannot get it to script some objects. It scripts others, but skips some.

i can provide detailed screen shots

  • the options being selected
  • including all tables
  • the folder where the script files will go
  • the folder being empty before scripting
  • the scripting process saying Sucess when scripting a table
  • the destination folder no longer empty, with a hundred or so script files
  • the script of some tables not being in the folder.

And earlier SSMS would not script some views.

Is this a known thing that the the Generate Scripts task does not generate scripts?


Update

Known issue on Microsoft Connect, but Microsoft couldn't repro the steps, so they closed closed the ticket.

Fails on SQL Server 2005, also fails on SQL Server 2008.


Update Two

Some basic questions:

1.What version of SQL Server?

 Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
 Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
 Microsoft SQL Server 2008 - 10.0.2531.0 (Intel X86)
 Microsoft SQL Server 2008 - 10.0.5768.0 (X64)
 Microsoft SQL Server 2005 Management Studio: 9.00.4035.00
 Microsoft SQL Server 2008 Management Studio: 10.0.1600.22

2.What O/S are you running on?

 Windows Server 2000
 Windows Server 2003
 Windows Server 2008
 Windows Server 2008 R2 Standard

3.How are you logging in to SQL server?

 sa/password
 Trusted authentication

4.Have you verified your account has full access to all objects?

 Yes, i have access to all objects.

5.Can you use the objects that fail to script? (eg: select top(10) * from nonScriptingTable)

 Yes, all objects work fine.
 SQL Server Enterprise Manager can script the objects fine.

Update Three

They fail no matter what version of SQL Server you script against. It wasn't a problem in Enterprise Manager:

Client Tools  SQL Server 2000  SQL Server 2005  SQL Server 2008
============  ===============  ===============  ===============
2000          Yes              n/a              n/a
2005          No               No               No
2008          No               No               No

Update Four

No errors found in the database using:

DBCC CHECKDB
go
DBCC CHECKCONSTRAINTS
go
DBCC CHECKFILEGROUP
go
DBCC CHECKIDENT
go
DBCC CHECKCATALOG
go
EXECUTE sp_msforeachtable 'DBCC CHECKTABLE (''?'')'

Honk if you hate SSMS.

Update (four years later): Honk!

share|improve this question
    
So you scripted from 2000=>2005 and are running SP3 too? –  Wayne Sep 18 '09 at 23:28
    
i'm not sure what you're asking. We restored the database from 2000 on to 2005 and to 2008 (i.e. we didn't script them to get them into 2005 and 2008). Then tried scripting using the 2005 and 2008 tools against a 2005 and 2008 servers. –  Ian Boyd Sep 21 '09 at 15:23
    
Seeing if the scripting works if we fiddle the 2005 or 2008 database levels will be next. –  Ian Boyd Sep 21 '09 at 15:25
    
None of it works. We just bought SQL Compare and use it to generate scripts. le sigh –  Ian Boyd Oct 27 '09 at 20:51

2 Answers 2

I have never had this problem before.

Some basic questions:

  1. What version of SQL Server?
  2. What O/S are you running on?
  3. How are you logging in to SQL server?
  4. Have you verified your account has full access to all objects?
  5. Can you use the objects that fail to script? (eg: select top(10) * from nonScriptingTable)

With some further information we maybe able to offer some help.

Update 1:

Have you tried detaching your 2000 Database and reattaching to a SQL 2005/2008 instance and then raising the database level, and then try your scripting?

share|improve this answer
    
Today, four years later, i'm getting it with SQL Server 2008 SP3. The issue was never fixed; i just stopped trusting SSMS to script a database (and bought a 3rd party tool). Today this is a customer's database, on a customer's server, so i can't run the 3rd party tool; and was forced to trust SSMS. –  Ian Boyd Feb 11 '13 at 19:16

I wrote a command-line utility to script all MSSQL objects via SMO.

It would be interesting to know whether it manages to script all of your objects. If the tool fails for a single object, the SMO exception is written to stderr.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.