Hi,
Is it true that setting nocount on in a trigger is almost
a good practice? When it is not true?
--
Many thanks,
OskarOscar
When SET NOCOUNT ON SQL Server does not inform users about rows were
affected.
It is good practice to set it up at the beginning of stored procedures but i
have not seen this command within triggers. It's meaningless.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:3e7601c4a9fc$70b30650$a601280a@.phx.gbl...
> Hi,
> Is it true that setting nocount on in a trigger is almost
> a good practice? When it is not true?
> --
> Many thanks,
> Oskar
>|||On Mon, 4 Oct 2004 12:41:56 +0200, Uri Dimant wrote:
>Oscar
>When SET NOCOUNT ON SQL Server does not inform users about rows were
>affected.
>It is good practice to set it up at the beginning of stored procedures but i
>have not seen this command within triggers. It's meaningless.
Hi Uri,
This is not correct. Run the following code with and wothout the set
nocount on statement in the trigger to see the difference.
set nocount off
go
create table test (a int not null)
go
create table test2 (now datetime not null primary key default(getdate()),
num int not null)
go
create trigger testtrig on test after insert
as
--set nocount on
declare @.num int
select @.num = count(*)
from inserted
insert into test2(num)
select @.num
update test set a = a
go
insert test
select 1 union all select 2
go
select * from test2
go
drop table test2, test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Uri,
But what about this remark in books on-line:
"It is recommended that a trigger not return any results.
This is because special handling for these returned
results must be written into every application in which
modifications to the trigger table are allowed. To prevent
any results from being returned from a trigger, do not
include either SELECT statements or variable assignments
in the definition of the trigger. If variable assignment
must occur in a trigger, use a SET NOCOUNT statement at
the beginning of the trigger to eliminate the return of
any result sets."
I do assignments and selects in my triggers. I wonder if I
set nocount on in every trigger, will it have any
noticeable, positive effect.
--
Thanks,
Oskar
>--Original Message--
>Oscar
>When SET NOCOUNT ON SQL Server does not inform users
about rows were
>affected.
>It is good practice to set it up at the beginning of
stored procedures but i
>have not seen this command within triggers. It's
meaningless.
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3e7601c4a9fc$70b30650$a601280a@.phx.gbl...
>> Hi,
>> Is it true that setting nocount on in a trigger is
almost
>> a good practice? When it is not true?
>> --
>> Many thanks,
>> Oskar
>
>.
>|||Hi,Hugo
> declare @.num int
> select @.num = count(*)
What was a purpose to declare this variable and not using it?
If if does affect the perfomance (some big logic inside the trigger) so I
would not use trigger at all.
But now I have just finished read this article in the BOL and I will have
to review my triggers again.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2jb2m0dpt5k6g9vjr8c0hkmftaf2amhumd@.4ax.com...
> On Mon, 4 Oct 2004 12:41:56 +0200, Uri Dimant wrote:
> >Oscar
> >When SET NOCOUNT ON SQL Server does not inform users about rows were
> >affected.
> >It is good practice to set it up at the beginning of stored procedures
but i
> >have not seen this command within triggers. It's meaningless.
> Hi Uri,
> This is not correct. Run the following code with and wothout the set
> nocount on statement in the trigger to see the difference.
> set nocount off
> go
> create table test (a int not null)
> go
> create table test2 (now datetime not null primary key default(getdate()),
> num int not null)
> go
> create trigger testtrig on test after insert
> as
> --set nocount on
> declare @.num int
> select @.num = count(*)
> from inserted
> insert into test2(num)
> select @.num
> update test set a = a
> go
> insert test
> select 1 union all select 2
> go
> select * from test2
> go
> drop table test2, test
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 4 Oct 2004 13:35:42 +0200, Uri Dimant wrote:
>Hi,Hugo
>> declare @.num int
>> select @.num = count(*)
>What was a purpose to declare this variable and not using it?
Hi Uri,
It is used in the insert-statement following the select where it is set.
Of course, this trigger could have been written otherwise. The update
statement doesn't do anything usseful either. But I wanted a quick example
to show something and I didn't want to spend the time to make up something
more realistic.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi, Hugo
select @.num = count(*)
from inserted
insert into test2(num)
I see now , you just missed a @., :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:b7i2m0pgmvg03aus9duhbdvf65df3mlejm@.4ax.com...
> On Mon, 4 Oct 2004 13:35:42 +0200, Uri Dimant wrote:
> >Hi,Hugo
> >> declare @.num int
> >> select @.num = count(*)
> >What was a purpose to declare this variable and not using it?
> Hi Uri,
> It is used in the insert-statement following the select where it is set.
> Of course, this trigger could have been written otherwise. The update
> statement doesn't do anything usseful either. But I wanted a quick example
> to show something and I didn't want to spend the time to make up something
> more realistic.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 4 Oct 2004 15:12:01 +0200, Uri Dimant wrote:
>Hi, Hugo
>select @.num = count(*)
>from inserted
>insert into test2(num)
>I see now , you just missed a @., :)
Hi Uri,
Did I? Or did you miss a whole line?
select @.num = count(*)
from inserted
insert into test2(num)
select @.num -- Here, it is used!!!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi ,Hugo
You said
>It is used in the insert-statement following the >select where it is set.
Now you are saying
> select @.num -- Here, it is used!!!!
Or it was just time to go sleep
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rmi3m0tsge4jufmj7cukaqv72r6km8kjq0@.4ax.com...
> On Mon, 4 Oct 2004 15:12:01 +0200, Uri Dimant wrote:
> >Hi, Hugo
> >select @.num = count(*)
> >from inserted
> >insert into test2(num)
> >
> >I see now , you just missed a @., :)
> Hi Uri,
> Did I? Or did you miss a whole line?
> select @.num = count(*)
> from inserted
> insert into test2(num)
> select @.num -- Here, it is used!!!!
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Saturday, February 25, 2012
nocheck when creating a constraint
i was looking at a database creation script and i have a bunch of
constraints being created with a nocheck on them. i think i know what
the nocheck does (don't check for data issues). i'm not sure why the
original coder would define them like this since there is no data to
begin with when the tables are built. am i missing something? does
this hurt the database table?
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]That seems to be a stupid thing to do. You won't gain anything, and possibly just have a non-trusted
constraint which can lead to worse performance (non-trusted will limit some of the optimizations
that can be done).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199834.825140.114810@.y5g2000hsa.googlegroups.com...
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]
>|||Derek,
See this blog from SQL Server MVP Hugo Kornelis.
Can you trust your constraints?
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
AMB
"Derek" wrote:
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]
>
constraints being created with a nocheck on them. i think i know what
the nocheck does (don't check for data issues). i'm not sure why the
original coder would define them like this since there is no data to
begin with when the tables are built. am i missing something? does
this hurt the database table?
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]That seems to be a stupid thing to do. You won't gain anything, and possibly just have a non-trusted
constraint which can lead to worse performance (non-trusted will limit some of the optimizations
that can be done).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199834.825140.114810@.y5g2000hsa.googlegroups.com...
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]
>|||Derek,
See this blog from SQL Server MVP Hugo Kornelis.
Can you trust your constraints?
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
AMB
"Derek" wrote:
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]
>
nocheck when creating a constraint
i was looking at a database creation script and i have a bunch of
constraints being created with a nocheck on them. i think i know what
the nocheck does (don't check for data issues). i'm not sure why the
original coder would define them like this since there is no data to
begin with when the tables are built. am i missing something? does
this hurt the database table?
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMAR
Y]That seems to be a stupid thing to do. You won't gain anything, and possibly
just have a non-trusted
constraint which can lead to worse performance (non-trusted will limit some
of the optimizations
that can be done).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199834.825140.114810@.y5g2000hsa.googlegroups.com...
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [P
RIMARY]
>|||Derek,
See this blog from SQL Server MVP Hugo Kornelis.
Can you trust your constraints?
http://sqlblog.com/blogs/hugo_korne.../>
raints.aspx
AMB
"Derek" wrote:
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [
PRIMARY]
>
constraints being created with a nocheck on them. i think i know what
the nocheck does (don't check for data issues). i'm not sure why the
original coder would define them like this since there is no data to
begin with when the tables are built. am i missing something? does
this hurt the database table?
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMAR
Y]That seems to be a stupid thing to do. You won't gain anything, and possibly
just have a non-trusted
constraint which can lead to worse performance (non-trusted will limit some
of the optimizations
that can be done).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199834.825140.114810@.y5g2000hsa.googlegroups.com...
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [P
RIMARY]
>|||Derek,
See this blog from SQL Server MVP Hugo Kornelis.
Can you trust your constraints?
http://sqlblog.com/blogs/hugo_korne.../>
raints.aspx
AMB
"Derek" wrote:
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [
PRIMARY]
>
NOCASE attribute
Does someone could explain me the NOCASE attribute in the order by line?
I cant find that anywhere.
Thanks a lot,
venusgirrlhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html/dvconquerydesignerconsiderationssqlserver.asp|||I read that article, bui i couldnt understand clearly for what is the NOCASE attribute.
Thank you,
venus|||The NOCASE attribute means that Upper/Lower case are considered the same.
Consider the strings "This is a test" and "this is a test". On a server that is case sensitive, those are different. On a server that is not case sensitive (in other words sp_server_info shows NOCASE on line 18), those strings are considered to be the same.
-PatP|||Thanks you !!!!!!! :))))
venus
I cant find that anywhere.
Thanks a lot,
venusgirrlhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html/dvconquerydesignerconsiderationssqlserver.asp|||I read that article, bui i couldnt understand clearly for what is the NOCASE attribute.
Thank you,
venus|||The NOCASE attribute means that Upper/Lower case are considered the same.
Consider the strings "This is a test" and "this is a test". On a server that is case sensitive, those are different. On a server that is not case sensitive (in other words sp_server_info shows NOCASE on line 18), those strings are considered to be the same.
-PatP|||Thanks you !!!!!!! :))))
venus
NOCACHE equivalent ?
Oracle query :
create table test(sno int, sno1 int) NOCACHE
Is there any equivalent in SQL Server for the above (specifically for the NOCAHE syntax)
Please advice,
Thanks,
SamNOCACHE is the default in SQLServer.
More interesting would be the CACHE statement in a create.
In SQLServer it is not possible directly afaik (correct me if I am wrong)
You can however use DBCC PINTABLE(database_id, table_id)
Look in the documentation for the use and working.|||Hai..
If u really want a table to be placed in cache then u can make the table as pintable so that in remains in the cache itslef. Surely this will increase the performance of that particular table provided the table is small in size. If the size of the table is more, then it will require so much memory, more over it will adversely affect the performace of other tables.
So analyse ur requirement and if u really want to place the table in cache then u can use the syntax as specfied by Woosterom
with regards
Sudar
create table test(sno int, sno1 int) NOCACHE
Is there any equivalent in SQL Server for the above (specifically for the NOCAHE syntax)
Please advice,
Thanks,
SamNOCACHE is the default in SQLServer.
More interesting would be the CACHE statement in a create.
In SQLServer it is not possible directly afaik (correct me if I am wrong)
You can however use DBCC PINTABLE(database_id, table_id)
Look in the documentation for the use and working.|||Hai..
If u really want a table to be placed in cache then u can make the table as pintable so that in remains in the cache itslef. Surely this will increase the performance of that particular table provided the table is small in size. If the size of the table is more, then it will require so much memory, more over it will adversely affect the performace of other tables.
So analyse ur requirement and if u really want to place the table in cache then u can use the syntax as specfied by Woosterom
with regards
Sudar
nobody here can help
hello i sent two messge before, and nobody give me help, i think that this forum is not for repair the problem........., it is just for write the problem without solve it .
thanks anyway bye...........Wait until sometime to get answers
If you want to get quick replies, you need to post the qustions in other forums also
thanks anyway bye...........Wait until sometime to get answers
If you want to get quick replies, you need to post the qustions in other forums also
No_Log and With No_Truncate..
What is the difference b/w With No_Log and With No_Truncate..
Thanks
NOOR
On Thu, 5 Aug 2004 02:19:18 -0700, Noor wrote:
>What is the difference b/w With No_Log and With No_Truncate..
>Thanks
>NOOR
>
Hi Noor,
Have you checked Books Online?
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it
and truncates the log. This option frees space. Specifying a backup device
is unnecessary because the log backup is not saved. NO_LOG and
TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes,
immediately execute BACKUP DATABASE.
NO_TRUNCATE
Allows backing up the log in situations where the database is damaged.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Thanks
NOOR
On Thu, 5 Aug 2004 02:19:18 -0700, Noor wrote:
>What is the difference b/w With No_Log and With No_Truncate..
>Thanks
>NOOR
>
Hi Noor,
Have you checked Books Online?
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it
and truncates the log. This option frees space. Specifying a backup device
is unnecessary because the log backup is not saved. NO_LOG and
TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes,
immediately execute BACKUP DATABASE.
NO_TRUNCATE
Allows backing up the log in situations where the database is damaged.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
No. of User Accounts in SQL SERVER 2000 and SQL SERVER 2005 ..plz
I want to know the maximum number of users that can be created in SQL SERVER 2005 and the previous version...?
Yorker:
I am going to assume that you will hit the limitations of the "uid" field. This field is a SMALLINT datatype; however, it appears that groups begin @. 16384 and that this field must be non-negative. Therefore, it appears that a user database MIGHT be limited to 16384 distinct users. However, on a serverwide database you can have many databases therefore leading to at least N x 16384 distinct users. There does not look to be any "variable size" limitation on the LOGIN side because this is based now on SIDs whereas in much older versions of sql server it was formerly based on "SUID"s -- The cardinality of the number of SIDs appears to be enormous.
Would someone please check me on this?
|||The maximum number of logins in 2000 was 35,365, in 2005 it was increased to 65,000 (I think).
Dave
However, I would HIGHLY recommend using windows authentication and group membership for rights, instead of 35,000+ SQL logins.|||any evidence to prove your numbers... ? Gentleman ?|||See this: http://msdn2.microsoft.com/en-us/library/ms187376.aspx
This says in 2005 "you can create more than 32,767 users". I was slightly off with my number in 2000. I thought I read somewhere else about the 64k limit in 2005, but I might be mistaken.
I still it is a nighmare to manage 32k logins.|||From http://msdn2.microsoft.com/en-us/library/ms187376.aspx, it infers that SQL2K can only have 32767 SQL users, but SQL2K5 can have more. How much more, I don't know off-hand.
But this doesn't say how many logins there could be (although I would guess it would be the same). http://msdn2.microsoft.com/en-us/library/ms174355.aspx doesn't give many clues.
I would strongly recommend using Active Directory, where the limit doesn't actually apply in the same way. Plus, do you really want to have your DBAs managing this type of thing?
Rob|||But... it turns out that http://support.microsoft.com/default.aspx/kb/303879 quite clearly states that SQL2000 can only have 16K users.
But I guess the types used in sys.sysusers will cater for more.|||
I think that is refering to "database logins" and not "server logins".
The other one does say "This is because, in SQL Server 2005, you can create more than 32,767 users, groups, and roles, and 32,767 data types." This kind of implies, users/groups/roles use the same id counter, so combined you can't have more than 32,767.
MS added more than 16k of users for a reason, although I find it hard to believe someone requested that to be added because they ran out of logins. I have a hard enough time managing 100 SQL logins (excluding AD Groups), I can't imagine the headache I would have with more than that. :)
No. Of Subscribers in Transactional Replication...
Hi,
I am Using Transactional Replication with Updatable Subscriptions. Is there any limitations in the number of subscribers (Servers) used for this type of replication?
I have a scenario of configuring this replication in 60 Subscribers(Servers). And the replication should be in the Continuous running mode. Will Transactional Replication with Updatable Subscriptions work in this scenario? Or is it meant to work for less than 10 subscribers?
Please reply asap.
Thanks in advance.
Regards,
Swapna.B.
Hi Swapna,
This question has been answered in the following posting.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=754018&SiteID=1
60 subscribers should be fine for transaction replication. However, you should go over the "Considerations for Using Updatable Subscriptions" in following article.
http://msdn2.microsoft.com/en-us/library/ms151718.aspx
Gary
|||Hi Gary,
Thanks a lot.
Regards,
Swapna.B.
No. of records returned by a query.
Dear all
i want to run a query on sql server based on whether the no. of records returned by another query is 0 or >0. what will be the sql code for this.Assume your SELECT statement is called Q. Than you can write
if (select count(*) from (Q) q)>0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END|||...or, if you have already run your select statement as part of your procedure, use this so you don't have to execute it a second time:
if @.@.ROWCOUNT > 0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END
@.@.ROWCOUNT stores the number of records returned by the last executed statement. If you don't use the value immediately, you will need to store it in a variable because it will change with your next statement.
blindman
i want to run a query on sql server based on whether the no. of records returned by another query is 0 or >0. what will be the sql code for this.Assume your SELECT statement is called Q. Than you can write
if (select count(*) from (Q) q)>0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END|||...or, if you have already run your select statement as part of your procedure, use this so you don't have to execute it a second time:
if @.@.ROWCOUNT > 0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END
@.@.ROWCOUNT stores the number of records returned by the last executed statement. If you don't use the value immediately, you will need to store it in a variable because it will change with your next statement.
blindman
No. Of Records inserted into a table/database
How do I findout number of records inserted/sec into a table or database.
What is the version of SQL Server?
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
> How do I findout number of records inserted/sec into a table or database.
|||I want to findout this information both in SQL Server 2000 and SQL Server 2005
"Uri Dimant" wrote:
> What is the version of SQL Server?
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
>
>
|||If you want only INSERTED rows, you can create in both versions trigger on
table for INSERT and calculate them.
In SQL Server 2005 take a look at an OUTPUT clause
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:6FBE1B30-CC16-4BCB-9C6C-4E6D3852BC42@.microsoft.com...[vbcol=seagreen]
>I want to findout this information both in SQL Server 2000 and SQL Server
>2005
> "Uri Dimant" wrote:
|||In almost all cases, you have to clock it yourself.
Linchi
"Balaji" wrote:
> How do I findout number of records inserted/sec into a table or database.
What is the version of SQL Server?
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
> How do I findout number of records inserted/sec into a table or database.
|||I want to findout this information both in SQL Server 2000 and SQL Server 2005
"Uri Dimant" wrote:
> What is the version of SQL Server?
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
>
>
|||If you want only INSERTED rows, you can create in both versions trigger on
table for INSERT and calculate them.
In SQL Server 2005 take a look at an OUTPUT clause
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:6FBE1B30-CC16-4BCB-9C6C-4E6D3852BC42@.microsoft.com...[vbcol=seagreen]
>I want to findout this information both in SQL Server 2000 and SQL Server
>2005
> "Uri Dimant" wrote:
|||In almost all cases, you have to clock it yourself.
Linchi
"Balaji" wrote:
> How do I findout number of records inserted/sec into a table or database.
No. Of Records inserted into a table/database
How do I findout number of records inserted/sec into a table or database.What is the version of SQL Server?
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
> How do I findout number of records inserted/sec into a table or database.|||I want to findout this information both in SQL Server 2000 and SQL Server 20
05
"Uri Dimant" wrote:
> What is the version of SQL Server?
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
>
>|||If you want only INSERTED rows, you can create in both versions trigger on
table for INSERT and calculate them.
In SQL Server 2005 take a look at an OUTPUT clause
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:6FBE1B30-CC16-4BCB-9C6C-4E6D3852BC42@.microsoft.com...[vbcol=seagreen]
>I want to findout this information both in SQL Server 2000 and SQL Server
>2005
> "Uri Dimant" wrote:
>|||In almost all cases, you have to clock it yourself.
Linchi
"Balaji" wrote:
> How do I findout number of records inserted/sec into a table or database.
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
> How do I findout number of records inserted/sec into a table or database.|||I want to findout this information both in SQL Server 2000 and SQL Server 20
05
"Uri Dimant" wrote:
> What is the version of SQL Server?
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
>
>|||If you want only INSERTED rows, you can create in both versions trigger on
table for INSERT and calculate them.
In SQL Server 2005 take a look at an OUTPUT clause
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:6FBE1B30-CC16-4BCB-9C6C-4E6D3852BC42@.microsoft.com...[vbcol=seagreen]
>I want to findout this information both in SQL Server 2000 and SQL Server
>2005
> "Uri Dimant" wrote:
>|||In almost all cases, you have to clock it yourself.
Linchi
"Balaji" wrote:
> How do I findout number of records inserted/sec into a table or database.
No. Of Records inserted into a table/database
How do I findout number of records inserted/sec into a table or database.What is the version of SQL Server?
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
> How do I findout number of records inserted/sec into a table or database.|||I want to findout this information both in SQL Server 2000 and SQL Server 2005
"Uri Dimant" wrote:
> What is the version of SQL Server?
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
> > How do I findout number of records inserted/sec into a table or database.
>
>|||If you want only INSERTED rows, you can create in both versions trigger on
table for INSERT and calculate them.
In SQL Server 2005 take a look at an OUTPUT clause
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:6FBE1B30-CC16-4BCB-9C6C-4E6D3852BC42@.microsoft.com...
>I want to findout this information both in SQL Server 2000 and SQL Server
>2005
> "Uri Dimant" wrote:
>> What is the version of SQL Server?
>> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
>> news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
>> > How do I findout number of records inserted/sec into a table or
>> > database.
>>|||In almost all cases, you have to clock it yourself.
Linchi
"Balaji" wrote:
> How do I findout number of records inserted/sec into a table or database.
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
> How do I findout number of records inserted/sec into a table or database.|||I want to findout this information both in SQL Server 2000 and SQL Server 2005
"Uri Dimant" wrote:
> What is the version of SQL Server?
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
> > How do I findout number of records inserted/sec into a table or database.
>
>|||If you want only INSERTED rows, you can create in both versions trigger on
table for INSERT and calculate them.
In SQL Server 2005 take a look at an OUTPUT clause
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:6FBE1B30-CC16-4BCB-9C6C-4E6D3852BC42@.microsoft.com...
>I want to findout this information both in SQL Server 2000 and SQL Server
>2005
> "Uri Dimant" wrote:
>> What is the version of SQL Server?
>> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
>> news:406D4E62-A0D3-4CB9-9585-127A5A61D67B@.microsoft.com...
>> > How do I findout number of records inserted/sec into a table or
>> > database.
>>|||In almost all cases, you have to clock it yourself.
Linchi
"Balaji" wrote:
> How do I findout number of records inserted/sec into a table or database.
No. of Distribution Database
How many distribution databases can we have ? ... Say I have one publisher
/Published DB and multiple subscribers.
Can I have as many distribution dbs as the no. of subscribing dbs or just
one per published db or just one per publisher ? Can someone get my basics
straight ?
Each publisher can have its own distribution database, but only one.
You can have multiple distribution databases per distribution server with
one or more publisher's using each.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OHj4cDtFFHA.3732@.tk2msftngp13.phx.gbl...
> How many distribution databases can we have ? ... Say I have one publisher
> /Published DB and multiple subscribers.
> Can I have as many distribution dbs as the no. of subscribing dbs or just
> one per published db or just one per publisher ? Can someone get my basics
> straight ?
>
|||So even if the publisher has 4 published dbs, they all write to just one
distribution database.Right ?
Also how easy is to move the distribution db from a local server to a remote
server ?
Also what about once we do move to a remote server and say have multiple
distribution dbs, how easy is it to modify the properties ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX7r73tFFHA.1740@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Each publisher can have its own distribution database, but only one.
> You can have multiple distribution databases per distribution server with
> one or more publisher's using each.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OHj4cDtFFHA.3732@.tk2msftngp13.phx.gbl...
publisher[vbcol=seagreen]
just[vbcol=seagreen]
basics
>
|||Also curious to know if multiple publishers can use the same distribution db
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX7r73tFFHA.1740@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Each publisher can have its own distribution database, but only one.
> You can have multiple distribution databases per distribution server with
> one or more publisher's using each.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OHj4cDtFFHA.3732@.tk2msftngp13.phx.gbl...
publisher[vbcol=seagreen]
just[vbcol=seagreen]
basics
>
|||That is correct - all publications from one publisher will use the same
distribution db.
It is simple to move the distribution db from a local server to a remote
one. All you do is drop all publications and subscriptions, disable
replication, reinstall replication and configure it for the remote
distributor, and then recreate all of your publications and subscriptions.
Modifying the properties of a distribution database is simple. It is best
done on the distributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uOtuzHuFFHA.3648@.TK2MSFTNGP10.phx.gbl...
> So even if the publisher has 4 published dbs, they all write to just one
> distribution database.Right ?
> Also how easy is to move the distribution db from a local server to a
remote[vbcol=seagreen]
> server ?
> Also what about once we do move to a remote server and say have multiple
> distribution dbs, how easy is it to modify the properties ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OX7r73tFFHA.1740@.TK2MSFTNGP09.phx.gbl...
with
> publisher
> just
> basics
>
|||Multiple publishers by default will use the same distribution database,
unless you select another distribution database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:esvEUJuFFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Also curious to know if multiple publishers can use the same distribution
db[vbcol=seagreen]
> ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OX7r73tFFHA.1740@.TK2MSFTNGP09.phx.gbl...
with
> publisher
> just
> basics
>
/Published DB and multiple subscribers.
Can I have as many distribution dbs as the no. of subscribing dbs or just
one per published db or just one per publisher ? Can someone get my basics
straight ?
Each publisher can have its own distribution database, but only one.
You can have multiple distribution databases per distribution server with
one or more publisher's using each.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OHj4cDtFFHA.3732@.tk2msftngp13.phx.gbl...
> How many distribution databases can we have ? ... Say I have one publisher
> /Published DB and multiple subscribers.
> Can I have as many distribution dbs as the no. of subscribing dbs or just
> one per published db or just one per publisher ? Can someone get my basics
> straight ?
>
|||So even if the publisher has 4 published dbs, they all write to just one
distribution database.Right ?
Also how easy is to move the distribution db from a local server to a remote
server ?
Also what about once we do move to a remote server and say have multiple
distribution dbs, how easy is it to modify the properties ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX7r73tFFHA.1740@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Each publisher can have its own distribution database, but only one.
> You can have multiple distribution databases per distribution server with
> one or more publisher's using each.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OHj4cDtFFHA.3732@.tk2msftngp13.phx.gbl...
publisher[vbcol=seagreen]
just[vbcol=seagreen]
basics
>
|||Also curious to know if multiple publishers can use the same distribution db
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX7r73tFFHA.1740@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Each publisher can have its own distribution database, but only one.
> You can have multiple distribution databases per distribution server with
> one or more publisher's using each.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OHj4cDtFFHA.3732@.tk2msftngp13.phx.gbl...
publisher[vbcol=seagreen]
just[vbcol=seagreen]
basics
>
|||That is correct - all publications from one publisher will use the same
distribution db.
It is simple to move the distribution db from a local server to a remote
one. All you do is drop all publications and subscriptions, disable
replication, reinstall replication and configure it for the remote
distributor, and then recreate all of your publications and subscriptions.
Modifying the properties of a distribution database is simple. It is best
done on the distributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uOtuzHuFFHA.3648@.TK2MSFTNGP10.phx.gbl...
> So even if the publisher has 4 published dbs, they all write to just one
> distribution database.Right ?
> Also how easy is to move the distribution db from a local server to a
remote[vbcol=seagreen]
> server ?
> Also what about once we do move to a remote server and say have multiple
> distribution dbs, how easy is it to modify the properties ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OX7r73tFFHA.1740@.TK2MSFTNGP09.phx.gbl...
with
> publisher
> just
> basics
>
|||Multiple publishers by default will use the same distribution database,
unless you select another distribution database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:esvEUJuFFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Also curious to know if multiple publishers can use the same distribution
db[vbcol=seagreen]
> ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OX7r73tFFHA.1740@.TK2MSFTNGP09.phx.gbl...
with
> publisher
> just
> basics
>
No. of connections available for Per Seat License
We have a MSSQL with per seat license of 20. The MSSQL is sitting in the same machine as the asp.net. Does that mean that the the no. of connections and Max Pool Size are limited up to 20 only?
Thanks,BenI don't know that any of us here are going to be willing to venture a guess on the often confusing legalities of SQL Server licensing. I can recommend this link as a starting point for your research:SQL Server 2000 Pricing and Licensing. Be sure to look at theSQL2KLic.doc in the upper righthand corner.
No XML export
...Newbie alert...
I am picking up a project that makes significant use of SQL2005 reporting
services. The app, as it is currently, triggers the report by launching a new
window with a URL pointing at the report server and including the parameters
rs:Command=Render&rs:clearsession=true&rs:format=HTML4.0
The new window shows the "Report is being generated" message and eventually
it is displayed. I want to get the XML version of the report, but the EXPORT
drop down only offers Excel and PDF.
This seems to be consistent with a .net report viewer control in "local
mode" (whatever that is), but I don't think that this is what's going on.
If I go to the reportserver virtual directory, there is a
ReportServer.config file. It contains, in part the following:
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
under <Delivery> for various <Extension Name="..."> tags
It also shows
<Render>
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering"/>
...
So it doesn't seem to actively exclude xml rendering and it does seem to
provide a renderer for the file type.
I've done this using the client's service provider and also on my own
out-of-the-box SQL2005 installation and have the same result on each.
I'm obviously missing something silly, but I can't figure out what it is.
Thx
MarcOn Sep 17, 3:08 pm, MarcG <JoePt...@.newsgroup.nospam> wrote:
> ...Newbie alert...
> I am picking up a project that makes significant use of SQL2005 reporting
> services. The app, as it is currently, triggers the report by launching a new
> window with a URL pointing at the report server and including the parameters
> rs:Command=Render&rs:clearsession=true&rs:format=HTML4.0
> The new window shows the "Report is being generated" message and eventually
> it is displayed. I want to get the XML version of the report, but the EXPORT
> drop down only offers Excel and PDF.
> This seems to be consistent with a .net report viewer control in "local
> mode" (whatever that is), but I don't think that this is what's going on.
> If I go to the reportserver virtual directory, there is a
> ReportServer.config file. It contains, in part the following:
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> <RenderingExtension>RGDI</RenderingExtension>
> </ExcludedRenderFormats>
> under <Delivery> for various <Extension Name="..."> tags
> It also shows
> <Render>
> <Extension Name="XML"
> Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering"/>
> ...
> So it doesn't seem to actively exclude xml rendering and it does seem to
> provide a renderer for the file type.
> I've done this using the client's service provider and also on my own
> out-of-the-box SQL2005 installation and have the same result on each.
> I'm obviously missing something silly, but I can't figure out what it is.
> Thx
> Marc
If you are using SQL Server 2005 Express Edition w/Advanced Services,
according to MS's documentation, you will not be able to export to XML
(refer to: http://technet.microsoft.com/en-us/library/ms157153.aspx );
however, I'm using that version w/SP2 and can export to Tiff and XML.
If you don't have SP2 installed, you may want to do that. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
I'm not sure how to find the equivalent of "Help|About" to get the version
info, but the SQL Server Configuration Manager at the hosted site shows
Version-9.2.3042.00 and File Version 2005.090.3054.00. I can't find the
equivalent information for the Report Server.
Marc|||Actually, I didn't report this properly...
Both the hosting service and my test machine have near identical
reReportServer.config files.
They differ only in <dsn>, IInstallationID>,<UrlRoot>, and
WebServiceAccount> tags.
On the hosting service, Export offers only Excel and PDF. On my test host, I
get Excel, PDF, XML, csv, TIFF, and Web Archive, as expected.
So it appears that the answer is not in the config file. Is there a way that
I can confirm that the required renderers are installed on the hosting
service?
Thx|||Hello Joe,
I would like to know this issue more clearly.
Do you mean that you want to develop a windows form application to access
the Report via web access and you want to Export it via XML?
If so, please let me know whether you could Export the report to XML
directly in Report Server.
The Render Extension for the Reporting Services is specified in the
ReportServer.config file
Also, you could try to use the command parameter like this to export the
report to XML directly.
rs:Command=Render&rs:clearsession=true&rs:format=XML
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I want to have the report viewer that is used by default in IE. I DO NOT
want to use the .NET report viewer control. I want to leave the initial
display in HTML for viewing but allow for exporting and saving in XML. As I
said in the previous post, the rsreportserver.config files are effectively
identical on the two machines and I get the XML export optioin on my local XP
system but not on the hosted one.
I am a little confused about the installation that I am seeing on the
hosting service. Using the IIS Manager, I see the following...
Local Computer
Application Pools
AppPoolforRS
RManager (a "gear" icon, path is <ICCS>/RManager)
RServer (a "gear" icon, path is <ICCS>/RServer)
DefaultAppPool
Default Application (empty)
Default Application (empty)
Reports (empty)
ReportServer (empty)
MSSharePointAppPool
Root (empty)
Default Application (empty)
Web Sites
Default Web Site (stopped)
Microsoft SHarePoint Administration
ICCS
Reports ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportManager)
ReportServer ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportServer)
RManager ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportManager)
RServer ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportServer)
In the ReportServer directory, I have (with other files),
rsreportserver.config
rsreportserversp2update.config
rssrvrpolicy.config
web.config
These files match (by name) the files I have in the same directory on my
local WinXP test machine. There is no file named "reportserver.config" on
either machine.
By way of an experiment, on the hosted system I made copies of
rsreportserver.config and rsreportserversp2update.config and renamed them
reportserver.config and reportserversp2update.config. I then stopped and
restarted the ICCS web site and stopped and restarted the AppPoolforRS. This
made no difference.|||Hell Joe,
Sorry for mistyping. It should be rsreportserver.config.
The <Render> part in my side is like this:
<Render>
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Mi
crosoft.ReportingServices.XmlRendering"/>
<Extension Name="NULL"
Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsof
t.ReportingServices.NullRendering" Visible="false"/>
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.
ReportingServices.CsvRendering"/>
<Extension Name="IMAGE"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Micros
oft.ReportingServices.ImageRendering"/>
<Extension Name="PDF"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsof
t.ReportingServices.ImageRendering"/>
<Extension Name="RGDI"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RemoteGdiReport,Mi
crosoft.ReportingServices.ImageRendering" Visible="false"
LogAllExecutionRequests="false"/>
<Extension Name="HTML4.0"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExte
nsion,Microsoft.ReportingServices.HtmlRendering" Visible="false"
LogAllExecutionRequests="false"/>
<Extension Name="HTML3.2"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html32RenderingExte
nsion,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
<Extension Name="MHTML"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExten
sion,Microsoft.ReportingServices.HtmlRendering"/>
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering"/>
</Render>
You need to check whether you have this
Microsoft.ReportingServices.XmlRendering.dll under the folder:
C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportServer\bin
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
Unfortunately, my config matches what you sent me exactly, and
Microsoft.ReportingServices.XmlRendering.dll is in C:\Program Files\Microsoft
SQL Server\MSSQL.2\Reporting Services\ReportServer\bin
File version is 9.0.3042.0
'
Marc|||Hello Marc,
I would like to get the screenshot of your issue.
Also, have you applied the latest service pack of SQL 2005?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
I'm not sure what you want for a screen shot. I am looking at an IE5 browser
window with the HTML version of the report displayed. There is a drop down
box that says "Select a Format" and when you click it, it offers only
"Acrobat (PDF) File", and "Excel" If you send me an email address, I'll send
you the screen shot.
As noted earlier, I don't know how to ask SQL2005 what its SP level is. I
provided you with the file version numbers for the relevant DLL's
Marc|||Hello Marc,
You may send the email to me. Please remove the ONLINE in my display email
address. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
I am picking up a project that makes significant use of SQL2005 reporting
services. The app, as it is currently, triggers the report by launching a new
window with a URL pointing at the report server and including the parameters
rs:Command=Render&rs:clearsession=true&rs:format=HTML4.0
The new window shows the "Report is being generated" message and eventually
it is displayed. I want to get the XML version of the report, but the EXPORT
drop down only offers Excel and PDF.
This seems to be consistent with a .net report viewer control in "local
mode" (whatever that is), but I don't think that this is what's going on.
If I go to the reportserver virtual directory, there is a
ReportServer.config file. It contains, in part the following:
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
under <Delivery> for various <Extension Name="..."> tags
It also shows
<Render>
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering"/>
...
So it doesn't seem to actively exclude xml rendering and it does seem to
provide a renderer for the file type.
I've done this using the client's service provider and also on my own
out-of-the-box SQL2005 installation and have the same result on each.
I'm obviously missing something silly, but I can't figure out what it is.
Thx
MarcOn Sep 17, 3:08 pm, MarcG <JoePt...@.newsgroup.nospam> wrote:
> ...Newbie alert...
> I am picking up a project that makes significant use of SQL2005 reporting
> services. The app, as it is currently, triggers the report by launching a new
> window with a URL pointing at the report server and including the parameters
> rs:Command=Render&rs:clearsession=true&rs:format=HTML4.0
> The new window shows the "Report is being generated" message and eventually
> it is displayed. I want to get the XML version of the report, but the EXPORT
> drop down only offers Excel and PDF.
> This seems to be consistent with a .net report viewer control in "local
> mode" (whatever that is), but I don't think that this is what's going on.
> If I go to the reportserver virtual directory, there is a
> ReportServer.config file. It contains, in part the following:
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> <RenderingExtension>RGDI</RenderingExtension>
> </ExcludedRenderFormats>
> under <Delivery> for various <Extension Name="..."> tags
> It also shows
> <Render>
> <Extension Name="XML"
> Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering"/>
> ...
> So it doesn't seem to actively exclude xml rendering and it does seem to
> provide a renderer for the file type.
> I've done this using the client's service provider and also on my own
> out-of-the-box SQL2005 installation and have the same result on each.
> I'm obviously missing something silly, but I can't figure out what it is.
> Thx
> Marc
If you are using SQL Server 2005 Express Edition w/Advanced Services,
according to MS's documentation, you will not be able to export to XML
(refer to: http://technet.microsoft.com/en-us/library/ms157153.aspx );
however, I'm using that version w/SP2 and can export to Tiff and XML.
If you don't have SP2 installed, you may want to do that. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
I'm not sure how to find the equivalent of "Help|About" to get the version
info, but the SQL Server Configuration Manager at the hosted site shows
Version-9.2.3042.00 and File Version 2005.090.3054.00. I can't find the
equivalent information for the Report Server.
Marc|||Actually, I didn't report this properly...
Both the hosting service and my test machine have near identical
reReportServer.config files.
They differ only in <dsn>, IInstallationID>,<UrlRoot>, and
WebServiceAccount> tags.
On the hosting service, Export offers only Excel and PDF. On my test host, I
get Excel, PDF, XML, csv, TIFF, and Web Archive, as expected.
So it appears that the answer is not in the config file. Is there a way that
I can confirm that the required renderers are installed on the hosting
service?
Thx|||Hello Joe,
I would like to know this issue more clearly.
Do you mean that you want to develop a windows form application to access
the Report via web access and you want to Export it via XML?
If so, please let me know whether you could Export the report to XML
directly in Report Server.
The Render Extension for the Reporting Services is specified in the
ReportServer.config file
Also, you could try to use the command parameter like this to export the
report to XML directly.
rs:Command=Render&rs:clearsession=true&rs:format=XML
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I want to have the report viewer that is used by default in IE. I DO NOT
want to use the .NET report viewer control. I want to leave the initial
display in HTML for viewing but allow for exporting and saving in XML. As I
said in the previous post, the rsreportserver.config files are effectively
identical on the two machines and I get the XML export optioin on my local XP
system but not on the hosted one.
I am a little confused about the installation that I am seeing on the
hosting service. Using the IIS Manager, I see the following...
Local Computer
Application Pools
AppPoolforRS
RManager (a "gear" icon, path is <ICCS>/RManager)
RServer (a "gear" icon, path is <ICCS>/RServer)
DefaultAppPool
Default Application (empty)
Default Application (empty)
Reports (empty)
ReportServer (empty)
MSSharePointAppPool
Root (empty)
Default Application (empty)
Web Sites
Default Web Site (stopped)
Microsoft SHarePoint Administration
ICCS
Reports ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportManager)
ReportServer ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportServer)
RManager ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportManager)
RServer ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportServer)
In the ReportServer directory, I have (with other files),
rsreportserver.config
rsreportserversp2update.config
rssrvrpolicy.config
web.config
These files match (by name) the files I have in the same directory on my
local WinXP test machine. There is no file named "reportserver.config" on
either machine.
By way of an experiment, on the hosted system I made copies of
rsreportserver.config and rsreportserversp2update.config and renamed them
reportserver.config and reportserversp2update.config. I then stopped and
restarted the ICCS web site and stopped and restarted the AppPoolforRS. This
made no difference.|||Hell Joe,
Sorry for mistyping. It should be rsreportserver.config.
The <Render> part in my side is like this:
<Render>
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Mi
crosoft.ReportingServices.XmlRendering"/>
<Extension Name="NULL"
Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsof
t.ReportingServices.NullRendering" Visible="false"/>
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.
ReportingServices.CsvRendering"/>
<Extension Name="IMAGE"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Micros
oft.ReportingServices.ImageRendering"/>
<Extension Name="PDF"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsof
t.ReportingServices.ImageRendering"/>
<Extension Name="RGDI"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RemoteGdiReport,Mi
crosoft.ReportingServices.ImageRendering" Visible="false"
LogAllExecutionRequests="false"/>
<Extension Name="HTML4.0"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExte
nsion,Microsoft.ReportingServices.HtmlRendering" Visible="false"
LogAllExecutionRequests="false"/>
<Extension Name="HTML3.2"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html32RenderingExte
nsion,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
<Extension Name="MHTML"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExten
sion,Microsoft.ReportingServices.HtmlRendering"/>
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering"/>
</Render>
You need to check whether you have this
Microsoft.ReportingServices.XmlRendering.dll under the folder:
C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportServer\bin
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
Unfortunately, my config matches what you sent me exactly, and
Microsoft.ReportingServices.XmlRendering.dll is in C:\Program Files\Microsoft
SQL Server\MSSQL.2\Reporting Services\ReportServer\bin
File version is 9.0.3042.0
'
Marc|||Hello Marc,
I would like to get the screenshot of your issue.
Also, have you applied the latest service pack of SQL 2005?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
I'm not sure what you want for a screen shot. I am looking at an IE5 browser
window with the HTML version of the report displayed. There is a drop down
box that says "Select a Format" and when you click it, it offers only
"Acrobat (PDF) File", and "Excel" If you send me an email address, I'll send
you the screen shot.
As noted earlier, I don't know how to ask SQL2005 what its SP level is. I
provided you with the file version numbers for the relevant DLL's
Marc|||Hello Marc,
You may send the email to me. Please remove the ONLINE in my display email
address. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
No wrap fields?
Hello there..
Is there any way to make fields grow in width, and not to wrap the text?
As it is now, you have to specify a width that will suite the data in the field. That takes some time that should be easy to automate?
//P?r
No, textboxes are allowed to expand or contract automatically only in the vertical direction (height), not horizontal (width) by using CanGrow and CanShrink property respectively.
It may be because the width of the report will be affected if width of one or more fields in it is changed. This will lead to extra pages when rendered in some formats where the extra pages will have the spilled over data from the previous page. But extra pages caused due to increase in the height of the fields is acceptable and viewable from the users point of view.
Shyam
no working drilldown in reportViewer, ASP.NET
I have Report A and report B.
Report A has under "navigation->Jump To Report", report B selected from the
list. I also used the parameters button to insert both parameters required
by Report B.
Deploy both reports.
On the catalog (server/reports/<folder>/A), I run the report A and can drill
to report B.
PROBLEM:
When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
reportViewer (report viewer) in my asp.net page to show the report. When I
drill down on report A, the frame of the report seems to be reloading (get
the green thing) but then the same reports appears!
Additional info: I tried adding to my asp.net page code a handler for the
ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
function. But when I put a breakpoint there, it seems I don't even get
there. In any case, all the samples I saw use this function for windows
forms, and then they set the datasource which seems like something I
shouldn't be bothered with.
anyone can help?
regards.I am facing the same problem, if you got the solution pls let me know. Thanx
in advance.
Regards
"csmba" wrote:
> I have Report A and report B.
> Report A has under "navigation->Jump To Report", report B selected from the
> list. I also used the parameters button to insert both parameters required
> by Report B.
> Deploy both reports.
> On the catalog (server/reports/<folder>/A), I run the report A and can drill
> to report B.
> PROBLEM:
> When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
> reportViewer (report viewer) in my asp.net page to show the report. When I
> drill down on report A, the frame of the report seems to be reloading (get
> the green thing) but then the same reports appears!
> Additional info: I tried adding to my asp.net page code a handler for the
> ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
> function. But when I put a breakpoint there, it seems I don't even get
> there. In any case, all the samples I saw use this function for windows
> forms, and then they set the datasource which seems like something I
> shouldn't be bothered with.
> anyone can help?
> regards.
>
>|||We finally got the solution.
Pls check whether the report A is called only once, use postback.
"csmba" wrote:
> I have Report A and report B.
> Report A has under "navigation->Jump To Report", report B selected from the
> list. I also used the parameters button to insert both parameters required
> by Report B.
> Deploy both reports.
> On the catalog (server/reports/<folder>/A), I run the report A and can drill
> to report B.
> PROBLEM:
> When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
> reportViewer (report viewer) in my asp.net page to show the report. When I
> drill down on report A, the frame of the report seems to be reloading (get
> the green thing) but then the same reports appears!
> Additional info: I tried adding to my asp.net page code a handler for the
> ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
> function. But when I put a breakpoint there, it seems I don't even get
> there. In any case, all the samples I saw use this function for windows
> forms, and then they set the datasource which seems like something I
> shouldn't be bothered with.
> anyone can help?
> regards.
>
>
Report A has under "navigation->Jump To Report", report B selected from the
list. I also used the parameters button to insert both parameters required
by Report B.
Deploy both reports.
On the catalog (server/reports/<folder>/A), I run the report A and can drill
to report B.
PROBLEM:
When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
reportViewer (report viewer) in my asp.net page to show the report. When I
drill down on report A, the frame of the report seems to be reloading (get
the green thing) but then the same reports appears!
Additional info: I tried adding to my asp.net page code a handler for the
ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
function. But when I put a breakpoint there, it seems I don't even get
there. In any case, all the samples I saw use this function for windows
forms, and then they set the datasource which seems like something I
shouldn't be bothered with.
anyone can help?
regards.I am facing the same problem, if you got the solution pls let me know. Thanx
in advance.
Regards
"csmba" wrote:
> I have Report A and report B.
> Report A has under "navigation->Jump To Report", report B selected from the
> list. I also used the parameters button to insert both parameters required
> by Report B.
> Deploy both reports.
> On the catalog (server/reports/<folder>/A), I run the report A and can drill
> to report B.
> PROBLEM:
> When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
> reportViewer (report viewer) in my asp.net page to show the report. When I
> drill down on report A, the frame of the report seems to be reloading (get
> the green thing) but then the same reports appears!
> Additional info: I tried adding to my asp.net page code a handler for the
> ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
> function. But when I put a breakpoint there, it seems I don't even get
> there. In any case, all the samples I saw use this function for windows
> forms, and then they set the datasource which seems like something I
> shouldn't be bothered with.
> anyone can help?
> regards.
>
>|||We finally got the solution.
Pls check whether the report A is called only once, use postback.
"csmba" wrote:
> I have Report A and report B.
> Report A has under "navigation->Jump To Report", report B selected from the
> list. I also used the parameters button to insert both parameters required
> by Report B.
> Deploy both reports.
> On the catalog (server/reports/<folder>/A), I run the report A and can drill
> to report B.
> PROBLEM:
> When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
> reportViewer (report viewer) in my asp.net page to show the report. When I
> drill down on report A, the frame of the report seems to be reloading (get
> the green thing) but then the same reports appears!
> Additional info: I tried adding to my asp.net page code a handler for the
> ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
> function. But when I put a breakpoint there, it seems I don't even get
> there. In any case, all the samples I saw use this function for windows
> forms, and then they set the datasource which seems like something I
> shouldn't be bothered with.
> anyone can help?
> regards.
>
>
No workaround for interactive htmlfragments at all?
Hi!
I've been really happy during the week-end as I finally found a way to do
long wanted things with RS 2000. Yesterday at 22 pm, I found out that
interactive HTML fragments isn't possible, after MANY hours of developing..
Yeah, go on.. call it bad planning. :(
Anyway, would it be possible to create a disassembly of the HTML4.0 renderer
using reflector and then modifying the code to fit my project? (Links and
toggles..)
Is it legal? Has anyone done this allready and published the code? Are there
any other possibilities other than URL access you guys would recommend?
Part of the reason I need fragments is that the security is based on a
hierarchy in a self-referencing sql table. I need to use a business/security
layer to create parameters for the reports which will be generated by a
web-service on a protected server. No way to limit the results with NT or
sql server security. (well, maybe sql server, but I have to re-engineer)
Hope for good help! :)
Lars-ErikCould you elaborate what exactly you want and what problem you are having?
"Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
news:uMBw6fLnFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I've been really happy during the week-end as I finally found a way to do
> long wanted things with RS 2000. Yesterday at 22 pm, I found out that
> interactive HTML fragments isn't possible, after MANY hours of
> developing.. Yeah, go on.. call it bad planning. :(
> Anyway, would it be possible to create a disassembly of the HTML4.0
> renderer using reflector and then modifying the code to fit my project?
> (Links and toggles..)
> Is it legal? Has anyone done this allready and published the code? Are
> there any other possibilities other than URL access you guys would
> recommend?
> Part of the reason I need fragments is that the security is based on a
> hierarchy in a self-referencing sql table. I need to use a
> business/security layer to create parameters for the reports which will be
> generated by a web-service on a protected server. No way to limit the
> results with NT or sql server security. (well, maybe sql server, but I
> have to re-engineer)
> Hope for good help! :)
> Lars-Erik
>|||Hi again!
I'm trying to create an asp.net form that uses some business logic to create
parameters for a report, then uses the web-service to get the html for the
report. The parameters should not be appended to a user visible/editable url
since that will compromise security. The WebService will be running on a
secure server, and the webservice proxy will impersonate an authorized
account. The report is built with recursive detail groups and a visibility
toggle.
This means I have to pick one or the other option. If I don't want to expose
the parameters, I have to use HTML fragments, else RS will append then to
all links, including targetting each link to the report server "url access"
page. I can create a report that has everything expanded and discard the
toggling functionality, but that'll mean pretty long reports.
Hope this helps explaining.
L-E
"Smith" <smithj@.online.nospam> wrote in message
news:ebqOKhMnFHA.320@.TK2MSFTNGP09.phx.gbl...
> Could you elaborate what exactly you want and what problem you are having?
> "Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
> news:uMBw6fLnFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> Hi!
>> I've been really happy during the week-end as I finally found a way to do
>> long wanted things with RS 2000. Yesterday at 22 pm, I found out that
>> interactive HTML fragments isn't possible, after MANY hours of
>> developing.. Yeah, go on.. call it bad planning. :(
>> Anyway, would it be possible to create a disassembly of the HTML4.0
>> renderer using reflector and then modifying the code to fit my project?
>> (Links and toggles..)
>> Is it legal? Has anyone done this allready and published the code? Are
>> there any other possibilities other than URL access you guys would
>> recommend?
>> Part of the reason I need fragments is that the security is based on a
>> hierarchy in a self-referencing sql table. I need to use a
>> business/security layer to create parameters for the reports which will
>> be generated by a web-service on a protected server. No way to limit the
>> results with NT or sql server security. (well, maybe sql server, but I
>> have to re-engineer)
>> Hope for good help! :)
>> Lars-Erik
>|||Hi,
It's true that you will loose the interactive capabilities using HTML
fragments. An alternative would be using the ReportViewer control which
will not expose the parameters in address bar, but the parameters would
still be seen when you right-click the page and select 'View Source'.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi!
Thank you for answering. Do you have any views on my actual question?
> Anyway, would it be possible to create a disassembly of the HTML4.0
> renderer
> using reflector and then modifying the code to fit my project? (Links and
> toggles..)
> Is it legal? Has anyone done this allready and published the code? Are
> there
> any other possibilities other than URL access you guys would recommend?
Lars-Erik
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:qdHZ%23OmnFHA.940@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> It's true that you will loose the interactive capabilities using HTML
> fragments. An alternative would be using the ReportViewer control which
> will not expose the parameters in address bar, but the parameters would
> still be seen when you right-click the page and select 'View Source'.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||To the best of my knowledge, reverse-engineering MS code is illegal.
Actually it's not recommended to pass sensitive info as part of a report
parameter to begin with. Tudor's blog entry has addressed this issue:
http://blogs.msdn.com/tudortr/archive/2004/07/20/189398.aspx
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you! This was pretty helpful. :)
I'll consider running Reporting Services on the front-end server using a
custom authentication module.
Lars-Erik
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:rhnK2AloFHA.588@.TK2MSFTNGXA01.phx.gbl...
> To the best of my knowledge, reverse-engineering MS code is illegal.
> Actually it's not recommended to pass sensitive info as part of a report
> parameter to begin with. Tudor's blog entry has addressed this issue:
> http://blogs.msdn.com/tudortr/archive/2004/07/20/189398.aspx
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Lars,
I'm glad to hear the information provided is helpful. Please let me know if
you have any other concerns, or need anything else.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
I've been really happy during the week-end as I finally found a way to do
long wanted things with RS 2000. Yesterday at 22 pm, I found out that
interactive HTML fragments isn't possible, after MANY hours of developing..
Yeah, go on.. call it bad planning. :(
Anyway, would it be possible to create a disassembly of the HTML4.0 renderer
using reflector and then modifying the code to fit my project? (Links and
toggles..)
Is it legal? Has anyone done this allready and published the code? Are there
any other possibilities other than URL access you guys would recommend?
Part of the reason I need fragments is that the security is based on a
hierarchy in a self-referencing sql table. I need to use a business/security
layer to create parameters for the reports which will be generated by a
web-service on a protected server. No way to limit the results with NT or
sql server security. (well, maybe sql server, but I have to re-engineer)
Hope for good help! :)
Lars-ErikCould you elaborate what exactly you want and what problem you are having?
"Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
news:uMBw6fLnFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I've been really happy during the week-end as I finally found a way to do
> long wanted things with RS 2000. Yesterday at 22 pm, I found out that
> interactive HTML fragments isn't possible, after MANY hours of
> developing.. Yeah, go on.. call it bad planning. :(
> Anyway, would it be possible to create a disassembly of the HTML4.0
> renderer using reflector and then modifying the code to fit my project?
> (Links and toggles..)
> Is it legal? Has anyone done this allready and published the code? Are
> there any other possibilities other than URL access you guys would
> recommend?
> Part of the reason I need fragments is that the security is based on a
> hierarchy in a self-referencing sql table. I need to use a
> business/security layer to create parameters for the reports which will be
> generated by a web-service on a protected server. No way to limit the
> results with NT or sql server security. (well, maybe sql server, but I
> have to re-engineer)
> Hope for good help! :)
> Lars-Erik
>|||Hi again!
I'm trying to create an asp.net form that uses some business logic to create
parameters for a report, then uses the web-service to get the html for the
report. The parameters should not be appended to a user visible/editable url
since that will compromise security. The WebService will be running on a
secure server, and the webservice proxy will impersonate an authorized
account. The report is built with recursive detail groups and a visibility
toggle.
This means I have to pick one or the other option. If I don't want to expose
the parameters, I have to use HTML fragments, else RS will append then to
all links, including targetting each link to the report server "url access"
page. I can create a report that has everything expanded and discard the
toggling functionality, but that'll mean pretty long reports.
Hope this helps explaining.
L-E
"Smith" <smithj@.online.nospam> wrote in message
news:ebqOKhMnFHA.320@.TK2MSFTNGP09.phx.gbl...
> Could you elaborate what exactly you want and what problem you are having?
> "Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
> news:uMBw6fLnFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> Hi!
>> I've been really happy during the week-end as I finally found a way to do
>> long wanted things with RS 2000. Yesterday at 22 pm, I found out that
>> interactive HTML fragments isn't possible, after MANY hours of
>> developing.. Yeah, go on.. call it bad planning. :(
>> Anyway, would it be possible to create a disassembly of the HTML4.0
>> renderer using reflector and then modifying the code to fit my project?
>> (Links and toggles..)
>> Is it legal? Has anyone done this allready and published the code? Are
>> there any other possibilities other than URL access you guys would
>> recommend?
>> Part of the reason I need fragments is that the security is based on a
>> hierarchy in a self-referencing sql table. I need to use a
>> business/security layer to create parameters for the reports which will
>> be generated by a web-service on a protected server. No way to limit the
>> results with NT or sql server security. (well, maybe sql server, but I
>> have to re-engineer)
>> Hope for good help! :)
>> Lars-Erik
>|||Hi,
It's true that you will loose the interactive capabilities using HTML
fragments. An alternative would be using the ReportViewer control which
will not expose the parameters in address bar, but the parameters would
still be seen when you right-click the page and select 'View Source'.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi!
Thank you for answering. Do you have any views on my actual question?
> Anyway, would it be possible to create a disassembly of the HTML4.0
> renderer
> using reflector and then modifying the code to fit my project? (Links and
> toggles..)
> Is it legal? Has anyone done this allready and published the code? Are
> there
> any other possibilities other than URL access you guys would recommend?
Lars-Erik
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:qdHZ%23OmnFHA.940@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> It's true that you will loose the interactive capabilities using HTML
> fragments. An alternative would be using the ReportViewer control which
> will not expose the parameters in address bar, but the parameters would
> still be seen when you right-click the page and select 'View Source'.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||To the best of my knowledge, reverse-engineering MS code is illegal.
Actually it's not recommended to pass sensitive info as part of a report
parameter to begin with. Tudor's blog entry has addressed this issue:
http://blogs.msdn.com/tudortr/archive/2004/07/20/189398.aspx
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you! This was pretty helpful. :)
I'll consider running Reporting Services on the front-end server using a
custom authentication module.
Lars-Erik
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:rhnK2AloFHA.588@.TK2MSFTNGXA01.phx.gbl...
> To the best of my knowledge, reverse-engineering MS code is illegal.
> Actually it's not recommended to pass sensitive info as part of a report
> parameter to begin with. Tudor's blog entry has addressed this issue:
> http://blogs.msdn.com/tudortr/archive/2004/07/20/189398.aspx
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Lars,
I'm glad to hear the information provided is helpful. Please let me know if
you have any other concerns, or need anything else.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
No Word wrap
How can I stop word wrap happening? If the data is too big for the column
then I want it truncated.
Any ideas?Go to the properties of the text box... There is a property named something
like 'Allow automatic expansion''' Make sure it is not checked and you will
be good to go.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe90" <Joe90@.discussions.microsoft.com> wrote in message
news:AE9DC820-52DA-4A6B-A17C-0EF0D9354C81@.microsoft.com...
> How can I stop word wrap happening? If the data is too big for the column
> then I want it truncated.
> Any ideas?|||Right click the field in question, click on properties. Uncheck "Can
increase to accomidate contents".
Catadmin
"Joe90" wrote:
> How can I stop word wrap happening? If the data is too big for the column
> then I want it truncated.
> Any ideas?|||Thanks but I already have 'Can Expand' set to 'false'.
That property stops the the textbox being increased in size to fit the text.
The problem I am getting is that the text is being wrapped within the fixed
size textbox, in some case losing the top half of the first line and bottom
half of the second line.
I've discovered that by adjusting the Top & Bottom padding I can stop it
happening but was wondering if there was a property.
Thanks again
"Catadmin" wrote:
> Right click the field in question, click on properties. Uncheck "Can
> increase to accomidate contents".
> Catadmin
> "Joe90" wrote:
> > How can I stop word wrap happening? If the data is too big for the column
> > then I want it truncated.
> >
> > Any ideas?|||I think you just found it. The only other option I can think of is to
re-size the font smaller, which sometimes makes an item unreadable.
Sorry.
Catadmin
"Joe90" wrote:
> Thanks but I already have 'Can Expand' set to 'false'.
> That property stops the the textbox being increased in size to fit the text.
> The problem I am getting is that the text is being wrapped within the fixed
> size textbox, in some case losing the top half of the first line and bottom
> half of the second line.
> I've discovered that by adjusting the Top & Bottom padding I can stop it
> happening but was wondering if there was a property.
> Thanks again
> "Catadmin" wrote:
> > Right click the field in question, click on properties. Uncheck "Can
> > increase to accomidate contents".
> >
> > Catadmin
> >
> > "Joe90" wrote:
> >
> > > How can I stop word wrap happening? If the data is too big for the column
> > > then I want it truncated.
> > >
> > > Any ideas?|||Also, you could use the left function to display only a certain number of
characters. Of course, that works best if you're using Courier, but perhaps
it could help.
Mike G.
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:424C4F08-511D-4100-B675-3A6479DDC76B@.microsoft.com...
>I think you just found it. The only other option I can think of is to
> re-size the font smaller, which sometimes makes an item unreadable.
> Sorry.
> Catadmin
> "Joe90" wrote:
>> Thanks but I already have 'Can Expand' set to 'false'.
>> That property stops the the textbox being increased in size to fit the
>> text.
>> The problem I am getting is that the text is being wrapped within the
>> fixed
>> size textbox, in some case losing the top half of the first line and
>> bottom
>> half of the second line.
>> I've discovered that by adjusting the Top & Bottom padding I can stop it
>> happening but was wondering if there was a property.
>> Thanks again
>> "Catadmin" wrote:
>> > Right click the field in question, click on properties. Uncheck "Can
>> > increase to accomidate contents".
>> >
>> > Catadmin
>> >
>> > "Joe90" wrote:
>> >
>> > > How can I stop word wrap happening? If the data is too big for the
>> > > column
>> > > then I want it truncated.
>> > >
>> > > Any ideas?
then I want it truncated.
Any ideas?Go to the properties of the text box... There is a property named something
like 'Allow automatic expansion''' Make sure it is not checked and you will
be good to go.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe90" <Joe90@.discussions.microsoft.com> wrote in message
news:AE9DC820-52DA-4A6B-A17C-0EF0D9354C81@.microsoft.com...
> How can I stop word wrap happening? If the data is too big for the column
> then I want it truncated.
> Any ideas?|||Right click the field in question, click on properties. Uncheck "Can
increase to accomidate contents".
Catadmin
"Joe90" wrote:
> How can I stop word wrap happening? If the data is too big for the column
> then I want it truncated.
> Any ideas?|||Thanks but I already have 'Can Expand' set to 'false'.
That property stops the the textbox being increased in size to fit the text.
The problem I am getting is that the text is being wrapped within the fixed
size textbox, in some case losing the top half of the first line and bottom
half of the second line.
I've discovered that by adjusting the Top & Bottom padding I can stop it
happening but was wondering if there was a property.
Thanks again
"Catadmin" wrote:
> Right click the field in question, click on properties. Uncheck "Can
> increase to accomidate contents".
> Catadmin
> "Joe90" wrote:
> > How can I stop word wrap happening? If the data is too big for the column
> > then I want it truncated.
> >
> > Any ideas?|||I think you just found it. The only other option I can think of is to
re-size the font smaller, which sometimes makes an item unreadable.
Sorry.
Catadmin
"Joe90" wrote:
> Thanks but I already have 'Can Expand' set to 'false'.
> That property stops the the textbox being increased in size to fit the text.
> The problem I am getting is that the text is being wrapped within the fixed
> size textbox, in some case losing the top half of the first line and bottom
> half of the second line.
> I've discovered that by adjusting the Top & Bottom padding I can stop it
> happening but was wondering if there was a property.
> Thanks again
> "Catadmin" wrote:
> > Right click the field in question, click on properties. Uncheck "Can
> > increase to accomidate contents".
> >
> > Catadmin
> >
> > "Joe90" wrote:
> >
> > > How can I stop word wrap happening? If the data is too big for the column
> > > then I want it truncated.
> > >
> > > Any ideas?|||Also, you could use the left function to display only a certain number of
characters. Of course, that works best if you're using Courier, but perhaps
it could help.
Mike G.
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:424C4F08-511D-4100-B675-3A6479DDC76B@.microsoft.com...
>I think you just found it. The only other option I can think of is to
> re-size the font smaller, which sometimes makes an item unreadable.
> Sorry.
> Catadmin
> "Joe90" wrote:
>> Thanks but I already have 'Can Expand' set to 'false'.
>> That property stops the the textbox being increased in size to fit the
>> text.
>> The problem I am getting is that the text is being wrapped within the
>> fixed
>> size textbox, in some case losing the top half of the first line and
>> bottom
>> half of the second line.
>> I've discovered that by adjusting the Top & Bottom padding I can stop it
>> happening but was wondering if there was a property.
>> Thanks again
>> "Catadmin" wrote:
>> > Right click the field in question, click on properties. Uncheck "Can
>> > increase to accomidate contents".
>> >
>> > Catadmin
>> >
>> > "Joe90" wrote:
>> >
>> > > How can I stop word wrap happening? If the data is too big for the
>> > > column
>> > > then I want it truncated.
>> > >
>> > > Any ideas?
No witness, primary shutsdown, bring failover online
First of all, Great webcast today. My question is, I have everything up and running and would like to know what to do when the machine my primary is on quits or has a some type of disaster. Do I need to manually run recovery on each db that was mirrored? I'm not currently running a witness.
thanks
No, you don't need to recover anything but depending on which mode you are running you will need to iether manually initiate a failover from server A to B or force the service from server A to B because you are running without a witness.
do you know what mode you are running, high-performance or high-protection?
|||Everything runs in high Safty and just to be clear I'm looking at the scenario where the primary just dies.|||ok, so you're running high protection mode.
I will have to correct myself here first, running in either high performance or high protection requires that you force service from server A to B. In books Online, look up the topics:
Forced Service (with Possible Data Loss)
and
How to: Force Service in a Database Mirroring Session (Transact-SQL)
these will explain in more detail than I ever could!
Hope this helps
|||This is correct. If the principal cannot be contacted, then the way to make the databases available on the mirror is to turn off mirroring or to issue a "FORCE_SERVICE_ALLOW_DATA_LOSS"
Thanks,
Mark
No Windows, Only SQL Server Authentication
Greetings,
I am unable to log onto the MS SQL Server 2000 using my Windows Login name
and Password from the MS command prompt program locally on a new test
installation Windows Enterprise Server 2003 system. However, if I use an
SQL Server Login and Password, it works every time. The command string I am
using is:
"osql.exe -S (local) -U login_name -P password"
When successful (SQL Authentication), an SQL command prompt is returned.
When unsuccessful (Windows Authentication), the following message is
received:
"Login failed for user login_name"
The SQL Server 2000 installation is enabled for both Windows and SQL
authentication and has been updated to the latest service packs.
The installation is a new test set up to experiment with Web Services using
MS SQL Server. The original problem was the installation of the
"wscrRecordStore" demonstration data base from the book, Programming
Microsoft .NET XML WEB SERVICES, by Foggon et al. However, the problem was
reduced to the simple command line issue above in order to troubleshoot it.
Since I am also the Windows and SQL Server system administrator, I have
tried a wide variety of different login strategies. Only users set up with
SQL Authentication seem to be able to access the SQL Server programatically.
What's the problem? This seems to be a straight forward but stubborn
failure.
Richard Scott
Critical Connections, Inc.To establish a Windows authenticated connection from OSQL, you need to
specify the -E parameter instead of -U and -P. This will use the account of
the currently logged in user. For example:
OSQL -S (local) -E
> Only users set up with SQL Authentication seem to be able to access the
SQL Server programatically.
Your connection string needs to specify the type of authentication desired.
Assuming you are using SqlClient:
Windows authentication:
Data Source=MyServer;Integrated Security=SSPI;Initial
Catalog=MyDatabase
SQL Server authentication:
Data Source=MyServer;User ID=MyLogin;Password=MyPassword;Initial
Catalog=MyDatabase
Also, see the article below for additional information on establishing a
trusted connection from ASP.NET:
http://support.microsoft.com/defaul...989&Product=sql
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Scott" <rtscott@.pacbell.net> wrote in message
news:6XL6c.12481$zh1.7776@.newssvr27.news.prodigy.com...
> Greetings,
> I am unable to log onto the MS SQL Server 2000 using my Windows Login name
> and Password from the MS command prompt program locally on a new test
> installation Windows Enterprise Server 2003 system. However, if I use an
> SQL Server Login and Password, it works every time. The command string I
am
> using is:
> "osql.exe -S (local) -U login_name -P password"
> When successful (SQL Authentication), an SQL command prompt is returned.
> When unsuccessful (Windows Authentication), the following message is
> received:
> "Login failed for user login_name"
> The SQL Server 2000 installation is enabled for both Windows and SQL
> authentication and has been updated to the latest service packs.
> The installation is a new test set up to experiment with Web Services
using
> MS SQL Server. The original problem was the installation of the
> "wscrRecordStore" demonstration data base from the book, Programming
> Microsoft .NET XML WEB SERVICES, by Foggon et al. However, the problem
was
> reduced to the simple command line issue above in order to troubleshoot
it.
> Since I am also the Windows and SQL Server system administrator, I have
> tried a wide variety of different login strategies. Only users set up
with
> SQL Authentication seem to be able to access the SQL Server
programatically.
> What's the problem? This seems to be a straight forward but stubborn
> failure.
> Richard Scott
> Critical Connections, Inc.
>|||Not sure if this applies to you, but vefiry that the user who is attempting
to connect actually has a login established in SQL Server. You can do this
by expanding the Security folder in Enterprise Manager and looking at the
Logins.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I am unable to log onto the MS SQL Server 2000 using my Windows Login name
and Password from the MS command prompt program locally on a new test
installation Windows Enterprise Server 2003 system. However, if I use an
SQL Server Login and Password, it works every time. The command string I am
using is:
"osql.exe -S (local) -U login_name -P password"
When successful (SQL Authentication), an SQL command prompt is returned.
When unsuccessful (Windows Authentication), the following message is
received:
"Login failed for user login_name"
The SQL Server 2000 installation is enabled for both Windows and SQL
authentication and has been updated to the latest service packs.
The installation is a new test set up to experiment with Web Services using
MS SQL Server. The original problem was the installation of the
"wscrRecordStore" demonstration data base from the book, Programming
Microsoft .NET XML WEB SERVICES, by Foggon et al. However, the problem was
reduced to the simple command line issue above in order to troubleshoot it.
Since I am also the Windows and SQL Server system administrator, I have
tried a wide variety of different login strategies. Only users set up with
SQL Authentication seem to be able to access the SQL Server programatically.
What's the problem? This seems to be a straight forward but stubborn
failure.
Richard Scott
Critical Connections, Inc.To establish a Windows authenticated connection from OSQL, you need to
specify the -E parameter instead of -U and -P. This will use the account of
the currently logged in user. For example:
OSQL -S (local) -E
> Only users set up with SQL Authentication seem to be able to access the
SQL Server programatically.
Your connection string needs to specify the type of authentication desired.
Assuming you are using SqlClient:
Windows authentication:
Data Source=MyServer;Integrated Security=SSPI;Initial
Catalog=MyDatabase
SQL Server authentication:
Data Source=MyServer;User ID=MyLogin;Password=MyPassword;Initial
Catalog=MyDatabase
Also, see the article below for additional information on establishing a
trusted connection from ASP.NET:
http://support.microsoft.com/defaul...989&Product=sql
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Scott" <rtscott@.pacbell.net> wrote in message
news:6XL6c.12481$zh1.7776@.newssvr27.news.prodigy.com...
> Greetings,
> I am unable to log onto the MS SQL Server 2000 using my Windows Login name
> and Password from the MS command prompt program locally on a new test
> installation Windows Enterprise Server 2003 system. However, if I use an
> SQL Server Login and Password, it works every time. The command string I
am
> using is:
> "osql.exe -S (local) -U login_name -P password"
> When successful (SQL Authentication), an SQL command prompt is returned.
> When unsuccessful (Windows Authentication), the following message is
> received:
> "Login failed for user login_name"
> The SQL Server 2000 installation is enabled for both Windows and SQL
> authentication and has been updated to the latest service packs.
> The installation is a new test set up to experiment with Web Services
using
> MS SQL Server. The original problem was the installation of the
> "wscrRecordStore" demonstration data base from the book, Programming
> Microsoft .NET XML WEB SERVICES, by Foggon et al. However, the problem
was
> reduced to the simple command line issue above in order to troubleshoot
it.
> Since I am also the Windows and SQL Server system administrator, I have
> tried a wide variety of different login strategies. Only users set up
with
> SQL Authentication seem to be able to access the SQL Server
programatically.
> What's the problem? This seems to be a straight forward but stubborn
> failure.
> Richard Scott
> Critical Connections, Inc.
>|||Not sure if this applies to you, but vefiry that the user who is attempting
to connect actually has a login established in SQL Server. You can do this
by expanding the Security folder in Enterprise Manager and looking at the
Logins.
Rand
This posting is provided "as is" with no warranties and confers no rights.
No way to save database diagram outside of database?
This is to express my frustration that there's apparently no way to save a database diagram outside of the database that it's diagramming.
Like many developers, we maintain our databases under source control as SQL scripts. When we need to install the database, we just run the scripts to build the database, from scratch, no need to save any binaries.
I recently took the time to create a database diagram for one of our existing databases, using the tool in Management Studio (2005). For starters, the diagram editor is a primitive toy that lacks many obvious features. But now I that I have the diagram, I find I've painted myself into a corner: The next time I want to build the database from script, I'll lose the diagram, because as far as I can tell, it's persisted as a BLOB in a system table.
I thought maybe I would be able to right-click on the diagram and that I would find an option to "Save diagram to script", or something like that, but the closest I get is "Copy diagram to clipboard" [as a bitmap]. I could save the bitmap, but this leaves me with something I can't maintain.
I might suggest that the Visual Database Tools team take a look at the Visual Studio class diagrams. The editor for these is also a rather primitive toy, but at least the diagrams are persisted as XML, so you can save the diagram script as text in source control, and if you need the diagram to look better than the designer will do on its own, you can edit the code.
Thanks for your consideration. If there is a way to do what I want that I've overlooked, I would appreciate if someone would let me know.
-Allan
1. Insert the rows in dbo.sysdiagrams into a common dev database before you script a new database then reinsert them into a newly created sysdiagrams table when you create a database from the script.
2. Use Visio
|||Sorry to hear you are unhappy with the database diagram. We've had a number of suggestions filed on Microsoft Connect to improve this area: http://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=diagram.
Take a look through those and vote on the issues that are important to you. If you feel there are issues another customer hasn't filed, submit a new suggestion and we'll take it into consideration.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
No way to pass identity back to SqlDataSource?
Is there no way to pass identity info back through SqlDataSource? You can only do that with ADO.NET code?
In other words, if I want to run a complex INSERT statement to a table that uses Identity, I can't take that key value back through something like SCOPE_IDENTITY() and use it?
I know how to do this with ADO code, but I can't figure out how to do it purely with SqlDataSource. I was hoping to do this without having to write a new Insert statement -- just using the one that's already in the SqlDataSource control. But there doesn't seem to be any facility for Identity in there. I tried embedding a separate select statement after the insert statement and a semi-colon, but that didn't seem to do anything.
Thanks!
Check out my reply on this post:http://forums.asp.net/thread/1284900.aspx
This is how I successfully got an output parameter back from my stored procedure using the sqldatasource, for instance the Identity after I inserted a record.
Hope this helps.
|||
I appreciate the reply, but I just don't see how that can work without a stored procedure. The problem is that I can't sneak the SCOPE_IDENTITY event into the Insert transaction through SqlDataSource. I can declare it separately in ADO code during the same (SqlDataSource-based) Insertion loop, but SQL Server sees that as a separate transaction and returns a DBNULL -- the scope is different, so no identity gets returned.
I can define the entire Insert event in ADO code, but then that would remove the function from the SqlDataSource, which was a design goal. Or I can dump the whole thing in a Stored Procedure, but that would also violate a design goal. (I realize this is a fairly academic exercise, but I've become curious at this point and really want to find the correct answer.)
In short, I'm just a-boggle that there's no way to return Identity entirely within the confines of SqlDataSource. It seems a fairly obvious thing to include. They obviously want us using this thing for Insertions, and it's a fairly obvious thing to do when inserting, but it does not appear to be allowed.
Surely I must just be missing something.
|||
Does anybody have any further thoughts on this? It doesn't appear there's any way to pass Identity back through SqlDataSource without moving the Insert statement out to ADO code or using a Stored Procedure. SqlDataSource doesn't appear to have any facility to handle the returning parameter.
|||
I did try the following and it works fine:
InsertCommand="INSERT INTO [table]([fieldName]) VALUES(@.value); Select @.myId = @.@.Identity;"Add a additional parameter to the InsertParameters of the SQLDataSource:
<InsertParameters><asp:Parameter Name="name" Type="type"/><asp:Parameter Direction="output" Name="myId" Size=4 Type=int64/></InsertParameters>
Handle the inserted event of de sqldatasource:
Protected Sub SqlDataSource_Inserted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource.InsertedDim idAs Integerid = e.Command.Parameters("@.myId").ValueEnd Sub|||Thank you so much! I had tried the same thing but couldn't getit to work -- turns out I left out the @. symbol after the semi-colon inthe follow-on select statement within the SqlDataSource. Once Istuck that in there it worked perfectly!
Much obliged!
No way out Please help second post
I am trying to write a query which when run can tell what courses a
department require and what courses were taken by the employee
/*
create table #temp(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into #temp
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','8'
)
insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into #temp1 values(3,'SQL DBA','7')
insert into #temp1 values(3,'FoxPro','7')
insert into #temp1 values(4,'FoxPro','8')
select * from #temp
select * from #temp1
*/
select coalesce (people_id,null),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
right join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
--group by people_id,dept_taken,coursecode,course_t
aken,deptid
union
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
left outer join #temp1 t1
on t1.deptid=t.dept_taken
and t1.coursecode=t.course_taken
) apple
This is the result set I am getting
How I can show peole id 3224in the first lin so it can show me which
courses was
required and were not taken or courses were taken which were not required.
peopleid Course Dept Required Taken
NULL FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
I like to see the outpurt come as this
peopleid Course Dept Required Taken
3224 FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
Thanks
TanweeHi
Thanks for posting DDL. But actaully your tables don't have primary key and
store redundant data, you are really need to normalize your database
I'm not certain that understand your requirements. It seems you don't need
to use UNION instead FULL join is giving you the data.
select isnull((select max(people_id) from #temp where
deptid=4),people_id),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
FULL join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
) AS APPLE
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> I am trying to write a query which when run can tell what courses a
> department require and what courses were taken by the employee
>
> /*
> create table #temp(people_id int,course_taken varchar(50),
> date_taken datetime,full_name varchar(50),
> assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
> insert into #temp
> values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
> insert into #temp
values(3223,'VB','11/20/ 1996','Doney,Vicke',6738,'3','7')darkred">
> insert into #temp
> values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
> insert into #temp values(3223,'SQL
> DBA','11/20/1999','Doney,Vicke',6738,'3','7')
> insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
> insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
> create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
> varchar(50))
> insert into #temp1 values(3,'SQL DBA','7')
> insert into #temp1 values(3,'FoxPro','7')
> insert into #temp1 values(4,'FoxPro','8')
>
> select * from #temp
> select * from #temp1
> */
> select coalesce (people_id,null),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> right join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> --group by people_id,dept_taken,coursecode,course_t
aken,deptid
> union
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> left outer join #temp1 t1
> on t1.deptid=t.dept_taken
> and t1.coursecode=t.course_taken
> ) apple
>
> This is the result set I am getting
> How I can show peole id 3224in the first lin so it can show me which
> courses was
> required and were not taken or courses were taken which were not required.
>
> peopleid Course Dept Required Taken
> NULL FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> I like to see the outpurt come as this
> peopleid Course Dept Required Taken
> 3224 FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> Thanks
> Tanwee
>|||This work great as long as another employee don't have the same department.
By adding this statement it will through the previous query
insert into #temp
values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
"Uri Dimant" wrote:
> Hi
> Thanks for posting DDL. But actaully your tables don't have primary key an
d
> store redundant data, you are really need to normalize your database
> I'm not certain that understand your requirements. It seems you don't need
> to use UNION instead FULL join is giving you the data.
>
> select isnull((select max(people_id) from #temp where
> deptid=4),people_id),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> FULL join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> ) AS APPLE
>
>
> "Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
> news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
> Yo',6790,'4','8')
>
>|||On Mon, 21 Mar 2005 16:31:08 -0800, Tanweer wrote:
>This work great as long as another employee don't have the same department.
>By adding this statement it will through the previous query
> insert into #temp
>values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
Hi Tanweer,
Upon inspection of your data, I've come to the conclusion that your
problems are caused by lack of normalization. Unless I misunderstand
your data, you should split the #temp table in two parts:
Part 1: people_id, fullname, dept_taken (peoople_id is PK)
Part 2: people_id, course_taken, date_taken (people_id is FK to part 1;
people_id + course_taken is PK)
I didn't want to change your data, so I renamed the temp tables to
permanent tables and created views to mimic how it would look with
normalized data:
create table temp1(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into temp1
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into temp1 values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into temp1 values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
insert into temp1 values(3224,'VB','11/20/1999','Ricky
Yo',6790,'4','8')
-- Extra insert statement - uncomment for seecond test case
-- insert into temp1
--values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
create table temp2(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into temp2 values(3,'SQL DBA','7')
insert into temp2 values(3,'FoxPro','7')
insert into temp2 values(4,'FoxPro','8')
go
-- Create two views as placeholders for normalized tables
create view people as
select distinct people_id, full_name, assign_id, dept_taken
from temp1
go
create view coursetaken as
select people_id, course_taken, date_taken
from temp1
go
-- Show normalized data
select * from people
select * from coursetaken
go
-- And here's the query
select coalesce(p.people_id, ct.people_id) as people_id,
coalesce(c.coursecode, ct.course_taken) as coursecode,
coalesce(p.dept_taken, (select dept_taken from people where
people_id = coalesce(p.people_id, ct.people_id))) as deptid,
case when c.deptid is null then 0 else 1 end as required,
case when ct.people_id is null then 0 else 1 end as taken
from people as p
inner join temp2 as c
on c.deptid = p.dept_taken
full join coursetaken as ct
on ct.people_id = p.people_id
and ct.course_taken = c.coursecode
go
drop view people
drop view coursetaken
drop table temp1
drop table temp2
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
department require and what courses were taken by the employee
/*
create table #temp(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into #temp
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','8'
)
insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into #temp1 values(3,'SQL DBA','7')
insert into #temp1 values(3,'FoxPro','7')
insert into #temp1 values(4,'FoxPro','8')
select * from #temp
select * from #temp1
*/
select coalesce (people_id,null),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
right join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
--group by people_id,dept_taken,coursecode,course_t
aken,deptid
union
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
left outer join #temp1 t1
on t1.deptid=t.dept_taken
and t1.coursecode=t.course_taken
) apple
This is the result set I am getting
How I can show peole id 3224in the first lin so it can show me which
courses was
required and were not taken or courses were taken which were not required.
peopleid Course Dept Required Taken
NULL FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
I like to see the outpurt come as this
peopleid Course Dept Required Taken
3224 FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
Thanks
TanweeHi
Thanks for posting DDL. But actaully your tables don't have primary key and
store redundant data, you are really need to normalize your database
I'm not certain that understand your requirements. It seems you don't need
to use UNION instead FULL join is giving you the data.
select isnull((select max(people_id) from #temp where
deptid=4),people_id),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
FULL join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
) AS APPLE
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> I am trying to write a query which when run can tell what courses a
> department require and what courses were taken by the employee
>
> /*
> create table #temp(people_id int,course_taken varchar(50),
> date_taken datetime,full_name varchar(50),
> assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
> insert into #temp
> values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
> insert into #temp
values(3223,'VB','11/20/ 1996','Doney,Vicke',6738,'3','7')darkred">
> insert into #temp
> values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
> insert into #temp values(3223,'SQL
> DBA','11/20/1999','Doney,Vicke',6738,'3','7')
> insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
> insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
> create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
> varchar(50))
> insert into #temp1 values(3,'SQL DBA','7')
> insert into #temp1 values(3,'FoxPro','7')
> insert into #temp1 values(4,'FoxPro','8')
>
> select * from #temp
> select * from #temp1
> */
> select coalesce (people_id,null),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> right join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> --group by people_id,dept_taken,coursecode,course_t
aken,deptid
> union
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> left outer join #temp1 t1
> on t1.deptid=t.dept_taken
> and t1.coursecode=t.course_taken
> ) apple
>
> This is the result set I am getting
> How I can show peole id 3224in the first lin so it can show me which
> courses was
> required and were not taken or courses were taken which were not required.
>
> peopleid Course Dept Required Taken
> NULL FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> I like to see the outpurt come as this
> peopleid Course Dept Required Taken
> 3224 FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> Thanks
> Tanwee
>|||This work great as long as another employee don't have the same department.
By adding this statement it will through the previous query
insert into #temp
values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
"Uri Dimant" wrote:
> Hi
> Thanks for posting DDL. But actaully your tables don't have primary key an
d
> store redundant data, you are really need to normalize your database
> I'm not certain that understand your requirements. It seems you don't need
> to use UNION instead FULL join is giving you the data.
>
> select isnull((select max(people_id) from #temp where
> deptid=4),people_id),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> FULL join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> ) AS APPLE
>
>
> "Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
> news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
> Yo',6790,'4','8')
>
>|||On Mon, 21 Mar 2005 16:31:08 -0800, Tanweer wrote:
>This work great as long as another employee don't have the same department.
>By adding this statement it will through the previous query
> insert into #temp
>values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
Hi Tanweer,
Upon inspection of your data, I've come to the conclusion that your
problems are caused by lack of normalization. Unless I misunderstand
your data, you should split the #temp table in two parts:
Part 1: people_id, fullname, dept_taken (peoople_id is PK)
Part 2: people_id, course_taken, date_taken (people_id is FK to part 1;
people_id + course_taken is PK)
I didn't want to change your data, so I renamed the temp tables to
permanent tables and created views to mimic how it would look with
normalized data:
create table temp1(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into temp1
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into temp1 values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into temp1 values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
insert into temp1 values(3224,'VB','11/20/1999','Ricky
Yo',6790,'4','8')
-- Extra insert statement - uncomment for seecond test case
-- insert into temp1
--values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
create table temp2(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into temp2 values(3,'SQL DBA','7')
insert into temp2 values(3,'FoxPro','7')
insert into temp2 values(4,'FoxPro','8')
go
-- Create two views as placeholders for normalized tables
create view people as
select distinct people_id, full_name, assign_id, dept_taken
from temp1
go
create view coursetaken as
select people_id, course_taken, date_taken
from temp1
go
-- Show normalized data
select * from people
select * from coursetaken
go
-- And here's the query
select coalesce(p.people_id, ct.people_id) as people_id,
coalesce(c.coursecode, ct.course_taken) as coursecode,
coalesce(p.dept_taken, (select dept_taken from people where
people_id = coalesce(p.people_id, ct.people_id))) as deptid,
case when c.deptid is null then 0 else 1 end as required,
case when ct.people_id is null then 0 else 1 end as taken
from people as p
inner join temp2 as c
on c.deptid = p.dept_taken
full join coursetaken as ct
on ct.people_id = p.people_id
and ct.course_taken = c.coursecode
go
drop view people
drop view coursetaken
drop table temp1
drop table temp2
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
No way out please help
I am trying to resolve this issue for more than 3 hours.
I have two tables Emp table and dept course table. I need to find employee
who haven't took the course required by department or employee who already
took all the required courses by dept.
Employee table has this data
emp_id name course dept_id
1 T VB 1
1 T Access 1
1 T FOXPro 1
2 M Network 2
2 M Lan 2
Dept Course Table
dept Course
1 Vb
1 Access
1 SQL
1 Database
1 C Sharp
2 Network
2 Lan
the result will be
emp_id name course Taken dept_id Corse Required
1 T VB 1 VB
1 T Access 1 Access
1 T FOXPro 1 Null
1 T NULL 1
Database
1 T NULL 1 C
sharp
2 M Network 2 Network
2 M Lan 2 Lan
Thanks in advance.
Tanweer
thussain@.appleone.comTanweer,
How about ...
select
emp.emp_id,
emp.name,
taken.course,
coalesce(required.dept,taken.dept),
required.course
from
emp, dept taken, dept required
where
(empt.dept = taken.dept) or
(empt.dept = required.dept)
group by
emp.emp_id, emp.name,
taken.course,
required.dept, required.course
-- Alex Papadimoulis
"Tanweer" wrote:
> I am trying to resolve this issue for more than 3 hours.
> I have two tables Emp table and dept course table. I need to find employee
> who haven't took the course required by department or employee who already
> took all the required courses by dept.
> Employee table has this data
> emp_id name course dept_id
> 1 T VB 1
> 1 T Access 1
> 1 T FOXPro 1
> 2 M Network 2
> 2 M Lan 2
> Dept Course Table
> dept Course
> 1 Vb
> 1 Access
> 1 SQL
> 1 Database
> 1 C Sharp
> 2 Network
> 2 Lan
>
> the result will be
> emp_id name course Taken dept_id Corse Required
> 1 T VB 1 VB
> 1 T Access 1 Access
> 1 T FOXPro 1 Null
> 1 T NULL 1
> Database
> 1 T NULL 1
C
> sharp
> 2 M Network 2 Network
> 2 M Lan 2 Lan
>
> Thanks in advance.
> Tanweer
> thussain@.appleone.com|||Thanks for fast response
I only have two table Emp and Course required
I am confuse about this statement
from emp, dept taken, dept required
Thanks
Tanweer
"Alex Papadimoulis" wrote:
> Tanweer,
> How about ...
>
> select
> emp.emp_id,
> emp.name,
> taken.course,
> coalesce(required.dept,taken.dept),
> required.course
> from
> emp, dept taken, dept required
> where
> (empt.dept = taken.dept) or
> (empt.dept = required.dept)
> group by
> emp.emp_id, emp.name,
> taken.course,
> required.dept, required.course
>
> -- Alex Papadimoulis
>
> "Tanweer" wrote:
>|||Select E.emp_id,
E.name, E.Dept, C.course,
Case When Exists
(Select * From Emp
Where Emp_ID = E.Emp_ID
And Course = C.Course)
Then 1 Else 0 End Taken,
Case When E.Dept = C.Dept
Then 1 Else 0 End Required
From (Select Distinct emp_ID,
Name, Dept
From Emp) As E
Cross Join DeptCourse As C
"Tanweer" wrote:
> I am trying to resolve this issue for more than 3 hours.
> I have two tables Emp table and dept course table. I need to find employee
> who haven't took the course required by department or employee who already
> took all the required courses by dept.
> Employee table has this data
> emp_id name course dept_id
> 1 T VB 1
> 1 T Access 1
> 1 T FOXPro 1
> 2 M Network 2
> 2 M Lan 2
> Dept Course Table
> dept Course
> 1 Vb
> 1 Access
> 1 SQL
> 1 Database
> 1 C Sharp
> 2 Network
> 2 Lan
>
> the result will be
> emp_id name course Taken dept_id Corse Required
> 1 T VB 1 VB
> 1 T Access 1 Access
> 1 T FOXPro 1 Null
> 1 T NULL 1
> Database
> 1 T NULL 1
C
> sharp
> 2 M Network 2 Network
> 2 M Lan 2 Lan
>
> Thanks in advance.
> Tanweer
> thussain@.appleone.com|||On Fri, 18 Mar 2005 12:57:10 -0800, Tanweer wrote:
>I am trying to resolve this issue for more than 3 hours.
(snip)
Hi Tanweer,
Looks like a FULL OUTER JOIN to me:
SELECT e.emp_id, e.name,
e.course AS CourseTaken,
COALESCE(dc.dept_id, e.dept_id) AS dept_id,
dc.course as CourseRequired
FROM Employees AS e
FULL OUTER JOIN DeptCourses AS dc
ON dc.dept_id = e.dept_id
(Untested. For a tested solution, post CREATE TABLE and INSERT
statements - see www.aspfaq.com/5006)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There are some problems with your initial post. Based on your sample data
"FoxPro" course does not belong to any departments, but your expected
results shows it as if it belongs to Dept 1? What determines employee 1
should take courses from department 1? Can you post your DDLs which match
this sample data? What are the keys in your tables?
SELECT e1.Emp_id, e1.Emp_name,
ec1.Course AS "course Taken",
de1.dept_id, dc1.Course AS "Course Required"
FROM DeptCourses dc1
INNER JOIN ( SELECT DISTINCT Emp_id, Dept_id
FROM Employees ) de1
ON dc1.Dept_id = de1.Dept_id
FULL OUTER JOIN ( SELECT DISTINCT Emp_id, course
FROM Employees ) ec1
ON de1.Emp_id = ec1.Emp_id
AND dc1.Course = ec1.Course
INNER JOIN ( SELECT DISTINCT emp_id, "name"
FROM Employees ) e1 ( emp_id, Emp_name )
ON e1.Emp_id = COALESCE( de1.Emp_id, ec1.Emp_id )
ORDER BY e1.Emp_id ;
On an initial glance the schema seems under-normalized. One generic sign of
a poorly designed schema requires complicated relational expressions to
answer simple queries. For instance, if dept can be determined by the course
it offers, having the department identifier in the Employee table is
redundant. Based on some obvious assumptions, to correct the design flaw,
you should remove the dept_id column in the Employee table. You should have
an "association" table which models the relationship between an employee and
a department. Depending on your business rules, the same issue may be
applicable for the Employee name column as well.
CREATE TABLE Employees (
Emp_id INT NOT NULL PRIMARY KEY,
Emp_name VARCHAR(40)
) ;
CREATE TABLE DeptCourses (
Dept_id INT NOT NULL,
--REFERENCES Departments( dept_id )
Course VARCHAR(20) NOT NULL
PRIMARY KEY ( Dept_id, Course )
) ;
CREATE TABLE EmployeeCourses (
Emp_id INT NOT NULL
REFERENCES Employees( Emp_id ),
Course VARCHAR(20) NOT NULL
PRIMARY KEY ( Emp_id, Course )
);
CREATE TABLE DeptEmployees (
dept_id INT NOT NULL,
-- REFERENCES Departments( dept_id ),
Emp_id INT NOT NULL
REFERENCES Employees( Emp_id )
PRIMARY KEY ( dept_id, Emp_id )
);
Now add the sample data:
INSERT Employees SELECT 1, 'T' ;
INSERT Employees SELECT 2, 'M' ;
INSERT DeptCourses SELECT 1 ,'VB' ;
INSERT DeptCourses SELECT 1 ,'Access' ;
INSERT DeptCourses SELECT 1 ,'SQL' ;
INSERT DeptCourses SELECT 1 ,'Database' ;
INSERT DeptCourses SELECT 1 ,'C Sharp' ;
INSERT DeptCourses SELECT 2 ,'Network' ;
INSERT DeptCourses SELECT 2 ,'Lan' ;
INSERT EmployeeCourses SELECT 1, 'VB' ;
INSERT EmployeeCourses SELECT 1, 'Access' ;
INSERT EmployeeCourses SELECT 1, 'FOXPro' ;
INSERT EmployeeCourses SELECT 2, 'Network' ;
INSERT EmployeeCourses SELECT 2, 'Lan' ;
INSERT DeptEmployees SELECT 1, 1 ;
INSERT DeptEmployees SELECT 2, 2 ;
Now you can have:
SELECT e1.Emp_id, e1.Emp_name,
ec1.Course AS "course Taken",
de1.dept_id, dc1.Course AS "Course Required"
FROM DeptCourses dc1
INNER JOIN DeptEmployees de1
ON dc1.Dept_id = de1.Dept_id
FULL OUTER JOIN EmployeeCourses ec1
ON de1.Emp_id = ec1.Emp_id
AND dc1.Course = ec1.Course
INNER JOIN Employees e1
ON e1.Emp_id = COALESCE( de1.Emp_id, ec1.Emp_id )
ORDER BY e1.Emp_id ;
Now try different queries, most of them can be answered with simpler SQL
formulations.
Anith|||I am sorry to be lazy,. Here I posted the whole
/*
drop table #temp
create table #temp(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into #temp values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7
')
insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','8'
)
insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into #temp1 values(3,'SQL DBA','7')
insert into #temp1 values(3,'FoxPro','7')
insert into #temp1 values(4,'FoxPro','8')
*/
--select * from #temp
--select * from #temp1
--give me only the course taken and have valid code
select distinct t.people_id,t.course_taken,
t.date_taken,t.full_name,t.dept_taken,t.jobcode_taken
from #temp t, #temp1 t1
where dept_taken=t1.deptid
and t.jobcode_taken=t1.jobcode_id
I like to see it
people_id Course Name Required Taken Dept Job Code
3223 Access Doney,Vicke 0 1 3 7
3223 VB Doney,Vicke 0 1 3 7
3223 FoxPro Doney,Vicke 1 1 3 7
3223 SQL DBA Doney,Vicke 1 1 3 7
3224 SQL DBA Ricky Yo 0 1 4 8
3224 VB Ricky Yo 0 1 4 8
select * from #temp
select * from #temp1
Thanks for all the help
Tanweer
"Hugo Kornelis" wrote:
> On Fri, 18 Mar 2005 12:57:10 -0800, Tanweer wrote:
>
> (snip)
> Hi Tanweer,
> Looks like a FULL OUTER JOIN to me:
> SELECT e.emp_id, e.name,
> e.course AS CourseTaken,
> COALESCE(dc.dept_id, e.dept_id) AS dept_id,
> dc.course as CourseRequired
> FROM Employees AS e
> FULL OUTER JOIN DeptCourses AS dc
> ON dc.dept_id = e.dept_id
> (Untested. For a tested solution, post CREATE TABLE and INSERT
> statements - see www.aspfaq.com/5006)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Did u try this?
Select E.emp_id,
E.name, E.Dept, C.course,
Case When Exists
(Select * From Emp
Where Emp_ID = E.Emp_ID
And Course = C.Course)
Then 1 Else 0 End Taken,
Case When E.Dept = C.Dept
Then 1 Else 0 End Required
From (Select Distinct emp_ID,
Name, Dept
From Emp) As E
Cross Join DeptCourse As C
"Tanweer" wrote:
> I am sorry to be lazy,. Here I posted the whole
> /*
> drop table #temp
> create table #temp(people_id int,course_taken varchar(50),
> date_taken datetime,full_name varchar(50),
> assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
> insert into #temp values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3',
'7')
> insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7'
)
> insert into #temp
> values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
> insert into #temp values(3223,'SQL
> DBA','11/20/1999','Doney,Vicke',6738,'3','7')
> insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','
8')
> insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
> create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
> varchar(50))
> insert into #temp1 values(3,'SQL DBA','7')
> insert into #temp1 values(3,'FoxPro','7')
> insert into #temp1 values(4,'FoxPro','8')
> */
> --select * from #temp
> --select * from #temp1
> --give me only the course taken and have valid code
> select distinct t.people_id,t.course_taken,
> t.date_taken,t.full_name,t.dept_taken,t.jobcode_taken
> from #temp t, #temp1 t1
> where dept_taken=t1.deptid
> and t.jobcode_taken=t1.jobcode_id
> I like to see it
> people_id Course Name Required Taken Dept Job Code
> 3223 Access Doney,Vicke 0 1 3 7
> 3223 VB Doney,Vicke 0 1 3 7
> 3223 FoxPro Doney,Vicke 1 1 3 7
> 3223 SQL DBA Doney,Vicke 1 1 3 7
> 3224 SQL DBA Ricky Yo 0 1 4 8
> 3224 VB Ricky Yo 0 1 4 8
>
> select * from #temp
> select * from #temp1
> Thanks for all the help
> Tanweer
> "Hugo Kornelis" wrote:
>|||I used it this way
Select E.people_id,
E.full_name, E.Dept_taken, C.coursecode,
Case When Exists
(Select * From #temp
Where people_ID = E.people_ID
And Course_taken = C.Coursecode)
Then 1 Else 0 End Taken,
Case When E.Dept_taken = C.Deptid
Then 1 Else 0 End Required
From (Select Distinct people_ID,
full_Name, Dept_taken
From #temp) As E
Cross Join #temp1 As C
However the result is not coming up correctly
3223 Doney,Vicke 3 SQL DBA 1 1
3223 Doney,Vicke 3 FoxPro 1 1
3223 Doney,Vicke 3 FoxPro 1 0
3223 Doney,Vicke 3 ORACLE 0 1
3224 Ricky Yo 4 SQL DBA 1 0
3224 Ricky Yo 4 FoxPro 0 0
3224 Ricky Yo 4 FoxPro 0 1
3224 Ricky Yo 4 ORACLE 0 0
Line 3,4 should not be there because they are only require for dept 4 and
also it is not showing any course taken by the employee however it was not
required.
Thanks for all the help
"CBretana" wrote:
> Did u try this?
> Select E.emp_id,
> E.name, E.Dept, C.course,
> Case When Exists
> (Select * From Emp
> Where Emp_ID = E.Emp_ID
> And Course = C.Course)
> Then 1 Else 0 End Taken,
> Case When E.Dept = C.Dept
> Then 1 Else 0 End Required
> From (Select Distinct emp_ID,
> Name, Dept
> From Emp) As E
> Cross Join DeptCourse As C
>
> "Tanweer" wrote:
>|||Your sample data, there was only one row per course in the Dept Course table
.
Your results indicate that the 'FoxPro' course must be in the table more
than once (twice actually). That is why the results show the row twice. Th
e
following makes the adjustment for that...
Select E.people_id,
E.full_name, E.Dept_taken,
C.coursecode,
Case When Exists
(Select * From #temp
Where people_ID = E.people_ID
And Course_taken = C.Coursecode)
Then 1 Else 0 End Taken,
Case When Exists
(Select * From #temp1
Where coursecode = E.Course_taken
And Deptid = Dept_taken)
Then 1 Else 0 End Required
From (Select Distinct people_ID,
full_Name, Dept_taken
From #temp) As E
Cross Join
(Select Distinct Course
from #Temp1) As C
"Tanweer" wrote:
> I used it this way
> Select E.people_id,
> E.full_name, E.Dept_taken, C.coursecode,
> Case When Exists
> (Select * From #temp
> Where people_ID = E.people_ID
> And Course_taken = C.Coursecode)
> Then 1 Else 0 End Taken,
> Case When E.Dept_taken = C.Deptid
> Then 1 Else 0 End Required
> From (Select Distinct people_ID,
> full_Name, Dept_taken
> From #temp) As E
> Cross Join #temp1 As C
>
> However the result is not coming up correctly
> 3223 Doney,Vicke 3 SQL DBA 1 1
> 3223 Doney,Vicke 3 FoxPro 1 1
> 3223 Doney,Vicke 3 FoxPro 1 0
> 3223 Doney,Vicke 3 ORACLE 0 1
> 3224 Ricky Yo 4 SQL DBA 1 0
> 3224 Ricky Yo 4 FoxPro 0 0
> 3224 Ricky Yo 4 FoxPro 0 1
> 3224 Ricky Yo 4 ORACLE 0 0
> Line 3,4 should not be there because they are only require for dept 4 and
> also it is not showing any course taken by the employee however it was not
> required.
> Thanks for all the help
>
> "CBretana" wrote:
>
I have two tables Emp table and dept course table. I need to find employee
who haven't took the course required by department or employee who already
took all the required courses by dept.
Employee table has this data
emp_id name course dept_id
1 T VB 1
1 T Access 1
1 T FOXPro 1
2 M Network 2
2 M Lan 2
Dept Course Table
dept Course
1 Vb
1 Access
1 SQL
1 Database
1 C Sharp
2 Network
2 Lan
the result will be
emp_id name course Taken dept_id Corse Required
1 T VB 1 VB
1 T Access 1 Access
1 T FOXPro 1 Null
1 T NULL 1
Database
1 T NULL 1 C
sharp
2 M Network 2 Network
2 M Lan 2 Lan
Thanks in advance.
Tanweer
thussain@.appleone.comTanweer,
How about ...
select
emp.emp_id,
emp.name,
taken.course,
coalesce(required.dept,taken.dept),
required.course
from
emp, dept taken, dept required
where
(empt.dept = taken.dept) or
(empt.dept = required.dept)
group by
emp.emp_id, emp.name,
taken.course,
required.dept, required.course
-- Alex Papadimoulis
"Tanweer" wrote:
> I am trying to resolve this issue for more than 3 hours.
> I have two tables Emp table and dept course table. I need to find employee
> who haven't took the course required by department or employee who already
> took all the required courses by dept.
> Employee table has this data
> emp_id name course dept_id
> 1 T VB 1
> 1 T Access 1
> 1 T FOXPro 1
> 2 M Network 2
> 2 M Lan 2
> Dept Course Table
> dept Course
> 1 Vb
> 1 Access
> 1 SQL
> 1 Database
> 1 C Sharp
> 2 Network
> 2 Lan
>
> the result will be
> emp_id name course Taken dept_id Corse Required
> 1 T VB 1 VB
> 1 T Access 1 Access
> 1 T FOXPro 1 Null
> 1 T NULL 1
> Database
> 1 T NULL 1
C
> sharp
> 2 M Network 2 Network
> 2 M Lan 2 Lan
>
> Thanks in advance.
> Tanweer
> thussain@.appleone.com|||Thanks for fast response
I only have two table Emp and Course required
I am confuse about this statement
from emp, dept taken, dept required
Thanks
Tanweer
"Alex Papadimoulis" wrote:
> Tanweer,
> How about ...
>
> select
> emp.emp_id,
> emp.name,
> taken.course,
> coalesce(required.dept,taken.dept),
> required.course
> from
> emp, dept taken, dept required
> where
> (empt.dept = taken.dept) or
> (empt.dept = required.dept)
> group by
> emp.emp_id, emp.name,
> taken.course,
> required.dept, required.course
>
> -- Alex Papadimoulis
>
> "Tanweer" wrote:
>|||Select E.emp_id,
E.name, E.Dept, C.course,
Case When Exists
(Select * From Emp
Where Emp_ID = E.Emp_ID
And Course = C.Course)
Then 1 Else 0 End Taken,
Case When E.Dept = C.Dept
Then 1 Else 0 End Required
From (Select Distinct emp_ID,
Name, Dept
From Emp) As E
Cross Join DeptCourse As C
"Tanweer" wrote:
> I am trying to resolve this issue for more than 3 hours.
> I have two tables Emp table and dept course table. I need to find employee
> who haven't took the course required by department or employee who already
> took all the required courses by dept.
> Employee table has this data
> emp_id name course dept_id
> 1 T VB 1
> 1 T Access 1
> 1 T FOXPro 1
> 2 M Network 2
> 2 M Lan 2
> Dept Course Table
> dept Course
> 1 Vb
> 1 Access
> 1 SQL
> 1 Database
> 1 C Sharp
> 2 Network
> 2 Lan
>
> the result will be
> emp_id name course Taken dept_id Corse Required
> 1 T VB 1 VB
> 1 T Access 1 Access
> 1 T FOXPro 1 Null
> 1 T NULL 1
> Database
> 1 T NULL 1
C
> sharp
> 2 M Network 2 Network
> 2 M Lan 2 Lan
>
> Thanks in advance.
> Tanweer
> thussain@.appleone.com|||On Fri, 18 Mar 2005 12:57:10 -0800, Tanweer wrote:
>I am trying to resolve this issue for more than 3 hours.
(snip)
Hi Tanweer,
Looks like a FULL OUTER JOIN to me:
SELECT e.emp_id, e.name,
e.course AS CourseTaken,
COALESCE(dc.dept_id, e.dept_id) AS dept_id,
dc.course as CourseRequired
FROM Employees AS e
FULL OUTER JOIN DeptCourses AS dc
ON dc.dept_id = e.dept_id
(Untested. For a tested solution, post CREATE TABLE and INSERT
statements - see www.aspfaq.com/5006)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There are some problems with your initial post. Based on your sample data
"FoxPro" course does not belong to any departments, but your expected
results shows it as if it belongs to Dept 1? What determines employee 1
should take courses from department 1? Can you post your DDLs which match
this sample data? What are the keys in your tables?
SELECT e1.Emp_id, e1.Emp_name,
ec1.Course AS "course Taken",
de1.dept_id, dc1.Course AS "Course Required"
FROM DeptCourses dc1
INNER JOIN ( SELECT DISTINCT Emp_id, Dept_id
FROM Employees ) de1
ON dc1.Dept_id = de1.Dept_id
FULL OUTER JOIN ( SELECT DISTINCT Emp_id, course
FROM Employees ) ec1
ON de1.Emp_id = ec1.Emp_id
AND dc1.Course = ec1.Course
INNER JOIN ( SELECT DISTINCT emp_id, "name"
FROM Employees ) e1 ( emp_id, Emp_name )
ON e1.Emp_id = COALESCE( de1.Emp_id, ec1.Emp_id )
ORDER BY e1.Emp_id ;
On an initial glance the schema seems under-normalized. One generic sign of
a poorly designed schema requires complicated relational expressions to
answer simple queries. For instance, if dept can be determined by the course
it offers, having the department identifier in the Employee table is
redundant. Based on some obvious assumptions, to correct the design flaw,
you should remove the dept_id column in the Employee table. You should have
an "association" table which models the relationship between an employee and
a department. Depending on your business rules, the same issue may be
applicable for the Employee name column as well.
CREATE TABLE Employees (
Emp_id INT NOT NULL PRIMARY KEY,
Emp_name VARCHAR(40)
) ;
CREATE TABLE DeptCourses (
Dept_id INT NOT NULL,
--REFERENCES Departments( dept_id )
Course VARCHAR(20) NOT NULL
PRIMARY KEY ( Dept_id, Course )
) ;
CREATE TABLE EmployeeCourses (
Emp_id INT NOT NULL
REFERENCES Employees( Emp_id ),
Course VARCHAR(20) NOT NULL
PRIMARY KEY ( Emp_id, Course )
);
CREATE TABLE DeptEmployees (
dept_id INT NOT NULL,
-- REFERENCES Departments( dept_id ),
Emp_id INT NOT NULL
REFERENCES Employees( Emp_id )
PRIMARY KEY ( dept_id, Emp_id )
);
Now add the sample data:
INSERT Employees SELECT 1, 'T' ;
INSERT Employees SELECT 2, 'M' ;
INSERT DeptCourses SELECT 1 ,'VB' ;
INSERT DeptCourses SELECT 1 ,'Access' ;
INSERT DeptCourses SELECT 1 ,'SQL' ;
INSERT DeptCourses SELECT 1 ,'Database' ;
INSERT DeptCourses SELECT 1 ,'C Sharp' ;
INSERT DeptCourses SELECT 2 ,'Network' ;
INSERT DeptCourses SELECT 2 ,'Lan' ;
INSERT EmployeeCourses SELECT 1, 'VB' ;
INSERT EmployeeCourses SELECT 1, 'Access' ;
INSERT EmployeeCourses SELECT 1, 'FOXPro' ;
INSERT EmployeeCourses SELECT 2, 'Network' ;
INSERT EmployeeCourses SELECT 2, 'Lan' ;
INSERT DeptEmployees SELECT 1, 1 ;
INSERT DeptEmployees SELECT 2, 2 ;
Now you can have:
SELECT e1.Emp_id, e1.Emp_name,
ec1.Course AS "course Taken",
de1.dept_id, dc1.Course AS "Course Required"
FROM DeptCourses dc1
INNER JOIN DeptEmployees de1
ON dc1.Dept_id = de1.Dept_id
FULL OUTER JOIN EmployeeCourses ec1
ON de1.Emp_id = ec1.Emp_id
AND dc1.Course = ec1.Course
INNER JOIN Employees e1
ON e1.Emp_id = COALESCE( de1.Emp_id, ec1.Emp_id )
ORDER BY e1.Emp_id ;
Now try different queries, most of them can be answered with simpler SQL
formulations.
Anith|||I am sorry to be lazy,. Here I posted the whole
/*
drop table #temp
create table #temp(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into #temp values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7
')
insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','8'
)
insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into #temp1 values(3,'SQL DBA','7')
insert into #temp1 values(3,'FoxPro','7')
insert into #temp1 values(4,'FoxPro','8')
*/
--select * from #temp
--select * from #temp1
--give me only the course taken and have valid code
select distinct t.people_id,t.course_taken,
t.date_taken,t.full_name,t.dept_taken,t.jobcode_taken
from #temp t, #temp1 t1
where dept_taken=t1.deptid
and t.jobcode_taken=t1.jobcode_id
I like to see it
people_id Course Name Required Taken Dept Job Code
3223 Access Doney,Vicke 0 1 3 7
3223 VB Doney,Vicke 0 1 3 7
3223 FoxPro Doney,Vicke 1 1 3 7
3223 SQL DBA Doney,Vicke 1 1 3 7
3224 SQL DBA Ricky Yo 0 1 4 8
3224 VB Ricky Yo 0 1 4 8
select * from #temp
select * from #temp1
Thanks for all the help
Tanweer
"Hugo Kornelis" wrote:
> On Fri, 18 Mar 2005 12:57:10 -0800, Tanweer wrote:
>
> (snip)
> Hi Tanweer,
> Looks like a FULL OUTER JOIN to me:
> SELECT e.emp_id, e.name,
> e.course AS CourseTaken,
> COALESCE(dc.dept_id, e.dept_id) AS dept_id,
> dc.course as CourseRequired
> FROM Employees AS e
> FULL OUTER JOIN DeptCourses AS dc
> ON dc.dept_id = e.dept_id
> (Untested. For a tested solution, post CREATE TABLE and INSERT
> statements - see www.aspfaq.com/5006)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Did u try this?
Select E.emp_id,
E.name, E.Dept, C.course,
Case When Exists
(Select * From Emp
Where Emp_ID = E.Emp_ID
And Course = C.Course)
Then 1 Else 0 End Taken,
Case When E.Dept = C.Dept
Then 1 Else 0 End Required
From (Select Distinct emp_ID,
Name, Dept
From Emp) As E
Cross Join DeptCourse As C
"Tanweer" wrote:
> I am sorry to be lazy,. Here I posted the whole
> /*
> drop table #temp
> create table #temp(people_id int,course_taken varchar(50),
> date_taken datetime,full_name varchar(50),
> assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
> insert into #temp values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3',
'7')
> insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7'
)
> insert into #temp
> values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
> insert into #temp values(3223,'SQL
> DBA','11/20/1999','Doney,Vicke',6738,'3','7')
> insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','
8')
> insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
> create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
> varchar(50))
> insert into #temp1 values(3,'SQL DBA','7')
> insert into #temp1 values(3,'FoxPro','7')
> insert into #temp1 values(4,'FoxPro','8')
> */
> --select * from #temp
> --select * from #temp1
> --give me only the course taken and have valid code
> select distinct t.people_id,t.course_taken,
> t.date_taken,t.full_name,t.dept_taken,t.jobcode_taken
> from #temp t, #temp1 t1
> where dept_taken=t1.deptid
> and t.jobcode_taken=t1.jobcode_id
> I like to see it
> people_id Course Name Required Taken Dept Job Code
> 3223 Access Doney,Vicke 0 1 3 7
> 3223 VB Doney,Vicke 0 1 3 7
> 3223 FoxPro Doney,Vicke 1 1 3 7
> 3223 SQL DBA Doney,Vicke 1 1 3 7
> 3224 SQL DBA Ricky Yo 0 1 4 8
> 3224 VB Ricky Yo 0 1 4 8
>
> select * from #temp
> select * from #temp1
> Thanks for all the help
> Tanweer
> "Hugo Kornelis" wrote:
>|||I used it this way
Select E.people_id,
E.full_name, E.Dept_taken, C.coursecode,
Case When Exists
(Select * From #temp
Where people_ID = E.people_ID
And Course_taken = C.Coursecode)
Then 1 Else 0 End Taken,
Case When E.Dept_taken = C.Deptid
Then 1 Else 0 End Required
From (Select Distinct people_ID,
full_Name, Dept_taken
From #temp) As E
Cross Join #temp1 As C
However the result is not coming up correctly
3223 Doney,Vicke 3 SQL DBA 1 1
3223 Doney,Vicke 3 FoxPro 1 1
3223 Doney,Vicke 3 FoxPro 1 0
3223 Doney,Vicke 3 ORACLE 0 1
3224 Ricky Yo 4 SQL DBA 1 0
3224 Ricky Yo 4 FoxPro 0 0
3224 Ricky Yo 4 FoxPro 0 1
3224 Ricky Yo 4 ORACLE 0 0
Line 3,4 should not be there because they are only require for dept 4 and
also it is not showing any course taken by the employee however it was not
required.
Thanks for all the help
"CBretana" wrote:
> Did u try this?
> Select E.emp_id,
> E.name, E.Dept, C.course,
> Case When Exists
> (Select * From Emp
> Where Emp_ID = E.Emp_ID
> And Course = C.Course)
> Then 1 Else 0 End Taken,
> Case When E.Dept = C.Dept
> Then 1 Else 0 End Required
> From (Select Distinct emp_ID,
> Name, Dept
> From Emp) As E
> Cross Join DeptCourse As C
>
> "Tanweer" wrote:
>|||Your sample data, there was only one row per course in the Dept Course table
.
Your results indicate that the 'FoxPro' course must be in the table more
than once (twice actually). That is why the results show the row twice. Th
e
following makes the adjustment for that...
Select E.people_id,
E.full_name, E.Dept_taken,
C.coursecode,
Case When Exists
(Select * From #temp
Where people_ID = E.people_ID
And Course_taken = C.Coursecode)
Then 1 Else 0 End Taken,
Case When Exists
(Select * From #temp1
Where coursecode = E.Course_taken
And Deptid = Dept_taken)
Then 1 Else 0 End Required
From (Select Distinct people_ID,
full_Name, Dept_taken
From #temp) As E
Cross Join
(Select Distinct Course
from #Temp1) As C
"Tanweer" wrote:
> I used it this way
> Select E.people_id,
> E.full_name, E.Dept_taken, C.coursecode,
> Case When Exists
> (Select * From #temp
> Where people_ID = E.people_ID
> And Course_taken = C.Coursecode)
> Then 1 Else 0 End Taken,
> Case When E.Dept_taken = C.Deptid
> Then 1 Else 0 End Required
> From (Select Distinct people_ID,
> full_Name, Dept_taken
> From #temp) As E
> Cross Join #temp1 As C
>
> However the result is not coming up correctly
> 3223 Doney,Vicke 3 SQL DBA 1 1
> 3223 Doney,Vicke 3 FoxPro 1 1
> 3223 Doney,Vicke 3 FoxPro 1 0
> 3223 Doney,Vicke 3 ORACLE 0 1
> 3224 Ricky Yo 4 SQL DBA 1 0
> 3224 Ricky Yo 4 FoxPro 0 0
> 3224 Ricky Yo 4 FoxPro 0 1
> 3224 Ricky Yo 4 ORACLE 0 0
> Line 3,4 should not be there because they are only require for dept 4 and
> also it is not showing any course taken by the employee however it was not
> required.
> Thanks for all the help
>
> "CBretana" wrote:
>
Subscribe to:
Posts (Atom)