Hello,
I have a Sql Server 2005 database with many tables, each with millions of records within them.
They all have a Receive Date field, with records going back 10 years or so.
What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?
~Lewhy do you want to partition them in the first place? Just because they have millions of rows?
Are these tables transactional, or do you only read from them? If you are only reading, then you can add appropriate indexes without worrying about hurting insert/update/delete perf (because you don't do any inserts/updates/deletes) and don't partition them at all.
In my job I work with tables in sql server that have 100's of millions of rows without problems (not transactional though) - the important thing is to make sure the indexes are correct. also you need decent disks of course. :)|||Hello,
I have a Sql Server 2005 database with many tables, each with millions of records within them.
They all have a Receive Date field, with records going back 10 years or so.
What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?
~Le
I have only tested functions/ schemes in 2005, but from my testing and reading I havent seen anything that would indicate 10 would be too many. Test it out. You need to realize that this whole concept/ feature is new to 2005, so you are blazing the path for the rest of us to follow. ;)
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Tuesday, March 20, 2012
Sunday, March 11, 2012
[SELECT] question
Hi all,
I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [01], the third field the values of parameter code [02] and so forth??
Table structure
CREATE TABLE `test` (
`date` datetime NOT NULL ,
`code` varchar(3) NOT NULL ,
`value` float NOT NULL
) TYPE=MyISAM;
Sample data
+-------+--+---+
| date | code | value |
+-------+--+---+
| 2004-06-01 00:00:00 | 001 | 89 |
| 2004-06-01 00:00:00 | 002 | 1 |
| 2004-06-01 01:00:00 | 001 | 76 |
| 2004-06-01 01:00:00 | 002 | 5 |
| 2004-06-01 02:00:00 | 001 | 67 |
| 2004-06-01 02:00:00 | 002 | 7 |
| 2004-06-01 03:00:00 | 001 | 46 |
| 2004-06-01 03:00:00 | 002 | 4 |
| 2004-06-01 04:00:00 | 001 | 43 |
| 2004-06-01 04:00:00 | 002 | 3 |
+-------+--+---+
Recordset
+-------+----+----+
| date | value_cod1 | value_cod2 |
+-------+----+----+
| 2004-06-01 00:00:00 | 89 | 1 |
| 2004-06-01 01:00:00 | 76 | 5 |
| 2004-06-01 02:00:00 | 67 | 7 |
|..................................... ..............|
+-------+----+----+
Thank you very much!
Paolo SaudinTo select all of the columns in an arbitrary order, you can use:SELECT *
FROM testTo select the columns in a specific order, you can use:SELECT date, code, value
FROM test-PatP|||ah, the good old denormalization question
mysql doesn't support full outer join, but this is an equivalent --
select t1.date
, t1.value as value_cod1
, t2.value as value_cod2
from `test` as t1
left outer
join `test` as t2
on t1.date
= t2.date
and t2.code = '002'
where t1.code = '001'
union all
select t2.date
, null
, t2.value
from `test` as t1
right outer
join `test` as t2
on t1.date
= t2.date
and t1.code = '001'
where t2.code = '002'
and t1.date is null
you'll need mysql 4.0 to support union, and if you don't have any code 002 rows without a code 001 row, then you can get away with just the first of the two queries|||ah, the good old denormalization questionI wondered what on earth you were babbling about as I read your response, but when I went back and re-read the question carefully I discovered that you were correct! I've always preferred the structure:SELECT date
, Sum(CASE WHEN '001' = code THEN value END) AS '001'
, Sum(CASE WHEN '002' = code THEN value END) AS '002'
, Sum(CASE WHEN '003' = code THEN value END) AS '003'
FROM test
GROUP BY dateThis only has to make one pass through the table, so it can be more efficeient if the optimiser does its job.
-PatP|||i babble often
i'm wrong occasionally
:cool:|||Thank you all very much !! :)
Paolo Saudin
I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [01], the third field the values of parameter code [02] and so forth??
Table structure
CREATE TABLE `test` (
`date` datetime NOT NULL ,
`code` varchar(3) NOT NULL ,
`value` float NOT NULL
) TYPE=MyISAM;
Sample data
+-------+--+---+
| date | code | value |
+-------+--+---+
| 2004-06-01 00:00:00 | 001 | 89 |
| 2004-06-01 00:00:00 | 002 | 1 |
| 2004-06-01 01:00:00 | 001 | 76 |
| 2004-06-01 01:00:00 | 002 | 5 |
| 2004-06-01 02:00:00 | 001 | 67 |
| 2004-06-01 02:00:00 | 002 | 7 |
| 2004-06-01 03:00:00 | 001 | 46 |
| 2004-06-01 03:00:00 | 002 | 4 |
| 2004-06-01 04:00:00 | 001 | 43 |
| 2004-06-01 04:00:00 | 002 | 3 |
+-------+--+---+
Recordset
+-------+----+----+
| date | value_cod1 | value_cod2 |
+-------+----+----+
| 2004-06-01 00:00:00 | 89 | 1 |
| 2004-06-01 01:00:00 | 76 | 5 |
| 2004-06-01 02:00:00 | 67 | 7 |
|..................................... ..............|
+-------+----+----+
Thank you very much!
Paolo SaudinTo select all of the columns in an arbitrary order, you can use:SELECT *
FROM testTo select the columns in a specific order, you can use:SELECT date, code, value
FROM test-PatP|||ah, the good old denormalization question
mysql doesn't support full outer join, but this is an equivalent --
select t1.date
, t1.value as value_cod1
, t2.value as value_cod2
from `test` as t1
left outer
join `test` as t2
on t1.date
= t2.date
and t2.code = '002'
where t1.code = '001'
union all
select t2.date
, null
, t2.value
from `test` as t1
right outer
join `test` as t2
on t1.date
= t2.date
and t1.code = '001'
where t2.code = '002'
and t1.date is null
you'll need mysql 4.0 to support union, and if you don't have any code 002 rows without a code 001 row, then you can get away with just the first of the two queries|||ah, the good old denormalization questionI wondered what on earth you were babbling about as I read your response, but when I went back and re-read the question carefully I discovered that you were correct! I've always preferred the structure:SELECT date
, Sum(CASE WHEN '001' = code THEN value END) AS '001'
, Sum(CASE WHEN '002' = code THEN value END) AS '002'
, Sum(CASE WHEN '003' = code THEN value END) AS '003'
FROM test
GROUP BY dateThis only has to make one pass through the table, so it can be more efficeient if the optimiser does its job.
-PatP|||i babble often
i'm wrong occasionally
:cool:|||Thank you all very much !! :)
Paolo Saudin
Tuesday, March 6, 2012
[MS Sql] Data type nvarchar
Hi everybody,
I'm using SQL Server Management Studio Express.
I'm trying to create a field which contains a text entered by the user. So, it should be able to contain at least 500 characters.
I used the type "nvarchar(MAX)". The problem is that the type contains about 50 characters max!!
I couldn't find out where and how to fix that.
If you have any idea :)
Thanks a lotType over the the MAX with the desired length of 500.|||Hi tmorton
Of course I tried nvarchar(500) (forgot to tell that). But it's not working either.
It sounds like nvarchar(MAX) is the max length type, and limited to about 50...
Thanks for your reply. And if someone has any idea :)|||That's all it takes from the SQL Server end. If you are only managing to store 50 characters in that column, then I would venture a guess that your data is being truncated before it hits the database. What does your code look like that is adding the data to your table?|||I believe that SQL Management Studio truncates text that it displays. Use something else to view/edit the column.|||Hi
It's weird, it's working now. And yet I didn't change anything particular. Maybe a "bug" on SQL Server... weird though. Thanks a lot to you guys :)
Thursday, February 16, 2012
[DB2] Update Field in Table A to a Field in Table B
I am trying to change the values in Field2 of table A to the values in Field2 of table B wherever the tables have the same value for Field1.
This works in Microsoft Access, but I can't figure out how to make it work in DB2.
UPDATE A INNER JOIN B ON A.Field1 = B.Field1 SET A.Field2 = [B].[Field2];
Any ideas?I know this works in Oracle, but not tested in DB2...
UPDATE a
SET a.field2 = NVL( ( SELECT b.field2
FROM b
WHERE b.field1 = a.field1), a.field2);|||try this (untested; i don't have DB2, but i know it allows scalar subqueries in the UPDATE statement) --update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )|||Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.|||Rudy,
Just so you know... I tried your variation in an attempt to find a solution for d_lynch... problem I found was that:
update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )
...works for the fields that have a match, however, if there is no match, whatever was in A.FIELD2 is now replaced with a NULL.|||thanks, joe, i understand that
i wouldn't update A.Field2 with itself, though -- could be lotsa useless log activity
i'd use a WHERE clause to ensure that only those rows which had a match are actually updated|||Cool... not really into correcting other people's code, but had tried it so I thought I'd mention the results. BTW, always appreciate your answers to questions... very well thought out.
This works in Microsoft Access, but I can't figure out how to make it work in DB2.
UPDATE A INNER JOIN B ON A.Field1 = B.Field1 SET A.Field2 = [B].[Field2];
Any ideas?I know this works in Oracle, but not tested in DB2...
UPDATE a
SET a.field2 = NVL( ( SELECT b.field2
FROM b
WHERE b.field1 = a.field1), a.field2);|||try this (untested; i don't have DB2, but i know it allows scalar subqueries in the UPDATE statement) --update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )|||Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.|||Rudy,
Just so you know... I tried your variation in an attempt to find a solution for d_lynch... problem I found was that:
update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )
...works for the fields that have a match, however, if there is no match, whatever was in A.FIELD2 is now replaced with a NULL.|||thanks, joe, i understand that
i wouldn't update A.Field2 with itself, though -- could be lotsa useless log activity
i'd use a WHERE clause to ensure that only those rows which had a match are actually updated|||Cool... not really into correcting other people's code, but had tried it so I thought I'd mention the results. BTW, always appreciate your answers to questions... very well thought out.
[CR6] Can I use "SELECT FROM WHERE"?
Hi, sorry for my english :-\
I'm working on Crystal Report 6.0
I have 2 tables that are not in join, but I need a field with a "where" condition.
Example:
TableA:
fldDescription
TableB:
fldCode
fldDesc
I have a formula field which should show TableB.fldDesc, using a code in TableA.fldDescription
I extract 2 char in TableA.fldDescription:
strMyField = Trim (Mid ({TableA.fldDescription},2 , 2));
Now I must show TableB.fldDesc where TableB.fldCode = strMyField.
How can I do this? I have no join between this tables, I tried to put in the formula field "Select TableB.fldDesc where TableB.fldCode = strMyField" but it doesn't work.
Could you help me, please?How are TableA and TableB related? Do you even need any data from TableA?
"Select TableB.fldDesc where TableB.fldCode = strMyField"
If this were VB, you would have to do it like this:
"Select TableB.fldDesc where TableB.fldCode = '" & strMyField & "'"
In other words, you would have to concatenate the variable into the string you're using for your select statement. Also, you would need to put the string in single quotes (as shown in Red) to tell your database that it's a string. (These examples are derived from VB 6 and SQL Server 7, so you may need to adjust them accordingly).
I don't use Crystal Reports for direct database access. I let VB do all the data gathering and I just pass the data itself to Crystal for displaying. I'm not sure what the correct syntax is for Database SQL Statement within Crystal Reports, but I hope that my examples might give you a starting point to figure out your problem.
Good Luck!
I'm working on Crystal Report 6.0
I have 2 tables that are not in join, but I need a field with a "where" condition.
Example:
TableA:
fldDescription
TableB:
fldCode
fldDesc
I have a formula field which should show TableB.fldDesc, using a code in TableA.fldDescription
I extract 2 char in TableA.fldDescription:
strMyField = Trim (Mid ({TableA.fldDescription},2 , 2));
Now I must show TableB.fldDesc where TableB.fldCode = strMyField.
How can I do this? I have no join between this tables, I tried to put in the formula field "Select TableB.fldDesc where TableB.fldCode = strMyField" but it doesn't work.
Could you help me, please?How are TableA and TableB related? Do you even need any data from TableA?
"Select TableB.fldDesc where TableB.fldCode = strMyField"
If this were VB, you would have to do it like this:
"Select TableB.fldDesc where TableB.fldCode = '" & strMyField & "'"
In other words, you would have to concatenate the variable into the string you're using for your select statement. Also, you would need to put the string in single quotes (as shown in Red) to tell your database that it's a string. (These examples are derived from VB 6 and SQL Server 7, so you may need to adjust them accordingly).
I don't use Crystal Reports for direct database access. I let VB do all the data gathering and I just pass the data itself to Crystal for displaying. I'm not sure what the correct syntax is for Database SQL Statement within Crystal Reports, but I hope that my examples might give you a starting point to figure out your problem.
Good Luck!
[bug?] Hidden parameters
Hello,
I have troubles with hidden parameters. What am I doing:
1) define a report parameter based on a dataset's field (a dataset is
"Stored proc." type, the procedure returns the only string value -- if it is
important)
2) Build & deploy a solution, run the report from Report manager -- it's ok
3) run the report from Report manager, click on "Properties" tab, choose
"parameters":
Has Default - leave checked on
Default Value - (Query Based)
Null - (none)
Prompt User - leave checked on
Prompt String - I cleared this textbox
4) run the report again:
Reporting Services Error
Default value or value provided for the report parameter 'MyParam' is not a
valid value. (rsInvalidReportParameter) Get Online Help
Refresh button on RM or the IE's same button don't change this error message
5) Properties -> Parameters -> Prompt String: type a former prompt string
6) run the report: it works ok, however the aforementioned parameter acts
like it doesn't have Default Value anymore.
What have I do to hide param properly?
(MS RS sp1)
Microsoft SQL Server Reporting Services Version 8.00.878.00
Thanks,
Lucy.Does it have list of available values?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have troubles with hidden parameters. What am I doing:
> 1) define a report parameter based on a dataset's field (a dataset is
> "Stored proc." type, the procedure returns the only string value -- if it
> is
> important)
> 2) Build & deploy a solution, run the report from Report manager -- it's
> ok
> 3) run the report from Report manager, click on "Properties" tab, choose
> "parameters":
> Has Default - leave checked on
> Default Value - (Query Based)
> Null - (none)
> Prompt User - leave checked on
> Prompt String - I cleared this textbox
> 4) run the report again:
> Reporting Services Error
> Default value or value provided for the report parameter 'MyParam' is not
> a
> valid value. (rsInvalidReportParameter) Get Online Help
> Refresh button on RM or the IE's same button don't change this error
> message
> 5) Properties -> Parameters -> Prompt String: type a former prompt string
> 6) run the report: it works ok, however the aforementioned parameter acts
> like it doesn't have Default Value anymore.
> What have I do to hide param properly?
> (MS RS sp1)
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> Thanks,
> Lucy.
>|||Yes. From query. Same to "default".
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Does it have list of available values?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I have troubles with hidden parameters. What am I doing:
> >
> > 1) define a report parameter based on a dataset's field (a dataset is
> > "Stored proc." type, the procedure returns the only string value -- if
it
> > is
> > important)
> > 2) Build & deploy a solution, run the report from Report manager -- it's
> > ok
> > 3) run the report from Report manager, click on "Properties" tab, choose
> > "parameters":
> > Has Default - leave checked on
> > Default Value - (Query Based)
> > Null - (none)
> > Prompt User - leave checked on
> > Prompt String - I cleared this textbox
> > 4) run the report again:
> >
> > Reporting Services Error
> > Default value or value provided for the report parameter 'MyParam' is
not
> > a
> > valid value. (rsInvalidReportParameter) Get Online Help
> >
> > Refresh button on RM or the IE's same button don't change this error
> > message
> >
> > 5) Properties -> Parameters -> Prompt String: type a former prompt
string
> > 6) run the report: it works ok, however the aforementioned parameter
acts
> > like it doesn't have Default Value anymore.
> >
> > What have I do to hide param properly?
> >
> > (MS RS sp1)
> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >
> > Thanks,
> > Lucy.
> >
> >
>|||To summarize, error rsInvalidReportParameter happened because same stored
procedure was used for available values and default value, and this stored
procedure returned unique result for every call.
Report Server was executing this stored procedure twice (1st time for
available values and 2nd time for default value) and was unable to match
default value with available value.
Solution is not to set available values.
Also because you just wanted to have read-only parameter, it is enough to
leave prompt blank, and Report Desinger would set parameter properties
during Deploy procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Yes. From query. Same to "default".
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
>> Does it have list of available values?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lusy Crown" <evesq@.uk2.net> wrote in message
>> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
>> > Hello,
>> >
>> > I have troubles with hidden parameters. What am I doing:
>> >
>> > 1) define a report parameter based on a dataset's field (a dataset is
>> > "Stored proc." type, the procedure returns the only string value -- if
> it
>> > is
>> > important)
>> > 2) Build & deploy a solution, run the report from Report manager --
>> > it's
>> > ok
>> > 3) run the report from Report manager, click on "Properties" tab,
>> > choose
>> > "parameters":
>> > Has Default - leave checked on
>> > Default Value - (Query Based)
>> > Null - (none)
>> > Prompt User - leave checked on
>> > Prompt String - I cleared this textbox
>> > 4) run the report again:
>> >
>> > Reporting Services Error
>> > Default value or value provided for the report parameter 'MyParam' is
> not
>> > a
>> > valid value. (rsInvalidReportParameter) Get Online Help
>> >
>> > Refresh button on RM or the IE's same button don't change this error
>> > message
>> >
>> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> string
>> > 6) run the report: it works ok, however the aforementioned parameter
> acts
>> > like it doesn't have Default Value anymore.
>> >
>> > What have I do to hide param properly?
>> >
>> > (MS RS sp1)
>> > Microsoft SQL Server Reporting Services Version 8.00.878.00
>> >
>> > Thanks,
>> > Lucy.
>> >
>> >
>>
>|||How do you not set available values...in report designer report params
dialog, my parameter has a name, 'tick', has the non-queried available values
radio button clicked and the list to the right is completely blank, as I want
it to be. This parameter is unique everytime a report is executed and cannot
be selected from a list of existing values...but I'm still getting the
following error when I call the web service render method...
+ System.SystemException {"The value provided for the report parameter
'tick' is not valid for its type. --> The value provided for the report
parameter 'tick' is not valid for its type. --> The value provided for the
report parameter 'tick' is not valid for its type."} System.SystemException
"Lev Semenets [MSFT]" wrote:
> To summarize, error rsInvalidReportParameter happened because same stored
> procedure was used for available values and default value, and this stored
> procedure returned unique result for every call.
> Report Server was executing this stored procedure twice (1st time for
> available values and 2nd time for default value) and was unable to match
> default value with available value.
> Solution is not to set available values.
> Also because you just wanted to have read-only parameter, it is enough to
> leave prompt blank, and Report Desinger would set parameter properties
> during Deploy procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Yes. From query. Same to "default".
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> >> Does it have list of available values?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Lusy Crown" <evesq@.uk2.net> wrote in message
> >> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> >> > Hello,
> >> >
> >> > I have troubles with hidden parameters. What am I doing:
> >> >
> >> > 1) define a report parameter based on a dataset's field (a dataset is
> >> > "Stored proc." type, the procedure returns the only string value -- if
> > it
> >> > is
> >> > important)
> >> > 2) Build & deploy a solution, run the report from Report manager --
> >> > it's
> >> > ok
> >> > 3) run the report from Report manager, click on "Properties" tab,
> >> > choose
> >> > "parameters":
> >> > Has Default - leave checked on
> >> > Default Value - (Query Based)
> >> > Null - (none)
> >> > Prompt User - leave checked on
> >> > Prompt String - I cleared this textbox
> >> > 4) run the report again:
> >> >
> >> > Reporting Services Error
> >> > Default value or value provided for the report parameter 'MyParam' is
> > not
> >> > a
> >> > valid value. (rsInvalidReportParameter) Get Online Help
> >> >
> >> > Refresh button on RM or the IE's same button don't change this error
> >> > message
> >> >
> >> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> > string
> >> > 6) run the report: it works ok, however the aforementioned parameter
> > acts
> >> > like it doesn't have Default Value anymore.
> >> >
> >> > What have I do to hide param properly?
> >> >
> >> > (MS RS sp1)
> >> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >> >
> >> > Thanks,
> >> > Lucy.
> >> >
> >> >
> >>
> >>
> >
> >
>
>
I have troubles with hidden parameters. What am I doing:
1) define a report parameter based on a dataset's field (a dataset is
"Stored proc." type, the procedure returns the only string value -- if it is
important)
2) Build & deploy a solution, run the report from Report manager -- it's ok
3) run the report from Report manager, click on "Properties" tab, choose
"parameters":
Has Default - leave checked on
Default Value - (Query Based)
Null - (none)
Prompt User - leave checked on
Prompt String - I cleared this textbox
4) run the report again:
Reporting Services Error
Default value or value provided for the report parameter 'MyParam' is not a
valid value. (rsInvalidReportParameter) Get Online Help
Refresh button on RM or the IE's same button don't change this error message
5) Properties -> Parameters -> Prompt String: type a former prompt string
6) run the report: it works ok, however the aforementioned parameter acts
like it doesn't have Default Value anymore.
What have I do to hide param properly?
(MS RS sp1)
Microsoft SQL Server Reporting Services Version 8.00.878.00
Thanks,
Lucy.Does it have list of available values?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have troubles with hidden parameters. What am I doing:
> 1) define a report parameter based on a dataset's field (a dataset is
> "Stored proc." type, the procedure returns the only string value -- if it
> is
> important)
> 2) Build & deploy a solution, run the report from Report manager -- it's
> ok
> 3) run the report from Report manager, click on "Properties" tab, choose
> "parameters":
> Has Default - leave checked on
> Default Value - (Query Based)
> Null - (none)
> Prompt User - leave checked on
> Prompt String - I cleared this textbox
> 4) run the report again:
> Reporting Services Error
> Default value or value provided for the report parameter 'MyParam' is not
> a
> valid value. (rsInvalidReportParameter) Get Online Help
> Refresh button on RM or the IE's same button don't change this error
> message
> 5) Properties -> Parameters -> Prompt String: type a former prompt string
> 6) run the report: it works ok, however the aforementioned parameter acts
> like it doesn't have Default Value anymore.
> What have I do to hide param properly?
> (MS RS sp1)
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> Thanks,
> Lucy.
>|||Yes. From query. Same to "default".
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Does it have list of available values?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I have troubles with hidden parameters. What am I doing:
> >
> > 1) define a report parameter based on a dataset's field (a dataset is
> > "Stored proc." type, the procedure returns the only string value -- if
it
> > is
> > important)
> > 2) Build & deploy a solution, run the report from Report manager -- it's
> > ok
> > 3) run the report from Report manager, click on "Properties" tab, choose
> > "parameters":
> > Has Default - leave checked on
> > Default Value - (Query Based)
> > Null - (none)
> > Prompt User - leave checked on
> > Prompt String - I cleared this textbox
> > 4) run the report again:
> >
> > Reporting Services Error
> > Default value or value provided for the report parameter 'MyParam' is
not
> > a
> > valid value. (rsInvalidReportParameter) Get Online Help
> >
> > Refresh button on RM or the IE's same button don't change this error
> > message
> >
> > 5) Properties -> Parameters -> Prompt String: type a former prompt
string
> > 6) run the report: it works ok, however the aforementioned parameter
acts
> > like it doesn't have Default Value anymore.
> >
> > What have I do to hide param properly?
> >
> > (MS RS sp1)
> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >
> > Thanks,
> > Lucy.
> >
> >
>|||To summarize, error rsInvalidReportParameter happened because same stored
procedure was used for available values and default value, and this stored
procedure returned unique result for every call.
Report Server was executing this stored procedure twice (1st time for
available values and 2nd time for default value) and was unable to match
default value with available value.
Solution is not to set available values.
Also because you just wanted to have read-only parameter, it is enough to
leave prompt blank, and Report Desinger would set parameter properties
during Deploy procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Yes. From query. Same to "default".
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
>> Does it have list of available values?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lusy Crown" <evesq@.uk2.net> wrote in message
>> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
>> > Hello,
>> >
>> > I have troubles with hidden parameters. What am I doing:
>> >
>> > 1) define a report parameter based on a dataset's field (a dataset is
>> > "Stored proc." type, the procedure returns the only string value -- if
> it
>> > is
>> > important)
>> > 2) Build & deploy a solution, run the report from Report manager --
>> > it's
>> > ok
>> > 3) run the report from Report manager, click on "Properties" tab,
>> > choose
>> > "parameters":
>> > Has Default - leave checked on
>> > Default Value - (Query Based)
>> > Null - (none)
>> > Prompt User - leave checked on
>> > Prompt String - I cleared this textbox
>> > 4) run the report again:
>> >
>> > Reporting Services Error
>> > Default value or value provided for the report parameter 'MyParam' is
> not
>> > a
>> > valid value. (rsInvalidReportParameter) Get Online Help
>> >
>> > Refresh button on RM or the IE's same button don't change this error
>> > message
>> >
>> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> string
>> > 6) run the report: it works ok, however the aforementioned parameter
> acts
>> > like it doesn't have Default Value anymore.
>> >
>> > What have I do to hide param properly?
>> >
>> > (MS RS sp1)
>> > Microsoft SQL Server Reporting Services Version 8.00.878.00
>> >
>> > Thanks,
>> > Lucy.
>> >
>> >
>>
>|||How do you not set available values...in report designer report params
dialog, my parameter has a name, 'tick', has the non-queried available values
radio button clicked and the list to the right is completely blank, as I want
it to be. This parameter is unique everytime a report is executed and cannot
be selected from a list of existing values...but I'm still getting the
following error when I call the web service render method...
+ System.SystemException {"The value provided for the report parameter
'tick' is not valid for its type. --> The value provided for the report
parameter 'tick' is not valid for its type. --> The value provided for the
report parameter 'tick' is not valid for its type."} System.SystemException
"Lev Semenets [MSFT]" wrote:
> To summarize, error rsInvalidReportParameter happened because same stored
> procedure was used for available values and default value, and this stored
> procedure returned unique result for every call.
> Report Server was executing this stored procedure twice (1st time for
> available values and 2nd time for default value) and was unable to match
> default value with available value.
> Solution is not to set available values.
> Also because you just wanted to have read-only parameter, it is enough to
> leave prompt blank, and Report Desinger would set parameter properties
> during Deploy procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Yes. From query. Same to "default".
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> >> Does it have list of available values?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Lusy Crown" <evesq@.uk2.net> wrote in message
> >> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> >> > Hello,
> >> >
> >> > I have troubles with hidden parameters. What am I doing:
> >> >
> >> > 1) define a report parameter based on a dataset's field (a dataset is
> >> > "Stored proc." type, the procedure returns the only string value -- if
> > it
> >> > is
> >> > important)
> >> > 2) Build & deploy a solution, run the report from Report manager --
> >> > it's
> >> > ok
> >> > 3) run the report from Report manager, click on "Properties" tab,
> >> > choose
> >> > "parameters":
> >> > Has Default - leave checked on
> >> > Default Value - (Query Based)
> >> > Null - (none)
> >> > Prompt User - leave checked on
> >> > Prompt String - I cleared this textbox
> >> > 4) run the report again:
> >> >
> >> > Reporting Services Error
> >> > Default value or value provided for the report parameter 'MyParam' is
> > not
> >> > a
> >> > valid value. (rsInvalidReportParameter) Get Online Help
> >> >
> >> > Refresh button on RM or the IE's same button don't change this error
> >> > message
> >> >
> >> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> > string
> >> > 6) run the report: it works ok, however the aforementioned parameter
> > acts
> >> > like it doesn't have Default Value anymore.
> >> >
> >> > What have I do to hide param properly?
> >> >
> >> > (MS RS sp1)
> >> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >> >
> >> > Thanks,
> >> > Lucy.
> >> >
> >> >
> >>
> >>
> >
> >
>
>
Monday, February 13, 2012
[*-)] About Auto Increment Field
Hi,
I have one Auto increment field in the table.
I have a problem that suppose I have 10 Records in the table, Now If someone delete record no 3 and 5 then it has 8 records.
But The field will give 11 no to new record I want it first fill 3 and 5 then give 11 to the new record.
Is it possible with the auto increment field in sql server 2005.
No it's not possible with autoincrement fields. You would have to create your own primary key field, and query the table to get the lowest available number everytime you performed an insert. I would never recommend doing this. It's a potential nightmare, and I can't think of one single good reason for doing it. All you need is a unique identifier for each row. It doesn't matter if it's not nice and pretty (in terms of a sequential series), so long as it works.
Subscribe to:
Posts (Atom)