One more easy one.

What's the use of Filegroup backup?
Let's say we have around 60 tables. 30 in one filegroup
rest are in another filegroup.
What's the application of backing up only filegroup
instead of whole databse.
Because in any situation if we have to restore WHOLE
database. We can't just restore just 30 tables (in 1
filegroup) we have to restore the WHOLE database.
How would the integrity of databse is mainained if we
restore from filegroup datbase of filegroup1 taken at 2 pm
and restore from filgroup datbaase of filegroup2 taken at
3 pm.
The main advantage of filegroup backup is for very large databases. You can
back up only the required parts. Together with log backups you can the
restore the filegroup backup and reapply the logs to bring everything back
to a consistent point.
So very useful if the size gets really big. In most circumstances full
backups and appropriate log backups are probably fine.
Mike John
<anonymous@.discussions.microsoft.com> wrote in message
news:00bf01c49fff$e77d9360$a401280a@.phx.gbl...
> What's the use of Filegroup backup?
> Let's say we have around 60 tables. 30 in one filegroup
> rest are in another filegroup.
> What's the application of backing up only filegroup
> instead of whole databse.
> Because in any situation if we have to restore WHOLE
> database. We can't just restore just 30 tables (in 1
> filegroup) we have to restore the WHOLE database.
> How would the integrity of databse is mainained if we
> restore from filegroup datbase of filegroup1 taken at 2 pm
> and restore from filgroup datbaase of filegroup2 taken at
> 3 pm.
>

One more easy one.

What's the use of Filegroup backup?
Let's say we have around 60 tables. 30 in one filegroup
rest are in another filegroup.
What's the application of backing up only filegroup
instead of whole databse.
Because in any situation if we have to restore WHOLE
database. We can't just restore just 30 tables (in 1
filegroup) we have to restore the WHOLE database.
How would the integrity of databse is mainained if we
restore from filegroup datbase of filegroup1 taken at 2 pm
and restore from filgroup datbaase of filegroup2 taken at
3 pm.The main advantage of filegroup backup is for very large databases. You can
back up only the required parts. Together with log backups you can the
restore the filegroup backup and reapply the logs to bring everything back
to a consistent point.
So very useful if the size gets really big. In most circumstances full
backups and appropriate log backups are probably fine.
Mike John
<anonymous@.discussions.microsoft.com> wrote in message
news:00bf01c49fff$e77d9360$a401280a@.phx.gbl...
> What's the use of Filegroup backup?
> Let's say we have around 60 tables. 30 in one filegroup
> rest are in another filegroup.
> What's the application of backing up only filegroup
> instead of whole databse.
> Because in any situation if we have to restore WHOLE
> database. We can't just restore just 30 tables (in 1
> filegroup) we have to restore the WHOLE database.
> How would the integrity of databse is mainained if we
> restore from filegroup datbase of filegroup1 taken at 2 pm
> and restore from filgroup datbaase of filegroup2 taken at
> 3 pm.
>

One more Beginner Problem

I need to restore multiple tables in a sequence. I am writing separate data flow for each table restore.

If there is any error in any of the table restore data flow, I want to write that error in a log file.

I am writing a script component in every data flow, which will get error code & error description, that i am writing in the log fie.

Is there a way to create a public reusable error logging script file, which we can call from every data flow and log only errors which we want?

Also want to confirm if the way i am doing is correct way or is there any easier/better way to achieve this?

It goes something like this:

Try
'create sqlcommand specifying connection and query
'create reader

reader = sqlcommand.ExecuteReader() <== this throws an "Object reference not set to an instance of an object" exception

'... other stuff here...
Catch ex as Exception
Row.DirectToErrorOutput()
Finally
'dispose sqlcommand and reader here...
End Try

Despite the Catch block, the Script Component still fails, and doesn't get redirected to the ErrorOutput path.

Any ideas on how to solve this?sql

One large database or a few smaller databases?

Does it make sense to break up data into a few smaller (identical)
databases to keep from having hundreds of millions of rows in a few
tables in one single database?
I ask because I've got a website that's pumping about 500k new records
into 3 or 4 tables per day. In several months I'll probably be near 400
or 500 million rows in some of them.
Users to the website query the data, and so far, it's working well
(with only a few million rows in each). My indexes look good and my
queries only join 2 or 3 tables at most, but I am mostly doing
aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
rows at a time.
My app is set up to handle the data being in separate identical
databases, but I'd like to keep it all in the same place to keep things
more simple (for backups & upgrades, etc..)
But my fear is when I start to reach half a billion rows my query times
are going to suck, even if I cram more memory into my database server
(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
Anyone have any experience with this much data?
Thanks!!You should not split up your solution into more than one database. A
good approach would be to use the analysis services from Sql Server as
you are requireing a lot of aggregation data which could be
precalculated. Therefore these cubes will provide a much feaster
response than quering just an OLTP database.
HTH, Jens Suessmeyer.|||Jens, thanks for the quick reply. I'm using SQL 2005 standard... Does
that I mean need to upgrade to Enterprise?
Forgive my lack of knowledge on AS and data cubes, but are they kept in
real-time? Ideally the queries would be doing in "near-time" to when
the data has been inserted.
Thanks for your help.|||No, AS is already included in SQL Server 2k5, but has to be instaleld
if not.
You sure can design the cube that it can fallback on non aggregated
realtime data , but perhaps the Analysis Services newsgroup can help
you further Within this they should know was is possible in your case.
HTH, Jens Suessmeyer.|||On 28 Jan 2006 11:29:29 -0800, Jeff Turner wrote:
>Does it make sense to break up data into a few smaller (identical)
>databases to keep from having hundreds of millions of rows in a few
>tables in one single database?
>I ask because I've got a website that's pumping about 500k new records
>into 3 or 4 tables per day. In several months I'll probably be near 400
>or 500 million rows in some of them.
>Users to the website query the data, and so far, it's working well
>(with only a few million rows in each). My indexes look good and my
>queries only join 2 or 3 tables at most, but I am mostly doing
>aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
>rows at a time.
>My app is set up to handle the data being in separate identical
>databases, but I'd like to keep it all in the same place to keep things
>more simple (for backups & upgrades, etc..)
>But my fear is when I start to reach half a billion rows my query times
>are going to suck, even if I cram more memory into my database server
>(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
>Anyone have any experience with this much data?
>Thanks!!
Hi Jeff,
I agree with Jens - keep it in one database.
In addition to Jens' suggestion to use analysis server, here are some
other ideas that might help you gain speed as the amount of data grows.
1. Use indexed views. If your queries are usually for aggregates, they
could prove to be an excellent way to speed up the process, since they
are precalculated. Because SQL Server has to modify each indexed view as
the base data changes, indexed views do cause a slowdown for data
modifications, though. Don't use indexed views if you need millisecond
performance on insert and update operations.
2. Consider the use of partitioned views (SQL Server 2000) or table
partitioning (SQL Server 2005).
--
Hugo Kornelis, SQL Server MVP|||Hi Jeff,
Some extra thoughts:
1. The optimizer is going to do better work for you if it's in one database.
2. Backups/restores become much more complicated if you have to keep
multiple databases in sync. Consider what you might have to do to restore to
a point in time. There are methods but it just starts to complicate things.
HTH,
Greg
"Jeff Turner" <zigjst@.gmail.com> wrote in message
news:1138476569.362520.240210@.f14g2000cwb.googlegroups.com...
> Does it make sense to break up data into a few smaller (identical)
> databases to keep from having hundreds of millions of rows in a few
> tables in one single database?
> I ask because I've got a website that's pumping about 500k new records
> into 3 or 4 tables per day. In several months I'll probably be near 400
> or 500 million rows in some of them.
> Users to the website query the data, and so far, it's working well
> (with only a few million rows in each). My indexes look good and my
> queries only join 2 or 3 tables at most, but I am mostly doing
> aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
> rows at a time.
> My app is set up to handle the data being in separate identical
> databases, but I'd like to keep it all in the same place to keep things
> more simple (for backups & upgrades, etc..)
> But my fear is when I start to reach half a billion rows my query times
> are going to suck, even if I cram more memory into my database server
> (dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
> Anyone have any experience with this much data?
> Thanks!!
>|||Thanks to everyone for replying.
One thing I should point out is the data in these tables is broken down
by users to the website.
For example, if I did split this up by database, I could have one
database that holds all of the records for users with last names of
A-G. They would never have a need to query against the data in the
other databases.
So the practical minded side of me says, why bother running queries for
an A-G user in tables that hold everyone's data, when I could
physically have it narrowed down already.
Does that make sense? Or is SQL just smart & efficient enough with
indexing that the performance gain would be negligable compared to my
backup/maintenance costs.
Thanks again!

One fact table/cube or multiple fact tables/cube

Hi,

I am now starting on my 2nd analysis server project and I have 8 dimensions and 6 fact tables. One fact table has 90 million rows, the other one has 30 million rows. The other four are less then 1 million rows big.

Should I create one cube with 6 fact tables in it or 6 cubes with one fact table ?

The advantage of the first one is that if you need to make a report you can have all the data in one query which is great for the users.

The advantage of the last one is that if you develop, you can easily calculate and test a small cube.

I am also thinking to get the best of both worlds namely going for the last option and a a 7th cube which has links to all the other six cubes.

Any suggestions ?

Constantijn Enders

This will have thoughts or considerations to address most of your questions.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1358193&SiteID=1

I would love to hear other people's thoughts, too.

|||

And here's a separate discussion on the same topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1402506&SiteID=1

|||

Hi,

I finally ended up at this blog http://prologika.com/CS/blogs/blog/archive/2006/06/27/1331.aspx

This confirmed my final thought, split into smaller and then one cube to rule them all

CE

One DELETE sql statement to delete from two tables

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

I think Triggers may help for this scenario...

I am not sure about one delete statement to delete from two tables.

Thanks

Sreekanth

|||

If both tables has the relation (child-master with foreign key), then you can use the ON DELETE CASCADE on your Primary Key (Master) constraint.

Code Snippet

Create MasterTable

(

Id int Primary Key On Delete Cascade,

..

..

)

Create Childtable

(

Master_TableId int References MasterTable(Id)

..

..

..

)

If the tables doesn’t have any relation (logically bound), then you can use the Trigger to delete the values from the tables – using DELETED special table.

Code Snippet

Create trigger trigger_name On FirstTable For Delete

As

Begin

Delete From SecondTable Where ID in (Select ID from DELETED)

End;

Go

Delete From FirstTable

If you use SQL Server 2005, then you can use the OUTPUT clause, to get the deleted values from the current table, and supply those deleted values to remove the records from other table.

Code Snippet

Declare @.DeletedIds Table

(

Id int

)

Delete FirstTable OUTPUT DELETED.ID INTO @.DeletedIds Where <condition>;

Delete From SecondTable Where ID in (Select Id from @.DeletedIds);

|||

More information may be required for answering this. The simple answer is, no, you cannot "technically" do this. Any method that you can simulate this will technically be multiple SQL operations. Triggers, ON CASCADE constraints, etc.

There is really no need for one statement to delete from multiple tables, the key for your needs is probably that you want to make sure that if one statement completes, then the other statement completes. This is referred to as an atomic operation, and is managed by Transactions. So, simplistically (you need error handling/messaging for sure) in the following:

BEGIN TRY

BEGIN TRANSACTION

DELETE 1

DELETE 2

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

If DELETE 2 fails, but DELETE 1 succeeds, DELETE 1 will be undone.

sql

One DataRegion(Table) Multiple DataSets

I have a complexed report
It makes use of two queries and 2 tables
I need to use a group in order to display the information correctly,
If I had one query it would have worked perfectly, But the data I am
retrieving is so complexed that I need to make use of two queries other wise
I get duplicate data
Table 1 contains section1, and 2 of the displayed info
Table 2 contains the 3rd section
it looks like this;
Page 1
header
Section1
Section 2
Section 3
Footer
Page 2
header
Section1
Section 2
Section 3
Footer
So in order to accomplish this I take two tables link them to one dataset.
Add a group, But this results in the following. I need page breaks so I set
the page break option in the group properties
Page 1
header
section 1
section2
Footer
Page 2
section1
section2
Page 3
Section 3
Page 4 Section 3
I then put the 2 tables in a list box, and set the grouping on the list, And
This works 100 %. It groups all the data brilliantly. The problem is I cant
use one query, I need to use two!
SO Is their a work around or some way to link 2 datasets to one list
control.By adding the full path or something. The only way I can currently
reference more than one dataset per table is by using aggeragate funtions.
But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1 result
so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that returns
an errorData regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed. To achieve
the effect you want will have to be done in the query. Some of the tools
available to you are joins, unions, openrowset, or linked servers.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griffen" <Griffen@.discussions.microsoft.com> wrote in message
news:B7A2C3C0-4522-466A-B5D2-08ECCD3471C1@.microsoft.com...
> I have a complexed report
> It makes use of two queries and 2 tables
> I need to use a group in order to display the information correctly,
> If I had one query it would have worked perfectly, But the data I am
> retrieving is so complexed that I need to make use of two queries other
wise
> I get duplicate data
> Table 1 contains section1, and 2 of the displayed info
> Table 2 contains the 3rd section
> it looks like this;
> Page 1
> header
> Section1
> Section 2
> Section 3
> Footer
>
> Page 2
> header
> Section1
> Section 2
> Section 3
> Footer
> So in order to accomplish this I take two tables link them to one dataset.
> Add a group, But this results in the following. I need page breaks so I
set
> the page break option in the group properties
>
> Page 1
> header
> section 1
> section2
> Footer
> Page 2
> section1
> section2
> Page 3
> Section 3
> Page 4 Section 3
> I then put the 2 tables in a list box, and set the grouping on the list,
And
> This works 100 %. It groups all the data brilliantly. The problem is I
cant
> use one query, I need to use two!
> SO Is their a work around or some way to link 2 datasets to one list
> control.By adding the full path or something. The only way I can currently
> reference more than one dataset per table is by using aggeragate funtions.
> But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1
result
> so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that
returns
> an errorsql

One database to rule them all

How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.

Thanks

You would have to write a whole lot of TRIGGERS.

That 'could' be a substaintial performance penalty.

(And most likely a very unwieldy design.)

Let us know what you are attempting to accomplish, and perhaps we can guide you to a solution that will work AND be efficient.

|||I am attempting to update all the IDs throughout all of my databases. For example, imagine you had several databases for different applications. Maybe one application is for health information, another application is penitentary clients, and a third application is for penitentary client rehabilitation. A penitentary client would be assigned a unique number and the client shows up in the penitentary client program. If the client receives health or rehabilitation services, then their unique number is used in the other applications so you don't need to duplicate information such as their name. However, for some reason the clients number has to be changed. I want to be able to change the information in the penitentary clients db and have it automatically change in the other databases. If for some reason the ID changes at 3:00 PM, then it would be nice to have all the data updated in real time without having to wait to run a job that updates all of the data. If I get a list of new client IDs to be updated, then I would like a better way to update the tables in a database rather than running an update statement against several tables in different dbs. If a table gets added to a db, I don't want to have to remember to go and add another update statement to some job.

One database to rule them all

How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.

Thanks

You would have to write a whole lot of TRIGGERS.

That 'could' be a substaintial performance penalty.

(And most likely a very unwieldy design.)

Let us know what you are attempting to accomplish, and perhaps we can guide you to a solution that will work AND be efficient.

|||I am attempting to update all the IDs throughout all of my databases. For example, imagine you had several databases for different applications. Maybe one application is for health information, another application is penitentary clients, and a third application is for penitentary client rehabilitation. A penitentary client would be assigned a unique number and the client shows up in the penitentary client program. If the client receives health or rehabilitation services, then their unique number is used in the other applications so you don't need to duplicate information such as their name. However, for some reason the clients number has to be changed. I want to be able to change the information in the penitentary clients db and have it automatically change in the other databases. If for some reason the ID changes at 3:00 PM, then it would be nice to have all the data updated in real time without having to wait to run a job that updates all of the data. If I get a list of new client IDs to be updated, then I would like a better way to update the tables in a database rather than running an update statement against several tables in different dbs. If a table gets added to a db, I don't want to have to remember to go and add another update statement to some job.
sql

One data source view and multiple data source

Hi,

In my datawarehouse we have different database one for dimensions and one for fact tables.

can we create a cube to pull dimensions from one data soure and fact from other databsource?

I recommend you to have the fact tables and the dimensions in the same database.

Your long term quick-fix is to use views between the databases.

Your short scenario description looks like you are building a cube directly from a source system.

If you need to connect another source system you will have to create a data wareouse to consolidate each source.

If not, you wille be creating information silos above each source system that you cannot connect to a second system.

HTH

Thomas Ivarsson

|||both the source are on the same SQL Server but different databases, I was planning to use View but was just considering the performance impact that will cause.|||

Actually Analysis Services allows for having dimensions and parittions to come from different datasources.

The caveat here is not to use different datasources to define your dimension. In such case Analysis Services might decide to use OPENROWSET clause as part of the query it sends during processing of dimension. This would slow you down considerably. But having partitions to come from different datasource should be perfectly fine.

Run Profier to capture SQL queries Aanlysis Server sends during processing and verify you dont get OPENROWSET is these queries.

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

one column DISTINCT on 2 Tables

hello I am trying to get a distinct on one column and 2 tables but it doesnt work

Table1
ID_Table1
Name1
Number1

Table2
ID_Table2
ID_Table1
Name2

I want to get : ID_Table1, DISTINCT(Name1), Name2
WHERE Name1 LIKE 'A%'

how can I do it ?

thank youDISTINCT is not a function

does your query join these tables?

could you show your actual query please?|||yes of course the 2 tables are JOIN by Table1.ID_Table1 AND Table2.ID_Table1|||and you want one result row per value of Name1, correct?

okay, here is one way --select Table1.Name1
, min(Table1.ID_Table1)
, max(Table2.Name2)
from Table1
inner
join Table2
on Table2.ID_Table1 = Table1.ID_Table1
where Table1.Name1 like 'A%'
group
by Table1.Name1|||it works fine !

thanks again !

on similar tables

hi all,
so i have these tables:
Company
Job
Resume
user searches for a company, views that company. there is a list of
"viewed" companies for that user. Users can attach/delete a (private to
themselves) note about that company.
same for jobs and resumes.
What I want to know is, should i have a CompanyNote, JobNote,
ResumeNote table, or have one Note table?
with the first option, can have a foreign key to the corresponding
Company/Job/Resume table, with the second option cannot.
TIA
Neili suppose i should have one note table:
NOTE
noteid
text
a Company table:
COMPANY
companyid
a User table:
USER
userid
and a COMPANY_USER_NOTE table
COMPANY_USER_NOTE
companyid
userid
noteid
and a JOB_USER_NOTE table:
JOB_USER_NOTE
jobid
userid
noteid
then foreign keys not a problem|||(neilmcguigan@.gmail.com) writes:
> i suppose i should have one note table:
> NOTE
> noteid
> text
> a Company table:
> COMPANY
> companyid
> a User table:
> USER
> userid
> and a COMPANY_USER_NOTE table
> COMPANY_USER_NOTE
> companyid
> userid
> noteid
> and a JOB_USER_NOTE table:
> JOB_USER_NOTE
> jobid
> userid
> noteid
> then foreign keys not a problem
But now you can have multiple notes for the same user to the same item.
And the same note can be connected to both job and company.
But maybe that's a feature?
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|||would the alternative would be...
table JobNotes:
UserID
JobID
NoteText
and table CompanyNotes:
UserID
CompanyID
NoteText
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BB794BC5320Yazorman@.127.0.0.1...
> (neilmcguigan@.gmail.com) writes:
> But now you can have multiple notes for the same user to the same item.
> And the same note can be connected to both job and company.
> But maybe that's a feature?
> --
> 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

On Failure WorkFlow not getting Exectuted

i created a DTS package in SQL 2000 using the enterprise manager.

I have defined a SQL task to drop some temporary tables on failure of another SQL task. Also the same temporary tables need to be dropped on success of another task. the on success workflow is working fine. but when i add the on failure workflow to the temporary table dropping task, the temp table droppin is not getting excuted at all either for success nor for the failure. please help me out.

So multiple conditions are pointed to the same failure "workflow"?

If so, change the Multiple constraints option on one of the precedence constraints from the default of "Logical AND" (all constraints must evaluluate to true) to "Logical OR" (only one constraint must evaluate to true).|||Logical OR is great feature in SSIS, but I think the question is about DTS 2000, which did not have this option. In DTS 2000 you need to duplicate error-handling tasks. Or move to SSIS :)|||It worked when i duplicated the error-handling tasks..

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On DELETE On UPDATE Cascade syntax error

Hello

I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data. However, I have having problems.

I have already created the tables with primary keys on each table and foreign keys linking each table to the next.

I tried to delete a row from the parent table and was given this error:

DELETE FROM [dbo].[DomNam]
WHERE [DomNam]=N' football '

Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.

I tried to insert an alter table query:

ALTER TABLE dbo.DomNam
ADD CONSTRAINT FK_Dom_ID

REFERENCES dbo.Dom_CatA (Dom_ID)

ON DELETE CASCADE ON UPDATE CASCADE

But on Execute I saw this error:

Error]
Incorrect syntax for definition of the 'TABLE' constraint

What is wrong with the above syntax?

Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?
If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables.

I would be grateful for any advice. Thanks.

Hi

Have you missed something like this:

ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_IDFOREIGN KEY (Dom_ID)REFERENCES dbo.Dom_CatA (Dom_ID)ON DELETE CASCADE ON UPDATE CASCADE

Trigger could result such an error( DELETE statement conflicted with COLUMN REFERENCE constraint ..... ) either.

|||

(I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data.)

The above means what you are looking for require a trigger because Cascade On DELETE and UPDATE is DRI(declarative referential integrity), that means if A references B then B must exist, it is very clean simple relational Algebra, anything outside that you need a trigger. But you may have some columns that qualify for it, try the threads below for how to enable it. Hope this helps.

http://forums.asp.net/thread/1315554.aspx

http://forums.asp.net/thread/1120122.aspx

|||

Hi

Thanks guys for your reply and help.

I think that my problem is that I have created a many to many relationship without foreign key restraints.

On the parent table I have used the primary key from the first child table "A" as the foreign key.

The child tables are from A to Z.

With the first child table "A" I have used the Primary key from the parent as the foreign key.

With the child table "B" I have used the Primary key from the previous "A" table as the foreign key in "B" table. I have used the primary key as the foreign key in all subsequent tables without declaring referential integrity.

I know that in the very near future I will need to update and delete much of the records in these tables.

How do I remove the foreign keys so that I might be able to create a junction table, which I have since found that I need for a many to many table relationship. Or is there another easier way to do this?

Thanks

Lynn

|||

Hi guys

I have since learned how to delete the foreign keys.

I am viewing my SQL 2000 database using Aqua Data,

* Click on the last sibbling table.

* Click Alter table

* Select constrains tab

* Select FK + delete + OK

I have kept my Primary Keys in tact.

Now I need to relink my tables and ensure that I am able to cascade update or delete. All tables contain similar content, which will require updating or deleting often. Tables are from A to Z.. What is the best method to do this?

Thanks

Lynn

|||I have told you that all you can do is one to one referencing for DRI(declarative referential integrity) to work so if you have figured out the tables that qualify for if A references B then B must exist at the top of Enterprise Manager you will see enable relationship. And those that did not qualify you need a trigger. I hope I am very clear it is not DRI for A to Z but A to B. Hope this helps.|||

I just remembered I had a conversation with a database person who did not understand DRI(declarative referential integrity) so let me explain you have tables A to Z meaning 26 tables it means you can have only 13 DRIs that is 13 Cascade DELETE and Cascade UPDATE because it is a location based way to delete and update related data. So although foreign key is required to enable DRI not all foreign keys can be DRI.

You said the data is related that is DDL(data definition language) where relationship is determined by Upper and Lower bound Cardinality while DRI is DML(data manipulation language) keeping track of child data through one to one mapping if A references B then B must exist. I hope this makes it clear. You have to be careful with the ALTER TABLE ADD or DROP CONSTRAINT in 2000 because most ALTER must be done with Enterprise Manager or SQL Server 2000 will tell you it is wrong. One more thing you can use trigger to do it the way you want but triggers are resource intensive and may not fire all the time. Hope this helps.

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx?mfr=true

|||

Hi Caddre

Thanks for the reply, as you have gathered I am not a sql database expert, I am trying to understand DRI.

The whole think would be easier if I had everything in the one table (but it would be too wide to manage) because the same column of data is repeated on every table A to Z., therefore data row of "John Smith" exists in all tables. But in the child tables "A" table for example "John Smith" has extra columns e.g. art, apples, auto, etc.,

Consequently if I remove or insert a row in parent table, the children need the same edit.

Would it be just as complicated if I created a new table "juntion table" and include all the primary key's from the parent and children tables as foreign keys in the junction table. Or would that not work?

Thanks

Lynn

|||

I am sorry I did not come to this thread to confuse you but what you are saying now is not relational, so I have found you two solutions you can add to valid DRI. I also think it is Microsoft documentation that confused you because in 2005 multiple Cascade is allowed but one table cannot be repeated twice that is the same thing I am saying. Hope this helps.

http://support.microsoft.com/kb/142480

http://msdn2.microsoft.com/en-us/library/aa224818(sql.80).aspx

ON DELETE CASCADE

hello guys!

Well im using MS SQL 2000 and i have over 250 Tables in one of my Database the problem is all the Foreign Key in the Table is not turn ON in ON DELETE CASCADE.

The question is, is there anyway to write a script to turn the ON DELETE CASCADE on all the Tables?

Thanks for the reply!

Novelle

Nope, not really. You will just have to script your keys out, then change to ON DELETE CASCADE manually. Unless there is some tool to do it, but I don't know of it.

A trick I would use is to take your database scripts (or if you don't have any, use a comparison tool, compare my full database to an empty one) and just replace all ON DELETE NO ACTION values with ON DELETE CASCADE. Then use your script to build a database that matched your database, except for the CASCADE constraints.

Then just do the comparison again, and let the tool do the work. (I use RedGate personally, but there are others)

sql

oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
--
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'AS400USERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL
> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
> Here is my linked server script, hope it will help you:
[...]