Creating DB from scripts: February 2012

Saturday, February 25, 2012

Creating a Databse on the Fly

I wanted to Write A Stored Procedure which Will accepts the name of the "Database" as a parameter
But when i am trying to do so i am getting error
--------------------------
create procedure create_DB
@.db_name as varchar(30)
as
create database @.db_name
------------------------
The error is as Follows
'Incorrect syntax near '@.db_name'.'

Pl help me in this regardsOriginally posted by pankaj_bidwai
I wanted to Write A Stored Procedure which Will accepts the name of the "Database" as a parameter
But when i am trying to do so i am getting error
--------------------------
create procedure create_DB
@.db_name as varchar(30)
as
create database @.db_name
------------------------
The error is as Follows
'Incorrect syntax near '@.db_name'.'

Pl help me in this regards

I guess you missed the brackets:

create procedure create_DB
(@.db_name as varchar(30))
as
create database @.db_name|||Hi
No that is not the prob even if i use the brackets i will get an error
If i give harcoded name instead of variable i don't get an error
Originally posted by DoktorBlue
I guess you missed the brackets:

create procedure create_DB
(@.db_name as varchar(30))
as
create database @.db_name|||pankaj_bidwai, CREATE DATABASE does not accept a variable for the db name. Have you tried:

declare @.db_name varchar(30)
set @.db_name = 'PSYTEST'
execute('create database ' + @.db_name)
execute('drop database ' + @.db_name)|||Remove the "AS" in the parameter list in the stored proc signature.|||Hu?? Please post corrected code demonstrating how this works.|||Here you go...

create procedure create_DB
@.db_name varchar(30)
as

exec('create database ' + @.db_name)

go|||What happend to removing the "AS"?|||Alas, that wasn't the problem after all. Using "as" in the parameter list is a technique that I've not seen before. I didn't think it was valid SQL. The real problem was the lack of dynamic SQL to build the CREATE DATABASE statement properly.

I didn't read any of the other threads, so my reply may have been redundant.

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 with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.
1)How are you connecting to the other databases ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.
|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:

> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> to
> seperate
> I
> me.
>
>

Creating a database with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.1)How are you connecting to the other databases ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:

> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote
in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> to
> seperate
> I
> me.
>
>

Creating a database with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.1)How are you connecting to the other databases ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:

> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote
in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> to
> seperate
> I
> me.
>
>

Creating a database with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.1)How are you connecting to the other databases ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:
> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> > Hello,
> >
> > I am trying to figure out the best way to design a database that links
> to
> > external information like freedb.org and amazon, ect. I have created the
> > tables of the information I am looking for, but am wondering if I have to
> > create a table to link to those places to import the information in the
> > database I am trying to design. I think it is possible I have two
> seperate
> > questions in here. So I will try and breakdown to what I am looking for.
> I
> > have a database with specific information, I am looking for. I have media
> > that contains some, but not all of the information readily available to
> me.
> > Now what I want to do is take that bits and pieces of that information and
> > enter it into my current database and have it search other external
> > databases, to find the rest of the information and input that information
> > into my current database. Any help I would appreciate.
>
>

Creating a database with an MSDE installation

Hello,
I'm Trying to setup a XP workstation with the msde sql server engine, but I
can't find a way to create a database or import a database with it.
greetings
hi,
rgerla wrote:
> Hello,
> I'm Trying to setup a XP workstation with the msde sql server engine,
> but I can't find a way to create a database or import a database with
> it.
> greetings
MSDE comse with no tools but oSql.exe, a command line tool you can use for
both administrative task and DML operations.. please have a look at
http://support.microsoft.com/default...;EN-US;q325003 for further
info about oSql..
for your convenience, syou can have a look at a prj of mine, at the link
following my sign., which provide a free user interface similar to Microsoft
Enterprise Manager to administer and manage MSDE... further tools, both
commercial and free can be found at
http://www.microsoft.com/sql/msde/partners/default.asp and/or
http://www.aspfaq.com/show.asp?id=2442
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Using DBAMgr To access the MSDE installation I get the follow error while
trying to logon:
-2147203052 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
for user 'sa'. Reason: Not associated with a trusted SQL Server Connection
What am I doing wrong?
"Andrea Montanari" wrote:

> hi,
> rgerla wrote:
> MSDE comse with no tools but oSql.exe, a command line tool you can use for
> both administrative task and DML operations.. please have a look at
> http://support.microsoft.com/default...;EN-US;q325003 for further
> info about oSql..
> for your convenience, syou can have a look at a prj of mine, at the link
> following my sign., which provide a free user interface similar to Microsoft
> Enterprise Manager to administer and manage MSDE... further tools, both
> commercial and free can be found at
> http://www.microsoft.com/sql/msde/partners/default.asp and/or
> http://www.aspfaq.com/show.asp?id=2442
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
rgerla wrote:
> Using DBAMgr To access the MSDE installation I get the follow error
> while trying to logon:
> -2147203052 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login
> failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server Connection
> What am I doing wrong?
MSDE installs by default enabling only WinNT (trusted) connections and
disabling standard SQL Server connections (userid=xxx; password=xxx)
you can modyfy this behaviour both at install time, specifying the
SECURITYMODE=SQL parameter to the setup.exe MSDE boostrap installer, or
later, at run time, modifying a Windows registry key,
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
LoginMode=2
for a default instance,
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
LoginMode=2
for a named instance...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Creating a database with a GUI

Is there any tools to create a database with tables, views, and all that jazz with a GUI. I went through the exammle on beta.asp.net but the way they created their db was with a script. I want to create everything with a GUI app. If this gui app doesn't exist can someone point me to where I can find more info about creating db, tables etc with sql server 2005 express? Thanks in advance.
-Daticus

Try the link below and download the Express Manager. Hope this helps.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx#EBAA|||I think this is what your looking for...
Andrea Montanari
andrea.sql@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
You can also use an Access Project to build an SQL database graphically
Good Luck
Simon

Creating a Database using ASP.NET

Hi All,

Can anybody help me/does anybody have the code to create a database on ms sql server 2000 using asp.net?

This needs to be done without being asked for the sql server login credentials (i.e the sa username/password can be inserted in the code)Using Microsoft Data Access Application Blocks, I would do the following (C#):
SQLHelper.ExecutNonQuery("Connection string", CommandType.Text, "Create Database...");
That's a simple brute force way to do it, you'll obviously need to play and tweak it, but hopefully that will get you started.

http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider
is a link to where the connection strings for ADO.NET are.

HTH|||Thanks for the pointer.

Not being much of a .net coder, do you know of any complete solutions (web based database administration tools) or even better, do you know anybody that would be able to create the script for me...i'll pay of course!|||http://weblogs.asp.net/ashben/archive/2003/09/28/29469.aspx is a link to some web based datbase administration tools. Let us know if that is what you need or if you actually need someone to do this for you.|||Hi ericlandes,

Thanks for the link. I do still require someone to code an asp.net page for me to create databases.

* The page must be available to anonymous users (anyone can create a database) - So maybe the 'sa' user credentials are coded into the asp.net form?

* The form will allow the user to specify a database name to create and create an sql username/password with permissions to that database

* When the database is created, it will have certain options set (i.e maximum database size of 2mb)|||So will each person not only create the database, but create different fields, with different types, etc, or will each person basically generate a new 'template' database, all of them having the same fields/datatypes, with the only difference being the name of the database, it's username & password?|||The basic concept is:

User visits page, enters the name of the database they want to create, and also picks a username/password to create which will have access this new database.

Basic checks will need to be made to ensure that the database and username do not already exist.

The database will be created totaly blank, with the maximum size of the database set to 2mb|||anyone got any ideas?|||Hello xmsms,

It sounds to me like you are creating a web-hosting environment with data-access.

Even if this is not your intended useage, I'll bet that if you look for hosting tools for SQL-Server then you will be on the right track to finding the tool, or the right code for your needs.

Did you ever find your coder to do the job for you? If not and you are still stuck, I will be able to look into your question further after June 20th.

Creating a database schema from a DTD?

Assuming a DTD would lead to a well defined schema, isn't
there a tool to map DTDs to relational schemas?
SQL Server does not support DTD since it gives not enough information about
types and cannot be easily annotated as the XML-based schema formats XDR and
W3C's XML Schema.
You can either use a tool to translate your DTD into one of the XML-based
schema formats or look for a third-party tool (I think Ron Bourret had one).
Best regards
Michael
"Bob Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:167901c4e55b$3336f1c0$a601280a@.phx.gbl...
> Assuming a DTD would lead to a well defined schema, isn't
> there a tool to map DTDs to relational schemas?

Creating a database schema from a DTD?

Assuming a DTD would lead to a well defined schema, isn't
there a tool to map DTDs to relational schemas?SQL Server does not support DTD since it gives not enough information about
types and cannot be easily annotated as the XML-based schema formats XDR and
W3C's XML Schema.
You can either use a tool to translate your DTD into one of the XML-based
schema formats or look for a third-party tool (I think Ron Bourret had one).
Best regards
Michael
"Bob Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:167901c4e55b$3336f1c0$a601280a@.phx.gbl...
> Assuming a DTD would lead to a well defined schema, isn't
> there a tool to map DTDs to relational schemas?

Creating a database report card

I'm creating a report card for the systems I'm responsible for and have
setup most of the data structures and collection infrastructure I need.
However, there is still the question of exactly what I need to collect.
I will be storing these values for an extended period of time (not sure how
long just yet, probably monthly averages over 5'ish years).
I'm defiantly tracking:
Overall drive space used/free
Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out of
the engine, not assuming extensions)
Log file usage
Data file usage.
However, I'm also looking at the SQL Server system variables: (I assume
these are for SQL Server only and don't include anything happening in
Windows).
Primary interest:
@.@.CPU_BUSY
@.@.IDLE
@.@.IO_BUSY
Secondary interest:
@.@.TOTAL_READ
@.@.TOTAL_WRITE
@.@.TOTAL_ERRORS
@.@.CONNECTIONS
Tertiary interest:
@.@.PACK_RECEIVED
@.@.PACK_SENT
@.@.PACKET_ERRORS
I know they get reset when SQL Server restarts (and I suppose they rollover
if the server has been up so long it can max the counter - like 5 years, or
something), however, that would produce an easily identifiable situation.
Opinions please.
Thanks,
JayI don't think it's that useful to track these system variables. What
questions are you going to answer with a history of these system variable
values?
You are better off tracking relevant perfmon counters and SQL Server
internal stats such as wait stats and I/O file stats.
Linchi
"Jay" wrote:
> I'm creating a report card for the systems I'm responsible for and have
> setup most of the data structures and collection infrastructure I need.
> However, there is still the question of exactly what I need to collect.
> I will be storing these values for an extended period of time (not sure how
> long just yet, probably monthly averages over 5'ish years).
> I'm defiantly tracking:
> Overall drive space used/free
> Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out of
> the engine, not assuming extensions)
> Log file usage
> Data file usage.
> However, I'm also looking at the SQL Server system variables: (I assume
> these are for SQL Server only and don't include anything happening in
> Windows).
> Primary interest:
> @.@.CPU_BUSY
> @.@.IDLE
> @.@.IO_BUSY
> Secondary interest:
> @.@.TOTAL_READ
> @.@.TOTAL_WRITE
> @.@.TOTAL_ERRORS
> @.@.CONNECTIONS
> Tertiary interest:
> @.@.PACK_RECEIVED
> @.@.PACK_SENT
> @.@.PACKET_ERRORS
>
> I know they get reset when SQL Server restarts (and I suppose they rollover
> if the server has been up so long it can max the counter - like 5 years, or
> something), however, that would produce an easily identifiable situation.
> Opinions please.
> Thanks,
> Jay
>
>|||Hi Linchi,
Thank you for your response.
>I don't think it's that useful to track these system variables. What
> questions are you going to answer with a history of these system variable
> values?
Average and peak CPU would measure when we we would need more processor
power, I/O would measure the efficiency of the drive subsystem.
> You are better off tracking relevant perfmon counters and SQL Server
> internal stats such as wait stats and I/O file stats.
Ya, that would be more traditional, however, they are more diffucult to get
into the database for long term storage. The @.@. system variables would be a
lot easier. Which is why I'm looking at them.
Jay
> Linchi
> "Jay" wrote:
>> I'm creating a report card for the systems I'm responsible for and have
>> setup most of the data structures and collection infrastructure I need.
>> However, there is still the question of exactly what I need to collect.
>> I will be storing these values for an extended period of time (not sure
>> how
>> long just yet, probably monthly averages over 5'ish years).
>> I'm defiantly tracking:
>> Overall drive space used/free
>> Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out
>> of
>> the engine, not assuming extensions)
>> Log file usage
>> Data file usage.
>> However, I'm also looking at the SQL Server system variables: (I assume
>> these are for SQL Server only and don't include anything happening in
>> Windows).
>> Primary interest:
>> @.@.CPU_BUSY
>> @.@.IDLE
>> @.@.IO_BUSY
>> Secondary interest:
>> @.@.TOTAL_READ
>> @.@.TOTAL_WRITE
>> @.@.TOTAL_ERRORS
>> @.@.CONNECTIONS
>> Tertiary interest:
>> @.@.PACK_RECEIVED
>> @.@.PACK_SENT
>> @.@.PACKET_ERRORS
>>
>> I know they get reset when SQL Server restarts (and I suppose they
>> rollover
>> if the server has been up so long it can max the counter - like 5 years,
>> or
>> something), however, that would produce an easily identifiable situation.
>> Opinions please.
>> Thanks,
>> Jay
>>

creating a database on the server

Is there a way to create a brand new database on the server?
Someone gave me the asp code below, but I haven't gotten it to work yet.
Thanks for any help.
<%
Dim strSQL
Dim cnnTest
strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
strSQL = strSQL & "(" & vbCrLf
strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
strSQL = strSQL & ")" & vbCrLf
Set cnnTest = Server.CreateObject("ADODB.Connection")
response.write "got to here"
cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
& "Initial Catalog=test;User Id=sa;Password=;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
response.write "are we heare"
cnnTest.Execute strSQL
cnnTest.Close
Set cnnTest = Nothing
%>
that creates a Table, not a new DB...which is it you are after? Also, why
are you posting it to these groups? some .Net, some not, some SQL... ?
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Is there a way to create a brand new database on the server?
> Someone gave me the asp code below, but I haven't gotten it to work yet.
> Thanks for any help.
> <%
> Dim strSQL
> Dim cnnTest
> strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
> strSQL = strSQL & "(" & vbCrLf
> strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
> strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
> strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
> strSQL = strSQL & ")" & vbCrLf
> Set cnnTest = Server.CreateObject("ADODB.Connection")
> response.write "got to here"
> cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
> & "Initial Catalog=test;User Id=sa;Password=;" _
> & "Connect Timeout=15;Network Library=dbmssocn;"
> response.write "are we heare"
> cnnTest.Execute strSQL
> cnnTest.Close
> Set cnnTest = Nothing
> %>
>
>
|||Hello,
Thanks for your help. I just wanted to create the actual database on the
server, then create a table within the database. I realize the sample code
I sent is probably not the right code.
I posted to several groups I thought seemed related to this question. Maybe
I picked the wrong ones.
Thanks.
Scott Baxter
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
> that creates a Table, not a new DB...which is it you are after? Also, why
> are you posting it to these groups? some .Net, some not, some SQL... ?
> --
> Curt Christianson
> Owner/Lead Developer, DF-Software
> Site: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
> "Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
> news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
>
|||You need to create a script, sql statement that does
something like:
create database YourDB
go
use YourDB
go
create table YourTable
(col1 int not null)
-Sue
On Mon, 24 May 2004 11:12:28 -0700, "Scott Baxter"
<sbaxter@.websearchstore.com> wrote:

>Hello,
>Thanks for your help. I just wanted to create the actual database on the
>server, then create a table within the database. I realize the sample code
>I sent is probably not the right code.
>I posted to several groups I thought seemed related to this question. Maybe
>I picked the wrong ones.
>Thanks.
>Scott Baxter
>"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
>news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
>

creating a database on the server

Is there a way to create a brand new database on the server?
Someone gave me the asp code below, but I haven't gotten it to work yet.
Thanks for any help.
<%
Dim strSQL
Dim cnnTest
strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
strSQL = strSQL & "(" & vbCrLf
strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
strSQL = strSQL & ")" & vbCrLf
Set cnnTest = Server.CreateObject("ADODB.Connection")
response.write "got to here"
cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
& "Initial Catalog=test;User Id=sa;Password=;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
response.write "are we heare"
cnnTest.Execute strSQL
cnnTest.Close
Set cnnTest = Nothing
%>that creates a Table, not a new DB...which is it you are after? Also, why
are you posting it to these groups? some .Net, some not, some SQL... '
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Is there a way to create a brand new database on the server?
> Someone gave me the asp code below, but I haven't gotten it to work yet.
> Thanks for any help.
> <%
> Dim strSQL
> Dim cnnTest
> strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
> strSQL = strSQL & "(" & vbCrLf
> strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
> strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
> strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
> strSQL = strSQL & ")" & vbCrLf
> Set cnnTest = Server.CreateObject("ADODB.Connection")
> response.write "got to here"
> cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
> & "Initial Catalog=test;User Id=sa;Password=;" _
> & "Connect Timeout=15;Network Library=dbmssocn;"
> response.write "are we heare"
> cnnTest.Execute strSQL
> cnnTest.Close
> Set cnnTest = Nothing
> %>
>
>|||Hello,
Thanks for your help. I just wanted to create the actual database on the
server, then create a table within the database. I realize the sample code
I sent is probably not the right code.
I posted to several groups I thought seemed related to this question. Maybe
I picked the wrong ones.
Thanks.
Scott Baxter
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
> that creates a Table, not a new DB...which is it you are after? Also, why
> are you posting it to these groups? some .Net, some not, some SQL... '
> --
> Curt Christianson
> Owner/Lead Developer, DF-Software
> Site: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
> "Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
> news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
>|||You need to create a script, sql statement that does
something like:
create database YourDB
go
use YourDB
go
create table YourTable
(col1 int not null)
-Sue
On Mon, 24 May 2004 11:12:28 -0700, "Scott Baxter"
<sbaxter@.websearchstore.com> wrote:

>Hello,
>Thanks for your help. I just wanted to create the actual database on the
>server, then create a table within the database. I realize the sample code
>I sent is probably not the right code.
>I posted to several groups I thought seemed related to this question. Mayb
e
>I picked the wrong ones.
>Thanks.
>Scott Baxter
>"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
>news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
>

Creating a database on SQL server 2005

Hi,

This might be a dumd question,

But how do I create a database in SQL2005 the application installed on the server is below

Windows 2003 server , IIS 6.0 , .NET Framework 2.0 Beta 2 , Visual studio 2005 Team suite Beta 2 , Visual studio 2005 Team Foundation Server Beta 2, SQL server April Community Technology.

Thanks in advance.

Hi,

you can use the Sql Server Management Studio Shiped with Sql Server 2005.

Or you code your own app using SQL-Statements (Create Database myDB).

Creating a database on a network path

When I try to create a new database on a network path, I get the following error:

"\\server\test\testdatabase.mdf" is on a network path that is not supported for database files.

Am I trying something that isn't possible? Should databases always be stored on the server PC running SQL (Express)?
I'm using Visual Basic Express and SQL Express (installed on a local PC not the server, so the connectionstring is ".\SQLEXPRESS"), TCP/IP is enabled.

Regards, EBAMore about storage in network path in SQL Server can be found here:

http://support.microsoft.com/kb/304261/en-us

Generally spoken, this is not supported.

HTH, Jens Suessmeyer.

http://www.slqserver2005.de|||Jens, thanks very much for your help. It would have taken me quite some time to find this answer. I guess I'll abandon this idea.

Regards, EBA|||

WOW, I am impressed!

Creating a database on a mapped drive

Is it possible to create a database (MSSQL2k) on a mapped drive which is not a SAN?
Thanks,
Peter SchaussAssuming you mean a drive that is mapped to another server on the network, you can probably do that. Unfortunately, it will probably corrupt every time you bounce either of the machines.

So can you? Maybe
Should you? no.|||No, read BOL.|||True from SQl 2K you can create database on a removable media such as CD-ROM but not on shared(mapped) drive.

creating a database link to oracle

I am new to SQL SERVER and need to link MS to Oracle. We have an Oracle
database where we need to pull some data from through a procedure. Can you
please give me step-by-step instructions on how to do this (the Dummy guide).
Download the sample chapter on the below link. It tells you how to create
oracle subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"charlie" <charlie@.discussions.microsoft.com> wrote in message
news:77F7B2C8-A662-4E54-9113-2AD81E429698@.microsoft.com...
> I am new to SQL SERVER and need to link MS to Oracle. We have an Oracle
> database where we need to pull some data from through a procedure. Can
you
> please give me step-by-step instructions on how to do this (the Dummy
guide).
>

creating a database link between DB2 and SQL server

Hi all,

My requirement is to migrate all the data from DB2 database to SQL Server database. Is it possible to create a database link between these two databases.

regards,
Naren

Quote:

Originally Posted by ntiruhar

Hi all,

My requirement is to migrate all the data from DB2 database to SQL Server database. Is it possible to create a database link between these two databases.

regards,
Naren

Question is being moved to the SQL Server forum.

ADMIN

creating a database in SQL 2005 - using windows 2003

when i try to create a new database i get a error: The server could not
load DCOM

what is wrong and how can i fix it?

i have updated mssql/win 2003 with the lates updates

Rgds

BobbyHi Bobby

This usually has something to do with user permissions. Please ensure
that your database login has permissions to create new databases. Also
ensure that you have sufficient Windows user permissions to the
directory where SQL Server is trying to create the .mdf and the .ldf
file.

Cheers
Louis

Creating a database from script - permissions problems

We deploy our app via ClickOnce. Rather than shipping the .mdf and .ldf, the app detects whether the database is there and creates it via script if it's not. The script was originally generated from SQL Exrpess Mangement Console, etc.

In the past, we've made the stipulation that the user must have administrator access to the machine. However, we now need to find a workable solution for users who are not administrators on their machine. Currently, we use the following connection string before attempting to run the db creation script:

conn.ConnectionString = "Server=.\\sqlexpress;Integrated Security = true;User Instance=false";

The script fails when trying to execute "CREATE DATABASE [myDatabaseName] ON PRIMARY "

with the error:

"CREATE DATABASE permission denied in database 'Master'"

How can we get around this permissions issue in the most automated way possible?

TIA!

Hi,

you will need to have a user with dbcreator permissions, otherwise you won′t be able to make an entry in the sysdatabases (Creating a database). Can you use any administrative SQL Server login to connect to the database and make this possible ? The connection string has to include the userid as well as the password then.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Creating a Database from multiple databases accross multiple servers

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a

I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||

'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.

Your only option is to create a linked server and use OpenQuery() or 4-part name query.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

|||

Thanks for the reply

I have managed to create a linked server using the following bit of code:

Code Snippet

EXEC sp_addlinkedserver

@.server = 'APPOLO/ACT7',

@.srvproduct = 'SQLServr OLEDB Provider',

@.provider = 'MSDASQL',

@.datasrc='ACT7'

I then created and ran the following statement:

Code Snippet

SELECT *

FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')

I then get the following error message:

"Incorrect Syntac near '/'"

I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.

Any Ideas?

|||How about:

[APPOLO/ACT]

HTH!|||

Great - that solved that problem -

I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?

Thanks

Tom

|||

You use this to set the login.

Code Snippet

EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'

Creating a Database from multiple databases accross multiple servers

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a

I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||

Your only option is to create a linked server and use OpenQuery() or 4-part name query.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

|||

Thanks for the reply

I have managed to create a linked server using the following bit of code:

Code Snippet

EXEC sp_addlinkedserver

@.server = 'APPOLO/ACT7',

@.srvproduct = 'SQLServr OLEDB Provider',

@.provider = 'MSDASQL',

@.datasrc='ACT7'

I then created and ran the following statement:

Code Snippet

SELECT *

FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')

I then get the following error message:

"Incorrect Syntac near '/'"

I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.

Any Ideas?

|||How about:

[APPOLO/ACT]

HTH!|||

Great - that solved that problem -

I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?

Thanks

Tom

|||

You use this to set the login.

Code Snippet

EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'

Creating a database from a form.

I've made a form and now all I want to do is save everything in the form into a new database file but I don't know how. I'm using Visual Web Developer 2005. Somehow I'm guessing that I have to set something somewhere so when the submit button is hit, it knows to save it to a database file which doesn't exist yet so it would have to create it. It should be easy but I can't figure it out.

This is an excellent article to review to fully answer your question:

http://aspnet.4guysfromrolla.com/articles/110905-1.aspx

Hope this helps.

Creating a Database for deployment in SQL Server

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

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

Any advice appreciated.

Cheers,

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

Creating a Database for deployment in SQL Server

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

Any advice appreciated.

Cheers,

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

creating a database by sql script

I developed a program. This program will use a SQL server database. How can I create the database using code. I think I need to use sql scipt like this:

create database customers

But where should I write the script and How can I make VB code implement the script.

THanks a lot

You can just execute the sql statement.

e.g.

using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "create database [db1]";
cmd.CommandType = CommandType.Text;
//execute
cmd.ExecuteNonQuery();
}

|||

Thank you very much.

I used the following code. It worked in the first time. But when I debugged the program for the second time, I had this error " database db1 already exists"

How can I solve this problem?

And can you give me a sql script to create one database with one three-column table?

thanks

Using (cmdGet)

cmdGet.Connection = conGet

cmdGet.CommandText = "create database [db1]"

cmdGet.CommandType = CommandType.Text

cmdGet.ExecuteNonQuery()

End Using

|||

Code Snippet

'create db

cmdGet.CommandText = "if db_id('db1') is null exec('create database [db1]')"

cmdGet.CommandType = CommandType.Text

cmdGet.ExecuteNonQuery()

'create table

cmdGet.CommandText = "use db1; if object_id('tb1') is null exec('create table tb1(col1 int, col2 int, col3 int)')"

cmdGet.ExecuteNonQuery()

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

Hi. Im brand new to using SQL. I downloaded the SQL Server 2005 Trial Edition (Enterprise Evaluation Edition) and installed it. How do i create a database ? The only thing i can seem to open is the SQL Server Configuration Manager which just lets me start the Server. Did I install it wrong or open the wrong program? If someone could help I would greatly appreciate it.

you should be able to find the enterprise manager

probably you have downloaded the Express edition or

you failed to set up the database engine and the client tools

|||well how would I install those ?
|||Because neither of those were an option when installing SQL
|||try reinstalling again|||

Snyper

This should get you going....

http://msdn2.microsoft.com/en-us/library/ms233763.aspx

Patrick

|||Um, im not using Express. Im using the Trial Version, the 180 day one.
|||Did you install the client tools as well? If you did, you should be able to see Microsoft SQL Server 2005 in your Program Files which contains SQL Server Management Studio|||Yes, i missed it on the first installation. Thanks.
|||

How the hell did you get the trial version to install Enterprise Manager? Why is everything Microsoft so poorly developed

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

you should be able to find the enterprise manager

probably you have downloaded the Express edition or

you failed to set up the database engine and the client tools

|||well how would I install those ?|||Because neither of those were an option when installing SQL|||try reinstalling again|||

Snyper

This should get you going....

http://msdn2.microsoft.com/en-us/library/ms233763.aspx

Patrick

|||Um, im not using Express. Im using the Trial Version, the 180 day one.|||Did you install the client tools as well? If you did, you should be able to see Microsoft SQL Server 2005 in your Program Files which contains SQL Server Management Studio|||Yes, i missed it on the first installation. Thanks.|||

How the hell did you get the trial version to install Enterprise Manager? Why is everything Microsoft so poorly developed

Creating a database

I have an excel document that Imported to access database. That database should count the number of tickets created by each tech rep; list the area, sub area and description of call created by a the tech rep. Can access perform this task and if yes, any idea I to approach that project or should i use SQL, And if access won't be able to do it. please make a suggestion how to approach this project. it will be nice if i can get the steps.

EXAMPLE
Request Id SRS Started Call Description Area Sub Area Request Status Closed Date Assign To Created ByI have an excel document that Imported to access database. That database should count the number of tickets created by each tech rep; list the area, sub area and description of call created by a the tech rep. Can access perform this task and if yes, any idea I to approach that project or should i use SQL, And if access won't be able to do it. please make a suggestion how to approach this project. it will be nice if i can get the steps.

EXAMPLE
Request Id SRS Started Call Description Area Sub Area Request Status Closed Date Assign To Created By

I think its an Access Question ,so I think it should be moved to Access forum.|||I think its an Access Question ,so I think it should be moved to Access forum.Agreed. Except it is already duped in Access:
http://www.dbforums.com/showthread.php?p=6237173&posted=1#post6237173

I am locking this thread. As mentioned in the dupe, please repost a question in SQL Server if it turns out that Access is not sufficient for your needs. Hopefully this should not cause a problem but please PM me if you want to discuss.

creating a database

Hi;
If I have a schema for creating a database, is there a command line
application I can run and pass it the filename of the schema and it will then
create that database? If so, can someone point me to a url showing the
command line syntax for it?
And, what registry entry can I use to determine where this program is - as
some people do not install in C:\Program Files\...
thanks - dave
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Maybe this helps. http://support.microsoft.com/kb/325003
Use the variable %programfiles%
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| Hi;
|
| If I have a schema for creating a database, is there a command line
| application I can run and pass it the filename of the schema and it will
then
| create that database? If so, can someone point me to a url showing the
| command line syntax for it?
|
| And, what registry entry can I use to determine where this program is - as
| some people do not install in C:\Program Files\...
|
| thanks - dave
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm
|
|
|||osql is the tool I need.
I am retecient to use %programfiles% because we used to put Sql Server on d:
while programfiles was on c: - so on those systems at least, that would not
find it.
Is there anything Sql Server writes to the registry with it's location? I
think there was and I used it in a program where I used to work - but I don't
have access to that code.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Dave Patrick" wrote:

> Maybe this helps. http://support.microsoft.com/kb/325003
> Use the variable %programfiles%
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "David Thielen" wrote:
> | Hi;
> |
> | If I have a schema for creating a database, is there a command line
> | application I can run and pass it the filename of the schema and it will
> then
> | create that database? If so, can someone point me to a url showing the
> | command line syntax for it?
> |
> | And, what registry entry can I use to determine where this program is - as
> | some people do not install in C:\Program Files\...
> |
> | thanks - dave
> |
> | --
> | thanks - dave
> | david_at_windward_dot_net
> | http://www.windwardreports.com
> |
> | Cubicle Wars - http://www.windwardreports.com/film.htm
> |
> |
>
>
|||You could look at 'Path' and or 'SQLPath' found below but you really
shouldn't need the fully qualified path;
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup
since the installation of client tools appends to the system path variable.
C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program
Files\Microsoft SQL Server\80\Tools\Binn\

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| osql is the tool I need.
|
| I am retecient to use %programfiles% because we used to put Sql Server on
d:
| while programfiles was on c: - so on those systems at least, that would
not
| find it.
|
| Is there anything Sql Server writes to the registry with it's location? I
| think there was and I used it in a program where I used to work - but I
don't
| have access to that code.
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm
|||thank you - that looks perfect.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Dave Patrick" wrote:

> You could look at 'Path' and or 'SQLPath' found below but you really
> shouldn't need the fully qualified path;
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup
> since the installation of client tools appends to the system path variable.
> C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program
> Files\Microsoft SQL Server\80\Tools\Binn\
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "David Thielen" wrote:
> | osql is the tool I need.
> |
> | I am retecient to use %programfiles% because we used to put Sql Server on
> d:
> | while programfiles was on c: - so on those systems at least, that would
> not
> | find it.
> |
> | Is there anything Sql Server writes to the registry with it's location? I
> | think there was and I used it in a program where I used to work - but I
> don't
> | have access to that code.
> |
> | --
> | thanks - dave
> | david_at_windward_dot_net
> | http://www.windwardreports.com
> |
> | Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
|||You can use sp_helpfile to return the file locations.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:FC83AF0A-B244-4B64-A83F-36A21A6ADECA@.microsoft.com...[vbcol=seagreen]
> osql is the tool I need.
> I am retecient to use %programfiles% because we used to put Sql Server on
> d:
> while programfiles was on c: - so on those systems at least, that would
> not
> find it.
> Is there anything Sql Server writes to the registry with it's location? I
> think there was and I used it in a program where I used to work - but I
> don't
> have access to that code.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
> "Dave Patrick" wrote:
|||You're welcome.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| thank you - that looks perfect.
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm

Creating a database

When creating a database using OSQL -E -I CreateDatabase.sql
I have the following in my input file:
CREATE TABLE admin
(
ID char(7),
Pwd varchar(32)
)
GO
How could I make it so that Pwd does not allow nulls and has a default value
of 'Password'?
hi Terry,
Terry Olsen wrote:
> When creating a database using OSQL -E -I CreateDatabase.sql
> I have the following in my input file:
> CREATE TABLE admin
> (
> ID char(7),
> Pwd varchar(32)
> )
> GO
> How could I make it so that Pwd does not allow nulls and has a
> default value of 'Password'?
SET NOCOUNT ON
USE tempdb
CREATE TABLE dbo.Admin (
ID varchar(7) NOT NULL ,
Pwd varchar(32) NOT NULL DEFAULT 'Password'
)
INSERT INTO dbo.Admin VALUES ('Andrea' , DEFAULT )
SELECT * FROM dbo.Admin
DROP TABLE dbo.Admin
--<--
ID Pwd
-- --
Andrea Password
please have a look at
http://msdn.microsoft.com/library/de...eate2_8g9x.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Creating a database

I have a database in Access to keep records for CDs. The tables are as below
Artist
ArtID - 1,2,3...
ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai...
Genre
GenID - 1,2,3...
Genre - Pop, Rock, Hip Hop, Jazz...
CDType
CDTypeID - 1,2,3...
CDType - Album CD, Single CD, Double Cd, limited edition...
MainCDInformation
LineID - 1,2,3...
Artist - Linked with ArtID
CDTitle - Name of Album
CDType - linked with CDTypeID
Genre - Linked with GenID
Cost - User can enter a cost
Notes - User can enter addtional notes
I am getting conflicting advise whether this approach is correct or not the
way this database has been designed. Would you guys agree to it or not? If
not how would you create it and what rules would you follow?
Thanks in advance"Shelly" <Shelly@.discussions.microsoft.com> wrote in message
news:8D19B7CF-EBC4-4894-81D3-1D6E5B19B140@.microsoft.com...
>I have a database in Access to keep records for CDs. The tables are as
>below
> Artist
> ArtID - 1,2,3...
> ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai...
> Genre
> GenID - 1,2,3...
> Genre - Pop, Rock, Hip Hop, Jazz...
> CDType
> CDTypeID - 1,2,3...
> CDType - Album CD, Single CD, Double Cd, limited edition...
> MainCDInformation
> LineID - 1,2,3...
> Artist - Linked with ArtID
> CDTitle - Name of Album
> CDType - linked with CDTypeID
> Genre - Linked with GenID
> Cost - User can enter a cost
> Notes - User can enter addtional notes
> I am getting conflicting advise whether this approach is correct or not
> the
> way this database has been designed. Would you guys agree to it or not? If
> not how would you create it and what rules would you follow?
"Correct" is defined by the user. No schema is ever perfect and there are
frequently times when a schema is compromised in some way to achieve some
objective. Does that make the schema incorrect? If it works for you and
satisifies your requirements, why do you care if others think it is not
"correct"? If you are getting advice about the schema that is useful or
helps meet your requirements in some "better" way, then incorporate it. It
would help to have some examples of this advice to offer meaningful ... umm
... advice.
In any event, a more complete definition of the tables (and how they are
intended to be used) is required before anyone can make useful suggestions.
If you want comments:
* You have focused on a single medium (CD vs DVD, tape, vinyl, etc.).
* You appear to have overlooked the fact that an album may have multiple
artists or that an album may have no actual designated artist (insert late
night party-time CD commercial reference here).
* Notes? Your system supports a single user?
* You appear to be focused on music only. What about books or other types
of content?
* Is it not possible to have a limited edition double CD? CDType seems to
be a confusion of mutiple attributes.
If you really want to go nuts, then go visit some audiophile sites - you are
just scratching the surface of music content information. It may help to
explore some online music sites to see what information they provide, how
they organize, and how they link (or reference) related information.
Alternatively, have you considered purchasing this type of software - there
must be hundreds (if not thousands) of music cataloging programs (assuming
that is the purpose of this schema).|||Thanks for your advise. Sorry for the basic format of the table. This was
merely an idea to give you guys rather than having a lengthy page with tables
and fields.
I agree with what you have wrote but the issue is in Access this database is
acceptable. Only 1 user enters the details. For me to retrieve information
lets say about the Artist and the album title i would have a query like
Artist.ArtistName with CDTitle.MainCDInformation
(After the "." is the table name). Now this approach works. When I upgrade
this DB to SQL 2005 - all ok here too. The problem occurs when i create an
application in ASP .Net as the TableAdapter Preview looks correct but when
generating the same information (Artist and Cd Title) and then running this
application I have some data missing from fields that are joined to other
tables and cant figure out why.
Hope this makes some sense if not please let me know. If you wish i can post
a snippet of the SQL code it generates.
Thanks
"Scott Morris" wrote:
> "Shelly" <Shelly@.discussions.microsoft.com> wrote in message
> news:8D19B7CF-EBC4-4894-81D3-1D6E5B19B140@.microsoft.com...
> >I have a database in Access to keep records for CDs. The tables are as
> >below
> >
> > Artist
> > ArtID - 1,2,3...
> > ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai...
> >
> > Genre
> > GenID - 1,2,3...
> > Genre - Pop, Rock, Hip Hop, Jazz...
> >
> > CDType
> > CDTypeID - 1,2,3...
> > CDType - Album CD, Single CD, Double Cd, limited edition...
> >
> > MainCDInformation
> > LineID - 1,2,3...
> > Artist - Linked with ArtID
> > CDTitle - Name of Album
> > CDType - linked with CDTypeID
> > Genre - Linked with GenID
> > Cost - User can enter a cost
> > Notes - User can enter addtional notes
> >
> > I am getting conflicting advise whether this approach is correct or not
> > the
> > way this database has been designed. Would you guys agree to it or not? If
> > not how would you create it and what rules would you follow?
> "Correct" is defined by the user. No schema is ever perfect and there are
> frequently times when a schema is compromised in some way to achieve some
> objective. Does that make the schema incorrect? If it works for you and
> satisifies your requirements, why do you care if others think it is not
> "correct"? If you are getting advice about the schema that is useful or
> helps meet your requirements in some "better" way, then incorporate it. It
> would help to have some examples of this advice to offer meaningful ... umm
> ... advice.
> In any event, a more complete definition of the tables (and how they are
> intended to be used) is required before anyone can make useful suggestions.
> If you want comments:
> * You have focused on a single medium (CD vs DVD, tape, vinyl, etc.).
> * You appear to have overlooked the fact that an album may have multiple
> artists or that an album may have no actual designated artist (insert late
> night party-time CD commercial reference here).
> * Notes? Your system supports a single user?
> * You appear to be focused on music only. What about books or other types
> of content?
> * Is it not possible to have a limited edition double CD? CDType seems to
> be a confusion of mutiple attributes.
> If you really want to go nuts, then go visit some audiophile sites - you are
> just scratching the surface of music content information. It may help to
> explore some online music sites to see what information they provide, how
> they organize, and how they link (or reference) related information.
> Alternatively, have you considered purchasing this type of software - there
> must be hundreds (if not thousands) of music cataloging programs (assuming
> that is the purpose of this schema).
>
>|||> (After the "." is the table name). Now this approach works. When I upgrade
> this DB to SQL 2005 - all ok here too. The problem occurs when i create an
> application in ASP .Net as the TableAdapter Preview looks correct but when
> generating the same information (Artist and Cd Title) and then running
> this
> application I have some data missing from fields that are joined to other
> tables and cant figure out why.
> Hope this makes some sense if not please let me know. If you wish i can
> post
> a snippet of the SQL code it generates.
Not really. You've gone from a relatively simply schema question to an
Access->SQL Server/ASP migration issue. If the actual schema is not an
issue (i.e., it stores what you need and you can get the information you
want from it), then you should probably post details of the actual problem
to a NG that is focused on ASP.|||I thought it wouldnt which is why i wanted to keep this a simple question.
However now you can understand why i was questioning if the DB structure i
had was correct or not and why some say it was incorrect (since it doesnt
show up correctly when the application runs but does otherwise).
I have already asked in an ASP .Net group, which is why i asked here to see
if someone could shed some light here or not :-).
"Scott Morris" wrote:
> > (After the "." is the table name). Now this approach works. When I upgrade
> > this DB to SQL 2005 - all ok here too. The problem occurs when i create an
> > application in ASP .Net as the TableAdapter Preview looks correct but when
> > generating the same information (Artist and Cd Title) and then running
> > this
> > application I have some data missing from fields that are joined to other
> > tables and cant figure out why.
> >
> > Hope this makes some sense if not please let me know. If you wish i can
> > post
> > a snippet of the SQL code it generates.
> Not really. You've gone from a relatively simply schema question to an
> Access->SQL Server/ASP migration issue. If the actual schema is not an
> issue (i.e., it stores what you need and you can get the information you
> want from it), then you should probably post details of the actual problem
> to a NG that is focused on ASP.
>
>|||"Shelly" <Shelly@.discussions.microsoft.com> wrote in message
news:BBF6E91D-F447-4384-8995-E0D2AE814619@.microsoft.com...
>I thought it wouldnt which is why i wanted to keep this a simple question.
> However now you can understand why i was questioning if the DB structure i
> had was correct or not and why some say it was incorrect (since it doesnt
> show up correctly when the application runs but does otherwise).
IMO, the clue here is that it "doesnt show up correctly when the application
runs but does otherwise". I think you should give some serious thought to
the code you are using and, to a lesser degree, the queries it generates to
access the database. In your previous post, you also mentioned "joining to
other tables". This also leads me to believe the issue is with the code,
not the schema. The information you previously posted wasn't a complete
schema, so perhaps you left out things needed for your application to work
"correctly" (like primary keys, foreign keys, other tables, etc.).|||I had a feeling you might have said its code related as soon as i clicked
Post on my last response. Its definately not the code as other DB works fine
using this code also i tested the code which was provided with ASP .Net
tutorials.
I can post a detailed database structure if you think it could be something
else. Its a tough one as thsi DB works fine with Access - the code works fine
with other databases and when i generate the code under the TableAdapter i
can view the results and they show up correctly (A TableAdapter is a bit like
creating a query in design mode in SQL - sorry i added this incase you wasnt
aware of what a TableAdapter is - if you do, my apologies)
Thanks
"Scott Morris" wrote:
> "Shelly" <Shelly@.discussions.microsoft.com> wrote in message
> news:BBF6E91D-F447-4384-8995-E0D2AE814619@.microsoft.com...
> >I thought it wouldnt which is why i wanted to keep this a simple question.
> > However now you can understand why i was questioning if the DB structure i
> > had was correct or not and why some say it was incorrect (since it doesnt
> > show up correctly when the application runs but does otherwise).
> IMO, the clue here is that it "doesnt show up correctly when the application
> runs but does otherwise". I think you should give some serious thought to
> the code you are using and, to a lesser degree, the queries it generates to
> access the database. In your previous post, you also mentioned "joining to
> other tables". This also leads me to believe the issue is with the code,
> not the schema. The information you previously posted wasn't a complete
> schema, so perhaps you left out things needed for your application to work
> "correctly" (like primary keys, foreign keys, other tables, etc.).
>
>

creating a database

OK,

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

I have a database in Access to keep records for CDs. The tables are as below
Artist
ArtID - 1,2,3...
ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai...
Genre
GenID - 1,2,3...
Genre - Pop, Rock, Hip Hop, Jazz...
CDType
CDTypeID - 1,2,3...
CDType - Album CD, Single CD, Double Cd, limited edition...
MainCDInformation
LineID - 1,2,3...
Artist - Linked with ArtID
CDTitle - Name of Album
CDType - linked with CDTypeID
Genre - Linked with GenID
Cost - User can enter a cost
Notes - User can enter addtional notes
I am getting conflicting advise whether this approach is correct or not the
way this database has been designed. Would you guys agree to it or not? If
not how would you create it and what rules would you follow?
Thanks in advance
"Shelly" <Shelly@.discussions.microsoft.com> wrote in message
news:8D19B7CF-EBC4-4894-81D3-1D6E5B19B140@.microsoft.com...
>I have a database in Access to keep records for CDs. The tables are as
>below
> Artist
> ArtID - 1,2,3...
> ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai...
> Genre
> GenID - 1,2,3...
> Genre - Pop, Rock, Hip Hop, Jazz...
> CDType
> CDTypeID - 1,2,3...
> CDType - Album CD, Single CD, Double Cd, limited edition...
> MainCDInformation
> LineID - 1,2,3...
> Artist - Linked with ArtID
> CDTitle - Name of Album
> CDType - linked with CDTypeID
> Genre - Linked with GenID
> Cost - User can enter a cost
> Notes - User can enter addtional notes
> I am getting conflicting advise whether this approach is correct or not
> the
> way this database has been designed. Would you guys agree to it or not? If
> not how would you create it and what rules would you follow?
"Correct" is defined by the user. No schema is ever perfect and there are
frequently times when a schema is compromised in some way to achieve some
objective. Does that make the schema incorrect? If it works for you and
satisifies your requirements, why do you care if others think it is not
"correct"? If you are getting advice about the schema that is useful or
helps meet your requirements in some "better" way, then incorporate it. It
would help to have some examples of this advice to offer meaningful ... umm
... advice.
In any event, a more complete definition of the tables (and how they are
intended to be used) is required before anyone can make useful suggestions.
If you want comments:
* You have focused on a single medium (CD vs DVD, tape, vinyl, etc.).
* You appear to have overlooked the fact that an album may have multiple
artists or that an album may have no actual designated artist (insert late
night party-time CD commercial reference here).
* Notes? Your system supports a single user?
* You appear to be focused on music only. What about books or other types
of content?
* Is it not possible to have a limited edition double CD? CDType seems to
be a confusion of mutiple attributes.
If you really want to go nuts, then go visit some audiophile sites - you are
just scratching the surface of music content information. It may help to
explore some online music sites to see what information they provide, how
they organize, and how they link (or reference) related information.
Alternatively, have you considered purchasing this type of software - there
must be hundreds (if not thousands) of music cataloging programs (assuming
that is the purpose of this schema).
|||Thanks for your advise. Sorry for the basic format of the table. This was
merely an idea to give you guys rather than having a lengthy page with tables
and fields.
I agree with what you have wrote but the issue is in Access this database is
acceptable. Only 1 user enters the details. For me to retrieve information
lets say about the Artist and the album title i would have a query like
Artist.ArtistName with CDTitle.MainCDInformation
(After the "." is the table name). Now this approach works. When I upgrade
this DB to SQL 2005 - all ok here too. The problem occurs when i create an
application in ASP .Net as the TableAdapter Preview looks correct but when
generating the same information (Artist and Cd Title) and then running this
application I have some data missing from fields that are joined to other
tables and cant figure out why.
Hope this makes some sense if not please let me know. If you wish i can post
a snippet of the SQL code it generates.
Thanks
"Scott Morris" wrote:

> "Shelly" <Shelly@.discussions.microsoft.com> wrote in message
> news:8D19B7CF-EBC4-4894-81D3-1D6E5B19B140@.microsoft.com...
> "Correct" is defined by the user. No schema is ever perfect and there are
> frequently times when a schema is compromised in some way to achieve some
> objective. Does that make the schema incorrect? If it works for you and
> satisifies your requirements, why do you care if others think it is not
> "correct"? If you are getting advice about the schema that is useful or
> helps meet your requirements in some "better" way, then incorporate it. It
> would help to have some examples of this advice to offer meaningful ... umm
> ... advice.
> In any event, a more complete definition of the tables (and how they are
> intended to be used) is required before anyone can make useful suggestions.
> If you want comments:
> * You have focused on a single medium (CD vs DVD, tape, vinyl, etc.).
> * You appear to have overlooked the fact that an album may have multiple
> artists or that an album may have no actual designated artist (insert late
> night party-time CD commercial reference here).
> * Notes? Your system supports a single user?
> * You appear to be focused on music only. What about books or other types
> of content?
> * Is it not possible to have a limited edition double CD? CDType seems to
> be a confusion of mutiple attributes.
> If you really want to go nuts, then go visit some audiophile sites - you are
> just scratching the surface of music content information. It may help to
> explore some online music sites to see what information they provide, how
> they organize, and how they link (or reference) related information.
> Alternatively, have you considered purchasing this type of software - there
> must be hundreds (if not thousands) of music cataloging programs (assuming
> that is the purpose of this schema).
>
>
|||> (After the "." is the table name). Now this approach works. When I upgrade
> this DB to SQL 2005 - all ok here too. The problem occurs when i create an
> application in ASP .Net as the TableAdapter Preview looks correct but when
> generating the same information (Artist and Cd Title) and then running
> this
> application I have some data missing from fields that are joined to other
> tables and cant figure out why.
> Hope this makes some sense if not please let me know. If you wish i can
> post
> a snippet of the SQL code it generates.
Not really. You've gone from a relatively simply schema question to an
Access->SQL Server/ASP migration issue. If the actual schema is not an
issue (i.e., it stores what you need and you can get the information you
want from it), then you should probably post details of the actual problem
to a NG that is focused on ASP.
|||I thought it wouldnt which is why i wanted to keep this a simple question.
However now you can understand why i was questioning if the DB structure i
had was correct or not and why some say it was incorrect (since it doesnt
show up correctly when the application runs but does otherwise).
I have already asked in an ASP .Net group, which is why i asked here to see
if someone could shed some light here or not :-).
"Scott Morris" wrote:

> Not really. You've gone from a relatively simply schema question to an
> Access->SQL Server/ASP migration issue. If the actual schema is not an
> issue (i.e., it stores what you need and you can get the information you
> want from it), then you should probably post details of the actual problem
> to a NG that is focused on ASP.
>
>
|||"Shelly" <Shelly@.discussions.microsoft.com> wrote in message
news:BBF6E91D-F447-4384-8995-E0D2AE814619@.microsoft.com...
>I thought it wouldnt which is why i wanted to keep this a simple question.
> However now you can understand why i was questioning if the DB structure i
> had was correct or not and why some say it was incorrect (since it doesnt
> show up correctly when the application runs but does otherwise).
IMO, the clue here is that it "doesnt show up correctly when the application
runs but does otherwise". I think you should give some serious thought to
the code you are using and, to a lesser degree, the queries it generates to
access the database. In your previous post, you also mentioned "joining to
other tables". This also leads me to believe the issue is with the code,
not the schema. The information you previously posted wasn't a complete
schema, so perhaps you left out things needed for your application to work
"correctly" (like primary keys, foreign keys, other tables, etc.).
|||I had a feeling you might have said its code related as soon as i clicked
Post on my last response. Its definately not the code as other DB works fine
using this code also i tested the code which was provided with ASP .Net
tutorials.
I can post a detailed database structure if you think it could be something
else. Its a tough one as thsi DB works fine with Access - the code works fine
with other databases and when i generate the code under the TableAdapter i
can view the results and they show up correctly (A TableAdapter is a bit like
creating a query in design mode in SQL - sorry i added this incase you wasnt
aware of what a TableAdapter is - if you do, my apologies)
Thanks
"Scott Morris" wrote:

> "Shelly" <Shelly@.discussions.microsoft.com> wrote in message
> news:BBF6E91D-F447-4384-8995-E0D2AE814619@.microsoft.com...
> IMO, the clue here is that it "doesnt show up correctly when the application
> runs but does otherwise". I think you should give some serious thought to
> the code you are using and, to a lesser degree, the queries it generates to
> access the database. In your previous post, you also mentioned "joining to
> other tables". This also leads me to believe the issue is with the code,
> not the schema. The information you previously posted wasn't a complete
> schema, so perhaps you left out things needed for your application to work
> "correctly" (like primary keys, foreign keys, other tables, etc.).
>
>

Saturday, February 25, 2012

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

Will someone pleeese answer this post!

Blog Archive

About Me