tsql

SQL join from multiple tables

Hi all We've got a system (MS SQL 2008 R2-based) that has a number of "input" database and a one "output" database. I'd like to write a query that will read from the output DB, and JOIN it to data in one of the source DB. However, the source table may be one or more individual tables :( The name of the source DB is included in the outpu...

deleting and reusing a temp table in a stored precedure

Hi I need to SELECT INTO a temp table multiple times with a loop but I just can't do it, because after the table created by SELECT INTO you can't simply drop the table at the end of the loop, because you can't delete a table and create it again in the same batch. so how can I delete a table in a stored procedure and create it again? i...

New help with SQL Server select statement

I need help with select statement in SQL Server / T-SQL My table looks like this: Id (int) QuestionId (int) GenreId (int) QuestionScore (int) I want to select random N rows from this table so that maximum number of same GenreId in the result set is less than X for all GenreId-s except one. For that one GenreId, I need row ...

How to do bulk update of views?

My database has about 30 views, most of which have a reference to another database on this server (call it DB1). Now, without going into the reasons why, I need to update all those views to DB2, also on the local server. I would hate to have to do this manually on each view. Is there some SQL query I can run that will replace all occu...

Undo table updates in SQL Server 2008

I updated a table in my SQL Server 2008 by accident, I was updating a table from another by copying cell by cell, but I have overwritten the original table. Is there a way that I can restore my table contents as it was? ...

SQL Server 2005 - Building a WHERE clause

Hello, I have a stored procedure that is dynamically building a query. The where clause associated with this query is based on filter values selected by a user. No matter what I do though, the where clause does not seem to get set. -- Dynamically build the WHERE clause based on the filters DECLARE @whereClause as nvarchar(1024) IF (@ha...

SSIS package randomly hangs during execution

Hi Guys, I am having an ongoing and painful problem with an SSIS package. The package runs every 5 minutes as an SQL Agent Job and every 2-10 days the package will start running and never stop (thus preventing further executions). If I stop the hung job manually it will begin working perfectly again in the next 5 minute interval. The S...

T-SQL: How to make a positive value turn into the equivalent negative value (e.g "10.00" to "-10.00"

Ok so i have a DECIMAL field called "Score". (e.g 10.00) Now, in my SP, i want to increment/decrement the value of this field in update transactions. So i might want to do this: SET @NewScore = @CurrentScore + @Points Where @Points is the value im going to increment/decrement. Now lets say @Points = 10.00. In a certain scenario, i...

how to get result from this data.

I want to compute result from this table. I want quantity 1 - quantity2 as another column in the table shown below. this table has more such records I am trying to query but not been able to get result. select * from v order by is_active desc, transaction_id desc PK_GUEST_ITEM_ID FK_GUEST_ID QUANTITY TRANSACTI...

Execute SPROC after completion of another SPROC

Essentially, I want to do this in T-SQL 1) Execute SPROC1 2) Upon completion of SPROC1, execute SPROC2 3) Upon completion of SPROC2, execute SPROC3 I know this is possible using SSIS. If there any way I can do this? Thanks ...

Insert into temp table from stored procedure generating distributed transaction error in SQL 2000

Similar to Insert into a temp table from a stored procedure on Sql Server 2000 from last year, but now with a strange error. I'm using Microsoft SQL Server 2000 to dump results from one stored procedure into the temp table of another procedure. Both procedures are on the same physical server, yet I'm getting an error message as if the s...

Order By Problem T-Sql

I have a sql statement I am using a simple sort such as the following Select numbers From theTable Order By numbers What I get in return is the following 1 11 12 14 2 21 22 23 3 35 37 etc... I want it to be ordered in normal order 1 2 3 4 5 6 etc... ...

TSQL Grouping and Ordering for Dynamic ASP.Net Table

I hoping someone may be able to point me in the right direction for this... Basically Im writing an asp.net web control to dynamically display a set of data based on field data stored in the database. I want the control to create a dynamic asp table from a set of data from the database, however I cant get my head round the query needed...

TSQL Shred XML - Is this right or is there a better way (newbie @ shredding XML)

Ok, I'm a C# ASP.NET dev following orders: The orders are to take a given dataset, shred the XML and return columns. I've argued that it's easier to do the shredding on the ASP.NET side where we already have access to things like deserializers, etc, and the entire complex of known types, but no, the boss says "shred it on the server, ret...

TSQL Shred XML - Working with namespaces [ANSWERED]

Here's a link to my previous question on this same block of code with a working shred example Ok, I'm a C# ASP.NET dev following orders: The orders are to take a given dataset, shred the XML and return columns. I've argued that it's easier to do the shredding on the ASP.NET side where we already have access to things like deserializers,...

How to query database where date and time field are separate?

In Table, Time and date fields are two separate fields. I need to make a query something like this give all records where date and time <= givenDateTime. How do i do that please? EDIT My query:- select DateField, TimeField from Table1 where DateField <= '07/26/2009' and TimeField <= '16:26:20' Result is:- 2008-04-22 00:00:00.000 189...

Problem with parsing XML into table variable

I'm using the following code to read a SQL XML Variable into a table variable. I am getting the following error. " Incorrect syntax near '.'. " Can't quite Figure it out DECLARE @LOBS Table ( LineGUID varchar(40) ) DECLARE @lg xml SET @lg = '<?xml version="1.0" encoding="utf-16" standalone="yes"?> <Table> <LOB> <LineGuid>d6e3a...

T-SQL Conditonal Select Statement

So this isn't really a normal conditional select statement. I'm helping a colleague out with some SQL and we have this issue. I current there is a coulumn ID and a column Number. Either ID is 0 or Number is 0. So my select statement needs to do Select colA, colB, colC, crazy part From Table A. the crazy part is this: (If ID > 0, ID, N...

T-SQL: How Do I Create A "Private" Function Inside A Stored Procedure

Okay so im writing a SQL Server 2008 Stored Procedure (maintenance script). In doing so, being a good boy i've done plenty of error handling, checking rowcounts, printing output messages, etc But in doing this, ive found myself writing over and over again something like this: SELECT @RowsAffected = @@ROWCOUNT IF @RowsAffected > 0 BEGI...

In TSQL, how can I get the rindex functionality(search a substring from right and get the position)?

Hi guys, In TSQL, how can I get the rindex functionality(search a substring from right and get the position)? Great thanks. ...