How to get only DATE portion out of DATETIME column in MSSQL?

Challenge:

One of my colleague was writing a query to get records between start date and enddate.

SELECT EmployeeName From Employees WHERE EmployeeJoiningDate >= @startDate AND EmployeeJoiningDate<=@endDate

it was working fine but some records are not showing up in the list because it was checking time also. Which he don’t want to compare..So, how to get just date part and compare it with provided params?

Solution:

We found the way for it using

SELECT CONVERT(VARCHAR(10),GETDATE(),101) --101 = mm/dd/yyyy

it made his work simple!!. So, just convert your date and then compare..for brevity i left up to you to write a new sql statement

Here 101 is one of the style to get date. for list of style go to MSDN

Happy Programming!

Checking Execution Time of Stored Procedure

Hi All…I was invisible since so long..:)…But was too busy with my stuff so can’t post anything here…But today I learnt something new which I would like to share with you all…

Challenge:

I have written one Stored Procedure with 958 Lines..which deletes data from around >70 tables. [Ooh..]. But when I was executing it I was eager to check its execution time. And I found it..So here is the way to check execution time of your stored procedure.

Solution:

DECLARE @startproc DATETIME
DECLARE @endproc DATETIME
DECLARE @time INTEGER 

SELECT @startproc = getdate()  --take start time 

--execute sp

EXEC <YOUR SP NAME> <PARAMETERS> 

SELECT @endproc = getdate()  --take end time 

SELECT @time = DATEDIFF(millisecond, @startproc, @endproc)  --take difference in milliseconds.

PRINT str(@time) + ' Milliseconds.'

I think the way which I did is self explanatory.

The main function which I would like to elaborate further is DATADIFF : To get difference between two dates:

Here i have used millisecond as an argument you can put DAY,MONTH YEAR etc. [Beware!!! If your SP Is taking DAY/MONTH/YEAR to run then you need to think for that SP...:) Just kidding].

Here is the Link for more arguments of DATEDIFF function :http://msdn.microsoft.com/en-us/library/ms189794.aspx

Fetching value from XML parameter in SP-SQL SERVER 2005

XML Document as a parameter

[sourcecode language='xml']

DECLARE @xmlParam as XML

SET @xmlParam =

‘<Root>

<List>

<ID>1</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>2</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>3</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>13</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>14</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>15</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>16</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>17</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

</Root>’

[/sourcecode]

SQL Syntax for retrieving values:

SELECT

–get the ID

ParamValues.Data.value(‘ID[1]‘,’bigint’) as ID,

–get the Cost

ParamValues.Data.value(‘Cost[1]‘,’decimal(10,2)’) as Cost,

ParamValues.Data.value(‘Active[1]‘,’bit’) as Active –Active

FROM

–get the data from entityfields using Xquery

@xmlParam.nodes(‘Root/List’) as ParamValues(Data)

Output

Updated on 10-Oct-2008

At a time of Update…How to fetch values??

DECLARE @employeesData as XML

SET @employeesData =
‘<Employees>
<Employee>
<EmployeeID>1</EmployeeID>
<EmployeeName>ABC</EmployeeName>
</Employee>
<Employee>
<EmployeeID>2</EmployeeID>
<EmployeeName>XYZ</EmployeeName>
</Employee>
</Employees>’

UPDATE Employees
SET [EmployeeName] = ParamValues.Data.value(‘EmployeeName[1]‘,’varchar(50)’)
FROM @employeesData .nodes(‘/Employees/Employee’) as ParamValues(Data)
WHERE EmployeeID IN (ParamValues.Data.value(‘EmployeeID[1]‘,’bigint’))

Hope it helps some one…..runing out of time..

EXECUTE permission denied on object ‘ABC’, database ‘XYZ’, schema ‘dbo’.

Error:

EXECUTE permission denied on object ‘usp_GetEmployeeDetails’, database ‘Employee’, schema ‘dbo’.

Solution:

1. Expand your database Node. For me it’s “Employee”.
2. Underneath of it Expand “Security” Node.
3. Click on the user which you are using for Database operations. By default it is

MACHINENAME\ASPNET [MYMACHINE\ASPNET].

1. It will open the window as shown below from database Roles Select db_owner:

That’s it…

How to Test Stored Procedure which has OUTPUT Parameter as an argument?

you have created on SP which takes bunch of args and one of it is OUTPUT parameter which gives ID of last inserted record.. you want to test it using Management studio how can you do it??

Here is the steps :

DECLARE @givemebackID bigint –it can be your parameter name[not compulsory]

exec usp_AddEmployee ‘ABC’,'USA’,@givemebackID= @givemebackID OUTPUT

SELECT @givemebackID

Execute it and enjoy!!!

tinyint+invalid cast exception with DataReader

today i had faced a wired error in my DAL it was throwing an error invalid cast my code is like this:

int empid = reader.GetInt32(“EMPID”); //throws invalid cast..

i checked it with my database schema EMPID is tinyint. which is main cause of an error..

Solution:

I had done R&D and come in to know that:

SQL SERVER Stored tinyint as 8bit[1byte] .so we can’t fetch it using getint32…so to fetch it i had changed my code to:

 int empid = Convert.ToInt32(reader.GetBytes(“EMPID”)); //worked 

Generate Scripts from Database+SQL SERVER 2005

Hi.. yesterday one of my colleague has asked me that how can i generate a script from Database with A Relationship,Tables and all the stuff….and i have shown it may you need it tomorrow..

Scenario/Problem:

Want to generate a Script from  Database with all the Relationship,Tables…etc..

Solution:

Just follow me:

1. I guess that you have opened Management studio and ready with Database.

2. just Right click on your database.

3. Will popup a  context menu. Select “Script Database as”->”Create to->New Query Editor Window [you can select anyone of it nothing strict in it ].

It will generate a Script for Creating a Database. Now let’s see how can we create Script for Tables and all the stuff

4. Now, Right click on Database->Tasks->Generate Scripts.

5.follow a Wizard..[By clicking Next..Next] .

5.1. select database

5.2.Next

5.3. select Object types you need [Table,SP,Views]

5.4 Choose SP [better do Select All].

5.5. Choose Tables.

5.6. Select Script mode..which is simple..so keep it on you for braveity.

5.7 Now make one file of both of this scripts and make it one [TOTAL SCRIPT=DATABSE SCRIPT+TABLE,SP,VIEWS SCRIPT]

5.8 That’s it now distribute. it to your client.. and enjoy!!!

6. How to generate  a database from Script file which was earlier generated.

6.1 Just open generated file in new query editor window.

6.2 Press F5 [to run script] .

6.3 That’s it.

you will have Database with all the stuff..

Happy Database Scripting!!!

-Kiran Patil

Cannot Create Database Diagram with SQL Server 2005

Cannot Create Database Diagram with SQL Server 2005

Today one of my associate has faced one problem: He wants to create Database Diagram in SQL SERVER Management Studio. But he is getting the error like shown as below.

TITLE: Microsoft SQL Server Management Studio

——————————

Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

——————————

BUTTONS:

OK

He asked me to find the solution and I found it. So I am putting it here. So you don’t have to struggle for it.

SOLUTION:

1. Just copy below SP and copy it to Management studio

EXEC sp_dbcmptlevel ‘<Dbse name>’, ‘90′;

go

ALTER AUTHORIZATION ON DATABASE:: <Dbse name>TO “<USER Name>“

go

use [<Dbse name>]

go

EXECUTE AS USER = N‘dbo’ REVERT

Go

1. Now just change <Dbse name> to your Database name for which you are facing problem and <USER Name> with your username of SQL.
2. Now Try it!!! And Enjoy

Happy Database Diagramming

-Kiran