How to create table with filestream column and Insert data

How to create table with filestream column and Insert data?

Filestream data type in SQL Server  can be used to store images,documents,etc., in database.

In this blog I will explain how to create table with filestream data type.

Before creating table with filestream  column,we have to enable filestream feature in SQL Server configuration manager.Follow “How to enable and configure Filestream in SQL SERVER 2008 / 2012

Create a table and adding filestream data:

1.Create Filestream enabled database.

Syntax:

CREATE DATABASE test
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

2.After you have a filestream enabled database.we now need to create a table and add data to it.

Syntax:

CREATE TABLE [dbo].[test](
   [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [Number] VARCHAR(20),
   [Description] VARCHAR(50),
   [Image] VARBINARY(MAX) FILESTREAM NULL
)

3.Insert values into table. In below example I am inserting image file in to the table. 

Syntax:

DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(Bulk  ‘C:\image\jellyfish.jpg’, SINGLE_BLOB ) AS y
— Insert the data to the table         
INSERT INTO test (ID,Number,Description, Image)
SELECT NEWID(), ‘MS1001′,’jellyfish’, @img

clip_image001[8]

4.To view the table with filestream included column

image

Advertisements

FILESTREAM feature is disabled

Restore database (or) SQL Server setup fails in script upgrade mode with below error
{
Msg 5591, Level 16, State 4, Line 2
FILESTREAM feature is disabled.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
}

Follow the steps in http://msdn.microsoft.com/en-us/library/cc645923.aspx and enable File stream, then restore the database.

If the upgrade for SQL Server is failing with error “ESTREAM feature is disabled” then enable file stream in configuration manager by following above article and start SQL Server by following steps mentioned in

 SQL Server2008/SQL Server2012: Script level upgrade for database ‘master’ failed  and run below statement to change file stream access level

sp_configure filestream_access_level, 2
RECONFIGURE with override

After you executed the above statement start the SQL Server normally.

TCP Provider: The semaphore timeout period has expired

TCP Provider: The semaphore timeout period has expired error from SQL Server agent and other applications at times.

1. Disable TCP Chimney.Refer KB:942861

2. If you are in windows 2003 Change the value of the processor affinity to match the number of processors in the system.Follow KB:892100

{
1.Click Start, click Run, type regedit, and then click OK.
2.Expand the following registry subkey:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\NDIS\Parameters
3.Right-click ProcessorAffinityMask, and then click Modify.
4.In the Value data box, type one of the following values, and then click OK:
◦If you have two processors, use the binary value 0b11, or hex value 0x3.
◦If you have three processors, use the binary value 0b111, or hex value 0x7.
◦If you have four processors, use the binary value 0b1111, or hex value 0xF.
5.Quit Registry Editor.
Note The 0x0 or 0xFFFFFFFF values are used to disable the ProcessorAffinityMask entry.
}

3. Check if priority boost is enabled for SQL Server. If yes disable it.

4. Make sure there is no working set trim and system wide memory pressure. You can use second query in significant part of sql server process memory has been paged out to identify and follow the same blog to fix it)

5. Check if paged pool and non-paged is empty. (Event ID:  2019  in event log)

6. If you see this problem in cluster make sure you have set the network priority of “private heart beat” network higher than the “public” network.Refer KB:258750

 

Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction.

An error occurred while recovering database. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction.

You might get below errors when you try to restore database from SAN snapshot and see a Orphan Msdtc transaction Spid -2

    Error

Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.

An error occurred while recovering database Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:12345). Fix MS DTC, and run recovery again.

    Resolution

Change the sp_configure option
sp_configure ‘in-doubt xact resolution’,2
–Presume abort. Any MS DTC in-doubt transactions are presumed to have aborted.
Go
DBCC dbrecover(‘Dbname’);

Addition details on above sp_configure option in http://msdn.microsoft.com/en-us/library/ms179586(v=sql.90).aspx

Note: Use above option with caution

SQL Server integration services fails to start.

If SQL Server integration services fails to start. Create a backup of DTExec.exe.config and MsDtsSrvr.exe.config file  in   “C:\Program Files\Microsoft SQL Server\90\DTS\Binn” and add  <generatePublisherEvidence enabled=”false”/> under runtime tag and the start the SSIS service.

 

 

Here is an example DTExec.exe.config file for SSIS 2005:
<configuration>
<startup>
<requiredRuntime version=”v2.0.50727″/>
</startup>
<runtime>
<generatePublisherEvidence enabled=”false”/>
</runtime>
</configuration>

 

Here is an example msdtssrvr.exe.config file for SSIS 2005:
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
  <runtime>
    <gcServer enabled=”true”/>
    <generatePublisherEvidence enabled=”false”/>
  </runtime>
  <system.diagnostics>
    <switches>
      <add name=”TraceClientConnections” value=”0″ />
      <add name=”TraceManagementCalls” value=”0″ />
      <add name=”ServerTraceSwitch” value=”0″ />
    </switches>
  </system.diagnostics>
</configuration>

More details about the above tab is mentioned in http://blogs.msdn.com/b/amolravande/archive/2008/07/20/startup-performance-disable-the-generatepublisherevidence-property.aspx

Query to find SQL Server CPU utilization 2012.

Query to find SQL Server CPU utilization 2012. This query will help you to find the CPU utilization of server and SQL. If like to know the steps to bring down the SQL Server CPU utilization follow http://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/

 	
	declare @ms_now bigint
	select @ms_now = ms_ticks from sys.dm_os_sys_info;
select top 15 record_id,
		dateadd(ms, -1 * (@ms_now - [timestamp]), GetDate()) as EventTime, 
		SQLProcessUtilization,
		SystemIdle,
		100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
	from (
		select 
			record.value('(./Record/@id)[1]', 'int') as record_id,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
			timestamp
		from (
			select timestamp, convert(xml, record) as record 
			from sys.dm_os_ring_buffers 
			where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			and record like '%<SystemHealth>%') as x
		) as y 
	order by record_id desc