Automating the importation of an Oracle table into Sql Server 2000

Hi guys,

I figure this should not be a complex one. I know how to manually pull in data from Oracle 9i into SQL Server 2000 using DTS. However this is my issue....

I simply want to automate the pulling in of data from 1 table in my ORACLE 9i database into another table in my Sql Server 200. I was hoping I could simple write a stored procedure that would sort of utilize a dblink like in ORACLE and then schedule that procedure. Is this feasible in Sql Server, and how would one go about setting this automated import up??

Thanks in Advance all.........

'WaleDid 9i ever come out with an export utility?

Your best bet is to schedule the Oracle export, TRUNCATE the SQL Server table, then do a bcp load or BULK INSERT...

or you can set up a linked server in sql server and do a delete and an insert from the oracle table...

The latter will incur more over head...but will appear to be easier...|||problem is that the ORACLE database is not in my control. So I can't just schedule an export.|||I wonder if you can bcp out data from a linked server...

look into sp_addlinkedserver

You must authority to that box, right?|||yeah I do have authority on the SQL Server box. i'll look into it.|||You'll have to look into sp_addlinkedsrvlogin as well

automating the dumping of databaser in sql server 6.5

I don't happen to have any sql server 6.5 books. I need to know how
to automate the dumping of databases at a preset interval. I can do
it manually and do so each day but it would be nice if it would do it
on its own.
thanks
Hi,
You can Schedule this using the "Schedule Tasks" available in SQL Server 6.5
Enterprise manager.
THis schedule tasks will be ran by the service "SQL Executive", so ensure
that the service is up all the time.
How to do:-
1. Open enterprise manager
2. COnnect to the server
3. In the menu ,choose Server option and select "Scheduled tasks"
4. Click the first Icon (New Task)
5. Give the name as "Backup" and then select the type as "TSQL".
6. In the command type your "DUMP DATABASE COMMAND"
7. In the schedule option click "Recurring"
8. Click the change command button and select the intervals and days to
schedule
9. CLick ok
Thanks
Hari
MCDBA
<kg7poe@.yahoo.com> wrote in message
news:lcala0t54hin158dlsqtq9bka6k4f5b5ke@.4ax.com...
> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>
|||"kg7poe@.yahoo.com" wrote:

> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
Erik
Hi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:

> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:

> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created index
es
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexe
s
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automated DTS Package Problem

I need to create an Automated DTS package. At present I am running it manually each morning to import our phone switch data to an SQL table. The reason I had to go this route is this.

Because the Access tables are locked, I have a System DSN that connects to the System.mda file, this in turn gives me access to the locked tables to extract the data I need into Excel. I then drop the existing SQL table and run the DTS package recreating the SQL table with the data from the Excel file. I do not want to use OLEDB to connect to the Access table from within my application because it changes each month and of course the traffic load, I have as many 150 clients hitting the DB at one time.

I actually need the DTS package to update the SQL table with the latest data.

I'm no DTS guru, so how can I do this automagically?I managed to create the DTS package using my DSN, but I still have a few problems.

1. This is the Query that I need to use, it works every where else except insde the DTS Query Pane:

SELECT * FROM CallLog WHERE
(TTExtDest = '2002') AND (TTAnswered = '1')
AND (TTDateTimeIn >= DATEDIFF(dd, 1, GETDATE()))

It throws an OLEDB error telling me that GETDATE is an Undefined function.

2. I need to insert the above results into another table, this should work right?:

INSERT INTO SD_2004
SELECT * FROM CallLog
WHERE (TTExtDest = '2002') AND (TTAnswered = '1')
AND (TTDateTimeIn >= DATEDIFF(dd, 1, GETDATE()))

If I can overcome this Undefined Function bit, I got it whipped.

AUTO_UPDATE_STATISTICS

We have the "AUTO_UPDATE_STATISTICS" set to "On" in our
database. When you update the stats manually you can chose
either a full scan or a sample (%/rows). What amount does
the "auto_update_statistics" sample? Is it sample or full?
Can we modify what the "AUTO_UPDATE_STATISTICS" is
sampling?
I checked with BOL and it didn't indicate either way.
Any help would be greatly appreciated.
Thanks
SusanHi Susan
This should have the answer to your question:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/statquery.asp
Also, DBCC SHOW_STATISTICS should tell you whether the existing statistics
were generated using a fullscan or sampling.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Susan" <susanbauer@.yahoo.com> wrote in message
news:046101c35612$af627050$a301280a@.phx.gbl...
> We have the "AUTO_UPDATE_STATISTICS" set to "On" in our
> database. When you update the stats manually you can chose
> either a full scan or a sample (%/rows). What amount does
> the "auto_update_statistics" sample? Is it sample or full?
> Can we modify what the "AUTO_UPDATE_STATISTICS" is
> sampling?
> I checked with BOL and it didn't indicate either way.
> Any help would be greatly appreciated.
> Thanks
> Susan
>|||Kalen
This link does not seem to work anymore
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsql2k/html/statquery.asp
Regards
John|||It worked for me once I unwrapped it.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:089901c35677$db2ca920$a501280a@.phx.gbl...
> Kalen
> This link does not seem to work anymore
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/dnsql2k/html/statquery.asp
> Regards
> John

Auto stats

Hi all,
I have an dtsx (SSIS) for "clone" manually Sql server database to another.
How I copy all stats from one database to another ? I have problem with
"auto stats".
When I try DROP statitics for auto stats I get this error:
No se puede DROP el índice 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'.
No es una colección de estadísticas.
Cannot DROP index 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. Not
statitics collection.
What can I do '
-- Get Stats list
SELECT
'[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + ''
+ '.' + st.name + '' AS [Estadistica]
FROM
sys.tables AS tbl
INNER JOIN sys.stats st ON st.object_id=tbl.object_id
ORDER BY
[Table_Name_With_Schema] ASC,[Name] ASC
Thanks in advance, any help will be appreciated, regards, greetings
--
http://www.alhambra-eidos.es/web2005/index.html
www.kiquenet.net
http://www.setbb.com/putainformatica/viewtopic.php?p=843
www.trabajobasura.com/solusoft"Alhambra Eidos Kiquenet" <AlhambraEidosKiquenet@.discussions.microsoft.com>
wrote in message news:3B43B633-9B97-4AAF-9B5E-7B597F7EA970@.microsoft.com...
> Hi all,
> I have an dtsx (SSIS) for "clone" manually Sql server database to another.
> How I copy all stats from one database to another ? I have problem with
> "auto stats".
> When I try DROP statitics for auto stats I get this error:
> No se puede DROP el índice
> 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'.
> No es una colección de estadísticas.
> Cannot DROP index 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. Not
> statitics collection.
>
> What can I do '
>
> -- Get Stats list
> SELECT
> '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
> [Table_Name_With_Schema],
> '[' + st.name + ']' AS [Name],
> '' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + ''
> + '.' + st.name + '' AS [Estadistica]
> FROM
> sys.tables AS tbl
> INNER JOIN sys.stats st ON st.object_id=tbl.object_id
> ORDER BY
> [Table_Name_With_Schema] ASC,[Name] ASC
>
> Thanks in advance, any help will be appreciated, regards, greetings
> --
> http://www.alhambra-eidos.es/web2005/index.html
> www.kiquenet.net
> http://www.setbb.com/putainformatica/viewtopic.php?p=843
> www.trabajobasura.com/solusoft
>
Hi
You are probably trying to drop indexes as statistics, to exclude indexes
try:
SELECT '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + '' + '.' + st.name + ''
AS [Estadistica]
FROM sys.tables AS tbl
JOIN sys.stats st ON st.object_id=tbl.object_id
LEFT JOIN sys.indexes i on i.index_id = st.stats_id and i.object_id =st.object_id
WHERE i.index_id IS NULL
ORDER BY [Table_Name_With_Schema] ASC,[Name] ASC
If you only want auto_stats then
SELECT '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + '' + '.' + st.name + ''
AS [Estadistica]
FROM sys.tables AS tbl
JOIN sys.stats st ON st.object_id=tbl.object_id
LEFT JOIN sys.indexes i on i.index_id = st.stats_id and i.object_id =st.object_id
WHERE i.index_id IS NULL
AND st.auto_created = 1
ORDER BY [Table_Name_With_Schema] ASC,[Name] ASC
John

Auto Render as .pdf

Is there a way to force the output of a manually run report using
Report Manager to output as .pdf rather than html and having to export
from there?
I would like to enter a shipment number as a prompt, then go straight
to creating a shippping document as a .pdf
Thanks
BobHi Bob,
This should answer your query
http://msdn2.microsoft.com/en-us/library/ms152835.aspx
"Bob" wrote:
> Is there a way to force the output of a manually run report using
> Report Manager to output as .pdf rather than html and having to export
> from there?
> I would like to enter a shipment number as a prompt, then go straight
> to creating a shippping document as a .pdf
> Thanks
> Bob
>