A script to generate a script?

Hi,
I'm using 2005's management studio, and I was wondering if there's
anyway to script the "generate scripts - script wizard" so that when I
want to take a snapshot of my database structure I can just run a quick
script instead of having to use the wizard. The fact that it's a
"wizard" leads me to think that there is a "non-wizard" way of doing
it, but I can't find anything in the docs.
Thanks
Chris.Hmm, just a guess , take a look at SMO object library (former SQL DMO)
"chrisb" <chrisbuckett@.gmail.com> wrote in message
news:1150792870.625977.257100@.h76g2000cwa.googlegroups.com...
> Hi,
> I'm using 2005's management studio, and I was wondering if there's
> anyway to script the "generate scripts - script wizard" so that when I
> want to take a snapshot of my database structure I can just run a quick
> script instead of having to use the wizard. The fact that it's a
> "wizard" leads me to think that there is a "non-wizard" way of doing
> it, but I can't find anything in the docs.
> Thanks
> Chris.
>|||You can use SMO...
check this link.. for a start
http://davidhayden.com/blog/dave/ar...02/09/2795.aspx
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chrisb" <chrisbuckett@.gmail.com> wrote in message
news:1150792870.625977.257100@.h76g2000cwa.googlegroups.com...
> Hi,
> I'm using 2005's management studio, and I was wondering if there's
> anyway to script the "generate scripts - script wizard" so that when I
> want to take a snapshot of my database structure I can just run a quick
> script instead of having to use the wizard. The fact that it's a
> "wizard" leads me to think that there is a "non-wizard" way of doing
> it, but I can't find anything in the docs.
> Thanks
> Chris.
>

A problem getting value out of Stored Procedure

Server management studio does not give error from following query, but output parameter (kokonaissumma) is always NULL. I tested it other ways, by making it return value then it worked. But that required changes to the query, so I really don't know. The problem query is the last.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END
return (SELECT count(*) FROM dbo.t_osto)
select @.kokonaissumma =sum(Yhteensa)FROM dbo.t_osto

sum(Yhteensa)FROM dbo.t_osto

your missing a space:

sum(Yhteensa) FROM dbo.t_osto

|||

Move your SELECTs before the RETURN.

create PROCEDURE [dbo].[kori2]( @.Tuotekoodivarchar(20),@.kokonaissummamoney output)ASBEGIN SET NOCOUNT ON;IFNOT EXISTS(SELECT *FROM dbo.t_ostoWHERE Tuotekoodi=@.Tuotekoodi)BEGIN INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,AlvFROM dbo.t_TuoteWHERE Tuotekoodi= @.TuotekoodiENDELSE BEGIN UPDATE dbo.t_ostoSET Maara=Maara+1WHERE Tuotekoodi=@.TuotekoodiENDselect @.kokonaissumma =sum(Yhteensa)FROM dbo.t_ostoEND

|||

It works. Thank you both. It looks like this now.

...

...

UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)
END

Regards

Leif

|||

You dont need the return statement. The count is being returned through the OUTPUT parameter.

|||

I see. I'll fix that too. Below is part of the query now, it has now all features what I planned.

--

--

select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto --total money
select @.tuotemaara =sum(Maara) FROM dbo.t_osto -- how many items

return (SELECT count(*) FROM dbo.t_osto) --how many lines (and no return)

END

I should learn some sql. It is my weakest point in ASP.NET. I looked at "Books on line", but even first page used so unfamiliar terms, I was not able to go much further than that. Well, search is there and today I found some tutorials in there.

Thanks

Leif

|||

Hi again

I tried my stored procedure without return. Like this. This is my latest a refresh only version.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
SELECT count(*) FROM dbo.t_osto

With this the return value was 0. That was not the correct value. I wonder what else is wrong in my code.

This gave correct value as before. Below.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)

Regards

Leif

A few things to do with store variables.

Okay,

I'm creating a small application for my local school district that will allow them to do a small management of inventory.

Is there a way to create an executable script that will create the default databases the first time? If so, what all do I need to do for the script and to make it execute? I want to make a section in my OPTIONS area where users can put the path to their db (will not be hosted on the same PC as the software) along with the username and password for the db. I know I can store this information in a predetermined variable in the program so that it can reference it off and on.

Could someone please help me with this..... If it's in the wrong forum please let me know.

Thanks,

QWERTYtech

What kind of database does your program use? Full SQL Server, SQL Server Express, Access, or other?|||SQL Express|||

Move the thread from Visual Basic General. There are more exports on storing variables in SQL Express database. I believe you can get satisfying answers in this forum.

Thanks again for your question.

|||

Perhaps these sources will help:

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648