IDENTITY_INSERT Problem When Using With EXECUTE Statement.
Apr 21, 2008
Hi,
I am trying to copy data from DB1.VersionNumber to DB2.VersionNumber (Where VersionNumber on both the database have same schema),where the table contains an IDENTITY column. so i want to maintain same IDENTITY to that of source db when copying to DB2.
But when i am executing the following statments
USE DB2
DECLARE @CommandString NVARCHAR(2500)
DECLARE @ConstructString1 NVARCHAR(2500)
SELECT @CommandString = 'SET IDENTITY_INSERT dbo.VersionNumber ON'
SELECT @ConstructString1 = 'INSERT INTO VersionNumber([VersionNumberEndDate],[VersionNumberID],[VersionNumberName],[FiscalYearNumber],[RowGUID]) SELECT [ChangedBy],[ChangedDateTime],[CreatedDateTime],[VersionNumberEndDate],[VersionNumberID],[VersionNumberName],[FiscalYearNumber] FROM MapssR14SR2.dbo.VersionNumber'
EXECUTE (@CommandString)
EXECUTE (@ConstructString1)
i am getting error saying
Cannot insert explicit value for identity column in table 'ExchangeRate' when IDENTITY_INSERT is set to OFF
But if i try to execute without EXECUTE :
USE DB2
SET IDENTITY_INSERT dbo.VersionNumber ON
INSERT INTO VersionNumber([VersionNumberEndDate],[VersionNumberID],[VersionNumberName],[FiscalYearNumber])
SELECT [VersionNumberEndDate],[VersionNumberID],[VersionNumberName],[FiscalYearNumber] FROM DB1.dbo.VersionNumber
SET IDENTITY_INSERT dbo.VersionNumber OFF
i am able to execute successfully.
Can anybody please explain me what is problem in my code. Why when executing thru EXECUTE statments throwing error, and no errors when executing directly ? Is there any restriction is stetments when using EXECUTE?
I tried using
EXECUTE sp_executesql @CommandString
EXECUTE sp_executesql @ConstructString1
But same error is coming up.
Plese help me.
View 1 Replies
ADVERTISEMENT
Oct 16, 2000
Hello
While working on SQL Server 7.0 Query analyser, the following systax use to work but SQL 2000, this is giving problem, please help me out
declare @db varchar(10)
set @db = 'XXXXX'
EXECUTE ("USE " + @DB + "
Select * from VTable
....couple of sql statements....
")
The same statement is not working in SQL 2000.
View 4 Replies
View Related
Jul 20, 2005
Dear Friens,I am writing a SP.But storeing a query in a variable.But at the timeof execution generating error.Exam===================Declare @query varchar{500)Set @query = 'Select * from table'if exists (exec (@query))print 'Hi'====================But "if exists" line giving error.How do I solve this.Please help meout.ReagrdsArijit Chatterjee
View 4 Replies
View Related
Jan 3, 2006
Hello,
I thought this was a neat solution I came up with, but I'm sure it's
been thought of before. Anyway, it's my first post here.
We have a process for importing data which generates a SELECT statement
based on user's stored configuration. Since the resulting SELECT statement
can be massive, it's created and stored in a text field in a temp table.
So how do I run this huge query after creating it? In my tests, I was
getting a datalength > 20000, requiring 3 varchar(8000) variables in
order to use the execute command. Thing is, I don't know how big it could
possibly get, I wanted to be able to execute it regardless.
Here's what I came up with, it's very simple:
Table is named #IMPORTQUERY, one field SQLTEXT of type TEXT.
>>
declare @x int, @s varchar(8000)
select @x = datalength(sqltext) / 8000 + 1, @s = 'execute('''')' from #importquery
while @x > 0
select @s = 'declare @s' + cast(@x as varchar) + ' varchar(8000) ' +
'select @s' + cast(@x as varchar) +
'=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery ' +
replace(@s,'execute(','execute(@s' + cast(@x as varchar) + '+')
, @x = @x - 1
set @s = 'declare @x int set @x=1 ' + @s
execute(@s)
<<
At the end, I execute the "@s" variable which is SQL that builds and
executes the massive query. Here's what @s looks like at the end:
>>
declare @x int set @x=1
declare @s1 varchar(8000)
select @s1=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery
declare @s2 varchar(8000)
select @s2=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery
declare @s3 varchar(8000)
select @s3=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery
execute(@s1+@s2+@s3+'')
<<
View 4 Replies
View Related
Dec 4, 2006
I would like to put a select statement inside this execute statement
use [AnalysisReport]
GO
GRANT EXECUTE ON (select statement) TO [test]
GO
anyone?
View 5 Replies
View Related
Jul 23, 2005
Hi everyone,My brain refuses to remember the (undocumented?) stored procedure I'mthinking of. It takes at least two parameters: a sql statement toexecute, and a table name (or something of that nature).Then, for each value in the table, it executes the sql statement andpasses the value as a parameter.Can anybody refresh my memory? The functionality may be slighlydifferent than described, but the principle is the same. Thanks verymuch...-Joe
View 3 Replies
View Related
Oct 15, 2007
Hi all,
I have set up a package that copies data from one server to another server, then delete the data from the source tables. Now I want to add a task where it asks if the copying data was successful, then delete the data, ELSE stop the package and give an error msg, or some kind of a roll back so I don't delete the data without copying it to the destination server.
So what I want to ask is is that possible using Execute SQL task to write the script? if not how do I approach to it?
And I need some help with the roll back script as well..( IF previous task fails ..... ELSE Go on to the next task )
any help is appreciated!
View 15 Replies
View Related
Oct 15, 2007
i have two machines. i was working in a "Execute SQL Task" object's SQL window on a rather long sql task on one machine and reached some kind of limit on the length of the sql statements. i can not add another line of code. i cut and pasted this same code into the exact same "Execute SQL Task" object's SQL window on the second machine and it does not have this limit. does anyone know what causes this? (in fact....i could paste it in twice - doubling the length)
View 2 Replies
View Related
Jan 22, 2007
Hi all,
How do i execute a stored procedure in the THEN CLAUSE of my CASE STATEMENT? Av been getting errors since.
Here is my code:
Alter PROCEDURE sp_getTxn (
@m1 int = Null,
@txn int = Null,
@p2 int = Null,
@amt int = Null,
@pAccountno varchar(50) = 'Null',
@DAcct int = Null,
@Balance Decimal(19,4) = NULL OUTPUT,
@pBalance Decimal(19,4) = NULL OUTPUT,
@RowsReturned smallint = NULL OUTPUT )
AS
SET NOCOUNT ON
select CASE
WHEN @m1 = 200 THEN case
when @txn = 00 then ('exec dbo.CustOrderHist (@CrAcct int)')
when @txn = 01 then ('exec dbo.Sp_withdrawal')
when @txn = 31 then exec dbo.CheckBalance(@pAccountno varchar(50), @pBalance Decimal(19,4) OUTPUT)
when @txn = 38 then ('exec dbo.Sp_StatementOfAcct')
END
END
WHEN @m1 = 420 THEN case
when @txnType = 00 then ('exec dbo.Sp_reversal')
when @txnType = 01 then ('exec dbo.Sp_reversal2')
when @txnType = 31 then ('exec dbo.Sp_reversal3')
END
END
SET @Balance = @pBalance
Print @Balance
Or is there an alternative to the above CASE statement that is easier and faster?
Thanks
View 2 Replies
View Related
Apr 21, 2008
I have the following insert how can i execute to multiple databases on same server:
insert into Tablerecords(labelkey,moduletype,english,spanish,updatedby)
values('hypUnderConstruction','MENU','Under Construction','Under Construction','admin')
databases: db1,db2,db3,db4,db5 etc
Thanks.
View 5 Replies
View Related
Oct 13, 2006
I am new in SSIS,
and I am building some ETL just to make some exercises.
In the "Execute SQL task" page general and property: SQL Statement, I try to insert this SQL Statement,
"INSERT INTO table SELECT '" + @[User::myVariable] + "'"
or
"INSERT INTO table SELECT '" + @myVariable + "'"
but parsing, I get an error:
incorrect syntiax near '+'.
Do you know any suggestions?
Thank
View 1 Replies
View Related
Aug 14, 2007
I am running an update statement in an execute sql task, it will run one time but then fails after that. Whats going on?
Here is my query I'm running.
UPDATE encounter
SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime
FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN
encounter ON encounter.encounter_number = r.Encounter
What I'm doing is pulling info from a flat file, inserting it into the encounter table, I then run this sql statement to pull additional info from another table on another server, and update the encounter table with the corresponding info. Pretty straight forward. But what is really getting me is that the package will run 1 time but if I wait ten minutes and try running it again it bombs. Any ideas?
View 6 Replies
View Related
Oct 11, 2002
Is there anyway anyone knows of that i can select columns using variable names without building an execute statement??
ie.
DECLARE @col varchar(10)
SELECT @col = "AuditID"
SELECT @Col FROM tblAudit
??
Anyhelp a bonus
Thanks
Daniel/
View 8 Replies
View Related
Mar 20, 2014
I am a junior dba not a developer. So I'm just trying to get use to write code in T-SQL.
Anyways, I have a table which is dba.dbhakyedek.
Columns are
dbname, username, class_desc, object_name, permission_name, state_desc
I have statement
select case
when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username
WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC'
end
from dba.dbhakyedek
where username='MC'
This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it.
View 6 Replies
View Related
Mar 17, 2008
The following exception is thrown with sqljdbc.jar (not with jtds0.9.jar)
com.microsoft.sqlserver.jdbc.SQLServerException: New request is not allowed to start because it should come with valid transaction descriptor.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source)
at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:265)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:288)
While using the query :
if not exists ( select 1 from sysindexes where id = object_id('aaa') and name = 'aaa_pk')create unique nonclustered index aaa_pkon aaa(id)using Statement.executeBatch()at conection.commit()
Sample Code
conn = getConnection();
// create the statement and execute the query
Statement stmt = null;
try
{
conn.setAutoCommit(false);
stmt = conn.createStatement();
for ( String sql : sqlList )
{
stmt.addBatch( sql );
}
results = stmt.executeBatch();
stmt.clearBatch();
conn.commit(); //throws the exception
}
catch ( SQLException e )
{
try
{
conn.rollback();
}
catch ( SQLException e1 )
{
throw new DataSourceException( e1 );
}
throw new DataSourceException( "Error executing sql: %1", e, sqlList.toString() );
}
View 1 Replies
View Related
Nov 27, 2006
Hi you all,
In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource.
In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!
View 1 Replies
View Related
Aug 25, 2006
I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.
Query is sort of
Select field1,fiel2... from table 1 where id = @id
@id is each ID from lookup
Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.
Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.
View 8 Replies
View Related
May 20, 2015
I have a function like below
CREATE FUNCTION [dbo].[UDF_GetCode]
(
@TableName NVARCHAR(50)
)
RETURNS NVARCHAR(50)
[code]...
This function is called in insert statement like below. exec sp_executesql N'INSERT INTO Table ([Code], [Name]) VALUES (dbo.UDF_ GetGlobal ConfigCode (''TableName''), @Name)'I am getting following error.Only functions and some extended stored procedures can be executed from within a function.
View 3 Replies
View Related
Jun 12, 2007
I have some "Execute T-SQL Statement Tasks" in a package. I would like to run this same package on another SQL Server without having to change it on the other server. Since the server name can be given when setting up the connection, I think if I leave the server name out then the package could run on any server? Is my assumption correct?
View 10 Replies
View Related
Dec 16, 2007
Below is a simplified table & dataset to illustrate a problem I'm experiencing with a more complex one.
Code Block
create table #test(
recno smallint PRIMARY KEY,
value decimal (18,2))
insert into #test values (1, 3.57)
insert into #test values (2, 5.32)
insert into #test values (3,6.29)
insert into #test values (4, 9.25)
insert into #test values (5, 0.84)
Method 1: I tried inserting rows from #test into a temp table (#table) as follows
Code Block
declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
However, this yields an error message:
Code Block
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name '##table'.
Note - you can comment out the insert into ##table line above to view the results that I'm trying to put into ##table.
Method 2: next I tried explicitly creating ##table & rerunning the loop containing the insert
Code Block
create table ##table (
recno smallint,
value decimal (18,2),
originalrecno smallint)
declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
This worked - it inserted the data from the select statements in the loop into ##table.
Question - why won't method 1 work?
View 6 Replies
View Related
Aug 1, 2007
Is there a way to directly do this in one step(Execute SQL Insert Statement from Script Task using package OLEDB Connection)?
Right now I'm using a script task to build a sql insert statement using package variables (to fill values) populated by certain logic in the package.
Then assigning this command string to a package variable.
Then using a sql execute task to execute this variable.
A link to an article or code would be greatly appreciated.
View 1 Replies
View Related
Dec 5, 2005
I am trying to insert a new record to a table in my application created by VWD Express. I get beack the responce "Cannot insert explicit value for identity column in table 'Tradersa' when IDENTITY_INSERT is set to OFF" . I have a key record in the table which I would like to increment automatically as I add records so I have set the is identity value to true and both the identity seed and increment to 1.
I have done a fair bit or searching but do not know how to set the table value of IDENTITY_INSERT to ON. Is this as the table is set up or as the record is about to be added? I beleive I should set this when I add the record, but do not know how to in VWD.
Any help would be most welcome. Many thanks in advance
View 4 Replies
View Related
Apr 23, 2001
Hi
--SQL SERVER 7.0
I have a table P1 which has one identity column and 9 other columns.
i need to put all the data from this table into another table with same structure as P1 called P2.
even though i used Set identity_insert P2 on
I get an error saying column list must be specified
Basically i was trying to do this
SET IDENTITY_INSERT P2 ON
go
insert into P2
select * from P1
could somebody throw light onto how to specify the column list,but at the same time inserting the entire data from P1 in one shot(using select * from...)?
Any help ragarding this is highly appreciated
Thanx
SK
View 2 Replies
View Related
Mar 17, 2000
The SQL Server 7 documentation says "SET IDENTITY_INSERT permissions default to all users".
I run this command:
SET IDENTITY_INSERT database.dbo.tablename ON
I am getting an error
Server: Msg 8104, Level 16, State 1, Line 1
The current user is not the database or object owner of table
1) Is this an error in the documentation?
2) If so, is there a way I can grant rights to a non-dbo account to perform this?
Thanks
View 1 Replies
View Related
Nov 11, 2007
I need to archive from one table to another but the new table, which is a duplicate of the old one, won't allow inserts into the ID column.
I am using:
set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'
but I get this error:
Error -2147217900
An explicit value for the identity column in table 'soldVehicles' can only be specified when a column list is used and IDENTITY_INSERT is ON.
set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'
As I have turned ID_insert ON it must be the column list?...
not sure what to do next.
View 14 Replies
View Related
Dec 24, 2007
hi
i need to set the identity_insert on and off to a remote table in order to insert rows into it
can anyone help me please
View 11 Replies
View Related
Aug 28, 2006
my SP worked on friday, then today i ran it, but it's not working and throws these errors. i googled the error, but i still can't fix it. can you help me?
Server: Msg 8101, Level 16, State 1, Procedure USP_Trio_Popul_Stg_Tbls, Line 31
An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl2' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Procedure USP_Trio_Popul_Stg_Tbls, Line 57
An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl3' can only be specified when a column list is used and IDENTITY_INSERT is ON.
View 4 Replies
View Related
Mar 31, 2008
I have 2 databases, HS and BoardAnalyst and the table TCompanies exist in both. I want to delete all of the records from HS.TCompanies and repopulate it with data from BoardAnalyst.TCompanies. I'm getting the error message "Table 'HS.dbo.CompID' does not exist or cannot be opened for SET operation." CompID is the PK for the table. I'm not sure what the problem is. Thanks
DELETE FROM HS.dbo.TCompanies
GO
SET IDENTITY_INSERT HS.dbo.TCompanies ON
GO
INSERT INTO HS.dbo.TCompanies
(
HS.dbo.AnnualMtg,
HS.dbo.BdMtgs,
HS.dbo.CompanyName,
HS.dbo.CompID,
HS.dbo.DirectorsTotal,
HS.dbo.Exchange,
HS.dbo.IndexFortune,
HS.dbo.Industry,
HS.dbo.LinkComp,
HS.dbo.MailAddress,
HS.dbo.MailCity,
HS.dbo.MailCountry,
HS.dbo.MailFax,
HS.dbo.MailingAddress1a,
HS.dbo.MailingAddress2,
HS.dbo.MailPhone,
HS.dbo.MailPostCode,
HS.dbo.MailState,
HS.dbo.MarketCap,
HS.dbo.ProxyDate,
HS.dbo.Revenues,
HS.dbo.StateHQ,
HS.dbo.Ticker,
HS.dbo.Updated
)
SELECT
BoardAnalyst.dbo.TCompanies.AnnualMtg,
BoardAnalyst.dbo.TCompanies.BdMtgs,
BoardAnalyst.dbo.TCompanies.CompanyName,
BoardAnalyst.dbo.TCompanies.CompID,
BoardAnalyst.dbo.TCompanies.DirectorsTotal,
BoardAnalyst.dbo.TCompanies.Exchange,
BoardAnalyst.dbo.TCompanies.IndexFortune,
BoardAnalyst.dbo.TCompanies.Industry,
BoardAnalyst.dbo.TCompanies.LinkComp,
BoardAnalyst.dbo.TCompanies.MailAddress,
BoardAnalyst.dbo.TCompanies.MailCity,
BoardAnalyst.dbo.TCompanies.MailCountry,
BoardAnalyst.dbo.TCompanies.MailFax,
BoardAnalyst.dbo.TCompanies.MailingAddress1a,
BoardAnalyst.dbo.TCompanies.MailingAddress2,
BoardAnalyst.dbo.TCompanies.MailPhone,
BoardAnalyst.dbo.TCompanies.MailPostCode,
BoardAnalyst.dbo.TCompanies.MailState,
BoardAnalyst.dbo.TCompanies.MarketCap,
BoardAnalyst.dbo.TCompanies.ProxyDate,
BoardAnalyst.dbo.TCompanies.Revenues,
BoardAnalyst.dbo.TCompanies.StateHQ,
BoardAnalyst.dbo.TCompanies.Ticker,
BoardAnalyst.dbo.TCompanies.Updated
FROM
BoardAnalyst.dbo.TCompanies
GO
SET IDENTITY_INSERT HS.dbo.CompID OFF
GO
View 1 Replies
View Related
Apr 23, 2008
I have a table where IDENTITY_INSERT is set to OFF. I also have one stored procedure that I need to run that requires IDENTITY_INSERT to be set to ON. The only problem is the users who run the stored procedure are not owners of the table and are getting the error "The current user is not the database or object owner of table ... Cannot perform SET IDENTITY_INSERT" Is there a way to use the IDENTITY_INSERT command without being the owner of the object?
View 5 Replies
View Related
Sep 30, 2007
Hi,
I have Product table in SQL Server 2005 with Primary key ProductID. This is column with Identity set so the values for this column is auto-generated. I also have Orders table has a key that is foreign key to table Products. Now my dev environment has SQL ME whereas the deployment will be done on SQL CE.
Now I have a requirement where we want to synchronize the master tables from SQL Server with the device. For this we want to have the same identity values on the device. When I try to issue SET IDENTITY_INSERT Products ON on SQL ME it does not work. Is there any way I can set the identity ON on the SQL Mobile Edition?
Also another question I have is: Is SET IDENTITY_INSERT supported on SQL Compact Edition?
Regards,
vnj
View 3 Replies
View Related
Mar 24, 2006
We have created an "AdminUser" with the bare minimum rights for the activites it will need to do. One thing the "AdminUser" will need to do is a "SET IDENTITY_INSERT ON/OFF".
I understand that for this user to be able to do this they will need ddl_admin rights. But that gives them all kinds of permission that I don't want this user to have.
Is there a specific permission I can give the user so they can do the "IDENTITY_INSERT"?
I tried "GRANT create table to AdminUser" but that didn't work. I tried "GRANT alter any database DDL trigger to AdminUser" but that didn't work either. I'm looking for something more specific than "ddl_admin".
Thanks.
Trish
View 5 Replies
View Related
Jun 20, 2013
Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?
I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.
Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.
Server Configuration:
SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM
SQL Code:
Target Table:
USE TPS;
IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;
[code]....
View 9 Replies
View Related
Jan 21, 2008
I'm new to ASP/VS/Linq and I'm having a small problem.
I have one table setup in SQL Server Express 2005 through Visual Studio 2008. The table name is "Users" and has three columns (accountID, userName, email). AccountID is the primary key and set to auto incriment. I've added a couple of records by hand and it works.
I have a single form with a button, a label, and two text boxes. The button code is below. After entering some fake data that does not already exist in the database and clicking the button I get this.
Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.
I understand that it is trying to insert something into the accountID field but I don't understand why since I'm only providing a username and e-mail address to insert.
Your help is greatly appreciated.protected void Button1_Click(object sender, EventArgs e)
{
MyDatabaseDataContext db = new MyDatabaseDataContext();
var query = from u in db.Users
where u.email == txtEmail.Text
select u;
var count = query.Count();
if (count == 0)
{
//Create a new user object.
User newUser = new User();
newUser.username = txtUsername.Text;
newUser.email = txtEmail.Text;
//Add the user to the User table.
db.Users.InsertOnSubmit(newUser);
db.SubmitChanges();
}
else
{
Label1.Text = txtEmail.Text + " already exists in the database.";
}
View 6 Replies
View Related
