delete BLOB objects

When I delete rows from a table that contains an NTEXT column, I get a delet
e
capacity of 100 rows per second. I think this is slow. How can I make it
delete faster?
The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246 byte
s
When I populate the same table with BULK INSERT I get performance of
2000-3000 rows per second. Shouldn’t deletion of rows reach about the same
performance as population of the same data?
I have a primary key, id, which I use to select my rows for deletion like
this:
DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
optimal with a single “clustered index delete”.
I have removed all constraints and all indexes on the table to isolate the
problem as much as possible. I'm running in simple recovery mode.
The funny thing is that if I do the following exercise the delete
performance is about 1000-2000 rows per seconds:
Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
id > 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
.. The update, however, does about 50 rows per second.
If I do this:
Step 1: UPDATE mytable SET ntextcolumn = N’-1’ WHERE id < 10000 and id
> 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
…then delete capacity is ca 15.000 rows per second. The update, however,
does about 33 rows per second.
Is the above behavior normal? Does it really take that much work for SQL
Server to remove the blob-object?When you do the bulk Insert you are most likely getting a minimally logged
load which does not log the actual data in the transaction log. It only
marks which extents have been altered in the bulk load. But when you delete
or Update the row it has to log the text data in the transaction log. That
is a lot of data to log all at once. The delete after the update is faster
for two reasons. One the data is already all in cache and you have no text
data to log. Where is your log file located? If it is not on a RAID 1 or
Raid 10 by itself you should think about moving it.
Andrew J. Kelly SQL MVP
"HenrikF" <HenrikF@.discussions.microsoft.com> wrote in message
news:B2A7FD8D-B356-4EBA-B201-B761C0673EF2@.microsoft.com...
> When I delete rows from a table that contains an NTEXT column, I get a
> delete
> capacity of 100 rows per second. I think this is slow. How can I make it
> delete faster?
> The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246
> bytes
> When I populate the same table with BULK INSERT I get performance of
> 2000-3000 rows per second. Shouldn't deletion of rows reach about the same
> performance as population of the same data?
> I have a primary key, id, which I use to select my rows for deletion like
> this:
> DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
> optimal with a single "clustered index delete".
>
> I have removed all constraints and all indexes on the table to isolate the
> problem as much as possible. I'm running in simple recovery mode.
> The funny thing is that if I do the following exercise the delete
> performance is about 1000-2000 rows per seconds:
>
> Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
> id > 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .. The update, however, does about 50 rows per second.
>
> If I do this:
> Step 1: UPDATE mytable SET ntextcolumn = N'-1' WHERE id < 10000 and id >
> 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .then delete capacity is ca 15.000 rows per second. The update, however,
> does about 33 rows per second.
>
> Is the above behavior normal? Does it really take that much work for SQL
> Server to remove the blob-object?
>sql

delete all rows from a specific partition in SQL 2005?

Hi,
does it possible to truncate a specific partition on a partitionned table?
I have a large table where each partition contains 50 million of rows, I
have to truncate 1 partition (which is 1 year) and then refill it.
its a data warehouse, so there is no concurrent usage, its during my loading
process.
does the delete statement is good enough?
thanks.
Jerome."Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
>
ALTER TABLE ... SWITCH can be used to switch the partition with an empty
table, which can then be truncated.
David|||interesting...
for the moment this command works fine:
alter table ProdTable switch partition 6 to DummyTable
this "truncate" my partition 6 of my prodtable, which is exactly what I
want.
This is my scenario...
1 I load new content to an empty partitionned table. when the loading is
completed; I move the current production data of the targeted partition to
my dummy table; Now the prodtable doesn't contains the year I want to load;
I move the data from the temporary partitionned table to the prodtable;
now my prodtable contain the new content.
This reduce the downtime for my users and, in case of failure, preserve the
current content because I move the data only at the end.
does it a good scenario?
but this cause to fill a partitionned table because I can switch only data
from 2 partitionned tables, I can't switch from non-partitionned to a
partitionned one.
now a question... does filling a partitionned table using SSIS cause an
overhead in the insert statement?
thanks.
Jerome.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O99MI3OiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> ALTER TABLE ... SWITCH can be used to switch the partition with an empty
> table, which can then be truncated.
> David
>|||Use the SWITCH operator to move the partition to a staging table and then
truncate the staging table.
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
> thanks.
> Jerome.
>|||"Jeje" <willgart@.hotmail.com> wrote in message
news:eijqvmQiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> interesting...
> for the moment this command works fine:
> alter table ProdTable switch partition 6 to DummyTable
> this "truncate" my partition 6 of my prodtable, which is exactly what I
> want.
> This is my scenario...
> 1 I load new content to an empty partitionned table. when the loading is
> completed; I move the current production data of the targeted partition to
> my dummy table; Now the prodtable doesn't contains the year I want to
> load; I move the data from the temporary partitionned table to the
> prodtable;
> now my prodtable contain the new content.
> This reduce the downtime for my users and, in case of failure, preserve
> the current content because I move the data only at the end.
> does it a good scenario?
Yes.

> but this cause to fill a partitionned table because I can switch only data
> from 2 partitionned tables, I can't switch from non-partitionned to a
> partitionned one.
Sure you can. The non-partitioned table must be on the same file group as
the target partition, and it must have a check constraint on the
partitioning columns that guarantees that all the rows in the table are
belong in that partition. But it is often easier to use a partitioned
staging table for loading, switching and truncating.

> now a question... does filling a partitionned table using SSIS cause an
> overhead in the insert statement?
>
Some, perhaps. It depends on the distributation of the data across
partitions, I suppose.
David

delete all rows from a specific partition in SQL 2005?

Hi,
does it possible to truncate a specific partition on a partitionned table?
I have a large table where each partition contains 50 million of rows, I
have to truncate 1 partition (which is 1 year) and then refill it.
its a data warehouse, so there is no concurrent usage, its during my loading
process.
does the delete statement is good enough?
thanks.
Jerome."Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
>
ALTER TABLE ... SWITCH can be used to switch the partition with an empty
table, which can then be truncated.
David|||interesting...
for the moment this command works fine:
alter table ProdTable switch partition 6 to DummyTable
this "truncate" my partition 6 of my prodtable, which is exactly what I
want.
This is my scenario...
1 I load new content to an empty partitionned table. when the loading is
completed; I move the current production data of the targeted partition to
my dummy table; Now the prodtable doesn't contains the year I want to load;
I move the data from the temporary partitionned table to the prodtable;
now my prodtable contain the new content.
This reduce the downtime for my users and, in case of failure, preserve the
current content because I move the data only at the end.
does it a good scenario?
but this cause to fill a partitionned table because I can switch only data
from 2 partitionned tables, I can't switch from non-partitionned to a
partitionned one.
now a question... does filling a partitionned table using SSIS cause an
overhead in the insert statement?
thanks.
Jerome.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O99MI3OiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> does it possible to truncate a specific partition on a partitionned
>> table?
>> I have a large table where each partition contains 50 million of rows, I
>> have to truncate 1 partition (which is 1 year) and then refill it.
>> its a data warehouse, so there is no concurrent usage, its during my
>> loading process.
>> does the delete statement is good enough?
> ALTER TABLE ... SWITCH can be used to switch the partition with an empty
> table, which can then be truncated.
> David
>|||Use the SWITCH operator to move the partition to a staging table and then
truncate the staging table.
--
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
> thanks.
> Jerome.
>|||"Jeje" <willgart@.hotmail.com> wrote in message
news:eijqvmQiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> interesting...
> for the moment this command works fine:
> alter table ProdTable switch partition 6 to DummyTable
> this "truncate" my partition 6 of my prodtable, which is exactly what I
> want.
> This is my scenario...
> 1 I load new content to an empty partitionned table. when the loading is
> completed; I move the current production data of the targeted partition to
> my dummy table; Now the prodtable doesn't contains the year I want to
> load; I move the data from the temporary partitionned table to the
> prodtable;
> now my prodtable contain the new content.
> This reduce the downtime for my users and, in case of failure, preserve
> the current content because I move the data only at the end.
> does it a good scenario?
Yes.
> but this cause to fill a partitionned table because I can switch only data
> from 2 partitionned tables, I can't switch from non-partitionned to a
> partitionned one.
Sure you can. The non-partitioned table must be on the same file group as
the target partition, and it must have a check constraint on the
partitioning columns that guarantees that all the rows in the table are
belong in that partition. But it is often easier to use a partitioned
staging table for loading, switching and truncating.
> now a question... does filling a partitionned table using SSIS cause an
> overhead in the insert statement?
>
Some, perhaps. It depends on the distributation of the data across
partitions, I suppose.
Davidsql

degrading performance on one table

Hello, we use one table in our SQL2k SP3a server for storing all sorts of
parameters. See the description below. It contains about 150.000 records.
What we see happening over the day is that the performance on accessing this
table deteriorates. When joins are made with other tables that use the
parameter table, they get slow too. It is very fast when SQL is freshly
started, but after a day or two spurious locks show up (I assume because of
the lack of response), and at somepoint I can't even do a select count (*)
anymore. Takes forever. There are no locks when I do this, I just wait
forever. Restarting SQL solved the problem, after that it is as fast as
ever!
This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
particular problem. Index has been defragged, to no avail.
Are we missing something obvious? Pointers as to where to look?
René
CREATE TABLE [dbo].[parameter] (
[entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[entity_id] [int] NOT NULL ,
[name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
(
[entity_name],
[entity_id],
[name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOYou mentioned that you did reindex.
Do you do any deletes and updates on the table?
You mentioned no locks on the table when you run count(*). Is the server
performing bad for other tables at that time? Are there any open
transactions (run dbcc opentran). how about DBCC SHOWCONTIG (tablename)
"René" <rene.de.vries/atsign/kexdotnl> wrote in message
news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hello, we use one table in our SQL2k SP3a server for storing all sorts of
> parameters. See the description below. It contains about 150.000 records.
> What we see happening over the day is that the performance on accessing
this
> table deteriorates. When joins are made with other tables that use the
> parameter table, they get slow too. It is very fast when SQL is freshly
> started, but after a day or two spurious locks show up (I assume because
of
> the lack of response), and at somepoint I can't even do a select count (*)
> anymore. Takes forever. There are no locks when I do this, I just wait
> forever. Restarting SQL solved the problem, after that it is as fast as
> ever!
> This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
> particular problem. Index has been defragged, to no avail.
> Are we missing something obvious? Pointers as to where to look?
> René
> CREATE TABLE [dbo].[parameter] (
> [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [entity_id] [int] NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> (
> [entity_name],
> [entity_id],
> [name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>|||Did you look at the query execution plan using one or more typical
"slowed-down" queries? Are the correct indexes being used? How about update
statistics? Is there tempdb issue 'cause it seems OK after service restart?
Any Perfmon findings on memory, processor, disk I/O counters?
Richard
"René" <rene.de.vries/atsign/kexdotnl> wrote in message
news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hello, we use one table in our SQL2k SP3a server for storing all sorts of
> parameters. See the description below. It contains about 150.000 records.
> What we see happening over the day is that the performance on accessing
this
> table deteriorates. When joins are made with other tables that use the
> parameter table, they get slow too. It is very fast when SQL is freshly
> started, but after a day or two spurious locks show up (I assume because
of
> the lack of response), and at somepoint I can't even do a select count (*)
> anymore. Takes forever. There are no locks when I do this, I just wait
> forever. Restarting SQL solved the problem, after that it is as fast as
> ever!
> This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
> particular problem. Index has been defragged, to no avail.
> Are we missing something obvious? Pointers as to where to look?
> René
> CREATE TABLE [dbo].[parameter] (
> [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [entity_id] [int] NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> (
> [entity_name],
> [entity_id],
> [name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>|||I did have a look at temdb, and there was 98% unused space.. But in total it
is currently only
The query plans for a typical query looks ok, In fact, right after a
restart, that query is really fast - 0 second responses. The indexes look
okay, we experimented earlier with different indexes.
There is no change in load on CPU, memory of disk I/Owhen the performance
goed bad... CPU (2) are at maxed out at 50, sometimes peeking when a
full-text query is requested.
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uIE7$0sUDHA.360@.TK2MSFTNGP11.phx.gbl...
> Did you look at the query execution plan using one or more typical
> "slowed-down" queries? Are the correct indexes being used? How about
update
> statistics? Is there tempdb issue 'cause it seems OK after service
restart?
> Any Perfmon findings on memory, processor, disk I/O counters?
>
> Richard
> "René" <rene.de.vries/atsign/kexdotnl> wrote in message
> news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> > Hello, we use one table in our SQL2k SP3a server for storing all sorts
of
> > parameters. See the description below. It contains about 150.000
records.
> >
> > What we see happening over the day is that the performance on accessing
> this
> > table deteriorates. When joins are made with other tables that use the
> > parameter table, they get slow too. It is very fast when SQL is freshly
> > started, but after a day or two spurious locks show up (I assume because
> of
> > the lack of response), and at somepoint I can't even do a select count
(*)
> > anymore. Takes forever. There are no locks when I do this, I just wait
> > forever. Restarting SQL solved the problem, after that it is as fast as
> > ever!
> >
> > This is REALLY puzzling us. We've run traces, etc- nothing to indicate
any
> > particular problem. Index has been defragged, to no avail.
> >
> > Are we missing something obvious? Pointers as to where to look?
> >
> > René
> >
> > CREATE TABLE [dbo].[parameter] (
> > [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> > ,
> > [entity_id] [int] NOT NULL ,
> > [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> > CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> > (
> > [entity_name],
> > [entity_id],
> > [name]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> >
>

Degenerate dimension with non-numeric data in SSAS

Dear,

I have the following problem/question:

I have a fact table that contains for example all orderline data, with next to that all skey's to my dimension tables (product, customer, date, ...). Next to the measures that are included in my fact table, I was planning to also include a degenerate dimension that contains my ordernumber. So far no problem creating the star schema in a relational database. But trying to set this up in SSAS, the problem comes up that my order number is not a real number but a string (eg 'ABC12345') and as far as I know, the fact table in SSAS cannot contain any string data (or am I wrong?).

Any suggestions?

Thank you in advance for your feedback!

Kind regards,

Jürgen

You are wrong :)

|||

OK, great! Can you also let me know how to add data formatted as string in my relational fact table to my SSAS cube (without creating an additional dimension)? I already tried a lot of things, but without any positive result...

Thx in advance,

Jürgen

|||I am not sure what you mean by "add data formatted as string". Are you talking about adding a varchar measure? I can't see any scenario where you would want to do that.|||

The notion of degenerate dimension in Analysis Services is modeled by using Fact dimension. Not sure why but legal guys didn't let us use "Degenerate" in the product . Must be the case of political correctness:)

You create a new dimension and you include all additional information you would like to see. You define a relationship between your dimension and the measure group as Fact. Then using Drillthrough functionatlity you should be able to provide your users access to this additional information.

Books online should have more information for you on the Fact dimensions and on the Drillthough.

Hope that helps.

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

|||Thanks for the useful information, Edward!

Defraging a drive with a database on it

Hi all,
I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
contains the database has become massively fragmented in the last two months
since the system was put in. I'd like to run defrag on the drive and then
set it up as a scheduled task to run weekly to keep the fragmentation down.
I'm new to the SQL world and don't think that running defrag will mess
anything up but am worried about the "what if" factor. I'm learning SQL out
of a couple of books (no training budget). None of my books say anything
about defrag itself.
Can anyone reassure me that running defrag on a drive with a database on it
is safe? Is there anything I should look out for?
Thanks.
DanIt is safe, as the database is stored on "regular" files (seen from the operating system's
perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
just in case. Also, consider why the files are so fragmented. For instance, read this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> Hi all,
> I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
> contains the database has become massively fragmented in the last two months
> since the system was put in. I'd like to run defrag on the drive and then
> set it up as a scheduled task to run weekly to keep the fragmentation down.
> I'm new to the SQL world and don't think that running defrag will mess
> anything up but am worried about the "what if" factor. I'm learning SQL out
> of a couple of books (no training budget). None of my books say anything
> about defrag itself.
> Can anyone reassure me that running defrag on a drive with a database on it
> is safe? Is there anything I should look out for?
> Thanks.
>
> Dan
>|||Tibor,
Thanks for the quick reply. I'm feeling more comfortable about it now.
Dan
"Tibor Karaszi" wrote:
> It is safe, as the database is stored on "regular" files (seen from the operating system's
> perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
> just in case. Also, consider why the files are so fragmented. For instance, read this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
> news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> > Hi all,
> >
> > I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
> > contains the database has become massively fragmented in the last two months
> > since the system was put in. I'd like to run defrag on the drive and then
> > set it up as a scheduled task to run weekly to keep the fragmentation down.
> >
> > I'm new to the SQL world and don't think that running defrag will mess
> > anything up but am worried about the "what if" factor. I'm learning SQL out
> > of a couple of books (no training budget). None of my books say anything
> > about defrag itself.
> >
> > Can anyone reassure me that running defrag on a drive with a database on it
> > is safe? Is there anything I should look out for?
> >
> > Thanks.
> >
> >
> > Dan
> >
>
>

Default Value for Parameter with Query

I am trying to use the User!UserID field as a default value for a parameter. The list for the parameter is populated using a dataset that contains a list of names and User IDs. The value field for the query is User_ID. This field matches exactly with the User!UserID field, but it won't seem to default to that value. I'm sure I'm missing something obvious here. How can I get that list to default to my default value.If you keep User_ID in a field of fixed length (like char[X], not
varchar[X]), you may have spaces appended at the end of values which are
hard to notice. If you have trailing spaces in valid values and no trailing
spaces in the default value, default value won't be selected. See if this is
the case.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"leehaak" <leehaak@.discussions.microsoft.com> wrote in message
news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> I am trying to use the User!UserID field as a default value for a
parameter. The list for the parameter is populated using a dataset that
contains a list of names and User IDs. The value field for the query is
User_ID. This field matches exactly with the User!UserID field, but it
won't seem to default to that value. I'm sure I'm missing something obvious
here. How can I get that list to default to my default value.|||Thanks, unfortunately, that doesn't appear to be the problem, my User_ID field is a varchar field. I know that I can use User!UserID as a parameter in my query, and it works exactly as intended and has no trailing spaces. The value I'm setting as the default value should match my value field, correct? Are there any other requirements I'm missing to make those synch up?
"Dmitry Vasilevsky [MSFT]" wrote:
> If you keep User_ID in a field of fixed length (like char[X], not
> varchar[X]), you may have spaces appended at the end of values which are
> hard to notice. If you have trailing spaces in valid values and no trailing
> spaces in the default value, default value won't be selected. See if this is
> the case.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> ---
> "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > I am trying to use the User!UserID field as a default value for a
> parameter. The list for the parameter is populated using a dataset that
> contains a list of names and User IDs. The value field for the query is
> User_ID. This field matches exactly with the User!UserID field, but it
> won't seem to default to that value. I'm sure I'm missing something obvious
> here. How can I get that list to default to my default value.
>
>|||Here are a few things you can try.
1. Create a report with three text boxes. First text box should show value
of parameter. Second textbox should show value of Users!UserID. Last textbox
should show expression like (Parameters!Param.Value == Users!UserID). Run
this report and select the user you think is current, see if expression
returns true.
2. Make default value a query that would return first value from your table.
See if it is selected in this case.
Please, tell me you findings.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"leehaak" <leehaak@.discussions.microsoft.com> wrote in message
news:69A7ECF1-3BC4-4B5D-B411-81DC6347C44A@.microsoft.com...
> Thanks, unfortunately, that doesn't appear to be the problem, my User_ID
field is a varchar field. I know that I can use User!UserID as a parameter
in my query, and it works exactly as intended and has no trailing spaces.
The value I'm setting as the default value should match my value field,
correct? Are there any other requirements I'm missing to make those synch
up?
> "Dmitry Vasilevsky [MSFT]" wrote:
> > If you keep User_ID in a field of fixed length (like char[X], not
> > varchar[X]), you may have spaces appended at the end of values which are
> > hard to notice. If you have trailing spaces in valid values and no
trailing
> > spaces in the default value, default value won't be selected. See if
this is
> > the case.
> >
> > --
> > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> >
> > ---
> > "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> > news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > > I am trying to use the User!UserID field as a default value for a
> > parameter. The list for the parameter is populated using a dataset that
> > contains a list of names and User IDs. The value field for the query is
> > User_ID. This field matches exactly with the User!UserID field, but it
> > won't seem to default to that value. I'm sure I'm missing something
obvious
> > here. How can I get that list to default to my default value.
> >
> >
> >|||Thanks, I did this as a diagnostic, and discovered that the problem was case.
Apparently, the when determining whether the value matches a value in the
list, case is evaluated. I changed the case of the default value, and it
worked exactly as expected.
"Dmitry Vasilevsky [MSFT]" wrote:
> Here are a few things you can try.
> 1. Create a report with three text boxes. First text box should show value
> of parameter. Second textbox should show value of Users!UserID. Last textbox
> should show expression like (Parameters!Param.Value == Users!UserID). Run
> this report and select the user you think is current, see if expression
> returns true.
> 2. Make default value a query that would return first value from your table.
> See if it is selected in this case.
> Please, tell me you findings.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> ---
> "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> news:69A7ECF1-3BC4-4B5D-B411-81DC6347C44A@.microsoft.com...
> > Thanks, unfortunately, that doesn't appear to be the problem, my User_ID
> field is a varchar field. I know that I can use User!UserID as a parameter
> in my query, and it works exactly as intended and has no trailing spaces.
> The value I'm setting as the default value should match my value field,
> correct? Are there any other requirements I'm missing to make those synch
> up?
> >
> > "Dmitry Vasilevsky [MSFT]" wrote:
> >
> > > If you keep User_ID in a field of fixed length (like char[X], not
> > > varchar[X]), you may have spaces appended at the end of values which are
> > > hard to notice. If you have trailing spaces in valid values and no
> trailing
> > > spaces in the default value, default value won't be selected. See if
> this is
> > > the case.
> > >
> > > --
> > > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > --
> > >
> > > ---
> > > "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> > > news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > > > I am trying to use the User!UserID field as a default value for a
> > > parameter. The list for the parameter is populated using a dataset that
> > > contains a list of names and User IDs. The value field for the query is
> > > User_ID. This field matches exactly with the User!UserID field, but it
> > > won't seem to default to that value. I'm sure I'm missing something
> obvious
> > > here. How can I get that list to default to my default value.
> > >
> > >
> > >
>
>