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...
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...
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...
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...
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...
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?
...
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...
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';...
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...
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...
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...
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...
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 ...
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...
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...
Hello,
Does anyone know the definitions for all the columns returned by sys.dm_exec_query_stats?
...
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
...
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?
...
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...
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...