DECLARE @Query NVARCHAR(1000) SET @Query = N'DBCC SHRINKFILE(2,0,''TRUNCATEONLY'')' EXEC YourServerName.YourDBName.dbo.sp_executesql @statement = @QueryDBCC is database console command, 2 in SHRINKFILE signifies log file.
Hello friend, In this post i would like share solution of one small problem, which occurs many times in SQL Server.
When we restore Database to any other server and try to create database diagram then it does not allow to create database diagram and its give error
Error:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects
Solution is very small. just execute below query:
Replace MYDB with your DB name then execute the query
ALTER AUTHORIZATION ON DATABASE::MYDB TO sa
After executing above query we can create DB diagram
Following are the steps to create condition in SQL Server
1. Go to Management -> Conditions as show below
2. Right click on Condition and click on New Condition, a new window will appear as show below
3. Fill all the fields
Note: This rule is to start stored procedures with ‘usp’, if we want stored procedure to start with ‘usp_’ the value should be ‘usp[_]%’
4. After clicking on OK button condition will be created
5. Now right click on Policy and click on New Policy
6. New Policy window will open, fill all the fields
7. After Clicking on OK button policy will be created
8. Now if we execute following script to create stored procedure ‘sp_MyStoredProc’
CREATE PROCEDURE [dbo].[spMyStoredproc] AS BEGIN SELECT * FROM [dbo].[APIConfiguration] END
Message from SQL Server for violating policy:
Policy 'StoredProcNamingConventionPolicy' has been violated by 'SQLSERVER:\SQL\ABCServer\DEFAULT\Databases\JPDB\StoredProcedures\dbo.spMyStoredproc'. This transaction will be rolled back. Policy condition: '@Name LIKE 'usp%'' Policy description: '' Additional help: '' : '' Statement: 'CREATE PROCEDURE [dbo].[spMyStoredproc] AS BEGIN SELECT * FROM [dbo].[APIConfiguration] END'.Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65 The transaction ended in the trigger. The batch has been aborted.
9. But if we write stored procedure name which start with ‘usp’, then script will be executed successfully
CREATE PROCEDURE [dbo].[uspMyStoredproc] AS BEGIN SELECT * FROM [dbo].[APIConfiguration] END
Message
We can convert a character to byte in C#, we can also convert a string to byte array using Encoding.ASCII.GetBytes
But if we want to convert a hexadecimal string to byte array then there is no inbuilt method in C#.
We can achieve this in C# by reading characters in the hexadecimal string
Below is a method "HexToByte" which take string as input and return byte array
private static byte[] HexToByte(string hexString)
{
int lengthCount = 0;
// offset value is 2 for removing first two characters '0x' from hexadecimal string
int offset = 2;
int byteLength = 0;
// byte array length will be half of hexadecimal string length
byte[] bytes = new byte[(hexString.Length - offset) / 2];
byteLength = bytes.Length;
for (lengthCount = 0; lengthCount < byteLength; lengthCount++)
{
// Adding two nybble from hexadecimal string to create one byte
bytes[lengthCount] = (byte)((int.Parse(hexString[offset].ToString(), System.Globalization.NumberStyles.HexNumber, CultureInfo.InvariantCulture) << 4) | int.Parse(hexString[offset + 1].ToString(), System.Globalization.NumberStyles.HexNumber, CultureInfo.InvariantCulture));
offset += 2;
}
return bytes;
}
Basic concept of above code is read two characters from hex string and create one byte, because one hexadecimal character is nibble, so to create a byte we can add two nibble
It is useful when you store a file in SQL table and column type is varchar as we have seen in post: How To Store Any File into SQL Database
Using above method "HexToByte" we can convert file stored in sql table of varchar type column in byte array, which can be used to read stored file
Follow below steps to generate script of table with data
Right click on DB -> Task -> GenerateScripts
A new Window will open as shown below, then click on 'Next' button
After clicking on Next button, a new wizard will open for selecting object for which you want to create script, select second radio button for selecting specific database object and then select the check-box of your table as shown below
After clicking on 'Next' button, Click on 'Advance' button
In the advance option change general settings of 'Types of data to script' -> Schema and data and then click on Ok button
After clicking on Ok button, you can change directory path where you want to store your script and then click on 'Next' button
Click on 'Next' button
Click finish button, your script will be stored in the given location
Open the script in SQL Server
Hello friends, I am posting a solution to store any file in SQL server table and use it any where in an application as downloadable file
using OPENROWSET you can insert a file data in binary format to SQL table
Lets take one example to understand completely
One simple scenario is to just store the file in table and read from it from an application/ website in WCF or .NET
Create one table with a column as varbinay type
CREATE TABLE [dbo].[FileTable]( [FileName] [nvarchar](50) NOT NULL, [FileData] [varbinary](max) NOT NULL ) GO
Using above query table will be created
Now to store file in this table use below SQL query
DECLARE @Data varbinary(MAX) = (SELECT * FROM OPENROWSET(BULK N'D:\JP\Bloger\PDFFile.pdf',SINGLE_BLOB) AS AnyFile);
INSERT INTO [JPDB].[dbo].[FileTable]
VALUES('PDFFile.pdf',@Data)
Above SQL Query will store binary data of file: 'D:\JP\Bloger\PDFFile.pdf' in the column 'FileData' of table 'FileTable'
Now if we execute below query
SELECT [FileName]
,[FileData]
FROM [JPDB].[dbo].[FileTable]
We will get the result
This table can be used in any application to download this stored file easily
For example in C# you can use Response.BinaryWrite to download the file from SQL DB
I would like to explain one more thing here as per my experience in IT industry, many projects does not allow varbinary cloumn data type in a table
In these type of cases, we can insert data existing schema of database in varchar type column
To insert file data in a varchar type column we can use same approach as explained above
Suppose we have one table with following schema
To insert file data in this table having column varchar type, we need two steps:
First insert binary data in a temporary Table in varbinary column using below query
DECLARE @Data varbinary(MAX) = (SELECT * FROM OPENROWSET(BULK N'D:\JP\Bloger\PDFFile.pdf',SINGLE_BLOB) AS AnyFile);
SELECT 'PDFFile.pdf' AS [FileName],@Data AS [FileData]
INTO [JPDB].[dbo].[temporaryTable]
SELECT [FileName]
,[FileData]
FROM [JPDB].[dbo].[temporaryTable]
Above query will insert file data in table 'temporaryTable'
Now in second step copy this binary data of file in the table which has varchar column data type
Best way to insert binary data (actually this is hexadecimal string) in varchar type column is, generate sql script from temporaryTable
Follow below steps to generate script of table with data. Also you can refer my blog "How to generate Insert Script of a table in SQL Server"
Right click on DB -> Task -> GenerateScripts
A new Window will open as shown below, then click on 'Next' button
After clicking on Next button, a new wizard will open for selecting object for which you want to create script, select second radio button for selecting specific database object and then select the check-box of your table as shown below
After clicking on 'Next' button, Click on 'Advance' button
In the advance option change general settings of 'Types of data to script' -> Schema and data and then click on Ok button
After clicking on Ok button, you can change directory path where you want to store your script and then click on 'Next' button
Click on 'Next' button
Click finish button, your script will be stored in the given location
Open the script in SQL Server
Copy the hex string as highlighted in the above image to insert in the table which has column of varchar type, as shown in below query
SELECT TOP 1000 [FileName]
,[FileData]
FROM [JPDB].[dbo].[FileTable2]
Now table 'FileTable2' has PDF file data in hexadecimal string, we can convert hexadecimal string into Byte to read this file in C# or any other language
SQL Server 2012 provide better functionality to store files in DB with File Table, to see more detail refer msdn
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
Result:
DECLARE @Total [int]
SET @Total= (SELECT COUNT(*) FROM [JPDB].[dbo].[Employee])
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] ASC OFFSET (@Total-1) ROWS FETCH NEXT 1 ROWS ONLY
Result:
DECLARE @Total [int]
SET @Total= (SELECT COUNT(*) FROM [JPDB].[dbo].[Employee])
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] ASC OFFSET (@Total-3) ROWS FETCH NEXT 3 ROWS ONLY
Result:
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
Result
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
Result of above query is:
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
The number I have written after OFFSET skips rows, and the number written after NEXT get total number of rows
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] ASC OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
Result:
There are many cases when we want to read XML file and store data in SQL Server table
Suppose we have XML file as show below:
Inside First Node Inside Second Node 1 inside Second Node 2 - Item 1
- Item 2
- Item 3
There are two options to parse XML in T-SQL
1- nodes method msdn link
2- OPENXML msdn link
nodes method is useful when you want to read a node data from a XML, Lets take an example for above xml
If I want to read data from a specific node 'insidesecondnode1' from above xml file
DECLARE @x xml SET @x= '' SELECT T.c.query('text()') AS result FROM @x.nodes('/root/secondnode/insidesecondnode1') T(c) GO Inside First Node Inside Second Node 1 inside Second Node 2 - Item 1
- Item 2
- Item 3
Result for above query is as shown below
In the line 18, what ever path you will give this query will result the data inside the node
If a node is present multiple time in xml for example 'item' node inside 'thirdnode' in above xml, then this query will fetch data from all the nodes
DECLARE @x xml SET @x= '' SELECT T.c.query('text()') AS result FROM @x.nodes('/root/thirdnode/item') T(c) GO Inside First Node Inside Second Node 1 inside Second Node 2 - Item 1
- Item 2
- Item 3
Result of above query:
If you want to fetch particular node in above case and id value also, then use below query:
Inside First Node Inside Second Node 1 inside Second Node 2 ' SELECT T.c.value('@id','int') ID ,T.c.query('text()') AS result FROM @x.nodes('/root/thirdnode/item[2]') T(c) GO - Item 1
- Item 2
- Item 3
In above query line 18, by appending with [number] will read only that specific node, in this case second node
Result of above query:
Suppose you have many keys stored in one table as shown below
We can iterate over all the keys using this table to fetch corresponding data from xml file and store to another table as simple text
We can do this by applying a loop and passing all the keys stores in table
SQL Query to implement this approach is as shown below:
DECLARE @hdoc [int]
DECLARE @Key [varchar](1000)
DECLARE @XMLDoc [XML]
DECLARE @NumberOfKeys [int]
DECLARE @Counter [int] = 0;
DECLARE @Value [varchar](2000);
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempTable]') AND TYPE IN (N'U'))
BEGIN
DROP TABLE [dbo].[TempTable]
END
-- Creating temp table to store values parsed from XML
CREATE TABLE [dbo].[TempTable](
[ID] [int],
[Key] [varchar](2000),
[Value] [varchar](2000)
)
SET @XMLDoc=
'
Inside First Node
Inside Second Node 1
inside Second Node 2
- Item 1
- Item 2
- Item 3
'
SET @NumberOfKeys= (SELECT COUNT([Key]) FROM [JPDB].[dbo].[XMLKeysTable]);
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLDoc
WHILE(@Counter < @NumberOfKeys)
BEGIN
SELECT @Key = [Key] FROM [JPDB].[dbo].[XMLKeysTable] ORDER BY [Key] OFFSET @Counter ROWS FETCH NEXT 1 ROWS ONLY
SELECT @Value = Value FROM OPENXML (@hdoc, @Key,1) WITH (Value [varchar](1000) 'text()[1]')
INSERT INTO [dbo].[TempTable]
VALUES(@Counter, @Key, @Value)
SET @Counter += 1;
END
--Removes the internal representation of the XML document specified by the document handle and invalidates the document handle
EXEC sp_xml_removedocument @hdoc
SELECT * FROM [dbo].[TempTable]
Result of above query:
You can also refer my blog: Pass XML file in stored procedure as a input parameter from C#
Suppose we have XML file as show below:
Inside First Node Inside Second Node 1 inside Second Node 2 - Item 1
- Item 2
- Item 3
static void Main(string[] args)
{
string SProc = "dbo.usp_XMLParser";
string ConnectonString = @"Data Source=JITENDRAPAL\SQLEXPRESS;Initial Catalog=JPDB;Integrated Security=True;";
XmlDocument xmldoc = new XmlDocument();
xmldoc.Load(@"my.xml");
using (SqlConnection sqlConnection = new SqlConnection(ConnectonString))
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand(SProc, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(
new SqlParameter("@XMLFile", SqlDbType.Xml)
{
Value = new SqlXml(new XmlTextReader(xmldoc.InnerXml, XmlNodeType.Document, null))
});
using (DataTable dataTable = new DataTable())
{
dataTable.Locale = CultureInfo.InvariantCulture;
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
{
sqlDataAdapter.SelectCommand = sqlCommand;
sqlDataAdapter.Fill(dataTable);
}
Console.WriteLine(dataTable.Rows[0]["Col1"].ToString());
Console.Read();
}
}
}
Console.Read();
}
In above
code:XmlDocument xmldoc = new XmlDocument(); xmldoc.Load(@"my.xml");
sqlCommand.Parameters.Add(
new SqlParameter("@XMLFile", SqlDbType.Xml)
{
Value = new SqlXml(new XmlTextReader(xmldoc.InnerXml, XmlNodeType.Document, null))
});
Stored procedure ‘usp_XMLParser’ takes XML file as input and store XML file in SQL table
SQL Script of stored procedure is as shown below:
USE [JPDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_XMLParser] @XMLFile XML
AS
BEGIN
DECLARE @hdoc [int]
DECLARE @ID [int]
DECLARE @Key [varchar](1000)
DECLARE @NumberOfKeys [int]
DECLARE @Counter [int] = 0;
DECLARE @Value [varchar](2000);
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XMLDataTable]') AND TYPE IN (N'U'))
BEGIN
DROP TABLE [dbo].[XMLDataTable]
END
-- Creating table
CREATE TABLE [dbo].[XMLDataTable](
[ID] [int]
,[Key] [varchar](1000)
,[Value] [varchar](2000)
)
SET @NumberOfKeys= (SELECT COUNT([Key]) FROM [JPDB].[dbo].[XMLKeysTable]);
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLFile
WHILE(@Counter < @NumberOfKeys)
BEGIN
SELECT @ID= [ID], @Key = [Key] FROM [JPDB].[dbo].[XMLKeysTable] ORDER BY [Key] OFFSET @Counter ROWS FETCH NEXT 1 ROWS ONLY
SELECT @Value = Value FROM OPENXML (@hdoc, @Key,1) WITH (Value [varchar](1000) 'text()[1]')
INSERT INTO [dbo].[XMLDataTable]
VALUES(@ID, @Key, @Value)
SET @Counter += 1;
END
--Removes the internal representation of the XML document specified by the document handle and invalidates the document handle
EXEC sp_xml_removedocument @hdoc
SELECT 'true' AS [Col1]
END
@XMLFile XML
I am storing XML file in table ‘dbo.XMLDataTable’
To know more about XML parsing in T-SQL and explanation of above stored procedure refer my next post ‘XML Parser in SQL Server’