|
hi
This is the syntax for sending email in sql server 2005
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
regards
dharani
|
|
|
|
|
How to get different result/row of following select query :
Select * from Countries where country='India'
or
Select * from Countries where country='INDIA'
means Case-Sensitive searching in Character column.
Thanks in Advance
Regards
Girish Kumar Sharma
|
|
|
|
|
User UPPER/LOWER as below:
select upper('AsGas'),lower('AsGas')
if it doesn't solve your problem explain more...
Human knowlege belongs to the world
|
|
|
|
|
i mean; if there are two rows named "India" and "INDIA"; and if i want to get "India" then "INDIA" should not be there; and if i want "INDIA" then "India" should not be there.
Only given string should be in selection criteria please.
Data is in MS-Access.
Thanks in Advance.
Girish Kumar Sharma
-- modified at 6:34 Tuesday 16th January, 2007
|
|
|
|
|
You could cast is as a VARBINARY and compare the binary versions. I've never tried it, but you could give it a go:
SELECT *
FROM MyTable
WHERE CAST(MyColumn AS VARBINARY(100)) = CAST('SomeValue' AS VARBINARY(100));
|
|
|
|
|
Colin Angus Mackay wrote: SELECT * FROM MyTable WHERE CAST(MyColumn AS VARBINARY(100)) = CAST('SomeValue' AS VARBINARY(100));
Hi
Just curious to know if cast works on MS Access database?
Thanks
Chandra
|
|
|
|
|
ChandraRam wrote: Just curious to know if cast works on MS Access database?
No idea.
|
|
|
|
|
Oh, ok... the OP had specified Access and I was wondering... will try it at home tonight and post result here, anyway
|
|
|
|
|
Nope - there is no CAST function in MS Access... there are a few type conversion functions available, but will probably not help the OP.
|
|
|
|
|
Then, how to solve this string comparison problem in MS-Access; bcoz there is no such ready-made function available; by which user can match/compare two strings.
|
|
|
|
|
Hi all,
I want to install my program on multiple computers on a network, and my database on just one of them, then all the different computers run from the one database.
I'm using SQL Server 2005 Express, do I need to install the instance on each of the machines with the Disable Network Protocols set to 0, or do I just need to install the instance on the computer hosting the database, because I will point the connection of my program to that computer\instance, that's why I wonder if I will need to install the instance on every computer?
Is there maybe someway that I can install the instances on each machine, then point the current machine instance to the hosting machine instance, so that sql server can handle the transactions on the database? Hope it makes sense what I'm asking
He who laughs last is a bit on the slow side
|
|
|
|
|
NeroToxic wrote: do I need to install the instance on each of the machines with the Disable Network Protocols set to 0, or do I just need to install the instance on the computer hosting the database, because I will point the connection of my program to that computer\instance, that's why I wonder if I will need to install the instance on every computer?
The SQL Server must be on the same machine as the database. The applications must then connect to the SQL Server.
NeroToxic wrote: Is there maybe someway that I can install the instances on each machine, then point the current machine instance to the hosting machine instance, so that sql server can handle the transactions on the database? Hope it makes sense what I'm asking
Not really. How would installing SQL Server on each individual machine help SQL Server manage the transactions.
Installing SQL Server on one central machine helps transactions because all clients connect through the SQL Server process. The process then manages the transactions between all clients.
If this doesn't help, then it might be better if you just stated the outcome you wanted and asked how to get there.
|
|
|
|
|
Thank you for the reply.
Colin Angus Mackay wrote: Not really. How would installing SQL Server on each individual machine help SQL Server manage the transactions.
I had problems with SQL Server 2000, when multiple clients tried to access the same table simultaneously it threw an exception stating that the table is currently in use, was just a thought that I had.
Thanks again, organized a machine that I can install and test remote connections on to the database, will see if it works properly, this wasn't really intended for our application but recently got the request from one of our clients to run from multiple machines to one database.
He who laughs last is a bit on the slow side
|
|
|
|
|
When trying to get a connection to the remote machine via application on my machine I receive the following error.
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
I have set the DisableNetworkProtocols to 0, then enabled TCP\IP on the instance running the database, restarted the services and still no connection?
Any Ideas?
Thanks in advance
He who laughs last is a bit on the slow side
|
|
|
|
|
Not sure on that one. Possibly it is listening on an unusual port. You can add the port number to the connection string after the server name (and instance, if applicable). e.g.
server=MyServer\MyInstance,1433
Also, check the firewall settings between the clients and server. The port SQL Server uses must be open.
|
|
|
|
|
I've narrowed it down to being the firewall on the hosting machine. Ive added the program sqlservr.exe to the list of exceptions of the firewall, as provided by msdn help, but thats not working, when I enable the firewall I can't get a connection, and when I disable the firewall it works.
Last modified: 1hr 50mins after originally posted --
He who laughs last is a bit on the slow side
|
|
|
|
|
Finally got the answer.
First you need to enable tcp\ip on the instance for the database.
Then you set the startup of the SQL Server Brower to automatic so that it starts up the when windows is started up.
Then in the list of exceptions for the firewall, you must add program for the instance, i.e. C:\Program Files\SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
Then you must also add the SQL Server Browser to the list of exceptions, usually residing in C:\Program Files\SQL Server\90\Shared\sqlbrowser.exe
Bob's your uncle, you can accept remote connections to your sql database.
He who laughs last is a bit on the slow side
|
|
|
|
|
I have a VB.Net 2.0 app with a readonly DataGridView and a BindingSource and BindingNavigator which allows the user to edit, add and delete rows via my own custom controls (combobox, textbox etc). The table to edit is read in successfully then bound to (and displayed in) the DGV and I can do all the BindingNavigator stuff like editing fields from selected rows and adding/deleting rows as you'd expect. The problem I'm getting is that if I click on a row in the DGV but do not make any changes it is still marked as being changed (ie: RowState = Modified).
I would have expected that only the rows that actually changed would have this status - otherwise what's the point?
So I guess my question is why is the BindingSource (or BindingNavigator or DGV?) setting these unchanged rows to Modified and how can I stop it from doing that or tell which rows have genuinely changed data?
Hope someone can help as this one has me stumped!
Mike
-- modified at 23:15 Monday 15th January, 2007
|
|
|
|
|
Hi,
I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).
Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?
Imports System
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Collections
Imports System.DirectoryServices
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
#Region "Constants"
''' <summary>
''' The connection string for Active Directory.
''' </summary>
'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string>
''' <summary>
''' The LDAP search filter need to find a user in Active Directory.
''' </summary>
'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"
#End Region
''' <summary>
''' Gets all active directory groups for the user.
''' </summary>
''' <returns>All dataset permissions for the user.</returns>
<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _
Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable
' Setup the active directory search.
Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING)
searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName)
searcher.SearchScope = SearchScope.Subtree
searcher.PropertiesToLoad.Add("distinguishedname")
' Run the active directory search.
Dim result As SearchResult = searcher.FindOne()
Dim userEntry As DirectoryEntry = result.GetDirectoryEntry()
Dim userGroups As New ArrayList
GetActiveDirectoryGroupsForEntry(userEntry, userGroups)
Return userGroups
End Function
Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars)
GroupID = New SqlChars(CType(source, String))
End Sub
''' <summary>
''' Recursively gets the active directory groups for the directory entry.
''' </summary>
''' <param name="entry">The active directory entry.</param>
''' <param name="groups">The list of groups.</param>
Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList)
For i As Integer = 0 To entry.Properties("memberOf").Count - 1
Dim memberEntry As New DirectoryEntry("LDAP:
groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString())
GetActiveDirectoryGroupsForEntry(memberEntry, groups)
Next
End Sub
End Class
|
|
|
|
|
Hello,
I have two columns I need to perform a diff on.
Query:
Select f2 startcall, f3 endcall from monkey$
Results:
StartCall................... ......... EndCall
2006-12-04 14:33:00......... 2006-12-04 14:34:00
2006-12-04 14:34:00......... 2006-12-04 14:35:00
2006-12-04 14:35:00......... 2006-12-04 14:36:00
2007-01-04 14:38:00......... 2007-01-04 14:39:00
2007-01-04 14:39:00......... 2007-01-04 14:40:00
When I run:
SELECT
DATEDIFF (second, f3, f2)
I get
“Invalid column name 'f2'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'f3'.”
I would like to see length of each call.
What is the best way to achieve the desired results?
Any help or advice would be greatly appreciated.
Regards,
H
Regards,
Hulicat
|
|
|
|
|
Hulicat wrote: When I run:
SELECT
DATEDIFF (second, f3, f2)
You need to add your table name to the query:
SELECT DATEDIFF(second, f3, f2)
FROM your_table_name_here
|
|
|
|
|
Thanks Jon, I have been staring @ this stuff for 36 hours...That is a key indication I need sleep.
Thanks for answering my gross oversight.
Regards,
Hulicat
Regards,
Hulicat
|
|
|
|
|
I am trying to figure out the most efficient way to obtain and present a set of information....
I have a SQL table with information that is related to account numbers. There can be multiple lines with the same account number. I have a list of account numbers in a spreadsheet (about 100). I would like to know if there is at least one record in the table whose account number matches the number on the spreadsheet.
Is there ane efficient way to give me a yes/no or other info without hitting the database 100 times? It could be done in Excel or VS2005. Does anyone want to throw out a suggestion?
Much thanks in advance.
|
|
|
|
|
select count( accountnumber ) from table<br />
where accountnumber in ( a, b, c )
That will tell you the number of accounts that match, where a, b and c (and so on) are account numbers.
|
|
|
|
|
This would give me the number of accounts that match, but not which of the accounts match....correct? I am needing to figure out which of the numbers match. Thanks.
|
|
|
|