Script both schema and data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Script both schema and data

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 02:26:54

I just got hold of this article at Microsoft and I will try it out at once.
http://www.microsoft.com/downloads/details.aspx?FamilyID=16d8c1e9-a6a3-43a2-9ba7-916c6eb5023b&DisplayLang=en

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 02:35:48

Well, that was fast. I had to install MSXML6, SQLNCLI and XMO too, prior to the Wizard installation.
Then I realized that the Wizard is command based (DOS).

But typing "sqlpubwiz script -d Northwind c:\test_nw.sql", took only about 15 seconds to process and create!
And the script worked when running on a machine that did not have Northwind installed.

Thumbs up!

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 03:40:18

Peter,

That's wonderful tool then !
Does it script the data in the form of set of INSERT statements?

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 03:42:41

Yes. And complete schema too, with constraints.
And functions. And stored procedures.

This below is an excerpt from the script created

/****** Object:  ForeignKey [FK_CustomerCustomerDemo]    Script Date: 10/17/2006 08:32:15 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CustomerCustomerDemo]') AND type = 'F')
ALTER TABLE [dbo].[CustomerCustomerDemo] DROP CONSTRAINT [FK_CustomerCustomerDemo]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CustomerDemographics]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CustomerDemographics](
	[CustomerTypeID] [nchar](10) NOT NULL,
	[CustomerDesc] [ntext] NULL,
 CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY NONCLUSTERED 
(
	[CustomerTypeID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

/****** Object:  StoredProcedure [dbo].[t]    Script Date: 10/17/2006 08:32:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[t]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[t]
@i int
as

if @i = 1
begin
select s Name,identity (int,1,1) as intid into #T1
from 
(
select ''SS'' s) p
end
if @i = 2
begin
select s Name,identity (int,1,1) as intid into #T2
from 
(
select ''S'' s) p
end
' 
END
GO

TRUNCATE TABLE [dbo].[Order Details]
INSERT [dbo].[Order Details] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10248, 11, 14,0000, 12, 0)
INSERT [dbo].[Order Details] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10248, 42, 9,8000, 10, 0)
INSERT [dbo].[Order Details] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10248, 72, 34,8000, 5, 0)
INSERT [dbo].[Order Details] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10249, 14, 18,6000, 9, 0)

/****** Object:  ForeignKey [FK_Orders_Shippers]    Script Date: 10/17/2006 08:32:15 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Orders_Shippers]') AND type = 'F')
ALTER TABLE [dbo].[Orders]  WITH NOCHECK ADD  CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([ShipVia])
REFERENCES [dbo].[Shippers] ([ShipperID])
GO

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 03:54:39

Oops! Found a bug in the Wizard script.

Decimals are not scripted as 14.6 for me. Swedish notation is comma for decimal. Decimals are scripted as 14,6 which makes the script not working when inserting. I get an error for "Invalid number of columns".

What a mistake from Microsoft!

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 04:01:19

quote:
Originally posted by Peso

Oops! Found a bug in the Wizard script.
Decimals are not scripted as 14.6 for me. Swedish notation is comma for decimal. Decimals are scripted as 14,6 which makes the script not working when inserting. I get an error for "Invalid number of columns".

What a mistake from Microsoft!

Peter Larsson
Helsingborg, Sweden

Doesn't set of INSERT statements will make database creation slower for big databases?

quote:
What a mistake from Microsoft!

May be you could come out with SP1 for that !! (culture-neutral script)

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 04:03:55

I reported the problem to Microsoft and immediately after posting the report, my "Open in new tab" menu disappeared in Internet Explorer 7.

Did they just punish me for reporting a bug?

Peter Larsson
Helsingborg, Sweden

PS: I just rebooted my computer and the "Open in new Tab" is still gone...
Ps2: I just reinstalled IE7 and the menu "Open in new Tab" is still not there... Thanks for the coffee, Microsoft.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 05:36:05

quote:
Originally posted by Peso

I reported the problem to Microsoft and immediately after posting the report, my "Open in new tab" menu disappeared in Internet Explorer 7.

Did they just punish me for reporting a bug?

Peter Larsson
Helsingborg, Sweden

PS: I just rebooted my computer and the "Open in new Tab" is still gone...
Ps2: I just reinstalled IE7 and the menu "Open in new Tab" is still not there... Thanks for the coffee, Microsoft.

hehehe !!

mind it Peter, next time reporting bugs to Microsoft !
That could be injurious to your current working system

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 05:38:00

Dang.

I got it working now. I found out it was GOOGLE toolbar and IE7 that does not work well together anymore.
Uninstalling GOOGLE toolbar made the "Open in new Tab" reappear without closing the IE7 window.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 05:42:26

Now that's what I call Google vs Microsoft war revealed !

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 03:55:38

UPDATE.
I contacted Microsoft support and all they could tell me was that I had to contact Microsoft Sweden to fix this.
So I called them. They told me that they don't write the application and have to talk to MS USA.

What's that name of the book again, by Joseph Heller? Catch-22?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-24 : 05:00:20

i already suggested to MS a culture neutral decimal separator.

I suggested tu use a ~ or a # instead of the decimal separator in update and insert scripts only.
And it would be allowed only in numeric/decimal/float datatypes.

so you ca do:
insert into t1 (MyDecimalCol)
select 123#456 union all
select 789#123

and it would work. simple culture insensitive.
Their answer:
Thanx for the suggestion. we'll look into it for the next verion of sql server.
so Katmai (SS2k8) better have this

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 05:07:08

I want the scripts yesterday!

Peter Larsson
Helsingborg, Sweden

robert.dennis
Starting Member

1 Post

Posted - 2006-11-16 : 19:00:44

Can you post the name of the utility. I tried following the link but it did not work.

Thanks,

Rob

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-16 : 22:51:43

it's hosted on codeplex now:

http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-16 : 22:55:08

thought I would also mention scriptdb, an open source app i wrote that is similar to this wizard. it generates a separate file for each script though, and scripts the data out using bcp rather than insert statements (which is better for larger amounts of data).

you can get it here: http://www.elsasoft.org/tools.htm

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Sitka
Aged Yak Warrior

571 Posts

Posted - 2006-11-17 : 00:26:54

nice links folks, thanks.

I've learned a lot this week.

"it's definitely useless and maybe harmful".

maxim
Yak Posting Veteran

51 Posts

Posted - 2006-11-17 : 06:37:22

Please, forgive my question!

I tried install this program but i don't have mssql 2005 colletions installed.

This program only run into mssql 2005 ?

Thanks,
Max

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-17 : 12:44:53

You need to install MSXML6, SQLNCLI and SMO to get it to work, as Peso said near the beginning of the thread.

You can get it this stuff here (search for SMO on this page): http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

You'll also need .net 2.0 if you don't have it already.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

maxim
Yak Posting Veteran

51 Posts

Posted - 2006-11-17 : 14:05:53

thanks jezemine, but can i continue using mssql 2000 ?

Because i don't want install mssql 2005!

Max

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-18 : 01:00:34

you don't have to install 2005. both scriptdb and the publishing wizard work against a 2000 instance.

you need to install these components on the CLIENT machine, not the server.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources