Post reply

insert problem

  • July 1, 2008 at 8:45 am

    #184171

    Hello Guys,

    I seem to have a weird problem, can insert data into table X using T SQL, but cannot insert data into table X using SQL Server Management studio.

    User has read and write permissions on the table.

    If I give DDL permissions, user can do it through SQL Server management studio, and if I dont give the user DDL permission he can insert it using TSQL but cannot do it using SQL server management studio.

    Error looks like this

    No row was updated.

    The data in row 7 was not committed.

    Error Source: .Net sqlclient Data provider

    Error message: Incorrect syntax near the keyword 'COLLATE'.

    If any1 has any idea, let me know.

    This would of great help for me.

    THanks,

    Nikhil

  • July 1, 2008 at 8:56 am

    #836749

    Is it possible you could post the insert statement and the table structure also (probably best if you just generate a CREATE TABLE script for the table in question.

    Thanks,

    Matt.

  • July 1, 2008 at 9:06 am

    #836761

    CREATE TABLE [dbo].[Table1](

    [ID] [dbo].[ID] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [DATE_OF_COLLECTION] [datetime] NULL,

    [DATE_OF_POSTING] [datetime] NULL,

    [PAYMENT_AMOUNT] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PAYMENT_SOURCE] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

    Also User can do it if I give DDL permissions and cannot do it without DDL permissions. He can do it through T SQL though. I have edited my intial post, if you can read through that, it will be more clear.

    Thanks for the reply.

  • July 1, 2008 at 9:15 am

    #836769

    Can you post the Insert statement please?

    Thanks,

    Matt.

  • July 1, 2008 at 9:58 am

    #836790

    INSERT INTO [dbo].[Table1](

    [DATE_OF_COLLECTION]

    ,[DATE_OF_POSTING]

    ,[PAYMENT_AMOUNT]

    ,[PAYMENT_SOURCE])

    VALUES

    ('12/12/2008','12/12/2008',1000,'test')

    Thanks for your reply.

  • July 1, 2008 at 2:59 pm

    #836908

    I've seen the Management Studio "Open Table" method for editing table data fail where there is no primary key. I see an identity column in your table, but I don't see a primary key constraint. Does it have one?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply