|
actaully in sql serve it is telling that to_timestamp does not exit ..so i want wat is the alternate of this particular function in sql server
|
|
|
|
|
Your query is in Oracle and it to convert it in T-SQL then it should be near to select convert(varchar,cast('10-Sep-02 14:10:10.123000' as DateTime),120)
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 mr iam getting this error wen i executed in teh swl server
Conversion failed when converting datetime from character string.
|
|
|
|
|
Try using the ISO format[^] for date/time values; cast('2002-09-10T14:10:10.123' as DateTime)
Good luck
I are troll
|
|
|
|
|
Hi
I'am looking for good tool that can convert from Oracle to sql server and from sql server to oracle.
I found some, but there was many errors during the conversion
thank's in advance
|
|
|
|
|
Hey everyone,
How would you create a simple sequence that starts with the maximum value of a table column
CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH (SELECT MAX(ID) FROM TEST)
INCREMENT BY 1
CACHE 20
ORDER
/
START WITH (SELECT MAX(ID) FROM TEST)
*
ERROR at line 3:
ORA-01722: invalid number
May be by creating a temporary variable to store that value and use it after the START WITH .. If so, please show me how or kindly suggest another way round
Many thanks guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
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.
|
|
|
|