Tag: sql-server

How to drop SQL default constraint without knowing its name?

In Microsoft SQL Server, I know the query to check if a default constraint exists for a column and drop a default constraint is: IF EXISTS(SELECT * FROM sysconstraints WHERE id=OBJECT_ID(‘SomeTable’) AND COL_NAME(id,colid)=’ColName’ AND OBJECTPROPERTY(constid, ‘IsDefaultCnst’)=1) ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTable_ColName But due to typo in previous versions of the database, the name of […]

Real life example, when to use OUTER / CROSS APPLY in SQL

I have been looking at CROSS / OUTER APPLY with a colleague and we’re struggling to find real life examples of where to use them. I’ve spent quite a lot of time looking at When should I use Cross Apply over Inner Join? and googling but the main (only) example seems pretty bizarre (using the […]

Comma Separated values with SQL Query

My SQL table is like following City_Code Post_Code Post_Code_Description 100 A1 ABC 100 C8 XYZ 100 Z3 MNO 200 D4 LMN 300 E3 IJK 300 B9 RST It’s a mapping between city_code and Post_Code. One City_Code has many Post Codes. Now i want to run a Query to get something like following City_Code Post_Code Post_Code_Description […]

SQL Server 2008 Windows Auth Login Error: The login is from an untrusted domain

When attempting to connect to a SQL Server 2008 Instance using Management Studio, I get the following error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452) I can login using SQL Authentication without problem. I’ve been getting this error all of a […]

SSIS Source Format Implicit Conversion for Datetime

Does anyone know of a chart that shows which formats of dates are supported by the different datatypes in SSIS? For example, does DT_DBTimestamp support the format 1-Jan. I tried looking but couldn’t find anything. The reason I ask is that I found that when I try to do a Convert(datetime, ‘1-Jan’) it fails which […]

syntax for single row MERGE / upsert in SQL Server

I’m trying to do a single row insert/update on a table but all the examples out there are for sets. Can anyone fix my syntax please: MERGE member_topic ON mt_member = 0 AND mt_topic = 110 WHEN MATCHED THEN UPDATE SET mt_notes = ‘test’ WHEN NOT MATCHED THEN INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, […]

What is the Oracle equivalent of SQL Server's IsNull() function?

In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL?

SQL: Update a row and returning a column value with 1 query

I need to update a row in a table, and get a column value from it. I can do this with UPDATE Items SET Clicks = Clicks + 1 WHERE Id = @Id; SELECT Name FROM Items WHERE Id = @Id This generates 2 plans/accesses to the table. Is possibile in T-SQL to modify the […]

What does sp_reset_connection do?

sp_reset_connection seems to be called by SQL Server connection pooling, to ensure that connections reused from the pool have most of their settings reset. Does anyone know exactly what it does and doesn’t do though? eg I see from this post that it doesn’t reset the transaction isolation level

SQL Server query – Selecting COUNT(*) with DISTINCT

In SQL Server 2005 I have a table cm_production that lists all the code that’s been put into production. The table has a ticket_number, program_type, and program_name and push_number along with some other columns. GOAL: Count all the DISTINCT program names by program type and push number What I have so far is: SELECT DISTINCT […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.