How to save entire database structure without data?

I want to save a complete copy of the database structure without any of the
data. Tables, Sprocs, Permissions, Triggers, etc.
How do I go about doing this and getting EVERYTHING except the data? Thanks
On Dec 11, 12:13 pm, "JTMZ" <J...@.hotmail.com> wrote:
> I want to save a complete copy of the database structure without any of the
> data. Tables, Sprocs, Permissions, Triggers, etc.
> How do I go about doing this and getting EVERYTHING except the data? Thanks
You can create script for the entire object in the database. Right
click on the database and select Tasks then Generate Scripts which
will open a window of SQL Server Scripts Wizard. The rest is self
explanotary.
Bulent

How to save entire database structure without data?

I want to save a complete copy of the database structure without any of the
data. Tables, Sprocs, Permissions, Triggers, etc.
How do I go about doing this and getting EVERYTHING except the data? ThanksOn Dec 11, 12:13 pm, "JTMZ" <J...@.hotmail.com> wrote:
> I want to save a complete copy of the database structure without any of the
> data. Tables, Sprocs, Permissions, Triggers, etc.
> How do I go about doing this and getting EVERYTHING except the data? Thanks
You can create script for the entire object in the database. Right
click on the database and select Tasks then Generate Scripts which
will open a window of SQL Server Scripts Wizard. The rest is self
explanotary.
Bulentsql

How to save entire database structure without data?

I want to save a complete copy of the database structure without any of the
data. Tables, Sprocs, Permissions, Triggers, etc.
How do I go about doing this and getting EVERYTHING except the data? ThanksOn Dec 11, 12:13 pm, "JTMZ" <J...@.hotmail.com> wrote:[vbcol=seagreen]
> I want to save a complete copy of the database structure without any of th
e
> data. Tables, Sprocs, Permissions, Triggers, etc.
> How do I go about doing this and getting EVERYTHING except the data? Thanks[/vbco
l]
You can create script for the entire object in the database. Right
click on the database and select Tasks then Generate Scripts which
will open a window of SQL Server Scripts Wizard. The rest is self
explanotary.
Bulent

How to return an entire table using store procedure

hi all

this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.

I know how parameters can be passed in and out of store procedures, but this returns single value parameters.

when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.

But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application?

thanks for taking your time to read.

Cheers

jOriginally posted by nano_electronix
hi all

this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.

I know how parameters can be passed in and out of store procedures, but this returns single value parameters.

when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.

But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application?

thanks for taking your time to read.

Cheers

j

Hi, maybe I did not understand your problem entirely, but a stored procedure returning a recordset is as simple as

create procedure P as select * from sales
go

exec p
go

Hope, this helps.|||but wat happens when there are multiple select statements

how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp

besides given the store procedure you shown there, how would you get it into a recordset/dataset using either asp or asp.net.

for example, in asp.net i make use of dataadapter to retrieve a table into a dataset (which can store multiple table) but if i use store procedure i am not sure how that can be done.

cheers

j|||i think i might know why you don't understand my problem

the sql command that you gave is correct and will return a table of results if it is executed within the query analyser.

wat i am talking about is how would i retrieve the table of results if i were to use the store procedure within an application, how would i retrieve those results into a recordset/dataset (ASP/ASP.NET)

when i want to execute a storeprocedure using ms .net, wat i have to do is use a oledbcommand and make it of type storeprocedure and then execute it as a nonquery, but i have no idea how the result may be returned to the application when the oledbcommand is executed as a nonquery. i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).

I am sure someone would have run across a time when an application need to use a store procedure to execute a batch of sql commands and at the same time returns results to the application as a table of data.

if i can't do this, the only way that i could achieve the same effect is by creating temporary table and then execute an extra select statement to retrieve the data from the temporary table, this would be quite wasteful.

Please help

J|||Hi

Executing SP which will return a record set within .Net is straight forward. By the way for SQL2000 you should use SQLClient name space functions instead of OleDB for performance reason more then anything.

Use the SQLCommand.ExecuteReader and set the commandtext to the stored procedure and parameters and commandtype = CommandType.StoredProcedure. I enclosed the example from MSDN below for SQL. If you do need OleDB then just change the Sql to OleDb.

Regards

Richard...

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = salesCMD.Parameters.Add("@.CategoryName", SqlDbType.NVarChar, 15);
myParm.Value = "Beverages";

nwindConn.Open();

SqlDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();
nwindConn.Close();|||I had the same problem about a month ago. I think what you're looking for is the datareader. Here's an example of how it works:

Dim connection As New SqlConnection()
Dim cmdSelect As SqlCommand
Dim reader As SqlDataReader
Dim Num As Integer

Num = 12
connection = SqlConnection1

cmdSelect = New SqlCommand("exec sp_getNum @.date = " & Num), connection)
If connection.State <> ConnectionState.Open Then connection.Open()
reader = cmdSelect.ExecuteReader
datalist.DataSource = reader
datalist.DataBind()
reader.Close()
connection.Close()

If you need more tables, just created more readers. Is that what you're looking for?|||Originally posted by nano_electronix
but wat happens when there are multiple select statements

how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp
j

I would guess you would want to have one select statement per sproc. and then create a recordset for each executed sproc?|||thanx guys

I'll try the datareader again, but i have tried it exactly the same way as the sample code above, but like i said it gave me exception, i'll give it another try and get back to you guys, hope it work.

Originally posted by nano_electronix

i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).

J|||hi guys

first of all thanx for very much for all your help. i will sure to come back to this forum for more help later.

i found out where the problem was, it was a datatype conversion problem that gave me the exception, it wasn't the datareader that gave me the exception, i didn't check the exception carefully.

i am using oledb over the managed sql components because i want to try to make this application crossplatform for all databases, i haven't had a chance to try oracle yet, but that's where i am heading.

cheers :)

j

How to restore the entire replication topology in different boxe

We are migrating the entire sqlserver environment from Window2000 to Windows
2003 on different boxes with different NT servernames. I have installed the
sqlserver instances and I have restored the publisher, distribution db on the
primary side and the subscriber db on the replicated side. However, I don't
see any publications under the replication folder. Do I need to go through
the replication configuration first then restore the distribution?
Please advise. Thanks.
Wen,
for migrating to another server which has a different netbios name, the only
realistic solution is to script out the publications on the original setup,
install the new servers and then run the scripts on the new server, changing
the necessary parts eg servername and job owners.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)