Sequences?? - Database Discussion Boards

how this query can be written in sql server

lakshmichawala31-Mar-09 19:00

lakshmichawala

31-Mar-09 19:00

SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
FROM DUAL;

Re: how this query can be written in sql server

Blue_Boy31-Mar-09 21:02

Blue_Boy

31-Mar-09 21:02

Which result return your query?

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.

Re: how this query can be written in sql server

lakshmichawala31-Mar-09 21:05

lakshmichawala

31-Mar-09 21:05

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

Re: how this query can be written in sql server

Blue_Boy31-Mar-09 21:16

Blue_Boy

31-Mar-09 21:16

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.

Re: how this query can be written in sql server

lakshmichawala31-Mar-09 21:25

lakshmichawala

31-Mar-09 21:25

hi mr iam getting this error wen i executed in teh swl server

Conversion failed when converting datetime from character string.

Re: how this query can be written in sql server

Eddy Vluggen1-Apr-09 0:39

Eddy Vluggen

1-Apr-09 0:39

Try using the ISO format[^] for date/time values; cast('2002-09-10T14:10:10.123' as DateTime)

Good luck Smile | :)

I are troll Smile | :)

Looking for good tool to convert Oracle - sql server

E_Gold31-Mar-09 18:18

E_Gold

31-Mar-09 18:18

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

Sequences??

Muammar©31-Mar-09 6:31

Muammar©

31-Mar-09 6:31

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)
(><)

Re: Sequences??

Blue_Boy31-Mar-09 7:16

Blue_Boy

31-Mar-09 7:16

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.

Re: Sequences??

Muammar©31-Mar-09 18:47

Muammar©

31-Mar-09 18:47

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)
(><)

Re: Sequences??

Blue_Boy31-Mar-09 21:04

Blue_Boy

31-Mar-09 21:04

Thanks,I learned still some new techique in Oracle from your solution Big Grin | :-D

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.

Re: Sequences??

Jörgen Andersson31-Mar-09 8:45

Jörgen Andersson

31-Mar-09 8:45

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

Re: Sequences??

Muammar©31-Mar-09 18:42

Muammar©

31-Mar-09 18:42

That does it! Thanks mate!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Sequences??

Jörgen Andersson31-Mar-09 21:00

Jörgen Andersson

31-Mar-09 21:00

You're welcome.

"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon

Deploy report project

mehrdadc4831-Mar-09 4:00

mehrdadc48

31-Mar-09 4:00

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

Creating local database on sql server

gottimukkala31-Mar-09 2:53

gottimukkala

31-Mar-09 2:53

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,

Re: Creating local database on sql server

agent00zelda31-Mar-09 3:43

agent00zelda

31-Mar-09 3:43

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?

Re: Creating local database on sql server

Henry Minute1-Apr-09 1:26

Henry Minute

1-Apr-09 1:26

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.”

Accessing Multiple databases at runtime with ODBC

param_joshi31-Mar-09 1:19

param_joshi

31-Mar-09 1:19

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 Frown | :-(

.. 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 Smile | :)

Thanks
Param

please How can I resolve this error........

Motajo Tolu31-Mar-09 1:15

Motajo Tolu

31-Mar-09 1:15

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

Re: please How can I resolve this error........

Colin Angus Mackay31-Mar-09 1:39

Colin Angus Mackay

31-Mar-09 1:39

tolucole wrote:
See the SQL Server errorlog for more information.

And what did the the error log say?

*Developer Day Scotland - Free community conference Delegate Registration Open

Re: please How can I resolve this error........

Blue_Boy31-Mar-09 2:01

Blue_Boy

31-Mar-09 2:01

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.

duplicating table data while handeling primary key column

Muammar©

30-Mar-09 22:02

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)
(><)

Re: duplicating table data while handeling primary key column

Blue_Boy30-Mar-09 22:13

Blue_Boy

30-Mar-09 22:13

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.

Re: duplicating table data while handeling primary key column

Muammar©

30-Mar-09 22:44

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)
(><)

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