if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[TestTable]
GO
insert into testtable values ('123','pass',null,'fail','skip')
insert into testtable values ('456','fail',null,'pass','skip')
insert into testtable values ('789',null,'fail','skip','pass')
insert into testtable values ('345','pass','pass','pass','fail')
I would like to fetch the COLUMNNAME where the value is fail.
Basically I need to know which test failed, test1, test2, test3 or
test4?
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
DECLARE @strComma VARCHAR(1000) SET @strComma = 'cmpnt_name,ASC,1,2' SELECT CAST(RIGHT(LEFT(@strComma,Number-1), CHARINDEX(',',REVERSE(LEFT(','+@strComma,Number-1)))) AS CHAR(30)) FROM master..spt_values WHERE Type = 'P' AND Number BETWEEN 1 AND LEN(@strComma)+1 AND (SUBSTRING(@strComma,Number,1) = ',' OR SUBSTRING(@strComma,Number,1) = '')
however i getting the data in one field which is like
cmpnt_name ASC 1 2
i would like them to be in their specific column that is 4 different field
is there a way to do this in create/alter table without the overhead of a trigger? I want to ensure that one of the 'key' values (even though it may be duplicated) cannot change after it's been inserted.
In MySQL it's simple to insert new columns in specific locations in a table like this
sql Code:
Original - sql Code
alter table foo add column bar integer not null default 0 after baz
ALTER TABLE foo ADD COLUMN bar integer NOT NULL DEFAULT 0 AFTER baz
But attempting to do the same thing in MSSQL fails with a syntax error on the word "after." I can insert columns in MSSQL but only if they are added to the end. I looked at the Microsoft insert documentation here but don't see any way to replicate the above MySQL command.
Is there any way to do this in MSSQL? Or do I have to completely recreate all my tables from scratch?
I want to write SQL that will search the tables in a database for a specific column, like this. For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in. It is always the first column.
I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.
I would surely appreciate if someone else has already done this!
The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:
I'm working on a join between two tables where I only want one row returned... I'm matching on two columns between two tables. One of those columns in the target table could be null. I only want one record returned.
SQL 2000.I need a query that will search a field in the table that is 14characters long and begins with a number.I have a client that was allowing people to store credit card numbers,plain text, in an application. I need to rip through the table andreplace every instance of credit card numbers with "x" and the last 4digits. I got the replace bit going, but I am stuck on how to searchfor a string in a field of a specific length.Any ideas?Thanks,--Mike
I have created a matrix with multiple rows in the main data cell and a subtotal at the end of the row. The first row in my matrix main cell is just a count of records, whereas the 2nd row is a % of the value in the 1st row compared to the total of that row. I have 5 columns in the matrix as below (only first row is shown plus the total row. Ignore any rounding issues):
Status A B C D E Total
02/01/2007 No. 9 32 3 13 0 57
% Total 15.00% 56.00% 5.00% 24.00% 0.00% 100.00%
Total No. 77 143 25 72 2 319
% Total 135.00% 350.00% 48.00% 250.00% 2.00% 556.00%
I have a total column at the end. I want to hide the %Total row within the total row as it does not make sense in this context. Anyone know how to achieve this?
I am building a data warehouse. I have many columns I want to populate in a fact table using integration services. Sample fields are: companyID, companyName, companyNumberOfAccounts, company, NumberOfUsers.
In the integration services package, I would like to keep this fact table in place and populated with data and to build integration services packages that update each of the existing row's specific columns (e.g. companyNameOfAccounts) one by one. For example, I have a source of data that has companyID and companyNumberOfAccounts data.
Is it possible to use the SQL Server Destination or OLE DB Destination Integration Services elements to import companyID and companyNumberOfAccounts data so that existing records just have their companyNumberOfAccounts column updated?
I've got an OLEDB DB2 linked server to a db2/AS400 instance and selecting from a table on the server has never caused problems before. One of the columns is a large text field. If I select all the columns but the large text field, it returns as normal, but including the large text field now, I get:
"Transport error: shared memory provider error: 0 - no process is on the other end of the pipe"
The largest entry in the text field is about 5k characters, and there don't appear to be any strange characters.
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL 1 1 2 1 3 0 1 0
The expected ID i get as a result of my query should be 1 in this case.
I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id MyString 1 ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1| ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1| DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330| alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id DesiredString 1 Extracts^This^String1 2 Extracts^This^String2 3 Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U')) DROP TABLE [dbo].[StringExtract] GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[StringExtract]( [Id] [int] NULL,
I need a trigger to know who and when a char(1) column is changed. Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test]( [Customer] [varchar](12) NULL, [Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values )
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
I have some duplicate values for my query results, about 200 duplicates out of 30000 rows. Of these 200 duplicates I want to keep the ones that have a higher value for... 'UpdatedBatchID'.
SELECT IR.Id as 'ID' , CAST(IR.Priority as varchar) as 'Priority' , IRSupportGroupDN.DisplayName as 'Support Group' , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate) as 'Created Date' , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.ResolvedDate) as 'Resolved Date' , SLOConfig.DisplayName as 'SLO' , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),SLOFact.TargetEndDate) as 'SLO Target' , SLOStatusDN.DisplayName as 'SLO Status' , SLOMetric.DisplayName as 'SLO Metric' , SLOFact.UpdatedBatchId as 'UpdatedBatchID'
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table: Customer, Type, Amount Customer1, Revenue, -100 Customer2, Cost, 60 Customer1, Revenue, -200 Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group): Revenue Cost Total Customer1 -100 60 -40 Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ): Revenue Cost Total Customer1 100 60 40 Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
Is any way we can tell sql server to keep specific (long runing) query in procedure Cache. I already tried to do this by creating job (run every 1 hr from 8 am to 6 pm) but is not enough
I'm in an unfortunate situation. We are posting information using a stored procedure to an outside SQL server connected to through a System DSN on our server (win 2003 server) using php's ODBC functions (we never had any luck connecting directly to the SQL server using php's mssql functions).
Everything is working fine, we can connect, send querys, etc ... but between 1am and 10am we recieve errors when trying to execute queries (though we can connect fine).
Whoops ... forgot to get the error returned before it turned 10am ... I can post it tomorrow
I think the database is being locked, but unfortunately I know very little about MS SQL server
The people who's database we are connecting to are not being helpful ... I was hoping I could get some suggestions on what would be the cause.
Anything you can suggest would be a huge help! Thanks! - Joe
Hi,Can't post specifics at the moment but if this seems like a commonproblem any help would be appreciated.When querying with ~6 tables, using mostly left outer joins, I getstandard performance with the where clausewhere XXX is not nullhowever, if I try the clausewhere XXX = 4the query takes upwards of 5-6 minutes (I just stop it running at thatpoint.The field XXX is in the 'main' table (to which the joins attach), it'san integer field too so I can't see too many problems there.There's no index on the XXX field, but if I remove the joins, I getstandard performance doing either query.So why would the second query's performance differ so significantly?Cheers,Chris
