tsql

Swapping two DB rows without violating constraints

I have a table regionkey: areaid -- primary key, int region -- char(4) locale -- char(4) The entire rest of the database is foreign-keyed to areaid. In this table there is an index on (region, locale) with a unique constraint. The problem is that I have two records: 101 MICH DETR 102 ILLI CHIC And I need to swap the (r...

TSQL: How to get a list of groups that a user belongs to in Active Diretory

I have two queries that retrieve all groups and all users in a domain, Mydomain --; Get all groups in domain MyDomain select * from OpenQuery(ADSI, ' SELECT samaccountname,mail,sn,name, cn, objectCategory FROM ''LDAP://Mydomain/CN=users,DC=Mydomain,DC=com'' WHERE objectCategory=''group'' ORDER BY cn ') --; Get all users in dom...

T-SQL Output Clause: How to access the old Identity ID

Hi There, I have a T-SQL statement that basically does an insert and OUTPUTs some of the inserted values to a table variable for later processing. Is there a way for me to store the old Identity ID of the selected records into my table variable. If I use the code below, I get "The multi-part identifier "a.ID" could not be bound." error...

Search if a string word exists between two different tables in a comma-delimited field

I have two tables: EmployeeTypeA table Name varchar(2000) field contains - 'john,sam,doug' EmployeeTypeB table Name varchar(2000) field contains - 'eric,sam,allen,stephanie' What is the most efficient way to return a true or false when a name is found in both lists using MS SQL? This needs to be done within a stored procedure so I ca...

Change case of a column name

I have a table in my warehouse that has columns in all uppercase. It scripts out like this: CREATE TABLE [dbo].[Outlet]( [OUTLET_KEY] [varchar](10) NOT NULL, [OUTLET] [varchar](8) NULL, [CITY_CODE] [varchar](4) NULL, [OUTLET_NAME] [varchar](25) NULL, [COUNTRY_CODE] [varchar](3) NULL, [EXTENDED_CATEGORY_CODE] [var...

How do I define a work week in SQL Server 2000?

I need to split a report by work week, and our work week is Saturday through Friday. How would I convert an ISO week from DATEPART(WW, ) into a work week? ...

COALESCE with NULL

I found this snippet of SQL in a view and I am rather puzzled by it's purpose (actual SQL shortened for brevity): SELECT COALESCE(b.Foo, NULL) AS Foo FROM a LEFT JOIN b ON b.aId=a.Id I cannot think of a single reason of the purpose of coalescing with null instead of just doing this: SELECT b.Foo AS Foo FROM a LEFT JOIN b ON...

Executing multiple dynamic statements together over linked server

I need to execute three dynamic SQL statements synchronously on a linked server (SQL Server 2005) like this: declare @statement nvarchar(max); set @statement = 'exec ' + @server_name + '.' + @database_name + '.dbo.Foo;exec ' + @server_name + '.' + @database_name + '.dbo.Bar;exec ' + @server_name + '.' + @database_name + '.dbo.BigTime';...

SQL Convert Rows To Columns

I know this has been asked a few times before, but I can't find any solution that fits my example. I currently have a table of user permissions to use certain pages. The table would look like this: UserID pagename pageid ----------------------------------- 1 home 1 1 contacts 3 3 h...

T-Sql, How to get these results ?

This query SELECT PA.refPatient_id ,MAX(PA.datee) AS datee ,PR.temporary,PA.statue FROM PatientClinicActs AS PA ,PatientStatueReasons AS PR WHERE PA.refClinic_id = 25 AND PA.refreason_id = PR.reason_id GROUP BY PA.refPatient_id,PA.statue,PR.temporary returns these results: refPati...

How to calculate difference in hours (decimal) between two dates in SQL Server?

I have to calculate the difference in hours (decimal type) between two dates in SQL Server 2008. I couldn't find any useful technique to convert datetime to decimal with 'CONVERT' on MSDN. Can anybody help me with that? UPDATE: To be clear, I need the fractional part as well (thus decimal type). So from 9:00 to 10:30 it should return m...

2147217833 String or binary data would be truncated

Hi Have an error occuring when I try to update a record via stored procedure. The error I get is 2147217833 String or binary data would be truncated. I've done a length on each of the fields that I'm inserted and they should be fitting comfortably in to the the database fields - i.e. the length isn't greater that the column specificatio...

Combining multiple TSQL change scripts with skipping

I have a bunch of TSQL change scripts, all named appropriately in sequence. I want to combine these into one big script with a few twists. I include a version number function in the script that I update for each script so that once change 1 is run it returns 1, once 2 is run it returns 2 and so on. This function remains in the database ...

T-SQL Triple Self Join

Hey guys, ive have a problem with the following table: CREATE TABLE [dbo].[CA]( [Id] [int] IDENTITY(1,1) NOT NULL, [Type] [char](1), [Percent] [numeric](19, 5) NULL, [Crop] [int] NULL, [Currency] [int] NULL, [Ammount] [decimal](19, 5) [ProductionSite] [int] NOT NULL ) This table describes how a ProductionSite pays its suppliers. It m...

Problem adding foreign key in simple setup

I have two tables Users Users_Role I decided to try to add a foreign key as to my understanding it will let me cascade the delete procedure when removing a user from Users (as well as enforce integrity). So via Management Studio I right clicked my Users_Role table, Design, then went into Relationships. I added a new relationship betw...

sys.dm_exec_query_stats column descriptions?

Hello, Does anyone know the definitions for all the columns returned by sys.dm_exec_query_stats? ...

Interesting SQL problem

I have a SQL problem I am trying to digest. I am using SQL Server 2005. In a table I have data as such: ID Type 1 A 2 A 3 A 3 B 4 B I need to find all of the IDs that have a Type of both A and B. This is not a homework problem. It's a real work issue I'm trying to resolve. Thanks ...

How do you export to a file in a Stored Proc?

I tried BCP, but doesn't work. Sql keeps giving me some Help text.. Anyway, it doesn't matter, as I don't want to paste the contents of the stored proc AGAIN into a string so this can export. My Question: I have an existing stored proc. I would like it to automatically kick its result out into a text file. Any clues? ...

Why do many SQL Server severity 10 errors actually seem severe?

BOL states that SQL Server level 10 messages are "Informational messages that return status information or report errors that are not severe." However these errors seem severe and not informational: 2540 - The system cannot self repair this error. 2745 - Process ID %d has raised user error %d, severity %d. SQL Server is terminating th...

Update and Insert Stored Procedure

I want to create a stored procedure that performs insert or update operation on a column if that column does not contains a value that already exists in database it should allow insert when COUNT(field) = 0 or update when COUNT(field)=0 or 1 And I should know that either of these operation is performed or not. Please solve my problem us...