SQL Server 2005/2008 Express edition has the limitation of 4 GB per database. As far as I known the database engine considers data only, thus excluding log files, unused space, and index size. Getting the length of the MDF file should not give the correct database size in terms of SQL Server limitation. My question is how to get the dat...
Trying to perform a single boolean NOT operation, it appears that under MS SQL Server 2005, the following block does not work DECLARE @MyBoolean bit; SET @MyBoolean = 0; SET @MyBoolean = NOT @MyBoolean; SELECT @MyBoolean; Instead, I am getting more successful with DECLARE @MyBoolean bit; SET @MyBoolean = 0; SET @MyBoolean = 1 - @MyBo...
I need a select from table which does not have column that tells when row was inserted, only timestamp column (values like: 0x0000000000530278). Some data was imported to the table yesterday and now I need to find out what exactly was imported :( Is there a way to do it using only timestamp info? Here I found that: Timestamp is a 8 by...
I'm making a Stored Procedure that basically concatenates a long string with data from many records. I want to do: set @output = @output + 'aaa' + LINEBREAK How do I specify that line break? ...
I have the following function: CREATE FUNCTION fGetTransactionStatusLog ( @TransactionID int ) RETURNS varchar(8000) AS BEGIN declare StatusChanges cursor for select NewStatusID, FirstName + ' ' + LastName AS UserName, Stamp, CAST(Notes AS varchar(8000)) AS Notes from TransactionStatusChanges tsc left join Us...
I have a table which contains my server status create table ServerStatus ( ServerId int, StartTime datetime, Seconds int, [State] char(8) ) I would like a query that given a start and end date will summarize the time the server spends in each state during that time. I would also like the query to return the amount o...
I've got a column in a database table (SQL Server 2005) that contains data like this: TQ7394 SZ910284 T r1534 su8472 I would like to update this column so that the first two characters are uppercase. I would also like to remove any spaces between the first two characters. So T q1234 would become TQ1234. The solution should be able to...
Specifically MSSQL 2005. ...
Whenever I restore a backup of my database in SQL Server I am presented with the following error: Msg 3101, Level 16, State 1, Line 1 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Usually to get around this I just restart the serve...
Converting a couple stored procedures from MySQL to Microsoft SQL server. Everything is going well, except one procedure used the MySQL SHA1() function. I cannot seem to find an equivalent to this in MS-SQL. Does anyone know a valid equivalent for SHA1() on MS-SQL? ...
I have a project with a formidable data access layer using LinqtoSQL for just about anything touching our databases. I needed to build a helper class that bridges some common crud operations from CLSA objects to LinqToSql ones. Everything has been operating swimmingly until I needed to do a truncate on a table and all I had were “delete”...
I want to do this in code, not with ALT+F1. ...
I am current writing an application that will require multiple inserts, updates and deletes for my business entity. I am using the TransactionScope class to guarantee all the stored procedures can commit or roll back as a single unit of work. My question is, I am required to also use COMMIT TRAN and ROLLBACK TRAN is each of my stored p...
I've been led to believe that for single variable assignment in T-SQL, set is the best way to go about things, for two reasons: it's the ANSI standard for variable assignment it's actually faster than doing a SELECT (for a single variable) So... SELECT @thingy = 'turnip shaped' becomes SET @thingy = 'turnip shaped' But how fas...
Imagine I have a chunk of initialisation code at the top of a stored procedure with a number of variable assignments: SET @proc = 'sp_madeupname' SET @magic_number = 42 SET @tomorrows_date = DATEADD(dd, 1, GETDATE()) ... Clearly doing all of the above as one SELECT would be faster: SELECT @proc = 'sp_madeupname' ,@magic_numb...
Is there any way of determining whether or not a specific temp table has been created in a session without referencing the tempdb database that it was created on? Users are allocated to a specific tempdb when they log in, so I don't know which tempdb they'll be using. I don't need to specify a tempdb to select data out of the temp table...
I have created the following stored procedure.. CREATE PROCEDURE [dbo].[UDSPRBHPRIMBUSTYPESTARTUP] ( @CODE CHAR(5) , @DESC VARCHAR(255) OUTPUT ) AS DECLARE @SERVERNAME nvarchar(30) DECLARE @DBASE nvarchar(30) DECLARE @SQL nvarchar(2000) SET @SERVERNAME = Convert(nvarchar, (SELECT spData FROM dbSpecificData WHERE spLookup = 'CM...
You'd like to call a stored proc on MS SQL that has a parameter type of TIMESTAMP within T-SQL, not ADO.NET using a VARCHAR value (e.g. '0x0000000002C490C8'). What do you do? UPDATE: This is where you have a "Timestamp" value coming at you but exists only as VARCHAR. (Think OUTPUT variable on another stored proc, but it's fixed alread...
Is there a succinct way to retrieve a random record from a sql server table? I would like to randomize my unit test data, so am looking for a simple way to select a random id from a table. In English, the select would be "Select one id from the table where the id is a random number between the lowest id in the table and the highest i...
I'm using SqlServer for the first time, and in every single one of our create procedure scripts there is a block of code like below to remove the procedure if it already exists: IF EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = 'SomeProcedureName' AND routine_type = 'PROCEDURE' B...