Creating an MS SQL data table programatically

I use Visual Studio and VB.
If I want to create an SQL data table on the server, I go to ServerExplorer and make a small table with the corerct columns. Then I addrows programatically as needed from the Application I'm working with.
It would suit me, for a current job, to be able to create the table itself programatically.
I don't know how to do this. Can it be done ? If so, could someone giveme a starter, please ? Four cols with a key in the first.
David Morley
SQL understands both Data Manipulation Language (DML) and Data Definition Language (DDL). The DML is the stuff you use to change the values in the table whereas the DDL allows you to change the structure of your database. Look up 'CREATE TABLE' and start there. PS, I'd avoid ADOX and the like unless you're making a cross database product...even then I'd probably stay clear.

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 stored procedure in SQL Server Management Studio

I have a database that I want to add stored procedures but when I go to save the stored procedure it asked me to save it as a .sql file and doesn;t add it to the database.

This is what I'm doing:

right click on database > programability > stored procedures

select "new Stored Procedure"

writting the procedure

then save - this is when i get the save dialogue rather than seeing the sp added to the database.

There must be something very simple that I'm doing wrong but I just can't figure it out, any help would be greatly appreciated.

Damien

You need to execute this command (that begins with CREATE PROCEDURE) instead of saving to disk.

So, you need to press F5 or press the Execute Button from program bar.

|||Thanks

Creating a SQL Database File in a Specific Location

In SQL Server 9.0 (Standard Edition), I want to create a database from within
the Management Studio. But when I create it, I don't seem to have any control
of WHERE the actual database files are placed. I *must* be able to control
that somewhere, right?
Alex
Hi Alex
I presume you are using the New Database Dialog in Management Studio. In the
box where you supply the db name, you also supply all the details about each
of the files, including their physical location. When you scroll to the
right, you can see the text boxes for the path, and there is a button to
click to allow you to browse and choose a different path than the default.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex
|||Alex
Yes you can specify the database file location
General-> under Database file(move the scroll bar right) section
VT
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex
|||In SQL Server Management Studio on the General page for creating a new
database you can expand the window or scroll to the right to see the Path
field. This is where you change the default location of the database files.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex
|||Yeah, OOPS. Just found that Thanks.
"Kalen Delaney" wrote:

> Hi Alex
> I presume you are using the New Database Dialog in Management Studio. In the
> box where you supply the db name, you also supply all the details about each
> of the files, including their physical location. When you scroll to the
> right, you can see the text boxes for the path, and there is a button to
> click to allow you to browse and choose a different path than the default.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
>
>

Creating a SQL Database File in a Specific Location

In SQL Server 9.0 (Standard Edition), I want to create a database from withi
n
the Management Studio. But when I create it, I don't seem to have any contro
l
of WHERE the actual database files are placed. I *must* be able to control
that somewhere, right?
AlexHi Alex
I presume you are using the New Database Dialog in Management Studio. In the
box where you supply the db name, you also supply all the details about each
of the files, including their physical location. When you scroll to the
right, you can see the text boxes for the path, and there is a button to
click to allow you to browse and choose a different path than the default.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex|||Alex
Yes you can specify the database file location
General-> under Database file(move the scroll bar right) section
VT
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex|||In SQL Server Management Studio on the General page for creating a new
database you can expand the window or scroll to the right to see the Path
field. This is where you change the default location of the database files.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex|||Yeah, OOPS. Just found that Thanks.
"Kalen Delaney" wrote:

> Hi Alex
> I presume you are using the New Database Dialog in Management Studio. In t
he
> box where you supply the db name, you also supply all the details about ea
ch
> of the files, including their physical location. When you scroll to the
> right, you can see the text boxes for the path, and there is a button to
> click to allow you to browse and choose a different path than the default.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
>
>

Creating a report model against an SSAS Cube in Visual Studio

I'm trying to create a report model in visual studio. I need the model
to report against an exiting cube. I can easily make the model in SQL
Server management studio. When I try to do the same in Visual Studio I
run up against a problem when I try to create the datasource view. The
steps I'm taking:
- I add a new Report Model Project into the solution that contains my
Analysis Services Project.
- I add a new datasource to the Report Model Project choosing to create
the source on an object and choosing the existing Analysis Services
Project.
- If I try to add a datasource view, the datasource I created doesn't
show up. (I've read in Microsoft documentation that this is designed
functionality)
- I can't find a way to create a report model directly against the
datasource and of course there is no datasource view. It would seem
that I should be able to create a report model directly against the
datasource since that's how both SQL Server Management Studio and the
reporting web interface works but I can't figure it out.
Can someone help me?I'm not sure I really understood what do you want to achieve
The way to create a model with Analysis Services data source is to :
a) open your Reporting Seervices with Mangement Studio
b) Choose your Analysis Services data source
c) Right click and choose "Generate a model..." from the contextual menu
Med Bouchenafa
"krosschell@.rdacorp.com" <talldrink@.gmail.com> a écrit dans le message de
news: 1138217467.115673.224510@.z14g2000cwz.googlegroups.com...
> I'm trying to create a report model in visual studio. I need the model
> to report against an exiting cube. I can easily make the model in SQL
> Server management studio. When I try to do the same in Visual Studio I
> run up against a problem when I try to create the datasource view. The
> steps I'm taking:
> - I add a new Report Model Project into the solution that contains my
> Analysis Services Project.
> - I add a new datasource to the Report Model Project choosing to create
> the source on an object and choosing the existing Analysis Services
> Project.
> - If I try to add a datasource view, the datasource I created doesn't
> show up. (I've read in Microsoft documentation that this is designed
> functionality)
> - I can't find a way to create a report model directly against the
> datasource and of course there is no datasource view. It would seem
> that I should be able to create a report model directly against the
> datasource since that's how both SQL Server Management Studio and the
> reporting web interface works but I can't figure it out.
> Can someone help me?
>|||When you say "Management Studio" I believe you are refering to SQL
Server Management Studio. I agree, creating a reporting model from
there is easy as cake. I'm trying to create a Reporting model from
Business Inteligence Management Studio. There is a project type "Report
Model Project" and I want to create a report model in this environemnt.
(one that uses a cube as it's data source). Does that make more sense?

Creating a project from an existing Database

I have the full blown Microsoft SQL Server Management Studio (MSSMS) installed on my workstation.

We have a number of existing databases that I'd like to manage with MSSMS and put into source control.

How do I get MSSMS to "import" or "Convert" an existing SQL server 2000 database into a project that I can manage with MSSMS? We have not used Source safe up to this point, but would like to start doing so now.

This seems like it ought to be explained well up front in any discussion of converting from SQL 2000 to SQL 2005 or installing 2005, but I can't find ANYTHING useful in the BOL or other help.

Thanks for any help you can give me.

-Rob Marmion

All what you need is to script your code (and other objects, if you'd like) into separate files and add them to the SSMS project (simply drag'n'dropping). The problem is that SSMS is not able to script your database objects into separate files, only into one, but you can use Enterprise Manager "Generate SQL Script" feature.

Creating a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005

Hello, How are you?
I have a problem when I try to create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE. When I’m going to choose the data source, the SQL Server 2005 Compact Edition provider doesn’t appear in the list. I installed the SQL Server 2005 Compact Edition from this page http://www.microsoft.com/downloads/details.aspx?FamilyId=%2085E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en. In the additional information says by installing SQLServerCE31-EN.msi installs the provider (System.Data.SqlServerCe.dll) in the GAC (global assembly cache) and registers the OLEDB provider (sqlceoledb30.dll). So, I don’t understand why couldn’t I create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE?

I appreciate your help…

I believe you need to be on Visual Studio 2005 SP 1 for the Data Source name to show up as 'Microsoft SQL Server 2005 Compact Edition' in the Change Data Source dialog in Visual Studio. I just checked and mine does show the new provider. My guess is that if you aren't on SP1, you'll see SQL Mobile in the list but the provider that gets referenced in your project will be the updated provider.

Darren

|||

I had the exact same problem and wanted to share the solution with you.

1) Uninstall SQL Server 2005 Compact Edition (SSCE)

2) Uninstall SSCE SDK

3) Uninsall SSCE SP1 Tools

4) Run setup from your Visual Studio 2005 CD

5) Click Change or remove

6) Add or Remove Features

7) Uncheck Smart Device Programability

8) Click Update

9) Repeat steps 4-8 except this time Check Smart Device Programability

10) Install SQL Server 2005 Compact Edition (SSCE)

11) Install SSCE SDK

12) Install SSCE SP1 Tools

It should now work. This is the only way I could get it to show up. I hope this helps.

|||

Took me a long time to find this post, your solution solved my problem. I did not have to do steps 4-8 twice. The first time I went to Add or Remove Features the Smart Device programability was not checked, so I checked it and clicked update. After update was complete I installed the SQL Server 2005 Compact Edition (SSCE) and it now appears as a choice for a Data Source when adding a new connection.

Thanks!!!!!!!!!!!!!!!!

|||

Yes!!! It worked out for me too. Thanks a lot dear Winblazer! I spent a day fighting with Microsft, I mean this issue. I am wondering if Bill Gates ever tried to install any of MS development tool and create an application just to display "Hello World!". Just for fun. I am sure he would have a lot of fun.

Thank again.

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?
In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?
|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
> > Is there a way to create a login and password, or change a login's password
> > in management studio (sql 2005) without using a sql script? Any wizard or
> > dialog box?|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:
> > In the Management Studio, open the Object Explorer and expand your Server,
> > then Security, then Logins and right-click on the login you want to change. A
> > dialog box will open and you can change the password in there, near the top.
> > Type in the password in the Password box, and the same password again in the
> > Confirm Password box.
> >
> >
> > --
> > AndyP,
> > Sr. Database Administrator,
> > MCDBA 2003
> >
> >
> > "mkiger" wrote:
> >
> > > Is there a way to create a login and password, or change a login's password
> > > in management studio (sql 2005) without using a sql script? Any wizard or
> > > dialog box?

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change.
A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's passwor
d
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change
. A
> dialog box will open and you can change the password in there, near the to
p.
> Type in the password in the Password box, and the same password again in t
he
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
>|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way
.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have
a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:
>

Creating a drill-through report

I'm building my report using the Report Designer in the SQL Server Business Intelligence Development Studio. At the moment I've got a single report which consists of 2 tables. The top table shows some summary data and the bottom table shows more detail.

What I'd like to do is design the report so that when the summary data (in the top table) appears, the user can click on a row in the table and this takes them to a new screen which shows more detail about that item, extracted from the bottom table.

Is this possible? I know it's possible to create a drill-through report if you have the high-level summary data in one report and the low-level detailed data in a separate report, but is it possible to create a drill-through report if all the data is in one single report (albeit in 2 separate tables)?

If the data in the top table are directly associated (same number of rows and in the same position) as the data in the bottom table, then I would say it doesn't matter whether you are clicking the top table or the bottom one.

However, I doubt that is the case.

|||

??€?§Q? wrote:

If the data in the top table are directly associated (same number of rows and in the same position) as the data in the bottom table, then I would say it doesn't matter whether you are clicking the top table or the bottom one.

However, I doubt that is the case.

The bottom table doesn't contain the same number of rows as the top one. The top table has one row for each member of staff, which consists summary data about how many pieces of work they have completed. What I want is for the user to be able to click on the name of a member of staff, and then they will be taken to a separate screen showing the full details for that particular staff-member (this data should be taken from what is currently the bottom table in the report). The bottom table contains one row for each piece of work that a particular staff-member has completed, so the number of rows would vary depending on whose name has been selected.

I hope this makes sense; it's always hard to explain what you're trying to do on a forum.

|||

Ok, so why not just create a drilldown report and enable navigation on the top report to that drilldown?

Why does the bottom table play into this at all?

|||

You could have a hidden parameter in your report got StaffID. Setup Visibility on the bottom table to hide if staffid is null, and show when it isnt. Do the opposite with the top table, if is hidden if staffid is not null, and visible if it is null.

Setup the navigation on the top table to go to the same report, passing the StaffID that was clicked on.

Since StaffID will not be null, the report will hide the top and only show the bottom.

Now, all that being said, the better way to do that is to have 2 reports, one for the list of staff and one for the details.

HtH

BobP

|||

And to open the report in the new window, you will have to use javascript in the Navigation window.

Select Jump to URL and add this code:

="javascript:void window.open('http://server/reportserver/pages/reportviewer.aspx?%2fFOLDER%2fREPORT&StaffID=" & Fields!StqaffID.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

BobP

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 conflict viewer/resolver

Hello,

I see that MS has a conflict viewer that can be accessed by managment studio, however I would like to create one with some customized options. Is there a sample of code somewhere to start this? I could really find anything in BOL. Thanks in advance.

John

what version of sql server are you using? Books online should have examples for two types of conflict resolvers - COM and stored procedure. Search Books online for "custom conflict resolver". Let me know if you cannot find it.

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 logins

They're under Security>Logins in Management Studio Object Explorer
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"robert salazar" <robert salazar@.discussions.microsoft.com> wrote in message
news:406F979C-5E66-48FA-BF8D-06ECF466F6ED@.microsoft.com...
> In sql 2000 the logins were displayed under "Security" In sql 2005 I
> sucessfully create the login but I cant find them?
> Where have they moved themI looked, there not. But the logins created succesfully
"Jasper Smith" wrote:

> They're under Security>Logins in Management Studio Object Explorer
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "robert salazar" <robert salazar@.discussions.microsoft.com> wrote in messa
ge
> news:406F979C-5E66-48FA-BF8D-06ECF466F6ED@.microsoft.com...
>
>|||In sql 2000 the logins were displayed under "Security" In sql 2005 I
sucessfully create the login but I cant find them?
Where have they moved them|||They're under Security>Logins in Management Studio Object Explorer
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"robert salazar" <robert salazar@.discussions.microsoft.com> wrote in message
news:406F979C-5E66-48FA-BF8D-06ECF466F6ED@.microsoft.com...
> In sql 2000 the logins were displayed under "Security" In sql 2005 I
> sucessfully create the login but I cant find them?
> Where have they moved them|||I looked, there not. But the logins created succesfully
"Jasper Smith" wrote:

> They're under Security>Logins in Management Studio Object Explorer
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "robert salazar" <robert salazar@.discussions.microsoft.com> wrote in messa
ge
> news:406F979C-5E66-48FA-BF8D-06ECF466F6ED@.microsoft.com...
>
>|||Did you refresh?
Hope this helps.
Dan Guzman
SQL Server MVP
"robert salazar" <robertsalazar@.discussions.microsoft.com> wrote in message
news:AE163B15-7538-41F4-AC25-48C377DBB76C@.microsoft.com...[vbcol=seagreen]
>I looked, there not. But the logins created succesfully
> "Jasper Smith" wrote:
>|||Did you refresh?
Hope this helps.
Dan Guzman
SQL Server MVP
"robert salazar" <robertsalazar@.discussions.microsoft.com> wrote in message
news:AE163B15-7538-41F4-AC25-48C377DBB76C@.microsoft.com...[vbcol=seagreen]
>I looked, there not. But the logins created succesfully
> "Jasper Smith" wrote:
>

Created database in Visual Studio 2005; How do I add it to SQL Server?

I have created a database in Visual Studio 2005 by adding a new "SQL Database" item to my project, and then designing the tables with Server Explorer.

How can I add this database to my local instance of SQL Server Express, and to my SQL Server 2000 database engine?

You will find that the database has probably been created as a User Instance. The easy way to check this is to have a look at the app.config file, look for an Attach file string in the connection String. To add it to the SQL Express Engine you will need to attach it, this can be done through the command line or through the SQL Management Studio for Express (Or Full version). For information on the command line syntax for attaching the database I would have a look at the help or books online for Attaching Databases.

In regards to adding it to the SQL 2000 engine you might find that you will have to change the database structure as several functions will not work with the old engine. And in summary the only way to add it would be to strip out the DDL (Data Definition Language) scripts as well as the scripts to add the data and recreate the database.

|||

Thank you for the response.

I'd like to ask a more general question. What is the best way to define databases within Visual Studio 2005, so that they will be part of my C# project and Source Safe version control? I guess what I'm looking for is sort of a "best practice" approach to developing my program and the associated databases together.

I will appreciate any information or MSDN references you can provide on this topic.

|||

I do not know of any real best practices, But I do not stor e the Database files in Source Safe I use a seperate database project and only store the Data Definition Language for the database (SQL Scripts). I can then add the project to the solution and then to the VSS Project. I have had Problems in the past adding the MDF Files used by SQl Express in VSS so have chossen to follow the above procedures.

Hope this helps.