Creating an Index timing out

I am creating an index on a table wit 35 million records but I get the error

'TT_ObjPerformance' table
- Unable to create index 'IX_TT_ObjPerformance_CACode'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How can I get the index created?

Thanks
SQL Server newbie

From where are you creating the index? via query analyzer/job/some front end( hopefully not)?

Creating a View

I have 2 tables. T1 is for current data. T2 is a audit tracking table for T1. There will be several records in T2 for each 1 in T1. T2 has a Action Field that stores the last action and a auditID to record changes on T1.

What I want to do is create a view that shows the current records in T1 and all the records in the audit tabel T2. I can do the Join but this would duplicate all the fields.

I am looking for something like this:

Select 'AuditID' AuditID,TD.*,'Action' Action from TrakrDetails TD
--Union
Select TDA.* from TrakrDetails_Audit TDA
order by AuditID desc

This craps out because there are 2 additional Fields in T2.

Any Suggestions?

Thanks
JonSorry but you have to list out all of the columns...

If you want to show columns that aren't in the other table you can use a literal like space, or you can use a null

SELECT ' ' AS Col1,
, Null As Col2
, Col3 FROM myTable99
UNION ALL
SELECT Col1
, Col2
, Col3
FROM myTable00|||Thanks Brett

Your way works.

Normally I would have done it this way but it seemed like it was the long way around (thats the way it normally goes for me).

I thought there might be a easy way I was missing.

Thanks Again
Jon|||As an aside NEVER use SELECT *

(Except for analysis, never for code...save yourself a lot of pain)

Creating a table from data in a SQL server db

I have a form with a drop down box so the user can select a quote.. When a quote is selected i need to populate a table of all the records associated with the quote id. I need the table to be created in such a way that the user can add new rows, delete rows and edit the data. Then all of the changes need to be written back to the database. Whats the most efficient/best way of doing this and if you have any ideas can you explain them as thoroughly as possible! I'm currently upgrading an access database to a sql server back end with an asp.net client and it's taking me a while to get to grips with all the changes!
Thanks in advance,
Chrisyou could create a dataset/datatable at the front end with the samestructure as the actual table in the db. You can then use thedataset/datatable and do all the modifications and push the entiretable back to the sql db. check out articles about datasets and youwould find enough info.
sql

Creating a loop

Hi

I need to create an SQL table and automatically populate it with 100,000 records (just one column).

How can I achieve this? The create table part is straight forward enough but how can I get all those rows in there using a single script?

I imagine the statement will require Loop and While.

This is for testing purposes.declare @.i int
set @.i=100000
while @.i>0
begin
insert into table_name values (@.i)
set @.i=@.i-1
end|||Much appreciated!
I'm running this against a table I've already created and I'm getting..
"Server: Msg 213, Level 16, State 4, Line 5
Insert Error: Column name or number of supplied values does not match table definition." :confused:|||can u paste the ddl for the table? how many columns does the table have?
replace the insert with:
insert into table_name (column_name) values (@.i)|||No worries - I got it to work in the end - I created a new table with just one column and everything is fine.

Thanks for your help amigo/amiga..|||hi Harshal
I'm hoping you can help with a problem leading on from this. The purpose of this exercise was to measure how long two scenarios take to create the tables and insert records.

Scenario A : Stand alone desktop
Scenario B : Server with dual processor Xeon

The database on each is identical - yet the Desktop took 01:07 to insert compared to the server's 06:48 !! Nearly 7 minutes!

Any ideas what could be causing this??

Cheers

Samsara

Creating a fork in the road

Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?

Question the second: After I aggregate my records (down one of the paths), I need to store some columns as xml. Is there a tool for this?

Thanks for all your help!

Jim Work

Jim Work wrote:

Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?

Use the multicast transformation.|||For "forking" the data like that, you can use the Multicast component in the data flow tab.|||Oh, wow! Thanks a bunch!

Jim Work

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

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

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

Creating "columns" from transaction data

Hi,
I have a transaction table that basically has the following fields
RecId, PeriodId, Quantity (a single RecId can have multiple records, i.e.
quantities in multiple periods)
I need to convert an entire table of these records to one that looks like
this...
RedId , P1Qty, P2Qty, P3Qty, P4Qty etc...
Which has one row per RecId and places the quantity (quantities) in the
appropriate "period" column(s) based on the value(s) of "Period" in
the transaction file for each record.
I've done this before in Access, using the IIF function for each of P1...P4
columns (IIF(Period = 1, Quantity, 0), IIF(Period = 2, Quantity, 0) for
each of the columns of the derived table I was making. This doesn't seem to
work for SQL Server. IIF exists, but I can't get the computed columns to
work properly (Syntax error near "=").
So, I've thought about...
1) Use CreateTable to create my derived table with periods as columns,
2) Write a series of INSERT queries that reads the transaction file for
each possible individual value for "Period" and populates the appropriate
column in the derived table
3) Sum the derived table on every column by RecId
4) Run the whole batch as the SelectCommand of my DataAdapter. The last
command in the batch is Select * from DerivedTable and this is the table
that the DataSet gets.
There has to be a better way to do this?
Thanks.
BBMYou can use CASE expressions instead of IIF. But why would you ever create a
table like this? What you are asking for is a report not a table. Any
reporting tool will construct a cross tab report for you.
David Portas
SQL Server MVP
--|||Do you have any idea what First Normal Form is? You might want to
learn about RDBMS before you write any code.|||Thanks David, CASE was just what I'm looking for.
In this instance, this result set is used as one of the tables in
multi-table DataSet used on a fairly complex display.
Thanks again.
BBM
"David Portas" wrote:

> You can use CASE expressions instead of IIF. But why would you ever create
a
> table like this? What you are asking for is a report not a table. Any
> reporting tool will construct a cross tab report for you.
> --
> David Portas
> SQL Server MVP
> --
>
>|||Yes, in fact I do. I simplified the underlying data structure in my questio
n
to hopefully make it easier to reply to. I was only using the "extra" table
,
because I couldn't figure out how to get the result set I wanted in one pass
.
Thanks for your response anyway.
"--CELKO--" wrote:

> Do you have any idea what First Normal Form is? You might want to
> learn about RDBMS before you write any code.
>

Createing SQL data base record for comma delimted records

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

hi,

have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720600&SiteID=1... you can there find a hint on how to bulk insert using a format file into an existing table, but, again, this is to populate a table object..

if you have to create a brand new database, you have to execute a "CREATE DATABASE newDB" statement, using the syntax provided at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp, then you have to create the table as well or use a SELECT .. INTO statement which creates the destination table at execution time (the table must of course not already exists )

regards

|||

Thank you for the info! I was planning to create the database and file directly in SQL Express! It the stored procedure that will populate the data file that has me hung up! The original file (the CVS - comma delimited file) needs to be inserted into the database file. I seem to know the direction to go--but how to

Once again thank you

|||

hi,

I do apologise and please excuse my poor english, but I'm not able to understand your requirements...

can you please rephrase?

again, please excuse my poor understanding..

regards

|||Guess I am not explaining my problem correctly, my lack of understanding SQL is not helping. I have been tasked to create a data base using SQL Express (this part I think I know how to do) an then write a stored procedure to create a table. The input table is a CSV file that will have up to ten fields in it. Being a CSV file the fields are seperated by comma. After I create this table (with the data in it) I have to display it using VB.NET.|||

hi,

try having a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709813&SiteID=1..

regards

Createing SQL data base record for comma delimted records

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

hi,

have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720600&SiteID=1... you can there find a hint on how to bulk insert using a format file into an existing table, but, again, this is to populate a table object..

if you have to create a brand new database, you have to execute a "CREATE DATABASE newDB" statement, using the syntax provided at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp, then you have to create the table as well or use a SELECT .. INTO statement which creates the destination table at execution time (the table must of course not already exists )

regards

|||

Thank you for the info! I was planning to create the database and file directly in SQL Express! It the stored procedure that will populate the data file that has me hung up! The original file (the CVS - comma delimited file) needs to be inserted into the database file. I seem to know the direction to go--but how to

Once again thank you

|||

hi,

I do apologise and please excuse my poor english, but I'm not able to understand your requirements...

can you please rephrase?

again, please excuse my poor understanding..

regards

|||Guess I am not explaining my problem correctly, my lack of understanding SQL is not helping. I have been tasked to create a data base using SQL Express (this part I think I know how to do) an then write a stored procedure to create a table. The input table is a CSV file that will have up to ten fields in it. Being a CSV file the fields are seperated by comma. After I create this table (with the data in it) I have to display it using VB.NET.|||

hi,

try having a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709813&SiteID=1..

regards