|
sp_databases - This [system stroed procedure] return's all databases that reside in an instance of Microsoft SQL Server.
Regards
J O H N ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
hi
this is available in sql 2000 also
sp_helpdb
get back for more info.
|
|
|
|
|
OK thanx buddies. So nice of u.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
through my application i craete a login int the sql server(express) and then i created a user using login create in the above .. in the sql server it shows the login and user i created. but when login into sql server using the created (above) user name and password it says
" login failed for user 'user01'.The user is not associated with a trusted sql server connection. Microsoft sql server ,Error 10452.
could anyone explain me the reason and the solution..
|
|
|
|
|
Please check the credentials given in the connectionString of web.config
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
The problem is related to the fact that when you installed SQLEXPRESS, it only enabled Windows Authentication. What you need to do is to enable both Windows and SQL Authentication modes. If you have a tool like Management Studio, just login to the server with Windows Authentication, then right-click on the SQLEXPRESS instance. From the menu that appears click Properties. You will find a number of Server Properties, select the Security page. From this page change Server Authentication to 'SQL Server and Windows Authentication mode'. Click OK. That should solve the problem you are experiencing.
|
|
|
|
|
ok, here is the code:
Private Sub CreateProcedure(ByVal con As SqlClient.SqlConnection)
Dim curRowIndex As Integer = ((GetCurPageIndex() * RowsPerPage) + 1)
If curRowIndex > GetTotalRowCount() Then
mCurPageIndex = NumPagesAvailable()
curRowIndex = GetTotalRowCount()
End If
Dim createProcedureSQL As String = "CREATE PROCEDURE [dbo].[PageResultsProcedure]"
createProcedureSQL &= "AS" & vbCr
createProcedureSQL &= "DECLARE @FirstID INT, @StartRow INT" & vbCr
createProcedureSQL &= "SET ROWCOUNT " & curRowIndex & vbCr
createProcedureSQL &= "SELECT @FirstID = " & TablePKName & " FROM " & TableName & " " & WhereSegment & vbCr
createProcedureSQL &= "SET ROWCOUNT " & RowsPerPage & vbCr
createProcedureSQL &= "SELECT " & ColumnsToReturn & " FROM " & TableName & CStr(IIf(WhereSegment = "", " WHERE ", " " & WhereSegment & " AND ")) & TablePKName & " >= @FirstID" & vbCr
createProcedureSQL &= "ORDER BY " & TablePKName & vbCr
createProcedureSQL &= "SET ROWCOUNT 0"
Dim cmd As SqlClient.SqlCommand
cmd = con.CreateCommand
cmd.CommandText = createProcedureSQL
con.Open()
cmd.ExecuteNonQuery()
con.Close()
cmd.Dispose()
cmd = Nothing
End Sub
This code is part of a procedure to allow paging a data table...the question is; I need a way of doing this, but being able to sort the results by a given column that is not guaranteed to have unique values in the table...Any Ideas? Thanks in advance for any help.
--
"Keyboard not found. Press < F1 > to RESUME. "
Source unknown (appears in many common BIOSes as a real error message)
|
|
|
|
|
Umm... Wow!
There are a number of things that are wrong with this before we even get to solving the problem you posted about.
1. Why are you creating a stored procedure like this?
2. Your code is vulnerable to a SQL Injection attack.
3. The use of SET ROWCOUNT should be discouraged because it will affect other queries that happen to be running at the same time. Use SELECT TOP n instead (where n is the number of rows you want)
If you are running SQL Server 2005 the answer is quite simple as there is some new functionality built in to address this problem. If you are running SQL Server 2000 then it is workable, but the query will be more complex. Which version of SQL Server are you using?
|
|
|
|
|
I'm using SQL Server 2000, and the code wont actually be building the query in the long run, this is just basically a proof of concept and is like this for the time being so that I can play with it in a designer I coded...SET ROWCOUNT is set back to 0 at the end to restore it for latter query's. An explanation of why I'm using SET ROWCOUNT can be found here:
http://www.4guysfromrolla.com/webtech/042606-1.shtml[^]
It is a fairly efficient way of doing this...
I'm only trying to find a way to incorporate an order by into this...
--
"Keyboard not found. Press < F1 > to RESUME. "
Source unknown (appears in many common BIOSes as a real error message)
|
|
|
|
|
Polymorpher wrote: SET ROWCOUNT is set back to 0 at the end to restore it for latter query's. An explanation of why I'm using SET ROWCOUNT can be found here:
http://www.4guysfromrolla.com/webtech/042606-1.shtml[^]
It is a fairly efficient way of doing this...
Maybe, but it is also a fairly dangerous thing to do. SET ROWCOUNT will affect other queries running at the same time. If you are never going to have other users then this is fine. But in that case why are you using something like SQL Server.
As for incorportating an order. Use subquerys - If you expect to be paging through lots of data then you may wish to use a temp table for the innermost SELECT. Also, remember to only use the columns you actually need, this will help the performance of the query rather than the demonstration SELECT * I've used here.
First lets, say you want rows 76 to 100 (25 rows in total). Get the first 100 rows
SELECT TOP 100 *
FROM MyTable
ORDER BY OrderColumn
Then Reverse the order of the result set getting only 25 rows
SELECT TOP 25 *
FROM (SELECT TOP 100 *
FROM MyTable
ORDER BY OrderColumn)
ORDER BY OrderColumn DESC
Finally, flip the order back again:
SELECT *
FROM (SELECT TOP 25 *
FROM (SELECT TOP 100 *
FROM MyTable
ORDER BY OrderColumn)
ORDER BY OrderColumn DESC)
ORDER BY OrderColumn
|
|
|
|
|
|
|
Given that you didn't say which version of SQL Server in your original post...
|
|
|
|
|
Hello,
I have table1 with fields id, date, usernamename. I can get the count of records created by user(s) for certaing date range, but if user 'A' has not inserted any records it is missing from result. What I want to achieve is to be able to display the username, with count=0. I know it should be some sort of self join but I can't manage to get it work.
All suggestions appreciated!
|
|
|
|
|
You can use GROUP BY with the HAVING clause.
SELECT username
FROM your_table
GROUP BY username
HAVING COUNT(*) = 0
|
|
|
|
|
I've tried this also, it does not return records in my case.
|
|
|
|
|
You're right! I've should've seen that. It can't possibly work...
Will something like this work?
SELECT y1.Username
FROM your_table y1
LEFT JOIN your_table y2
ON y1.Username = y2.Username
WHERE y2.date BETWEEN start AND end
GROUP BY y1.Username
HAVING COUNT(y2.Username) = 0
The table alias y1 represent all usernames and y2 represent only the records within the requested range.
|
|
|
|
|
i would like to know the differnce between the fixed server roles and data base roles in sql server 2005 . and in my application i use three types of users called admin,mgr,operator and i want to give privileges to each of them to accss the db and the application.so using the roles how can i do this ,Specially apply privileges to each user...
|
|
|
|
|
|
I am not understanding the permission problem I am having and what the Schema object is for.
My Database has Stored procedures owned by a user, let say corp. If I look at the database there is a Schema also called corp owned by corp.
corp has all roles except db_DenyDatareader and db_DenyDatawriter and is not aliased to dbo.
Now it has been my understanding that since the user corp owns the Stored procedure it should have execute rights to it.
The problem is when my application opens and tries to execute a stored procedure I get denied for lack of Execute permission. If I log into the database using enterprise manager (2005 version) with the corp login I can get into the database but I can not see any of the Stored procedures that the corp user owns.
I can not figure out this issue any ideas are greatly appreciated.
Thanks
|
|
|
|
|
i cant run my program, there's an error:
Type 'BOOK_Locator.Dataset1' is not declared
in codes:
Friend WithEvents DataSet11 As BOOK_LOCATOR.DataSet1
i have connected my database in connection wizard properly but why there's an error in it? how can i fix?
|
|
|
|
|
|
As others have said, don't cross post. That is very rude.
Rharzkie wrote: Type 'BOOK_Locator.Dataset1' is not declared
That pretty much tells you the problem. Is the error occurring within the scope of where you have it declared?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
i used the data adapter connection wizard, but when i chose my database, there's an error...
it says:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
what is the meaning of that? how will i fix it?? i really need to make a connection wizard for my project...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
