How do I create a multi-column constraint in SQL Server?
I know this is very simple, but how do I do this in plain SQL? ...
sql-server
Is it possible to create a Unique ID in an SQL Server View that will remain the same each time the view is called?
I've got 10 tables that I'm joining together to create a view. I'm only selecting the ID from each table, but in the view, each ID can show more than once, however the combination of all ID's will always be unique. Is there a way to create another column in this view that will be a unique ID? I'd like to be able to store the unique ID a...
sql query to get daily payments for a month even if no payments on a given day
I am using SQL Server 2005 and trying to write a query where I want to retrieve payments for a given month. I currently have: select sum(p1.paymentamount) as subtotal, CONVERT(char(10), p1.paymentdate, 103) as paymentdate from tblpayment p1 where 1=1 and p1.paymentdate >= @fromdate and p1.paymentdate <= @todate group by...
Identify a specific sequence of records in a table
Assume a table with the fields TransactionId, ItemId, Code, EffectiveDate, and CreateDate. +---------------+--------+------+------------------+------------------+ | TransactionId | ItemId | Code | EffectiveDate | CreateDate | +---------------+--------+------+------------------+------------------+ | 1| 1| ...
TFS Reporting and SQL Server 2008 Developer
Hello I have a TFS 2010 beta 2 installation on a server 2008 r2, with a full instalation of sql server 2008 developer with SP1. I want to enable the reporting capabilities of TFS, but i can't get to connect TFS with analysis services. The app Tier - Reporting - Edit Dialog keeps me saying that the analysis server cannot be reached. I h...
OBJECT_ID of object in another database - how to find database ID or name/fully qualified object name?
Example: USE AnotherDB -- This works - same ID as from other DB SELECT OBJECT_ID('AnotherDB.ASchema.ATable') -- This works SELECT OBJECT_NAME(OBJECT_ID('AnotherDB.ASchema.ATable')) USE ThisDB -- This works - same ID as from other DB SELECT OBJECT_ID('AnotherDB.ASchema.ATable') -- Gives NULL SELECT OBJECT_NAME(OBJECT_ID('AnotherDB.ASche...
SQL Server Month Totals
SQL Server newbie The following query returns SRA by Student and month only if there is a record for a student in Discipline table. I need a query to return all students and month totals even if there is no record for student in Discipline table. Any direction appreciated SELECT TOP 100 PERCENT MONTH(dbo.Discipline.DisciplineDate) AS ...
SQL Server Developer/Standard/Enterprise for local development?
DO these versions mean anything to me as a developer? I understand they have limitations on connections/processors/etc but none of that matters to me for a local development instance. All of the ISOs on MSDN are the same size, does it make a difference which of these I choose to download? ...
Compare two DATETIME only by date not time in SQL Server 2008
I have two tables (bank statement and client requests) where column [date] is type of DATETIME2(0). I have to compare two records only by theirs Date parts (day+month+year), discarding Time parts (hours+minutes+seconds). How can I dot that? I tried CAST and CONVERT but it didn't helped. Update: Marc_s's answer works DATEADD(dd, 0, D...
Why does NULL = NULL evaluate to false in SQL server
In SQL server if you have nullParam=NULL in a where clause, it always evaluates to false. This is counterintuitive and has caused me many errors. I do understand the IS NULL and IS NOT NULL keywords are the correct way to do it. But why does SQL server behave this way? ...
Set data type (xml) for a field in data table, using Visual Studio
Hi I have datatype for a field recently converted to xml from varchar(MAX) in SqlServer DB. I want to set this type for the corresponing field in the data table, but it has no option for xml. Any suggestion ? Thanks using VS 08 and SQL SERVER 08 ...
how to check if the log-in exists in sql server ?
I am creating a new login in my SQL Server and I want to check if the login exists, if not, then I have to execute the following query to create a new login. Can anyone help me in checking whether the login exists or not using query? USE [master] GO CREATE LOGIN [Emsmgr1] WITH PASSWORD=N'welcome', DEFAULT_DATABASE=[master], CHECK_EXPI...
Is there a difference bt Integrated Security = True and Integrated Security = SSPI?
Is there a difference between Integrated Security = True and Integrated Security = SSPI when used in the connection string. I could sense some difference between them throug i went through some document which says there is no difference. ...
why this behaviour with int in SQL Server 2005
I have a requirement in which i need to see whether that key already exists. If it already exists update the data based on the key else just insert new data. The approach which i took is DECLARE @Tracking_Id INT SELECT @Tracking_Id = Tracking_Id FROM DOCUMENT_TRACKING WHERE Secondary_Document_Id = @Secondary_Docum...
Converting an SQL Server database to an SQL Server Compact database. And back...
Simple problem. I have an SQL Server database (MyData.mdf) and I want to convert it to a Compact database. (MyData.sdf) Just can't find the proper tool which can do this for me. And I would also be able to convert the compact database back to a regular SQL Server database... ...
SQL Syntax: Casting a datatype that is stored as data
Hi, I have a table that looks like this: id datatype name value 0 nvarchar(255) myName 'Paul' 1 int age '30' 2 float(53) Custom1 0.5 3 float(53) Custom2 1.3 4 float(53) Custom3 2.7 I am wondering if it is possible to do something like the following where I cast ...
Using Quest Geo Solutions GIQ60 library on a 64bit machine
We are using Quest Geo Solution's Grid InQuest DLL (GIQ60.DLL) within an SQL Server Integration Services (SSIS) package. We have managed to use this without problems locally (32bit) by using tlbimp to create a .NET wrapper (interop). However, when this moved to our integration server this does not work as the server is 64bit. The GIQ6...
Trim trailing space from table contents
I have a table in a SQL server 2000 database with a nvarchar(30) field "details". There are some 10,000 records in that with a trailing space. I need a query to trim the particular field content in all rows. How can I achieve this? Thanks. ...
Month wise display
Start Date: (mm/dd/yyyy) 05/09/2007 End Date: (mm/dd/yyyy) 08/20/2007 While giving the start date and end date using the above values, need to display the report like the following format. Notes: Need Single query Start Date End Date 05/09/2007 05/31/2007 06/01/2007 06/30/2007 07/01/2007 07/31/2007 08/01/2007 08/20/2007 Please help...
Problem with indexed view msql
hi I have problem with a counting column in my view. SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ColumnC, ModuloColAColB Query is similar to this MSDN example: http://msdn.microsoft.com/en-us/library/ms191432.aspx When I try to compile view I received error message like: "invalid ...