SQLAuthority News – Best Articles on SQLAuthority.com

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 – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

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 – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

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 – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

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 Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

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 – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

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 (http://blog.SQLAuthority.com)

About these ads

15 thoughts on “SQLAuthority News – Best Articles on SQLAuthority.com

  1. 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.

  2. 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.

  3. 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

  4. @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.

  5. 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.

  6. 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 (http://www.w3schools.com/Schema/default.asp) 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.

  7. 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

  8. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s