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.
...
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
...
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...
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 ...
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...
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...
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
...
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...
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...
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 =...
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...
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...
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...
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...
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...
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...
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!
...
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...
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
...
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...