|
This question relates to MS-SQL 2000
I would like to write a query such as..
SELECT * FROM Users WHERE id IN @UserIDs
Where @UserIDs is a list of integers passed into the stored proc.
Is this possible? The list of ID's is generated by a user, so I cant use a sub query to get the required list of ID's.
If it is possible, what type do I define the parameter as?
Thanks
Stephen
|
|
|
|
|
There is a way, but I don't recommend it. For one thing, you lose out on one of the key reasons for using a stored proc...the pre-compilation by SQL Server.
Here's how you do it....
create procedure dbo.usp_Test
(
@ids varchar(100)
)
as
declare @sql as varchar(255)
set @sql = 'select * from table where tableid in (' + @ids + ')'
exec(@sql)
GO
then you pass the ids in as a comma delimited string...
exec usp_Test '1000, 1001, 1010'
|
|
|
|
|
I have a problem here. I tried to connect sqlserver as many time as I can in one function, because I want to know about the limitation of connection of sqlserver, btw, the sqlserver is not in my computer, the code is like :
private void ExecuteCommand_ConnTest_process()<br />
{<br />
ArrayList conn = new ArrayList(3000);<br />
int num = 0;<br />
<br />
string conn_s = "...";<br />
conn_s += "Connect Timeout=600;Max Pool Size=1000000;";<br />
<br />
try<br />
{<br />
while ( true ) <br />
{<br />
conn.Add(new SqlConnection(conn_s));<br />
((IDbConnection)conn[num]).Open();<br />
num ++; <br />
Thread.Sleep(10);<br />
}<br />
}<br />
catch( InvalidOperationException ioexp )<br />
{<br />
StringBuilder buildError = new StringBuilder();<br />
buildError.Append( "InvalidOperationException thrown when trying to connect " );<br />
buildError.Append( ", error given = " + ioexp.Message);<br />
MessageBox.Show(builderError.ToString());<br />
}<br />
catch ( Exception e ) <br />
{<br />
StringBuilder buildError = new StringBuilder();<br />
buildError.Append( "Exception thrown when trying to connect " );<br />
buildError.Append( ", error given = " + e.Message );<br />
<br />
MessageBox.Show(builderError.ToString());<br />
}<br />
finally<br />
{<br />
MessageBox.Show(builderError.ToString());<br />
}<br />
}
After certain number connections, such 6000, it throws an exception:
"SQL Server does not exist or access denied". Then I found I can not access Internet. I have to restart my computer. Why this happened? What's wrong with my code? Could anyone please tell me? Thanks in advance!
|
|
|
|
|
Hi all,
I am using ADOCE. My requirement is to open a connection to a database (Test.sdf) in the Pocket PC. The problem is _Connection::Open throws an exception "File not found" even though I am giving the right path. My question is, What is the exact format format in which the connection string has to be passed to the _Connection::Open() function ?
Any help would be appreciated.
Regards,
Amit
|
|
|
|
|
I'm looking for an embedded .NET database that operates completely in managed code. So far the only one I've found is TurboDB.NET (and I am not sure whether that operates completely in managed code). Are there others?
|
|
|
|
|
Why do you care? Is there something that Jet can't do that you need to do?
While a wholly-managed application doesn't incur any interop overhead, a P/Invoke call (to a flat API) only has about 10 machine instructions overhead, while a COM Interop call has about 50 machine instructions overhead (source: Managed/Unmanaged Code Interoperability[^]. Some people have reported that ODBC connections from ADO.NET (using System.Data.Odbc classes in Framework 1.1, or Microsoft.Data.Odbc from the download for Framework 1.0) perform better than OLE DB connections.
If you're doing anything serious with the database, though, the interop overhead is likely to be swamped by the cost of actually reading and writing to files, and looking up records in indexes.
If you need your application to be portable across CLI implementations (and across operating systems) then you will have to consider an alternative.
'Pure managed' code on .NET is far less important than for Java, where performance with unmanaged code is reportedly poor.
|
|
|
|
|
Hi Mike.
I am an experienced programmer and am new to databases. Please be nice.
Maybe a bit more background about the application will help. I am writing a server application that runs on a dedicated host. I don't need to do complicated SQL queries and I'd be perfectly happy to use a custom API instead of SQL queries. Doing SQL queries may be fine too. The main reasons I have been looking at databases is to get atomic transactions so that the database is always in a consistent state. In my situation recovery is also nice although it's less important than atomic transactions. The ability to access the database from multiple threads would be extremely helpful.
Benefits of staying in managed code for this application are type safety, debuggability, and security.
Benefits of embedding the database over using a database server are relative ease of deployment and system management. If performance is better using embedding than a database server, that would be another benefit. As you say though, maybe performance will be about the same either way.
You mentioned Jet.. Is Jet something that can be used directly from my C# application, or does it have to be used with a higher level package like Access? If it can be used directly, is there somewhere I can read about Jet from a non-propaganda standpoint? I looked around some in Google and found mostly Jet error messages and security bulletins. I have been avoiding Access because my understanding is that Access does not perform well with large databases.
|
|
|
|
|
|
Thanks, that is a good possibility. I had looked into in-RAM databases a bit before, and your note inspired me to look at them some more.
|
|
|
|
|
Does anyone know what is the limitation of Max Pool Size in ADO.NET? I mean how great the number I can set to "Max Pool Size".
|
|
|
|
|
Hi,
I am using C# and would like to know how to setup a Windows App to accept
info from the Form and to add to a NEW .dbf Dbase IV file. Can someone give me the connection, adapter, etc. that I would use?
Thanks, Don.
|
|
|
|
|
You could use the FoxPro drivers for creating DBF-files, and then use ODBC to access it. 
--
The moment of terror is the beginning of life.
|
|
|
|
|
J.S.
Would you expand your reply with a link to some code examples, or please explain the steps to accomplish this?
Thanks, Don.
|
|
|
|
|
Hmm.. I wish I could. I have dealt with dBase/FoxPro before, but it was (fortunately) a long time ago.
Anyway, if you install MDAC (Microsoft Data Access Components), you get an ODBC-driver for FoxPro. You can use that driver, just like you would use Access. The SQL syntax is fairly similar, so simple CREATE's, SELECT's, etc should work.
I am not sure how ODBC works with .NET, but I would assume it works (haven't fiddled much with anything other than MSSQL in .NET)
--
The moment of terror is the beginning of life.
|
|
|
|
|
Thanks for you time/input, Jorgen.
I guess that I will have to trial-and-error as we engineers say!
Xiao!
|
|
|
|
|
|
Arjan,
Thank You for this information resource!
Don.
|
|
|
|
|
I have a table and everything works out okay if I log in as sa, naturally.
If I run:
SELECT TOP 1000 * FROM [site] WHERE [date]>='2000-10-01' And [date]<='2000-10-07 23:59:00'
select count(*) from site
select * from site
I get back everything I expect.
However if I log is as another user, the first SQL results in zero rows (as sa it was ~700), the second statement reports there are ~5000 in the table, and the third returns all ~5000 rows. Weird!
I have a feeling I am missing something totally obvious, but I can't figure it out, especially as the statment without the WHERE clause works fine.
Any Ideas?
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
It was a date thing. At some point a particular user got set to "British English" rather than just "English"???? And it flipped the month and days around.
Now... (perhaps I should go off to the soapbox at this point) WHY THE $&*% is American localisation formats called "English" and British localisation formats called "British English". If someone said this date is in "English" format I'd expect the format to be the one used in England. Makes Sense? Doesn't it?! But oooohhh! noooooo! in SQL Server 2000 "English" format dates mean the format of dates used in the United States of America! (which is about 3000 miles from England).
Aaaarrrrrrggghhhh!
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
What country is Microsoft located in? US. Thus English is US English.
Actually, if I saw "date format: English" I'd immediately be suspicious because I know that different English-speaking countries use different date formats.
--Mike--
Ericahist [updated Oct 26] | CP SearchBar v2.0.2 | Homepage | RightClick-Encrypt | 1ClickPicGrabber
#include "witty-quote.h"
|
|
|
|
|
I colleague of mine is having similar problems to what you're describing.
<rant>What's amazing to me is why the hell localization is done on this level. Why not stick with ISO dates and let applications worry about localization and presentation. Same thing goes for decimal points. The DBMS vendors have gotten it all wrong if you ask me. A database is seldom accessed by end users with a console anyway. A DBA should be able to handle ISO-dates.</rant>
--
The moment of terror is the beginning of life.
|
|
|
|
|
I totally agree - The RDMS should not be handling presentation layer issues like date formats.
However some localisation issues may still be important - For example, collation order - It is much more efficient if SQL Server handles localised collation sequences. For example, take the values:
Lladró
London
is correct if you are dealing with the English language. But if you select Traditional Spanish as the locale in SQL Server 2000 you get the order:
London
Lladró
because in Traditional Spanish "LL" is considered a letter in its own right that is placed after "L" in the alphabet.
And if the database is localised to non-latin character sets how would the ORDER BY work without locale specific collation rules.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
Ah yes, but the sort order of things falls outside the scope of abstract datatypes. A date is an abstract datatype, while order is just.. well order! 
A chosen few should sit down and rethink SQL...
--
The moment of terror is the beginning of life.
|
|
|
|
|
Its interesting what you say about it being a localisation issue, as thats an ISO format, so it should not matter. Access will identify it as ISO always yyyy-mm-dd, but when you have 01-10-2000 it will then use the local version.
Iteresting that SQL Server tries to read it as yyyy-mm-dd or yyyy-dd-mm depending on where it thinks it is.
"Je pense, donc je mange." - Rene Descartes 1689 - Just before his mother put his tea on the table.
Shameless Plug - Distributed Database Transactions in .NET using COM+
|
|
|
|
|
Yes, that's what I thought - All the code uses ISO date strings except on the UI (where normal british dd/mm/yyyy is accepted also). That's why it took so long to discover the fault. And because it only happened to one user I was sure it was some kind of strange permissions thing.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
