SQL Tips & Tricks

How to Perform Analysis with SQL Server Analysis Services Project (Step by Step Instruction)

2013-05-14T02:11:00.000-07:00

How to Create Cubes, Dimensions and Measures in SQL Server Business Intelligence

What is the use of data if it is not for Analysis. What is the use of RDBMS if it is not providing the Analytics functionality. Here we take a look at the SQL Services Project of SQL Server 2008

There are multiple open source softwares like Pentaho / Jasper etc. that provide the functionality we will see below but nothing comes with the ease like SSAS

To start, open Business Intelligence Studio and create Analysis Services Project

The wizard will create a project aling with required folders - Dimensions and Cubes the two most important of any Business Intelligence project

Creating Cube is easy in SQL Server. Right click on Cubes and select New Cube - a beautiful Wizard will guide

Select the Generate tables option

The below step helps you in Creating new Dimensions - OLAP is all about measures (Facts and Dimensions)

Facts and Dimensions in SQL Server

Time dimension is created and filled automatically by SQL Server based on settings

The above figure shows the measures and dimensions created by SQL SERVER.

Now we have the logical data model, we can create the Schema . SQL SERver Schema Creation Wizard helps to create the required schema

How to import Large files (TXT / CSV) to SQL Server 2008 using SSIS

2013-05-08T21:22:00.000-07:00

SQL Server's Integration Services provides the functionality to laod

This can be done from the SQL Server by selecting the integration services project

The following

How to import CSV / Excel file to SQL Server Table

2012-09-10T01:37:00.003-07:00

How to Load Excel File / CSV File to a SQL Server Table / Create SQL Server Table from Excel File

Step 1: Select Import and Export Wizard from Program

Step 2: Select Import and Export Wizard from Program

Step 3: Select Flat File as Source

Step 4: Select the CSV / Text file

Step 5: Choose the Server

Step 6: Choose Database

Step 7: Specify Schema and Table Names

Step 8: Run the Pachage

Specified column precision 50 is greater than the maximum precision of 38.

2012-09-10T00:39:00.002-07:00

What is the Preferred Data Type for Price and Amount Fields

This error occurs when you are trying to define a Decimal field with precision more than 38. If you would require more precission, please use any of the following:

[Sales Price] [Numeric](50,2) NULL,

[Quantity] [int](50) NULL,

[Amount] [Numeric](50,2) NULL,

Money
Float


CREATE TABLE [dbo].[Transact.Details](


 [Store ID] [varchar](50) NULL,


 [Customer ID] [varchar](50) NULL,


 [Transaction ID] [varchar](50) NULL,


 [Transaction Date] [varchar](50) NULL,


 [Category ID] [varchar](50) NULL,


 [Item ID] [varchar](50) NULL,


 [Sales Price] [Numeric](38,2) NULL,


 [Quantity] [int] NULL,


 [Amount] [Numeric](38,2) NULL,


) ON [PRIMARY]

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

2012-07-08T06:54:00.002-07:00

The error occurs when I try to run the BCP utility

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

How to Export Query as XML - SQL Server 2008

2012-07-08T05:49:00.002-07:00

Save Recordset as XML using Select Query in SQL Server 2008

Let us consider the a table (shown below) with couple of columns that needs to be exported as XML

The following query

SELECT TOP 1000 [TrainID] as 'TrainNum'

,[TrainName]

FROM [OnlineTrans].[dbo].[TrainMaster] for XML PATH

Is used for generating a XML

On the other hand, If you want to have a meaningfully named element instead of , you need to specify the same in the query. The following query has the row element as well as the root element

SELECT TOP 1000 [TrainID] as 'TrainNum'

,[TrainName]

FROM [OnlineTrans].[dbo].[TrainMaster] for XML path ('TrainInfo'), root ('ParentInfo')

'int' is not a recognized CURSOR option.

2012-05-06T08:21:00.002-07:00

This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table

declare #myi int

Should be replaced by

declare @myi int

While Loop in SQL Server

2012-05-06T08:21:00.001-07:00

While loop in T-SQL

Here is a simple example of While loop

Declare @temp tinyint = 1

while (@temp < 11)

begin

print cast(@temp as varchar(2)) + ' X 2 = ' + cast(@temp * 2 as char)

set @temp += 1

end

The above code will be having the following output:

Output

1 X 2 = 2

2 X 2 = 4

3 X 2 = 6

4 X 2 = 8

5 X 2 = 10

6 X 2 = 12

7 X 2 = 14

8 X 2 = 16

9 X 2 = 18

10 X 2 = 20

How to create a SQL Function that Returns a Table

2012-05-06T08:21:00.000-07:00

SQL Create Table Valued Function (SQL SERVER 2008) / How to Create SQL_TABLE_VALUED_FUNCTION

The following snippet uses a Table variable to create a table and inserts value into it, which the function returns

CREATE FUNCTION ReturnATable()
RETURNS @TabVar TABLE
(
 OrderID int not null,
 OrderDate datetime,
 OrderStatus bit,
 OrderValue decimal,
 BilledBy varchar(20)
)
AS
BEGIN
DEclare @Date datetime ;
Set @Date= GETDATE();
Insert Into @TabVar values
(21, @Date , 1, 212.42, 'Jose');
return 
END

The function can be executed as shown below

Select * from ReturnATable()

How to get Column Names of All Tables in a database through SQL Query / SysTables in SQL Server 2008

2011-09-05T20:12:00.000-07:00

How to list all tables and each table, loop through each column using SQL query

It is not uncommon to get the list of all column names from all available tables; at times some valuable information peeps out when one researches it. The following query will get you exactly that

select sys.syscolumns.name as ColumnName, sys.sysobjects.name as TableName from sys.syscolumns,sys.sysobjects where sys.sysobjects.id = sys.syscolumns.id

How to Split SQL String to Multiple String using Delimiter

2011-06-03T01:11:00.000-07:00

How to check if a text exists within another in SQL Server 2008 / How to split SQL Column based on Comma

You can use the combination of CharIndex and Substring function in SQL to split the string based on delimiter.

Here is a way to split a column based on Comma

Select LTRIM(SUBSTRING(EmpAddress, CHARINDEX( ',',EmpAddress)+1,len(EmpAddress))) from #TempEmployee

We have used Ltrim to remove any leading spaces

A RETURN statement with a return value cannot be used in this context. - Create Function

2011-05-31T06:35:00.000-07:00

If this error occurs in the Create Function that uses a table then the variable after return statement should be removed

The syntax should be like

Create Function
Returns Table
(
Table Definition here
..
..
)
as
BEGIN
..
..
RETURN -- note no value is returned here
END

How to get the list of Functions in Database

2011-05-31T06:06:00.000-07:00

How to Filter Functions from Sys.Objects

The following query will list of the Functions that are part of the database:

SELECT * FROM

sys.objects

where type in ('FN', 'IF', 'TF')

Cannot alter column 'X' because it is 'timestamp'.

2011-02-21T01:31:00.000-08:00

How to Change DataType of TimeStamp Column.

Datatype of Timestamp column cannot be deleted. Hence it is advised to drop the column and re-create it

For example,

ALTER TABLE [dbo].[PerformanceMaster] DROP COLUMN PerformanceDate
ALTER TABLE [dbo].[PerformanceMaster] ADD PerformanceDate datetime2

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

2011-02-21T00:59:00.000-08:00

TimeStamp Column in SQL Server

TimeStamp is used to store unique binary numbers within a database. This column is autogenerated with a storage size of 8 bytes.

SQL Server - TimeStamp Column

IF you want to store the Date and Time (e.g., updated time etc) use datetime2 datatype instead of timestamp.

SQL Server DateTime Column to Record Date and Time

The error "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." occurs when you try to insert the value using Insert statement. Leaving the column in the Insert will automatically update the value

Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, PerformanceDate )
values (@PerformanceID, @EventID, @ArtistID, @UpdateDate )

Should throw and error. Use the following instead:

Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, )
values (@PerformanceID, @EventID, @ArtistID)

Information_Schema.Routines doesn't return all procedure names

2011-01-14T18:33:00.000-08:00

How to get a list of Procedures containing Specific Text / How to get a list of Procedures using Particular Table

Here is one method we generally use for returning Stored Procedures containing a particular text

SELECT ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%used_by_id%'
AND ROUTINE_TYPE = 'PROCEDURE'

order by ROUTINE_NAME

However, we found it has some problems when the text appears at the fag end of big procedures. Instead try the following also and match the result

SELECT OBJECT_NAME(id)

FROM syscomments

WHERE [text] LIKE '%used_by_id%'

AND OBJECTPROPERTY(id, 'IsProcedure') = 1

GROUP BY OBJECT_NAME(id)

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(object_id) LIKE '%used_by_id%'

order by name

SELECT OBJECT_NAME(object_id)

FROM sys.sql_modules

WHERE Definition LIKE '%used_by_id%'

AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

order by OBJECT_NAME(object_id)

How to Specify Foriegn Key Relationships in SQL Server 2008

2010-12-16T19:51:00.000-08:00

Foriegn Key in SQL Server 2008 - Use the Relationships option

Specify the relationship between the columns

Add new Relationship name and save when prompted

Unable to change Identity Specification in SQL Server 2008

2010-12-16T19:41:00.000-08:00

Unable to change Identity Specification in SQL Server 2008

How to make Server Explorer visible in Visual Studio

2010-12-16T19:37:00.000-08:00

Server Explorer not visible in Visual Studio

Your pending changes require the following tables to be dropped and re-created

2010-12-16T19:30:00.000-08:00

Remove the checkbox 'Prevent saving changes'

How to create auto-increment column in SQL Server

2010-12-16T19:26:00.000-08:00

How to Set Increment value in SQL Server

Set Column properties - goto Identity Specification, set (Is Identity) to True (Yes)

SQL Server Identity Specification

How to specify Default value for a column in SQL Server

2010-12-16T19:18:00.000-08:00

SQL Server Default Column Values

Here is a way to specify default values in Sql Server Management Studio for a column

SQL Server 2008 - Saving changes is not permitted

2010-04-05T18:36:00.000-07:00

Error Message: String or binary data would be truncated. - SQL SERVER

2010-04-05T18:16:00.000-07:00

No row was updated.

The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.

Correct the errors and retry or press ESC to cancel the change(s).

The above error occurs in Microsoft SQL Server Management Studio when you attempt to enter more characters than the maximum level for that field

How to set Case Sensitive comparison in SQL Server 2008

2010-04-05T18:13:00.000-07:00

How to set a field as Case Sensitive in SQL Server 2008.

Here is a simple way to do that