Has anyone come up with a solution for the following scenario yet?
We have many reports which have input parameters that are lists of values.
As an example the user might want to report on 30 of 100 available product
codes, or 12 of 50,000 account numbers.
What might be an idea would be to store the lists of values that userâ's want
to report on, in a general purpose Lists table. One column would be a List
Name and the other a delimited list of values. This List table could then be
used as the input to a Drop Down box for the relevant report parameter.
Has anybody tried something similar, or does anyone have any other
suggestions?
(Weâ're aware that multi-select list boxes will be available in SSRS 2005,
but that would still be a bit tiresome for a user that needs to select a few
values from a long list).
Regards,
John MarshHi John,
I have done similar thing like that.
i have a lookup parameter report which is a webpage with listbox and the
value is populated by calling a param report. The param report contain 2
fileds and it like name- value pair. The report is called programttically and
the result is returned in xml format so that it can be bind to a dataset and
to tht listbox or dropdown box.
So this act as a lists of values as report parameter input..
Regards
Bava
"John Marsh" wrote:
> Has anyone come up with a solution for the following scenario yet?
> We have many reports which have input parameters that are lists of values.
> As an example the user might want to report on 30 of 100 available product
> codes, or 12 of 50,000 account numbers.
> What might be an idea would be to store the lists of values that userâ's want
> to report on, in a general purpose Lists table. One column would be a List
> Name and the other a delimited list of values. This List table could then be
> used as the input to a Drop Down box for the relevant report parameter.
> Has anybody tried something similar, or does anyone have any other
> suggestions?
> (Weâ're aware that multi-select list boxes will be available in SSRS 2005,
> but that would still be a bit tiresome for a user that needs to select a few
> values from a long list).
> Regards,
> John Marsh
>
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Wednesday, March 28, 2012
precisions of DateTime
Hi,
I am trying to add a uniqueness constraint on a DATETIME column of a table,
which contains rows with timestamp values differ by several milliseconds.
Upon adding the unique index, SQL complains that table contains duplicate
values are found as they are only comparing up to the second interval. Sinc
e
my database has a fixed schema, it is not possible to introduce additional
columns. Any ideas to solve this problem? Thanks.> I am trying to add a uniqueness constraint on a DATETIME column of a
table,
This is not a great idea. How can you possibly enforce that two events
can't happen at the same time? Why would you want to identify a row based
on date and time down to the millisecond? I can see why you would want a
clustered index on a datetime column, but I can't comprehend the purpose of
enforcing uniqueness there.
A|||We have an automated data loader application that loads data into the
database. In order to avoid loading duplicate rows, we are trying to add
some checking mechanism. Since the column schema is fixed, we are not able
to add Identity columns so we will have to add uniqueness constraints agains
t
the existing columns. There are 15 columns in total and I am unsure whether
it is a good idea to make add the uniqueness constraints against all 15
columns. Please advise.
"Aaron [SQL Server MVP]" wrote:
> table,
> This is not a great idea. How can you possibly enforce that two events
> can't happen at the same time? Why would you want to identify a row based
> on date and time down to the millisecond? I can see why you would want a
> clustered index on a datetime column, but I can't comprehend the purpose o
f
> enforcing uniqueness there.
> A
>
>|||> the existing columns. There are 15 columns in total and I am unsure
whether
> it is a good idea to make add the uniqueness constraints against all 15
> columns. Please advise.
Is it really only a duplicate if ALL 15 rows are identical? That seems to
be a much different set of criteria than just checking the datetime value.
Why does the application load duplicate rows in the first place? Can't you
fix that problem? You seem to be trying to solve application problems with
constraints...|||If all 15 rows are identical then the row is identical. The application is
maintained by another team and changing its design would be my last resort.
Because the INSERT INTO statement is "hard-coded" within the application, I
am trying to find solutions at the database level to fix this. Another thin
g
is, users can load data manually into the database and we are trying to
eliminate rows that were manually loaded. Do you have any ideas to solve
this problem?
Thanks.
"Aaron [SQL Server MVP]" wrote:
> whether
> Is it really only a duplicate if ALL 15 rows are identical? That seems to
> be a much different set of criteria than just checking the datetime value.
> Why does the application load duplicate rows in the first place? Can't yo
u
> fix that problem? You seem to be trying to solve application problems wit
h
> constraints...
>
>|||Does the application *only* insert? If so, you could make a new table with
the structure of the real table, swap the names, and have the app insert
into the copy. Then, use a scheduled job to insert *unique* rows into the
real table, and then either log or discard the duplicates.
Better yet, make the app call a stored procedure instead of running its own
queries, then you can have a lot more control.
I'm still not clear on how defining a unique constraint on a datetime column
would ever have done anything to solve this problem.
"Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> If all 15 rows are identical then the row is identical. The application
is
> maintained by another team and changing its design would be my last
resort.
> Because the INSERT INTO statement is "hard-coded" within the application,
I
> am trying to find solutions at the database level to fix this. Another
thing
> is, users can load data manually into the database and we are trying to
> eliminate rows that were manually loaded. Do you have any ideas to solve
> this problem?
> Thanks.
> "Aaron [SQL Server MVP]" wrote:
>
15
to
value.
you
with|||Defining unique constraint on datetime is just a way to avoid loading
duplicate rows. With few exceptions, the values in my datetime column will
be able to determine whether the rows are re-imported. This is why I though
t
of that solution at the beginning.
Thanks for your tips. I will take this up with the application owner.
"Aaron [SQL Server MVP]" wrote:
> Does the application *only* insert? If so, you could make a new table wit
h
> the structure of the real table, swap the names, and have the app insert
> into the copy. Then, use a scheduled job to insert *unique* rows into the
> real table, and then either log or discard the duplicates.
> Better yet, make the app call a stored procedure instead of running its ow
n
> queries, then you can have a lot more control.
> I'm still not clear on how defining a unique constraint on a datetime colu
mn
> would ever have done anything to solve this problem.
>
>
> "Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
> news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> is
> resort.
> I
> thing
> 15
> to
> value.
> you
> with
>
>
I am trying to add a uniqueness constraint on a DATETIME column of a table,
which contains rows with timestamp values differ by several milliseconds.
Upon adding the unique index, SQL complains that table contains duplicate
values are found as they are only comparing up to the second interval. Sinc
e
my database has a fixed schema, it is not possible to introduce additional
columns. Any ideas to solve this problem? Thanks.> I am trying to add a uniqueness constraint on a DATETIME column of a
table,
This is not a great idea. How can you possibly enforce that two events
can't happen at the same time? Why would you want to identify a row based
on date and time down to the millisecond? I can see why you would want a
clustered index on a datetime column, but I can't comprehend the purpose of
enforcing uniqueness there.
A|||We have an automated data loader application that loads data into the
database. In order to avoid loading duplicate rows, we are trying to add
some checking mechanism. Since the column schema is fixed, we are not able
to add Identity columns so we will have to add uniqueness constraints agains
t
the existing columns. There are 15 columns in total and I am unsure whether
it is a good idea to make add the uniqueness constraints against all 15
columns. Please advise.
"Aaron [SQL Server MVP]" wrote:
> table,
> This is not a great idea. How can you possibly enforce that two events
> can't happen at the same time? Why would you want to identify a row based
> on date and time down to the millisecond? I can see why you would want a
> clustered index on a datetime column, but I can't comprehend the purpose o
f
> enforcing uniqueness there.
> A
>
>|||> the existing columns. There are 15 columns in total and I am unsure
whether
> it is a good idea to make add the uniqueness constraints against all 15
> columns. Please advise.
Is it really only a duplicate if ALL 15 rows are identical? That seems to
be a much different set of criteria than just checking the datetime value.
Why does the application load duplicate rows in the first place? Can't you
fix that problem? You seem to be trying to solve application problems with
constraints...|||If all 15 rows are identical then the row is identical. The application is
maintained by another team and changing its design would be my last resort.
Because the INSERT INTO statement is "hard-coded" within the application, I
am trying to find solutions at the database level to fix this. Another thin
g
is, users can load data manually into the database and we are trying to
eliminate rows that were manually loaded. Do you have any ideas to solve
this problem?
Thanks.
"Aaron [SQL Server MVP]" wrote:
> whether
> Is it really only a duplicate if ALL 15 rows are identical? That seems to
> be a much different set of criteria than just checking the datetime value.
> Why does the application load duplicate rows in the first place? Can't yo
u
> fix that problem? You seem to be trying to solve application problems wit
h
> constraints...
>
>|||Does the application *only* insert? If so, you could make a new table with
the structure of the real table, swap the names, and have the app insert
into the copy. Then, use a scheduled job to insert *unique* rows into the
real table, and then either log or discard the duplicates.
Better yet, make the app call a stored procedure instead of running its own
queries, then you can have a lot more control.
I'm still not clear on how defining a unique constraint on a datetime column
would ever have done anything to solve this problem.
"Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> If all 15 rows are identical then the row is identical. The application
is
> maintained by another team and changing its design would be my last
resort.
> Because the INSERT INTO statement is "hard-coded" within the application,
I
> am trying to find solutions at the database level to fix this. Another
thing
> is, users can load data manually into the database and we are trying to
> eliminate rows that were manually loaded. Do you have any ideas to solve
> this problem?
> Thanks.
> "Aaron [SQL Server MVP]" wrote:
>
15
to
value.
you
with|||Defining unique constraint on datetime is just a way to avoid loading
duplicate rows. With few exceptions, the values in my datetime column will
be able to determine whether the rows are re-imported. This is why I though
t
of that solution at the beginning.
Thanks for your tips. I will take this up with the application owner.
"Aaron [SQL Server MVP]" wrote:
> Does the application *only* insert? If so, you could make a new table wit
h
> the structure of the real table, swap the names, and have the app insert
> into the copy. Then, use a scheduled job to insert *unique* rows into the
> real table, and then either log or discard the duplicates.
> Better yet, make the app call a stored procedure instead of running its ow
n
> queries, then you can have a lot more control.
> I'm still not clear on how defining a unique constraint on a datetime colu
mn
> would ever have done anything to solve this problem.
>
>
> "Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
> news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> is
> resort.
> I
> thing
> 15
> to
> value.
> you
> with
>
>
Monday, March 26, 2012
poweroff & index
power-off happened, and server [w2k, sql2k] stopped during working day.
quick analyze showed there is no evident data loss [last changed values are
still there], so they restarted the server and continued to work.
later, some mallfunctions [broken relations] assured us that indices [at
least some of them] are damaged.
questionn is: how are primary key [and other uniques] indices handled?
is it possible, if index not repaired, doubled [not-unique] key being
inserted?
what is the most efficient way to examine if this happened.
after rebuilding primary key index [if broken], what happens with duplicated
keys?
some comments or experience about this?
thnx.
SQL Server should in many cases handle a hard power off just fine. There are two cases where this
can go bad, though:
1. Hardware caching without battery backup. SQL Server depends on what has been written is actually
on the disk. It directs the OS to not cache write operations, but if you have HW caching, then all
bets are off.
2. Torn page. SQL Server directs the OS to do write in page sizes as smallest size. So the OS will
direct the disk subsystem to do the write, but typically smallest size for write operation at disk
level is sector (typically 512 bytes) so a page can be partially written.
The best place to start if you want to read more about this are below two articles:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
Also, I suggest you visit for lots of good stuff related to this topic.
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:eEYuqK4wHHA.4300@.TK2MSFTNGP04.phx.gbl...
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are still there], so they
> restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at least some of them] are
> damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated keys?
> some comments or experience about this?
> thnx.
>
|||I don't no if you have the resources but.
Why don't you make a online full-backup.
Restore it....do the rebuilld index or repairs and see what happens.
Offcourse you can also query the database to check for duplicate keys.
I don't think this is happening.
But don't take my word for it.
Make sure you have a recent a log back-up and get to work!
Good luck!
"sali" wrote:
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are
> still there], so they restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at
> least some of them] are damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being
> inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated
> keys?
> some comments or experience about this?
> thnx.
>
>
|||thnx
right now server is working, but i want to be sure there is no hidden
problem with corrupted primary & unique keys.
i am affraid that simple :
select count(*), prim_key
group by prim_key
wouldn't show me there is a prim_keys with count()>1
or could i may select with prime_key index switched_off [simple flat scan]?
it all may depends of internal architecture of database engine, i've got a
link about it and going to examine it.
"Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
napisao u poruci interesnoj
grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...[vbcol=seagreen]
> Offcourse you can also query the database to check for duplicate keys.
> Good luck!
>
> "sali" wrote:
|||You might want to check out the DBCC CHECKCONSTRAINTS command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23EyhH44wHHA.4640@.TK2MSFTNGP03.phx.gbl...
> thnx
> right now server is working, but i want to be sure there is no hidden
> problem with corrupted primary & unique keys.
> i am affraid that simple :
> select count(*), prim_key
> group by prim_key
> wouldn't show me there is a prim_keys with count()>1
> or could i may select with prime_key index switched_off [simple flat scan]?
> it all may depends of internal architecture of database engine, i've got a
> link about it and going to examine it.
>
> "Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
> napisao u poruci interesnoj
> grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...
>
quick analyze showed there is no evident data loss [last changed values are
still there], so they restarted the server and continued to work.
later, some mallfunctions [broken relations] assured us that indices [at
least some of them] are damaged.
questionn is: how are primary key [and other uniques] indices handled?
is it possible, if index not repaired, doubled [not-unique] key being
inserted?
what is the most efficient way to examine if this happened.
after rebuilding primary key index [if broken], what happens with duplicated
keys?
some comments or experience about this?
thnx.
SQL Server should in many cases handle a hard power off just fine. There are two cases where this
can go bad, though:
1. Hardware caching without battery backup. SQL Server depends on what has been written is actually
on the disk. It directs the OS to not cache write operations, but if you have HW caching, then all
bets are off.
2. Torn page. SQL Server directs the OS to do write in page sizes as smallest size. So the OS will
direct the disk subsystem to do the write, but typically smallest size for write operation at disk
level is sector (typically 512 bytes) so a page can be partially written.
The best place to start if you want to read more about this are below two articles:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
Also, I suggest you visit for lots of good stuff related to this topic.
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:eEYuqK4wHHA.4300@.TK2MSFTNGP04.phx.gbl...
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are still there], so they
> restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at least some of them] are
> damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated keys?
> some comments or experience about this?
> thnx.
>
|||I don't no if you have the resources but.
Why don't you make a online full-backup.
Restore it....do the rebuilld index or repairs and see what happens.
Offcourse you can also query the database to check for duplicate keys.
I don't think this is happening.
But don't take my word for it.
Make sure you have a recent a log back-up and get to work!
Good luck!
"sali" wrote:
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are
> still there], so they restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at
> least some of them] are damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being
> inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated
> keys?
> some comments or experience about this?
> thnx.
>
>
|||thnx
right now server is working, but i want to be sure there is no hidden
problem with corrupted primary & unique keys.
i am affraid that simple :
select count(*), prim_key
group by prim_key
wouldn't show me there is a prim_keys with count()>1
or could i may select with prime_key index switched_off [simple flat scan]?
it all may depends of internal architecture of database engine, i've got a
link about it and going to examine it.
"Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
napisao u poruci interesnoj
grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...[vbcol=seagreen]
> Offcourse you can also query the database to check for duplicate keys.
> Good luck!
>
> "sali" wrote:
|||You might want to check out the DBCC CHECKCONSTRAINTS command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23EyhH44wHHA.4640@.TK2MSFTNGP03.phx.gbl...
> thnx
> right now server is working, but i want to be sure there is no hidden
> problem with corrupted primary & unique keys.
> i am affraid that simple :
> select count(*), prim_key
> group by prim_key
> wouldn't show me there is a prim_keys with count()>1
> or could i may select with prime_key index switched_off [simple flat scan]?
> it all may depends of internal architecture of database engine, i've got a
> link about it and going to examine it.
>
> "Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
> napisao u poruci interesnoj
> grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...
>
poweroff & index
power-off happened, and server [w2k, sql2k] stopped during working day.
quick analyze showed there is no evident data loss [last changed values are
still there], so they restarted the server and continued to work.
later, some mallfunctions [broken relations] assured us that indices [at
least some of them] are damaged.
questionn is: how are primary key [and other uniques] indices handled?
is it possible, if index not repaired, doubled [not-unique] key being
inserted?
what is the most efficient way to examine if this happened.
after rebuilding primary key index [if broken], what happens with duplicated
keys?
some comments or experience about this?
thnx.SQL Server should in many cases handle a hard power off just fine. There are two cases where this
can go bad, though:
1. Hardware caching without battery backup. SQL Server depends on what has been written is actually
on the disk. It directs the OS to not cache write operations, but if you have HW caching, then all
bets are off.
2. Torn page. SQL Server directs the OS to do write in page sizes as smallest size. So the OS will
direct the disk subsystem to do the write, but typically smallest size for write operation at disk
level is sector (typically 512 bytes) so a page can be partially written.
The best place to start if you want to read more about this are below two articles:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
Also, I suggest you visit for lots of good stuff related to this topic.
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:eEYuqK4wHHA.4300@.TK2MSFTNGP04.phx.gbl...
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are still there], so they
> restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at least some of them] are
> damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated keys?
> some comments or experience about this?
> thnx.
>|||I don't no if you have the resources but.
Why don't you make a online full-backup.
Restore it....do the rebuilld index or repairs and see what happens.
Offcourse you can also query the database to check for duplicate keys.
I don't think this is happening.
But don't take my word for it.
Make sure you have a recent a log back-up and get to work!
Good luck!
"sali" wrote:
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are
> still there], so they restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at
> least some of them] are damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being
> inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated
> keys?
> some comments or experience about this?
> thnx.
>
>|||thnx
right now server is working, but i want to be sure there is no hidden
problem with corrupted primary & unique keys.
i am affraid that simple :
select count(*), prim_key
group by prim_key
wouldn't show me there is a prim_keys with count()>1
or could i may select with prime_key index switched_off [simple flat scan]?
it all may depends of internal architecture of database engine, i've got a
link about it and going to examine it.
"Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
napisao u poruci interesnoj
grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...
> Offcourse you can also query the database to check for duplicate keys.
> Good luck!
>
> "sali" wrote:
>> power-off happened, and server [w2k, sql2k] stopped during working day.
>> quick analyze showed there is no evident data loss|||You might want to check out the DBCC CHECKCONSTRAINTS command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23EyhH44wHHA.4640@.TK2MSFTNGP03.phx.gbl...
> thnx
> right now server is working, but i want to be sure there is no hidden
> problem with corrupted primary & unique keys.
> i am affraid that simple :
> select count(*), prim_key
> group by prim_key
> wouldn't show me there is a prim_keys with count()>1
> or could i may select with prime_key index switched_off [simple flat scan]?
> it all may depends of internal architecture of database engine, i've got a
> link about it and going to examine it.
>
> "Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
> napisao u poruci interesnoj
> grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...
>> Offcourse you can also query the database to check for duplicate keys.
>> Good luck!
>>
>> "sali" wrote:
>> power-off happened, and server [w2k, sql2k] stopped during working day.
>> quick analyze showed there is no evident data loss
>sql
quick analyze showed there is no evident data loss [last changed values are
still there], so they restarted the server and continued to work.
later, some mallfunctions [broken relations] assured us that indices [at
least some of them] are damaged.
questionn is: how are primary key [and other uniques] indices handled?
is it possible, if index not repaired, doubled [not-unique] key being
inserted?
what is the most efficient way to examine if this happened.
after rebuilding primary key index [if broken], what happens with duplicated
keys?
some comments or experience about this?
thnx.SQL Server should in many cases handle a hard power off just fine. There are two cases where this
can go bad, though:
1. Hardware caching without battery backup. SQL Server depends on what has been written is actually
on the disk. It directs the OS to not cache write operations, but if you have HW caching, then all
bets are off.
2. Torn page. SQL Server directs the OS to do write in page sizes as smallest size. So the OS will
direct the disk subsystem to do the write, but typically smallest size for write operation at disk
level is sector (typically 512 bytes) so a page can be partially written.
The best place to start if you want to read more about this are below two articles:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
Also, I suggest you visit for lots of good stuff related to this topic.
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:eEYuqK4wHHA.4300@.TK2MSFTNGP04.phx.gbl...
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are still there], so they
> restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at least some of them] are
> damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated keys?
> some comments or experience about this?
> thnx.
>|||I don't no if you have the resources but.
Why don't you make a online full-backup.
Restore it....do the rebuilld index or repairs and see what happens.
Offcourse you can also query the database to check for duplicate keys.
I don't think this is happening.
But don't take my word for it.
Make sure you have a recent a log back-up and get to work!
Good luck!
"sali" wrote:
> power-off happened, and server [w2k, sql2k] stopped during working day.
> quick analyze showed there is no evident data loss [last changed values are
> still there], so they restarted the server and continued to work.
> later, some mallfunctions [broken relations] assured us that indices [at
> least some of them] are damaged.
> questionn is: how are primary key [and other uniques] indices handled?
> is it possible, if index not repaired, doubled [not-unique] key being
> inserted?
> what is the most efficient way to examine if this happened.
> after rebuilding primary key index [if broken], what happens with duplicated
> keys?
> some comments or experience about this?
> thnx.
>
>|||thnx
right now server is working, but i want to be sure there is no hidden
problem with corrupted primary & unique keys.
i am affraid that simple :
select count(*), prim_key
group by prim_key
wouldn't show me there is a prim_keys with count()>1
or could i may select with prime_key index switched_off [simple flat scan]?
it all may depends of internal architecture of database engine, i've got a
link about it and going to examine it.
"Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
napisao u poruci interesnoj
grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...
> Offcourse you can also query the database to check for duplicate keys.
> Good luck!
>
> "sali" wrote:
>> power-off happened, and server [w2k, sql2k] stopped during working day.
>> quick analyze showed there is no evident data loss|||You might want to check out the DBCC CHECKCONSTRAINTS command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23EyhH44wHHA.4640@.TK2MSFTNGP03.phx.gbl...
> thnx
> right now server is working, but i want to be sure there is no hidden
> problem with corrupted primary & unique keys.
> i am affraid that simple :
> select count(*), prim_key
> group by prim_key
> wouldn't show me there is a prim_keys with count()>1
> or could i may select with prime_key index switched_off [simple flat scan]?
> it all may depends of internal architecture of database engine, i've got a
> link about it and going to examine it.
>
> "Hate_orphaned_users" <Hateorphanedusers@.discussions.microsoft.com> je
> napisao u poruci interesnoj
> grupi:A7465133-50EA-43AB-92BB-2040C3844328@.microsoft.com...
>> Offcourse you can also query the database to check for duplicate keys.
>> Good luck!
>>
>> "sali" wrote:
>> power-off happened, and server [w2k, sql2k] stopped during working day.
>> quick analyze showed there is no evident data loss
>sql
Wednesday, March 21, 2012
Posting Multi-Value Parameter
Hi,
I tried Posting the values for a multi value parameter from my application to the reporting services, But the query string is not being hidden. Why is that ? since the the data is sent through the browser address bar, I am not able to send values more than the allowed lenght. Is there any work around ?
Thanks In Advance
Regards
Raja Annamalai S
Hi Raja-
Yes, you will be limited to the URL length restriction. If possible I might suggest using the Web Service to render reports rather than crafting the URL. This will not be limited by URL length.
Otherwise you can perform a POST instead of a GET on the http call. The post will send the parameter values in the body rather than appended to the URL. However, all parameters need to be in the body if you perform a POST.
Thanks, Jon
Postal Codes 10001-10005 as a parameter
Dear all
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Tuesday, March 20, 2012
POST Method
We've run into a rather sizable problem here.
When passing querystring values using GET, to our SQL Server Reporting Services Reports, we've discovered that we've exceeded the maximum URL length,.
We are thinking that we need to change how we're passing this data to our reports.
Can we use POST and transfer the data throught hte HTTP header and bypass this limitation?
Thanks.
Doug
Yes you can use a Post method. The following link is an example:
http://blogs.msdn.com/bryanke/articles/73153.aspx
Post Deployment - Running Pkg With Set Values !!VOID
I was wondering if someone could provide me with a bit info on how to Set Values in the dtexecui?
I have a package that does a T-SQL SELECT query with a @.var, and that @.var needs to be set at runtime, in the Agent Job....
Thanks in advance.
Nevermind, having a slllllowwww moment this morning, need more coffee:)In case others would like to know:
\Package.Variables[User::VAR_NAME].Properties[Value] : VAR_VALUE|||I've submitted a DCR to get a GUI interface as part of dtexecui that allows you to generate these property pathswithout typing them in yourself. It shouldn't be hard seeing as the same thing already exists in SSIS Designer.
In the meantime, the way to generate these property paths is using the XML configuration file wizard within SSIS Designer.
I'm sure you know this Jason :)
-Jamie|||Is it Feedback. Got a reference I can vote on? Drives me round the twist too.|||
DarrenSQLIS wrote:
Is it Feedback. Got a reference I can vote on? Drives me round the twist too.
Nah. I did it thru betaplace!
Post Deployment - Running Pkg With Set Values
I was wondering if someone could provide me with a bit info on how to Set Values in the dtexecui?
I have a package that does a T-SQL SELECT query with a @.var, and that @.var needs to be set at runtime, in the Agent Job....
Thanks in advance.
Nevermind, having a slllllowwww moment this morning, need more coffee:)In case others would like to know:
\Package.Variables[User::VAR_NAME].Properties[Value] : VAR_VALUE|||I've submitted a DCR to get a GUI interface as part of dtexecui that allows you to generate these property pathswithout typing them in yourself. It shouldn't be hard seeing as the same thing already exists in SSIS Designer.
In the meantime, the way to generate these property paths is using the XML configuration file wizard within SSIS Designer.
I'm sure you know this Jason :)
-Jamie|||Is it Feedback. Got a reference I can vote on? Drives me round the twist too.|||
Is it Feedback. Got a reference I can vote on? Drives me round the twist too.
Nah. I did it thru betaplace!
Monday, March 12, 2012
Possibly incorrect query result
/* Test table */
create table test (c1 char(1), c2 varchar(1));
insert into test values ('','');
/* Query */
select
c1,
len(c1) len_c1,
c2,
len(c2) len_c2
from test
The result of the len(c1) expression is 0. I would expect the correct result to be 1, since "c1" is a fixed-length character string type and the values are right-padded with spaces to fit the defined length, in this case 1.
I'm using SQL Server 2005.
Regards,
Ole Willy Tuv
LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Change LEN to DATALENGTH and run this
select
c1,
datalength(c1) len_c1,
c2,
datalength(c2) len_c2
from test
select datalength(' '),len(' ')
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Possible values for custom property in combobox
I have a custom property and want to offer the possible values at designtime in the property sheet or editor in a combobox. I there a way I can accomplish this? Or do I have to make a custom UI for this?
Thanks,
HenkHave a look at the updated BOL topic Doug posted for your earlier (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=74057).
You probably want to use an enumeration to define the values, and set a TypeConverter as per the example in BOL.
For more advanced properties, or where you want a fancy UI, then set a UITypeEditor instead. There are a few more UITypeEditors in the box (Framework 2.0) now as well -
UITypeEditor Hierarchy
(http://msdn2.microsoft.com/library/bfc7teys(en-us,vs.80).aspx)|||Thanks Darren, I missed the post (and a valuable one!) indeed. Unfortunately I can't use an enumeration, because I need to determine the possible values dynamically from a table. Guess, that if I will persue this, I need an UITypeEditor
Regards,
Henk|||You can write a TypeConverter with dynamic values, but for more interaction
you will need a UITypeEditor. For example on tasks that consume connections, I use a custom UITypeEditor to display a drop-down of connections, and offer a <new connection> option. This needs you own code to do, as I want to do fancy stuff when selecting the new option.
It may be easier to do in a custom UI rather than through the advanced UI. You can provide a richer user experience, especially when there are interactions between several properties. Talking to other properties can be done by working through the context reference you get, but it can be a pain.
There are plenty of articles out there on Property Grids, and converters/editors, and the underlying concepts have been around for a while, totally unrelated to SSIS, so be sure to check out regular .Net resources too, e.g.
The Code Project - PropertyGrid and Drop Down properties - C# Programming
(http://www.thecodeproject.com/csharp/DropDownProperties.asp)
Friday, March 9, 2012
Possible to perform mathematical functions on two or more text boxes?
I am looking to take the sum of the values displayed in a couple text
boxes and display the result in another text box. Is it at all
possible to include the values displayed in other text boxes in
expressions in other text boxes?On Jul 11, 8:19 am, mtnbiker419 <rdrace...@.gmail.com> wrote:
> I am looking to take the sum of the values displayed in a couple text
> boxes and display the result in another text box. Is it at all
> possible to include the values displayed in other text boxes in
> expressions in other text boxes?
If I'm understanding you correctly and you want field values displayed
in textboxes (i.e., table control cells/text boxes, etc), you can
reference them within the dataset itself (if they are aggregates or
can be aggregated):
=Fields!FieldName.Value + Sum(Fields!FieldNameInOtherDataset.Value,
"DatasetNameUsedInTextboxB")
-or similarly -
=Fields!FieldName.Value + Avg(Fields!FieldNameInOtherDataset.Value,
"DatasetNameUsedInTextboxB")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
boxes and display the result in another text box. Is it at all
possible to include the values displayed in other text boxes in
expressions in other text boxes?On Jul 11, 8:19 am, mtnbiker419 <rdrace...@.gmail.com> wrote:
> I am looking to take the sum of the values displayed in a couple text
> boxes and display the result in another text box. Is it at all
> possible to include the values displayed in other text boxes in
> expressions in other text boxes?
If I'm understanding you correctly and you want field values displayed
in textboxes (i.e., table control cells/text boxes, etc), you can
reference them within the dataset itself (if they are aggregates or
can be aggregated):
=Fields!FieldName.Value + Sum(Fields!FieldNameInOtherDataset.Value,
"DatasetNameUsedInTextboxB")
-or similarly -
=Fields!FieldName.Value + Avg(Fields!FieldNameInOtherDataset.Value,
"DatasetNameUsedInTextboxB")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Wednesday, March 7, 2012
Possible to assign a value to a textbox?
Hi,
Is it possible to assign a value to a textbox when viewing reports, depending on certain values of the fields. E.g if Fields!Month.Value = Parameters!Month.Value, i want to display some message in the textbox.
Hi eeyore21
It is possible unless I'm not understanding the question correctly. Try this:
Select the textbox properties. In the 'Value: ' property select 'Expression' (button next to textbox)
type in '=IIF( Parameters!PamaterName.Value = Fields!FieldName.Value,"Print this if true","Print this if False")'
whereParameters!PamaterName.Value - this is the parameter value which you want to compare to Fields!FieldName.Value ( dataset field name)
In your case it would be :=IIF(Fields!Month.Value = Parameters!Month.Value,"IT IS TRUE DO SOMETHING","NOT TRUE DO SOMETHING ELSE")'
Is it possible to assign a value to a textbox when viewing reports, depending on certain values of the fields. E.g if Fields!Month.Value = Parameters!Month.Value, i want to display some message in the textbox.
Hi eeyore21
It is possible unless I'm not understanding the question correctly. Try this:
Select the textbox properties. In the 'Value: ' property select 'Expression' (button next to textbox)
type in '=IIF( Parameters!PamaterName.Value = Fields!FieldName.Value,"Print this if true","Print this if False")'
whereParameters!PamaterName.Value - this is the parameter value which you want to compare to Fields!FieldName.Value ( dataset field name)
In your case it would be :=IIF(Fields!Month.Value = Parameters!Month.Value,"IT IS TRUE DO SOMETHING","NOT TRUE DO SOMETHING ELSE")'
Subscribe to:
Posts (Atom)