Search Results

Search found 23901 results on 957 pages for 'mysql stored procedure'.

Page 46/957 | < Previous Page | 42 43 44 45 46 47 48 49 50 51 52 53  | Next Page >

• What is a proper MySql replication configuration for frequent db updates and rare selects?

  - by serg555

  We currently have 1 master db on its own server and slave db on app server. App executes very frequent but light updates (like increasing counters), and occasional (once in a few minutes) heavy selects (which is the most important part of the app). When app was connected only to master db there were no performance issues. With slave db introduction CPU load avg on app server increased to about 6-10 during that heavy select period (from 3-4 as before). When server doesn't run those frequent updates it seems like performance for selects stays within the limits. So I have a feeling that those updates is what is causing the performance drop (also these frequent updates are not critical so if slave db doesn't have them in sync with master for some time it would be ok). What would be a good db replication setup for such kind of app? What are the replication parameters we could tweak? Thanks.

  Read the article

• php mysql cpanel high cpu usage

  - by Megahostzone Santu

  server taking high cpu usage load average: 108.87, 105.92, 85.82 netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n Reselt showing too much connect from server IP cpanel Process Manager showing 19.4 | 0.5 | /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/zebra546.serverstall.com.err --pid-file=/var/lib/mysql/zebra546.serverstall.com.pid 3.0 | 0.2 | /usr/bin/php /home/nowwatch/public_html/index.php

  Read the article

• Cannot create Windows Service for MySQL (Vista)

  - by Matt W

  I uninstalled MySQL 5.1 from Vista as I lost the username/password, however I forgot to stop the MySQL service before doing so. Now whenever I try to reinstall it I get to the final configuration screen which then fails with the message "Cannot create Windows Service for MySQL Error:0". I've uninstalled MySQL and removed all related files/folders/registry entries but still no joy... Does anyone know a way to resolve this as I can't seem to find a fix online anywhere!

  Read the article

• mysql problem (cannot connect)

  - by user32328

  hey, i have written php script and it frozen mysql and when i restarted my linux (debian) there was something written that mysql database is full and now when i want to run mysql there is an error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) does someone know how to fix it?

  Read the article

• MySQL Connect Conference: My Experience

  - by Hema Sridharan

  It was a great experience to attend the MySQL Connect Conference for the first time ever. Personally I was very much enthralled to present about "How to make MySQL Backups" besides attending different sessions to absorb more knowledge about the technical prospects of MySQL. One of the agenda items in my presentation was "MySQL Enterprise Backup" functionality and features. There were total of 40 attendees in the session, who were very much interested about the MySQL Enterprise Backup product and gave positive feedback as well as areas of improvements on our product. Some of our features brought lot of excitement and smile amongst our customers including,1. Performance improvements in MEB 3.8.02. Incremental Base option from MEB 3.7.1 where there is no need to specify the directory name of the previous backup to fetch the lsn values and instead can directly fetch from backup_history table using --incremental-base=history: last_backup3. only-innodb-with-frm option introduced in MEB 3.7 version. A true online hot backup of InnoDB tables.I also attended a session with similar topic "MEB Best Practices" conducted by Sanjay Manwani, where he double clicked all the features and best strategies of backup & restore. I also got an opportunity to attend other sessions including,* Enabling the new generation of web and cloud services with MySQL 5.6 replication* Getting the most out of MySQL with MySQL Workbench* InnoDB compression for OLTP* Scaling for the Web and Cloud with MySQL replication.Above all, had some special moments in the conference including meeting some of the executives / colleagues for the first time f2f. On a whole, the first MySQL Connect conference was a great success in terms of manifesting the features of our products, direct feedback from customer and team building.  We also had some applauding yahoo moments when Tomas Ulin announced different releases including MySQL 5.6 RC, Connector Python 1.0 and ODBC 5.2 release, MySQL Cluster 7.3, additions to MySQL Enterprise edition etc.

  Read the article

• entity framework 4 POCO's stored procedure error - "The FunctionImport could not be found in the container"

  - by user331884

  Entity Framework with POCO Entities generated by T4 template. Added Function Import named it "procFindNumber" specified complex collection named it "NumberResult". Here's what got generated in Context.cs file: public ObjectResult<NumberResult> procFindNumber(string lookupvalue) { ObjectParameter lookupvalueParameter; if (lookupvalue != null) { lookupvalueParameter = new ObjectParameter("lookupvalue", lookupvalue); } else { lookupvalueParameter = new ObjectParameter("lookupvalue", typeof(string)); } return base.ExecuteFunction<NumberResult>("procFindNumber", lookupvalueParameter); } Here's the stored procedure: ALTER PROCEDURE [dbo].[procFindNumber] @lookupvalue varchar(255) AS BEGIN SET NOCOUNT ON; DECLARE @sql nvarchar(MAX); IF @lookupvalue IS NOT NULL AND @lookupvalue <> '' BEGIN SELECT @sql = 'SELECT dbo.HBM_CLIENT.CLIENT_CODE, dbo.HBM_MATTER.MATTER_NAME, dbo.HBM_MATTER.CLIENT_MAT_NAME FROM dbo.HBM_MATTER INNER JOIN dbo.HBM_CLIENT ON dbo.HBM_MATTER.CLIENT_CODE = dbo.HBM_CLIENT.CLIENT_CODE LEFT OUTER JOIN dbo.HBL_CLNT_CAT ON dbo.HBM_CLIENT.CLNT_CAT_CODE = dbo.HBL_CLNT_CAT.CLNT_CAT_CODE LEFT OUTER JOIN dbo.HBL_CLNT_TYPE ON dbo.HBM_CLIENT.CLNT_TYPE_CODE = dbo.HBL_CLNT_TYPE.CLNT_TYPE_CODE WHERE (LTRIM(RTRIM(dbo.HBM_MATTER.CLIENT_CODE)) <> '''')' SELECT @sql = @sql + ' AND (dbo.HBM_MATTER.MATTER_NAME like ''%' + @lookupvalue + '%'')' SELECT @sql = @sql + ' OR (dbo.HBM_MATTER.CLIENT_MAT_NAME like ''%' + @lookupvalue + '%'')' SELECT @sql = @sql + ' ORDER BY dbo.HBM_MATTER.MATTER_NAME' -- Execute the SQL query EXEC sp_executesql @sql END END In my WCF service I try to execute the stored procedure: [WebGet(UriTemplate = "number/{value}/?format={format}")] public IEnumerable<NumberResult> GetNumber(string value, string format) { if (string.Equals("json", format, StringComparison.OrdinalIgnoreCase)) { WebOperationContext.Current.OutgoingResponse.Format = WebMessageFormat.Json; } using (var ctx = new MyEntities()) { ctx.ContextOptions.ProxyCreationEnabled = false; var results = ctx.procFindNumber(value); return results.ToList(); } } Error message says "The FunctionImport ... could not be found in the container ..." What am I doing wrong?

  Read the article

• Establishing Upper / Lower Bound in T-SQL Procedure

  - by Code Sherpa

  Hi. I am trying to establish upper / lower bound in my stored procedure below and am having some problems at the end (I am getting no results where, without the temp table inner join i get the expected results). I need some help where I am trying to join the columns in my temp table #PageIndexForUsers to the rest of my join statement and I am mucking something up with this statement: INNER JOIN #PageIndexForUsers ON ( dbo.aspnet_Users.UserId = #PageIndexForUsers.UserId AND #PageIndexForUsers.IndexId >= @PageLowerBound AND #PageIndexForUsers.IndexId <= @PageUpperBound ) I could use feedback at this point - and, any advice on how to improve my procedure's logic (if you see anything else that needs improvement) is also appreciated. Thanks in advance... ALTER PROCEDURE dbo.wb_Membership_GetAllUsers @ApplicationName nvarchar(256), @sortOrderId smallint = 0, @PageIndex int, @PageSize int AS BEGIN DECLARE @ApplicationId uniqueidentifier SELECT @ApplicationId = NULL SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName IF (@ApplicationId IS NULL) RETURN 0 -- Set the page bounds DECLARE @PageLowerBound int DECLARE @PageUpperBound int DECLARE @TotalRecords int SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageSize - 1 + @PageLowerBound BEGIN TRY -- Create a temp table TO store the select results CREATE TABLE #PageIndexForUsers ( IndexId int IDENTITY (0, 1) NOT NULL, UserId uniqueidentifier ) -- Insert into our temp table INSERT INTO #PageIndexForUsers (UserId) SELECT u.UserId FROM dbo.aspnet_Membership m, dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId ORDER BY u.UserName SELECT @TotalRecords = @@ROWCOUNT SELECT dbo.wb_Profiles.profileid, dbo.wb_ProfileData.firstname, dbo.wb_ProfileData.lastname, dbo.wb_Email.emailaddress, dbo.wb_Email.isconfirmed, dbo.wb_Email.emaildomain, dbo.wb_Address.streetname, dbo.wb_Address.cityorprovince, dbo.wb_Address.state, dbo.wb_Address.postalorzip, dbo.wb_Address.country, dbo.wb_ProfileAddress.addresstype,dbo.wb_ProfileData.birthday, dbo.wb_ProfileData.gender, dbo.wb_Session.sessionid, dbo.wb_Session.lastactivitydate, dbo.aspnet_Membership.userid, dbo.aspnet_Membership.password, dbo.aspnet_Membership.passwordquestion, dbo.aspnet_Membership.passwordanswer, dbo.aspnet_Membership.createdate FROM dbo.wb_Profiles INNER JOIN dbo.wb_ProfileAddress ON ( dbo.wb_Profiles.profileid = dbo.wb_ProfileAddress.profileid AND dbo.wb_ProfileAddress.addresstype = 'home' ) INNER JOIN dbo.wb_Address ON dbo.wb_ProfileAddress.addressid = dbo.wb_Address.addressid INNER JOIN dbo.wb_ProfileData ON dbo.wb_Profiles.profileid = dbo.wb_ProfileData.profileid INNER JOIN dbo.wb_Email ON ( dbo.wb_Profiles.profileid = dbo.wb_Email.profileid AND dbo.wb_Email.isprimary = 1 ) INNER JOIN dbo.wb_Session ON dbo.wb_Profiles.profileid = dbo.wb_Session.profileid INNER JOIN dbo.aspnet_Membership ON dbo.wb_Profiles.userid = dbo.aspnet_Membership.userid INNER JOIN dbo.aspnet_Users ON dbo.aspnet_Membership.UserId = dbo.aspnet_Users.UserId INNER JOIN dbo.aspnet_Applications ON dbo.aspnet_Users.ApplicationId = dbo.aspnet_Applications.ApplicationId INNER JOIN #PageIndexForUsers ON ( dbo.aspnet_Users.UserId = #PageIndexForUsers.UserId AND #PageIndexForUsers.IndexId >= @PageLowerBound AND #PageIndexForUsers.IndexId <= @PageUpperBound ) ORDER BY CASE @sortOrderId WHEN 1 THEN dbo.wb_ProfileData.lastname WHEN 2 THEN dbo.wb_Profiles.username WHEN 3 THEN dbo.wb_Address.postalorzip WHEN 4 THEN dbo.wb_Address.state END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN EXEC wb_ErrorHandler RETURN 55555 END CATCH RETURN @TotalRecords END GO

  Read the article

• Calling a Stored Procedure using C# with XML Datatype

  - by Lakeshore

  I am simply trying to call a store procedure (SQL Server 2008) using C# and passing XMLDocument to a store procedure parameter that takes a SqlDbType.Xml data type. I am getting error: Failed to convert parameter value from a XmlDocument to a String. Below is code sample. How do you pass an XML Document to a store procedure that is expecting an XML datatype? Thanks. XmlDocument doc = new XmlDocument(); //Load the the document with the last book node. XmlTextReader reader = new XmlTextReader(@"C:\temp\" + uploadFileName); reader.Read(); // load reader doc.Load(reader); connection.Open(); SqlCommand cmd = new SqlCommand("UploadXMLDoc", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Year", SqlDbType.Int); cmd.Parameters["@Year"].Value = iYear; cmd.Parameters.Add("@Quarter", SqlDbType.Int); cmd.Parameters["@Quarter"].Value = iQuarter; cmd.Parameters.Add("@CompanyID", SqlDbType.Int); cmd.Parameters["@CompanyID"].Value = iOrganizationID; cmd.Parameters.Add("@FileType", SqlDbType.VarChar); cmd.Parameters["@FileType"].Value = "Replace"; cmd.Parameters.Add("@FileContent", SqlDbType.Xml); cmd.Parameters["@FileContent"].Value = doc; cmd.Parameters.Add("@FileName", SqlDbType.VarChar); cmd.Parameters["@FileName"].Value = uploadFileName; cmd.Parameters.Add("@Description", SqlDbType.VarChar); cmd.Parameters["@Description"].Value = lblDocDesc.Text; cmd.Parameters.Add("@Success", SqlDbType.Bit); cmd.Parameters["@Success"].Value = false; cmd.Parameters.Add("@AddBy", SqlDbType.VarChar); cmd.Parameters["@AddBy"].Value = Page.User.Identity.Name; cmd.ExecuteNonQuery(); connection.Close();

  Read the article

• sql server 2005 stored procedure unexpected behaviour

  - by user283405

  i have written a simple stored procedure (run as job) that checks user subscribe keyword alerts. when article posted the stored procedure sends email to those users if the subscribed keyword matched with article title. One section of my stored procedure is: OPEN @getInputBuffer FETCH NEXT FROM @getInputBuffer INTO @String WHILE @@FETCH_STATUS = 0 BEGIN --PRINT @String INSERT INTO #Temp(ArticleID,UserID) SELECT A.ID,@UserID FROM CONTAINSTABLE(Question,(Text),@String) QQ JOIN Article A WITH (NOLOCK) ON A.ID = QQ.[Key] WHERE A.ID > @ArticleID FETCH NEXT FROM @getInputBuffer INTO @String END CLOSE @getInputBuffer DEALLOCATE @getInputBuffer This job run every 5 minute and it checks last 50 articles. It was working fine for last 3 months but a week before it behaved unexpectedly. The problem is that it sends irrelevant results. The @String contains user alert keyword and it matches to the latest articles using Full text search. The normal execution time is 3 minutes but its execution time is 3 days (in problem). Now the current status is its working fine but we are unable to find any reason why it sent irrelevant results. Note: I am already removing noise words from user alert keyword. I am using SQL Server 2005 Enterprise Edition.

  Read the article

• MySQL FULLTEXT aggravation

  - by southof40

  Hi - I'm having problems with case-sensitivity in MySQL FULLTEXT searches. I've just followed the FULLTEXT example in the MySQL doco at http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html . I'll post it here for ease of reference ... CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ); INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'), ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'), ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); ... my problem is that the example shows that SELECT returning the first and fifth rows ('..DataBase..' and '..database..') but I only get one row ('database') ! The example doesn't demonstrate what collation the table in the example had but I have ended up with latin1_general_cs on the title and body columns of my example table. My version of MySQL is 5.1.39-log and the connection collation is utf8_unicode_ci . I'd be really grateful is someone could suggest why my experience differs from the example in the manual ! Be grateful for any advice.

  Read the article

• Why does stored procedure invalidate SQL Cache Dependency?

  - by Fabio Milheiro

  After many hours, I finally realize that I am working correctly with the Cache object in my ASP.NET application but my stored procedures stops it from working correctly. This stored procedure works correctly: CREATE PROCEDURE [dbo].[ListLanguages] @Page INT = 1, @ItemsPerPage INT = 10, @OrderBy NVARCHAR (100) = 'ID', @OrderDirection NVARCHAR(4) = 'DESC' AS BEGIN SELECT ID, [Name], Flag, IsDefault FROM dbo.Languages END But this (the one I wanted) doesn't: CREATE PROCEDURE [dbo].[ListLanguages] @Page INT = 1, @ItemsPerPage INT = 10, @OrderBy NVARCHAR (100) = 'ID', @OrderDirection NVARCHAR(4) = 'DESC', @TotalRecords INT OUTPUT AS BEGIN SET @TotalRecords = 10 EXEC('SELECT ID, Name, Flag, IsDefault FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' ' + @OrderDirection + ') as Row, ID, Name, Flag, IsDefault FROM dbo.Languages) results WHERE Row BETWEEN ((' + @Page + '-1)*' + @ItemsPerPage + '+1) AND (' + @Page + '*' + @ItemsPerPage + ')') END I gave the @TotalRecords parameter the value 10 so you can be sure that the problem is not from the COUNT(*) function which I know is not supported well. Also, when I run it from SQL Server Management Studio, it does exactly what it should do. In the ASP.NET application the results are retrieved correctly, only the cache is somehow unable to work! Can you please help? Maybe a hint I believe that the reason why the dependency HasChanged property is related to the fact that the column Row generated from the ROW_NUMBER is only temporary and, therefore, the SQL SERVER is not able to to say whether the results are changed or not. That's why HasChanged is always set to true. Does anyone know how to paginate results from SQL SERVER without using COUNT or ROW_NUMBER functions?

  Read the article

• Stored Procedure: Variable passed from PHP alters second half of query string

  - by Stephanie

  Hello everyone, Basically, we have an ASP website right now that I'm converting to PHP. We're still using the MSSQL server for the DB -- it's not moving. In the ASP, now, there's an include file with a giant sql query that's being executed. This include sits on a lot of pages and this is a simple version of what happens. Pages A, B and C all use this include file to return a listing. In ASP, Page A passes through variable A to the include file - page B passes through variable B -- page C passes through variable C, and so on. The include file builds the SQL query like this: sql = "SELECT * from table_one LEFT OUTER JOIN table_two ON table_one.id = table_two.id" then adds (remember, ASP), based on the variable passed through from the parent page, Select Case sType Case "A" sql = sql & "WHERE LOWER(column_a) <> 'no' AND LTRIM(ISNULL(column_b),'') <> '' ORDER BY column_a Case "B" sql = sql & "WHERE LOWER(column_c) <> 'no' ORDER BY lastname, firstname Case "C" sql = sql & "WHERE LOWER(column_f) <> 'no' OR LOWER(column_g) <> 'no' ORDER BY column_g As you notice, every string that's added on as the second part of the sql query is different than the previous; not just one variable can be substituted out, which is what has me stumped. How do I translate this case / switch into the stored procedure, based on the varchar input that I pass to the stored procedure via PHP? This stored procedure will actually handle a query listing for about 20 pages, so it's a hefty one and this is my first major complicated one. I'm getting there, though! I'm also just more used to MySQL, too. Not that they're that different. :P Thank you very much for your help in advance. Stephanie

  Read the article

• Modify SQL result set before returning from stored procedure

  - by m0sa

  I have a simple table in my SQL Server 2008 DB: Tasks_Table -id -task_complete -task_active -column_1 -.. -column_N The table stores instructions for uncompleted tasks that have to be executed by a service. I want to be able to scale my system in future. Until now only 1 service on 1 computer read from the table. I have a stored procedure, that selects all uncompleted and inactive tasks. As the service begins to process tasks it updates the task_active flag in all the returned rows. To enable scaleing of the system I want to enable deployment of the service on more machines. Because I want to prevent a task being returned to more than 1 service I have to update the stored procedure that returns uncompleted and inactive tasks. I figured that i have to lock the table (only 1 reader at a time - I know I have to use an apropriate ISOLATION LEVEL), and updates the task_active flag in each row of the result set before returning the result set. So my question is how to modify the SELECT result set iin the stored procedure before returning it?

  Read the article

• Stored Procedure Parameters Not Available After Declared

  - by SidC

  Hi All, Pasted below is a stored procedure written in SQL Server 2005. My intent is to call this sproc from my ASP.NEt web application through the use of a wizard control. I am new to SQL Server and especially to stored procedures. I'm unsure why my parameters are not available to the web application and not visible in SSMS treeview as a parameter under my sproc name. Can you help me correct the sproc below so that the parameters are correctly instantiated and available for use in my web application? Thanks, Sid Stored Procedure syntax: USE [Diel_inventory] GO /****** Object: StoredProcedure [dbo].[AddQuote] Script Date: 05/09/2010 00:31:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[AddQuote] as Declare @CustID int, @CompanyName nvarchar(50), @Address nvarchar(50), @City nvarchar(50), @State nvarchar(2), @ZipCode nvarchar(5), @Phone nvarchar(12), @FAX nvarchar(12), @Email nvarchar(50), @ContactName nvarchar(50), @QuoteID int, @QuoteDate datetime, @NeedbyDate datetime, @QuoteAmt decimal, @ID int, @QuoteDetailPartID int, @PartNumber float, @Quantity int begin Insert into dbo.Customers (CompanyName, Address, City, State, ZipCode, OfficePhone, OfficeFAX, Email, PrimaryContactName) Values (@CompanyName, @Address, @City, @State, @ZipCode, @Phone, @FAX, @Email, @ContactName) set @CustID = scope_identity() Insert into dbo.Quotes (fkCustomerID,NeedbyDate,QuoteAmt) Values(@CustID,@NeedbyDate,@QuoteAmt) set @QuoteID = scope_identity() Insert into dbo.QuoteDetail (ID) values(@ID) set @ID=scope_identity() Insert into dbo.QuoteDetailParts (QuoteDetailPartID, QuoteDetailID, PartNumber, Quantity) values (@ID, @QuoteDetailPartID, @PartNumber, @Quantity) END

  Read the article

• Advice on Minimizing Stored Procedure Parameters

  - by RPM1984

  Hi Guys, I have an ASP.NET MVC Web Application that interacts with a SQL Server 2008 database via Entity Framework 4.0. On a particular page, i call a stored procedure in order to pull back some results based on selections on the UI. Now, the UI has around 20 different input selections, ranging from a textbox, dropdown list, checkboxes, etc. Each of those inputs are "grouped" into logical sections. Example: Search box : "Foo" Checkbox A1: ticked, Checkbox A2: unticked Dropdown A: option 3 selected Checkbox B1: ticked, Checkbox B2: ticked, Checkbox B3: unticked So i need to call the SPROC like this: exec SearchPage_FindResults @SearchQuery = 'Foo', @IncludeA1 = 1, @IncludeA2 = 0, @DropDownSelection = 3, @IncludeB1 = 1, @IncludeB2 = 1, @IncludeB3 = 0 The UI is not too important to this question - just wanted to give some perspective. Essentially, i'm pulling back results for a search query, filtering these results based on a bunch of (optional) selections a user can filter on. Now, My questions/queries: What's the best way to pass these parameters to the stored procedure? Are there any tricks/new ways (e.g SQL Server 2008) to do this? Special "table" parameters/arrays - can we pass through User-Defined-Types? Keep in mind im using Entity Framework 4.0 - but could always use classic ADO.NET for this if required. What about XML? What are the serialization/de-serialization costs here? Is it worth it? How about a parameter for each logical section? Comma-seperated perhaps? Just thinking out loud. This page is particulary important from a user point of view, and needs to perform really well. The stored procedure is already heavy in logic, so i want to minimize the performance implications - so keep that in mind. With that said - what is the best approach here?

  Read the article

• C#: ExecuteNonQuery() returns -1 when execute the stored procedure

  - by user1122359

  I'm trying to execute stored procedure in Visual Studio. Its given below. CREATE PROCEDURE [dbo].[addStudent] @stuName varchar(50), @address varchar(100), @tel varchar(15), @etel varchar(15), @nic varchar (10), @dob date AS BEGIN SET NOCOUNT ON; DECLARE @currentID INT DECLARE @existPerson INT SET @existPerson = (SELECT p_ID FROM Student WHERE s_NIC = @nic); IF @existPerson = null BEGIN INSERT INTO Person (p_Name, p_RegDate, p_Address, p_Tel, p_EmergeNo, p_Valid, p_Userlevel) VALUES (@stuName, GETDATE(), @address, @tel, @etel, 0, 'Student' ); SET @currentID = (SELECT MAX( p_ID) FROM Person); INSERT INTO Student (p_ID, s_Barcode, s_DOB, s_NIC) VALUES (@currentID , NULL, @dob, @nic); return 0; END ELSE return -1; END Im doing so by using this code below. SqlConnection con = new SqlConnection(); Connect conn = new Connect(); con = conn.getConnected(); con.Open(); cmd = new SqlCommand("addStudent", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@stuName", SqlDbType.VarChar).Value = nameTxt.Text.ToString(); cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = addressTxt.Text.ToString(); cmd.Parameters.Add("@tel", SqlDbType.VarChar).Value = telTxt.Text.ToString(); cmd.Parameters.Add("@etel", SqlDbType.VarChar).Value = emerTxt.Text.ToString(); cmd.Parameters.Add("@nic", SqlDbType.VarChar).Value = nicTxt.Text.ToString(); cmd.Parameters.Add("@dob", SqlDbType.DateTime).Value = dobTime.Value.ToString("MM-dd-yyyy"); int n = cmd.ExecuteNonQuery(); MessageBox.Show(n.ToString()); But it returns me -1. I tried this stored procedure by entering the same values I captured from debugging. It was successful. What can be the possible error? Thanks a lot!

  Read the article

• What a Performance! MySQL 5.5 and InnoDB 1.1 running on Oracle Linux

  - by zeynep.koch(at)oracle.com

  The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines. Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores. A full analysis of results and MySQL configuration parameters are documented in a new whitepaperIn addition to the benchmark, the new whitepaper, also includes:- A discussion of the use-cases for each storage engine- Best practices for users considering the migration of existing applications from MyISAM to InnoDB- A summary of the performance and scalability enhancements introduced with MySQL 5.5 and InnoDB 1.1.The benchmark itself was based on Sysbench, running on AMD Opteron "Magny-Cours" processors, and Oracle Linux with the Unbreakable Enterprise Kernel You can learn more about MySQL 5.5 and InnoDB 1.1 from here and download it from here to test whether you witness performance gains in your real-world applications.  By Mat Keep

  Read the article

• Use a stored procedure parameter for unit parameter of DATE_SUB

  - by Mike

  I would like to know if it's possible to pass a parameter to a mysql stored procedure and use this parameter as the unit parameter of the DATE_SUB function. It seems that the unit parameter is a reserved word so I don't know if there's a type for unit. Exemple of what I'm trying to do : DELIMITER $$ DROP PROCEDURE IF EXISTS `test` $$ CREATE DEFINER=`root`@`%` PROCEDURE `test`(param1 unit) BEGIN select DATE_SUB(now(), INTERVAL 1 param1); END $$ DELIMITER ;

  Read the article

• Error executing IBM DB2 Stored Proceedure in EJB container

  - by n002213f

  I'm getting the error below when i try to execute a stored procedure in a Stateless bean with container managed persistance; com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-751, SQLSTATE=38003, SQLERRMC=STORED PROCEDURE;FXTR324;FXTR324;COMMIT, DRIVER=4.7.85 The stored proc executes without errors if i manually create the connection the database, i.e. unmanaged transaction. Is there anything i need to do for it to execute in the EJB bean?

  Read the article

• ERROR 1005 (HY000): Can't create table 'tmp' (errno: 13)

  - by kobey

  Hi, I'm Running Mysql on ubuntu 9.10, the process of Mysql is running as root, I'm using root account when logging to Mysql, which I gave all privileges, I'm using my own db(not mysql), I can create a table, but when i try to create Temporary table i get this error: ERROR 1005 (HY000): Can't create table 'tmp' (errno: 13) For this query: CREATE TEMPORARY TABLE tmp (id int); I've plenty of space in my hard drive, all permissions are granted(also var/lib/mysql have mysql permissions). Any idea? Thanks, Koby

  Read the article

• Error when calling Mysql Stored procedure

  - by devuser

  This is my stored procedure to search throgh all databases,tables and columns. This procedure got created with out any error. DELIMITER $$ DROP PROCEDURE IF EXISTS mydb.get_table$$ CREATE DEFINER=root@% PROCEDURE get_table(in_search varchar(50)) READS SQL DATA BEGIN DECLARE trunc_cmd VARCHAR(50); DECLARE search_string VARCHAR(250); DECLARE db,tbl,clmn CHAR(50); DECLARE done INT DEFAULT 0; DECLARE COUNTER INT; DECLARE table_cur CURSOR FOR SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM ', table_schema,'.', table_name, ' WHERE ', column_name,' REGEXP ''',in_search,'''' ) ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('mydb','information_schema'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; #Truncating table for refill the data for new search. PREPARE trunc_cmd FROM 'TRUNCATE TABLE temp_details'; EXECUTE trunc_cmd ; OPEN table_cur; table_loop:LOOP FETCH table_cur INTO search_string,db,tbl,clmn; #Executing the search SET @search_string = search_string; SELECT search_string; PREPARE search_string FROM @search_string; EXECUTE search_string; SET COUNTER = @CNT_VALUE; SELECT COUNTER; IF COUNTER0 THEN # Inserting required results from search to table INSERT INTO temp_details VALUES(db,tbl,clmn); END IF; IF done=1 THEN LEAVE table_loop; END IF; END LOOP; CLOSE table_cur; #Finally Show Results SELECT * FROM temp_details; END$$ DELIMITER ; But when calling this procedure following error occurs. call get_table('aaa') Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete REGEXP 'aaa'' at line 1 (0 ms taken)

  Read the article

• Improving Performance of Crystal Reports using Stored Procedures

  - by mjh41

  Recently I updated a Crystal Report that was doing all of its work on the client-side (Selects, formulas, etc) and changed all of the logic to be done on the server-side through Stored Procedures using an Oracle 11g database. Now the report is only being used to display the output of the stored procedures and nothing else. Everything I have read on this subject says that utilizing stored procedures should greatly reduce the running time of the report, but it still takes roughly the same amount of time to retrieve the data from the server. Is there something wrong with the stored procedure I have written, or is the issue in the Crystal Report itself? Here is the stored procedure code along with the package that defines the necessary REF CURSOR. CREATE OR REPLACE PROCEDURE SP90_INVENTORYDATA_ALL ( invdata_cur IN OUT sftnecm.inv_data_all_pkg.inv_data_all_type, dCurrentEndDate IN vw_METADATA.CASEENTRCVDDATE%type, dCurrentStartDate IN vw_METADATA.CASEENTRCVDDATE%type ) AS BEGIN OPEN invdata_cur FOR SELECT vw_METADATA.CREATIONTIME, vw_METADATA.RESRESOLUTIONDATE, vw_METADATA.CASEENTRCVDDATE, vw_METADATA.CASESTATUS, vw_METADATA.CASENUMBER, (CASE WHEN vw_METADATA.CASEENTRCVDDATE < dCurrentStartDate AND ( (vw_METADATA.CASESTATUS is null OR vw_METADATA.CASESTATUS != 'Closed') OR TO_DATE(vw_METADATA.RESRESOLUTIONDATE, 'MM/DD/YYYY') >= dCurrentStartDate) then 1 else 0 end) InventoryBegin, (CASE WHEN (to_date(vw_METADATA.RESRESOLUTIONDATE, 'MM/DD/YYYY') BETWEEN dCurrentStartDate AND dCurrentEndDate) AND vw_METADATA.RESRESOLUTIONDATE is not null AND vw_METADATA.CASESTATUS is not null then 1 else 0 end) CaseClosed, (CASE WHEN vw_METADATA.CASEENTRCVDDATE BETWEEN dCurrentStartDate AND dCurrentEndDate then 1 else 0 end) CaseCreated FROM vw_METADATA WHERE vw_METADATA.CASEENTRCVDDATE <= dCurrentEndDate ORDER BY vw_METADATA.CREATIONTIME, vw_METADATA.CASESTATUS; END SP90_INVENTORYDATA_ALL; And the package: CREATE OR REPLACE PACKAGE inv_data_all_pkg AS TYPE inv_data_all_type IS REF CURSOR RETURN inv_data_all_temp%ROWTYPE; END inv_data_all_pkg;

  Read the article

• Creating a Model using Stored Procedures with Zend Framework

  - by jwhat

  I'm using Zend Framework and I'd like to build a model to perform read/write operations on a database... using stored procedures. I know how stored procedures work, but I have yet to use them within Zend Framework. Is there any built in support for stored procedures that I should know about? What is the best practice way to create a model in this senario? Should I extend Zend_Db_Table_Abstract or some other class?

  Read the article

• Debugging stored procedure in SQL Server 2008 Management Studio

  - by larryq

  Hi everyone, I have a stored proc I'd like to debug in the SQL Server 2008 management studio. I see a number of tutorials about doing this, but the ones I've seen don't have any input parameters going into the SP. My SP has several, and an output parameter as well. Could someone show me how to do debug a stored procedure with parameters in SQL 2008? Thanks very much.

  Read the article

• Hibernate and stored procedures

  - by cc96ai

  As my understanding on setting hibernate, I need to create table meta data file (person.hbm.xml), include all the fields mapping java object (person.java) If we use stored procedures for all transaction, do we still need the above configuration? It seems hibernate and stored procedures will overlap, We set up the stored procedure because we don't want the to developer know all the field in db. If tables change, then we need update above files. Does it mean if we purely use stored procedure, we should just go for JDBC? If hibernate, we should stay in HQL?

  Read the article

< Previous Page | 42 43 44 45 46 47 48 49 50 51 52 53  | Next Page >