Roll backing the server - Database Discussion Boards

Howto dynamically set "Start with" value on a sequence in Oracle

Jörgen Andersson18-Mar-09 22:29

18-Mar-09 22:29

I want to create a sequence in Oracle, and I want the start value to be the same as the value in another sequence.

This doesn't work:

CREATE SEQUENCE CUSTOMERID_SEQ 
  MINVALUE 1
  MAXVALUE 99999999
  INCREMENT BY 1 
  START WITH (SELECT THAT_OTHER_TABLESPACE.CUSTOMERID_SEQ.NEXTVAL FROM DUAL)
  NOCYCLE
  NOORDER
  NOCACHE

As there can be no subqueries in a Create Sequence

Any hints?

Re: Howto dynamically set "Start with" value on a sequence in Oracle

Garth J Lancaster19-Mar-09 0:10

Garth J Lancaster

19-Mar-09 0:10

cant be done .. at least, not like this - start has to be an integer (so it doesnt even evaluate your select clause)

I could do it from c++ using oci - I'd do a [SELECT THAT_OTHER_TABLESPACE.CUSTOMERID_SEQ.NEXTVAL FROM DUAL] into an integer variable and then use that variable as a replacement into a create statement and execute the create statement.

Sorry, likely not what you were looking for ...

'g'

Re: Howto dynamically set "Start with" value on a sequence in Oracle

Jörgen Andersson19-Mar-09 1:11

Jörgen Andersson

19-Mar-09 1:11

I wonder if it's possible to do that in pl-sql?
Then it could be saved to a script.
Thanks for the idea!
I'll try it this afternoon.

Re: Howto dynamically set "Start with" value on a sequence in Oracle

Garth J Lancaster19-Mar-09 1:17

Garth J Lancaster

19-Mar-09 1:17

it should definately be do-able in pl-sql

'g'

Re: Howto dynamically set "Start with" value on a sequence in Oracle

Jörgen Andersson19-Mar-09 5:31

Jörgen Andersson

19-Mar-09 5:31

It worked, thanks again.

Re: Howto dynamically set "Start with" value on a sequence in Oracle

Jörgen Andersson19-Mar-09 5:30

Jörgen Andersson

19-Mar-09 5:30

For anyone interested, this is one way that works:

DECLARE
  SEQ_ID NUMBER;
BEGIN
  SELECT AN_OTHER_TABLESPACE.CUSTOMERID_SEQ.NEXTVAL INTO SEQ_ID FROM dual;
  
  EXECUTE IMMEDIATE 'CREATE SEQUENCE CUSTOMERID_SEQ
  MINVALUE 1
  MAXVALUE 99999999
  INCREMENT BY 1
  START WITH ' || SEQ_ID || '
  NOCYCLE
  NOORDER
  NOCACHE';
END;
/

Create and Execute a control flow task withing the Script task in SSIS

CodalcoholicNBrainy18-Mar-09 22:07

CodalcoholicNBrainy

18-Mar-09 22:07

I have one script task. Withing this script task, I need to create and execute Execute SQL Task.
Can be do so?

CodeManiac
xxxxxxxxxx
xxxxxxxxxx

Roll backing the server

jishbalan18-Mar-09 17:37

jishbalan

18-Mar-09 17:37

Hello Friends,

I am in trouble while loading data to SQL Server. i am using bulkcopy code to load datas.

I dont have much knowledge with SQL

While loading, if error occurs (in xtreme cases) loading wont be perfect(ie I having four tables to be loaded. error may occur while loading the fourth table. for me its like corrupting the tables. because for tables are interconnected)

So if error occurs i want to roll back other three tables to last state.
The below code is i used for loading and these function is calling four times for updating four tables

Can Rollback property be applied to the below code?? Please do help

Or any other way to prevent the tables from getting corrupted

------------------------------------------
Public Function rb_BCP(ByVal Server As String, ByVal Database As String, _
ByVal Table As String, ByVal FileName As String)

Dim objServer As SQLDMO.SQLServer
Dim objBCP As SQLDMO.BulkCopy
Dim objDB As SQLDMO.Database

On Error GoTo ErrorHandler
Set objServer = New SQLDMO.SQLServer
Set objBCP = New SQLDMO.BulkCopy

objServer.LoginSecure = True
objServer.EnableBcp = True
objServer.QuotedIdentifier = True
objServer.Connect (Server)

Set objDB = objServer.Databases(Database)

With objBCP
.UseExistingConnection = True
.DataFilePath = FileName
.RowDelimiter = vbCrLf
.IncludeIdentityValues = True
.ServerBCPKeepNulls = True
.DataFileType = SQLDMODataFile_SpecialDelimitedChar
.ColumnDelimiter = "|"
.IncludeIdentityValues = True
.ImportRowsPerBatch = 1000000
'Below speeds things up
'but does not log the bulk copy operation
'comment out if this is not what you
.UseBulkCopyOption = True
End With
objDB.Tables(Table).ImportData objBCP

Set objBCP = Nothing
objServer.Disconnect
Set objServer = Nothing

Exit Function
ErrorHandler:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description

End Function

jishith

No rollback with Blkcopy

David Mujica19-Mar-09 4:36

David Mujica

19-Mar-09 4:36

Check this article out ...

http://msdn.microsoft.com/en-us/library/tchktcdk(VS.80).aspx[^]

Setup a remote MySQL database

Member 441789218-Mar-09 4:38

Member 4417892

18-Mar-09 4:38

Hello.

Im running a webserver with apache and mysql 5.xx
Now I want to connect to the MySQL database on a server outside my own. But it doesnt allow remote connections from my mysql database.
I have port 3306 open and the firewall off.

How can I setup MySQL to accept remote connection?

Best Regard
Mads

Re: Setup a remote MySQL database

Eytukan18-Mar-09 4:54

Eytukan

18-Mar-09 4:54

Did you check the "Enable TCP/IP connections" option when you run the instance configuration? If not you can just reconfigure.

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

Re: Setup a remote MySQL database

Member 441789218-Mar-09 5:09

Member 4417892

18-Mar-09 5:09

I installed it together with wamp.
And can't find such just thing in the mysql.ini .. What do I have to change in the mysql.ini to enable TCP/IP connections ?

Re: Setup a remote MySQL database

Eytukan18-Mar-09 5:41

Eytukan

18-Mar-09 5:41

Try this one[^] the url looks insultingly explanatory. Wink | ;)

lol

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

Re: Setup a remote MySQL database

Member 441789218-Mar-09 6:00

Member 4417892

18-Mar-09 6:00

I use windows for hosting mysql..
I have tried look in my.ini for these bind-adress and the other configs.. But can't find anything at all in my.ini that seems to have something to do with TCP/IP configuration.

sqlconnection::open()

Eytukan18-Mar-09 4:17

Eytukan

18-Mar-09 4:17

Why SQL "blocks" when it cannot connect to a server? I would want it to return immediately on failure. Connection-timeout attribute looks like useless. I gave it as 5 seconds, but it continues to block for an eternity. Any help?

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

Re: sqlconnection::open()

Vimalsoft(Pty) Ltd19-Mar-09 1:01

Vimalsoft(Pty) Ltd

19-Mar-09 1:01

Please Explain in Detail what is your Problem

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za

Re: sqlconnection::open()

Eytukan19-Mar-09 5:40

Eytukan

19-Mar-09 5:40

I thought I explained enough. Anyway here I go:

1. There's an SQL server waiting for you to connect. It's address it 123.134.1.2.
2. Now your application connects, uses DB, finally closes connection. Fine.
3. Now server shuts down, restarts. It's IP is 123.134.2.3 (changed)
4. Your application tries to connect to the old address 123.134.1.2, which doesn't exist.
Waits,
Waits,
Waits,
Waits,
Waits,
Waits,
Waits,
Waits,
Waits,
5. Throws a DB exception (May be wrong server/network error)

I want to remove

Waits,
Waits,
Waits,
Waits,
Waits,
Waits,
Waits,
Waits,
Waits,

and want it to throw the error immediately.

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

Re: sqlconnection::open()

Vimalsoft(Pty) Ltd19-Mar-09 20:26

Vimalsoft(Pty) Ltd

19-Mar-09 20:26

Why do you use an IP ? Because it changes, why dont you use its name ?

Re: sqlconnection::open()

srinivasa rao dutta3-Jan-10 18:13

srinivasa rao dutta

3-Jan-10 18:13

I think even using DB name is not a proper solution. What will happen if there is some error in DB Name?
(http://www.improve.dk/blog/2008/03/10/controlling-sqlconnection-timeouts[^])This URL gives some good solution for this problem.

Creating Table dynamically in Oracle 9i

Mygrp18-Mar-09 4:08

Mygrp

18-Mar-09 4:08

Hey friends
can anyone suggest the method for creating Table Dynamically in Oracle 9i..
Will b very Thankfull for ur Help..

Re: Creating Table dynamically in Oracle 9i

Eddy Vluggen18-Mar-09 4:21

Eddy Vluggen

18-Mar-09 4:21

You'd do a "create table" from code.

No thanks, you're welcome Smile | :)

I are troll Smile | :)

how can write this oracle statement in sql server

lakshmichawala18-Mar-09 1:58

lakshmichawala

18-Mar-09 1:58

InStr(AUD_COMMENTS,tTempEx1,1,1)-(InStr(AUD_COMMENTS,tTempEx +,1,1)+2)

Re: how can write this oracle statement in sql server

Eddy Vluggen18-Mar-09 2:02

Eddy Vluggen

18-Mar-09 2:02

You're looking for the parts about CharIndex and SubString, and the documentation is here[^] Smile | :)

I are troll Smile | :)

crossmatch data

AndyInUK18-Mar-09 1:04

AndyInUK

18-Mar-09 1:04

Hi All,

There is a table with 200,000 rows and 7-8 columns with registeration number as a unique key.

Now i have xls sheet where i have got 16,000 registeration numbers. I need to crossmatch these numbers with the one in sql 2008 database to see how many matches with the database ?

Any help would be greatly appreciated.
Thanks

Re: crossmatch data

moon_stick18-Mar-09 1:11

moon_stick

18-Mar-09 1:11

You need to import the Excel spreadsheet into a new table in the database and then join the existing table onto the new table using the registration number as the join condition.

It definitely isn't definatley

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.