More recursive questions

I have a list of categories that I have to find the path to each
my table is set up like
ID CategoryNum CategoryName ParentID
1 AA Top Level 1 0
2 AB Top Level 2 0
3 BA Second Level 1 1
I need my query/stored proc to return
Tope Level 1/Second Level 1 if I pass in the integer of 3
I can do this in programming but cant seem to wrap my head around it in SQL Server
TIA for the helpHi,
Is your requirement to get a Tree structure from SQL Server?
SQL Server has got FOR XML clause which will return the data in hierarchial manner. But it does have implementaiton in returning n-level tree. You have to use EXPLICIT method which will require you to do a lot of coding to get a hierarchial Tree.
May be if you can explain more on the format you would like to get the data, can help.
Thanks.|||no really just an arraylist (working in vb) of the output (Top Level 1/Second Level 1 etc. etc. ) - trying to do it on sqlserver because the recursive code (even thought the maximum level is 5) is taking too long to run
|||Dylan,
Try the link below most things you want to do with Arrays in SQL is covered by Joe Celko, browse his book in the link at your local book store. Hope this helps.
http://www.onlamp.com/pub/a/onlamp/2004/08/05/hierarchical_sql.html|||okay some of that makes sense - doing this in SQL is really really new to me - the 'Searching For Superiors' section seems to cover what I need but his example uses a path string to show the path to the node - I dont have that I am using a child sub child method
oh this is so fun|||Joe Celko is an ANSI SQL purist so what you are looking for he did not cover because he thinks most things you do in the database is covered by ANSI SQL. This new link I have given you before he uses T-SQL and there is code files you can download to play with. BTW click on 2000 so you don't have to go through old code for earlier versions. Hope this helps
http://www.sommarskog.se/arrays-in-sql.html#iterative|||

You can always utilize recursion within self-joining tables. For example a function to "expand" the hierarchy (assuming the table name is CATEGORIES)

CREATE Function [dbo].[GetParent](@.currentid int,@.includestartnode bit)
returns @.results TABLE(id int,orderid int)
AS

BEGIN

DECLAre @.level int;
SET @.level=-1

IF @.includestartnode=1
BEGIN
INSERT INTO @.results
VALUES(@.currentid,0)
END

WHILE EXISTS (SELECT parentid FROM CATEGORIES WHEREid=@.currentid)
BEGIN

SELECT @.currentid=parentId
FROM CATEGORIES
WHEREid=@.currentid

IF @.@.ROWCOUNT=0
BEGIN
BREAK
END

INSERT INTO @.results(id,orderid)
SELECT @.currentid, @.level
FROM CATEGORIES
WHEREid=@.currentid

SET @.level=@.level - 1

END

RETURN

END

Using this with a call:

SELECT idFROM dbo.GetParent(3,1)

ORDERby orderid

would return
ID
===
1
3
As you can see it returns the ID list of the hierarchical nodes so it is rather generic function (returns nodes who have parent-child relationship) going with "bottom-to-up" principle. You could utilize this in getting the "path"
==
DECLARE @.pathnvarchar(1000)

SELECT @.path=COALESCE(@.path+'/','')+

+CAST(CategoryNameasnvarchar)

from CATEGORIES cINNERJOIN dbo.GetParent(3,1) tmp

ON c.id=tmp.id

ORDERBY orderid

select @.path

Returns

Top level 1/Second level 1

Note that of course, if you want, you can combine getting the data into the function or just on plain proc to do the job. E.g you can do the recutrsive stuff which I here do in function, also in stored proc if you want to.

More Questions on Permission

Thanks Hari, That helps a lot.
Do I have a way to know previlage details about an Object?
such as is the SELECT on a table been Granted, revoked or
denied?
Greg Chang
Subject: Re: Questions about syspermissions
From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
6/24/2004 9:29:17 PM
HI,
Have a look into sysprotects table, column action
contains the granted
previlages for each objects.
The explanation for Action column:-
26 = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE
Simply you can execute the below system procedure to get
the objct level
previlages:-
sp_helprotect <object_name>
Thanks
Hari
MCDBA
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message
news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
> is syspermissions table contains the Permissions of
> delete, select, insert, update, execute and DRI on all
> the objects?
> how do I use this syspermissions table? For example,
> which column stored the INSERT Privileges?
> thanks
> Greg Chang
..
See the BOL for information about the sp_helprotect stored procedure.
Jim
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
> Thanks Hari, That helps a lot.
> Do I have a way to know previlage details about an Object?
> such as is the SELECT on a table been Granted, revoked or
> denied?
> Greg Chang
>
> Subject: Re: Questions about syspermissions
> From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
> 6/24/2004 9:29:17 PM
> HI,
> Have a look into sysprotects table, column action
> contains the granted
> previlages for each objects.
> The explanation for Action column:-
> 26 = REFERENCES
> 178 = CREATE FUNCTION
> 193 = SELECT
> 195 = INSERT
> 196 = DELETE
> 197 = UPDATE
> 198 = CREATE TABLE
> 203 = CREATE DATABASE
> 207 = CREATE VIEW
> 222 = CREATE PROCEDURE
> 224 = EXECUTE
> 228 = BACKUP DATABASE
> 233 = CREATE DEFAULT
> 235 = BACKUP LOG
> 236 = CREATE RULE
>
> Simply you can execute the below system procedure to get
> the objct level
> previlages:-
> sp_helprotect <object_name>
> --
> Thanks
> Hari
> MCDBA
> "Greg Chang" <anonymous@.discussions.microsoft.com> wrote
> in message
> news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
>
> .
>
|||What is BOL?

>--Original Message--
>See the BOL for information about the sp_helprotect
stored procedure.
>Jim
>"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
Object?[vbcol=seagreen]
or[vbcol=seagreen]
get[vbcol=seagreen]
wrote[vbcol=seagreen]
all
>
>.
>
|||See the BOL for information about the sp_helprotect stored procedure.
Jim
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
> Thanks Hari, That helps a lot.
> Do I have a way to know previlage details about an Object?
> such as is the SELECT on a table been Granted, revoked or
> denied?
> Greg Chang
>
> Subject: Re: Questions about syspermissions
> From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
> 6/24/2004 9:29:17 PM
> HI,
> Have a look into sysprotects table, column action
> contains the granted
> previlages for each objects.
> The explanation for Action column:-
> 26 = REFERENCES
> 178 = CREATE FUNCTION
> 193 = SELECT
> 195 = INSERT
> 196 = DELETE
> 197 = UPDATE
> 198 = CREATE TABLE
> 203 = CREATE DATABASE
> 207 = CREATE VIEW
> 222 = CREATE PROCEDURE
> 224 = EXECUTE
> 228 = BACKUP DATABASE
> 233 = CREATE DEFAULT
> 235 = BACKUP LOG
> 236 = CREATE RULE
>
> Simply you can execute the below system procedure to get
> the objct level
> previlages:-
> sp_helprotect <object_name>
> --
> Thanks
> Hari
> MCDBA
> "Greg Chang" <anonymous@.discussions.microsoft.com> wrote
> in message
> news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
>
> .
>
|||BOL = Books OnLine, which is the documentation for SQL Server. You can find
it in the SQL Server program group, and you can download the latest updated
version from www.microsoft.com/sql
Jacco Schalkwijk
SQL Server MVP
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...[vbcol=seagreen]
> What is BOL?
> stored procedure.
> in message
> Object?
> or
> get
> wrote
> all
|||What is BOL?

>--Original Message--
>See the BOL for information about the sp_helprotect
stored procedure.
>Jim
>"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
Object?[vbcol=seagreen]
or[vbcol=seagreen]
get[vbcol=seagreen]
wrote[vbcol=seagreen]
all
>
>.
>
|||hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...
> What is BOL?
BOL stands for Books On Line, the official on-line guide for SQL Server, you
can download, in localized version at
http://www.microsoft.com/sql/techinf...2000/books.asp
it's the best documentation ever about SQL Server...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||BOL = Books OnLine, which is the documentation for SQL Server. You can find
it in the SQL Server program group, and you can download the latest updated
version from www.microsoft.com/sql
Jacco Schalkwijk
SQL Server MVP
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...[vbcol=seagreen]
> What is BOL?
> stored procedure.
> in message
> Object?
> or
> get
> wrote
> all
|||hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...
> What is BOL?
BOL stands for Books On Line, the official on-line guide for SQL Server, you
can download, in localized version at
http://www.microsoft.com/sql/techinf...2000/books.asp
it's the best documentation ever about SQL Server...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

More queries more problems

I'm trying to write a query that returns a list of all employess from one table, and their hours worked from another table. If there are no entries in the hours table, I want to still show them with value of zero hours worked. I almost have this working, but I'm having some issues. Here's my query:

SELECT th.ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID
WHERE (th.Start_Date = '" + myStartDate + "' OR th.Start_Date IS NULL)
ORDER BY tp.FirstName

For testing, in my profiles table, I have 5 users. In my hours table, I have entries for 3 of those users. Of those three users, 2 have entries for both 1/1/03 and 1/16/03, and the other has just 1 entry for 1/1/03.

When I run the query above for myStartDate = '1/1/03', I get the correct output, which shows the hours for 3 users, and shows 0 hours for the other 2. However, if I run the query on myStartDate = '1/16/03', my result set has the hours for the 2 users that have entries for that date, and also has 0 hours for the 2 users that have no entries. The user that has only 1 entry (for 1/1/03)should show up with 0 hours, but instead, it doesn't show up at all.

I'm guessing that the problem is with the way the table is joined, and that because that user has 1 valid Start_Date, it is getting excluded from the WHERE conditions, but I can't seem to make it work.

help!can you show the structure of the 2 tables ...|||tblProfiles has the following columns:

ID FirstName LastName ... (other unimportant columns)

tblHours has the following columns:

ID userID Start_Date End_Date Total_Hours

|||have you tried running this query in Query Analyzer as with the straight sql to make sure that you are getting the same results?|||yeah, I did... in Enterprise Manager, if I run the same query, I get the same results... the user with 1 entry for 1/1/03 does not show up at all if I run the query with:

WHERE Start_Date = '1/16/03' OR Start_Date IS NULL

I am assuming that "Start_Date IS NULL" is not evaluating to true because, with the table join, it is picking up the other record with the 1/1/03 Start_Date ...|||In my WHERE clause:

WHERE Start_Date = '" + myStartDate + "' OR Start_Date IS NULL

I think theStart_Date IS NULL part needs to specifically refer only the record for the given "myStartDate", but I can't make it jive...|||Yeah this is ringing a bell, I had something similiar. Maybe try wrapping everything in parens, like

WHERE ((Start_Date = '" + myStartDate + "') OR (Start_Date IS NULL))

Not sure but this might force SQL to evaluate the where clause correctly.|||I tried your suggestion, but it didn't change the results at all...|||have you thought abt using a cursor...
loop through for each record in tblprofiles...and for each record join with tblhours on id and start_date and get the hours..
this way you can get those ids tht dont have any entries...startdates/hours...etc and still show them.

HTH|||I'm not familiar with using cursors, but I'm willing to try anything... can you give me an example of how I would use it?|||heres a sample cursor tht i am using

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT ID,FirstName,LastName, ... (other columns)
OPEN rs
fetch next from rs into @.id,@.fname,@.lname...( other columns)
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 /* here do your stuff - join with the other table and get the stuff */
 use the @.id to join with tblhours..
 FETCH NEXT FROM rs INTO @.id,@.fname,@.lname...( other columns)
END
close rs
deallocate rs

HTH|||Ok, I'm getting lost in the syntax... here's what I have, but it's not working:

DECLARE @.id integer, @.fname varchar(50), @.lname varchar(50)
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT tp.ID, tp.FirstName, tp.LastName FROM tblProfiles tp
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 SELECT @.id, @.fname, @.lname
SELECT th.Total_Hours FROM tblHours th WHERE th.userID = @.id AND th.Start_Date='1/16/03'
 fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs

what am I doing wrong?|||I finally got it to work, and didn't have to use the cursor! Here's the working query:

SELECT IsNull(th.ID, '0') as ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID AND th.Start_Date = '" + myStartDate + "'
WHERE ((th.Start_Date = '" + myStartDate + "') OR (th.Start_Date IS NULL)) ORDER BY th.Total_Hours DESC, tp.FirstName

The solution was to addStart_Date = '" + myStartDate + "' to the LEFT JOIN criteria.

Thanks for all the help along the way guys...
<sigh of relief>|||

CREATE PROCEDURE get_hrs AS
begin
DECLARE @.id as int, @.fname as varchar(50), @.lname as varchar(50)
declare @.mystartdate as datetime
set @.mystartdate='1/1/2003'
declare @.hrs as int
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT [ID], FName, LName FROM tblpro
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
select @.hrs=hours from tblhours where ([ID] =@.id and startdate=@.mystartdate)
print convert(varchar(5),@.id) + ' ' + @.fname + '-' + @.lname + ' ' + convert(nvarchar(10),@.hrs)
set @.hrs=0
 fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs
end
GO

you might need to change the column names, table names...

HTH

more problems with unique sequences

I have another different schema with the same data, but this one its like this

Code Snippet

CREATE Table events (
id INT not null,
PxMiss Real Not Null,
PyMiss Real Not Null,
filenames Varchar(50));

ALTER TABLE events
ADD CONSTRAINT pk_particle PRIMARY KEY (id,filenames);

GO

CREATE Table Muon (
idap INT Not Null,
id INT Not Null,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Electron(
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Jet (
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

Create View lepton AS
select * from Muon
Union all
select * from Electron;
GO

Create View particle AS
select * from lepton
Union all
select * from Jet;
GO

I need that every particle had a different idap, but all the date is filled in muon, electron and jet. and then is joined in a view called particle.

The way that you are going about this is likely to cause some problems.

For example, while it is possible to create a VIEW that would provide a unique idap for for each Particle, since it is a view and will be re-constituted at every execution, there is no certainity that the idap will be the same at each execution. (In my opinion -that is a big issue. -but maybe not for your situation...)

It seems more stable if you were to create a Particle idap that was the combination of each constituent Identifier + idap. Something like this for example:

CREATE VIEW Particle
AS

SELECT
'M' + cast( idap AS varchar(12)),
id,
eventid,
Px,
Py
Pz
Kf,
Ee
FROM Muon

UNION ALL

SELECT
'E' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Electron

UNION ALL

SELECT
'J' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Jet
GO

With this example, each idap will be unique, and also give you a clue about the constitutent component.

More not in issue

Hi,
I have three tables, one is the client information table (T1) contain an
uniqueid for the client, the other is client address table (T2), which
contains different type of address for the client (e.g. business address,
home address) and the different type of address is identified by addtype_c,
and it is link to T1 via clientid. The third table (T3) contains the media
request from the clients and the address (add_c) they want the media sent to
.
and of course, it has the fields clientid.
I need to generate the mailing lable for the client according to the address
that the client want the materials sent to. However, how can I create a
query to alert me that the address type that the clients want the materials
sent to is not in the address table.
Thanks in advance.One option change your business logic to require a ship-to address when the
client is filling in this information. Or add a WHERE clause to your query
to exclude NULL addresses or if you use a DEFAULT to exclude addresses = to
the DEFAULT.
HTH
Jerry
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:421420FC-090A-499F-9715-32A63D9A2930@.microsoft.com...
> Hi,
> I have three tables, one is the client information table (T1) contain an
> uniqueid for the client, the other is client address table (T2), which
> contains different type of address for the client (e.g. business address,
> home address) and the different type of address is identified by
> addtype_c,
> and it is link to T1 via clientid. The third table (T3) contains the
> media
> request from the clients and the address (add_c) they want the media sent
> to.
> and of course, it has the fields clientid.
> I need to generate the mailing lable for the client according to the
> address
> that the client want the materials sent to. However, how can I create a
> query to alert me that the address type that the clients want the
> materials
> sent to is not in the address table.
> Thanks in advance.
>|||On Fri, 30 Sep 2005 07:16:04 -0700, qjlee wrote:

>Hi,
>I have three tables, one is the client information table (T1) contain an
>uniqueid for the client, the other is client address table (T2), which
>contains different type of address for the client (e.g. business address,
>home address) and the different type of address is identified by addtype_c,
>and it is link to T1 via clientid. The third table (T3) contains the media
>request from the clients and the address (add_c) they want the media sent t
o.
>and of course, it has the fields clientid.
>I need to generate the mailing lable for the client according to the addres
s
>that the client want the materials sent to. However, how can I create a
>query to alert me that the address type that the clients want the materials
>sent to is not in the address table.
>Thanks in advance.
>
Hi qjlee,
Since you didn't post DDL and sample data (www.aspfaq.com/5006), I'll
have to make some wild assumptions about your tables.
SELECT T3.ClientID, T3.add_c
FROM T3
WHERE NOT EXISTS
(SELECT *
FROM T2
WHERE T2.ClientID = T3.ClientID
AND T2.AddressType = T3.AddressType)
or
SELECT T3.ClientID, T3.add_c
FROM T3
LEFT JOIN T2
ON T2.ClientID = T3.ClientID
AND T2.AddressType = T3.AddressType
WHERE T2.ClientID IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

More Info: Stored Procedure Security Question

Dear Group

I have found that table A had SELECT permissions for 'Public' but not table
B.
Giving 'Public' SELECT permissions on table B did the trick.

HOWEVER, I don't want anyone to be able to do a direct SELECT on table A or
B but only give them access to the data by using the stored procedures. Is
there any way this can be set up?

Thanks for your efforts!
Have a nice day!

Martin

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message news:...
> Dear Group
> I'm having two stored procedures, sp_a and sp_b
> Content of stored procedure A:
> CREATE PROCEDURE dbo.sp_a
> SELECT * FROM a
> GO
> Content of stored procedure B:
> CREATE PROCEDURE dbo.sp_b
> SELECT * FROM b
> GO
> I have created a user that has execute permissions for both procedures.
> When I run procedure A, all works fine but when running procedure B I'm
> getting an error saying that the user must have SELECT permissions on
> table B.
> Both tables are owned by dbo, and the security role for the user doesn't
> has any SELECT permission on table a and b.
> I'd be grateful if anyone could point me in a direction why this error
> might come up for procedure B but not for A,
> with a possible solution without giving the user SELECT permissions.
> Thanks very much for your help!
> MartinMartin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
>> Content of stored procedure A:
>> CREATE PROCEDURE dbo.sp_a
>> SELECT * FROM a
>> GO
>>
>> Content of stored procedure B:
>> CREATE PROCEDURE dbo.sp_b
>> SELECT * FROM b
>> GO
> I have found that table A had SELECT permissions for 'Public' but not
> table B. Giving 'Public' SELECT permissions on table B did the trick.
> HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
> or B but only give them access to the data by using the stored
> procedures. Is there any way this can be set up?

I have a strong feeling that you are not telling us the full story,
because what you have described is the typical usage of ownership
chaining, and users should indeed be able to access the data in the
tables through the stored procedures.

Is there by chance some dynamic SQL involved?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I also responded to your previous thread. As Erland said, this should work
as long as the objects are in the same database. If in different databases,
you'll need to enable cross-database chaining and the databases need to have
the same owner in order to maintain an unbroken ownership chain for
dbo-owned objects.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:cg53pg$rbo$1@.sparta.btinternet.com...
> Dear Group
> I have found that table A had SELECT permissions for 'Public' but not
table
> B.
> Giving 'Public' SELECT permissions on table B did the trick.
> HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
or
> B but only give them access to the data by using the stored procedures. Is
> there any way this can be set up?
> Thanks for your efforts!
> Have a nice day!
> Martin
>
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:...
> > Dear Group
> > I'm having two stored procedures, sp_a and sp_b
> > Content of stored procedure A:
> > CREATE PROCEDURE dbo.sp_a
> > SELECT * FROM a
> > GO
> > Content of stored procedure B:
> > CREATE PROCEDURE dbo.sp_b
> > SELECT * FROM b
> > GO
> > I have created a user that has execute permissions for both procedures.
> > When I run procedure A, all works fine but when running procedure B I'm
> > getting an error saying that the user must have SELECT permissions on
> > table B.
> > Both tables are owned by dbo, and the security role for the user doesn't
> > has any SELECT permission on table a and b.
> > I'd be grateful if anyone could point me in a direction why this error
> > might come up for procedure B but not for A,
> > with a possible solution without giving the user SELECT permissions.
> > Thanks very much for your help!
> > Martin|||Thanks for your help guys!
Well, as Erland suspected, I haven't given you the full story as I
thought it doesn't matter but as I found out the hard way, it was
indeed dynamic SQL that caused the problem.

Erland, please don't tell me off for using dynamic SQL! LOL
I've read your wonderful fantastic guides and obbey all rules on
dynamic SQL but although I'm not happy myself, I think I really can't
avoid it this time.
However, if you'd offer to have a look at my script and just tell me
whether it can be done without dynamic SQL then this would make me
very happy!

Anyway, I solved the permission problem by basing the stored
procedures that contain the dynamic SQL on Views and implementing row
level security in those.

Thanks again for your efforts!
Have a nice day!

Martin

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<07JVc.8691$3O3.3742@.newsread2.news.pas.earthlink.n et>...
> I also responded to your previous thread. As Erland said, this should work
> as long as the objects are in the same database. If in different databases,
> you'll need to enable cross-database chaining and the databases need to have
> the same owner in order to maintain an unbroken ownership chain for
> dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:cg53pg$rbo$1@.sparta.btinternet.com...
> > Dear Group
> > I have found that table A had SELECT permissions for 'Public' but not
> table
> > B.
> > Giving 'Public' SELECT permissions on table B did the trick.
> > HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
> or
> > B but only give them access to the data by using the stored procedures. Is
> > there any way this can be set up?
> > Thanks for your efforts!
> > Have a nice day!
> > Martin
> > "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:...
> > > Dear Group
> > > > I'm having two stored procedures, sp_a and sp_b
> > > > Content of stored procedure A:
> > > CREATE PROCEDURE dbo.sp_a
> > > SELECT * FROM a
> > > GO
> > > > Content of stored procedure B:
> > > CREATE PROCEDURE dbo.sp_b
> > > SELECT * FROM b
> > > GO
> > > > I have created a user that has execute permissions for both procedures.
> > > When I run procedure A, all works fine but when running procedure B I'm
> > > getting an error saying that the user must have SELECT permissions on
> > > table B.
> > > > Both tables are owned by dbo, and the security role for the user doesn't
> > > has any SELECT permission on table a and b.
> > > I'd be grateful if anyone could point me in a direction why this error
> > > might come up for procedure B but not for A,
> > > with a possible solution without giving the user SELECT permissions.
> > > > Thanks very much for your help!
> > > > Martin
>|||Martin (theintrepidfox@.hotmail.com) writes:
> Erland, please don't tell me off for using dynamic SQL! LOL
> I've read your wonderful fantastic guides and obbey all rules on
> dynamic SQL but although I'm not happy myself, I think I really can't
> avoid it this time.
> However, if you'd offer to have a look at my script and just tell me
> whether it can be done without dynamic SQL then this would make me
> very happy!

Well, there are cases where dynamic SQL is the best solution and there
are cases where dynamic SQL is a really poor choice.

The whole message of http://www.sommarskog.se/dyn-search.html is that
for dynamic search conditions is "use dynamic SQL, if you can handle
the security issues". If you can make it with views, then you should
be fine.

Beware though, that a very skilled person can be able to cram out
information from a view for row-based security that he is not supposed
to have access to. It is not that he can actually get to see the rows,
but he can make conclusions from query plans statistical IO and such.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks very much Erland!

>It is not that he can actually get to see the rows,
> but he can make conclusions from query plans statistical IO and such.

Do you know any source with more information on this view security issue?
What harm can it do? It's a CRM app, not a top secret military app.

Thanks for your efforts!

M

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns954FEB97D2796Yazorman@.127.0.0.1>...
> Martin (theintrepidfox@.hotmail.com) writes:
> > Erland, please don't tell me off for using dynamic SQL! LOL
> > I've read your wonderful fantastic guides and obbey all rules on
> > dynamic SQL but although I'm not happy myself, I think I really can't
> > avoid it this time.
> > However, if you'd offer to have a look at my script and just tell me
> > whether it can be done without dynamic SQL then this would make me
> > very happy!
> Well, there are cases where dynamic SQL is the best solution and there
> are cases where dynamic SQL is a really poor choice.
> The whole message of http://www.sommarskog.se/dyn-search.html is that
> for dynamic search conditions is "use dynamic SQL, if you can handle
> the security issues". If you can make it with views, then you should
> be fine.
> Beware though, that a very skilled person can be able to cram out
> information from a view for row-based security that he is not supposed
> to have access to. It is not that he can actually get to see the rows,
> but he can make conclusions from query plans statistical IO and such.|||Martin (theintrepidfox@.hotmail.com) writes:
> Do you know any source with more information on this view security issue?
> What harm can it do? It's a CRM app, not a top secret military app.

As long as you don't let SQL Server MVP Steve Kass anywhere near the
database, I think your data is fairly safe. :-) That is, Steve Kass was
the one who discovered this issue, and to exploit you would need to
a query tool like Query Analyzer, and you would probably have to have
some knowledge about the schema. And you need a very good understanding
of SQL Server. Finally a good dosis of patience is good for the task.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

More grouping questions - sum of maximum

I have a table with two groups, unit and domicile. Structure appears as follows:

GH1 - Domicile Group: Bentwood
GH2- Unit Group: 200
Detail: LegID Unit ID Miles TripMiles
1 200 25 150
2 200 75 150
3 200 50 150

Subtotal for GH2 Count(Fields!legID.value) Sum(Fields!Miles.value) First(Fields!TripMiles.value)
3 150 150

This works, what breaks is the subtotal for GH1. on the TripMiles Field.
It doesnt like Sum(First(Fields!Tripmiles.value,"Unit_Group")), nor can I do Sum(ReportItems!Trip_group.value) I get the error that an aggregate cannot occur on a report item unless it is in the page header or footer.

Of course if I try Sum(Fields!TripMiles.value,"Domicile_Group") I get 450 which is incorrect.

I've tried making First(fields!tripmiles.value) a calculated field which I just then drop into the header of the Unit group<GH2>. This works perfectly until I then try to Sum that field in the Domicile group. I can create the expression but once I click ok, VS2005 reports that it must shut down and crashes to desktop, when I do a recovery all my changes are lost.

<by the way this crash always occurs when I create a calculated field that uses First/Max or Min, then try to use that field in another calculation. Fields based on IIF work without issue>

To clarify, I want to take the TripMiles Column and for The Unit group show the first value that occurs for that unit as the value.

In the Domicile Group, I want to sum all of those Unit Values.
if unit 200 had 3 occurances of 150, my Unit group would have a value of 150 <not 450>
if Unit 300 had 4 occurances of 100, my Unit group would have a value of 100 <not 400>
My total for the Domicile would be 250 <not 850>

Thank you for any assistance.

I can't think of a way of doing this but surely in your case the subtotal at the Domicile level for TripMiles column should equal that of the Miles column.

I would cheat and just put Sum(Fields!Miles.Value) in the subtotal for the TripMiles column in the Domicile group footer.

|||

Bah I jinxed myself using a perfect scenario in that mielage column didnt I <sigh>

Yes I agree with you in this particular instance with the data I displayed. TripMiles never change, it is a base amount for the order. I should have added two more rows to show that the two columns do not always match.

Let's pretend that the TripColumn was labeled something else, Like fuel Consumed.

If I could do an aggregate on the Max/min/first of that field I would be set :(

Edit: OK, I found another thread similar to my situation here :http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106424&SiteID=1

Where it is stated that at the time that post was made, One year ago last week, you could not do an aggregate of an aggregate and gave a work expression that is one I've tried but errors out.

They then stated to 'modifey' the query to insert nulls at the duplicate. So in my example I'd have
150
null
null
null

How do I do that?

|||

You can use the SQL 2005 ROW_NUMBER() aggreagate function as follows:

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY LegID) WHEN 1 THEN TripMiles ELSE NULL END
FROM YourTable

More grouping questions - sum of maximum

I have a table with two groups, unit and domicile. Structure appears as follows:

GH1 - Domicile Group: Bentwood
GH2- Unit Group: 200
Detail: LegID Unit ID Miles TripMiles
1 200 25 150
2 200 75 150
3 200 50 150

Subtotal for GH2 Count(Fields!legID.value) Sum(Fields!Miles.value) First(Fields!TripMiles.value)
3 150 150

This works, what breaks is the subtotal for GH1. on the TripMiles Field.
It doesnt like Sum(First(Fields!Tripmiles.value,"Unit_Group")), nor can I do Sum(ReportItems!Trip_group.value) I get the error that an aggregate cannot occur on a report item unless it is in the page header or footer.

Of course if I try Sum(Fields!TripMiles.value,"Domicile_Group") I get 450 which is incorrect.

I've tried making First(fields!tripmiles.value) a calculated field which I just then drop into the header of the Unit group<GH2>. This works perfectly until I then try to Sum that field in the Domicile group. I can create the expression but once I click ok, VS2005 reports that it must shut down and crashes to desktop, when I do a recovery all my changes are lost.

<by the way this crash always occurs when I create a calculated field that uses First/Max or Min, then try to use that field in another calculation. Fields based on IIF work without issue>

To clarify, I want to take the TripMiles Column and for The Unit group show the first value that occurs for that unit as the value.

In the Domicile Group, I want to sum all of those Unit Values.
if unit 200 had 3 occurances of 150, my Unit group would have a value of 150 <not 450>
if Unit 300 had 4 occurances of 100, my Unit group would have a value of 100 <not 400>
My total for the Domicile would be 250 <not 850>

Thank you for any assistance.

I can't think of a way of doing this but surely in your case the subtotal at the Domicile level for TripMiles column should equal that of the Miles column.

I would cheat and just put Sum(Fields!Miles.Value) in the subtotal for the TripMiles column in the Domicile group footer.

|||

Bah I jinxed myself using a perfect scenario in that mielage column didnt I <sigh>

Yes I agree with you in this particular instance with the data I displayed. TripMiles never change, it is a base amount for the order. I should have added two more rows to show that the two columns do not always match.

Let's pretend that the TripColumn was labeled something else, Like fuel Consumed.

If I could do an aggregate on the Max/min/first of that field I would be set :(

Edit: OK, I found another thread similar to my situation here :http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106424&SiteID=1

Where it is stated that at the time that post was made, One year ago last week, you could not do an aggregate of an aggregate and gave a work expression that is one I've tried but errors out.

They then stated to 'modifey' the query to insert nulls at the duplicate. So in my example I'd have
150
null
null
null

How do I do that?

|||

You can use the SQL 2005 ROW_NUMBER() aggreagate function as follows:

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY LegID) WHEN 1 THEN TripMiles ELSE NULL END
FROM YourTable

|||what other solution, if i'm using SQL 2000? what other method to achived the above reasult?|||

Code Block

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE LegID

WHEN (SELECT MIN(LegID) FROM YourTable)

THEN TripMiles

ELSE NULL

END
FROM YourTable

more FREETEXT

Continuing problems with FREETEXT...
So... I went through the wizard to create FullText catalog, everything went
nice without problems (selected table and fields to search on)
The problem is that I still have Item Count 0 (properties of the catalog)
after rebuild and full population was done (I have about 1000 records in the
table)
Fields that were searched are "ntext" all containing some data
What to do? What could be problem?
problem was that administrators were not un SQL users list and apparently
MSSearch service was not able to connect to server because of that
"borko" <borko@.borko.co.co> wrote in message
news:d81fbi$mmt$1@.ss405.t-com.hr...
> Continuing problems with FREETEXT...
> So... I went through the wizard to create FullText catalog, everything
> went nice without problems (selected table and fields to search on)
> The problem is that I still have Item Count 0 (properties of the catalog)
> after rebuild and full population was done (I have about 1000 records in
> the table)
> Fields that were searched are "ntext" all containing some data
> What to do? What could be problem?
>
>
|||Borko,
For the benefit of others & to clarify for others who may also encounter
this problem with either FREETEXT or CONTAINS when using SQL Server 2000
Full-text Search (FTS), if you remove or alter the BUILTIN\Administrators
login, then the "Microsoft Search" (mssearch.exe) service will not be able
to connect to SQL Server. Thus the MSSearch service will not be able to
successfully execute a Full or Incremental Population (0 item count is a
symptom of this problem). There are several kb articles on this topic and a
pure TSQL workaround if the BUILTIN\Administrators login must not exist for
security reasons, you can use the following T-SQL code as a workaround:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"borko" <borko@.borko.co.co> wrote in message
news:d8m5vo$63m$1@.ss405.t-com.hr...[vbcol=seagreen]
> problem was that administrators were not un SQL users list and apparently
> MSSearch service was not able to connect to server because of that
>
> "borko" <borko@.borko.co.co> wrote in message
> news:d81fbi$mmt$1@.ss405.t-com.hr...
catalog)
>

more FREETEXT

Continuing problems with FREETEXT...
So... I went through the wizard to create FullText catalog, everything went
nice without problems (selected table and fields to search on)
The problem is that I still have Item Count 0 (properties of the catalog)
after rebuild and full population was done (I have about 1000 records in the
table)
Fields that were searched are "ntext" all containing some data
What to do? What could be problem?problem was that administrators were not un SQL users list and apparently
MSSearch service was not able to connect to server because of that
"borko" <borko@.borko.co.co> wrote in message
news:d81fbi$mmt$1@.ss405.t-com.hr...
> Continuing problems with FREETEXT...
> So... I went through the wizard to create FullText catalog, everything
> went nice without problems (selected table and fields to search on)
> The problem is that I still have Item Count 0 (properties of the catalog)
> after rebuild and full population was done (I have about 1000 records in
> the table)
> Fields that were searched are "ntext" all containing some data
> What to do? What could be problem?
>
>|||Borko,
For the benefit of others & to clarify for others who may also encounter
this problem with either FREETEXT or CONTAINS when using SQL Server 2000
Full-text Search (FTS), if you remove or alter the BUILTIN\Administrators
login, then the "Microsoft Search" (mssearch.exe) service will not be able
to connect to SQL Server. Thus the MSSearch service will not be able to
successfully execute a Full or Incremental Population (0 item count is a
symptom of this problem). There are several kb articles on this topic and a
pure TSQL workaround if the BUILTIN\Administrators login must not exist for
security reasons, you can use the following T-SQL code as a workaround:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"borko" <borko@.borko.co.co> wrote in message
news:d8m5vo$63m$1@.ss405.t-com.hr...
> problem was that administrators were not un SQL users list and apparently
> MSSearch service was not able to connect to server because of that
>
> "borko" <borko@.borko.co.co> wrote in message
> news:d81fbi$mmt$1@.ss405.t-com.hr...
catalog)[vbcol=seagreen]
>

More effiect update from linked server

I am updating data from a smaller linked table into a larger table (SQL
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Daniel
danielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegrou ps.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>
|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:

> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>
|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
|||Thanks Uri and John!
John Bell wrote:[vbcol=seagreen]
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote:

More effiect update from linked server

I am updating data from a smaller linked table into a larger table (SQL
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Danieldanielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegroups.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:

> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
>|||Thanks Uri and John!
John Bell wrote:[vbcol=seagreen]
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote:
>

More effiect update from linked server

I am updating data from a smaller linked table into a larger table (SQL
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Danieldanielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegroups.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:
> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
> > I am updating data from a smaller linked table into a larger table (SQL
> > server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> > previously). The update now looks like:
> >
> > UPDATE tableA
> > SET colx = (select colx FROM linkedtableB rem
> > WHERE rem.pkcol = tableA.pkcol),
> > coly = (select coly FROM linkedtableB rem
> > WHERE rem.pkcol = tableA.pkcol)
> > WHERE EXISTS (SELECT * FROM linkedtableB as rem
> > WHERE rem.pkcol = tableA.pkcol)
> >
> > This seems to run an inordinately long time (in excess of 1hr + for
> > this one table, and I have to update 40 more tables). My question it,
> > what would be a more effiecient way to update this data? My reasoning
> > for updating this data is, while doing the insert earlier, I may have
> > gathered partial data during an operation, thus requiring an update at
> > some later point.
> >
> > Any insight into a better solution would be greatly appriciated.
> >
> > Daniel
> >
> >|||Thanks Uri and John!
John Bell wrote:
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote:
> > Hi Daniel
> >
> > Try
> >
> > UPDATE A
> > SET colx = B.colx
> > coly = B.coly
> > FROM tableA A
> > JOIN linkedtableB B ON B.pkcol = A.pkcol
> >
> > John
> >
> > "danielp" wrote:
> >
> > > I am updating data from a smaller linked table into a larger table (SQL
> > > server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> > > previously). The update now looks like:
> > >
> > > UPDATE tableA
> > > SET colx = (select colx FROM linkedtableB rem
> > > WHERE rem.pkcol = tableA.pkcol),
> > > coly = (select coly FROM linkedtableB rem
> > > WHERE rem.pkcol = tableA.pkcol)
> > > WHERE EXISTS (SELECT * FROM linkedtableB as rem
> > > WHERE rem.pkcol = tableA.pkcol)
> > >
> > > This seems to run an inordinately long time (in excess of 1hr + for
> > > this one table, and I have to update 40 more tables). My question it,
> > > what would be a more effiecient way to update this data? My reasoning
> > > for updating this data is, while doing the insert earlier, I may have
> > > gathered partial data during an operation, thus requiring an update at
> > > some later point.
> > >
> > > Any insight into a better solution would be greatly appriciated.
> > >
> > > Daniel
> > >
> > >sql

More efficient way of checking for value

This trigger needs to check the contact1 table for the deleted accountno. If
it is not found there it will then use the record in contact1del.
This trigger works but I know there is a better way to check for the deleted
accountno in contact1 in the IF statement. Accountno values in Contact1 are
always unique
Thank you in advance for reading this. Any help and\or advise on this
trigger in general is greatly appreciated
CREATE TRIGGER Supp_contact_delete ON Contsupp
FOR DELETE
AS
if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from Contact1
)
BEGIN
INSERT INTO Contact1Del
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, contact1.company, contact1.key5
FROM deleted
INNER join
contact1
ON contact1.accountno = deleted.accountno
WHERE deleted.rectype='C' AND deleted.accountno > ''
END
ELSE
BEGIN
INSERT INTO Contact1Del
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
FROM deleted
INNER join
contact1del
ON contact1del.accountno = deleted.accountno
WHERE contact1del.rectype IS NULL and deleted.rectype='C'
ENDJenks,
Try using EXISTS instead.
See:
http://msdn.microsoft.com/library/d...br />
0a2b.asp
HTH
Jerry
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:1D3AE7DF-B678-461B-B0FF-11F4010DFDD6@.microsoft.com...
> This trigger needs to check the contact1 table for the deleted accountno.
> If
> it is not found there it will then use the record in contact1del.
> This trigger works but I know there is a better way to check for the
> deleted
> accountno in contact1 in the IF statement. Accountno values in Contact1
> are
> always unique
> Thank you in advance for reading this. Any help and\or advise on this
> trigger in general is greatly appreciated
>
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from
> Contact1)
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join
> contact1
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> END
> ELSE
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
> FROM deleted
> INNER join
> contact1del
> ON contact1del.accountno = deleted.accountno
> WHERE contact1del.rectype IS NULL and deleted.rectype='C'
> END
>|||jenks wrote:
> This trigger needs to check the contact1 table for the deleted
> accountno. If it is not found there it will then use the record in
> contact1del.
> This trigger works but I know there is a better way to check for the
> deleted accountno in contact1 in the IF statement. Accountno values
> in Contact1 are always unique
> Thank you in advance for reading this. Any help and\or advise on this
> trigger in general is greatly appreciated
>
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from
> Contact1)
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1,
> fax, address1, address2, address3, city, state, zip, country,
> mergecodes, lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact,
> deleted.title, deleted.contsupref, deleted.dear, deleted.phone ,
> deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join
> contact1
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> END
> ELSE
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1,
> fax, address1, address2, address3, city, state, zip, country,
> mergecodes, lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact,
> deleted.title, deleted.contsupref, deleted.dear, deleted.phone ,
> deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
> FROM deleted
> INNER join
> contact1del
> ON contact1del.accountno = deleted.accountno
> WHERE contact1del.rectype IS NULL and deleted.rectype='C'
> END
IF EXISTS (
SELECT *
FROM dbo.Contact1
INNER JOIN deleted
on deleted.accountno = Contact1.accountno )
But your original code does not handle multi-row deletes where one row
deleted exists in the Contact1 table and one row that is deleted does
not. Tthe EXISTS code above won't work correctly in that case either.
You could just always run both inserts for rows that exist and for rows
that do not, assuming those rows are mutually exclusive and forget
checking for existence.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In this case, the deleted rows with rectype of 'C' will always have an
accountno value in one of the two tables(contact1 or contact1del). EXISTS
will always work, unless I am missing something.
Thanks alot guys. Really appreciate it.
"David Gugick" wrote:
IF EXISTS (
SELECT *
FROM dbo.Contact1
INNER JOIN deleted
on deleted.accountno = Contact1.accountno )

> But your original code does not handle multi-row deletes where one row
> deleted exists in the Contact1 table and one row that is deleted does
> not. Tthe EXISTS code above won't work correctly in that case either.
> You could just always run both inserts for rows that exist and for rows
> that do not, assuming those rows are mutually exclusive and forget
> checking for existence.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||David, I just read your response more carefully and I see I missed your poin
t.
In this case, the records are touched through a front end app. All Contsupp
records with a rectype of 'C' will always have an associated Accountno in
Contact1. It is possible to delete individual Contsupp records, in which cas
e
the first part of the query with the IF EXISTS statement will be true. The
second part of the trigger is to handle deletion of Contact1 records. When a
Contact1 record is deleted, the associated Contsupp records are automaticall
y
deleted imediately after the Contact1 record. (there is a deletion trigger o
n
Contact1 as well).
Again, thank you for taking a look
"jenks" wrote:
> In this case, the deleted rows with rectype of 'C' will always have an
> accountno value in one of the two tables(contact1 or contact1del). EXISTS
> will always work, unless I am missing something.
> Thanks alot guys. Really appreciate it.
> "David Gugick" wrote:
> IF EXISTS (
> SELECT *
> FROM dbo.Contact1
> INNER JOIN deleted
> on deleted.accountno = Contact1.accountno )
>|||jenks wrote:
> In this case, the deleted rows with rectype of 'C' will always have an
> accountno value in one of the two tables(contact1 or contact1del).
> EXISTS will always work, unless I am missing something.
> Thanks alot guys. Really appreciate it.
> "David Gugick" wrote:
> IF EXISTS (
> SELECT *
> FROM dbo.Contact1
> INNER JOIN deleted
> on deleted.accountno = Contact1.accountno )
Let's say that two rows are deleted in a single statement:
Delete from dbo.Contsupp
Where accountno in (1, 2)
One of the rows deleted exists in Contact1. The other one doesn't. The
EXISTS statement will return true because one row exists in the
relationship between deleted and Contact1 and the corresponding insert
will take place. However, there is another row in the deleted table
which does not exist in Contact1 and its insert will not be executed.
So I think you still have a problem in your code.
David Gugick
Quest Software
www.imceda.com
www.quest.com

More efficient than LEFT JOIN

I have a table with data that is refreshed regularly but I still need to
store the old data. I have created a seperate table with a foreign key
to the table and the date on which it was replaced. I'm looking for an
efficient way to select only the active data.

Currently I use:

SELECT ...
FROM DataTable AS D
LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
WHERE D.Key IS NULL

However I am not convinced that this is the most efficient, or the most
intuitive method of acheiving this.

Can anyone suggest a more efficient way of getting this information
please.

Many thanks.

*** Sent via Developersdex http://www.developersdex.com ***Hi, Brian

I think that you wanted to write "WHERE I.Key IS NULL" (instead of
"WHERE D.Key IS NULL"). In this case, you can use something like this:

SELECT ...
FROM DataTable WHERE Key NOT IN (
SELECT Key FROM InactiveTable
)

Razvan

PS. I assume that the "Key" column does not allow NULL-s.|||On Wed, 15 Feb 2006 12:41:09 GMT, Brian Wotherspoon wrote:

>
>I have a table with data that is refreshed regularly but I still need to
>store the old data. I have created a seperate table with a foreign key
>to the table and the date on which it was replaced. I'm looking for an
>efficient way to select only the active data.
>Currently I use:
>SELECT ...
>FROM DataTable AS D
>LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
>WHERE D.Key IS NULL
>However I am not convinced that this is the most efficient, or the most
>intuitive method of acheiving this.
>Can anyone suggest a more efficient way of getting this information
>please.
>Many thanks.

Hi Brian,

The most intuitive way, IMO, is

SELECT ...
FROM DataTable AS D
WHERE NOT EXISTS
(SELECT *
FROM InActiveTable AS I
WHERE I.Key = D.Key)

The most efficient is either the above or your own LEFT OUTER JOIN query
(but do change D.Key to I.Key in the IS NOT NULL check!!) - but it'll be
only efficient if the I.Key column is indexed.

--
Hugo Kornelis, SQL Server MVP|||try to bring it up a level.

for instance, you are probably creating a temp table? Perhaps create
the temp table with closer to the data you really need.
if you are looking for only one cusotmer, then only pull that one
customer. or, for a specfiic time period, then only that time period.

also, make sure you have an index on inactivetable.key.

if you knew that inactivetable started at some timeframe for all
records, then you could create a composite index on
inactivedata.timestamp plus key.

would it be worth putting an "inactive data datestamp" at the customer
level? perhaps if you have enough rows.

More DTS Help

Hello,
I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:

Function Main()
'esemail is the validation function
if EsEmail(DTSSource("email")) then
DTSDestination("email") = DTSSource("email")
else
'Here i need HELP!!!!!
end if

Main = DTSTransformStat_OK
End Function

So what should I put in the else statement to transfer the email to another destination table?

Thanks,
A.Hello,

I think you work with the DataPumpTask, aren't you?
If I'm right, then you have to change the destination table in this task when going through the ELSE-path.

For details how to change the properties of a DataPumpTask refer to http://www.sqldts.com/default.aspx?213.

Hope this helps otherwise give me a hint!
Carsten

Originally posted by ArgenSQL
Hello,
I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:

Function Main()
'esemail is the validation function
if EsEmail(DTSSource("email")) then
DTSDestination("email") = DTSSource("email")
else
'Here i need HELP!!!!!
end if

Main = DTSTransformStat_OK
End Function

So what should I put in the else statement to transfer the email to another destination table?

Thanks,
A.|||Tambien te puede convenir hacer dos tareas
Una para correos validos y la otra para los no.

Suerte|||Thanks CarstenK, I read that sample, it help a lot but I'm still having a problem I'm getting an error.
This is the code I'm using:

Function Main()
Dim oPkg, oDataPump

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSDataPumpTask_1").CustomTask

if EsEmail(DTSSource("email")) then

oDataPump.DestinationObjectName = "dbo.ValidEmail"
DTSDestination("email") = DTSSource("email")

else

oDataPump.DestinationObjectName = "dbo.NonValidEmail"
DTSDestination("email") = DTSSource("email")

end if

Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTransformStat_OK
End Function

but I'm getting this error "Task 'DTSStep_DTSDataPumpTask_1' not found"

In the link you gave me they explain how to get the name, I did it but still the same error, and I'm sure the name is correct (copy & paste).

Maybe I'm doing something wrong, I'm using this ActiveX code in the "Transform Data Task Properties", "transformations" tab, in the arrow connecting the source column with the destination column. I think there is where the script should go...
well... if anyone can help me with this would be great!

fhnth: hacer dos tareas no seria practico por que tendria que recorrer la tabla dos veces.

Bye,
A.|||Originally posted by ArgenSQL
Hello,
I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:

Could you import to a staging table and use T-SQL to move the records into the correct destination table?|||Originally posted by RogerWilco
Could you import to a staging table and use T-SQL to move the records into the correct destination table?

I could but I don't want to do that, what I want to do is a dts package that do all the work in one (or few) steps. I hope someone with experience on that could help me...
DTS have all the functionality in the world, I want to learn how to use it so I can do things in the best way and using all the technology I have access to.

Bye,
A.|||Hello !

After reading your reply a third time, I think I do know your problem. You said
Maybe I'm doing something wrong, I'm using this ActiveX code in the "Transform Data Task Properties", "transformations" tab, in the arrow connecting the source column with the destination column.

I think you should create an ActiveXTask, that contains your code and runs before the actual data import and adjusts the properties.

I hope that brings you a step further!

Greetings,
Carsten|||I am not sure whether this can be achieved in a single DTS step (one source (a .csv file) and 2 destinations (2 diff. tables).

You can make a 2 step DTS package>
1). 1st Step:
source : .csv file
dest : 1st table
ActiveX :
If email1 = email2
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if

2). 2nd Step:
source : .csv file
dest : 2nd table
ActiveX :
If email1 = email2
Main = DTSTransformStat_SkipRow
else
Main = DTSTransformStat_OK
end if|||Hi!

My thoghts are going this way:

Create an ActiveX-Task which determines whether an email is valid or not and then adjusts the appropriate destination for the following import task.
By taking this way, you can avoid processing an email twice!

Carsten|||Originally posted by CarstenK
Create an ActiveX-Task which determines whether an email is valid or not and then adjusts the appropriate destination for the following import task.

I can't make it work, I tryed everyway and still getting different errors, if I put the ActiveX code before the data pump, I don't get an error by detecting the task name, but I get an error because I ask for the email in the DTSsource and that doesn't happen until I get in the data part, so I can assign a different destination table but not commanded by the email type. I can't find a solution for this I search the web a lot but couldn't find a solution without passing two times trought the table, so I'm doing that I don't like it but I have to finish this anyway, if anyone finds the way to do this, please tell me.

Bye, Thanks all for your help.sql

More browser support for Safari and Firefox?

When I try to run a simple table report using Safari or Firefox the experience is less than stellar.

Using Safari the report never returns, basically the pag body is blank.

Using Firefox the table layout is very compressed.

Anything changes in the works to allow the reporting server to be used by non IE browser.

We run in a multi-platform environment and need the multiple browser support.

Thanks.

--sean

SQL Server 2005 Reporting Services

Hi sean

try adding this into ReportingServices.css file ( in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles)

/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}

After adding this

Some changes has to be done in report as well

Add the table into Rectangle

Deploy the report.

Hope this solves the problem..

safari browser Not sure even i am facing lots of problem

if u find any solution please let me know

More browser support for Safari and Firefox?

When I try to run a simple table report using Safari or Firefox the experience is less than stellar.

Using Safari the report never returns, basically the pag body is blank.

Using Firefox the table layout is very compressed.

Anything changes in the works to allow the reporting server to be used by non IE browser.

We run in a multi-platform environment and need the multiple browser support.

Thanks.

--sean

SQL Server 2005 Reporting Services

Hi sean

try adding this into ReportingServices.css file ( in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles)

/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}

After adding this

Some changes has to be done in report as well

Add the table into Rectangle

Deploy the report.

Hope this solves the problem..

safari browser Not sure even i am facing lots of problem

if u find any solution please let me know

Month-Wise Report.

Hi All,
I have a table with the following structure
EMP_ID VARCHAR(5)
START_DATE DATETIME
END_DATE DATETIME
NO_OF_DAYS INT
This table tracks the leave taken by employees.
Say, I have a record like this
emp_id start_date end_date no_of_days
---
10005 Jan 26, 2005 Feb 02, 2005 8 (total
number of days on leave inclusive of start_date and end_date)
I want a report like this
10005 Jan - 6 Days Feb - 2 Days
Is there any way I can split a date range like this using SQL?
Thanks in advance...
Raju JosephTry this and let me know if it does what you require:
-- BEGIN SCRIPT
declare @.tbl table
(emp_id varchar(5)
, start_date datetime
, end_date datetime
, no_of_days int
)
insert into @.tbl
values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
insert into @.tbl
values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
select emp_id
, substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
day(start_date)+1) vacation
, substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
day(end_date))
, no_of_days
from @.tbl
where month(start_date)< month(end_date)
-- END SCRIPT
"Raju Joseph" wrote:

> Hi All,
> I have a table with the following structure
> EMP_ID VARCHAR(5)
> START_DATE DATETIME
> END_DATE DATETIME
> NO_OF_DAYS INT
> This table tracks the leave taken by employees.
> Say, I have a record like this
> emp_id start_date end_date no_of_day
s
> ---
> 10005 Jan 26, 2005 Feb 02, 2005 8 (total
> number of days on leave inclusive of start_date and end_date)
> I want a report like this
> 10005 Jan - 6 Days Feb - 2 Days
> Is there any way I can split a date range like this using SQL?
> Thanks in advance...
> Raju Joseph
>
>|||That's what I need.
Thanks a lot Valdez
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi Valdez,
Is there any way I can make the sql more generic
ie. if an employee has been on leave for say more than 2 months (Jun 23,
2005 to Aug 5, 2005), I would like all the 3 months to show up. Can I do
this using just a single sql?
Thanks in advance
Raju Joseph
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi There,
You can have one calendar table which will store all the dates from say
'1-jan-2000' to '31-dec-2009'
then your query would become as easy as this
Select Month(caldate),Year(caldate),count(*) Days From Calendar C ,
(Select * from yourtable where empid=@.empid) EMP Where caldate between
EMP.startdate and
emp.enddate group by Year(caldate),Month(caldate)
With Warm regards
Jatinder Singh|||Sorry to ask again.
Any way to display the result like this
emp_id Jan Feb Mar Apr
.... Dec
----
--
101 2 1
102 3
.
.
.
Thanks in advance...
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi there,
I am bit worried abut the leave extending from one year to another like
'31-Dec' to '4-Jan' .How would you like to show this '
If it is OK to ignore Year then You can try this .
Select empid,
Count(Case When Month(caldate) = 1 Then 1 else null ENd ) Jan,
Count(Case When Month(caldate) = 2 Then 1 else null ENd ) Feb,... And
SoON
>From Emp,calendar where caldate between startdate and enddate
Group by empid;
With Warm regards
Jatinder Singh|||Thanks a lot Jatinder and Valdez...
Coola Boola...
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1144239010.621694.145890@.u72g2000cwu.googlegroups.com...
> Hi there,
> I am bit worried abut the leave extending from one year to another like
> '31-Dec' to '4-Jan' .How would you like to show this '
> If it is OK to ignore Year then You can try this .
> Select empid,
> Count(Case When Month(caldate) = 1 Then 1 else null ENd ) Jan,
> Count(Case When Month(caldate) = 2 Then 1 else null ENd ) Feb,... And
> SoON
> Group by empid;
> With Warm regards
> Jatinder Singh
>

Months appear in alphabetical order in Excel 2003 pivot table

I have a SSAS 2005 cube attached to a pivot table in Excel 2003. When the months are dragged to the column area, they appear in alphabetical order (Apr, Aug, etc) instead of Jan, Feb, Mar.

The same cube displays the months properly in Visual Studio.

I know I can re-arrange the columns manual but that sort of ruins the point of OLAP if you have to do that every time.

Any pointers would be appreciated.

Make sure you have set the OrderBy property to Key (assuming you have a numeric key column for the month) and set the Type property to Months on the attribute and Time on the dimension. Setting the Type properties will also enable date range filters in Excel 2007 on an attribute of type Date.|||

Thanks for the response.

Unfortunately, all of the properties were already set as you ask.

One thing I am unclear on is the meaning of the term "key" in the OrderBy property. How do I know if the attribute has a numeric key?

Also, this is Excel 2003, not 2007.

|||

The answer is to create another attribute containing the values 1 - 12 (representing the various months) and set the Month variable OrderBy property to AttributeKey and OrderByAttribute to contain the name of the new attribute (you will have to relate the new attribute to the month attribute by expanding the month attribute and dragging the new attribute to <new attribute relationship> under the month attribute.

Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.

|||

john shahan wrote:

Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.

It sounds like you are using the string of the month name as the attribute key, as such SSAS is just sorting them alphabetically. If you had set your key column to be an actual date or a number it would have sorted using that. (the name and key don't have to be the same column). SSAS works with multiple languages so it will not automatically true to parse month names and sort them in a date order.