Capture Messages Window Into Log File Using Powershell Program
Jun 25, 2014
As we know there are two output windows in sql server management studio like 'Results' and 'Messages', I want to capture that 'Messages' window into a log file using powershell program . like into a log file it should display like :
I am using an extended stored procedure that sends email using SMTP. The procedure can be downloaded here (http://www.sqldev.net/xp/xpsmtp.htm). If I execute the procedure in QA and it fails for some reason, the error message is printed in the Messages tab. Is there any way to capture this message/messages when I use the XP in my stored procedures?
Does anybody know of a way to set up a task which will send an email with an attachment other than using sp_sendmail. Sp_SendMail will run a query and send the results as a text file. But I need to run a stored procedure which generates an Excel spreadsheet then have an email sent with the Excel file as an attachment. The sp_sendmail text file just doesn't work with the information I am generating so I need to find another solution.
I am pulling files from the FTP site using the FTP task. I want to also capture the date and timestamp of each of these files so that I can insert the values into a database and track when are these files get created normally on the FTP server.
Dim strComputer Dim objRegistry Dim strKeyPath Dim strValueName Dim strValue Dim arrValues Dim intValue Dim strpath Dim strpath2 Dim strDBServerName Dim objSQLServer
strComputer = "."
Set objRegistry = GetObject("winmgmts:\" & strComputer & " ootdefault: StdRegProv")
what the program will do if we caught that exception .. i need some suggestions ... i got this exception(String or binary data would be truncated. The statement has been terminated.).. will it affect the functionality of the program...
I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
I have a flat file with several rows of entire type in one of the rows a string comes and when it goes away to guard in the BD it falls, since I can know in that this row of the flat file the string?????
the idea is to put together a folder that is in some place in the computer, like "c:excel_fles" and the database full backup in a single .bak file, the database can be recovered with sql server management studio(but no the folder), the restoration of the program can be done with a program, I think it can be done with msdb, but I'm a little lost in the road.
Let me preface by saying I am not very familiar with SSIS.
Ideally, since the Transfer SQL Server Objects task can do all tables, I would like to use it to copy only data from one server to a new server that has the tables pre-created. When I encounter any kind of error, in addition to the error information provided by SSIS, I also need the actual row data.
If using the Transfer Object task can't do that, how would I loop through all the tables on an OLEDB source and capture the same error information on the destination? I figured out how to do the Data Flow a table with a redirect error output but that does not give me the actual row data.
Ok, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be. 1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String) If sIncomingfile <> "" And sOutputDirectory <> "" Then Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None) f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read Dim file As New IO.FileInfo(sIncomingfile) Dim filefs As IO.FileStream = Nothing If file.Exists Then Try filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1 Catch ex As Exception SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1") Application.Exit() End Try End If Dim reader As New IO.StreamReader(filefs) Dim counter As Integer = 0 Dim CurrentFS As IO.FileStream Dim CurrentWriter As IO.StreamWriter Dim extension As String = IO.Path.GetExtension(file.FullName) If extension = ".mnt" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If IsNumeric(Line.Substring(0, 1)) Then Dim Parts() As String = Line.Split(" "c) ' split row into parts If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If End If End While If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() MoveFilesFTP(sOutputDirectory, "mnt") ElseIf extension = ".naf" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If End While If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() MoveFilesFTP(sOutputDirectory, "naf") End If Else 'input file not valid SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1") End If End Sub
I have a windows 8 pc that I just got and installed sqlexpress 2014. My buddy haw windows 7 and installed sqlexpress on his pc. We create a db on his pc, did a backup, copied the backup to my pc. In ssms I right click on "database" > restore database. click device and the button to find my file. I navigate to the folder where the file shows in file explorer but the .bak file does not show in ssms to restore from. This is probably a windows thing but I have don't know what to look at.
I am writing a automated backup script using job scheduler of the management studio and would like to know how can i copy the output messages given by the SQL Server to an external file automatically.
I have created a trigger to call a program that is written by our program. The program is basically read the record in the table and write to a text file, then delete the record from the table.
The trigger is a after insert trigger. After we added the trigger, we insert a record to the table. The result is that the record still and did not get deleted. Also, the text file didn't get created either. It seems that it take a long time for the record to be written to the table.
But if we just run the program (a exe file), it can write a text file in the folder and delete the record. the trigger is basically:
USE [Zinter] GO /****** Object: Trigger [dbo].[ZinterProcess] Script Date: 04/29/2014 18:34:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
I am working on an HR project and I have one final component that I am stuck on.
I have an Excel File that is loaded into a folder every month.
I have built a package that captures the data from the excel file and loads it into a staging table (transforming a few bits of data).
I then combine it with another table in a view.
I have another package that loads that view into a Master table and I have added a Slowly Changing Dimension so that it only updates what has been changed. (it’s a table of all employees, positions, hire dates, term dates etc).
Our HR wants to have this data in a report (with charts and tables) and they wanted it to be in a familiar format. So I made a data connection with Excel loading the data into a series of pivot tables.
I have one final component that i cant seem to figure out. At the end of every year I need to capture a count of all Active Employees and all Termed employees for that year. Just a count.
The data is in one table labeled [EEMaster]. To test the count I have the following.
SELECT COUNT([PersNo]) AS HistoricalHC FROM [dbo].[EEMaster] WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Active'
this returns the HistoricalHC for 2013 as 418.
SELECT COUNT([PersNo]) AS NumbOfTermEE FROM [dbo].[EEMaster] WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Withdrawn' AND [TermYear] = '2013'
This returns the Number of Termed employees for 2013 as 42.
I have created a table to report from called [dbo.TORateFY] that I have manually entered previous years data into.
Hello all I have a sql file that I want execute by using the cmdsql command line. But when I create a text file I receive two Informational Messages: 1. "Changed database context to 'DataBaseName'." 2. (2 row(s) affected) How can I ignore these messages in my text file? there is a parametter or something elese to configure to avoir these Informational Messages?
Below is the test data and my query. Basically i am trying to get the File records along with if any error messages occurred. I am getting the output as expected but the message column has "," at the front and sometime at the end of the column. How do i avoid that.
declare @Files table (ID int identity(1,1) primary key,Filename varchar(50),Date_Created datetime) insert into @Files (Filename,Date_Created) select 'File1',GETDATE()-1 union all select 'File2',GETDATE()-1 union all select 'File3',GETDATE()-1 union all
When I install the SQL Server 2005 SP2 on my vista laptop, I get the following message: SQL server setup failed to compile te Managed Object Format (MOF) file c:program filesmicrosoft SQL Server90sharedsqlmgmproviderxpsp2up.mof. To proceed, see "troubleshooting an installation of SQL Server 2005" or "how to: View SQL Server 2005...
In MSDN, it says that it is recommended to use windows authentication to connect to SQL Server rather than use mixed authentication.
I create user deltasqluser on windows OS, and I specify in my webform ASP.NET script below :
protected System.Web.UI.WebControls.Label Label1; private string _connString = @"data source=deltasql2000;initial catalog=northwind;integrated security=false;user id=deltasqluser"; /* comment : I login to my windows as deltakoronx, and I want to every user (including me), connected to sql server through IIS, will be identified as deltasqluser not as user's login (impersonate) */ private void Page_Load(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(_connString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select suser_sname()";
at web.config, I add : <identity impersonate="false" userName="deltasqluser" password="" />
at IIS webApplication1's properties, tab "Directory Security", at "Authentication and access control" section, I checked "enable anonymous access" with user : DELTAIUSR_DELTA and checked "Integrated Windows Authentication",
at query analyzer, I login as "sa" and execute script below : exec sp_grantdbaccess 'deltasqluser','northwind'
when I run the ASP.NET script, error at conn.Open(); with error message : Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
What should I do so that IIS login to SQL Server as user deltasqluser not as "NT AUTHORITYNETWORK SERVICE" ?
I am trying to install SQL Server 2000 on a Win2K OS machine but Iget this error message:"A previous program installation created pending file operations onthe installation machine. You must restart the computer before runningsetup."I see others have had this problem but have fixed it using the solutionin:http://support.microsoft.com/defaul...kb;en-us;312995I have tried the solution but to no avail. The registry key:HKEY_LOCAL_MACHINESYSTEMCurrentControlSetContro lSessionManagerPendingFileRenameOperations keeps reappearing as soon as Iclose RegEdit.Any ideas?Thanks,lq
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
i am using powershell to get the output..I am planning implement sorting to my output.below is the format I am writing my objects to html file.I want sort the object of lastrunoutocome field.
Hi, I am trying to get performance data using powershell. When I run the command "get-wmiobject -list" I get classes like Win32_PerfRawData_MSSQLSQLEXPRESS1_MSSQLSQLEXPRESS1Locks. However when I run the command
get-wmiobject -query "select * from Win32_PerfRawData_MSSQLSQLEXPRESS1_MSSQLSQLEXPRESS1Locks" powershell gives me an error. Why is this happening? any help in this regard is appreciated.
I would get from scripting the replication out from SQL Server itself. However, there are a couple of things that are not showing up in the script,Adding the option to IncludeUninstallDistributor or UninstallDistributor doesn't appear to add any code to actually do that. i.e. the below code is never added.
/****** Uninstalling the server as a Distributor. Script Date: 10/1/2015 4:34:13 PM ******/ use master exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1 GO Also, adding the reverse for the create script generation, IncludeInstallDistributor or InstallDistributor, does not add the below code. use master exec sp_adddistributor @distributor = N'xxxxxx', @password = N'' GO
The purpose of the script is simply to script out all drop/create scripts for all publications and subscriptions, run the drop script, restore over the databases using backups of non-replicated databases, run the create replication script, and have it re-sync.
I would like to use powershell commands within my TSQL scripts. how to do that?
The reason is that up until now I have been using SQLCMD and XP_CMDSHELL to rename files before importing them into a database. Powershell is a better tool for this job.