This is a continuation from When I update/insert a single row should it lock the entire table? Here is my problem. I have a table that holds locks so that other records in the system don’t have to take locks out on common resources, but can still queue the tasks so that they get executed one at a time. When I access a record in this l...
Can we return null value from stored procedure. i dont want to use collase or isnull. I want to capture NULL at the frontend. Is it possible ? Edit: I am using Sql Server 2005 eg. where i want to use CREATE PROCEDURE [Authentication].[spOnlineTest_CheckLogin] @UserName NVARCHAR(50) AS BEGIN TRY BEGIN TRAN ...
I have a table that has a one to many relationship with itself. Each record can have n number of children from that same table. For example create table folder ID: Number 20 PK PARENT_ID: Number 20 FK references folder.ID SIZE: NUMBER 20 ... Given an ID, I want to select the SUM(SIZE) of all folder records recursively. The target d...
Is this possible? Using SQL Server 2005....... SELECT * FROM Data0304 UNION SELECT * FROM Data0506 UNION SELECT * FROM Data0708 ...
I removed some rows from a very large table. Then I ran a query that usually runs within a few seconds and found it to be running very slowly after deleting the rows. I re-built my index and ran my query and found it to be fast again. Could deleting those rows caused the index to be fragmented? ...
Hi all. I came across a weird situation when trying to count the number of rows that DO NOT have varchar values specified by a select statement. Ok, that sounds confusing even to me, so let me give you an example: Let's say I have a field "MyField" in "SomeTable" and I want to count in how many rows MyField values do not belong to a dom...
I have two tables that are indirectly related by another table TableA - ID, SomeFieldA TableB - ID, SomeFieldB TableAB - IDA, IDB, SomeFieldAB I have to generate data from the ground up. So I've put some data in TableA, and I've put some data in TableB. The problem is, I need to insert data into TableAB now, and I don't...
This is going to sound like a crazy request. The databases that I report from do not have any foreign keys, and every single primary key is an identity_column. This makes using tools such as TOAD difficult because the Intellisense works by reading the PK and FK relationships. Anyone have a script to remove the primary keys from every t...
Today at work we got into a discussion about which is the best way to do a query like this : For instance lets assume a users table : tblUsers ID = Autoint Name = String and a login table : tblLogin ID = AUtoint UserID = Int IP = String Browser = String OS = String timestamp = DateTime What would...
I need to find if /3GB switch and /PAE is enabled on a server. Also, I want to know the size of page file and physical RAM on the server. I can check them manually but how can I check them using TSQL on both SQL 2000 and SQL 2005? ...
I've created a view on 3 tables in my database, they are as follows: Activity ActivityRole aspnet_UsersInRoles I am trying to grab the combination of what's in Activity and ActivityRole, if the UserId I pass in is a member of the role specified as required in the Activity Role. I'm creating a view, because I want to generate an obj...
I have a stored procedure that will give the latest records i.e., order by added date this is my procedure.... select distinct top 5 videos.videoid,videos.videotitle,videos.videoname, convert(varchar,videos.posteddate,106) as posteddate,videos.approvedstatus, videos.videoimage,(ISNULL(videos.views,0.0)) as [views],videos.privac...
Hi, I need to run the replication of one table in SQL Server 2005. Replication should be one way. From 1 master server to many children servers. I thougt about snapshot replication, but I don't want to schedule it only for example every hour/minute ect. (I know how to do this.) but ALSO triggered it evry time new data appears in master...
yesterday i went for an interview to be a sql / .net developer. my experience with sql is limited to basic pl/sql with oracle. they drilled me "do you know ssrs, do you know tsql, etc" well i kept saying no because i havent worked with them. question: what do i have to learn in order to be able to work with microsoft sql? is it really ...
I have the following function: CREATE FUNCTION [dbo].[ListStockBySubCategory] ( @CategoryID varchar(10), @SubCategoryID varchar(10), @startRowIndex int, @maximumRows int ) RETURNS TABLE AS RETURN ( SELECT ISBN FROM ( SELECT ISBN, ROW_NUMBER() OVER(AddedDate DESC) AS RowNum FROM ( SELECT DISTINCT RTRIM(...
I am using SQL Server 2008's Full Text Search engine in my website. I have a search SP, which shows results sorted based on ranking. I break up the search string and pass it to the FTS query engine like so (search string is 'test search': ("*test*" ~ "*search*") OR ("*test*" OR "*search*"). If the results row has the row 'test search...
Starting in SQL 2005, VARCHAR(MAX) is no longer limited to 8000 bytes, it instead can go up to 2GB using "overflow" pages. But what if I want to limit this column to say, 10k bytes? It seems I get an error if I try to put anything in the size parameter above 8000. Which is odd because MAX is the same as asking for a 2GB limit. Seems ...
I'm trying to create my first data mart. I am creating my dimension table with a "Select Into" The code is as follows: select distinct fpc_number, fpc_desc into m2mdata01dw..ProdClass from m2mdata01..INPROD How can I set up a autonumber primary key in this situation? ...
I'm having a problem with TRY...CATCH blocks. Can someone explain why the following code will not execute my sp? DECLARE @Result int SET @Result = 0 BEGIN TRY SELECT * FROM TableNoExist END TRY BEGIN CATCH SET @Result = ERROR_NUMBER() END CATCH EXEC dbo.spSecurityEventAdd @pSecurityEventTypeID = 11, @pResult = @Result But this...
Here is my select statement, SELECT TOP 1 EmpId, RemainingAdvance FROM SalaryDetails WHERE EmpId IN (SELECT Emp_Id FROM Employee WHERE Desig_Id='27') ORDER BY CreatedDate DESC When i executed SELECT Emp_Id FROM Employee WHERE Desig_Id='27' the results were Emp_Id 16 17 But when i execute my...