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

Automatically Fire Trigger Or Procedure in sql server 2000

Hi Guys

I want to automatically fire a trigger or Procedure every day 8:00 P.m. How can we achieve this? is there any way in sql server which checks the time regularly and can fire the trigger or we have to write a batch which runs at the specified time and try to create a situation so that the trigger is automatically fired?
plssssss get back with solution.. this is very urgent....
thanks in advance...

Krishna

In Enterprise Manager, <server name> -> Management -> SQL Server agent -> Jobs

Here you can create your job.

|||

when i execute a procedure it will display error like this

Server: Msg 217, Level 16, State 1, Procedure sp_PPS_send_cdontsmail, Line 12
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Thanks

Krishna

|||Then you need to debug your stored proc and figure out what you're doing wrong. Looks like you have an inadvertent loop that's causing something to continuously call itself.|||

Hi Greg

it working i changed my procedure name then it will be working sucessfully message. but i mail was not sending. it will take more time. i am executing procedure in queryanalyzer it's work fine. i have a created a job it's won't work.

if you don't mind are you online in yahoo please response me

i am in rama_krishna76@.yahoo.com.

Krishna

|||I don't know anything about sending mail, but could it have to do with the login account that's executing the stored proc? What account is used to start the sql server agent service? When you logged into query analyzer, did you log in with the same account, or did you use a different one?

Automatic Data Fill-up

Hello guys!

I am relavtively new to ASP.NET programming ang was just starting out on my first project. I am using ASP.NET2.0 technology by using Visual Web Developer 2005 Express Edition and of course with SQL 2005 Express Edition.

I would like to develop a database for our IP addresses, so one field of my table in a SQL data is the field for IP addresses.

I would like to write a program wherein after clicking the button, that field will be automatically filled up with IP addresses (e.g, from 192.168.0.0 to 192.168.0.255).

How do I accomplish this kind of dynamic filling up of fields? Thanks a lot!

When the client clicks the button ,you can get client ip using the following code:

Dim strClientIPAs StringstrClientIP = Request.UserHostAddress()

Then you can save the strClientIP into your table:

 
Dim connAs New SqlConnection("Data Source=.\yourserver;Database=pubs;Integrated Security=SSPI;")Dim cmdAs New SqlCommand("INSERT INTO yourtable(clientip) SELECT @.clientIP", conn) conn.Open() cmd.Parameters.AddWithValue("@.clientIP",strClientIP)Dim iAs Int32 i = cmd.ExecuteNonQuery() Response.Write(i.ToString() +" row(s) has been written to clientIP") conn.Dispose()
 

Autoincrement in varchar value

hi Guys,
I've tangled in serious problem,
Is there any way in which we can autoincrement in
varchar value like i've one column in my table called PayCardId that should
be in 9 digit say '900001@.@.@.', now requirement is this to increment in same
format but it should automatically save in Database, for example
900001@.@.@.
.
.
900100@.@.@.
900101@.@.@.
900102@.@.@.
When it completes it's hundred series it should come in
thousand series like 901001@.@.@. and then
901002@.@.@.
901003@.@.@.
so on and so for
I'm sure there should be any way to
increment this, but i'm not able to think it this time, Please help me ASAP
Any help would be appriciated
ThanksHi
I am not sure whether this code will help you. if this is the logic. u can
cast the final value as string and insert into database.
CREATE TABLE #TempTable (CustID VARCHAR(9))
INSERT INTO #TempTable (CustID) VALUES ('900001ABC')
INSERT INTO #TempTable (CustID) VALUES ('900002XYZ')
INSERT INTO #TempTable (CustID) VALUES ('900003ABC')
DECLARE @.intNewValue INT
SELECT @.intNewValue = CAST(LEFT(MAX(CustID),6) AS INT) FROM #TempTable
SELECT @.intNewValue
SET @.intNewValue = @.intNewValue + 1
SELECT @.intNewValue
Thanks,
Ciju
"Manish Sukhija" wrote:

> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||Thanks a lot Ciju, it was realy helpful for me, i've got a good idea from
this code
thanks a lot again for giving quick response,
may god bless you
"Manish Sukhija" wrote:

> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||If you can touch on the table design yet..
then try this.
create TABLE [char_increment] (
[a] AS cast(id_num as varchar) + '@.@.@.',
[id_num] [bigint] IDENTITY (900000, 1) NOT NULL ,
[fname] [varchar] (20)
)
insert into char_increment (fname)
values ('a')
insert into char_increment (fname)
values ('b')
insert into char_increment (fname)
values ('c')
insert into char_increment (fname)
values ('d')

Auto-Grow and Shrink Via Code

Hey guys,
Does anybody know of a way to turn on the Auto-Grow feature for a
transaction log via T-SQL code? What we want to do is during a
scheduled job, turn the auto grow feature on then after the job is
finished, shrink the log, then cut auto grow back off?
Any thoughts? Thanks in advance!
Hunter
**************Please Post to Group so that all can benefit!Have you looked at the ALTER DATABASE topic in Books Online? Of particular
interest:
AUTO_SHRINK ON | OFF
If ON is specified, the database files are candidates for automatic periodic
shrinking.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Hunter" <bamared36054@.yahoo.com> wrote in message
news:2a0a8bc1.0312020836.3923452e@.posting.google.com...
> Hey guys,
> Does anybody know of a way to turn on the Auto-Grow feature for a
> transaction log via T-SQL code? What we want to do is during a
> scheduled job, turn the auto grow feature on then after the job is
> finished, shrink the log, then cut auto grow back off?
> Any thoughts? Thanks in advance!
> Hunter
> **************Please Post to Group so that all can benefit!|||Sorry, wrong section... 'grow' != 'shrink' Aaron! Look under filespec:
< filespec > ::=( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/