If you set up a table's column to be a computed column whose Formula calls a Function, it becomes a pain to change that underlying Function. With every change, you have to find every single column whose Formula that references the Function, remove the reference, save the Table, alter the Function, add everything back, and save again. Ev...
In MS-SQL 2005 when i exec select * from sys.sysusers in master database , guest user hasdbaccess = 1 . how to set hasdbaccess = 0 ? ...
I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide.... What are your strategies / best practices when it comes to dealing with a reasonably sized SQL Server database (anything larger than Northwind or AdventureWorks) - do you use multiple filegrou...
Is it possible to restore a backup of a SQL Server 2008 database onto an instance of SQL Server 2005? I need to work on an sample application for which database backup is in sql server 2008. But I'll not be able to install 2008. So is it possible to restore that back up in 2005? ...
Hello, I've implemented the following trigger: CREATE TRIGGER [OnContactDeleted] ON [TABLE].[Contact] INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON /*Store contact ID to variable*/ Update [TABLE].[Account] Set [PrimaryContactID] = null where [TABLE].[Account].[PrimaryContactID] = (Select ContactID fr...
We installed SP1 on our Team Foundation Server 2008 server. Everything seems fine after the install, except there is a red X on the Reports folder in the Team Explorer in Visual Studio. If we attempt to access the Reporting Services web site, we get a message that says that the "report server is unavailable". There were no errors during ...
I have a simple question. I need to return results as simple text, either as a string or similar. ie I want the same behaviour such as Results to Text (Cntrl + T) when you run a Query from SQL Server Management Studio. How can this be done programatically against calling a Stored Proc from C#? ie similar to ExecuteReader(); or ...
I need to add a constraint to an existing SQL server table but only if it does not already exist. I am creating the constraint using the following SQL. ALTER TABLE [Foo] ADD CONSTRAINT [FK_Foo_Bar] FOREIGN KEY ([BarId]) REFERENCES [Bar] ([BarId]) ON UPDATE CASCADE ON DELETE CASCADE I'm hoping I can add some SQL to the begining of the...
I have a query that runs each night on a table with a bunch of records (200,000+). This application simply iterates over the results (using a DbDataReader in a C# app if that's relevant) and processes each one. The processing is done outside of the database altogether. During the time that the application is iterating over the results...
Hi, I am trying to do some complex pivot query... and I need to do a select in the IN Clause... Example of the Pivot Looking at the Example in the above link, you can see they use the employee ids explicitly in their pivot. I would like to be able to do select instead of specifying the employee ids. The only way I can think of doing...
The table in question is part of a database that a vendor's software uses on our network. The table contains metadata about files. The schema of the table is as follows Metadata ResultID (PK, int, not null) MappedFieldname (char(50), not null) Fieldname (PK, char(50), not null) Fieldvalue (text, null) There is a clustered index ...
I am trying to do a many different queries on a result set which has a very large creation time. To get performance gains I wish to use a temp table and just do many queries on this temp table. Seems pretty standard. Yet I am struggling to share this temp table in dynamic sql. As I understand it, each SqlCommand object executes in its o...
I've got data in a SQL Server 2005 table that I need to insert into an application using a SOAP web service that takes in an XML fragment. I'm looking at different options for this and I would like to know if it is possible to get SQL Server to return me XML that conforms to a particular Schema. If it is possible, how do I do it? ...
Is there a way to check statistics for table access, by update, delete or select? Im trying to find obsolete unused tables for a database cleanup. Thanks ...
I'm using SQL Server 2005. Our application almost never deletes without it being a logical delete and therefore we have no need for cascading deletes. In fact, its quite a comfort knowing that the foreign key contraints give us some protection against an accidental delete statement. However, very occasionally I need to delete a top le...
How do you create a MembershipProvider for use in an ASP.NET/MVC website? Doesn't it generate the tables it needs for you in your database? ...
Is there a special way to declare a DateCreated column in a MS Sql Server table so that it will automatically fill it with the appropriate time-stamp when created? Or.. do I have to provide the datetime to it when I do the query, manually? ...
Can anyone help me with this problem please? I have 2 databases on the same server, both owned by sa. A Windows login has permission to execute a stored proc in database A that selects data from a table database B. The stored proc and table are also both owned by dbo. The Windows login is a member of a Windows domain group that is a mem...
Hi, I am actually having 100's of SP in my database. I have to find a set of 10 SP's in that which have a particular comment inside them. Is there any search query for this. ...
Hi, I am adding "Tagging" functionality in my web app. My applications table structures are as following; Tag: (TagId INT IDENTITY, TagName VARCHAR(60)) TaggedRecords: (TaggedId INT IDENTITY, TagId, TaggedRecordId) Now, I want when anyone adds a tag to any record then following action should be performed using a single sql query...