tsql

Optionally use a UNION from another table in T-SQL without using temporary tables or dynamic sql?

I have two sql server tables with the same structure. In a stored procedure I have a Select from the first table. Occasionally I want to select from the second table as well based on a passed in parameter. I would like a way to do this without resorting to using dynamic sql or temporary tables. ...

How to convert Seconds to HH:MM:SS using T-SQL

The situation is you have a value in Seconds (XXX.XX), and you want to convert to HH:MM:SS using T-SQL. Example: 121.25 s becomes 00:02:01.25 ...

T-SQL query - GROUP BY issue

I have a table in SQL Server consisting of two columns that track how many users are logged into a system at a particular interval. The first column is Time15, which is the time rounded to the nearest 15 minute interval (datetime). The second column is UserCount, which is the number of users logged into the system during that 15 minut...

Ranking Students by Grade in SQL

I have a table like this: Date StudentName Score 01.01.09 Alex 100 01.01.09 Tom 90 01.01.09 Sam 70 01.02.09 Alex 100 01.02.09 Tom 50 01.02.09 Sam 100 I need to rank the students in the result table by score within different dates, like this: Date ...

TSQL DateTime issue

I have a simplified case here: a table with 5 fields of startdate, starttime, enddate, endtime and totalduration, all as varchar(20) type. Date fields are in the format like '02/02/2009' and time format like '02:02:00'. There are no null values at all. There is no problem for the following query: select cast(startdate + ' ' + sta...

Preserving SQLServer permissions

How can I stop a particular user from accessing the database for a period of time but at the same time not lose the permissions the user has on db objects. Basically when he comes back (ie when access is given back) he should have all the permissions he had before his access was cut off. If I use sp_revokedbaccess 'myuser' the user myu...

How to use the PRINT statement to track execution as stored procedure is running?

Reference: SQL Server I have a stored procedure with a while loop in it and I want some messages to be printed after every 500 loops. So, I've written - CREATE spxxx AS BEGIN BEGIN TRAN DECLARE @counter = 0; WHILE <somecondition> SET @counter = @counter + 1; IF @counter % 50 = 0 ...

T-SQL Conditional Select Across Tables

I have a table (lets call it main_guys) that looks like: id, other_item_id, other_item_type, another_column, group_id The other_item_type tells me which additional table holds additional information identified by the other_item_id. Additional columns required in the query depends on the table identified by other_item_type. So for oth...

Current transaction ID in an audit trigger

I was looking at storing some form of transaction id from an audit trigger. The solution appeared to be to use sys.dm_tran_current_transaction as in this post http://stackoverflow.com/questions/650761/sql-server-triggers-grouping-by-transactions/651112#651112. However, I cannot use this because the user account running sql statements wi...

TSQL: Get all rows for given ID

I am trying to output all of my database reports into one report. I'm currently using nested select statements to get each line for each ID (the number of ID's is unknown). Now I would like to return all the rows for every ID (e.g. 1-25 if there are 25 rows) in one query. How would I do this? SELECT ( (SELECT ... FROM ... WHERE id =...

T-SQL Get file via HTTP?

Is it possible to grab a file using T-SQL via HTTP? I'm familiar with grabbing a file via FTP using a script and xp_cmdshell. I want to do the same thing except the file is only available via HTTP. My guess is that it's not possible. If not, what are some alternatives? wget for Windows? My goal is to get the file onto the SQL serve...

'Database may not be activated yet or may be in transition' error

I have this heavily nested sql statement which works well in my sql server 2008 express. [code block below] However, when I move it over to our preliminary test server (sql server 2000) it doesn't work unless I use fully resolved table references in the from clauses of each statement. Which I can't do as the database name varies by inst...

Use composite select items in where clause

This question is best phrased with a simple example. Why can't I do this? select (lastname + ', ' + firstname) as fullname from people where fullname = 'Bloggs, Joe' instead I have to do this: select (lastname + ', ' + firstname) as fullname from people where (lastname + ', ' + firstname) = 'Bloggs, Joe' which smells bad to me. T...

TSQL Get partial string of DateTime value?

I have the following TSQL query to get date time value different: SELECT CONVERT(DATETIME, EndDtField - StartDtField, 120) AS Duration FROM myTable; The result will be: Duration 1900-01-01 23:02:04.000 .... I tried to get partial string from the result like this '01 23:02:04.000' by using RIGHT() function (actually preferab...

OPENXML Remote Scan Performance

I'm looking for some advice on OPENXML. Specifically, the performance. I am seeing very slow performance on a very small piece of XML. Something about this is causing a Remote Scan. Any ideas on how to go about tuning it? DECLARE @idoc int EXEC sp_xml_preparedocument @idoc OUTPUT, @ResourceXML DECLARE @tmpRes TABLE (ResourceID...

Correct String Escaping for T-SQL string literals

I want to use a query as following, I am looking for exact information/link to escape strings BookTitle is NVARCHAR(200) SELECT * FROM Books WHERE BookTitle IN ('Mars and Venus', 'Stack''s Overflow \r\n') Question: Does only "'" needs to be escaped or even \r\n needs to be escaped as well? MySql .Net Provider exposes a method to esca...

SQL Query to search schema of all tables.

I am working on a SQL Server 2008 Db that has many tables in it (around 200). Many of these tables contain a field by the name "CreatedDate". I am trying to identify all the table schema with this particular field. Is there a SQL query to do this? Appreciate your response! ...

SQL Server (2005) Linked Server Issue

I have SQL Server 2005 with several linked server defined. One of them is a connection to an Oracle server and another one is an ODBC bridge to another server on a remote machine (ODBC server). Recently I tried to use the linked server to Oracle to update data with two large size tables by using several joints. The update query took too...

tsql : best way to cast variables to string type?

I wrote a tsql procedure which inserts a string into a text file which means it requires all variables be converted to a string. Instead of using a case by case statement, is there an easier to do this which encompasses all cases and forces whatever type to a string type? thanks in advance ...

How to refactor this sql query

Hi, I have a lengthy query here, and wondering whether it could be refactor? Declare @A1 as int Declare @A2 as int ... Declare @A50 as int SET @A1 =(Select id from table where code='ABC1') SET @A2 =(Select id from table where code='ABC2') ... SET @A50 =(Select id from table where code='ABC50') Insert into tableB Select Case when @A1...