Role permissions disappear

I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?First, you don't add tables to roles...you grant permissions to users on objects.

Second, it sounds like you're either recreating the tables every night (unlikely), or youre doing a restore...

which is it...

There are no miracles...|||I think you hit the nail on the head. Most if not all of these tables are dropped and recreated every night. Doh!!! Sorry, I'm stumbling through this. Our DBA up and quit out of the blue and this got dumped in my lap.|||No sweat...set this up as a stored procedure, then schedule it as the last step of your nightly batch job..

just change PUBLIC to whatever role yo have...

DECLARE myGrants99 CURSOR FOR
SELECT 'GRANT SELECT ON [' + TABLE_NAME + '] TO PUBLIC '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN myGrants99

DECLARE @.SQL varchar(8000)

FETCH NEXT FROM myGrants99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC(@.SQL)
FETCH NEXT FROM myGrants99 INTO @.SQL
END

CLOSE myGrants99
DEALLOCATE myGrants99|||Thanks for this. It'll help tremendously. I'll give this to the guys that created the DTS packages to do the loads.|||OK, Now I have a silly question...

Why are they dropping the tables?

You'd be better off if they TRUNCATE the tables...

alos I'm assuming this is not an OTLP (going out on a limb, eh) database, ratyher a reporting/ warehouse...right?

Is there any RI?

Do you work in the Northeast US?

I work cheap...

:D|||Don't know why they're dropping and not truncating. I will suggest that to the developers. And this is a reporting warehouse for Crystal. Sorry, but working for a major telecommunications company doesn't allow me to contract outside assistance. OJT is our main source of training. Sounds really stupid doesn't it?|||Naaaahhhh

Jump in to the pool...deep end...feet first...keep your head above water...
Also, Go out and buy (and read) some good books...

Check out:

http://www.sqlteam.com/store.asp

EDIT: Oh, and keeping coming back to here or sql team...|||I'll definitely keep coming back. The response has been great. This is the second question I've posted here and both have been answered quickly.

Thanks again.

Role design issue

I have a strange roles problem.

Is there a way we can accomplish this:

Say, the fact table has 100 records. each record having a new account number (100 accounts in 100 records).

The problem is we have users who have to see only some accounts. For example, user 1 has to see only accounts 1-10, user 2 has to see only accounts 11-20, user 3 has to see only accounts 21-30. and so on...

I know we can create different roles and create perspectives for these roles, but the problem is the list that defines what they can see is a table of 1000 user entries, each row has its own conditions for each user type. (Actually, each condition is a combination of different dimensions. For example, user 1 has to see account 1-10 and products A-D)

Does anyone have any idea on how to do this?

Thanks in advance

Luckily the problem you describe is very common. So common it has it's own name, it is oftenly referred as "Dynamic Dimension Security".

There are quite a few articles about it. Here is one for you http://sqljunkies.com/WebLog/mosha/archive/2004/12/16/5605.aspx

Search for it, you will find quite a bit information.

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

|||Thanks Edward, I looked at it and I think thats what am looking for. will come back for your help if I cant figure something out :-)

Reverse Sign for Display

In Crystal Reports, there was a "reverse sign for display" property that could be checked for a number box. This was used for debits and credits (and such) so a negative number would be displayed as a positive and a positive displayed as a negative.

How would this work, or is there something simple, in Reporting Services?

Thanks for the information.

Just a simple way to implement this is =iif(fields!abc.value<0, abs(fields!abc.value), - & fields!abc.value)

|||

Use something like this for the value of the field:

=Fields!myField.Value * -1

That seems to simple. Is that what you're asking for?

|||Thank you both for your help. I knew it had to be a simple answer.

reverse order of hex bytes

is there a function in sql server to select the REVERSE order of a 32bit hex
value?
then i also need to convert each byte to a decimal number.
any help is much appreciated..
tia,
jtCan you post an example of what you are trying to accomplish?
If I understood your question right, it is not too hard to write a simple
function to change the order of hexdigits. In SQL 2000, there is an
undocumented proc xp_varbintohexstr, which you can use in a procedural loop
& get this done as well.
Anith|||Take a look at this example:
http://milambda.blogspot.com/2005/0...a.blogspot.com/|||>> is there a function in SQL Sserver to select the REVERSE order of a 32-bi
t hex value? <<
Why are you doing low level bit manipulation in SQL? This is like
driving screws wirh a brick. What are you trying to do? What is your
data model? Surely it is NOT at that physical level!!|||The benefit of a database is not just for storage but its ability todo
efficient data manipulation.
You seem to be stuck in the 70's store and retreieve model, client/server
techniques have passed you by!
I have a client who uses SQL Server to store GBytes of technical data and
requires the ability to do conversion like this to produce taylor data
exports for his own clients; the question - does he program the export in a
3gl or more easily using T-SQL (what its designed for) and binary
manipulation on the SELECT statement?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137469420.061461.205740@.g47g2000cwa.googlegroups.com...
> Why are you doing low level bit manipulation in SQL? This is like
> driving screws wirh a brick. What are you trying to do? What is your
> data model? Surely it is NOT at that physical level!!
>

Reverse Bill Of Materials Query?

Hello!
We have a bill of materials table with a classic parent/child relationships.
What I need to be able to do is to take a specific part number and return
the highest level parent for that part number.
For instance, If part number A is used as a component in part number B, and
part number B is then used in another component called C, then the highest
level parent for part number A is C.
so the result of the query when ran against part number A would be C
How can I set up such a query?
Thanks
JoeBelow is a solution using a user-defined function. If this
is a frequent requirement, you may want to consider alternate
ways of modeling your hierarchy. If you search groups.google.co.uk
or www.google.com for hierarchy+itzik+sqlserver you'll find some nice
ideas.
-- Original thread at http://groups.google.co.uk/groups?q=A9B05D_C5E784
CREATE TABLE Employee (
pk int not null primary key,
parent int
)
go
INSERT INTO Employee VALUES (1,NULL)
INSERT INTO Employee VALUES (2,NULL)
INSERT INTO Employee VALUES (3,1)
INSERT INTO Employee VALUES (4,2)
INSERT INTO Employee VALUES (5,4)
go
CREATE FUNCTION rootPK(
@.pk INT
) RETURNS INT
AS
BEGIN
DECLARE @.parent INT, @.this INT
SET @.this = NULL
SET @.parent = @.pk
WHILE @.parent IS NOT NULL BEGIN
SELECT
@.this
= pk
, @.parent
= parent
FROM
Employee
WHERE
pk
= @.parent
END
RETURN @.this
END
go
ALTER TABLE Employee ADD rootPK as dbo.rootPK(pk)
go
SELECT * FROM Employee
go
-- drop table Employee
-- drop function dbo.rootPK
-- Steve Kass
-- Drew University
Joe Williams wrote:

> Hello!
> We have a bill of materials table with a classic parent/child relationship
s.
> What I need to be able to do is to take a specific part number and return
> the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B, an
d
> part number B is then used in another component called C, then the highest
> level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>|||There is also a good example at:
http://msdn.microsoft.com/library/d...r />
_5yk3.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe Williams" <Joe@.anywhere.com> schrieb im Newsbeitrag
news:%234YOY0jWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hello!
> We have a bill of materials table with a classic parent/child
> relationships. What I need to be able to do is to take a specific part
> number and return the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B,
> and part number B is then used in another component called C, then the
> highest level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>

Reusing package configuration in child packages

I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.

Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?

Sachin

I have found that one file per connection is ultimately the best way to go. You may be able to have more than that, if you are sure you will always use the same configurations at the same time, but that seems to be more useful when you have some variables, perhaps a couple of file paths used for all of your packages to do logging or check pointing, but you need to be careful of not falling into the same trap again of over grouping. Keep it at the lowest level you can, it is just more flexible I have found, and copes better with change as well. (Deleted Post?)

|||

Darren

Looks like that might very well be the way to go then. I wonder if there is any way to include other config files into a single file, but that's for another day.

Thanks for the prompt reply.

Sachin

|||

Nice idea, support for XML Inclusions (XInclude) would do it (http://www.w3.org/TR/xinclude/).

One for MS Connect I think - http://connect.microsoft.com

Reuse of report elements

I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.

We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.

What is the best way to approach this?

My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.

My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination

If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!

You can't link common bits into a report unfortunately. IMO your best option is to keep using a template report as your base. Because reports are deployed to a RS server, you can't just update a file and expect all the reports to pick it up, the change has to be added to the reports and then they should be redeployed. I do believe there are ways to change the *styles* of already deployed reports, there are some css files you can tweak under the Reports and ReportServer virtual dirs in IIS.

You just have to make sure that whoever orders the changes knows the true cost of implementing it.

|||

Well, the subreport method got us most of the way there, but we had to do things that way.

The only part that I couldn't figure out was the ability to specify the report source for the subreport as a parameter or field. That way we could share the base report across our projects (using sourcesafe) and changes to it would propagate to all the reports (yes, we'd still have to redeploy, but the issue we're trying to avoid is the need to manually edit things, where a change could be forgotten on one report and introduce a bug.)

ReturnProviderSpecificTypes

Is it possible to set this in Analysis Services 2005?

I am going against an oracle number and Analysis Services is picking Double as the datatype but it is not working?

I am getting the following as the error- 'Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: Arithmetic operation resulted in an overflow..'

If I cast the column to number(28) it works which is the size of system.decimal

Moved to SQL Server Analysis Services forum.

For AS group -- ReturnProviderSpecificTypes is a setting on the DataAdapter that allows the DataReader to return column types that more closely align with the underlying backend types, rather than standard CLR types. For example, OracleNumber instead of Decimal or Double.

DataAdapter.ReturnProviderSpecificTypes Property
http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.returnproviderspecifictypes(d=ide).aspx

Thanks,
Sarah

|||Is there anything i can do in analysis services to accept the number(38) that oracle is sending instead of casting to number(28)?|||

What provider you are using to connect to Oracle?

You have 2 options: Microsoft OLEDB provider and .NET native Oracle client.

If you are using one, try the second option, see if it works for you.

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

returning unique data

I have a table with 3 columns: number, name, rowid (and identity column). I
want to return the name of the first row (first meaning lowest rowid) when
there are more than one row with the same number. Given the table looks
like this:
number name rowid
102 bob 1
102 allen 2
104 carl 3
104 mike 4
I want only bob and carl to be returned.
Can anyone point me in the right direction?
Tx for any help.
Bernie YaegerYou're looking for a correlated subquery.
CREATE TABLE #foo
(
rowid INT IDENTITY(1,1),
[name] VARCHAR(12),
[number] INT
)
SET NOCOUNT ON
INSERT #foo([name], [number]) SELECT 'bob', 102
INSERT #foo([name], [number]) SELECT 'allen', 102
INSERT #foo([name], [number]) SELECT 'carl', 104
INSERT #foo([name], [number]) SELECT 'mike', 104
SELECT [name],[number],rowid
FROM #foo f1
WHERE rowid = (SELECT MIN(rowid)
FROM #foo f2
WHERE f2.[number] = f1.[number])
ORDER BY [number]
DROP TABLE #foo
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>I have a table with 3 columns: number, name, rowid (and identity column).
>I want to return the name of the first row (first meaning lowest rowid)
>when there are more than one row with the same number. Given the table
>looks like this:
> number name rowid
> 102 bob 1
> 102 allen 2
> 104 carl 3
> 104 mike 4
> I want only bob and carl to be returned.
> Can anyone point me in the right direction?
> Tx for any help.
> Bernie Yaeger
>|||Bernie
CREATE TABLE #Test
(
[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
number INT NOT NULL,
[name]CHAR(1)NOT NULL,
rowid INT NOT NULL
)
INSERT INTO #Test(number,[name],rowid) VALUES (102,'A',2)
INSERT INTO #Test (number,[name],rowid)VALUES (102,'B',1)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'C',1)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'D',2)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'A',3)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'B',4)
INSERT INTO #Test(number,[name],rowid) VALUES (102,'T',0)
INSERT INTO #Test(number,[name],rowid) VALUES (104,'a',2)
SELECT [name]FROM #Test
WHERE rowid=(SELECT MIN(rowid) FROM #Test T WHERE T.number=#Test.number)
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>I have a table with 3 columns: number, name, rowid (and identity column).
>I want to return the name of the first row (first meaning lowest rowid)
>when there are more than one row with the same number. Given the table
>looks like this:
> number name rowid
> 102 bob 1
> 102 allen 2
> 104 carl 3
> 104 mike 4
> I want only bob and carl to be returned.
> Can anyone point me in the right direction?
> Tx for any help.
> Bernie Yaeger
>|||Hi Uri,
Tx for your help Uri.
Bernie
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eLVoxKmkFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Bernie
> CREATE TABLE #Test
> (
> [ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> number INT NOT NULL,
> [name]CHAR(1)NOT NULL,
> rowid INT NOT NULL
> )
> INSERT INTO #Test(number,[name],rowid) VALUES (102,'A',2)
> INSERT INTO #Test (number,[name],rowid)VALUES (102,'B',1)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'C',1)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'D',2)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'A',3)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'B',4)
> INSERT INTO #Test(number,[name],rowid) VALUES (102,'T',0)
> INSERT INTO #Test(number,[name],rowid) VALUES (104,'a',2)
>
> SELECT [name]FROM #Test
> WHERE rowid=(SELECT MIN(rowid) FROM #Test T WHERE T.number=#Test.number)
>
>
> "Bernie Yaeger" <berniey@.optonline.net> wrote in message
> news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>|||Hi Aaron,
Tx so much for your help.
Bernie
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ORACDEmkFHA.708@.TK2MSFTNGP10.phx.gbl...
> You're looking for a correlated subquery.
>
> CREATE TABLE #foo
> (
> rowid INT IDENTITY(1,1),
> [name] VARCHAR(12),
> [number] INT
> )
> SET NOCOUNT ON
> INSERT #foo([name], [number]) SELECT 'bob', 102
> INSERT #foo([name], [number]) SELECT 'allen', 102
> INSERT #foo([name], [number]) SELECT 'carl', 104
> INSERT #foo([name], [number]) SELECT 'mike', 104
> SELECT [name],[number],rowid
> FROM #foo f1
> WHERE rowid = (SELECT MIN(rowid)
> FROM #foo f2
> WHERE f2.[number] = f1.[number])
> ORDER BY [number]
> DROP TABLE #foo
>
>
> "Bernie Yaeger" <berniey@.optonline.net> wrote in message
> news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>

returning the number of rows from TOP PERCENT

Greetings.

I want to write a sql query that returns the number of rows from a query that uses top percent. For example,

select top (7) percent object from objecttable

It appears that @.@.rowcount will give the info but it does so as a separate column on each returned row.

Any suggestions would be appreciated.

Thanks.

alan

Try this

select top (7) percent object , count(1) over (partition by 1)

from objecttable order by somecolumn

You are using the new window aggregate ability.

Remember you should always use an order by with TOP

|||

Thanks for the quick reply.

I tried

select top (7) percent objectid , count(1) over (partition by 1)

from bases order by state

and got

(objectid) (no column name)

16 85
19 85
23 85
85 85
38 85
34 85

I'm not sure what '85' represents. When I select and use @.@.rowcount I get '6' in each row. All I really want to do is return '6'. I figure I missed something.

alan

|||@.@.ROWCOUNT gives the number of rows affected by the last statement. So when you use it in the SELECT list you are returning the rows that were affected by the statement before the SELECT. It is not the same as the rows that will be returned by the query. Best is to issue another query "SELECT @.@.ROWCOUNT as rows" after the query or count on the client-side. This will provide the best performance. You could use COUNT with OVER clause but note that has performance implications due to additional computation, sorting etc.|||

This will return the single value 6 (Given your table holds 85 rows)

WITH t1 AS
(SELECT TOP(7) PERCENT * FROM test)
SELECT COUNT(*) FROM t1;

Or this one:

SELECT COUNT(*)
FROM (SELECT TOP(7) PERCENT * FROM test) as t1;

The Execution Plan for Both Queries is the same.

|||Works great. Thanks !|||Works for me. Cool ! Thanks.|||

Or just

select count(1) *7/100

from mytable

|||

Sorry, but the result is not the same (Given the table test holds 85 rows):

SELECT COUNT(1) *7/100 FROM test;

Returns 5

SELECT COUNT(*)
FROM (SELECT TOP(7) PERCENT * FROM test) as t1;

Returns 6

To do it like your hint, the formula must be

SELECT CAST(ROUND(COUNT(1) * 7./100, 1) AS INT) FROM test;

Which returns 6.

There's no performance difference, but i'll prefer the solution which is the easiest to understand.

|||

The answers are different because the TOP will return whole rows i.e. rounds up.

So the answer is something like 5.3

Returning the newest rows

I have a query set up that returns the data that I would like, but I
would only like the latest data for each vehicle number. The query I
have set up is

SELECT TOP 100 PERCENT dbo.vwEvents.EventName,
dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,
dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,
dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTime
FROM dbo.vwSessions INNER JOIN dbo.vwEvents ON
dbo.vwSessions.Event = dbo.vwEvents.EventID
INNER JOIN
dbo.luSessionAll ON dbo.vwEvents.EventID =
dbo.luSessionAll.Event INNER JOIN
dbo.luOuting ON dbo.luSessionAll.SessionID =
dbo.luOuting.SessionID INNER JOIN
dbo.luVehicle ON dbo.luSessionAll.Vehicle =
dbo.luVehicle.VehicleID INNER JOIN
dbo.tblOutings ON dbo.luOuting.OutingID =
dbo.tblOutings.OutingID INNER JOIN
dbo.tblSessions ON dbo.tblOutings.[Session] =
dbo.tblSessions.SessionID
GROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,
dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,
dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,
dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDate
ORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,
dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTime

this returns all the outings. I would like the outing that has, in
order of importance, the latest session date, latest session time and
latest outing start time. Outing start time can sometimes be <<Null>>
but the other two always have values. How would I go about doing this?
thanks in advance for any help(pltaylor3@.gmail.com) writes:
> I have a query set up that returns the data that I would like, but I
> would only like the latest data for each vehicle number. The query I
> have set up is
> SELECT TOP 100 PERCENT dbo.vwEvents.EventName,
>...
> ORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,
> dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTime

I don't have the time to look into the problem as such, but I feel
obliged to point out that the above looks dubious.

TOP 100 PERCENT does not make any sense at all, that is just white noice,
so I suggest that you remove.

At this point, I am not surprised if you say that this in fact a view
definition, and you need the TOP 100 for the ORDER BY to be permitted.
Well, it is still white noise. In SQL 2000 a SELECT from the view is
very like to return rows in the order set up by the ORDER BY clause, but
that is just mere chance. In SQL 2005 this is far likely and more than one
has been bitten by this.

The only way to get an ordered result from a query is to include an ORDER
BY clause in the query itself. You cannot use views to encapsulate order.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(pltaylor3@.gmail.com) writes:
> I have a query set up that returns the data that I would like, but I
> would only like the latest data for each vehicle number. The query I
> have set up is
>...
> this returns all the outings. I would like the outing that has, in
> order of importance, the latest session date, latest session time and
> latest outing start time. Outing start time can sometimes be <<Null>>
> but the other two always have values. How would I go about doing this?
> thanks in advance for any help

Without know what is what in the tables, I will have to guess a bit.
Maybe this is what you are looking for. And if it's not, maybe it's
enough to get you going:

SELECT E.EventName, SA.SessionName, O.OutingNumber,
V.VehicleName, O.OutingID, Os.OutingStartTime,
S.SessionDate,S.SessionStartTime
FROM dbo.vwSessions Ss
JOIN dbo.vwEvents E ON Ss.Event = E.EventID
JOIN dbo.luSessionAll SA ON E.EventID = SA.Event
JOIN dbo.luOuting O ON SA.SessionID = O.SessionID
JOIN dbo.luVehicle V ON SA.Vehicle = V.VehicleID
JOIN dbo.tblOutings Os ON O.OutingID =
(SELECT TOP 1 Os.OutingID
FROM dbo.tblOutings Os1
JOIN dbo.tblSesseions S1 ON
Os.[Session] = S.SessionID
ORDER BY S1.SessionDate DESC,
S1.SessionSessionStartTime DESC,
Os1.OutingStartTime DESC)
JOIN dbo.tblSessions S ON Os.[Session] = S.SessionID
ORDER BY V.VehicleName, S.SessionDate, S.SessionStartTime,
Os.OutingStartTime

I replaced the table/view names with alias to make the queries easier
to read.

I also did away with the GROUP BY that did not seem to serve any
purporse. Maybe it's a DISTINCT you need. Then again, if you need a
DISTINCT this is an indication that the query is lacking a condition
somewhere.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

returning the Identity of the auto number column

Hi ,,

How to write the Sql Query to return the next generated Identity from the Sql server database.Which version of SQL Server are you using?

If <= 7.0 - have a look at @.@.identity
If 2000 - have a look at SCOPE_IDENTITY

Returning table from UDF

Thank you David
My problem is the following:
I have certain tables to which apart from a standard number of fields
additional fields are added depending on customer requirements. A "Select *
From Table_A" statement returns all fields but because there are several
versions of the same record in the table some quite elaborate filtering
(based on current criteria) is needed to get a single record. This results
in cubersome queries. So I was wondering if I could write a UDF which would
"hide" all the complication and call it as "Select * From
UDF_A(param1,param2,..)"?
What I already have done is write a UDF returning as a table the Ids of the
records satisfying the criteria and then using that to filter the original
table ie
"Select * From Table_A where Table_A.Id IN (Select Id From UDF_X(params))"
but it is still a litle complicated for "report writers" who don't know the
internals.
Yannis
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:nbidnZWNSYMQ_2zcRVn-jA@.giganews.com...
> No. Stored procedures can return variable numbers of columns though.
That's
> not to say that dynamic result sets from an SP is necessarily a good way
to
> accomplish things. Maybe if you describe your problem we can suggest a
> solution.
> --
> David Portas
> SQL Server MVP
> --
>A UDF has only one returned set. If varying return sets are needed,
multiple UDF's will have to be written. If additional fields are added to
the table in the future, those fields will have to be added to any UDF's for
the fields to appear in their return sets.
If you have a very limited param set, you can get away with making a view
for each one:
CREATE VIEW View_1 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(1))
CREATE VIEW View_2 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(2))
CREATE VIEW View_3 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(3))
These views won't need modifying as the fields are added. They are very
simple for your report writers:
SELECT * FROM View_2
If you don't have a limited param set, you could try stored proc's...
CREATE PROC ShowTableA @.param1 int, @.param2 varchar(10)
AS
Select * From Table_A where Table_A.Id IN (Select Id From UDF_X(@.param1,
@.param2))
RETURN
Called by:
EXEC ShowTableA 22,'blue'
The downside here is that to capture the result in (for example) query
analyzer, you have to jump through some hoops (one of the hoops is knowing
the number and datatypes of the columns in the returned set)... maybe your
report writers are using some other tool where they don't issue sql against
the result set to do further modification? If so, this is a good choice.
"Y.Makarounis" <ymak@.otenet.gr> wrote in message
news:ONWYyJGAFHA.1908@.TK2MSFTNGP15.phx.gbl...
> Thank you David
> My problem is the following:
> I have certain tables to which apart from a standard number of fields
> additional fields are added depending on customer requirements. A "Select
*
> From Table_A" statement returns all fields but because there are
several
> versions of the same record in the table some quite elaborate filtering
> (based on current criteria) is needed to get a single record. This results
> in cubersome queries. So I was wondering if I could write a UDF which
would
> "hide" all the complication and call it as "Select * From
> UDF_A(param1,param2,..)"?
> What I already have done is write a UDF returning as a table the Ids of
the
> records satisfying the criteria and then using that to filter the original
> table ie
> "Select * From Table_A where Table_A.Id IN (Select Id From
UDF_X(params))"
> but it is still a litle complicated for "report writers" who don't know
the
> internals.
> Yannis
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:nbidnZWNSYMQ_2zcRVn-jA@.giganews.com...
> That's
> to
>

Returning SSIS stored procedure name

Hi

I am currently trying to write a number of processe's to keep track of what information is held in my SSIS package. The package I have created is rather large and it would prove a long labourious process to look through every task to see what stored procedure has been used.

What I wanted to do was write a stored procedure in SQL Server 2005 that pick's up each package name and checks for any stored procedures used and returns the names of these stored procedures and any other relevant information i.e required variables.

So far I have managed to create a stored procedure that picks up the name(s) of the packages but I am stuck after this.

Declare @.Filename varchar(1000)
Declare @.cmd varchar(1000)

Create table #dir (Filename varchar(1000))
Insert #dir
Exec master..xp_cmdshell 'dir /B C:\Development\SumColumn\SumColumn\*.dtsx'
delete #dir where Filename is null or Filename like '%not found%'
Select @.Filename = ''
While @.Filename < (select max(Filename) from #dir)

drop table #dir

Any help would be appreciated.

Thanks

Hi,

you can use a "SQL profiler" trace to trace whats happening in your server when the package is run.

anyway here's something that might help you. dtsx file of SSIS is stored in XML format

you can open the file with notepad and figure out from there. you can use the notepad "find" feature

if you have some XML background

or data processing skill you might be able to extract the information you need.

you may also use the Sql server 2005 XML datatype to do that.

regards

,joey

|||

Using the SSIS API, it's generally possible to load a package, find all the instances of some specific tasks, and retrieve the values of some of their properties. At the time that you wrote the code, you'd need to determine which types of tasks you wanted to scan. For each of these types, you'd need to write code to intelligently parse the values stored in the properties of the task for the name of the stored procedure. You can read up on the SSIS API, here: http://msdn2.microsoft.com/en-us/library/ms136025.aspx

In particular, you'll probably want to take a look at:

http://msdn2.microsoft.com/en-us/library/ms136090.aspx

http://msdn2.microsoft.com/en-us/library/ms135956.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.executesqltask.executesqltask.aspx

While it might be possible to scan through the xml, that approach would be unsupported.

Returning numbers formatted as a percent

I have a stored procedure that feeds a graph in an access ADP, and the
numbers are being sent as 0.883838.... can i format the number being
returned from the stroed procedure to a percent? (ie 88.38%)? and also, how
do i limit the number of decimal places? the columns being used in the
calculations are of type decimal.
thanksSELECT CONVERT(DECIMAL(5,2), 100.0*0.883838)
(You should add the % sign elsewhere, however, IMHO.)
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:72D396F4-78AD-4AA5-932C-F998ECEB61D7@.microsoft.com...
>I have a stored procedure that feeds a graph in an access ADP, and the
> numbers are being sent as 0.883838.... can i format the number being
> returned from the stroed procedure to a percent? (ie 88.38%)? and also,
> how
> do i limit the number of decimal places? the columns being used in the
> calculations are of type decimal.
> thanks
>|||Ben,
If you are adding the % symbol via t-sql for ad-hoc reports, you could try:
select convert(varchar(20),convert(decimal(8,2)
,column*100)) + '%' from
table
HTH
Jerry
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:72D396F4-78AD-4AA5-932C-F998ECEB61D7@.microsoft.com...
>I have a stored procedure that feeds a graph in an access ADP, and the
> numbers are being sent as 0.883838.... can i format the number being
> returned from the stroed procedure to a percent? (ie 88.38%)? and also,
> how
> do i limit the number of decimal places? the columns being used in the
> calculations are of type decimal.
> thanks
>|||Thank you both for your quick responses! I will give them a try as soon as
I
can.
thanks again!
"Jerry Spivey" wrote:

> Ben,
> If you are adding the % symbol via t-sql for ad-hoc reports, you could try
:
> select convert(varchar(20),convert(decimal(8,2)
,column*100)) + '%' from
> table
> HTH
> Jerry
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:72D396F4-78AD-4AA5-932C-F998ECEB61D7@.microsoft.com...
>
>|||If you pre-format the column as a varchar with symbols, then whatever
application is consuming the rowset may have trouble summing, averaging, or
applying their localized formatting.
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:72D396F4-78AD-4AA5-932C-F998ECEB61D7@.microsoft.com...
>I have a stored procedure that feeds a graph in an access ADP, and the
> numbers are being sent as 0.883838.... can i format the number being
> returned from the stroed procedure to a percent? (ie 88.38%)? and also,
> how
> do i limit the number of decimal places? the columns being used in the
> calculations are of type decimal.
> thanks
>

Returning number of tables in SQL Server2000 using ADO in VB6

I'm using a VB6 application to access data from an SQL Server 2000 database. There are a few things I would like to do:

1. Get the number of tables in the database
2. Automatically begin reading data from each table in the database. Is it possible to write some code that will cause the application to start on first table retrieve all the data, then move to next table (similar to moving to next row)?

Thanks,
JoeyIt depends on what version of ADO you are using. However, there is a function called NextRecordset. Honestly I can't imagine why you would ever want to pull so much information at once though?

hth!

Originally posted by joey55
I'm using a VB6 application to access data from an SQL Server 2000 database. There are a few things I would like to do:

1. Get the number of tables in the database
2. Automatically begin reading data from each table in the database. Is it possible to write some code that will cause the application to start on first table retrieve all the data, then move to next table (similar to moving to next row)?

Thanks,
Joey|||Unfortunately I have been assigned to create a database comparison tool in VB6 to test data integrity between original and upgraded databases. How does NextRecordset work? Is it a member of the connection object?

I want to be able to loop through all the tables in the database from first to last withought having to specify them, cause we have thousands of tables.|||It is part of the recordset object. What is the purpose of retrieving all the data from the table - what will you do with it ?|||I will compare it with the data in the same table retrieved from the upgraded database. This needs to be done for each table. and there are thousands of them.

I'm not sure if NextRecord can help me, you have to Open a recordset to one table as far as I know. I need an object outside of that that can return the name of all the tables.|||So are you looking for missing records, differences in column data ... ? Just describe what happened that provoked this comparison. Are you trying to sync the 2 up. Is the existing database (not the upgraded one) considered the master or is the upgraded considered the master - or is it somewhere in between ?|||Ok here is the scenario...

We just released our latest version of our software. Many clients of previous release versions want to upgrade to the newest version. In the new version some changes have been made to the database, so the DBA wrote some scripts to upgrade client databases to the newest version. This is where I come in. We want to make sure that NO data is lost when the upgrade scripts are run. There are thousands of tables and probably hundreds of thousands of records so it is impossible to be manually done.

So I have been assigned the task of creating a tool that will go through each table in both the original and the upgraded database and compare the exising data, to make sure nothing is lost or changed by fault in the upgrade scripts.

My plan is (unless someone suggests something better) to write some code that will start on the first table in the original database, find same table in upgraded one, and make sure all the data is identical in both and if it differs will right the problem to a log file.

What I wamt to know is if there is a way to return all the tables (like a recordset would rows). Maybe from the sysobjects table or something. I'm not used to working with SQL Server 2000 from the development point of view. All suggestions and help welcome.

Thanks,
Joey

Returning more than one of same acct instead of sum for both

Code not combining acct number results.
The code below returns for example:
74981995170968 1595.00 253.54 1341.46 940.40
74981995170968 1595.00 2281.86 3876.86 940.40
It should be:
74981995170968 1595.00 2535.4 1341.36 940.40
above values come from
2281.86 + 253.54 = 2535.4
2535.4 - 1595.00
When I need it to combine the information and it's not because of the CASE statement within my first INNER join where I choose to SUM rmstranamt based on if there are more than one record or not
.
Here's the query:
SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
rf10.rmstranamt10 AS [Sum rmstranamt 10],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) - rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
END
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 <> 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND rf10.rmstranamt10 = 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
INNER JOIN
(
SELECT DISTINCT RMSFILENUM,
CASE WHEN COUNT(*) > 1 THEN
SUM(rmstranamt) as rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
ELSE
rmstranamt as rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
END
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
WHERE rf.rmstrancde IN ('10', '16','18','19','20','21','22','29','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
AND rm.rmsacctnum = '74981995170968'
GROUP BY rm.rmsacctnum, cb.CurrentBalance, rf10.rmstranamt10
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

sorry I'm getting dizzy just looking at that. However, I think the following line might be flawed:
CASE WHEN COUNT(*) > 1 THEN

Basically this will always return true whenever the rfinanl table has more than one row.

Sorry I can't be of more assistance.

|||

finally, I got this figured out:

INNER JOIN
(
SELECT RMSFILENUM,
SUM(distinct rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

returning limited number of records!

I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.

Thanks.Since SELECT * FROM Table ORDER BY NEWID() will return the rows in a random order, all you need to do is use the TOP keyword to limit the results for that particular query.

SELECT TOP 5 * FROM Table
ORDER BY NEWID()

If, for some reason, that doesn't work, there's the slightly less elegant solution of

SET ROWCOUNT = 5
SELECT * FROM Table
ORDER BY NEWID()
SET ROWCOUNT = 0

I hope this helps.|||it's working perfect.

thanks.

Returning How Many Times a Character Occurs in a String

I need to know how to figure out the number of times a character occurs within a string, in SQL.

For example, "happy" concerning "p" would be evaluated to 2.This is generic but you could taylor it:

DECLARE @.MyString NVARCHAR(20)

SET @.MyString = 'Happy'

SELECT LEN(@.MyString) - LEN(REPLACE(@.MyString, 'p', ''))

Basically you are asking for the length of the string (5) minus the length of the string if there were no 'p' characters in it (3). The difference is the number of 'p' characters.

Hope that helps,
Ian|||^ That one is awesome. lol very nice.