Skip to main content

SQL Server Related Query

HOW TO MODIFY A COLUMN SIZE, IF WE HAVE MULTIPLE TABLES WITH DIFFERENT SUFFIX AND THAY ALL CONTAINS SAME COLUMN DEFINITIONS, I NEED TO MODIFY A SPECIFIC COLUMN'S SIZE OF ALL TABLES USING ONE SCRIPT. 


AS LIKE MENTIONED BELOW:

UPDATE 

INFORMATION_SCHEMA.COLUMNS

SET 

CHARACTER_MAXIMUM_LENGTH = 100,

CHARACTER_OCTET_LENGTH = 100

WHERE 

TABLE_NAME LIKE '__MYTABLENAME'

AND 

COLUMN_NAME = 'MYCOLUMNNAME'

AND 

CHARACTER_MAXIMUM_LENGTH < 100

----------------------------------------------------------------------------------------------------------------------------------

Using OPENROWSET to read large files into SQL Server


Problem
OPENROWSET is a T-SQL function that allows for reading data from many sources including using the SQL Server's BULK import capability. One of the useful features of the BULK provider is its ability to read individual files from the file system into SQL Server, such as loading a data from a text file or a Word document into a SQL Server table. This capability is the subject of this tip.
Solution
The BULK option was added to T-SQL in SQL Server 2005 and it persists in SQL Server 2008. When using SQL Server 2000 it was possible to read and write to the file system using the sp_OA_Create and sp_OA_Method extended stored procedures. These XPs continue to work, but are disabled by default because of security concerns and it is a better practice to use more secure capabilities like OPENROWSET when they are available.
When used with the BULK provider keyword you can name a data file to read as one of three types of objects:
  • SINGLE_BLOB, which reads a file as varbinary(max)
  • SINGLE_CLOB, which reads a file as varchar(max)
  • SINGLE_NCLOB, which reads a file as nvarchar(max)
OPENROWSET returns a single column, named BulkColumn, as its result. Here's an example that reads a text file:
SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\temp\mytxtfile.txt', SINGLE_CLOB) MyFile
The correlation name, in this case MyFile, is required by OPENROWSET.
There are additional requirements when reading single files that must also be observed as mentioned below.
Access control is always a concern. The operating system level file operations to read the file are executed with the privileges of the account that the SQL Server data engine is using. Therefore, only files accessible to that account may be read. This includes network drives or UNC paths, which are permitted if the account has the privileges. If you want to read network files, run SQL Server as a domain user.
The BULK provider won't convert between Unicode and plain ASCII files. It must be told which type of encoding is used in the file. If you don't the result is error 4806 as seen here:
SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_CLOB) MyFile
Msg 4806, Level 16, State 1, Line 1
SINGLE_CLOB requires a double-byte character set (DBCS) (char) input file. The file specified is Unicode.
Unicode files must be read with the SINGLE_NCLOB option shown here:
SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_NCLOB) MyFile
Similarly, files with non text structures, such as Word documents are not converted. They must be converted by some other mechanism before being read or they can be read as binary files with the SINGLE_BLOB option.
OPENROWSET isn't flexible about how you provide the name of the file. It must be a string constant. That requirement forces the use of dynamic SQL when the file name isn't known in advance.
Here's a stored procedure that reads any text file and returns the contents as an output variable:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO 
CREATE PROC [dbo].[ns_txt_file_read]  
    
@os_file_name NVARCHAR(256
   ,
@text_file VARCHAR(MAXOUTPUT  /* Reads a text file into @text_file 

* Transactions: may be in a transaction but is not affected 
* by the transaction. 

* Error Handling: Errors are not trapped and are thrown to 
* the caller. 

* Example: 
    declare @t varchar(max) 
    exec ns_txt_file_read 'c:\temp\SampleTextDoc.txt', @t output 
    select @t as [SampleTextDoc.txt] 

* History: 
* WHEN       WHO        WHAT 
* ---------- ---------- --------------------------------------- 
* 2007-02-06 anovick    Initial coding 
**************************************************************/  
AS  
DECLARE 
@sql NVARCHAR(MAX
      , 
@parmsdeclare NVARCHAR(4000)  
SET NOCOUNT ON  

SET 
@sql 'select @text_file=(select * from openrowset ( 
           bulk ''' 
@os_file_name ''' 
           ,SINGLE_CLOB) x 
           )' 
SET @parmsdeclare '@text_file varchar(max) OUTPUT'  
EXEC sp_executesql @stmt @sql 
                 
@params @parmsdeclare 
                 
@text_file @text_file OUTPUT 
To see how it works, just execute the example script:   First create a text file called "SampleTextDoc.txt" and add some text data to the file.  For our example we added the following text "The quick brown fox jumped over the lazy dog.".
DECLARE @t VARCHAR(MAXEXEC ns_txt_file_read 'c:\temp\SampleTextDoc.txt'@t output SELECT @t AS [SampleTextDoc.txt] 
The results are:
SampleTextDoc.txt
The quick brown fox jumped over the lazy dog.
(1 row(s) affected)
The performance of reading text files is remarkably fast because the files are read sequentially. Using a 64 bit SQL Server 2008 on a development machine, reading a file of 750,000,000 bytes took only 7 seconds.

Comments

Popular posts from this blog

Problem: Date Formatting cannot be Changed in Microsoft Excel

In this article, we will learn how to change the date formatting. We will use “Text to Column” wizard to resolve the problem of change the date formatting in Microsoft Excel. Let’s understand the functions: - Text to Column:  “Text to Column” is used for separating the cell content which is depending on the way your data is arranged. You can divide the data on the basis of content in the cell such as space, comma, period, semicolon, etc. Let’s take an example and understand how we can convert the date into Text. We have dates, foramatted as text in column A. Now, we want to convert it into date format.     If we want to convert the formatting into numbers, then we need to follow below given steps:- Select the range A2:A11. Go to Data tab, and click on Text to Columns from the Data tools group.     Covert Text to Columns Wizard – Step1 of 3 dialog box will appear. Select fixed width, and click on Next button.     Skip step-2, and...

Now You Can Use Reliance Jio 4G Services On 2G And 3G Smartphones

Indians will always be at the top in availing any free internet facility. As, Reliance Jio aims to offer free 4G internet to the 90% of Indians, hence, with one of its services now you can use Jio 4G services on 2G and 3G smartphones. Now You Can Use Reliance Jio 4G Services On 2G And 3G Smartphones Who doesn’t want the free internet? Of course, we all want, Indians will always be at the top in availing any free internet facility. Jio, which is also known as Reliance Jio and officially as Reliance Jio Infocomm Limited has already given its users free unlimited 4G data for 90 days. As the Reliance Jio aims to offer free 4G internet to the 90% of Indians along with the free voice calls and messaging services. So, we all must agree that Indians are always at the peak when it comes about available any internet facility. We all know Jio, which is also known as Reliance Jio Infocomm Limited has previously given its users free unlimited 4G data for 90 days. Not only that but even th...

Apple’s Lisa operating system to be released for free in 2018

Apple’s Lisa operating system to be released for free in 2018 The Computer History Museum in California has planned to release Apple’s legendary Lisa operating system (OS) for free as open source this year, the media reported. The Mountain View-based museum announced that the source code for Lisa, Apple’s computer that predated the Mac, has been recovered and was being reviewed by the tech giant itself, reports 9to5Mac. Lisa, released in 1983, was one of the first personal computers to come equipped with a graphical user interface and a support for mouse, but ultimately only sold 10,000 units. “Just wanted to let everyone know the sources to the OS and applications were recovered, I converted them to Unix end of line conventions and spaces for Pascal tabs after recovering the files using Disk Image Chef, and they are with Apple for review,” said Al Kossow, a software curator at the museum. “After that’s done, the code that is cleared for release by Apple will be m...