EzDevInfo.com

Saving changes is not permitted in SQL Server

When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error:

Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created.

Why do I have to re-create the table? I just want to change a data type from smallint to real.

The table is empty, and I didn't use it until now.

how to see active SQL Server connections?

I am using SQL Server 2008 Enterprise. I want to see any active SQL Server connections, and the related information of all the connections, like from which IP address, connect to which DB or something.

Any existing tools to solve this issue?

nvarchar(max) vs NText

What are the advantages and disadvantages of using the nvarchar(max) vs. NText data types in SQL Server? I don't need backward compatibility, so it is fine that nvarchar(max) isn't supported in older SQL Server releases.

Edit: Apparently the question also applies to TEXT and IMAGE vs. varchar(max) and varbinary(max), for those searching for those data-types later.

Thanks!

How to check if column exists in SQL Server table

I need to add a specific column if it does not exist. I have something like this, but it always returns false:

IF EXISTS(SELECT *
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'myTableName'
                 AND COLUMN_NAME = 'myColumnName') 

How can I check if a column exists in a table of SQL Server database?

How can I get column names from a table in SQL Server?

I would like to query the name of all columns of a table. I found how to do this in:

• Oracle
• MySQL
• PostgreSQL

But I need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Why am I getting an error doing an insert when IDENTITY_INSERT is set to OFF?

How do I turn it on properly in SQL Server 2008? Is it by using SQL Server Management Studio?

I have run this query:

SET IDENTITY_INSERT Database. dbo. Baskets ON

Then I got the message back in the console that the Command(s) completed successfully. However when I run the application, it still gives me the error shown below:

Cannot insert explicit value for identity column in table 'Baskets' when 
IDENTITY_INSERT is set to OFF.

Search Text in Stored Procedure in SQL Server

I want to search a text from my all database stored procedures. I use the below SQL:

SELECT DISTINCT 
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m 
       INNER JOIN 
       sys.objects o 
         ON m.object_id = o.object_id
 WHERE m.definition Like '%[ABD]%';

I want to search for [ABD] in all stored procedures including square brackets but its not giving proper result. Please tell me how I can change my query to achieve this.

SET versus SELECT when assigning variables?

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

Connect different Windows User in SQL Server Management Studio (2005 or later)

Is there a way in SQL Server Management Studio 2005 (or later) to change the Windows Authentication user (as you could in SQL Server 2000 and older)?

This is the general connection properties dialog I am referring to (note the greyed out UID/PWD when selecting Windows Auth; sorry, I'm new so can't embed an img).

FYI - One workaround is to use 'runas' but I'm looking for a solution that will allow me to work with multiple Windows accounts across multiple servers (across multiple domains) . . .

Rename column SQL Server 2008

I am using SQL Server 2008 and navicat. I need to rename a column in a table using SQL.

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

This statement doesn't work.

Sql Server add auto increment primary key to existing table

As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null).

I'm assuming I can run a query to fill this column with incremental numbers, and then set as primary key and turn on auto increment. Is this the correct way to procede? And if so, how do I fill the initial numbers?

Unique constraint on multiple columns

  CREATE TABLE [dbo].[user](
    [userID] [int] IDENTITY(1,1) NOT NULL,
    [fcode] [int] NULL,
    [scode] [int] NULL,
    [dcode] [int] NULL,
    [name] [nvarchar](50) NULL,
    [address] [nvarchar](50) NULL,
 CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED 
(
    [userID] ASC
)
) ON [PRIMARY]

GO

how can add unique constraint for columns fcode, scode, dcode with t-sql and/or management studio? fcode, scode, dcode must be unique together.

Check if a row exists, otherwise insert

I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction.

This is for a booking system, so it must be atomic and reliable. It must return true if the transaction was commited and the flight booked.

I'm new to T-SQL, and not sure on how to use @@rowcount. This is what I've written until now. Am I on the right road? I'm sure is an easy problem for you. Thanks

-- BEGIN TRANSACTION (HOW TO DO?)

UPDATE Bookings
 SET TicketsBooked = TicketsBooked + @TicketsToBook
 WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)

-- Here I need to insert only if the row doesn't exists.
-- If the row exists but the condition TicketsMax is violated, I must not insert 
-- the row and return FALSE

IF @@ROWCOUNT = 0 
BEGIN

 INSERT INTO Bookings ... (omitted)

END

-- END TRANSACTION (HOW TO DO?)

-- Return TRUE (How to do?)

Multi-statement Table Valued Function vs Inline Table Valued Function

A few examples to show, just incase:

Inline Table Valued

CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS 
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
    FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
        ON a.SaleId = b.SaleId
        INNER JOIN Production.Product c ON b.ProductID = c.ProductID
    WHERE a.ShipDate IS NULL
GO

Multi Statement Table Valued

CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
RETURNS @CustomerOrder TABLE
(SaleOrderID    INT         NOT NULL,
CustomerID      INT         NOT NULL,
OrderDate       DATETIME    NOT NULL,
OrderQty        INT         NOT NULL)
AS
BEGIN
    DECLARE @MaxDate DATETIME

    SELECT @MaxDate = MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID

    INSERT @CustomerOrder
    SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
    FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b
        ON a.SalesOrderID = b.SalesOrderID
        INNER JOIN Production.Product c ON b.ProductID = c.ProductID
    WHERE a.OrderDate = @MaxDate
        AND a.CustomerID = @CustomerID
    RETURN
END
GO

Is there an advantage to using one type (in-line or multi statement) over the other? Is there certain scenarios when one is better than the other or are the differences purely syntactical? I realise the two example queries are doing different things but is there a reason I would write them in that way?

Reading about them and the advantages/differences haven't really been explained.

SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine:

    CREATE PROCEDURE [dbo].[Item_AddItem]
        @CustomerId uniqueidentifier,
        @Description nvarchar(100),
        @Type int,
        @Username nvarchar(100),
    AS
    BEGIN

        DECLARE @TopRelatedItemId uniqueidentifier;
        SET @TopRelatedItemId = 
        (
           SELECT top(1) RelatedItemId 
           FROM RelatedItems 
           WHERE CustomerId = @CustomerId
        ) 

        DECLARE @TempItem TABLE
        (
            ItemId uniqueidentifier,
            CustomerId uniqueidentifier,
            Description nvarchar(100),
            Type int,
            Username nvarchar(100),
            TimeStamp datetime
        );

        INSERT INTO Item
        OUTPUT INSERTED.* INTO @TempItem
        SELECT NEWID(), @CustomerId, @Description, @Type, @Username, GETDATE()

        SELECT
            ItemId,
            CustomerId,
            @TopRelatedItemId,
            Description,
            Type,
            Username,
            TimeStamp
        FROM
            @TempItem
END
GO

So the question for you guys is is there a possibility to do something along the lines of:

DECLARE @TempCustomer TABLE
(
   CustomerId uniqueidentifier,
   FirstName nvarchar(100),
   LastName nvarchar(100),
   Email nvarchar(100)
);
SELECT 
    CustomerId, 
    FirstName, 
    LastName, 
    Email 
INTO 
    @TempCustomer 
FROM 
    Customer
WHERE 
    CustomerId = @CustomerId

So that I could reuse this data from memory in other following statements? SQL Server throws a fit with the above statement, however i don't want to have to create separate variables and initialize each one of them via a separate SELECT statement against the same table.... UGH!!!

Any suggestions on how to achieve something along the lines without multiple queries against the same table?