I want to lock a table when a store procedure is executing. Would this be a good way? CREATE PROCEDUE sp_test @tableName nvarchar(128) AS DECLARE @sql nvarchar(MAX) = 'SELECT * FROM ' + @tableName + 'WITH TABLOCK' EXEC @sql -- DO my operations -- How Do I release the lock? or does it get release when the execution is done Or is th...
I'm looking for an app that will compare the layout of 2 SQL tables. When developing in a DEV environment, I need a tool that will make the production tables exactly like the DEV tables (the layout). ...
So I'm trying to understand how SQL Server makes use of indexes on tables behind views. Here's the scenario: Table A has a composite clustered index on fields 1 & 2 and a nonclustered index on fields 3 & 4. View A is written against Table A to filter out additional fields, but fields 1-4 are part of the view. So we write a query th...
Hi there I have this following data: 0297144600-4799 0297485500-5599 The 0297485500-5599 based on observation always on position 31 char from the left which this is an easy approach. But I would like to do is to anticipate just in case the data is like this below which means the position is no longer valid: 029714460...
Hi there I've to manage the data into this and it has blank space in between: 0297144600-4799 0297485500-5599 0297144600-0297144799 0297485500-5599 0297144600-0297144799 0297485500-0297485599 I want to normalise the first/2nd row like the last row which means 0297144600-4799 >> 0297144600-0297144799 The challenge is that it can ...
I have this table with pages, these pages have parents that are also pages in the same table. For this examples sake the table looks like: table: Pages PageId :Key PageParent :Foreign Key PageName Now my question is what would the SQL look like when creating a menustructure like: PageId PageParent PageName 1 NULL ...
Ok - long story short. Have an e-comm site hosted on Windows VPS using SQL Server 2005, site been up for years and works just fine. I am now trying to set up a new site hosted on another Windows VPS Server that will share the same database. Both sites are using classic asp and I have put up a duplicate page to each server that has a s...
Hello All, I have a table with unique values within it and once a stored procedure is called, I use the following code within a sub-query to get a random value from the table: SELECT TOP 1 UniqueID FROM UniqueValues WHERE InitiatingID is NULL ORDER BY NewID() ASC I have however noticed that I am managing now and then (and I'm guessin...
ALTER PROCEDURE [Lending].[uspHMDALarIncomeGet] (@ApplicationId int) AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Total Income Data DECLARE @ApplicantId int = (SELECT AT.ApplicantId FROM Lending.Applicant AT WHERE AT.ApplicationId = @ApplicationId) SELECT I.Amount FROM Lending....
Background I'm currently working on a project that heavily utilizes SQL stored procedures. Some tables have as many as few hundred thousand procedure executions every day. Because the project serves critical business function for our client logging and fast error detection is crucial. Question How to implement logging and error report...
I have a blacklist of people that should never be contacted. When I want to see if a person is in this list, I do the following: -- Query 1 SELECT * FROM bldb.dbo.blacklist l WHERE l.matchcode = dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert') The query runs very fast, since there is an index on matchcode column, and ...
I need to write a procedure that will allow me to select x amount of rows and at the same time update those rows so the calling application will know those records are locked and in use. I have a column in the table named "locked". The next time the procedure is called it will only pull the next x amount of records that do not have the "...
I got 3 server. development (my local development machine) Test purpose (Beta site). Production I got Instances in both SQL server 2005 and 2008. So, When I want updated data, i take the latest backup from our production server and restore it on the Test or development Server. I got a script that make the changes (restore the file ...
I have a table as follows, A A B B1 A A1 B B A A2 B B3 A A3 B B2 My result set should be, A A A A1 A A2 A A3 B B B B1 B B2 B B3 Note: A, A1 are all GUIDs. I have tried quite a few tricks. Please help me solve this problem. ...
Hello again, StackOverflow - I'm still trying to deploy this site, but with every problem I solve, another arises. Anyways - I've set up the database at my hosting to allow remote connections, and it is running Sql Server 2005. On my development machine, I am working with Sql Server 2008. I've installed the asp.net schema on my hosted ...
hi, I want to update a Table with auto incrementing dates in date column in Sql server 2005. ...
I have a SQL Server table with 3000 rows in it. When I retrieve those rows it is taking time using a Select Statement. What is the best solution to retrieve them? ...
Dear Friends i m created a Database Backup job in SQL server 2005 under maintainace Plan, but when i run below query SQL sever return 0 Rows Why? select * from sysdbmaintplan_databases I want to retrive Using SQL Query Created Job for Database Backup with Database name & Path. ...
Hello again, StackOverflow - I'm still trying to deploy this site, but with every problem I solve, another arises. Anyways - I've set up the database at my hosting to allow remote connections, and it is running Sql Server 2005. On my development machine, I am working with Sql Server 2008. I've installed the asp.net schema on my hosted ...
Hi, I am using SQL Sever 2005, VC 6 ++ and Crsystal Report API to print a report in a child window. If the records vary Only by Milliseconds, Crystal Report is showing only the 1st record. Otherwise, it is perfectly fine. I am picking the timestamp from SQL Server DateTime Field. In Formula inside Crystal Report, i am only giving the...