:::: MENU ::::

Posts Categorized / Data Modeling and Database Design

  • Apr 13 / 2009
  • 0
Data Modeling and Database Design, Database Diagrams, dbDigger, SSMS tips and tricks

This database does not have one or more of the support objects required to use database diagramming

SQL Srever Management Studio is equipped with a comprehensive solution for working with database diagrams. But whenever diagram folder inside a database is clicked for the first time following dialog is shown
This database does not have one or more of the support objects required to use database diagramming

SSMS error for database diagrams

The option dialog just asks your permissions to create some system stored procedures that are required to work with diagrams in SSMS. These stored procedures are not created by default in new databases. So You may allow SQL Server to create these system stored procedures for your database. The system stored procedures will be created in your database after you allow to do so. Following are system stored procedures that are created for working with SQL Server diagrams

  • sp_upgraddiagrams
  • sp_helpdiagrams
  • sp_helpdiagramdefinition
  • sp_creatediagram
  • sp_renamediagram
  • sp_alterdiagram
  • sp_dropdiagram;

Once these system stored procedures are created you will never be prompted again by SQL Server for creating these for that database.

  • Apr 13 / 2009
  • 1
Data Modeling and Database Design, Data Types, dbDigger

Signed and unsigned integer data types in SQL Server

SQL Server have following data types of integer family

  • bigint
  • int
  • smallint
  • tinyint
  • bit

All members in integer family of data types except tinyint may handle negative values. So whenever using tinyint make sure that always positive value will be inserted against tinyint data type. Tinyint has values in range of 0 to 255. If an out of range value is presented then Arithmetic overflow error for data type tinyint will be generated.

  • Mar 21 / 2009
  • 4
Data Modeling and Database Design, dbDigger, SQL Server Collations, System Functions

Get list of all SQL Server collations

Various collations are available in all versions of SQL Server. According to BOL A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages). SQL Server collation may effect sort order for unicode, non-unicode data types and code page used for storing non-unicode character data types. In order to get list of all available collations of SQL Server use following script

 
SELECT *  
 FROM ::fn_helpcollations()  
 GO  

In case of SQL Server 2005 and onwards function prefix :: may be removed. Hence script for collation list in SQL Server 2005 and onwards will be

SELECT *  
 FROM fn_helpcollations()  
 GO  
  • Dec 12 / 2008
  • 0
Data Modeling and Database Design, dbDigger, T-SQL Scripts

Column names in each table must be unique. Column name ‘%.*ls’ in table ‘%.*ls’ is specified more than once.

The error message
Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name ‘%.*ls’ in table ‘%.*ls’ is specified more than once.

is run time error message of severity level 16. This error message appears when you try to specify a name for a column more than once upon creation of a table. To execute the statement columns names within a table must be unique.
You may add any check through if exists. In that case you can query following meta data to check existence of any column in a table.

select * from INFORMATION_SCHEMA.COLUMNS
where table_name = 'tableName'
and column_name = 'columnName'
GO
Consult us to explore the Databases. Contact us