Search Results

Search found 35121 results on 1405 pages for 'sql stored procedure'.

Page 164/1405 | < Previous Page | 160 161 162 163 164 165 166 167 168 169 170 171  | Next Page >

• SQL Server 2012 memory usage steadily growing

  - by pgmo

  I am very worried about the SQL Server 2012 Express instance on which my database is running: the SQL Server process memory usage is growing steadily (1.5GB after only 2 days working). The database is made of seven tables, each having a bigint primary key (Identity) and at least one non-unique index with some included columns to serve the majority of incoming queries. An external application is calling via Microsoft OLE DB some stored procedures, each of which do some calculations using intermediate temporary tables and/or table variables and finally do an upsert (UPDATE....IF @@ROWCOUNT=0 INSERT.....) - I never DROP those temporary tables explicitly: the frequency of those calls is about 100 calls every 5 seconds (I saw that the DLL used by the external application open a connection to SQL Server, do the call and then close the connection for each and every call). The database files are organized in only one filgegroup, recovery type is set to simple. Some questions to diagnose the problem: is that steadily growing memory normal? did I do any mistake in database design which probably lead to this behaviour? (no explicit temp-table drop, filegroup organization, etc) can SQL Server manage such a stored procedure call rate (100 calls every 5 seconds, i.e. 100 upsert every 5 seconds, beyond intermediate calculations)? do the continuous "open connection/do sp call/close connection" pattern disturb SQL Server? is it possible to diagnose what is causing such a memory usage? Perhaps queues of wating requests? (I ran sp_who2, but I didn't see a big amount of orphan connections from the external application) if I restrict the amount of memory which SQL Server is allowed to use, may I sooner or later get into trouble?

  Read the article

• Free eBook: SQL Server Hardware

  SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS. New! SQL Prompt 6 – now with tab historyWriting, exploring, and editing SQL just became even more effortless with SQL Prompt 6. Download a free trial.

  Read the article

• Sql statement return with zero result [closed]

  - by foodil

  I am trying to choose the row where 1)list.ispublic = 1 2)userlist.userid='aaa' AND userlist.listid=list.listid I need 1)+2) There is a row already but this statement can not get that row, is there any problem? List table: ListID ListName Creator IsRemindSub IsRemindUnSub IsPublic CreateDate LastModified Reminder 1 test2 aaa 0 0 1 2012-03-09 NULL NULL user_list table (No row): UserID ListID UserRights My test version SELECT l.*, ul.* FROM list l INNER JOIN user_list ul ON ul.ListID = l.ListID WHERE l.IsPublic = 1 AND ul.UserID = 'aaa' There is zero result. How can I fix that?

  Read the article

• Advised auditing method for MS SQL to track changes made to a specific table by a specific user?

  - by scape

  What is the best method for tracking changes or logging the queries done to a table by a specific user when the person is using Management Studio? I'm using 2008 R2 Express Edition and want to specifically track a single user who logs in through Management studio and runs queries to make changes manually. I want to see what query was run and thus determine what was changed and how. I am not interested in restoring the information. I considered Change Tracking but read that it is not ideal for auditing as well I am unsure how to read the data, then I considered the Bulk-Logging option on the database however I then have to consider handling the log files which may grow huge as the database is used constantly by a web app. I am wondering if there is a more concise method to do what I want?

  Read the article

• SQL Server Semantic Search to Find Text in External Files

  Sometimes it is necessary to search for specific content inside documents stored in a SQL Server database. Is it possible to do this in SQL Server? Can I run T-SQL queries and find content inside Microsoft Word files? Yes, now with SQL Server 2012 you can do a semantic search. 12 essential tools for database professionalsThe SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

  Read the article

• SQL Server Stored Procedures Comparer

  Windows application that compares stored procedures between two SQL Server database

  Read the article

• Mimicking Network Databases in SQL

  Unlike the hierarchical database model, which created a tree structure in which to store data, the network model formed a generalized 'graph' structure that describes the relationships between the nodes. Nowadays, the relational model is used to solve the problems for which the network model was created, but the old 'network' solutions are still being implemented by programmers, even when they are less effective.

  Read the article

• Can’t connect to SQL Server 2008 - looks like Shared Memory problem

  - by user38556

  I am unable to connect to my local instance of SQL Server 2008 Express using SQL Server Management Studio. I believe the problem is related to a change I made to the connection protocols. Before the error occurred, I had Shared Memory enabled and Named Pipes and TCP/IP disabled. I then enabled both Named Pipes and TCP/IP, and this is when I started experiencing the problem. When I try to connect to the server with SSMS (with either my SQL server sysadmin login or with windows authentication), I get the following error message: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233) Why is it returning a Named Pipes error? Why would it not just use Shared Memory, as this has a higher priority order in the list of connection protocols? It seems like it is not listening on Shared Memory for some reason? When I set Named Pipes to enabled and try to connect, I get the same error message. My windows account is does not have administrator priviliges on my computer - perhaps this is making a difference in some way (as some of the discussions in this post about an "SuperSocketNetLib\Lpc" registry key seems to suggest). I have tried restarting the SQL Server service, by the way, and also tried to get someone to log onto the machine with an admin account to restart the SQL Server service. Still no luck.

  Read the article

• Helloooooooooooo, is anybody here?

  - by The Official Microsoft IIS Site

  Well, to start off with, we got a great response to our PHP survey and …. But wait, first things first, let us start off with introductions. I joined the team recently as the Program Manager for this driver. I’ve worked on several technologies in my career and very excited to be a part of this great team and look forward to working with you all, the PHP developer community! PHP and its ecosystem is new to me, so I’ve been ramping up over the last few months as well as helping the team as best I can...(read more)

  Read the article

• SQL Saturday #263 Manila, Phillipines

  SQL Saturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Nov 9 2013, admittance to this event is free, all costs are covered by donations and sponsorships. New! SQL Prompt 6 – now with tab historyWriting, exploring, and editing SQL just became even more effortless with SQL Prompt 6. Download a free trial.

  Read the article

• How do I iterate through hierarchical data in a Sql Server 2005 stored proc?

  - by AlexChilcott

  Hi, I have a SqlServer2005 table "Group" similar to the following: Id (PK, int) Name (varchar(50)) ParentId (int) where ParentId refers to another Id in the Group table. This is used to model hierarchical groups such as: Group 1 (id = 1, parentid = null) +--Group 2 (id = 2, parentid = 1) +--Group 3 (id = 3, parentid = 1) +--Group 4 (id = 4, parentid = 3) Group 5 (id = 5, parentid = null) +--Group 6 (id = 6, parentid = 5) You get the picture I have another table, let's call it "Data" for the sake of simplification, which looks something like: Id (PK, int) Key (varchar) Value (varchar) GroupId (FK, int) Now, I am trying to write a stored proc which can get me the "Data" for a given group. For example, if I query for group 1, it returns me the Key-Value-Pairs from Data where groupId = 1. If I query for group 2, it returns the KVPs for groupId = 1, then unioned with those which have groupId = 2 (and duplicated keys are replaced). Ideally, the sproc would also fail gracefully if there is a cycle (ie if group 1's parent is group 2 and group 2's parent is group 1) Has anyone had experience in writing such a sproc, or know how this might be accomplished? Thanks guys, much appreciated, Alex

  Read the article

• Get Multiple Values From Database ASP.NET/C#

  - by user1043177

  I am trying to get/return multiple values from an SQL-Server database using and display them on an ASP.NET page. I am using a stored procedure to perform the SELECT command on the Database side. I am able to return the first value that matches the variable @PERSON but only one row is returned each time. Any help would be much appreciated. Database handler class public MainSQL() { _productConn = new SqlConnection(); _productConnectionString += "data source=mssql.database.co.uk;InitialCatalog=test_data;User ID=username;Password=password"; _productConn.ConnectionString = _productConnectionString; } public string GetItemName(int PersonID) { string returnvalue = string.Empty; SqlCommand myCommand = new SqlCommand("GetItem", _productConn); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add(new SqlParameter("@PERSON", SqlDbType.Int)); myCommand.Parameters[0].Value = PersonID; _productConn.Open(); returnvalue = (string)myCommand.ExecuteScalar(); _productConn.Close(); return (string)returnvalue; } Stored Procedure USE [test_data] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ppir].[GetItem] ( @PERSON int ) AS /*SET NOCOUNT ON;*/ SELECT Description FROM [Items] WHERE PersonID = @PERSON RETURN return.aspx namespace test { public partial class Final_Page : System.Web.UI.Page { MainSQL GetInfo; protected void Page_Load(object sender, EventArgs e) { int PersonId = (int)Session["PersonID"]; GetInfo = new MainSQL(); string itemname = GetInfo.GetItemName(PersonId); ReturnItemName.Text = itemname; } // End Page_Load } // End Class } // End Namespace

  Read the article

• Displaying a message after adding duplicate records in database

  - by user1770370

  I wrote program in C# winforms and SQL server and LINQ to SQL. I use user control instead of form. In my user control, I put 3 textbox, txtStartNumber, txtEndNumber, txtQuantity. user define value of textboxes, when clicked button, it will insert some records according to the value of txtQuantity. I want to when duplicate number is created, it won't add to database and display message. how do i do? I must write code in code behind or server side? i must set this in store procedure or trigger? private void btnSave_Click(object sender, EventArgs e) { long from = Convert.ToInt64(txt_barcode_f.Text); long to = Convert.ToInt64(txt_barcode_t.Text); long quantity = Convert.ToInt64(to - from); int card_Type_ID=Convert.ToInt32(cmb_BracodeType .SelectedValue); long[] arrCardNum = new long[(to - from)]; arrCardNum[0]=from; for (long i = from; i < to; i++) { for(int j=0; j<(to-from) ;j++) { arrCardNum[j]=from+j; string r = arrCardNum[j].ToString(); sp.SaveCards(r, 2, card_Type_ID, SaveDate, 2); } } } Stored Procedure code. ALTER PROCEDURE dbo.SaveCards @Barcode_Num int ,@Card_Status_ID int ,@Card_Type_ID int ,@SaveDate varchar(10) ,@Save_User_ID int AS BEGIN INSERT INTO [Parking].[dbo].[TBL_Cards] ([Barcode_Num] ,[Card_Status_ID] ,[Card_Type_ID] ,[Save_User_ID]) VALUES (@Barcode_Num ,@Card_Status_ID ,@Card_Type_ID ,@Save_User_ID) END

  Read the article

• Visual C# 2008 Express connection to SQL Server 2008 Express problem

  - by Phil

  Hi guys, I have a problem with Visual C# 2008 express (SP1) connecting to SQL Server 2008 express. The "Add Connection" window (wherever initiated) doesn't list existing sql server and no option for sql server except a compact edition. Note that, I've got the VWD 2008 express (SP1) on the same machine which shows the window regularly (with SQL server listed) and SQL Server Management studio works fine with the server as well. I've seen other similar posts, did take some advices: reinstalled the VC#, services run ok, etc... but with no success with VC# so far. Again, on the same machine the VWD shows the dialog with sql server option regularly, but VC# shows only 3 options in "Change data source" dialog (1. Microsoft Access Database File (OLE DB) 2. Microsoft SQL Server Compact 3.5, 3. Microsoft SQL Server Database File) Any idea? Thanks in advice, Phil

  Read the article

• Finding all Stored procedures calling a function

  - by Abhishek Jain

  Hi, How can I find out all the stored procedures that are calling a particular user defined function in SQL Server 2005. Or how to assign a defult value to a parameter in a user defined function so that when a stored procedure calls that function and does not pass any value to that parameter function assumes the default value. Regards, Abhishek jain

  Read the article

• LINQ InsertOnSubmit Required Fields needed for debugging

  - by Derek Hunziker

  Hi All, I've been using the ADO.NET Strogly-Typed DataSet model for about 2 years now for handling CRUD and stored procedure executions. This past year I built my first MVC app and I really enjoyed the ease and flexibility of LINQ. Perhaps the biggest selling point for me was that with LINQ I didn't have to create "Insert" stored procedures that would return the SCOPE_IDENTITY anymore (The auto-generated insert statements in the DataSet model were not capable of this without modification). Currently, I'm using LINQ with ASP.NET 3.5 WebForms. My inserts are looking like this: ProductsDataContext dc = new ProductsDataContext(); product p = new product { Title = "New Product", Price = 59.99, Archived = false }; dc.products.InsertOnSubmit(p); dc.SubmitChanges(); int productId = p.Id; So, this product example is pretty basic, right, and in the future, I'll probably be adding more fields to the database such as "InStock", "Quantity", etc... The way I understand it, I will need to add those fields to the database table and then delete and re-add the tables to the LINQ to SQL Class design view in order to refresh the DataContext. Does that sound right? The problem is that any new fields that are non-null are NOT caught by the ASP.NET build processes. For example, if I added a non-null field of "Quantity" to the database, the code above would still build. In the DataSet model, the stored procedure method would accept a certain amount of parameters and would warn me that my Insert would fail if I didn't include a quantity value. The same goes for LINQ stored procedure methods, however, to my knowledge, LINQ doesn't offer a way to auto generate the insert statements and that means I'm back to where I started. The bottom line is if I used insert statements like the one above and I add a non-null field to my database, it would break my app in about 10-20 places and there would be no way for me to detect it. Is my only option to do a solution-side search for the keyword "products.InsertOnSubmit" and make sure the new field is getting assigned? Is there a better way? Thanks!

  Read the article

• Sql Server Backup and move backup file: How to cope with file permissions?

  - by Stefan Steinegger

  With our product we have a simple backup tool for the sql server database. This tool should just make a full backup and restore to and from any folder. Of course, the user (usually an administrator) needs permission to write to the target folder. To avoid the problem of not being able to perform a backup to a network drive, I write the backup to a temp file in the Sql Server backup directory. Then I move it to the target folder. This requires permission to delete the temporary file from the sql servers backup folder. Restore is the same in the other direction. This seemed to work fine until someone tested it on vista, where the user does not have write access to the backup folder by default. So there are many solutions to solve this, but none of them seemed to be really nice. One solution would be to find another folder for the temporary file. Both the sql server user as well as the administrator performing the backup need read and write permissions. Is there such a directory? Any other ideas? Thanks a lot.

  Read the article

• Is it possible to call a procedure within an SQL statement?

  - by darren

  Hi everyone I thought I would use a stored routine to clean up some of my more complex SQL statements. From what I've read, it seems impossible to use a stored procedure within an sql statement, and a stored function only returns a single value when what I need is a result set. I am using mySQL v5.0 SELECT p.`id`, gi.`id` FROM `sport`.`players` AS p JOIN `sport`.`gameinstances` AS gi ON p.`id` = gi.`playerid` WHERE (p.`playerid` IN (CALL findPlayers`("Canada", "2002"))) AND (gi.`instanceid` NOT IN (CALL findGameInstances`("Canada", "2002"))); For example, the procedures 'findPlayers' and 'findGameInstances' are are stored routines that execute some SQL and return a result set. I would prefer not to include their code directly within the statement above.

  Read the article

• How to pass a Dictionary variable to another procedure

  - by salvationishere

  I am developing a C# VS2008/SQL Server website application. I've never used the Dictionary class before, but I am trying to replace my Hashtable with a Dictionary variable. Here is a portion of my aspx.cs code: ... Dictionary<string, string> openWith = new Dictionary<string, string>(); for (int col = 0; col < headers.Length; col++) { @temp = (col + 1); @tempS = @temp.ToString(); @tempT = "@col" + @temp.ToString(); ... openWith.Add(@tempT, headers[col]); } ... for (int r = 0; r < myInputFile.Rows.Count; r++) { resultLabel.Text = ADONET_methods.AppendDataCT(myInputFile, openWith); } But this is giving me a compiler error on this last line: Argument '2': cannot convert from 'System.Collections.Generic.Dictionary' to 'string' How do I pass the entire openWith variable to AppendDataCT? AppendDataCT is the method that calls my SQL stored proc. I want to pass in the whole row where each row has a unique set of values that I want to add to my database table. For example, if each row requires values for cells A, B, and C, then I want to pass these 3 values to AppendDataCT, where all of these values are strings. How do I do this with Dictionary?

  Read the article

• Wildcards in T-SQL LIKE vs. ASP.net parameters

  - by Vinzcent

  In my SQL statement I use wildcards. But when I try to select something, it never select something. While when I execute the query in Microsoft SQL Server Management Studio, it works fine. What am I doing wrong? Click handler protected void btnTitelAuteur_Click(object sender, EventArgs e) { cvalTitelAuteur.Enabled = true; cvalTitelAuteur.Validate(); if (Page.IsValid) { objdsSelectedBooks.SelectMethod = "getBooksByTitleAuthor"; objdsSelectedBooks.SelectParameters.Clear(); objdsSelectedBooks.SelectParameters.Add(new Parameter("title", DbType.String)); objdsSelectedBooks.SelectParameters.Add(new Parameter("author", DbType.String)); objdsSelectedBooks.Select(); gvSelectedBooks.DataBind(); pnlZoeken.Visible = false; pnlKiezen.Visible = true; } } In my Data Access Layer public static DataTable getBooksByTitleAuthor(string title, string author) { string sql = "SELECT 'AUTHOR' = tblAuthors.FIRSTNAME + ' ' + tblAuthors.LASTNAME, tblBooks.*, tblGenres.GENRE " + "FROM tblAuthors INNER JOIN tblBooks ON tblAuthors.AUTHOR_ID = tblBooks.AUTHOR_ID INNER JOIN tblGenres ON tblBooks.GENRE_ID = tblGenres.GENRE_ID " +"WHERE (tblBooks.TITLE LIKE '%@title%');"; SqlDataAdapter da = new SqlDataAdapter(sql, GetConnectionString()); da.SelectCommand.Parameters.Add("@title", SqlDbType.Text); da.SelectCommand.Parameters["@title"].Value = title; DataSet ds = new DataSet(); da.Fill(ds, "Books"); return ds.Tables["Books"]; }

  Read the article

• How does C#'s DateTime.Now affect query plan caching in SQL Server?

  - by Bill Paetzke

  Given: Let's say we have a stored procedure. It reports data back to a user on a webpage. The user can set a date range. If the user sets today's date as the "end date," which includes today's data, the web app passes DateTime.Now to the sql proc. Let's say that one user runs a report--5/1/2010 to now--over and over several times. On the webpage, the user sees "5/1/2010" to "5/4/2010." But the web app passes DateTime.Now to the sql proc as the end date. So, the end date in the proc will always be different, although the user is querying a similar date range. Assume the number of records in the table and number of users are large. So any performance gains matter. Hence the importance of the question. Question: Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains? Possible Solution: I thought DateTime.Today.AddDays(1) would be a possible solution. It would allow the user to get the latest data and always pass the same end date to the sql proc--"5/5/2010" in this case. Please speak to this as well. Sample proc and execution (if that helps to understand): CREATE PROCEDURE GetFooData @StartDate datetime @EndDate datetime AS SELECT * FROM Foo WHERE LogDate >= @StartDate AND LogDate < @EndDate Here's a sample execution using DateTime.Now: EXEC GetFooData '2010-05-01', '2010-05-04 15:41:27' -- passed in DateTime.Now Here's a sample execution using DateTime.Today.AddDays(1) EXEC GetFooData '2010-05-01', '2010-05-05' -- passed in DateTime.Today.AddDays(1) The same data is returned for both procs, since the current time is: 2010-05-04 15:41:27.

  Read the article

• Hidden features of PL/SQL

  - by Adam Paynter

  In light of the "Hidden features of..." series of questions, what little-known features of PL/SQL have become useful to you? Edit: Features specific to PL/SQL are preferred over features of Oracle's SQL syntax. However, because PL/SQL can use most of Oracle's SQL constructs, they may be included if they make programming in PL/SQL easier.

  Read the article

• How does DateTime.Now affect query plan caching in SQL Server?

  - by Bill Paetzke

  Question: Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains? Possible Solution: I thought DateTime.Today.AddDays(1) would be a possible solution. It would pass the same end-date to the sql proc (per day). And the user would still get the latest data. Please speak to this as well. Given Example: Let's say we have a stored procedure. It reports data back to a user on a webpage. The user can set a date range. If the user sets today's date as the "end date," which includes today's data, the web app passes DateTime.Now to the sql proc. Let's say that one user runs a report--5/1/2010 to now--over and over several times. On the webpage, the user sees 5/1/2010 to 5/4/2010. But the web app passes DateTime.Now to the sql proc as the end date. So, the end date in the proc will always be different, although the user is querying a similar date range. Assume the number of records in the table and number of users are large. So any performance gains matter. Hence the importance of the question. Example proc and execution (if that helps to understand): CREATE PROCEDURE GetFooData @StartDate datetime @EndDate datetime AS SELECT * FROM Foo WHERE LogDate >= @StartDate AND LogDate < @EndDate Here's a sample execution using DateTime.Now: EXEC GetFooData '2010-05-01', '2010-05-04 15:41:27' -- passed in DateTime.Now Here's a sample execution using DateTime.Today.AddDays(1) EXEC GetFooData '2010-05-01', '2010-05-05' -- passed in DateTime.Today.AddDays(1) The same data is returned for both procs, since the current time is: 2010-05-04 15:41:27.

  Read the article

• Advice needed: cold backup for SQL Server 2008 Express?

  - by Mikey Cee

  What are my options for achieving a cold backup server for SQL Server Express instance running a single database? I have an SQL Server 2008 Express instance in production that currently represents a single point of failure for my application. I have a second physical box sitting at the installation that is currently doing nothing. I want to somehow replicate my database in near real time (a little bit of data loss is acceptable) to the second box. The database is very small and resources are utilized very lightly. In the case that the production server dies, I would manually reconfigure my application to point to the backup server instead. Although Express doesn't support log shipping, I am thinking that I could manually script a poor man's version of it, where I use batch files to take the logs and copy them across the network and apply them to the second server at 5 minute intervals. Does anyone have any advice on whether this is technically achievable, or if there is a better way to do what I am trying to do? Note that I want to avoid having to pay for the full version of SQL Server and configure mirroring as I think it is an overkill for this application. I understand that other DB platforms may present suitable options (eg. a MySQL Cluster), but for the purposes of this discussion, let's assume we have to stick to SQL Server.

  Read the article

• How is timezone handled in the lifecycle of an ADO.NET + SQL Server DateTime column?

  - by stimpy77

  Using SQL Server 2008. This is a really junior question and I could really use some elaborate information, but the information on Google seems to dance around the topic quite a bit and it would be nice if there was some detailed elaboration on how this works... Let's say I have a datetime column and in ADO.NET I set it to DateTime.UtcNow. 1) Does SQL Server store DateTime.UtcNow accordingly, or does it offset it again based on the timezone of where the server is installed, and then return it offset-reversed when queried? I think I know that the answer is "of course it stores it without offsetting it again" but want to be certain. So then I query for it and cast it from, say, an IDataReader column to a DateTime. As far as I know, System.DateTime has metadata that internally tracks whether it is a UTC DateTime or it is an offsetted DateTime, which may or may not cause .ToLocalTime() and .ToUniversalTime() to have different behavior depending on this state. So, 2) Does this casted System.DateTime object already know that it is a UTC DateTime instance, or does it assume that it has been offset? Now let's say I don't use UtcNow, I use DateTime.Now, when performing an ADO.NET INSERT or UPDATE. 3) Does ADO.NET pass the offset to SQL Server and does SQL Server store DateTime.Now with the offset metadata? So then I query for it and cast it from, say, an IDataReader column to a DateTime. 4) Does this casted System.DateTime object already know that it is an offset time, or does it assume that it is UTC?

  Read the article

< Previous Page | 160 161 162 163 164 165 166 167 168 169 170 171  | Next Page >