How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx
.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some oth
er filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by cre
ate a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you ne
ed to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the
DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear
. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports
Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
H
Hi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H
|||Thanks Hari
I'll give it a go!
Regards
H
|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H
|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
--
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

How do I make xp_cmdshell transactions run on the client instead of the server

Post title says it all. Any ideas? I asked this earlier but it seems to
have gotten lost in the shuffle.
Randall Arnoldxp_cmdshell runs OS level commands on the server -not the client. Using
xp_cmdshell to run executables on the client is not, in my experience,
something that you even want to attempt. (I'm not saying it can't be
done -just that it is too risky and very troublesome.)
However, if you are trying to have xp_cmdshell read the client computer's
file system, you could take the following steps.
Put xp_cmdshell into a Stored Procedure, use host_name to determine the
client computer and build the unc filepath.
You will be able to read the directory, and read files and write files to
the client computer. (Assuming you have appropriate permissions on the
client computer. Of course, you would NEVER be executing xp_cmdshell with
network admin priviledges, would you?)
While this can be done, the better question is why would you want to do it,
and what are the security ramifications?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:GGaog.32766$Nb2.601914@.news1.nokia.com...
> Post title says it all. Any ideas? I asked this earlier but it seems to
> have gotten lost in the shuffle.
> Randall Arnold
>|||I want to be able to periodically launch a VB script on the client PC.
Security is not an issue at all in this environment.
Randall
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:Ohd5kvfmGHA.4700@.TK2MSFTNGP02.phx.gbl...
> xp_cmdshell runs OS level commands on the server -not the client. Using
> xp_cmdshell to run executables on the client is not, in my experience,
> something that you even want to attempt. (I'm not saying it can't be
> done -just that it is too risky and very troublesome.)
> However, if you are trying to have xp_cmdshell read the client computer's
> file system, you could take the following steps.
> Put xp_cmdshell into a Stored Procedure, use host_name to determine the
> client computer and build the unc filepath.
> You will be able to read the directory, and read files and write files to
> the client computer. (Assuming you have appropriate permissions on the
> client computer. Of course, you would NEVER be executing xp_cmdshell with
> network admin priviledges, would you?)
> While this can be done, the better question is why would you want to do
> it, and what are the security ramifications?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:GGaog.32766$Nb2.601914@.news1.nokia.com...
>|||Security is always an issue :) My question would be do you absolutely have
to do this from within SQL Server? If not, I would create a Windows Service
to launch your script on a timer, or something to that effect.
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:fObog.32769$Nb2.601990@.news1.nokia.com...
>I want to be able to periodically launch a VB script on the client PC.
>Security is not an issue at all in this environment.
> Randall
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:Ohd5kvfmGHA.4700@.TK2MSFTNGP02.phx.gbl...
>|||I'm looking at any and all reasonable options to solving this need, working
several threads in parallel. One idea similar to this one but it requires
the symin role to have write/execute privileges on another server in a
certain folder but the IT guys here can't figure out how to give symin
that ability.
*sigh*
Randall
"Mike C#" <xyz@.xyz.com> wrote in message
news:OzgeLjgmGHA.3600@.TK2MSFTNGP02.phx.gbl...
> Security is always an issue :) My question would be do you absolutely
> have to do this from within SQL Server? If not, I would create a Windows
> Service to launch your script on a timer, or something to that effect.
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:fObog.32769$Nb2.601990@.news1.nokia.com...
>|||You are using SQL 2000 right? Honestly this task doesn't belong on SQL
Server. You can probably force the issue, but you'd be better off overall
if you made this a separate application that operated independently of SQL
Server. Is there some particular reason you feel you need to have it kick
off from inside SQL Server?
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:MsAog.33256$_k2.585855@.news2.nokia.com...
> I'm looking at any and all reasonable options to solving this need,
> working several threads in parallel. One idea similar to this one but it
> requires the symin role to have write/execute privileges on another
> server in a certain folder but the IT guys here can't figure out how to
> give symin that ability.
> *sigh*
>|||Well there's always VBScript, .BAT files and the dos command shell "at"
command :) I think someone else already mentioned sharing a directory on
the client and mapping a drive to it from the server. Another possibility
(note that I haven't actually tried this...) might be to install MSDE on the
client and run an SP via linked server that runs xp_cmdshell on the client.
Note again that I'm not even sure this would work, as I haven't tried it,
but it might be worth a try... I'd still recommend using a scripting
language of some sort to do the job, but if xp_cmdshell is what you want,
then you might give this a try.
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:ldDog.33266$_k2.585753@.news2.nokia.com...
> I'm trying to do as much as possible via SQL server because that's what we
> have and I lack the tools to "do it right" otherwise. I'd much rather be
> doing this in ASP.NET and deploying everything on the intranet the way it
> SHOULD be done. This facility will be closed by this time next year so
> I'm not exactly seeing people jump all over my resource requests. ; )
> But in the meantime, I still have this demand to deal with...
> Randall|||I've tried scripting, but can't get our IT guys to figure out how to give
symin write/execute privileges on a protected share (where the work needs
to take place and results stored).
Randall
"Mike C#" <xxx@.yyy.com> wrote in message news:jNEog.63$Ur7.47@.fe09.lga...
> Well there's always VBScript, .BAT files and the dos command shell "at"
> command :) I think someone else already mentioned sharing a directory on
> the client and mapping a drive to it from the server. Another possibility
> (note that I haven't actually tried this...) might be to install MSDE on
> the client and run an SP via linked server that runs xp_cmdshell on the
> client. Note again that I'm not even sure this would work, as I haven't
> tried it, but it might be worth a try... I'd still recommend using a
> scripting language of some sort to do the job, but if xp_cmdshell is what
> you want, then you might give this a try.
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:ldDog.33266$_k2.585753@.news2.nokia.com...
>|||If you don't mind, can I ask specifically what you're trying to accomplish?
Someone might be able to give you a better solution if we knew exactly what
you were trying to do. So far it sounds like you might be trying to read
some file(s) in, do some processing on them and write them back out? Or are
you trying to import or export data from SQL Server? Or maybe some
combination?
As for periodically executing a VBScript on a timer, the "at" command could
probably do the trick for you. As for setting share permissions, it all
depends on your network -- you might want to try one of the .networking, .vb
or .vbscript newsgroups (if you haven't already)
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:nyUog.32924$Nb2.605940@.news1.nokia.com...
> I've tried scripting, but can't get our IT guys to figure out how to give
> symin write/execute privileges on a protected share (where the work
> needs to take place and results stored).
> Randall|||I'm trying to take daily SQL queries and automatically build Powerpoint
presentations containing charts and tables representing data from those
queries (production performance/yield metrics). It's too much work for
people do be constantly burdened with, but I lack the proper tools and have
had trouble getting expenditures approved. This facility will be shuttered
by this time next year... but meanwhile work has to get out and we're told
we have to solve everything for the folks in Mexico who will be taking our
jobs.
Anyway, one poster here showed me how to get SQL Server 2000 Reporting
Services for free, so I've ordered that. I'll try to put management off
until it comes in.
As for the folder access, I have found that simply granting the
Adminstrators group on server A write/execute privileges on a folder on
server B solves my scripting problem. Just having a hard time getting IT
guys to do it.
Thanks for your interest.
Randall
"Mike C#" <xyz@.xyz.com> wrote in message
news:utHt9nEnGHA.3656@.TK2MSFTNGP03.phx.gbl...
> If you don't mind, can I ask specifically what you're trying to
> accomplish? Someone might be able to give you a better solution if we knew
> exactly what you were trying to do. So far it sounds like you might be
> trying to read some file(s) in, do some processing on them and write them
> back out? Or are you trying to import or export data from SQL Server? Or
> maybe some combination?
> As for periodically executing a VBScript on a timer, the "at" command
> could probably do the trick for you. As for setting share permissions, it
> all depends on your network -- you might want to try one of the
> .networking, .vb or .vbscript newsgroups (if you haven't already)
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:nyUog.32924$Nb2.605940@.news1.nokia.com...
>sql

how do I make 30 sec running query (select c1 sum(x) from t1 where c1 > 1000 group by c1) run

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.

Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?

Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?

Thanks in for you help in advance

if the memory available is not enough for internal operations like aggregation and ordering, SQL Server will implictly go to Tempdb and will store the results intermediately here. You cannot avoid is, beside putting more available RAM on the process. Don′t know why this slows down your process that much, did you had a look in the SQL Server logs, esprically on database growth ? Maybe SQL Server is increasing the data files one by one, leading to the problem that the query wioll be halted for the time needed to extend the database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How do I know if an index column is in descending order from SQL Server?

Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
>
> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>> Hi Peter
>>
>> EXECUTE sp_MShelpindex N'authors', N'aunmind'
>>
>> try this in PUBS
>>
>> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
>>
>>
>> --
>> kind regards
>> Greg O
>> Need to document your databases. Use the firs and still the best AGS SQL
>> Scribe
>> http://www.ag-software.com
>>
>>
>> "Peter Reid" <noreply@.microsoft.com> wrote in message
>> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>> How do I know if an index column is in descending order from SQL Server?
>>
>> I don't want to use "indexkey_property" as it doesn't work from another
>> DB.
>> I also know that I can do something like this:
>>
>> USE <db1>
>> select into a temp table
>>
>> USE <db2>
>> select into another temp table
>>
>>
>> But what I'm actually interested in knowing is where this information is
>> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
>> and furthermore how to query it.
>>
>> Thanks
>>
>>
>>
>>
>
>Hi Peter
Did you try:
EXECUTE pubs..sp_MShelpindex N'authors', N'aunmind'
John
"Peter Reid" wrote:
> Hey all that I want is to be able to run from another DB.
> This "sp_MShelpindex" like the "indexkey_property" only work running from
> the DB where the index sits.
> I need to join this information from multiple DBs in a single query, and I
> was wondering if (and how) this is possible.
> Thanks
> >
> > "GregO" <grego@.community.nospam> wrote in message
> > news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> >> Hi Peter
> >>
> >> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> >>
> >> try this in PUBS
> >>
> >> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
> >>
> >>
> >> --
> >> kind regards
> >> Greg O
> >> Need to document your databases. Use the firs and still the best AGS SQL
> >> Scribe
> >> http://www.ag-software.com
> >>
> >>
> >> "Peter Reid" <noreply@.microsoft.com> wrote in message
> >> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> >> How do I know if an index column is in descending order from SQL Server?
> >>
> >> I don't want to use "indexkey_property" as it doesn't work from another
> >> DB.
> >> I also know that I can do something like this:
> >>
> >> USE <db1>
> >> select into a temp table
> >>
> >> USE <db2>
> >> select into another temp table
> >>
> >>
> >> But what I'm actually interested in knowing is where this information is
> >> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> >> and furthermore how to query it.
> >>
> >> Thanks
> >>
> >>
> >>
> >>
> >
> >
>
>sql

How do i implement Multi value parameter

Hi..

I want to have a multivalue parameter in my report... I have selected that parameter and clicked on multivalue and then when i run the report.. it works fine for one and and not for more than one...

any help will be appreciated.

Regards

Karen

Karen, a question, how is your multivalue parameter used in your datasource? It should be :

SELECT *
FROM HELLO_DATA
WHERE COLUMN1 IN (@.MULTIVALUEPARAMETER1)

Hope this helps,

|||

thanks i figured it out and now it works fine.

Regards

Karen

sql

How do I get started talking to my webmatrix sql server

get a prompt up (in a program like telnet / msdos window) so that I can run sql commands on my webmatrix accounts sql server. I've been trying to connect using telnet but i think maybe I've got the wrong end of the stick or I'm doing something wrong.

here's a link where you click through "how to connect" on the "myserver page" I searched the page for "connect" and found 0 occurances.

http://europe.webmatrixhosting.net/text.aspx?tmpl=qa#3.7anyone please help,

the only way I know of to create table is through an sql prompt,

is there another way or can somebody give me some pointers cos i'm well stuck.|||Will webmatrixhosting let you use Enterprise Manager and Query Analyzer?|||i have windows XP pro, it doesn't come with it does it?

this isn't my main priority at the moment though,

trying to get an msde installed however is :

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=447274

http://www.gotdotnet.com/Community/MessageBoard/Thread.aspx?id=181961&Page=1#183199

then I might actually be able to start coding the ASP.NET I'm required to produced (pretty much by the end of the week)|||Enterprise Manager and Query Analyzer are "free" tools provided with SQL Server 2k.
Even when demo period is over, you can still use 'em.

Install MSDE.
Download SQL Server 2k.
Then just choose "SQL Server Tools only" during install.|||wicked!

I was looking for a trial on the ms site but couldn't find one b4 - didn't think they had one, downloading it now :)

How do I get SQL to run jobs automatically

Hi All,
We are putting in a SQL 2000 Server and my boss wants to know how we can run "Determine how SQL-Server runs jobs automatically". Any help or KB articles would be appreciated.
Thanks,
JohnSQL Server scheduled jobs are run by the SQl Server Agent. Check Books
OnLine for more info.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Chase" <johnc@.hcd.net> wrote in message
news:3A34B4A6-BF80-469C-8BEC-B2D284A97094@.microsoft.com...
> Hi All,
> We are putting in a SQL 2000 Server and my boss wants to know how we can
run "Determine how SQL-Server runs jobs automatically". Any help or KB
articles would be appreciated.
> Thanks,
> John|||There is a service that gets installed with SQL Server called the SQL Server
Agent. Once this service is started, you can go into Enterprise
Manager ->your server->Management->SQL Server Agent->Jobs and create a new
job, and schedule it. Look in Books on Line once you have it installed, or
search the KB for SQL Server Agent. That should get you started.
Jackie
"John Chase" <johnc@.hcd.net> wrote in message
news:3A34B4A6-BF80-469C-8BEC-B2D284A97094@.microsoft.com...
> Hi All,
> We are putting in a SQL 2000 Server and my boss wants to know how we can
run "Determine how SQL-Server runs jobs automatically". Any help or KB
articles would be appreciated.
> Thanks,
> John

How do I get Management Studio to not reset the selected database to Master when I open a new qu

Hi there,

I often have to run multiple SQL scripts against a single database. SQL Server Management Studio always reverts the selected database to Master whenever I open a different SQL file. This requires me to re-select the correct database, and if I forget, I end up running the script against the Master database. SQL 2000's query analyzer "remembered" your selected database and did not force you to re-connect each time.

Is there any way to make SQL Server Management Studio act this way?

Thanks!

Kiron

Kiron,

You could set the default database for the sql login. so that the default DB will be selected automatically.

To set default databasefor a sql login, In Object Explorer, expand Security -> Logins

right click on login , properties, set the default database

After setting default DB for the SQL login, if you open up new query with the above mentioned login, you would see the default database selected

If you feel that the above solution does not meet your requirements, Please log a feature request in connect web site

https://connect.microsoft.com/SQLServer/Feedback

Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||

Hi Sethu -

thanks for the response. Unfortunately, this will reset the default database, as opposed to having the new query session just "remember" the database that was selected for other open query sessions. Ideally, I would want the same behavior as in Query Analyzer from 2000 - you connect once to the database and from then on, all query windows are assumed to be related to the same database.

As part of my work, I often have to open multiple SQL scripts and execute them against the same database - the database is never the same, hence, the default database approach will merely shift the issue from "master" to some other database. What I would need is an option to tell Management Studio to pick the database that is used by all open query windows as the default when a new query window is opened vs. picking the user's default database.

I'll log a feature request as suggested!

Thanks again!

Kiron

How do I get Management Studio to not reset the selected database to Master when I open a ne

Hi there,

I often have to run multiple SQL scripts against a single database. SQL Server Management Studio always reverts the selected database to Master whenever I open a different SQL file. This requires me to re-select the correct database, and if I forget, I end up running the script against the Master database. SQL 2000's query analyzer "remembered" your selected database and did not force you to re-connect each time.

Is there any way to make SQL Server Management Studio act this way?

Thanks!

Kiron

Kiron,

You could set the default database for the sql login. so that the default DB will be selected automatically.

To set default databasefor a sql login, In Object Explorer, expand Security -> Logins

right click on login , properties, set the default database

After setting default DB for the SQL login, if you open up new query with the above mentioned login, you would see the default database selected

If you feel that the above solution does not meet your requirements, Please log a feature request in connect web site

https://connect.microsoft.com/SQLServer/Feedback

Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||

Hi Sethu -

thanks for the response. Unfortunately, this will reset the default database, as opposed to having the new query session just "remember" the database that was selected for other open query sessions. Ideally, I would want the same behavior as in Query Analyzer from 2000 - you connect once to the database and from then on, all query windows are assumed to be related to the same database.

As part of my work, I often have to open multiple SQL scripts and execute them against the same database - the database is never the same, hence, the default database approach will merely shift the issue from "master" to some other database. What I would need is an option to tell Management Studio to pick the database that is used by all open query windows as the default when a new query window is opened vs. picking the user's default database.

I'll log a feature request as suggested!

Thanks again!

Kiron

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005's taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I embed and retrieve Crystal Reports from my app

Using VB .Net 2005 with Crystal Reports XI Release 2. I have a form that has a CrystalReportViewer control on it. When someone prints I run a sub that create a new instance of the form, creates a new reportdocument, loads the report to the new document, then passes the document to the report viewer on the form. This works great except I would like to either embed my crystal reports directly into my app or better yet embed them into thier own reports dll file. I want to do this so updates are easier and I can use the "Publish" function of VB 05 which doesn't seem to like publishing all the seperate files. The embedding should be the easy part, just set them as embedded resource. But I'm having trouble with the syntax to pull them out. For example you can use this to pull out a embdded icon:
Function GetEmbeddedIcon(ByVal strName As String) As Icon
Return New Icon(System.Reflection.Assembly.GetExecutingAssembly.GetManifestResourceStream(strName))
End Function

But I cannot get the syntax right to pull out and return a crystal report. Anyone have any ideas on how to pull the reports back out or a better way to do this. I dont have a lot of reports, about 15 right now, but that could grow.

-AllanWell...I couldn't find the answer anywhere and no one replied here. But I figured it out so I might as well help someone down the road.

Set your crystal reports to "embedded resource" and the copy to "do not copy". Then in your code you can clal them like any other object with a "New (reportname)". For example I have a crystal reports viewer control (apptly named CrystalReportViewer) on a form called ReportViewerForm. I made a small sub to print reports:

Friend Sub PrintReports(ByVal myReportFileName As ReportDocument, Optional ByVal mySQLFormula As String = "")
' Prints out reports pass to it by other procedures and forms.
Dim PrintPreview As New ReportViewerForm
Try
If mySQLFormula <> "" Then myReportFileName.RecordSelectionFormula = mySQLFormula
PrintPreview.CrystalReportViewer.ReportSource = myReportFileName
PrintPreview.ShowDialog()
Catch ex As CrystalDecisions.CrystalReports.Engine.EngineException
MsgBox("A error was generated by the Crystal Reports engine. Error details: " & ex.Message, MsgBoxStyle.Critical, "Engine Error")
Finally
myReportFileName = Nothing
PrintPreview = Nothing
End Try
End Sub

Then when I need to print a report, for example my Log report (called LogEntryReport.rpt) I call it like this:

PrintReports(New LogEntryReport, "{qLogEntryReport.LogIdNumber} = " & CurrentLogId)

passing my report as a new ReportDocument and my sql command (which is optional for those reports that don't need it). Now I can have one routine that will display reports and call it from anywhere passing the report name. Works dandy and the reports are embedded in the app...less clutter.

Hopefully this helps someone and its not a waste of bandwidth.

-Allan.

How do I edit a query through code?

Hi!

Can someone tell me how or where I can find information on editind an SQL query through code?

I want to be able to run a user-defined lookup, where the user can choose what the query will look for.

Thanks in advance.

Guy

Your description is not very detailed. Do you want to create a dynamic query ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hi!

Thanks for the advice.

I was in a rush when I posted that, but I can provide some more detail now.

I want to make a query, where the user can change what the search criteria is.

I want the user to enter a string literal value from a text box or drop-down list, and then view the query with that criteria.

This needs to be done through code, and ideally work in Visual Web Developer as well as Visual Basic.

Thanks.

|||

check this

http://www.sommarskog.se/dynamic_sql.html

Madhu

|||

Hi.

Thanks for that link.

I will have a look when I have time.

Guy