|
Worths to check this link.[^],I guess you have to convert result SELECT MAX(ID) FROM TEST into number.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hey mate,
Thank you for standing up for me until now, I just found the complete answer and thought to let you know..
DECLARE
N NUMBER(10);
BEGIN
SELECT MAX(ID)+1 INTO N FROM TEST;
-- CREATING SEQUENCE
Execute immediate ('DROP SEQUENCE SEQ_TEST');
Execute immediate ('CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH '||N||'
INCREMENT BY 1
CACHE 20
ORDER');
END;
/
CREATE OR REPLACE TRIGGER TRG_TEST_AUTONUMBER
BEFORE INSERT ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT SEQ_TEST.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
/
INSERT INTO TEST(NAME,YEAR)
SELECT NAME,2009 FROM TEST WHERE YEAR = 2008
/
Thanks again my friend!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Thanks,I learned still some new techique in Oracle from your solution
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I had the same kind of problem recently.
This is how I solved it:
DECLARE
ID NUMBER;
BEGIN
SELECT MAX(ID) INTO ID FROM TEST;
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH ' || ID || '
INCREMENT BY 1
CACHE 20
ORDER';
END;
/
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
That does it! Thanks mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
You're welcome.
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
Hi,
I'm going to deploy a Report Service Project, created in VS2008.
I've set these info:
OverwriteDataSources : False
TargetDataSourceFolder : \
TargetReportFolder : RSProjectTest (it's my project name)
TargetServerUrl : http://localhost/Reportserver
when I want to deploy 'Reporting Services Login' dialog appears. I've entered my sql login and my windows login, but none of them was accepted.
What should I do? what is this login ?
Best wishes
|
|
|
|
|
Hi
I installed Sql Server 2005 ManagemenStudio in my local machine to create sample tables in my local database. When I open the Sql Server its keep on asking me to connect to the server, can't I create local database and create tables and stored procedure there.
Can any one help me please.
Thanks in advance,
|
|
|
|
|
You should be able to connect to the localhost with windows authentication mode. Once you connect you can create databases and such. Doe sit give you an error message when you try to connect to the localhost?
|
|
|
|
|
You are totally missing the point.
In order to create any SQLServer database, whether local or remote, you have to be connected to an SQLServer server.
When you create the database you can set its location in the dialogue that Management Studio presents.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hello All,
I want to access different database(MySQL,Oracle,SqlServer etc) using ODBC dsn less connection with my application by providing the connection and database schema details at run time . so is there any specific connection string pattern which is accepted by all database providers. I mean to say
for MySQL connection string is like
Driver ={MySql Driver};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Port=3306;
for oracle we have
Driver=(Oracle in XEClient);dbq=111.21.31.99:1521/XE;Uid=myUsername;Pwd=myPassword;
so it replaced Server with dbq .. so is there any standard string parameters which could be used irrespective to database. and only SQL based databases I need to connect.
I hope you understand what I want to say
Thanks
Param
|
|
|
|
|
I have been off work for some days now and since I came back this morning, I keep getting this error when I start my SQL Server 2005... this is the error:
"An Exception occurred while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)"
Additional Information:
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)
What can I do to rectify it because it keeps giving similar error each time I try to update anything in my database
|
|
|
|
|
tolucole wrote: See the SQL Server errorlog for more information.
And what did the the error log say?
|
|
|
|
|
Why you don't put error message in google and check for solution?!
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi database experts,
How would you duplicate this table's data for the year 2009 taking care of the primary key column ID
ID NAME YEAR
-- ---- ----
1 A 2008
2 B 2008
3 C 2008
4 D 2008
To make it:
ID NAME YEAR
-- ---- ----
1 A 2008
2 B 2008
3 C 2008
4 D 2008
5 A 2009
6 B 2009
7 C 2009
8 D 2009
Please help guys,
Many thanks!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
insert into tablename<br />
select name,'2009' from tablename where [year]='2008'
Hope it will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hey there,
Thanks for your time but I get an error message of not enough values obviously because of ignoring the ID column, this's what my test table looks like:
SQL> DESC TEST
Name Null? Type
----------------------------------------- -------- ----------------
ID NOT NULL NUMBER
NAME VARCHAR2(10)
YEAR NUMBER(4)
SQL>
And I tried
SQL> INSERT INTO TEST
2 SELECT NAME,2009 FROM TEST WHERE YEAR=2008
3 /
INSERT INTO TEST
*
ERROR at line 1:
ORA-00947: not enough values
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Hi there, I found the answer and thought to let you know...
CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20
ORDER
/
CREATE OR REPLACE TRIGGER TRG_TEST_AUTONUMBER
BEFORE INSERT ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT SEQ_TEST.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
/
Now I can insert values the way you suggested but it will still be problematic that the sequence generates values already in the table's ID column.. Any idea how to make the above sequence starts with the MAX(ID) FROM TEST ??
Many thanks mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I'm glad that you found solution,by the way I apologyse because I didn't have free time to explore more solutions for your question.
Are you using Oracle as backend database,right? If yes then I didn't use yet Oracle because for our clients is enough MS SQL Server as backend database.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi Experts
Pls Help Me .
When i Login to SQL Server 2005 It Always Run in SQL server Authication mode Not in Window Auth. Mode. I mean to Disable The Window Auth. mode.
if It is not Possible Then How I Can Implementing Security on Particular Database even in Window Auth. mode .
So That Another Person Not To Open The database even in Window Auth. Mode.
Thank U
Dinesh Sharma
Software Developer
|
|
|
|
|
Sharma Dinesh Kumar wrote: I mean to Disable The Window Auth. mode.
That's not an option on the securities tab.
Sharma Dinesh Kumar wrote: So That Another Person Not To Open The database even in Window Auth. Mode.
The owner should be able to query the database, but you can argue about who the owner is. That might be a "real" user (a person), but it might also be an account that's specific for your application (a program account). There's an ASP.NET account on your machine that you probably don't have a password to. Similar, you might create an account for your SQL-demands, giving rights to that account to read/write to the database.
I are troll
|
|
|
|
|
I created a temp table with an order by clause. If later on I query it, would my result set still follow the order by sort or is it going to be indeterminate like querying an actual table? This is hard to test because 999 of 1000 times I might get an ordered result set.
Thanks in advance.
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
data in temp table will remain in the state and order as its been inserted untill you make any operation(sorting,shorting etc) on it
|
|
|
|
|
are you sure? someone answered differently from another (dbforums) forum.
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
The somone else doesn't know what they are talking about.
Simple to test, create a table with 1 field, insert 2 records and then select from the records. They will ALWAYS be returned in the order they were inserted unless you order them.
Here you go
DECLARE @tbl TABLE (sField VARCHAR(20))
INSERT @tbl (sField) VALUES ('Z')
INSERT @tbl (sField) VALUES ('A')
SELECT * FROM @tbl
Never underestimate the power of human stupidity
RAH
|
|
|
|