|
|
I think it's safer to use the (documented) INFORMATION_SCHEMA views:
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Grimolfr wrote:
I think it's safer to use the (documented) INFORMATION_SCHEMA views
Or the equally documented sysobjects. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-o_4zll.asp[^]
And quite frankly, after using the INFORMATION_SCHEMA views earlier this year... Or should I say attempt to use them. I threw them out and refuse to use them until Microsoft come up with something that is actually useful. Have you actually looked at the source for the views - What a load of complete rubbish!
For example, this is part of the SELECT clause from the ROUTINES view:
SPECIFIC_CATALOG = db_name(),
SPECIFIC_SCHEMA = user_name(o.uid),
SPECIFIC_NAME = o.name,
ROUTINE_CATALOG = db_name(),
ROUTINE_SCHEMA = user_name(o.uid),
ROUTINE_NAME = o.name,
Why have SPECIFIC_CATALOG and ROUTINE_CATALOG? They are the same thing.
Why have SPECIFIC_SCHEMA and ROUTINE_SCHEMA? They are also the same thing.
Why have SPECIFIC_NAME and ROUTINE_NAME? It is just wasting bandwidth and processing time repeating the same data again and again.
Do you want to know more?
|
|
|
|
|
Because they may not be the same thing in, say, SQL Server 2005 (don't know, haven't bothered to fool with it yet), or the first service pack for SQL2k5.
When I tried to go to your documentation link, I got:
Page Cannot Be Found
We apologize for the inconvenience, but the page you are seeking cannot be found in this location.
My original point being that the system tables are subject to completely change their function, schema, usage, etc from one service pack to the next. The schema views aren't.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Grimolfr wrote:
When I tried to go to your documentation link, I got:
Page Cannot Be Found
Don't worry, you can find it in the SQL Server books online that come with SQL Server. In the index type sysobjects.
Grimolfr wrote:
My original point being that the system tables are subject to completely change their function, schema, usage, etc from one service pack to the next.
That's fine if you can put up with the limitations of the views. But I found them to be very restrictive and they do not return full information.
Do you want to know more?
|
|
|
|
|
"Try:
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 "
Worked like a treat - thanks for your help and all the associated input
|
|
|
|
|
hello, Can any body help me as how the return the ID of the row which is not deleted by a stored procedure.
Description:
I have a Table1 which is being referenced by another Table2, am sending the ID of Table1 to delete the row, due to Referential intergrity the row is not getting deleted, i want to display the id which is not getting deleted to the user in the front end how do i do this pleaseee help in this regard.
Advance Thanx.
Irsh
|
|
|
|
|
Hello every buddy,
How can I add for example some minutes to a datetime type value and get result again as a datetime?
Thank you so much,
- Den
---
"Art happens when you least expect it."
|
|
|
|
|
This example takes the current time and adds 5 minutes to it:
DATEADD(minute, 5, getdate())
The first arg is the date part (minute, hour, year, month, etc.)
The second arg is the amount you want to add (or for negative values, subtract)
The third argument is the source datetime.
Do you want to know more?
|
|
|
|
|
All I can do to appreciate now is to say thanks 
---
"Art happens when you least expect it."
|
|
|
|
|
An interview question again: Please tell me what is the difference between a ADO recordset and an ADO.NET dataset? 
Salil Khedkar [^]
|
|
|
|
|
Hello all!
I have created a trigger insert in a table. But I need to insert a row at a time. So every time the trigger fires, it only affects the next row, 1, 2, 3, ect. I know i can specify a a row, with a key id. But I need to make it so it goes to the next row automaticly. Any suggestions would be great!
Thanks
Rudy
|
|
|
|
|
Please clarify.
Are you saying that you need to support multirow inserts?? If so you can use @@rowcount to determine the number of rows being inserted and then use a cursor to process each row individualy.
This posting is provided "AS IS" with no warranties, and confers no rights.
Alex Korchemniy
|
|
|
|
|
Almost every example I have seen describe how to access Access database located on the local machine (C:/...)
But how do I work with Access database located on a hosted server. I would be grateful, if some can show how to connect to hosted Access databases using VS.net 2003.
Also, The follwoing command works fine with sqlCommand, but VS.net complaints when I try to use it with oleDbCommand. Any idea why?? CanI use stored procedures with Access?
INSERT INTO QR_Suppliers (SupplierName, QuestionID, Response, Comments) VALUES (@name, @question, @response, @comments)
regards
|
|
|
|
|
|
This may seem like a dumb question, but did you reboot the SQL server after you installed SP3a? And, are you installing the Reporting Services as an administrator of the SQL Server?
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Dave Kreskowiak wrote:
did you reboot the SQL server after you installed SP3a?
After installation I also reboot my machine and reboot SQL server service (at the system tray). But when I install, it still not working. But during the installation of sql sp3a I found the splash screen display sql 2000 service pack 3 not servic pack 3a. I don't know why, but I still continue because the file that I download get from the link of Microsoft Website.
Note: I used to install sql 2000 service pack 3 in the last 2 month, before I install the sp3a that i download from the internet yesterday.
Dave Kreskowiak wrote:
, are you installing the Reporting Services as an administrator of the SQL Server?
The user account that I use is the member of Administrators group. I think it is enough for me to install any application.
Any solution???
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
hi all,
i have a dts that copies table contents from remote server to local server.
remote table:
ItemID|name
1 |name1
2 |name22
3 |name33
local table:
ItemID|name
1 |name1
2 |name2
i want that the dts will only copy the new rows( aka. row with ItemID = 3) and leave the other rows as they are.
can any one help me create such a dts.
|
|
|
|
|
You don't really want DTS. You want replication.
using System.Beer;
|
|
|
|
|
dts and replication.. not the same?
how should i do this using replication?
please post some guide/tutorial/example.. thank you!
|
|
|
|
|
I am setting up and MSDE Instance by using the Setup.Exe method (versus the MSI method).
The setup progresses just fine until it tries to run the SQLREDIS.EXE files.
Here is an exerpt from the verbose MSDE log file...
Starting custom action InstallSqlRedis<br />
Executing "F:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlredis.exe /q:a"<br />
MSI (c) (BC:74): Font created. Charset: Req=0, Ret=0, Font: Req=MS Shell Dlg, Ret=MS Shell Dlg<br />
<br />
Installation of the Microsoft Data Access Components package failed.<br />
MSI (s) (3C!64): Product: Microsoft SQL Server Desktop Engine -- Installation of the Microsoft Data Access Components package failed.
I have manually preinstalled MDAC 2.8 and used Component Checker to verify everything is okay. Does anyone have any ideas what could be wrong?
The "F:\Program Files\Microsoft SQL Server" folder does not exist prior to nor after the setup is done/failed.
|
|
|
|
|
I searched for "sqlredis msde" and found this[^].
Hope it helps.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Yes, that was VERY helpful, and I thank you very much for that lead.
I think the problem, as others have found out, was that I was waiting on the process handle for the setup program to exit.
I will resort to examining the log file, and some other 'process ended tricks' to wait for the setup to finish instead of waiting on its handle.
|
|
|
|
|
Here is the solution for calling it from Visual C++...
Might help someone else out there...
bSuccess = CreateProcess(<br />
szSetupFilePath,<br />
szCommandLine,<br />
NULL,
NULL,
FALSE,
NORMAL_PRIORITY_CLASS,<br />
NULL,
NULL,
&startinfo,<br />
&procinfo<br />
);<br />
<br />
if( !bSuccess ){<br />
m_ErrorWindows = ::GetLastError();<br />
TRACE("MSDE Setup.Exe CreateProcess Error Code = %lu\r\n", m_ErrorWindows);<br />
goto FuncExit;<br />
}<br />
<br />
hProcess = OpenProcess(SYNCHRONIZE|PROCESS_QUERY_INFORMATION, FALSE, procinfo.dwProcessId);<br />
<br />
CloseHandle(procinfo.hProcess);<br />
procinfo.hProcess = 0;<br />
::CloseHandle(procinfo.hThread);<br />
procinfo.hThread = 0;<br />
<br />
dwWaitResult = ::WaitForSingleObject(hProcess, 10 * 60 * 1000);<br />
TRACE("MSDE Setup.Exe Wait Result Code = %lu\r\n", dwWaitResult);<br />
<br />
::GetExitCodeProcess(hProcess, &m_MsdeExitCode);<br />
TRACE("MSDE Setup.Exe Exit Code = %lu\r\n", m_MsdeExitCode);
|
|
|
|
|
Hello,
I am building a Windows Forms .NET application entirely using typed DataSets. It is a straightforward administration application which does CRUD and some more for many tables in a database. The strategy we have followed on data access is to get all records to the client, then use them. So the Windows Forms application running on the client PC always has a complete set of records, reflecting the database. To save updates we use the GetChanges() method, so only updates are sent to the data layer. To keep the data current, the DataSets are regularly refreshed from the database.
Working entirely with typed DataSets in the presentation layer has many advantages. The basic functionality for scrolling through a table, editing records, filtering, selecting, is all there. DataSets have their peculiarities, but once you have learned to handle these, DataSets are just a pleasure to work with.
Now introducing my problem: performance with large numbers of records. All is well with tables of <10000 records. Data is loaded in couple of seconds and the performance on the client side is definitely sufficient. One part of the application however has a much larger number of records. One table has about 200000 records, and a related table has about 600000. Filling the typed DataSet with these tables works, but it takes over a minute to complete. Furthermore the performance of the application when working with this DataSet is far too low, even on modern hardware. E.g. finding a row takes seconds, not milliseconds.
I realize it's foolish to think that it is good practice to load such a large database entirely to the clients. However, I want to use the power of DataSets. How can I do this, without requiring enormous changes to the application? Any hints on and links to best practices to handle this situation would be greatly appreciated.
Regards,
Aron van Ammers
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
