SQL,tables in a DB: table

Showing posts with label table. Show all posts

Sunday, March 25, 2012

(Optimizer) - Number of Indexes Per Table

Hi all,
We were all told that SQL 7.0 and up could use more than one index per table
during query optimization. In practice, how often does this actually occur?
If and when it does occur, how does this appear in the output of the 'Show
Estimated Execution Plan?". It seems to us that this almost never occurs.
We're seeing the first index, and then after that all HASH joins and such.
Thanks!
James HokesHi James
It happens when you have two where clauses referencing two different columns
with nonclustered indexes, and for each, there are only a few rows that
satisfy the condition.
In the showplan output, it looks like a join, but both tables are the same
table, and it's usually a hash join. SQL Server has build an internal
worktable from the results of using each of the indexes, and then those two
worktables have to be joined. SInce there are no good indexes on the
worktables, a hash join is used to find the rows in common between the
worktables.
Here is an example
USE Northwind
select * into od from [order details]
create index qnt_index on od(quantity)
create index price_index on od(unitprice)
go
select * from od
where quantity = 4 and unitprice = 6
Look at the plan for the select, after building the table and the two
indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Sounds like you have a complicated multi-table join. After the first couple
joins all you have are work tables (intermediate data results) which have no
indexes. In this case it may be necessary to do hash joins due to the lack
of indexes. The key is to whittle down the result sets to a manageable set
in the first joins using the appropriate indexes so the joining of the
intermediate tables are relatively painless. Maybe if you post a query
example and the associated query plan we can cnfirm or deny that theory.
--
Andrew J. Kelly SQL MVP
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Kalen,
Interesting that it would only happen for 'a few rows'.
That being the case, I suspect that this is due to 'total page reads', i.e.
query cost.
Is that a safe assumption?
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi James
> It happens when you have two where clauses referencing two different
columns
> with nonclustered indexes, and for each, there are only a few rows that
> satisfy the condition.
> In the showplan output, it looks like a join, but both tables are the same
> table, and it's usually a hash join. SQL Server has build an internal
> worktable from the results of using each of the indexes, and then those
two
> worktables have to be joined. SInce there are no good indexes on the
> worktables, a hash join is used to find the rows in common between the
> worktables.
> Here is an example
> USE Northwind
> select * into od from [order details]
> create index qnt_index on od(quantity)
> create index price_index on od(unitprice)
> go
> select * from od
> where quantity = 4 and unitprice = 6
> Look at the plan for the select, after building the table and the two
> indexes.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||This is a general principle for any use of nonclustered indexes. They will
only be cost effective if there are only a few rows that meet the condition,
so only a few pages need to be read. If you need to use a nc index to access
100's of rows, which means 100s of pages, it is often considered by the
optimizer to be more cost effective to just scan the table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eoTyWLMxDHA.3208@.tk2msftngp13.phx.gbl...
> Kalen,
> Interesting that it would only happen for 'a few rows'.
> That being the case, I suspect that this is due to 'total page reads',
i.e.
> query cost.
> Is that a safe assumption?
> Thanks,
> James Hokes
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Hi James
> >
> > It happens when you have two where clauses referencing two different
> columns
> > with nonclustered indexes, and for each, there are only a few rows that
> > satisfy the condition.
> >
> > In the showplan output, it looks like a join, but both tables are the
same
> > table, and it's usually a hash join. SQL Server has build an internal
> > worktable from the results of using each of the indexes, and then those
> two
> > worktables have to be joined. SInce there are no good indexes on the
> > worktables, a hash join is used to find the rows in common between the
> > worktables.
> >
> > Here is an example
> >
> > USE Northwind
> >
> > select * into od from [order details]
> > create index qnt_index on od(quantity)
> > create index price_index on od(unitprice)
> > go
> > select * from od
> > where quantity = 4 and unitprice = 6
> >
> > Look at the plan for the select, after building the table and the two
> > indexes.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Andrew,
Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
clause, multi-column output, multi-million rows. My main curiosity revolved
around the 'multiple tables per query'. I had pretty much put that out of my
mind for the last few years, because it never happens in this environment.
One of our team asked about it this morning during a debugging session, and
I remarked that I never see it. As for whittling the result sets down,
there's just no way.
Oh, I should mention that there's also no possibility of building covering
indices here, because of disk space constraints, as well as the constant
influx of new data, and the need for high UPDATE performance.
Hence, we got curious as to when it actually _would_ happen, and by the
sounds of it, pretty much never. :-)
Thanks,
James Hokes.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Sounds like you have a complicated multi-table join. After the first
couple
> joins all you have are work tables (intermediate data results) which have
no
> indexes. In this case it may be necessary to do hash joins due to the
lack
> of indexes. The key is to whittle down the result sets to a manageable
set
> in the first joins using the appropriate indexes so the joining of the
> intermediate tables are relatively painless. Maybe if you post a query
> example and the associated query plan we can cnfirm or deny that theory.
> --
> Andrew J. Kelly SQL MVP
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||However, it is not true that you won't have indexes to use even when you
have a multi-table join. If your join density (the average number of
duplicates on your join column) is not too high, and you have an index on
the join column, I have seen cases where an index is used for every table in
a 5 or 6 table join.
Have you tried index tuning wizard?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
> clause, multi-column output, multi-million rows. My main curiosity
revolved
> around the 'multiple tables per query'. I had pretty much put that out of
my
> mind for the last few years, because it never happens in this environment.
> One of our team asked about it this morning during a debugging session,
and
> I remarked that I never see it. As for whittling the result sets down,
> there's just no way.
> Oh, I should mention that there's also no possibility of building covering
> indices here, because of disk space constraints, as well as the constant
> influx of new data, and the need for high UPDATE performance.
> Hence, we got curious as to when it actually _would_ happen, and by the
> sounds of it, pretty much never. :-)
> Thanks,
> James Hokes.
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Sounds like you have a complicated multi-table join. After the first
> couple
> > joins all you have are work tables (intermediate data results) which
have
> no
> > indexes. In this case it may be necessary to do hash joins due to the
> lack
> > of indexes. The key is to whittle down the result sets to a manageable
> set
> > in the first joins using the appropriate indexes so the joining of the
> > intermediate tables are relatively painless. Maybe if you post a query
> > example and the associated query plan we can cnfirm or deny that theory.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Right.
I never said it wasn't using indexes; all of our queries do. I've tuned them
all.
I was just wondering about the 'multiple indexes per table' in the optimizer
step,
since we never see it happen. My original post referenced the fact that we
_are_ seeing the one index used per table,
but never multiple.
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, didn't mean to make it sound like it couldn't use indexes. Just
that it can and does happen<g>.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||No harm done!
I just didn't want people to spend any of their newsgroup time on that part
of the thread.
I know I only get to go on here a little bit, so I know your time and the
time of others is also valuable.
Thanks for yours!
James Hokes
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uev3vyRxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> Sorry, didn't mean to make it sound like it couldn't use indexes. Just
> that it can and does happen<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> > However, it is not true that you won't have indexes to use even when you
> > have a multi-table join. If your join density (the average number of
> > duplicates on your join column) is not too high, and you have an index
on
> > the join column, I have seen cases where an index is used for every
table
> in
> > a 5 or 6 table join.
> >
> > Have you tried index tuning wizard?
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Andrew,
> > >
> > > Right on. We're talking multi-table, multi-column JOIN, multi-column
> WHERE
> > > clause, multi-column output, multi-million rows. My main curiosity
> > revolved
> > > around the 'multiple tables per query'. I had pretty much put that out
> of
> > my
> > > mind for the last few years, because it never happens in this
> environment.
> > > One of our team asked about it this morning during a debugging
session,
> > and
> > > I remarked that I never see it. As for whittling the result sets down,
> > > there's just no way.
> > >
> > > Oh, I should mention that there's also no possibility of building
> covering
> > > indices here, because of disk space constraints, as well as the
constant
> > > influx of new data, and the need for high UPDATE performance.
> > >
> > > Hence, we got curious as to when it actually _would_ happen, and by
the
> > > sounds of it, pretty much never. :-)
> > >
> > > Thanks,
> > > James Hokes.
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Sounds like you have a complicated multi-table join. After the
first
> > > couple
> > > > joins all you have are work tables (intermediate data results) which
> > have
> > > no
> > > > indexes. In this case it may be necessary to do hash joins due to
the
> > > lack
> > > > of indexes. The key is to whittle down the result sets to a
> manageable
> > > set
> > > > in the first joins using the appropriate indexes so the joining of
the
> > > > intermediate tables are relatively painless. Maybe if you post a
> query
> >
> > > > example and the associated query plan we can cnfirm or deny that
> theory.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > > Hi all,
> > > > >
> > > > > We were all told that SQL 7.0 and up could use more than one index
> per
> > > > table
> > > > > during query optimization. In practice, how often does this
actually
> > > > occur?
> > > > >
> > > > > If and when it does occur, how does this appear in the output of
the
> > > 'Show
> > > > > Estimated Execution Plan?". It seems to us that this almost never
> > > occurs.
> > > > > We're seeing the first index, and then after that all HASH joins
and
> > > such.
> > > > >
> > > > > Thanks!
> > > > > James Hokes
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

(ODBC driver) timeout expired when query "DELETE FROM mytable"

Hi,

I'm running SQL server 2000. I have at table with about 12 million records.

I want to empty the table. I use the Query option from Tables/mytable/opentable/query.

I type in the query and select Run. The process runs for some time, then raises

an error box as follows: (title)SQL Server Enterprise Manager. The body text says:

[Microsoft][ODBC SQL Server Driver] Timeout Expired. I've tried every timeout

setting I can find. I've tried setting all timeouts to 0 (infinite) to no avail.

Please help.........

Note: I can get the table empty if I select TOP n records, then DELETE FROM but

that takes forever!! It is also not a process that's very amenable to a clean programatic

solution.

Thanks, jack

That's very strange. Setting timeout to '0' should do the trick.

If all you need to do is empty the table you could just call "truncate table table_name". That should run pretty quickly.

Sorry you're having issues. Please reactivate this thread if the 'truncate' command doesn't fix your issue.

~Warren

|||

Warren,

Thanks!

I have changed the query to "Truncate Table" and it's much faster. I haven't

tried it with the large table yet -- I'll have to reload the data before I know for

sure. I used the "Delete" command only because I hadn't stumbled on the "Truncate"

command. I still would like to know why I'm getting the timeout since I'm sure it will

jump up and bite me later because I'm dealing with such large tables, and Murphy is

looking over my shoulder -- ha!

jack

|||Consider that when you execute DELETE, the operation is logged so you are essentially "moving" the deleted rows to the transaction log with all of the associated disk IO expenses. Truncate table is also logged, but simply tells SQL Server to "drop" the data and leave the schema--it's dramatically simpler and faster.|||

William,

"Dramatically" is an understatement! I'm amazed at how quickly the table is

emptied using "Truncate". Although knowing why, it makes perfect sense. Thanks

for the expanation of the 'why', that's even more valuable than the 'how'. Is there an

explicit means of preventing the transaction logging - since it's so time costly? Is

there a downside to such a thing if it exists?

I'm still bewildered about the "timeout expired" error inspired by the length of time

the DELETE takes. I guess I'll have to pull my copy of the "Guide to..." off the shelf

and review ADO/ODBC query timeouts etc.

thanks, jack

|||

No, you can't (and should not) "turn off" the transaction log--it's your safety net. Yes, there are other operations that can be executed without the log getting in the way (like BulkCopy).

Consider that the Delete command must also delete the Index(es) for each row as well as reallocate space and execute other operations that take CPU time, RAM and disk IO. While the Truncate is fast, it also means that the server can clean up the freed space when it has idle time and it needs the space. For long operations you can set the CommandTimeout to a higher number, but whenever I find a neeed to do this I look for a more efficient way to handle the task...

hth

|||

William,

Thanks! I'm in good shape now.

jack

(Newbie) Outer Join Simulation

I have two tables invoice(fact table) and agents(dimension). Not all invoices have an agent. How do I use the agents table as an outer join? When I include agents as a dimension and look at all agents, I only get invoices that have an agent. I know I could create a view in SQL but I want to do this in the cube.

Thanks,

Bill

You can create an unknown member in your agent dimension table and connect all fact records without an agent key to that member. You will have to use TSQL to this.

SSAS2005 can help you with doing this without writing code. Have a look at inferred members in BOL!

Regards

Thomas Ivarsson

(newbie) - Stored Procedure Problem

Can anyone tell me what is wrong with this stored procedure?

CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
ALTER TABLE tblUserGroups ADD @.Perm VARCHAR(1) NULL
GO

When I click on Check Syntax, I get 'Error 170 - Line 4: Incorrect syntax near @.Perm'

I have checked the syntax for the ALTER command, and it looks correct to me...

This is my first day at using SQL Server in anger, so any help appreciated :)You must use dynamic sql for that, check out :

http://www.sqlteam.com/item.asp?ItemID=4599

The following will work

CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
exec ('ALTER TABLE tblUserGroups ADD ' + @.Perm + 'VARCHAR(1) NULL')
GO|||Oh wow! Thanks for that - I didn't even know you could do that directly in SQL server - I'm used to doing it in ASP pages, of course, but this DB is new to me :)

I'm off to check your link now - thanks very much for taking the time to post.

Mark.sql

Thursday, March 22, 2012

(Error) Conversion from "DT_TEXT" to "DT_WSTR" is not supported.

Below is the error I get when trying to convert a Visual FoxPro memo field to a DT_WSTR (4000 ) in a SQL table. It does not let me convert a DT_TEXT to a DT_WSTR.

Thank in advance

TITLE: Editing Component

The component is not in a valid state. The validation errors are:

Error at NMF [Data Conversion [6328]]: Conversion from "DT_TEXT" to "DT_WSTR" is not supported.

Do you want the component to fix these errors automatically?

BUTTONS:

&Yes

&No

Cancel

Long Object types such as DT_TEXT have limited conversion support. Since DT_TEXT is non-unicode, likely you would have to convert in two steps:

DT_TEXT -> DT_STR using same codepage

DT_STR -> DT_WSTR.

|||

Mark's spot on of course. Stick the following expression into a Derived Column Component

(DT_WSTR, 4000) (DT_STR, 4000, 1252) [ColumnName]

-Jamie

(almost) duplicates

I have a script to remove duplicate records from a table. I have since
found out that I have dups with a date/time stamp that are a few seconds off
(thus, I guess technically making them not duplicates). How can I properly
get rid of the later records. Again the only difference is the seconds in a
date/time field.
JeffPlease post your table structures and sample data along with the script
which you use to distingush the duplicates. Without clear specifications and
an usable repro, it is hard for others to understand what would
"technically" make some rows non-duplicates when in reality, they are.
--
Anith|||Any other column to identitfy the row?
delete t
where exists (select * from t as t1 where t1.col1 = t.col1 and
t1.col_datetime < t.col_datetime)
AMB
"J. Clarke" wrote:
> I have a script to remove duplicate records from a table. I have since
> found out that I have dups with a date/time stamp that are a few seconds off
> (thus, I guess technically making them not duplicates). How can I properly
> get rid of the later records. Again the only difference is the seconds in a
> date/time field.
> Jeff
>
>|||Hmmm...wouldn't this delete everything except the most recent record (or am
I missreading this)?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
> Any other column to identitfy the row?
> delete t
> where exists (select * from t as t1 where t1.col1 = t.col1 and
> t1.col_datetime < t.col_datetime)
>
> AMB
> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few seconds
>> off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>|||I was afraid of this. Basically all the fields in a multiple rows contain
the same values EXCEPT the datetime field. So technically their not
duplicates (the datetimes are a couple of seconds off from each other).
However, I know the front end application had an error that was sticking
them in. I need to keep the 1st record it stuck in and get rid of the rest
that are a few seconds off from the 1st record.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%239EpuQ7MFHA.2656@.TK2MSFTNGP10.phx.gbl...
> Please post your table structures and sample data along with the script
> which you use to distingush the duplicates. Without clear specifications
> and an usable repro, it is hard for others to understand what would
> "technically" make some rows non-duplicates when in reality, they are.
> --
> Anith
>|||No, it wouldn't . this is a correlated subquery... Take a look at the table
alias t and t1... the same table but treated as 2 different tables...
This takes a row from table t and is trying to decide whether or not to
delete it... It looks to find a row in the same table ( but aliased to t1)
that has the same col1 ( supposedly the id column) but which has a date
which is < than the date of the row in t you are considering for
deletion... If that expression is true, that means there is another row
with the same key but which has an earlier date, so this row must be the
additionaly row that was added later , and there fore should be deleted.
Hope this helps..
--
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
"J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hmmm...wouldn't this delete everything except the most recent record (or
> am I missreading this)?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
>> Any other column to identitfy the row?
>> delete t
>> where exists (select * from t as t1 where t1.col1 = t.col1 and
>> t1.col_datetime < t.col_datetime)
>>
>> AMB
>> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few seconds
>> off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>
>|||On Mon, 28 Mar 2005 10:35:43 -0500, J. Clarke wrote:
>I have a script to remove duplicate records from a table. I have since
>found out that I have dups with a date/time stamp that are a few seconds off
>(thus, I guess technically making them not duplicates). How can I properly
>get rid of the later records. Again the only difference is the seconds in a
>date/time field.
>Jeff
>
Hi Jeff,
Assuming Col1, Col2 and Col3 are exactly the same and Col4 is the
datetime column with a few seconds difference, and that you want to
delete the duplicates if the time difference is no more than 20 seconds,
use:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.Col1 = MyTable.Col1
AND b.Col2 = MyTable.Col2
AND b.Col3 = MyTable.Col3
AND b.Col4 < MyTable.Col4
AND b.Col4 >= DATEDIFF(second, 20, MyTable.Col4))
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||similar to Hugo's idea you could also try the following:
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1,
and my.Col2 = gb.Col2,
and my.Col3 = gb.Col3,
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
This does not take into account any specific time lag. This would find
duplicates that are days apart not just 20 seconds.
This uses a subquery "group by" to identify distinct records and the
"earliest" (min) date stamp and then matches it back and drops all the
matching records that are not the earliest datetime.
The magic comes from the LEFT JOIN and the fact that MinCol4 IS NULL
Good luck.
--
Message posted via http://www.sqlmonster.com|||Just to make you feel better, you can try this test to prove the theory:
(also note: I inadvertantly had commas in the "and" section of the left
join)
if exists(select name from sysobjects where name = 'MyTable' and type = 'U')
drop table MyTable
go
create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
insert MyTable select 1, 'Lucy', 101, '1/1/2005'
insert MyTable select 1, 'Lucy', 101, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/1/2005'
insert MyTable select 2, 'Ricky', 102, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/3/2005'
insert MyTable select 3, 'Fred', 103, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/2/2005'
insert MyTable select 4, 'Ethel', 104, '1/3/2005'
insert MyTable select 4, 'Ethel', 104, '1/4/2005'
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1
and my.Col2 = gb.Col2
and my.Col3 = gb.Col3
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
select * from MyTable
--
Message posted via http://www.sqlmonster.com|||Ah...Thanks for the explaination Wayne. That helps alot.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%232IH7KGNFHA.1476@.TK2MSFTNGP09.phx.gbl...
> No, it wouldn't . this is a correlated subquery... Take a look at the
> table alias t and t1... the same table but treated as 2 different
> tables...
> This takes a row from table t and is trying to decide whether or not to
> delete it... It looks to find a row in the same table ( but aliased to t1)
> that has the same col1 ( supposedly the id column) but which has a date
> which is < than the date of the row in t you are considering for
> deletion... If that expression is true, that means there is another row
> with the same key but which has an earlier date, so this row must be the
> additionaly row that was added later , and there fore should be deleted.
> Hope this helps..
> --
> 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
> "J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
> news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
>> Hmmm...wouldn't this delete everything except the most recent record (or
>> am I missreading this)?
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
>> Any other column to identitfy the row?
>> delete t
>> where exists (select * from t as t1 where t1.col1 = t.col1 and
>> t1.col_datetime < t.col_datetime)
>>
>> AMB
>> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few
>> seconds off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>
>>
>|||Thanks Hugo - I'm not sure if this will work for me as the time may expand
beyond a set periord of seconds (maybe to a few minutes?). I get the gist
though - I appreciate your help and explaination!
Jeff
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:92nj411t3o257ghsm8jfm3oicddhrc6p3k@.4ax.com...
> On Mon, 28 Mar 2005 10:35:43 -0500, J. Clarke wrote:
>>I have a script to remove duplicate records from a table. I have since
>>found out that I have dups with a date/time stamp that are a few seconds
>>off
>>(thus, I guess technically making them not duplicates). How can I
>>properly
>>get rid of the later records. Again the only difference is the seconds in
>>a
>>date/time field.
>>Jeff
> Hi Jeff,
> Assuming Col1, Col2 and Col3 are exactly the same and Col4 is the
> datetime column with a few seconds difference, and that you want to
> delete the duplicates if the time difference is no more than 20 seconds,
> use:
> DELETE FROM MyTable
> WHERE EXISTS
> (SELECT *
> FROM MyTable AS b
> WHERE b.Col1 = MyTable.Col1
> AND b.Col2 = MyTable.Col2
> AND b.Col3 = MyTable.Col3
> AND b.Col4 < MyTable.Col4
> AND b.Col4 >= DATEDIFF(second, 20, MyTable.Col4))
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Wow! Thanks for the explaination and the example. Your example I think may
work for the best of me. From one example I was peeking at I may up to 4000
'dups' off the 1st record (yikes!). I'm concerned tho, that I may have an
actual record on another day and I need to ensure I'm not including those
puppies
Jeff
"Geoffrey Kahan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b532bdf904ce437fb849575d7ccfd8fb@.SQLMonster.com...
> Just to make you feel better, you can try this test to prove the theory:
> (also note: I inadvertantly had commas in the "and" section of the left
> join)
> if exists(select name from sysobjects where name = 'MyTable' and type => 'U')
> drop table MyTable
> go
> create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
> insert MyTable select 1, 'Lucy', 101, '1/1/2005'
> insert MyTable select 1, 'Lucy', 101, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/1/2005'
> insert MyTable select 2, 'Ricky', 102, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/3/2005'
> insert MyTable select 3, 'Fred', 103, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/2/2005'
> insert MyTable select 4, 'Ethel', 104, '1/3/2005'
> insert MyTable select 4, 'Ethel', 104, '1/4/2005'
> DELETE MyTable
> from MyTable my
> LEFT JOIN (
> select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
> group by Col1, Col2, Col3) as gb
> ON my.Col1 = gb.Col1
> and my.Col2 = gb.Col2
> and my.Col3 = gb.Col3
> and my.Col4 = gb.MinCol4
> where gb.MinCol4 IS NULL
> select * from MyTable
> --
> Message posted via http://www.sqlmonster.com|||I don't think this is gonna work Geoffrey - I have 928,457 records in the DB
and the scripts been running now for 16+ hours - I don't think my sites are
gonna want to be shutdown this long. It's on my local box, nothing other
than SQL QA is connected to it.
Here is my complete script (maybe I did it wrong):
DELETE dbo.TRM_VISN_REPORT
from dbo.TRM_VISN_REPORT my
LEFT JOIN (
select VistaUserName, MIN(DateTimeofCall)as MinDateTimeofCall,
UserLocStation, CallDuration, CPTCode, CPTDescription, ClinicalCall,
RegisteredPatient, ChiefComplaint, FollowupIntRec,
FollowupIntAct, FollowupLoc, CallerResponse, CallerArea, VEJDIFN,
TypeOfCall, CallFiledAtStation, PatientName, SSN
from dbo.TRM_VISN_REPORT
group by VistaUserName, UserLocStation, CallDuration, CPTCode,
CPTDescription, ClinicalCall, RegisteredPatient, ChiefComplaint,
FollowupIntRec,
FollowupIntAct, FollowupLoc, CallerResponse, CallerArea, VEJDIFN,
TypeOfCall, CallFiledAtStation, PatientName, SSN) as gb
ON my.VistaUserName = gb.VistaUserName
and my.DateTimeofCall = gb.MinDateTimeofCall
and my.UserLocStation = gb.UserLocStation
and my.CallDuration = gb.CallDuration
and my.CPTCode = gb.CPTCode
and my.CPTDescription = gb.CPTDescription
and my.ClinicalCall = gb.ClinicalCall
and my.RegisteredPatient = gb.RegisteredPatient
and my.ChiefComplaint = gb.ChiefComplaint
and my.FollowupIntRec = gb.FollowupIntRec
and my.FollowupIntAct = gb.FollowupIntAct
and my.FollowupLoc = gb.FollowupLoc
and my.CallerResponse = gb.CallerResponse
and my.CallerArea = gb.CallerArea
and my.VEJDIFN = gb.VEJDIFN
and my.TypeOfCall = gb.TypeOfCall
and my.CallFiledAtStation = gb.CallFiledAtStation
and my.PatientName = gb.PatientName
and my.SSN = gb.SSN
where gb.MinDateTimeofCall IS NULL
Jeff
"Geoffrey Kahan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b532bdf904ce437fb849575d7ccfd8fb@.SQLMonster.com...
> Just to make you feel better, you can try this test to prove the theory:
> (also note: I inadvertantly had commas in the "and" section of the left
> join)
> if exists(select name from sysobjects where name = 'MyTable' and type ='U')
> drop table MyTable
> go
> create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
> insert MyTable select 1, 'Lucy', 101, '1/1/2005'
> insert MyTable select 1, 'Lucy', 101, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/1/2005'
> insert MyTable select 2, 'Ricky', 102, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/3/2005'
> insert MyTable select 3, 'Fred', 103, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/2/2005'
> insert MyTable select 4, 'Ethel', 104, '1/3/2005'
> insert MyTable select 4, 'Ethel', 104, '1/4/2005'
> DELETE MyTable
> from MyTable my
> LEFT JOIN (
> select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
> group by Col1, Col2, Col3) as gb
> ON my.Col1 = gb.Col1
> and my.Col2 = gb.Col2
> and my.Col3 = gb.Col3
> and my.Col4 = gb.MinCol4
> where gb.MinCol4 IS NULL
> select * from MyTable
> --
> Message posted via http://www.sqlmonster.comsql

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJ
Descending.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ
|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJDescending.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJDescending.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

((cdate("1/1/2001")+30) as task_due_date (Not working)

I have the below function written in VB, and everything works fine
EXCEPT that the sql code is not executing correctly. The date field is
in the table always shows "12/1/1899 11:59:17 PM" no other date. I'm
should have the date of the input into the function + or - the integer
in the task_due_days field.

for example (cdate("1/1/2002")+30) as task_due_date

What am I doing wrong?

Function SetTasks(trans_id As Long, trans_type As Integer, event_date
As Date)

Dim task As String

task = "Insert into tbl_tasks
(trans_id,task_name,Task_due_date,comments) SELECT (" & trans_id & ")
as trans_id,task_name,(cdate(" & event_date & ")+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=" &
trans_type

Debug.Print task

DoCmd.RunSQL (task)

End Function

the actual SQL code is....

Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

ANY HELP IS GREATLY APPRECIATED!(stoppal@.hotmail.com) writes:
> the actual SQL code is....
> Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
> SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
> task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

Apparently you are not using SQL Server, as there is no cdate function
in SQL Serever.

I can tell what the problem is though: 1/1/2001 = 0 with integer division,
and with floating-point division you get 0.0005. Since you got
11:59:17, I guess that in whatever you are using, you have floating-
point division. (In SQL Server you would get integer division here.)

So you need to delimit the date string. In SQL Server that would be
'1/1/2001'. But it looks a bit likely you are using Access, in which
case maybe ## is better. But you better ask in comp.databases.ms-access
it you are uncertain.

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

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

'ß' = 'ss' - are they equal?

Hi,
I've set up an NVARCHAR table column having a UNIQUE constraint. This = UNIQUE constraint is supposed to keep double entries from being entered = into that column. So far so good...
In my new project I'm quite baffled about the fact that SQL Server 2000 = seems to believe that '=DF' and 'ss' are identical!
This assumption is only true for special cases. The German words = "Ma=DFe" and "Masse", for instance, have totally different meanings = ("measures", "mass"/"crowd").
How can I set up my table column definition (COLLATE perhaps?) in SQL = Server 2000 to have it regard '=DF' and 'ss' being distinct characters?
Your help is quite appreciated.
Best regards,
Axel DahmenAlex,
http://www.ureader.com/message/1343195.aspx has a conversation on this
subject you may find useful.
Most languages (especially English) have a fair number of homographs, which
are words spelled the same but with different pronunciations and meanings.
"Wind" can mean 'moving air' or 'tightening a spring', etc. I think that
you have probably run into this with your German words.
RLF
"Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
Hi,
I've set up an NVARCHAR table column having a UNIQUE constraint. This UNIQUE
constraint is supposed to keep double entries from being entered into that
column. So far so good...
In my new project I'm quite baffled about the fact that SQL Server 2000
seems to believe that 'ß' and 'ss' are identical!
This assumption is only true for special cases. The German words "Maße" and
"Masse", for instance, have totally different meanings ("measures",
"mass"/"crowd").
How can I set up my table column definition (COLLATE perhaps?) in SQL Server
2000 to have it regard 'ß' and 'ss' being distinct characters?
Your help is quite appreciated.
Best regards,
Axel Dahmen|||Great, Russel, thanks!
From the conversation you mentioned I've learned that according to DIN =and ANSI-SQL "=DF" equals to "ss". But in fact this isn't true anymore =since German has been updated to new spelling rules a few years ago.
With the spelling reform we've got they changed the meaning of "=DF". It =has now become a letter of its own and is no more a replacement for ="ss". They are even planning to introduce a new, "capital =DF" letter.
With the old spelling rules words like "Ma=DFe" were equal to "Masse". =But nowadays this isn't true anymore.
Are there any plans to update ANSI-SQL and SQL-Server collation rules =according to current German rules?
TIA,
Axel Dahmen
"Russell Fields" <russellfields@.nomail.com> schrieb im Newsbeitrag =news:e6VOWBxQIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Alex,
> > http://www.ureader.com/message/1343195.aspx has a conversation on this =
> subject you may find useful.
> > Most languages (especially English) have a fair number of homographs, =which > are words spelled the same but with different pronunciations and =meanings. > "Wind" can mean 'moving air' or 'tightening a spring', etc. I think =that > you have probably run into this with your German words.
> > RLF
> > > "Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message > news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> > I've set up an NVARCHAR table column having a UNIQUE constraint. This =UNIQUE > constraint is supposed to keep double entries from being entered into =that > column. So far so good...
> > In my new project I'm quite baffled about the fact that SQL Server =2000 > seems to believe that '=DF' and 'ss' are identical!
> > This assumption is only true for special cases. The German words ="Ma=DFe" and > "Masse", for instance, have totally different meanings ("measures", > "mass"/"crowd").
> > How can I set up my table column definition (COLLATE perhaps?) in SQL =Server > 2000 to have it regard '=DF' and 'ss' being distinct characters?
> > Your help is quite appreciated.
> > Best regards,
> Axel Dahmen
> > >=20|||Axel,
I imagine from the ANSI-SQL docuements that SQL is tracking with the ISO
standard on this. I see that the ISO collation standard was updated in
2007, but I am not willing to pay to find out what changed.
http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=44872
So, I don't know who to ask to get a good answer for you about what SQL is
doing. (Maybe Joe Celko has an update.)
In the meantime, if is worth the extra work for you, you can use this
workaround.
SELECT * FROM Composers
WHERE LastName = 'Strauß'
AND LastName COLLATE Latin1_General_BIN = 'Strauß'
The extra binary collation compare will ensure that you get 'Strauß' and do
not get ' Strauss'.
RLF
"Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
news:%23EJWJu4QIHA.5400@.TK2MSFTNGP04.phx.gbl...
Great, Russel, thanks!
From the conversation you mentioned I've learned that according to DIN and
ANSI-SQL "ß" equals to "ss". But in fact this isn't true anymore since
German has been updated to new spelling rules a few years ago.
With the spelling reform we've got they changed the meaning of "ß". It has
now become a letter of its own and is no more a replacement for "ss". They
are even planning to introduce a new, "capital ß" letter.
With the old spelling rules words like "Maße" were equal to "Masse". But
nowadays this isn't true anymore.
Are there any plans to update ANSI-SQL and SQL-Server collation rules
according to current German rules?
TIA,
Axel Dahmen
"Russell Fields" <russellfields@.nomail.com> schrieb im Newsbeitrag
news:e6VOWBxQIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Alex,
> http://www.ureader.com/message/1343195.aspx has a conversation on this
> subject you may find useful.
> Most languages (especially English) have a fair number of homographs,
> which
> are words spelled the same but with different pronunciations and meanings.
> "Wind" can mean 'moving air' or 'tightening a spring', etc. I think that
> you have probably run into this with your German words.
> RLF
>
> "Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
> news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I've set up an NVARCHAR table column having a UNIQUE constraint. This
> UNIQUE
> constraint is supposed to keep double entries from being entered into that
> column. So far so good...
> In my new project I'm quite baffled about the fact that SQL Server 2000
> seems to believe that 'ß' and 'ss' are identical!
> This assumption is only true for special cases. The German words "Maße"
> and
> "Masse", for instance, have totally different meanings ("measures",
> "mass"/"crowd").
> How can I set up my table column definition (COLLATE perhaps?) in SQL
> Server
> 2000 to have it regard 'ß' and 'ss' being distinct characters?
> Your help is quite appreciated.
> Best regards,
> Axel Dahmen
>
>

Tuesday, March 20, 2012

"where" statement in list

I have one dataset and two lists with one table in each list. the two tables should get their data from the same dataset. but the tables need different "where" statements. in the dataset I only can define one "where" statment. it is possible to displace the "where" statment into the two lists? they should access to the dataset with their own "where" statement.

Depending on the complexity of your where clause you could remove the where clause to retrieve the data for both lists in one dataset and then use the filter tab of the table to make it display just the data you want

|||

My where statement for the first table is

WHERE (dbo.endgeraet.ansprechpartner IN (@.usernr))

when I remove it in the dataset and put it in the table as filter, the preview gives me an error

"the processing of filterexpression for the table 'table1' cannot be performed. cannot compare data of types system.int32 and system.string. please check the data type returned by the filterexpression"

I don't understand that, because dbo.endgeraet.ansprechpartner is an INT and usernr is an INT, too.

|||

Check the following:

make sure the parameter in not multivalue|||

Ok, my parameter is not multivalue and the filter in the table properties looks like this:

What else could be wrong?

|||

*deleted*

|||In the value column put Parameters!usernr.Value instead @.usernr|||Ok, I changed it, but the error is still there. what else could be wrong?

|||why dont you use 2 different datasets?
one for each list

greets gerhard|||

A valid suggestion and definately the easiest to implement.

I'll persevery merely to see what the problem is with jori0001's report. Have you created a report parameter called "usernr". If so can you post a screenshot of the definition of this parameter in the parameters pane?

|||

@. Gerhard

when I use two different datasets, my report would totally have 32 datasets. if I use one dataset for two tables my report will only have 16 datasets.

@. Adam

Thank you, the parameters pane was the right hint. the parameter "usernr" had the data type "string". I changed it into "integer" and now it works. but I had to do one more change. the filter must be set in the list properties, not in the table properties.

"Views"(Edited)

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

sql

"Views"(Edited)

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

"Views"

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

Monday, March 19, 2012

"Table '?' could not be loaded. Column '?' does not exist"

Hi,

I am getting message while modifying one table in SSMS like "Table '?' could not be loaded. Column '?' does not exist" . Howevery particular column exist in table and even i am able to modify it with T-SQL.

Any Idea?

Please post the error message, including the stack trace from the error window.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Thanks for your reply. I got the work around. I created another table with same schema with different name and also created all FKs and constrains with different name and it worked.

"Table ''?'' could not be loaded. Column ''?'' does not exist"

Hi,

Any Idea?

Please post the error message, including the stack trace from the error window.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Thanks for your reply. I got the work around. I created another table with same schema with different name and also created all FKs and constrains with different name and it worked.

"Static variable" in function

Hi!

I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?

Thank you!

No. There is no such functionality in TSQL. Your approach sounds fine. Alternatively, you can define a scalar UDF that returns the constant value instead of storing it in the table.

Sunday, March 25, 2012

(Optimizer) - Number of Indexes Per Table

(ODBC driver) timeout expired when query "DELETE FROM mytable"

(Newbie) Outer Join Simulation

(newbie) - Stored Procedure Problem

Thursday, March 22, 2012

(Error) Conversion from "DT_TEXT" to "DT_WSTR" is not supported.

(almost) duplicates

(almost) duplicates

(almost) duplicates

'(-)' in list of index columns which I get after sp_helpindexes

'(-)' in list of index columns which I get after sp_helpindexes

'(-)' in list of index columns which I get after sp_helpindexes

((cdate("1/1/2001")+30) as task_due_date (Not working)

'ß' = 'ss' - are they equal?

Tuesday, March 20, 2012

"where" statement in list

"Views"(Edited)

"Views"(Edited)

"Views"

Monday, March 19, 2012

"Table '?' could not be loaded. Column '?' does not exist"

"Table ''?'' could not be loaded. Column ''?'' does not exist"

"Static variable" in function

SQL,tables in a DB

Blog Archive

About Me