No @@IDENTITY When Inserting Text
Apr 26, 2002
I have an odd issue with a stored procedure using output parameters and a database text field.
I am using custom VB6 COM+ objects in the middle tier and SQL Server 2000 on the back end. The stored procedure has an int as it's first parameter; it is an output parameter which gets set to @@IDENTITY after the data is written to the tables. The last parameter is a text.
The COM+ object executes the proc; ADO 2.7 incorrectly identifies the text parameter as an adVarChar, so I explicitely convert it to an adLongVarChar when I detect that the incoming data is > 8000 characters.
The proc writes the data correctly to the database in all cases. However, when the data is > 8000 characters, SQL doesn't appear to correctly set the output variable. No errors are generated, I simply don't get any value written to the variable. I've searched through MS's documentation, but can't seem to find anything on this issue.
Any help or thoughts are appreciated.
View 1 Replies
ADVERTISEMENT
Sep 27, 2007
Someone asked me the other day about creating a table with one column that is an identity field and doing an insert so they can capture the SCOPE_IDENTITY() to use across seferal tables. I've never done anything like it and it seemed trivial at the time, but I'm not seeing how to make an insert work. So, for example, we have a table defines as: DECLARE @Bar TABLE (ID INT IDENTITY(1, 1) NOT NULL)
Now, how do you write a simple insert statement that will add a row to the table?
Basicaly, this is an acedemic exercise, but I can't seem to make it work unless I add another column to the table. Any ideas? Maybe I'm not caffinated enough..? :)
View 14 Replies
View Related
Jan 2, 2001
Hi Friends
Happy new year 20001.
Is it requires DBO Permission for do 'SET IDENTITY_INSERT FARRentalUnit ON'
or Write permission is enough.
I don't want give dbo perivilage to user.
Please reply soon.
Thanks for reply in advance.
Murali
View 3 Replies
View Related
Oct 12, 2006
Hi
I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.
How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?
Björn
View 1 Replies
View Related
Nov 15, 2006
I have a table with the following schema:
CREATE TABLE [itis].[wrk_taxon_authors] (
[wb_taxon_author_id] [int] NOT NULL ,
[taxon_author_id] [int] IDENTITY (1, 1) NOT NULL ,
[taxon_author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[kingdom_id] [smallint] NOT NULL
) ON [PRIMARY]
GO
I am trying to insert the following data (as you can see fields are seperated with the | ):
19||Flores-Villela and Sánchez-H., 2003|5|
20||Wiegmann, 1828|5|
16|17482|Gray, 1838|5|
17|9823|(Wiegmann, 1828)|5|
I get the following error:Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 1, column 2. Destination column (taxon_author_id) is defined NOT NULL.
Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 2, column 2. Destination column (taxon_author_id) is defined NOT NULL.
Since I have defined column 2 as an identity column, I don't understand why SQL Query analyzer is upset when I do not have a value in that field. To me, I would think it would auto-insert an integer (starting at seed 1 and incrementing by 1), but it doesn't. Could someone tell me what I'm doing wrong?
View 7 Replies
View Related
May 5, 2008
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have a database table as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4]
Using the myGameRecencyAllBalls table ---
I wish to insert a date into a new row but have not been able to determine how to with the identity specification on the GameId column.
Can anyone please assist?
Thank you.
lpbcorp
sqlCmd.CommandText = "DECLARE @date datetime SET @date = '" + Date +
"DECLARE @lastRowGameId int " +
"DECLARE lastrow_gameidcursor CURSOR SCROLL FOR " +
"(SELECT GameId FROM " + DBGameName.ToString() + "RecencyAllBalls) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_gameidcursor INTO @lastRowGameId " +
"' INSERT INTO " + DBGameName.ToString() + "RecencyAllBalls.Date VALUES (@date) WHERE GameId = @lastrow_gameidcursor + 1";
sqlCmd.ExecuteScalar();
View 6 Replies
View Related
Jul 26, 2007
I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.
How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure.
Waht is the way to achive this please?
Marc
View 2 Replies
View Related
Jul 14, 2014
We are in the conversion process of making the database ints.This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:
1) Insert all the data into the dbo table
2) Update the parent Id in the table
3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program
This is a high transaction table that will have a lot of records (millions).
--Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JSets](
[SetsID] [uniqueidentifier] NOT NULL,
[Sets_Id] bigint Identity (1,1) NOT NULL,
[code]...
View 0 Replies
View Related
Apr 2, 2008
I am having troubles trying to copy some rows from a table on my local computer to a table on a remote SQL Server 2005 that is being hosted by one of thos web hosting companies. The problem is that the table has an identity column. I first tried using the the following command:
SET IDENTITY_INSERT [remoteservername].Library2005.dbo.tblLanguages ON
but that results in the error:
Msg 8103, Level 16, State 1, Line 1
Table 'remoteservername.Library2005.dbo.tblLanguages' does not exist or cannot be opened for SET operation.
I read in another topic, that I should change this into the following:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON'
That command executes without error, but the problem is that I cannot perform the actual insert, because it is not within the execute statement. In other words, the following doesn't work:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON'
INSERT INTO [remoteservername].Library2005.dbo.tblLanguages
(colLangID, colEnglish, colGerman, colSpanish)
SELECT colLangID, colEnglish, colGerman, colSpanish FROM tblLanguages
This results in the error:
Msg 7344, Level 16, State 1, Line 2
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[remoteservername].[Library2005].[dbo].[tblLanguages]' because of column 'colLangID'. The user did not have permission to write to the column.
The remote server is linked correctly on my end via the sp_addlinkedserver and sp_addlinkedsrvlogin. Is there any way to force the remote server to turn IDENTITY_INSERT ON permanently and then let me execute as many INSERTS as I want and then turn it back OFF?
View 2 Replies
View Related
May 2, 2007
Hi All
Is it possible to insert the identity cloumn in a table via SSIS. I've an ID (Identity) column is a table and I am importing data in the table using Excel sheet. I want to insert the value of ID column as Identity(1,1).
View 10 Replies
View Related
Apr 21, 2008
Hi,
I am using SQL Server 2005 Mobile Edition & Merge Replication
in this I want to insert a record into table,
in that table I have taken UserID coloumn as auto incrementing Identity type.
In a book I read that,
If you are using an Identity column, you must find the next available number and reseed before an insert can be successful. You will also have to set up ranged identity columns on the published database to prevent errors when the new data is merged.
Now I want to ask here that, how should I ressed that value before inserting?????
any help in a form of CODe will be appriciated.....
thanks in advance...
View 8 Replies
View Related
Jan 16, 2006
Hi, All:Please help. I use sql server as back end and Access 2003 as front end(everything is DAO).A table on SQL server has an identity column as the key.We have trouble on adding records to this table using the following SQL.strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,E FROM myTableonAccessLocal"db.execute strSQLThe schema of the table "myTableOnSQLServer" and the schema of the table"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" andthe table "myTableonAccessLocal" does not have a key.When we try to run the query, it gives errors indicating the key is violatedor missing.Should I figure out the autonumber for it first and then add to the SQLserver table?Many thanks,HS
View 1 Replies
View Related
Apr 24, 2015
I am writing an Instead of Insert trigger. I would like to fire an error when inserting into an 'Identity' column. Since UPDATE([ColumnName]) always returns TRUE for insert statements, is there an easy/fast way around this? I don't want to use:Â
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NULL))
here is my pseudo-code...
CREATE VIEW [org].[Assets]
WITH SCHEMABINDING
[Code] .....
-- How does this statement need to be written to throw the error?
--UPDATE([AS_ID]) always returns TRUE
IF(UPDATE([AS_ID]))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Is there a faster/better method than this?
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NOT NULL))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Do Stuff
END;
-- Should error for inserting into [AS_ID] field (which is an identity field)
INSERT INTO [org].[Assets]([AS_ID], [Tag], [Name])
VALUES(1, 'f451', 'Paper burns'),
(2, 'k505.928', 'Paper burns in Chemistry');
-- No error should occur
INSERT INTO [org].[Assets]([Tag], [Name])
VALUES('f451', 'Paper burns'),
('k505.928', 'Paper burns in Chemistry');
View 7 Replies
View Related
Feb 20, 2007
Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an
Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)
My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....
//insert an attendance record to the attendance table
string userID;
userID = Session["User"].ToString();
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//insert an aerobics record into the aerocibs table
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";
pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//same code as above for the resistance table
However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
Scotty
View 8 Replies
View Related
Aug 7, 2007
Hello friends....
I am looking for 2 things(using c#.net or vb.net and sql svr 2000)
1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space)
2.Insert the data from text file back to database.
Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.
View 10 Replies
View Related
Oct 21, 2007
I fixed all the free text stuff but now I have another problem. I created a web app one page to enter data and anoter to display it. I have a multiple row text box and if you enter something with returns it it when you display the data it ignores the returns and puts everything on the same line BTW the column type is text
example
this
"want to hest this out.
========================
now I am going to put in some test data and see how it comes out
finished"
will come out like this
"want to hest this out.========================now I am going to put in some test data and see how it comes out finished"
how do you fix this?
View 16 Replies
View Related
Feb 19, 2004
Hi SQL Gurus,
I have an XML question to which I am kind of new.
I have an XML string that I get by using a query similar to the following....
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Zip_ROOT!1!],
NULL AS [Zip!2!Value!element],
NULL AS [Zip!2!State!element]
FROM ZipCodes
UNION
SELECT 2,
1,
NULL,
Zip,
State
FROM ZipCodes where id < 3
FOR XML EXPLICIT
This returns an XML string, which is very long. I need to take this string and INSERT/UPDATE to a Text column in a table.
Any kind of help in this regard will be grately appreciated.
Regards,
Varma
View 4 Replies
View Related
Oct 21, 2007
I fixed all the free text stuff but now I have another problem. I created a web app one page to enter data and anoter to display it. I have a multiple row text box and if you enter something with returns it it when you display the data it ignores the returns and puts everything on the same line BTW the column type is text
example
this
"want to hest this out.
========================
now I am going to put in some test data and see how it comes out
finished"
will come out like this
"want to hest this out.========================now I am going to put in some test data and see how it comes out finished"
how do you fix this?
View 7 Replies
View Related
Mar 19, 2008
Hello all,
I have a question regarding importing text file data into SQL Server. I'm hoping someone can point me in the right direction, as my searches haven't turned up anything specific enough.
I'm trying to parse a large (24MB) text file. It's a fixed-width file, with multiple columns. I need to parse this file, check if a record already exists, and then import the data into the database. But I don't need to insert every column. There's only a few columns from the file I need to insert. This parsing also needs to occur at regular intervals (daily).
I looked at BULK INSERT, but I can't find an example that uses only some of the columns. Every example uses all columns, and the file is delimited, not fixed-width.
Is there anything within SQL Server that can accomplish this? I haven't turned up anything that will solve my problem. The only other solution I can think of is an application that parses the file for me and inserts the data into the database. But can I schedule that application to run every night at midnight (for example) through SQL Server?
I'm not too familiar with SQL Server, so I appreciate any help offered.
Thanks,Jay
View 7 Replies
View Related
Jun 2, 2008
Hello Everyone,
I have looked and looked for any information on how to insert data from a db into a text field using a stored procedure. If anyone has any helpful links or suggestions, my sanity would greatly appreciate the help.
Thank you
View 1 Replies
View Related
Jul 22, 1998
If this is the wrong place for this question, would someone please tell me so. I am new to SQL Server and still feeling out resources. I have a few books on SQL Server but none cover this question.
I have a text file of dates and numbers that I want to insert into a table. There are way too many rows of data in the file to do this by hand.
Question == How can I essentially insert the text file into my table?
Thank you,
Doug
View 2 Replies
View Related
Apr 16, 2008
in sqlserver 2008, under edit/insert file as text, the default path
is my documents/management studio/projects. Can that path be changed?
I've looked under tools/options but don't see where or how.
Thanks for any response!
View 1 Replies
View Related
Aug 12, 2005
I am running into a problem inserting large amounts of text into my table. Everything works well when I test with a few simple words but when I try to do a test with larger amounts of text (ie 35,000 characters) the appropriate field is left blank. The Insert still performs (all the other fields recieve their data, but the "Description" field is blank. I have tried this with both "text" and "ntext" datatypes. I am using a stored procedure with input parameters. As I mentioned, the query goes off flawlessly with small amounts of data (eg "Hi there!") but not with the larger amount.I check and the ntext field claims to be able to accept 1073741823 bytes of data. Is there some other thing I should consider with large amounts of text?
View 6 Replies
View Related
Mar 14, 2002
Update TableName
Set Field2 = 'This text contains '' single quote's'
Where Field1 = 10
How is this usually done?
Thanks
View 1 Replies
View Related
Jul 7, 2004
I have a large text colum I am trying to insert into a DB
This colum is about 800 chars longs
I have set the colum type in the table to text
I have set the table option for text in row to on
I have set the table option for text in row to 1000
But it is still chopping the text at the 256 char mark on insert.
Anyone have any ideas ?? This is SQL 2000.
Chris
View 4 Replies
View Related
Feb 11, 2014
I have written up a grid consisting of properties and units.The way it works is we have properties, and within properties there are units. They are two seperate tables.Some properties do not have any units so in the unit reference (UN_UREF) column for those records which do not have units and are NULL I would like to insert the text 'NO UNIT'.Please see below for my SQL for the grid which works fine.
create or replace view VWC_PROPMKUNIT_TEST AS ( SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, UN_UREF, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF);
All I need to do now is insert 'NO UNIT' within the Unit Reference column where it is NULL.
View 5 Replies
View Related
Jul 20, 2005
When we insert text into field in a table, SQL SERVER apparentlyreplaces apostrophes with question marks -- is there a way to not havethis occur? We don't have this happen with the mySQL databases thatwe also support.Much help appreciated.
View 1 Replies
View Related
Nov 24, 2006
Hello,
I have an application taht requires the use of a table. The device that this application works on, has a local memory that does not allow me to insert the 800,000 records that I need. Therefore I have two approaches:
1. To insert less records into my local memory database e.g 40,000 but not row by row, bulk insert is better. How do I do the bulk insert?
2. This is the most prefferable way: To find a way to insert all 800,000 records into a table on the storage card which is 1GB. What do you suggest? Will using threads be helpfull? Any ideas?
I use C# from VS 2005, SQL ME, compact framework 2.0 and windows 4.2.
Thanks in advance,
John.
View 6 Replies
View Related
Jan 2, 2007
I have a Detailsview with Insert and Update options connected to a SQL 2005 table with templated textboxes for input. The textboxes have maxlength set to the number of characters in each respective field in the SQL 2005 table. When text is inserted it gets padded with spaces if all the field length is not used. When you try to edit the field the cursor does not move because of the padded spaces. The End key must be pressed to find the end of the string and the padded space removed before adding edited text. I am working in VB.net. If I check the field in SQL Studio Express is shows the text I typed plus blank space for the remainder of the field.
My question is how can I add text to the textboxes without the padded spaces being added when the maxlength of the field is not used?
View 3 Replies
View Related
Mar 8, 2015
See attached image...
The columns are of type text
I made this insert stmt using a stored proc...
I mean the [text] field values from another table is converted to varchar(max) and then to VARBINARY(max) and then to HEX value.
Why this error only when I try to insert to this particular column [[Conclusions]] ?
Other columns ( of type text ) did not have this issue
View 1 Replies
View Related
Apr 21, 2005
Here is the error message that I'm getting
Error string: Error opening datafile: The system cannot find the path specified.
The file it's bombing out on is the text file that Im importing into one of my tables through a dts package (which is getting called by the dtsrun statement -- that is giving off this message.) If anyone would know what may be sending this message off to me let me know.
Thanks in advance.
RB
View 2 Replies
View Related
Jan 15, 2014
I am trying to insert an NTEXT value from one table T1 to another table T2 within a database. Table structures are as below;
Create table T1 (T1ID INT NOT NUll, SourceColumn Ntext null)
Create table T2 (T2ID INT NOT NUll, TargetColumn NVARCHAR(MAX) null)
Every time when long text value is getting inserted into T2.TargetColumn , it is appending with an unwanted character '?' either at the beginning or at the ending of the text string. Same problem happens even when I am trying to update T2.TargetColumn = T1.TextColumn. Because of this, the same column never matches to the source column and gets updated every time even there is no change...
I am also converting NTEXT column to NVARCHAR(MAX) and replacing CHAR(10) to '' .
I am using SQL Server 2012. How can I avoid inserting '?' in T2.TargetColumn . Is there any setting which I need to set in the target table?
View 1 Replies
View Related
May 2, 2006
I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.
What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?
Thanks.
View 4 Replies
View Related
