Archive for the ‘SQL’ Category


Some times while trying to contribute to your site you will get ‘Unexpected error’ or HResult error with some error code. If you investigate further by looking into the event viewer log, you will find that a SQL Server error has occured which states that the transaction log is full. Follow the below steps to truncate the transaction log.

  1. Open SQL Server Management Studio and locate your site’s content db(You can find it from the central admin site).
  2. Select your  db and right click to open the backup task.
  3. Select the Transaction Log to backup, go to options and select the radio button Truncate Log.
  4. Start the backup.
  5. That’s it. Transaction Log is truncated 🙂
Advertisements

I was working on a SSRS report having two dropdown controls State & District which behaves as a parent -child dropdown.

My table looks like

State1 District1
State1 District2
State1 District3
State1 District4
State2 District5
State2 District6
State2 District7


And when I select State from the first Drop Down, I need the District drop down to be populated with values

~~State1~~

District1

District2

District3

~~State2~~

District4

District5

District6

District7


This way user will come to know which state the district’s belong to.To do this I used the following query.


(select [State],District from dbo.T_TownSnapshot where state in (@State)

union

select [state], ‘~~’ + state + ‘~~’ as District from dbo.T_TownSnapshot where state in (@State))


CREATE PROCEDURE [dbo].[T_Town Snapshot_Call]
AS
BEGIN
DECLARE @FilePath   varchar(1000)
DECLARE @Filename   varchar(128)
DECLARE @path varchar(2000)
DECLARE @cmd varchar(7000)

SET @FilePath=’C:\Inetpub\wwwroot\CRITown\CRITownDatabase\’
SET @Filename=’TownSnapshotPackage.dtsx’
SET @path='”C:/Program Files (x86)/Microsoft SQL Server/90/DTS/Binn/”‘

SELECT @cmd = ‘C:\PROGRA~2\MICROS~2\90\DTS\BINN\DTExec.exe /F “‘ + @FilePath + @Filename + ‘” /CHECKPOINTING OFF’

EXEC master..xp_cmdshell  @cmd
END


Below is the query to get all the column names from your table.

SELECT column_name ‘Column Name’
FROM information_schema.columns
WHERE table_name =’table_name’

The query uses  “information_schema.columns”  to get the column details of the table.

There are also other information of your table like data type of column same as column_name which you can retrieve using this.