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
- Dim objCmd As SqlCommand
- Dim objDataAdapter As New SqlDataAdapter
- Dim objReader As SqlDataReader
- Dim strSQL As String
- Dim con As SqlClient.SqlConnection
- Dim adapter As SqlClient.SqlDataAdapter
- Dim OtherConnection As String
- OtherConnection = "Data Source=. ;Initial Catalog=MainSaleData;user id=mike;password=mike227;Connect Timeout=360"
- strSQL = "SET IDENTITY_INSERT [Products]ON INSERT INTO Products (ProductID, [Product Code], ProductName VALUES ( '" & ProductID & "', '" & pcode & "', '" & ProductName & " ) " & " SET IDENTITY_INSERT [Products]OFF "
- con = New SqlClient.SqlConnection(str)
- con.ConnectionString = OtherConnection
- con.Open()
- objCmd = New SqlCommand(strSQL, con)
- objDataAdapter.InsertCommand = objCmd
- objReader = objCmd.ExecuteReader
Mike