Search This Blog

Loading...

Saturday, January 2, 2016

Finding user Id's and details of objects like, tables, procedures or functions in SQL server


As an active user of various forums and groups someone asked below questions on my SQL/MSBI Developer & Jobs Group.  

Issues asked by Users

Requirement 1

Can someone please help me to trace the user Id who created the objects like, tables, procedures and functions etc.... in SQL server?

Requirement 2

Can anyone help me for the below requirement
1) Need to get Deleted Stored Procedure’s name
2) Need to get Deleted Table name and function With User name and Timing

Solution is simple and same for all two above Requirement.

Fetching all this type of Info like who has created the objects (Table, Function, SP and view) in which database and on which date in SQL is simple. Just follow the below steps.

1. Right click on Your Server (SQL Server)

2. Click on Reports.

3. Click on standard Reports.

4. Click on Schema Change History as below.    

                                        
5. Finally you will get the complete Report who has created what object with what name as below.
                                                                         
In the above Report you will see I have created one SP, one Function one table with different users and so on. with different user (Sa and Rakesh).




Sunday, December 27, 2015

Use temp table inside Stored Procedure with Oledb Source in SSIS


It’s a common practise to use Temp tables inside SP’s to optimize the performance but when you will call the same SP in SSIS which is using temp table inside it you will get the below error.

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.

Error at Data Flow Task [SSIS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
 we will use the below Table script and SP to produce this Error then in the below I will show the steps to solve it.

Below is the test script of table
.
CREATE TABLE [dbo].[LKP_Countries](
       [Country] [varchar](100) NULL,
       [Code] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'INDIA', 91)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'SINGAPORE', 65)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'USA', 1)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'PAKISTAN', 92)
GO


Now we will create a Stored Procedure that will have a temp table inside it using the above table



create PROC  USP_COUNTRIES
@CODE VARCHAR(200)

AS
BEGIN

DECLARE @COUNTRYCODE VARCHAR(200)

SET @COUNTRYCODE=@CODE
      
SELECT * INTO #COUNTRIES FROM  [DBO].[LKP_COUNTRIES]
       IF @CODE=91
              SELECT * FROM #COUNTRIES WHERE CODE=@COUNTRYCODE
       ELSE
              SELECT * FROM #COUNTRIES

END

Next step we will open SSIS and drop an oledb connection into it

Will call the above SP in oledb destination and map the code parameter accordingly


Since SSIS does mapping in design time so if you will click on mapping you will get the below error with no column information available as below.

The component reported the following warnings:

Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.

Now click ok and try to run the package you will get the below error.


Solution:

Since SSIS does a mapping in design time so will generate a fake condition 1=0 or 1=2 in the SP which contains all the SP’s columns and help SSIS to generate the column list in the design time as below as highlighted.

ALTER PROC  USP_COUNTRIES
@CODE VARCHAR(200)

AS
BEGIN

DECLARE @COUNTRYCODE VARCHAR(200)

SET @COUNTRYCODE=@CODE
IF 1=0
BEGIN
SELECT  CONVERT(VARCHAR(200), NULL) AS CODE,
        CONVERT(VARCHAR(200), NULL) AS COUNTRY
END   
SELECT * INTO #COUNTRIES FROM  [DBO].[LKP_COUNTRIES]
       IF @CODE=91
              SELECT * FROM #COUNTRIES WHERE CODE=@COUNTRYCODE
       ELSE
              SELECT * FROM #COUNTRIES

END

Since we have only two columns in our select so we have added both the column name

Note : You have to add all the column names in the 1=0 condition which you want in your SSIS Package.

Once you have altered your SP Go to SSIS click on mapping now this time it will map all the columns


Add a Flat file destination Map the column names and Run your SSIS Package.

It will export all result to your destination.

Note:You can also use SET FMTONLY ON; above your sp to get the temp table column information but it has disadvantage that to get the meta data information it used to execute the SP five times.

Saturday, November 28, 2015

SSIS Error-Column "Name" cannot convert between unicode and non-unicode string data types.


Error-One of the most common error in SSIS is while exporting data from SQL to Excel destination 
as below.

TITLE: Package Validation Error
------------------------------

Package Validation Error
------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [Excel Destination [41]]: Column "Name" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SSIS.Pipeline]: "Excel Destination" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

Solution-To solve this issue we need to use a Data Conversion transformation in between oledb source and Excel destination and change the data type Unicode string [DT_WSTR].

Below is the complete Step by Step Solution

First we will create a table in the SQL and will insert some records into it to produce the above error

create table test_Unicode
(Id int identity(1,1),
 Name varchar(50)
 )

 Insert into test_Unicode values ('Rakesh'),('Mukesh'),('Nitin')

 Select * from test_Unicode


Now we will create a SSIS package that will have a Oledb Source which is connected to our test_Unicode table now we will take a excel destination and try to load data into excel destination.

You will see a cross mark in excel destination with a error showing as below.

Column "Name" cannot convert between unicode and non-unicode string data types.



Below are the steps that need to be followed to remove the above highlighted error.

1. Add a data conversion transformation between Source and destination.

2. Connect source to data conversion transformation.

3. Open data conversion add two column and change the data type to Unicode string [DT_WSTR] as in below screenshot

4.Click ok

5. Now connect data conversion to excel destination

6. Map the new column which we have converted to Unicode string [DT_WSTR] to excel destination columns as below screenshot


7. Click ok

8. Execute package

9. It will run successfully 

 Note:To fix this Issue in SQL table side, change the datatype to nvarchar instead of varchar then create your SSIS package to load table data into excel destination you won't face the above issue.

Sunday, October 25, 2015

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.


I was working in a scenario where I need to get distinct names order by some other column which was not a part of Select Query. Below I will show you how to fix this error if you will stuck with the same error.

The error message was as below.

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Below I will create a test environment to create the error and in next step I will show how to fix this above error.

--test table script

create table #TestOrder
(id int identity(1,1),OrderName varchar(100),Period varchar(100),Month int)

Insert into #TestOrder values('TV','Jan15',1)
Insert into #TestOrder values('WashingMachine','Feb15',2)
Insert into #TestOrder values('TV','Mar15',3)
Insert into #TestOrder values('Refrigrator','Apr15',4)
--Need Distinct of below two since we don't know the month
Insert into #TestOrder values('TV','2015',13)
Insert into #TestOrder values('TV','2015',14)

If you will analyse the above table you can notice that we have three main Column orderName Period and Month column.

OrderName-  Shows the order Name
Period-          MonthName along with the Year when the order was made
Month-         No. of Month for the order for eg-for Jan=1,for Feb=2 and so on…

Now if  you will see the last two records the Period is 2015 and Month is 13 and 14 which means for these two records it was not sure for which month this Product was ordered so they made period entry as 2015 and put a random entry as 13 and 14 for the Month.

Requirement-So, the Requirement was we wants to see a single column in the output which will be distinct and will be concatenation of Order Name and period order by Month. Since for the last two records the order is same so we will take distinct of it and keep it them in the top as below screenshot



 I tried the below query to take distinct of order and period name order by month I got the above error.

select distinct OrderName +'_'+Period as OrderPeriod  from #TestOrder
order by month

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Solution- So below code will give you distinct name order by a column Month which is not a part of Select query.

select  OrderName +'_'+Period as Order_Period  from #TestOrder
group by  OrderName +'_'+Period
order by min(Month)

Output as Required.



Note: Since we are using aggregate function in the order by so you must need to write group by before order by clause other wise it will throw the error. for eg as below

select  OrderName +'_'+Period as Order_Period  from #TestOrder
--group by  OrderName +'_'+Period
order by min(Month)

Msg 8120, Level 16, State 1, Line 1
Column '#TestOrder.OrderName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So the above query will solve your issue if in case you are stuck with some similar scenario where you want distinct record order by a column which is not a part of Select query.


If you have any other issue regarding SQL & BI where you are stuck write to me in the comment box I will try to help you out.

Sunday, September 27, 2015

Count number of each word in a string in SQL

Question asked by a user of SQL & MSBI Groups-I have a paragraph, I have taken paragraph as a string in sql then I want how many words are repeated, and how many times the words are repeated?

Eg-Ram name is Ram because of lord Ram since Ram Father is devoted to lord Ram.

Output should be words_Repeated Word_Count as below

Solution-I have written the below code that will count each alphabet in the string.Secondly it will also handle the comma and full Stop in the word for eg if a word contains comma or full stop it will remove the (Comma & full stop) and count it as a word as example below.
declare @str varchar(200) = 'Ram name is Ram because of lord Ram, since Ram Father is devoted to lord Ram.'
declare @temptable table(word varchar(10), word_count int, length int)
declare @value varchar(10)
declare @index int

while (LEN(@str) > 0)
begin
set @index = CHARINDEX(' ',@str,1)
if (@index = 0)
begin
set @value =Replace(@str,'.','')
if exists (select * from @temptable where word=@value)
update @temptable set word_count=word_count+1 where word=@value
else
insert into @temptable select @value,1,LEN(@value)
break;
end
else
set @value = LEFT(@str,charIndex(' ',@str,1))
begin
set @value=left(@str,charindex(' ',@str,1))
IF @value LIKE '%,%' OR @value LIKE '%.%'
      begin
        SET @VALUE=left(@value,len(@value)-1)
      end
end

if exists (select * from @temptable where word=@value)
update @temptable set word_count=word_count+1 where word=@value
else
insert into @temptable select @value,1,LEN(@value)

set @str= RIGHT(@str, Len(@str)-CharIndex(' ',@str,1))
end
select * from @temptable

Output


If you have any issue regarding SQL & BI where you are stuck write to me in the comment box I will try to help you out.
       

Monday, August 31, 2015

Negative and positive values in separate column in SQL


Issue:As an active user of various forums and groups someone asked below question on SQL Groups.             I found the question interesting so I am writing a post of it in my website.
Below is the question asked by user?

Hi friends
Column A
2000
-1000
4000
-3000
(Using SQL server)
Q/ I want the negative and positive values in separate column's.
Note ; without using case and string functions..
Please share the ans asap
Thanks

Solution: Even though there are lots of solutions of this Question,but below answer is simple and it's without using any Case statement.

--Test table code contain Positive and Negative values
create table #temp
(Numbers int)
insert into #temp values(-1),(2),(-3),(4),(4)

--Select * from #temp
--Output query
select Neg.Numbers as Negative ,Pos.Numbers as Positive from 
(Select * ,Row_Number() over(order by Numbers) Rownum from #temp
where numbers <0)Neg
Full outer join 
(Select * ,Row_Number() over(order by Numbers)Rownum from #temp
where numbers >=0)Pos
on Neg.Rownum=Pos.Rownum

Output 
Negative Positive
-3         2
-1         4
NULL 4

Note:If you want sum of all negative and sum of all positive values in different column then you can check my older post Sum of only Positive Values of a column and sum of negative value of a column in SQL-http://www.sqlandssrssolutions.com/2013/11/sum-of-only-positive-values-of-column.html 

Thursday, July 30, 2015

Export Data from SQL to Excel Sheet using SSIS

Export Data from SQL to Excel Sheet using SSIS

Exporting Data from SQL table to Excel is a simple process using SSIS.

Below are the Steps that needs to be followed

First create a table in your database using below SQL script.

CREATE TABLE [dbo].[Employee](
       [Empid] [int] IDENTITY(1,1) NOT NULL,
       [EmpName] [nvarchar](50) NULL,
       [EmpSalary] [float] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'Amit', 5000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'Sumit', 6000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'Raj', 8000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'vijay', 9000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'suresh', 10000)
SET IDENTITY_INSERT [dbo].[Employee] OFF


Requirement- we will export the Data of this sql table into excel file 

Open SSIS follow the below Step one by one.

Open BIDS(Visual Studio)

Click on New Project

Select Business Intelligence Project

Select Integration Service Project

Right click SSIS Packages

Create new SSIS Package

Give a name to the Package otherwise by default it will take Package1.dtsx

Control Flow

1-Go to Control flow(Left Side)

2-Drop a data flow Task

3-Name the Data flow Task as Exporting Excel Data (Optional ) as below Screenshot.


Data Flow Task

1-Now open the Data flow task by double clicking on it.

2-Drop  OlEDB Source

3-Drop Excel Destination as below Screenshot


Creating Connection for Source and Destination

1-Double Click OlEDB Source

2-Create a connection with your SQL Database as below screenshot.

3-Next we will create connection with excel Destination

4-Double click Excel Destination

5-Click on New(Excel Connection Manager)

6-Select the Path where you want to keep you file.

7-Give Name for the output file as ExcelData.xls

8-Now we will create sheet in the excel file.

9-Click on New(Name of Excelsheet)

10-It will automatically create a sheet for you with the name Excel_Destination

11-Once done click ok 


Next we will connect the Oledb Source with Excel Destination by dragging the blue arrow from Oledb source to Excel Destination

Once source and destination connected Run the Package by Right clicking the package and executing it.


Open Excel sheet to verify the Result. The data from Employee table has been exported to excel file as below screenshot.




Write to comment box if you are facing any issue while exporting the data from sql to excel file.