Although we are doing data loading in Salesforce from ages, its very common to miss some steps or considerations. Therefore thought to list some of best practices and considerations I follow before or during data load process.
Recently I was in need to analyze Salesforce debug log for one stubborn issue which was very hard to reproduce. Was able to download 1500+ debug logs on my system, however to analyze it, I decided to take help of SQL Server.
Very soon I came into challenge to read all files in a directory and store it back in SQL Server table.
Found very good article here which helped me to come up with below queries. If you need to read more in detail about below T-SQL and stored procedures please navigate to original article.
I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server. However, finally came up with below solution which worked very well. Below script was used to export around 25GB of files stored in SQL Server.
To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob .
CREATE TABLE [dbo].[Document]( [Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [Extension] [varchar](50) NULL, [FileName] [varchar](200) NULL, [Doc_Content] [varbinary](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
For demo purpose, we will save two files in our table using below queries
INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*
FROM OPENROWSET
(BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB) [Doc_Data]
INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'html', 'Progress.html',[Doc_Data].*
FROM OPENROWSET
(BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB) [Doc_Data]
If we try to see content in actual table, it will look like
For demo purpose, we want to save documents on local disc. We will use Doc_Num to be created as folder and document will be saved in that folder. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post.
Note : We will create only one folder per document. If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder. Its explained in this blog post.
Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.
USE [POC] DECLARE @outPutPath varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract Blob' , @i bigint , @init int , @data varbinary(max) , @fPath varchar(max) , @folderPath varchar(max) --Get Data into temp Table variable so that we can iterate over it DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num] varchar(100) , [FileName] varchar(100), [Doc_Content] varBinary(max) ) INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content]) Select [Doc_Num] , [FileName],[Doc_Content] FROM [dbo].[Document] --SELECT * FROM @table SELECT @i = COUNT(1) FROM @Doctable WHILE @i >= 1 BEGIN SELECT @data = [Doc_Content], @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName], @folderPath = @outPutPath + '\'+ [Doc_Num] FROM @Doctable WHERE id = @i --Create folder first EXEC [dbo].[CreateFolder] @folderPath EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created EXEC sp_OASetProperty @init, 'Type', 1; EXEC sp_OAMethod @init, 'Open'; -- Calling a method EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method EXEC sp_OAMethod @init, 'Close'; -- Calling a method EXEC sp_OADestroy @init; -- Closed the resources print 'Document Generated at - '+ @fPath --Reset the variables for next use SELECT @data = NULL , @init = NULL , @fPath = NULL , @folderPath = NULL SET @i -= 1 END
Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.
Below image shows output in action :
Recently, I was in need to search complete Database for some value and found below very useful script (T-SQL)
DECLARE @SearchStr nvarchar(100) = 'SEARCHSTRING'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
BEGIN TRY
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
END TRY
BEGIN CATCH
END CATCH
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
If we run above script for word ‘king’ then output in below format will be displayed
SQL Server has some standard stored procedures that allow OLE automation.
First step, is to check whether Ole Automation Procedures are enabled in SQL Server or not ? It can be enabled by simply executing below T-SQL commands.
T-SQL to enable Ole Automation Procedures in SQL Server
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Next step is to create a stored procedure, which will use an OLE Automation procedures and create a folder on system drive
After a long time i am going to write any article on SQL Server. That last article i think was before 18 months. As currently i am exploring the Salesforce and its API’s, i caught in situation to create a stored procedure and provide the offset feature, that means i needed the pagination support in my stored procedure. There is no direct keyword available in SQL Server something like LIMIT in mysql. And therefore i thought to share my solution with community. So, here we go…
I am considering that there will be table named “Employee” in which i want to search by employees firstName with pagesize and offset attribute.
In This article, i am going to demonstrate that how to use the “Update From” statement in SQl Server. Inner Join or Joins can be easily used in Insert Statement, but some times it is needed in Update Statements Also.
Lets have the below example:
Open Database Connectivity (ODBC) :
In computing, Open Database Connectivity (ODBC) provides a standard software interface for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of programming languages, database systems, and operating systems. Thus, any application can use ODBC to query data from a database, regardless of the platform it is on or DBMS it uses.
Following are the basic difference between the ADO and ADO.Net in Microsoft .Net framework.
In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string. OpenXML is used to parse the XML in Rowset data form.
In this tutorial, i have created one XML string and saved the parsed data in Table variable. If XML data is present in table, then any operation can be performed.