Extract data from multiple oracle database

I have to extract data from 5 different oracle databases with same schema.This will be scheduled job.Can someone guide me.

Use a For Each loop to iterate through the database connections. Use data flows inside the For Each loop to copy the data.

You might want to start with the SSIS tutorials in Books Online, if this is your first time using SSIS.

sql

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
--
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx
.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
Hari
If you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

External notification of database updates

MS-SQL 2005, C# 2.0

I will have many client databases that will be updated. When they are updated I need to transfer some of that data to a central Server database somewhere on the Internet. Note, schemas do not necessarily match.

Transfering the data (Web Services, Remoting) is not a problem.

What I am looking for is a cool, correct, advisable way, for the client database to notify me of an Insert, Update or Delete. I can then initiate the connection to the Server and 'push' the data (maybe pull some back from the Server too).

Obviously Triggers may be a place to start... But I need to know 'external to the database' that an update has happened or can I send the data from within the SQL Server assembly?

Anyone have any ideas or a technique, maybe something new in SQL Server 2005 (all databases will be 2005), for acheiving this? Just so I don't go down the 'wrong' path...

There are some BLOB's involved (1-2 page PDF's), if that makes a difference.

I envisage that the process transfering the data will be a Windows Service running on each client. The connection may not always be available, so some kind of 'to do' list of outstanding data to be transfered is required.

I'm just starting on this, so any pointers would be great, I'm sure it's all been done before ;)

Thanks

Rob.

One interesting technique to consider would be using triggers and service broker. When an insert, update or delete occurs, send a message via service broker to your central server informing it of the change along with whatever data is necessary to identify it. The central server can then asynchronously receive and process the message.

Dan

external drive, SCSI, same controller...

I have a guy who wants me to move his databases to an external drive. Hardware is my major weakness and I usually think I am McGyver when I can swap out a RAM board in my home PC. Whenever guys in the office start talking hardware, I go hide in the bathroom.

I know I have read that this is a very bad idea in some book and a few message board threads. This guy is going to use SCSI and it is going to be attached to the same controller as his other disks. I know he should have a battery backup for the drive especially if it employs write caching and that it should be formatted to NTFS and not FAT32.

I googled\technetted\BOLed\MSDNed this for 2 hours yesterday because I remember reading something about disc controllers and external drives and SQL Server being a recipe for disaster but I could not find anything to back me up.

Any words of wisdom or advice?Sean,

Well, it sounds like he is going to use a external drive array attached to his existing hardware.

If that's the case, then I wouldn't get too worried...with the following exceptions.

- Make sure that it is a external array device of some kind. Something that they can have HotSwap drives. Something like HP's Modular Smart Array Storage Systems(I'll let you Google for a picts/desc...MSA30 or MSA20). Home brewed equipment may not be the best choice.
- UPS the external drive array. Using the existing UPS or not can be a touchy subject. My suggestion would be to get a second UPS if the budget will allow it.
- If the budget will allow it, get a second drive card for the external array. Spend the money on a top end SCSI controler card. On-Board battery backup, monster cache, etc.

Most/All clustered SQL servers use a external drive array attached to the cluster nodes by a SCSI cable. Got one running now with no issues. Prior SQL server also used a external array and did not have any issues.

My company runs Novell servers with external arrays. From a HW standpoint it is a solid performer.

If they need it, with the external array they would have the ability to create multiple RAID arrays and seperate the data from the log files which could give them some performance benifits.

From an operational standpoint, when they use the external array with the server the powerup/powerdown sequence is important.

Powerdown the server first, then the external device. Sounds simple, but people sometimes forget and this is the easyest way to data corruption and loss.

When they powerup, powerup the external devise first and let the drives spin to speed prior to powering up the server. Powering up both at the same time is not recommended basically because I was always told not to do it that way and it sounds reasonable to me.

About all I can think of off the top of my head....

bEH|||thank you.

Extents

I am running a series of consistancy checks against our
SQL Server 6.5 databases to ensure the integrity of the
data prior to migration to 2000. I am noticing that
several of our databases have thousands of extents. Does
SQL Server complely manage how it allocates its primary
data allocation as well as the extents? In other words,
does the administrator have any control as to how table
space is allocated? Doesn't performance decrease as the
number of extents is generated? Should I be concerned
with the thousands of extents I am seeing. Thanks.I wouldn't be too concerned unless for some reason the scan densities are
way too low,
such as extreme data modification over time resulting in lots of data
movement.
Run DBCC SHOWCONTIG on your largest tables to see what's up with that.
Otherwise, just let the server run its own show. It knows which extents are
allocated and which aren't, and does this on its own.
James Hokes
"NewGuy" <anonymous@.discussions.microsoft.com> wrote in message
news:00c301c3c8c2$f84f40e0$a501280a@.phx.gbl...
> I am running a series of consistancy checks against our
> SQL Server 6.5 databases to ensure the integrity of the
> data prior to migration to 2000. I am noticing that
> several of our databases have thousands of extents. Does
> SQL Server complely manage how it allocates its primary
> data allocation as well as the extents? In other words,
> does the administrator have any control as to how table
> space is allocated? Doesn't performance decrease as the
> number of extents is generated? Should I be concerned
> with the thousands of extents I am seeing. Thanks.|||The more data the more extents you will have. Your fill factor can have a
large influence on that as well as any fragmentation. When it gets migrated
to sql server it will get rearranged anyway so I wouldn't be too concerned
until after you convert it all. Just make sure there aren't any errors
shown by the CHECKDB or any of the associated DBCC commands before you move
it.
--
Andrew J. Kelly
SQL Server MVP
"NewGuy" <anonymous@.discussions.microsoft.com> wrote in message
news:00c301c3c8c2$f84f40e0$a501280a@.phx.gbl...
> I am running a series of consistancy checks against our
> SQL Server 6.5 databases to ensure the integrity of the
> data prior to migration to 2000. I am noticing that
> several of our databases have thousands of extents. Does
> SQL Server complely manage how it allocates its primary
> data allocation as well as the extents? In other words,
> does the administrator have any control as to how table
> space is allocated? Doesn't performance decrease as the
> number of extents is generated? Should I be concerned
> with the thousands of extents I am seeing. Thanks.|||Andrew,
LOL. I guess I could have read 'prior to migration'.
Silly me. :-)
James Hokes
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#szC0ENyDHA.3196@.TK2MSFTNGP11.phx.gbl...
> The more data the more extents you will have. Your fill factor can have a
> large influence on that as well as any fragmentation. When it gets
migrated
> to sql server it will get rearranged anyway so I wouldn't be too concerned
> until after you convert it all. Just make sure there aren't any errors
> shown by the CHECKDB or any of the associated DBCC commands before you
move
> it.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "NewGuy" <anonymous@.discussions.microsoft.com> wrote in message
> news:00c301c3c8c2$f84f40e0$a501280a@.phx.gbl...
> > I am running a series of consistancy checks against our
> > SQL Server 6.5 databases to ensure the integrity of the
> > data prior to migration to 2000. I am noticing that
> > several of our databases have thousands of extents. Does
> > SQL Server complely manage how it allocates its primary
> > data allocation as well as the extents? In other words,
> > does the administrator have any control as to how table
> > space is allocated? Doesn't performance decrease as the
> > number of extents is generated? Should I be concerned
> > with the thousands of extents I am seeing. Thanks.
>

extending function: view dependencies

So I have a number of separate databases on my SQL 2005 Server.

I also have a number of Reports in SSRS.

Many of the stored procedures in the various databases reference tables, functions, and stored procedures in other databases on the same server.

How can I accomplish the effect of right clicking on a stored procedure for instance, clicking view dependencies, and having everything show up in that list, not just the items in that DB?

It is all stored in the database in one form or another, but I must be missing some crucial piece to integrating it all together.

Note: I did not design this system, just maintaining and modifying existing items. There are no schemas to speak of. SSRS will most likely move to a dedicated server at one point with other data warehousing functions, so the ability to span servers would be useful.

Thanks!

Matt

Mhmm, I think this is not possible unless you parse the procedue on your own. There are no entries for dependencies in the system tables created for non-db-local objects. Additionally you can′t use schemabinding for maintainance as schemabinding applies only to two part names.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Are there any third party tools that would combine all this information into an enterprise version of "view dependencies"?

thanks|||

Hi,

Jens is correct. There is a lot of confusion about "sysdepends", so we've added a new section explaining SQL dependencies in a web refresh section of the BOL. Please read:

SQL Server 2005 Books Online

Understanding SQL Dependencies

New: 5 December 2005
http://msdn2.microsoft.com/en-us/library/ms345449.aspx

Re: " any third party tools" - A quick MSN search turned up

http://www.red-gate.com/products/sql_dependency_tracker/index.htm

which I have never used.

Regards

Exporting User/Role Permissions

I am not a DBA so please be gentle...

I am trying to export all of the user and role permissions out of several databases for auditing purposes. I see the Users and Roles listed under the Security tree view when I log into the database, but I do not see an option to export or query the permissions. In addition, we do not have any tables that reference user permissions in our databases. So, how would one go about exporting or querying this information?

I've seen similar topics where they recommend querying sys tables to gather the info, but I don't see those tables either. Any help would be greatly appreciated.

All my thanks!

- Isaac

Edit: I should add in that I am connecting to 7 and 2k DBs using 2k5 SMS. Not sure if that makes a difference...

You can query the tables, such as sys.server_permissions, sys.server_principals, sys.database_principals, sys.database_permissions, to display the permissions of database user and role and logins. E.g., if you want to look at the permission of user Bob, you can query as follows

select * from sys.database_permissions where grantee_principal_id =(select principal_id from sys.database_principals where name='Bob')

Exporting tables to another location.

Dear reader,
In SQL-server 2005 what are the preferred method to transport tables from
one database to another database, if those databases are not connected ? (To
copy the content).
In SQL-server 2000 I used sp_generate_inserts (Copyright © 2002 Narayana
Vyas Kondreddi. All rights reserved.) a lot to export smal tables to
transport them and import them in other locations. For most situations this
does also work in SQL-server 2005.
But what if this doesn't work, because of the size (length of records or
number of records in the table), or because of datatypes. What are preferred
methods to export a database to something that can be transported (a file
which is fairly compact) and then imported in another database ?
Thanks for your time and attention,
Ben BrugmanOn May 22, 1:32 pm, "ben brugman" <b...@.niethier.nl> wrote:
> Dear reader,
> In SQL-server 2005 what are the preferred method to transport tables from
> one database to another database, if those databases are not connected ? =(To
> copy the content).
> In SQL-server 2000 I used sp_generate_inserts (Copyright =A9 2002 Narayana
> Vyas Kondreddi. All rights reserved.) a lot to export smal tables to
> transport them and import them in other locations. For most situations th=is
> does also work in SQL-server 2005.
> But what if this doesn't work, because of the size (length of records or
> number of records in the table), or because of datatypes. What are prefer=red
> methods to export a database to something that can be transported (a file
> which is fairly compact) and then imported in another database ?
> Thanks for your time and attention,
> Ben Brugman
1=2E At Source Use BCP OUT to text file with Delimiter , Zip ( If file
is huge) , Upload through FTP At Destination FTP Download, UnZIP, BCP
IN (or BULK INSERT)
2=2E use SSIS or DTS

Exporting tables to another location.

Dear reader,
In SQL-server 2005 what are the preferred method to transport tables from
one database to another database, if those databases are not connected ? (To
copy the content).
In SQL-server 2000 I used sp_generate_inserts (Copyright 2002 Narayana
Vyas Kondreddi. All rights reserved.) a lot to export smal tables to
transport them and import them in other locations. For most situations this
does also work in SQL-server 2005.
But what if this doesn't work, because of the size (length of records or
number of records in the table), or because of datatypes. What are preferred
methods to export a database to something that can be transported (a file
which is fairly compact) and then imported in another database ?
Thanks for your time and attention,
Ben Brugman
On May 22, 1:32 pm, "ben brugman" <b...@.niethier.nl> wrote:
> Dear reader,
> In SQL-server 2005 what are the preferred method to transport tables from
> one database to another database, if those databases are not connected ? (To
> copy the content).
> In SQL-server 2000 I used sp_generate_inserts (Copyright 2002 Narayana
> Vyas Kondreddi. All rights reserved.) a lot to export smal tables to
> transport them and import them in other locations. For most situations this
> does also work in SQL-server 2005.
> But what if this doesn't work, because of the size (length of records or
> number of records in the table), or because of datatypes. What are preferred
> methods to export a database to something that can be transported (a file
> which is fairly compact) and then imported in another database ?
> Thanks for your time and attention,
> Ben Brugman
1. At Source Use BCP OUT to text file with Delimiter , Zip ( If file
is huge) , Upload through FTP At Destination FTP Download, UnZIP, BCP
IN (or BULK INSERT)
2. use SSIS or DTS

Exporting tables to another location.

Dear reader,
In SQL-server 2005 what are the preferred method to transport tables from
one database to another database, if those databases are not connected ? (To
copy the content).
In SQL-server 2000 I used sp_generate_inserts (Copyright 2002 Narayana
Vyas Kondreddi. All rights reserved.) a lot to export smal tables to
transport them and import them in other locations. For most situations this
does also work in SQL-server 2005.
But what if this doesn't work, because of the size (length of records or
number of records in the table), or because of datatypes. What are preferred
methods to export a database to something that can be transported (a file
which is fairly compact) and then imported in another database ?
Thanks for your time and attention,
Ben BrugmanOn May 22, 1:32 pm, "ben brugman" <b...@.niethier.nl> wrote:
> Dear reader,
> In SQL-server 2005 what are the preferred method to transport tables from
> one database to another database, if those databases are not connected ? =
(To
> copy the content).
> In SQL-server 2000 I used sp_generate_inserts (Copyright =A9 2002 Narayana
> Vyas Kondreddi. All rights reserved.) a lot to export smal tables to
> transport them and import them in other locations. For most situations th=
is
> does also work in SQL-server 2005.
> But what if this doesn't work, because of the size (length of records or
> number of records in the table), or because of datatypes. What are prefer=
red
> methods to export a database to something that can be transported (a file
> which is fairly compact) and then imported in another database ?
> Thanks for your time and attention,
> Ben Brugman
1=2E At Source Use BCP OUT to text file with Delimiter , Zip ( If file
is huge) , Upload through FTP At Destination FTP Download, UnZIP, BCP
IN (or BULK INSERT)
2=2E use SSIS or DTS

Exporting table structures to another database

We have databases with large numbers of tables. We have a separate database for each year. For various reasons, we need to export about 100 of the tables (Structure only, not their data) from last years database into this year's database. What is the best method for doing this? The import/export wizard creates the tables but does not bring in important things like keys.

Regards Shirley A

hi Shirley,

if you are using sql server 2000.

you can use the enterprise manager.

you can right click the database click on "all task" and then

click on "generate Sql scripts" then clcik on options.

click on the objects you need such as PKs, triggers and foreign keys"

for Sql server 2005 you can use the Management studio

right click the database. clcik on task. clcik on generate scripts

and check the options that you need.

after you generate the scripts run it on the new server

regards,

joey

|||

Thanks very much, that worked a treat. I used Enterprise Manager as our databases are SQL Server 2000. Regards

Shirley

Exporting SQL column to excel

Hello. For my first post, I will admit I'm a SQL Newb. I don't know alot, but I am learning as fast as possible. My access to SQL databases in previous positions has been limited, but not here.

I have a list of database users which I'd like to change the passwords for across the board. The SQL writers have requested an Excel file with two columns (username and password to be), but I don't know how to export the single column in either a notepad document or into excel. Any help would be appreciated.

Thanks!There are literally dozens of ways to do this. For a one time deal I'll usually just copy past the columns out of Enterprise Manager or Query Analyzer. It will paste right into Excel no problem.

If you want to get a little more fancy with it you can use an Excel query. Excel is capable of connecting directly to SQL Server and pulling down data. Check out this little article that explains the basics.
http://iis.asu.edu/ceslab/kb/excelquery.htm|||Use query analyzer

Type the following code:

Select dbo.tablename.field1, dbo.tablename.field2 from tablename

tablename as ur tablename which contain the information u required. Field1 is ur fild name.

Example: If the database has user table and it has field like employeeid, employeename, employeepassword, employeemail.

you can select ur records by the following code

Select employeename, employeepassword from user

Run this code and copy ur result to excel sheet.

or you can use Queryanalyzer-->Query Menu-->Result to fle (Then run the code and save the file.