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 windows athentication login

Hi all. Here is my problem. Computer A has sql server 2005 express installed on it together with sql management studio. Computer B has only sql management studio. Computers are members of a corporate domain and are running xp professional. After starting management studio on computer B the databese engin on computer A is visible in the 'select server to connect to' dialog under network servers. When i try to connect i get the message login failed - so i try to add a windows athentication login for the appropriate user on computer A. The problem is as follows: Under 'Create new login/Select user or group/location' no network location apear and i can not add a network user name. This seem to be a network connectivity issue. I have, however, adjusted network connectivity via surface area configuration and enabled tcp/ip and named pipes - the server is visible form another computer as i have prviously mentioned.

I am not sure how you go there in the GUI. Do you right-click the Security->Logins folder and choose New Login?

From T-SQL, you can just use

CREATE LOGIN domain\user FROM WINDOWS

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.

If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.

Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.

Many thanks.

In SQL Server Management Studio Express, when you login to the SQL Express Server, you'll see "Databases" as an item in the Object Explorer. If you expand that, you'll see all of your databases listed. Select the database you want and expand that, then you'll see Security as one of the items. Expand that one more time and you'll see Users. You can create a new user there, as well as right click on a user and select Properties to fine tune the permissions of that user.

You can also create global users (Logins) by going to the Security item at the top level list in the Object Explorer. Once you've created a Login, you can map it to certain users within a database.

Thanks< MJ

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.
If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.
Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.
Many thanks.
hi Andrew,
Andrew Chalk wrote:
> I have created a user in SQL Server Management Studio Express.
> However, whenever my ASP.NET application tries to connect I get the
> error Cannot open database "ABC" requested by the login. The login failed.
> Login failed for user 'Fred'.
> If I change my login string to Integrated Security=SSPI it works.
> Also, I was able to add a user successfully in SQL 2000 but
> SQLExpress is differnt.
> Can anyone tell me how to add a user and give him permissions for a
> particular database? Or refer me to a URL that does.
> Many thanks.
please verify your SQLExpress instance accepts standard SQL Server logins,
enabling mixed security.. your connection string changed to ...Integrated
Security=SSPI implies a trusted connection...
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
GRANT {database permission}
http://msdn2.microsoft.com/en-us/library/ms178569.aspx
GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea: Thanks you for your detailed reply.
I understand the concptual difference between Windows and SQL authentication
and thought I had done all the necessary steps, which is why I am puzzled.
As I mentioned, with SQL 2000 I used SQL Enterprise manager and have the
login working. I have tried to map the steps I took in SQL2000's SQL EM to
those in the new SQL Server Mgt. Studio Express.
1) I created a login under the server->security folder.
I did not give this Login any Roles. It is just a plain ol' user (public).
2) I went to the server instance name at the top and GRANTed "Connect SQL"
permission to the new Login just created;
3) I went to the database and granted "Connect" permission to the Login.
After these steps I get the error reported in my earlier post.
I should add that I am not an SQL Server expert and first looked at the SQL
Server Mgt. Studio Express BOL.They are absolutely useless.
Hope the above hels.
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> please verify your SQLExpress instance accepts standard SQL Server logins,
> enabling mixed security.. your connection string changed to ...Integrated
> Security=SSPI implies a trusted connection...
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
> made
> member of none, 1 or all of the fixed server roles, which include
> "sysadmin"
> role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||I don't understand the following stement:
> database access, but a (database) user must be mapped to the corresponding
> login..
what is the difference between a Login and a User?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> Andrea: Thanks you for your detailed reply.
> 1) I created a login under the server->security folder.
> I did not give this Login any Roles. It is just a plain ol' user
> (public).
it's not a user, it's a login and there's no "public" server role... it's
just a login which is not member of server roles...

> 2) I went to the server instance name at the top and GRANTed "Connect
> SQL" permission to the new Login just created;
yep... the login has been created, enabled, and granted access to the
instance..

> 3) I went to the database and granted "Connect" permission to the
> Login.
ok, you granted db access to the login, which is now member of th public
database role..

> After these steps I get the error reported in my earlier post.
I reproduced your steps and I can log on to SQLExpress as a standard SQL
Server Login, and access a db the login has been mapped to a corresponding
user.. I did it via SSMSExpress and not via ASP..

> I should add that I am not an SQL Server expert and first looked at
> the SQL Server Mgt. Studio Express BOL.They are absolutely useless.
:D
download the full BOL, december released..

> what is the difference between a Login and a User?
a Login is the very first part of the 2 phases security policy enforcement
of SQL Server... I repeat what I already wrote..
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created in order to
grant access to the SQL Server instance... at the server level a login can
be made member of none, 1 or all of the fixed server roles, which include
"sysadmin" role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires credential
becouse it's directly provided by Windows via the logins' ID (sid), which
authenticate (Windows) user's login at the windows login step... SQL Server
only needs to verify that the corresponding login and/or group is granted to
log on the instance (checking the granted Login's existence in the SQL
Server instance)...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
The phrase:
> the login has been mapped to a corresponding user..
Can you explain that.
How did it happen?
Who is the user?
Is it correct to say that the user is an instance of utilizing a login? I.e.
"users" have logins. Some may use the same logins as others (for example
several instances of an application). In which case each application trying
to use SQL server is a user and their name/password pair correspond to their
login?
Thanks.
|||I now have a role named 'fred' and a user named 'fred' of the database that
I want the application to access. Is the use of the same name the problem?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||OK, fixed it, though mainly through luck than knowhow.
Deleted user "fred" and login "fred".
Recreated login Fred but made sure that I went to 'User Mapping' and checked
the database that I wanted this Login to be able to access;
Login was created and, like magic, a user appeared, also named 'fred', under
the database.
I still don't understand this.
Nonetheless, thanks for your help,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> The phrase:
> Can you explain that.
> How did it happen?
> Who is the user?
perhaps my english is not as good as I thought.. :D
perhaps here,
http://msdn.microsoft.com/library/de...rity_4fol.asp,
the architectural design is more clear..
a SQL Server Login (both standard SQL Server login or a WinNT login) is just
the very first brick in the wall in order to access a SQL Server instance...
without that you can not log in and connect to a specified instance... it
has a server wide range...
a User is just a database user (can have the same name as the corresponding
Login [Login is now somehaw obsolete, to be replaced by principals] but this
is not mandatory even if it's recommended in order to minimize troubles :D)
that, at creation time, is mapped via it's sid column
(dbname.sys.sysusers.sid) to an existing login
(master.sys.server_principals.sid)
SELECT u.name AS [Name in DB], u.hasdbaccess ,
p.name AS [Login Name]
FROM master.sys.server_principals p JOIN sys.sysusers u
ON p.sid = u.sid
Principals
http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
DB Users
http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx

> Is it correct to say that the user is an instance of utilizing a
> login? I.e. "users" have logins.
Logins can be granted db access as database users, but they can even not..

>Some may use the same logins as
> others (for example several instances of an application). In which
> case each application trying to use SQL server is a user and their
> name/password pair correspond to their login?
an application is not a user or a login... in certain case an application
role can represent this scenario
(http://msdn2.microsoft.com/en-us/library/ms190998.aspx)

> Deleted user "fred" and login "fred".
> Recreated login Fred but made sure that I went to 'User Mapping' and
> checked the database that I wanted this Login to be able to access;
> Login was created and, like magic, a user appeared, also named
> 'fred', under the database.
it's correct... you created a login... then you granted that login db access
via a database user (in the defined database) mapping it to the original
login ...
if you only create a login without grating him/her access to your desired
database(s), that login will only access it if the predefined "guest"
database user is available (and usually that db user is not available for
security reason)...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That URL does not use the work 'Login', only 'User'.
What is the difference between a login and a user?
Thanks,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40dk3lF198jigU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> perhaps my english is not as good as I thought.. :D
> perhaps here,
> http://msdn.microsoft.com/library/de...rity_4fol.asp,
> the architectural design is more clear..
> a SQL Server Login (both standard SQL Server login or a WinNT login) is
> just the very first brick in the wall in order to access a SQL Server
> instance... without that you can not log in and connect to a specified
> instance... it has a server wide range...
> a User is just a database user (can have the same name as the
> corresponding Login [Login is now somehaw obsolete, to be replaced by
> principals] but this is not mandatory even if it's recommended in order to
> minimize troubles :D) that, at creation time, is mapped via it's sid
> column (dbname.sys.sysusers.sid) to an existing login
> (master.sys.server_principals.sid)
> SELECT u.name AS [Name in DB], u.hasdbaccess ,
> p.name AS [Login Name]
> FROM master.sys.server_principals p JOIN sys.sysusers u
> ON p.sid = u.sid
> Principals
> http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
> DB Users
> http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx
>
> Logins can be granted db access as database users, but they can even not..
>
> an application is not a user or a login... in certain case an application
> role can represent this scenario
> (http://msdn2.microsoft.com/en-us/library/ms190998.aspx)
>
> it's correct... you created a login... then you granted that login db
> access via a database user (in the defined database) mapping it to the
> original login ...
> if you only create a login without grating him/her access to your desired
> database(s), that login will only access it if the predefined "guest"
> database user is available (and usually that db user is not available for
> security reason)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

creating a table dynamically in SQL Server Express with asp.net and vb

Hi,

Is there a way to dynamically create a new table in sql server express using the code behind with vb on a Page_Load event?

Thanks Matt

sure you can. Create a command object, sent the text as

CREATE

TABLE [dbo].[userdata](

[userID] [int]

IDENTITY(1,1)NOTNULL,

[varchar]

(50)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[name] [varchar]

(50)COLLATE SQL_Latin1_General_CP1_CI_ASNULL

)

ON [PRIMARY]

execute the command you will get your table in the database,

Hope this help.

|||

Thank You. That works great. It is exactly what I was looking for.

Matt

Creating a row guid in SQL Express

I am an asp developer who is finally serious about learning .net. I have downloaded SQL Express and am working through the ADO Step by Step book. I can easily create a table and set all the columns. What I cannot do is create a row guid. The option for this is grayed out in the column properties window and when I try to set a field in the "Row Guid Column" in the right side properties window I get an error mesage stating the field must match an entry in the list.

I have set the id field to primary key and no nulls and to identity and I still cannot set as row guid. I even downloaded Northwind and these tables also do not have a rowguid and I cannot set.

I noticed this as I tried to create a data adapter. I was able to create the Select and Insert statements but the update and delete failed I think due to the rowguid issue. Any help is appreciated Thanks Brad

Hi Brad...in order to set the rowguid column attribute, the column must be of the datatype 'uniqueidentifier'...once you have a column of that datatype, you can then assign the column the rowguid attribute. Note that you can only have a single rowguid column in a single table, though you can have multiple columns of the datatype uniqueidentifier.

HTH

|||Thanks Chad I have been working with MySql the last 3 years and was not used to the uniqueidentifier column. I now know and have fixed my db.

Creating a Report Model

I am primarily a web developer as of late utilizing Visual Web Developer 2005 Express with SQL 2005. I'd like to generate some reports from the data stored in SQL and like the options that the Reporting Services provides. However, I need to create a report model which I understand requires the full Visual Studio product.

Do, or will any of the Express editions of Visual Studio 2005 allow me to create a report model? I really don't have a need for Visual Studio 2005 - except it appears, to create a report model.

Thanks for any insight!

Visual Studio 2005 is not required. Model Designer is part of SQL Server 2005.

Creating a Report Model

I am primarily a web developer as of late utilizing Visual Web Developer 2005 Express with SQL 2005. I'd like to generate some reports from the data stored in SQL and like the options that the Reporting Services provides. However, I need to create a report model which I understand requires the full Visual Studio product.

Do, or will any of the Express editions of Visual Studio 2005 allow me to create a report model? I really don't have a need for Visual Studio 2005 - except it appears, to create a report model.

Thanks for any insight!

Visual Studio 2005 is not required. Model Designer is part of SQL Server 2005.

Creating a report in Landscape Mode

I am trying to create a report in landscape mode. I am using VWD 2005 express with the SQLServer2005_ReportAddin. My report apears to be in landscape mode on the screen, but when I export it to pdf it is not in landscape. I have my pagesize set to 11in wide and 8.5in high. and body set to 9x6.5 with 1in margin all around. any help would be appreciated.

Hi,

From your description, it seems that you are not able to export the right landscape mode into a PDF format, right?

Generally, it is the width which causes the format issue. Before exporting, you should control the report width which is on the Report Properties Tab.

There are properties which brings you to the Landscape Printing option. To set your report toalways print / export in Landscape mode, you need to go into the report properties, select the Layout tab and inverse the Width and Height

ie. The normal settings are 20cm for Page Width and 30cm for Page Height.

Thanks.

Creating a new named instance

As understand it, the only way to add another SQL Express 2005 named instance is to run SQLEXPR32.EXE again.However, when you to, right at the end, you get the following error message:

"An installation package for the product MS SQLK Server VSS Writer cannot be found.Try the installation again using a valid copy of the installation package 'SQLWriter.msi'"

So, I deleted all named instances, and the repeated the procedure and did not get this error and it installed fine. Obviously, I would not be able to delete all instances, but in this case it didn't matter.

So, here are my questions:

Question 1) Am I correct you must rerun SQLEXPR32.EXE to create a new named instance? (I think the answer is yes)

Question 2) Is there a way to rename an existing named instance?(I think the answer is no)

Question 3) The install/uninstall for SQLEXPR32.EXE is incredibly buggy. I have spend days trying to remove an named instance with no succes. Once your remove all named instances from Control Pannel, you stll have not removed SQL Express 2005.How do you remove it?We are considering other avenues because of the incredible problems with this product. Also, we inadvertantly installed SQL Express 2005 Advanced Services, when we meant to install SQL Express 2005, so we would like to uninstall Advance Serviuces and reinstall SQL Express 2005

Question 4) I am switching from the Outlook Express newsgroups to these groups. In the context of these groups, what is the difference betwee a Forum and a Newsgroup?

Thanks

Bob

hi Bob,

BobInIndy wrote:

As understand it, the only way to add another SQL Express 2005 named instance is to run SQLEXPR32.EXE again. However, when you to, right at the end, you get the following error message:

"An installation package for the product MS SQLK Server VSS Writer cannot be found. Try the installation again using a valid copy of the installation package 'SQLWriter.msi'"

you can try unpacking the SQLExpress installer and manual install SQLWriter.msi and, eventually, SQL Native Client msi as well, as this is usually the one causing problems.. but your case does not indicates it..

So, I deleted all named instances, and the repeated the procedure and did not get this error and it installed fine. Obviously, I would not be able to delete all instances, but in this case it didn't matter.

So, here are my questions:

Question 1) Am I correct you must rerun SQLEXPR32.EXE to create a new named instance? (I think the answer is yes)

yes..

Question 2) Is there a way to rename an existing named instance? (I think the answer is no)

no

[quote]

Question 3) The install/uninstall for SQLEXPR32.EXE is incredibly buggy. I have spend days trying to remove an named instance with no succes. Once your remove all named instances from Control Pannel, you stll have not removed SQL Express 2005. How do you remove it? We are considering other avenues because of the incredible problems with this product. Also, we inadvertantly installed SQL Express 2005 Advanced Services, when we meant to install SQL Express 2005, so we would like to uninstall Advance Serviuces and reinstall SQL Express 2005

some times you have to manually remove phantoms accessing file system and registry, as described in here and/or here ..

Question 4) I am switching from the Outlook Express newsgroups to these groups. In the context of these groups, what is the difference betwee a Forum and a Newsgroup?

newsgroup are another community type, usually diffused and accessed via NNTP protocol, where forums have been designed for web access, usually with additional features like link sections, tips section and the like.. anyway this one should be consider a forum, AFAIK..

regards

creating a new instacnce

Hi I'm tyring to redistribute SQL Express edition with my product.

I'm using installShiald 12 to create my install using the using the Redistributable
wizard

I'm trying to install a new instance of of SQL Server that will be used only by my
product.

When i'm install the SQL server on a clean computer (without SQL Server) i have
no problem, but when i'm installing SQL server on a computer wich all ready have
SQL server installed on it I get a massage :

"Name: Microsoft SQL Server 2005 Express Edition (ISHAY)
Allowed Action: Maintain (Action is only available from Add or Remove Programs)
Reason: Cannot maintain product, to maintain the product go to Add or Remove
Programs".

If using the '/qn' flag I get an error code returned from SQL server install.

but the install did create a new instance by the name "ishay".

how can i prevent this from happening?

the commmand line u use is:

"UPGRADE=SQL_Engine INSTANCENAME=ishay
SQLBROWSERAUTOSTART=1 SQLACCOUNT="NT AUTHORITY\SYSTEM"
SKIPUPGRADEBLOCKERS=1".

thank ishay

The reason you are seeing this is because you are using the UPGRADE parameter on your command line, which attempts to upgrade an existing instance on the machine. Switch that to ADDLOCAL and you will then install a new instance (named ishay) on the machine regardless of whether there are existing SQL instances on the machine already.

Thanks,
Sam Lester (MSFT)

creating a diagram in sql server express

hi..

i have a pc that is on a domain with domain user rights and i installed a sql server express 2005..

when trying to create a new database am encountering an error:

create database permission denied in database 'master' (microsoft sql server error:262)

could anyone help how to go about this..am new to sql express.

thanks

So it seems that your are not logged in with a Windows account which has sufficient priviliedge "Create Database" to perform this action. If you have an account that is local administrator, try to login with this one and execute your DDL Actions.

HTH, Jens Suessmeyer.|||

what do you mean by an account that is local admin? an account outside the domain?

what if i still want to work within the domain? what probably the best sufficient priviledge that i could use? domain admin account?

pls. clarify

thanks

|||By default the local admin group is in in the group of the server administrators, so the domain admin are usally also members of the local admin group and therefore sa′s. YOu just have to login with a user with more permissions than the current one you are trying with.

HTH, Jens Suessmeyer.|||

btw, am using windows authentication mode and not sql authentication mode.. will that be ok...and also a check with my privileges the user am currently using is also a member of administrator built in group.. but not domain admin.

thanks

|||what am trying to achieve here is that.. clients pc can use sql server express without giving them full priveleges to access others pc's folders except for those folders that are shared..|||You could create a group on the local machine and add your user to that group. Then in SQL Server give that group the permissions you want.|||

am having this error when trying to create a diagram. (you are not logged on as database owner or system administrator. you might not be able to save changes to tables that you do not own in sql server express...certain edits require create table permission)

i would greatly appreaciate helping me how to give permissions.. if possible the most step by step guide pls.)

thanks

|||

Hi

THIS WORKED!!!!

Go to SQL Server manager >> Security >> Logins and find an account named "NT AUTHORITY\NETWORK SERVICE"

Open this and in the SERVER ROLE tab, give it DBCREATOR permissioin. Thats it. now you will be able to create the database.

Reason: When SharePoint administration website connects SQL database, it uses the account "NT AUTHORITY\NETWORK SERVICE". So this account must have DBCREATEOR permission

hope this helps

Regards,

Ash

|||

It took me a while to find the "SQL Server manager" mentioned in the post above this post.

Eventually I found out that Ash31 meant the "Microsoft SQL Server Management Studio". =)

Hopefully this will help people like me who can't find the SQL Server manager.

creating a database, tables, records, and fields in a Sql server 2005 express

I am at my wits end as to how to do this.
I have downloaded Sql server management Studio and tried to create a database but I can't figure it out. There is an almost nonexistant help file so I am lost as to how to start. I have succeeded in looking at some system databases but that is about the extent of it. Can someone show me the proper procedure? Am I using the wrong tool?
Thanks,
Paul

If you are using the managment studio, right click on the Databases node, --> New Database. If you are using a script tool for this, the fastest way to create a database is CREATE DATABASE <name>, which stores the data files in the defautl location, specified at setup time.

HTH, Jens Suessmeyer.

|||Thanks, Jen! boy was that ever easy! I've got tables and columns now too! Tell me though, can I input some data in the management Studio Also?
Thanks,
Paul
|||Well I think I spoke too soon. Now in VWD I drop a gridview and try to connect it to my database. When I am building a connection string, I get the dialog box, "Add Connection. When I try to browse and select my database I get this real long error, the essence of "Unable to open the phsical file c:\blah|blah\blah\prince.mdb. Operating system error 32(The process cannot access the file because it is being used by another process)" I know that it isn't being used because I closed the database and exited Management Studio.
Thanks,
Paul
|||

Make sure the database is AutoClose=true. If the database is not set to automatically close, the SQL Server service will keep it open as long as the server is running.

You can check this in the Options page of the database properties dialog in Management Studio.

Hope this helps,

Steve

|||Thanks, Steve. That was the problem. However, I still can't add data or insert blank records in the database. I noticed when I used an Access database I was able to edit and delete the data. I would like to be able to do that with Sql server express database also and in addition to inserting a blank record. I am using a a gridview component.

In Visual basic 6.0 I could drop a few buttons on the form and with next, previous delete and insert methods I could alter the database where the columns were bound to individule Textboxes.
I have a lot to learn and there are very few books and tutorials written with info on what I want to learn.

I guess the gridview tasks are limited because I have no data to edit or delete, and I don't know how to use the Management Studio to enter the data. :(
|||I tried executing a query like this:
INSERT INTO family VALUES ("Paul", "Handley"); family is my table and there are only two columns. I tried putting the column names but got an error. I also get this error when I try to run the above query:
Msg 128, Level 15, State 1, Line 1
The name "Paul" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Any suggestions?
Paul
|||

You can either set the following command:

SET QUOTED_IDENTIFIER is OFF

to use double quotation marks, or you use single quotation marks which wont cause the problem.

HTH, jens Suessmeyer.

|||Thanks, Jen. That did the trick. As far as the inserting and the editing of a database record within the web application I am developing, I still can't do that. Is it possible to attach a query to a button and just insert a blank record. Like this:
Insert into family Values( '','') **Those are single quotes**
Thanks,
Paul
|||Ok I've got to the point where when I am configuring the datasource and I get to the "Configure the select statement and I choose "Specify columns from a table or view. I click on te advanced tab and I want to choose"Generate Insert, Update and Delete Statements" but the whole dialog box is grayed out! What do I do now? I want the capabilities of editing, insertsing, deleteing and updating my records
Thanks,
Paul

|||Ok I found the where I could add the various links to Update Insert and edit my fields and when I click on New I get the blank fields to add new data but when I click on update I get this error msg:

Inserting is not supported by data source 'SqlDataSource1' unless InsertCommand is specified.

I feel sure If I could fix this I would be on my way, but the as I stated above the whe option are grayed out!

Will someone pleeese answer this post!

|||Ok, that's it. If you are going to just leave me hanging here, I'm through. I'm going back to Linux and PHP and Mysql. I should have know better than to try this Microsoft garbage!
Paul
|||

Hi Paul,

there is no need yelling in this group. As we are doing this all in our spare free time and on a voluntary basis, the poster of a question sometimes has to wait until he gets an answer. IMHO I would rather learn the basis things first, before comming to the groups and posting question which would have been solved if you just took a walkthough-easy-to-use-example.

The question you are posting is related that you appearantly didn′t specifiy which command has to be executed in the case of an insert. YOu can either specify your own command or use a commanbuilder (But that are , as I told below some basic things which wouldn′t concern you if you have done some ADO.NET basics first)

Fell free to come any time again in this group, but be aware that you sometime need to have a bit patience.

HTH; jens Suessmeyer.

|||p3aul,
your statement:
INSERT INTO family VALUES ("Paul", "Handley");
should be:
INSERT INTO family (fieldname, fieldname) VALUES ("Paul", "Handley");
where fieldname is the name of your field.
|||Well I wasn't aware that I was shouting, I just c & p the error that appeared on the webpage. i figured it would just paste as normal 10 or 12 pt type. I enlarged the fon't and chose the color red in my plea for help because Time was going by and I was beginning to lose my train of thought.. I don't sit idley by and wait for someone to give me an idea that might help, I try to persue a solution on my on. If I leave the a file i am working on and load something else in and put my mind ont I am apt to forget what I was doing on the first file. I apologise for feeling frustrated but Microsoft tools are frustraing things to work with.

tonic999 Thank you for the post. I don't remember where I was now. In working with MySQL , though, in the insert statement (fieldname, fieldname) are optional.

My original question, If I can find the file I was working on, still stands, to wit:
Ok I've got to the point where when I am configuring the datasource and I get to the "Configure the select statement and I choose "Specify columns from a table or view. I click on te advanced tab and I want to choose"Generate Insert, Update and Delete Statements" but the whole dialog box is grayed out! What do I do now? I want the capabilities of editing, insertsing, deleteing and updating my records
Thanks,
Paul

Creating a database backup

Hi,

I have my MSSQL database developed in Visual Web Developer 2005 Express Edition. I am trying to create a backup file of this databse (.bak). Is SQL Server 2005 Studio Express the program that will let me do this?

Also in SQL Server, how can I attach to my database that I have made in Web Developer 2005? if I go to attach it doesn't list anything.

Thanks for any help.

I have fixed the problem now.

creating a database

OK,

I have downloaded and installed 2005 express. All I want to do now is create a new database. I do not see where to do that. All that was installed was the SQL Server Configuration Manager and the Surface Area configuration. Am I missing something. I guess I am looking for something similar to Enterprise Manager. Where is it?

Yep, what you need is SQL Server Management Studio Express which is a version of SQL Server Management Studio specially tailored for SQL Server Express.

you can download it from http://msdn.microsoft.com/vstudio/express/sql/download/

|||

Newbie here...

I installed the SQL Server from the VWD. I would like to get the SSMSE, do I need to uninstall everything (i.e. VWD, SQL Server) before downloading SSMSE?

The other scenario:

What if I would like the Advanced Services instead, do I need to uninstall everything before downloading?

|||

You should be able to install SSMSE on top of your current installation without uninstalling SQL Express.

wrt Advanced Services, you should be able to install it to a different instance. Haven't tried though.

Cheers.

creating a database

OK,

I have downloaded and installed 2005 express. All I want to do now is create a new database. I do not see where to do that. All that was installed was the SQL Server Configuration Manager and the Surface Area configuration. Am I missing something. I guess I am looking for something similar to Enterprise Manager. Where is it?

Yep, what you need is SQL Server Management Studio Express which is a version of SQL Server Management Studio specially tailored for SQL Server Express.

you can download it from http://msdn.microsoft.com/vstudio/express/sql/download/

|||

Newbie here...

I installed the SQL Server from the VWD. I would like to get the SSMSE, do I need to uninstall everything (i.e. VWD, SQL Server) before downloading SSMSE?

The other scenario:

What if I would like the Advanced Services instead, do I need to uninstall everything before downloading?

|||

You should be able to install SSMSE on top of your current installation without uninstalling SQL Express.

wrt Advanced Services, you should be able to install it to a different instance. Haven't tried though.

Cheers.

Creating a .NET Stored Procedure in Sql Server 2005 Express Edition

Could somebody tell me how do we create a .NET Stored Procedure in Sql Server 2005 Express Edition and deploy and debug it against the database from Visual Studio 2005 or Visual Web Developer? Can some one also let me know which approach is faster among .NET stored procedure or T-SQL stored procedure?

Regards...

Shashi Kumar Nagulakonda.

I can't help you with the first question.

The answer to the second question is obviously it depends. As far as I know (and I know very little about .NET sps), given the same task, the T-SQL sp should execute faster. Now that doesn't preclude that yes, you can write a really bad T-SQL sp that performs worse than a well written .NET sp, but as far as I know the .NET sp's work on sets of data very similiarly to a cursor, and therefore will experience the same speed (or lack thereof). Not to mention the overhead of loading the .NET runtime into memory, and doing all the jitting. Then again, some things would be much easier in .NET because T-SQL isn't a full programming language, so the .NET sps have a much greater versitality.

Again, I have actually no experience with using, writing, or debugging .NET sp's. If someone more knowledgable wants to chime in, please do so.

Createing New Triggers in SQL Server Management Studio Express

Is it possible to create a new trigger for a SQL 2000 Database using SQL Server Management Studio Express?

hi,

yes...

connect to the desired instance.. select the required database and navigate to the "table" node.. expand the desired table/view object and select the Triggers node.. right click and write your trigger.. a template is presented as well..

or just open a new query window, select the desired database and type the trigger code..

regards

Created table in SQL SERVER EXPRESS but don't see it

I installed SQL SERVER 2005 Developer Edition over SQL SERVER EXPRESS in order to get the Management Studio.
I created a new Database called TRADING.
I then started a NEW QUERY and created a table and inserted 2 rows.
I then selected from the table and got 0 rows .
I then went to Trading database on the left side and clicked tables and did not see the table I created.
What am I doing wrong. SHould I remove SQL SERVER EXPRESS and then install SQL SERVER 2005 Developer Edition.

look in the master database. you might have created it there.

|||

As Karl suggested, you likely created the table in a database other than your new TRADING database.

In order to use a database, you have to be in the context of that database. You do this by using the USE statement. For example:

CREATE DATABASE Trading

GO

USE Trading

CREATE TABLE MyTable (C1 int, C2 varchar(50))

GO

INSERT INTO MyTable VALUES (1, 'Test')

See the topic 'USE (Transact-SQL)' in Books Online for more information.

You can also see the current database context when you're in the SQL editor by looking at the database listed in the Standard toolbar. Selecting another database from the dropdown list in the toolbar is the same as running a USE <databasename> statement.

Regards,

Gail

Created database at school but can't open it at home

Please help me.

I'm a college student working on a database project using MS SQL Server 2005 Express Edition.

The program (SQL SMSEE) is installed on both the computers in class and on two of my computers at home. The first installation resulted in the "remote connections" error. No matter what I do, I can't get the program to fully load. So I tried installing on another computer at home. The second installation went well. Didn't do anything different from the first installation, but any hoo....................

My 2-week old problem is this - I save my database that I do in class on my thumbdrive. Last class, I saved all the files that had my database's name on it on my thumbdrive--.mdf, .log, .bak, etc. On the second home computer, I cannot open the databases that I work on at school. Even my professor is stumped on this one.

Here is what I'm doing......

After I connect to the server, I right-click "Databases" then left-click "Restore Database".

In the "To database" box, I enter the name of the database, as I saved it at school.

In the "To a point in time" box, I leave the default "Most recent possible" entry.

I select "From device" as the location of backup and click the "..." button. The file is on my thumbdrive, so I click "Add" and select the location on my usb (with the .bak extension) and click OK.

I pick the most recent file checkbox and click "OK".

The green progress circle goes to 50% and then gets stuck. The database never opens and the Object Explorer shows the database name followed by "(Restoring...)". So if I try to do anything else with it, I get an error message stating the database in the middle of a restore and that I have to wait until its done. Well, of course it never finishes.

Please help me. Right now I am stuck doing duplicate work at home and at school and am making little to no progress. The final project is due on 26 March and right now I only have my tables, a few attributes and a couple of relationships. And I have a LOT more work to do.

Thanks.

i have more luck using the attach task than with restore --

not sure why -- but sounds like you have a need for speed so you might want to try using the attach task.

When you copied the files to your thumb drive was this a database backup or just a copy?

if its just a copy did you stop the sql engine before you made the copy (this is a "best practice")

then copy the .mdf and .ldf files

try using the attach task in smsee instead of restoring

1. copy the .mdf and .ldf files from your thumb drive to your computer.

2. open smsee and connect to a server --probably <yourcomputername>\SQLEXPRESS

3. in the object browser:

select the database node and right click

select the attach task on the context menu

4. an ATTACH DATABASE dialog will open -- click the add button

5. a LOCATE FILE... dialog will open -- drill down to the database.mdf file location and select it

and click OK on the LOCATE FILE dialog

6. the database details should be in the bottom of the ATTACH DATABASE dialog -- click OK

you should now have a node under the databases node for your attached database.

good luck

|||

I LOVE YOU!!!!!!!!!!!!

(in context, of course )

You have just saved me from days of mental anguish. I'm telling you, I only had a few strands of hair left.

Now I don't have to do double work any more and I actually have a shot at completing this project on time.

Thank you so VERY much. I wish I would have visited this forum sooner.

Gotta go.........plenty work to be done!

.........did I say THANK YOU?