|
I am using a MS Access Database.
Can anyone suggest some SQL to find the lowest unused number from one a column, say 'Quantity' for example?
Thanks
|
|
|
|
|
pjholliday wrote:
Can anyone suggest some SQL to find the lowest unused number
What do you mean by unused number? Do you mean that the number that is the lowest value in the table. If so, try the following statement:
SELECT * tblTest ORDER BY tblTest.Quantity
The above statement will sort the column Quantity from the lowest value to the highest value (suppose from 0 to 100). Then the first record of your database will be the lowest value.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
What I mean by unused number is for example I have the following numbers in my column
1
2
3
4
6
7
8
I want it to return the value 5 because that is the lowest value that does not appear in the column.
Is it possible to write a query to do that?
|
|
|
|
|
It is, if you have another table with integer values.
The SQL will be like this:
SELECT MIN(tblInteger.Number)
FROM tblInteger
WHERE tblInteger.Number NOT IN
(
SELECT Numbers
FROM tblData
)
tblData will be the table you described while tblInteger will have integer numbers you use.
So far that's the only way I can think of, maybe others can help?
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi
How can the database can be backup and restore across network in sql server
suji
|
|
|
|
|
The destination should have a share on it for the sql server user.
You need to give more details.
Are you trying to do it through app, query analyzer or mmc?
|
|
|
|
|
Yes I tried it But it doen't support. can u explain little brief.
i'm tried in mmc and Query analyzer
suji
|
|
|
|
|
I think you can that, try to read the information from here[^], and SQL server product documentation.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hi,
I run this in query analyzer
dump database MYDB to disk = 'c:\temp.dat' with init
sql server fails to back up and returns
Server: Msg 3202, Level 16, State 1, Line 1
Write on 'c:\temp.dat' failed, status = 64. See the SQL Server Error Log For More details.
Server: Msg 3013, Level 16, State 1, Line 1
Backup Database is terminating abnormally
Please help, this has to be resolved before production goes online on monday.
Any hints will be highly appreciated.
Thanks in advance.
|
|
|
|
|
Ok I think I know what the problem is. Sorry for the 'c:\temp.dat'. The path is actually
'\\myserver\c$\temp.dat'. The status = 64 means The specified network name is no longer available ( ERROR_NETNAME_DELETED ). But the myserver is the name of the computer on which sql server runs. I mean sql server is installed on myserver. So it should be same as saying c:\temp.dat.
So the query becomes
dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
But in case of c:\temp.dat it succeeds and in case of \\myserver\c$\temp.dat it fails ( it used to work, but ahs been failing recently).
Now i tried following.
1. I am able to access c drive on myserver by typing \\myserver\c$ in windows explorer from another computer on LAN.
2. I am able to access c drive by typing \\myserver\c$ in the windows explorer on myserver.
3. ping 127.0.0.1 return normal results on myserver.
So why does windows generates ERROR_NETNAME_DELETED when i run
dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
in query analyzer???
|
|
|
|
|
I'm missing something and would appreciate a pointer to what might be wrong. The following code
CString strSQLSelectID = "SELECT PARAM_COUNT, SELECT_STRING FROM P2P.SELECT_SOURCE_COMPLETE";
CString strParamCount;
CString strSelectString;
CRecordset* pSet = new CRecordset(m_DB);
if ( pSet->Open(CRecordset::forwardOnly, strSQLSelectID, CRecordset::readOnly) )
{
pSet->GetFieldValue("PARAM_COUNT", strParamCount);
pSet->GetFieldValue("SELECT_STRING", strSelectString);
}
results in an exception being thrown in the first call to GetFieldValue(). The details of the error are
Error: GetFieldValue operation failed on field 0.
Invalid cursor state
State:24000,Native:0,Origin:[Microsoft][ODBC Driver Manager]
Thinking that the cursor is not being setup correctly I tried more 'ordinary' values on the open statement, like the following
if ( pSet->Open(AFX_DB_USE_DEFAULT_TYPE, strSQLSelectID) )
but this results in the Open call throwing an exception with it's details being
No columns were bound prior to calling SQLFetchScroll/SQLExtendedFetch
State:SL009,Native:0,Origin:[Microsoft][ODBC Cursor Library]
The SQL should return 1 row and when I step through the Open call, I can see that the instance of pSet does allocate memory for two field definitions that corresponds to the two columns being queried. I'm missing something basic, I hope, but not sure what. Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
I am new to programming so this is probably a newbie question. I am using visual studio 2002, and am writing in c#. I just want to connect to a jet 4.0 database. These are the steps that I followed.
1. Opened up Server Explorer.
2. Dragged and Dropped the Connection I wanted onto the Form.
3. Dragged and Dropped a DataAdapter from the toolbox onto the Form.
4. I configured the DataAdapter to query all the columns from the Data Table.
5. I right-clicked the DataAdapter and chose generate dataset.
At this point I closed visual studio. When I Opened it again an error came up which said "Specified cast is not valid". Can anyone tell me why I am getting this error?
Thanks.
|
|
|
|
|
Hmmm... it'd be easier if we could see the code from the InitializeComponent part of the form, but just a quick check, Are you using OleDbConnection and OleDbDataAdapter?
|
|
|
|
|
Good day. I was able to connect to a database server using SQL Server Enterprise Manager. The Server name specified on the tree is JOMARGON(Windows NT). But no server was detected using either Visual Studio .NET and SQL Server's query analyzer.
I highlighted one database (master) on the SQL Server Enterprise Manager and chose 'SQL Query Analyzer' under the 'Tools' menu. It worked. The Title of the Query Analyzer window is
SQL Server Analyzer - [Query- JOMARGON.master.JOMARGON\JM Gonzalez
and below on the status bar, I can see
JOMARGON(8.0) and JOMARGON\JM Gonzalez(52)
But again, I cannot connect manually using Query Analyzer as nothing is listed in the SQL Server drop-down list
Thanks.
|
|
|
|
|
|
Did that already and still cannot connect. By the way, I'm using Windows NT Integrated Security. Pleae help.
|
|
|
|
|
I am able to connect to the server using query analyzer. All I did was restart my PC. The server name was JOMARGON.
But the only problem is, I still cannot connect to the server using Visual Studio .NET's Server Explorer. Please help.
Thanks.
|
|
|
|
|
jomargon wrote:
I still cannot connect to the server using Visual Studio .NET's Server Explorer
Ah, your previous post appeared to indicate that it was Query Analyser you had problems with.
In Visual Studio, right click the "Data Connections" node and select "Add Connection..." If the server name does not appear in the drop down box you can type it in. Type the other details in and click "Test Connection". Hopefully everything should work out.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Did that too. And a server already appeared on the drop-down list. But after choosing the server and clicking on the Select database on the server, it says
--SQL Server does not exist or Access denied.--
But how come I can access the same server through Enterprise Manager and Query Analyzer? I also use Windows Integrated Security to access the server. By the way, thanks for your replies
|
|
|
|
|
Did that too. And a server already appeared on the drop-down list. But after choosing the server and clicking on the Select database on the server, it says
--SQL Server does not exist or Access denied.--
But how come I can access the same server through Enterprise Manager and Query Analyzer? I also use Windows Integrated Security to access the server. By the way, thanks for your replies. Please help.
|
|
|
|
|
I am executing a call to "sp_helptext" from a web service, in order to obtain the text of a stored procedure. The call itself seems to be ok but eventually it throws an exception saying that the specified object does not exist in the database - which is a false statement, i've checked carefully.
I probably do not have enough rights to access something from the ASPNET account. Do i have to login as "sa"? Is this the only solution available?
"though nothing
will keep us together
we can beat them
for ever and ever"
rechi
|
|
|
|
|
You don't give enough information. An exception gives information about what went wrong, What does it say?
Bogdan Rechi wrote:
Do i have to login as "sa"? Is this the only solution available?
Never ever ever under any circumstances run SQL Server through ASP.NET an application with the sa (or any user that has the sysadmin fixed server role). I cannot emphasise enough the large wide gaping security hole you will open in your application if you do.
Also, the SQL Server documentation have this to say about sp_helptext: "Execute permissions default to the public role." That means everyone who can log in to the SQL Server and has access to that database can call the stored procedure. So, you have absolutely no reason what-so-ever for using sa.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Colin Angus Mackay wrote:
An exception gives information about what went wrong, What does it say?
It's an SqlException saying that 'dbo.a_procedure' does not exist in database 'a_database', where a_procedure is a stored procedure and a_database is the database.
If i call sp_helptext 'a_procedure' from the Query Analizer then it works just fine and prints the text as expected. That's why i was so confused that i even thought about using 'sa'.
Thank you for the reply.
"though nothing
will keep us together
we can beat them
for ever and ever"
rechi
|
|
|
|
|
I believe sp_helptext is in the master database so it needs the full namespace to the stored procedure. Try executing it with the prefix "master.dbo.sp_helptext" like so.
EXEC master.dbo.sp_helptext yourstoredprocedure
If this doesn't work then step two is to type in the servername before "master".
If that doesn't work, then also try
EXEC master.dbo.sp_helptext yourdatabase.yourdatabaseowner.yourstoredprocedure
The first one should be all that's needed, but if it doesn't then try the other two.
|
|
|
|
|
I'm not exactly sure whether what tojamismis said is enough. sp_helptext reads syscomments table of the particular database.
AFAIK, first you need to give ASPNET permission to execute the stored procedure itself. If it's not enough, try giving it read access to the syscomments table of the database.
HTH!
Edbert P.
Sydney, Australia.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
