SQL SERVER – Cursor to Kill All Process in Database
SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure
SQL SERVER – Shrinking Truncate Log File – Log Full
SQL SERVER – Simple Example of Cursor
SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case
SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
SQL SERVER – T-SQL Script to find the CD key from Registry
SQL SERVER – Delete Duplicate Records – Rows
SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation
SQL SERVER – Union vs. Union All – Which is better for performance?
SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity
SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By
SQL SERVER – Index Seek Vs. Index Scan (Table Scan)
SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive
SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005
SQL SERVER – Performance Optimization of SQL Query and FileGroups
SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX
SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling
SQL SERVER – Script to find SQL Server on Network
SQL SERVER – Stored Procedures Advantages and Best Advantage
SQL SERVER – CASE Statement/Expression Examples and Explanation
SQL SERVER – Raid Configuration – RAID 10
SQL SERVER – Six Properties of Relational Tables
SQL SERVER – TRIM() Function – UDF TRIM()
SQL SERVER – Difference between Unique Index vs Unique Constraint
SQL SERVER – 2005 Locking Hints and Examples
SQL SERVER – Good, Better and Best Programming Techniques
SQL SERVER – Random Number Generator Script – SQL Query
SQL SERVER – 2005 TOP Improvements/Enhancements
SQL SERVER – 2005/2000 Examples and Explanation for GOTO
SQL SERVER – Explanation SQL Commando GO
SQL SERVER – 2005 – List all the database
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
SQL SERVER – Disable Index – Enable Index – ALTER Index
SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator
SQL SERVER – SQL Code Formatter Tools
SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN
SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases
SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key
SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples
SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility
SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length
SQL SERVER Database Coding Standards and Guidelines Complete List Download
SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL
SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice
SQL SERVER – Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY
SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression
SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
SQL SERVER – Explanation and Comparison of NULLIF and ISNULL
SQL SERVER – 2005 Row Overflow Data Explanation
SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable
SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause
SQL SERVER – 2005 Best Practices Analyzer Tutorial – Sample Example
SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days
SQL SERVER – Count Duplicate Records – Rows
SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable
SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup
SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday
SQL SERVER – One Thing All DBA Must Know
SQL SERVER – 2005 – List Tables in Database Without Primary Key
SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date
SQL SERVER – UDF – Validate Integer Function
SQL SERVER – What is SQL? How to pronounce SQL?
SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()
SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop
SQL SERVER – Find Last Day of Any Month – Current Previous Next
SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code
SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database
SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan
SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
SQL SERVER – 2005 – Introduction and Explanation to sqlcmd
SQL SERVER – UDF – User Defined Function – Get Number of Days in Month
SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt
SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function
SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT
SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE
SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE
SQL SERVER – Three T-SQL Script to Create Primary Keys on Table
Reference : Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
Hello Sir:
is ther any complete tutorial…….which starts from creation of store procedure and go on further trigers and views……..means a complete layout. start to end.
Dear Pinal,
Thanks for giving all these helpfull articles.
Regards
Dharmendra Dixit
sir ,
i want to encrypt and then store the passwords in the database using sql server 2005.
i can encrypt and decrypt by keys and certificate either strings or integers but not alphanumeric passwords or that include special characters…
plz help.
Hello Sir,
I have written one C# windows application where in I am using SQLBulkCopy – but the target table has one insert trigger defined – I could manage to execute the trigger on bulk insert by employing SQLbulkCopyOptions.FireTrigger enumeration – but trigger executes after ALL records inserted – which is I DO NOT want – I want trigger should fire for individual record as if independent insert trigger – how do I do that?
regards
Jeet
@Jeet,
You can expect these kind of shocks when using Bulk Copy.
I can tell you one work around, Dont know if this will work for you. When you are doing a SQLBulkCopy, why dont you do it in a global/local temporary table. Store all values into a temporary table and from that temporary table insert into your main table.
Once you have data in temporary table, then you can do
Insert into YourMainTable
select * from #TemporaryTableName
This should definitely fire your trigger for every record.
Also, I had similar problem earlier with triggers, I was doing a insert into select * from, and trigger was firing for the last record, I thought its a bug in SQL Server, but the problem was with the trigger. Trigger was not properly written.
Why dont you first check the trigger, run insert into YourTableName and select * from AnotherTableName see if trigger fires for every record.
If you need help with this, Let us know.
~ IM.
Hey I love this blog. i have a crazy question for you. is there a way to email into SQL server? if a friend sends me an email, can it go staight into SQL server and store it in a designated table? please let me know thanks.
Hi,
This can be acheived by two methods:
1. By synchronyzing the server with outlook and
2. With some ADO coding to create connection to outlook.Application and SQL server.
About first method get details at below page:
Regards,
Pinal Dave
Hi Pinal,
1). Is it possible to create a table using the XML schema file. If yes how? can you please help.
2). I have a table which is created using OPENXML. how can we correlate the different columns (id, parentid,prev etc) to get the correct data.
Please help me.
Thanks in advance
Rupesh
1) Yes, of course. You could try to write XSL template to generate SQL script from XML Schema and run it with custom application. Or you could write T/SQL procedure which builds SQL using XQuery/XPath and runs it with sp_executesql.
But are you talking about XML Schema or some custom schema file? There’s some restrictions and limitations so if you could provide some snippet from your schema file I think I could help you more.
Hi Marko,
Thnx for your suggestion.
I am trying to import the file through OPENXML. I get stuck on how to provide the SCHEMADECLARAION in the “WITH” clause of OPENXML. I dont know the fields of the XML file so I can create the table in advance or pass the columns of the file in “WITH” clause.
Is there something like, I pass the XML file and its Schema and it will automatically import the file (using XML). I want the whole process to be done only in T-SQL (so that I can create a procedure for importing any XML file to SQL tables.)
Thanks in advance.
Rupesh
Hi,
I’m not sure if I understood correctly and I’ve never used OPENXML before but I came up with something like this.
— This is the table that is created and where data is imported
declare @TableName nvarchar(max)
set @TableName = N’MyXMLTable’
— Drop temp table if it exists
IF NOT OBJECT_ID(‘tempdb..#xml_temp’) IS NULL DROP TABLE #xml_temp
— Here we have the XML
— This piece of code is taken from MSDN
DECLARE @doc varchar(1000)
SET @doc = ‘
‘
— Prepare the document and create a handle to it
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
— Select XML into temp table
SELECT *
INTO #xml_temp
FROM OPENXML (@idoc, ‘/ROOT/Customer/Order/OrderDetail’, 2)
WITH (
CustomerID varchar(10) ‘../@CustomerID’,
OrderDate datetime ‘../@OrderDate’,
ProdID int ‘@ProductID’,
Qty int ‘@Quantity’)
— Build CREATE clause based on temp table’s schema
declare @sql nvarchar(max)
set @sql = N’CREATE TABLE [‘ + @TableName + N’] (‘
select
@sql = @sql + N'[‘ + c.name + N’] ‘ + ty.name +
case
when ty.name in (‘char’, ‘nchar’, ‘varchar’, ‘nvarchar’, ‘varbinary’) then N'(‘ + cast(c.max_length as nvarchar) + N’)’
when ty.name in (‘decimal’, ‘numeric’) then ‘(‘ + cast(c.precision as nvarchar) + N’, ‘ + cast(c.scale as nvarchar) + N’)’
else N”
end +
N’, ‘
from tempdb.sys.columns c
join sys.types ty on ty.system_type_id = c.system_type_id
where c.object_id = object_id(‘tempdb..#xml_temp’)
order by c.column_id
— Execute dynamic CREATE clause
set @sql = substring(@sql, 0, len(@sql) – 1) + ‘)’
exec sp_executesql @sql
— Copy data into newly created table from temp table
set @sql = ‘INSERT INTO [‘ + @TableName + ‘] SELECT * FROM #xml_temp’
exec sp_executesql @sql
— Cleanup stuff
— Select data out of new table
set @sql = ‘SELECT * FROM [‘ + @TableName + ‘]’
exec sp_executesql @sql
— Drop it
set @sql = ‘DROP TABLE [‘ + @TableName + ‘]’
exec sp_executesql @sql
— Finally drop the temp table
DROP TABLE #xml_temp
Blog ate my XML but here it is: https://docs.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql?view=sql-server-2017
hi
i am using .net code in sript task in ssis,but is giving error.plz any one can solve
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports System.Net.Mail
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As Object
Dim varAddresses As String
varMailBody = Dts.Variables(“varSalesSummaryHTML”).Value
varAddresses = Dts.Variables(“varMailTo”).Value.ToString
varHTMLMail = New MailMessage(“noreply@domain.com”, varAddresses, “Daily Order Summary”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“Your_SMTP_Server_Name”)
varSMTPClient.UseDefaultCredentials = True
varSMTPClient.Send(varHTMLMail)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class