Bitlink Development Blog

The error message:

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

This can be fixed by the following steps:

Tools -> Options -> Designer Page -> "Prevent saving changes that require table re-creation" -> uncheck

Tags:

make sure to copy the database in the APP_Folder

use the following connection string as template:

<add name="GuestbookConnectionString1" connectionString="Data Source=.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\Guestbook.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
   providerName="System.Data.SqlClient" />

Tags: local databases

BEGIN TRANSACTION;

BEGIN TRY

      select * from ......

      update .....

      save TRANSACTION whatever

      update ......

    RAISERROR ('now we throw this error as if naturally occured', 18, 10 );

      update .....

      COMMIT TRANSACTION; -- it will not reach this code because of forced error

END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION whatever;

        -- or ...

            -- IF XACT_STATE() <> -1

           -- ROLLBACK TRANSACTION ProcedureSave;

    DECLARE @ErrorMessage NVARCHAR(4000);

    DECLARE @ErrorSeverity INT;

    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE();

    SELECT @ErrorSeverity = ERROR_SEVERITY();

    SELECT @ErrorState = ERROR_STATE();

      --or re raise the error as below

  /*  RAISERROR (@ErrorMessage, -- Message text.

               @ErrorSeverity, -- Severity.

               @ErrorState -- State.

               );*/

END CATCH

Tags: sql transactions

I encountered the following issue and couldn't find any information on the internet on this issue:

The null value cannot be assigned to a member with type System.Guid which is a non-nullable value type.

After quite a struggle I solved it as follow (which really is bizarre and still unexplained):

 I change:

SELECT *, guidanceType.name as guidanceType, legacyID as refid
FROM  HospitalGuide INNER JOIN
               GuidanceType ON HospitalGuide.GuidanceTypeId = GuidanceType.Id

 To:

SELECT HospitalGuide.*, guidanceType.name as guidanceType, legacyID as refid
FROM  HospitalGuide INNER JOIN
               GuidanceType ON HospitalGuide.GuidanceTypeId = GuidanceType.Id

 and it solved my problem.

Tags:

Database diagram support objects cannot be installed because this database does not have a valid owner.

This sometimes happen when you attach a database from a different machine or after restoring a database from scratch

Solution:

EXEC sp_dbcmptlevel '<<DBNAME>>', '90';
go
ALTER AUTHORIZATION ON DATABASE::<<DBNAME>> TO "<<LOGINNAME>>"
go
use <<DBNAME>>
go
EXECUTE AS USER = N'dbo' REVERT
go

Tags:

Cannot open database "DBName" requested by the login. The login failed.
Login failed for user 'DBUser'.

This sometimes happen when you attach a database from a different machine
(because the security doesn't reside inside the database itself)

Solution:

use <<DBNAME>>
go

---create LOGIN <<USERNAME>> WITH PASSWORD = '<<PASSWORD>>' , check_policy = off;
---OR (uncomment which one is needed)
alter LOGIN <<USERNAME>> WITH PASSWORD = '<<PASSWORD>>' , check_policy = off;
go

exec sp_change_users_login 'Auto_Fix', '<<USERNAME>>', null, '<<PASSWORD>>'
go

Make sure your username and password match the web.config file's: <configuration>
     <connectionStrings>
          <clear/>
          <add name="MyConnectionString" connectionString="Data Source=MYPC\SQL2005;Initial Catalog=DBNAME;Persist Security Info=True;User ID=<<USERNAME>>;Password=<<PASSWORD>>" providerName="System.Data.SqlClient"/>

Tags:

Capturing Errors and Rowcount at once (only avail for one SQL command - therefore grab both at once)
SELECT * FROM customer
SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR

Declaring vars:
DECLARE @j varchar(10)

Casting:
CAST(@@ROWCOUNT AS varchar(12))

Temp tables:
CREATE TABLE #Temp (i int, j varchar(10))
INSERT INTO #Temp (i, j) VALUES (1, 'First Row')
INSERT INTO #Temp (i, j) VALUES (1, 'Second Row')
GO

other interresting examples:

select n as [Matched state], a as Episodes  , b as Isolates from
(
SELECT 'Matched' as n, 1024 as a, 1024 as b
union SELECT 'Partially' , 12 ,12
union SELECT'No Match' as n , 33 as a ,27 as b
) as t

Tags:

USE [master]
GO
CREATE DATABASE cliniweb ON
( FILENAME = N'C:\work\NHS\Kings Colleage PHASE 1\old database\cliniweb.mdf' ),
( FILENAME = N'C:\work\NHS\Kings Colleage PHASE 1\old database\cliniweb.ldf' )
 FOR ATTACH ;
GO

/* verify as follows */
select name from sys.databases
go

Tags:

Getting a summary of your database content (database named XXXXX)  
[replace XXXXX with your database name]

Tablename vs Row count 

/*--------------start here------------ */

use XXXXX
go


declare x cursor local for
select name from sys.tables  where name != 'sysdiagrams' order by name;

declare @Tname nvarchar(50)
declare @SQL nvarchar(4000)
select @SQL = N''
declare @xFETCH_STATUS int
declare @statement nvarchar(250)

open x
FETCH NEXT FROM x
into @Tname
select @xFETCH_STATUS  =  @@FETCH_STATUS
while @xFETCH_STATUS = 0
begin
    select @SQL = @SQL + N' select '
    select @SQL = @SQL + N' '''+@Tname+''' as Tname, count(*) as Count from ' +  @Tname
    EXEC dbo.sp_executesql @statement
    FETCH NEXT FROM x into @Tname
    select @xFETCH_STATUS  =  @@FETCH_STATUS
if @xFETCH_STATUS = 0
select @SQL = @SQL + ' union '
end
close x
deallocate x
--print @SQL   -- if you want to see the code
exec dbo.sp_executesql @SQL
go

Tags: