Creating an application on SQL server 2005 Express that will be migrated to SQL Server

I have several general questions about this but I thought I would describe what I am doing first. My senior project team is developing a web application that will wind up using SQL Server for the database. We are writing it in ASP.net and developing the database on SQL Server Express. My job is to develop and deploy the database. I have an ERD designed and am getting ready to start coding the tables, constraints and stored proceedures but am unsure of a couple of things.

1) How do I create a new schema?

2) I can see how to create tables in the GUI but what I am trying to do is create scripts that can be used to create the tables and objects on the actual server that our client will be using. How do I create scripts in 2005 Express? Do I just go click on the new query button?

3) Are scripts that work for Express going to have any problem executing on SQL Server?

Asp.net 2.0 comes what application services database built for you by the Asp.net team at Microsoft it uses the DBO schema, that is the Asp.net runtime uses the DBO schema. It helps you create users with a few lines of code, you have the option to use that database separately or add those tables to your database. Go to the location below in your hard drive and use the aspnet_regsql utility to create the database, then add connection string and you can add your users with the Web site admin tool. Try the links below for code sample and how to videos, the second link first section video 9 and 7 and 11 in the second section. Hope this helps.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

http://www.asp.net/learn/videos/

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 new database beside 1 that currently exists

I am developing an application that will use msde or SQL sevrer depending on
if they are in the office or workign on the road. If they are on the road
they currently have MSDE installed by a different program. I also want to
use the msde service to run my database.
is it possible to run two databases on the same PC?
if it is do I need to know the sa password for msde? if i do is there a way
around it, I doubt that the company whos program runs on msde would want me
knowing the sa password
I have my database scripted in 3 sql files and I know that I will have to
use oSQL to execute them
If any one can point me how to do it or provide sample code I would be very
grateful
cheers
Comments inline.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"steven scaife" <stevenscaife@.discussions.microsoft.com> schrieb im
Newsbeitrag news:399E4B40-6312-423F-99B1-D3566EA9BEA6@.microsoft.com...
>I am developing an application that will use msde or SQL sevrer depending
>on
> if they are in the office or workign on the road. If they are on the road
> they currently have MSDE installed by a different program. I also want to
> use the msde service to run my database.
> is it possible to run two databases on the same PC?
Yeah, you are only stuck in to one instance.
> if it is do I need to know the sa password for msde? if i do is there a
> way
> around it, I doubt that the company whos program runs on msde would want
> me
> knowing the sa password
if you got windows auth. activated you can easily log on to the msde
with an administrive account, they are usally members of the system
administrator group, if they disabled it, you can reenable that via:
HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
MicrosoftSQLServer\<instance_nXame>\MSSQLServer\Lo ginMode
auf "1" (Windows Auth)
http://www.microsoft.com/sql/tXechin...tion/MaXy3.asp

> I have my database scripted in 3 sql files and I know that I will have to
> use oSQL to execute them
> If any one can point me how to do it or provide sample code I would be
> very
> grateful
You can call external scripts via the switch -i
OSQL -iC:\Test.sql

> cheers
|||thanks for the reply
Am I right in assuming that I just re-run the msde2000 setup but name an
instance
eg. C:\MSDERelA\setup.exe sapwd="<password>" INSTANCENAME="Kaisen"
TARGETDIR="C:\Program files\KaisenDB\"
then I can play around with it using my SA password and its totally seperate
but just using the sql service
sorry if i sound dumb but I dont have much experience with administering
msde, just running from the bits i picked up
"Jens Sü?meyer" wrote:

> Comments inline.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "steven scaife" <stevenscaife@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:399E4B40-6312-423F-99B1-D3566EA9BEA6@.microsoft.com...
> Yeah, you are only stuck in to one instance.
> if you got windows auth. activated you can easily log on to the msde
> with an administrive account, they are usally members of the system
> administrator group, if they disabled it, you can reenable that via:
> HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
> MicrosoftSQLServer\<instance_nXame>\MSSQLServer\L oginMode
> auf "1" (Windows Auth)
> http://www.microsoft.com/sql/tXechi...ion/MaXy3.asp
>
> You can call external scripts via the switch -i
> OSQL -iC:\Test.sql
>
>
|||hi Jens,
Jens Smeyer wrote:
> if you got windows auth. activated you can easily log on to the
> msde with an administrive account, they are usally members of the
> system administrator group, if they disabled it, you can reenable
> that via:
> HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
> MicrosoftSQLServer\<instance_nXame>\MSSQLServer\Lo ginMode
> auf "1" (Windows Auth)
Windows authentication can not be disabled even in the value of "0" is
reported as SQLDMOSecurity_Normal (Allow SQL Server Authentication only)..
anyway, you'd require administrative WinNT privileges to modify that
registry settings...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Steven,
steven scaife wrote:
> thanks for the reply
> Am I right in assuming that I just re-run the msde2000 setup but name
> an instance
> eg. C:\MSDERelA\setup.exe sapwd="<password>" INSTANCENAME="Kaisen"
> TARGETDIR="C:\Program files\KaisenDB\"
> then I can play around with it using my SA password and its totally
> seperate but just using the sql service
MSDE installs by default disable standard SQL Server connection and only
allowing trusted ones...
to modify this behaviour at install time you have to provide the
SECURITYMODE=SQL paramenter to the setup.exe boostrap installer... or,
after install, as already discussed, you can modifiy a registry key..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

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...
>