Jun282010

SQL 2008 modifying table structures from within Management Studio

Published by Pete Celliers at 5:16 PM under Errors | SQL SERVER

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



[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses

Mar012010

working with local databases in asp.net

Published by Pete Celliers at 5:56 PM under c# | SQL SERVER

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" />

 

 



[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses

Nov202009

SQL transaction template / skeleton

Published by Pete Celliers at 6:23 PM under SQL SERVER

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



[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses

Nov132009

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

Published by Pete Celliers at 3:51 PM under LINQ | SQL SERVER

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.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

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

Source Error:

Line 1690: Kingsweb2DataAccess.Kingsweb2DataClassesDataContext newdb = new Kingsweb2DataAccess.Kingsweb2DataClassesDataContext();
Line 1691: List<Kingsweb2DataAccess.SearchKingsFullListResult> x = (from m in newdb.SearchKingsFullList(KingsHospitalID) select m).ToList();
Line 1692: foreach (string word in words)

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.

 

 



[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses

Sep072009

Database diagram support objects ... error

Published by Pete Celliers at 12:14 PM under Errors | SQL SERVER

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



[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses

Sep012009

SQL fix users

Published by Pete Celliers at 1:27 PM under Errors | SQL SERVER

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"/>



[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses

Aug272009

SQL commands Overview

Published by Pete Celliers at 9:41 PM under SQL SERVER

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



[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses

May262009

sql command for attaching database in SQL Server

Published by Pete Celliers at 10:38 AM under SQL SERVER

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



[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses

Feb042009

Database summary Table/Rowcount

Published by Pete Celliers at 11:41 PM under SQL SERVER

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




[KickIt] [Digg] [del.icio.us] [Facebook] [Google] [StumbleUpon]

Tags:

E-mail | Permalink | Post RSSRSS comment feed 0 Responses