SQLAuthority News – Best Articles on SQLAuthority.com

15 Comments. Leave new

• 

  Abdul Rauf

  November 12, 2007 4:57 pm

  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.

  Reply
• 

  Dharmendra Dixit

  January 8, 2008 5:56 pm

  Dear Pinal,

  Thanks for giving all these helpfull articles.

  Regards
  Dharmendra Dixit

  Reply
• 

  kavita

  May 13, 2009 1:06 am

  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.

  Reply
• 

  jeet

  May 22, 2009 3:14 pm

  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

  Reply
• 

  Imran Mohammed

  May 22, 2009 7:36 pm

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

  Reply
• 

  Osi

  January 14, 2010 3:09 am

  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.

  Reply
• 

  Pinal Dave

  January 15, 2010 7:51 pm

  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

  Reply
• 

  Rupesh

  April 7, 2010 6:02 pm

  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

  Reply
  • 

    Marko Parkkola

    April 7, 2010 11:33 pm

    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.

    Reply
• 

  Rupesh

  April 9, 2010 10:26 am

  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

  Reply
  • 

    Marko Parkkola

    April 9, 2010 3:35 pm

    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

    Reply
    • 

      Marko Parkkola

      April 9, 2010 5:04 pm

      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

• 

  swathi

  September 6, 2010 2:28 pm

  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

  Reply