Can anyone tell me how to translate the following T-SQL statement: SELECT fileld1 = CASE WHEN T.option1 THEN -1 ELSE CASE WHEN T.option2 THEN 0 ELSE 1 END END FROM Table1 AS T The point is I need to validate two diff...
This happens when I query a text field using linked server. For example: select * from openquery(LS,'select text_field from table') then my server blows up! HELP? ...
I need to update a table from another table several times in succession. In order to make this script easier for people to use, I'd like to be able to dynamically reference it. Something like this: declare @databasename sysname set @databasename = 'm2mdata01.dbo' select * from @databasename.mytable This isn't working. Any suggestion...
We have a problem with our table schema falling out of sync with our view schema. I would like to know how I could have a stored procedure (for Sql Server) that gets all views in the database, and executes each one via select * Here is what I imagined (pseudo): Declare x Set x = Select object from sysobjects where object = view fore...
How could I make this query better? SELECT ClientID, BatchID, jobid, subjobid, count(clientid) as Total FROM data with(nolock) WHERE batchid in (select BatchID from data with(nolock) where lookupcode = '111111111111') and clientid in (select ClientID from data with(nolock) where lookupcode = '111111111111') and jobid in (select jobi...
Four column table - id (int), double1 (bit), double2 (bit), score (int) Want to write a query the returns the SUM() of the score column, grouped by id, where each row score can be changed based on the two double columns. So if the score is 10 and double1 and/or double2 columns are true, the row score is doubled once or twice. ...
How can I produce something like this with Linq? I'm trying to work something out, this is related to my other post, but my distinct keeps coming back with more rows than expected http://stackoverflow.com/questions/3533629/add-conditional-join-dynamically-with-linq select distinct c.CompanyID, c.CompanyName from Company c left join Com...
Someone please change my title to better reflect what I am trying to ask. I have a table like Table(id,value,value_type,data) ID is NOT unique. There is no unique key. value_type has two types. lets say A and B. Type B is better than A, but often not available. For each id if any records with value_type B exsits I want all the reco...
I don't want the list of all process, just a total percentage like you would see in windows taskmanager. I will be using this information via coldfusion, but i am having all kinds of problems just trying to find a total number of current cpu usage. I don't care if it comes from wmi or t-sql, i just want a total number for which i wil...
Let's say I have the following two tables: Fields FieldID INT FieldName NVARCHAR(50) Values FieldID INT Value NVARCHAR(1000) From the following stored procedure, how can I select only the values that are linked to a field which has the same FieldName than the one received as a parameter? CREATE PROCEDURE [dbo].[GetValues] @FieldN...
I am trying to develop a query to insert unique records but am receiving the SQL Server Primary Key error for trying to insert duplicate records. I was able to insert some values with this query but not for this record (score_14). So now I am trying to find duplicate record with the following query. The challenge is that my PK is ba...
My function ALTER FUNCTION GetProductCount (@CatID int) RETURNS int AS BEGIN DECLARE @return_value int EXEC @return_value = [dbo].[USP_Products_GetList] @ProductName = NULL, @ProductID = NULL, @Description = NULL, @CatID = @CatID, @CatName = NULL, @Price1 = NULL, @Price2 = NU...
Is there any sane way to capture execution-time object dependencies in SQL Server? For instance, take this dynamic SQL scenario: DECLARE @table SYSNAME = 'SomeTable' DECLARE @column SYSNAME = 'SomeColumn' DECLARE @proc SYSNAME DECLARE @command NVARCHAR(MAX) = 'SELECT TOP 1 @proc = '+@column+' FROM '+@table EXEC sp_executesql @command, ...
Hi all I have a Oracle linked server (10.g) on a Sql server 2005. Trying to import oracle table schema(s) into SQL using Select * into ttdsls030010 from openQuery(hades,'select * from baan.ttdsls030010 where rownum =1'); This throws an Error converting data type DBTYPE_DBTIMESTAMP to datetime Is there a way to autocast Oracle type ...
I've backup from a sqlserver created with this procedure: protected void WriteXML(string tableName) { using (SqlConnection cnn = new SqlConnection(ConnectionString)) { using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [" + tableName + "];", cnn)) { cnn.Open(); DataSet ds...
Observe the following table model: CREATE TABLE [site].[Permissions] ( [ID] INT REFERENCES [site].[Accounts]( [ID] ) NOT NULL, [Type] SMALLINT NOT NULL, [Value] INT NULL ); The site.Accounts->site.Permissions is a one-to-many relationship so 'ID' cannot be made a primary key due to the uniqueness that a PK imposes....
Hi All, I wanted to see if others are using SQL Server 2008 Change Data Capture and if so how do you like it? We currently use APEXSQL Audit Triggers for our auditing purposes which seems to work pretty well, but means we have to add triggers to all of our "audited" tables. Some of the articles I have read have pointed out things lik...
Hi All, We currently use Auditing triggers to track all of our DB changes. In order to capture the user_id for the person that submitted the delete we have to use an update and then have the trigger perform a delete so we can capture that USER_ID. Does anyone else use a different approach or have a better approach for this process? T...
Hi, I want to count the cities by gender, like this; City GenderFCount GenderMCount Redmond 10 20 Here is my query gets city and gender in AdventureWorks database select Gender,City from HumanResources.Employee as t1 inner join HumanResources.EmployeeAddress as t2 on t1.EmployeeID = t2.EmployeeID inner ...
Hello all, What's the right way to compare NTEXT column with a constant value? If I use something like [ntext2] <> '1,032.5', I get this error: The data types ntext and varchar are incompatible in the not equal to operator. The best possible solution would be if comparison is implemented in the same way for any column type. (<...