Hi All,
I have a table called Test1 on a MS SQL 7 Database. Wes ran an insert on the table however it is now locking the table, so we are unable to do anything with the table.
We have tried to kill the process through both Enterprise Manager as well as Query Ananlyser however it still appears. If we try to drop the table it just sits there trying to complete this with no luck.
Is there anyway possible to be able to kill this??
Thanks
AnthonyROLLBACK can sometimes take hours. Try stop and restart MSSQLSERVER service.
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Thursday, February 16, 2012
Can''t install sql server 2005
Can't install sql server 2005
When I insert the disk autoplay flashes on the screen, then I get the message -
windows cannot open this file
splash.hta
I tried directly clicking several setup icons, but cancelled them because they didn't seem to be the initial setup of the server.
I've done this with both the dvd and the cds
dennist685
It worked with virtual-CD
This typically is an issue of a missing file association for the hta extension. To manually make the association, open Windows Explorer. From the Tools menu select Folder Options. From the Folder Options dialog select the File Types tab. Scroll down to see if there is an entry for HTA. Under File Types for HTA it should say "HTML Application". The associated information should be:Action: Open
Application used to perform action: C:\WINDOWS\System32\mshta.exe "%1" %*
Use DDE: On
Application: mshta
Topic: System
Thanks,
Samuel Lester (MSFT)|||Thanks Samuel,
Can't say whether it'll work or not. I'm out for the morning, and won't be home until the late afternoon. Meantime I made the association on my laptop.
Hope it works. Thank you very much for the time you spent helping me. Somehow I still get the feeling I'll have to uninstall vs studio 2005, then reinstall beta2, then sqlserver beta2, then uninstall them. Which should I uninstall first. I want to make sure beta2 net framework beta 2.0 is there when I uninstall beta2.
dennist685|||
Did anybody get a solution on this. I am facing the same problem. I tried to open using windows explorer and it first throws javascript error and then it shows the Splash Screen on selecting install server components it throws a message saying. "Please go to Control panel to install and configure system components."
Any help is appreciated.
|||The message Rahul.Net reported is actually a different problem than what started this thread."Please go to Control panel to install and configure system components" is usually caused by having remnants of a previous SQL Server installation. This could be the same or an earlier version of SQL Server. It could even be a client only installation - and that is what can make it particularly difficult to troubleshoot. Any number of third party applications could install SQL client software to connect with a remote SQL Server. The presence of these items can confuse the SQL Server 2005 installer and prevent it from working. You may very well find yourself needing to uninstall third party software, install SQL Server 2005, then reinstall the third party software.
Can't install sql server 2005
Can't install sql server 2005
When I insert the disk autoplay flashes on the screen, then I get the message -
windows cannot open this file
splash.hta
I tried directly clicking several setup icons, but cancelled them because they didn't seem to be the initial setup of the server.
I've done this with both the dvd and the cds
dennist685
It worked with virtual-CD
This typically is an issue of a missing file association for the hta extension. To manually make the association, open Windows Explorer. From the Tools menu select Folder Options. From the Folder Options dialog select the File Types tab. Scroll down to see if there is an entry for HTA. Under File Types for HTA it should say "HTML Application". The associated information should be:Action: Open
Application used to perform action: C:\WINDOWS\System32\mshta.exe "%1" %*
Use DDE: On
Application: mshta
Topic: System
Thanks,
Samuel Lester (MSFT)|||Thanks Samuel,
Can't say whether it'll work or not. I'm out for the morning, and won't be home until the late afternoon. Meantime I made the association on my laptop.
Hope it works. Thank you very much for the time you spent helping me. Somehow I still get the feeling I'll have to uninstall vs studio 2005, then reinstall beta2, then sqlserver beta2, then uninstall them. Which should I uninstall first. I want to make sure beta2 net framework beta 2.0 is there when I uninstall beta2.
dennist685|||
Did anybody get a solution on this. I am facing the same problem. I tried to open using windows explorer and it first throws javascript error and then it shows the Splash Screen on selecting install server components it throws a message saying. "Please go to Control panel to install and configure system components."
Any help is appreciated.
|||The message Rahul.Net reported is actually a different problem than what started this thread."Please go to Control panel to install and configure system components" is usually caused by having remnants of a previous SQL Server installation. This could be the same or an earlier version of SQL Server. It could even be a client only installation - and that is what can make it particularly difficult to troubleshoot. Any number of third party applications could install SQL client software to connect with a remote SQL Server. The presence of these items can confuse the SQL Server 2005 installer and prevent it from working. You may very well find yourself needing to uninstall third party software, install SQL Server 2005, then reinstall the third party software.
Can't install sql server 2005
Can't install sql server 2005
When I insert the disk autoplay flashes on the screen, then I get the message -
windows cannot open this file
splash.hta
I tried directly clicking several setup icons, but cancelled them because they didn't seem to be the initial setup of the server.
I've done this with both the dvd and the cds
dennist685
It worked with virtual-CD
This typically is an issue of a missing file association for the hta extension. To manually make the association, open Windows Explorer. From the Tools menu select Folder Options. From the Folder Options dialog select the File Types tab. Scroll down to see if there is an entry for HTA. Under File Types for HTA it should say "HTML Application". The associated information should be:Action: Open
Application used to perform action: C:\WINDOWS\System32\mshta.exe "%1" %*
Use DDE: On
Application: mshta
Topic: System
Thanks,
Samuel Lester (MSFT)|||Thanks Samuel,
Can't say whether it'll work or not. I'm out for the morning, and won't be home until the late afternoon. Meantime I made the association on my laptop.
Hope it works. Thank you very much for the time you spent helping me. Somehow I still get the feeling I'll have to uninstall vs studio 2005, then reinstall beta2, then sqlserver beta2, then uninstall them. Which should I uninstall first. I want to make sure beta2 net framework beta 2.0 is there when I uninstall beta2.
dennist685|||
Did anybody get a solution on this. I am facing the same problem. I tried to open using windows explorer and it first throws javascript error and then it shows the Splash Screen on selecting install server components it throws a message saying. "Please go to Control panel to install and configure system components."
Any help is appreciated.
|||The message Rahul.Net reported is actually a different problem than what started this thread."Please go to Control panel to install and configure system components" is usually caused by having remnants of a previous SQL Server installation. This could be the same or an earlier version of SQL Server. It could even be a client only installation - and that is what can make it particularly difficult to troubleshoot. Any number of third party applications could install SQL client software to connect with a remote SQL Server. The presence of these items can confuse the SQL Server 2005 installer and prevent it from working. You may very well find yourself needing to uninstall third party software, install SQL Server 2005, then reinstall the third party software.
Can't install sql server 2005
Can't install sql server 2005
When I insert the disk autoplay flashes on the screen, then I get the message -
windows cannot open this file
splash.hta
I tried directly clicking several setup icons, but cancelled them because they didn't seem to be the initial setup of the server.
I've done this with both the dvd and the cds
dennist685
It worked with virtual-CD
This typically is an issue of a missing file association for the hta extension. To manually make the association, open Windows Explorer. From the Tools menu select Folder Options. From the Folder Options dialog select the File Types tab. Scroll down to see if there is an entry for HTA. Under File Types for HTA it should say "HTML Application". The associated information should be:Action: Open
Application used to perform action: C:\WINDOWS\System32\mshta.exe "%1" %*
Use DDE: On
Application: mshta
Topic: System
Thanks,
Samuel Lester (MSFT)|||Thanks Samuel,
Can't say whether it'll work or not. I'm out for the morning, and won't be home until the late afternoon. Meantime I made the association on my laptop.
Hope it works. Thank you very much for the time you spent helping me. Somehow I still get the feeling I'll have to uninstall vs studio 2005, then reinstall beta2, then sqlserver beta2, then uninstall them. Which should I uninstall first. I want to make sure beta2 net framework beta 2.0 is there when I uninstall beta2.
dennist685|||
Did anybody get a solution on this. I am facing the same problem. I tried to open using windows explorer and it first throws javascript error and then it shows the Splash Screen on selecting install server components it throws a message saying. "Please go to Control panel to install and configure system components."
Any help is appreciated.
|||The message Rahul.Net reported is actually a different problem than what started this thread."Please go to Control panel to install and configure system components" is usually caused by having remnants of a previous SQL Server installation. This could be the same or an earlier version of SQL Server. It could even be a client only installation - and that is what can make it particularly difficult to troubleshoot. Any number of third party applications could install SQL client software to connect with a remote SQL Server. The presence of these items can confuse the SQL Server 2005 installer and prevent it from working. You may very well find yourself needing to uninstall third party software, install SQL Server 2005, then reinstall the third party software.
Sunday, February 12, 2012
Cant insert the value NULL into column
I'm trying to modify the data type in a field on a table. I rec'd this message:
Unable to modify tblship_sched
Can't insert the value NULL into column mfg_qty table qc.dbo.Tmp_tblship_sched; Column doesn't allow nulls.
Insert fails.
What is or where is the Tmp table?
Thank you.I am not sure what you mean when you ask where the table is.
SELECT * FROM SYSOBJECTS WHERE NAME = 'Tmp_tblship_sched'
If the table exists then I suggest you check the column constraint for the table in question. Looks like it has a NOT NULL constraint.
You can disable it for your insert.
Good Luck!
Unable to modify tblship_sched
Can't insert the value NULL into column mfg_qty table qc.dbo.Tmp_tblship_sched; Column doesn't allow nulls.
Insert fails.
What is or where is the Tmp table?
Thank you.I am not sure what you mean when you ask where the table is.
SELECT * FROM SYSOBJECTS WHERE NAME = 'Tmp_tblship_sched'
If the table exists then I suggest you check the column constraint for the table in question. Looks like it has a NOT NULL constraint.
You can disable it for your insert.
Good Luck!
Cant insert NULL to DateTime field
Hi I'm using DetailView and I have a text box which show the date. I have formated the date as shortDate {0:d} format. I want to insert/update null if that text box is empty.
I have tried putting null value in my Update statement in sqlDataSource. And I'm getting error saying can't convert varchar to smalldatetime.
If I don't set null value as above, some large date (01/01/2033) has been inserted in my database.
Can anyone help me?
Moe
insert DBNull.Value not "null"Hope this help|||Thanks a lot.. :)|||Dear,Can you explain where I put this code? I am newbie and have the same with formview, and I wonder where to enter this DBNULL?Can you help please?|||
Here is sample code:
sqlStmt ="insert into Emp (FirstName,LastName,Date) Values (?,?,?) ";conString ="Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost";cn =new OleDbConnection(conString);cmd =new OleDbCommand(sqlStmt, cn) ;cmd.Parameters.Add(new OleDbParameter("@.FirstName", OleDbType.VarChar, 40));cmd.Parameters.Add(new OleDbParameter("@.LastName", OleDbType.VarChar, 40));cmd.Parameters.Add(new OleDbParameter("@.Date", OleDbType.Date)); cmd.Parameters["@.FirstName"].Value = txtFirstName.Text;cmd.Parameters["@.LastName"].Value = txtLastName.Text;if ((txtDate.Text =="") ){cmd.Parameters["@.Date"].Value = DBNull.Value;}else{cmd.Parameters["@.Date"].Value = DateTime.Parse(txtDate.Text);}cn.Open();cmd.ExecuteNonQuery();Label1.Text ="Record Inserted Succesfully";
Cant insert new record to database.
SOS
I am working with SQL 2000 located on a Networksolutions server and I have a Test record I inserted manually, but I am unable to insert a new record and get this error "can't insert new record to database".
No doubt this is a no brainer, but I can't solve it.
Many Thanks
Who ever you are.SOS
I am working with SQL 2000 located on a Networksolutions server and I have a Test record I inserted manually, but I am unable to insert a new record and get this error "can't insert new record to database".
No doubt this is a no brainer, but I can't solve it.
Many Thanks
Who ever you are.
Its too little info,plz provide more.This can occur due to lots of reason.|||The Table was manually created in Enterprise Manager.
This is the Script :
= = =
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProfile]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProfile]
GO
CREATE TABLE [dbo].[tblProfile] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Password] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[LastName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[ZipCode] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Email] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[Digit9] [varchar] (9) COLLATE Latin1_General_CI_AS NOT NULL ,
[Digit81] [varchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[Digit16] [varchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[StateAffiliation] [varchar] (9) COLLATE Latin1_General_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
The Connection String has been tested and is OK.
===
Thank you for your response|||Do you have insert statements for the record you inserted and the one you are attempting to insert?|||Hello and Thank you
I'm not sure what you mean by "insert statement"|||Let me make sure we are talking about the same thing. You have created the table that is scripted above. Now you are attempting to add data to this table by typing it into the table in enterprise manager. Is this correct?|||No.
The Table is for a Registration form.
Can't insert new record to database is the Error Message I get when I try to register a new user.
Sorry I was not clear.|||OK, as rudra said, could be lots of things. Here's my first suggestion, maybe the form is trying to put a value in the [ID] field.
Another thing, does the form retrieve data from the table before trying to write to it so you know it is not a security problem?
Is there any way you can display the output of the form before it sends it to the database. This sort of problem can be really hard to diagnose from a distance!!
I am working with SQL 2000 located on a Networksolutions server and I have a Test record I inserted manually, but I am unable to insert a new record and get this error "can't insert new record to database".
No doubt this is a no brainer, but I can't solve it.
Many Thanks
Who ever you are.SOS
I am working with SQL 2000 located on a Networksolutions server and I have a Test record I inserted manually, but I am unable to insert a new record and get this error "can't insert new record to database".
No doubt this is a no brainer, but I can't solve it.
Many Thanks
Who ever you are.
Its too little info,plz provide more.This can occur due to lots of reason.|||The Table was manually created in Enterprise Manager.
This is the Script :
= = =
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProfile]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProfile]
GO
CREATE TABLE [dbo].[tblProfile] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Password] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[LastName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[ZipCode] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Email] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[Digit9] [varchar] (9) COLLATE Latin1_General_CI_AS NOT NULL ,
[Digit81] [varchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[Digit16] [varchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[StateAffiliation] [varchar] (9) COLLATE Latin1_General_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
The Connection String has been tested and is OK.
===
Thank you for your response|||Do you have insert statements for the record you inserted and the one you are attempting to insert?|||Hello and Thank you
I'm not sure what you mean by "insert statement"|||Let me make sure we are talking about the same thing. You have created the table that is scripted above. Now you are attempting to add data to this table by typing it into the table in enterprise manager. Is this correct?|||No.
The Table is for a Registration form.
Can't insert new record to database is the Error Message I get when I try to register a new user.
Sorry I was not clear.|||OK, as rudra said, could be lots of things. Here's my first suggestion, maybe the form is trying to put a value in the [ID] field.
Another thing, does the form retrieve data from the table before trying to write to it so you know it is not a security problem?
Is there any way you can display the output of the form before it sends it to the database. This sort of problem can be really hard to diagnose from a distance!!
Cant INSERT into databases table !
Hi everyone !!!
The past two days I am trying to make a survey to work !
Unfortunately I haven't accomplised yet :-(
So this is my problem...
I am using ASP, MS-SQL for the survey.
When I take the survey for the first time evetything is working and the fields are inserted correctly in the table !
If I take the survey for the second time... there is no insert in the table !!!
This is code...
dim Rs_Qu
set Rs_Qu = myCon.Execute("Select * From QUESTION ")
if Rs_Qu.Eof then
myCon.Execute ("Insert into QUESTION (" & SqlStr_1 & ") values (" & SqlStr_2 & ")")
Also, I must tell you that when I use the SQL Pane and do ...
Insert into QUESTION (" & SqlStr_1 & ") values (" & SqlStr_2 & ")
replacing of course " & SqlStr_1 & " and " & SqlStr_2 & "
i can insert the entry !!!
Please help !OK, I'm no VB expert, but after you insert a record isn't the EOF no longer satisfied upon opening the recordset? Shouldn't you have a command to move to the end of the recordset?|||Thanks mate !
It worked !!!
:beer:
The past two days I am trying to make a survey to work !
Unfortunately I haven't accomplised yet :-(
So this is my problem...
I am using ASP, MS-SQL for the survey.
When I take the survey for the first time evetything is working and the fields are inserted correctly in the table !
If I take the survey for the second time... there is no insert in the table !!!
This is code...
dim Rs_Qu
set Rs_Qu = myCon.Execute("Select * From QUESTION ")
if Rs_Qu.Eof then
myCon.Execute ("Insert into QUESTION (" & SqlStr_1 & ") values (" & SqlStr_2 & ")")
Also, I must tell you that when I use the SQL Pane and do ...
Insert into QUESTION (" & SqlStr_1 & ") values (" & SqlStr_2 & ")
replacing of course " & SqlStr_1 & " and " & SqlStr_2 & "
i can insert the entry !!!
Please help !OK, I'm no VB expert, but after you insert a record isn't the EOF no longer satisfied upon opening the recordset? Shouldn't you have a command to move to the end of the recordset?|||Thanks mate !
It worked !!!
:beer:
can't insert into ...because of nulls problme!
Hello,
I am trying to insert some records into a table from an SP but get this
message:
Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line 6
Cannot insert the value NULL into column 'CurEntryDate', table
'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I am inserting into only a few fields. How can I negotiate this?
Thanks,
RichYou cannot, unless you pass a value for every non-nullable column in the
table.
The column CurEntryDate sounds like a column that captures the insertion
time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
in the INSERT statement. If being passed through a stored procedure, declare
a local variable that has the current date/time value and pass it as an
input parameter.
You may also consult your database designer and see if he can set a default
on this column to eliminate the need to pass this value in every INSERT
statement.
Anith|||Thanks. What I did was to temporarily allow nulls in these fields. and the
n
restored the table back. Is this practice OK?
"Anith Sen" wrote:
> You cannot, unless you pass a value for every non-nullable column in the
> table.
> The column CurEntryDate sounds like a column that captures the insertion
> time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
> in the INSERT statement. If being passed through a stored procedure, decla
re
> a local variable that has the current date/time value and pass it as an
> input parameter.
> You may also consult your database designer and see if he can set a defaul
t
> on this column to eliminate the need to pass this value in every INSERT
> statement.
> --
> Anith
>
>|||If constraints have no purpose don't use them at all. I'd suggest going
through the documentation, but then again you may have already done that
without any success. My guess is that if a constraint was ever defined it wa
s
defined for a reason.
If you've now gone and inserted data through a backdoor, this just might
cause problems for you later on - i.e. with queries written against
non-nullable columns which now contain null values.
ML
http://milambda.blogspot.com/|||On Thu, 19 Jan 2006 14:29:02 -0800, Rich wrote:
>Thanks. What I did was to temporarily allow nulls in these fields. and th
en
>restored the table back. Is this practice OK?
Hi Rich,
No, it definitely isn't.
There is probably a reason why this column is defined to not allow
NULLs. If you overide that restriction, you're probably entering invalid
data and endagnering the integrity of your database.
Would you also temporarly disable a CHECK constraint that allows only
'Male' and 'Female' in the gender column to enable the value 'Water' to
be entered there?
Hugo Kornelis, SQL Server MVP|||> Thanks. What I did was to temporarily allow nulls in these fields. and
> then
> restored the table back. Is this practice OK?
The practice doesn't make sense. Don't you think the NOT NULL constraints
are there for a reason? Why bother having them at all if you're just going
to remove them every time you insert data? Isn't this kind of like not
wearing your seatbelt?|||Thank you all for your input on this matter. I undid what I was going to
try. Anyway, No, there is no documentation. I am on my own! Truth be told
,
I am not at the guru level. I am going to have to pay my dues with a lot of
heart ache. The task was one of these add-hoc things, and I just started on
assuming the duties of this project which was already in motion for a while.
So I rushed! then I slowed down and created fake data ('' for varchars and
an agreed upon fake date for date fields) to accommodate the constraints.
That did the trick. The first reply said I can't insert records if nulls ar
e
not allowed unles I have some values. So I created the values. Anyway, I
really appreciate the replies. Even though I was suggesting ideas of an
amateur nature these replies will help to keep me on the proper course.
Rich
"Rich" wrote:
> Hello,
> I am trying to insert some records into a table from an SP but get this
> message:
> Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line
6
> Cannot insert the value NULL into column 'CurEntryDate', table
> 'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I am inserting into only a few fields. How can I negotiate this?
> Thanks,
> Rich|||In case of lacking documentation maybe one of the users can help. And there'
s
got to be at least some shread of documentation. A leaflet, a greasy paper
napkin, something.
ML
http://milambda.blogspot.com/|||Your idea was reasonable for someone new to database programming. You had
some reservations and asked the right folks a question. We all have to ask
questions like this when we start out.
An amateur would have made the change and not bothered asking anyone if it
made sense.
One additional note though...
If you had removed the constraint in order to insert the null values, you
would not have been able to put the constraint back without first changing
the null values to actual values. If your data does not follow the rules of
the constraint you are trying to make, then the database will not let you
create the constraint.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:43A5D9EE-1103-409F-A440-7EFCDC318DEF@.microsoft.com...
> Thank you all for your input on this matter. I undid what I was going to
> try. Anyway, No, there is no documentation. I am on my own! Truth be
told,
> I am not at the guru level. I am going to have to pay my dues with a lot
of
> heart ache. The task was one of these add-hoc things, and I just started
on
> assuming the duties of this project which was already in motion for a
while.
> So I rushed! then I slowed down and created fake data ('' for varchars
and
> an agreed upon fake date for date fields) to accommodate the constraints.
> That did the trick. The first reply said I can't insert records if nulls
are
> not allowed unles I have some values. So I created the values. Anyway, I
> really appreciate the replies. Even though I was suggesting ideas of an
> amateur nature these replies will help to keep me on the proper course.
> Rich
> "Rich" wrote:
>
Line 6|||> Your idea was reasonable for someone new to database programming. You had
> some reservations and asked the right folks a question. We all have to as
k
> questions like this when we start out.
> An amateur would have made the change and not bothered asking anyone if it
> made sense.
I strongly agree. No one is born smart.
ML
http://milambda.blogspot.com/
I am trying to insert some records into a table from an SP but get this
message:
Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line 6
Cannot insert the value NULL into column 'CurEntryDate', table
'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I am inserting into only a few fields. How can I negotiate this?
Thanks,
RichYou cannot, unless you pass a value for every non-nullable column in the
table.
The column CurEntryDate sounds like a column that captures the insertion
time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
in the INSERT statement. If being passed through a stored procedure, declare
a local variable that has the current date/time value and pass it as an
input parameter.
You may also consult your database designer and see if he can set a default
on this column to eliminate the need to pass this value in every INSERT
statement.
Anith|||Thanks. What I did was to temporarily allow nulls in these fields. and the
n
restored the table back. Is this practice OK?
"Anith Sen" wrote:
> You cannot, unless you pass a value for every non-nullable column in the
> table.
> The column CurEntryDate sounds like a column that captures the insertion
> time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
> in the INSERT statement. If being passed through a stored procedure, decla
re
> a local variable that has the current date/time value and pass it as an
> input parameter.
> You may also consult your database designer and see if he can set a defaul
t
> on this column to eliminate the need to pass this value in every INSERT
> statement.
> --
> Anith
>
>|||If constraints have no purpose don't use them at all. I'd suggest going
through the documentation, but then again you may have already done that
without any success. My guess is that if a constraint was ever defined it wa
s
defined for a reason.
If you've now gone and inserted data through a backdoor, this just might
cause problems for you later on - i.e. with queries written against
non-nullable columns which now contain null values.
ML
http://milambda.blogspot.com/|||On Thu, 19 Jan 2006 14:29:02 -0800, Rich wrote:
>Thanks. What I did was to temporarily allow nulls in these fields. and th
en
>restored the table back. Is this practice OK?
Hi Rich,
No, it definitely isn't.
There is probably a reason why this column is defined to not allow
NULLs. If you overide that restriction, you're probably entering invalid
data and endagnering the integrity of your database.
Would you also temporarly disable a CHECK constraint that allows only
'Male' and 'Female' in the gender column to enable the value 'Water' to
be entered there?
Hugo Kornelis, SQL Server MVP|||> Thanks. What I did was to temporarily allow nulls in these fields. and
> then
> restored the table back. Is this practice OK?
The practice doesn't make sense. Don't you think the NOT NULL constraints
are there for a reason? Why bother having them at all if you're just going
to remove them every time you insert data? Isn't this kind of like not
wearing your seatbelt?|||Thank you all for your input on this matter. I undid what I was going to
try. Anyway, No, there is no documentation. I am on my own! Truth be told
,
I am not at the guru level. I am going to have to pay my dues with a lot of
heart ache. The task was one of these add-hoc things, and I just started on
assuming the duties of this project which was already in motion for a while.
So I rushed! then I slowed down and created fake data ('' for varchars and
an agreed upon fake date for date fields) to accommodate the constraints.
That did the trick. The first reply said I can't insert records if nulls ar
e
not allowed unles I have some values. So I created the values. Anyway, I
really appreciate the replies. Even though I was suggesting ideas of an
amateur nature these replies will help to keep me on the proper course.
Rich
"Rich" wrote:
> Hello,
> I am trying to insert some records into a table from an SP but get this
> message:
> Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line
6
> Cannot insert the value NULL into column 'CurEntryDate', table
> 'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I am inserting into only a few fields. How can I negotiate this?
> Thanks,
> Rich|||In case of lacking documentation maybe one of the users can help. And there'
s
got to be at least some shread of documentation. A leaflet, a greasy paper
napkin, something.
ML
http://milambda.blogspot.com/|||Your idea was reasonable for someone new to database programming. You had
some reservations and asked the right folks a question. We all have to ask
questions like this when we start out.
An amateur would have made the change and not bothered asking anyone if it
made sense.
One additional note though...
If you had removed the constraint in order to insert the null values, you
would not have been able to put the constraint back without first changing
the null values to actual values. If your data does not follow the rules of
the constraint you are trying to make, then the database will not let you
create the constraint.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:43A5D9EE-1103-409F-A440-7EFCDC318DEF@.microsoft.com...
> Thank you all for your input on this matter. I undid what I was going to
> try. Anyway, No, there is no documentation. I am on my own! Truth be
told,
> I am not at the guru level. I am going to have to pay my dues with a lot
of
> heart ache. The task was one of these add-hoc things, and I just started
on
> assuming the duties of this project which was already in motion for a
while.
> So I rushed! then I slowed down and created fake data ('' for varchars
and
> an agreed upon fake date for date fields) to accommodate the constraints.
> That did the trick. The first reply said I can't insert records if nulls
are
> not allowed unles I have some values. So I created the values. Anyway, I
> really appreciate the replies. Even though I was suggesting ideas of an
> amateur nature these replies will help to keep me on the proper course.
> Rich
> "Rich" wrote:
>
Line 6|||> Your idea was reasonable for someone new to database programming. You had
> some reservations and asked the right folks a question. We all have to as
k
> questions like this when we start out.
> An amateur would have made the change and not bothered asking anyone if it
> made sense.
I strongly agree. No one is born smart.
ML
http://milambda.blogspot.com/
Cant insert enough characters...
I must be doing something wrong, but I cannot imagine what it could be. I made a brand new table and started to enter some data. I am putting the initail data in using Enterprise Manager. This is essentially test data for development. I have several varchar columns set to 8000 for their size. When I try to enter text into them it is getting truncated at 999 characters. I thought that maybe I misunderstood the varchar type and that 1000 characters is 8000 bits and that is what the 8000 means. So, I redid the table with the datatype "text" for my long columns. Same thing. It is being truncated at 999. Is there something I am missing here? I am not trying to do anything tricky or confusing, I am simply trying to put more than 1000 characters into a table column.
I should mention that I have been pasting the text that is copied from Notepad (to remove any extranous formatting). The text cuts cut off and I cannot even type in the column after that.
Any clues would really be appreciated.I believe there is this 1000 odd (may be 1024) limit for entering data through Enterprise Manager.
Don't forget that Enterprise Manager is not the ultimate tool for data entry.
If you run an INSERT or UPDATE statement through SQL Query Analyzer,you should be just fine. Or even submitting more than 1000 charactersfrom an ASP.NET webform and performing an INSERT.
Hope that helps.
Wim
|||Ok, that makes sense. I just wanted to make sure I wasn't going crazy :) Thank-you for the information.
I should mention that I have been pasting the text that is copied from Notepad (to remove any extranous formatting). The text cuts cut off and I cannot even type in the column after that.
Any clues would really be appreciated.I believe there is this 1000 odd (may be 1024) limit for entering data through Enterprise Manager.
Don't forget that Enterprise Manager is not the ultimate tool for data entry.
If you run an INSERT or UPDATE statement through SQL Query Analyzer,you should be just fine. Or even submitting more than 1000 charactersfrom an ASP.NET webform and performing an INSERT.
Hope that helps.
Wim
|||Ok, that makes sense. I just wanted to make sure I wasn't going crazy :) Thank-you for the information.
Cant insert Decimal from textbox
Hi guys.
Hopefully someone can help me, im pulling out my hair over this!
Im trying to insert a price into my MSSQL database.
The Column type is decimal.
If i use the following simple script
<%@. Page Language="VB" Debug="true" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Sub Submit_Click(Sender As Object, E As EventArgs)
Dim sqlCmd AS SQLCommand
Dim sqlConn as SQLConnection = New SqlConnection("************;")
Dim mySQL as String = "Insert Into Products (Delivery, Price) Values (" & Dvalue.Text & ", " & Pvalue.Text & ")"
sqlCmd = New SQLCommand(mySQL,sqlConn)
sqlConn.Open()
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
End Sub
</script>
<form action="" runat="server">
Delivery - <asp:TextBox ID="Dvalue" runat="server" /><br>
Price - <asp:TextBox ID="Pvalue" runat="server" /><br>
<Asp:Button id="Submit" runat="server" text="Insert" onclick="Submit_Click" />
</form
And add in the values as
Delivery - 12.9999
Price - 56.777
The resulting numbers on the database are
Delivery - 13
Price - 57
Im using 1and1 to host my sql database and using their own SQL admin tool to configure it.
The price column has the following options
Name - Price
Type - Decimal
size(if char) - 19 ( i cant seem to change this)
default value - <blank> (I cant seem to change this)
No other options are ticked (as in nullable, indexed, unique key, primary key, identity)
Is there some setting or something ive missed/ dont know about? (ive only really used access and mysql in the past)
I would really appreciate any helpFirst, I woulduse parameters.
Next, run SQL Profiler and look at what you are actually sending to the database.|||Thanks for the quick reply :)
I cant use SQL Profiler as its a shared account over at www.1and1.co.uk
(I cant seem to even run proper SQL statements that alter tables etc)
The script im trying to fix uses a dataset and the update command, but has the exact same problem as the little example of my woe script i posted here.
Im trying to figure out if its me whos to blame, or some setting on the SQL database i need to annoy them to fix.|||What is the scale set to on your table? Precision will likely be 18, but the default scale is 0, and so you will loose anything to the right of the decimal.|||Im going to call them now. Their administration thingy isnt exactly great.
Thanks for your help|||Suffice to say they dont know whats wrong and ive been put on the mighty waiting list.
They suspect it might to be with the server being German and using , instead of . for the decimal.
So i tried both using their control panel by entering in the SQL for it
Insert Into Products (Price, RRP, Delivery) values ('12.99', '12.99', '22.22')
Which rounded it up again.
There isnt any way i can change the precision from what i can see, so ive had to nag them.
Thanks again for your help.|||I would try to create a table like so (using SQL Script):
CREATE TABLE [dbo].[DecimalTestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[DecimalTest] [decimal](18, 5) NULL
) ON [PRIMARY]
Now, if you can insert properly into this table (up to 5 decimal places) then that is your issue.|||I just ran the SQL statement on it
Insert Into DecimalTestTable (DecimalTest) Values ('12.99999')
And Sucess!
12.99999
However, when i try it using their "add new row" it comes up as 129999!
No wonder im confused...
Suffice to say im going to throw their admin tool in the bin and recreate all my tables (nothing much important on them at this stage)
Thanks again, you saved my hairline!
Cant insert data through view
I am a database administrator at my company.
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
--
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
--
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
--
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
--
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>
Cant insert data through view
I am a database administrator at my company.
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>
Cant insert data into sql database
hi
It seems that my code can insert data into memory, but not into the database. What I mean is that after "insert data", I can "read data",
which I just insert. When I check the actual database table, it didn't
get updated.
I am using VS 2005 and table designer. Regarding to this problem, is
it related to any setting of setup of the database? I check the code,
and I have no idea how it occurs.
private static string connectionString = null;
private static SqlConnection connection = null;
private static string commandString = null;
private static SqlCommand command = null;
private static SqlDataReader reader = null;
static void Main(string[] args)
{
connectionString = ConfigurationManager
.ConnectionStrings["appDatabase.Properties.Settings.databaseConnection String"]
.ConnectionString;
connection = new SqlConnection(connectionString);
try
{
// Insert data
commandString = @."INSERT INTO userTable
(userID, permissionLevel, mobile, emailAddress, mailAddress, pager) VALUES(1, 2, '123', 'aa@.a.com', 'oz', 'no')";
SqlCommand command = new SqlCommand(commandString, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
// Read data
commandString = @."select * from userTable";
command = new SqlCommand(commandString, connection);
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["permissionLevel"].ToString());
}
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
regards
figo2476have u got any sqlexception when u tried to insert data?|||Hi Figo
Welcome to the forum :D
So the select code at the bottom works? Are you certain the conection string in your config file defo matches the database you have been accessing manually? There is no setting (certainly in SQL Server) that would do what you think is happening.|||Have you tried wrapping your transaction with...
BEGIN TRAN
...your sql statement...
COMMIT
?|||have you tried using stored procedures?|||Server=.\SQLExpress;AttachDbFilename=|DataDirector y|mydbfile.mdf;Database=dbname;Trusted_Connection= Yes;"
I figured out the problem by relacing the |DataDirectory| with the physical path of the mdf file. I double check this on the web & find that both of methods should work without a problem. I am not sure why this occurs
It seems that when using virtual path, the data is sored in Memory, not actually in the database.
figo2476
It seems that my code can insert data into memory, but not into the database. What I mean is that after "insert data", I can "read data",
which I just insert. When I check the actual database table, it didn't
get updated.
I am using VS 2005 and table designer. Regarding to this problem, is
it related to any setting of setup of the database? I check the code,
and I have no idea how it occurs.
private static string connectionString = null;
private static SqlConnection connection = null;
private static string commandString = null;
private static SqlCommand command = null;
private static SqlDataReader reader = null;
static void Main(string[] args)
{
connectionString = ConfigurationManager
.ConnectionStrings["appDatabase.Properties.Settings.databaseConnection String"]
.ConnectionString;
connection = new SqlConnection(connectionString);
try
{
// Insert data
commandString = @."INSERT INTO userTable
(userID, permissionLevel, mobile, emailAddress, mailAddress, pager) VALUES(1, 2, '123', 'aa@.a.com', 'oz', 'no')";
SqlCommand command = new SqlCommand(commandString, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
// Read data
commandString = @."select * from userTable";
command = new SqlCommand(commandString, connection);
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["permissionLevel"].ToString());
}
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
regards
figo2476have u got any sqlexception when u tried to insert data?|||Hi Figo
Welcome to the forum :D
So the select code at the bottom works? Are you certain the conection string in your config file defo matches the database you have been accessing manually? There is no setting (certainly in SQL Server) that would do what you think is happening.|||Have you tried wrapping your transaction with...
BEGIN TRAN
...your sql statement...
COMMIT
?|||have you tried using stored procedures?|||Server=.\SQLExpress;AttachDbFilename=|DataDirector y|mydbfile.mdf;Database=dbname;Trusted_Connection= Yes;"
I figured out the problem by relacing the |DataDirectory| with the physical path of the mdf file. I double check this on the web & find that both of methods should work without a problem. I am not sure why this occurs
It seems that when using virtual path, the data is sored in Memory, not actually in the database.
figo2476
Can't Insert Binary Data Into Sql Mobile
Hi guys, I'm having a problem inserting a file into a SQL Mobile table.
I'm updating the table via a tableadapter designed through the Dataset designer in VS2k5.
The SQL used to insert is "Insert into TestTable (itemid,itemdata) values (@.itemid,@.itemdata)"
Where ItemID is a GUID, and ItemData is an Image field. The tableadapter has typed the parameters as GUID and Binary properly already.
Only problem is when I actually call the command with a byte array containing my file, I get a "Byte truncation" error message.
I remember this working properly in SQL CE 2.0 but so far I don't seem to get it to work in SQL mobile, anyone got any pointers?
How big is the array? If you try to insert array of, say, one byte in size, does that work?
|||Here is a sample of what you need to do to insert binary data into an image column in SQL Mobile:
MemoryStream ms = new MemoryStream();
pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
byte[] imageBytes = ms.ToArray();
SqlCeConnection cn = DatabaseManager.GetInstance().DBConnection;
string sql = "INSERT INTO Photos (ID, Photo) VALUES (NewID(), ?)";
SqlCeCommand command = new SqlCeCommand(sql, cn);
command.Parameters.Add("@.image", SqlDbType.Image, imageBytes.Length);
command.Parameters["@.image"].Value = imageBytes;
command.ExecuteNonQuery();
-Darren
Cant insert and delete
Hi,
Can anyone help me? I am using MSDE 2000 with VS.NET. I can SELECT from the database but i cant insert and delete and update. Below are my code
public static int Add(string tmpSql, string connectionString)
{
oleConn = new OleDbConnection(connectionString);
oleConn.Open();
oleTran = oleConn.BeginTransaction();
try
{
oleCom = new OleDbCommand(tmpSql, oleConn);
oleCom.Transaction = oleTran;
int a =oleCom.ExecuteNonQuery();
return(0);
}
catch
{
oleTran.Rollback();
return(1);
}
finally
{
oleConn.Close();
}
}
my int a give a value of 1 but when i look at the database the data is not inserted? but my primary key of the table did increase by one. what went wrong? is it due to my configuration which dun allow my to insert and update? help. Thank you
Please some1 pls help me or send me any useful link and i will figure out my self thanks..
|||Is it possible that you need to explicitly commit your transaction inside your try block before returning? In the example code on MSDN at the link below the transaction is explicitly committed.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbconnectionclassbegintransactiontopic.asp
Hope this helps,
Vaughn|||oh ya how can i forget that.. thanks alot.. 
Subscribe to:
Posts (Atom)