tsql

MS SQL Update within a loop

Hi, im trying to realize something like this: DECLARE @id int declare @cont varchar(max) declare @temp varchar(max) DECLARE curs CURSOR FOR SELECT id, [content] FROM [dbo].[Contents] OPEN curs FETCH NEXT FROM curs into @id, @cont WHILE @@FETCH_STATUS = 0 BEGIN SET @temp = replace(@cont, @param_ReplaceThis, @param_WithReplace...

How can I find out which data won't cast?

I have a SQL table with a date field defined as char(8), or 20090609, and a time field defined as char(4), or 1230. I am moving this data into another table and I want to combine the two fields and put them in a smalldatetime field in the new table. My query is like this: INSERT NewTable(eventdate) SELECT CAST((datecol + ' ' + substr...

Adding rowguid column broke this Stored Procedure?

I have added a bounty to this as I have, as yet been able to figure this out and time is out. The below stored procedure will not allow me to add it to Modify it. When attempting to modify it I get the following error --> Msg 213, Level 16, State 1, Procedure spPersonRelationshipAddOpposing, Line 51 Insert Error: Column name or n...

Using "GO" within a transaction

I'm building a web app that attempts to install/upgrade the database on App_Start. Part of the installation procedure is to ensure that the database has the asp.net features installed. For this I am using the System.Web.Management.SqlServices object. My intention is to do all the database work within an SQL transaction and if any of it ...

loopback detection

I have a Master File table on a server(A). Data is first replicated to a central server(B) and then to all of our locations(C). Then from C->B->A A = Sql 2005 B = SQL 2005 C = Sql 2000 My issue is that data gets all the way to C but replication is set for updates yet I get a Primary Constraint error from C -> B. I set loopback detect...

Taking an average in SQL after throwing away outliers

I have a generic log table which I can attach to processes and their results. I get the average time using a process performance view: WITH Events AS ( SELECT PR.DATA_DT_ID ,P.ProcessID ,P.ProcessName ,PL.GUID ,PL.E...

How to add values coming from 2 queries

From the 1st query I am getting some value and from 2nd query I am getting some value. I want the sum of the two values. Query 1: select sum(EAmount) from EstimateAmount where pid='3' group by pid Query 2: select sum(OPEAmount) from OPEAmount where pid='3' group by pid ...

Effective SQL query to find first (by date) associated record in a 1-N relationship

Here's the scenario: there is a Software table (PK = SoftwareID) and an associated Release table (PK = [SoftwareID,Version]). A release can be major or minor, release type is identified by Release.ReleaseType ('MAJ', 'MIN'). A release is also characterized by a date: Release.ReleaseDate. Software is partitioned into categories, ident...

How can i change datetime format in sql?

How can i change dateformat?Forexample: 2009-06-10 10:16:41.123->2009-June 2009-05-10 10:16:41.123->2009-May ...

T-SQL 2000: Four part table name

I don't usually work with linked servers, and so I'm not sure what I'm doing wrong here. A query like this will work to a linked foxpro server from sql 2000: EXEC('Select * from openquery(linkedServer, ''select * from linkedTable'')') However, from researching on the internet, something like this should also work: Select * from link...

How do I return a new IDENTITY column value from an SQLServer SELECT statement?

I'm inserting into an SQLServer table with an autoincrementing key field. (I believe this is called an IDENTITY column in SQLServer.) In Oracle, I can use the RETURNING keyword to give my INSERT statement a results set like a SELECT query that will return the generated value: INSERT INTO table (foreign_key1, value) VALUES (9, 'text') ...

Can a stored procedure have dynamic parameters to be used in an "IN" clause?

I want to run a query like this: SELECT * FROM Studio WHERE Id IN (134, 144, 132, 138, 7432, 7543, 2566) but the amount of Id's passed to the IN clause is only determined at runtime. Do I have to use dynamic SQL or can this be done with a stored procedure? UPDATE: If either option is available, which one is better? Thanks. ...

Many to Many Relation Design - Intersection Table Design

I'm wondering what a better design is for the intersection table for a many-to-many relationship. The two approaches I am considering are: CREATE TABLE SomeIntersection ( IntersectionId UNIQUEIDENTIFIER PRIMARY KEY, TableAId UNIQUEIDENTIFIER REFERENCES TableA NOT NULL, TableBId UNIQUEIDENTIFIER REFERENCES TableB NOT NUL...

How to get entries of current date portion from column with datetime as datatype (TSQL)

Hi, I have daily scheduled task that queries a table which includes column named AttemptDate with datetime as datatype. However the task queries all entries regardless of the date. So when the tasks executes, it outputs entries of all the dates as shown below: 2009-06-06 06:01:30.852 2009-06-07 01:41:46.719 2009-06-08 03:58:23.945...

How can I copy data using T-SQL?

I have tables named Categories, Questions and Selections. The relationship of the tables are: there can be 1 or more Selections in a Question and there can be one or more Questions in a Category. The table columns are the following: Categories - CategoryID (pk) - Name Questions - QuestionID (pk) - Question - Cat...

How can I join a table to itself in T-SQL?

I have the following query: SELECT [ApplicationTitle] AS "ApplicationTitle", SUM([Visits]) AS "Visits", SUM([NewVisits]) AS "NewVisits" FROM [HeadlineFigures] WHERE [DataDate] >= '01/06/2009' AND [DataDate] < '08/06/2009' GROUP BY [ApplicationTitle] ORDER BY [ApplicationTitle] This returns data for 7 days. I want the same again, in ...

How to improve 'Incorrect syntax near the keyword 'select'.'

this query give me 'Incorrect syntax near the keyword 'select'.' look please below bold character area. declare @date1 smalldatetime, @date2 smalldatetime, @page nvarchar(100) ,@sum int select @date1='2009-06-06',@date2='2009-06-13',@page='Tüm Sayfalar' set @sum = select Sum(t.[VISITINGCOUNT]) from ( select count(page) as [VISIT...

Can I create a One-Time-Use Function in a Script or Stored Procedure?

In SQL Server 2005, is there a concept of a one-time-use, or local function declared inside of a SQL script or Stored Procedure? I'd like to abstract away some complexity in a script I'm writing, but it would require being able to declare a function. Just curious. Thanks!! ...

tsql string concat with select and order by does not work with function in order by clause?

Consider the following tsql... create function dbo.wtfunc(@s varchar(50)) returns varchar(10) begin return left(@s, 2); end GO select t.* into #test from ( select 'blah' as s union select 'foo' union select 'bar' ) t select * from #test; declare @s varchar(100); set @s = ''; select @s = @s + s from #test order by s; selec...

the multi-part identifier could not be bound

select (case when t.freeplayabandoned != f.freeplayabandoned then 'freeplayabandoned' when t.freeplaydownloaded != f.freeplaydownloaded then 'freeplaydownloaded' end) from testtable t where not exists (select * from freeplay.egmfreeplay f where f.freeplaydownloaded = t.freeplaydownloaded) I was trying to get the col...