|
I took the books online sample and modified it with my data. When I run the query it sends the email, with the Header, and nothing else. The Table data never shows. Here is the code I used;
USE NMS_RT
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>MCP Alarms for previous 24 Hours</H1>' +
N'<table border="1>' +
N'<tr><th>Wayside_Name</th><th>ST</th><th>Alarm_Message</th>' +
N'<th>Error_Count</th><th>Error_Date</th><th>Alarm_State</th></tr>' +
CAST ( ( SELECT td = RT_MCP_Name, '',
td = RT_MCP_State, '',
td = RT_MCP_Alarm_Text, '',
td = COUNT(RT_MCP_Alarm_Text), '',
td = CONVERT(char(10), RT_MCP_Date_Time, 111), '',
td = RT_MCP_Bit_State, ''
FROM RT_MCP_Historic_Alarms
WHERE [RT_MCP_Date_Time] >= Convert(char(10), DateADD(DAY, -1, GETDATE()), 101) AND [RT_MCP_Date_Time] < Convert(char(10), GETDATE(), 101) and RT_MCP_Bit_State > 0
GROUP BY RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, RT_MCP_Bit_State, CONVERT(char(10), RT_MCP_Date_Time, 111)
HAVING COUNT(RT_MCP_Alarm_Text) >= 10
ORDER BY CONVERT(char(10), RT_MCP_Date_Time, 111), COUNT(RT_MCP_Alarm_Text) DESC, RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, RT_MCP_Bit_State
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients='user@anywhere.com',
@subject = 'MCP Alarms for Previous 24 Hours',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'test';
Any suggestions?
|
|
|
|
|
The answer was a simple. I missed a double quote in this field;
N'<table border="1>' It should read this N'<table border="1">'
|
|
|
|
|
hi there,
i have a problem that is when i try to copy my sql database files it gives error that "Cannot copy : It is being used by another person or program"
however i just start my pc but it gives the error
i also checked it by task manager and found some sql applications that are the cause of it, if i end them then my app. dont work
is there any way that i can copy my database files?
thanks
Becoming Programmer...
|
|
|
|
|
You can't copy the data files if the database is in use.
You should take the database offline if you want to copy the data files. Alternatively, you could take a backup of the database which doesn't require it to be taken offline.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
how can i offline database?
Becoming Programmer...
|
|
|
|
|
Right-click the database in Management Studio, select 'Tasks' and 'Take Offline'.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
thanks dude
rated 5
Becoming Programmer...
|
|
|
|
|
hi,
i have formed query using several joins. Now that i want the data to be unique, so thuoght of using 'distinct' but it does not seem to help.
There is a column named Id in the query and i dont want this to be duplicated hence i gave select distinct(id), .......
but this showed up some error. when using distict it is applying for the entire row, so its checking for each and evry column. Is it possible to use distinct keyword for one or two coulmns instead for the entire row i.e. all columns
gauthee
|
|
|
|
|
gauthee wrote: Is it possible to use distinct keyword for one or two coulmns instead for the entire row i.e. all columns
No. Adding the DISTINCT keyword specifies that only unique rows can appear in the resultset.
You should add a GROUP BY clause to your query and group on the fields where you do not want duplicate values in the resultset.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi All
i have this Stored Procedure, i want to use it in my VB.NET to Accept intoput and put a record in the table
Create Proc Insert_Client
(@inClientName varchar(30),
@inContactPerson Varchar(30),
@inClientAddress Varchar(38),
@inClientTelephone varchar(15),
@inClient_Cell_NO varchar(15),
@inSerialNumber varchar(30),
@inProduct_Status int,
@inClient_Email varchar(34))
as
insert into Clients (Client_Name,Contact_Person,Purchase_date,Client_Address,Client_Telephone, Client_Cell_NO,SerialNummber,Product_Status,Client_Email)
Values(@inClientName,@inContactPerson,Getdate(),@inClientAddress,@inClientTelephone, @inClient_Cell_NO, @inSerialNumber,@inProduct_Status,@inClient_Email) .
So i created a method and this method like this
Public Sub AddContacts(ByVal ClientName As String, ByVal ContactPerson As String, _
ByVal ClientAddress As String, ByVal ClientTelephone As String, _
ByVal Client_Cell_NO As String, ByVal SerialNumber As String, _
ByVal Product_Status As Integer, ByVal Client_Email As String)
Dim con As SqlConnection
Dim cmd As New SqlCommand
cnClients = New SqlConnection
'Declaring the Parameters
Dim prmClientName As New SqlParameter
Dim prmContactperson As New SqlParameter
Dim prmClientAddress As New SqlParameter
Dim prmTelephone As New SqlParameter
Dim prmClient_Cell_No As New SqlParameter
Dim prmSerialNumber As New SqlParameter
Dim prmProduct_status As New SqlParameter
Dim prmClient_Email As New SqlParameter
con = New SqlConnection(constr)
cmd.Connection = con
'Adding a Paramer
cmd.Parameters.Add(prmClientName)
With prmClientName
.ParameterName = "inClientName"
.SqlDbType = SqlDbType.VarChar
.Size = 30
.Value = ClientName
End With
'Adding a Paramer
cmd.Parameters.Add(prmContactperson)
With prmContactperson
.ParameterName = "inContactperson"
.SqlDbType = SqlDbType.VarChar
.Size = 30
.Value = ContactPerson
End With
'Adding a Paramer
cmd.Parameters.Add(prmClientAddress)
With prmClientAddress
.ParameterName = "inClientAddress"
.SqlDbType = SqlDbType.VarChar
.Size = 38
.Value = ClientAddress
End With
'Adding a Paramer
cmd.Parameters.Add(prmTelephone)
With (prmTelephone)
.ParameterName = "inClientTelephone"
.SqlDbType = SqlDbType.VarChar
.Size = 15
.Value = ClientTelephone
End With
'Adding a Parameter
cmd.Parameters.Add(prmClient_Cell_No)
With prmClient_Cell_No
.ParameterName = "inClient_Cell_No"
.SqlDbType = SqlDbType.VarChar
.Value = Client_Cell_NO
.Size = 15
End With
'Adding a Parameter
cmd.Parameters.Add(prmSerialNumber)
With prmSerialNumber
.ParameterName = "inSerialNumber"
.SqlDbType = SqlDbType.VarChar
.Value = SerialNumber
.Size = 30
End With
'Adding a Parameter
cmd.Parameters.Add(prmProduct_status)
With prmProduct_status
.ParameterName = "inProduct_status"
.SqlDbType = SqlDbType.Int
.Value = Product_Status
.Size = 4
End With
'Adding Parameter
cmd.Parameters.Add(prmClient_Email)
With prmClient_Email
.ParameterName = "inClient_Email"
.SqlDbType = SqlDbType.VarChar
.Value = Client_Email
.Size = 30
End With
cmd.CommandText = "Execute Insert_Client"
con.Open()
cmd.ExecuteNonQuery()
con.Close() .
And this method will be called in my form like this
Dim WS As Admin_DAL.Clients
Try
WS = New Admin_DAL.Clients
WS.AddContacts(txtname.Text, txtcontactperson.Text, txtadress.Text, txttelephone.Text, txtcellnumber.Text, txtserialnumber.Text, CInt(cmbstatus.Text), txtemail.Text)
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
.
Now,am accepting input from the vb form textboxes. the above code will be in my save button. when i try to save i get the Following Error.
Line1:Incorrect syntax near 'inClientName'. .
Remember i have declared this in the Procedure.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Missing an @
With prmClientName
.ParameterName = "@inClientName"
.SqlDbType = SqlDbType.VarChar
.Size = 30
.Value = ClientName
End With
|
|
|
|
|
hi Paddy
Thanks for your reply
i have add the "@" before the variable as you said, now i get another Error
Procedure 'Insert_Client'excepts parameter '@inClientName',which was not supplied
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
hi all
i wount to know if
the sql server 2005 Developer Edition Souport the SQlServer authentecation
thanks for any body hellp me
Thaer
|
|
|
|
|
Yes it does.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
how can i download backup of messagese as a file
|
|
|
|
|
Hi,
My aim is to insert a image in to a picture box from the access database
As we know that access database stores images in the bitmap format with Ole object as the datatype.
To do this task i am having two codes
byte []b ;
b = (byte[]) bincomm.ExecuteScalar();
if (b.Length > 0)
{
MemoryStream stream = new MemoryStream(b, true);
stream.Write(b, 0, b.Length);
$$ Image i = Image.FromStream(stream);
pbox.BackgroundImage = i;
}
bindcon.Close();
for this code a exception is thrown at line $$ saying invalid parameter .........
if anybody knows how to do this please let me know as i have strucked at this point
regards
sindhu tiwari
Always Innovative
|
|
|
|
|
i have checked the contents in the tables for one of the tables in msaccess
what i found was that the first record of the table was filled with the values as [select].
can somebody tell me why it appeared that way?
|
|
|
|
|
Hello, im trying to create a simple web service that allows a user to insert data (from a form) into a DBtable. Here is my webMethod:
-----------------------------------
<webmethod()> _
Public Sub SubmitRatingsForCourse(ByRef dataSet As DataSet)
Using connection As New SqlConnection("ConnectionStrings:RatingsConnection")
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = New SqlCommand("SELECT ID, CourseID, Rating, Review, ReviewID, DateStamp FROM RatingsTable", connection)
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)
connection.Open()
Dim dSet As DataSet = New DataSet
adapter.Fill(dSet, "RatingsTable")
Dim INSstr As String
INSstr = "INSERT INTO [RatingsTable] ([ID], [CourseID], [Rating], [Review], [ReviewID], [DateStamp]) VALUES ('@ID', '@CourseID', '@Rating', '@Review', '@ReviewID', '@DateStamp')"
builder.GetUpdateCommand()
adapter.Update(dSet, "RatingsTable")
End Using
End Sub
----------------------------------------
Now when I try to create the clientSide that consumes the service I:
- Launch Visual Web Developer 05 Express.
- Create the WebReference
- Drag a detailsView onto the page
- Create an ObjectDataSource
- Choose my BusinessObject and click next
- Then for the *Select* I choose GetRatingsForCourse() Returns DataSet
- Then in the *insert* tab I choose SubmitRatingsForCourseAsync(DataSet dataSet)
Then when I go to test, the page comes up, the form fields are there, I insert some test data and submit and here is the error:
-------------------------
Could not find a property named 'ID' on the type specified by the DataObjectTypeName property in ObjectDataSource 'ObjectDataSource1'.
-------------------------
Where am I going wrong? Please help...
-- modified at 15:52 Wednesday 7th November, 2007
-- modified at 15:54 Wednesday 7th November, 2007
-swo
|
|
|
|
|
I have three tables.
Modules - contains one or more software modules that the user can subscribe to
Subscribed - contains subscription info for a module that is subscribed to by users
Membership - contains user membership info
The idea is to return all modules whether they are subscribed to by the user or not, but for the subscribed ones, I need all the data from the subscribed and membership tables as well. For modules that are not subscribed to by the user, the columns that came from the subscribed and membership tables would have null values.
More info on the tables:
The Modules table contains 1 or more rows, with one row indicating a unique module (no dupes).
The Membership table contains 1 or more rows, with each row indicating a unique user (no dupes)
userName UserID MoreData
john 1 dog
mary 2 cat
bob 3 bear
martha 4 monkey
sally 5 rat
The subscribed table contains 0 or more rows, and there could be multiple rows for a given user (but never more than one row per subscribed module for that user, like so:
<code>userID moduleID
1 1
1 2
2 1
3 3
3 4
4 3
5 1
5 2
5 3</code>
So, if there were four modules, and I was interested in john's subscription status, I would want this outcome (remember I want all modules in the dataset, but with john's subscription status on the subscribed ones):
<code>ModuleID ModuleName UserID SubscribeDate LastUpdate MoreData UserName
1 Blah 1 1/2/2007 1/2/2007 dog john
2 Gack 1 2/3/2007 2/3/2007 dog john
3 Shoot NULL NULL NULL NULL NULL
4 Darn NULL NULL NULL NULL NULL</code>
I kept the number of columns to a dull roar for the sake of sanity, but it's safe to assume I want all columns from all three tables when appropriate.
Can someone help me out?
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Which part of the query are you having trouble with?
It looks like you need to do a LEFT JOIN between Module and Subscribed and a LEFT JOIN between Subscribed and Membership, fetching whichever fields are required.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Off the top of my head, this should do it:
select mod.ModuleId, ModuleName,
UserId, SubscribeDate, LastUpdate,
MoreData, UserName
From Subscribed s
Left Join
Membership m
On m.userId = s.userId
Left Join
mod.ModuleId = s.ModuleId
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I think he meant to have Module as the main table.
SELECT
m.ModuleID,
m.ModuleName,
s.UserID,
s.SubscribeDate,
s.LastUpdate,
u.UserName,
u.MoreData
FROM
Modules m
LEFT JOIN
Subscribed s
ON (m.moduleID = s.moduleID)
LEFT JOIN
Membership u
ON (s.UserId = u.UserId
ORDER BY
ModuleName
|
|
|
|
|
Oh. OK then. Nice catch.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Smells like homework anyway...
|
|
|
|
|
Michael Potter wrote: Smells like homework anyway...
Naaaaaaw.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|