|
Your'e network people are idiots (or have strict policies about what they can allow departments)  . You need some arguments that will move them off this policy position or get an exception. They will have some database they use and may allow you to use part of it, may be oracle or one of the other enterprise DBs.
Our network team for instance will not allow Access anywhere near a server, it has been known to crash a server and they are terrified of it. Unless properly managed it can grow horrendiously. Are you sure you do not need to install the jet drivers or even office on the server? This would be a good argument against Access.
Good luck.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello everyone,
I've a exe file in C:\. I need to run it from stored proc using xp_cmdshell. Please note that I'm using SQL Server 2000.
My stored proc is :
Create proc callExe
As
EXEC master.dbo.xp_cmdshell 'C:\PrintToPDFConsole.exe'
After executing this, the output is :
NULL
Unhandled Exception: System.ComponentModel.Win32Exception: No application is associated with the specified file for this operation
at PrintToPDF.Program.Main(String[] args)
NULL
and the exe file did not run.
Please help in this.
Also, I've tried with common exe file like 'notepad.exe' like:
ALTER proc callExe
As
EXEC master.dbo.xp_cmdshell 'notepad.exe'
When I executed this, the execution is going on and not showing any output. The execution time is more that 10 mins and going on...
Then I forcefully closed the isqlw.exe from Task Manager.
Is that mean exe file can't be executed in sql server 2000 using xp_cmdshell ?
--Krushna Chandra Sahu
prfkrushna@gmail.com
|
|
|
|
|
You cannot run exe which has GUI. You can run exe which is running in background process.
|
|
|
|
|
Yes, I forgot to write there that the exe file does not have a GUI.
It will automatically convert the any file to pdf file and save to My Document folder.
--Krushna Chandra Sahu
prfkrushna@gmail.com
|
|
|
|
|
Hello,
My Question is related to SQL Server Memory Usage,
I have a VB.net application with MSDE.
When i run my application, the sqlserver.exe in Task Manager uses memory of about 20 MB,
Then when I View any Crystal Report created report, the memory goes on increasing as the report is loaded and if reaches about 150 to 200 MB, but even after closing the report and also closing the application, SQL Server does not release memory.
So when we again start the application, again memory goes on accumulating but is never released.
Memory is released only when we Stop and start the SQL Server or restart the PC.
Can anyone tell me how to make SQL Server Release Memory from my VB.net application.
Thanks in Advance.
|
|
|
|
|
Hello,
sqlservr.exe gives back never the memory except when there is a memory pressure, that's to say when the operating system is needing memory for itself or for another application. For example, SQL Server is releasing memory when too much paging is occuring.
When the operating system is short of free memory, sqlserv.exe frees RAM memory ( K. Delaney neede about 20 pages in her book (Inside Microsoft SQL Server 2005 : the storage engine ) to explain the
management of the memory by SQL Server.
You may limit the amount of memory used by a sqlservr.exe ( there is 1 sqlservr.exe for each started instance of SQL Server ) thru the options Min server memory and Max server memory ( right-click on an instance , properties, tab memory ). For my 3 instances, i have limited Max server memory to 500 MB, so i am sure that never a sqlservr.exe will use more than 500 MB ( in fact, it is rare that the 3 instances are running, so i am sure that, with 2 GB of RAM , i am sure that the OS and applications other than sqlservr will have at least 500 MB of free memory.
You can't manage and free memory from your VB.net application.
But a connection to a server or database is one of the heaviest operation and i prefer to limit the number of connections by opening the connection once ( at the beginning of the application ) and closing the connection when exiting the application ( don't forget to close the connection in the exceptions treatement )
|
|
|
|
|
Hi
While installing SQL Server 2008, it asks for Service account and account name, pls click: [^]
Here, what are the meanings of options: "NT AUTHORITY\SYSTEM", "NT AUTHORITY\NETWORK SERVICE" etc..?
Are these registry keys or something like that. Where can i find information about this stuff?
I don't even know which subject they are related and where i must start to search about these.
Ihanks...
|
|
|
|
|
A simple table:
create table temptable(userid int,username varchar(100),score int)
Values inserted:
insert into temptable values(1,'BOB',17)
insert into temptable values(1,'BOB',21)
Test:
select * from temptable where userid =1
Result:
1,BOB,17
1,BOB,21
Returns 2 rows. Fine.
The requirement: Find number of times BOB has scored above 20.
Expected result
1,BOB,1
My attempt:
select userid,username,count(*) from temptable where userid=1 group by userid,username
Here how do I specify a condition for the "Count()"? It just need to count score>20 for BOB.
I'm a rock bottom sql noob. Any help?
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
Try:
select userid,username,count(*) from temptable where userid=1 and score>20 group by userid
|
|
|
|
|
ed5756 wrote: select userid,username,count(*) from temptable where userid=1 and score>20 group by userid
 imagine if BOB doesn't have any record that has score over 20. It won't return any result at all. May be I should have stressed that point. In this case, the result should be like :
1,BOB,0
So I'm clear with my question now?
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
OK, you want to return a 0 if there are no scores over 20. That is trickier.
This will return the count of Bob's scores over 20:
Select userid,username,count(*) as [count] from temptable where userid=1 and score>20 group by userid
This union query will return that same result and also a dummy record with a 0 count:
Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1
One would think then that using the MAX function in a sub-select would get your desired result:
select userid,username,max(ct) as
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1)
group by userid,username
But I find that in T-SQL for some reason it throws an error. However, if I first define a view based on that union, then this works:
select userid,username,max(ct) from view_c as Over20 group by userid, username
|
|
|
|
|
|
Sorry, there was a typo in my last post. That query should have read
select userid,username,max(ct) as Over20
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username union Select top 1 userid,username,0 as ct from temptable as b where userid=1) group by userid,username
I have run the 2-step solution using a view on SQL Server and it works.
|
|
|
|
|
Yeah thanks. Also the one provided by Jorgen is also cool.
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
Something like ?
select * from
(select userid,username from temptable where userid=1 group by userid,username)as a
inner join
(select userid, count as cnt from temptable where score>21
group by userid)as b on a.userid=b.userid
But this doesn't work too 
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
Got it almost!
select * from
(select userid,username from temptable where userid=1
group by userid,username)as a
left join (select userid, count(*)as cnt from temptable
where score>21 group by userid)as b on a.userid=b.userid
But this returns "NULL" instead of 0. How can I make it 0 ?
Can I use "CASE" on the count? Like:
CASE cnt when NULL then 0 END
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
How about:
SELECT DISTINCT userid
,username
,(SELECT Count(*) FROM temptable t2 WHERE t1.userid=t2.userid AND score > 20) as topscorecount
FROM temptable t1
WHERE userid = 1
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
select count(*),userid,username from template
where username ='Bob'and score > 20
group by userid,username
|
|
|
|
|
Check here[^]
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
select count(*),userid,username from temptable
where username ='Bob' and score > 20
group by userid,username
union
select 0,userid,username from temptable
where username ='Bob'and score < 20
group by userid,username
|
|
|
|
|
set nocount on
create table tempuser (userid int, username varchar(10))
create table tempscore(userid int, score int)
insert into tempuser (userid, username) values (1, 'Bob')
insert into tempscore (userid, score) values(1, 17)
insert into tempscore (userid, score) values(1, 21)
select u.userid, u.username,
coalesce(s.total, 0) as total
from tempuser u
left outer join
(
select userid, count(*) as total
from tempscore
where score > 20
group by userid
) s on s.userid = u.userid
drop table tempscore
drop table tempuser
|
|
|
|
|
Dear friend
I want to write a query that produced single row find the max form two table and also
query that delete all redundant data from table in sql 2005
Thanks and regards
Azad Yadav
|
|
|
|
|
azad yadav wrote: write a query that produced single row find the max form two table
you can get max for each table and then find max from two result . same as this code:
select max(twotable.fieldmatch) from
(
select max(field1) as fieldmatch from table1
UNION
select max(field1) as fieldmatch from table2
)twotable
azad yadav wrote: query that delete all redundant data from table in sql 2005
This is a general question , you must specify what you mean exactly.
|
|
|
|
|
one thing between two table should have delete cascading on master table .
then u can use
delete from mastertable where id = select top 1 id from (select max(id) as id from master a join detail b on a.id=b.id )as a
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
It will take a day for at least to understand. So you can be free till I come back