Script to Export SQL Tables to Access

Mello

I am not sure if this is eaven possible but I need a script to quickly
export selected tables from a SQL database into Access.

The script should preferably run from an ASP page on demand or perhaps
be triggered by an update of one of the selected tables.

Any help will be greatly appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

You could either use DTS and run the package or create a linked server and
use regular insert statements.

Dor DTS this may help:
http://www.sqldts.com/default.aspx?288

John

"Mello" <anonymous@.devdex.com> wrote in message
news:41383ecb$0$26170$c397aba@.news.newsgroups.ws.. .
> Mello
> I am not sure if this is eaven possible but I need a script to quickly
> export selected tables from a SQL database into Access.
> The script should preferably run from an ASP page on demand or perhaps
> be triggered by an update of one of the selected tables.
> Any help will be greatly appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Nic?

DTS should work just fine, or just write an aspx to fill a dataset, then
use a component to create an MDB. Fill the MDB from the dataset and
you're good.

Weird that this was posted under my account, I never posted the original
message in this thread.

Good Luck,
Joel

--
Joel Mello
Mello Software Consulting
www.MelloSoftwareConsulting.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.
Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer.SetDefaultInitFields(GetType(StoredProce dure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.
|||http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database with
> about a hundred tables and a few hundred derived objects - views, functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.|||http://www.sqlteam.com/item.asp?ItemID=23185
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database with
> about a hundred tables and a few hundred derived objects - views, functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer. SetDefaultInitFields(GetType(StoredProce
dure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.|||http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database wit
h
> about a hundred tables and a few hundred derived objects - views, function
s,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.