Converting a Oracle PL/SQL command into MS SQL

hi

I was wondering if anyone would be able to advise on converting oracle PL/SQL features into MSSQL.

For example i have the following sequence + trigger below but cant find any information on creating a sequence in MSSQL, is it possible?

create sequence a_SEQ
start with 001
increment by 1

create or replace trigger a_TG
before insert a
for each row
begin
select (concat('D',(cast(a_SEQ.nextval as varchar(4))))) into :new.a_id from dual;
end;

any links or tutorials would be great, i've got a couple of MSSQL 2005 books which do explain triggers but examples are really needed to understand the full functionality.

cheersAs it was for SQL 2000, the SQL 2005 Books Online (BOL) is for me the quickest way to research something.

The Oracle squence is one of the things I miss in SQL Server. The thing that comes close is an IDENTITY column. In your example you seem to generate an unique number for a table. So define a_id as an IDENTITY column (see CREATE TABLE in the BOL) and forget about the trigger.

Note, after rereading this, the IDENTITY column works better/simpler than a sequence :)|||hi there

yes that identity column works great and only takes a second through the GUI.

Just with oracle we were taught to insert a letter before the unique ID to help identify the tables more, (this was done using a sequence + trigger).

for example on a table called detective instead of:
ID fname sname
1 bil fish
2 fred frog
3 dave dog

It would display:
ID fname sname
D1 bil fish
D2 fred frog
D3 dave dog

This would help identify that the ID was coming from the detective table.

Do you know a way of doing something along these lines with MS SQL.

cheers|||I would say don't do it.

You know what table it's in and what column it's in

Personally I would avoid surrogate keys|||OK

cheers for the advise|||Personally I would avoid surrogate keys
Sputter...choke...cough...
...but anyway, PROPER use of surrogate keys would not require adding prefixes to indicate their location. That should be discouraged. A surrogate key should have no inherent relationship to the data it identifies.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:
> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
> >I have imported a dbf table into mssql in a char format 19720628.
> >
> >
> > How can I convert nvarchar to datetime?
>
>|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:
> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
> > DECLARE @.str varchar(10)
> >
> > SET @.str = '19720628'
> >
> > SELECT CONVERT(datetime, @.str, 101)
> >
> >|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
> > Thanks!
> >
> > How can I convert all the rows in a column using the method?
> >
> >
> > "GlennThomas5" wrote:
> >
> > > DECLARE @.str varchar(10)
> > >
> > > SET @.str = '19720628'
> > >
> > > SELECT CONVERT(datetime, @.str, 101)
> > >
> > >|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?
Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?
|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:

> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>
>
|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)
|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:

> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>
|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
[vbcol=seagreen]
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
[vbcol=seagreen]
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:

> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>
>|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:

> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
[vbcol=seagreen]
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
>|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
[vbcol=seagreen]
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
>|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert mssql file

I am running mssql server for a sharepoint site. I have a mssql .bak file
that needs to be converted to plain text sql queries.
Is it possible to convert it somehow?
You have to restore it (in SQL Server) and query its with SQL Server or pump
the data out to some more propetary format liek access.
HTH, Jens Suessmeyer.
"Nick Mirro" <dirdx@.comcast.net> schrieb im Newsbeitrag
news:utFWDpuVFHA.3488@.TK2MSFTNGP10.phx.gbl...
>I am running mssql server for a sharepoint site. I have a mssql .bak file
>that needs to be converted to plain text sql queries.
> Is it possible to convert it somehow?
>

Convert mssql file

I am running mssql server for a sharepoint site. I have a mssql .bak file
that needs to be converted to plain text sql queries.
Is it possible to convert it somehow?
You have to restore it (in SQL Server) and query its with SQL Server or pump
the data out to some more propetary format liek access.
HTH, Jens Suessmeyer.
"Nick Mirro" <dirdx@.comcast.net> schrieb im Newsbeitrag
news:utFWDpuVFHA.3488@.TK2MSFTNGP10.phx.gbl...
>I am running mssql server for a sharepoint site. I have a mssql .bak file
>that needs to be converted to plain text sql queries.
> Is it possible to convert it somehow?
>