Insert Into Statment Kills My Database Connection?
Sep 13, 2006
Hi
Simple statment is giving me a logout on the server
INSERT INTO Angebot (Nummer,Variante,Bearbeiter,Datum,Geld,Kurs,Language,Flag,AngebotStatus,TStatus)
VALUES (90360,3,'Admin',DEFAULT,'CPI_BE-D',1,'CPI_AG-1',4,NULL,59)
The statment works fine on production, however i have played a backup from production to my laptop to test something and as soon as I add a record to the database my connection to the database is droped (the data is not added to the table).
the error message I get is double, however I execute the statement only once.
Msg 0, Level 11, State 0, Line 0
FΓΌr den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. LΓΆschen Sie eventuelle Ergebnisse.
Msg 0, Level 20, State 0, Line 0
FΓΌr den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. LΓΆschen Sie eventuelle Ergebnisse.
Sugestions anyone?
PS: the message in English is:
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?
Following update runs 20 hours till TEMPDB grows up to 400GB and runs out of space with error message:
Msg 1105, Level 17, State 2, Line 8 Could not allocate space for object 'dbo.Large Object Storage System object: 440701391536128' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
T_PERF_LOC has 30,000,000 rows and T_MASTER_LOC has 2,000,000 rows There is and index on V_KEY in both tables. CHANNEL_KEY is of Integer datatype and not indexed. F_MAP function performs simple lookup on very small table (10 rows)
UPDATE A SET CHANNEL_KEY = OTHERDB.DBO.F_MAP(b.ID) FROM T_PERF A JOIN dbo.T_MASTER b on a.V_KEY = b.V_KEY
i have 3 tables in sql the relation between them is one to one person ==> employee ==> sales_department_stuff& another relation ( one to one )between person(the same as above ) ==> customer i want to put person id in employee not repeated in customer becase all (employee and customer is a person ) sooooooo i put this sql statment to try to insert person id in employee then in sales_department_stuff table to complet his ordersinsert into Person (Person_Name_Ar,Person_Name_En) values ('aaaaa',' ssssssss')select @@identity from Personinsert into Employee values (@@identity,'1') select @@identity from Personinsert into sales_department_Staff values (@@identity,'1')select @@identity from Personinsert into Driver values (@@identity,'2','2222','1/1/2005','5') /* SET NOCOUNT ON */ RETURN
the first 3 statment is run and success but underline stetment return error that he can insert null value in id field ,,,,,,,,,,,,,,,, he can compile @@identity in these statment ?????????????? plz help me
I have a table "Person" that has two columns "FirstName" and "LastName". How can I insert multiple rows into this table using the INSERT stsmt. I want to use just one insert statement.
Hello, How can I do something like: Insert Into Displayed(snpshot_id, user_id, user_domain, ddisplay, iWidth, iHeight, disp_size, disp_format, watermark, frc_update, creditcost) Values ((Select snpsht_id from SiteIndex Where user_domain like '%domain'), 1000, (Select domain_id from UserDomains Where user_domain like 'domain'), GetDate(), 480, 360, 2, 1, 1, 0, 3) Im getting an error: Subqueries are not allowed in this context. Only scalar expressions are allowed.
I have two tables that have a one-to-one relationship. The reason they are two table is that one table has user data, whereas the other table has security information. The security information will be in a different security zone.
How do I do an insert into the two tables so that I do not get an integrity error?
Hello, I have a table (SQL Server 2000) which contains data and I want to create insert statements based on this data. The reason for this is to create a .sql file to run this against another database to insert the same data. I know I can do an import but was trying to do this via a .sql script. Is there any way to generate these insert statement automatically using some SQL tool? Thanks
Hi, I'm having problems passing a querystring from the datanavigateurlformatstring to be used in a insert sp on a webform. Please can someone take a look at my code and point me in the right direction. At the moment I'm gettingthis error messageCompiler Error Message: BC30451: Name 'userid' is not declared. but in the url i can see the querystring I've passed from the previous page. Thanks in advance Dave 1 Protected Sub Execute_Clicked(ByVal sender As Object, ByVal e As EventArgs) 2 3 Dim objConn As New System.Data.SqlClient.SqlConnection() 4 objConn.ConnectionString = "My connection string" 5 objConn.Open() 6 Dim objCmd As New System.Data.SqlClient.SqlCommand("test_insert", objConn) 7 objCmd.CommandType = System.Data.CommandType.StoredProcedure 8 9 10 Dim puserid As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@userid", System.Data.SqlDbType.Int) 11 Dim pdate As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@date", System.Data.SqlDbType.DateTime) 12 Dim papplicants As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@applicants", System.Data.SqlDbType.Int) 13 Dim pclients As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@clients", System.Data.SqlDbType.Int) 14 Dim pcontacts As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@contacts", System.Data.SqlDbType.Int) 15 16 17 18 19 20 puserid.Direction = System.Data.ParameterDirection.Input 21 puserid.Value = Convert.ToInt32(userid.QueryString) 22 pdate.Direction = System.Data.ParameterDirection.Input 23 pdate.Value = Convert.ToDateTime([date].Text) 24 papplicants.Direction = System.Data.ParameterDirection.Input 25 papplicants.Value = Convert.ToInt16(applicants.Text) 26 pclients.Direction = System.Data.ParameterDirection.Input 27 pclients.Value = Convert.ToInt16(clients.Text) 28 pcontacts.Direction = System.Data.ParameterDirection.Input 29 pcontacts.Value = Convert.ToInt16(contacts.Text) 30 31 32 33 command.ExecuteNonQuery() 34 35 36 objConn.Close() 37 38 End Sub
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior. For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this? Thank you
I am createing A DTS package to move data from Access 2003 to SQL Server 2000. I have a datetime field in Access that has the values '1/1/1700'. These error when DTSing over.
I have a SQL 2005 server on a separate machine from my IIS machine, and anytime the SQL server restarts (like for last night's automatic updates) the connection pooling seems to break. Among the apps on this IIS box is Community Server 2.1, along with some other custom-built apps. The only way to resolve it is to stop and restart the IIS services. Previously all SQL-dependent apps on that IIS box broke when SQL was reset, but I seemed to have addressed it, at least on my custom-built apps, by forcing a TCP connection in the connection strings (instead of the default named pipes method). I did change the Community Server connection strings as well, but for some reason that didn't seem to work. Has anyone else experienced similar problems with interruptions in SQL connectivity bringing down apps that connect to that SQL server? I hate to turn off automatic updates just to make sure my ASP.NET apps are available. I apologize if this isn't clearly an ASP.NET question, but hopefully someone can help me out. Thanks,Josh
I'm reading data from sets of CSV source files with " text qualifiers. Most of the data is good, but I do run into some bad data that doesn't seem to get handled by redirecting rows. The type of data that is killing me is this:
I am testing a merge publication with a push subscription. The publication contains a single table. That table has an INSTEAD OF UPDATE trigger marked NOT FOR REPLICATION and an AFTER trigger that needs to go off during replication.
During Synchronization neither trigger goes off. If I drop the instead of trigger the after trigger will go off. What is going on?
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
I installed the following: * SP2 for SQL Server 2005 on Server 2 * Web-front end install of Sharepoint 2007 on Server 2 - to install the object model * SSRS2005 reporting services add-in for Sharepoint technologies on Server 2
Since doing that, I can no longer get to central administration on Server 1 - I now get the following error when navigating to central admin:
Server Error in '/' Application.
Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Which web.config do I have to edit to get the full error to show, and has anyone else experienced this?
This happened to me when my servers were fully configured and had data - so I rebuilt them from scratch and the same problem occured again!
This may be more of a Sharepoint or server question but I thought I would post it here to start off with. I have an installation of SQL 2000 SP4 and sharepoint services 2003 all on one server. For example the SQL 2000 (SP4), sharepoint and its indexes all on the one server (HP G4 DL380). I have installed the index service and the full text services on the SQL server then configured searching in sharepoint. The search worked fine for about a year until one day the server started randomly hanging for no apparent reason. After a hard reboot the server would begin to hand again after around 2-3 minutes of being online, sometimes less. After lots of problem shooting I nailed it down to being the Microsoft search service that caused the issue. Basically if I set this service to manual and boot I have no problem. As soon as I start the service the server will start to hang about 2-3 minutes later.
I tried this again last night after trying some fixed and got the same result. The only error I get are in the application log which are about 4 of these with different file types.
One or more documents stored in image columns with extension X? did not get full-text indexed because loading the filter failed with error '0x1'
The server continues to log messages while it is hanging and you can ping the server but you can not access sharepoint, remote in to the server, you can login locally but as soon as you try anything like bring up the start menu all resources will freeze up. During this time there is no disk activity and the CPU usage is 1-3% and mem usage is low.
Can anyone suggest anything to fix this problem so I can start the Microsoft search service and use search in sharepoint again.
I am having problems with syntax errors being generated from the search clause of a full-text search. When the syntax error is raised, the batch and transaction are stopped. Placing the search within a SQL Try/Catch allows the batch to continue, but the transaction is uncommitable.
I need to trap and handle the full-text search syntax error then control the commit or rollback of the transaction based on other transactions.
Below is a simplified version of the type of logic I am working with. The XACT_STATE goes to -1 because of the syntax error generated by the full-text search. This forces a rollback of the whole transaction.
Declare @sSearch NVarChar(50) /* @sSearch is passed by a web page and could come back empty or with an invalid Full-Text search clause*/
Begin Transaction Begin Try Print XACT_STATE() Select * /*The table 'KnowledgeBase' is a table with a populated Full-Text Search*/ From Containstable(KnowledgeBase,*,@sSearch) End Try Begin Catch Print XACT_STATE() Print 'Number- ' + Convert(VarChar,ERROR_NUMBER()) + ', ' + 'Severity- ' + Convert(VarChar,ERROR_SEVERITY()) + ', ' + 'State- ' + Convert(VarChar,ERROR_STATE()) + ', ' + 'Message- "' + ERROR_MESSAGE() + '"' End Catch
Happy Friday! A while since I have posted a question, and this one is probably real easy. I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
Hello,Would it be possible to open a socket connection and send few data based on an insert TRIGGER?My client would basically like to send the ID of a product via socket connection when a new product is inserted into the SQL database.Thanks for your helpArnold
Hi, I'm developing a service application (.NET 2.0) and this service listens to a network port. Client application connects to the service and passes messages to service and then the service has to write it in to a SQL Server 2005 database. Since there can be many messages per second I like to insert records as quick as i can and then response to the next request.
I was going to implement an internal queue to store messages in the memory and write to the database from the queue. I believe that SQL Server can handle a lot of requests per second hence would writing an internal queue be effective or are there any other techniques like connection pooling so I can pass the load to the sql server.
Or else should I use both. Appreciate your ideas on this.
I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo
I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si!
