SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Script both schema and data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/17/2006 :  02:26:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 10/17/2006 :  02:35:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 10/17/2006 02:36:30
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/17/2006 :  03:40:18  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/17/2006 :  03:42:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 10/17/2006 03:49:20
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/17/2006 :  03:54:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/17/2006 :  04:01:19  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/17/2006 :  04:03:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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.


Edited by - SwePeso on 10/17/2006 04:33:39
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/17/2006 :  05:36:05  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/17/2006 :  05:38:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/17/2006 :  05:42:26  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Now that's what I call Google vs Microsoft war revealed !

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/24/2006 :  03:55:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 10/24/2006 :  05:00:20  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/24/2006 :  05:07:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I want the scripts yesterday!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

robert.dennis
Starting Member

1 Posts

Posted - 11/16/2006 :  19:00:44  Show Profile  Reply with Quote
Can you post the name of the utility. I tried following the link but it did not work.

Thanks,

Rob
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 11/16/2006 :  22:51:43  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 11/16/2006 :  22:55:08  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 11/17/2006 :  00:26:54  Show Profile  Reply with Quote
nice links folks, thanks.

I've learned a lot this week.

"it's definitely useless and maybe harmful".
Go to Top of Page

maxim
Yak Posting Veteran

51 Posts

Posted - 11/17/2006 :  06:37:22  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 11/17/2006 :  12:44:53  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

maxim
Yak Posting Veteran

51 Posts

Posted - 11/17/2006 :  14:05:53  Show Profile  Reply with Quote
thanks jezemine, but can i continue using mssql 2000 ?

Because i don't want install mssql 2005!

Max
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 11/18/2006 :  01:00:34  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000