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 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. ...
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...
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 ...
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...
I'm having to insert values into a new column in our database but I can't get my head around doing this in a consistent manner. There is a lot of data so doing anything manually is pretty much out of the question. Let me set the stage: We have a table called Occurrence and a table called OccurenceBuckets where each occurrence is referen...
Hi I have the following SQL: SELECT * FROM Name INNER JOIN ( SELECT 2 AS item, NameInAddress.NameID as itemID, NameInAddress.AddressID FROM NameInAddress INNER JOIN Address ON Address.AddressID = NameInAddress.AddressID WHERE (Address.Country != 'UK') ) AS Items ON (Items.itemID = Name .Name ID) I have been asked to r...
Is there a way I can search for a particular string value in a SQL SERVER 2005 database? I have this image path value that I want to modify and I think it's stored in the database. Also - is there a way that I can search for a column's name for any table in a database? Likewise, search for a tables name in a database? ...
Which is faster for millions of records: Permanent Table or Temp Tables? I have to use it only for 15 million records. after Processing complete. we delete these records.. ...
For several tables that have identity fields, we are implementing a Row Level Security scheme using Views and Instead Of triggers on those views. Here is a simplified example structure: -- Table CREATE TABLE tblItem ( ItemId int identity(1,1) primary key, Name varchar(20) ) go -- View CREATE VIEW vwItem AS SELECT * F...
Here is what I want to do: I am trying to obfuscate data and create a working database to be used for documentation. I have a list of first names that I want to mix up in a table. KEY FIRSTNAME 135 CYNTHIA 181 MARK 186 MARGARET 212 DESIVANITA 506 AMY 606 BRENDA 628 KATHLEEN 629 Johnna 636 TAMARA 652 KATHLEEN Th...
Hi, I have to create a query that checks across several different columns, and if any of them have a 1, I want to return true. Ideal output would be along the lines of: ID:55 Name:John Doe IsDealerType1:True IsDealerType2:True IsDealerType3:False IsDealerType4:False IsDealerType5:True The problem is, instead of those 5 dealer col...
Consider the following composite clustered index: CREATE UNIQUE CLUSTERED INDEX ix_mytable ON mytable(a, b) Obviously, a separate index on b will make searching for a particular value of b faster. However, if a separate index on b is not employed, it seems to me that the composite index can still be used to find tuples with a particu...
I am creating partition function and schemes. In SQL Server 2008, it only defines range partitioning and not list partitions. Dont we have list partitioning in SQL Server? I am using SQL Server 2008 Enterprise edition. ...
In an attempt to build example code for this question, I ran into a problem with CONTEXT_INFO(). What I'm doing is converting an int to varbinary(128) so I can pass that to SET CONTEXT_INFO. I can convert the varbinary back to int before I do the SET, but after I SET and then GET, the CONVERT always returns zero even though the varbinar...
How can i export table data with column name in text file in sql server 2005? ...
I face one problem, one column is i have taken as numeric(38,0) Data stored in this column is going out of range, means it is reach it's storage limitation. i have tried with also bigint , but facing same problem.i can not take it as varchar because i have to use in another calculation so at that time i must need to convert it from varc...
I have a web page that runs under an account named, WebUser (IIS runs under this account) Now the problem here is that, when the webpage is accessed by users (intranet), users are authenticated through Windows Authentication. The webpage calls a stored procedure, SaveClientInfo. When I was trying to get the user's name (say, User1) wh...
Have a temp table with schema: ID | SeqNo | Name ID - Not unique SeqNo - Int (can be 1,2 or 3). Sort of ID+SeqNo as Primary key Name - Any text And sample data in the table like this 1 | 1 | RecordA 2 | 1 | RecordB 3 | 1 | RecordC 1 | 2 | RecordD 4 | 1 | RecordE 5 | 1 | RecordF 3 | 1 | RecordG Need to select from this ...