July 1, 2011 at 12:52 am
Hi All,
I have a view which have all columns from table name abc ,while inserting the table in sql server 2005 record got inserted into table successfully ,But query same when i executed in sql server 2008 R2 i got the following error.
Please see the below query.
INSERT
INTO abc ( mnc, desc, act,type ) VALUES ( 'new test', 'new test', 'N', 'N' )
Column Details:
Mnc -char(10) not null column
id -not null column with identity (1,1)
desc -char(50) null column
act - char(1)
type-char(1)
error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Note :table originally created in different database and view used from different database, above query in running in view created db,its through error,but query works in actually created database(ie., sql server 2008 r2 where table structure created )
Let me know, if you have any questions or concern
Thanks
Senthil
July 1, 2011 at 2:06 am
This was removed by the editor as SPAM
July 1, 2011 at 3:05 am
USE db1
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[test] AS
SELECT*
FROMdb2..test
Note: In db2 database actual table structure created.
July 1, 2011 at 3:10 am
This was removed by the editor as SPAM
July 1, 2011 at 3:33 am
Hi,
I recreated the view and inserted the record, still same error occurs.
the problem i found is in mnc field (char(10)), when i just run
INSERT INTO dbo.test (mnc) values ('ddd')
USE db1
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[test] AS
SELECT id,mnc,desc,act,type,t_editstamp FROM db2..test
Please let me know,any other changes i have to do.
July 1, 2011 at 3:59 am
This was removed by the editor as SPAM
July 1, 2011 at 5:14 am
Hi,
I drop the view and recreated using schema name in the view, still facing same issue.
USE db1
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[test] AS
SELECT id,mnc,desc,act,type,t_editstamp FROM db2.dbo.test
July 1, 2011 at 6:03 am
what kind of column is t_editstamp timestamp, datetime, varchar etc.
July 1, 2011 at 6:20 am
This was removed by the editor as SPAM
July 1, 2011 at 7:12 am
Hi,
Thanks for your reply, as i don’t have sysadmin role, i am not able to open the sql profiler.(I don’t have sa password).
Below are the exact table and view structure.
NOTE:
When I taken the structure and compared with previous version(i.e sql server 2005)the sys.sp_bindefault and sys.sp_bindrule details are missed in SQL Server 2008 R2,marked in yellow color below
SO I added sp_bindefault and sp_bindrule in table (sql 2008 R2 server) and executed the query
QUERY executed in Database2(i.e view database area)
INSERT INTO test ( mnc, desc, act, counter_type_P ) VALUES ( 'usdsdf', 'sfs', 'Y', 'N' );
Error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
QUERY executed in Database1
INSERT INTO test ( mnc, desc, act, counter_type_P ) VALUES ( 'usdsdf', 'sfs', 'Y', 'N' );
(1 row(s) affected)
View structure details (database2 database)
===========================================
USE [database2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[test] AS
SELECT urn,mnc,desc,act,type_P,editstamp
FROMdatabase1.dbo.temp_counter_group
Table structure in Database1 database:
================================
USE [Database1] database
GO
/****** Object: Table [dbo].[test] Script Date: 06/23/2011 07:58:54 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[test](
[urn] [int] IDENTITY(1,1) NOT NULL,
[mnc] [char](10) NOT NULL,
[desc] [char](50) NULL,
[act] [dbo].[ActiveFlag] NULL,
[type_P] [dbo].[BinaryFlag] NULL,
[editstamp] [dbo].[EditStamp] NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[urn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[Y]', @objname=N'[dbo].[test].[act]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[yes_no]', @objname=N'[dbo].[test].[act]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[N]', @objname=N'[dbo].[test].[type_P]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[yes_no]', @objname=N'[dbo].[test].[type_P]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[CurrentDateTime]', @objname=N'[dbo].[test].[editstamp]' , @futureonly='futureonly'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'urn'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'group mnemonic' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'mnc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value= description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'desc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'active flag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'act'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Physical counter flag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'counter_type_P'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last edit date/time stamp.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'editstamp'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'No longer used.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test'
GO
July 1, 2011 at 9:10 am
what are de definitions of the custom data types?
July 4, 2011 at 5:35 am
Hi Team,
I compared user defined datatype (activeflag , binaryflag and editstamp) again with sql server 2005 and sql server 2008,
In the Sql server 2008 the sp_bindefault and sp_bindrule missing, i drop and recreate the userdefind datatype again, now i am able to insert the records successfully.
Thank you all,
Senthil
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply