Linked Server Pervasive <--> SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Linked Server Pervasive <--> SQL

Author

Topic

Yakkity
Starting Member

5 Posts

Posted - 2010-04-08 : 06:51:55

Hi all,

For quite some time I have a nightly script running which copies certain tables from a pervasive database to a SQL database. Now there are 3 fields which apparantly give freaky data, weird signs such as * $ etc. These fields, and only these fields, are LONGVARCHAR fields in the Pervasive database.

In VS Studio 2008 I get the following information of these fields:
Column Size: 65500
Data Type: OdbcType.Text
Length:
Nullable: yes
Ordinal: 85
Scale:
Type Name: LONGVARCHAR

Now for the script ( see below ), this has all the fields that work and one of the 3 fields that don't work. This is the last field in the SELECT statement.
If I remove in the line:
CAST(OCVOORW AS VARCHAR(250))

both times in the code in the case statement. Then I can run the script, but that's without the bad field.
I've tried quite a lot of other things, char instead of varchar .. convert ... none works.

Does anybody know how I can resolve this issue ?

USE [Briljant_Omnitracker]

GO

/****** Object: StoredProcedure [dbo].[P_RunSelect2] Script Date: 04/07/2010 12:15:29 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[P_RunSelect2]

(@Table_Name char(255) = NULL,

@MyLinkedServer varchar(255),

@ErrorNumber int = NULL OUTPUT)

as

BEGIN

Declare @SQL varchar(max)

BEGIN TRY

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME=@Table_Name)

EXEC ('DROP TABLE ' + @Table_Name)

END TRY

BEGIN CATCH

RAISERROR(52001, 16, 127)

END CATCH

--EXEC ('TRUNCATE TABLE ' + @Table_Name)

--Set @SQL = 'SELECT * INTO test2.dbo.' + @table_name + ' FROM OPENQUERY (MyLinkedServer, ''Select * from ' + @table_name + ''')'

SET @SQL = CASE UPPER(@Table_Name)

WHEN 'AFLEVER' THEN
'SELECT AFLEVERBK, AFLEVERNR, DATUM, KLANT, LVANR, NAAM AS FAKTD INTO Briljant_Omnitracker.dbo.' + @table_name + ' FROM OPENQUERY (' + @MyLinkedServer + ', ''Select * from ' + @Table_name + ''')'

WHEN 'EXVKLA' THEN'SELECT NUMMER, CLUSTERNR, VOICE, VX, VX2, AMV, VCENTRALE, VMODEM, RLA, OCDATUM, CCX, VINSTDATUM, AVSUPPORT, OCX, OCCCX, OCOX, VPRIME, VSECE,

VDUUROC, VTYPEOC, ALGEMEEN, NACECODE, ACTIEVEKLX, WAARDELR, WAARDEAT,

WAARDEPXS, LR2004, LR2005, LR2006, LR2007, LR2008, AT2004, AT2005, AT2006,

AT2007, AT2008, PXS2004, PXS2005, PXS2006, PXS2007, PXS2008, MX, AMM,

SHOPX, "2009", "2010", Q1X, Q2X, Q3X, Q4X, Q12010X, OX,

OFFICEOCX, OFFICEANDEX, AMO, OPRIME, OSECE, OCOFX, DX, DATAHARDX,

DATAURENX, AMD, PRIMENG, SECENG, "2009LR", "2009AT", "2009PXS", Q22010X,

Q32010X, Q42010X, VGRPFAC, MGRPFAC, OGRPFAC, DGRPFAC, VTYPECCC, VCCCHW,

VSOFTSUB, VSOFTSDAT, VOC, VOCADM, BADDEPTORX, BKMPLAN, CAST(OCVOORW AS VARCHAR(250))

INTO Briljant_Omnitracker.dbo.' + @table_name + ' FROM OPENQUERY (' + @MyLinkedServer + ', ''Select NUMMER, CLUSTERNR, VOICE, VX, VX2, AMV, VCENTRALE, VMODEM, RLA,

OCDATUM, CCX, VINSTDATUM, AVSUPPORT, OCX, OCCCX, OCOX, VPRIME, VSECE,

VDUUROC, VTYPEOC, ALGEMEEN, NACECODE, ACTIEVEKLX, WAARDELR, WAARDEAT,

WAARDEPXS, LR2004, LR2005, LR2006, LR2007, LR2008, AT2004, AT2005, AT2006,

AT2007, AT2008, PXS2004, PXS2005, PXS2006, PXS2007, PXS2008, MX, AMM,

SHOPX, "2009", "2010", Q1X, Q2X, Q3X, Q4X, Q12010X, OX,

OFFICEOCX, OFFICEANDEX, AMO, OPRIME, OSECE, OCOFX, DX, DATAHARDX,

DATAURENX, AMD, PRIMENG, SECENG, "2009LR", "2009AT", "2009PXS", Q22010X,

Q32010X, Q42010X, VGRPFAC, MGRPFAC, OGRPFAC, DGRPFAC, VTYPECCC, VCCCHW,

VSOFTSUB, VSOFTSDAT, VOC, VOCADM, BADDEPTORX, BKMPLAN, CAST(OCVOORW AS VARCHAR(250))

from ' + @Table_name + ''')'

ELSE
''

END BEGIN TRY

if @SQL <> '' exec (@SQL) /* use sp_executesql ??? */

Else

RAISERROR(52002, 16, 127)

END TRY

BEGIN CATCH

RAISERROR(52003, 16, 127)

END CATCH
END

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 07:56:18

WARNING: You may lose some data if OCVOORW contains more than 250 characters.

The problem is that you're casting it twice. The first time you cast it, you lose the column name. The second time you cast it, there is no column named OCVOORW in the result set returned by the FROM OPENQUERY.

To fix this, change the second occurrence of "CAST(OCVOORW AS VARCHAR(250))" TO "OCVOORW". This way, the first occurrence, which is selecting the result of the second occurrence, will be able to reference the OCVOORW column, and cast it accordingly.

There are 10 types of people in the world, those that understand binary, and those that don't.

Yakkity
Starting Member

5 Posts

Posted - 2010-04-08 : 10:28:37

Unfortunatly that didn't resolve the problem.

Error:
Msg 18054, Level 16, State 1, Line 40
Error 51002, severity 16, state 127 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 10:42:46

quote:
Originally posted by Yakkity

Unfortunatly that didn't resolve the problem.

Error:
Msg 18054, Level 16, State 1, Line 40
Error 51002, severity 16, state 127 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

That's a user defined error No, but there's nowhere in your code that raises that error No. As such, I can't tell you where it's coming from.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources