DTSX failed to execute when invoked from web application ASP.Net

Hi there...

Usually i use to have a console application to execute a DTSX file(SQL Server Integration Service - Business Intelligence application) And schedule it to run for automated execution.
(http://mohammedfauzi.blogspot.com/2009/10/how-to-run-dtsx-programmatically.html)

This time the requirement was to run another DTSX file on demand by end user. So we decided to go for a web application.

I made use of the same code which i used in previous post on submit click event here. Earlier i was getting permission related errors. The issue was solved when i gave permission for the respective user on whose credential the request is executed as anonymous user. When this issue was fixed, The other issue came was that the "Execution Failed" When the web page was executed from outside. Apparently it was working great when executed from Visual studio! Still the permission related issue was there...

After lots of Googling & trials found the solution!!!

Solution:
you need to the following:
1. In IIS, Right Click the Project -> Properties
2. Click on ASP.NET tab
3. Click 'Edit Configuration' button
4. Click 'Application' Tab
5. Under Identity Settings -> Click the Checkbox 'Local Impersonation'
6. Give the user name & password for local user of the machine(who has access to run the DTSX applicaiton)

Thats it... now when you run the web application it works fine :)

Hope It Helps...

Regards
Fauzi

xp_sendmail: Procedure expects parameter @user, which was not supplied.

Hello...

When you try to send E-mail from your SQL Server 2005 T-SQL with xp_sendmail, there will be an error message shown as follows:

xp_sendmail: Procedure expects parameter @user, which was not supplied.

And probably when you try to pass @user also, it will say invalid parameter.

It seems in SQL Server 2005, xp_sendmail is obsolete and we can use sp_send_dbmail instead.

Following is the syntax:

EXEC msdb.dbo.sp_send_dbmail
@recipients='vm.mohammedfauzi@gmail.com',
@subject = 'SQL:DataBase Mail Test',
@body = 'Hello Test From SQL.Hello Test From SQL.',
@profile_name = 'The Profile you created in Configure Database Mail',
@body_format = 'HTML'

The following link has screen shots to setup Email Profile in SQL Server : Click Here

Hope it helps

Fauzi

SQL: comparision of SQL Server Databases 2005

hi,

Here is the comparisons of different types of SQL Server 2005 databases in terms of Scalability and Performance, High Availability, Manageability, Security, Programmability, Integration and Interoperability,Business Intelligence

Resource : http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

Hope it helps

Regards
4Z ~ Fauzi

SQL Server: Linked Server

Hi...

Seems in SQL Server 2005 there is a way to link to other Databases like Oracle,DB2 for extracting data through LinkedServer.

Once you add them as a Linked server, later you can write queries like how you use to write in SQL Server.

Syntax:
EXEC sp_addlinkedserver

server='LINKEDDB2NAME',

@srvproduct='Microsoft OLE DB Provider for DB2',

@catalog='DB2',

@provider='DB2OLEDB',

@provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=SOME;Network Port=50000;Package Collection=admin;Default Schema=admin;'

To check:

SELECT * FROM LinkedServer.Catalog.Schema.Table

Related Links:
To know more on Linked Sever : http://msdn.microsoft.com/en-us/library/ms190479.aspx

Defining a DB2 as a linked server in SQL Server 2005: http://blogs.msdn.com/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx

Hope it helps...
Regards
Fauzi

SQL Server Schedule Job : Unable to cast object of type

Hi Folks...

One of my pal, when he was trying to create SQL Server Job to call a SSIS he got the following error;

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

When we googled for the same it was mentioned in lot of forums that the it worked fine once we install SQL Server 2005 service pack 2 (SP 2)

Reference: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e7c0e73e-8d4b-4624-a19e-352a69995f9e

Download: Microsoft SQL Server 2005 Service Pack 2

Regards
Fauzi ~ 4Z

How to setup Database from MDF file

Dear Reader,

I was writing a user manual for a client, A part of it is about setting up a database in SQL Server 2008 from .MDF file. Thought let me blog this simultaneously so that it could be useful for surfer who look for a way to setup a database in SQL Server.Necessary measures are taken to protect sensitive information :)

Following are the steps to be done to setup database in SQL Server 2008.

Step #1: Right click on Database, Select Attach…

Step #2: A window with title Attach Databases will be shown to add the database files. Click on Add button.

Step #3: A window with title Local Database files – Computer Name, will be displayed. Browse and select the respective .mdf file for which you need to add the Database.

(Click to view full screen)



















Step #4: The select database file with the .mdf life & the .ldf file will be loaded. Please refer Screen #48. There are two options either we can click OK to finish adding the Database or If you want to reset log file .ldf file, select and remove the second file as shown in screen #48 and click OK to add file Database with log file. When this activity is done the SQL Server automatically creates a .ldf file.

(Click to view full screen)



















Hope it helps...

Cheers :)
Fauzi

SQL Server: How to reduce LDF file size

Hi...

I use SQL Server 2005 in my development server and the Database files (.MDF & .LDF) were in C drive. As we were working on the development the LDF (Transaction log files) increased drastically.
So i thought its times to reduce it or later it might make the system slow or space crunch would be there.

The tried following options to reduce the LDF file:

1. Right click on Database -> Tasks -> Shrink -> Files -> Choose File type : Log > Selected Shrink action : Release unused space & later Reorganize pages, Both did not help as the log was more than 1 GB whihc is not so much because i have seen some people had it more 40 GB when log file was not monitored frequently.

2. Used DBCC shrinkfile some time before, which was also not helpful.

Then found from the forums & did the following which worked:

Steps:
1. Make the database offline.
2. Right click & did 'Detach...' for the respective database.
3. Went to the file system where the LDF file resides and moved to a safer place.
4. Then on databases folder (below the Server icon), did right click & clicked Attach.
5. In Attach database window, selected the database & attached it without LDF file. So that system automatically created a new one which is of very small size :)

Thus the issue was solved. I believe there are other options as well to reduce LDF files,
If you are aware of any other options feel free to write it down :)

[Update: July 12 2009]

Now there seems to be much more easier way to reduce LDF file :)
http://mohammedfauzi.blogspot.com/2009/07/moss-sharepointconfig-ldf-file-grows.html

Thanks
Fauzi

Solution for SSIS job : The package execution failed. The step failed.

Hi...

I created a dtsx package which will fetch data from Excel file and insert them into respective SQL Server table. When i run the package from SQL Server Business Intelligence Development studio it works fine. When i run package from Command prompt also it works fine. But when we create it as a SQL Server Agent Job, It says the following error.

The package execution failed. The step failed.

I found this article from Microsoft Support team for all scenarios while execution of Job failed.
http://support.microsoft.com/kb/918760

It was clear, in my case it was permission issue, So i kept looking for blogs on such case. Managed to find this blog which helped me to solve the issue.

Following are two points which i believe, did the trick for me...

1. Under SQL Server Configuration Manager:

* Right click SQL Server Agent(MSSQLSERVER)



* Click Properties

* Select radio button this Account, Gave the User Name & Password (the user name with which i have created the .dtsx package )



2. In Computer Management(Right click on my computer & Click Manage)

* Under Local Users and Groups
* Under Groups
* Right Click SQLSERVER2005SQLAGENTUSER$YourServerName$MSSQLSERVER
* In Properties add the user name with which i have created the .dtsx package



These steps solved my problem, Hope this should be helpful :)

Regards
Mohammed Fauzi

SQL Server 2005 Management studio missing after New Installation

Hey all...

I wannu blog this for a long time... I have faced this problem many times after installation. I have installed SQL Server 2005 Enterprise edition on Windows 2003 Server and SQL Server 2005 Developer edition on Windows XP professional edition.
In both the cases when I installed SQL server 2005, SQL Server Management Studio was missing after successful installation. The simple solution is you need to uninstall it and reinstall the setup again. When I goggled for it many of them suggest the same solution, still wondering what could be the cause? Any ways it works now :)

Note: Remember to delete all the left over folders after uninstalling the software before re-installation.

Regards
Fauzi

SQL SP without Cursors for Row by Row Operations

Hi...

As everyone now's we can use Cursors for row-by-row operations in SQL Stored procedures. The drawback of using this option is Performance. So to overcome the issue we can write a work around code to implement row-by-row operations in your SQL stored procedure. Following are the code written for this with appropriate comments to explain its operation.

BEGIN

-- Here we Initialize variables

DECLARE @iNextRowId int,@iCurrentRowId int,@iLoopControl int,@FundID int, @FundIndexId int

SELECT @iLoopControl = 1

SELECT @iNextRowId = MIN(ID)FROM Fund_Index_Relationship Where Fund_ID = @ID

-- Retrieve 1st row

SELECT @iCurrentRowId = ID,

@FundID = @ID,

@FundIndexId = FundIndex_ID

FROM Fund_Index_Relationship

WHERE ID = @iNextRowId AND Fund_ID = @ID,

-- Your Code Logic Starts

-- Code...

-- Your Code Logic End

-- Loop is started

WHILE @iLoopControl = 1

BEGIN

SELECT @iNextRowId = NULL

-- Getting next ID

SELECT @iNextRowId = MIN(ID)

FROM Fund_Index_Relationship

WHERE Fund_ID = @ID AND ID > @iCurrentRowId

-- check whether there is a ID next

IF ISNULL(@iNextRowId,0) = 0

BEGIN

BREAK

END

-- Your Code Logic Starts

-- Code...

-- Your Code Logic End

-- get the next row.

SELECT @iCurrentRowId = ID,

@FundID = @ID,

@FundIndexId = FundIndex_ID

FROM Fund_Index_Relationship

WHERE ID = @iNextRowId

END

END