Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98Please don't re-post. I provided an answer in your prior post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>|||Hi Bill,
Ok then 'Why the go statement didn't work'
The reason is that the GO statement only works through
osql and isql (or in this case Query Analyser), as your
running this through VB, the go statement isn't supported
so it doesn't work.
The second error is a standard sql error, which I don't
think has anything to do with the go statement.
Could you possibly post the first 30 lines of your code ?
Thanks
J
>--Original Message--
>I know that most of the persons could not get me exactly
>so i am again going to clarify my problem.
>I want to create tables and views from inside the VB-code
>using SQL-script. I can do this by reading a text
(script)
>file, storing the string in a variable say MYSQLSTR and
>running it like CN.EXECUTE MYSQLSTR inside my code. Now I
>have created the sql-script from enterprise manager which
>includes GO for each create statement of the table. I run
>it from inside my VB-Code. error raised which says 'GO'
is
>not recognizable. OK. I removed 'GO' from the file and
run
>it again as i described above. It is running fine and
>creating tables in the database. This file, after
removing
>all the 'GOs' runs inside the query analyser too.
>Now when I do this with VIEWS, same problems come and I
>remove 'GO' but this time it says like (even inside query
>analyser):
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>10
>Incorrect syntax near the keyword 'SET'.
>Server: Msg 111, Level 15, State 1, Procedure DmView1,
>Line 16
>'CREATE VIEW' must be the first statement in a query
batch.
>Now I have to create views at one go as i create all the
>tables at one go by running the SQL-script through my
code.
>ur suggestions are welcome.
>Billi98
>.
>

Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98
Please don't re-post. I provided an answer in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>

Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98Please don't re-post. I provided an answer in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>sql

Receiving errors when using foreach loop and excel connection manager...

Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.

I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.

After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.

Creating a Foreach Loop Container:

1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:\Clients\Dep Comm\BEA\BEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.

I. Created Excel Connection:

1. Created excel connection called, “Dynamic Excel Connection Manager,” that initially pointed to one of the excel workbooks.
2. Went to the connection properties by right clicking the connection manager.
3. Expanded Expressions and clicked the ellipsis button to bring up property expressions
4. Chose Connection String in the Property.
5. Clicked the Expression Ellipsis button.
6. Put the following inside the Expression multi line text box:

A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

7. Clicked the Evaluate Expression button to get the following:
   1. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties=""
8. Clicked Ok button
9. Inserted a Data flow task inside the foreach loop container.

II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:

1. Set the Foreach loop container Delay Validation to true.
2. Set the Data Flow Task Container Delay Validation to true.
3. Set the Dynamic Excel Connection Manager Delay Validation to true.
4. Set the SQL Server Connection Manager Delay Validation to true.
5. Set the Package Delay Validation to true.
6. Package Locale ID set to English

Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.

Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.Source: "Microsoft JET Database Engine"Hresult: 0x80004005Description: "Could not find installable ISAM.".

I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile]

I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=

I ran the package and received the following error in part: OLE DB record is available.Source: "Microsoft JET Database Engine"Hresult: 0x80004005Description: "Unrecognized database format 'E:\Clients\Dep Comm\BEA\BEA_Test_Source\PersonnelExpense_OCCs_051007.xls'."

I did not find anything helpful when I searched for the above errors and would very much appreciate anyone’s assistance on this issue as this issue needs to be taken care of ASAP.

Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?

Your assistance in this matter is truly appreicated!

Thanks!!

Lee

Are there headers on the Excel spreadsheets?

The error is looking for a database format where the headers are matched as column headers.

If there are no headers, it doesnt' know how to match it.

Just my twist on it,

Adamus

|||

Hi Adamus,

I appreciate your feedback. To answer your question, the first row does contain column headers.The data flow task has an excel source file which uses the Dynamic Excel Connection manager that I mentioned in my original post. The excel source file connects to a derived column data task that creates derived columns and changes the data type to reflect that of the destination. The derived columns is then connected to the SQL Server destinationation table that matches the excel header columns to the destination table columns.

What should I do with the connection string that I mentioned in my first post? I have provided it here for you or anyone else to review.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

Can you or anyone else tell me what I am doing wrong here?

Thanks!!

Lee

|||

Can you actually import at least one single file? forget about the foreach loop and the expression in the connection manager.

I just did a search on 'Could not find installable ISAM ' and found this:

http://support.microsoft.com/kb/209805

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=477902&SiteID=1

So, before going deeper, try to connect to a single execel file.

BTW, I just found a test package I created long time ago, and I see some differences with your approach:

I have only one variable in my for each loop container to hold the file name; and since I configured the loop container to retrive 'Fully Qualified" file name; the variable contains the path and the file name. In the excel connection manager I applied an expression to the ExcelFilePath property (not to the Connection String). The expression is just: @.{User::VarFileName] ; which is the variable I use in the loop container. I don't mess with the extended properties.sql

Rebuildix clutered index on different field

Hello!
I have got a table with clustered key defined on primary key. This tables is
referenced using FK by hundreds of other tables using its primary key. I am
trying to build clustered index on different column in the most efficient
way. It looks like I have to drop PK constraint first before changing
clustered index. This also means I have to drop/recreate all FK constraints
which could be time consuming. I suppose disabling constraints wouldn't
help. Is there quickest way of changing clustered index?
Thanks,
IgorHi
As there can only be one clustered index on a table I think you will need to
drop it which would require all FKs referencing it to be dropped first,
although alternatively you may want to create an indexed view that can have
it's own clustered index.
John
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eftV0UGPFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I have got a table with clustered key defined on primary key. This tables
> is referenced using FK by hundreds of other tables using its primary key.
> I am trying to build clustered index on different column in the most
> efficient way. It looks like I have to drop PK constraint first before
> changing clustered index. This also means I have to drop/recreate all FK
> constraints which could be time consuming. I suppose disabling constraints
> wouldn't help. Is there quickest way of changing clustered index?
> Thanks,
> Igor
>|||Thanks, John. I have come up with script that drops recreates FK
automatically.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23qxCINOPFHA.1932@.tk2msftngp13.phx.gbl...
> Hi
> As there can only be one clustered index on a table I think you will need
> to drop it which would require all FKs referencing it to be dropped first,
> although alternatively you may want to create an indexed view that can
> have it's own clustered index.
> John
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eftV0UGPFHA.3356@.TK2MSFTNGP12.phx.gbl...
>

Rebuilding Indexes

I am trying to rebuild indexes on system tables by using a script that will be called from a batch job. Does anyone have any ideas of how to do this? I know DBCC DBREINDEX does not support use on sytem tables.What version of SQL?

I'm also wondering why you want to do this. I've never even thought about it. SQL 2000 is supposed to update this stuff automatically. Maybe I shouldn't AssUme? hmm.

I really can't find anything on the subject.|||Never ever play with system tables using DBCCs, even MS discourages and does not support to do so.

As referred why do you want screw up your system?

Rebuilding dimensions with huge fact tables

Hi,

I have a dimension which is used in two Measure Groups. It has many levels (let's say 10). One fact table handles level 1-8, the other 8-10.

The facts are huge, about 2 billion records for the first table (for level 1-8) and 100 million records for 8-10.

We discussed a fitting partitioning schema and this will not be the big issue.

Our problem is, that in the last levels (9-10) members can move from one parent to another. That means that the dimension structure changes, it has to be updated which also means a reprocession of all partitions related to this dimension.

This is really a problem because of the first fact table (2 billion records). I shouldn't be that big deal for the second table.

My question is: Since the structure of level 1-8 is quite fixed is there any way to get around reprocessing the partitions of the first fact table? Their aggregations will not change! It's only the aggregations of the lower level which are not stored in that fact table!

Any idea?

You have a classic case of slowly changing dimension. I dont remember which number 1, 2, or 3 given to this case. You can search for a term "slowly changing dimensions" you should get a lot of information for this design.

To make a story short: You can avoid re-processing your partitions by doing Update to dimension instead of Full Process. When issuing ProcessUpdate for your dimension, you can still get your members moving from one parent to another, but the data in the partitions will not be lost.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Edward,

thanks for your reply. This is a SCD Type 1 scenario since I don't save any history...

Are you sure that an update of a dimension doen't affect the aggregations? That's what BOL says:

Process Update

Forces a re-read of data and an update of dimension attributes. Flexible aggregations and indexes on related partitions will be dropped. For example, this processing option can add new members to a dimension and force a complete re-read of the data to update object attributes. This processing option is supported for dimensions and mining models.

So the question might be what "flexible aggregations" are?! But I guess that all aggregations are flexible...

Regards,

|||

That correct. Updating of dimension will cause Analysis Server dropping flexible aggregations.

But that is way different from what your initial concern of having fully to re-process all the partitions in your cube.
Processing of aggregations will not require Analysis Server to read data again from relational database. Aggregations are built based on the data that already exists in your partitions.

If you think of it. It is logical that Analysis Server drops aggregations. Aggregations are being pre-calculated totals are no longer valid as soon as you move memeber from one parent to another.

Now about difference between flexible and rigid aggregations. It has to do with exactly the topic of this discussion. When defining relationship between attributes as flexible, you're telling the server that members of the child attribute could move from one parent to another and therefore any aggregaton build based on these attribures will be dropped in case of incremental update. You have an option to mark relationship as ridid. In this case if you try to move the member, you will get an error during processing.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Edward,

thanks for your reply, things are getting clearer now...

So if you have a processed partition with aggregations defined and you do a "process update" of one or more dimensions, you'll still have the data in the partitions but then without aggregations and indices. I guess you can still query the cube but it will be slow. To speed it up again you have to do a "process index" to recreate aggregations (I still get a litte bit confused about idecies and aggregations, I understand that you always have indeces but you can add aggregations in a partition if you want to).

How big is the performance inpact on having flexible dimensions (if there is any)?

So it's right that aggregations on rigid relationships are not dropped in an update?! So having them i.e. on a time dimension keeps aggregations on time even when you "process update" the dimension?! What about other dimensions? If you "process update" your product dimension are aggregations on the region dimension also dropped? You only wrote that you get an error when you move a member in a rigid relationship. But there has to be an advantage as well ;-)

I'm really missing something like a "incremental" update of a dimension... This would mean that keys have to keep the same, only member names can be change. New members can be added but existing member couldn't move. That would be perfect for lots of cases, because you can add new members to a dimension without doing any processing of aggregations, just because any existing data will not be changed. But you can i.e. process the partition with the "actual" data and then you could take advantage of the new added members...

Thanks,

|||

So if you have a processed partition with aggregations defined and you do a "process update" of one or more dimensions, you'll still have the data in the partitions but then without aggregations and indices. I guess you can still query the cube but it will be slow. To speed it up again you have to do a "process index" to recreate aggregations (I still get a litte bit confused about idecies and aggregations, I understand that you always have indeces but you can add aggregations in a partition if you want to).

<E.M> You got this one right.

How big is the performance inpact on having flexible dimensions (if there is any)?

<E.M> There is no performance difference per-se between flexible and rigid aggregations. It is just that flexible once are being dropped during update of the dimension.

So it's right that aggregations on rigid relationships are not dropped in an update?! So having them i.e. on a time dimension keeps aggregations on time even when you "process update" the dimension?!

<E.M> Right here.

What about other dimensions? If you "process update" your product dimension are aggregations on the region dimension also dropped? You only wrote that you get an error when you move a member in a rigid relationship. But there has to be an advantage as well ;-)

<E.M> The aggregations are not per dimension. A single aggregation could will be defined to have aggregates across several dimensions. For instance totals for Product category and Year could be one aggregation. For aggregation to be rigid, it should be based on attributes with Rigid relationships across all dimensions "participating" in it.

I'm really missing something like a "incremental" update of a dimension... This would mean that keys have to keep the same, only member names can be change. New members can be added but existing member couldn't move. That would be perfect for lots of cases, because you can add new members to a dimension without doing any processing of aggregations, just because any existing data will not be changed. But you can i.e. process the partition with the "actual" data and then you could take advantage of the new added members...

<E.M> You are correct here again. Many users find "incremental" update of the dimension very useful. But in many cases you still need to have ability to move memeber from one parent to another, delete a member. Sometimes you have a lot of historical data in many partitions and cannot afford to re-process.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

Rebuild index issue - - strange

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:

Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned........................: 184867
- Extents Scanned.......................: 23203
- Extent Switches.......................: 23324
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..............: 11.13%
- Extent Scan Fragmentation ...............: 35.46%
- Avg. Bytes Free per Page................: 60.0
- Avg. Page Density (full)................: 99.26%

Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned........................: 303177
- Extents Scanned.......................: 37964
- Extent Switches.......................: 42579
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..............: 43.19%
- Extent Scan Fragmentation ...............: 24.78%
- Avg. Bytes Free per Page................: 75.1
- Avg. Page Density (full)................: 99.07%

Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***if you look at your database ID's, they are different. did you run
this on 2 different databases?

Raziq Shekha wrote:
> Hi Folks,
> SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
> the command :
> dbcc dbreindex ('tablename')
> go
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
>
> Second output is from after the reindex:
>
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
> - Extents Scanned.......................: 37964
> - Extent Switches.......................: 42579
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
> - Logical Scan Fragmentation ..............: 43.19%
> - Extent Scan Fragmentation ...............: 24.78%
> - Avg. Bytes Free per Page................: 75.1
> - Avg. Page Density (full)................: 99.07%
>
> Following are my concerns:
> The following numbers are all higher after reindex than before reindex:
> pages scanned, extent switches, logical scan fragmentation, avg bytes
> free per page, avg page density.
> scan density is lower after reindex than before reindex
> Seems to me that the numbers that are higher after reindex should be
> lower and numbers that are lower after reindex should be higher? I
> didn't specify the fill factor in the dbcc reindex command so it should
> have used the default fill factor. The fill factor has never been
> changed on this machine.
> Am I missing something?
> Thanks,
> Raziq.
> *** Sent via Developersdex http://www.developersdex.com ***

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certain
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite different
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
Eric
Eric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>
|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE for
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:

> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> certain
> INDEXDEFRAG
> problem
> good
> DTS
> different
> comparing
> hit
>
>

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certai
n
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite differen
t
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
EricEric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE fo
r
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:

> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> certain
> INDEXDEFRAG
> problem
> good
> DTS
> different
> comparing
> hit
>
>

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certain
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite different
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
EricEric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE for
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:
> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> > Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
> certain
> > query works against some datasets within a couple tables, but mysteriously
> > never finishes against other datasets, perhaps some recently loaded ones.
> > It's mainly just one big table with some smaller auxiliary tables (used in
> > this query).
> >
> > I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
> INDEXDEFRAG
> > without effect. However, if I do a DTS "Copy objects and data between SQL
> > Server databases" of the offending database to a fresh database, the
> problem
> > goes away (without touching indexes or anything).
> >
> > As a further test, I restored a backup of the offending database onto a
> > different server, tried the query and it never returned, did the DTS copy
> > objects, queried again and it's fine. Past experience has shown that this
> > fresh, fixed database works fine for a while but can re-offend after a
> good
> > bit more data are loaded. It won't always be feasible to try this silly
> DTS
> > trick. The query plans for the query in the two databases are quite
> different
> > but I don't know enough to discern what the problem might be from
> comparing
> > them.
> >
> > I've searched around but cannot see a similar issue, but maybe I haven't
> hit
> > upon the right search keywords.
> >
> > Has anyone heard of or encountered such a problem? or other suggestions?
> >
> > thanks,
> > Eric
> >
>
>

Reason for using Filegroup

Vendor has produced a Read Only system for us in SQL
Server 2000.
The database size is around 10GB and there are around 2000
tables in it.
I find that they have configured the database to use 5
files in the primary filegroup with size of 2GB each. All
of them are located in a RAID 1 disk. The autogrow is not
enabled for that database as it is READ ONLY.
I would like to know what is the benefit of placing them
in 5 files in a filegroup instead of 1 single physical
file ? Is it due to performance issue ?
ThanksThis is a multi-part message in MIME format.
--020103050106080203040701
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
If they're all on the same spindle then there is no benefit. They may
as well have had them in a single file.
The main reason for multiple files in a filegroup is improving
performance by putting the files on different spindles (and also when
you get low on disk space on one logical volume you can seamlessly
"extend" the filegroup by adding another file to it that resides on a
different logical volume (presumedly with more disk space)).
Perhaps the vendor meant to actually locate the 5 physical files on 5
separate RAID arrays but...forgot?
--
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Stephen wrote:
>Vendor has produced a Read Only system for us in SQL
>Server 2000.
>The database size is around 10GB and there are around 2000
>tables in it.
>I find that they have configured the database to use 5
>files in the primary filegroup with size of 2GB each. All
>of them are located in a RAID 1 disk. The autogrow is not
>enabled for that database as it is READ ONLY.
>I would like to know what is the benefit of placing them
>in 5 files in a filegroup instead of 1 single physical
>file ? Is it due to performance issue ?
>Thanks
>
--020103050106080203040701
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>If they're all on the same spindle then there is no benefit. They
may as well have had them in a single file.<br>
<br>
The main reason for multiple files in a filegroup is improving
performance by putting the files on different spindles (and also when
you get low on disk space on one logical volume you can seamlessly
"extend" the filegroup by adding another file to it that resides on a
different logical volume (presumedly with more disk space)).<br>
<br>
Perhaps the vendor meant to actually locate the 5 physical files on 5
separate RAID arrays but...forgot?<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<em><font face="Tahoma" size="2"> mallesons</font><font face="Tahoma"> </font><font
face="Tahoma" size="2">stephen</font><font face="Tahoma"> </font><font
face="Tahoma" size="2"> jaques</font></em><font face="Tahoma"><br>
</font><font face="Tahoma" size="2">blog:</font><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=/">http://sqlnerd.blogspot.com">
http://sqlnerd.blogspot.com</a></font></span> </p>
</div>
<br>
<br>
Stephen wrote:
<blockquote cite="mid033901c57851$56fa1040$a501280a@.phx.gbl" type="cite">
<pre wrap="">Vendor has produced a Read Only system for us in SQL
Server 2000.
The database size is around 10GB and there are around 2000
tables in it.
I find that they have configured the database to use 5
files in the primary filegroup with size of 2GB each. All
of them are located in a RAID 1 disk. The autogrow is not
enabled for that database as it is READ ONLY.
I would like to know what is the benefit of placing them
in 5 files in a filegroup instead of 1 single physical
file ? Is it due to performance issue ?
Thanks
</pre>
</blockquote>
</body>
</html>
--020103050106080203040701--

Realtional Tables

For starters, please forgive my newbie'ness as SQL is not my strongest area in any sense.

I am trying to create a web application which consists of a DDL which is databound and based on the selection in the 1st DDL, a gridview should be generated and populated with the criteria I'm looking for.

My trouble starts with the database. I have 2 tables that I'm working with - users and messages.

The users table consists of 2 columns; userid and useruri. The messages table consists of 2 tables; userid, body.

The first drop down list is populated from a database call of SELECT useruri, userid FROM users. The output of this DDL is simply each user's name. This works pefectly.

To build the gridview or output (because i'm not sure if gridview is the right word), the second table, messages, needs to be queried but I can't figure out how to take the userid from the users table and match it to the userid of the messages table, based on the selection in the DDL of the useruri, to build the view.

Does that make sense? Any help would be greatly appreciated.

Thanks,

Casey

You could use SqlDataSource controls and do it something like this:

User:<asp:dropdownlist id="ddlUsers" runat="server" autopostback="True" datasourceid="sdsUsers"datatextfield="Name" datavaluefield="Id" /><asp:sqldatasource id="sdsUsers" runat="server"connectionstring="<%$ ConnectionStrings:ed_testConnectionString%>" selectcommand="SELECT * FROM [User]"></asp:sqldatasource>Message:<asp:dropdownlist id="ddlMessages" runat="server" datasourceid="sdsMessages" datatextfield="Message"datavaluefield="msgId" /><asp:sqldatasource id="sdsMessages" runat="server" connectionstring="<%$ ConnectionStrings:ed_testConnectionString%>"selectcommand="SELECT [msgId], [Message] FROM [Message] WHERE ([Id] = @.Id)"><selectparameters><asp:controlparameter controlid="ddlUsers" name="Id" propertyname="SelectedValue"type="Int32" /></selectparameters></asp:sqldatasource>

Really complex query that has me stymied

I'm attempting to write a query that pulls data from two tables in a really
complex way, and I can't figure a way around it. Because of the architecture
in use, it has to be done in a single query, without using a stored
procedure. This is a simplified version of what I'm doing:
Let's say I have two tables, each with four columns, named A, B, C and D:
Table 1
A,B,C,D
1,2,3,5
3,5,7,9
3,9,2,6
3,6,7,1
9,8,3,0
2,5,8,1
Table 2
A,B,C,D
5,3,2,3
4,8,7,1
3,5,7,2
1,4,5,6
8,9,3,2
2,6,2,2
Each of the tables has thousands of records. I need a query that does this:
For each row in table 1, look up the values in table 2. If a row can be
found in table 2 whose columns A, B and C match columns A, B and C in table
1, and whose column D equals 2, then return the row from table 1. Do not
return a row unless all three columns A, B and C match, and Table2.D equals
2.
Using this rule on the table values above, it should be returning only the
row "3,5,7,9" from Table 1 because there is only one row in Table 2 that has
columns A,B,C,D equal to "3,5,7,2"
Here's what I'm trying now:
SELECT * FROM Table1
WHERE
Table1.A IN (SELECT DISTINCT(A) FROM Table2 WHERE Table2.D = 2)
AND
Table1.B IN (SELECT DISTINCT(B) FROM Table2 WHERE Table2.D = 2)
AND
Table1.C IN (SELECT DISTINCT(C) FROM Table2 WHERE Table2.D = 2)
This returns 3,5,7,9, 3,9,2,6 and 3,6,7,1 from Table 1, when it should only
be returning 3,5,7,9. It does this because column A matches (3), Column B
matches multiple records in Table 2 with a Table2.D=2, and so does Column C.
Even though I am specifying AND for the three columns, it is actually acting
as an OR, because each SELECT DISTINCT is separate, and does not take into
consideration the other SELECTs. The first SELECT DISTINCT pulls a result
set, and Column A is compared against it. The second SELECT DISTINCT pulls a
different result set, and Column B is compared against it. And so on. This
results in an additive comparison. What I need, in effect, is for the SELECT
DISTINCT(B) to be more like "Take the SELECT DISTINCT(A) result set and pare
it down further using the SELECT DISTINCT(B) values". Then The SELECT
DISTINCT(C) needs to be more like "Take the SELECT DISTINCT(B) result set
and pare it down even further using the SELECT DISTINCT(C) values".
I hope this makes sense, and that someone can see what I'm missing. I've
spent hours trying to figure out a way of doing this, without success.Scott,
Try this:
SELECT A, B, C, D
FROM [Table 1] AS T1
WHERE EXISTS (
SELECT * FROM [Table 2] AS T2
WHERE T2.A = T1.A
AND T2.B = T1.B
AND T2.C = T1.C
AND T2.D = 2
)
or
SELECT T1.A, T1.B, T1.C, T1.D
FROM [Table 1] AS T1
JOIN [Table 2] AS T2
ON T2.A = T1.A
AND T2.B = T1.B
AND T2.C = T1.C
WHERE
T2.D = 2
The latter query will return duplicate rows, so it could need
DISTINCT. Use whichever is faster (hopefully at least one
has no typos).
Steve Kass
Drew University
Scott MacLean wrote:

>I'm attempting to write a query that pulls data from two tables in a really
>complex way, and I can't figure a way around it. Because of the architectur
e
>in use, it has to be done in a single query, without using a stored
>procedure. This is a simplified version of what I'm doing:
>Let's say I have two tables, each with four columns, named A, B, C and D:
>Table 1
>A,B,C,D
>1,2,3,5
>3,5,7,9
>3,9,2,6
>3,6,7,1
>9,8,3,0
>2,5,8,1
>Table 2
>A,B,C,D
>5,3,2,3
>4,8,7,1
>3,5,7,2
>1,4,5,6
>8,9,3,2
>2,6,2,2
>Each of the tables has thousands of records. I need a query that does this:
>For each row in table 1, look up the values in table 2. If a row can be
>found in table 2 whose columns A, B and C match columns A, B and C in table
>1, and whose column D equals 2, then return the row from table 1. Do not
>return a row unless all three columns A, B and C match, and Table2.D equals
>2.
>Using this rule on the table values above, it should be returning only the
>row "3,5,7,9" from Table 1 because there is only one row in Table 2 that ha
s
>columns A,B,C,D equal to "3,5,7,2"
>Here's what I'm trying now:
>SELECT * FROM Table1
>WHERE
> Table1.A IN (SELECT DISTINCT(A) FROM Table2 WHERE Table2.D = 2)
>AND
> Table1.B IN (SELECT DISTINCT(B) FROM Table2 WHERE Table2.D = 2)
>AND
> Table1.C IN (SELECT DISTINCT(C) FROM Table2 WHERE Table2.D = 2)
>This returns 3,5,7,9, 3,9,2,6 and 3,6,7,1 from Table 1, when it should only
>be returning 3,5,7,9. It does this because column A matches (3), Column B
>matches multiple records in Table 2 with a Table2.D=2, and so does Column C
.
>Even though I am specifying AND for the three columns, it is actually actin
g
>as an OR, because each SELECT DISTINCT is separate, and does not take into
>consideration the other SELECTs. The first SELECT DISTINCT pulls a result
>set, and Column A is compared against it. The second SELECT DISTINCT pulls
a
>different result set, and Column B is compared against it. And so on. This
>results in an additive comparison. What I need, in effect, is for the SELEC
T
>DISTINCT(B) to be more like "Take the SELECT DISTINCT(A) result set and par
e
>it down further using the SELECT DISTINCT(B) values". Then The SELECT
>DISTINCT(C) needs to be more like "Take the SELECT DISTINCT(B) result set
>and pare it down even further using the SELECT DISTINCT(C) values".
>I hope this makes sense, and that someone can see what I'm missing. I've
>spent hours trying to figure out a way of doing this, without success.
>
>|||Hello, Scott
This should do the job:
SELECT * FROM Table1 T1
WHERE EXISTS (
SELECT * FROM Table2 T2
WHERE T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
AND T2.D=2
)
Razvan|||Thanks Steve and Razvan, this did the trick. Once I saw this I thought; "of
COURSE" (hand slap on forehead).
Thanks again.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1110347824.072514.140200@.z14g2000cwz.googlegroups.com...
> Hello, Scott
> This should do the job:
> SELECT * FROM Table1 T1
> WHERE EXISTS (
> SELECT * FROM Table2 T2
> WHERE T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
> AND T2.D=2
> )
> Razvan
>

'Real only' Cells in SQL Server Management Studio / VS 2005

I have been using both SQL Server Management Studio and the Server Explorer of Visual Studio 2005 to populate my database tables as I write my application. Basically, I right-click on a table name and select "Open Table' in SMS and select 'View Table Data' in VS 2005. From there I normally just edit data as needed.

Today, I open up a particular table and one column of my table is made read-only, meaning I cannot update that cell in any records and I cannot add any new records. I can tell this column is read-only because the data is faded and 'Cell is Read only' is visible in the status pane under the results when I mouse-over that column. These read-only columns seem to pop-up almost at random. At work the other day a similar thing happened to anther table I was trying to modify.

It just so happens that the read-only column is the Primary Key of this particular table, but even when I remove the Primary Key designation I cannot edit data in this column. In addition, I have other tables with Primary Keys and I have no trouble updating data in those table columns.

This most certainly has me pulling my hair out and have been searching online for two days now trying to find the cause of this. Any help would be appreciated.

IMHO editing data directly to the table using the SSMS or VS has always downside for having issues with data commit. You will be able to control the transaction commit or rollback is the biggest advantage in using TSQL instead. THe behaviour you are getting is opening up in VS is considered as a view where updates are not allowed, fyi http://msdn2.microsoft.com/en-us/library/ms172022(VS.80).aspx and may be look at the properties to see update speicification to set it to UPDATE.

|||Thanks for the reply. According to this http://msdn2.microsoft.com/en-us/library/sb0bay6k(VS.80).aspx I'm doing it exactly as they explain. At this point I just want to insert a farking row and I don't care how I do it. I've tried to write straight queries and can't even get that to work. I've written command line queries for 4 years now so I know how to write SQL, but I've been screwing with this application for 2 days now just trying to insert a row, its beyond ridiculous. I still cannot find a single resource online that explains to my why I have an entire column that is read-only.

Someone for the love of God just tell me how to insert a row using this steaming pile of BS called Micrrosoft SQL Server Management Studio (or VS 2005). I have a table that looks like this:

StatID int (Primary Key)
SeasonCode char(4)
PlayerID int
StatCode char(3)
StatValue float

and all I want to do is insert a record. Someone please tell me how the fark to insert a row into this table. I don't care what it takes. The column StatID has its Identify Specification column set to 'Yes'.
|||

I had the exact same problem and found out what was causing it in my case.

I started with 'Open Table' which I then wanted to filter so I opened criteria pane. This showed '*' in the columns selected - fine, that's what I expected.

I then selected a specific column to create a filter but didn't bother to unclick the 'display' column and that column became Read Only in the results set.

When I unclicked the 'display' option for the filtered column, and executed it again, the column was editable.

I haven't had time to play around with this but it looks to me that SSMS cannot handle editing on a column that is displayed twice.

Don't ever remember having that problem in EM.

|||

When I unclicked the 'display' option for the filtered column

I meant the 'Output' option