Scripting a Database

All,
I need to be able to create a script of a database from within a .NET
application. Does anyone know of any code (T-SQL, C# etc.) available to do
this?
Thanks.Search for "SQLDMO .Net" in your favourite search engine and you will get a
ton of info.
Darrel
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:jsCdnac1rpQIBqXeRVn-hg@.buckeye-express.com...
> All,
> I need to be able to create a script of a database from within a .NET
> application. Does anyone know of any code (T-SQL, C# etc.) available to do
> this?
> Thanks.
>|||I was under the impression that SQLDMO was still only available as a COM
object ... thus in .Net, you would have to use a wrapper.
"Darrel Miller" <darrel@.tavis.ca> wrote in message
news:Oc1IaLwwFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Search for "SQLDMO .Net" in your favourite search engine and you will get
> a ton of info.
> Darrel
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:jsCdnac1rpQIBqXeRVn-hg@.buckeye-express.com...
>|||Earl wrote:
> I was under the impression that SQLDMO was still only available as a
> COM object ... thus in .Net, you would have to use a wrapper.
> "Darrel Miller" <darrel@.tavis.ca> wrote in message
> news:Oc1IaLwwFHA.3236@.TK2MSFTNGP14.phx.gbl...
That's true, but it's somewhat automated by Visual Studio .Net:
Visual Studio .NET generates an interop assembly containing metadata
when you add a reference to a given type library. If a primary interop
assembly is available, Visual Studio uses the existing assembly before
generating a new interop assembly.
To add a reference to a type library
1.. Install the COM DLL or EXE file on your computer, unless a Windows
Setup.exe performs the installation for you.
2.. From the Project menu, select References.
3.. Select the COM tab.
4.. Select the type library from the Available References list, or
browse for the TLB file.
5.. Click OK.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Scripting a Database

All,
I need to be able to create a script of a database from within a .NET
application. Does anyone know of any code (T-SQL, C# etc.) available to do
this?
Thanks.
You probably need to wrap COM (SQLDMO). Here are some options:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:18qdnYXLU7Y_BqXeRVn-gA@.buckeye-express.com...
> All,
> I need to be able to create a script of a database from within a .NET application. Does anyone
> know of any code (T-SQL, C# etc.) available to do this?
> Thanks.
>
|||I used to use SQL-DMO, but when I built my scripts, the dependancies
were off. I found the following article, which takes this into
account:
http://www.sqlservercentral.com/colu...tionscript.asp
It's very fast as well.
Stu

Script your data in a T-SQL format (which can be executed in Query Analyzer)

Hi all,

i'm working with SQL Server for about 4 years, and i have ever
searching for a tool which can script my DATA in a T-SQL format to
copy them very easy from one server to another.

For example scripts like that:
IF NOT EXISTS (...)
INSERT INTO (...)
ELSE
UPDATE (...)

I have never found a tool like that, so i write a it by myself.
This software is a free version in the moment, you can download it on
my website:

http://www.sqlscripter.com

..NET Framework on your client PC is required.
Have fun with it ...

ThomasThomas (postmaster@.sqlscripter.com) writes:
> i'm working with SQL Server for about 4 years, and i have ever
> searching for a tool which can script my DATA in a T-SQL format to
> copy them very easy from one server to another.
> For example scripts like that:
> IF NOT EXISTS (...)
> INSERT INTO (...)
> ELSE
> UPDATE (...)
>
> I have never found a tool like that, so i write a it by myself.
> This software is a free version in the moment, you can download it on
> my website:
> http://www.sqlscripter.com
> .NET Framework on your client PC is required.
> Have fun with it ...

I don't want to belittle your work, but the standard reference for this kind
of thing is SQL Server MVP Naryana Vyas Kondreddi's utility at
http://vyaskn.tripod.com/code.htm#inserts.

I don't think he supports UPDATE though.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93B2EBD6198ECYazorman@.127.0.0.1>...
> Thomas (postmaster@.sqlscripter.com) writes:
> > i'm working with SQL Server for about 4 years, and i have ever
> > searching for a tool which can script my DATA in a T-SQL format to
> > copy them very easy from one server to another.
> > For example scripts like that:
> > IF NOT EXISTS (...)
> > INSERT INTO (...)
> > ELSE
> > UPDATE (...)
> > I have never found a tool like that, so i write a it by myself.
> > This software is a free version in the moment, you can download it on
> > my website:
> > http://www.sqlscripter.com
> > .NET Framework on your client PC is required.
> > Have fun with it ...
> I don't want to belittle your work, but the standard reference for this kind
> of thing is SQL Server MVP Naryana Vyas Kondreddi's utility at
> http://vyaskn.tripod.com/code.htm#inserts.
> I don't think he supports UPDATE though.

Hi Erland,

thank you for your comment, the SP from "SQL Server MVP Naryana Vyas
Kondreddi" is very powerful ! Unfortunately only INSERTS are
supported, and in most of my cases, i need more functionality.

However, thanks for this link, good to know !

Thomas

Script Out T-SQL Code Programmatically

I'm looking for a way to programmatically (ideally using T-SQL) script
out code objects in order of precedence .
I must make clear that I'm quite satisfied that I have found a way of
listing the names Views, Stored Procs & Functions that need to be
changed in order of precedence (eg if SP spEmployeeUpdate depends on
view vselEmployee which in turn depends on table tblEmployee then if
tblEmployee has been updated you need to recompile (?) vselEmployee
then spEmployeeUpdate). OK, so that's what this post is not about!
So . . . I have a temporary T-SQL table which comprises in order the
code objects which need to be recompiled - all well and good but how
can I script out the objects so I can run the script once and it's
done?
Using the above example, after tblEmployee has been updated I would
like to generate code like this . . .
ALTER VIEW vselEmployee
AS
BEGIN
|
|
END
GO
ALTER PROCEDURE spEmployeeUpdate
AS
BEGIN
|
|
END
GO
I've tried using SysComments.Text (which contains the code) but without
success (truncation of text, loss of formatting etc).
I know that Enterprise Manager allows me to script out a selection of
code objects but 1) I can't control it programmatically; 2) it's not in
any useful order; 3) It uses "DROP" & "CREATE" rather than the
preferred "ALTER" etc.
I've seen several posts on this topic but they tend to get bogged down
on the bit I've already solved.
Any ideas?Hi
You may want to look at using DMO to do this, similar to
http://www.nigelrivett.net/DMOScriptAllDatabases.html
John
"Pete Nurse" wrote:

> I'm looking for a way to programmatically (ideally using T-SQL) script
> out code objects in order of precedence .
> I must make clear that I'm quite satisfied that I have found a way of
> listing the names Views, Stored Procs & Functions that need to be
> changed in order of precedence (eg if SP spEmployeeUpdate depends on
> view vselEmployee which in turn depends on table tblEmployee then if
> tblEmployee has been updated you need to recompile (?) vselEmployee
> then spEmployeeUpdate). OK, so that's what this post is not about!
> So . . . I have a temporary T-SQL table which comprises in order the
> code objects which need to be recompiled - all well and good but how
> can I script out the objects so I can run the script once and it's
> done?
> Using the above example, after tblEmployee has been updated I would
> like to generate code like this . . .
> ALTER VIEW vselEmployee
> AS
> BEGIN
> |
> |
> END
> GO
> ALTER PROCEDURE spEmployeeUpdate
> AS
> BEGIN
> |
> |
> END
> GO
> I've tried using SysComments.Text (which contains the code) but without
> success (truncation of text, loss of formatting etc).
> I know that Enterprise Manager allows me to script out a selection of
> code objects but 1) I can't control it programmatically; 2) it's not in
> any useful order; 3) It uses "DROP" & "CREATE" rather than the
> preferred "ALTER" etc.
> I've seen several posts on this topic but they tend to get bogged down
> on the bit I've already solved.
> Any ideas?
>|||Thanks John, that's excellent code - very clear and well written. I'm
busy now trying to work out what it's doing!

Script code for "Reinitialize All Subscripers" function SQL 2005

Hello All
We are running Merge replication on SQL 2005 with Pocket PC Clients.
My question is, can i script (T-SQL) the same function as if i rightclick on
the Replication Publication, and select "Reinitialize All Subscripers" and
then click on "Create New SnapShot" + "Upload Changes"?
Which system stored procedures should i use? I need to do the same just with
T-SQL.
Thanks
use sp_reinitsubscription
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RU Developer" <RUDeveloper@.discussions.microsoft.com> wrote in message
news:30B41C88-47D1-4C19-B580-757A3F40FE77@.microsoft.com...
> Hello All
> We are running Merge replication on SQL 2005 with Pocket PC Clients.
> My question is, can i script (T-SQL) the same function as if i rightclick
> on
> the Replication Publication, and select "Reinitialize All Subscripers" and
> then click on "Create New SnapShot" + "Upload Changes"?
> Which system stored procedures should i use? I need to do the same just
> with
> T-SQL.
> Thanks
|||or sp_reinitpullsubscription if you have pull subscriptions
Hilary Cotter wrote:[vbcol=seagreen]
> use sp_reinitsubscription
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "RU Developer" <RUDeveloper@.discussions.microsoft.com> wrote in message
> news:30B41C88-47D1-4C19-B580-757A3F40FE77@.microsoft.com...