I have written this T-SQL script to roll up duplicate rows in a database that are created by a reconstruction process. To do this, it performs the following: Gets and stores the minimum target table primary key (ColTargetPK) per set of duplicate entries in a table variable (@minColTargetPKTable); determining duplicates by matching on t...
Hello everyone, I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. I am using the following code to return a part of data for a query to implement paging (i.e. page up/down to show a part of result at each page, like Google search result paging) on my web application (I use pageCount as number of results showed on ...
Hi, we have a ASP.NET site with a SQL2008 DB. I'm trying to implement transactions in our current system. We have a method that is inserting a new row to a table (Table_A), and it works fine with transaction. If an exception is throwed, it will do the rollback. My problem is, that another user can't do anything that invole with Table...
I have an application which uses the Microsoft.SqlServer.Rmo DLL build against SQL Server 2005. In a production environment only SQL Server 2008 is installed, which has a different version of the Microsoft.SqlServer.Rmo DLL. The program fails in trying to load the 2005 version DLL. Assuming that the 2008 version DLL is backwards compati...
i was just working with cursor in SQL 2008 but i face some problems. the first is that with the line i declared the cursor every time i execute the query it gives me an error. which says the cursor with such a name already exists. i want to offer me a way that if the cursor exists do not create it and if it is not defined yet sql creates...
I need to create a test database out of a huge database where the most data is contained as filestream data. I need to test not filestream related data, so what I'd like to do is to remove the varbinary(max) info. These are the fields I have in my FILE_REPOSITORY_TABLE table: [ID_FILE] [int] NOT NULL, [FILE_DATA] [varbinary](max) FILE...
After asking this question it is clear to me that I need to be able to perform the garbage collection in the fastest possible time. How is it possible to tell SQL Server filestream's garbage collector to delete all the files with high priority? I tried with the CHECKPOINT statement, even by setting a duration (CHECKPOINT 100), but noth...
Hi , I have a job say somthing like X. If i want to give permissions to execute the X job to a user . what role I have to assign to the user. But the user should not be able to edit or delete or modify the job and this job is in share environment. no other should not prevlge to run the job X. ...
How do I select all rows for a table, their isn't part of any transaction that hasn't committed yet? Example: Let's say, Table T has 10 rows. User A is doing a transaction with some queries: INSERT INTO T (...) SELECT ... FROM T // doing other queries Now, here comes the tricky part: What if User B, in the time between User A in...
I have installed Team Foundation Server 2010 (basic configuration) on a Windows Server 2003 without any problems. But when I go to administer the Team Foundation Server Administration Console, I get the error when in the press "Administer Security" or "Group Membership" Server was Unable to process request. ---> Team Foundation services...
I have a stored procedure (see below) which inserts data into a physical table and then joins information with sys.databases. I was thinking that would it be better to not have a physical table for data insertion? Would it be better to fetch these results into a table variable within this procedure? If so, how to do that? CREATE PROCEDU...
Before inserting filestream data I'd like to check the following NTFS settings: 1) 8.3 naming status (this is disabled by using fsutil behavior set disable8dot3 1) 2) last access status (this is disabled by using fsutil behavior set disablelastaccess 1) 3) cluster size (this is set with format F: /FS:NTFS /V:MyFILESTREAMContainer /A:...
I have the following user-defined function: create function [dbo].[FullNameLastFirst] ( @IsPerson bit, @LastName nvarchar(100), @FirstName nvarchar(100) ) returns nvarchar(201) as begin declare @Result nvarchar(201) set @Result = (case when @IsPerson = 0 then @LastName else case when @FirstName = '' then @LastName el...
Hello, in our company's product, we attach a database to SQL Server, and we need to add credentials to some accounts to the catalog. Currently we're using SMO to do this, but this gives us problems (x86 vs x64, SQL Server 2005 vs 2008, etc) Is there's anyway of adding a login (to the server) and a user (to the catalog) using SQL? This...
Hi, I need to be able to return a list of all children given a parent Id at all levels using SQL. The table looks something like this: ID ParentId Name --------------------------------------- 1 null Root 2 1 Child of Root 3 2 Child of Child of Root Give an Id of '1', how would I return the entire...
In SQL Server there is two schemas for metadata: INFORMATION_SCHEMA SYS I have heard that INFORMATION_SCHEMA tables are based on ANSI standard. When developing e.g. stored procedures, should it be wise to use INFORMATION_SCHEMA tables over sys tables? ...
Hi I'm migrating a few databases from sql 2000 to sql 2008. while running upgrade advisor I got a message that says that there are objects with the Dump/Load sintax. that is not on use anymore. So I found the following text in a proc Dump DataBase @name to @path With Init Further investigating I discovered that this was a backup and...
Hello What is the best way to sort records on Guid. Below never gives right results. With UIDs As (-- Select ID = 1, UID = NEWID() Union Select ID = 2, UID = NEWID() Union Select ID = 3, UID = NEWID() Union Select ID = 4, UID = NEWID() Union Select ID = 5, UID = NEW...
I joined a windows host who runs SQL Server 2008 Express (Negative Surprise)! I think this is wrong since a host should expect some medium companies join them so they should put at least standard version. I don't know much about dba, but this is my suggestion. Am i right? OK! Here is my question, the host has SQL Reporting Service as an...
How do I cause all my tests to do a rollback on the SQL Server 2008 database from my Visual Studio 2010 test harness? I'm using TestDriven.NET to run the tests. ...