Tag Archives: ms sql server 2008

HTTP Connection Manager and Script task to download file via HTTP in SSIS 2008 with C# syntax

Download file via HTTP connection task would have lots of coding in .NET but in SSIS this task become much much easier with the help of “HTTP Connection Manager” , “HTTP Connection Manager” is one of the connection member of rich set of stock connection in SQL Server 2008 integration services.
Let us move ahead with creating package which can download the file from HTTP. Open new SSIS project.
To add “HTTP Connection Manager” in your package, right click on “Connection Manager” window and click on “New Connection”, from the “New Connection” dialog box, select “HTTP” and click on “Add” button.
For more detail, please look at below screen shot:
Once you insert “HTTP Connection Manager”, double click on it to configure. It will open dialog box editor, you have to give the path of file you wanted to download in “Server Settings” property.I wanted to download my blog header so I am giving path of the same which is as below.

for more detail, look at below screen shot.

 

Once you are done with setup of “HTTP Connection Manager”, drag “Script Task” from tool box and drop it to the “Control Flow” which is your design area. Double click on “Script Task” to configure it and click on “Edit Script” button to write down script.
In the MAIN()  method which is our Entry Point, I am going to have following script.
Microsoft.SqlServer.Dts.Runtime.HttpClientConnection httpConn;
            Object obj;
            try
            {
                obj = Dts.Connections[“HTTP Connection Manager”].AcquireConnection(null);
                httpConn = new HttpClientConnection(obj);
                httpConn.DownloadFile(“d:\\SQLHub.jpg”, true);
            }
            catch (Exception e)
            {
                Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message, “”, 0);
            }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
As soon as you copy the script there, save and close script editor and run your package by hitting F5. You will see file created at the destination path. In this case, I would found SQLHub.JPG in D drive of my system.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Generate SP or function definition in SQL Server 2005/2008

Well, today I would like to share one very quick and useful simple TSQL which can help you to generate script (definition) of stored procedure, trigger, functions etc. Generally you can do it with SSMS easily by right click on object and click on option like “Script Stored Procedure as->Create to->File”  or  “Script function as->Create to->File” etc. but if you want to generate script for many object at a time, there is one very small TSQL can come to your help which I myself used to use so many time.
Look at the TSQL below:
select so.name as ObjectName,sc.text as ObjectDefination from sysobjects so join syscomments sc on so.id=sc.id
–P for Stored Procedure
–FN for scalar function
–IF for Inlined table function
–TF for Table function
–TR for trigger
where so.type in (‘P’,‘FN’,‘IF’,‘TF’,‘TR’)
–if you want defination of specific function or SP, include below condition too
–and so.name=’YourSPorFunctionName’
Order by so.name,sc.colid
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

ERROR FIX: Alter failed for Login sa – Cannot set a credential for principal ‘sa’. (Microsoft SQL Server, Error: 15535)

Generally It is good practice to disable SA login in SQL Server and use SysAdmin account which is created by you to secure you SQL Server from few threats. Well, this is not a scope of this article to explain why you should disable it but I would like to share one quick tip when you face above error while enabling you SA account back.
If you see error like:
ERROR FIX: Alter failed for Login sa – Cannot set a credential for principal ‘sa’. (Microsoft SQL Server, Error: 15535)
While enabling your SA account, you have to enable one check box “Map to credential” from “General” table. Once you are done with that, go to “Status” table and make your user “Enable” with radio button under “Status” tab.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

TimeStamp datatype in SQL Server

Well TimeStamp datatype is one of the old datatype in SQL Server and going to deprecated in newer version so it is not advisable to use it. The main intention to write this article is, recently I have see few people discussing TimeStamp datatype with misconception in one of the forum.
I wonder people got this datatype wrong by understanding it as a real data or time datatype. Actually TimeStamp datatype has nothing to do with Date or time in SQL Server.
As per MSDN, TimeStamp is
timestamp is a  data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.”
Let us see one small example which proves above statement.
create table TimeStampTesting
(
Name varchar(10),
TS TimeStamp
)
Insert Into TimeStampTesting(Name)
Select ‘Ritesh’ union all
Select ‘Rajan’ union all
Select ‘Bihag’
GO
–since we are making order by on TS
–Bihag should be first as that record was inserted last
Select * from TimeStampTesting order by TS desc
Go
Update TimeStampTesting set Name=‘Rajan S.’ where Name=‘Rajan’
GO
–if you observe, this time Bihag wouldn’t first
–but Rajan S. would be the first as it updated last
–so TS is a binary unique number which updates itself automatically
–for new upate and/or insert
Select * from TimeStampTesting order by TS desc
Go
BTW, now a day, you should use RowVersion datatype rather than TimeStamp as I told you above too that TimeStamp will be deprecated and RowVersion is synonyms for TimeStamp. For more information, look at the below URL:
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Error Fix: Msg 8101 An explicit value for the identity column in table ‘idIn_copy’ can only be specified when a column list is used and IDENTITY_INSERT is ON

This is really very well known and famous error when you are trying to insert value in identity column.  If you want to get rid of this error, you have to set “Identity_Insert” property to ON so that you can manually insert Identity value.
Let us see one small example.
–Source table
create table idIn
(
id int identity(1,1),
name varchar(10)
)
go
–destination table
create table idIn_copy
(
id int identity(1,1),
name varchar(10)
)
GO
–insert data into source table
insert into idIn
select ‘ritesh’ union all
select ‘rajan’
GO
–setting Identity_insert property ON
SET IDENTITY_INSERT idIn_copy ON
GO
–insert from source table to destination
insert into idIn_copy
select * from idIn
go
–setting Identity_Insert property to OFF
SET IDENTITY_INSERT idIn_copy OFF
go
One you will run INSERT query after setting IDENTITY_INSERT on, you will get the same error J
You have to give field list in INSERT statement, once you will give it, you will be ok. Have a look:
–setting Identity_insert property ON
SET IDENTITY_INSERT idIn_copy ON
GO
–insert from source table to destination
insert into idIn_copy(id,name)
select id,name from idIn
go
–setting Identity_Insert property to OFF
SET IDENTITY_INSERT idIn_copy OFF
go
This test is perform on SQL Server 2008 but expect to have same result in SQL Server 2005 too.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Save sub report as from main crystal report

Recently I was working in one of the very big windows application which has been developed with the following tools, framework and software.
1.)    Visual Studio 2008
2.)    .Net Framework 3.5
3.)    C#
4.)    SQL Server 2005
5.)    Crystal Report 11
There are lots of crystal reports used in that application. While working on that application (product) I needed to create on very big crystal report. Fortunately or unfortunately the same kind of report was a part of (as a sub report) one main report. If I can extract that sub report from main reports, I could probably save my so many hours.
Basically we used to create a simple report and import that report as a part of sub report in other main reports. Unfortunately main report was deleted by somebody so the only way was to extract report from main report as that report was working fine under main report.
I have opened that main report which has a link to sub report, I right click on that sub report link to get option “Save Sub report as” in visual studio but no luck.  Look at the image below.

Actually visual studio doesn’t have that option so I have seen many programmers start looking at sub report and create new report based on that which kills few hours if report is big.
So what is the solution? If you are having full licensed version of crystal report than open crystal report viewer, open same report in that and you will get an option to “Save sub report as” look at image below.

Isn’t it easy and time saving stuff?
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Deadlocks in SQL Server 2008

There are few different kinds of lock available in SQL Server but the most resource consuming especially CPU power is DeadLock. It is better to know about deadlock and try to reduce as much as possible. First of all let us understand what is deadlock and how deadlock happens?
Deadlock happens when two or more processes waiting for the resource and wait for other process to finish and eventually neither of them ever does it at that time SQL Server select one process and abort other process.
Let us see one small example to create dead lock.
–create one two tables for demo and insert dummy records.
create table DL1
(
id int,
name varchar(10)
)
go
create table DL2
(
id int,
name varchar(10)
)
go
insert into DL1
select 1,‘ritesh’ union all
select 2,‘rajan’
go
insert into DL2
select 1,‘vipul’ union all
select 2,‘darshan’
go
–open other query window, will consider this window as A1
begin tran
update dl1 set name=‘rit’ where id=1
go
–open other query window, will consider this window as A2
begin tran
update dl2 set name=‘vi’ where id=2
go
–again come back to window A1
update dl2 set name=‘d’ where id=2
go
Now this statement will create lock and query got hold but it is not a dead lock so far.
–now comeback to A2 window and execute following command
update dl1 set name=‘raj’ where id=1
go
Now, this statement creates dead lock and you will be greeted with the error message something like below and your
Msg 1205, Level 13, State 45, Line 2
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
As soon as you execute COMMIT or ROLLBACK, your lock gets release. So, this is how deadlock generates so while designing the database, keep relatively normalize. Keep your transaction as short as possible and try to use TRY….CATCH block so if there is any error in your TRY block, it transfer the focuses to CATCH block and you can ROLLBACK transaction from there.
BTW, if you want to find tables which are being locked, have a look at my article here.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find Schema Name, row size in bytes and Total Number of rows in table for all tables in SQL Server 2008

System catalog and views provided inbuilt with SQL Server, are really wonderful, very useful and handy to get some inside information immediately. Today I want to show you use of following different system catalog and views.
SysObjects: used to show you list of all objects in database
SysColumns: used to show you list of all columns of all tables
Information_Schema.Columns:  also going to show you the list of columns for all tables, some information are easy to access other than SysColumns but note that SysColumns is powerful than this one
Sys.Partitions: Generally tables and indexes are in at least one partition in SQL Server 2008 so I use it as a handy tool to get total number of rows in each table.

Now, here I present one very small code snippet which will show you Schema Name, Table Name, Total Maximuz size of row in table and total number of rows exits in table. I have used above introduced system catalog to get these information, You may have different combination to get this kind of information.
select
      isc.TABLE_SCHEMA as SchemaName,
      so.name as TableName,
      SUM(sc.length) AS RowSizeInBytes,
      sp.rows as TotRowsInTable
from
      sysobjects so join
      syscolumns sc on so.name = OBJECT_NAME(sc.id) join
      INFORMATION_SCHEMA.COLUMNS isc on sc.name=isc.COLUMN_NAME and so.name=isc.TABLE_NAME join
      sys.partitions sp on OBJECT_NAME(sp.object_id)=so.name
where
      so.type = ‘U’
GROUP BY
      so.name, isc.TABLE_SCHEMA,sp.rows;
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Restart SQL Server and SQL Server Agent from Command Prompt.

Many times I need to restart SQL Server service and SQL Server Agent service in my testing server.. Generally people used to go at Control Panel->Administrative Tools->Services, find SQL Server service or/and SQL Server agent service and start, restart or stop it. This is general practice but I would like to do this from command prompt itself.

Syntax is:

Net Stop mssql$YourSQLServerInstanceName
Net Start mssql$YourSQLServerInstanceName
Above command is used to start and stop SQL Server Service.  Now, here is the command for SQL Server agent.
Net Stop sqlagent$YourSQLServerInstanceName
Net Start sqlagent $YourSQLServerInstanceName
My instance name of SQL Server 2008 is “SQL2k8” so my command would be
Net Stop mssql$SQL2K8
Net Start mssql$SQL2K8
Net Stop sqlagent$SQL2K8
Net Start sqlagent $SQL2K8
Indeed I have one batch file which do this stuff for me. Just go to command prompt and run .BAT file is really easy than going to Control Panel->Administrative Tools->Services, find services and restart it.
Look at the screen below for more details.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find out column name, data type and maximum number of character in that column in SQL Server 2008

Yesterday I got one question about how to find column name, data_type and maximum number of character used in that column if it is varchar and if it is numeric than maximum number in that column. Basically you can find column_name and Data_type from the Information_Schema.Columns but it won’t return length of real data inserted into table so I tried to achieve it logically. Look at the script.
–using AdventureWorks database for demo
–if you don’t have AdventureWorks db
–you can use your own
use adventureworks
GO
declare @sql varchar(max), @table sysname
–going to show you demo on Person.Contact table
–in adventureworks database,
–you can change your own table name
select @table=‘Contact’, @sql=
select @sql=
case when character_maximum_length is null then
@sql+‘select
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(‘+column_name+‘) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
else
    @sql+‘select
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(len(‘+column_name+‘)) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
end
from information_schema.columns
where table_name=+@table+ and  data_type not in (‘xml’,‘bit’,‘datetime’,‘uniqueidentifier’)
set @sql=left(@sql,len(@sql)-9)
–print @sql
exec(@sql)
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah