OleDbProvider doesn''t support that type

• Kafka750

  SSC-Addicted

  Points: 479

  More actions

  February 27, 2006 at 1:52 am

  #89639

  Dear all,

   

  I am trying to insert a uniqueidentifier type into a varchar value but I am not be able to do; I can't work out. According BOL such type is not supported by OleDB Provider.

  So, I was wondering myself how might I do in order to save that value in my sql table???

   

  Value affected is the following: {41141BDF-0300-4A43-AB5B-C53C6ECA85BC} and at the end of the time is saved this: {41141BDF-0300-4A43-AB5B-C53C6.

  Nevertheless, it is fine saved from MMC.                                            

  Snippet VB code:

   

  params.Append cmd.CreateParameter("@PackageID", adWChar, adParamInput, 75)

  (or)

  params.Append cmd.CreateParameter("@PackageID", adVarChar, adParamInput, 75)

   

  adGuid -> (DataTypeEnum VB) -> DBTYPE_GUID (ole db Provider)-> uniqueidentifier (sql server type)

   

  DDL. Source Table:

   

  CREATE TABLE [sysdtspackages] (

   [name] [sysname] NOT NULL ,

   [id] [uniqueidentifier] NOT NULL ,

   [versionid] [uniqueidentifier] NOT NULL ,

   [description] [nvarchar] (1024) COLLATE Traditional_Spanish_CI_AS NULL ,

   [categoryid] [uniqueidentifier] NOT NULL ,

   [createdate] [datetime] NULL ,

   [owner] [sysname] NOT NULL ,

   [packagedata] [image] NULL ,

   [owner_sid] [varbinary] (85) NOT NULL CONSTRAINT [DF__sysdtspac__owner__1332DBDC] DEFAULT (suser_sid(N'sa')),

   [packagetype] [int] NOT NULL CONSTRAINT [DF__sysdtspac__packa__14270015] DEFAULT (0),

   CONSTRAINT [pk_dtspackages] PRIMARY KEY  CLUSTERED

   (

    [id],

    [versionid]

  &nbsp  ON [PRIMARY] ,

    UNIQUE  NONCLUSTERED

   (

    [versionid]

  &nbsp  ON [PRIMARY] ,

    FOREIGN KEY

   (

    [categoryid]

  &nbsp REFERENCES [sysdtscategories] (

    [id]

  &nbsp

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO

   

  DDL. Destination Table:

   

  CREATE TABLE [VISDTS_Dts] (

   [DtsId] [int] IDENTITY (1, 1) NOT NULL ,

   [Name] [varchar] (35) COLLATE Traditional_Spanish_CI_AS NULL ,

   [Owner] [varchar] (75) COLLATE Traditional_Spanish_CI_AS NULL ,

   [PackageID] [nvarchar] (75) COLLATE Traditional_Spanish_CI_AS NULL ,

   [CreationDate] [datetime] NULL ,

   [CreatorComputerName] [varchar] (25) COLLATE Traditional_Spanish_CI_AS NULL ,

   [Description] [varchar] (50) COLLATE Traditional_Spanish_CI_AS NULL ,

   CONSTRAINT [PK_VISDTS_Dts] PRIMARY KEY  CLUSTERED

   (

    [DtsId]

  &nbsp  ON [PRIMARY]

  ) ON [PRIMARY]

  GO

   

   

   

   

   

  Any ideas or thoughts about this issue would be very appreciated!!

  Thanks in advance and regards,

   

• Jo Pattyn

  SSC-Dedicated

  Points: 32350

  More actions

  February 27, 2006 at 11:03 am

  #623504

  Have you declared all sizes in your stored procedure?

  Like convert(varchar(mylength),my variable) instead of convert(varchar, myvariable)?