November 10, 2022 at 2:02 pm
Looking for SSIS/TSQL Error Handling solution.
Ideally, SSIS Script Task that captures SSIS package error and logs this information
to SQL table.
November 10, 2022 at 2:05 pm
#Region "Imports"
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Runtime
#End Region
'ScriptMain is the entry point class of the script. Do not change the name, attributes,
'or parent of this class.
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Public Sub Main()
Dim sPackageName As String
Dim sSourceName As String
Dim sErrorDescription As String
Dim c_SourceCode As String
Dim c_FileCode As String
Dim a_CurrFileName As String
Dim a_BatchID_Created As String
Dim cn_value As String
Dim Query As String
sPackageName = ReadVariable("System::PackageName").ToString
sSourceName = ReadVariable("System::SourceName").ToString
sErrorDescription = ReadVariable("System::ErrorDescription").ToString
c_SourceCode = ReadVariable("User::c_SourceCode").ToString
c_FileCode = ReadVariable("User::c_FileCode").ToString
a_CurrFileName = ReadVariable("User::a_CurrFileName").ToString
a_BatchID_Created = ReadVariable("User::a_BatchID_Created").ToString
cn_value = ReadVariable("$Project::pETLtoolsConnStr").ToString
sErrorDescription = Replace(sErrorDescription, """", "")
sErrorDescription = Replace(sErrorDescription, "'", "")
If String.IsNullOrEmpty(c_SourceCode) Then
'in case First step failed. No Config data available
Query = "EXEC p_ETL_Log "
Query = Query + "@P_Source_Code = 'BNS',"
Query = Query + "@P_Batch_ID = -1,"
Query = Query + "@P_PARENT_PROC_NAME = '" + sPackageName + "',"
Query = Query + "@P_PROC_NAME = '" + sSourceName + "',"
Query = Query + "@P_STATUS = 'FAILURE',"
Query = Query + "@P_MSG = '" + sErrorDescription + "',"
Query = Query + "@P_PROC_TYPE = 'SSIS'"
Else
Query = "EXEC p_ETL_Log "
Query = Query + "@P_Source_Code = '" + c_SourceCode + "',"
Query = Query + "@P_Batch_ID = '" + a_BatchID_Created + "',"
Query = Query + "@P_File_Code = '" + c_FileCode + "',"
Query = Query + "@P_File_Name = '" + a_CurrFileName + "',"
Query = Query + "@P_PARENT_PROC_NAME = '" + sPackageName + "',"
Query = Query + "@P_PROC_NAME = '" + sSourceName + "',"
Query = Query + "@P_STATUS = 'FAILURE',"
Query = Query + "@P_MSG = '" + sErrorDescription + "',"
Query = Query + "@P_PROC_TYPE = 'SSIS'"
End If
Dim cn As OleDbConnection = New OleDbConnection()
Dim cmd As OleDbCommand = New OleDbCommand()
cmd.Connection = cn
cmd.CommandText = Query
cmd.CommandType = CommandType.Text
cn.ConnectionString = cn_value
cn.Open()
cmd.ExecuteNonQuery()
cn = Nothing
cmd = Nothing
Dts.TaskResult = ScriptResults.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables = Nothing
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex '//Handle things your way or throw back
End Try
Return result
End Function
#Region "ScriptResults declaration"
'This enum provides a convenient shorthand within the scope of this class for setting the
'result of the script.
'This code was generated automatically.
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
#End Region
End Class
November 10, 2022 at 2:18 pm
C# code
=====================================
public void Main()
{
// TODO: Add your code here
int BatchId = 1;
FileStream IpFileStream = null;
GZipInputStream IpGzipStream = null;
StreamReader IpStreamReader = null;
try
{
string JobServerName = Dts.Variables["JobServerName"].Value.ToString();
string InputFilePath = Dts.Variables["SourceDirectory"].Value.ToString() + Dts.Variables["InputFileName"].Value.ToString();
string DestinationTable = Dts.Variables["DestinationSchema"].Value.ToString() + "." + Dts.Variables["DestinationTable"].Value.ToString();
string connectionString = @"Data Source=" + Dts.Variables["DestinationServer"].Value.ToString() + ";Initial Catalog=" + Dts.Variables["DestinationDatabase"].Value.ToString() + ";Integrated Security=SSPI;";
string FieldDelimiter = Dts.Variables["FieldDelimiter"].Value.ToString(); int NumberOfBatches = (int)Dts.Variables["NumberOfBatches"].Value;
int NumberOfRecordsPerIteration = (int)Dts.Variables["NumberOfRecordsPerIteration"].Value;
string SSISCommandsDirectory = Dts.Variables["SSISCommandsDirectory"].Value.ToString();
string LogFilePath = Dts.Variables["LogFilePath"].Value.ToString();
string CMD_FilePath = Dts.Variables["CMD_FilePath"].Value.ToString();
string JQCommand = Dts.Variables["JQCommand"].Value.ToString();
int Debug = (int)Dts.Variables["Debug"].Value;
int RecordCount;
int RecordCountOfIteration = 0;
string Message;
string error;
string CMDArguments;
string IpJsonLine;
string CommandText;
string pipeName;
string JsonPipeName;
string Result;
//file path to store data if bulk insert fails to read from named pipe
string InputFilePath_Batch = Dts.Variables["SourceDirectory"].Value.ToString() + Dts.Variables["InputFileName"].Value.ToString().Substring(0, Dts.Variables["InputFileName"].Value.ToString().IndexOf(".gz")) + ".Batch" + BatchId.ToString() + ".temp";
//archive log file for this batch
string LogFilePath_Batch = Dts.Variables["LogFilePath"].Value.ToString() + ".Batch" + BatchId.ToString();
string ArchiveLogFilePath_Batch = Dts.Variables["ArchiveLogFilePath"].Value.ToString() + ".Batch" + BatchId.ToString();
if (File.Exists(LogFilePath_Batch))
{
if (File.Exists(ArchiveLogFilePath_Batch))
{
File.Delete(ArchiveLogFilePath_Batch);
}
File.Copy(LogFilePath_Batch, ArchiveLogFilePath_Batch);
File.Delete(LogFilePath_Batch);
}
IpFileStream = new FileStream(InputFilePath, FileMode.Open, FileAccess.Read);
IpGzipStream = new GZipInputStream(IpFileStream);
IpStreamReader = new StreamReader(IpGzipStream, Encoding.UTF8);
CMDArguments = "/C " + JQCommand;
Message = "CMD arguments: " + CMDArguments;
if (BatchId == 1)
{
WriteToLogFile(LogFilePath, Message, true);
}
System.Diagnostics.Process process = new System.Diagnostics.Process();
System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo(CMD_FilePath);
startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
startInfo.WorkingDirectory = Path.GetDirectoryName(SSISCommandsDirectory);
startInfo.UseShellExecute = false;
startInfo.CreateNoWindow = true;
startInfo.RedirectStandardOutput = true;
startInfo.RedirectStandardError = true;
IpJsonLine = IpStreamReader.ReadLine();
RecordCount = 1;
while (1 == 1)
{
using (var JsonRecords = new StringWriter())
{
//store json records into JsonRecords variable
while (IpJsonLine != null)
{
if ((RecordCount % NumberOfBatches) == BatchId - 1)
{
RecordCountOfIteration = RecordCountOfIteration + 1;
JsonRecords.Write(IpJsonLine);
if (RecordCountOfIteration == NumberOfRecordsPerIteration)
{
if (Debug == 1)
{
WriteToLogFile(LogFilePath_Batch, "\r\n\r\nRecord Count: " + RecordCount.ToString(), true);
}
break;
}
}
IpJsonLine = IpStreamReader.ReadLine();
RecordCount = RecordCount + 1;
}
if (JsonRecords.ToString().Length == 0)
{
JsonRecords.Flush();
JsonRecords.Close();
break;
}
JsonPipeName = Guid.NewGuid().ToString();
if (Debug == 1)
{
WriteToLogFile(LogFilePath_Batch, "\r\nCreating Named Pipe to Store JSON Records: " + JsonPipeName, true);
}
//convert json lines to flat structure
using (var namedJsonPipe = new NamedPipe(JsonPipeName, JsonRecords.ToString()))
{
CMDArguments = "/C " + JQCommand + " " + namedJsonPipe.Path;
startInfo.Arguments = CMDArguments;
process.StartInfo = startInfo;
process.Start();
Result = process.StandardOutput.ReadToEnd();
error = process.StandardError.ReadToEnd();
process.WaitForExit();
if (error.Length != 0)
{
throw new Exception(error);
}
}
}
Result = Result.TrimEnd('\r', '\n');
pipeName = Guid.NewGuid().ToString();
if (Debug == 1)
{
WriteToLogFile(LogFilePath_Batch, "\r\nCreating Named Pipe to Store Flat Records: " + pipeName, true);
}
//bulk insert flat structured named pipe into sql table
/*
try
{
using (new NamedPipe(pipeName, JobServerName, ""))
using (var namedPipe = new NamedPipe(pipeName, JobServerName, Result))
{
//CommandText = string.Format("BULK INSERT {0} FROM '{1}' WITH ( FIELDTERMINATOR ='{2}',ROWTERMINATOR ='{3}{4}',DATAFILETYPE ='{5}')", DestinationTable, namedPipe.Path, FieldDelimiter, '\r', '\n', "widechar");
CommandText = string.Format("BEGIN TRY BEGIN TRAN; BULK INSERT {0} FROM '{1}' WITH ( FIELDTERMINATOR ='{2}',ROWTERMINATOR ='{3}{4}',DATAFILETYPE ='{5}'); COMMIT TRAN; END TRY BEGIN CATCH ROLLBACK TRAN; THROW; END CATCH", DestinationTable, namedPipe.Path, FieldDelimiter, '\r', '\n', "widechar");
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand command = new SqlCommand(CommandText, con))
{
command.ExecuteNonQuery();
}
}
}
if (Debug == 1)
{
WriteToLogFile(LogFilePath_Batch, "\r\nBulk Inserted Data from Pipe: " + pipeName, true);
}
}
catch
*/ {
if (Debug == 1)
{
WriteToLogFile(LogFilePath_Batch, "\r\nFailed to Bulk Insert Data from Pipe: " + pipeName, true);
WriteToLogFile(LogFilePath_Batch, "\r\nCreating Temp File to Store Flat Records: " + InputFilePath_Batch, true);
}
//if bulk insert fails to load data from named pipe for named pipe, store data into physical file and run bulk insert from physical file
WriteToLogFile(InputFilePath_Batch, Result, false);
CommandText = string.Format("BULK INSERT {0} FROM '{1}' WITH ( FIELDTERMINATOR ='{2}',ROWTERMINATOR ='{3}{4}',DATAFILETYPE ='{5}')", DestinationTable, InputFilePath_Batch, FieldDelimiter, '\r', '\n', "widechar");
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand command = new SqlCommand(CommandText, con))
{
command.ExecuteNonQuery();
}
}
if (Debug == 1)
{
WriteToLogFile(LogFilePath_Batch, "\r\nBulk Inserted Data from File: " + InputFilePath_Batch, true);
}
}
if (IpJsonLine == null)
{
break;
}
IpJsonLine = IpStreamReader.ReadLine();
RecordCount = RecordCount + 1;
RecordCountOfIteration = 0;
}
File.Delete(InputFilePath_Batch);
}
catch (Exception E)
{
string LogFilePath_Batch = Dts.Variables["LogFilePath"].Value.ToString() + ".Batch" + BatchId.ToString();
string InputFilePath_Batch = Dts.Variables["SourceDirectory"].Value.ToString() + Dts.Variables["InputFileName"].Value.ToString().Substring(0, Dts.Variables["InputFileName"].Value.ToString().IndexOf(".gz")) + ".Batch" + BatchId.ToString() + ".temp";
WriteToLogFile(LogFilePath_Batch, "\r\n\r\nProcess failed with below errors:\r\n" + E.ToString(), true);
File.Delete(InputFilePath_Batch);
throw E;
}
finally
{
IpStreamReader.Close();
IpGzipStream.Close();
IpFileStream.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
November 10, 2022 at 3:08 pm
November 10, 2022 at 8:20 pm
foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator())
{
if ($provider.GetValue(0) -like "Microsoft.ACE*")
{
$v = New-Object PSObject
Add-Member -in $v NoteProperty "Provider Name" $provider.GetValue(0)
Add-Member -in $v NoteProperty "Description" $provider.GetValue(2)
$v
}
}
November 10, 2022 at 8:25 pm
if object_id('Rpt.fn_ParseJSON') is not null
drop function Rpt.fn_ParseJSON
GO
CREATE FUNCTION Rpt.fn_ParseJSON ( @JSON NVARCHAR(MAX))
--drop function Rpt.fn_ParseJSON
--created by R.Opeshansky
--created 10-11-2018
/*
Usage:
Select * from Rpt.fn_ParseJSON ('{ "Person":
{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"Address":
{
"streetAddress":"21 2nd Street",
"city":"New York",
"state":"NY",
"postalCode":"10021"
},
"PhoneNumbers":
{
"home":"212 555-1234",
"fax":"646 555-4567"
}
}
}
')
*/
RETURNS @hierarchy TABLE
(
element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
sequenceNo [int] NULL, /* the place in the sequence for the element */
parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
NAME NVARCHAR(2000),/* the name of the object */
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
)
AS
BEGIN
DECLARE
@FirstObject INT, --the index of the first open bracket found in the JSON string
@OpenDelimiter INT,--the index of the next open bracket found in the JSON string
@NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string
@NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string
@Type NVARCHAR(10),--whether it denotes an object or an array
@NextCloseDelimiterChar CHAR(1),--either a '}' or a ']'
@Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression
@Start INT, --index of the start of the token that you are parsing
@end INT,--index of the end of the token that you are parsing
@param INT,--the parameter at the end of the next Object/Array token
@EndOfName INT,--the index of the start of the parameter at end of Object/Array token
@token NVARCHAR(200),--either a string or object
@value NVARCHAR(MAX), -- the value as a string
@SequenceNo int, -- the sequence number within a list
@name NVARCHAR(200), --the name as a string
@parent_ID INT,--the next parent ID to allocate
@lenJSON INT,--the current length of the JSON String
@characters NCHAR(36),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@Escape INT --the index of the next escape character
DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */
(
String_ID INT IDENTITY(1, 1),
StringValue NVARCHAR(MAX)
)
SELECT--initialise the characters to convert hex to ascii
@characters='0123456789abcdefghijklmnopqrstuvwxyz',
@SequenceNo=0, --set the sequence no. to something sensible.
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
@parent_ID=0;
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT
@start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string
IF @start=0 BREAK --no more so drop through the WHILE loop
IF SUBSTRING(@json, @start+1, 1)='"'
BEGIN --Delimited Name
SET @start=@Start+1;
SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);
END
IF @end=0 --no end delimiter to last string
BREAK --no more
SELECT @token=SUBSTRING(@json, @start+1, @end-1)
--now put in the escaped control characters
SELECT @token=REPLACE(@token, FROMString, TOString)
FROM
(SELECT
'\"' AS FromString, '"' AS ToString
UNION ALL SELECT '\\', '\'
UNION ALL SELECT '\/', '/'
UNION ALL SELECT '\b', CHAR(08)
UNION ALL SELECT '\f', CHAR(12)
UNION ALL SELECT '\n', CHAR(10)
UNION ALL SELECT '\r', CHAR(13)
UNION ALL SELECT '\t', CHAR(09)
) substitutions
SELECT @result=0, @escape=1
--Begin to take out any hex escape codes
WHILE @escape>0
BEGIN
SELECT @index=0,
--find the next hex escape sequence
@escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin)
IF @escape>0 --if there is one
BEGIN
WHILE @index<4 --there are always four digits to a \x sequence
BEGIN
SELECT --determine its value
@result=@result+POWER(16, @index)
*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
@characters)-1), @index=@index+1 ;
END
-- and replace the hex sequence by its unicode value
SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))
END
END
--now store the string away
INSERT INTO @Strings (StringValue) SELECT @token
-- and replace the string with a token
SELECT @JSON=STUFF(@json, @start, @end+1,
'@string'+CONVERT(NVARCHAR(5), @@identity))
END
-- all strings are now removed. Now we find the first leaf.
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT @parent_ID=@parent_ID+1
--find the first object or list by looking for the open bracket
SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)--object or array
IF @FirstObject = 0 BREAK
IF (SUBSTRING(@json, @FirstObject, 1)='{')
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@firstObject
WHILE 1=1 --find the innermost object or list...
BEGIN
SELECT
@lenJSON=LEN(@JSON+'|')-1
--find the matching close-delimiter proceeding after the open-delimiter
SELECT
@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,
@OpenDelimiter+1)
--is there an intervening open-delimiter of either type
SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%',
RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object
IF @NextOpenDelimiter=0
BREAK
SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter
IF @NextCloseDelimiter<@NextOpenDelimiter
BREAK
IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@NextOpenDelimiter
END
---and parse out the list or name/value pairs
SELECT
@contents=SUBSTRING(@json, @OpenDelimiter+1,
@NextCloseDelimiter-@OpenDelimiter-1)
SELECT
@JSON=STUFF(@json, @OpenDelimiter,
@NextCloseDelimiter-@OpenDelimiter+1,
'@'+@type+CONVERT(NVARCHAR(5), @parent_ID))
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0
BEGIN
IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT
@SequenceNo=0,@end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name.
SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)--AAAAAAAA
SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1),
@endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin),
@param=RIGHT(@token, LEN(@token)-@endofname+1)
SELECT
@token=LEFT(@token, @endofname-1),
@Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1)
SELECT @name=stringvalue FROM @strings
WHERE string_id=@param --fetch the name
END
ELSE
SELECT @Name=null,@SequenceNo=@SequenceNo+1
SELECT
@end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null
IF @end=0
--HR Engineering notation bugfix start
IF ISNUMERIC(@contents) = 1
SELECT @end = LEN(@contents) + 1
Else
--HR Engineering notation bugfix end
SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' ' collate SQL_Latin1_General_CP850_Bin) + 1
SELECT
@start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)
--select @start,@end, LEN(@contents+'|'), @contents
SELECT
@Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
@Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end)
IF SUBSTRING(@value, 1, 7)='@object'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5), 'object'
ELSE
IF SUBSTRING(@value, 1, 6)='@array'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),
SUBSTRING(@value, 7, 5), 'array'
ELSE
IF SUBSTRING(@value, 1, 7)='@string'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string'
FROM @strings
WHERE string_id=SUBSTRING(@value, 8, 5)
ELSE
IF @value IN ('true', 'false')
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean'
ELSE
IF @value='null'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'null'
ELSE
IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'real'
ELSE
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'int'
if @Contents=' ' Select @SequenceNo=0
END
END
INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT '-',1, NULL, '', @parent_id-1, @type
--
RETURN
END
GO Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply