creating an existing db schema baseline

What is the best method of creating schema creation scripts that can be
stored into a version control system. The process of using em to
generate a script is not an appealing option. I am still learning the
MS Sql sys tables and have not found a useful list of all the codes &
types to join the tables etc.

mike

--
Posted via http://dbforums.comwukie <member30544@.dbforums.com> wrote in message news:<3242331.1060980041@.dbforums.com>...
> What is the best method of creating schema creation scripts that can be
> stored into a version control system. The process of using em to
> generate a script is not an appealing option. I am still learning the
> MS Sql sys tables and have not found a useful list of all the codes &
> types to join the tables etc.
>
> mike

I don't like the fact that all source code versioning systems are
using proprietary files instead of proven relational databases
(SourceSafe is not exception from this). The reasons for this are
probably RDBMS licensing costs in the past.

Database schema can be exported also as XML file, which can be further
manipulated. If you and your team have serious schema versioning needs
I suggest you to evaluate Meta Data Services in SQL Server 2000 and
XML. One article about this has been published in the MSDN Magazine:
http://msdn.microsoft.com/msdnmag/i...es/default.aspx

Metadata Repository can be created not only through Enterprise Manager
but also programmatically using Meta Data API. Further information
with examples can be found in Meta Data Services SDK 3.0, which can be
downloaded for free.

Sinisa Catic|||found what I was looking for...

in EM > Tools > Generate SQL Scripts. THis will create the total schema
of the existing database.

mike

any known issues with this tool??

--
Posted via http://dbforums.com

Creating a SQL MAX command

Hello All,

I'm currently in the process of developing a new PO system for work. What I need to accomplish is a SQL MAX command to find the largest PO number and then add 1 to that number. I'm then saving this in a session state so users can create multiple items for that same PO number. Here's what I have so far:

1protected void Page_Load(object sender, EventArgs e)2 {3// connection string to your database4 SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=Purchasing;Integrated Security=SSPI;");56// create command object to execute query7 SqlCommand mySqlCommand = mySqlConnection.CreateCommand();89// set command text to the INSERT sql query10 mySqlCommand.CommandText ="SELECT MAX(PONumber)FROM ItemMaster;";1112// open connection13 mySqlConnection.Open();1415// execute query1617int newPO1 = (int) mySqlCommand.ExecuteScalar();18int newPO = newPO1 + 1;1920// close connection21 mySqlConnection.Close();2223//Response.Write(newPO);24 Session["newPO"] = newPO.ToString();2526 }

I copied and modified the ExecuteScalar() command from another thread in another fourm, but continue to receive this error:

System.InvalidCastException: Specified cast is not valid.

Source Error:

Line 30: // execute queryLine 31:Line 32: int newPO1 = (int) mySqlCommand.ExecuteScalar();Line 33: int newPO = newPO1 + 1;Line 34:

I'm not sure what i'm doing wrong, any help to point me in the right direction would be greatly appreciated.

Thank you in advance

Instead of using (int), try Convert.ToInt32

Convert.ToInt32(mySqlCommand.ExecuteScalar());

|||

That did the trick, only thing is my logic must be incorrect somewhere. For each line item I add its adding another digit to the PO number. What I need it to do is create a new PO number and stick with that one throughout the session...but then when the user finishes the PO and wants to start a new one thats when it adds 1 to the max PO number. Any Ideas? I thought putting the code in the Protected void Page_Load would do the trick but apparently not.

|||

Hi GIZM,

I have some code here..

If u find this is useful mark it as answer

qlConnection con=new SqlConnection();
con.ConnectionString=System.Configuration.ConfigurationManager.AppSettings["strcon"];
con.Open();
SqlCommand cmd = new SqlCommand("select max(fdcatid) from maincate", con);

//SqlDataAdapter ad = new SqlDataAdapter(cmd);
int f0 = (int)cmd.ExecuteScalar();
int f1 = f0 + 1;
con.Close();
Session["f1"] = f1.ToString() ;
Response.Write(f0);

|||

Hey Raja,

That looks to be a lot like what I have in my code, I just cant figure out where to put it. As of right now, I have it on page load, and then later in the page I have a insert statement to add lines to the data table. Every time a user would update a line item I guess the page is reloading causing the PONumber to increase. I only want the number to increase after the page is redirected to my confirmation page and then back, or if the browser is closed and reopened.

|||

I wonder if I could pull this off using the "is post back" thing?

Creating a diagram with existing tables

I'd like to create a diagram for my existing tables without wiping them out
in the process. I rightclick & select New Diagram, place the various
tables, create relationships, then save. I get a dialog box asking if I
want to create these tables. No! They have data in them. Unfortunately I
don't see a way to save the relationships and the diagram without blowing
everything away.
Jeremy
The diagram utility operates directly on the underlying tables so if you
only want to add the relationships for documentation or viewing purposes and
not have them permanently saved to the database, then you will need to use
another tool.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Brian, thanks. The diagram tool is asking about saving tables, not the
relationships. I can understanding the need to save the relationship, but
don't see why it wants to save my already-existing tables and potentially
blow away the data.
Jeremy
"Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
> The diagram utility operates directly on the underlying tables so if you
> only want to add the relationships for documentation or viewing purposes
> and not have them permanently saved to the database, then you will need to
> use another tool.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "JeremyGrand" <jeremy@.ninprodata.com> wrote in message
> news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
>
|||I agree that the messaging in the dialog could certainly be improved however
no matter what changes you make, it will always say that it is saving the
tables. Unless you changed the table structure itself, it should only apply
the relationship constraints via ALTER TABLE syntax and not recreate your
tables. If you want to see exactly how your changes are going to be
applied, there is an option to "Save Change Script" which will generate a
SQL script with the code it plans to apply.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:uUygVYEnFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Brian, thanks. The diagram tool is asking about saving tables, not the
> relationships. I can understanding the need to save the relationship, but
> don't see why it wants to save my already-existing tables and potentially
> blow away the data.
> Jeremy
> "Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
> news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
>
|||By the way, DataAnalyst saves relationship discovered in its own access
file, so it won't touch your original table at all.
Download it at http://www.agileinfollc.com
Eric
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Hi Jeremy
You might want to look at this product
http://www.ag-software.com/?tabid=17
It is a diagram tool and database compare tool. All details are saved
outside of SQL Server
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>

Creating a Database for deployment in SQL Server

I am in the process of designing a Windows Forms 2.0 app, and need advice on the recommended deployment/creation of the database for my customers.

Should i use database creation scripts as I have previously used on other apps, or is there a more recognised modern way of creating the DB. I have heard mention of "Sql Server Management Studio" possibly being useful here. If so, will there be issues if one of the customers buying my application uses Oracle?

Any advice appreciated.

Cheers,

StewManagement Studio certainly won't work with Oracle. If your main objective is portability, I suppose scripts is still your best bet.

Creating a Database for deployment in SQL Server

I am in the process of designing a Windows Forms 2.0 app, and need advice on the recommended deployment/creation of the database for my customers.

Should i use database creation scripts as I have previously used on other apps, or is there a more recognised modern way of creating the DB. I have heard mention of "Sql Server Management Studio" possibly being useful here. If so, will there be issues if one of the customers buying my application uses Oracle?

Any advice appreciated.

Cheers,

StewManagement Studio certainly won't work with Oracle. If your main objective is portability, I suppose scripts is still your best bet.

creating a CSV file

Can anybody run me through the process to create a CSV file from an SQLexpress database using BCP.exe ?

Or is there a simpler way to create a CSV for that matter?

Really important and would be much appreciated.

Have a look at

http://www.codeproject.com/aspnet/ImportExportCSV.asp A Simplified SQL-CSV Import/Export Functionality

Creating a Connection to Database that Does Not Yet Exist

Hello All!
I am in the process of developing a DTS package. My
package will behave as follows:
1. Backup my live database.
2. Using the backup from Step 1., the backup will be
restored using a DIFFERENT name (NewDatabase).
3. Manipulate NewDatabase database.
How can I create a connection in my DTS Package when, at
the time I am creating the package, the NewDatabase
database does not exist? Do I have to use Disconnected
Edits? If so, how? Or, do I use Microsoft Data Link?
If so, how? Or, do I use something else. (As you can
tell from my questions, I am not an expert at this :-)
Any suggestions are greatly appreciated!!You should be able to connect to the master database to do what you need to
do initially.
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
> Hello All!
> I am in the process of developing a DTS package. My
> package will behave as follows:
> 1. Backup my live database.
> 2. Using the backup from Step 1., the backup will be
> restored using a DIFFERENT name (NewDatabase).
> 3. Manipulate NewDatabase database.
> How can I create a connection in my DTS Package when, at
> the time I am creating the package, the NewDatabase
> database does not exist? Do I have to use Disconnected
> Edits? If so, how? Or, do I use Microsoft Data Link?
> If so, how? Or, do I use something else. (As you can
> tell from my questions, I am not an expert at this :-)
> Any suggestions are greatly appreciated!!|||I forgot to mention that I do have an initial connection,
the one to the original database. However, I need my DTS
package to create a connection to the NewDatabase
database once the task of restoring with a new name is
complete.
Thanks in advance!

>--Original Message--
>You should be able to connect to the master database to
do what you need to
>do initially.
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"ERR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
at
>
>.
>|||To do what? If it's only to run a sql script you can use workflow and
connect to the master database and then do the "use yournewdb" command after
you have created it
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
> I forgot to mention that I do have an initial connection,
> the one to the original database. However, I need my DTS
> package to create a connection to the NewDatabase
> database once the task of restoring with a new name is
> complete.
> Thanks in advance!
>
> do what you need to
> message
> at|||what you need to do as per Ray's suggestion is to create the dts package and
set it to connect to master, then create a dynamic task properties and a
global variable <dbname> to change the value of catalog property in
Disconnect Edit DE at run time.
then when you execute the dts package need to execute with the /A switch to
pass the <db> variable which u proide at run time see example below
dtsrun /S <servername> /E /N<packagename> /A <db>:8=newdatabase
this way u can connect to a database at runtime.
--
Olu Adedeji
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eR5zJ8y8DHA.3880@.tk2msftngp13.phx.gbl...
> To do what? If it's only to run a sql script you can use workflow and
> connect to the master database and then do the "use yournewdb" command
after
> you have created it
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "ERR" <anonymous@.discussions.microsoft.com> wrote in message
> news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
>