How do you undo a change for a person who is no longer with the company?
In two steps you can perform this task.
1) Open Command Prompt and select an specified path
C:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin>cd C:\
Program Files\Microsoft Visual Studio 9.0\Common7\IDE
2) Give a proper file path with tf command
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE>tf undo /workspace:WorkSpace Name;tfs User name $/file path /s:http://172.0.1.1:port
finally you will get a successful message as below.
The operation completed successfully.
Friday, April 29, 2011
Wednesday, April 6, 2011
Using INSERT / OUTPUT in a SQL Server Transaction
Frequently I find myself in situations where I need to insert records into a table in a set-based operation wrapped inside of a transaction where secondarily, and within the same transaction, I spawn-off subsequent inserts into related tables where I need to pass-in key values that were the outcome of the initial INSERT command. Thanks to a Transact/SQL enhancement in SQL Server, this just became much easier and can be done in a single statement... WITHOUT A TRIGGER!
Solution
One of the Transact/SQL enhancements in Microsoft SQL Server is the OUTPUT sub-clause of the INSERT statement. You can now capture the records inserted via an INSERT statement (think also being able to capture IDENTITY column values for the new rows) for subsequent use in an additional INSERT statement for a child table to persist referential integrity without the need for an INSERT trigger.
Why not just use a trigger? It's a viable and proven construct of SQL Server, right?
The short answer is "Yes, it is." However, triggers are one of those nasty little secrets that the database keeps. They don't just jump right out at you and say "HERE I AM!" Take for example the troubleshooting process of deadlocks or tuning a poorly-performing query - a trigger sitting in the background performing as it's been asked to may be causing your issues, but you're going to go through many iterations of searching stored procedures, and ad-hoc T/SQL code before you probably even stop to consider there is a trigger firing off data modification language commands (DML) - INSERTS, UPDATES, or DELETES that are adjunct to what you're trying to diagnose. I associate the use of triggers with the use of ad-hoc T/SQL code used in an application's code stack and passed to a SQL Server instance for processing - practices to shy away from.
That is why I like what I see with the INSERT-OUTPUT construct. You get the benefits of being able to capture the inserted values that you can then pass to a secondary command - and you can wrap this all inside a single transaction for atomicity. The syntax for this construct is shown below and differs only slightly from the basic INSERT T/SQL command:
INSERT INTO
(
)
OUTPUT INSERTED. --and other columns from SOME_TABLE if need be
INTO
(
)
SELECT
(
)
FROM
WHERE
The only difference between this and a standard INSERT statement is the inclusion on the OUTPUT...INTO statement. To make this easy think of it as simply a secondary INSERT statement inside of the original INSERT statement that captures the values in the virtualized INSERTED table - the same table that a trigger would use - to process a secondary INSERT to another table. In the example below, and in keeping with the holiday season, let's say you're responsible for doing a bit of hiring at the corporate AdventureWorks offices. A right-jolly old elf is being hired for some in-store promotions and in keeping with corporate policy you always perform a 90 day review for any new hires. We want to have the notfication recorded when the new hire is entered without any additional work on the part of Human Resources. The code below demonstates how we can use INSERT-OUTPUT to do this.
USE AdventureWorks;
GO
---Create Example Tables
/*
Note, this is not fully-normalized. I would have included another table
for Notification Types if this was an actual solution.
I would also use an int NotificationTypeID column in Notifications table
instead of a varchar(xx) NotificationType column.
*/
CREATE SCHEMA [HR] AUTHORIZATION dbo;
GO
CREATE TABLE [HR].[Staff]
(
[StaffID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] VARCHAR(30) NOT NULL,
[LastName] VARCHAR(30) NOT NULL,
CONSTRAINT [PK_StaffID] PRIMARY KEY CLUSTERED
(
[StaffID] ASC
)ON [PRIMARY]
) ON [PRIMARY];
CREATE TABLE [HR].[Notification]
(
[NotificationID] [int] IDENTITY(1,1) NOT NULL,
[StaffID] [int] NOT NULL,
[NotificationDate] DATETIME NOT NULL,
[NotificationType] VARCHAR(30) NOT NULL,
CONSTRAINT [PK_NotificationID] PRIMARY KEY CLUSTERED
(
[NotificationID] ASC
)ON [PRIMARY]
) ON [PRIMARY]; Now that we've built the objects for this little exercise we can look at the INSERT-OUTPUT construct in action...
/*
Demonstrate how you can insert the key values added to Staff.StaffID
into Notifications.StaffID in single transaction
*/
INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Santa','Claus'); Selecting now from both the Staff and Notification tables you'll see that the key values were successfully entered into both tables:
SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;
Now there is a very important - and quite limiting caveat to using INSERT-OUTPUT. The Output target can't be part of any foreign key relationship. Even if there is no cascading relationship to any other object via that relationship in the database. Let's look at what happens if it is. We'll add a foreign key to Notification on StaffID, referencing the StaffID column in the Staff table and then try to add some additional holiday help:
--Add Foreign Key for StaffID column to Notifications table
ALTER TABLE HR.Notification ADD CONSTRAINT [FK_Notification_Staff]
FOREIGN KEY
(
StaffID
)
REFERENCES HR.Staff
(
StaffID
);
/*
Demonstrate how you can insert the key values added to Staff.StaffID
into Notifications.StaffID in single transaction
*/
INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Frosty','Snowman');
SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;
The following error message is returned as expected:
Msg 332, LEVEL 16, State 1, Line 17
The target TABLE 'HR.Notification' OF the OUTPUT INTO clause cannot be ON either side OF a (PRIMARY KEY, FOREIGN KEY) relationship. Found reference CONSTRAINT 'FK_Notification_Staff'.
For More Information please visit http://www.mssqltips.com/tip.asp?tip=2183
Solution
One of the Transact/SQL enhancements in Microsoft SQL Server is the OUTPUT sub-clause of the INSERT statement. You can now capture the records inserted via an INSERT statement (think also being able to capture IDENTITY column values for the new rows) for subsequent use in an additional INSERT statement for a child table to persist referential integrity without the need for an INSERT trigger.
Why not just use a trigger? It's a viable and proven construct of SQL Server, right?
The short answer is "Yes, it is." However, triggers are one of those nasty little secrets that the database keeps. They don't just jump right out at you and say "HERE I AM!" Take for example the troubleshooting process of deadlocks or tuning a poorly-performing query - a trigger sitting in the background performing as it's been asked to may be causing your issues, but you're going to go through many iterations of searching stored procedures, and ad-hoc T/SQL code before you probably even stop to consider there is a trigger firing off data modification language commands (DML) - INSERTS, UPDATES, or DELETES that are adjunct to what you're trying to diagnose. I associate the use of triggers with the use of ad-hoc T/SQL code used in an application's code stack and passed to a SQL Server instance for processing - practices to shy away from.
That is why I like what I see with the INSERT-OUTPUT construct. You get the benefits of being able to capture the inserted values that you can then pass to a secondary command - and you can wrap this all inside a single transaction for atomicity. The syntax for this construct is shown below and differs only slightly from the basic INSERT T/SQL command:
INSERT INTO
(
)
OUTPUT INSERTED.
INTO
(
)
SELECT
(
)
FROM
WHERE
The only difference between this and a standard INSERT statement is the inclusion on the OUTPUT...INTO statement. To make this easy think of it as simply a secondary INSERT statement inside of the original INSERT statement that captures the values in the virtualized INSERTED table - the same table that a trigger would use - to process a secondary INSERT to another table. In the example below, and in keeping with the holiday season, let's say you're responsible for doing a bit of hiring at the corporate AdventureWorks offices. A right-jolly old elf is being hired for some in-store promotions and in keeping with corporate policy you always perform a 90 day review for any new hires. We want to have the notfication recorded when the new hire is entered without any additional work on the part of Human Resources. The code below demonstates how we can use INSERT-OUTPUT to do this.
USE AdventureWorks;
GO
---Create Example Tables
/*
Note, this is not fully-normalized. I would have included another table
for Notification Types if this was an actual solution.
I would also use an int NotificationTypeID column in Notifications table
instead of a varchar(xx) NotificationType column.
*/
CREATE SCHEMA [HR] AUTHORIZATION dbo;
GO
CREATE TABLE [HR].[Staff]
(
[StaffID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] VARCHAR(30) NOT NULL,
[LastName] VARCHAR(30) NOT NULL,
CONSTRAINT [PK_StaffID] PRIMARY KEY CLUSTERED
(
[StaffID] ASC
)ON [PRIMARY]
) ON [PRIMARY];
CREATE TABLE [HR].[Notification]
(
[NotificationID] [int] IDENTITY(1,1) NOT NULL,
[StaffID] [int] NOT NULL,
[NotificationDate] DATETIME NOT NULL,
[NotificationType] VARCHAR(30) NOT NULL,
CONSTRAINT [PK_NotificationID] PRIMARY KEY CLUSTERED
(
[NotificationID] ASC
)ON [PRIMARY]
) ON [PRIMARY]; Now that we've built the objects for this little exercise we can look at the INSERT-OUTPUT construct in action...
/*
Demonstrate how you can insert the key values added to Staff.StaffID
into Notifications.StaffID in single transaction
*/
INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Santa','Claus'); Selecting now from both the Staff and Notification tables you'll see that the key values were successfully entered into both tables:
SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;
Now there is a very important - and quite limiting caveat to using INSERT-OUTPUT. The Output target can't be part of any foreign key relationship. Even if there is no cascading relationship to any other object via that relationship in the database. Let's look at what happens if it is. We'll add a foreign key to Notification on StaffID, referencing the StaffID column in the Staff table and then try to add some additional holiday help:
--Add Foreign Key for StaffID column to Notifications table
ALTER TABLE HR.Notification ADD CONSTRAINT [FK_Notification_Staff]
FOREIGN KEY
(
StaffID
)
REFERENCES HR.Staff
(
StaffID
);
/*
Demonstrate how you can insert the key values added to Staff.StaffID
into Notifications.StaffID in single transaction
*/
INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Frosty','Snowman');
SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;
The following error message is returned as expected:
Msg 332, LEVEL 16, State 1, Line 17
The target TABLE 'HR.Notification' OF the OUTPUT INTO clause cannot be ON either side OF a (PRIMARY KEY, FOREIGN KEY) relationship. Found reference CONSTRAINT 'FK_Notification_Staff'.
For More Information please visit http://www.mssqltips.com/tip.asp?tip=2183
Wednesday, January 12, 2011
Retrieving or Limiting the First N Records from a SQL Query
In SQL server 2005, SET ROWCOUNT n has the same behavior as SQL server 2000. It’s recommended to use TOP (n) instead of SET ROWCOUNT n.But in SQL server 2008 you are able to use variable at place of n in TOP (n).
Example:
for RowCount
DECLARE @RowsCount INT
SELECT @RowsCount = 0
SET ROWCOUNT @RowsCount
SELECT * FROM MyTable
SET ROWCOUNT 0
for TOP (n)
DECLARE @RowsCount INT
SELECT @RowsCount = 0
SELECT TOP (@RowsCount ) * FROM MyTable
Example:
for RowCount
DECLARE @RowsCount INT
SELECT @RowsCount = 0
SET ROWCOUNT @RowsCount
SELECT * FROM MyTable
SET ROWCOUNT 0
for TOP (n)
DECLARE @RowsCount INT
SELECT @RowsCount = 0
SELECT TOP (@RowsCount ) * FROM MyTable
Wednesday, January 5, 2011
Message Queue in .Net
Queue is nothing but FIFO (First In First Out).
Before going to Message Queue concept, why we need Message Queuing?
Answer is that, we require Message Queue mechanism when we want to store insignificant data which is not required to store and we want to do some operations on it.
For example, we want to accept data from multiple users and we have to do some operations on that data, after that we require writing data to some files. Then in this situation we need not require data to store in database, we just want to do some operations on that data and copy that to some files. This requirement will be fulfilled by Message Queue.
If we take another example, where we have two applications in two different systems. One application will send the data and another will need to process the data.
Here I am explaining second example but two applications are in same system.
To run Message Queue concept in .Net, we need Message Queue service to be installed in our system. By default installation of Operating System, it will not install. To install Message Queue service
Start->settings->control panel ->Add/Remove programs-> Click on Add/Remove window components-> Check Message Queuing ->Click Next button
If you unable install, please refer documentation of corresponding Operating System.
After successful installation of Message Queue, you have to write the code to use it.
Here I am explaining Message Queue by using VB.Net.
In this example we need two applications, one is to store data in Message Queue and another is to retrieve data from Queue.
namespace is "System.Messaging"
Step 1: Push Data in Queue
static void PushDatainQueue(string QueueData, string QueueLable)
{
#region Queue
string strQuePath = string.Empty;
MessageQueue objMessageQueue = new MessageQueue();
strQuePath = @".\Private$\syccrm";
if (MessageQueue.Exists(strQuePath))
//creates an instance MessageQueue, which points
//to the already existing MyQueue
objMessageQueue = new System.Messaging.MessageQueue(strQuePath);
else
//creates a new private queue called MyQueue
objMessageQueue = MessageQueue.Create(strQuePath);
#endregion
#region Message
System.Messaging.Message objMessage = new System.Messaging.Message();
objMessage.Priority = MessagePriority.Normal;
objMessage.Body = QueueData;
objMessage.Label = QueueLable;
#endregion
#region Push Message in Queue
objMessageQueue.Send(objMessage);
#endregion
}
Step 2: Receive Data from Queue
static void ReceiveDataFromQueue(string strQuePath)
{
MessageQueue objMessageQueue = new MessageQueue();
if (MessageQueue.Exists(strQuePath))
objMessageQueue = new System.Messaging.MessageQueue(strQuePath);
else
objMessageQueue = MessageQueue.Create(strQuePath);
Message objMessage = new Message();
objMessageQueue = objMessageQueue.Receive();
String strOperation = objMessageQueue.Label.ToString();
string queueData = objMessageQueue.Body.ToString();
}
Note:for private and public queue syntax
// References public queues.
public void SendPublic()
{
MessageQueue myQueue = new MessageQueue(".\\myQueue");
myQueue.Send("Public queue by path name.");
return;
}
// References private queues.
public void SendPrivate()
{
MessageQueue myQueue = new
MessageQueue(".\\Private$\\myQueue");
myQueue.Send("Private queue by path name.");
return;
}
Before going to Message Queue concept, why we need Message Queuing?
Answer is that, we require Message Queue mechanism when we want to store insignificant data which is not required to store and we want to do some operations on it.
For example, we want to accept data from multiple users and we have to do some operations on that data, after that we require writing data to some files. Then in this situation we need not require data to store in database, we just want to do some operations on that data and copy that to some files. This requirement will be fulfilled by Message Queue.
If we take another example, where we have two applications in two different systems. One application will send the data and another will need to process the data.
Here I am explaining second example but two applications are in same system.
To run Message Queue concept in .Net, we need Message Queue service to be installed in our system. By default installation of Operating System, it will not install. To install Message Queue service
Start->settings->control panel ->Add/Remove programs-> Click on Add/Remove window components-> Check Message Queuing ->Click Next button
If you unable install, please refer documentation of corresponding Operating System.
After successful installation of Message Queue, you have to write the code to use it.
Here I am explaining Message Queue by using VB.Net.
In this example we need two applications, one is to store data in Message Queue and another is to retrieve data from Queue.
namespace is "System.Messaging"
Step 1: Push Data in Queue
static void PushDatainQueue(string QueueData, string QueueLable)
{
#region Queue
string strQuePath = string.Empty;
MessageQueue objMessageQueue = new MessageQueue();
strQuePath = @".\Private$\syccrm";
if (MessageQueue.Exists(strQuePath))
//creates an instance MessageQueue, which points
//to the already existing MyQueue
objMessageQueue = new System.Messaging.MessageQueue(strQuePath);
else
//creates a new private queue called MyQueue
objMessageQueue = MessageQueue.Create(strQuePath);
#endregion
#region Message
System.Messaging.Message objMessage = new System.Messaging.Message();
objMessage.Priority = MessagePriority.Normal;
objMessage.Body = QueueData;
objMessage.Label = QueueLable;
#endregion
#region Push Message in Queue
objMessageQueue.Send(objMessage);
#endregion
}
Step 2: Receive Data from Queue
static void ReceiveDataFromQueue(string strQuePath)
{
MessageQueue objMessageQueue = new MessageQueue();
if (MessageQueue.Exists(strQuePath))
objMessageQueue = new System.Messaging.MessageQueue(strQuePath);
else
objMessageQueue = MessageQueue.Create(strQuePath);
Message objMessage = new Message();
objMessageQueue = objMessageQueue.Receive();
String strOperation = objMessageQueue.Label.ToString();
string queueData = objMessageQueue.Body.ToString();
}
Note:for private and public queue syntax
// References public queues.
public void SendPublic()
{
MessageQueue myQueue = new MessageQueue(".\\myQueue");
myQueue.Send("Public queue by path name.");
return;
}
// References private queues.
public void SendPrivate()
{
MessageQueue myQueue = new
MessageQueue(".\\Private$\\myQueue");
myQueue.Send("Private queue by path name.");
return;
}
Tuesday, January 4, 2011
Parallel ForEach on DataTable
i would like to use the new Parallel.ForEach function to loop through a datatable and perform actions on each row.
Example:
Normal foreach loop
foreach (DataRow dr in objDS.Tables[0].Rows)
{
if (!objHT.Contains(dr["MessageKey"].ToString()))
objHT.Add(dr["MessageKey"].ToString(), dr["MessageValue"].ToString());
}
Parallel.ForEach loop for above DataTable
Parallel.ForEach(objDS.Tables[0].AsEnumerable(), dr =>
{
if (!objHT.Contains(dr["MessageKey"].ToString()))
objHT.Add(dr["MessageKey"].ToString(), dr["MessageValue"].ToString());
});
Example:
Normal foreach loop
foreach (DataRow dr in objDS.Tables[0].Rows)
{
if (!objHT.Contains(dr["MessageKey"].ToString()))
objHT.Add(dr["MessageKey"].ToString(), dr["MessageValue"].ToString());
}
Parallel.ForEach loop for above DataTable
Parallel.ForEach(objDS.Tables[0].AsEnumerable(), dr =>
{
if (!objHT.Contains(dr["MessageKey"].ToString()))
objHT.Add(dr["MessageKey"].ToString(), dr["MessageValue"].ToString());
});
Thursday, December 30, 2010
How to write some text in file from Sql Server
alter PROCEDURE spWriteStringToFile
(
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)
--
)
AS
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80)
set nocount on
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String
if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream
for more information please CLICK HERE
(
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)
--
)
AS
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80)
set nocount on
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String
if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream
for more information please CLICK HERE
How to read a file from sql server
Create FUNCTION [dbo].[uftReadfileAsTable]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS
@File TABLE
(
[LineNo] int identity(1,1),
line varchar(8000))
AS
BEGIN
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@String VARCHAR(8000),
@YesOrNo INT
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'OpenTextFile'
, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII
WHILE @hr=0
BEGIN
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
IF @YesOrNo<>0 break
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='reading from the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT
INSERT INTO @file(line) SELECT @String
END
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
insert into @File(line) select @strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set
RETURN
END
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS
@File TABLE
(
[LineNo] int identity(1,1),
line varchar(8000))
AS
BEGIN
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@String VARCHAR(8000),
@YesOrNo INT
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'OpenTextFile'
, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII
WHILE @hr=0
BEGIN
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
IF @YesOrNo<>0 break
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='reading from the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT
INSERT INTO @file(line) SELECT @String
END
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
insert into @File(line) select @strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set
RETURN
END
Subscribe to:
Posts (Atom)
Split the String values with a special character in MS Flow to convert this into Array
Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...
-
Sometimes you experienced when you have subgrid in your CRM Form, but when you click the ‘expand’ button to expand the view then it will re...
-
I have a web service that takes a serialize string and saves it. This works fine for "small" files, but once I hit a large siz...
-
Use the following line of code to create complete workflow activity with two output parameters. 1) Open Visual Studio 2010 ID. 2) Open ...
