Get Ancestor and Descendant in a Hierarchy

For a short time I have considered how to get the hierarchy in a self-linked table, incidentally I have found an article about Recursive CTEs in SQL Server Books Online, where an example is very impressive and arouses me to find a way to get the ancestors or descendants of a given node in a self-linked (self-referenced) table.

And now I want to share this method to anyone who has the same problem to resolve or someone like me who has addiction in SQL.

First of all we have to create a table for the following functions and build some test data. The statemens look like:

create table ST_CATEGORY(
CATEGORYID uniqueidentifier not null default NEWID(),
PARENTID uniqueidentifier,
[NAME] varchar(128),
COMMENT varchar(4096),
CONSTRAINT PK_ST_CATEGORY primary key (CATEGORYID)
)
go

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@.rootoid, NULL, 'ROOT', 'ROOT NODE')

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @.rootoid, 'Business Application', 'group for all business applications')

declare @.techoid uniqueidentifier
set @.techoid = NEWID()

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@.techoid, @.rootoid, 'Tech101', 'technical tips')

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @.techoid, 'C#', 'C# tips')
go

Now that the test table and data are prepared, we can get ancestors or descendants through the following four stored procedures of a given category.

CREATE PROCEDURE spGetAncestor
@.categoryID uniqueidentifier
AS
BEGIN
-- find parents/ancestor
WITH Ancestor( CATEGORYID ) AS
(
SELECT PARENTID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT PARENTID
FROM Ancestor, ST_CATEGORY
WHERE Ancestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM Ancestor
END
GO

CREATE PROCEDURE spGetSelfAndAncestor
@.categoryID uniqueidentifier
AS
BEGIN
-- find self and parents/ancestor
WITH SelfAndAncestor( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT PARENTID
FROM SelfAndAncestor, ST_CATEGORY
WHERE SelfAndAncestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM SelfAndAncestor
END
GO

CREATE PROCEDURE spGetDescendant
@.categoryID uniqueidentifier
AS
BEGIN
-- find children/descendant
WITH Descendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE PARENTID = @.categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM Descendant, ST_CATEGORY
WHERE Descendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM Descendant
END
GO

CREATE PROCEDURE spGetSelfAndDescendant
@.categoryID uniqueidentifier
AS
BEGIN
-- find self and children/descendant
WITH SelfAndDescendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM SelfAndDescendant, ST_CATEGORY
WHERE SelfAndDescendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM SelfAndDescendant
END
GOUhm......OK. Thanks for sharing with us information that is available in Books Online...

get an arithmetic overflow with count(*)

Doing a select count(*) on a table that I believe has more rows than the max
value of an int gives me an arithmetic overflow.
How can I rewrite my query to find the no. of rows ?Hi
Look up COUNT_BIG in Books online.
John
"Hassan" wrote:

> Doing a select count(*) on a table that I believe has more rows than the m
ax
> value of an int gives me an arithmetic overflow.
> How can I rewrite my query to find the no. of rows ?
>
>

Get all unused numbers in range

What I am trying to do is get all of the records in a table that are out of
sequence so I know which account numbers I can reuse. I have a range
of account numbers from 50100 to 70100. I need to know which account
numbers are not stored in the table (not currently used) so I can use.

For instance say I have the following data in table:

Account Name
--- ---
50100 Test1
50105 Test2
50106 Test4
..
..
..
I should see the results:
50101
50102
50103
50104

because 50101-50104 are available account numbers since not currently in
table.

Thanks."Jason" <NO-SPAM-xyz@.msn.com> wrote in message news:525Ka.43802$hI1.7823@.nwrddc01.gnilink.net...
> What I am trying to do is get all of the records in a table that are out of
> sequence so I know which account numbers I can reuse. I have a range
> of account numbers from 50100 to 70100. I need to know which account
> numbers are not stored in the table (not currently used) so I can use.
> For instance say I have the following data in table:
> Account Name
> --- ---
> 50100 Test1
> 50105 Test2
> 50106 Test4
> .
> .
> .
> I should see the results:
> 50101
> 50102
> 50103
> 50104
> because 50101-50104 are available account numbers since not currently in
> table.
> Thanks.

Here's a UDF that will fill in all missing integers between a
lower bound and an upper bound, both inclusive. By default,
the lower bound is 50100 and the upper bound is 70100.

CREATE TABLE Accounts
(
account_id INT NOT NULL PRIMARY KEY,
account_name VARCHAR(10) NOT NULL
)

CREATE TABLE Digits
(
d TINYINT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)

INSERT INTO Digits (d)
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9

CREATE TABLE NonnegativeIntegers
(
n INT NOT NULL PRIMARY KEY CHECK (n >= 0)
)

INSERT INTO NonnegativeIntegers (n)
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d +
1000 * Thousands.d + 10000 * TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands

CREATE FUNCTION IntervalGaps
(@.lower INT = 50100, @.upper INT = 70100)
RETURNS @.missing TABLE (n INT NOT NULL PRIMARY KEY)
AS
BEGIN
IF @.lower > @.upper
RETURN
INSERT INTO @.missing (n)
SELECT @.lower + I.n
FROM (SELECT MIN(account_id)
FROM Accounts) AS A(least_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < A.least_id - @.lower
INSERT INTO @.missing (n)
SELECT A1.account_id + I.n + 1
FROM Accounts AS A1
INNER JOIN
Accounts AS A2
ON A2.account_id > A1.account_id + 1 AND
NOT EXISTS (SELECT *
FROM Accounts
WHERE account_id > A1.account_id AND
account_id < A2.account_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < A2.account_id - A1.account_id - 1
INSERT INTO @.missing (n)
SELECT A.greatest_id + I.n + 1
FROM (SELECT MAX(account_id)
FROM Accounts) AS A(greatest_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < @.upper - A.greatest_id
RETURN
END

-- Sample data
INSERT INTO Accounts (account_id, account_name)
SELECT 50102, 'test1'
UNION ALL
SELECT 50105, 'test2'
UNION ALL
SELECT 50106, 'test4'

SELECT n AS account_id
FROM IntervalGaps(DEFAULT, DEFAULT)

account_id
50100
50101
50103
50104
50107
50108
...
70100

Regards,
jag|||SET NOCOUNT ON

declare @.lowwer as int
declare @.upper as int

declare @.cur as int

set @.lowwer = 50100
set @.upper = 50200

set @.cur = @.lowwer

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing
--temp table to hold list of missing
create table #missing (
Missing int
--,NotMissing int
)

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Account]'))
Drop table #Account
-- temp table to hold your range
create table #Account(
Account_number int
)

insert into #Account (Account_number)
select Account from [your table]
where inv_number like '[0-9][0-9][0-9][0-9][0-9][0-9]'
and inv_number between @.lowwer and @.upper
-- and any other filter you think would be handy

While( @.cur between @.lowwer and @.upper)
Begin --while

if not exists (select Account_number from #Account
where inv_number = @.cur)
Begin --if
insert into #missing (Missing) values (@.cur)
End --if
--else
--Begin --else
--insert into #missing (NotMissing) values (@.cur)
--End --else

set @.cur = @.cur + 1

End --while
-- return the results
select * from #missing
--clean up
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Inv]'))
Drop table #Inv

Get all the records of the table from the SQL Server Database

Hi, all I got one stupid question in mind. The idea is that I want my SQL database to install into another computer. The best way for me is I want to create a script file for creating tables, triggers, stored procedure. But I want all the SQL statement that insert all the records in to each table too.

Does anyone have a solution for me? Or there be other way?
I am always waiting for ur suggestion.You can take the backup of the database and then restore. This will restore everything (Table structure / SP's etc along with data.

Check the article for backup/restore:

http://vyaskn.tripod.com/backup_and_restore_with_sql_litespeed.htm|||Thank a lot.

Get all table names with a given column name

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
SashiSelect table_name from information_Schema.columns where
column_name='column name'

Madhivanan

Sashi wrote:

Quote:

Originally Posted by

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
Sashi

|||How can I do it?

One method is to query the INFORMATION_SCHEMA views:

SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON
t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
t.TABLE_NAME = c.TABLE_NAME
WHERE
t.TABLE_TYPE = 'BASE TABLE' AND
c.COLUMN_NAME = 'column order_date'
ORDER BY
c.TABLE_SCHEMA,
c.TABLE_NAME

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sashi" <smalladi@.gmail.comwrote in message
news:1152627046.364476.199010@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
Sashi
>

Get all records from 1 table

I have a view with the following FROM clause. I want to be able to get ALL
matching records from the ProfitCenterCodeSubs table, even if there are none
in the RepairOrderTasks table. Thanks.
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
DavidDavid
See if this helps
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
OR dbo.RepairOrderTasks.SubCenterID is null
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||Nope. Same results.
David
*** Sent via Developersdex http://www.examnotes.net ***|||So, please post DDL+ sample data + expected result
"David" <daman@.lifetime.com> wrote in message
news:esFBCr7WFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Nope. Same results.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Consider selecting from ProfitCenterCodeSubs and left joining to
RepairOrderTasks.
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
>I have a view with the following FROM clause. I want to be able to get ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
> none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
Swap the LEFT OUTER JOIN for a RIGHT OUTER JOIN, or switch the ON clause
around - you want the table that you need to retrieve all records from on
the side of the ON that the outer join indicates.
Dan|||Just try this
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode RIGHT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"David Developer" wrote:

> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>|||Then use a RIGHT OUTER JOIN instead.
FROM
(
dbo.RepairOrderTasks
INNER JOIN
dbo.Employees
ON dbo.RepairOrderTasks.EmployeeCode = dbo.Employees.EmployeeCode
)
right OUTER JOIN
dbo.ProfitCenterCodeSubs
ON dbo.RepairOrderTasks.SubCenterID = dbo.ProfitCenterCodeSubs.SubCenterID
AMB
"David Developer" wrote:

> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>sql

Get all records

Hi I have a table route with

routeid name

1 a

2 b

3 c

4 d

5 e

..

10 j

exExposure

caseid routeid

200 1

300 2

..

Now I have written a query which will join some other tables to get the results

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

left join exExposure e on c.caCaseID=e.caCaseID

Left Join Route er on er.RouteID=e.RouteID

where --conditions

When i run this query I am getting only the routes which have the values exist in exExposure table.If for some routes like i, j I don't have corresponding casesids.But i need to get all the routes which exist in Route table even they don't have caseids.

For this i am trying like this:

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

left join exExposure e on c.caCaseID=e.caCaseID

Left Join Route er on er.RouteID=e.RouteID

where --conditions

Union

Select er.RouteID,er.Name,0,0

From Route r

where r.routeID Not IN (Selelct RouteID From above Selelct query with the same where condtions and joins)

Any other better way for getting the unmatched routes other than this.

Thanks in advance.

I'm not sure what your question is, but I'll take a guess.

If you want the original query to contain all Routes (Route table), then name the Route table as the FIRST table in the series of joins, OR, in the current query, use a RIGHT JOIN with Routes. Either option 'should' give you what you seek.

|||

You can get your desired result using the following query..

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

Left Join exExposure e on c.caCaseID=e.caCaseID And {all Your exExposure table based conditions}

Left Join Route er on er.RouteID=e.RouteID And {all your Route table based conditions}

where {other conditions}

|||

Hi,

I placed all the ExExposure table condion after the exExposure table join this is for Route table also. But now also it is giving Routes which have the CaseID s only. Any other way.

Thanks in advance.

|||

The following query will be help you...

Code Snippet

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from Route er

Left Join exExposure e on er.RouteID=e.RouteID

Left Join cacase c on c.caCaseID=e.caCaseID

where other condition

Get a total on rows that are filtered using the Group Filter in a Table

Is it possible to get a sub total for a Top N filter?
I've got a group that contains that contains a top N filter.
This filters properly for example, I see only 3 rows per group.
However, I cannot get a sub total for only those 3 rows. It calculates
all
of the rows with the main group.
How would I get the sum for only those 3 rows that are being displayed
on
the report.hi,
keep a visibility condition for other subtotals. i mean to say to
select each three records you would have taken mod ,right? take
absolute value now for not showing other totals
some thing like this
Rownumber(Isnothing)\3<2 such that it will be invisible for that row
Thank you
Raj Deep.A

Get a Tables Row Count from system tables

Anyone knows how to get a Table's Row Count from system tables?

Thanks.select count(*) from sysobjects

sysobjects is a system table name. Replace this with the system table name which you want to get the row count.

Get a max value from one table base on date from another.

I have a problem where I need to join multiple tables. Part of the query needs me to make the following relationship in a join.

Say I have a table with items, prices, and dates.

PriceTable

ITEM PRICE DATE

A 1.00 5/5/2000

B 3.00 1/1/2000

A 2.50 6/5/2004

....

This table represents an items price from the date on. So Item 'A' costed 1.00 from 5/5/2000 through 6/5/2004, then it costed 2.50 from that day on.

Now say I have a table of transactions with items, amount bought, and dates.

TransactionTable

ITEM AMOUNT DATE

A 5 6/6/2003

A 1 8/5/2003

A 2 8/5/2004

The total for A should come out to be 11.00. There are multiple Items and multiple price changes.

If someone could point out how a inner join of this nature would work it will help me in my query. I guess the reason i say inner join is because I am joining multiple other tables to do my query.

My current Price Table has a start date and an end date and my query looks something like this. "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable Inner Join TransactionTable ON TransactionTable.Date Between PriceTable.StartDate and PriceTable.EndDate AND PriceTable.Item = TransactionTable.Item".

I want something like this because the tables need to be in the format above "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable INNER JOIN TransactionTable ON Max(PriceTable.Date) WHERE PriceTable.Date <= TransactionTable.Date AND PriceTable.Item = TransactionTable.Item".

Hope that made sense, thanks.

SELECT c.item, SUM(c.ItemAmount) AS "Total" FROM (SELECT a.item , a.amount*(SELECT TOP (1) b.price

FROM PriceTable AS b

WHERE (b.Date <= a.Date)

ORDER BY b.Date DESC) AS "ItemAmount"

FROM TransactionTable AS a) AS c

GROUP BY c.item

|||

I had to change it slightly:

SELECT c.item, SUM(c.ItemAmount) AS "Total" FROM (SELECT a.item , a.amount*(SELECT TOP (1) b.price

FROM PriceTable AS b

WHERE (b.Date <= a.Date) AND b.item = a.item

ORDER BY b.Date DESC) AS "ItemAmount"

FROM TransactionTable AS a) AS c

GROUP BY c.item

Thank you for your help. Now I just have to integrate this into my larger query.

sql

Get a list of columns

I'm trying to get a list of columns from a table. Here is my code:

public List<string> GetColumns(string serverName, string dataBaseName, string tableName)

{

List<string> columns = new List<string>();

Server server = new Server(serverName);

server.ConnectionContext.LoginSecure = true;

server.ConnectionContext.Connect();

Database dataBase = new Database(server, dataBaseName);

Table table = new Table(dataBase, tableName);

foreach (Column column in table.Columns)

{

columns.Add(column.Name);

}

columns.Sort();

return columns;

}

The problem is that table.Columns.Count is 0. How do I get a list of columns in a table?

I got this working. Instead of getting a reference to each object individually:

Database dataBase = new Database(server, dataBaseName);

Table table = new Table(dataBase, tableName);

Then looping through the Columns collection of the table, I combined it all in one:

foreach (Column column in server.Database[dataBaseName].Tables[tableName].Columns)

This now gets me a list of all the columns in the table.

Get a list of changed records in a table

Is there a way to get a list of records that have changed since a specific
Date/Time?
I could create a trigger and a new table and work off that data but I have a
lot of tables I want to query so I don't necessarily want to create triggers
for each one.You cannot get this unless you have a datetime column on the table that you
update when data changes. or a triggering mechanism to store changed data.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>|||> Is there a way to get a list of records that have changed since a specific
> Date/Time?
No, SQL Server does not keep this information for you automagically.
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
If you allow ad hoc access to your tables, then this is how it's done, I'm
afraid. If you deny INSERT/UPDATE rights to the table and force those
actions through stored procedures, then you could perform the logging within
a stored procedure, which at least avoids some of the problems with doing
this in a trigger. But for DELETE you would have to log to a separate table
or use a trigger.
While it doesn't help you today, SQL Server 2008 will have several options
to make this easier... from change tracking and change data capture to the
extreme of audit all actions.
A|||You could use the new OUTPUT clause to automatically pump the pre-existing
data into another table and then query that 2nd table
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>

get 5 latest invoices (based on InvoiceDate) for each customer.

I have two tables:
- CustomerID(CustID,CustName)
- Invoice(CustID,InvoiceID,InvoiceDate)
How can i get a table that has 5 latest invoices (based on InvoiceDate) for each customer.
Example: The result has format like this:
CustID CustName InvoiceID InvoiceDate
0001 Ng V A 126121 20061028
0001 Ng V A 126128 20061026
0001 Ng V A 126130 20061022
0001 Ng V A 126132 20061019
0001 Ng V A 126140 20061018
0003 Ng V B 126050 20061024
0003 Ng V B 126046 20061016
0003 Ng V B 126038 20061012
0003 Ng V B 126012 20061010
Please help me to use query to solve this problem. Many thanks for your kind support.
Best regardscan't you just do the following

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 InvoiceDate from Invoice Order By InvoiceDate DESC)|||

Quote:

Originally Posted by Taftheman

can't you just do the following

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 InvoiceDate from Invoice Order By InvoiceDate DESC)

Thanks for your responding. But I think you misunderstand my idea. I want to retreive a table which contains a customer list with their corresponding latest invoice.

I'm appreciate for your helping.|||

Quote:

Originally Posted by thanhphong122

Thanks for your responding. But I think you misunderstand my idea. I want to retreive a table which contains a customer list with their corresponding latest invoice.

I'm appreciate for your helping.

Im not sure but try just joining the two tables.

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 I.InvoiceDate from Invoice I Left Join CustomerID C I.CustId = C.CustID Order By InvoiceDate DESC)

If that doesn't work then put custid in both where clauses|||You can use this, it gets all the top 5 dates from the customer id of QUICK, change to suit your needs... You can even run it on query analyzer as it uses the northwind database

Select C.CustomerId, C.ContactName, O.OrderDate
from Customers C left join Orders O on C.CustomerId = O.CustomerId
Where C.CustomerId = 'QUICK' and O.OrderDate in (Select top 5 OrderDate from Orders Where CustomerId = 'QUICK' order by OrderDate DESC)

Let us know if it works|||The query below runs very good.

SELECT c.Customer, c.Name1, Invoice,i.ILDATE
FROM Customer C join Invoice I on (c.Customer = i.MKH)
WHERE (i.Invoice IN (SELECT TOP 5 Invoice FROM Invoice j WHERE (c.Customer = j.MKH) ORDER BY j.ILDate DESC))
ORDER BY c.Customer, i.ILDate DESC

Thanks for your help.|||

Quote:

Originally Posted by thanhphong122

The query below runs very good.

SELECT c.Customer, c.Name1, Invoice,i.ILDATE
FROM Customer C join Invoice I on (c.Customer = i.MKH)
WHERE (i.Invoice IN (SELECT TOP 5 Invoice FROM Invoice j WHERE (c.Customer = j.MKH) ORDER BY j.ILDate DESC))
ORDER BY c.Customer, i.ILDate DESC

Thanks for your help.

would this not be simpler?

SELECT DISTINCT TOP 5 c.Customer, c.Name1, i.ILDATE
FROM Customer c
INNER JOIN Invoice i
ON c.Customer = i.MKH
ORDER BY i.ILDATE|||

Quote:

Originally Posted by willakawill

would this not be simpler?

SELECT DISTINCT TOP 5 c.Customer, c.Name1, i.ILDATE
FROM Customer c
INNER JOIN Invoice i
ON c.Customer = i.MKH
ORDER BY i.ILDATE

Oops!

ORDER BY i.ILDATE DESC

Get 10 records fro each group

Hi
I have a table that has fromNode, toNode and idGroup columns
I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
1000
E.G. The query would return
fromNode, toNode, idGroup
1000,2001,1
1000,2002,1
1000,2003,1
1000,2004,2
1000,2005,2
1000,2006,2
1000,2013,3
1000,2014,3
1000,2016,4
But I am having trouble comming up with such a query. Can any one help.
SQL code and inserts below.
CREATE TABLE [dbo].[memberPathsGroups](
[fromNode] [int] NOT NULL,
[toNode] [int] NOT NULL,
[idGroup] [int] NOT NULL,
CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
(
[fromNode] ASC,
[toNode] ASC,
[idGroup] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into memberPathsGroups values (1000,2001,1)
insert into memberPathsGroups values (1000,2002,1)
insert into memberPathsGroups values (1000,2003,1)
insert into memberPathsGroups values (1000,2004,2)
insert into memberPathsGroups values (1000,2005,2)
insert into memberPathsGroups values (1000,2006,2)
insert into memberPathsGroups values (1000,2007,2)
insert into memberPathsGroups values (1000,2008,2)
insert into memberPathsGroups values (1000,2009,2)
insert into memberPathsGroups values (1000,2010,2)
insert into memberPathsGroups values (1000,2012,2)
insert into memberPathsGroups values (1000,2013,3)
insert into memberPathsGroups values (1000,2014,3)
insert into memberPathsGroups values (1000,2015,2)
insert into memberPathsGroups values (1000,2016,4)
insert into memberPathsGroups values (1001,2001,1)
insert into memberPathsGroups values (1001,2010,1)
insert into memberPathsGroups values (1001,2012,1)
insert into memberPathsGroups values (1001,2016,2)Here's one way:
SELECT
fromNode,
toNode,
idGroup
FROM dbo.memberPathsGroups mpg
WHERE
mpg.toNode IN
(
SELECT TOP 3
mpg1.toNode
FROM dbo.memberPathsGroups mpg1
WHERE
mpg1.fromNode = mpg.fromNode
AND mpg1.idGroup = mpg.idGroup
ORDER BY mpg1.toNode
)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rippo" <info@.rippo.co.uk> wrote in message
news:1131373532.924245.290810@.o13g2000cwo.googlegroups.com...
> Hi
> I have a table that has fromNode, toNode and idGroup columns
> I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
> 1000
> E.G. The query would return
> fromNode, toNode, idGroup
> 1000,2001,1
> 1000,2002,1
> 1000,2003,1
> 1000,2004,2
> 1000,2005,2
> 1000,2006,2
> 1000,2013,3
> 1000,2014,3
> 1000,2016,4
> But I am having trouble comming up with such a query. Can any one help.
> SQL code and inserts below.
>
> CREATE TABLE [dbo].[memberPathsGroups](
> [fromNode] [int] NOT NULL,
> [toNode] [int] NOT NULL,
> [idGroup] [int] NOT NULL,
> CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
> (
> [fromNode] ASC,
> [toNode] ASC,
> [idGroup] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into memberPathsGroups values (1000,2001,1)
> insert into memberPathsGroups values (1000,2002,1)
> insert into memberPathsGroups values (1000,2003,1)
> insert into memberPathsGroups values (1000,2004,2)
> insert into memberPathsGroups values (1000,2005,2)
> insert into memberPathsGroups values (1000,2006,2)
> insert into memberPathsGroups values (1000,2007,2)
> insert into memberPathsGroups values (1000,2008,2)
> insert into memberPathsGroups values (1000,2009,2)
> insert into memberPathsGroups values (1000,2010,2)
> insert into memberPathsGroups values (1000,2012,2)
> insert into memberPathsGroups values (1000,2013,3)
> insert into memberPathsGroups values (1000,2014,3)
> insert into memberPathsGroups values (1000,2015,2)
> insert into memberPathsGroups values (1000,2016,4)
> insert into memberPathsGroups values (1001,2001,1)
> insert into memberPathsGroups values (1001,2010,1)
> insert into memberPathsGroups values (1001,2012,1)
> insert into memberPathsGroups values (1001,2016,2)
>

Get @@Identity from a remote SQL Server

I am using SQL 2000 server. I have a SP that insert a
record to a remote server table with Identity column as
the ID. After the insert, I try to use @.@.Identity to get
the newly inserted ID back. I got NULL. However, it works
when I run the same SP against a DB on the same server.
How can I get the ID back from a remote server?
Thank you for any inputTom
It works.Thank you a lot.
>--Original Message--
>Try:
>declare @.id int
>exec OtherServer.OtherDB.dbo.sp_executesql
> N'insert MyTable (ColA, ColB) values (@.a, @.b)
> select @.id = @.@.identity'
>, '@.a int, @.b int, @.id int'
>, @.a = 1, @.b = 2, @.id = @.id out
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"omninews" <cxie@.omnichoice.com> wrote in message
news:015801c377f8$140a7410$a001280a@.phx.gbl...
> I am using SQL 2000 server. I have a SP that insert a
>record to a remote server table with Identity column as
>the ID. After the insert, I try to use @.@.Identity to get
>the newly inserted ID back. I got NULL. However, it works
>when I run the same SP against a DB on the same server.
> How can I get the ID back from a remote server?
> Thank you for any input
>|||Tom and others,
It works but sometimes I get dead lock. Because this
exec statement starts a new process on the remote server.
After this I have other update/delete might agaist the
same remote database objects which are within the same
procedure ,therefore the main thread. Looks like the exec
started process does not cleanup itself after it finishes
it's job. Is there a way to kill itself after it is done?
Thank you!
>--Original Message--
>Tom
> It works.Thank you a lot.
>
>>--Original Message--
>>Try:
>>declare @.id int
>>exec OtherServer.OtherDB.dbo.sp_executesql
>> N'insert MyTable (ColA, ColB) values (@.a, @.b)
>> select @.id = @.@.identity'
>>, '@.a int, @.b int, @.id int'
>>, @.a = 1, @.b = 2, @.id = @.id out
>>--
>> Tom
>>----
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>..
>>"omninews" <cxie@.omnichoice.com> wrote in message
>news:015801c377f8$140a7410$a001280a@.phx.gbl...
>> I am using SQL 2000 server. I have a SP that insert a
>>record to a remote server table with Identity column as
>>the ID. After the insert, I try to use @.@.Identity to get
>>the newly inserted ID back. I got NULL. However, it
works
>>when I run the same SP against a DB on the same server.
>> How can I get the ID back from a remote server?
>> Thank you for any input
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0090_01C3866C.3C5B1D20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Deadlocks are a bigger issue. You'll have to ensure that you keep your =transactions "narrow", i.e. do only the essential work inside the =transaction. Also, make sure you access the resources in the exact same =order in all transactions. You may want to consider doing the insert =via a stored proc on the remote server, instead of using sp_executesql.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"omninews" <cxie@.omnichoice.com> wrote in message =news:014f01c3842b$d18f3770$7d02280a@.phx.gbl...
Tom and others,
It works but sometimes I get dead lock. Because this exec statement starts a new process on the remote server. After this I have other update/delete might agaist the same remote database objects which are within the same procedure ,therefore the main thread. Looks like the exec started process does not cleanup itself after it finishes it's job. Is there a way to kill itself after it is done?
Thank you!
>--Original Message--
>Tom
> It works.Thank you a lot.
>
>>--Original Message--
>>Try:
>>declare @.id int
>>exec OtherServer.OtherDB.dbo.sp_executesql
>> N'insert MyTable (ColA, ColB) values (@.a, @.b)
>> select @.id =3D @.@.identity'
>>, '@.a int, @.b int, @.id int'
>>, @.a =3D 1, @.b =3D 2, @.id =3D @.id out
>>-- >> Tom
>>----
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>..
>>"omninews" <cxie@.omnichoice.com> wrote in message >news:015801c377f8$140a7410$a001280a@.phx.gbl...
>> I am using SQL 2000 server. I have a SP that insert a >>record to a remote server table with Identity column as >>the ID. After the insert, I try to use @.@.Identity to get >>the newly inserted ID back. I got NULL. However, it works >>when I run the same SP against a DB on the same server.
>> How can I get the ID back from a remote server?
>> Thank you for any input
>.
>
--=_NextPart_000_0090_01C3866C.3C5B1D20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Deadlocks are a bigger issue. =You'll have to ensure that you keep your transactions "narrow", i.e. do only the =essential work inside the transaction. Also, make sure you access the resources =in the exact same order in all transactions. You may want to consider =doing the insert via a stored proc on the remote server, instead of using sp_executesql.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"omninews" wrote in =message news:014f01c3842b$d1=8f3770$7d02280a@.phx.gbl...Tom and others, It works but sometimes I get dead lock. =Because this exec statement starts a new process on the remote server. After =this I have other update/delete might agaist the same remote database =objects which are within the same procedure ,therefore the main thread. Looks like =the exec started process does not cleanup itself after it finishes =it's job. Is there a way to kill itself after it is done? =Thank you! >--Original Message-->Tom> It works.Thank you a lot.>>>>--Original Message-->Try:>>declare @.id int>exec OtherServer.OtherDB.dbo.sp_executesql> =N'insert MyTable (ColA, ColB) values (@.a, @.b)> =select @.id =3D @.@.identity'>, '@.a int, @.b int, @.id =int'>, @.a =3D 1, @.b =3D 2, @.id =3D @.id =out>>-- > Tom>>---=-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>..>>=;"omninews" =wrote in message >news:015801c377f8$140a7410$a001280a@.phx.gbl...> &=nbsp; I am using SQL 2000 server. I have a SP that insert a >record =to a remote server table with Identity column as >the ID. After =the insert, I try to use @.@.Identity to get >the newly inserted ID =back. I got NULL. However, it works >when I run the same SP =against a DB on the same server.> How can I get the ID back =from a remote server?>> Thank you for any input>>.>

---

--=_NextPart_000_0090_01C3866C.3C5B1D20--

Get "Invalid cursor state" when modifying a table in SQL 2000

When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state.

SP3 has been applied to SQL Server 2000.

Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.

If you reuse an hstmt after doing a query that opens a cursor, you need to call SQLCloseCursor(hstmt) before you make your next call.

Hope that helps,

John

|||I guess that you message is from any GUI to manage SQL Server, right ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Gerating Excel Reports from SSIS

I want to be able to create an Excel report in SSIS after querying the data from a SQLSERVER table.

I have a IS package where I'm loading all the data required in the report and the final step of the IS package I would like to build the reports. I think it makes sense to take this approach instead of setting up a RSS package.

AS anyone seen any Blogs which explains such a flow?

You can use an Excel destination in your data flow, but beyond that, you won't be able to apply unique formatting rules, grid lines, images, charts, etc...

Not without programming a script component, anyway.

Geographical realignment dimension

I have a fact table filled with clients including their address information as well as their original geographical alignment info (RegionID, DistrictID and TerritoryID). In order to forecast as well as perform an “as if” analysis, I need to be able to realign the accounts based on previous or future alignments. I have an alignment table (dimension) that maintains alignment information by year and quarter and I can join to this table using the ClientAddressID field as well as the Year and Quarter fields. I’d like to be able to make the Year and Quarter fields/join be variable so that I can perform realignments simply by changing the year/quarter.

Is this possible in SSAS or am I going to have to find another solution? Any help will be greatly appreciated...

David

I resolved this using a many to many relationship. This write-up was very useful: http://msdn2.microsoft.com/en-us/library/ms170463.aspx

David

Geographical realignment dimension

I have a fact table filled with clients including their address information as well as their original geographical alignment info (RegionID, DistrictID and TerritoryID). In order to forecast as well as perform an “as if” analysis, I need to be able to realign the accounts based on previous or future alignments. I have an alignment table (dimension) that maintains alignment information by year and quarter and I can join to this table using the ClientAddressID field as well as the Year and Quarter fields. I’d like to be able to make the Year and Quarter fields/join be variable so that I can perform realignments simply by changing the year/quarter.

Is this possible in SSAS or am I going to have to find another solution? Any help will be greatly appreciated...

David

I resolved this using a many to many relationship. This write-up was very useful: http://msdn2.microsoft.com/en-us/library/ms170463.aspx

David

sql

Generic Stored procedure

Hi All!

i want to retrieve reults from table "tblCategory" by passing

search string as parameter and column name as parameter as well.

CREATE Procedure uspSearchCategory

(

@.Search nvarchar(255),

@.column varchar(100)

)

AS

SELECT

*

FROM

tblCategory

WHERE

@.column LIKE '%' + @.Search+'%'

This doesn't work as @.column in last line is incorrect. Can anybody tell me how can i achieve that.

If i write

name LIKE '%' + @.Search+'%' or

ID LIKE '%' + @.Search+'%'

it works.But can it works as general for ant column name i pass as @.column.

thanx

You need to turn your query into a string, then run the string using code like this:

exec sp_executesql @.query

where @.query is the nvarchar that contains the SQL to run