I've just migrated from SQL2000 to SQL2008 and I have started getting an execute permission issue on a stored proc which uses sp_OACreate. The rest of the system works fine with the db login which has been setup and added to the database. I've tried: USE master GO GRANT EXEC ON sp_OACreate TO [dbuser] GO But this fails with the foll...
I asked this question on SO. However, I wish to extend it further. I would like to find the max value of the 'Reading' column only where the 'state' is of value 'XX' for example. So if I join the two tables, how do I get the row with max(Reading) value from the result set. Eg. SELECT s.*, g1.* FROM Schools AS s JOIN Grades AS g1 ON g1....
There is a stored procedure: CREATE PROCEDURE [dbo].[TestProc] AS BEGIN SET NOCOUNT ON; create table #thistable (rid char(32)) insert into #thistable(rid) select A0RID from tblCdbA0 with (nolock) END When the procedure is executed alone it takes 400-500 ms, but when 10 threads are executing the same procedur...
I am trying to implement versioning of data I have two tables Client and Address. I have to display in the UI, the various updates in the order in which they were made but with the correct client version so, Client Table Address Table ---------- ---------- Client ...
This is regarding SQL server 2008 management studio.. I connect to different environment DB and every time I launch the Sql management console, I have to sign up every time to get those connections back in object explorer. Is there a way I could persist the connection so I don't have to login every time to different environments? ...
I'm trying to figure out if this is relatively well-performing T-SQL (this is SQL Server 2008). I need to create a stored procedure that updates a table. The proc accepts as many parameters as there are columns in the table, and with the exception of the PK column, they all default to NULL. The body of the procedure looks like this: CRE...
I have a production ASP.Net MVC application that has been using the membership service. It has worked fine, up until today. Nothing has changed on the server, but I now have this error: System.Configuration.Provider.ProviderException: The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema ver...
I have two databases, on two separate SQL Servers (trying to consolidate both). This is my setup, and I'm trying to import from Server1:Orders table to Server2:Orders table. Server1 Database1 Orders(ID, CustomerName, DateOrdered) Server2 Database2 Customers(ID, Name) Orders(ID, CustomerID, DateOrdered) ...
I have TABLE EMPLOYEE - ID,DATE,IsPresent I want to calculate longest streak for a employee presence.The Present bit will be false for days he didnt come..So I want to calculate the longest number of days he came to office for consecutive dates..I have the Date column field is unique...So I tried this way - Select Id,Count(*) from...
We use SQL server 2008 as our RDBMS and we have a database that has a different user rather than dbo as its owner. The problem is in one machine a stored procedure can not run unless its owner is mentioned. If we connect to our database using this user and try to execute the following : exec ourSP we get a "could not find ourSP" err...
product table pid modelnumber 1 a 2 b 3 c ProductTransation pid name description... 1 ball ball 2 bat cricket bat i create fullText for Modelnumber in product table. Same for name & Description in productTransaction table. Now i want to join this table if i search through modelnumber or name result sh...
We're in the middle of migrating from a windows & SQL 2000 box to a Virtualised Win & SQL 2k8 box The VMWare box is on a different site, with better hardware, connectivity etc... The old(current) physical machine is still in constant use - I've taken a backup of the DB on this machine, which is 21GB Transfering this to our virtual mach...
I have a table which has a bunch of columns but the two relevant ones are: Due_Amount MONEY Bounced_Due_Amount MONEY I have a SQL query like the following SELECT * FROM table WHERE (Due_Amount > 0 OR Bounced_Due_Amount > 0) Would the best index to put on this table for SQL Server 2008 be an index which includes both columns in th...
So I'm not necessarily saying this is even a good idea if it were possible, since the schema of the view would be extremely volatile, but is there any way to represent a has-many relationship in a single view? For example, let's say I have a customer that can have any number of addresses in the database. Is there any way to list out ea...
Is there a limit to the maximum number of concurrent users connected to SQL Server 2008? I am guessing computer resources determine that limit (if it exists), but I am also wondering if there is a theoretical limit. I have tried googling the answer, but all the information I have found is based upon earlier versions of SQL Server. If the...
What is the basic difference between SQL Server stored procedures (sp_) and extended stored procedures (xp_)? Why there are extended procedures anyway? ...
I have an sproc in SQL Server 2008. It basically builds a string, and then runs the query using EXEC(): SELECT * FROM [dbo].[StaffRequestExtInfo] WITH(nolock,readuncommitted) WHERE [NoteDt] < @EndDt AND [NoteTypeCode] = @RequestTypeO AND ([FNoteDt] >= @StartDt AND [FNoteDt] <= @EndDt) AND [FStaffID] = @StaffID AND [FNoteTypeCode]<>...
I am running a .NET 4.0 WCF service that attempts to access a SQL Server 2008 R2 database. System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Login failed for user 'IIS APPPOOL\ASP.NET v4.0'. In previous versions of SQL Server, I would make the [computerName]\IUSR_[...
Hello I was here trying to figure out why my Reports manager is empty, there's no tabs at all. I installed SQL Server 2008 complete, but didn't not configure Reporting Services. When installing SQL Server 2008, this Windows 7 version didn't have yet IIS installed, I installed it later. I don't see where is this localhost/Reports phy...
Say I have a table called Employee (has ID, NAME, ADDRESS, and PHONE columns). (Not my real problem, but simplified to make the question easier.) If I call a sproc called UpdateEmployee and I pass in a @Name, @Address, @Phone and @ID. Can merge be used to easily check to see if the ID exists? If it does to update the name, address an...