tsql

insert into sql table column as GUID

I have tried with ado.net create table columnName with unique name. as uniquename I use new Guid() Guid sysColumnName = new Guid(); sysColumnName = Guid.NewGuid(); string stAddColumn = "ALTER TABLE " + tableName + " ADD " + sysColumnName.ToString() + " " + convertedColumnType + " NULL"; SqlCommand cmdAddColumn = new Sq...

Why use a READ UNCOMMITTED isolation level?

In plan English what are the disadvanges and advantages of using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in query for .NET applications and reporting services application? ...

SQL Server: Granting db_datawriter on all databases

I want to manage permissions and access to a SQL Server database server on a development machine. I've created a local windows group (called DBReaderGroup) and assigned various people to it. The group was created as a SQL Server login successfully: create login [MYMACHINE\DBReaderGroup] from windows My goal is to grant this group read/...

TSQL 'Invalid column name' error on value of sproc parameter

here's my code: DECLARE @SQL varchar(600) SET @SQL = 'SELECT CategoryID, SubCategoryID, ReportedNumber FROM tblStatistics WHERE UnitCode = ' + @unitCode + ' AND FiscYear = ' + @currYEAR EXEC (@SQL) When i run this sproc with unitCode = 'COB' and currYEAR = '10', i get the following error: Invalid column name 'COB'. Do...

SQL Server copying table information between databases

Hi, I have a script that I am using to copy data from a table in one database to a table in another database on the same SQL Server instance. The script works great when I am connected to the SQL Server instance as myself as I have dbo access to both databases. The problem is that this won't be the case on the client's SQL Server. They...

We have multiple app servers running against a single database. How do I ensure that each row in a queue table only get processed once?

We have about 7 app servers running .NET windows services that ping a single sql server 2005 queue table and fetch a fixed amount of records to process at fixed intervals. The amount of records to process and the amount of time between fetches are both configurable and are initially set to 100 and 30 seconds initially. Currently, my qu...

Remove and Replace multiple chars ( spaces, hyphen, brackets, period) from string in sql

+39 235 6595750 19874624611 +44 (0)181 446 5697 +431 6078115-2730 +1 617 358 5128 +48.40.23755432 +44 1691 872 410 07825 893217 0138 988 1649 (415) 706 2001 00 44 (0) 20 7660 4650 (765) 959-1504 07731 508 486 please reply by email dont have one +447769146971 Please see the above given phone numbers. I need to replace all spaces, hyphe...

If a table has two xml columns, will inserting records be a lot slower?

Is it a bad thing to have two xml columns in one table? + How much slower are these xml columns in terms of updating/inserting/reading data? In profiler this kind of insert normally takes 0 ms, but sometimes it goes up to 160ms: declare @p8 xml set @p8=convert(xml,N'<interactions><interaction correct="false" score="0" id="0" gapid=...

SQL Server Table definition from stored procedure result set

Hi, Is anyone aware of a way to create a table definition from a stored procedure result set? I have a stored procedure which produces a result set with 30+ columns, I'd like to get this into a table without having to manually create all the table columns. Is there a built in procedure that will dump out the column names and types..? ...

Split query result by half in TSQL (obtain 2 resultsets/tables)

I have a query that returns a large number of heavy rows. When I transform this rows in a list of CustomObject I have a big memory peak, and this transformation is made by a custom dotnet framework that I can't modify. I need to retrieve a less number of rows to do "the transform" in two passes and then avoid the memory peak. How can ...

Why is my left join not returning nulls?

In sql server 2008, I have the following query: select c.title as categorytitle, s.title as subcategorytitle, i.title as itemtitle from categories c join subcategories s on c.categoryid = s.categoryid left join itemcategories ic on s.subcategoryid = ic.subcategoryid left join items i on ic.itemid = i.itemid and i.site...

How efficient is a details table?

At my job, we have pseudo-standard of creating one table to hold the "standard" information for an entity, and a second table, named like 'TableNameDetails', which holds optional data elements. On average, for every row in the main table will have about 8-10 detail rows in it. My question is: What kind of performance impacts does this ...

t-sql string concatenation

i have a table that has following column Type -------- type 1 type 2 type 3 How can i convert the above to a string like ('type 1', 'type 2', 'type 3') I want to use the output in my t-sql query with IN clause. Something like select * from TableA where SomeColumn IN ('Type 1','Type 2', Type 3') I used to following to come up with ou...

how to rethrow same exception in sql server

I want to rethrow same exception in sql server that has been occured in my try block. I am able to throw same message but i want to throw same error. BEGIN TRANSACTION BEGIN TRY INSERT INTO Tags.tblDomain (DomainName, SubDomainId, DomainCode, Description) VALUES(@DomainName, @SubDomainId, @Domai...

SQl rows to columns conversion

Hi, I have a table ClassAttendance and I'm using MSSQL 2005 studentID attendanceDate status ------------------------------------- *1004 2010-03-17 0 1005 2010-03-17 1 1006 2010-03-17 0 1007 2010-03-17 0 *1004 2010-03-19 0 1005 2010-03-19 ...

How to Deal with SET ANSI_NULLS ON or OFF ?

I want to call this procedure that sends one value that can be NULL or any int value. SELECT DomainName, DomainCode FROM Tags.tblDomain WHERE SubDomainId =@SubDomainId I simply want to use this single query rather than what i m doing right now in below given code. I searched for this how could i do this then i got this Link. Accordi...

How to Delete all data from a table which contain self referencing foreign key

I have a table which has employee relationship defined within itself. i.e. EmpID Name SeniorId ----------------------- 1 A NULL 2 B 1 3 C 1 4 D 3 and so on... Where Senior ID is a foreign key whose primary key table is same with refrence column EmpId I want to clear all rows f...

Keeping DB Table sorted using multi-field formula (Microsoft SQL Server)

I have a JOB table, with two interesting columns: Creation Date Importance (high - 3, medium 2, low - 1). A JOB record's priority calculated like this: Priority = Importance * (time passed since creation) The problem is, every time I would like to pick 200 jobs with highest priority, and I don't want to resort the table. Is t...

SQL Server identity counterpart problem

Hi there, I'm using SQL Server and I want to use identity constraint in it I know how to use it in following manner create table mytable ( c1 int primary key identity(1,1); ) the above code works fine but what if i want the identity column to have values as EMP001, EMP002,... instead of 1,2.... Thanks in advance, Guru ...

Create view or SP, only if the DB contains a pattern

I am working on a script, that needs to be run in many different SQL servers. Some of them, shared the same structure, in other words, they are identical, but the filegroups and the DB names are different. This is because is one per client. Anyway, I would like when running a script, If I chose the wrong DB, it should not be executed. I...