Basic Question on Join

Hi,

I'm pretty much a database beginner and have what I think is a basic
question:

If I have a table which has a 'status' column and I can have say three
statuses: "active", "pending", "inactive". Is it better to break these
out into a different "Statuses" table?

On the one hand it seems to me it would be better to be in a different
table since I'll index this column because I'll be in a where clause
sometimes and I think indexing it would be easier than indexing the
actual words. On the other hand, are joins going to slow queries down
too much? I read something about creating my own sql server 2000
datatype - would this be a good place to do such a thing?

Thanks for your help. (And if you could provide a reason why one
solution is better than the other so I can start understanding better,
I'd be very appreciative.)

Thanks.nick wrote:
> Hi,
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?
> Thanks for your help. (And if you could provide a reason why one
> solution is better than the other so I can start understanding better,
> I'd be very appreciative.)
> Thanks.

If you're storing addresses should you have 50 separate tables, one for
each state?

Zach|||"nick" <nickgieschen@.hotmail.com> wrote in message
news:305cab98.0411191325.29e48efc@.posting.google.c om...
> Hi,
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?
> Thanks for your help. (And if you could provide a reason why one
> solution is better than the other so I can start understanding better,
> I'd be very appreciative.)

Having a seperate lookup table may give you a better performance, testing is
the best way to determine. The lookup table could have an numeric value
that corresponds to active, pending, inactive and then you store the numeric
value in your main table. You wouldn't necessarily have to join to the
lookup table, you could just say WHERE Status = 1.

Using lookup tables can be more flexible for front-end coding because if
you have additions to your lookup choices you can simply edit the table
rather than digging around in the front-end code.

I wouldn't think a user-defined datatype would be useful for this.

hth|||nick (nickgieschen@.hotmail.com) writes:
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?

Basic question? It is actually one that I run into when I'm database design
every now and when, and the answer is not always the same. Mainly it is a
judgement's call. But these days, I tend to go for the table.

I like to first point that when I decide whether to add a table, or to
just have a column with some distinct values, performance has none to with
that decision. More important is the aspect of maintenance, the possibility
to present the value, extensibility and whether the value is important
enough to deserve a table.

Having a single column like:

status chat(1) NOT NULL CHECK (status IN ('A', 'P', 'I'))

has the advantage that it's up little space in the documentation, and
so easier to grasp. But if the value is to be presented in a GUI, the
GUI gets responsible for doing the translation (or the user gets to see
A, P and I. Another issue, is whether you need to add a new value. For
various reasons when we change tables, our standard routine is to take
the long way and reload the data into a new table. A bit expensive for
a changed CHECK constraint.

If you have a column with a lookup-table like:

status char(1) NOT NULL REFERENCES statusvalues (status)

this is more work for me initially, because I have to create an INSERT-
file with the statusvalues, and there is one more table in the database.
(In our case actually two, because there would also be a name table
that gives translations in different languages.). But if I later need
to add a new value, it's just a matter of changing that INSERT-file and
no need to reload the table. And with all strings in the database, the
GUI gets a simpler job.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

basic question for sp

Hi,
I need to write a sp. In the sp first I do a select statement (select cycle
from table where ... ) it only returns a single value. and the rest of the s
p
will do different select based the value. how can assign the result to a
varable? ThanksJen,
Try:
DECLARE @.MYVAR INT --OR CHAR OR VARCHAR OR...DEPENDING ON VALUE
SELECT @.MYVAR = COL FROM...
HTH
Jerry
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:CB644854-6C94-48BC-8E45-9DC9A959F0C5@.microsoft.com...
> Hi,
> I need to write a sp. In the sp first I do a select statement (select
> cycle
> from table where ... ) it only returns a single value. and the rest of the
> sp
> will do different select based the value. how can assign the result to a
> varable? Thanks|||Something along the lines of:
SET @.var = ( SELECT ... ) ;
You will have to make sure the select statement returns a single value or
you will get an error.
Anith|||DECLARE @.Variable <type>
SELECT @.Variable = cycle FROM table WHERE ...
John Scragg
"Jen" wrote:

> Hi,
> I need to write a sp. In the sp first I do a select statement (select cycl
e
> from table where ... ) it only returns a single value. and the rest of the
sp
> will do different select based the value. how can assign the result to a
> varable? Thankssql

Basic Full Text search question

Hi

I am just starting to do a full text search on a sql 2000 database. My question is:

If I want to query a table Employee for 1. a particular word in a very large varchar field named "resume" 2 - a boolean field named "willtransfer" and 3. a field that holds the index of another table named "Cities" Cities just holds an index and cityname field of 15 characters.

do I create a fulltext index for all three fields or only the "resume" field since it is the only field that is being searched? What would a query for these 3 fields look like if I am searching for. I have seen no examples that combine multiple fields.

"speaks French" in resume, "true" for "willtransfer " field, and "Miami" in the cities table (there would need to be an inner join on the cityid field in both Employee and City table.

Thanks for any help on this. This is not the tables I am using, but this seemed like an easy example to explain.

smhaig

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE e.resume LIKE '%FRENCH%'
AND e.willtransfer = True
AND c.CityName = 'MIAMI'

I think maybe you might be looking for something like:

DECLARE @.mystring varchar(50)
SET @.mystring = 'french transfer=true Miami'

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE @.MyString LIKE '%FRENCH%'
AND @.MyString LIKE '%transfer=True%'
AND @.MyString@. = 'Miami'
AND e.resume LIKE '%FRENCH%'
AND e. willtransfer = 'True'
AND c.cityname = 'Miami'

I'm not sure if you want to concatenate all three fields or not.

Please see above

Adamus

|||

You only put a full text index on the 1 field you filter on the other columns using a standard where clause

i.e.

select employeeId

from employee

join city on employee.cityId = cities.cityId

where willtransfer = 1

and cityname = 'Miami'

and contains (employee.resume,'French')

This would match any resume that contains French you might want to include more complex search like "French NEAR speaks"

Basic Fact Table Design Question

I'm not sure if this is the proper board....but....

I have a sales data from two different cost centers that generate the same data. When setting up my OLAP db, should I:

(1) design two fact tables that would have the same attributes,

(2) one fact table and one dimension table that contained a key field and a sales type field, or

(3) a fact table in which one of the attributes was the sales type field.

Thanks

Do the facts from the different cost centers represent the different instances of the same business process or event? If so, then they should be modeled as a single fact. Another way to approach this is to ask, "Would my users consider these to be the same thing and therefore would expect these to be in the same place in the DW?".

If you model these as a single fact, the cost center would be best handled in a dimension. Sounds like it would have low cardinality. (Is it possible you would ever have more than two cost centers? Many organizations have hundreds or even thousands of cost centers.)

B.

|||Cost center was probably a bad term. The business makes money selling stuff and performing services. They want total sales data and then sales broken between sales and services. The facts are pretty much the same: amount, date, cost of sale/service, etc. I thought a dimension table would work but it's going to be a table with an attribute that has only one of two conditions, plus a key field and a foreign key in the fact table. Seems like a lot of work for a simple A/B field.|||

I think option 2 is the better, but I need to understand some more requirements to decide.

Create a dimension is better, because tomorrow if you have more cost centers, your structure will be more flexible.

But option 3 is not bad...

Regards!

|||

This sounds like a line item fact table. For example, I bring my car in for repairs and what not, I receive a bill (an invoice). On that bill, there will be items for various parts and labor for various services, each with the number of units consumed, a price per unit, and the total cost for that line.

Each item purchased, it's code, name, base price, etc., belongs in a "product" dimension. We may identify each of these products as belonging to a "Parts" or "Service" category, but that catorigation too would belong in the dimension table.

Take a look at the Kimball book, "The Data Warehouse Toolkit", Chapters 2 & 5, for more ideas on this.

Good luck,

B.

Basic advice on good practise required

I have master tables that I will be updating from our ERP system. Some examples I have seen take an approach of dropping a table in SQL server then creating it again before importing; some, and probably my choice, append and update; I have not seen an example where records are all deleted then the data appended afterwards. Of the three approaches which is generally regarded as best practise / most efficient?

The create and re-load versus the delete and then append are really the same thing from a process perspective, but they will have different performance due to the overhead of data change in SQL Server. Delete is a logged operation, so incurs overhead by writing the transaction log each row that is deleted. Dropping the table is just much faster. Truncating the table would be similar to the drop. It is an non-logged, non-selective delete in effect.

As to which is faster of the two methods, re-load or partial load and update, it depends on lots of factors. The speed of extract will be constant since both methods require you to extract all data, so it will come down to the cost of inserting lots of data, lots of write activity versus the cost of comparison in trying to reduce that write to only the changes required. I cannot give answers as things like row overall row width, comparison columns and how much data needs to be moved around to do each method will impact this. Here is an article that looks at two methods of comparing tables.

Get all from Table A that isn't in Table B
(http://www.sqlis.com/311.aspx)

Another option is to just insert all data, and capture failures due to a primary key violation. Then for the failures check or updates. I did some tests of a similar approach for patching bad data, and it was generally quicker to let it fail the insert and fix afterwards that check and potentially fix all rows. This will depend on the ratio of good to bad, or in your case, new rows, old rows, old that need to be updated.

If the cost of updates is high, then you will want to check if an update is really required, or has nothing changed. The Checksum Transform can be useful for this, as comparing an integer is faster than moving and comparing 200 columns of data to detect and change. Again it depends on your data.

Checksum Transformation
(http://www.sqlis.com/21.aspx)

The thing you need to work out is where is the bottle neck in your process. It may be that the extract query or network transfer is the slowest part and it does not matter which method you use for downstream loading.

My opinion is always go for deltas if you can, but if not then delete (drop/truncate) and reload all. It can be harder with foreign keys but it is going to be faster I think.

sql

Based on backup and recovery

hello,
i have taken a backup at 3pm ,
some data was entered in an xx table at 5pm,
which was lost due to some reasons at 530pm,
now if i want to get back the data what i should do..

as we have a concept of time based recovery in oracle
what is the method used here to get my data back
i have taken my second backup at 7pm
its an imm. requirement for me
just help me out...
thnx & regards
pavanSimply use FULL recovery model to the database which benefits in - No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).

Bar graphic chart is not in the same order with data table

Hi All,

I have a very simple bar-graphic report that has the following data:

Sale Region Total Sale

A 1,000,000

B 350,000

On the table, the data is sort by the Sale Region, so the A would come before the B. But on the bar-chart, the B is always displayed before the A. If it is a vertical bar chart, and then the B is on the left, the A is on the right. And if it's a hoz. bar chart, the B bar is on the top, and the A bar is on the bottom. I try to modify the RDL to make the bars displayed in order, but failed after many tries.

Here is the RDL code that I wrote in an attempt to make the bars display in ASC order, but does not work:

<CategoryGroupings>

<CategoryGrouping>

<DynamicCategories>

<Sorting>

<SortBy>

<SortExpression>Fields!SaleRegion.Value</SortExpression>

</SortBy>

</Sorting>

<Grouping Name="Chart1CategoryGroup">

<GroupExpressions>

<GroupExpression>=RowNumber(Nothing)</GroupExpression>

</GroupExpressions>

</Grouping>

<Label>=Fields!SaleRegion.Value</Label>

</DynamicCategories>

</CategoryGrouping>

</CategoryGroupings>

Can someone shed a light?

Thanks in advance

You are missing the "=" in the SortExpression. Right now it is just a constant string "Fields!SaleRegionValue".

Change it to:
<SortExpression>=Fields!SaleRegion.Value</SortExpression>

-- Robert

|||

That's it! Thanks Robert. You surely have a pair of hawk's eyes.

I have the chart sorted in order the way I wanted it too. However, I could not get the table order in the same way. I event go into the VS2005 report designer, set the sort property of the table. But no matter what order I set, the table is always displayed in a non-order. I added an ORDER BY clause in the SQL retrieve statement, the table is still displayed in a kind of random order.

Is this a known problem with Reporting Service? Or are there properties that I have to set in order for the table displayed on an ASC or DESC sequence?

Thanks in advance.

|||

It sounds like you have an explicit table detail group or a table group expression defined. In that case, you have to add the sorting directly on the grouping expression.

-- Robert

sql

Bank numbers check

I want to make a query that check in a table for bank numbers that are incorrect. This need to be done in sql and using modulo 97.

Thanks

Modulo operator in SQL server is % (which returns the remainder of an integer division).

So to get modulo 97 you do:

<somenumber> % 97

You might need to convert the datatype of your bank number from varchar to int or bigint to be able to perform this operation. If the bank number contains any non-number characters such as . or - then you must strip these out.

|||

Don't know if this is what you're after but I wrote this to validate ABA numbers:

public class CheckFedWireRoutingNumber
{

//Checks that the fed wire routing number (also known as an ABA number)
//is in the valid format. Does not verify that it exists, just that
//it's in the right format
static public bool IsABA(string abaNumber)
{
//Validate format of ABA number. This is taken fromhttp://www.brainjar.com/js/validation/
//andhttp://javascript.internet.com/forms/aba-routing-number-checksum.html
//andhttp://www.azcode.com/ABA/aba.htm
//function isABA(t)
//
//Here's a javascript version, taken from the first url, above
//{
// // Remove dashes if entered
// t = removeCharacters(t, '-');
//
// // All digits should be numeric (0 - 9)
// if (!isInteger(t))
// return false;
//
// // Run through each digit and calculate the total.
// n = 0;
// for (i = 0; i < t.length; i += 3)
// {
// n += parseInt(t.charAt(i), 10) * 3
// + parseInt(t.charAt(i + 1), 10) * 7
// + parseInt(t.charAt(i + 2), 10);
// }
// if (n != 0 && n % 10 == 0)
// return true;
// else
// return false;
//}

//remove dashes and spaces
abaNumber=abaNumber.Replace("-","").Replace(" ","");
if (!IsNumeric(abaNumber))
{
return false;
}

//Perform checksum
int n = 0;
try
{
for (int i = 0; i < abaNumber.Length; i += 3)
{
n += (Convert.ToInt32(abaNumber.Substring(i,1)) * 3) +
(Convert.ToInt32(abaNumber.Substring(i+1,1)) * 7) +
(Convert.ToInt32(abaNumber.Substring(i+2,1)));
}
}
catch (ArgumentOutOfRangeException)
{
//Caused by an ABA number that is not the right length
return false;
}
if (n != 0 && n % 10 == 0)
{
return true;
}
else
{
return false;
}
}

//Each character should be a valid number
static public bool IsNumeric(string input)
{
foreach(char c in input)
{
if(!char.IsNumber(c)) return false;
}
return true;
}

}

|||

Thanks dbland07666 but I need to do it in sql.

Johram how does this convert work? I now how to do that in vb.net code but not in sql. And another problem is that I only need to see the record where the bank number is incorrect. And I also don't may see the records where there is no bank number.

Thanks

|||

Could you give us an example of how a bank number looks like in your database? And what is the algorithm for verifying that it is valid? Is modulo 97 the only check you do? If so, what result do you expect in order to determine if it is valid? We need to know how your check is made in order to help you with the SQL code. Thanks!

|||

My bank numbers are only numbers.

BN = 72020290081: 97 - (modulo 97 van 720202900) = 97 - 16 =81

Thanks

Davidnyh

|||

Basically, what you need to do in your SQL is to split the number to get the "number part" and the "checksum part". Then you perform the modulo check and compare with the given checksum.

OK, so here's our first shot at it. Assuming table nameAccount and column nameNumber. This query will list all accounts with their calculated checksums, next to their given checksums.

SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account

Visually, we can now compare the calculated checksum with the given checksum and spot the deviations. But we need to do this in code as well.

If we only want the numbers that are valid, we can do something like:

SELECT NumberFROM AccountWHERECAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT) = 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97

Maybe you would like to list all accounts and have a column which says whether it is valid or not? Then we can do like this:

SELECT Number,'Yes'AS ValidFROM(SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account)AS T1WHERE GivenChecksum = CalculatedChecksumUNIONSELECT Number,'No'AS ValidFROM(SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account)AS T2WHERE GivenChecksum != CalculatedChecksumOR GivenChecksumISNULL

Good luck with the numbers! If you have any more questions, just let us know!

Please don't forget to mark this post as answer if it was useful to you. Thanks!

|||

I get this error: Argument data type decimal is invalid for argument 1 of substring function.

And if I try CAST(Number AS INT) I get this error: Arithmetic overflow error converting expression to data type int.

|||It works.Thanks|||It works. Thanks

Baffled! Cant figure out how to do this query. Is it even possible?

I have an "Issues" table for my technicians. An issue can be on "hold"
or "assigned".
I want to get a count for each tech with a column showing number of
issues on hold and a column for number of issues assigned. It would
look like this --

Tech Num_Assigned Num_On_Hold
Fred 3 10
Carol 6 7

I can get each column separately, but I want both in the same answer
table!
Is that too much to ask? :)You need to do a self join on the table. Without your table
definition, it would be something like this

SELECT TI1.Tech,
Num_Assigned = COUNT(TI1.TechID),
Num_On_Hold = COUNT(TI2.TechID)
FROM TechIssues TI1, TechIssues TI2
WHERE TI1.TechID = TI2.TechID
GROUP BY TI1.Tech

Now, the above assumes that all techs have issues assigned AND issues
on HOLD. You'd need to UNION a couple more of these to handle where
Techs have records assigned but not on hold and vice versa. But this
should get you started.

Hope it helps
Teresa Masino|||jonescv@.gw.ccsd.net wrote:
> I have an "Issues" table for my technicians. An issue can be on "hold"
> or "assigned".
> I want to get a count for each tech with a column showing number of
> issues on hold and a column for number of issues assigned. It would
> look like this --
> Tech Num_Assigned Num_On_Hold
> Fred 3 10
> Carol 6 7
>
> I can get each column separately, but I want both in the same answer
> table!
> Is that too much to ask? :)

Here's a guess:

SELECT tech,
COUNT(CASE WHEN status = 'assigned' THEN 1 END),
COUNT(CASE WHEN status = 'hold' THEN 1 END)
FROM your_table
GROUP BY tech ;

--
David Portas
SQL Server MVP
--

Baffled

I have a table called lab_results_import, and one of the fields is called
qualifier_id. The table was empty, I imported 1 Excel spreadsheet with 150
0
records. 134 of these records have an empty cell in the Excel spreadsheet i
n
the qualifier_id column. The ss imports fine, but I am baffled by this quer
y
that I am running on the table. The query is
SELECT *, qualifier_id
FROM lab_results_import
WHERE (qualifier_id = '')
which returns 134 records, BUT
SELECT *, qualifier_id
FROM lab_results_import
WHERE (qualifier_id = ' ')
also returns 134 records, and regardless of how many spaces are in my
(qualifier_id = '') where clause, the query returns 134 records.
Anybody know what is going on, and/or how to search for a field containing a
certain number of spaces.
Thanks a lot.
ArcherWhat data type is this column (qualifier_id)? Is it nullable?
ML|||>> Anybody know what is going on, and/or how to search for a field
It is a known behavior where the trailing blanks of are being trimmed off
for comparison. The workaround is to use a character which is not in your
column like '~' or '*' like:
SELECT *
FROM tbl
WHERE '~' + col + '~' = '~' + SPACE( 10 ) + '~' ;
Note that such padding in general, will avoid the usage of any existing
indexes in the column, though.
Anith|||Try,
select
c1, datalength(c1)
from
(
select cast(space(0) as varchar(25))
union all
select space(1)
union all
select space(2)
union all
select space(3)
union all
select space(4)
) as t1(c1)
where
'.' + c1 + '.' = '.' + space(3) + '.'
-- or
select
c1, datalength(c1)
from
(
select cast(space(0) as varchar(25))
union all
select space(1)
union all
select space(2)
union all
select space(3)
union all
select space(4)
) as t1(c1)
where
c1 = ' ' and datalength(c1) = 3
go
Joe Celko explained one time why sql server seems to ignore trailing spaces
when comparing varchar data type, but I could not find it.
AMB
"bagman3rd" wrote:

> I have a table called lab_results_import, and one of the fields is called
> qualifier_id. The table was empty, I imported 1 Excel spreadsheet with 1
500
> records. 134 of these records have an empty cell in the Excel spreadsheet
in
> the qualifier_id column. The ss imports fine, but I am baffled by this qu
ery
> that I am running on the table. The query is
> SELECT *, qualifier_id
> FROM lab_results_import
> WHERE (qualifier_id = '')
>
> which returns 134 records, BUT
> SELECT *, qualifier_id
> FROM lab_results_import
> WHERE (qualifier_id = ' ')
> also returns 134 records, and regardless of how many spaces are in my
> (qualifier_id = '') where clause, the query returns 134 records.
> Anybody know what is going on, and/or how to search for a field containing
a
> certain number of spaces.
> Thanks a lot.
> Archer|||varchar(10)
and nulls are allowed. I actually want the cells to be null and I am trying
to avoid having cells with emtpy spaces.
A
"ML" wrote:

> What data type is this column (qualifier_id)? Is it nullable?
>
> ML

Bad Update Performance with very large recordsets

When updating a table with 80 million records we are experiencing very bad
times. We found using maxdop1 to keep the process in one thread helpful,
but something is still causing problems.
Updating one bigint field which is nullable to a bigint value from a table
inner joined with the current table is crawling.
Should we remove the nullability of the field and set it to a default of -1?
Would this ensure space is allocated for the field? What else can we look
for?
What about NO LOCK or Serializable? Could
What would be the *fastest* way to update records in a table assuming only 1
connection to the database? How do fields allowing NULLs affect this?
Thanks.Never try to update 80 million rows at once if you can avoid it. Wrap the
updates in smaller batches of say 10, 50 or 100K in size. If no one else
needs to use the table while this is happening you can use TABLOCK hint to
help.
--
Andrew J. Kelly
SQL Server MVP
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:u$j1RcBbDHA.1744@.TK2MSFTNGP12.phx.gbl...
> When updating a table with 80 million records we are experiencing very bad
> times. We found using maxdop1 to keep the process in one thread helpful,
> but something is still causing problems.
> Updating one bigint field which is nullable to a bigint value from a table
> inner joined with the current table is crawling.
> Should we remove the nullability of the field and set it to a default
of -1?
> Would this ensure space is allocated for the field? What else can we look
> for?
> What about NO LOCK or Serializable? Could
> What would be the *fastest* way to update records in a table assuming only
1
> connection to the database? How do fields allowing NULLs affect this?
> Thanks.
>

bad table design

A developer has just gave me a few tables table to put
into production, with all character columns defined as
varchar(255), null.
As I am absolutely positive that the majority of these
columns will not be storing this amount of data in each
of the columns, as some of them are code columns, types,
and one description field.
After asking him to review all of the columns and the
maximum estimated data stored in each one, he said that
it shouldn't make any difference because A) they're
varchar and B) they're defined as null.
What can I say to him to make him do the needful?
Thanks,
MarcusMarcus
Well, variablle-length characters ( as in your case) is stored 1 byte per
character.Declared but unused characters don't consume storage.
On the other hand I'll be conserned about using NULL's when you will be
querieng the tables.
Using IS NULL/NOT NULL clause will not allow Query Optimizer to use an index
and respectively your query will suffer from perfomance hit.
Also consider using DEFAULT values in those columns.
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus|||Pick up one of Joe Celko's post from here regarding how important it is to
get the database design right and where he state that he almost never have
to use NULL. If he question Joe's credibility, search for instance Amazon of
the books he has been writing (or go to www.celko.com).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus|||Right. I've brought that up also and he's come back and
said that the table is for importing data only and it
shouldn't matter.
You're thoughts?
Thanks!
Marcus
>--Original Message--
>Marcus
>Well, variablle-length characters ( as in your case) is
stored 1 byte per
>character.Declared but unused characters don't consume
storage.
>On the other hand I'll be conserned about using NULL's
when you will be
>querieng the tables.
>Using IS NULL/NOT NULL clause will not allow Query
Optimizer to use an index
>and respectively your query will suffer from perfomance
hit.
>Also consider using DEFAULT values in those columns.
>
>"Marcus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
types,
>
>.
>|||Marcus
Look, we can discuss a lot on this topic.
In outer-join operation you should carefully account for NULL's values that
are generated to preserve rows that don't have a match in the table being
joined.
Also dealing with NULL's adding complexity to the storage engine because SQL
Server keeps a special bitmap in every row to indicate which nullable
columns actually are NULL. SQL Server must decode the bitmap for every row
accessed.
Again I'd recommed you/your opponent to make all nullable columns as NOT
NULL and DEFAULT values.
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8c2301c404f2$255e24a0$a501280a@.phx.gbl...
> Right. I've brought that up also and he's come back and
> said that the table is for importing data only and it
> shouldn't matter.
> You're thoughts?
> Thanks!
> Marcus
> stored 1 byte per
> storage.
> when you will be
> Optimizer to use an index
> hit.
> message
> types,|||"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
Two aspects :
varchar(255), there is nothing wrong defining a varchar
type with a number of characters. Varchar(20) and varchar(255)
both occupie the same number of bytes if the strings are the same.
255 doesn't look like a 'natural' number, it is typical a number
which comes out of the head of a 'computer' oriented mind.
(80, 100, 200, 400 are more natural). But if the data comes
from another 'computer' system 255 could be the right number.
Null,
Never prefered and should be avoided. But for only importing
data (on the way to another table) and for text holding fields
on which no selection and no join is done a 'logical' choice.
If the text is human generated and a 'non' text is one of
the posibilities, I think that null is ok.
(When indexed, joined, used in a where clause one should
avoid a null).
Also concatenating with <null> strings should be avoided,
because this is handled differently in different databases.
(One could choose as wel for an empty string instead of
a null, this is supported in SQL-server, but be aware there
are RDBMSses which consider an empty string equal to
a Null string.)
I would not know of a default in a text string which is a
good replacement for a null string.
(Considering other factors as usage by programs, utilities
and other databases. Empty string is not supported
in all databases.
Using a single character as a default might conflict
with a 'real' single character even if it is a space.)
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvvvvv
Does anybody know of a good 'default' for character strings
instead of using a null ?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ben brugman

> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned the
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
Robert
"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned the
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
--
Robert"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned th
e
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
--
Robert"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
Regards
Johan
Johan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan
|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
Regards
Johan
"Uri Dimant" wrote:

> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>
|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||Yes, it goes through 2 Indes Seeks, one for each table.
Regards
Johan
"Uri Dimant" wrote:

> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>
|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
Regards
Johan
"Uri Dimant" wrote:

> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>
|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh
|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:
> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > Hi!
> > I have a database wich is about 40GB. One table is very big (and many
> > rows),
> > about 20GB.
> > I can see in Sql Profiler that some questions are taking a few minutes to
> > end and during this time the processors are going 100% (sqlserver.exe).
> > After I did a maintenance job "Reorganize data and index pages" the same
> > questions ended in a few seconds only and no processor activity. Now a
> > week
> > later I have the same problem. I cannot run the maintenance job "online"
> > because it's causing locks and heavy processor activity.
> > Can I solve this problem in any other way than "Reorganize data and index
> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> > not
> > help.
> > --
> > Regards
> > Johan
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> Johan
>> Identify a slow running queries. Get the execution plan of them and see
>> does the optimizer available to use index or it doesn't
>>
>>
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> > Hi!
>> > I have a database wich is about 40GB. One table is very big (and many
>> > rows),
>> > about 20GB.
>> > I can see in Sql Profiler that some questions are taking a few minutes
>> > to
>> > end and during this time the processors are going 100% (sqlserver.exe).
>> > After I did a maintenance job "Reorganize data and index pages" the
>> > same
>> > questions ended in a few seconds only and no processor activity. Now a
>> > week
>> > later I have the same problem. I cannot run the maintenance job
>> > "online"
>> > because it's causing locks and heavy processor activity.
>> > Can I solve this problem in any other way than "Reorganize data and
>> > index
>> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> > did
>> > not
>> > help.
>> > --
>> > Regards
>> > Johan
>>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:
> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >I have identified a slow running query and run it in the execution plan.
> > The query (a join query) runs through 2 tables, both have one clustered
> > index and several non-clustered indexes.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Johan
> >>
> >> Identify a slow running queries. Get the execution plan of them and see
> >> does the optimizer available to use index or it doesn't
> >>
> >>
> >>
> >>
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> > Hi!
> >> > I have a database wich is about 40GB. One table is very big (and many
> >> > rows),
> >> > about 20GB.
> >> > I can see in Sql Profiler that some questions are taking a few minutes
> >> > to
> >> > end and during this time the processors are going 100% (sqlserver.exe).
> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> > same
> >> > questions ended in a few seconds only and no processor activity. Now a
> >> > week
> >> > later I have the same problem. I cannot run the maintenance job
> >> > "online"
> >> > because it's causing locks and heavy processor activity.
> >> > Can I solve this problem in any other way than "Reorganize data and
> >> > index
> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> > did
> >> > not
> >> > help.
> >> > --
> >> > Regards
> >> > Johan
> >>
> >>
> >>
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> And the optimizer uses indexes to create an execution plan
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>> >I have identified a slow running query and run it in the execution plan.
>> > The query (a join query) runs through 2 tables, both have one clustered
>> > index and several non-clustered indexes.
>> > --
>> > Regards
>> > Johan
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Johan
>> >>
>> >> Identify a slow running queries. Get the execution plan of them and
>> >> see
>> >> does the optimizer available to use index or it doesn't
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> >> > Hi!
>> >> > I have a database wich is about 40GB. One table is very big (and
>> >> > many
>> >> > rows),
>> >> > about 20GB.
>> >> > I can see in Sql Profiler that some questions are taking a few
>> >> > minutes
>> >> > to
>> >> > end and during this time the processors are going 100%
>> >> > (sqlserver.exe).
>> >> > After I did a maintenance job "Reorganize data and index pages" the
>> >> > same
>> >> > questions ended in a few seconds only and no processor activity. Now
>> >> > a
>> >> > week
>> >> > later I have the same problem. I cannot run the maintenance job
>> >> > "online"
>> >> > because it's causing locks and heavy processor activity.
>> >> > Can I solve this problem in any other way than "Reorganize data and
>> >> > index
>> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> >> > did
>> >> > not
>> >> > help.
>> >> > --
>> >> > Regards
>> >> > Johan
>> >>
>> >>
>> >>
>>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:
> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > Yes, it goes through 2 Indes Seeks, one for each table.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> And the optimizer uses indexes to create an execution plan
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >> >I have identified a slow running query and run it in the execution plan.
> >> > The query (a join query) runs through 2 tables, both have one clustered
> >> > index and several non-clustered indexes.
> >> > --
> >> > Regards
> >> > Johan
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Johan
> >> >>
> >> >> Identify a slow running queries. Get the execution plan of them and
> >> >> see
> >> >> does the optimizer available to use index or it doesn't
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> >> > Hi!
> >> >> > I have a database wich is about 40GB. One table is very big (and
> >> >> > many
> >> >> > rows),
> >> >> > about 20GB.
> >> >> > I can see in Sql Profiler that some questions are taking a few
> >> >> > minutes
> >> >> > to
> >> >> > end and during this time the processors are going 100%
> >> >> > (sqlserver.exe).
> >> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> >> > same
> >> >> > questions ended in a few seconds only and no processor activity. Now
> >> >> > a
> >> >> > week
> >> >> > later I have the same problem. I cannot run the maintenance job
> >> >> > "online"
> >> >> > because it's causing locks and heavy processor activity.
> >> >> > Can I solve this problem in any other way than "Reorganize data and
> >> >> > index
> >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> >> > did
> >> >> > not
> >> >> > help.
> >> >> > --
> >> >> > Regards
> >> >> > Johan
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
> > Well, how much data does the query return ?
> >
> > Perhaps , you need to re-design the indexes to be more efficient
> >
> > Look at SET STATISTICS IO ON commad. (Logical reads)
> >
> >
> >
> >
> > "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > > Yes, it goes through 2 Indes Seeks, one for each table.
> > > --
> > > Regards
> > > Johan
> > >
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> And the optimizer uses indexes to create an execution plan
> > >>
> > >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> > >> >I have identified a slow running query and run it in the execution plan.
> > >> > The query (a join query) runs through 2 tables, both have one clustered
> > >> > index and several non-clustered indexes.
> > >> > --
> > >> > Regards
> > >> > Johan
> > >> >
> > >> >
> > >> > "Uri Dimant" wrote:
> > >> >
> > >> >> Johan
> > >> >>
> > >> >> Identify a slow running queries. Get the execution plan of them and
> > >> >> see
> > >> >> does the optimizer available to use index or it doesn't
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > >> >> > Hi!
> > >> >> > I have a database wich is about 40GB. One table is very big (and
> > >> >> > many
> > >> >> > rows),
> > >> >> > about 20GB.
> > >> >> > I can see in Sql Profiler that some questions are taking a few
> > >> >> > minutes
> > >> >> > to
> > >> >> > end and during this time the processors are going 100%
> > >> >> > (sqlserver.exe).
> > >> >> > After I did a maintenance job "Reorganize data and index pages" the
> > >> >> > same
> > >> >> > questions ended in a few seconds only and no processor activity. Now
> > >> >> > a
> > >> >> > week
> > >> >> > later I have the same problem. I cannot run the maintenance job
> > >> >> > "online"
> > >> >> > because it's causing locks and heavy processor activity.
> > >> >> > Can I solve this problem in any other way than "Reorganize data and
> > >> >> > index
> > >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> > >> >> > did
> > >> >> > not
> > >> >> > help.
> > >> >> > --
> > >> >> > Regards
> > >> >> > Johan
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:

> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:

> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:

> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows)
,
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did no
t
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0
,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads
0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
>

Bad page ID

<<Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:6195408) has invalid
PFS_PAGE page header values.
Type is 0. Check type, object ID and page ID on the page.>>
The above error seems to indicate that I can check the
header values of this page.
How do I do that?
Many thanks.The message should really be "Have your support provider check the ..."
You can search for DBCC PAGE on the Net.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Karl" <kekurzenknabe@.hotmail.com> wrote in message news:2f6f01c373d6$f67b09c0$a601280a@.phx.gbl...
> <<Server: Msg 8946, Level 16, State 12, Line 2
> Table error: Allocation page (1:6195408) has invalid
> PFS_PAGE page header values.
> Type is 0. Check type, object ID and page ID on the page.>>
> The above error seems to indicate that I can check the
> header values of this page.
> How do I do that?
> Many thanks.|||Looks like you've got a corrupt PFS page. This problem cannot be
automatically repaired (even with REPAIR_ALLOW_DATA_LOSS) so you need to
restore your database from a backup after working out why the problem
occurred (check your event logs, the errorlog, run hardware diagnostics) -
or call PSS to help you.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eH7TZYhdDHA.416@.tk2msftngp13.phx.gbl...
> The message should really be "Have your support provider check the ..."
> You can search for DBCC PAGE on the Net.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Karl" <kekurzenknabe@.hotmail.com> wrote in message
news:2f6f01c373d6$f67b09c0$a601280a@.phx.gbl...
> > <<Server: Msg 8946, Level 16, State 12, Line 2
> > Table error: Allocation page (1:6195408) has invalid
> > PFS_PAGE page header values.
> > Type is 0. Check type, object ID and page ID on the page.>>
> >
> > The above error seems to indicate that I can check the
> > header values of this page.
> >
> > How do I do that?
> >
> > Many thanks.
>

Bad Directory- DTS - Global Variables

I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>