Function containg DML script and It’s calling way in Oracle

suppose I have written a function that contain DML script.

create or replace function test_func(value integer)return integer

as

result integer;

begin

insert into test_table values(value);

return 1;

end test_func;

To get the return value if I call the function by using the query “select test_func(1)from dual” I get an error.

To get the return value I have to use another way

begin

dbms_output.put_line(test_func(1));

end;

For SQL Developer,DBMS output can be viewed in dbms output window. to get the window,go to view->Dbms output. Then select the schema name in what my function is created.

In my recent project, I need to create a Sql server agent job to run a batch file that runs a Sql server integration service package.

Suppose my package name is Test_Package.dtsx and it is situated at the file path D:\Projects\Packages\MyPackage.

To create a windows scheduler for running the package, I need to write a batch file in which the command will be :

“C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe” /FILE “D:\Projects\Packages\MyPackage\Test_Package.dtsx” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF >>TestLog.txt

Now my target is to run this batch file using a sql job. To do so,

• Open sql server.
• on object explorer tab, select sql server agent and expand the jobs.
• select new job.
• In general tab, named the job and select the category as uncategorized(local).
• Select steps tab and select new steps. Then name the step name, select type as Operating system(CmdExe). In the command open the batch file or write the command cmd.exe /c “D:\Projects\mybatch.cmd”. Here mybatch.cmd is the batch file and D:\Projects\mybatch.cmd is it’s path.

•     Then select OK and job is ready.

But in my project, I faced a problem. I created the job as Sql server Agent Service Account. The user of this service account has not the credential to access the database that is used in my package. So, the job failed and said login failure to access database. On the other hand, the user who has credential to access database has no credential to run the CmdExe.  To solve the problem I need to create a proxy and a credential.

To create a credential…….

• Expand Security and select credential and select new credential. then create the credential.

Then create the proxy……….

• Expand Sql server Agent and then expand Proxies.
• Name the proxy
• Select Credential
• Select the Subsystem.

Then select this Proxy as Run As when creating the job.

This has worked fine. 🙂

Script task to check whether a folder is empty or not.

In my recent project I need to know whether a folder contains any file or not. So, I write a script task that check: Is the folder empty or not?

Here is the C# code.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_9bc9e2c135c64f98b0c884c9c1698dad.csproj
{
[System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
String A;

if (File.Exists(Dts.Variables[“foldername”].Value.ToString()) == false)
{
A = “0”;
}
else
{
A = “1”;
}

Dts.TaskResult = (int)ScriptResults.Success;
MessageBox.Show(Dts.Variables[“foldername”].Value.ToString());
MessageBox.Show(A.ToString());

}
}
}

Here, foldername variable contains filepath. If the filepath is  valid then the script return 1 else 0.

Find the size of database using SQL command

In my recent project, I need to know the size of database. I used the following sql command to find the size:

SELECT DB_NAME(database_id), (size*8)/1024   FROM sys.master_files

WHERE DB_NAME(database_id) = ‘MyDb’

Here, MyDB is database name whose size I need to know. 🙂

Row and Page Level Compression using Sql Command

To do Row level compression, the command is

ALTER TABLE Table_Name REBUILD
WITH (DATA_COMPRESSION = ROW)

To do Page level compression, the command is

ALTER TABLE Table_Name REBUILD
WITH (DATA_COMPRESSION =PAGE)

Row and Page Level Compression in SQL Server 2008

Real World DBA

A new type of data compression was introduced in SQL Server 2008, available in Enterprise and Datacenter editions.  Compression yields performance gains, as both storage and memory requirements are reduced due to the data being compressed both on disk on in the data cache.    I/O is reduced by there being less data to actually have to read; however, this benefit comes at extra CPU overhead, but the net gain should be an overall performance boost.

What types of objects can be compressed?  Tables, nonclustered indexes, and indexed views.

There are two basic types of compression in SQL Server 2008:

Row-Level Compression:  not exactly true data compression.  Savings are achieved here by employing a more efficient storage format for fixed-length datatypes.   For example, the int datatype requires 4 bytes of storage, regardless of how much data is actually stored in the field, NULLs included.  Row-level compression allows the usage of…

View original post 320 more words

Shrinking Transaction log

A transaction log is a history of actions executed by a database management system to guarantee ACID properties over crashes or hardware failures. Physically, a log is a file of updates done to the database, stored in stable storage.

A transaction log file contains unused space that you will not be needing, you can reclaim the excess space by reducing the size of the transaction log. This process is known as shrinking the log file.

There are two way to shrink transaction log.

• Using Object Explorer in SQL Server Management Studio.
• Transact-SQL command.

Using Object Explorer in SQL Server Management Studio:

• Right-click the database, choose properties, then options.
• Make sure “Recovery model” is set to “Simple”, not “Full”
• Click Ok
• Right-click the database again, choose tasks -> shrink files
• Change file type to “log”
• Click ok.

Transact-SQL Command:

 ALTER DATABASE mydatabase SET RECOVERY SIMPLE
 DBCC SHRINKFILE (mydatabase_Log, 1)

Windows Scheduler for running a SSIS Package.

In my latest project I need to create a  scheduler task for running a ssis package. Suppose my package name is Test_Package.dtsx and it is situated at the file path D:\Projects\Packages\MyPackage.

To create a windows scheduler for running the package, I need to write a batch file in which the command will be :

“C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe” /FILE “D:\Projects\Packages\MyPackage\Test_Package.dtsx” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF >>Testlog.txt

Here, Testlog.txt contains log information.

The batch file extention would be .cmd or .bat.

Run Task Scheduler and select create task. Then Create Task wizard will open. In general tab named the scheduler. Then select trigger tab and and click on new. Then schedule the task about when the task will run. If you need to repeat the task in a fixed interval,then do your change on advance setting. Then select Action tab and select the batch file you want to run. By doing these hope you will be able to create a scheduler task. 🙂 🙂

Related articles

• Introduction to SQL Server Integration Services (SSIS) (sqlserverjunction.wordpress.com)

Load flat file data when the file name is not Fixed

In my recent project I need to load data from flat file but the file name is not fixed. To create a flat file connection, we need to define a file path with file name. But when the file name is not fixed then an error will occur that The file is not found in the file path. To solve the problem, I have used file system task to rename the file. I just use the two variables named file_name and renamed_file_name.I set my desired file name that is used in flat file connection into renamed_file_name. Before loading the flat file I renamed the file     name with renamed_file_name. Then load the flat file data. Then again renamed the file name with the previous name and did the another task. 🙂 🙂

SQL LOADER command help

SQL LOADER command help