Monday, February 20, 2012

Single row result set is specified but no rows were returned

If you try to execute a SQL query in SSIS with the resultset as 'single row' and if your query doesn't return any records then you will get this error.

Solution

IF NOT EXISTS (Query Here)
SELECT ''
ELSE
Query Here

Saturday, February 18, 2012

How to configure SQL SERVER to accept remote connections?

STEP 1: Enabling TCP/IP

First we must tell SQL Server to listen on TCP/IP, to do this
perform the following steps:

1. Launch the SQL Server Configuration Manager from the "Microsoft SQL
Server 2008 R2 > Configuration Tools" Program menu
2. Click on the "Protocols for your SQL instance"
3. Right click on "TCP/IP" in the list of Protocols and choose, "Enable"

STEP 2: SQL Browser Service

Next, we have to determine if we want the SQL Browser service to be running or not. The benefit of having this service run is that users connecting remotely do not have to specify the port in the connection string.

STEP 3: Firewall..?

At this point you should be able to remotely connect. If you still
can't chances are you have a firewall configured on the computer where SQL
Server is running.

Make sure to add the port number you used for tcp/ip connection added to the exceptions list. The default port for SQL Server is 1433 unless you changed it to listen on another port

If you chose to use the SQL Browser service, you must also add
sqlbrowser service executable to the exceptions list as it listens on udp
port 1434.

Could not locate file 'FIelD' for database db in sys.database_files. The file either does not exist, or was dropped.

If you get the above error while truncating the transaction logs then either you haven't selected the proper database to run the script for truncating the logs or you haven't provided the correct logical log file name for the database.

Truncating Transaction Logs in SQL SERVER

Truncating transacation logs!!! Normally you need to find why your transactional logs are growing. The major reason would be that your transaction logs are not getting backed up.

First thing is to check your database recovery model and if you have it as 'FULL' and you deal with too many transactions within the day then start backing up your transaction logs otherwise change your recovery model to 'SIMPLE' (not suitable for databases dealing with large transactions)

Before you change the recovery model you need to truncate your logs.

USE [DatabaseName]
GO

ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE
GO

DBCC ShrinkFile('[Database log file Logical name]',100)
GO

Note: For Data Warehouse Databases, it is recommended to use 'SIMPLE' recovery model