I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)
My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.
The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)
Any help is greatly appreciated!
JT
The goodness we share is rewarded twice!try to find the record that cause the failure:
select * From <your table name> where isdate(<date varchar column name>)=0
varchars in yyyy-mm-dd format usually converted to dates data type without problems...|||Thank you for the reply.
So your saying, if the table name is findet (financial detail) and the column name is fdate (nvarchar, 50).
Run the select against the varchar column?|||You've got data that can't be converted to date...
To see the offending rows...
SELECT * FROM findet WHERE ISDATE(fdate) = 0|||OK. I am testing it now. When I attempted to convert the column in Enterprise Mgr, I got the following error:
/*
Tuesday, April 13, 2004 13:20:17
User: sa
Server: NYCRPSTOR01
Database: Mysis
Application: MS SQLEM - Data Tools
*/
'findet' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.|||Originally posted by Brett Kaiser
You've got data that can't be converted to date...
To see the offending rows...
SELECT * FROM findet WHERE ISDATE(fdate) = 0
Did you run this?
What did you get?
$1,000 bucks you've got non date data in that column|||That worked!!! Thank you for your help!!!|||What worked?
Did you find non date data?|||before you change the column type find what cause the conversion error!|||There was data mis-entered 0004-04-10 (example). Since the year is out of range, converting it to datetime was not possible.
Now, how do I script my DTS package to exclude records that are out of range and put them in a separate table for exception reporting?
Again, thank you for your help! It is greatly appreciated!!!|||just load the entire data to table with varchar column then copy the wrong date format to errors table and delete these records from the original table, convert the good data from the original table into target table drop the original table fix the failed record and enter them into the target table
Showing posts with label informix. Show all posts
Showing posts with label informix. Show all posts
Thursday, March 29, 2012
Sunday, February 19, 2012
Convert Informix to SQL Server
Hello, i need to convert this store procedure from Informix to SQL
Server and only obtain error's. Can anyone help me?
CREATE PROCEDURE atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
DECLARE @.aux_numcarta INT,
@.aux_numcarta1 INT,
@.var_medant LIKE mudmed.medant,
@.var_cartatipo LIKE mudmed.cartatipo,
@.var_ramo LIKE mudmed.ramo,
@.var_apolice LIKE mudmed.apolice,
@.var_dataalter LIKE mudmed.dataalter,
@.var_registo LIKE mudmed.registo,
@.aux_medant LIKE mudmed.medant,
@.aux_cartatipo LIKE mudmed.cartatipo,
@.aux_ramo LIKE mudmed.ramo,
@.aux_apolice LIKE mudmed.apolice,
@.aux_dataenvio LIKE mudmed.dataenvio,
@.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
@.ja_esta_em_trans INT,
@.sql_error_num INT,
@.isam_error_num INT,
@.error_msg VARCHAR(100)
--ON EXCEPTION
-- SET sql_error_num, isam_error_num, error_msg
IF ja_esta_em_trans <> 1
ROLLBACK WORK
RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
END EXCEPTION;
LET ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
LET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF aux_numcarta IS NULL
LET aux_numcarta = 0
IF aux_numcarta1 IS NULL
LET aux_numcarta1 = 0
IF aux_numcarta1 > aux_numcarta
LET aux_numcarta = aux_numcarta1
END IF
LET @.aux_medant = -1;
LET @.aux_cartatipo = -1;
LET @.aux_ramo = -1;
LET @.aux_apolice = -1;
LET @.aux_numcarta1 = -1;
LET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999))
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta = aux_numcarta + 1;
LET aux_numcarta1 = aux_numcarta;
IF aux_cartatipo == var_cartatipo AND
aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
LET aux_dataenvio = NULL;
ELSE
LET aux_ramo = var_ramo;
LET aux_apolice = var_apolice;
LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END IF;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
Thanks,
Apaxe2000If you understand t-SQL as well as the Informix's SQL dialect, it shouldn't
be hard. Instead of translating line by line, understand the overall logic
in the stored procedure and re-write using t-SQL.
Anith|||But the problem is i don't know SQL Server dialect and need to convert
this.
This is the only thing i don't know how resolve in the project i have
to do. Programming in VB is easy. Programming in SQL Server not.|||Apaxe2000 wrote:
> But the problem is i don't know SQL Server dialect and need to convert
> this.
> This is the only thing i don't know how resolve in the project i have
> to do. Programming in VB is easy. Programming in SQL Server not.
You need to spec the problem properly and work from a spec rather than
someone else's code. At least that may be what you'll have to do if you
want an answer from this forum.
The code you posted appears to be a cursor and that probably isn't the
best way to accomplish the same thing in SQL Server. But without more
information on keys, constraints and your data it's difficult to give
you a good answer.
The following article explains the best way to describe your problem
here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||It seems that you've tried converting the procedure but you are getting
errors.
Why not show us what you've done and the errors that you're getting.
You would have to be lucky to find someone here that knows both dialects and
is inclined to do all the work for you.
Besides, there are few comments in the procedure showing us what's going on
and we don't know your table structures.
@.var_medant LIKE mudmed.medant - nothing like this in SQL. You'll have to
use the parameter name @.var_medant in the procedure
LET = SET in SQL server
http://www.sommarskog.se/ for error handling in SQL server
FOREACH would be a Cursor in SQL server although you may be able to write
this without one
CURRENT = getdate()
"Apaxe2000" <Apaxe2000@.gmail.com> wrote in message
news:1135096937.302611.60280@.g14g2000cwa.googlegroups.com...
> Hello, i need to convert this store procedure from Informix to SQL
> Server and only obtain error's. Can anyone help me?
> CREATE PROCEDURE atribui_num_carta
> @.dia_env INT,
> @.mes_env INT,
> @.ano_env INT
> DECLARE @.aux_numcarta INT,
> @.aux_numcarta1 INT,
> @.var_medant LIKE mudmed.medant,
> @.var_cartatipo LIKE mudmed.cartatipo,
> @.var_ramo LIKE mudmed.ramo,
> @.var_apolice LIKE mudmed.apolice,
> @.var_dataalter LIKE mudmed.dataalter,
> @.var_registo LIKE mudmed.registo,
> @.aux_medant LIKE mudmed.medant,
> @.aux_cartatipo LIKE mudmed.cartatipo,
> @.aux_ramo LIKE mudmed.ramo,
> @.aux_apolice LIKE mudmed.apolice,
> @.aux_dataenvio LIKE mudmed.dataenvio,
> @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
> @.ja_esta_em_trans INT,
> @.sql_error_num INT,
> @.isam_error_num INT,
> @.error_msg VARCHAR(100)
> --ON EXCEPTION
> -- SET sql_error_num, isam_error_num, error_msg
> IF ja_esta_em_trans <> 1
> ROLLBACK WORK
>
> RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
> END EXCEPTION;
> LET ja_esta_em_trans = 0;
> BEGIN
> ON EXCEPTION IN (-535)
> LET ja_esta_em_trans = 1;
> END EXCEPTION;
> BEGIN WORK;
> END
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
> FROM mudmedhist_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> IF aux_numcarta IS NULL
> LET aux_numcarta = 0
> IF aux_numcarta1 IS NULL
> LET aux_numcarta1 = 0
> IF aux_numcarta1 > aux_numcarta
> LET aux_numcarta = aux_numcarta1
> END IF
>
> LET @.aux_medant = -1;
> LET @.aux_cartatipo = -1;
> LET @.aux_ramo = -1;
> LET @.aux_apolice = -1;
> LET @.aux_numcarta1 = -1;
> LET aux_dataenvio_comp = CURRENT;
> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
> IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
> OR ((var_medant>=800000 AND var_medant<=899999)
> OR (var_medant>=5003000 AND var_medant<=5003500) OR
> (var_medant>=5012000 AND var_medant<=5012999))
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta1 = -1;
> ELSE
> IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
> aux_cartatipo <> var_cartatipo
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta = aux_numcarta + 1;
> LET aux_numcarta1 = aux_numcarta;
> IF aux_cartatipo == var_cartatipo AND
> aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
> LET aux_dataenvio = NULL;
> ELSE
> LET aux_ramo = var_ramo;
> LET aux_apolice = var_apolice;
> LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
> END IF;
> UPDATE mudmed
> SET numcarta = aux_numcarta1,
> dataenvio = aux_dataenvio,
> dataenvio_comp = aux_dataenvio_comp
> WHERE registo = var_registo;
> END FOREACH ;
> IF ja_esta_em_trans <> 1 THEN
> COMMIT WORK;
> END IF;
> END PROCEDURE;
> Thanks,
> Apaxe2000
>|||Hi.
This is the procedure i used:
CREATE PROCEDURE dbo.atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
AS
DECLARE @.aux_numcarta INT
DECLARE @.aux_numcarta1 INT
DECLARE @.var_medant LIKE mudmed.medant
DECLARE @.var_cartatipo LIKE mudmed.cartatipo
DECLARE @.var_ramo LIKE mudmed.ramo
DECLARE @.var_apolice LIKE mudmed.apolice
DECLARE @.var_dataalter LIKE mudmed.dataalter
DECLARE @.var_registo LIKE mudmed.registo
DECLARE @.aux_medant LIKE mudmed.medant
DECLARE @.aux_cartatipo LIKE mudmed.cartatipo
DECLARE @.aux_ramo LIKE mudmed.ramo
DECLARE @.aux_apolice LIKE mudmed.apolice
DECLARE @.aux_dataenvio LIKE mudmed.dataenvio
DECLARE @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp
DECLARE @.ja_esta_em_trans INT
DECLARE @.sql_error_num INT
DECLARE @.isam_error_num INT
DECLARE @.error_msg VARCHAR(100)
IF @.ja_esta_em_trans <> 1 BEGIN
ROLLBACK WORK
RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
END EXCEPTION;
SET @.ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
SET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF (aux_numcarta) IS NULL BEGIN
SET @.aux_numcarta = 0
END
IF aux_numcarta1 IS NULL BEGIN
SET @.aux_numcarta1 = 0
END
IF aux_numcarta1 > aux_numcarta
SET @.aux_numcarta = @.aux_numcarta1
END
SET aux_medant = -1;
SET aux_cartatipo = -1;
SET aux_ramo = -1;
SET aux_apolice = -1;
SET aux_numcarta1 = -1;
SET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999)) BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta = aux_numcarta + 1;
SET aux_numcarta1 = aux_numcarta;
END
IF aux_cartatipo == var_cartatipo AND aux_ramo = var_ramo AND
aux_apolice = var_apolice BEGIN
SET aux_dataenvio = NULL;
ELSE
SET aux_ramo = var_ramo;
SET aux_apolice = var_apolice;
SET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
I obtain this errors:
Error 156: Incorrect Syntax near the keyword 'LIKE'
Line 32: Incorrect Syntax near 'EXCEPTION'
Incorrect Syntax near the keyword 'ON'
Incorrect Syntax near the keyword 'END'
Line 75: Incorrect Syntax near ','
Line 82: Incorrect Syntax near '='
Line 87: Incorrect Syntax near '='
Line 94: Incorrect Syntax near '='
Line 95: Incorrect Syntax near '='
Line 108: Incorrect Syntax near 'FOREACH'
Incorrect Syntax near the keyword 'END'
The variable are:
LIKE mudmed.medant -> integer
LIKE mudmed.cartatipo -> smallint
LIKE mudmed.ramo -> smallint
LIKE mudmed.apolice -> smallint
LIKE mudmed.dataalter -> datetime
LIKE mudmed.registo -> integer
LIKE mudmed.dataenvio -> datetime
LIKE mudmed.dataenvio_comp -> datetime
Thanks for your help,
Apaxe2000|||Apaxe2000 (Apaxe2000@.gmail.com) writes:
> This is the procedure i used:
I don't know if you seriously expect someone to rewrite your Informix
code to SQL Server for you for free. Since I don't know Informix, I
don't know how far from Informix you have strayed, but you have quite
some way to go, before you are in SQL Server land.
If you are not interesting in learning T-SQL, and this is just a one-off,
I suggest that you try the local phonebook for consultants.
If you want to learn SQL Server, I will give you some hints.
> DECLARE @.var_medant LIKE mudmed.medant
Oracle has a similar feature, but SQL Server does not. You can only
declare a variable to be of a certain type. You can create your
own data-type alias, so that you can say:
EXEC sp_addtype mytype, 'varchar(12)'
and then you can can use mytype both in tables and for variables. But
I suspect that this does not help you here, as your tables supposedly
already exists. You will have to declare @.var_medant explicitly as
whatever type you need.
> IF @.ja_esta_em_trans <> 1 BEGIN
> ROLLBACK WORK
> RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
Note that if you pass a number to RAISERROR, this must be a number >= 50000,
and should have been defined with sp_addmessage. The most common is to
pass a string to RAISERROR with the error message.
The other two parameters are severity and state. Severity should be >= 11
and <= 16 and state is best set to 1.
> BEGIN
> ON EXCEPTION IN (-535)
> SET ja_esta_em_trans = 1;
> END EXCEPTION;
This syntax is not in SQL Server. You did not say which SQL Server you
are using, and this is an area where there are great difference. In SQL
2005, you can say:
BEGIN TRY
.. statements here
END TRY
BEGIN CATCH
.. error handling here
END CATCH
But in SQL 2000, you have no other choice than checking the global
variable @.@.error after each statement. Notice that this variable changes
value after each statement, so you need to put it into a local variable.
Also, note that in SQL 2000, there are errors you cannot catch at all,
since they abort the batch and rolls back the current transaction.
As for what you should check for in your error handler, I don't know,
but -535 is not an error number in SQL Server.
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
I don't know what this statement does in Informnix, but in SQL Server
it creates a table, and it fails since you did not provide a name
for the column. If aux_numcarta is a variable, the syntax is
SELECT @.aux_numcarta = Max(M1.NUMCARTA)
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = @.ano_env;
Assuming that DATAENVIO is indexes, the WHERE clause is best written as
WHERE M1:DATAENVIO BETWEEN ltrim(str(@.ano_env)) + '0101' AND
ltrim(str(@.ano_env)) + '1231'
This is because, if you put an indexed column into an expresson, you
nullify the benefit of the index. (I would suspect that this applies to
Informix as well.)
> SET aux_dataenvio_comp = CURRENT;
This means nothing in SQL Server.
> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
I don't know what this FORACH means, but it looks like a syntax for a
cursor. But looking at the code, I can't see anything that calls for
an iteration at all. You should proably rewrite this as a single
UPDATE statement in Informix first.
The CASE expression can be handy:
SELECT @.x = CASE WHEN @.y > 10 THEN 234
WHEN @.y > 6 AND @.w > 12 THEN 123
ELSE -23
END
This is ANSI, so it should work on Informix as well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||At 15/01/06 i go to another project in Oracle and VB.NET but i need to
do this before, but i continue obtain errors and can anyone helpme only
this time?
Thanks,
Apaxe2000|||Apaxe2000 wrote:
> At 15/01/06 i go to another project in Oracle and VB.NET but i need to
> do this before, but i continue obtain errors and can anyone helpme only
> this time?
> Thanks,
> Apaxe2000
Do you really think that "continue obtain errors" is an adequate
specification of a problem? No-one's likely to be able to offer much
help without a fuller description.
I don't want to give you bad advice based on my wild guesses about the
meaning of your legacy code. That's why I asked for the extra
information I did and gave you the article explaining how to do it. You
still haven't provided any DDL, sample data or even shown us what end
result you want. If you take the time to do that you'll be pretty sure
to get more responses.
David Portas
SQL Server MVP
--
Server and only obtain error's. Can anyone help me?
CREATE PROCEDURE atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
DECLARE @.aux_numcarta INT,
@.aux_numcarta1 INT,
@.var_medant LIKE mudmed.medant,
@.var_cartatipo LIKE mudmed.cartatipo,
@.var_ramo LIKE mudmed.ramo,
@.var_apolice LIKE mudmed.apolice,
@.var_dataalter LIKE mudmed.dataalter,
@.var_registo LIKE mudmed.registo,
@.aux_medant LIKE mudmed.medant,
@.aux_cartatipo LIKE mudmed.cartatipo,
@.aux_ramo LIKE mudmed.ramo,
@.aux_apolice LIKE mudmed.apolice,
@.aux_dataenvio LIKE mudmed.dataenvio,
@.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
@.ja_esta_em_trans INT,
@.sql_error_num INT,
@.isam_error_num INT,
@.error_msg VARCHAR(100)
--ON EXCEPTION
-- SET sql_error_num, isam_error_num, error_msg
IF ja_esta_em_trans <> 1
ROLLBACK WORK
RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
END EXCEPTION;
LET ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
LET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF aux_numcarta IS NULL
LET aux_numcarta = 0
IF aux_numcarta1 IS NULL
LET aux_numcarta1 = 0
IF aux_numcarta1 > aux_numcarta
LET aux_numcarta = aux_numcarta1
END IF
LET @.aux_medant = -1;
LET @.aux_cartatipo = -1;
LET @.aux_ramo = -1;
LET @.aux_apolice = -1;
LET @.aux_numcarta1 = -1;
LET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999))
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta = aux_numcarta + 1;
LET aux_numcarta1 = aux_numcarta;
IF aux_cartatipo == var_cartatipo AND
aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
LET aux_dataenvio = NULL;
ELSE
LET aux_ramo = var_ramo;
LET aux_apolice = var_apolice;
LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END IF;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
Thanks,
Apaxe2000If you understand t-SQL as well as the Informix's SQL dialect, it shouldn't
be hard. Instead of translating line by line, understand the overall logic
in the stored procedure and re-write using t-SQL.
Anith|||But the problem is i don't know SQL Server dialect and need to convert
this.
This is the only thing i don't know how resolve in the project i have
to do. Programming in VB is easy. Programming in SQL Server not.|||Apaxe2000 wrote:
> But the problem is i don't know SQL Server dialect and need to convert
> this.
> This is the only thing i don't know how resolve in the project i have
> to do. Programming in VB is easy. Programming in SQL Server not.
You need to spec the problem properly and work from a spec rather than
someone else's code. At least that may be what you'll have to do if you
want an answer from this forum.
The code you posted appears to be a cursor and that probably isn't the
best way to accomplish the same thing in SQL Server. But without more
information on keys, constraints and your data it's difficult to give
you a good answer.
The following article explains the best way to describe your problem
here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||It seems that you've tried converting the procedure but you are getting
errors.
Why not show us what you've done and the errors that you're getting.
You would have to be lucky to find someone here that knows both dialects and
is inclined to do all the work for you.
Besides, there are few comments in the procedure showing us what's going on
and we don't know your table structures.
@.var_medant LIKE mudmed.medant - nothing like this in SQL. You'll have to
use the parameter name @.var_medant in the procedure
LET = SET in SQL server
http://www.sommarskog.se/ for error handling in SQL server
FOREACH would be a Cursor in SQL server although you may be able to write
this without one
CURRENT = getdate()
"Apaxe2000" <Apaxe2000@.gmail.com> wrote in message
news:1135096937.302611.60280@.g14g2000cwa.googlegroups.com...
> Hello, i need to convert this store procedure from Informix to SQL
> Server and only obtain error's. Can anyone help me?
> CREATE PROCEDURE atribui_num_carta
> @.dia_env INT,
> @.mes_env INT,
> @.ano_env INT
> DECLARE @.aux_numcarta INT,
> @.aux_numcarta1 INT,
> @.var_medant LIKE mudmed.medant,
> @.var_cartatipo LIKE mudmed.cartatipo,
> @.var_ramo LIKE mudmed.ramo,
> @.var_apolice LIKE mudmed.apolice,
> @.var_dataalter LIKE mudmed.dataalter,
> @.var_registo LIKE mudmed.registo,
> @.aux_medant LIKE mudmed.medant,
> @.aux_cartatipo LIKE mudmed.cartatipo,
> @.aux_ramo LIKE mudmed.ramo,
> @.aux_apolice LIKE mudmed.apolice,
> @.aux_dataenvio LIKE mudmed.dataenvio,
> @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
> @.ja_esta_em_trans INT,
> @.sql_error_num INT,
> @.isam_error_num INT,
> @.error_msg VARCHAR(100)
> --ON EXCEPTION
> -- SET sql_error_num, isam_error_num, error_msg
> IF ja_esta_em_trans <> 1
> ROLLBACK WORK
>
> RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
> END EXCEPTION;
> LET ja_esta_em_trans = 0;
> BEGIN
> ON EXCEPTION IN (-535)
> LET ja_esta_em_trans = 1;
> END EXCEPTION;
> BEGIN WORK;
> END
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
> FROM mudmedhist_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> IF aux_numcarta IS NULL
> LET aux_numcarta = 0
> IF aux_numcarta1 IS NULL
> LET aux_numcarta1 = 0
> IF aux_numcarta1 > aux_numcarta
> LET aux_numcarta = aux_numcarta1
> END IF
>
> LET @.aux_medant = -1;
> LET @.aux_cartatipo = -1;
> LET @.aux_ramo = -1;
> LET @.aux_apolice = -1;
> LET @.aux_numcarta1 = -1;
> LET aux_dataenvio_comp = CURRENT;
> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
> IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
> OR ((var_medant>=800000 AND var_medant<=899999)
> OR (var_medant>=5003000 AND var_medant<=5003500) OR
> (var_medant>=5012000 AND var_medant<=5012999))
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta1 = -1;
> ELSE
> IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
> aux_cartatipo <> var_cartatipo
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta = aux_numcarta + 1;
> LET aux_numcarta1 = aux_numcarta;
> IF aux_cartatipo == var_cartatipo AND
> aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
> LET aux_dataenvio = NULL;
> ELSE
> LET aux_ramo = var_ramo;
> LET aux_apolice = var_apolice;
> LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
> END IF;
> UPDATE mudmed
> SET numcarta = aux_numcarta1,
> dataenvio = aux_dataenvio,
> dataenvio_comp = aux_dataenvio_comp
> WHERE registo = var_registo;
> END FOREACH ;
> IF ja_esta_em_trans <> 1 THEN
> COMMIT WORK;
> END IF;
> END PROCEDURE;
> Thanks,
> Apaxe2000
>|||Hi.
This is the procedure i used:
CREATE PROCEDURE dbo.atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
AS
DECLARE @.aux_numcarta INT
DECLARE @.aux_numcarta1 INT
DECLARE @.var_medant LIKE mudmed.medant
DECLARE @.var_cartatipo LIKE mudmed.cartatipo
DECLARE @.var_ramo LIKE mudmed.ramo
DECLARE @.var_apolice LIKE mudmed.apolice
DECLARE @.var_dataalter LIKE mudmed.dataalter
DECLARE @.var_registo LIKE mudmed.registo
DECLARE @.aux_medant LIKE mudmed.medant
DECLARE @.aux_cartatipo LIKE mudmed.cartatipo
DECLARE @.aux_ramo LIKE mudmed.ramo
DECLARE @.aux_apolice LIKE mudmed.apolice
DECLARE @.aux_dataenvio LIKE mudmed.dataenvio
DECLARE @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp
DECLARE @.ja_esta_em_trans INT
DECLARE @.sql_error_num INT
DECLARE @.isam_error_num INT
DECLARE @.error_msg VARCHAR(100)
IF @.ja_esta_em_trans <> 1 BEGIN
ROLLBACK WORK
RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
END EXCEPTION;
SET @.ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
SET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF (aux_numcarta) IS NULL BEGIN
SET @.aux_numcarta = 0
END
IF aux_numcarta1 IS NULL BEGIN
SET @.aux_numcarta1 = 0
END
IF aux_numcarta1 > aux_numcarta
SET @.aux_numcarta = @.aux_numcarta1
END
SET aux_medant = -1;
SET aux_cartatipo = -1;
SET aux_ramo = -1;
SET aux_apolice = -1;
SET aux_numcarta1 = -1;
SET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999)) BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta = aux_numcarta + 1;
SET aux_numcarta1 = aux_numcarta;
END
IF aux_cartatipo == var_cartatipo AND aux_ramo = var_ramo AND
aux_apolice = var_apolice BEGIN
SET aux_dataenvio = NULL;
ELSE
SET aux_ramo = var_ramo;
SET aux_apolice = var_apolice;
SET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
I obtain this errors:
Error 156: Incorrect Syntax near the keyword 'LIKE'
Line 32: Incorrect Syntax near 'EXCEPTION'
Incorrect Syntax near the keyword 'ON'
Incorrect Syntax near the keyword 'END'
Line 75: Incorrect Syntax near ','
Line 82: Incorrect Syntax near '='
Line 87: Incorrect Syntax near '='
Line 94: Incorrect Syntax near '='
Line 95: Incorrect Syntax near '='
Line 108: Incorrect Syntax near 'FOREACH'
Incorrect Syntax near the keyword 'END'
The variable are:
LIKE mudmed.medant -> integer
LIKE mudmed.cartatipo -> smallint
LIKE mudmed.ramo -> smallint
LIKE mudmed.apolice -> smallint
LIKE mudmed.dataalter -> datetime
LIKE mudmed.registo -> integer
LIKE mudmed.dataenvio -> datetime
LIKE mudmed.dataenvio_comp -> datetime
Thanks for your help,
Apaxe2000|||Apaxe2000 (Apaxe2000@.gmail.com) writes:
> This is the procedure i used:
I don't know if you seriously expect someone to rewrite your Informix
code to SQL Server for you for free. Since I don't know Informix, I
don't know how far from Informix you have strayed, but you have quite
some way to go, before you are in SQL Server land.
If you are not interesting in learning T-SQL, and this is just a one-off,
I suggest that you try the local phonebook for consultants.
If you want to learn SQL Server, I will give you some hints.
> DECLARE @.var_medant LIKE mudmed.medant
Oracle has a similar feature, but SQL Server does not. You can only
declare a variable to be of a certain type. You can create your
own data-type alias, so that you can say:
EXEC sp_addtype mytype, 'varchar(12)'
and then you can can use mytype both in tables and for variables. But
I suspect that this does not help you here, as your tables supposedly
already exists. You will have to declare @.var_medant explicitly as
whatever type you need.
> IF @.ja_esta_em_trans <> 1 BEGIN
> ROLLBACK WORK
> RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
Note that if you pass a number to RAISERROR, this must be a number >= 50000,
and should have been defined with sp_addmessage. The most common is to
pass a string to RAISERROR with the error message.
The other two parameters are severity and state. Severity should be >= 11
and <= 16 and state is best set to 1.
> BEGIN
> ON EXCEPTION IN (-535)
> SET ja_esta_em_trans = 1;
> END EXCEPTION;
This syntax is not in SQL Server. You did not say which SQL Server you
are using, and this is an area where there are great difference. In SQL
2005, you can say:
BEGIN TRY
.. statements here
END TRY
BEGIN CATCH
.. error handling here
END CATCH
But in SQL 2000, you have no other choice than checking the global
variable @.@.error after each statement. Notice that this variable changes
value after each statement, so you need to put it into a local variable.
Also, note that in SQL 2000, there are errors you cannot catch at all,
since they abort the batch and rolls back the current transaction.
As for what you should check for in your error handler, I don't know,
but -535 is not an error number in SQL Server.
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
I don't know what this statement does in Informnix, but in SQL Server
it creates a table, and it fails since you did not provide a name
for the column. If aux_numcarta is a variable, the syntax is
SELECT @.aux_numcarta = Max(M1.NUMCARTA)
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = @.ano_env;
Assuming that DATAENVIO is indexes, the WHERE clause is best written as
WHERE M1:DATAENVIO BETWEEN ltrim(str(@.ano_env)) + '0101' AND
ltrim(str(@.ano_env)) + '1231'
This is because, if you put an indexed column into an expresson, you
nullify the benefit of the index. (I would suspect that this applies to
Informix as well.)
> SET aux_dataenvio_comp = CURRENT;
This means nothing in SQL Server.
> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
I don't know what this FORACH means, but it looks like a syntax for a
cursor. But looking at the code, I can't see anything that calls for
an iteration at all. You should proably rewrite this as a single
UPDATE statement in Informix first.
The CASE expression can be handy:
SELECT @.x = CASE WHEN @.y > 10 THEN 234
WHEN @.y > 6 AND @.w > 12 THEN 123
ELSE -23
END
This is ANSI, so it should work on Informix as well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||At 15/01/06 i go to another project in Oracle and VB.NET but i need to
do this before, but i continue obtain errors and can anyone helpme only
this time?
Thanks,
Apaxe2000|||Apaxe2000 wrote:
> At 15/01/06 i go to another project in Oracle and VB.NET but i need to
> do this before, but i continue obtain errors and can anyone helpme only
> this time?
> Thanks,
> Apaxe2000
Do you really think that "continue obtain errors" is an adequate
specification of a problem? No-one's likely to be able to offer much
help without a fuller description.
I don't want to give you bad advice based on my wild guesses about the
meaning of your legacy code. That's why I asked for the extra
information I did and gave you the article explaining how to do it. You
still haven't provided any DDL, sample data or even shown us what end
result you want. If you take the time to do that you'll be pretty sure
to get more responses.
David Portas
SQL Server MVP
--
Convert Informix Stored Procedure
I would like to convert a couple informix stored procedures to SQL
Server stored procedures. I have no idea how to accomplish this. Here
is an example of one of the procedures I need to convert.
Server stored procedures. I have no idea how to accomplish this. Here
is an example of one of the procedures I need to convert.
drop function mnaf_calc_calendar_quarter;
CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
--************************************************** ***************************
-- Name: mnaf_calc_calendar_quarter
-- Description/Notes:
-- Calculates the most recent calendar quarter based on the end date.
--
-- Parms:
-- End Date.
-- Returns:
-- The calculated period start date and end date.
--
--************************************************** ***************************
-- Revisions:
-- PgmrDate # Description
-- HPI05/03/2005
--************************************************** ***************************
RETURNING date, date;
DEFINE dtStartDate date;
DEFINE dtEndDate date;
LET dtStartDate = mdy(12,31,1899);
LET dtEndDate = pEndDate;
-- If the end date parameter is equal to a calendar quarter,
-- calculate the start date by subtracting three months.
IF month(pEndDate) = 3 or month(pEndDate) = 6 or
month(pEndDate) = 9 or month(pEndDate) = 12 then
LET dtEndDate = pEndDate;
ELSE
-- Otherwise find the closest previous calendar quarter end date
-- then calculate the start date.
IF month(pEndDate) = 1 or month(pEndDate) = 4 or
month(pEndDate) = 7 or month(pEndDate) = 10 then
-- Subtract 1 month off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
month);
ELSE
-- Month must be equal to 2, 5, 8, 11
-- Subtract 2 months off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
month);
END IF;
END IF;
-- Calcuate the start date by subtracting off two months
LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);
RETURN dtStartDate, dtEndDate;
END FUNCTION;
grant execute on mnaf_calc_calendar_quarter to public;"Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1118858793.011839.15120@.g49g2000cwa.googlegro ups.com...
>I would like to convert a couple informix stored procedures to SQL
> Server stored procedures. I have no idea how to accomplish this. Here
> is an example of one of the procedures I need to convert.
> drop function mnaf_calc_calendar_quarter;
> CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
> --************************************************** ***************************
> -- Name: mnaf_calc_calendar_quarter
> -- Description/Notes:
> -- Calculates the most recent calendar quarter based on the end date.
> --
> -- Parms:
> -- End Date.
> -- Returns:
> -- The calculated period start date and end date.
> --
> --************************************************** ***************************
> -- Revisions:
> -- Pgmr Date # Description
> -- HPI 05/03/2005
> --************************************************** ***************************
> RETURNING date, date;
> DEFINE dtStartDate date;
> DEFINE dtEndDate date;
> LET dtStartDate = mdy(12,31,1899);
> LET dtEndDate = pEndDate;
> -- If the end date parameter is equal to a calendar quarter,
> -- calculate the start date by subtracting three months.
> IF month(pEndDate) = 3 or month(pEndDate) = 6 or
> month(pEndDate) = 9 or month(pEndDate) = 12 then
> LET dtEndDate = pEndDate;
> ELSE
> -- Otherwise find the closest previous calendar quarter end date
> -- then calculate the start date.
> IF month(pEndDate) = 1 or month(pEndDate) = 4 or
> month(pEndDate) = 7 or month(pEndDate) = 10 then
> -- Subtract 1 month off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
> month);
> ELSE
> -- Month must be equal to 2, 5, 8, 11
> -- Subtract 2 months off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
> month);
> END IF;
> END IF;
> -- Calcuate the start date by subtracting off two months
> LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);
> RETURN dtStartDate, dtEndDate;
> END FUNCTION;
> grant execute on mnaf_calc_calendar_quarter to public;
See "Date and Time Functions" in Books Online - DATEPART() and DATEADD()
will probably be the ones you're looking for. This article might also be
useful for general background information about manipulating datetime data:
http://www.karaszi.com/sqlserver/info_datetime.asp
Simon
Subscribe to:
Posts (Atom)