473,326 Members | 2,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Identity Insert

Dear All,

May I ask you a question.

I have got bottle neck in my application.

My vb.net program read Product info from one database and insert to another database.

I wanna to be same ProductID for all databases.

So I used Identity Insert statement in my code and it run properly with sa (system admin role) but it cannot work with other user who is not in system admin role.

Please give me suggestion for this issue.

Thanks in advance.

Here is my code for it.

Expand|Select|Wrap|Line Numbers
  1. Dim objCmd As SqlCommand
  2. Dim objDataAdapter As New SqlDataAdapter
  3. Dim objReader As SqlDataReader
  4. Dim strSQL As String
  5. Dim con As SqlClient.SqlConnection
  6. Dim adapter As SqlClient.SqlDataAdapter
  7. Dim OtherConnection As String
  8.  
  9. OtherConnection = "Data Source=. ;Initial Catalog=MainSaleData;user id=mike;password=mike227;Connect Timeout=360"
  10.  
  11.  
  12. strSQL = "SET IDENTITY_INSERT [Products]ON INSERT INTO Products (ProductID, [Product Code], ProductName   VALUES  ( '" & ProductID & "', '" & pcode & "', '" & ProductName & " ) " & "   SET IDENTITY_INSERT [Products]OFF "
  13.  
  14. con = New SqlClient.SqlConnection(str)
  15. con.ConnectionString = OtherConnection
  16. con.Open()
  17. objCmd = New SqlCommand(strSQL, con)
  18. objDataAdapter.InsertCommand = objCmd
  19. objReader = objCmd.ExecuteReader
  20.  

Mike
Jun 16 '11 #1
2 3831
Joseph Martell
198 Expert 128KB
This sounds like you have a permissions problem with the user you are using to run the query. I have seen some companies in the past create an "application user id" which is just a user account that is created for, and used by, a specific application. This application user id is then given the required permissions for the target database, file access, etc. This avoids needing to elevate a normal user to an administrator role, which most SQL Admins and network admins do not like to do.

The other option is to talk to your SQL Admin (or do it yourself, if you have that privilege) to change the permissions of your table so it does not take an administrator to use Identity_Insert.
Jun 16 '11 #2
Dear Joseph,


Thanks for your suggestion.

I have already talked to SQL Admin for it but they do not agree and do not want to modify back end.

So I have got headache to solve it from my program.


Mike
Jun 17 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: (Pete Cresswell) | last post by:
This doesn't work because the first INSERT is creating multiple records for multiple projects. @@IDENTITY, then, contains the Identity column value for the last tblWeekReportedLine record...
2
by: Edward | last post by:
SQL 7.0 I have a form in ASP.NET and I want to write the values to the SQL Server tables. The tables are Customer and Address tables. There will be an insert into the Customer table, and I...
6
by: Maria L | last post by:
I have a client who needs to copy an existing sale. The problem is the Sale is made up of three tables: Sale, SaleEquipment, SaleParts. Each sale can have multiple pieces of equipment with...
5
by: grzes | last post by:
MS SQL Server 2000. My case is: I have the table T with primary key calling __recid int without identity property. This table includes a lot of records (about 1000000). I need to convert __recid's...
3
by: isaacrajan | last post by:
Hello, How do I get SQL server 2000 to continue the sequence of identity column values after I perform an insert into the table with the set identity_insert <table name> on statement? Eg if I...
8
by: shenanwei | last post by:
I have 2 same windows machine, same instance configure and Database , all run DB2 UDB V8.1.5 Test 1 : create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL GENERATED ALWAYS AS...
0
by: shubhangi | last post by:
Hi, I have created a update trigger on a table which has auto incremental field. trigger contains "set identity_insert on" statement. I am facing problem that only logins who have db_owner...
1
by: ddice | last post by:
I need to update and then read a sequence number, but I think its failing when there are many users updating at the same time. Here's what I'm doing update datatable set nextnr = nextnr+1 where...
2
by: Marie-Christine Bechara | last post by:
I have a table with id an identity column. Can I forbid the identity column of taking the same values of deleted ones? How can I resolve this issue? *** Sent via Developersdex...
0
by: BreckCogdill | last post by:
I have a problem with MS SQL Server 2005 - When I do an Import or Export to/from databases, the Identity is lost. I check the Enable Identity Insert box on each table, but it does not work. Is there...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.