|
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.
|
|
|
|
|
select distinct accountnumber from table<br />
where accountnumber in ( a, b, c )<br />
|
|
|
|
|
After installing SQL Server 2005 Express, I find that I cannot view database diagrams from an MSDE database. After some more poking around the internet, I discover that diagrams have been completely removed from SQL Server 2005!
Is this true, or have I entered the Twilight Zone?
|
|
|
|
|
Miszou wrote: After installing SQL Server 2005 Express, I find that I cannot view database diagrams from an MSDE database. After some more poking around the internet, I discover that diagrams have been completely removed from SQL Server 2005!
Is this true, or have I entered the Twilight Zone?
That is incorrect. The diagraming facility in SQL Server 2005 has changed. You cannot open SQL Server 2000 diagrams in SQL Server 2005 Management Studio. You can create new diagrams, however, I don't know if it will allow them to be written to a SQL Server 2000 database.
|
|
|
|
|
As Colin states, there is diagramming available in Management Studio, but it will not allow you to create new diagrams in a SQL 2000 database. It relies on too many things that aren't present in SQL 2000, and when you first create a diagram in a 2005 database it asks you if you want to install the diagramming extensions.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi All,
I wanted to use the undocumented SP sp_MSForEachTable to selectively delete data from tables. So in short i want to achieve something like this using sp_MSForEachTable.
delete from TableName where Date = '2007-01-03'
I looked at various queries on the internet using sp_MSForEachTable, but was not able to find out the usage of sp_MSForEachTable with a "Where" clause, so if anybody has the query or links request to post it here.
All that I found was to delete all data from all the tables in the db.
I tried using the @whereand option like this, but the got an error saying Invalid column: SomeDate. I have this column in all the tables in that database. I did a quick check looping thro all the tables using cursors and that works fine.
EXEC sp_MSForEachTable @command1='DELETE FROM ?'
,@whereand='and SomeDate = "2007-01-15"'
Thanks.
PJ
-- modified at 14:39 Monday 15th January, 2007
|
|
|
|
|
Sir/Madam
I am Final Year Engineering student. I want to get information about connection of computers at different cites.
Actually if I want to computerize a department of any company which have its offices at several cities then what steps I should follow.
Means how I can connect that no of computers in appropriate manner to get desired output.
I want to make it just like railway reservation system through in which if I enter data at same time then database must be able to prioritize data in sequence to enter in database.
Which database & platform or language I should use to code this kind of project.
Thank You
Ashish Porwal
|
|
|
|
|
|
I've used an Access database for this purpose. The windows operating system will most likely already have the Jet database engine (which access uses) installed. If not you can require users to download MDAC in your program's setup/install package which is a small download compared to MSSQL, SQL Express or other full database engines. The downside is that Access is a relatively small database with not as much capability as other engines. But the projects I was doing were quite small.
Hope this helps.
|
|
|
|
|
That is really helpful. So what you are saying is when i need to distribute my program, i would not need to install a database engine on the clients computer?
|
|
|
|
|
Correct. You will only need to make sure that they have MDAC installed and if not, then install it. But the install is under 6 MB and most computers will already have it installed. Windows XP SP2 already has it, for example. I can't remember what service pack Windows 2000 has it.
In any case, it is pretty easy to require MDAC when you are creating an install program with Visual Studio 2005. You just right click on the project icon in the Solution Explorer. Select Properties. Click the Prerequisites button. And make sure that Microsoft Data Access Components 2.8 is checked in the list. And if you want the user to be able to just install the MDAC without having to go to Microsoft's website I would recommend selecting the "Download prerequisites from the same location as my application" option.
Also, remember that if a user happens to have microsoft access on their computer, they can access your database through that. So you may want to put a password on it to make sure they don't change things/mess up your schema.
Hope this helps.
|
|
|
|
|
Yes, your help is much appreciated.
Somebody else suggested programs like Microsoft Visual Fox Pro, but I had doubts about the availability of them, well from the users perspective at least.
Once again thank you very much for your help.
|
|
|
|
|
|
if it's SQL Server you can use:
<br />
select table_name<br />
from<br />
information_schema.tables<br />
if it's Oracle you can use the meta data views, like:
<br />
select *<br />
from<br />
user_tables;<br />
-- modified at 14:16 Monday 15th January, 2007
Uri
|
|
|
|
|
Thank you ,very much.
In Ms Access database,how to do?
|
|
|
|
|
Hi....
Please tel me how to fetch dates from a date column in which interval between 2 subsequent dates should be 7 weeks... I have trued a lot but i m not able to build query for this...
Plz help me out..
Thanx.
Gurudatta B. Shelke
|
|
|
|
|
You would add 49 days to the first date to get the second date. I'm not being funny here about the syntax, because the exact syntax differs between databases.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|