Tuesday, March 27, 2012
delete builtin\administrators
I delete the login buildin\administrator and now I can't use anymore SQL
Server Enterprise Manager.
Can somebody tell how to valid an other user or haw to recreate the account
buildin\adminstrators ?
I still have sa.
Thanks in advance
rmeuser@.free.frHi,
IN Enterprise manager, Right click above the server name and remove
registration. After that register the server again with SA user.
Thanks
Hari
MCDBA
"Ralf Meuser" <rmeuser@.free.fr> wrote in message
news:4030c1c4$0$20229$626a14ce@.news.free.fr...
> Hi there
> I delete the login buildin\administrator and now I can't use anymore SQL
> Server Enterprise Manager.
> Can somebody tell how to valid an other user or haw to recreate the
account
> buildin\adminstrators ?
> I still have sa.
> Thanks in advance
> rmeuser@.free.fr
>
Delete an excel worksheet only if it exists, in a dts package
1. I import data into sqlserver, from an excel file, from a worksheet
within it, named 'input_data'
2. then i scrub the data in sqlserver
3. then i export some data into the same excelfile but into another
worksheet named 'output_data'
I have got it working and this is how i am doing it right now. I am
having a problem in the few steps identified below :
1. I delete the worksheet 'output_data' using an execsql task
DROP TABLE `output_data'
2. Then i have another execsql task to create it
CREATE TABLE `output_data`
3. Then i have datapump task to put data into worksheet.
The work fine, but i am running into a problem, when the output_data
worksheet doesnt exist.
So i need to write a query telling
IF `output_data` doesnt exist, then DROP IT
How do i do this
I am open to doing this in ActiveX Script(vbscript) and ExecSQL task
Also i do not have excel dll or runtime in my dbserver.
Please let me know
thanks
RSapologize for typo
I do have a problem, when 'output_data' worksheet does exist
and hence need a query telling,
if output_data worksheet exists, then drop it
thank you
RS
Wednesday, March 21, 2012
Delay in running queries
I have a problem in running queries.
I developed an application uses sqlserver 2005 express edition
I create all queries in storedprocedures.every things work perfect but some times I get long delay in running queries. but after some minutes it comes regular . I coudlnt find any relation between delay and time of work.it comes by chance . also I set timeout for running query for 30 sec. but some times it took more than minutes.
what should I do?
It sounds like you are experience 'blocking' and 'locking' behavior. If you post the complete stored procedure code, we 'may' be able to help you.
And you could also use Profiler to determine what procedures and/or statements in the procedures are causing the blocks.
|||my data base has so many procudures and this problem may happend in running each one . one simple of them is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[STPListStock]
AS
SELECT GoodSyscode, Title, Code, BarCode, Type, FstUnit, SecUnit, UnitRate, OrderPoint, SalePrice1, SalePrice2, SalePrice3, SalePrice4, SalePrice5,
VisitorPer, Comment, DiscontPer, UserPrice, GroupName1, GroupName2, Term, Weight, SerialNo, TypeId, GroupId1, GroupId2
FROM VWCompleteGood
|||try this
EXEC sp_dboption '<db_name>', 'autoclose', 'false';
Monday, March 19, 2012
Defragmenting Local Disk
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy
Defragment disk
Is it acceptable practice to run Disk Defragmenter on a Disk when SQLServer
is installed and running?
(using Win2000, SQLServer 2000)
ThanksSure, as long as you exclude the SQL Server database files (perhaps the tool
will not even try on open files). If it is a dedicated SQL Server and you
don't overuse autogrow, then there shouldn't really be any need for this.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:eQ0ykPfoDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is it acceptable practice to run Disk Defragmenter on a Disk when
SQLServer
> is installed and running?
> (using Win2000, SQLServer 2000)
> Thanks
>|||How does one exclude these from the defragmenter?
I guess what I need to know is will anything bad happen if the data & log
files are defragmented? Is it best to switch off SQLServer first?|||> How does one exclude these from the defragmenter?
You have to check that with your defrag tool. If you are using the built-in
defrag tool, I suggest you check this in a Windows group.
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
Probably, until you got info from the defrag tool vendor that the tool does
not touch locked files. SQL Server does not itself have a problem with the
files being defragged, but you don't want the risk of having two tools
working against the same file(s) at the same time.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> How does one exclude these from the defragmenter?
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
>|||There is no requirement that SQL database devices be excluded from being
defragmented. There is also no requirement that SQL services first be
stopped prior to defragmenting. Microsoft's defrag APIs fully support
defragmenting SQL database devices online.
- Greg/Raxco Software
Microsoft MVP - Windows File System
Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
commercial defrag utility, as a systems engineer in the support department.
Want to email me? Delete ntloader.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > How does one exclude these from the defragmenter?
> You have to check that with your defrag tool. If you are using the
built-in
> defrag tool, I suggest you check this in a Windows group.
>
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> Probably, until you got info from the defrag tool vendor that the tool
does
> not touch locked files. SQL Server does not itself have a problem with the
> files being defragged, but you don't want the risk of having two tools
> working against the same file(s) at the same time.
> --
> Tibor Karaszi
>
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > How does one exclude these from the defragmenter?
> >
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> >
> >
>|||Thanks for the info, Greg! Good to know.
I was trying to get that kind of info from another defrag manufacturer, but
never got a response to my question. Hence my careful approach. :-)
--
Tibor Karaszi
"Greg Hayes/Raxco Software" <ghayesntloader@.raxco.com> wrote in message
news:%23EXhy9uoDHA.2216@.TK2MSFTNGP12.phx.gbl...
> There is no requirement that SQL database devices be excluded from being
> defragmented. There is also no requirement that SQL services first be
> stopped prior to defragmenting. Microsoft's defrag APIs fully support
> defragmenting SQL database devices online.
> - Greg/Raxco Software
> Microsoft MVP - Windows File System
> Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
> commercial defrag utility, as a systems engineer in the support
department.
> Want to email me? Delete ntloader.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > > How does one exclude these from the defragmenter?
> >
> > You have to check that with your defrag tool. If you are using the
> built-in
> > defrag tool, I suggest you check this in a Windows group.
> >
> >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> >
> > Probably, until you got info from the defrag tool vendor that the tool
> does
> > not touch locked files. SQL Server does not itself have a problem with
the
> > files being defragged, but you don't want the risk of having two tools
> > working against the same file(s) at the same time.
> > --
> > Tibor Karaszi
> >
> >
> > "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> > news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > > How does one exclude these from the defragmenter?
> > >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> > >
> > >
> >
> >
>|||Thanks everyone...
Griff
Sunday, March 11, 2012
defrag SQLServer
Is it safe to defrag SQL server? What's the best utility for this if any?
thanks,
joel"Joel Gacosta" <joel@.bluemediacomm.com> wrote in message
news:#J#AnMWpDHA.3612@.TK2MSFTNGP11.phx.gbl...
> Is it safe to defrag SQL server? What's the best utility for this if any?
>
If you are running a file level defragmenter, then you should have no
issues. If the disk is severely fragmented then the first time it's run, it
would not be a bad idea to stop the MSSQLServer service as this would close
the open database files and allow them to be de-fragmented. As far as
products, we use Diskkeeper on all of our servers, no issues with this
product...
Steve|||Hi Joel
Take a look at this whitepaper which contains almost everything you'll ever
need to know about SQL Server fragmentation:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
ol/sql/maintain/optimize/ss2kidbp.asp
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Joel Gacosta" <joel@.bluemediacomm.com> wrote in message
news:#J#AnMWpDHA.3612@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Is it safe to defrag SQL server? What's the best utility for this if any?
> thanks,
> joel
>|||There is nothing called Defragmenting SQL Server.
I think you are asking about DeFragmenting SQL Server
Database objects. If so,then "YES".
As part of database maintenance to improve performance,
you have to regularly DEFRAGMENT the Fragmented objects.
Table fragmentation occurs through the process of data
modifications (INSERT, UPDATE, and DELETE statements) made
against the table. Because these modifications are not
usually distributed equally among the rows of the table,
the fullness of each page can vary over time.
"DBCC SHOWCONTIG" , Displays fragmentation information for
the data and indexes of the specified table.
When an index is heavily fragmented, there are two choices
for reducing fragmentation:
1. Drop and re-create a clustered index.
2. You can defragment the indexes using "DBCC INDEXDEFRAG"
Also,If you want to shrink the databases, you can do using
SQL Server maintenance plans or in the database options.
Hope this helps.
-SQLVarad(MCDBA-1999,MCSE-1999)
>--Original Message--
>Hi All,
>Is it safe to defrag SQL server? What's the best utility
for this if any?
>thanks,
>joel
>.
>|||Some of what SQLVarad said below is incorrect.
> As part of database maintenance to improve performance,
> you have to regularly DEFRAGMENT the Fragmented objects.
You should only defragment indexes if you have empirical evidence that
increasing fragmentation is causing decreasing performance.
> When an index is heavily fragmented, there are two choices
> for reducing fragmentation:
> 1. Drop and re-create a clustered index.
> 2. You can defragment the indexes using "DBCC INDEXDEFRAG"
To reduce fragmentation in an index (clustered or non-clustered) there are 3
options:
1) run DBCC INDEXDEFRAG
2) run DBCC DBREINDEX
3) manually drop and re-create the index
3) is just a manual version of 2). Read the whitepaper Kalen referenced for
a description of these and when to use them.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message
news:040e01c3a568$665b09d0$a101280a@.phx.gbl...
> There is nothing called Defragmenting SQL Server.
> I think you are asking about DeFragmenting SQL Server
> Database objects. If so,then "YES".
> As part of database maintenance to improve performance,
> you have to regularly DEFRAGMENT the Fragmented objects.
> Table fragmentation occurs through the process of data
> modifications (INSERT, UPDATE, and DELETE statements) made
> against the table. Because these modifications are not
> usually distributed equally among the rows of the table,
> the fullness of each page can vary over time.
> "DBCC SHOWCONTIG" , Displays fragmentation information for
> the data and indexes of the specified table.
> When an index is heavily fragmented, there are two choices
> for reducing fragmentation:
> 1. Drop and re-create a clustered index.
> 2. You can defragment the indexes using "DBCC INDEXDEFRAG"
> Also,If you want to shrink the databases, you can do using
> SQL Server maintenance plans or in the database options.
> Hope this helps.
> -SQLVarad(MCDBA-1999,MCSE-1999)
> >--Original Message--
> >Hi All,
> >
> >Is it safe to defrag SQL server? What's the best utility
> for this if any?
> >
> >thanks,
> >joel
> >
> >.
> >
Defrag file problem
We have a 24GB data file with 40GB free space. However even if we stop SQL
server and run the Microsoft Defragmenter it says it cannot defrag the file.
Would I be correct in thinking that if we backed up the database, deleted
and recreated the database making it's starting size 24GB, then restored the
backup file, this would create an unfragmented file.
Any other suggestions?
Thanks,
Adrian.
Adrian
try contig.exe from sysinternals.
www.sysinternals.com
It gives you the option to defrag single files and I've used it
succesfully in the past even on open database files.
Markus
|||Markus,
Thanks. That's a useful link. I will let you know how I get on.
Adrian
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1162998868.542722.265490@.i42g2000cwa.googlegr oups.com...
> Adrian
> try contig.exe from sysinternals.
> www.sysinternals.com
> It gives you the option to defrag single files and I've used it
> succesfully in the past even on open database files.
> Markus
>
Defrag file problem
We have a 24GB data file with 40GB free space. However even if we stop SQL
server and run the Microsoft Defragmenter it says it cannot defrag the file.
Would I be correct in thinking that if we backed up the database, deleted
and recreated the database making it's starting size 24GB, then restored the
backup file, this would create an unfragmented file.
Any other suggestions?
Thanks,
Adrian.Adrian
try contig.exe from sysinternals.
www.sysinternals.com
It gives you the option to defrag single files and I've used it
succesfully in the past even on open database files.
Markus|||Markus,
Thanks. That's a useful link. I will let you know how I get on.
Adrian
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1162998868.542722.265490@.i42g2000cwa.googlegroups.com...
> Adrian
> try contig.exe from sysinternals.
> www.sysinternals.com
> It gives you the option to defrag single files and I've used it
> succesfully in the past even on open database files.
> Markus
>
Wednesday, March 7, 2012
Define Job using SQL DMO
I have to define a new job, to take the backup automatically, at the SQL
Server 2000. on the Local Network if i login using the Machine name (Only
Default instance is installed), it works fine. But in the following scenario
s
it does not work -
-- If i login using IP Address of the machine on the local network.
-- If the Server is on other network on the internet.
I get the Folloing error-
[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @.server_name
('192.168.100.19') does not exist.
With Thanks and Regards,
LalitHi Lalit,
Can you be more specific in what situation you see the failure? Is it when
creating the job? Can you share the script with us then (of course, strip
out any confidential info)?
Regards,
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lalit" <Lalit@.discussions.microsoft.com> wrote in message
news:12AA128D-0919-457D-95E9-86FA861372CA@.microsoft.com...
> Hi Friends,
> I have to define a new job, to take the backup automatically, at the SQL
> Server 2000. on the Local Network if i login using the Machine name (Only
> Default instance is installed), it works fine. But in the following
> scenarios
> it does not work -
> -- If i login using IP Address of the machine on the local network.
> -- If the Server is on other network on the internet.
> I get the Folloing error-
> [Microsoft][ODBC SQL Server Driver][SQL Server]The specified @.server_name
> ('192.168.100.19') does not exist.
> With Thanks and Regards,
> Lalit
>|||Hi Ciprian,
Thanks for your reply.
I am doing the following
'Adding Schedule to Job
objJob.BeginAlter 'Begin changes to the job object
objJob.RemoveAllJobSchedules
objJob.RemoveAllJobSteps
'Setting JobSchedule Name
objJobSchedule.Name = "strJobName" & "_Schedule"
'Properties of Schedule
objJobSchedule.Schedule.FrequencyType = SQLDMOFreq_Daily
objJobSchedule.Schedule.FrequencyInterval = Val(txtDays.Text)
'Set Active Start date
strStartYear = DatePart("yyyy", Now)
strStartMonth = Format(DatePart("m", Now), "00")
strStartDay = Format(DatePart("d", Now), "00")
objJobSchedule.Schedule.ActiveStartDate = strStartYear & strStartMonth &
strStartDay
objJobSchedule.Schedule.ActiveStartTimeOfDay = intTime
objJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
objJob.JobSchedules.Add objJobSchedule
objJob.JobSchedules.Refresh
objjobStep.Name = "Take_DataBackup"
'Setting Properties of JobStep
objjobStep.DatabaseName = cboDatabaseName.Text
objjobStep.Server = cboBackupServer.Text
objjobStep.Command = "DatabaseBackup"
objjobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
objjobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
'Adding JobSteps to Job object
objJob.AddStepToJob objjobStep
'Commit changes to the job object
objJob.DoAlter
'Target the server to enable the job
objJob.ApplyToTargetServer "192.168.100.19"
objJob.Refresh
I Get the erro in the ApplyToTargetServer method when i am using IP address.
Where 192.168.100.19 is the IP address of the DB Server where i am connecte
d.
But the Error Message Says
[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @.server_name
('192.168.100.19') does not exist
If i am providing the name of the machine then no error occurs.
With regadrs,
Lalit
Sunday, February 19, 2012
Default value in Picture column in MSDE SQLServer ??
Can I set a default picture to be a default value in my picture column in my database ?
If so, how ?The data type would be Image not picture, and no it would not have a capability to have a default value.
default value for a parameter to select all
I'm using SQLServer 2005 and I build a report in the visual studio.
I need a way to set as default value for a multi-value parameter
its option of 'Select All'.
Thanks, Talia.
Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.
The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.
-- Robert
|||Thanks.
If somebody has another idea - I'll be happy to hear...
talia.
|||Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.
-- Robert
|||Hey, I think I found the way:
In the Default Values of the parameter, we must choose:
From Query
then, choose the same dataSet and the same ValueField of those
of the parameter - and it select all!
Talia.
Talia,
I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.
It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.
The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":
Define a dataset named "ValidStatuses" as follows:
-- begin dataset definition
DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)
INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')
SELECT * FROM @.tblValidStatuses
-- end of dataset definition
Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.
I hope this helps.
-NFox
|||
In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.
I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.
|||In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension
Thank you very much,
Viky V
default value for a parameter to select all
I'm using SQLServer 2005 and I build a report in the visual studio.
I need a way to set as default value for a multi-value parameter
its option of 'Select All'.
Thanks, Talia.
Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.
The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.
-- Robert
|||Thanks.
If somebody has another idea - I'll be happy to hear...
talia.
|||Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.
-- Robert
|||Hey, I think I found the way:
In the Default Values of the parameter, we must choose:
From Query
then, choose the same dataSet and the same ValueField of those
of the parameter - and it select all!
Talia.
Talia,
I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.
It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.
The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":
Define a dataset named "ValidStatuses" as follows:
-- begin dataset definition
DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)
INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')
SELECT * FROM @.tblValidStatuses
-- end of dataset definition
Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.
I hope this helps.
-NFox
|||In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.
I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.
|||In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension
Thank you very much,
Viky V
default value for a parameter to select all
I'm using SQLServer 2005 and I build a report in the visual studio.
I need a way to set as default value for a multi-value parameter
its option of 'Select All'.
Thanks, Talia.
Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.
The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.
-- Robert
|||Thanks.
If somebody has another idea - I'll be happy to hear...
talia.
|||Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.
-- Robert
|||Hey, I think I found the way:
In the Default Values of the parameter, we must choose:
From Query
then, choose the same dataSet and the same ValueField of those
of the parameter - and it select all!
Talia.
Talia,
I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.
It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.
The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":
Define a dataset named "ValidStatuses" as follows:
-- begin dataset definition
DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)
INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')
SELECT * FROM @.tblValidStatuses
-- end of dataset definition
Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.
I hope this helps.
-NFox
|||In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.
I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.
|||In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension
Thank you very much,
Viky V
Friday, February 17, 2012
Default SqlServer database creation path
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.
Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT
SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]
|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||
The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.
The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:
HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot
and
HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath
The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...
If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.
You can find the registry key for your SQL 2005 instance here:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>
...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>
Now go to:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup
to get SQLDataRoot and SQLPath...
then go to:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer
to get DefaultData and DefaultLog...
Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.
|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:
master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT
But fails for
master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures',
N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT
it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths
Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1
Default SqlServer database creation path
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.
Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT
SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]
|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||
The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.
The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:
HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot
and
HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath
The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...
If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.
You can find the registry key for your SQL 2005 instance here:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>
...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>
Now go to:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup
to get SQLDataRoot and SQLPath...
then go to:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer
to get DefaultData and DefaultLog...
Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.
|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT
But fails for
master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures', N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT
it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths
Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1
Default SqlServer database creation path
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.
Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT
SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]
|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||
The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.
The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:
HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot
and
HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath
The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...
If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.
You can find the registry key for your SQL 2005 instance here:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>
...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>
Now go to:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup
to get SQLDataRoot and SQLPath...
then go to:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer
to get DefaultData and DefaultLog...
Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.
|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:
master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT
But fails for
master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures',
N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT
it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths
Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1