advice on querying large tables

I have to deal with a new database with a number of tables with 100,000+
records. Can anybody give me any advice on the different methods of
querying large tables as opposed to small tables?
Cheers,
Mike
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" <mike.parr@.gmail.com> wrote in message
news:eEhRZu8tFHA.360@.TK2MSFTNGP12.phx.gbl...
>I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
Learn to use the WHERE clause.
Learn how indexes work (in depth) and then build them appropriately for your
queries. Take a look at the index tuning wizard for additional help there.
Only ask for the data you actually need.
This should get you started..
Rick Sawtell
MCT, MCSD, MCDBA|||Learn how to use the Show Execution Plan feature of Query Analyzer.
"Mike P" <mike.parr@.gmail.com> wrote in message
news:eEhRZu8tFHA.360@.TK2MSFTNGP12.phx.gbl...
>I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
>
> Cheers,
> Mike
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Regarding how learning about indexes and execution plans, I have found the
following book to be very helpful:
Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, by
Ken England
"Mike P" wrote:

> I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
>
> Cheers,
> Mike
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes, I have that one too and found it informative.
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:FBBB8707-300C-4510-BE0E-CDE1D4408B75@.microsoft.com...
> Regarding how learning about indexes and execution plans, I have found the
> following book to be very helpful:
> Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, by
> Ken England
> "Mike P" wrote:
>|||I found this book better....
"SQL Server Query: Performance Tuning Distilled" by Sajal Dam (Curlingstone
Publisher)
"JT" wrote:

> Yes, I have that one too and found it informative.
> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:FBBB8707-300C-4510-BE0E-CDE1D4408B75@.microsoft.com...
>
>sql

Advice on database volume

Hi all,
I'm new to datawarehousing. I've built a DW with two fact tables, each one
will be added with more than 5 to 10 millions rows each day. Do you think
that this volume is compatible with Analysis Services 2005 or do I have to
change the model I use, by creating a fact table for each customer I have
(for instance).
Second question: if the awnser of the previous question is that I can keep
the model I've built, will I have to investigate cube partition ?
thank's.
Jerome
You can work with this volume of data in Analysis Services 2005 without
changing your schema (and I strongly suggest you to not change your
schema!).
You can use partitions to handle this volume of data without having a
single big partition that is hard to process if for whatever reasons
you need to full process your cube.
Be careful with aggregation design - you really need preaggregations on
this volume of data.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Jerome wrote:
> Hi all,
> I'm new to datawarehousing. I've built a DW with two fact tables, each one
> will be added with more than 5 to 10 millions rows each day. Do you think
> that this volume is compatible with Analysis Services 2005 or do I have to
> change the model I use, by creating a fact table for each customer I have
> (for instance).
> Second question: if the awnser of the previous question is that I can keep
> the model I've built, will I have to investigate cube partition ?
> thank's.
> Jerome
|||Hi Marco,
What do you mean exactly by "aggregation design"?
What is exactly preaggregation? Do you know some references on the web?
Thanks
Jerome
"Marco Russo" wrote:

> You can work with this volume of data in Analysis Services 2005 without
> changing your schema (and I strongly suggest you to not change your
> schema!).
> You can use partitions to handle this volume of data without having a
> single big partition that is hard to process if for whatever reasons
> you need to full process your cube.
> Be careful with aggregation design - you really need preaggregations on
> this volume of data.
> Marco Russo
> http://www.sqlbi.eu
> http://www.sqljunkies.com/weblog/sqlbi
>
> Jerome wrote:
>
|||yeah aggregation % in Analysis Services
-Susie
Jerome wrote:[vbcol=seagreen]
> Hi Marco,
> What do you mean exactly by "aggregation design"?
> What is exactly preaggregation? Do you know some references on the web?
> Thanks
> Jerome
>
> "Marco Russo" wrote:
|||yeah % aggregation in analysis services
Jerome wrote:[vbcol=seagreen]
> Hi Marco,
> What do you mean exactly by "aggregation design"?
> What is exactly preaggregation? Do you know some references on the web?
> Thanks
> Jerome
>
> "Marco Russo" wrote:

Advice on database volume

Hi all,
I'm new to datawarehousing. I've built a DW with two fact tables, each one
will be added with more than 5 to 10 millions rows each day. Do you think
that this volume is compatible with Analysis Services 2005 or do I have to
change the model I use, by creating a fact table for each customer I have
(for instance).
Second question: if the awnser of the previous question is that I can keep
the model I've built, will I have to investigate cube partition ?
thank's.
JeromeYou can work with this volume of data in Analysis Services 2005 without
changing your schema (and I strongly suggest you to not change your
schema!).
You can use partitions to handle this volume of data without having a
single big partition that is hard to process if for whatever reasons
you need to full process your cube.
Be careful with aggregation design - you really need preaggregations on
this volume of data.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Jerome wrote:
> Hi all,
> I'm new to datawarehousing. I've built a DW with two fact tables, each one
> will be added with more than 5 to 10 millions rows each day. Do you think
> that this volume is compatible with Analysis Services 2005 or do I have to
> change the model I use, by creating a fact table for each customer I have
> (for instance).
> Second question: if the awnser of the previous question is that I can keep
> the model I've built, will I have to investigate cube partition ?
> thank's.
> Jerome|||Hi Marco,
What do you mean exactly by "aggregation design"?
What is exactly preaggregation? Do you know some references on the web?
Thanks
Jerome
"Marco Russo" wrote:

> You can work with this volume of data in Analysis Services 2005 without
> changing your schema (and I strongly suggest you to not change your
> schema!).
> You can use partitions to handle this volume of data without having a
> single big partition that is hard to process if for whatever reasons
> you need to full process your cube.
> Be careful with aggregation design - you really need preaggregations on
> this volume of data.
> Marco Russo
> http://www.sqlbi.eu
> http://www.sqljunkies.com/weblog/sqlbi
>
> Jerome wrote:
>|||yeah aggregation % in Analysis Services
-Susie
Jerome wrote:[vbcol=seagreen]
> Hi Marco,
> What do you mean exactly by "aggregation design"?
> What is exactly preaggregation? Do you know some references on the web?
> Thanks
> Jerome
>
> "Marco Russo" wrote:
>|||yeah % aggregation in analysis services
Jerome wrote:[vbcol=seagreen]
> Hi Marco,
> What do you mean exactly by "aggregation design"?
> What is exactly preaggregation? Do you know some references on the web?
> Thanks
> Jerome
>
> "Marco Russo" wrote:
>sql

advicce needed, which task to choose in DTS package

Hi guys, I need to bits of advice.

I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script

The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.

many thanks

SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?

If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.

HTH!

|||

Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?

many thanks

|||

I think you'd need a DTS package with the following flow:

SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task

Your T-SQL Task could be an SQL stored procedure which did something like the following:

Code Snippet

CREATE PROC AddRecords
AS

UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2

FROM DstTable Dst

INNER JOIN HoldingTable Hld

ON Dst.Col4 = Hld.Col4

INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld

WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)

DELETE
FROM HoldingTable

Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.

You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.

HTH!

|||

Thanks for that.

advicce needed, which task to choose in DTS package

Hi guys, I need to bits of advice.

I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script

The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.

many thanks

SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?

If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.

HTH!

|||

Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?

many thanks

|||

I think you'd need a DTS package with the following flow:

SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task

Your T-SQL Task could be an SQL stored procedure which did something like the following:

Code Snippet

CREATE PROC AddRecords
AS

UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2

FROM DstTable Dst

INNER JOIN HoldingTable Hld

ON Dst.Col4 = Hld.Col4

INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld

WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)

DELETE
FROM HoldingTable

Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.

You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.

HTH!

|||

Thanks for that.

Advenced select

Hi,

I have 3 tables.

1. TableValues with MainID, FieldID and value
2. TableFields with FieldID, FieldName, FieldTypeID
3. TableFieldsTypes with FieldTypeID, TypeName

I want to create select that returns me columns as fields names and values taken from TableValues and types taken from TableFieldsTypes.

How can I do this?

Thank's
Alexei

Select tf.FieldName
tft.fieldType,
tv.[value] AS Value
FROM TableValues tv
INNER JOIN TableFields tf ON (tf.fieldID = tv.fieldID)
INNER JOIN TableFieldsTypes tft ON (tf.FieldTypeID = tft.FieldTypeID)

|||

Hi,

I mean that:
1. In table of fields I have 144 fields.
2. Not to all fields I have the value.
3. In the summary I need table with 144 columns based on fields.

Thank's
Alexei

|||

Switch so you select from the table that contain the rows you must have in the output, left outer join any that might not exist.

Modifying RPaulo's answer a bit, highlighted my changes with bold.

SELECT tf.FieldName
tft.fieldType,
tv.[value] AS Value
FROM TableFields tf
INNER JOIN TableFieldsTypes tft ON (tf.FieldTypeID = tft.FieldTypeID)
LEFT OUTER JOIN TableValues tv ON (tf.fieldID = tv.fieldID)

If TableFieldTypes also does not contain a record for each TableFields record you should change it to an outer join.

|||

Hi,

Try to read the third note. I want 144 columns and that for all of them I will get the values.

For example:

MainID | Column1 | Column2 | .... | Column144
1 | Value 1 | Value 2 | .... | Value 144
2 | Value 1 | Value 2 | .... | Value 144
3 | Value 1 | Value 2 | .... | Value 144
.....

Values doesn't same.

Thank's
Alexei

|||

Do you really need to get the data like that from SQL? Depending on how you are presenting the data this method could also work and is much simpler than creating a query that gives you the results your last post suggests.

Divide it up into 2 queries. 1 to get the column titles with ID and another to get the data sorted by TableValues.MainID, TableValues.FieldID

This sorting will make sure the data appears in the correct orders.

select FieldID, FieldName from TableFields order by FieldID

From this query I would be able to build up the columnheaders

select MainID, FieldID, [Value] from TableValues order by MainID, FieldID

A new MainID creates a new row and add the [Value] to the column that has the same FieldID.

|||

Hi,

I really nead the data like that.

Thank's
Alexei

|||I think you need a crosstab query. You are trying to effectively build schema based on data, which usually involves a crosstab.|||

Something like this:

SELECT MainID, TypeName, f1 AS f1, f2 AS f2, f3 AS f3,f4 AS f4, f5 AS f5, f6 AS f6, f7 AS f7, f8 AS f8, f9 AS f9, f10 AS f10, f11 AS f11, f12 AS f12, f13 AS f13, f14 AS f14
FROM (Select tv.MainID, tft.TypeName, tf.FieldName, tv.[value] AS Value
FROM TableValues tv
LEFT JOIN TableFields tf ON (tf.fieldID = tv.fieldID)
LEFT JOIN TableFieldsType tft ON (tf.FieldTypeID = tft.FieldTypeID)) p
PIVOT (AVG(value) for fieldName IN ([f1], [f2], [f3], [f4], [f5], [f6], [f7], [f8], [f9], [f10], [f11], [f12], [f13], [f14]))
AS PVT
ORDER BY MainID

|||

Hi,

I need something like this. But in T-SQL I don't have the pivot tables.

Thank's
Alexei

|||

Use CASE instead.

SELECT MainID, TypeName,
AVG(CASE WHEN FieldName ='f1' THEN value END) as f1,
AVG(CASE WHEN FieldName ='f2' THEN value END) as f2,
AVG(CASE WHEN FieldName ='f3' THEN value END) as f3,
AVG(CASE WHEN FieldName ='f4' THEN value END) as f4,
AVG(CASE WHEN FieldName ='f5' THEN value END) as f5,
AVG(CASE WHEN FieldName ='f6' THEN value END) as f6,
AVG(CASE WHEN FieldName ='f7' THEN value END) as f7,
AVG(CASE WHEN FieldName ='f8' THEN value END) as f8,
AVG(CASE WHEN FieldName ='f9' THEN value END) as f9,
AVG(CASE WHEN FieldName ='f10' THEN value END) as f10,
AVG(CASE WHEN FieldName ='f11' THEN value END) as f11,
AVG(CASE WHEN FieldName ='f12' THEN value END) as f12,
AVG(CASE WHEN FieldName ='f13' THEN value END) as f13,

AVG(CASE WHEN FieldName ='f14' THEN value END) as f14
FROM (Select tv.MainID, tft.TypeName, tf.FieldName, tv.[value] AS Value
FROM TableValues tv
LEFT JOIN TableFields tf ON (tf.fieldID = tv.fieldID)
LEFT JOIN TableFieldsType tft ON (tf.FieldTypeID = tft.FieldTypeID)) p
WHERE fieldName IN ('f1', 'f2', 'f3', 'f4', 'f5', 'f6', 'f7', 'f8', 'f9', 'f10', 'f11', 'f12', 'f13', 'f14')
GROUP BY MainID, TypeName
ORDER BY MainID

|||

Hi,

I don't want to calculate the average.

Thank's
Alexei

|||Average doesn't really do anything since there will be only one row per group (based on the CASE expression). It is needed for generating the multiple columns within each group. You can use MIN or MAX or any other aggregate function depending on the CASE expression.|||

Hi,

I have tried this but it doesn't work:

SELECT ElevatorID, FieldType,
AVG(CASE WHEN FieldID =1 THEN FieldValue END) as f1,
AVG(CASE WHEN FieldID =2 THEN FieldValue END) as f2,
AVG(CASE WHEN FieldID =3 THEN FieldValue END) as f3,
AVG(CASE WHEN FieldID =4 THEN FieldValue END) as f4
FROM (Select tv.ElevatorID, tft.FieldType, tf.FieldID, tv.FieldValue
FROM TechnicalPage tv
LEFT JOIN TechnicalPageFields tf ON (tf.fieldID = tv.fieldID)
LEFT JOIN TechnicalPageFieldsTypes tft ON (tf.FieldTypeID = tft.FieldTypeID)) p
WHERE FieldType IN (1,2,3,4)
GROUP BY ElevatorID, FieldType
ORDER BY ElevatorID

I get errors:

Server: Msg 409, Level 16, State 2, Line 1
The average aggregate operation cannot take a char data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The average aggregate operation cannot take a char data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The average aggregate operation cannot take a char data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The average aggregate operation cannot take a char data type as an argument.

Thank's
Alexei

|||If FieldValue is character data type then use MIN/MAX aggregate. Usually it is best to post some sample schema and data in your post so that we don't have to guess lot of things like data types, columns, join conditions etc.

Advantages of Table variables over Temp tables

What are the advantages of using a table type variables over a
temporary table. Isn't the data stored in the temp db in the case of
table type variables? if not, where is the data stored?An article on this here:
http://www.aspfaq.com/show.asp?id=2475
David Portas
SQL Server MVP
--|||You can read following article written by Mike Gunderloy, describing
usage and advantages with respect to Table variable and temporary
tables.
<http://developer.com/db/article.php/10920_3414331_1>
-Hari Sharma
India|||http://toponewithties.blogspot.com/...nd.h
tml
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<balacr@.gmail.com> wrote in message
news:1127109967.631131.113870@.g14g2000cwa.googlegroups.com...
> What are the advantages of using a table type variables over a
> temporary table. Isn't the data stored in the temp db in the case of
> table type variables? if not, where is the data stored?
>

Advantages of Database Diagram

What is the advantages/disadvantages of using Database Diagram and link all the tables in MS SQL Server Management Studio versus letting the application check and link the different tables at run time? Currently, I do not have all my tables linked in a Database Diagram. I do everything at run time in my application code behind. What are the best practices? Which is easier or perhaps more secure?

I always used the diagram tool in SQL2000 - not only is it a great resource for new DBA's and developers, it adds constraints that you might otherwise forget. It's a great simple way to ensure you don't end up with missing links in your data.

|||

Many thanks for the response. Is constraint the biggest advantage of using diagram? By the way, can I use MS SQL Server Management Studio 2005 to diagram a database tables on SQL Server 2000?

|||

Lack of referential integrity can lead to very hard to trace faults. Where relationships exist, it is far better to define then at the database level as the relationship is then always enforced.

You can have more than one diagram. It is not necessary to have diagrams to define foreign key relationships, it is just a bit easier that way.

|||

Thanks for the responses.

sql

Advantage of using Catalog views

Hi,

Can anyone tell the advantage of using the new catalog views over the traditional system tables which were given as compatiblity views in sql server 2005

The compatibility views will not be upgraded to support newer metadata information (post-2000), also because some of the limits such as user numbers have increased the old views will error or return NULLs if the value ranges for the old system tables are exceeded (see - http://msdn2.microsoft.com/en-us/library/ms187376.aspx).

Basically you are locked into a view of the database that does not allow for all the features in SQL Server 2005 if you use the compatibility views.

|||

for all new feature SQL Server 2005 , you have use Catalog views or DMVs. Compatibility views are only for backward compatibility , which will be removed in the next version. All the new development work should not use Compatibility views.

Madhu

Advanced Join?

I have two tables X and Y which need to join together. Table X has an ID field which connects to an ID field in Table Y. My problem is the ID field in Table X can contain multiple ID's EX:

Table X
ID
-
2,1,4,
2,5,
1,
3,1,2,4,
ect...

whereas the ID field in Table Y contains one ID in each row EX:

Table Y
ID Color
- -
1 Green
2 Blue
3 Red
4 Yellow
5 Orange
ect...

Is there a way to join these two tables together? I need the output to be...

ID Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow
ect...

Yes, but it will be messy and inefficient. And most likely, it will ignore any indexing.

You have violated a cardinal rule with ID columns. In order to efficiently link tables, they must be individual values -NOT simulated arrays of values. This is really is a bad design that will continue to haunt you throughout the project lifecycle.

SELECT

x.ID,

y.Color

FROM Table_X x

JOIN Table_Y x

ON ( ',' + x.ID ) LIKE ( ',' + y.ID + ',' )

There are some other methods as well, one of which would create a #Temp table, split apart the separate ID's in Table_X, and then JOIN that #Temp table with these two tables. Here is a link to that approach. And another.

You really 'shouldn't do that kind of 'trickery' with key values. The continual hassle with the data just isn't worth the 'buzz' gained from being so clever...

It's NOT an advanced JOIN, it is a very, very silly table design!

|||If you think this is "silly" you should see the rest of the database! The sad part about this whole thing is that our company purchased this application from a leading medical management software company for 1,$$$,$$$. Your example doesn't give me the output I was looking for but I will try the example from the link you provided on Monday when I get back to work. Thanks for your help.
|||

I see that I left out an important piece of the code. My apologies.

SELECT

x.ID,

y.Color

FROM Table_X x

JOIN Table_Y x

ON ( ',' + x.ID ) LIKE ( '%,' + y.ID + ',%' )

That 'should' work a bit better.

I understand. Sometimes I just don't get how seemingly intelligent folks spend so much money for such poorly designed software. But it happens every day. And then we have to support it.

|||

Your query gives me....

Table_X.ID Table_Y.Color

2,1,4, Blue
2,1,4, Green
2,1,4, Yellow
2,5, Blue
2,5, Orange
1, Green
3,1,2,4, Red
3,1,2,4, Green
3,1,2,4, Blue
3,1,2,4, Yellow

How do you get it to.?.?.?.

Table_X.ID Table_Y.Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow

|||

What you are now requesting is a function for the display tool. The client application 'should' be creating a string array to put those values together. Folks often forget, SQL Server excels at storing and retrieving data, not at creating displays. But so many application are being created by folks that really aren't very good programmers at all, so things like this get stuffed into SQL Server.

You can use one of the methods listed in these resources.

Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

|||

Thanks for all your help!!!

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNum1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's names
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped in
families.
"Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNum1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's names
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped in
families."Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers
.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNu
m1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's name
s
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped i
n
families."Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Advanced Execution Plan Question

I have a scenario where I have 3 tables joined together. The first is the
"main" table that will define the result set. The second is a lookup table;
the main table has a foreign key to it. The third is a detail table; it has
a foreign key to the main table. Both the second and third tables are on
the right side on a LEFT OUTER JOIN. In this particular query, I only need
the detail data for a particular row if the lookup row exists. The reason
for this is that if the lookup row exists, it will be used to further filter
the result set based upon a value in the detail row. But if the lookup row
doesn't exist, there's no need to lookup the detail row. In order to
optimize the query I attempted to add an additional condition to the ON
clause joining the main and detail table saying only join if the lookup key
is not null:
SELECT
...
FROM
tbFilterTest F
LEFT JOIN tbFilterTestLookup L ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail D ON L.LookupID IS NOT NULL
AND
F.FilterID
= D.FilterID
My hope was that in the join in the execution plan that there would be an
argument "L.LookupID <> null". What I'm ending up with in the actual
execution plan is the "L.LookupID <> null" is not in the join but its
showing up as an argument in the index s for the inner input, meaning
(I'm thinking) that the s is still getting executed and then the
"L.LookupID <> null" is being applied afterward, which means the s was
wasted if "L.LookupID = null".
Now I put together the 3 tables listed above to test this more closely.
Interestingly enough, the "L.LookupID <> null" argument WAS in the join and
not in the inner input, meaning the index s was not being performed on
the detail table when the lookup was null. HOWEVER, I noticed the inner
input (detail table) was having an index scan because I didn't have an index
on FilterID. Once I put the index on FilterID on the detail table, the
"L.LookupID <> null" moved back to the inner input (detail) index s!
I'm trying to understand the logic of this and also wonder if there is a way
to make the "L.LookupID <> null" always occur in the join. The actual
query I'm doing this for is very vital and regularly executed.
Below is DDL, sample data, and the query. Also is the index that "makes the
difference". If you execute the query without the index and then with the
index, you should see the "L.LookupID <> null" move from the join to the
inner input index s. Note that the sample query simply demonstrates "the
moving WHERE clause" in the execution plan. It does not demonstrate the
condition in my real query that moved me to try and accomplish this.
Thanks,
Mike
----
SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTestDetail')
DROP TABLE tbFilterTestDetail
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTest')
DROP TABLE tbFilterTest
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTestLookup')
DROP TABLE tbFilterTestLookup
GO
CREATE TABLE tbFilterTestLookup
(
LookupID int NOT NULL CONSTRAINT PK_FilterTestLookup PRIMARY KEY
)
GO
INSERT tbFilterTestLookup (LookupID) VALUES (1000)
INSERT tbFilterTestLookup (LookupID) VALUES (2000)
INSERT tbFilterTestLookup (LookupID) VALUES (3000)
INSERT tbFilterTestLookup (LookupID) VALUES (4000)
INSERT tbFilterTestLookup (LookupID) VALUES (5000)
INSERT tbFilterTestLookup (LookupID) VALUES (5001)
INSERT tbFilterTestLookup (LookupID) VALUES (5002)
INSERT tbFilterTestLookup (LookupID) VALUES (5003)
INSERT tbFilterTestLookup (LookupID) VALUES (5004)
INSERT tbFilterTestLookup (LookupID) VALUES (5005)
INSERT tbFilterTestLookup (LookupID) VALUES (5006)
INSERT tbFilterTestLookup (LookupID) VALUES (5007)
INSERT tbFilterTestLookup (LookupID) VALUES (5008)
INSERT tbFilterTestLookup (LookupID) VALUES (5009)
INSERT tbFilterTestLookup (LookupID) VALUES (5010)
INSERT tbFilterTestLookup (LookupID) VALUES (5011)
INSERT tbFilterTestLookup (LookupID) VALUES (5012)
INSERT tbFilterTestLookup (LookupID) VALUES (5013)
INSERT tbFilterTestLookup (LookupID) VALUES (5014)
GO
CREATE TABLE tbFilterTest
(
FilterID int NOT NULL CONSTRAINT PK_FilterTest PRIMARY KEY,
LookupID int NOT NULL
)
GO
INSERT tbFilterTest (FilterID, LookupID) VALUES (100, 10) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (200, 11) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (300, 12) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (400, 1000) -- Match
INSERT tbFilterTest (FilterID, LookupID) VALUES (500, 13) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (600, 14) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (700, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (800, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (900, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (620, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (730, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (840, 20) -- No match
GO
CREATE TABLE tbFilterTestDetail
(
DetailID int IDENTITY (10000, 1) NOT NULL CONSTRAINT PK_tbFilterTestDetail
PRIMARY KEY,
FilterID int NOT NULL CONSTRAINT FK_tbFilterTestDetail_tbFilterTest
REFERENCES tbFilterTest (FilterID),
Descrip varchar(128) NOT NULL
)
GO
CREATE INDEX IX_tbFilterTestDetail_FilterID ON tbFilterTestDetail (FilterID)
GO
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (100, '100-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (100, '100-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (200, '200-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (200, '200-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (300, '200-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (300, '200-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (400, '400-1-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (400, '400-2-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (500, '500-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (500, '500-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (600, '600-1-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (600, '600-2-match')
GO
-- Sample Query
SELECT
F.FilterID,
L.LookupID,
D.Descrip
FROM
tbFilterTest F
LEFT JOIN tbFilterTestLookup L ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail D ON L.LookupID IS NOT NULL -- this is
the condition that moves in the execution plan
AND
F.FilterID =
D.FilterID
-- Magic index: try sample query w/ and w/o
CREATE INDEX IX_tbFilterTestDetail_FilterID ON tbFilterTestDetail (FilterID)
GOOn Thu, 18 Aug 2005 13:53:36 -0400, Mike Jansen wrote:
(snip)
>Both the second and third tables are on
>the right side on a LEFT OUTER JOIN. In this particular query, I only need
>the detail data for a particular row if the lookup row exists.
Hi Mike,
Then why use a LEFT OUTER JOIN' Outer joins should only be used if
you must retain rows from one table even though there is no match in the
other table. The standard join type (INNER JOIN) will retain only rows
that match (as per your request) and will probably perform faster as
well.
SELECT ...
FROM tbFilterTest AS F
INNER JOIN tbFilterTestLookup AS L
ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail AS D
ON F.FilterID = D.FilterID
If you are sure that there will always be at least one matching row in
the FilterTestDetail table, change the second join to an INNER JOIN as
well. It won't change the results, but it might speed up the
performance.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

ADP Report Problems

Hi all I have two tables joined in a query and I need for one record to show up in the report rather then the whole history. Does that make sense??Does that make sense??No, but if you could post the SELECT statment, an example of what you don't want, and a "cut down" example or two of what you do want, I bet we could figure it out! ;)

-PatP|||Hi all I have two tables joined in a query and I need for one record to show up in the report rather then the whole history. Does that make sense??

No ... but if you hum a few bars I think we can fake it :D|||"ADP Report Problem"?
Should this thread be in the Access forum?|||"ADP Report Problem"?
Should this thread be in the Access forum?I thought the same but I was waiting until anyone actually figured out what the problem was....|||Considering that 95% or better of Desireemm's problems are SQL, I'm willing to give this one the benefit of the doubt. If it turns out that the question belongs elsewhere, we can move it once we know enough to know where to move it to!

-PatP|||I'm so sorry guys I didnt post the Select Statement. You see I have two tables that are joined, the ActiveConditionals table holds the employees conditionals and they could have several of them but I only want to show in my report the most recent ones, from the last two years I would say?/

SELECT TOP 100 PERCENT dbo.EmployeeGamingLicense.TM#, dbo.EmployeeGamingLicense.LASTNAME, dbo.EmployeeGamingLicense.FIRSTNAME,
dbo.ActiveConditional_tbl.CONDITIONAL, dbo.ActiveConditional_tbl.DATEOFCONDITIONAL, dbo.ActiveConditional_tbl.INVESTIGATOR_COND,
dbo.ActiveConditional_tbl.REASONFORCOND
FROM dbo.ActiveConditional_tbl INNER JOIN
dbo.EmployeeGamingLicense ON dbo.ActiveConditional_tbl.TM# = dbo.EmployeeGamingLicense.TM#
ORDER BY dbo.EmployeeGamingLicense.LASTNAME|||So what do you want. The most recent record, or the records from the past 2 years (possibly multiple records)?|||I figured it out guys I'm sorry to waste your time, your the BEST|||I figured it out guys I'm sorry to waste your time, your the BESTI know - we are that good. Ever so glad we sorted out the problem for you :D

ADOX problem...?

I need to rename tables in code VB2005 (access database). On the

forum I found post about ADOX and this code to rename the tables:

Private Sub RenameTables(ByVal sTextToRemove As String)

Dim i As Integer

Dim dbRename As Database

Dim Connect As New PrivDBEngine

dbRename = Connect.OpenDatabase(tbDBPath.Text)

dbRename.CreateTableDef()

For i = 0 To dbRename.TableDefs.Count - 1

If dbRename.TableDefs(i).Name.Length >= sTextToRemove.Length - 1 Then

If dbRename.TableDefs(i).Name.Substring(0, 9) = sTextToRemove Then

dbRename.TableDefs(i).Name = dbRename.TableDefs(i).Name.Substring(9)

End If

End If

Next i

dbRename.Close()

dbRename = Nothing

MessageBox.Show("Tables in " + tbDBPath.Text + " have been renamed.

Rename Access Tables", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

But it doesn't work because i get an error on Dim dbRename As Database Dim Connect As New PrivDBEngine

What I need to do for this to work?

Thank you!

The code is for DAO so the first thing you need to do is add the DAO 3.6 COM library reference to your application (Project...Add Reference).

Then, I would declare those objects and fully qualify the types using the library name:

Dim dbRename As DAO.Database
Dim Connect As DAO.PrivDBEngine

|||

Hi,

Please do the following process to add the required references,

1. Right click on your project in Solution explorer and select Add references...
2. Select COM tab and search for following two libraries and add it,
- Microsoft ADO Ext. 2.8 for DLL and Security
- Microsoft DAO 3.6 Object Library
To see those dlls in References section,
Select Project -> Show all files
So under the References the following two dlls will be shown:
1. ADOX
2. DAO

The code does the following things in MS Access
1. Create database
2. Create a table
3. Rename a table

[The following code creates TestDB.mdb in C:\ and create a table named MyTable. Then it renamed as "MyNewNameTable"]

Imports ADOX

Imports DAO

Public Class Form1

Dim catalog As Catalog = New ADOX.Catalog()

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

CreateDatabaseAndTable("TestDB.mdb", "MyTable")

RenameTables("MyTable", "MyNewNameTable")

End Sub

Private Sub CreateDatabaseAndTable(ByVal databasename As String, ByVal tablename As String)

catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\" + databasename + ";" & _

"Jet OLEDB:Engine Type=5")

Console.WriteLine("Database Created Successfully")

Dim firsttable As Table = New Table()

firsttable.Name = tablename

firsttable.Columns.Append("CustID", ADOX.DataTypeEnum.adInteger)

firsttable.Columns.Append("CustName", ADOX.DataTypeEnum.adVarWChar, 30)

firsttable.Keys.Append("PK_CustID", 1, "CustID")

catalog.Tables.Append(firsttable)

firsttable = Nothing

catalog = Nothing

End Sub

Private Sub RenameTables(ByVal sOldName As String, ByVal sNewName As String)

Dim i As Integer

Dim databasepath = "C:\TestDB.mdb"

Dim dbRename As Database

Dim Connect As New PrivDBEngine

dbRename = Connect.OpenDatabase(databasepath)

dbRename.CreateTableDef()

For i = 0 To dbRename.TableDefs.Count - 1

Try

If dbRename.TableDefs(i).Name = sOldName Then

dbRename.TableDefs(i).Name = sNewName

End If

Catch ex As Exception

Console.WriteLine(ex.Message)

End Try

Next i

dbRename.Close()

dbRename = Nothing

MessageBox.Show("Tables in " & databasepath & " have been renamed. Rename Access Tables")

End Sub

End Class

For further information, please refer,
http://www.4guysfromrolla.com/webtech/013101-1.shtml
http://www.4guysfromrolla.com/webtech/tips/t030802-1.shtml
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch02.mspx?mfr=true
http://www.codeguru.com/vb/gen/vb_database/microsoftaccess/article.php/c5149/

HTH,

ADO connection

In VB6 I use more Forms where users can Add, Edit, Delete Records from more tables.

WHICH IS THE BEST METHOD

1. To open a connection when user start the application and I close the connection when user leave the application.

In Login form (Public cn As ADODB.Connection)

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

OR

2. To Open a connection in each Form and close the connection when Form is UnLoadetThe first case is not possible because

Set cn = New ADODB.Connection (this is in Form Login)
In other form it is necesary to set the connection again.|||The rule is that you only keep the connection open as long as needed - and no longer. What are your concerns ?|||How many clients will use this application ?|||5-8 users|||Will the security credentials be different for each user ? What is the purpose for the application and the multiple forms ? Are you thinking about opening 1 connection object that will be used for multiple recordset/command objects ?|||How I can open a connection when user start the application and kepp open until user leave the application ?

In module:
Public cn As ADODB.Connection

In first form when is load:
SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use "cn" in other forms is not possible (is not open)|||Are you destroying/closing the connection before the 2nd form is loaded ?|||I don`t close the connection, but I close (Unload) the first form.
The connection is closed ?|||Where are you declaring - Public cn As ADODB.Connection ?|||This declaration is made on a Module

Public cn As ADODB.Connection

In the Startup Form (where user input UserName and Password)

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use this connection on other forms I can't because is Closed.
I want to open the connection only one time(when application startup), and if it possible to use the connection in all form.|||Open the connection in a module or class when login in form successfull. Close it when application main form is closed.|||This Looks Like VB code If it is Do not forget to set the DBconnection ( and all all objects associated to it) = nothing

set cn = nothing

I personnally like to use 1 public DB connection instead of multiple connections per app.

Hope this helps.
LJ|||Hello Gurus out there!

I want to know what would be the best method in opening a connection to server:

1. a connection (declared globally) that is open once during Login and access thru all forms and be closed only when application is terminated
What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

2. a connection is opened only when needed but everytime i execute a query against the database i have to open also that connection and terminate when it is not used...What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

Secondly, how can we know the resources used by the users that are connected to my SQL SERVER in terms of memory usage and CPU?

Im using VB/FOxPro and I want to know the best practice in terms of opening a connection to the database coz Im expecting to have 20 or more users online simultanously and hook to my server as soon as we are finished with our system.

I hope you can light up our minds with these concerns.

Thanks,

Bernie|||Bernie - What driver are you using ?

AdjustTokenPrivileges () failed

I have an SSIS package that parses a text file into 3 smaller text files and then takes the data and puts it into tables. The package runs fine up to the point where it needs to insert the data. I turned logging on but no errors are generated. But I do get a file named SQLDUMPER_ERRORLOG.log that is generated with the info below. Any ideas of where to look?

11/16/06 13:31:38, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:

4 supplied

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID =

1844

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags

= 0x0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr =

0x0100C5D0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr =

0x00000000

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump

completed: C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\SQLDmpr0033.mdmp

11/16/06 13:31:43, ACTION, DtsDebugHost.exe, Watson Invoke: No

11/16/06 13:31:43, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:

4 supplied

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID =

1844

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags

= 0x0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr =

0x0100C5D0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr =

0x00000000

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used

11/16/06 13:31:44, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpWriteDump

() Failed 0x80070005 - Access is denied.

11/16/06 13:31:44, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Watson Invoke: No

Do you have anything in your Windows Event log that might indicate a better error?

Also, how are you authenticating to the databases? SQL Server users? Active Directory?

I'm just blurting out stuff to check, I guess.

Phil|||Also, you didn't need to start this thread when you replied to the other one. We don't need two threads about the same topic. Having more than one thread only makes it cumbersome to help you.|||

Nothing in the Application Log. I've tried authentication using windows auth and sql auth both result in the same error.

I was using an OLE DB Destination and I changed it to a SQL Server Destination which got rid of the error above but presented another. I still believe OLE DB Destination should work though.

I should add that I can run the package from my machine without a problem. But whenever I try to run it from the server It will be scheduled on is when I have the problem

Adjusting the summary view

I'm using SQL 2005 Management Studio SP 2 on Windows XP against a SQL 2005 SP 2 server. In the summary view for Database Tables, I can only see the date for when the tables are created, yet they sort by date and time. How do I make the table creation time stamp part show up along with the date? I tried selecting List and Details, with no more detail than date.

Hi Andymcdba1,

We only support name,schema, and created in this view in the current release with no way to change the details.

We have a work-item in our backlog to offer more choices and options in the next general release of SQL Server.

Thanks,

Terrence Nevins

SQL Server Program Manager

Adjusting the summary view

I'm using SQL 2005 Management Studio SP 2 on Windows XP against a SQL 2005 SP 2 server. In the summary view for Database Tables, I can only see the date for when the tables are created, yet they sort by date and time. How do I make the table creation time stamp part show up along with the date? I tried selecting List and Details, with no more detail than date.

Hi Andymcdba1,

We only support name,schema, and created in this view in the current release with no way to change the details.

We have a work-item in our backlog to offer more choices and options in the next general release of SQL Server.

Thanks,

Terrence Nevins

SQL Server Program Manager