“Prevent saving changes that require the table to be re-created” negative effects
Tools –> Options –> Designers node –> Uncheck “ Prevent saving changes that require table recreation “.
sql-server-2005
What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage. Since NVARCHAR uses 2 bytes per character, that’s approx. 1 billion characters. Leo Tolstoj’s War and Peace is a 1’440 page book, containing about 600’000 words – so that might be 6 million characters – well rounded up. So you could … Read more
Check if a string contains a substring in SQL Server 2005, using a stored procedure
CHARINDEX() searches for a substring within a larger string, and returns the position of the match, or 0 if no match is found if CHARINDEX(‘ME’,@mainString) > 0 begin –do something end Edit or from daniels answer, if you’re wanting to find a word (and not subcomponents of words), your CHARINDEX call would look like: CHARINDEX(‘ … Read more
How do I use an INSERT statement’s OUTPUT clause to get the identity value?
You can either have the newly inserted ID being output to the SSMS console like this: INSERT INTO MyTable(Name, Address, PhoneNo) OUTPUT INSERTED.ID VALUES (‘Yatrix’, ‘1234 Address Stuff’, ‘1112223333’) You can use this also from e.g. C#, when you need to get the ID back to your calling app – just execute the SQL query … Read more
Create a date from day month and year with T-SQL
Try this: Declare @DayOfMonth TinyInt Set @DayOfMonth = 13 Declare @Month TinyInt Set @Month = 6 Declare @Year Integer Set @Year = 2006 — ———————————— Select DateAdd(day, @DayOfMonth – 1, DateAdd(month, @Month – 1, DateAdd(Year, @Year-1900, 0))) It works as well, has added benefit of not doing any string conversions, so it’s pure arithmetic processing … Read more
How do I drop a foreign key constraint only if it exists in sql server?
This is a lot simpler than the current proposed solution: IF (OBJECT_ID(‘dbo.FK_ConstraintName’, ‘F’) IS NOT NULL) BEGIN ALTER TABLE dbo.TableName DROP CONSTRAINT FK_ConstraintName END If you need to drop another type of constraint, these are the applicable codes to pass into the OBJECT_ID() function in the second parameter position: C = CHECK constraint D = … Read more
SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
To avoid duplicate rows for some columns, use user_type_id instead of system_type_id. SELECT c.name ‘Column Name’, t.Name ‘Data type’, c.max_length ‘Max Length’, c.precision , c.scale , c.is_nullable, ISNULL(i.is_primary_key, 0) ‘Primary Key’ FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id … Read more
INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
In your table dbo.Sup_Item_Cat, it has a foreign key reference to another table. The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table. If you have SQL Server Management Studio, open it up and sp_help ‘dbo.Sup_Item_Cat‘. See which … Read more
Extreme wait-time when taking a SQL Server database offline
After some additional searching (new search terms inspired by gbn’s answer and u07ch’s comment on KMike’s answer) I found this, which completed successfully in 2 seconds: ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE (Update) When this still fails with the following error, you can fix it as inspired by this blog post: ALTER DATABASE … Read more
How do you kill all current connections to a SQL Server 2005 database?
The reason that the approach that Adam suggested won’t work is that during the time that you are looping over the active connections new one can be established, and you’ll miss those. You could instead use the following approach which does not have this drawback: — set your current connection to use master otherwise you … Read more