If I were to select a row from a table I basically have two options, either like this int key = some_number_derived_from_a_dropdown_or_whatever SqlCommand cmd = new SqlCommand("select * from table where primary_key = " + key.ToString()); or use a parameter SqlCommand cmd = new SqlCommand("select * from table where primary_key = @pk")...
Hi, I'm tracking machine state which can be 0,1 and 2, and storing that data in sql table with time_stamp. I have table in sql server with next fields: id(int) time_stamp(datetime) machine_state(int) Machine state is connected with machine condition: machine_state =0 -machine stooped machine_state =1-machine with alarm machine_state =2...
I'd like to pass a table as a parameter into a scaler UDF. I'd also prefer to restrict the parameter to tables with only one column. (optional) Is this possible? EDIT I don't want to pass a table name, I'd like to pass the table of data (as a reference I presume) EDIT I would want my Scaler UDF to basically take a table of values ...
I want to update a table called Sorels_ext from a table called Sorels. The link between them is the fkey_id of Sorels_ext equals the identity_column of the Sorels table. This is my first attempt at a Merge statement and I'm trying to learn the syntax. MERGE Sorels_ext AS SORe USING (select SOR.identity_column, CASE WHEN left(SO...
I have a table in SQL Server 2000 with data similar to the following: ReferenceNumber ReferenceValue 00001 Not assigned 00002 Not assigned 00002 ABCDE in which each ReferenceNumber can appear multiple times in the table, either with a ReferenceValue of 'Not assigned' or a true ReferenceValue. ...
In SQL Server 2005, if you have a database with no backup - but it is running in full recovery mode - is it possible to "roll back" some deletes? If yes - how? ...
I have a water pump that the run state is stored in a mssql database every 10 seconds along with the time of day. The pump cycles often through out the day. How do I query for the total time the pump was on during the day? ...
This might be a loaded question, and it might get voted down, but it completely frustrates me. When you use SQL Server in your development the data access is fast and efficient, but when you use COM to talk to Excel it is piggy slow. It doesn't matter what data access technology you use, ADO, ADO.NET, LINQ, Entity Framework, Astoria (A...
Hello, I am passing a comma-delimited list of values into a stored procedure. I need to execute a query to see if the ID of an entity is in the comma-delimited list. Unfortunately, I think I do not understand something. When I execute the following stored procedure: exec dbo.myStoredProcedure @myFilter=N'1, 2, 3, 4' I receive the f...
I am doing some research on migrating away from Microsoft sql server to postgresql and am looking for the best tools to get the job done. Specifically looking for a tool similar to MySQL Migration Toolkit, I did a test migration of our databases to MySQL and had each one under way in under an hour. It looks like pgAdmin will do most of...
I'm trying to return the Id of a row I update in sql UPDATE ITS2_UserNames SET AupIp = @AupIp WHERE @Customer_ID = TCID AND @Handle_ID = ID SELECT @@ERROR AS Error, @@ROWCOUNT AS RowsAffected, SCOPE_IDENTITY() AS ID and I keep getting Null for the ID, how can I get this? ...
Given a self referencing table Item ------------- Id (pk) ParentId (fk) With a related table of associated values ItemValue ------------- ItemId (fk) Amount And some sample data Item ItemValues Id ParentId ItemId Amount -------------------- ---------------------- 1 null ...
I'm just wondering, as a hypothetical example, what would be the best way to layout a table for the following scenario: Let's say I'm writing an app for tracking student attendance. At the beginning of every year, I want to add all the students in (I'll do this manually - now, should a student ID be assigned to each one here? Let's call...
Hi , I have a dataset with some 30 records in it. I want to update it to the database tables. which is the best method to update the table. I am unable to use dataadapter.update() since i am using a procedure to populate the dataset. is there any efficient way to update other than iterating through EACH record ...
How can I write a select statement to select only integers (and nothing more) from a char column in SQL Server. For example, my table name is POWDER with 2 columns, ID (int) and Name(char (5)) ID Name -- ---------- 1 AXF22 2 HYWWW 3 24680 4 8YUH8 5 96635 I want to be able to select only those rows that contain...
Hi there, I was wondering if it is possible to move all rows of data from one table to another, that match a certain query? For example, I need to move all table rows from Table1 to Table2 where their username = 'X' and password = 'X', so that they will no longer appear in Table1. I'm using SQL Server 2008 Management Studio. Thank yo...
Hi there, I have been working on a stored procedure performance problem for over a week now and is related to my other post on Stackoverflow here. Let me give you some background information. We have a nightly process which runs and is started by a stored procedure which calls many many many other stored procedures. Lots of the called ...
Is there a way to change the location of the SQL Management Studio 2005 Registered Servers? We want to use the same registered servers inside our team, so it would be great to point to a network location, so after changing a server it will automatically be visible to other team members. The export / import functionality still needs manua...
Hi, I am trying to debug a stored procedure on SQL Server 2008 with Visual Studio Team System 2008. When right-clicking the stored procedure in the Server Explorer and clicking on "Step Into", the IDE simply crashes without any error message. Any suggestions on how to proceed would be greatly appreciated. Thanks, Adrian ...
Hi, We have ASP.Net MVC Website which has < 200 users with long running sessions. Each user is expected to stay logged in for at least 6-8 hrs before logging out. The average session per user is expected to be less than 1 MB. We are using the ASPState database to for session. We want to put a restriction on the maximumm size of the tra...