Oracle's dynamic SQL to SQL Server

In my PLSQL to TSQL migration I need to convert dynamic SQL. Do I have direct conversion of statements like:

DBMS_SQL.PARSE
DBMS_SQL.BIND_VARIABLE
DBMS_SQL.DEFINE_COLUMN
DBMS_SQL.FETCH_ROWS
DBMS_SQL.PARSE

I need to maintain the code as similar as possible to the original one.

Thanks

here is a good start

http://technet.microsoft.com/en-us/library/bb497070.aspx

|||Dynamic SQL interfaces are much simpler in SQL Server. Start by looking at sp_executesql system stored procedure first. The migration link posted by Meher will also be useful.

Oracle To SQLServer conversion for SELECT TO_CHAR(SYSDATE,DDMMYYYYHHMMSS)

Hi,

i am trying to convert the following oracle sql,

SELECT TO_CHAR(SYSDATE,'DDMMYYYYHHMMSS')
(oraclequery)
into a MSSQLServer sql query.

Could you help me to get the equivalent of the above query in sqlserver.

PS: i tried using the following query, but i cannot get the month equivalent as 01/02/03/04 instead i get january/febrauary/march, etc

SELECT
(DATENAME(d, GETDATE())+
DATENAME(m, GETDATE())+
DATENAME(yyyy, GETDATE())+
DATENAME(hh, GETDATE())+
DATENAME(mi, GETDATE())+
DATENAME(ss, GETDATE()))
AS "Month Name"
(ms sqlserver)
The above query gives output as "6November2004174837"

what i need as result is "06112004174837"

Thanks,
Gopi.
Follow your DREAMS...select replace(convert(char(10),getdate(),104),'.','')
+replace(convert(char(8),getdate(),14),':','')|||Thank you SQL Consultant. The following query also works.

SELECT
CASE
WHEN LEN(DATENAME(d, GETDATE())) = 1
THEN '0'+DATENAME(d, GETDATE())
ELSE
DATENAME(d, GETDATE()) END+
CAST(MONTH(GETDATE()) AS VARCHAR)+
DATENAME(yyyy, GETDATE())+
DATENAME(hh, GETDATE())+
DATENAME(mi, GETDATE())+
DATENAME(ss, GETDATE())
AS "Month Name"

Thanks,
Gopi.
Follow your DREAMS...|||actually, Registered User, that will not work in january through september

Oracle to Sql Server conversion

Something went a bit wrong. Converted Oracle 10g to Sql Server 2000.
Now, in the schema there is a column that allows nulls.
In Oracle if you look at the column, you see NULL.
However if I look at the same created in Sql Server it actually does not have a <NULL> value there.
When the conversion was done however, all of these NULLs from Oracle came across into Sql Server with the actual value of <NULL>.
This causes the problem that these objects are now no longer displayed within my application. They show up fine using Oracle, and if I use Sql Server from the start it is fine -- the column is just blank, not acutally NULL. I can force the <NULL> value by doing the ctrl+0 on the field, and that breaks it as well.

The column has to allow nulls, but the actual value cannot be <NULL> (in Sql Server). Any suggestions on getting rid of the NULL - I could do an update, but it actually just has to be blank rather than having a value. I tried an update to set it to ' ' but that didn't really work - here was my statement:
update [table] set [columnname] = '' where [columnname] = '<NULL>'

Any other suggestions besides try again? But if it has to be 'try the conversion again' then that's the answer.
Thanks muchupdate [table] set [columnname] = NULL where [columnname] = '<NULL>'

Oracle replication failed

Some replication jobs failed and it shows 'data conversion failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in online book. THanks in advance!

Where did you encounter this error? Command line interface or GUI?

Can you provide more information such as the repro steps, environment setting, and the table schema?

Regards,

Gary Chen|||I found the error 'The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.' in view sysnchronization status. and i checked the history in job agent that it shows
"Message
2005-12-14 03:35:33.805 Connecting to Subscriber 'CCBHK08D'
2005-12-14 03:35:33.977 Skipping file 'CCB_CLINM2_2.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2005-12-14 03:35:33.993 Bulk copying data into table 'CCB_CLINM2'
2005-12-14 03:35:34.259 Agent message code 20037. The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.
2005-12-14 03:35:34.321 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-14 03:35:34.337 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed"

A few small tables are replicated to the MSSQL2005 but some tables got the same error. The machine is running in 64-bits. And the replication testing in 32-bits is fine. Also, I found that some ODBC drivers are missing in MSSQL 2005 even installed with MDAC2.8. Is it support 32-bits only? Thanks in advance|||also, I found the error in replication monitor
"Error messages:
The process could not bulk copy into table '"dbo"."SYSTWODB_CABL"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Data conversion failed"|||

Below is the ddl of table (in both mssql and oracle) which failed in replication
USE [dev]
GO
/****** Object: Table [dbo].[SYSTWODB_EXCHR] Script Date: 12/14/2005 14:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYSTWODB_EXCHR](
[GLB_DTIME] [numeric](16, 0) NOT NULL,
[CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XCCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[EXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XEXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[REL_DAY] [numeric](5, 0) NOT NULL,
[XREL_DAY] [numeric](5, 0) NOT NULL,
[BASEX_CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[BSE_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[INP_DATE] [numeric](5, 0) NOT NULL,
[INP_TIME] [numeric](6, 0) NOT NULL,
[MID_MKT_LN] [numeric](14, 10) NOT NULL,
[MID_MKT_TY] [numeric](14, 10) NOT NULL,
[MKT_BUY] [numeric](14, 10) NOT NULL,
[MKT_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[MKT_SELL] [numeric](14, 10) NOT NULL,
[RATE_USAGE] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[SOFTLOCK] [numeric](2, 0) NOT NULL,
[WIDTH] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
CONSTRAINT [MSHREPL_49_PK] PRIMARY KEY CLUSTERED
(
[GLB_DTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
*******************************************

CREATE TABLE "LINC"."SYSTWODB_EXCHR"("GLB_DTIME" NUMBER(16) NOT
NULL, "CCY" VARCHAR2(4) NOT NULL, "XCCY" VARCHAR2(4) NOT NULL,
"EXCH_GRP" VARCHAR2(4) NOT NULL, "XEXCH_GRP" VARCHAR2(4) NOT
NULL, "REL_DAY" NUMBER(5) NOT NULL, "XREL_DAY" NUMBER(5) NOT
NULL, "BASEX_CCY" VARCHAR2(4) NOT NULL, "BSE_RATEID" VARCHAR2(7)
NOT NULL, "INP_DATE" NUMBER(5) NOT NULL, "INP_TIME" NUMBER(6)
NOT NULL, "MID_MKT_LN" NUMBER(14, 10) NOT NULL, "MID_MKT_TY"
NUMBER(14, 10) NOT NULL, "MKT_BUY" NUMBER(14, 10) NOT NULL,
"MKT_RATEID" VARCHAR2(7) NOT NULL, "MKT_SELL" NUMBER(14, 10) NOT
NULL, "RATE_USAGE" VARCHAR2(1) NOT NULL, "SOFTLOCK" NUMBER(2)
NOT NULL, "WIDTH" VARCHAR2(1) NOT NULL,
CONSTRAINT "SYSTWODB_EXCHR_PK" PRIMARY KEY("GLB_DTIME"),
CONSTRAINT "SYS_C0022019" CHECK("GLB_DTIME" IS NOT NULL),
CONSTRAINT "SYS_C0022020" CHECK("CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022021" CHECK("XCCY" IS NOT NULL),
CONSTRAINT "SYS_C0022022" CHECK("EXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022023" CHECK("XEXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022024" CHECK("REL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022025" CHECK("XREL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022026" CHECK("BASEX_CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022027" CHECK("BSE_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022028" CHECK("INP_DATE" IS NOT NULL),
CONSTRAINT "SYS_C0022029" CHECK("INP_TIME" IS NOT NULL),
CONSTRAINT "SYS_C0022030" CHECK("MID_MKT_LN" IS NOT NULL),
CONSTRAINT "SYS_C0022031" CHECK("MID_MKT_TY" IS NOT NULL),
CONSTRAINT "SYS_C0022032" CHECK("MKT_BUY" IS NOT NULL),
CONSTRAINT "SYS_C0022033" CHECK("MKT_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022034" CHECK("MKT_SELL" IS NOT NULL),
CONSTRAINT "SYS_C0022035" CHECK("RATE_USAGE" IS NOT NULL),
CONSTRAINT "SYS_C0022036" CHECK("SOFTLOCK" IS NOT NULL),
CONSTRAINT "SYS_C0022037" CHECK("WIDTH" IS NOT NULL))
TABLESPACE "URBIS" PCTFREE 10 PCTUSED 70 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 2604K NEXT 352K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING|||

Besides, I found a message regarding to the failed replication in error log

'Error: 14151, Severity: 18, State: 1'

Any Hints on it? Thanks in advance

|||

Let's try to narrow it down a bit further.

So, the replication works fine on 32bit server and all tables copied fine, right? Only 64bit SQL server is having problem? Has the replication ever work in your 64bit environment at all? Can you tell me what your environment, OS, topology, and platform?

A minimum series of steps that can help to reproduce the error.

What type of Oracle publisher do you use? Gateway or Complete?

I also notice that you uses "Chinese_Hong_Kong_Stroke_90_CI_AS" when you create your table. What is the character set that you have install on your SQL server, OS, and Oracle server?

Regards,

Gary

|||

1. same set of tables and same source DB (with vchar and number as data type)

2. source: oracle 9.2 running in AIX platform.

3, publisher is gateway

4. SQL collation is Chinese _hong_kong_stroke_90_CI_AS, OS is 'Chinese (Hong Kong SAR)'

5. transactional publication with push subscription

6. Destination: Win2003 standard x64 edition sp1 with MSSQL2005 EVAL edition (x64) while another machine is Win2003 standard edition sp1 MSSQL2005 eval edition

7. All tables can be replicated to MSSQL2005(32bits) but some of them failed in the 64 bits one.

points 1-5 are the same in both 32 and 64 bits machine

many many Thanks for your help

|||

Hi,

This may or may not help you but I came accross this "data conversion" error when i was trying to reinitialize a subscriber when we migrated the publisher from 2000 to 2005. Our distributor also migrated to a SQL 2005 instance and it was a push subscription.

The issue in this instance was that the underlying data type length for a UDF created at the subscriber originally by SQL 2000 was smaller than what SQL 2005 needed. During the initialization the UDF was not being created at the subscriber by the SQL 2005 as it was already there so the solution was to drop the UDF and let the initialization process create it. Luckily the UDF was not being used anywhere else so we were able to drop it.

Cheers,

Priyanga

Oracle Ltrim with 2 arguments conversion in SQL Server

How do I convert Oracle's LTRIM(char, set) to SQL Server?
Thanks,
JakeYou mean like..

DECLARE @.x char(100)
SELECT @.x = ' Brett '
SELECT '"'+@.x+'"'
SELECT '"'+RTRIM(LTRIM(@.x))+'"'|||Originally posted by Brett Kaiser
You mean like..

DECLARE @.x char(100)
SELECT @.x = ' Brett '
SELECT '"'+@.x+'"'
SELECT '"'+RTRIM(LTRIM(@.x))+'"'

I give you an example oracle Query:

Select RTRIM('abcdeee', 'e') from dual

Thanks,
Jake|||Man...it's been awhile for Oracle...damn I got get back to it...

Select REPLACE('abcdeee', 'e')

But it will be all chars in the string....|||What version are you talking about?

http://www.cit.uws.edu.au/docs/oracle/sqlref/function.htm#1025279|||hi Brett,

I think i'm not clear in my previous mail. Oracle 8 has an in-built function RTRIM with 2 arguments, namely 'char' and 'set'. It returns char, with all the rightmost characters that appear in 'set' removed.
Example:
SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
FROM DUAL;

RTRIM e.g
----
BROWNINGyxX

http://www.cit.uws.edu.au/docs/oracle/sqlref/function.htm#1025070
Is there any equivalent in-built function in SQL server?

Hope I'm clear now. Appreciate Your Help.
Jake

Originally posted by Brett Kaiser
What version are you talking about?

http://www.cit.uws.edu.au/docs/oracle/sqlref/function.htm#1025279|||anybody know how to do this.

Oracle 8 has an in-built function RTRIM with 2 arguments, namely 'char' and 'set'. It returns char, with all the rightmost characters that appear in 'set' removed.
Example:
SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
FROM DUAL;

RTRIM e.g
----
BROWNINGyxX

Is there any equivalent in-built function in SQL server?
If not, how to achieve this?

Jake

Originally posted by Jake K
hi Brett,

I think i'm not clear in my previous mail. Oracle 8 has an in-built function RTRIM with 2 arguments, namely 'char' and 'set'. It returns char, with all the rightmost characters that appear in 'set' removed.
Example:
SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
FROM DUAL;

RTRIM e.g
----
BROWNINGyxX

http://www.cit.uws.edu.au/docs/oracle/sqlref/function.htm#1025070
Is there any equivalent in-built function in SQL server?

Hope I'm clear now. Appreciate Your Help.
Jake|||Would this work for you:

select left('BROWNINGyxXxy', PATINDEX('%xy%','BROWNINGyxXxy') - 1)

I don't think it's precisely identical, but it yields the same result for your example:

BROWNINGyxX

Regards,

hmscott

Originally posted by Jake K
How do I convert Oracle's LTRIM(char, set) to SQL Server?

Thanks,
Jake|||thanks. It's working fine for me after doing a small correction - changed %xy% to %xy. If %xy% is used, the first occurence of xy will be return even if it is present in the mid of the char. i.e. for this string 'BROWNINGyxyXxy', the result will be 'BROWNINGy' but what the Oracle's RTRIM returns is 'BROWNINGyxyX'. If the pattern is changed to '%xy', then we will get the same result as the oracle's rtrim. hope i'm clear.

Once again thanks for the help.

Jake

Originally posted by hmscott
Would this work for you:

select left('BROWNINGyxXxy', PATINDEX('%xy%','BROWNINGyxXxy') - 1)

I don't think it's precisely identical, but it yields the same result for your example:

BROWNINGyxX

Regards,

hmscott

oracle date conversion

is really bugging me...
Found out that some date fields wont' be allowed on sql server so my syntax
is
select * from openquery('oracleserver', 'select column1, column2,
to_char(date, 'yyyy/mm/dd') as date from table')
this works fine when selecting but when inserting this result into a table
on sql server I'm in trouble
insert into table xx
(columns...)
select * from openquery('oracleserver', 'select column1, column2,
to_char(date, 'yyyy/mm/dd') as date from table')
I get an error saying use ROBUST PLAN.
then i put on Robust plan and it says The query processer could not produce
a query plan
Then I found this article that says post you troubles here...? Anyone ?
My next attempt would be to avoid linked server and use transform data task
in a DTS package to see if that helps
http://www.aspfaq.com/show.asp?id=2400Perhaps a language neutral datetime format will work better. Try converting
to the format yyyymmdd.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"michael v" <test@.test.com> wrote in message news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...

> is really bugging me...
> Found out that some date fields wont' be allowed on sql server so my synta
x
> is
> select * from openquery('oracleserver', 'select column1, column2,
> to_char(date, 'yyyy/mm/dd') as date from table')
> this works fine when selecting but when inserting this result into a table
> on sql server I'm in trouble
> insert into table xx
> (columns...)
> select * from openquery('oracleserver', 'select column1, column2,
> to_char(date, 'yyyy/mm/dd') as date from table')
> I get an error saying use ROBUST PLAN.
> then i put on Robust plan and it says The query processer could not produc
e
> a query plan
> Then I found this article that says post you troubles here...? Anyone ?
> My next attempt would be to avoid linked server and use transform data tas
k
> in a DTS package to see if that helps
> http://www.aspfaq.com/show.asp?id=2400
>|||Thanx for the reply but found out that it wasn't the date conversion at all.
It was a column with 4000 characters as then lenght.
When not trying to insert this column it works fine...
What to do ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOH5aqv0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Perhaps a language neutral datetime format will work better. Try
converting to the format yyyymmdd.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "michael v" <test@.test.com> wrote in message
news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...
syntax
table
produce
task|||Ahh, that explains the error message. You could try adding some substring to
short the number of
characters returned, I guess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"michael v" <test@.test.com> wrote in message news:eqzHfvv0FHA.1028@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> Thanx for the reply but found out that it wasn't the date conversion at al
l.
> It was a column with 4000 characters as then lenght.
> When not trying to insert this column it works fine...
> What to do ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uOH5aqv0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> converting to the format yyyymmdd.
> news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...
> syntax
> table
> produce
> task
>[/color]|||thanx
but I found an article mentioning trouble with row size limit / varchar
fields.
i changed the field from varchar to text and it worked.
Is there something I should now about the text field type ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#Wgp2vy0FHA.3892@.TK2MSFTNGP12.phx.gbl...
> Ahh, that explains the error message. You could try adding some substring
to short the number of
> characters returned, I guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "michael v" <test@.test.com> wrote in message
news:eqzHfvv0FHA.1028@.TK2MSFTNGP12.phx.gbl...
all.
in
?
data
>