Grab Last Word
Sep 2, 2013how can i only grab the last 4 ID:
data
19Jul hello ID UKAU
9Jul hello ID UJKO
7Jul hello ID POJP
ADVERTISEMENT
Can I Change Null Word In Report To Another Word
Feb 24, 2008i have report with parameter and he can have a null in parameter ther is null word can i change it to another word like all or any thing else
View 4 Replies View RelatedSql Search Command Word By Word?
Mar 30, 2005hi!
I'm just wondering does anyone know how to create an sql command that can search word by word?
what i mean is like I have a product with name 'harry potter broom'.
I want an sql command where if i type only 'harry broom' this 'harry potter broom' product will show up.
Does anyone have any idea?
Here's my sql comand: (I'm using asp.net vb script do develop this system)
-------------------------------------------------------------------------
query = "select distinct * from product where " & _
"(pname like '%" & keyword & "%' or " & _
"pdesc like '%" & keyword & "%' ) and " & _
"(price >= " & price1 & " and price <= " & price2 & _
") and status <> 'out of stock' order by price asc"
-------------------------------------------------------------------------
Thank you.
How To Grab In Between Data
Mar 25, 2014I have a list of data sample as below:
MYDATA
2/26/2014 19:35:0 2/26/2014 19:35:0 2/26/2014 22:25:0 UK AUS start end
3/1/2014 9:20:0 3/1/2014 9:20:0 3/1/2014 10:5:0 AUS UK start end
How can I only grab as below:
newColumn
UK AUS
AUS UK
Is It Possible To Grab Data From 2 Different Databases
Jan 28, 2004Hello-
I have a chart I am producing in .net and I need to values. The problem is the values (sums) i need are on 2 different servers. Is there any where to combine the query using two databases? Does anyone have any suggestions?
thanks
How To Grab User Name Into The Table
Aug 21, 2001I want to include the name of the user whom so ever has changed the record.
I want to insert the name of the user in the column.
How to grab the name of the user from the action...?
Thanks,
Harish
Table Name Changes - How To Grab Last 24 Hours
May 30, 2014My DB saves it's data into a table at the end of each day like:
'e4_event_20140530' where the last bit changes according to the date. So 30th May 2014 in this case.
What I am trying to do is query the last 24 hours. I know i can grab from 2 tables and do a 'between' with times but it means having to change table name and times in the query every time i run it. I'd just like to run it and for it to just fetch the last 24 hours at any point in time.
My DB outputs time like '2014-05-30 08:54:23'
How To Grab Specific Data
Aug 4, 2014<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="ABC"/>
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="KJHKH"/>
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="POJ"/>
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="AKJFISM"/>
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="PM"/>
how can i only grab the arrival code?
newColumnArrivalCode
ABC
KJHKH
POJ
AKJFISM
'
'
'
'
VB Programaticlly Grab Data From A DB Noob
Mar 6, 2008 Hi, I am very new to the .net framework, but I have a lot of experince in php/mysql applications. So this is this is proboly going to seem like a nwebie question.
I am trying to pull a single item out of the database programaticlly, take the value of that item and set it to a varible so I can work with it in other areas.
Here is basically what I have this is on the vb page in a sub I have the varible MonthlyPrice Defined earlier in the pageDim connString As String = _ConfigurationManager.ConnectionStrings("ConnectioString").ConnectionString'Create a SqlConnection instanceUsing myConnection As New SqlConnection(connString)'Specify the SQL queryConst sql As String = "SELECT Price FROM Plans Where PlainID='" & PlanId & "'"'Create a SqlCommand instanceDim myCommand As New SqlCommand(sql, myConnection)'Get back a DataSetDim myDataSet As New DataSet'Create a SqlDataAdapter instanceDim ReadData As New SqlDataReader(myCommand) 'Bind the DataSet to the GridViewMonthlyPrice = ReadData("Price")'Close the connectionmyConnection.Close()End Using
I just want to get the price of the plan where planID is equal to the value of my varible planID. I want to set that equal to MonthlyPrice which is a decimal so I can add it into some calculations and return a value. I don't really want to bind the data to anything but everytime I search google I can't find anything excpet for binding data to grideview and things like that.
I have tried several diffrent approaches that I have found out there but have gotten errors or and things I am not understanding.
I originally was trying to access a datasource I already have defined that is on the aspx page.<asp:SqlDataSource ID="SQLSelectPlan" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Plans] WHERE ([PlanID] = @PlanID2)">
<SelectParameters>
<asp:FormParameter FormField="grSelectPlan" Name="PlanID2" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Is there a way for me to grab this datasource which works and is tied to a detailed view, take the value in the price collom and stick it in the MonthlyPrice varible on my vb page? That is the approach that seems the most logical to me.
If someone has better way I would really like to know.
Thanks
Inserting New Row, Need To Grab Key From Another Table Thats Just Inserted
Oct 11, 2004i have 2 tables right now, po and notes...
the notes table contain the note_id and notetext, so example of a row would be:
1 this is the note for the first row
2 this is the note for computer parts
etc..
the po table has item info, description, po_id, and note_id, heres an example:
po_id item desc note_id
1 215-33 computer parts 2
the problem is, is there a way where say i insert the note row into the notes table first and then get the note_id of that, so that i can then insert the row into the po table which includes the note_id from the notes table?
would i have to use two separate inserts or can i use one? i was going to just insert the notes row first, and then use a query using the LIKE() function on the note text to get the note_id, but some notes may be the same as others, so the note_id wouldn't be the correct one :( and i think just getting the last row of the notes table would be bad too incase someone happens to insert a row into that table at the same time
any ideas please?
thanks!
How Can I Grab A Notification Email Address From A Job?
Dec 9, 2004Is there a handy-dandy way for me to be able, from a stored procedure, to capture the notification email address for a user associated with a job?
I need to send an email out from a stored procedure, and I want it to be set up to send to the same user that is set up in the job that calls the stored procedure.
the same notification stored proc will be used in multiple jobs, but the jobs are already set up with a notification email to be sent to the defined users "on completion" (error or not). I want a separate email to be sent, but I want to use xp_Sendmail because I have to perform a select who's output I want to appear in the body of the email (and unless I am missing something, I have no control over the body of the standard "job completion" email).
I would prefer to NOT have to "hard code" the user email address in the stored proc that calls xp_sendmail, OR to put it in some user table in the database, when the user I want to send it to will always be the same one defined in at the JOB level in Enterprise Manager.
Any thoughts? Thanks in advance for your instantaneous and informative answer-packed responses! ;)
Grab Output Of Dynamic Sql And Use As Variable
May 5, 2008hello,
I'd like to know how I can grab the output of the following code and use it as variable:
declare @sql nvarchar(25)
set @sql = 'SELECT CURRENT_TIMESTAMP'
EXEC(@sql)
Thank you.
Create Another Column To Grab In Between Data
Apr 28, 2014ColumnA
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 8769 datas, 9.20USD cash"/>
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 1000 datas, 14.40USD cash"/>
I have a list of data as above.How can i create another new column just to grab the data?
NewColumn
8769
1000
Want To Grab A List Of Available Databases On A Given Server
Jul 23, 2005I have MSDE running on my system and I want to get a list of databasesfor that server programmatically. I'm using vb.net and I want to view,add, delete and modify databases on a server from within a class.I'm aware I can use the "Server Explorer" feature in Visual Studio, butthat isn't what I'm looking for. Any references or suggestions on whereto start are appreciated.TIARalf
View 7 Replies View RelatedFinding Code That Will Automatically Grab 3 Digits Before Dash?
Jan 6, 2014I have the following table with two columns:
SEQ_NUM STATUS_CODE
101 D12
102 DWI - REGULAR DWW - EXPIRED
103 WRW - REG WRT - FUTURE
I would like to output the following:
SEQ_NUM STATUS_CODE
101 D12
102 DWI
102 DWW
103 WRW
103 WRT
The Status code column in the source table will have a single 3 DIGIT alphanumeric code, and sometimes a longer string. I need a code that will automatically grab the 3 digit code before the dash.
String Parsing - Grab Some Key Value Pairs From Text In Column
Jul 21, 2014I want to grab some key value pairs from the text in sql column
e.g.
some text[Key1=Val1]some text[Key2=Val2][Key3=Val3]some text
I want a function which takes Keyname as input & returns the value related with it if found.
GetValueFmKey('Key1') should return Val1 and like on.
SQL Select Statement (Textbox Used To Grab Data From Database)
Sep 21, 2007I have a problem....SOMEONE PLEASE HELP!
Here is the setup.
Text Box: User enters in customer transaction number
Button: User clicks button to display information about the customer
Now the database has a lot of unique customer numbers. What I am trying to do is take what the user enters so it can search the database and pull out that customers information. I am having a hard time getting that information from the textbox. Any suggestions! Here is what I have so far.
Private Sub btnViewFlow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewFlow.Click
Try
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.DataSet11 = New links.DataSet1
CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=<14852>;packet size=4096;user id=<userID>;password=<Strong Password>;data source=ZRTPD0WB;p" & _
"ersist security info=False;initial catalog=DTR"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "DTR_Document_Summary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DocumentId", "DocumentId"), New System.Data.Common.DataColumnMapping("PartnerId", "PartnerId"), New System.Data.Common.DataColumnMapping("PartnerName", "PartnerName"), New System.Data.Common.DataColumnMapping("Direction", "Direction"), New System.Data.Common.DataColumnMapping("TranSet", "TranSet")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT DocumentId, PartnerId, PartnerName, Direction, TranSet FROM DTR_Document_S" & _
"ummary "
'WHERE (DTR_Document_Summary.PartnerId = 'txtPartnerId.text.toString')"
'THE STATEMENT ABOVE DOESNT WORK
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
'
'DataSet11
'
Me.DataSet11.DataSetName = "DataSet1"
Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
'Open the connection
SqlConnection1.Open()
TextBox1.Text = "Connection Open"
'Populate DataSet11
SqlDataAdapter1.Fill(DataSet11)
TextBox1.Text = "DataSet11 has been filled!"
'Display Data
DataGrid1.DataBind()
TextBox1.Text = "Here is your requested information"
Catch ex As Exception
TextBox1.Text = ex.Message
End Try
'Close the connection
SqlConnection1.Close()
End Sub
How Do You Grab Keys From Dimensions And Store Them Back Into Your Table
Nov 16, 2006I have run into an issue that seems very simple but I am new to SSIS and DBs in general and therefore cannot solve it. I would like to take data from a SQL Server table, lookup a key from a dimension table and update the same SQL Server table with this data. Is there anyway to do an update using SSIS?
Thanks,
Curtis
How Do I Grab A Simple List Of SQL Server Views, Tables Or Sprocs?
Jul 20, 2005I need just the names of tables, views and sprocs within a SQL Serverdatabase. What's the easiest way to do this?
View 3 Replies View RelatedIs The Word Name A Reserved Word?
Sep 24, 2007
Is the word "Name" a reserved word in SQL? look at line 10 of my stored procedure. When I use the word "Name"it is highlited in blue by SQL Server?
Note I only list part of the stored proc
1 CREATE PROCEDURE [dbo].[GetXMLPeopleNames] 23 (4 @Status nvarchar(3)5)6 AS7 SELECT8 PersonId,9 PersonDescription,10 Name,11 UpdateDate,12 UpdateAppUser13 FROM14 Customer WHERE Customer.PersonDescription=@Status15 ORDER BY
Convert Microsoft Word XML To PDF Without Using Microsoft Word
Jan 19, 2007Hi all,
I work for a financial company where we send out lots of correspondence to clients on a daily and monthly basis. This would typically be something like a financial report, account statements, etc. We've decided to use PDF as the format which these documents must be in when our clients receive them.
Our marketing and sales departments designs the templates of these documents using Microsoft Word. They can save these MS Word documents in the Word XML format (either 2003 or 2007). We want it in XML format, because it is text based, and we can therefore string replace the content to update the template with a client€™s information. In the same token RTF would also do, but the file size when converted to RTF is a problem.
What I am looking for is a way to convert this XML into PDF using something like XSL-FO - i.e. we do not want to use the Word Interopt on our Production Server, firstly it is a little slow and secondly the production server is unmanned and we cannot allow a WinWord.EXE process to not close successfully for some or other reason.
I've tried a number of companies' evaluation software to try and achieve this (converting the WordML to PDF via XSL-FO), but so far no luck.
Does anybody know of a stylesheet out there with which I will be able to achieve this? Or an alternative method of converting MS Word files into PDF without using Word to do it?
Thank you in advance.
Regards
Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
Dec 28, 2005I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedWord And SQL 7.0
Feb 22, 2000Ever store a word document in a sql table...? Just wondering how you did it...
Thanks!
Dean
How To Get Last Word Of URL
Aug 5, 2013DECLARE @url VARCHAR(1000)
SET @url ='http://www.microsoft.com/technet/security/bulletin/ms11-022.mspx';
How to get the result like 'ms11-022.mspx'
Word Xp
Sep 30, 2005I have Sql Server 2000 and Word from Office Xp. Since a few weeks, I cannotimport Data from my database towards a Word document. I follow theprocedure, but at the end, it tells me Word cannot find the data source. IfI use Excel, well it works perfectly. What is the gig?thanks
View 5 Replies View RelatedRdl To Word Doc ??
Jun 18, 2007Hi,
Can anyone assist me to convert .rdl files into an word file ?
Shall i need to write a program ??
Regards
Harsh
Key Word Like
Sep 5, 2007Can anyone find what is wrong with this? I think I am using the keyword "Like" correctly. I am trying to enforce some rules by doing this this way. No obvious typos or syntax errors are jumping out at me.
CREATE DATABASE VetClinic
ON
(NAME='VetClinic',
FILENAME = 'c:Program FilesMicrosoft SQL ServerMSSQL.1mssqldataVetClinic.mdf',
SIZE = 100MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
LOG ON
(NAME = 'VetClinicLog',
FILENAME = 'c:Program FilesMicrosoft SQL ServerMSSQL.1mssqldataVetClinic.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB)
GO
CREATE TABLE Customers
(
CustID int IDENTITY NOT NULL PRIMARY KEY,
FirstName varchar(15) NOT NULL,
LastName varchar(15) NOT NULL,
StreetAddress varchar(25) NOT NULL,
City varchar(20) NOT NULL,
CustState char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Phone1 char(13) NOT NULL
LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",
Phone2 char(13) NULL
LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",
Email varchar(30) NULL,
DateInSystem smalldatetime NOT NULL
DEFAULT GETDATE()
)
GO
CREATE TABLE PetDetails
(
PetID int IDENTITY NOT NULL PRIMARY KEY,
CustID int NOT NULL
FOREIGN KEY REFERENCES Customers(CustID),
PetName varchar(20) NOT NULL,
BreedID int NOT NULL,
Gender char(1) NOT NULL
LIKE "m" OR "f",
Weight decimal NOT NULL,
DOB smalldatetime NULL,
DateFixed smalldatetime NULL,
DateLastSeen smalldatetime NULL,
VetLastSeen int NULL
)
GO
CREATE TABLE Employees
(
EmpID int IDENTITY NOT NULL PRIMARY KEY,
PositionID int NOT NULL
FOREIGN KEY REFERENCES Positions(PositionID),
FirstName varchar(15) NOT NULL,
LastName varchar(15) NOT NULL,
SSN char(11) NOT NULL
LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]",
StreetAddress varchar(25) NOT NULL,
City varchar(20) NOT NULL,
CustState char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Phone1 char(13) NOT NULL
LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",
Phone2 char(13) NULL
LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",
Email varchar(30) NULL,
DateInSystem smalldatetime NOT NULL
DEFAULT GETDATE(),
HireDate smalldatetime NOT NULL,
TerminationDate smalldatetime NOT NULL,
PaySatus char(6) NOT NULL
LIKE "hourly" OR "salary",
PayAmount money NOT NULL
)
GO
CREATE TABLE Appointment
(
ApptID int IDENTITY NOT NULL PRIMARY KEY,
EmpID int NOT NULL,
ApptDate smalldatetime NOT NULL,
ApptTime smalldatetime NOT NULL,
PetID int NOT NULL,
CustID int NOT NULL,
)
GO
CREATE TABLE PetProcedures
(
ProcedureID int IDENTITY NOT NULL PRIMARY KEY,
ProcedureDescrip varchar(20) NOT NULL,
Cost money NOT NULL
)
GO
CREATE TABLE Breed
(
BreedID int IDENTITY NOT NULL PRIMARY KEY,
BreedName varchar(20) NOT NULL
)
GO
CREATE TABLE Prescriptions
(
PrescriptionID int IDENTITY NOT NULL PRIMARY KEY,
PetID int NOT NULL,
MedID int NOT NULL,
EmpID int NOT NULL,
DatePrescribed smalldatetime NOT NULL
)
GO
CREATE TABLE Medications
(
MedID int IDENTITY NOT NULL PRIMARY KEY,
MedName varchar(30) NOT NULL,
MedType varchar(20) NOT NULL,
Dosage varchar(20) NULL,
Frequency varchar(10) NOT NULL
LIKE "daily", "weekly", "monthly" OR "annually"
)
GO
CREATE TABLE Positions
(
PositionID int IDENTITY NOT NULL PRIMARY KEY,
PositionName varchar(20) NOT NULL
LIKE "vet", "tech", "front desk" OR "office manager"
)
- -----------------------------------------------------------------------------------------------------------------
Here are the errors that I got
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'LIKE'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'LIKE'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'LIKE'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'LIKE'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'LIKE'.
Cutting To A Certain Word
Nov 29, 2006Hi there, i need to know how to cut a string to the nearest word. For example, i've got an article and i need to extract just a part of the beginning, i could use LEFT([content], 250) but there is little chance this will cut on a word. Therefore i need to know if there is a function that will cut to the nearest word in T-SQL or i will simply put a summary field in the database. (I prefer to generate the summary on the fly if possible)
View 3 Replies View RelatedHow To Do A Word Search?
Dec 29, 2006I need to be able to search article in several tables in a MSSQL DB. What is the best way to do a word search? Are there and freebies out there or code examples?
Dave
Length Word In Sql
May 5, 2008Is there a function how I can see how many caracters a word from my sql is?
for example:
if I want the lenght of the word "sql" => 3
if I want the lenght of the word "forum" => 5
Word Count
Aug 3, 2007Hi,
I was hoping somone could help. I need to count the number of words in a specfic column in a table if i didn't know what the words said.
I can count the number of words using
DECLARE @String VARCHAR(4000)
SET @String = ' words to be counted'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1
AS Results
but this requires me to know what the words are and not count them from the column.
Export To Word
Mar 19, 2007Not sure if i am in the right forum or not.
I have been given the task to create a mail merge application.
For time sakes while this is being built i need to just export the name and address in to a pre written letter in word. how can i do this?
Getting The Word Out...opinions?
Nov 9, 2004Hi all,
I took a search through the archives for related topics (and got Des in trouble along the way :( ) but couldn't find a directly related thread. If I missed one, feel free to tell me where to go (hey...watch that...only if I MISSED one!)
I wrote what is, essentially, a data verification stored proc that goes out to each of FOUR servers we have - each one running a mirror database. In a nutshell, there is one table that contains a row with a column in it that, if everything has gone well in the daily processing in all 4 databases, will match identically between all 4 DBs.
So, that said, here is the output: Job 'Index - Verify PortfolioIndex Across Servers' : Step 1, 'PortfolioIndex Check across all servers and portfolios' : Began Executing 2004-11-09 15:30:00
------------------- BEGINNING PortfolioIndex VERIFICATION -------------------- [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 2 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 3 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 11 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 67 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 72 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 84 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 90 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 92 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 100 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 105 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 110 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 115 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 120 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 125 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 130 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 135 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 140 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 145 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 150 on 11/09/2004! [SQLSTATE 01000]
WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 155 on 11/09/2004! [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 160 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 110.582 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 110.582 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1000 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 189.623 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 189.623 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1001 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 164.058 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 164.058 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1002 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 255.978 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 255.978 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1003 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 159.009 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 159.009 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1004 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 318.981 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 318.981 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1005 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 145.921 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 145.921 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1006 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 141.035 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 141.035 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1007 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of NULL [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1008 on 11/09/2004! [SQLSTATE 01000]
--> Server TA1 shows an index of 123.179 [SQLSTATE 01000]
--> Server TRADEANALYSIS shows an index of 123.179 [SQLSTATE 01000]
--> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
--> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
------------------- COMPLETE -------------------- [SQLSTATE 01000]
This was cut-n-pasted here from a log file created by the actual SQL SERVER 2000 job created to run the afore-mentioned stored procedure.
After all that...my quandry is this:
What is the best way to send this info out in an email format to interested parties? Currently I have the job send out an email notification on completion, but that still requires my lazy buttocks to go look at the log file in the job (or, more accurately, on the server in the logfile directory).
I want to get the actual DATA as shown above into the email.
As I see it, my options are:
(1) write the data out to a flat file during the run (or, as is done now, into a log file by the SQL Server scheduled job) and then attach that FILE to the email - this still requires my lazy buttocks to OPEN the attachment that comes with the email.
ro (2) write the message out a line at a time to a table with an IDENTITY column (used to order them on the select) and a VARCHAR(128) column that each line in the log would be written to. This option allows me to just do a SELECT in the call to xp_sendmail to get the data into the actual email...but I just really hate the idea of creating a permanent table for this cheesy solution.
I tried it with a temp table within my stored proc, but of course, when I made the call to xp_Sendmail, it can't see my temp table in order to select from (mind you, it's not that I mind USING a cheesy table, just that I don't want it to have a lifespan longer than the time I need to use it and toss it aside)
I know the common denominator here is "My Lazy Buttocks", but I really can't understate the laziness of my buttocks, so this is a valid concern ;)
Any thoughts? How do people get status messages like this into an email without using an attachment or a cheesy middleman table?
Sorry, as always, about the miniseries...just trying to set the mood before popping the question ;)