SQL, BI and Data Analytics Solutions: October 2013

Calculation on Numbers Resulting it as a Integer values not in decimal in SQL

A comman mistake while performing calculation in sql query not getting exact output or Result

In order to know more about this topic run a simple query as below.

select 13/2

Output 6

But not exact as the output should come as 6.5

Try to convert or cast it as float or decimal

select CAST((13/2) as float)

Output 6

select convert(decimal(12,0),(13/2))

Output 6

None of above query is giving exact result

Now run this one

select cast(13 as float)/2

Output 6.5

select 13/cast(2 as float)

Output 6.5

The Reason for above 2 queries not giving exact output is we are getting the result and after that we were casting it to float of decmial values

In last 2 query what we did is in the calculation time only we have casted or coverted the denominator or numerator into float so int divided by float of float divided by Int will result as Float.

Note : Float has higher preference then int .In order to check Datatype precedence go to msdn below link

http://technet.microsoft.com/en-us/library/ms190309.aspx

Concatenating Null values with a String or Number in SQL 2008

Concatenating Null values with a String or Number in SQL 2008 and 2012

Anything concatenate with NULL yields to output as NULL but in case when you want to concatenate NULL field with other but resulting NULL can hamper your output.

Take an example like you have a table where you have EmployeeFirst Name and EmployeelastName columns in the report and you want to show EmployeeFull Name as seprate column in the report

but some of employee dont have last Name so Concatenating Employee first and last will result employeefull name as null for employee who doesnt have last Name

below is the sample table with above example

create table Employee_NUll

(Empid int ,EmpFName nvarchar(50),EmpLName nvarchar(50))

Insert into Employee_NUll values(1,'Vijay','Rana')

,(2,'Amit',null),(3,'Suresh',null),(4,'Ravi','Rai')

select (EmpFName+EmpLName)EmpFullName,* from Employee_NUll

Output

EmpFullName Empid EmpFName EmpLName

VijayRana 1 Vijay Rana

NULL 2 Amit NULL

NULL 3 Suresh NULL

RaviRai 4 Ravi Rai

If you see output the Employee full Name is coming NULL for employee having last name NULL So In order to handles this

SET CONCAT_NULL_YIELDS_NULL Off;

select (EmpFName+EmpLName)EmpFullName,* from Employee_NUll

Output

EmpFullName Empid EmpFName EmpLName

VijayRana 1 Vijay Rana

Amit 2 Amit NULL

Suresh 3 Suresh NULL

RaviRai 4 Ravi Rai

Other Methods to concatenate with NULL values

IsNUll function

select isnull(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll

Coalesce Function

select coalesce(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll

SQL 2012 has provided a new function to concat with NULL values

--This will work only in SQL 2012 and above.....

select concat(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll

Traversing in all Tables of Database in SQL

In order to Traverse through all tables of SQL like you want to get Counts of all Records of Each table in a Database or suppose you want to delete data of all tables of a Database for these type of requirement where you want a looping in all tables sql has provided a system stored Procedure sp_MSForEachTable

1-To Get count of all tables of a database

Select your DB Run below query

--Count all Records of all Tables in Database TestingNew

exec sp_MSforeachtable 'select ''?''[TableName], count(*)[Count] from Testingnew.?'

2-To Delete Data of all Tables of a Database donot apply into your working Database create a seprate Database with sample tables to test this query.

Select your Testing DB Run below query

-- delete data of all tables

EXEC sp_MSForEachTable 'DELETE FROM Test.?'

This will delete data of all tables of a database

Explanation-sp_MSforeachtable traverse through all tables there is a question mark in the queries which denotes all Tables

NOTE: If you want to run delete all data query where tables have foriegn key dependencies then you have to disable all Constraints first.

Result of one Table into Another table in SQL

Some times you have requirement like you want to copy data of one table to another table or you want to have a new table like old table There are many ways to achieve this I will explain you all in each points with query and Scripts

Below is the script of table to be copied

--New table

CREATE TABLE [dbo].[testcopy](

[column1] [nvarchar](50) NULL,

[column2] [nvarchar](50) NULL

) ON [PRIMARY]

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'5', N's')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'4', N'd')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'8', N'f')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'9', N'r')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'6', N't')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'8', N'y')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'7', N'h')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'3', N'u')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'6', N'i')

1-Creating a new table along with data using above table Script i.e. copying structure and data using query

Select * into NewTestCopy from Testcopy

--to create a temp table using above table

Select * into #NewTestcopy from TestCopy

--try to create a temptable using Select into

Select * into @NewTestcopy from TestCopy

This will throw an error as Select * into doesn't work with table variables

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '@NewTestcopy '.

2-Copying data only from above table to another table or temp table or table variable

For this we will create a new table

CREATE TABLE [dbo].[testcopy1](

[column1] [nvarchar](50) NULL,

[column2] [nvarchar](50) NULL

) ON [PRIMARY]

--Copying Data of testcopy to testcopy2

insert into testcopy1

select * from testcopy

3-Copying data only of selected column from above table to another table selected columns

insert into testcopy1(column1)

select column1 from testcopy

4-If no. of Rows are less you can use GUI also to copy data from one table to another.

Right click -Sourcetable-Edit top 200 rows-Select All-Copy all data

Right click -Destination Table-Edit top 200 rows-Paste.

5-Using Import and Export wizard.

selecting your source and Destination table or you can create Destination table Same as Source table along with data but same drawback that it will not create contraints and key in the destination table.

Steps
-Rightclick ur Database-Tasks-Export data or Import Data-Choose source Datasource-Database-

Choose Destination-database table-Next-Choose Data from table and view-Select source table and view

-Next-finish.You can select multiple table ,single table or all tables to be copied from one database to another.

Note :

1- Select into TableName will create a new table with same table structure and data but will not copy keys & constraints.

2-Insert into with Select * will work with both Tempory table and Table variable but select * into will not work with table variable.

Interactive Sorting in SSRS Reports

Many times you have requirement like clicking on column names or legend name will sort the complete column in either ascending order or descending order or if its is a value field then sort them alphabetically

This is what we call as Interactive sorting in SSRS Report.

Steps to Perform Interactive sorting in Your Report

Assuming that i have a report with Column Name EmpName displaying all columns Name

Select your EmpName Column -Right click on text box

-Text Box properties

-Interactive Sorting tab

-Check box-Enable Interactive Sorting on this textbox

Checkbox-Detail Row

sort by-EmployeeName or clicking Fx-Field!EmployeeName.value

Apply this Sortingto-"YourdatasetName"

Below ScreenShot showing all Properties.

Click ok

Preview the Report

Clicking on EmployeeName column Header will Sort the report alphabatically.

The Tablix is invalid Dataset property is missing or Table without Dataset in SSRS Report

Today while creating a report i have requirement where i have to show some hardcoded values below the reports instead of writing separetely in each textboxes, i prefered table i dropped a table below my report data and copied all hardcoded data in the rows and columns as specified, when i preiview the report.

Report was throwing error.

The tablix 'Tablix2' is invalid. The value for the DataSetName property is missing

The reason was a table or tablix is always associated with a dataset but i was not using any dataset values i was directly hardcoding the values in the table rows and column.

So,Below is the Fix

Select your newely created table

Properties window-
General-Datasetname -which is blank change it to your report dataset Name.

or
Right click table-Tablix Properties-Select your Report datasetName.

Preview the Report the report will work without any issue.

Note:You can achieve the same thing by dropping textboxes in your report and copy hardcoded values in the textboxes but i believe it's quite time consuming as you have to drop multiple textboxes in the report then you have to aligned them properly.

Paging Issue in SSRS Report adds a question mark instead of display the total number of pages

Paging Issue in SSRS Report adds a question mark instead of displaying the total number of pages

While working with SSRS reports,We had one report where we have limited the no. of row to 5 .

but the report was showing question mark like in below screenshot.

if you will see the screenshot there is a question mark after 2 .we had a total of 2 pages only in the report

but instead of displaying the last page it was showing a question mark and we cannot show question mark as it was client request.

So i researched on this then i came to know that in

In SSRS 2005, if a report had 100 pages of records all pages were rendered as a whole.

In 2008,if a report had 100 pages only a page of data is rendered.

And the Reason was like rendering 100 pages as a whole will have more load on client machine so in order to avoid this a single page is rendered .

but as it was client request so we have provided a solution for this.

So here is the solution just add a textbox in your header and footer of the Report.

Write expression- =Globals!TotalPages

Hide the text box.by changing hidden property of Text box to true.

Preview the Report it will have no question mark as in above screenshot..

Saving Change is not Permitted Error while Saving Changes in a table in SQL

One of the developer in our company was trying to save the changes in a table he was changing the name of a column through Graphic Interface

Right click table -Design -Changing column name

But when he tried to save the changes he got the error Saving Change is not Permitted.

He again tried the same but failed again with same error as in below screenshot..

So,Below is the solution

In order to Save the changes you just have to do the below steps

Go to Menu bar of SSMS-Tools-Options-Designers

-Prevent saving Changes that require table re-creation -Uncheck this box

-ok

Now try to save changes in table it will save Succesfully.

Reseting Identity value back to previous value or 1 after Delete command in SQL

Reseting Identity value to previous value or 1 after Delete command in SQL

In order to explain this first you should know like if a table having Identity value column assuming that the number of records are 5 and you deleted 5th record and inserted a new record then the next value in Identity key column will be 6 not 5.It will be 1,2,3,4,6

secondly if you will delete all data from the table then when you will insert a new record in the table, it will starts from 7 whereas if you use truncate command to delete all data then insert a new record then it will starts from 1 this is one of the big difference between truncate and Delete commands
.

Now i will explain how to reset identity value back to previous value or 1.

--Creating table with identity column

CREATE TABLE [dbo].[Tableidentity](
[ID] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL
) ON [PRIMARY]
GO

--Inserting some values

INSERT INTO [Tableidentity]

VALUES ('value1'),('value2'),('value3')

select * from tableidentity

ID value

1 value1

2 value2

3 value3

Now we will delete a record from table

delete from tableidentity where id in (2,3)

insert value 2 once again

INSERT INTO [Tableidentity]

VALUES ('value2')

select * from tableidentity

ID value

1 value1

4 value2

you will notice that now Id value of new record is 4 as, 2 and 3 id value has been deleted but you want that it should be 2 i.e. same as previous value so remember as soon as you will delete a record reseed the value to its previous value.

delete from tableidentity where id =4

after deleting reseeding it back to previos values

-- identity column reset to 1

DBCC CHECKIDENT ('[Tableidentity]', RESEED, 1)

Now insert a new record

INSERT INTO [Tableidentity]

VALUES ('value2')

ID value

1 value1

2 value2

It will insert as required order.

Note: when you are deleteting all records of table using delete command then new value inserted will be maximum Identity value of deleted value+1

for eg we will delete all record and then will insert a new record.

--deleting all records of table

delete from [Tableidentity]

Now insert a new record

INSERT INTO [Tableidentity]

VALUES ('value1'),('value2')

ID value

3 value1

4 value2

The values are inserting from 3 as i mentioned above the maximum identity value when deleted was 2 so new records started from 3

So it's a best practise to use truncate when deleting all records so that you may not have to reseed it to 1.

--truncate command

truncate table tableidentity.

--Reseed to 1 without truncate i.e. after running delete all record command setting identity value back to 1

DBCC CHECKIDENT ('[Tableidentity]', RESEED, 1)

Granting a user Select only Permission or other permissions in SQL

In many cases for the security reasons of Database you want that developer or user should only have select permission to database they cannot perform insert,Update,Delete in the Database in order to give only select permission to user below are the steps and script.

--Create a New Login

--In our case we created a login Name as TestLogin

CREATE LOGIN TestLogin

WITH PASSWORD = 'test#login123';

If you will have password that doesnt match with windows policy or very small, then it will throw an error like

Msg 15116, Level 16, State 1, Line 1

Password validation failed. The password does not meet Windows policy requirements because it is too short.

--Now run below script to create a user for that login

USE Testingnew ;--only this DB will have select permission

CREATE USER usertest FOR login TestLogin ;

grant select to usertest

Now connect to user ssms login with TestLogin password-test#login123'

only TestingDB will have select permission try to open other db it will give an error

The database 'XYZ' is not accessible. (ObjectExplorer)

Now run a insert query for the DB (testing new) for which you have created your user.

insert into table1 (col1) values('2')

Msg 229, Level 14, State 5, Line 1

The INSERT permission was denied on the object 'table1', database 'Testingnew', schema 'dbo'.

Try to run a select query

select * from table1

It will work without any issue.

If you want to provide other permission like insert update delete to your user then use below script

USE Testingnew

grant  insert, update, delete to usertest

Note:If you will run above code with same login i.e TestLogin it will not works Displaying a message like

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

To solve this connect with your main login i.e sa or the login you used to create your login first time then run


USE Testingnew

grant  insert, update, delete to usertest










then run a insert query


insert into table1 (col1) values('2')


It will insert record into your table.

The Value expression for the text box ‘XYZ’ refers to the field ‘XYZ’. Report item expressions can only refer to fields within the current dataset scope Issue fixed in SSRS Report

The Reason of this error is you have changed any column name in your query and report is referring to old column Name of the sql,to fix this one change your Report old column Name to New column Name.

In order to reproduce create any report preview the report, Now, change ur column name in your

query inside Dataset Try to preview the Report .

This will throw the above error.

What i did was i Changed EmpName column to EmployeeName.

For Eg

Suppose my dataset query is

Select empid,Empname from Employee

where Empname in (@EmpName)

I preview the Report it works fine with above query.

then

I opened the Dataset change the Query to

Select empid,Empname as EmployeeName from Employee

where Employeename in (@EmpName)

I preview the report the Report was throwing above error.

In order to fix this issue .

In Design view of Report

Go to your table click on 2 row below columnName (EmpName) like in below Screenshot.

In my Screenshot it task change Depends according to your column Name of reports Select new value of your columnName from Dropdown

Preview the Report-The Issue will be fixed..

Select All as default value for Multivalue parameter in SSRS

In order to Set Select All as Default value for Multivalue Parameter in SSRS Report. The best way is

like set default values of Parameter same as Available values and in Dataset of Parameter add a where condition where ColumnName is not Null .

This is the same column which u are using as Parameter in your value field.

In order to explain in a more better way .

We will create a Dataset and a Paratemeter.

--Parameter Dataset query

--DSTEmployee

select empid,empname from employee

Add Parameter

--Employee

Parameter Properties

--Allow Multiple Selection

--Available Values Tab

-From DSTEmployee

Label field-Empname

Value Field-EmpName

--Default Value Tab

-From DSTEmployee

Value Field-EmpName

Now in order to add Select All as Default value add a where condition in your query where Empname is not Null like below

--DSTEmployee

select empid,empname from employee where EmpName is not null

Preview the Report-Report will have Parameter Default value as Select ALL EmployeeName.

Order By Months Name in SQL and SSRS Report

I was working on Report where i have to display all month Names in order by of Months if User selectes more than 12 months then it will display like screen below.

Next january month it is showing after december which means Next year.

So, I Handled this in my sql only in my query i ordered by month and year.

below is the demonstration.

--First we will create a table

Create TABLE [dbo].[EmployeeMonth]

(

[EmpID] [nvarchar](10) NULL,

[Name] [nvarchar](10) NULL,

[MonthName] [nvarchar](10) NULL,

[Year] nvarchar(10)

)

INSERT INTO [dbo].[EmployeeMonth]VALUES(1,'A','March','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(2,'B','May','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(3,'C','December','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(4,'D','November','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(5,'E','January','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(6,'F','June','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(7,'G','February','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(8,'H','April','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(9,'I','October','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(10,'J','August','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(11,'K','July','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(12,'L','September','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(5,'E','January','2014')

INSERT INTO [dbo].[EmployeeMonth]VALUES(7,'G','February','2014')

select * from [EmployeeMonth]

Output

EmpID Name MonthName Year

1 A March 2013

2 B May 2013

3 C December 2013

4 D November 2013

5 E January 2013

6 F June 2013

7 G February 2013

8 H April 2013

9 I October 2013

10 J August 2013

11 K July 2013

12 L September 2013

5 E January 2014

7 G February 2014

Now in order to get order by based on month and year to display in a report

below is the query

select * from [EmployeeMonth]

order by YEAR,DATEPART(mm,CAST([MONTHName]+ ' 1900' AS DATETIME)) asc

Output

EmpID Name MonthName Year

5 E January 2013

7 G February 2013

1 A March 2013

8 H April 2013

2 B May 2013

6 F June 2013

11 K July 2013

10 J August 2013

12 L September 2013

9 I October 2013

4 D November 2013

3 C December 2013

5 E January 2014

7 G February 2014

Write the query in you report and you will achieve the Output as shown in the above screenshot.

First and Last Date of Current Month,Previous Month or Current Year in SQL

First and Last Date of Current Month, Previous Month or Current Year in SQL

In my Previous post I have written on how to fetch First and Last Date of Current Month, Previous Month or Current Year in SSRS So, Now in this post. I will tell how to achieve the Same Result in SQL

Below are the queries

Current Date

select GETDATE()

First date of Current Month or Current month first date

select dateadd(mm,datediff(mm,0,getdate()),0)

select DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE()+1)

Last date of Current Month

select dateadd(day,-datepart(day,getdate()),DATEADD(mm,1,getdate()))

First date of Current Year

select cast(DATENAME(yy,getdate()) as DATE)

Last date of Current Year

select dateadd(day,-1,dateadd(mm,12,cast(DATENAME(yy,getdate()) as DATE)))

Previous Month Last Date

select DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE())

Previous Month First Date

select dateadd(mm,-1,dateadd(day,1,DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE())))

---Other way that are alternate of above all

--Previous month first date

select dateadd(mm,datediff(mm,0,getdate())-1,0)

--Previous month last date

select dateadd(mm,datediff(mm,0,getdate()),-1)

--current year first date

select dateadd(yy,datediff(YY,0,getdate()),0)

--current year last date

select dateadd(yy,datediff(YY,0,getdate())+1,-1)

--current month last date

select dateadd(mm,datediff(mm,0,getdate())+1,-1)

--current month first date

select dateadd(mm,datediff(mm,0,getdate()),0)

Note : There are alternative ways also to fetch the same things differ from logic to logic as below

--First Date and Last Date of Year you can use this logic also

SELECT '01/01/'+cast(year(getdate()) as varchar(4)) as [First Day],

'12/31/'+cast(year(getdate()) as varchar(4)) as [Last Day]

In order to achieve the same output in SSRS read my previous blog

http://sqlandssrssolutions.blogspot.in/2013/10/first-and-last-date-of-current-month-or.html

First and Last Date of Current Month or Current Year in Report Parameters in SSRS

In order to show firstdate and Last Date of Current month or Current Year or Previous Month dates as Report Parameter you have to write below expressions .

First date of Current Month
=Today.AddDays(1-Today.Day)

last date of Current Month
=Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)

First date of Current Year
=Today.AddMonths(1-Today.month).AddDays(1-Today.day)

Last date of Current Year
=Today.AddDays(1-Today.Day).AddMonths(13-today.month).AddDays(-1)

Previous Month Last Date
=DateAdd("d",-(Day(today)), Today)

Previous Month First Date
=dateadd(dateinterval.month, -1, today().AddDays(-(today().Day-1)))

SQL Server Product,Version,Edition and Other Information in SQL

Query to Check SQL Server Version Edition ,Product and Other Info Related SQL.

--Version Info

SELECT @@version SQLversioninfo

Output--My SQLServer Info

SQLversioninfo

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

--ProductVersion,Productlevel and Edition

SELECT SERVERPROPERTY('productversion') as Productversion , SERVERPROPERTY ('productlevel') Productlevel , SERVERPROPERTY ('edition') edition

Output-My SQLServer Info

Productversion Productlevel edition

10.50.2550.0 SP1 Enterprise Edition (64-bit)

--Product,Version,Platform and Other Info

exec xp_msver

Output-My SQLServer Info

Index Name Internal_Value Character_Value

1 ProductName NULL Microsoft SQL Server

2 ProductVersion 655410 10.50.2550.0

3 Language 1033 English (United States)

4 Platform NULL NT x64

5 Comments NULL SQL

6 CompanyName NULL Microsoft Corporation

7 FileDescription NULL SQL Server Windows NT - 64 Bit

8 FileVersion NULL 2009.0100.2550.00 ((KJ_SP1_GDR).120611-1632 )

9 InternalName NULL SQLSERVR

11 LegalTrademarks NULL Microsoft SQL Server is a registered trademark of Microsoft Corporation.

12 OriginalFilename NULL SQLSERVR.EXE

13 PrivateBuild NULL NULL

14 SpecialBuild 167116800 NULL

15 WindowsVersion 498139398 6.1 (7601)

16 ProcessorCount 4 4

17 ProcessorActiveMask NULL f

18 ProcessorType 8664 NULL

19 PhysicalMemory 8000 8000 (8388141056)

20 Product ID NULL NULL

Issue with SSRS Reports type charts Months as Horizontal axis in SSRS

Yesterday while creating a report i found a issue like In my Report i was displaying the Sales pr month

The horizontal axis was showing months name and vertical axis bar as sales per month

while testing the Report i faced a issue.

The Report was not showing all months Name it was showing Months Names every other.

Below is the ScreenShot how Month was coming into Reports.

If you look into the above screenshot it is not showing all months Names it is showing bars for each month but not displaying months Name below.

In order to fix this issue.

Go to Design Mode of your Report.

Go to Horizontal Properties--By Right clicking on Month Names in Design Mode

You will get the below Image.

You will See Interval option in the Image which is by default as Auto change it to 1 and it will fix down the issue.

Preview the Report.

As you will see in the above screenshot all months name are coming .

Searching Column Names in Tables and Views in a Database in SQL

Many times you have to Search Some column Names in Entire Database without Script it's very Difficult to get the table names or view Name you have to open Each table or View, then look for the Column you want which is quite time consuming.

Below are the script to find Column Names in Table

--To Search a Column Name in all Tables of a Database

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%Test%'

ORDER BY schema_name, table_name;

In above script Test is my ColumnName not sure exactly about column name therefore i have used like operator where columnName like '%Test'

It will display TableNames SchemaNames,ColumnName

--To Search a Column Name in all Views of a Database

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.views AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%Test%'

ORDER BY schema_name, table_name;

It will display ViewNames SchemaNames,ColumnName.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon in SQL.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The Reason for this error is when you are calling CTE Comman Table Expression Inside a Batch you have to start the cte with Semicolon otherwise it will throw the above error

To Demonstrate this i will create a table

CREATE TABLE [dbo].[Employee1_Errors](

[Empid] [int] IDENTITY(1,1) NOT NULL,

[EmpName] [nvarchar](50) NULL,

[EmpSalary] [float] NULL,

EmpDob datetime

) ON [PRIMARY]

insert into [Employee1_Errors] values ('vikas',2000,(getdate()-100))

insert into [Employee1_Errors] values ('vikas1',1000,(getdate()-200))

insert into [Employee1_Errors] values ('vikas2',3000,(getdate()-300))

insert into [Employee1_Errors] values ('vikas3',3000,(getdate()-400))

insert into [Employee1_Errors] values ('vikas4',3000,(getdate()-500))

select * from [Employee1_Errors]

Now we will display Result through cte where Salary greater than 2000 and Dateofbirth is less then current Date

declare @date datetime

set @date=GETDATE()

with cte as

(

select * from [Employee1_Errors]

)

select * from cte where empdob <@date and [EmpSalary]>=2000

Msg 319, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The Reason for this Error is we are using CTE in between the batch so in order to remove error we have to terminate previous Statement by semicolon or start CTE with semicolon

declare @date datetime

set @date=GETDATE()

;with cte as

(

select * from [Employee1_Errors]

)

select * from cte where empdob <@date and [EmpSalary]>=2000

This will Display the output without any Error

Note:It's always a good practise whenver you are using CTE always start it will Semi colon so that you can avoid these type of errors while working with CTE

Error Subreport could not be shown in SSRS

Error Subreport could not be shown in SSRS Issue Reason and Solution

Error SubReport could not be shown -The Main cause of this error is the SubReport Parameters Name have some difference with Main Report Parameters Name.

Below are points to Keep in Mind while Passing Parameter from Main Report to Sub Report.

1-The Name data type of Main Reports Parameter should be same as SubReport.

2-SSRS is Case Sensitive the error is Mainly caused when your CaseParameters of Main Report and Sub Reports are Different make sure not only the Parameter Name but also the case of Paramaters should be same.

one More Cause of this Issue which i faced I will explain you by example

I created a Main Report and Sub Report Passing Parameters from Main Report to Sub Report

with Same Name ,datatype and case also but still that error was there.

I Preview the Sub Report separetly it was working fine

for 2 hours i was not to resolve the issue when everything is fine.

Then again i checked my Main Report

In the Main Report I select my Sub Report

Right click-Properties -Parameters

I checked that i was manually writing the Main Reports Parameters Name instead of Selecting it from Parameters list

So I clicked on Fx

Select the Parameter from Parameter List Did the Same for all parameters

Preview the Report.

Finally i was able to Resolved the Issue.

3-So Keep in mind that instead of writing Parameters Name Manually it's better to Select Parameter Names from Parameters list.

Search This Blog

Calculation on Numbers Resulting it as a Integer values not in decimal in SQL

Concatenating Null values with a String or Number in SQL 2008 and 2012

Traversing in all Tables of Database in SQL

Result of one Table into Another table in SQL

Interactive Sorting in SSRS Reports

The Tablix is invalid Dataset property is missing or Table without Dataset in SSRS Report

Paging Issue in SSRS Report adds a question mark instead of displaying the total number of pages

Saving Change is not Permitted Error while Saving Changes in a table in SQL

Reseting Identity value to previous value or 1 after Delete command in SQL

Granting a user Select only Permission or other permissions in SQL

The Value expression for the text box ‘XYZ’ refers to the field ‘XYZ’. Report item expressions can only refer to fields within the current dataset scope Issue fixed in SSRS Report

Select All as default value for Multivalue parameter in SSRS

Order By Months Name in SQL and SSRS Report

First and Last Date of Current Month, Previous Month or Current Year in SQL

First and Last Date of Current Month or Current Year in Report Parameters in SSRS

SQL Server Product,Version,Edition and Other Information in SQL

Issue with SSRS Reports type charts Months as Horizontal axis in SSRS

Searching Column Names in Tables and Views in a Database in SQL

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Error Subreport could not be shown in SSRS Issue Reason and Solution