SQL Permission Issue? - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

SQL Permission Issue?

Author

Topic

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 17:38:50

I have an Access front end to a SQL Server 2012 back end. When I had a row insert, it shows up in Access, but not in my SQL Server Database despite being linked. Can someone explain to what is going on?

I also have another question that I posted where I am trying to insert 100 rows, but it is isn't automatically autonumbering the inserted rows so it will only insert 1 row at a time. Can someone help me with the syntax on that?

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 18:04:21

It looks like it is showing up. It just isn't ordering the PK field in the correct order.

The primary key field isn't incrementing by 1 in the SQL database.

Is that a setting in the Column properties? I am not sure why a PK int won't increment by 1.

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 18:22:37

It actually inserted the row between row 18 and 19 for row 27000 or so. Why wouldn't it append to the end in the correct order?

There has to be some little syntax secrets or something that I don't know about in SQL. Can someone please provide assistance?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 18:27:56

The order in a table is meaningless. If you want it inserted in a certain order, then add an ORDER BY to the query. I am making the assumption that Access is treating the linked file differently than say BULK INSERT/bcp/SSIS treats files.

Regarding your incrementing question, we would need to see the DDL for the table (CREATE TABLE script).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 18:52:37

I changed my table name to MyTable and Primary Key to PrimaryID

This is what I notice: There is a reference to WP_MyTable, which is an old table name and not the new one (MyTable) that is in there everywhere else.

The increment seems to be going by -1 for some reason.

==========================================================

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

==================================================

EXEC sys.sp_addextendedproperty @name=N'AggregateType', @value=N'-1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'AppendOnly', @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'17' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'CollatingOrder', @value=N'1033' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'ColumnHidden', @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'ColumnOrder', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'ColumnWidth', @value=N'1350' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'CurrencyLCID', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'DataUpdatable', @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'GUID', @value=N'????????' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'PrimaryID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'OrdinalPosition', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'Required', @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'ResultType', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'Size', @value=N'4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'SourceField', @value=N'PrimaryID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'SourceTable', @value=N'WP_MyTable' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'TextAlign', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

EXEC sys.sp_addextendedproperty @name=N'Type', @value=N'4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTable', @level2type=N'COLUMN',@level2name=N'PrimaryID'
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 18:59:17

We need to see the CREATE TABLE script. You can script it in the right click menu in Management Studio.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 19:04:34

I can't show the whole thing for other fields due to sensitive data. This is the reference to the PK field.

CREATE TABLE [dbo].[MyTable](
[PrimaryID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [aaaaaWP_MyTable_PK] PRIMARY KEY NONCLUSTERED
(
[PrimaryID] 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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 19:05:11

It is setup correctly to increment by 1 (+1).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 19:07:12

It isn't doing that for some reason.

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 19:13:43

Query within Access to Linked SQL Server Table:

INSERT INTO dbo_MyTable ( SecondaryKey, Field2, Field3 )
SELECT [PS].[PrimaryKey], [PS].[Field2], [PS].[Field3]
FROM PS
WHERE [PS].[PrimaryKey] NOT IN (SELECT [SecondaryKey] FROM [dbo_MyTable]);

There are about 100 rows. It will only insert 1 and then provide an error for the rest of them. Then the next time insert 1 and an error for the rest of them. Thus, would have to run it once for each insert. There shouldn't be a duplicate on the SecondaryKey or Primary key of MyTable.

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 19:19:35

dbo_MyTable.PrimaryID should be incrementing automatically with that query, should it not?

It is matching the SecondaryKey of that Table to the PrimaryKey of the PS table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 19:26:12

Your query looks fine and yes is incrementing. Show us some sample data and please post the error.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 19:35:42

Microsoft Access can't append all the records in the append query.
Microsoft Access set 0 fields(s) to Null due to a type conversion failure, and it didn't add 98 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 19:39:31

Run the query in Management Studio to test it out. If it too errors, then only run the SELECT portion of it. Show us the Management Studio errors, if any.

* I have no experience with MS Access but have extensive experience with SQL Server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 19:41:17

I can't run it in SQL Server. It is a Linked Excel file to a Linked SQL Server Table.

I am using Access as the front end to do this more easily.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 19:43:22

Import the excel file into SQL Server directly then so that we can test it. You can use the import wizard in Management Studio, bcp.exe, BULK INSERT, etc.

Import it into a new staging table that matches the layout of the file.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 19:49:20

Can you walk me through that?

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 19:54:11

There are a lot of columns. The purpose of the setup was to make it easy, not have to always build out huge tables to do inserts and updates, etc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 19:56:39

The import wizard will walk you through it. Right click on the database in Management Studio, go to Tasks then to Import Data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-29 : 20:35:33

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.MyTable' with unique index 'CSID'. The duplicate key value is (<NULL>).
The statement has been terminated.
=====================================================

I don't have such a field in my table so I am not sure what this is based on.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-29 : 20:41:58

You have a unique index on the CSID column. A unique index allows one row to be NULL, but not more than one row. You've got more than one row, hence the error.

You'll need to determine what to do for the other NULLs.

select CSID, *
from yourstagingtable
where CSID is null

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources