How robust is Reporting Services 2005

Hi,
I am thinking to suggest Reporting Services 2005 to our client to build
several complex/mission critical reports.
The only point that is not clear for me is that how robust is the current
beta version of SSRS 2005?
Can we start report development on SSRS 2005 latest beta version and then
continue using the final release in November?
Is the latest beta version of SSRS 2005 aligning with the Visual Studio 2005
Release Candidate 1?
I am asking because there are some report viewer user controls for visual
studio 2005 that is included in SSRS 2005. I am not sure those report viewer
user controls work in VS2005 release candidate 1.
Any help, suggestion or advice would be appreciated,
AlanAlan,
There will, I suspect, be few changes between Sept CTP of RS2005 and
the RTM version.
Sept CTP is certainly robust enough to develop on and previous
versions have been for quite some time.
Sept CTP of RS2005 is compatible with VS2005 RC.
Install Sept CTP first, then VS2005.
Andrew Watt
MVP - InfoPath
On Sat, 15 Oct 2005 12:01:05 -0400, "A.M-SG"
<alanalan@.newsgroup.nospam> wrote:
>Hi,
>
>I am thinking to suggest Reporting Services 2005 to our client to build
>several complex/mission critical reports.
>
>The only point that is not clear for me is that how robust is the current
>beta version of SSRS 2005?
>
>Can we start report development on SSRS 2005 latest beta version and then
>continue using the final release in November?
>
>Is the latest beta version of SSRS 2005 aligning with the Visual Studio 2005
>Release Candidate 1?
>
>I am asking because there are some report viewer user controls for visual
>studio 2005 that is included in SSRS 2005. I am not sure those report viewer
>user controls work in VS2005 release candidate 1.
>
>Any help, suggestion or advice would be appreciated,
>Alan
>
>|||Hello,
For questions of SQL server 2005, please access the following website and
then click "SQL Server 2005 Forums on MSDN":
http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
Thanks for cooperation.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.|||Sophie,
This newsgroup, as I understand it, is intended to cover all versions
of Reporting Services.
I have asked internally if there will be a separate Reporting Services
2005 newsgroup and was told that this newsgroup is the one where SQL
Server 2000 and 2005 Reporting Services will be answered.
I am happy to continue to help users and answer Reporting Services
2005 questions in this newsgroup.
Of course, the fora provide an alternate place to ask questions.
Andrew Watt
MVP - InfoPath
On Mon, 17 Oct 2005 02:02:13 GMT, v-sguo@.online.microsoft.com (Sophie
Guo [MSFT]) wrote:
>Hello,
>For questions of SQL server 2005, please access the following website and
>then click "SQL Server 2005 Forums on MSDN":
>http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
>Thanks for cooperation.
>Sophie Guo
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>=====================================================>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.
>
>|||Hello Andrew,
Thanks very much for your reply and useful information provided. I agreed
much with you that most of features should be OK now and it is OK to
program in SQL 2005. Perhaps there will be some minor change between CTP
and RTM version. But Alan doesn't need to change much code after the final
version releases. Besides, you are right that all SQL reporting service
related issues can be posted here.
For this particular post, Alan is a MSDN subscriber and posted his question
in MSDN managed newsgroup. Our team has commitment to reply him and work
with him on it. Or if it is beyond our newsgroup support boundary, we
should let Alan know as soon as possible so that he could find some other
proper channel if he feel it is necessary. That is why Sophie replied him
that.
Surely all we have done is not stopping community from answering Alan. I
believe all the information is welcome here expecially experience sharing.
Please do continue to share your experience here and let's work together to
make it better and better.
Thanks very much for your understanding.
Have a good day.
Best regards,
Yanhong Huang
Microsoft Community Support
Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.|||I'm surprised there are no words of caution.
Looking at your time frame, there are only a few weeks until the final
version is released, so there is a limited amount of work you'll be
able to accomplish before you'll be looking to re-install.
Installing the final release where you had beta versions running may
not be a trivial task. I don't know how many machines you are dealing
with, but you may want to limit the extent to which you install the
CTP.|||Hi Yanhong,
No problem. :) I am sure that we all want users to get maximum benefit
from Reporting Services 2005.
Andrew Watt
MVP - InfoPath
On Mon, 17 Oct 2005 09:29:03 GMT, yhhuang@.online.microsoft.com
(Yan-Hong Huang[MSFT]) wrote:
>Hello Andrew,
>Thanks very much for your reply and useful information provided. I agreed
>much with you that most of features should be OK now and it is OK to
>program in SQL 2005. Perhaps there will be some minor change between CTP
>and RTM version. But Alan doesn't need to change much code after the final
>version releases. Besides, you are right that all SQL reporting service
>related issues can be posted here.
>For this particular post, Alan is a MSDN subscriber and posted his question
>in MSDN managed newsgroup. Our team has commitment to reply him and work
>with him on it. Or if it is beyond our newsgroup support boundary, we
>should let Alan know as soon as possible so that he could find some other
>proper channel if he feel it is necessary. That is why Sophie replied him
>that.
>Surely all we have done is not stopping community from answering Alan. I
>believe all the information is welcome here expecially experience sharing.
>Please do continue to share your experience here and let's work together to
>make it better and better.
>Thanks very much for your understanding.
>Have a good day.
>Best regards,
>Yanhong Huang
>Microsoft Community Support
>Get Secure! ¨C www.microsoft.com/security
>This posting is provided "AS IS" with no warranties, and confers no rights.|||I'm surprised there are no words of caution.
Looking at your time frame, there are only a few weeks until the final
version is released, so there is a limited amount of work you'll be
able to accomplish before you'll be looking to re-install.
Installing the final release where you had beta versions running may
not be a trivial task. I don't know how many machines you are dealing
with, but you may want to limit the extent to which you install the
CTP.sql

How robust is Reporting Services 2005

Hi,

I am thinking to suggest Reporting Services 2005 to our client to build several complex/mission critical reports.

The only point that is not clear for me is that how robust is the current beta version of SSRS 2005?

Can we start report development on SSRS 2005 latest beta version and then continue using the final release in November?

Is the latest beta version of SSRS 2005 aligning with the Visual Studio 2005 Release Candidate 1?

I am asking because there are some report viewer user controls for visual studio 2005 that is included in SSRS 2005. I am not sure those report viewer user controls work in VS2005 release candidate 1.

Any help, suggestion or advice would be appreciated,

Alan

You should first install CTP September and then VS 2005 RC1.
CTP September should give you already a good view of SSRS 2005 and you can certainly use it to start your development. The final SQL Server 2005 RTM release will have a few additional improvements.

-- Robert

How ro append characetrs to auto-incrementing number

Hi,
I am creating a new table. in the table, I want to have a field called
OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
value will be the primary key and thus unique for thr record. How do I appen
d
'TTK-' to an autoincrementing number? Do I have to use a trigger which
automatically appends 'TTk-' to the auto-incrementing number as soon as it i
s
inserted in the databse? If so, what will be the code for that trigger? Or i
s
there a better way to do it.
Please help.
Thanks
--
pmudIf TTK- prepends every unique value in the column, then it is no more unique
than the number itself. Think about it.
Anyway, the way to do this and to needlessly use up disk space so you can
fool yourself into believing you have a better key is to use a computed
field:
CREATE TABLE dbo.Orders
(
OrderNo INT IDENTITY(1,1),
..,
FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
);
Or a view:
CREATE VIEW dbo.View_Orders
AS
SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
FROM dbo.Orders;
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:6D76A707-E2A2-46C1-8B86-5F06EB01C989@.microsoft.com...
> Hi,
> I am creating a new table. in the table, I want to have a field called
> OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
> value will be the primary key and thus unique for thr record. How do I
> append
> 'TTK-' to an autoincrementing number? Do I have to use a trigger which
> automatically appends 'TTk-' to the auto-incrementing number as soon as it
> is
> inserted in the databse? If so, what will be the code for that trigger? Or
> is
> there a better way to do it.
> Please help.
> Thanks
> --
> pmud|||Hi Aaron,
We have orders for differnt companies in different tables. The primary
reason for doing this is that we want to allocate a separate chunk of order
nu,bers for each company rather than haveing Order no 10 for Comapny1 and 1
1
for Company2. So to aviod jumping numbers, we broke orders into different
tables based on teh company.
Now we want to prefix each Order no, in each table with the first 3
characters of the company name. For this, will the following trigger work on
each of the tables:
CREATE TRIGGER IDENTITY_TRIGGER
ON INSERT
AS
{
UPDATE TABLE1
SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
}
Please let me know if there is any mistake I have made. Also, please let me
know if you have any better quick ideas.
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> If TTK- prepends every unique value in the column, then it is no more uniq
ue
> than the number itself. Think about it.
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );
> Or a view:
> CREATE VIEW dbo.View_Orders
> AS
> SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
> FROM dbo.Orders;
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:6D76A707-E2A2-46C1-8B86-5F06EB01C989@.microsoft.com...
>
>|||Warning, warning! I've got alarm bells going off in my head.
You just said you're using 1 column to store 2 facts. This is a really
bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
them in 2 different columns. You table should look something like:
create table dbo.Orders
(
OrderNo int identity(1,1) primary key,
CompanyCode char(3) not null,
..,
CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
varchar(15)),
..,
);
Use the OrderNo as your unique primary key, store the company code in a
separate column and if you want to combine those 2 facts then do so in a
computed column. Don't mix the 2 values in a single materialised
column, it'll just cause you headaches in the long term.
*mike hodgson*
http://sqlnerd.blogspot.com
pmud wrote:

>Hi Aaron,
>We have orders for differnt companies in different tables. The primary
>reason for doing this is that we want to allocate a separate chunk of order
>nu,bers for each company rather than haveing Order no 10 for Comapny1 and
11
>for Company2. So to aviod jumping numbers, we broke orders into different
>tables based on teh company.
>Now we want to prefix each Order no, in each table with the first 3
>characters of the company name. For this, will the following trigger work o
n
>each of the tables:
>CREATE TRIGGER IDENTITY_TRIGGER
>ON INSERT
>AS
>{
>UPDATE TABLE1
>SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
>}
>Please let me know if there is any mistake I have made. Also, please let me
>know if you have any better quick ideas.
>Thanks
>|||Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> If TTK- prepends every unique value in the column, then it is no more
> unique than the number itself. Think about it.
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );
To add to Aaron's post: if you need to, you can index the computed
column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||pmud (pmud@.discussions.microsoft.com) writes:
> We have orders for differnt companies in different tables. The primary
> reason for doing this is that we want to allocate a separate chunk of
> order nu,bers for each company rather than haveing Order no 10 for
> Comapny1 and 11 for Company2. So to aviod jumping numbers, we broke
> orders into different tables based on teh company.
You did what?
It's alright of wanting to have separate order numbers per company, but
you're criminal if you split the table into one per company only be able
to use IDENTITY for the order number. The normal key for the table would
be (company_id, order_id). "But then I can't use auto-increment?" So,
don't use auto-increment then. Rolling your own incrementing number is no
big deal:
BEGIN TRANSACTION
SELECT @.orderid = coalesce(MAX(orderid), 0) + 1
FROM orders WITH (UPDLOCK, HOLDLOCK)
WHERE company = @.company
INSERT orders (company, orderid, ...
VALUES (@.company, @.orderid, ...
..
COMMIT TRANSACTION
And I suspect that if you want one series per company, you want it to
be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
and not 1, 5, 7 which is what you could get if you use IDENTITY.
I will have to add one caveat: the chief reason to use IDENTITY is
not simplicity of proramming, but scalability. In a high-transaction
environment where hundreds of rows are inserted by minute, IDENTITY
may be essential to avoid conentention. But somehow, I don't expect
the order rate for a single company be that high.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Mike,
You said that it was criminal to break out tables into different orders
table per company. What do you think are the flaws in this approach? I know
that it is not a good design but what are the exact resons?
Thanks
--
pmud
"Mike Hodgson" wrote:

> Warning, warning! I've got alarm bells going off in my head.
> You just said you're using 1 column to store 2 facts. This is a really
> bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
> them in 2 different columns. You table should look something like:
> create table dbo.Orders
> (
> OrderNo int identity(1,1) primary key,
> CompanyCode char(3) not null,
> ...,
> CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
> varchar(15)),
> ...,
> );
> Use the OrderNo as your unique primary key, store the company code in a
> separate column and if you want to combine those 2 facts then do so in a
> computed column. Don't mix the 2 values in a single materialised
> column, it'll just cause you headaches in the long term.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> pmud wrote:
>
>|||Hi Earl,
What are the flaws in this structure?
Thanks
--
pmud
"Erland Sommarskog" wrote:

> pmud (pmud@.discussions.microsoft.com) writes:
> You did what?
> It's alright of wanting to have separate order numbers per company, but
> you're criminal if you split the table into one per company only be able
> to use IDENTITY for the order number. The normal key for the table would
> be (company_id, order_id). "But then I can't use auto-increment?" So,
> don't use auto-increment then. Rolling your own incrementing number is no
> big deal:
> BEGIN TRANSACTION
> SELECT @.orderid = coalesce(MAX(orderid), 0) + 1
> FROM orders WITH (UPDLOCK, HOLDLOCK)
> WHERE company = @.company
> INSERT orders (company, orderid, ...
> VALUES (@.company, @.orderid, ...
> ...
> COMMIT TRANSACTION
> And I suspect that if you want one series per company, you want it to
> be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
> and not 1, 5, 7 which is what you could get if you use IDENTITY.
> I will have to add one caveat: the chief reason to use IDENTITY is
> not simplicity of proramming, but scalability. In a high-transaction
> environment where hundreds of rows are inserted by minute, IDENTITY
> may be essential to avoid conentention. But somehow, I don't expect
> the order rate for a single company be that high.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Earl,
I am not very sure about transactions. Do I have to write teh code for
transactions in trigger window? Also, with the approach you mentioned, will
it be possible to have contiguous order nos for each company?
If so, then that will be great.
Thanks for your help..
--
pmud
"Erland Sommarskog" wrote:

> Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> To add to Aaron's post: if you need to, you can index the computed
> column.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||I didn't say it was criminal, Erland did.
But I agree that it's a bad idea to have 1 table per company - if you
add another company you'd have to change the schema of the DB rather
than simply inserting a row in a table. As a matter of course, you
shouldn't be changing the schema of production databases regularly
simply because you have new data (that has already been defined, eg. an
order). Also this design would make querying across multiple companies
much more complicated because you'd need to use joins or union operators
to combine the sets of data - yuk! To automate queries like this at
runtime you'd need to use dynamic SQL as the structure of your queries
would change each time you add a new company - imagine how ugly that
would quickly become. There are plenty of other things wrong with this
approach if you just think ahead a year or two and use you imagination a
bit.
When I initially posted in this thread I'd missed the bit where you said
you wanted to store orders specific to a company in company-specific
tables. I just thought you wanted to have 1 orders table and keep all
you orders in there. My main objection to your design was storing 2
facts in a single column. Why would you do that? If you store 2 facts,
why not 3, or 4 or 5 or 100? Imagine how hard it would be to write
queries against a table with a single varchar(8000) column that stored
an OrderNo, a CompanyCode, a OrderDate, a ShippingDate, a
DeliveryAddress, a ContactName, etc., etc.
The basic premise behind relational database theory (very much
simplified) is that a table represents a single entity or relationship
(this is in conflict with the idea of multiple orders tables to store
orders) and a column in a table represents a single fact or attribute of
that entity (this is in conflict with combining multiple attributes in a
single column). These are just the fundamental tenets of this area of
theory. (Oh no, I'm starting to sound a little like Celko - LOL!)
Chris Date, Fabian Pascal or E.F. Codd could give you a much better
explanation about relational theory than I could. If you're really
interested in the theory I recommend reading one or more of their books
(An Introduction to Database Systems
<http://www.amazon.com/gp/product/03...5Fencoding=UTF8>
by Chris Date is probably a good starting point or further to that his
latest book Database in Depth
<http://www.amazon.com/gp/product/05...5Fencoding=UTF8> ).
*mike hodgson*
http://sqlnerd.blogspot.com
pmud wrote:

>Hi Mike,
>You said that it was criminal to break out tables into different orders
>table per company. What do you think are the flaws in this approach? I know
>that it is not a good design but what are the exact resons?
>Thanks
>

How revoke different from deny?

Regarding the permission check marks, the manual says:
'The green check mark grants the permission, the red X denies the
permission, and the blank box revokes a previously set permission.'
I'm not clear how a blank box is different from a box which has a red X
mark? Do both all mean deny?
Bing
Never mind. I've figured out how revoke is different from deny.
Bing
"bing" wrote:

> Regarding the permission check marks, the manual says:
> 'The green check mark grants the permission, the red X denies the
> permission, and the blank box revokes a previously set permission.'
> I'm not clear how a blank box is different from a box which has a red X
> mark? Do both all mean deny?
> Bing
|||The grants, revokes and deny's work with our role based permissions.
Follow this example and see if it helps you to understand:
I have a database with a table in it called Table1.
I have a user named Frog who would like to work with Table1.
Frog belongs to the Public role. (Everyone is always a member of public).
Frog also belongs to another role called Marketing
Frog also belongs to another role called Accounting.
SELECT permissions on Table1 have been set as follows:
Public <-- Grant
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- None set (which is the same as Revoke).
You must combine all the permissions for Frog to determine whether or not he
is allowed to SELECT from Table1. Looking at all of the permissions, here
are the rules:
1. He must be Granted somewhere in his list of permissions.
2. He must NOT be denied anywhere in his list of permissions. (Once
Denied, ALWAYS denied).
From our example above, Frog does have SELECT permissions on Table1 becausse
he was granted them by his membership in Public.
If we modify that permission scheme as follows:
Public <-- Grant
Marketing <-- Deny
Accounting<-- Grant
Frog <-- Grant
We then follow our rules:
1. He has been Granted somewhere in his list of permissions.
2. He was denied somewhere in his list of permissions.
Therefore, He DOES NOT have SELECT permissions.
If we revoke all permission on the table as follows:
Public <-- Revoke
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- Revoke
Check our rules:
1. No grant anywhere in the hierarchy, therefore, no SELECT permissions.
2. Not denied anywhere, but see rule 1.
HTH
Rick Sawtell
MCT, MCSD, MCDBA

How revoke different from deny?

Regarding the permission check marks, the manual says:
'The green check mark grants the permission, the red X denies the
permission, and the blank box revokes a previously set permission.'
I'm not clear how a blank box is different from a box which has a red X
mark? Do both all mean deny?
BingNever mind. I've figured out how revoke is different from deny.
Bing
"bing" wrote:

> Regarding the permission check marks, the manual says:
> 'The green check mark grants the permission, the red X denies the
> permission, and the blank box revokes a previously set permission.'
> I'm not clear how a blank box is different from a box which has a red X
> mark? Do both all mean deny?
> Bing|||The grants, revokes and deny's work with our role based permissions.
Follow this example and see if it helps you to understand:
I have a database with a table in it called Table1.
I have a user named Frog who would like to work with Table1.
Frog belongs to the Public role. (Everyone is always a member of public).
Frog also belongs to another role called Marketing
Frog also belongs to another role called Accounting.
SELECT permissions on Table1 have been set as follows:
Public <-- Grant
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- None set (which is the same as Revoke).
You must combine all the permissions for Frog to determine whether or not he
is allowed to SELECT from Table1. Looking at all of the permissions, here
are the rules:
1. He must be Granted somewhere in his list of permissions.
2. He must NOT be denied anywhere in his list of permissions. (Once
Denied, ALWAYS denied).
From our example above, Frog does have SELECT permissions on Table1 becausse
he was granted them by his membership in Public.
If we modify that permission scheme as follows:
Public <-- Grant
Marketing <-- Deny
Accounting<-- Grant
Frog <-- Grant
We then follow our rules:
1. He has been Granted somewhere in his list of permissions.
2. He was denied somewhere in his list of permissions.
Therefore, He DOES NOT have SELECT permissions.
If we revoke all permission on the table as follows:
Public <-- Revoke
Marketing <-- Revoke
Accounting<-- Revoke
Frog <-- Revoke
Check our rules:
1. No grant anywhere in the hierarchy, therefore, no SELECT permissions.
2. Not denied anywhere, but see rule 1.
HTH
Rick Sawtell
MCT, MCSD, MCDBA

How return the duplicate row?

Hello,

I would like return in a file the duplicates lines from a table.

Is it possible with a composant?

Or must I use a sql request.

But my problem with the request is the null value in the cell of the line. When U do a join and there is a null value in the row, there no resoult from the request.

How can U do to solve this problem?

Thank you

Regards.

Laurent Albiac

You could copy them into a plain file, for example, keep in mind that your statement is like this:

SELECT f1,f2,f3,count(*)

from table

group by f1,f2,f3

having count(*)> 1

I don't see where's the problem

|||

or you can try this

SELECT ROW_NUMBER() OVER (ORDER BY field1 ASC, field2 ASC) AS ROWID,
field1,field2 FROM table

|||

rabbiwan wrote:

Hello,

I would like return in a file the duplicates lines from a table.

Is it possible with a composant?

Or must I use a sql request.

But my problem with the request is the null value in the cell of the line. When U do a join and there is a null value in the row, there no resoult from the request.

How can U do to solve this problem?

Thank you

Regards.

Laurent Albiac

If you want to find duplicates use the Aggregate component to see how many rows there are of each combination. Anything that is plural - those are your duplicates.

-Jamie

sql

How restore database on other server with rights to all objects

Can you help me?
Thanks
Alina
Alina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>
|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>

How restore database on other server with rights to all objects

Can you help me?
Thanks
AlinaAlina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Grna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>

How restore database on other server with rights to all objects

Can you help me?
Thanks
AlinaAlina
Look at RESTORE command in the BOL
Also you will have to move all logins from the OLD server to the NEW one.
"Alina Górna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>|||Hi,
You could see the below links to restore and map the users.
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Thanks
Hari
MCDBA
"Alina Górna" <a.gorna@.icnet.pl> wrote in message
news:1091512370.614501@.d1.icnet...
> Can you help me?
> Thanks
> Alina
>

How restore BAK file?

We use Avaya IP Office. Call records are stored in programs such as Delta
Server and Database Server Supervisor and backed up with SDX Archiver.
Avaya says it's using Microsoft's SQL database.
Question: How do I restore a database? I have a .BAK file that I need to
restore, to try to retreive some data. Avaya says it's a standard Microsoft
SQL backup.
Can someone tell me how to restore this backup? I know Windows, but I've
never used any flavor of SQL.
thanks,
BillYou can read about the RESTORE DATABASE command in Books Online. If you are totally new on SQL
Server, I suggest you pick up a book at appropriate level, that will save you lots of time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:01FB537F-9227-485F-9A4D-D23CB8EB3718@.microsoft.com...
> We use Avaya IP Office. Call records are stored in programs such as Delta
> Server and Database Server Supervisor and backed up with SDX Archiver.
> Avaya says it's using Microsoft's SQL database.
> Question: How do I restore a database? I have a .BAK file that I need to
> restore, to try to retreive some data. Avaya says it's a standard Microsoft
> SQL backup.
> Can someone tell me how to restore this backup? I know Windows, but I've
> never used any flavor of SQL.
> thanks,
> Bill|||Hi Bill,
If you don't know SQL Server, then you must be unawared of Books Online...
Here, you can find info regarding to Backup and Restore in SQL Server 2005
under this topic.
http://msdn2.microsoft.com/en-us/library/ms187048.aspx (This is Books
Online)
If you need further help, we are around here.
Ekrem Ã?nsoy
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:01FB537F-9227-485F-9A4D-D23CB8EB3718@.microsoft.com...
> We use Avaya IP Office. Call records are stored in programs such as Delta
> Server and Database Server Supervisor and backed up with SDX Archiver.
> Avaya says it's using Microsoft's SQL database.
> Question: How do I restore a database? I have a .BAK file that I need to
> restore, to try to retreive some data. Avaya says it's a standard
> Microsoft
> SQL backup.
> Can someone tell me how to restore this backup? I know Windows, but I've
> never used any flavor of SQL.
> thanks,
> Billsql

how report manager works?

Understand how Report Manager functions internally through the use of classes in the ReportingServicesWebUserInterface assembly and leverage its undocumented functionality.

Hi,

The power of the report manager is not in the report manager itself. Reporting Services is implemented as a web service. Report Manager is just an interface (.APSX) on top of that.

If needed, you can create your own interface using any language that understands web services.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

do you know any sample example? I would greatly appreciate.

Thanks,

How Replication Works with inserts and Autonumber pKs

Hi,
I am using SQL Server replication.
I have published articles and my subscribers are some users with pocket pc.

I have a couple of tables with Identity primary keys.
I have already set them to "Not for replication".

My questions:
For tables with identity values, say tblContact, when different users add new contacts new numbers are generated.
What happens when they sync? if two users added a new records , and both have the same number, will sql server consider one as updating the other? Or it will add both with new numbers to the master table and update both subscriptions.

In other words, how should the autonumber primary keys be handled in replication? should I create a composite key, say userID+autonumber to avoid such a situation?

I hope my question is clear.

Thanksmake your identity keys guids (sql type uniqueidentifier, set default property to Newid() ) or random integers. choice depends on number of records your table will have. ie: < 1billion, could probably get away with random integer with low risk of collision, > 1 billion better use guids. I personally use guids for most stuff because I don't have to ever worry about collisions and storage is not an issue, but this may be an issue for you on a pocketpc.

hth
burt|||btw...

if you need the new identity right away and your provider doesn't support refreshing the underlying value you may just wish to generate your own guid (don't set a default on the field). That's one other advantage to this method.

another way: if you're using stored procedures you could add an output parameter to return the newly created guid. this is especially helpful if your programming language doesn't have built in facilities to create a guid.|||I like the newid() idea, but if you are set on identity values, you can make them mutually exclusive sets. This will require you, however, to declare how many servers participate in the replication at the time of setup, because there won't be much going back (although I have not read up on dbcc fixidentity (sp?) lately).

Server 1 has

col1 int identity (1, 3) primary key

Server 2 has

col1 int identity (2, 3) primary key

Server 3 has

col1 int identity (3, 3) primary key

This way, server 1 has values 1,4,7,10,13,...
server 2 has 2,5,8,11,...
and server three has 3,6,9,12,...
Server 4 gets no break.|||Thanks for your reply,
I read up the Identity range and GUID in books online.

But I have a question:
Instead of using guid and identity range, what if we use a composite key, for example use an identity column combined with the userID?

How is that?|||then you'd might as well just find the natural key, assuming one exists?
compound primary keys, while fully supported, will make your queries much more complex.

the project i'm working on now has several compound primary keys (one with 4 fields in the key) and the queries are a nightmare is some places - especially because this is a big project with well over 100 stored procs.

note also that i'm not sure you can do a "compound foreign key". in other words you may still have to have an artificial primary key to establish relationships.

How replication works in this scenario.. Please help

Hello all,
I am a newbie when it comes to replication. I have read the BOL but I
was overwhilemed with the amount of information about replication.
We are planning to have a server with sql server 2005 and disconnected
laptops with sql server express. How would replication work in this
scenario assuming we are using merge replication:
Table AA [ a replicated table on the server and 9 columns]
User #1 has table AA on his laptop... and has added 3 records, deleted
1 and
update 1 record (5 transaction in total)...
Table AA has 3 new records added after AA last sync his replica...
Joe Admin comes and decided to make schema changes to table AA on the
server...
Q#1 can he do that even though the log has 3 transactions?
Q#2 can he does it if the log was empty?
Q#3 assume the admin did the schema changes to table AA...and Now user
#1 trying
to sync
does that mean User #1 will lose all her changes (the 5 transaction
above)...
Q#4: What is the normal best practice if you have sql express.. How
would handle such
scheduled schema change?
Thank you very much!
There are some complications with 'altering' the schema of merge replicated
tables while the subscriber does data changes, but from your description you
are just adding a column on the publisher and this scenario looks OK. The
laptop users should not lose their changes. Finally remember that
sp_repladdcolumn is run on the publisher only.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How replicate a db with msde?

How replicate a db in msde?
Best regards
RV
Hi,
Look the below article:-
http://support.microsoft.com/default...;en-us;Q324992
Thanks
Hari
MCDBA
"Richard Villaln" <rv@.cantv.net> wrote in message
news:eU#wC$AVEHA.484@.TK2MSFTNGP10.phx.gbl...
> How replicate a db in msde?
> Best regards
> RV
>

How remove blank values from report parameter list

Hi,

In report designer, while adding report parameters how to remove blank or empty values from parameter drop down list. Blank values will appear even after unchecking "Allow blank value" option.

Thanx

I think you messed something up. Allow blank values only means that you can put in a empty string as a parameter value. If you have a data driven combobox you will have to redefine the query that fills the combobox to make it only to show non blank values. Look for the column you specified as value column if you can filter the values out, that are retrieved from the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
sql

How remove all quote characters in a sql table?

Hi

I have a table with a few hundred emailadresses. How can I delete all quotes (') from the addresses, so that'email@.email.com' is replaced asemail@.email.com.

Thank you

zipfeli

You can use the REPLACE Function. And you want to an UPDATE not a DELETE.

UPDATE table SET Column =REPLACE(column, '''', '')

|||

UPDATE TableName SET EMailAddrCol = REPLACE(EMailAddrCol, '''', '')

I hope this helps,

Best of Luck!

How reliable is Microsoft's type4-JDBC driver for MsSQL Server?

Hai Friends,

I am using MsSQLServer in the backend and java in the front-end.

How reliable Microsoft's type4-JDBC driver for MsSQL Server?

I am very afraid as its only a beta version and version 1.0 and my project is a commercial one.

Is there any other better type4-JDBC driver for MsSQLServer available -which i can get for no cost-free of cost. Please guide me.

Thank You

Okay!

I am not going to use Microsoft's type4-JDBC driver for MsSQL Server.

I am going to use jTDC of sourceforge.com

|||

The new SQL Server JDBC driver shipped last month and is quite reliable -- it passes cert suites from all the major vendors and is already being widely used.

Check it out here -- http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en

-shelby

How redirect SQL Sserver XML output?

I have to generate an XMl from a SQL Server 2000 table. Using the clause FOR
XML i can only send the result to screen or to file.
I want, instead, store the result, into a field column in another table.
There's a way i can do it without passing through a file (and OpenXML), but
storing the result directly into the field column?
tnks in advance
andrea
Do you want to say something along the line of
insert into T(xmlcol) values (select ... for xml ...)
?
If so, you will need to wait for SQL Server 2005. In SQL Server 2000, you
had to do some really ugly workarounds by moving the XML to the client and
then reload it back into the server.
Best regards
Michael
"vikys" <vikys@.hotmail.com> wrote in message
news:utcDmwwcFHA.3712@.TK2MSFTNGP09.phx.gbl...
>I have to generate an XMl from a SQL Server 2000 table. Using the clause
>FOR
> XML i can only send the result to screen or to file.
> I want, instead, store the result, into a field column in another table.
> There's a way i can do it without passing through a file (and OpenXML),
> but
> storing the result directly into the field column?
> tnks in advance
> andrea
>

How realize Windows Authentication in small networks?

Hello,

In SQL Servern Books is written:
SQL Server Authentication is provided for backward compatibility only. When possible, use Windows Authentication.

How can Windows Authentication be realized in small networks from two ore more computers running Windows XP?

Having all a application written in VB Net 2005 for example, which connects to a central database on one computer. Where the cost and afford for a domain controller running Windows Server is not necessary.

If Windows Authentication can't be realized or can't be realized easy in such a scenario, and SQL Server Authentication is not supported any more, then SQL Server can't be taken as database server for this scenario, where the focus is at simplicity and low cost.

Regards,
Markus

I think that is a a bit confusing from the BOL. Backward means those machines that can not use Windows authentication, like workgroup machines, other platforms etc. I Don′t think they will deprecate that in the future. Perhaps that might be the help to your costly problems, don′t now if that applies to you, haven′t tried it yet (ADAM) http://www.microsoft.com/downloads/details.aspx?FamilyId=9688F8B9-1034-4EF6-A3E5-2A2A57B5C8E4&displaylang=en

HTH, Jens Suessmeyer.

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

> costly problems

i think simplicity and low cost need not be a problem. When possible, it should be a great advantage :-)

|||

We still support SQL Authentication, use it in good health.

BOL is just trying to encourage folks on larger networks who have Windows Auth available to them to use it as it offers several advantages from a management point of view. For small Workgroup networks such as that you describe, SQL Auth is your answer.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||Thank you, Mike. Then it was a misunderstanding of "SQL Server Authentication is provided for backward compatibility only." in the documentation. How already Jens expected.

How Query Same Columns w/Mulitple Criteria

I am trying to build my first report that is more than a simple columnar
report and wonder if you guys can help. I need to query the same sets of
fields but with different criteria and compare them over time as follows:
-show order NetAmount, Gross Amount, %NetAmount for each sales person for
2003
-show order NetAmount, Gross Amount, %NetAmount for each sales person for
2004
-show % change
Might look something like:
SalesPersId 03GrsAmt 03NetAmt 03NetAmt% | 04GrsAmt 04NetAmt 04NetAmt%
| GrsChg% NetChg%
Not sure if this needs subqueries/subreports or some othe report feature.
I am struggling with the documentation so many thanks for any helpTake a look at [Company Sales.rdl] sample report that ships with the
product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23gFwvgDrEHA.3868@.TK2MSFTNGP15.phx.gbl...
> I am trying to build my first report that is more than a simple columnar
> report and wonder if you guys can help. I need to query the same sets of
> fields but with different criteria and compare them over time as follows:
> -show order NetAmount, Gross Amount, %NetAmount for each sales person for
> 2003
> -show order NetAmount, Gross Amount, %NetAmount for each sales person for
> 2004
> -show % change
> Might look something like:
> SalesPersId 03GrsAmt 03NetAmt 03NetAmt% | 04GrsAmt 04NetAmt
04NetAmt%
> | GrsChg% NetChg%
> Not sure if this needs subqueries/subreports or some othe report feature.
> I am struggling with the documentation so many thanks for any help
>
>|||Thanks for the resonse. I don't have the sampe data base loaded that is
needed for the sampler reports and not sure why it wasn't created during the
install.
-How would I go about installing that DB?
-If I were using straigt SQL for this, what functions would I need;
subqueries, group by ..?
Thanks again!sql